August 27, 2009 at 4:26 am
HI
I need to put a row count in my SP but when I do it doesnt show how many rows there are it just shows the results of the first query.
How can I display the rowcount?
thanks
August 27, 2009 at 4:35 am
try playing around with SELECT @@ROWCOUNT
August 27, 2009 at 4:47 am
I have the code below.
I need the row count to display in the text file but it doesnt. I can see it in my query result.
How can I get it to display in the text file?
thanks so far.
ALTER PROCEDURE [dbo].[PROCEDURE]
AS
BEGIN
select field1,field2 from table
declare @sql varchar(8000)
select @@rowcount
select @sql = 'bcp "exec PROCEDURE" queryout C:\bcp\pro.txt -c -t, -T -S'+ @@servername
exec xp_cmdshell @sql
END
August 27, 2009 at 7:40 am
guestuser31 (8/27/2009)
I have the code below.I need the row count to display in the text file but it doesnt. I can see it in my query result.
How can I get it to display in the text file?
thanks so far.
ALTER PROCEDURE [dbo].[PROCEDURE]
AS
BEGIN
select field1,field2 from table
declare @sql varchar(8000)
select @@rowcount
select @sql = 'bcp "exec PROCEDURE" queryout C:\bcp\pro.txt -c -t, -T -S'+ @@servername
exec xp_cmdshell @sql
END
Is this meant to be self-referencing?
Basically, you need to SELECT @@rowcount immediately after the execution of whatever the query is doing in order to see it accurately. In the example above, I'd put it between the SELECT statement and the DECLARE statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 27, 2009 at 8:34 am
I don't think you can do this with the bcp command. You aren't running T-SQL here, you'd shelling out to a command prompt and then running a utility to pull data out.
I'm not sure bcp supports a row count (http://msdn.microsoft.com/en-us/library/ms162802.aspx)
What you could do is set up another proc, shell out to SQLCMD, run the proc, which then selects the same data, then selects @@rowcount, returns that value, append to the file.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply