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)

 

[JayKusch]

Give this a try ...

declare @SQL_Command VarChar(400)
declare @PackageName VarChar(20)

SET @PackageName = 'DTS_Import'

SET @SQL_Command = 
'EXEC @hr = sp_OAMethod @oPKG, ' 
+    'LoadFromSQLServer ("(local)", "", "", 256, , , , "' + @packagename + '"),  null'

EXEC (@SQL_Command)

Thanks

J. Kusch

 

[ESquared]

You'll need some extra single quotes in there, or use the QuoteName function if your string won't be longer than 256 chars.

 

[cyberbiker]

J

When I run the stored procedure I receive an error "Must declare the variable @hr"

Terry (cyberbiker)

 

[cyberbiker]

I should clarify that @hr is declared in the stored procedure already

Terry (cyberbiker)

 

[ESquared]

@hr in the context of the EXEC command is out of scope of the calling procedure. If you want to return results from dynamic SQL you might need to use sp_executeSQL with the OUTPUT keywords.

 

[cyberbiker]

Thanks I will look that up in BOL

I suspected the variable was out of scope but had no idea what to do about it

Terry (cyberbiker)

 

[SQLBill]

I'm curious....

Why are you doing this:

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

Isn't this the same and easier?

'EXEC sp_OAMethod @oPKG, ' 
+    'LoadFromSQLServer ("(local)", "", "", 256, , , , "' + @packagename + '"),  null'

-SQLBill

 

[ESquared]

SQLBill, there you go actually HELPING people instead of just tossing them a bone!

wink wink

... as in... way to go

 

[cyberbiker]

Well......
I was hoping to check for if @hr <> 0

Terry (cyberbiker)

 

[ESquared]

sp_executeSQL it is then

 

[cyberbiker]

never mind that last, sp_ExecuteSQL should return 0 if successful if I am reading BOL correctly

Terry (cyberbiker)

 

[cyberbiker]

Now I am getting syntax error near '(Local)'

I set the variable to the local server name on my laptop

set @WhichServer = '(local)' --@@servername

set @SQL_Command = 'EXEC sp_OAMethod @oPKG, ' + 'LoadFromSQLServer ("' + @WhichServer + '", "", "", 256, , , , "' + @packagename + '"), null'




exec sp_ExecuteSQL @SQL_Command, N'@hr int,@oPKG int, @WhichServer nvarchar, @packagename nvarchar'

I receive the same error if I replace the variable @WhichServer with the original (Local)

This should not be that difficult to master, but obviously I am missing something in BOL


Terry (cyberbiker)

 

[cyberbiker]

I am becoming totally frustrated at this point and feel like an idiot, but I cannot seem to find the problem

When I try to run sp_executeSQL I receive a syntax error for the server name.

I have included the full Stored Procedure.

If anybody can fiqure what I am doing wrong, I would appreciate it

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)


set @PackageName =
case @Pack
when 1 then 'DTS_Import1'
when 2 then 'DTS_Import2'
when 3 then 'DTS_Import3'
when 4 then 'DTS_Import4'
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

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

set @WhichServer = @@servername

set @SQL_Command = 'EXEC sp_OAMethod @oPKG, ' + 'LoadFromSQLServer ("' + @WhichServer + '", "", "", 256, , , , "' + @packagename + '"), null'

exec sp_ExecuteSQL @SQL_Command, N'@oPKG int ' --, @WhichServer nvarchar (255), @packagename nvarchar(30)'



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


Please note that I have seperate procedures written for each Package with the server hard coded and the package name also hardcoded which run well

Terry (cyberbiker)

 

[ESquared]

There are two methods I use to try to debug things like this.

1) Now that you've done all the work learning syntax for your statement, rebuild it from scratch. Do it one level at a time. For example, construct the correct final statement you want, complete with single quotes only around things that require single quotes. Then, take it to the next level, encapsulating it in the next "bigger" thing, turning every quote mark that ends up inside quotes into two quote marks. Rinse, repeat.

2) Use PRINT to display the actual SQL statement you've built to see if it is correct.

 

[SQLBill]

These are two places I see the server name being used:

set @SQL_Command = 'EXEC sp_OAMethod @oPKG, ' + 'LoadFromSQLServer ("' + @WhichServer + '", "", "", 256, , , , "' + @packagename + '"), null'

exec sp_ExecuteSQL @SQL_Command, N'@oPKG int ' --, @WhichServer nvarchar (255), @packagename nvarchar(30)'

One possibility of a problem is in the second one. Paste that command into Query Analyzer. Does the portion of the command after the -- turn green? If so, SQL Server thinks that's a comment and not runnable script.

Another possibility, can you run each of the above but replace @WhichServer with the server name (EXACTLY as returned by @@Servername). Sometimes you are required to have two slants (//) before a servername. @@servername won't return those two slants. For example if I want to use the four-part name in a script - it would be \\servername.database.owner.table - note the two slants at the beginning. If I used @@servername to get the first part, it would show up as servername.database.owner.table (note the slants are missing). This might be the problem.

-SQLBill

 

[ESquared]

Hey SQLBill, do the 2 slants indicate a network/netbios name as in UNC? Where can I read up on this.

 

[cyberbiker]

I hAve been tied up a bit, so have not had a chance to check everything out

--, @WhichServer nvarchar (255), @packagename is commented out.

I have taken the advice about using print and have some 'interesting' results.

I will post again Monday onceI've had a chance to work on this a bit and read up on things a bit more.

Terry (cyberbiker)

 

[SQLBill]

ESquared,

I don't know the answer to your question. I just know that every where I look in BOL, if they show the full name it's with two slants before the server name.

-SQLBill

 

[cyberbiker]

Please bear with me, but I am becoming more confused by the minute.

I now am receiving the error:

Server: Msg 245, Level 16, State 1, Procedure sp_ImportAll, Line 33
Syntax error converting the varchar value 'EXEC sp_OAMethod ' to a column of data type int.

Additionally, the "print '*****'+@SQL_Command" does not print.

I am not certain why. It was printing until I changed the Set @SQL_Command variable to the current line which was where I found my syntax error (Missing single quotes).


Line 33 is where I execute the sp_executeSQL.

Procedure 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 int


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

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

set @WhichServer = @@servername


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

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


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

exec sp_ExecuteSQL @SQL_Command, N'@oPKG int, @WhichServer nvarchar (255), @packagename nvarchar(30)'



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


Also, I do not think that I need the // in this case.
I am not totally certain of that, but based on experimentation I feel I do not.

I have individual sp's coded for each DTS package that work very well with the server hardcoded as either "(local)" or "TerryLaptop".

Terry (cyberbiker)