Hi there
I have a global variable say cnt in SSIS package, now I want to get total number of rows from a table say emp in that variable cnt.
how do we achieve that?
thanks and regards
Rahul Kuamr
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hi!Can anybody give me a hint how to put sa resut from EXEC into avariable.EXEC is called:EXEC(@TmpQuery) and it returns a single int value (SELECT COUNT(*)....)Thanks!Mario.
View 8 Replies View RelatedHello Again, did you miss me?
ok now i have this problem. I need to make a query that return one single data. I need to store this data in a variable. Something like that:
DECLARE @A VARCHAR(50)
@A=SELECT mycolumn FROM mytable WHERE mycolumn = 'Something'
There is a form to do it?
Thanks Friends
i need to know how many rows are in a certain table and store this value in a variable in order to process this variable later in the package. of course i can build a data flow task within a row count component but as far as i understand it's necessary to read all data from a data source in order to use the row count component. now the question is if it's possible to use a sql task in the control flow and put a select count(*) statement within the task and then write the result of this select statement into a variable. shouldn't that be much more faster than using a whole dataflow for this problem?
View 1 Replies View Related
Hi,
below is the sql statements for my web service using C#.
Code Block
string sql = "SELECT TOP 1 Pos FROM" + "TABLE1" +"ORDER BY Pos ASC"
SqlCommand comm = new SqlCommand(sql, conn);
Now if i want to set the Pos to a variable where i can call at another part of my program, how do i do that?
Hello,
I am interested in what the simplest was to get a query result that will only ever have one result (ie One column, one row) into a variable. An ugly way is to use a cursor that simply fetches the first row but that seems to be a horrible way to do it and it has sometimes major drawbacks sometimes (mainly if I have to dynamically choose the table). Surely there is a better way?
What do you think? A simple example would be nice.
Cheers
J
Hello All,
A select statement returns one column with more than one rows.Is there a way to store it in sql Variable without using a Cursor.
Thank you in advance
Hi this is probably a very stupid question, but I still need to know.
How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?
Is this possible as given below
declare @Qry as varchar(8000)
declare @Cnt as int
begin
set @Qry = 'select @Cnt=count(*) from ' + @TableName
exec @Qry
select @Cnt
end
But its not working....
can any one help me out in this.....
Thnx
Parag
I have looked thru several similar threads with errors like this, but have not found a resolution. I have a SQL Task that runs this query:
Code Snippet
select NetRevenue = cast(sum(Base_Price + AL + MI + PO) as dec(10,2))
from Lancelot.DataWhse.dbo.GrossMarginDetail
where tran_date_key <= ? + ' 23:59:59' and
label_group <> 'X' and
not (ar_ship_key in ('S999991','S998101''S998102')) and
Document <> 'Cust Bal Debit Memo'
There is a parameter mapping of "User::LastSaturday" of type date. I also have a result set with a result set name of "0" with a variable name of "User::GrossMargin," which is defined as a double in the package. The task has a resultset type of Single row, and at the moment the answer that is returned is 66228637.10.
If I change the package variable to a type of string it of course works, but then I cannot do comparisons against it. I have step by step manually copied this from an existing DTSX, so I am baffled why this isn't working, and I'm ready to throw myself under a truck!
I also tried to bring it in as a string, then convert it in a script task, but I cannot figure out how to reference the input and output variables. I can't seem to find any relevant docs on how to do that...
If anyone has any ideas, I and my sanity would greatly appreciate it.
Help - I am having a moment!
I am building a simple package that looks at the results of a sql query, prior to running the next step.
Basically Outline and settings
SQL task
Result Set: Full result set
SQL Source: Direct Input
SQL Statement: Select Count (*) FROM X
Parameter Mapping
Variable Name: User::C
Direction: Return Value
Data Type: Numeric
Parameter Name: NewParameterName
Result Set = NewResultName: User::C
Precedence Constraint
Evaluation Operation: Expression and Constraint
Value: Success
Expression: @C > 0 ( I originally tried User::C and received an error message)
Execute SQL Server Agent Job
If step one results greater than 1, then execute the SQL agent job (SRS Report)
Using this setup I receive an error message stating "
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value.
Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task".
(Microsoft.DataTransformationServices.VsIntegration)
Any thought or suggestions would be greatly appreciated.
r/ Anthony
hello
Does anybody know how to store the result of a select top 1 into a variable??
I have this code:
Select @status = Select top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc
And also this:
Select @status = top 1 status from venta where Origin ='Pedido Autos nuevos' order by fdate desc
But none of them work
Any ideas??
Thanks
Hi All,
I need to store the row count from two different servers (one 6.5 and one 7.0)
to compare by doing this:(in T-SQL)
declare @kount1 int, @kount2 int
exec master..xp_cmdshell 'isql -Sserver65 -d dbA -T -Q "select count(*) from tableA",no_output
exec master..xp_cmdshell 'isql -Sserver70 -d dbA -T -Q "select count(*) from tableA",no_output
How can I save the rowcount in @kount1, @kount2 respectively for comparison?
Appreciate your feedback.
David Nguyen
Is it possible to get the result from an EXEC(@sqlcommand) statement into a variable?
As part of a SQL loop, it is necessary for me to run an EXEC() command to process an SQL statement. I have succesfully implemented this, but have been unable to get the results from the EXEC() statement into a variable to allow this data to be inserted into a table. Is this possible?
For Example (I know this doesn't work, but it is effectively what I am trying to achieve):
select @result = EXEC(@sqlcommand)
I could then use the @result variable in an insert statement to update a table with the results from the EXEC command.
Any assistance would be greatly appreciated...
Regards,
Wayne
Good morning;
My Problem is :im my transaction i use insert code like the following :
" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"
In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.
" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "
I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.
Thinks.
The following query is failing when trying to apply the
MAX(field_x_order)
to the variable @max
Note the x is represented by the string variable @stri
declare @i int
declare @stri varchar(10)
declare @max int
set @i = 18
set @max = 0
while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
select @max = MAX(field_ + @stri + _ORDER) FROM table_name WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near the keyword 'IS'.
exec ('UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL')
end
I have also tried:
select MAX(field_ + @stri + _ORDER) INTO @max = FROM display_1a WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near '@max'.
and:
select @max = ('SELECT MAX(field_' + @stri + '_ORDER) FROM display_1a WHERE field_' + @stri + ' IS NOT NULL') -- error: Conversion failed when converting the varchar value 'SELECT MAX(field_19_ORDER) FROM display_1a WHERE field_19 IS NOT NULL' to data type int.
Thanks,
Lou
Lou
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure.
I hope i'm being clear.
Any help will be appreciated.
Here is an example of how i am finding my number
Employee is the name of my table and Number is the name of my column.
SELECT Max(Number)
FROM Employee
I have the following SQL. I need the results to be in the @NSTATUS variable. How do I do this?
DECLARE @HISTORY_ID INT,
@NSTATUS VARCHAR(20),
@IMPORT_DATE DATETIME,
@TSQL VARCHAR(8000);
SET @HISTORY_ID = 350721;
SET @TSQL = 'SELECT DM_IMPORT_STATUS FROM OPENQUERY(NGDEV2_LINK2, ''SELECT DM_IMPORT_STATUS from NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ''''' + CAST(@HISTORY_ID as nvarchar(30)) + ''''''')'
SELECT @TSQL, @HISTORY_ID;
EXEC (@TSQL) ;
Can I retrieve a result set from a sp into a variable within a Execute SQL Task?
View 23 Replies View RelatedHi!
Is it possible to set a query result (scalar) to scalar variable. I would like to set a qery result (SELECT COUNT(*) FROM MyTable) to a scalar variable:
DECLARE @temp int
SET @temp = query result...
Is it possible? I couldn't find the way to do that...
Hi,
Let's say that the query in my SQL Task returns a single integer number.
How can I put that single number in a variable?
Thank you.
Hellois there a variable that is available to me that contains the numberof rows contained in a dataset return from a database call?
have a class that runs a stored proc and returns a dataset/resultsetlooking to simply assign an integer this value if it is possible
i'm using (learning) vb.net and sql server
thanks in advance
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP
Hi:
Is there a way to assign a dynamic query result to a local variable?
declre @sqlString nvarchar(4000),
@minEventDate datetime,
@databaseName varchar(25)
selct @databaseName = 'customer_12345'
(actually, a cursor loop will assign the database name dynamically, here just to simplify the situation)
select @sqlString =
'select ' + @minEventDate + '= (select min(eventDate) from ' + @databaseName + '.dbo.tblABC)'
exec sql_executesql @sqlString
print '@minEventDate: ' + cast(@minEventDate as varchar(19))
Though the select min(eventDate) from customer_12345.dbo.tblABC
returns a date, ex. '02/01/2004 12:35 pm', however, the printed @minEventDate is always with Null value. It mean, the value was never correctly assigned to the local variable.
As an alternate way, I am using temp table to insert it with the query result and then assign to the local variable. Since I have many local variables to try to get the min, max, count for around 10 fields, perfer a way to direct assign to the local variable instead of 10 temp tables.
thanks
-D
Hi.
I am trying to store the column value to a variable from a distributed query.
The query is formed on the fly.
i need to accomplish something like this
declare @id int
declare @columnval varchar(50)
declare @query varchar(1024)
@Query = "select @columnval = Name from server.database.dbo.table where id ="+convert(varchar,@ID)
exec (@query)
print @Columnname
-MAK
I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated!
SELECT count(*) FROM hsi.itemdata a
JOIN hsi.keyitem105 b on a.itemnum = b.itemnum
JOIN hsi.keyitem106 c on a.itemnum = c.itemnum
JOIN hsi.keyitem108 d on a.itemnum = d.itemnum
WHERE a.itemtypegroupnum = 102
AND a.itemtypenum = 108
AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + "
AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + "
AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")
Hi!
I have an sproc that gives an xml result set. I want to save this to a file but by using ssis and script task. Now, I do exec usp_myProc in execute sql task and get the result as xml. (I can get it as result set too but niether has worked). Now I pass this variable to my script task User::XRset.
In the script task I create a file and then I want to write xml result of User::XRset to my file. I am at loss.
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
View 7 Replies View RelatedI have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
Declare @xml nvarchar(max)
EXEC @xml = StoredProc1 @Id
This doesn't work as expected as @xml doesn't get any value assigned or rather I would say
EXEC @xml = StoredProc1 @Id
outputs the entire xml whereas it should just save the xml in a variable.
I set up a connection to mysql using ADO.NET's ODBC Data Provider. And I'm running a simple query to return one table's maximum ID(int32 unsigned).But when I bind the result to a variable and excute task. It gives out error message: "An error occurred while assigning a value to variable "MaxAuditLogID": "Result binding by name "MaxID" is not supported for this connection type. " I tried to change the type of variable around but with no luck.
View 5 Replies View Related