I have problem with this procedure pleace help!?!?

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'dtsrun /Sws_1/Usa/P123/Npar/ADynamicFileName:8'.




CREATE PROCEDURE param
(
@myVar char (50)
)
AS

DECLARE @@DtsRunText varchar(64), @@MyVar varchar(70)
SET @@MyVar='DynamicFileName:8+@myVar+'
SET @@DtsRunText = 'EXEC xp_cmdshell ''dtsrun /Sws_1/Usa/P123/Npar/A'+ @@MyVar + ''''

EXEC(@@DtsRunText)
GO


---
Posted using Wimdows.net NntpNews Component -

Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Several problems I can see. First of all I would add some print statements
to help you diagnose the problem.
I don't understand the use of @@ for variable declarations, and will there
not be some confusion between @myVar and @@MyVar?

@myVar char (50) can lead to a padded string which is probably not what you
want. "Test Var " vs "Test Var".


64 is too short for DECLARE @@DtsRunText varchar(64), so the command line
will get truncated.

SET @@MyVar='DynamicFileName:8+@myVar+' doesn't make sense at all. There
is a variable called @myVar but here it is just treated as part of the
literal, and it does not recognise the parameter.

The lack of any spaces between the parameters is invalid DTSRUN syntax.

Try this as a start point for what you need -

DECLARE @myVar varchar (50)
SET @myVar = 'asdsa'

DECLARE @DtsRunText varchar(1000)

SET @DtsRunText = 'EXEC xp_cmdshell ''dtsrun /Sws_1 /Usa /P123 /Npar /A
"DynamicFileName":"8"="'+ @MyVar + '"'''
PRINT @DtsRunText

EXEC(@DtsRunText)


--
Darren Green
http://www.sqldts.com

"SqlJunkies User" <User@-NOSPAM-SqlJunkies.com> wrote