MSSQL => Stored Procedure => Recordset

RealFairPlayer · 18. August 2011, 18:33

Hi!

Ich habe folgendes MS SQL Procedure

Quellcode

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [dbo].[Recipient_EnumerateVE] (
@Pending INT = 0,
@JobPrefixId INT = 101,
@JobId INT = 727,
@RecipientId INT = 0,
@FailedOnly INT = 0
)
AS
/* Description : Return list of recipients based on JobId and other supplied
* parameters. VENALI EXPRESS FLAVOUR
* In : @Pending - Return only recipients for jobs in a pending state
* @JobPrefixId - Identifier of gateway submitting job
* @JobId - Identifier of job whose recipients are to be returned
* @RecipientId - Identifier of recipient to return
* @FailedOnly - Return only those recipients with a completed
* status of failed.
* Out : Two recordsets:
* Summary Totals for job, listing successful, failed and
* pending recipients. Values returned are based only on
* the JobId and do not reflect effects of other parameters.
* Recipient List, return all recipients that meet the
* selection parameters provided.
* Notes : Based on the selection criteria, return the list of recipients for
* given job. Create summary totals are return those as well.
* History :
*
* Date Who Action Notes
* --------------------------------------------------------------------------
* 13/07/2011 MAS Created
*/
DECLARE @ReturnCode INT,
@RecordCount INT,
@JobSuccessCount INT,
@JobFailureCount INT,
@JobPendingCount INT,
@SystemJobId INT,
@MessageStringShort varchar(32),
@MessageStringLong varchar(128)
SET NOCOUNT ON
-- Logging: Start a System Job and log the SP parameters
SELECT @MessageStringShort = 'Recipient_EnumerateWork<' + convert(varchar, @JobPrefixId) + '-' + convert(varchar, @JobId) + '>'
EXEC @SystemJobId = [UTIL_BeginSystemJob]
@JobType = 3,
@JobTag = @MessageStringShort,
@OwnerJobPrefixId = @JobPrefixId,
@OwnerJobId = @JobId,
@OwnerRecipientId = @RecipientId
SELECT @MessageStringLong = 'Returning recipient list with the following parameters: @Pending = ' + convert(varchar, ISNULL(@Pending, 0)) + ', '
SELECT @MessageStringLong = @MessageStringLong + '@JobPrefixId = ' + convert(varchar, ISNULL(@JobPrefixId, 0)) + ', '
SELECT @MessageStringLong = @MessageStringLong + '@JobId = ' + convert(varchar, ISNULL(@JobId, 0)) + ', '
SELECT @MessageStringLong = @MessageStringLong + '@RecipientId = ' + convert(varchar, ISNULL(@RecipientId, 0)) + ', '
SELECT @MessageStringLong = @MessageStringLong + '@FailedOnly = ' + convert(varchar, ISNULL(@FailedOnly, 0))
EXEC [dbo].[UTIL_LogSystemJobDetail]
@JobId = @SystemJobId,
@JobDetail = @MessageStringLong
-- Create temporary table to hold the selected recipients
CREATE TABLE #RecptResultSet(JobPrefixId INT NOT NULL, JobId INT NOT NULL, RecipientId INT NOT NULL,
EventStatusId INT NOT NULL, EventDlvryECD VARCHAR(32) NULL, EventStopDt DATETIME NULL,
EventFaxNumber VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , EventRecipientName NVARCHAR(64) NULL, EventRecipientCompanyName NVARCHAR(32) NULL,
EventDlvryAttempts SMALLINT NOT NULL, ErrorSource INT NULL, ErrorCode INT NULL,
ErrorClass INT NULL, ErrorExtra INT NULL, ErrorDesc VARCHAR(100) NULL, EventStatusDesc VARCHAR(100) NULL,
EventSubmitDt DATETIME, EventStartDt DATETIME, EventDuration smallint, EventDlvrySpeed VARCHAR(8),
DlvryServerRecptRecvCSID VARCHAR(39), PageCt int, CoverSubject NVARCHAR(128),UserField1 NVARCHAR(32),EventDlvryCurrentPageCt int,
RecipTitle nvarchar (32) NULL,RecipFirstName nvarchar (32) NULL,RecipLastName nvarchar (32) NULL,
RecipAddress1 nvarchar (55) NULL ,RecipAddress2 nvarchar (55) NULL ,RecipCity nvarchar (29) NULL,
RecipState nvarchar (19) NULL ,RecipZip nvarchar (19) NULL ,RecipCountry nvarchar (35) NULL , RecipVoice nvarchar (34) NULL,
RecipField1 nvarchar(32) NULL,
RecipField2 nvarchar(32) NULL, RecipField3 nvarchar(32) NULL, RecipField4 nvarchar(32) NULL, RecipField5 nvarchar(32) NULL,
RecipField6 nvarchar(32) NULL, RecipField7 nvarchar(32) NULL, RecipField8 nvarchar(32) NULL, RecipField9 nvarchar(32) NULL,
RecipField10 nvarchar(32) NULL, RecipField11 nvarchar(32) NULL, RecipField12 nvarchar(32) NULL,
SenderName nvarchar (64) NULL, SenderCompany nvarchar (34) NULL,SenderAddress1 nvarchar (55) NULL,
SenderAddress2 nvarchar (55) NULL,SenderCity nvarchar (29) NULL,SenderState nvarchar (19) NULL,SenderZip nvarchar (19) NULL,
SenderCountry nvarchar (35) NULL,SenderVoice nvarchar (34) NULL,SenderFax nvarchar (34) NULL,
EventFaxNumberCountryCode VARCHAR(8) NULL , EventFaxNumberCountryName VARCHAR(64) NULL ,
FailureBRIPErrorDesc VARCHAR(100) NULL, FailureBRIPErrorCode INT NULL,
FailureReportLongString VARCHAR(23) NULL,
FailureErrorEmailNotification VARCHAR(1000) NULL, FailureErrorWSResponse VARCHAR(1000) NULL
)
-- Initialise
SELECT @ReturnCode = 0,
@RecordCount = 0,
@JobSuccessCount = 0,
@JobFailureCount = 0,
@JobPendingCount = 0
-- Get completed as well as pending recipients
INSERT #RecptResultSet(JobPrefixId, JobId, RecipientId, EventStatusId, EventDlvryECD, EventStopDt, EventFaxNumber,
EventRecipientName, EventRecipientCompanyName, EventDlvryAttempts, ErrorSource, ErrorCode, ErrorClass,
ErrorExtra, ErrorDesc, EventStatusDesc, EventSubmitDt, EventStartDt, EventDuration, EventDlvrySpeed,
DlvryServerRecptRecvCSID, PageCt, CoverSubject, UserField1,EventDlvryCurrentPageCt,
RecipTitle,RecipFirstName,RecipLastName,RecipAddress1,RecipAddress2,RecipCity,
RecipState,RecipZip,RecipCountry,RecipVoice,
RecipField1,RecipField2,RecipField3,RecipField4,RecipField5,RecipField6,
RecipField7,RecipField8,RecipField9,RecipField10,RecipField11,RecipField12,
SenderName,SenderCompany,SenderAddress1,
SenderAddress2,SenderCity,SenderState,SenderZip,
SenderCountry,SenderVoice,SenderFax)
EXEC [dbo].[Recipient_EnumerateWorker] @Pending, @JobPrefixId, @JobId, @RecipientId, @FailedOnly,
@RecordCount OUTPUT, @JobSuccessCount OUTPUT, @JobFailureCount OUTPUT, @JobPendingCount OUTPUT
CREATE NONCLUSTERED INDEX [idx_RecipientJobId] ON #RecptResultSet
(
[JobPrefixId], [JobId], [RecipientId]
)
EXEC [dbo].[UTIL_LogSystemJobDetail]
@JobId = @SystemJobId,
@JobDetail = 'Adding EventFaxNumber details'
-- Get the friendly description of the event status
UPDATE r
SET EventFaxNumberCountryCode = '1',
EventFaxNumberCountryName = 'USA/Canada'
FROM #RecptResultSet r
WHERE LEFT(EventFaxNumber,1) = '1'
UPDATE r
SET EventFaxNumberCountryCode = idc.DialingCode,
EventFaxNumberCountryName = idc.CountryName
FROM #RecptResultSet r
INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 6
UPDATE r
SET EventFaxNumberCountryCode = idc.DialingCode,
EventFaxNumberCountryName = idc.CountryName
FROM #RecptResultSet r
INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 5
UPDATE r
SET EventFaxNumberCountryCode = idc.DialingCode,
EventFaxNumberCountryName = idc.CountryName
FROM #RecptResultSet r
INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 4
UPDATE r
SET EventFaxNumberCountryCode = idc.DialingCode,
EventFaxNumberCountryName = idc.CountryName
FROM #RecptResultSet r
INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 3
UPDATE r
SET EventFaxNumberCountryCode = idc.DialingCode,
EventFaxNumberCountryName = idc.CountryName
FROM #RecptResultSet r
INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 2
UPDATE r
SET EventFaxNumberCountryCode = idc.DialingCode,
EventFaxNumberCountryName = idc.CountryName
FROM #RecptResultSet r
INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 1
EXEC [dbo].[UTIL_LogSystemJobDetail]
@JobId = @SystemJobId,
@JobDetail = 'Added Failure details'
UPDATE r
SET FailureBRIPErrorDesc = 'Success',
FailureBRIPErrorCode = 0,
FailureReportLongString = 'Success'
FROM #RecptResultSet r
WHERE EventStatusId=1
UPDATE r
SET FailureBRIPErrorDesc = em.BRIPErrorDesc,
FailureBRIPErrorCode = em.BRIPErrorCode,
FailureReportLongString = em.ReportLongString,
FailureErrorEmailNotification = em.ErrorEmailNotification,
FailureErrorWSResponse = em.ErrorWSResponse
FROM #RecptResultSet r
INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
r.ErrorClass=em.ECDErrortype AND
r.ErrorCode=em.ECDErrorCode AND
r.ErrorExtra=em.ECDErrorExtra
WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
UPDATE r
SET FailureBRIPErrorDesc = em.BRIPErrorDesc,
FailureBRIPErrorCode = em.BRIPErrorCode,
FailureReportLongString = em.ReportLongString,
FailureErrorEmailNotification = em.ErrorEmailNotification,
FailureErrorWSResponse = em.ErrorWSResponse
FROM #RecptResultSet r
INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
r.ErrorClass=em.ECDErrortype AND
r.ErrorCode=em.ECDErrorCode AND
0=em.ECDErrorExtra
WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
UPDATE r
SET FailureBRIPErrorDesc = em.BRIPErrorDesc,
FailureBRIPErrorCode = em.BRIPErrorCode,
FailureReportLongString = em.ReportLongString,
FailureErrorEmailNotification = em.ErrorEmailNotification,
FailureErrorWSResponse = em.ErrorWSResponse
FROM #RecptResultSet r
INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
r.ErrorClass=em.ECDErrortype AND
0=em.ECDErrorCode AND
0=em.ECDErrorExtra
WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
UPDATE r
SET FailureBRIPErrorDesc = em.BRIPErrorDesc,
FailureBRIPErrorCode = em.BRIPErrorCode,
FailureReportLongString = em.ReportLongString,
FailureErrorEmailNotification = em.ErrorEmailNotification,
FailureErrorWSResponse = em.ErrorWSResponse
FROM #RecptResultSet r
INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
0=em.ECDErrortype AND
0=em.ECDErrorCode AND
0=em.ECDErrorExtra
WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
-- Return the summary totals for the Job
SELECT @RecordCount AS [RecordCount],
@JobSuccessCount AS [JobSuccessCount],
@JobFailureCount AS [JobFailureCount],
@JobPendingCount AS [JobPendingCount]
EXEC [dbo].[UTIL_LogSystemJobDetail]
@JobId = @SystemJobId,
@JobDetail = 'Returning recipient list'
-- Return the list of all recipients for the Job
SELECT *
FROM #RecptResultSet
ORDER BY JobPrefixId,
JobId,
RecipientId
EXEC [dbo].[UTIL_LogSystemJobDetail]
@JobId = @SystemJobId,
@JobDetail = 'Recipient list return complete'
-- Destroy temporary objects
DROP
TABLE #RecptResultSet
EXEC [dbo].[UTIL_EndSystemJob]
@JobId = @SystemJobId
SET NOCOUNT OFF
RETURN @ReturnCode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Alles anzeigen

Ich versuche folgendermaßen an das zweite OUTPUT Recordset zu kommen:

Quellcode

<?php
$Pending = "0";
$Prefix = "101";
$JobId = "315";
$RecipientId = "0";
$FailedOnly = "1";
// Connect to MSSQL and select the database
mssql_connect('expsq002', 'sa', 'MYPW!');
mssql_select_db('db_data');
// Create a new stored prodecure
$stmt = mssql_init('Recipient_Enumerate');
//mssql_bind($stmt, '@Pending',$Pending,SQLINT4,false,false,3);
mssql_bind($stmt, '@JobPrefixId',$Prefix,SQLINT4,false,false,3);
mssql_bind($stmt, '@JobId',$JobId,SQLINT4,false,false,3);
//mssql_bind($stmt, '@RecipientId',$RecipientId,SQLINT4,false,false,3);
//mssql_bind($stmt, '@FailedOnly',$FailedOnly,SQLINT4,false,false,3);
//mssql_bind($stmt, "@ReturnCode", $lala, SQLVARCHAR, true, true);
$result = mssql_execute($stmt);
$numProds = mssql_num_rows($result);
while($row = mssql_fetch_row($result))
{
var_dump($row);
}
?>

Alles anzeigen

Ich bekomme leider als Antwort:

Quellcode

Warning: mssql_execute(): message: Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails. (severity 16) in /var/www/reporting/test.php on line 23 Warning: mssql_execute(): General SQL Server error: Check messages from the SQL Server (severity 16) in /var/www/reporting/test.php on line 23 array(4) { [0]=> int(0) [1]=> int(0) [2]=> int(0) [3]=> int(0) }

Kann mir jemand weiterhelfen? Ich hatte noch nie mit SP's zu tun und das sind meine ersten Gehversuche in dieser Richtung. Per SQL-Query Tool funktioniert es mit diesen Parametern einwandfrei

Gruß,
Matze

Torben Brodt · 19. August 2011, 19:22

Also weil das absolut nichts mit PHP zu tun hat, verschiebe ich das mal ins Datenbank Forum.

Ansonsten kann ich mich mit MSSQL nicht gut genug aus um dir die Frage direkt beantworten zu können, aber es sieht schlicht danach aus als, als hättest du ein paar Variablen vergessen zu setzen.
Welche setzt du denn noch zusätzlich im SQL Browser. In deinem PHP Code hast du ja ein paar Variablen auskommentiert.

RealFairPlayer · 19. August 2011, 20:21

Hi Torben,

Ich habe nochmal mit dem DBA gesprochen, es sieht so aus als würde er eine TEMP Table füllen wollen, und PHP verhält sich hier falsch, hat irgendwas mit ANSI NULL settings zu tun, meiner Meinung nach müsste aber der DBA diese in der Stored Procedure setzen, nicht ich ... darüber streiten wir aktuell, oder liege ich da etwa falsch?

Matze

vince · 20. August 2011, 10:09

Hi,

was gibt der Server denn für eine Fehlermeldung aus?

General SQL Server error: Check messages from the SQL Server

Wie sieht dein $stmt aus ?
Die angehangenen SQL Befehle kannst du so nicht als 1 Statement nutzen, da du einzelnen Befehle mit einem Semikolon abschließen musst.

Also SELECT ...... ; ALTER ..... ; usw.

MSSQL => Stored Procedure => Recordset

MSSQL => Stored Procedure => Recordset

Quellcode

Quellcode

Quellcode

Teilen

Benutzer online 1

Tags