<wizkid8586@gmail.com> wrote in message
news:1146148840.843160.157630@y43g2000cwc.googlegroups.com...
>I have read through many of these topic and still no luck with this:
> My situation is that I have a DTS package with a Execute SQL Task. The
> task executes a stored procedure which has one input and one output
> parameter:
> EXEC Update_SupplierParts @FileName, @Return = @Return OUTPUT
>
> My stored procedure looks like:
> ALTER PROCEDURE [dbo].[Update_SupplierParts]
> @FileName VarChar(255),
> @Return VarChar(255) OUTPUT
> AS
> .....
>
> Right now @FileName is hard coded into the SQL task; I would like it to
> use a global variable instead of a local variable. I currently have an
> ini file setup with a dynamic properties task to read in a few settings
> and store them in global variables.
>
> I would like to be able to do something like:
> EXEC Update_SupplierParts ?, @Return = @Return OUTPUT
>
> or even SET @FileName = ?
>
> I have found:
> EXEC sp_foo ?, ?, ?
> will work perfectly fine as long as I do not declare any local
> variables. But as soon as I declare a variable (even if it is not
> used) the task will not parse and return an error saying "Syntax error
> or access violation".
> I have tried the SET NOCOUNT ON mentioned before, with no luck. I am
> looking for a simple solution, preferably not having to deal with VB or
> ActiveX scripts, as the SQL task is very long and dynamic SQL with
> ActiveX would probably not be possible.
>
> Any ideas on how to fix this would be greatly appriciated!
> Thanks
>