Returning messages from a SQL Server extended procedure

[billdvldog]

Hello all, I am running the xp_cmdshell stored procedure from an asp page to execute a DTS package. The code works fine, but I'd like to know how to display the success or error message returned by the xp_cmdshell procedure to the screen. Basically I need to know how to capture it using VBScript. For example, if I run the procedure with an invalid value for the /G parameter, I get the following error message:

Error: -2147024809 (80070057); Provider Error: 0 (0) Error string: DTSRun: Must specify a value for switch: /G The parameter is incorrect. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 0

I'd just like to display that on the screen, or display a success message.

Thanks in advance!

Bill

 

[jemminger]

i usually use adodb.command object to execute stored procedures, and the return value will be parameter(0):

dim oCmd, returnval

set oCmd = server.createobject("adodb.command&quot
oCmd.activeconnection = oConn
oCmd.commandtext = "xp_cmdshell"
oCmd.commandtype = &H0004 'or adCmdStoredProc from adovbs.inc
oCmd.execute

returnval = oCmd.parameters(0)

set oCmd = nothing


=========================================================
try { succeed(); } catch(E) { tryAgain(); }
-jeff

http://www.jeffemminger.com

 

[billdvldog]

I tried it and it gives me an error:

Error Type:
ADODB.Connection (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

It then references the "returnval = oConn.Parameters(0)" line. I've included the relevant code below. It's probably in how I'm using the Connection object. I'm relatively new to VBScript.

Thanks!

Bill

  Set oConn = Server.CreateObject("ADODB.Connection")
  sConnString = "PROVIDER=SQLOLEDB; DATA SOURCE=" & servername & "; INITIAL CATALOG=" & database & "; UID=" & loginname & "; PWD=" & auth & ";"
  oConn.open(sConnString)

  Set oRS = Server.CreateObject("ADODB.Recordset")
  SQL = "EXEC debtscape..sp_dts " & servername & ", " & pkgname & ", " & filename
  oRS.Open SQL, oConn

  returnval = oConn.Parameters(0)
  repsonse.write(&quot;returnval: &quot; & returnval & &quot;<p>&quot;)

 

[jemminger]

hi Bill,

you must use the Command object.

Set oConn = Server.CreateObject("ADODB.Connection")
  sConnString = "PROVIDER=SQLOLEDB; DATA SOURCE=" & servername & "; INITIAL CATALOG=" & database & "; UID=" & loginname & "; PWD=" & auth & ";"
  oConn.open(sConnString)

  set oCmd = server.createobject("adodb.command")
  oCmd.activeconnection = oConn
  oCmd.commandtext = "debtscape..sp_dts"
  oCmd.commandtype = &H0004 'or adCmdStoredProc from adovbs.inc
  oCmd.parameters(1) = servername
  oCmd.parameters(2) = pkgname 
  oCmd.parameters(3) = filename
  oCmd.execute

  returnval = oCmd.parameters(0)
  response.write(&quot;returnval: &quot; & returnval & &quot;<p>&quot;)

=========================================================
try { succeed(); } catch(E) { tryAgain(); }
-jeff

http://www.jeffemminger.com

 

[billdvldog]

Duh, thanks Jeff. I appreciate the info, and as soon as I have a chance (probably tomorrow) I will test it out!

Thanks,

Bill