1
create PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20))
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
declare @sql1 VARCHAR(1000)
declare @counts numeric(4)
set @counts=0;
SET @sql1='SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT '+@counts+' = count(*) FROM '+@TableName+' where processed=0"';
EXEC MASTER..XP_CMDSHELL @sql1;
IF @ COUNTS>0
SET @SQL='SQLCMD -S ICB3110\SQLEXPRESS -d teena -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv';
EXEC MASTER..XP_CMDSHELL @SQL;
END;

Error occured is:

Msg 8115, Level 16, State 6, Procedure tablepass12, Line 9 Arithmetic overflow error converting varchar to data type numeric.

2

Using xp_cmdshell for this kind of tasks is a very bad idea in my opinion. However, assuming that you have a good reason to be doing things this way, this is a possible solution:

CREATE PROCEDURE [dbo].[tablepass12](@TableName nvarchar(20))
AS
BEGIN
    DECLARE @output TABLE (output nvarchar(max));
    DECLARE @SQL VARCHAR(8000);
    declare @sql1 VARCHAR(1000);
    declare @counts numeric(4);
    set @counts=0;
    SET @sql1='SQLCMD -S ICB3110\SQLEXPRESS -d teena -h -1 -E -Q "SET NOCOUNT ON; SELECT count(*) FROM '+@TableName+' where processed=0"';
    INSERT @output
    EXEC MASTER..XP_CMDSHELL @sql1;
    SELECT @counts = CAST([output] AS int) FROM @output WHERE [output] IS NOT NULL;

    IF @COUNTS>0
    BEGIN
        SET @SQL='SQLCMD -S SQLCLP01\SQL2012 -d tempdb -E -Q "SELECT * FROM '+@TableName+'" -o d:\processing\'+@TableName+'.csv';
        EXEC MASTER..XP_CMDSHELL @SQL;
    END
END;
| improve this answer | |
  • Thanks it works, i have to call this procedure from SSIS,so i use this SQLCMD – sachin thana Nov 20 '15 at 12:31
  • Might be better though to use EXISTS and return 1 or 0, rather than having to scan the entire table. You only care if there are zero rows or at least 1 row, so in the case where at least 1 row exists, short circuiting can be more efficient. – Aaron Bertrand Nov 20 '15 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.