Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-04-13 : 07:19:35
|
| Ken writes "I have a stored procedure which wraps a second stored procedure and substitutes a string for a numeric value returned by the first. It works great when I execute it via Enterprise Manager, Query Manager and Exel. When I try to execute it programatically via ADO, it not only doesn't return any rows of data, it doesn't return a row of headers. This, of course, causes the program doing the calling to die when I try to access any returned values.In the following code sample (written in perl), the progam ends when I try to execute the line marked by XXX on the left margin (($msg = $rsPages->fields("message")->{Value};). Following the calling code snippet, you'll find first the outer (wrapper) stored procedure and then the inner (wrapped) stored procedure.The stored procedure is run under SQLServer 2000 SP 3(version 8.00.818) on Windows 2000 Server, SP 4.Any help you could offer would be greatly appreciated.Ken################################################################## # FUNCTION: GetPagesADODirect()# # DESCRIPTION: This function invokes the stored procedure Pager_getFormattedPages# to gather the latest CIM pages. It then throws Chears alerts# based a combination of the elements returned by the stored# procedure.# # RETURNS: void# # DATE: 3-1-05# # BY: Ken LeFevre# # REVISED: # #################################################################sub GetPagesADODirect(){ my $custNo; my $divNo; my $entryTime; my $fullPageMsg; my $loc; my $msg; my $rsPages = CreateObject OLE "ADODB.Recordset"; my $DTFmt = Cardinal::DateTimeFormatter->new(); $rsPages->{ActiveConnection}=$Conn; $rsPages->{Source} = "Pager_getFormattedPages"; $rsPages->Open; ADOErrorCheck($Conn); while (!$rsPages->EOF) {XXX $msg = $rsPages->fields("message")->{Value}; $divNo = $EzDB->fields("dc_nbr")->{Value}; $custNo = $EzDB->fields("account_nbr")->{Value}; $loc = $EzDB->fields("location_name")->{Value}; $fullPageMsg = $DTFmt->n0w() . " $msg $divNo-$custNo: $loc"; ReportEvent("CardinalASSIST Message", $fullPageMsg, -9510, "TNG"); $rsPages->MoveNext; } $rsPages->Close;}Here's the code for the wrapper stored procedure:CREATE PROCEDURE Pager_getFormattedPages AS/******************************************************************************* * * Copyright 2005 Cardinal Health * ******************************************************************************* * Author: Rj Padgitt * Product: CardinalASSIST * Date: * Description: The stored procedure acts as a shell for returning records to NetIQ * for more stable monitoring of the data flow in CardinalASSIST. * * * Revised by: * Revision History: * Revision: R1 - * ******************************************************************************/ --create the temp table to hold all messages from the original paging stored procedure create table #messagesToSend (page_type int, location_key int, dc_nbr int, account_nbr varchar(6), location_name varchar(50), is_Active bit, start_time datetime, cutoff_time datetime, minutes_late int) --get all the messages to send insert into #messagesToSend exec Pager_getLocationToPage select case when page_type = 1 then ' : Orders not received from ' when page_type = 2 then ' : Order Confirmations not received for ' when page_type = 3 then ' : MSI Bill not created for ' when page_type = 4 then ' : cfgNike file not received fo |
|
|
|
|
|
|
|
nested stored procedures and ADO