Data Results To Variables
Feb 28, 2007
Hello,
I am using VWD, SQL EE, VB
I have a select statement with a varaiable in it (as below). Rather than displaying the results directly in a DetailsView I want to add the results to a set of variables (Dim XXX as String etc) so I can check to see if each variable contains data or not, before then displaying only those lines which contain data. Can anyone suggest how I go about linking each data field to a variable?
SELECT PID, Postcode, Address1, Address2, Address3, Address4, Address5, Town_City, County, Postcode AS Expr1
FROM postcode
WHERE (Postcode = @Postcode)
cheers
marco gwiliani
View 2 Replies
ADVERTISEMENT
Apr 18, 2001
I need to manage the number of rows affected by a query.
If I run this script on my database:
SELECT COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
(SELECT*
FROMERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2)
the query analyzer gives me back the number of lines affected:
-----------
4,00
(1 row(s) affected)
I need to manage this amount (4,00), so I applied this modification:
DECLARE @NUM_ROWS INT
SET @NUM_ROWS = (SELECT COUNT(*)
FROM TABLE_NAME
WHERE TABLE_NAME.FLAG = 'I'
AND NOT EXISTS
(SELECT*
FROMERR_TABLE_NAME
WHERE ERR_TABLE_NAME.FIELD_1 = TABLE_NAME.FIELD_1
AND ERR_TABLE_NAME.FIELD_2 = TABLE_NAME.FIELD_2)
)
but it doesn't work!! the SRV returns me this:
Server: Msg 107, Level 16, State 3, Line 3
The column prefix 'TABLE_NAME' does not match with a table name or alias name used in the query.
Do you know another way to store the number of rows affected?
View 2 Replies
View Related
Sep 7, 2007
I'm doing Derived Column transformations, using a Findstring expression to locate field seperators in a column, and then a Substring expression to move the string data at those field seperator locations into a new column.
At the moment I'm using two Derived column transformations, one to store the result of the Findstring in a new column, and then a second to actually move the data into a new column with the Substring.
Is it possible to assign the result of the Findstring expression to a variable, that I can then use in the Substring expression? This would allow me to do the whole thing in one transformation
View 3 Replies
View Related
Jul 13, 2004
Hi,
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like
"select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
This is what I've got so far...
[code]
cmdSelect = New SqlCommand( "GetProd", connstr )
cmdSelect.CommandType = CommandType.StoredProcedure
dbcon.Open()
dbcon.Close()
[/code]
The main prob is just what to connect this record to in order to access the individual fields.
Thx :)
View 2 Replies
View Related
May 28, 2008
Below is the code. It gives 2 errors.
1 @StartDate must be defined (it is a report parameter)
2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field)
Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5))
DECLARE @sowners nvarchar(5)
DECLARE @mydate datetime
DECLARE @sstatus nvarchar(10)
DECLARE @sjtype nvarchar(5)
DECLARE @chardate nvarchar(7)
DECLARE @Jmonth Int
DECLARE @Jyear Int
DECLARE @Jcharyear nvarchar(4)
DECLARE @Jcharmonth Nvarchar(2)
DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype
FROM Requirements
WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate))
OPEN JobCur
FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Jyear = (Year(@mydate))
SET @Jmonth = (Month(@mydate))
SET @Jcharyear = CAST(@Jyear,4)
SET @Jcharmonth = CAST(@Jmonth,2)
IF Len(Jcharmonth) = 1
BEGIN
SET Jcharmonth = '0' + Jcharmonth
END
IF @Jmonth >= 10
BEGIN
SET @chardate = @Jyear + '/' + @Jmonth
END
IF @Jmonth < 10
BEGIN
SET @chardate = @Jyear + '/0' + @Jmonth
END
INSERT INTO @t (Owner, cdate, status, jtype)
VALUES (@sowners,@chardate,@sstatus,@sjtype)
FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype
END
CLOSE jobcur
DEALLOCATE jobcur
BEGIN
SELECT * FROM @t
END
View 6 Replies
View Related
Apr 12, 2008
I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line
(@Tester1 integer,@Tester2 integer,@Tester3 integer,@Tester4 integer,@Tester5 integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen:
@Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.
View 2 Replies
View Related
Sep 1, 2015
I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory
As an example I have created a (simple) stored procedure:
USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
[Code] ....
What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)
The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"
The directory: D:SQLServerResults
View 9 Replies
View Related
Sep 21, 2015
If I have a stored procedure that returns 15 tables, how do I distinguish the tables to assign variables to each table in c#?
View 6 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Jan 14, 2008
I have a problem about calculating data from the table. I want to sent a row to the local variables and calculate them by C#. Thank you in advance for every suggestion.
View 3 Replies
View Related
Feb 2, 2007
I'm storing a sql command (could be an SP call or simple select) that I would like to pass into the "OLE DB Source" container in my data flow task. If I choose Data Access Mode: SQL Command from variable, I get an error saying there is a data source column with no name.
I've fought SSIS pretty good up to this point. I realize you need to "initialize" variables & expressions in the control flow ahead of time if you want to access them in the data flow. But I was kind of hoping that all I would need to do in this case would be pass a SQL command into a variable and access that somehow in the data flow without jumping through any hoops. Is this possible?
This is a great forum btw, thanks,
Phil
View 15 Replies
View Related
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
View Related
Oct 20, 2013
I need to move data from one table to another using variable percentages and business days.
Here is the basic idea:
Variables: varTable1, varTable2, varPercentage
Get varPercentage of rows of VarTable1 that have a date of "current business date -1" and place into varTable2.
View 1 Replies
View Related
Feb 28, 2007
Greetings SSIS friends,
I have tried using a sql command for my data reader source. I added the following expression to my datareader source :
"select * from result where result_id > " + @[max_result_id]
but I get the following error message :
The SQL command has not been set correctly. Check SQLCommand property.
I then got rid of the variable (defined at package level) and replaced it with a string like so :
"select * from result where result_id > " + "123456"
but I still get the same error message!
What am I doing wrong?!
Please advise.
View 20 Replies
View Related
Sep 8, 2007
how to pass the variables as the data source in data flow in for each loop( ADO enumerator)?
View 1 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Jul 20, 2005
Is there ANY data type other than varchar that is valid for localvariables and allows more than 8000 characters?
View 3 Replies
View Related
Jan 12, 2006
I have a package variable that I set via an ExecuteSQL task. I want to reference it in a data flow script component. In the Script component I enter the variable into the ReadOnlyVariables collection, then in the script I reference it as Me.Variables.var. (E.G. counter = Me.Variables.var)
I'm getting errors when the data flow starts:
Error: 0xC0047062 at Provider, Set Surrogate Key [4261]: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute.
I have no problem referencing other variables that I have in DerivedColumn transformations. I've tried putting the variable in the ReadWriteVariables collection but I get the same error. I don't understand why this is so difficult. Please help.
View 6 Replies
View Related
Apr 27, 2006
Hi,
I've read the various posts and articles regarding this matter, but I seem to have problems getting to work:
In my control flow, I start by declaring a variable named "LastJobLedgerEntryID", to identify the records I need to add to the stage. From there I would like to use this variable in the source component in my dataflow, i.e.:
"SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > " + @[User::LastJobLedgerEntryID]
But this fails? I should note that the variable LastJobLedgerEntryID is stored as a int32, and with the default value of 0
Could someone please help me with this?
Thanks in advance!
View 5 Replies
View Related
May 22, 2007
I am running a sql task which will pass table as object variable to the result set
I have a for each loop container which is used to loop for all the servers. I use two of the parameters to establish connection string in the for each loop task from the reasult set variables.
Now my next step is a data flow task (Data Reader Source) where i have to run a query but the table name and column names are dynamic and i dont see an option to call variables.
can someone tell me if this is possible (var1,var2,... are variables in the package scope)
select var1, var2 from var3.var4
where var5 = 'y'
View 9 Replies
View Related
Apr 7, 2008
I'm reading a Flat File.
The 1ste record containts special Info that is needed for first preparing the database on a buld insert of the remaining lines.
How could i realize this, just read 1 line, store some of that data into variables, execute some proc's and then read the rest of the file ?
Kind Regards.
View 4 Replies
View Related
Aug 22, 2007
Hi
I am after some help with the following issue
I have a package that reads a table for a file path of a excel file. This gets passed to a variable and then this file is imported into a staging table for further transformation work. The issue i have is that file 1 may contain data in Column A which is 50 characters long in which case i have to import the excel as a DT_WSTR, do a data conversion to a DT_STR and load to the staging table. However file 2 may contain data in Column which is over 255 characters in which case it would import as a DT_NTEXT which i then transform to a DT_TEXT and then to a DT_STR. I used a fixed file path in the Excel Connection to start with which was for File 1 so the datatype for column 1 is a DT_WSTR. I then changed the excel connection to a filepath variable, put the path of file 2 in my table and called it from my package. It failed as the data exceeding 255 characters in column 1 needed to be a DT_NTEXT. I can change it and it works but if i then run the package using file 1 (less than 255 characters) it fails again as it wants it to be a DT_WSTR.
Is there anyway around this? Am i missing something as i would have thought that by setting it to DT_NTEXT this would cover data under 255 characters as well.
Any help is gratefully aprreciated.
View 6 Replies
View Related
Mar 19, 2014
 "variables in SSIS for setting any connection string". I tried to Google it but cannot find any specific link.
View 4 Replies
View Related
May 1, 2008
Hi,
I have Variable , data source and conditional transformation which checks the count(*) if the count == 0 then I connect an script component and change variable to false(initial it is True) and write into a log file...
Then I check that variable on predence constarint at workflow if variable==True then success. BUT
Whenever I run the package my dataflow gets green even the condition does not meet like count==0 . So
my variable's value is "False". Actually if the condition doesnt meet then my script shouldnt work.
Am I missing something???
View 7 Replies
View Related
Jul 2, 2010
In my SSIS Data Flow Task, I have a query that retrieves data based on a couple of date parameters. Is there a way we can pass/use the Variables defined in the SSIS package in the query ?
(I am assigning values to those variables from C# code)
The query should look like this:
select ordernumber, customerid from salesorder
where statecode=3 and datefulfilled between @variable1 and @variable2
View 8 Replies
View Related
Mar 31, 2008
The logic I am trying to recreate via SSIS is the following SQL statement:
insert into db3.dbo.targettable1 -- Target database table
(SiteC,
Objecte,
Attrib1)
select distinct ?,
?,
from ? -- Source database table
join dbo.targettable2 c1 -- Target database table
on c1.Alias = ? and
c1.CSetID = ? and
c1.FacID = (select f.PFacID
from dbo.Fac f
where f.FacID = ?)
where not exists (select * from dbo.targettable2 c -- Target database table
where c.Alias = ? and
c.FacID = ? and
c.CSetID = ?)
I have an OLE DB Source that consists of an expression to approximate the following portion of the Above Select statement:
Select ?,
from ? -- Source database table and
The package has 2 global variables User:CSetID and User::FacID whose scope is global to the package and whose values are set within a Foreach Loop Container outside of the Data Flow Task
I was trying to reference the 2 global variables within the Looup Transformation to recreate the following portion of the SQL statement.but encounter errors:
join dbo.targettable2 c1 -- Target database table
on c1.Alias = ? and
c1.CSetID = ? and
c1.FacID = (select f.PFacID
from dbo.Fac f
where f.FacID = ?)
In the Advanced Editor window of Lookup Transaction
select * from
(select * from [dbo].[targettable2 ]) as refTable
where [refTable].[Alias] = ? and [refTable].[FacID] = ? and
[refTable].[CSetID] = ?
Is there away to reference global variables in a Lookup Transformation that are set outside a Data Task Flow?
View 3 Replies
View Related
Dec 10, 2003
Is there a way to get multiple rows of data to show in 1 row of results?
I have a Data Table (ID, Name, Date, etc), Facility Table (ID, Name) and FacilityKey Table (Data ID and Facility ID). The Data table has a 1 to many relationship with the FacilityKey table.
I want to return the Data ID and each Facility Name associated with that Data ID to display in a DataGrid (preferably each Facility Name in the same cell on the grid too), but am stumped on how to get teh query to work (or where to look in the SQL documentation for something like this).
Thanks!
View 5 Replies
View Related
Jun 28, 2006
Hello,
I am trying to use €œSQL Server Business Intelligence Development Studio€? (what is the short name for this) to complete a check and then possibly email me depending on what happens.
The check is around two tables that I have. There is a chance that they will become out of sync with each other and so I want to know when a field (ACCOUNTID) has a value that is in one table and not the other. I can do the check but I would like the package to email me when there is a problem (and if possible email me the details) and do nothing when everything is OK.
Can anybody give me some hints to point me in the correct direction as I just seem to be chasing my tail at the moment,
Thanks,
View 5 Replies
View Related
Apr 25, 2007
I am reposting this to a different forum, becasue I got not response...
Using VS05 SP1 Pro SQL Express€¦
Take a simple stored procedure like the following to return a specific column from a specific row in a data table ....
SELECT fld_IX_UserID
FROM UserIDs
WHERE (fld_UserID_Table_ID = @USERID_TABLE_ID)
It works fine in Store Procedure, and if you create at table adapter to reference it, that works fine as well.
Here is the issue. In the store procedure (i.e. under Server Explorer), when you execute the command to test it, it gives you the correct results. In Edit with Dataset Designer, Table Adapater, if you highlight it, right click preview data, it gives you incorrect results. In code, the table adapter gives you the correct resutls.
In every case, Preview Results for a table adapter built on a stored procedure will give you the wrong results. This is clearly a bug and can result in a log of wasted time.
Am I missing something? FYI, I realize I don't need to use the Table Adapater to execute the above stored procedure, but we are using table adapters for everything to be consistent.
Thanks,
Bob
View 3 Replies
View Related
May 9, 2006
Do data mining results store at some files or in the database? I need the mining results at later time for reporting purpose.
View 1 Replies
View Related
Jul 20, 2007
Can the results screen on the data tab be permanently hidden?
Thanks
BobP
View 2 Replies
View Related
Feb 28, 2007
Hello
I have come across a problem today when working with data sets/table adapter in vs05 for use in ASP.net.
SELECT ws_Agent.Telephone, ws_Agent.Email, ws_Agent.Name, ws_Agent.AgentId, ws_Agent.forename, ws_Agent.surname, ws_Agent.Acc_PcentreID, ws_ProfitCentre.ShortCode, ws_Branch.Name AS branchname, ws_Branch.ManagerId, ws_Branch.Fax AS branchFax, ws_Branch.Telephone AS branchtelephone, ws_Branch.Address1, ws_Branch.Address4, ws_Branch.Postcode, ws_ProfitCentre.Telephone AS PCentreTelephone, ws_ProfitCentre.Status AS PCentreStatus, ws_ProfitCentre.Fax AS PCentreFax, ws_ProfitCentre.Email AS PCentreEmail, ws_Agent.Status, ws_Agent.DisableAccount, ws_ProfitCentre.PCentreID, ws_Branch.Status AS BranchStatusFROM ws_Agent INNER JOIN ws_ProfitCentre ON ws_Agent.Acc_PcentreID = ws_ProfitCentre.PCentreID INNER JOIN ws_Branch ON ws_ProfitCentre.BranchID = ws_Branch.BranchIdWHERE (ws_Agent.Status <> 3) AND (ws_ProfitCentre.Status = 0) AND (ws_Agent.forename LIKE @forename) AND (ws_Agent.surname LIKE @surname ) AND (ws_ProfitCentre.ShortCode LIKE @profitcentreshortcode) AND (ws_Branch.Name LIKE @branchname )
The wildcard syntax works fine when tested in the query builder when passing in % to the parameters
, but does not work correctly NO RESULTS WHEN using preview data and in the subsequent use of data set in code.
Can anyone please comment on this or is there a setting I have missed?
Regards,
Matthew
View 2 Replies
View Related
May 16, 2007
Code:
-- (1) Number of calls received for each priority of call [for a specified date range]
declare @startdate datetime,
@finishdate datetime
select RM.fldPriorityCode as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
group by RM.fldPriorityCode
union
select
'Total' as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
order by RM.fldPriorityCode asc
Results:
PriorityCalls
120
22912
3152
4571
54
Total3659
I would like to transfer these results to an excel sheet. For instance when the user opens up the excel worksheet and types in for a example a start date: 01-01-2007 and an end date: 03-05-2007 (into textboxes) then clicks a button say called 'Get stats' and then the results appear on the sheet.
How can this be done?
View 1 Replies
View Related
