Post reply

T-SQL/stored procedure/dtsrun data type error when passing parameters

  • June 2, 2009 at 8:37 am

    #71525

    I'm having some fun with passing parameters from T-SQL to a stored procedure in SQL 2000 which then passes on the parameters on again to a DTS job (using dtsrun) using global variables which interrogates an Informix database.

    I'm happy the DTS job is working fine because I've tested it with default values in the DTS properties.

    Below is a simplified version of the T-SQL code I'm running:

    DECLARE @intSuffix smallint

    SET @intSuffix = 3

    EXEC xsp_xINVgetheader FT027030, @intSuffix, INV51230, mmcardle

    This returns the folllowing error:

    Syntax error converting the varchar value 'dtsrun /Sdata-warehouse /E /NxDOCinvheader /AgvSalesOrder:8=FT027030 /AgvSuffix:3=' to a column of data type smallint.

    And here is the dtsrun command and accompanying code in my stored procedure:

    CREATE PROCEDURE xsp_xINVgetheader

    @so CHAR(8),

    @suff SMALLINT,

    @inv CHAR(8),

    @user CHAR(20)

    AS

    SET NOCOUNT ON

    DECLARE @cmd VARCHAR(100)

    SET @cmd = 'dtsrun /Sdata-warehouse /E /NxDOCinvheader /AgvSalesOrder:8=' + @so + ' /AgvSuffix:3=' + @suff + ' /AgvInvoiceNo:8=' + @inv + ' /AgvUserName:8=' + @user

    EXECUTE master..xp_cmdshell @cmd, NO_OUTPUT

    SELECT [Sales Order No], [Invoice No], [Reference], [Invoice Date], [Customer A/C], [Customer Name], [Customer Order No], [Eligibility], [Release], [Application], [Currency], [Payment Terms], [Delivery Terms], [Contract No], [Total Goods], [Total VAT], [Grand Total]

    FROM xINVheader

    WHERE [Sales Order No] = @so AND [Delivery Suffix] = @suff AND [Invoice No] = @inv AND [Username] = @user

    GO

    I don't get why the error is a complaint about converting from varchar to smallint for the intSuffix variable, when it is declared as smallint in the T-SQL code, the stored procedure and the global variable in the DTS job.

    I'm assuming I've used the correct data typeid in the dtsrun command (3), although I've found nothing on the Internet or my books about these and only got the value 3 from seeing some code written by someone else.

    Any ideas out there?

    Cheers,

    Mark

  • June 2, 2009 at 9:02 am

    #1003449

    Please don't cross post. It just wastes peoples time and fragments replies.

    No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic727464-19-1.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 2, 2009 at 9:06 am

    #1003453

    Sorry!

    I wasn't sure which category it fell into.

    Lesson learned though!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply