Executing a stored procedure with a variable

• sam-885667

  Valued Member

  Points: 71

  More actions

  October 10, 2008 at 10:26 am

  #126035

  hi,

  I am trying to run a foreach loop that takes a rep number as it's input (from a ExecuteSQL task) and for each rep, i want to run a stored procedure that takes in the rep number and returns a recordset.

  I am having trouble using the variable. here is the script task snipit that is giving me trouble:

  Dim SAgentNumber As String

  SAgentNumber = Dts.Variables("AgentNumber").Value.ToString()

  Dts.Variables("StoredProcedureCall").Value = " exec Proc_InsertShipmentsForNAmericaReportForRepNumber " + Dts.Variables("AgentNumber").Value.ToString()

  Dts.Variables("DestinationPath").Value = String.Concat("\\\\usmck-r2w\\Report2Web\\enterprise\\ftpupload\\RouterWatch\\DW North America Agent Number-" + SAgentNumber, String.Concat(Date.Today.Year & "-", Month & "-", Day), ".xls")

  So I am trying to call my stored procedure and pass it a variable (the agentNumber) and it just isn't working.

  the error i get is:

  [Execute SQL Task] Error: Executing the query " exec Proc_InsertShipmentsForNAmericaReportForRepNumber AUTOEXEC.BAT" failed with the following error: "Incorrect syntax near '.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  I have tried looking at the ResultSet and it seems just fine.

  If someone can help me with this, it would be greatly appreciated.

  Thanks in advance.

  Sam

• sam-885667

  Valued Member

  Points: 71

  More actions

  October 10, 2008 at 11:45 am

  #883575

  ok I got it to work but not the way i want it to work. I don't get the error but the files are empty and b/c the variable going in are:

  boot.ini,

  AutoExec.Bat

  Config.Sys

  Dell.Sdr

  ....

  So i'll keep plugging...if anybody has any ideas...pls share.

  oh by the way, this is what i did to get it to stop erroring on me (if anybody is wondering...)

  keeping everything the same but the stored proc line...

  Dts.Variables("StoredProcedureCall").Value = " exec Proc_InsertShipmentsForNAmericaReportForRepNumber " + "'SAgentNumber'"

  thanks.