Solved

Return a single value to a variable

Posted on 2006-07-07
2
826 Views
Last Modified: 2012-08-13
I'm trying to get a single value (row count) and write it to a text file.

I'm trying to get the row count value into the variable @spcount and then output it using this code:
set @cmd = 'master..xp_cmdshell "echo Record Count:' + @spcount + '>> d:\file.txt"'
exec (@cmd)

Unfortunately, I can't seem to pass the row count value via the @spcount variable to the file, I always get a value of 0

the code I'm trying to use to assign the value is:
exec @spcount=xdb..sp_executesql N'select count(*) from xtable'

another variation of that line:
exec xdb..sp_executesql N'select count(*) from xtable'

run by themselves, I get the correct output, but when trying to embed the results into the code above, again I get 0

any suggestions?



0
Comment
Question by:wppiexperts
  • 2
2 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17060614
this should work better:
select @spcount=count(*) from xtable
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 17060628
resp this:

exec xdb..sp_executesql N'select @res = count(*) from xtable',  N'@res int output'  , @spcount output
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 49
MS SQL tables, .net application and ETL connundrum 3 55
Sql server insert 13 29
Need some help to cast ntext to nvarchar SQL 2000 7 32
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question