none
Rowcount for a stored procedure

    Question

  • Hi,

    I have a stored procedure which runs a executable file using xp_cmdshell system SP. I would like to add some more code depending on the number of rows updated by the executable. IF I give @@rowcount after xp_cmdshell it always gives 1 irrespective of the number of rows it updates and even if doesnt update anything. Does any one know how to capture the rows affected from xp_cmdshell?

    Thanks,

    Preetha

    • Moved by SSISJoost Thursday, August 29, 2013 7:12 AM not ssis related, better chance of finding the answer at the tsql forum
    Thursday, August 29, 2013 2:23 AM

All replies

  • Kindly post your query here:

    http://social.msdn.microsoft.com/forums/en-us/home


    Balaji Kundalam

    Thursday, August 29, 2013 3:16 AM
  • What are you doing by using xp_cmdshell? Copy files? Create directory? Why using xp_cmdshell to update the rows?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, August 29, 2013 5:54 AM
    Answerer
  • What is the executable doing?

    You'll always see 1 row affected because xp_cmdshell is a command line utility stored procedure, it will only ever return the value given from the command line. In your case it returns "NULL".

    Thanks.


    Thursday, August 29, 2013 8:45 AM
  • Since you are acting on Windows command line Level, you will have to get the "row Count" from the command line.

    Whatever command you are using has to Output that.

    And this in return you would have to read (manually by extracting the string) from the Output...

    tricky, unreliable.. and really depending on the command


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Thursday, August 29, 2013 8:53 AM
  • Hi Preetha,

    I tested the following commands via SQL Server Management Studio and found @@ROWCOUNT will return the correct row count value.

    EXEC xp_cmdshell 'dir *.exe';
    GO
    select @@ROWCOUNT;

    If we query @@ROWCOUNT value directly, its value will be 1. With your question, I want to confirm what do you want to do with xp_cmdshell, if you tried to import the xp_cmdshell return data to a table, we can count the table rows to get the row count


    Allen Li
    TechNet Community Support

    Friday, August 30, 2013 8:16 AM
    Moderator
  • ...

    EXEC xp_cmdshell 'dir *.exe';
    GO
    select @@ROWCOUNT;

    If we query @@ROWCOUNT value directly, its value will be 1. With your question, I want to confirm what do you want to do with xp_cmdshell, if you tried to import the xp_cmdshell return data to a table, we can count the table rows to get the row count


    I am not sure, if this is what is supposed to be counted

    The rowcount will simply count all rows in the result set that is returned for SQL Server

    And this result set is basically comprised of every LINE that is returned on the command prompt

    I cannot imagine that lines like “NULL” or “Directory of C:\Windows\system32” would be important to count.

    Again: “Since you are acting on Windows command line Level, you will have to get the "row Count" from the command line.”-Programm/Executable to be more accurate. SQL Server cannot securely differentiate between “printed information” and “real results” returned from another executable and "count" on this level.

    Of course one could Count just every line, Import that into a temporary table and do a filtering with some logic... if the results are stable this will work.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Friday, August 30, 2013 8:26 AM