syntax error that I cannot find 1

[cyberbiker]

I am attempting to create a stored procedure that duns a DTS package according to an agument passed in.

This works fine

EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer ("(local)", "", "", 256, , , , "DTS_Import")', null

This gives me a syntax error at +
'LoadFromSQLServer ("(local)", "", "", 256, , , ,"' + @packagename + '")', null

Simply put, I want to pass in an argument (Integer value)

Then assign the package name according to that value and run one of several packages.

Can anybody see what is wrong with this syntax?

Terry (cyberbiker)

 

[SQLBill]

The first problem is with this line:

set @SQL_Command ='EXEC sp_OAMethod ' + @oPKG

In the declaration of the variables:

declare @hr int, @oPKG int, @PackageName varchar(30), @SQL_Command nvarchar(500),@WhichServer nvarchar(255),@ParamDef nvarchar(400), @Server nvarchar(255),
@package nvarchar(30),@PKG int

@oPKG is an INT. When SQL Server gets a + and one of the values to either side is an Integer; SQL Server interprets the + as ADD, not as concatenate. Solution is to CONVERT the INT value to a VARCHAR (or CHAR) value, which can be concatenated.

-SQLBill

 

[SQLBill]

On the non-printing error, it follows the command that is failing (the convert issue) so it won't process. There's nothing going into @SQL_Command at that point.

-SQLBill

 

[cyberbiker]

I truly appreciate all the help. I am learning (Just not certain what I am learning yet is all)

You are probably getting tired of me by now, but I am really stuck and cannot either find what I need or understand it if I am finding it but here goes again.

This time I receive:

first I print out my SQL Command

******EXEC sp_OAMethod 16711422, 'LoadFromSQLServer ("TERRYLAPTOP", "", "", 256, , , , "DTS_ImportInventory")', null

Then I receive this error.

Server: Msg 8178, Level 16, State 5, Line 0
Prepared statement '(@PKG nvarchar(30),
@WhichServer nvarchar(255), ' expects parameter @PKG, which was not supplied.

Apparently, If I understand this correctly, SP_OAMethod expects a integer as the first argument.

But, again if I understand this correctly, sp_executeSQL accepts nVarChar/VarChar/Char argument only.

There must be some way to do this that I am not finding.

If I attempt to convert @PKG back to an int after begining execution like this:


set @SQL_Command = N'EXEC sp_OAMethod cast(' + @PKG + ' as int), ' + char(39) + 'LoadFromSQLServer ("' + @WhichServer + '", "", "", 256, , , , "' + @package + '")' + char(39) +', null'

which prints as:

EXEC sp_OAMethod cast(16711422 as int), 'LoadFromSQLServer ("TERRYLAPTOP", "", "", 256, , , , "DTS_ImportOrders")', null

I receive Incorrect syntax near '16711422'. (Somewhat expected, but I needed to be certain)

Current code follows:

CREATE PROCEDURE [sp_ImportAll] @path varchar(255), @Pack Int as
declare @hr int, @oPKG int, @PackageName varchar(30), @SQL_Command nvarchar(500),@WhichServer nvarchar(255),@ParamDef nvarchar(400), @Server nvarchar(255),
@package nvarchar(30),@PKG varchar(30)


set @PackageName =
case @Pack
when 1 then 'DTS_ImportOrders'
when 2 then 'DTS_ImportInventory'
when 3 then 'DTS_ImportSalesman'
when 4 then 'DTS_ImportCustomer'
end

EXEC @hr = sp_OACreate 'DTS.Package', @oPKG out
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_OAGeterrorinfo @oPKG, @hr
RETURN
END

set @PKG = cast( @oPKG as nvarchar(30))
set @WhichServer = @@Servername
set @package = @PackageName

SET @ParamDef=N'@PKG nvarchar(30),
@WhichServer nvarchar(255),
@package nvarchar(30) '

print '***'+@PKG
Print '****'+@WhichServer
Print '***'+@Package


set @SQL_Command = 'EXEC sp_OAMethod ' + @PKG +', ' + char(39) + 'LoadFromSQLServer ("' + @WhichServer + '", "", "", 256, , , , "' + @package + '")' + char(39) +', null'

print '******'+@SQL_command


exec sp_ExecuteSQL @SQL_Command, @ParamDef

IF @hr <> 0
BEGIN
PRINT '*** ' + @packagename +' failed!'
EXEC sp_OAGeterrorinfo @oPKG, @hr
RETURN
END

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("sFileName").Value', @path

IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_OAGeterrorinfo @oPKG, @hr
RETURN
END

EXEC @hr = sp_OAMethod @PKG, 'Execute'

IF @hr <> 0
BEGIN
PRINT '*** Import operation failed unexpectedly'
EXEC sp_OAGeterrorinfo @oPKG , @hr
RETURN
END
GO


Terry (cyberbiker)

 

[SQLBill]

Nope, not getting tired of your questions.

Now the problem...

******EXEC sp_OAMethod 16711422, 'LoadFromSQLServer ("TERRYLAPTOP", "", "", 256, , , , "DTS_ImportInventory")', null

Then I receive this error.

Server: Msg 8178, Level 16, State 5, Line 0
Prepared statement '(@PKG nvarchar(30),
@WhichServer nvarchar(255), ' expects parameter @PKG, which was not supplied.

Apparently, If I understand this correctly, SP_OAMethod expects a integer as the first argument.

No, the error does NOT say it's expecting an integer. If it was, then it would be okay because you DID supply an integer.

16711422 is an integer
'16711422' is a char or varchar

The error message says it's expecting @PKG and in the initial declaration line:

),@PKG varchar(30)

@PKG is declared as a VARCHAR. However, here:

set @PKG = cast( @oPKG as nvarchar(30))

it's being used as an NVARCHAR.

So, which do you want @PKG to be: INT, VARCHAR, or NVARCHAR. I suggest making it a VARCHAR. Then when you 'test' and print the script it should look like this:

******EXEC  sp_OAMethod '16711422', 'LoadFromSQLServer ("TERRYLAPTOP", "", "", 256, , , , "DTS_ImportInventory")',  null

Remember, INT do not have quotes around them but strings do. Numbers (incl. INT) are added, strings are concatenated.

-SQLBill

 

[cyberbiker]

Bill,

I should give you about 23 stars for your patient help.

My final solution came to me about 2 seconds before I gave it up as a lost cause.

It may not be the best solution, but it works well and will satisfy all the requirements.

Even more importantly, I understand it well enough to do it again if I need to.

There are probably 55 posts or so that have helped me get this working, so thanks to everyone.

Oh yes, the // will need to be added to the server name for production I think


CREATE PROCEDURE [sp_ImportAll] @path varchar(255), @Pack Int as
declare @hr int, @oPKG int, @DTSString varchar(500), @WhichServer varchar(255), @Package varchar(100)

set @WhichServer = @@servername

EXEC @hr = sp_OACreate 'DTS.Package', @oPKG out
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_OAGeterrorinfo @oPKG, @hr
RETURN
END


--DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256

set @Package =
case @Pack
when 1 then 'DTS_Import1'
when 2 then 'DTS_Import2'
when 3 then 'DTS_Import3'
when 4 then 'DTS_Import4'
end

set @DTSString = 'LoadFromSQLServer ("' + @WhichServer + '", "", "", 256, , , , "' + @package + '")'
EXEC @hr= sp_OAMethod @oPKG, @DTSString,null



IF @hr <> 0
BEGIN
PRINT '*** Order Import failed'
EXEC sp_OAGeterrorinfo @oPKG, @hr
RETURN
END


IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_OAGeterrorinfo @oPKG, @hr
RETURN
END

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("sFileName").Value', @path
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_OAGeterrorinfo @oPKG, @hr
RETURN
END

EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Import operation failed unexpectedly'
EXEC sp_OAGeterrorinfo @oPKG , @hr
RETURN
END
GO


Terry (cyberbiker)

 

[ESquared]

Would you summarize for us what it is that you changed or fixed? It's a fairly big task to dissect this whole thread to extract that information, and maybe it would be helpful to someone else if it's stated clearly at the end.

 

[cyberbiker]

Ok,

I realized that my problem was that I was attempting to follow code examples that I did not fully understand. Always a bad idea, but I was becoming desperate.

Any way, I declared the variables properly.

CREATE PROCEDURE [sp_ImportAll]
@path varchar(255),--imput parameter containing the path to the text file which is later sent to a Global Variable created with the DTS Package

@Pack Int -- input parameter that indicates which package is being called from VB code

as

declare @hr int, @oPKG int,
@DTSString varchar(500), -- this is the argument needed by the sp_OAMethod (I will call it the connection string needed for want of knowing the proper terminology)

@WhichServer varchar(255), --server name set by @@servername

@Package varchar(100) --name of DTS package set by case statement depending on the second input argument.

This is the important change:

--- DTSSQLStgFlag_UseTrustedConnection = 256

set @DTSString = 'LoadFromSQLServer ("' + @WhichServer + '", "", "", 256, , , , "' + @package + '")'

EXEC @hr= sp_OAMethod @oPKG, @DTSString,null

I was being dumb and trying to concantenate 2 seperate arguments into a single string (Which I knew better, but the examples I found seemed to be doing that if I was following them correctly which I may not have been)


sp_OAMethod is fairly well documented in BOL and I was able to follow the explanation pretty well and realize what I needed to do.

I never did fiqure out why I was not able to use sp_executeSQL though.

The '//' pointed out by SQL Bill does not appear to be needed in this case (desktop) but I suspect will for the production version on a network. Just concantenate the // in front of the server name






Terry (cyberbiker)