June 2, 2009 at 8:34 am
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 10:05 am
Hi Mark,
I notice that the error message ends just before the variable @suff is concatenated to the string @cmd. You will need to convert (or cast) @suff to type CHAR in order to add it to the @cmd string. I think this is where your error lies and not in the parameter passing.
regards
David
June 3, 2009 at 2:22 am
Hi David,
Many many thanks for your contribution. It led me to the answer although I still had some problems at first.
To cut a long story short, I converted my integer variable intSuffix to a character variable strSuffix and passed that to the stored procedure. It works brilliantly and reliably.
So, thanks again. If it wasn't for your suggestion, it would have taken me a lot longer to figure out.
Regards,
Mark
June 3, 2009 at 2:37 am
Hello again,
I've re-done it your way! It just seems more elegant and efficient to add the CAST to the construction of the @cmd variable, rather than parp around converting the value earlier in the code.
Cheers again!
Regards,
Mark
June 3, 2009 at 2:47 am
Glad to be of help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply