MSSQL => Stored Procedure => Recordset

Diese Seite verwendet Cookies. Durch die Nutzung unserer Seite erklären Sie sich damit einverstanden, dass wir Cookies setzen. Weitere Informationen

  • MSSQL => Stored Procedure => Recordset

    Hi!

    Ich habe folgendes MS SQL Procedure

    Quellcode

    1. SET QUOTED_IDENTIFIER ON
    2. GO
    3. SET ANSI_NULLS ON
    4. GO
    5. ALTER PROCEDURE [dbo].[Recipient_EnumerateVE] (
    6. @Pending INT = 0,
    7. @JobPrefixId INT = 101,
    8. @JobId INT = 727,
    9. @RecipientId INT = 0,
    10. @FailedOnly INT = 0
    11. )
    12. AS
    13. /* Description : Return list of recipients based on JobId and other supplied
    14. * parameters. VENALI EXPRESS FLAVOUR
    15. * In : @Pending - Return only recipients for jobs in a pending state
    16. * @JobPrefixId - Identifier of gateway submitting job
    17. * @JobId - Identifier of job whose recipients are to be returned
    18. * @RecipientId - Identifier of recipient to return
    19. * @FailedOnly - Return only those recipients with a completed
    20. * status of failed.
    21. * Out : Two recordsets:
    22. * Summary Totals for job, listing successful, failed and
    23. * pending recipients. Values returned are based only on
    24. * the JobId and do not reflect effects of other parameters.
    25. * Recipient List, return all recipients that meet the
    26. * selection parameters provided.
    27. * Notes : Based on the selection criteria, return the list of recipients for
    28. * given job. Create summary totals are return those as well.
    29. * History :
    30. *
    31. * Date Who Action Notes
    32. * --------------------------------------------------------------------------
    33. * 13/07/2011 MAS Created
    34. */
    35. DECLARE @ReturnCode INT,
    36. @RecordCount INT,
    37. @JobSuccessCount INT,
    38. @JobFailureCount INT,
    39. @JobPendingCount INT,
    40. @SystemJobId INT,
    41. @MessageStringShort varchar(32),
    42. @MessageStringLong varchar(128)
    43. SET NOCOUNT ON
    44. -- Logging: Start a System Job and log the SP parameters
    45. SELECT @MessageStringShort = 'Recipient_EnumerateWork<' + convert(varchar, @JobPrefixId) + '-' + convert(varchar, @JobId) + '>'
    46. EXEC @SystemJobId = [UTIL_BeginSystemJob]
    47. @JobType = 3,
    48. @JobTag = @MessageStringShort,
    49. @OwnerJobPrefixId = @JobPrefixId,
    50. @OwnerJobId = @JobId,
    51. @OwnerRecipientId = @RecipientId
    52. SELECT @MessageStringLong = 'Returning recipient list with the following parameters: @Pending = ' + convert(varchar, ISNULL(@Pending, 0)) + ', '
    53. SELECT @MessageStringLong = @MessageStringLong + '@JobPrefixId = ' + convert(varchar, ISNULL(@JobPrefixId, 0)) + ', '
    54. SELECT @MessageStringLong = @MessageStringLong + '@JobId = ' + convert(varchar, ISNULL(@JobId, 0)) + ', '
    55. SELECT @MessageStringLong = @MessageStringLong + '@RecipientId = ' + convert(varchar, ISNULL(@RecipientId, 0)) + ', '
    56. SELECT @MessageStringLong = @MessageStringLong + '@FailedOnly = ' + convert(varchar, ISNULL(@FailedOnly, 0))
    57. EXEC [dbo].[UTIL_LogSystemJobDetail]
    58. @JobId = @SystemJobId,
    59. @JobDetail = @MessageStringLong
    60. -- Create temporary table to hold the selected recipients
    61. CREATE TABLE #RecptResultSet(JobPrefixId INT NOT NULL, JobId INT NOT NULL, RecipientId INT NOT NULL,
    62. EventStatusId INT NOT NULL, EventDlvryECD VARCHAR(32) NULL, EventStopDt DATETIME NULL,
    63. EventFaxNumber VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , EventRecipientName NVARCHAR(64) NULL, EventRecipientCompanyName NVARCHAR(32) NULL,
    64. EventDlvryAttempts SMALLINT NOT NULL, ErrorSource INT NULL, ErrorCode INT NULL,
    65. ErrorClass INT NULL, ErrorExtra INT NULL, ErrorDesc VARCHAR(100) NULL, EventStatusDesc VARCHAR(100) NULL,
    66. EventSubmitDt DATETIME, EventStartDt DATETIME, EventDuration smallint, EventDlvrySpeed VARCHAR(8),
    67. DlvryServerRecptRecvCSID VARCHAR(39), PageCt int, CoverSubject NVARCHAR(128),UserField1 NVARCHAR(32),EventDlvryCurrentPageCt int,
    68. RecipTitle nvarchar (32) NULL,RecipFirstName nvarchar (32) NULL,RecipLastName nvarchar (32) NULL,
    69. RecipAddress1 nvarchar (55) NULL ,RecipAddress2 nvarchar (55) NULL ,RecipCity nvarchar (29) NULL,
    70. RecipState nvarchar (19) NULL ,RecipZip nvarchar (19) NULL ,RecipCountry nvarchar (35) NULL , RecipVoice nvarchar (34) NULL,
    71. RecipField1 nvarchar(32) NULL,
    72. RecipField2 nvarchar(32) NULL, RecipField3 nvarchar(32) NULL, RecipField4 nvarchar(32) NULL, RecipField5 nvarchar(32) NULL,
    73. RecipField6 nvarchar(32) NULL, RecipField7 nvarchar(32) NULL, RecipField8 nvarchar(32) NULL, RecipField9 nvarchar(32) NULL,
    74. RecipField10 nvarchar(32) NULL, RecipField11 nvarchar(32) NULL, RecipField12 nvarchar(32) NULL,
    75. SenderName nvarchar (64) NULL, SenderCompany nvarchar (34) NULL,SenderAddress1 nvarchar (55) NULL,
    76. SenderAddress2 nvarchar (55) NULL,SenderCity nvarchar (29) NULL,SenderState nvarchar (19) NULL,SenderZip nvarchar (19) NULL,
    77. SenderCountry nvarchar (35) NULL,SenderVoice nvarchar (34) NULL,SenderFax nvarchar (34) NULL,
    78. EventFaxNumberCountryCode VARCHAR(8) NULL , EventFaxNumberCountryName VARCHAR(64) NULL ,
    79. FailureBRIPErrorDesc VARCHAR(100) NULL, FailureBRIPErrorCode INT NULL,
    80. FailureReportLongString VARCHAR(23) NULL,
    81. FailureErrorEmailNotification VARCHAR(1000) NULL, FailureErrorWSResponse VARCHAR(1000) NULL
    82. )
    83. -- Initialise
    84. SELECT @ReturnCode = 0,
    85. @RecordCount = 0,
    86. @JobSuccessCount = 0,
    87. @JobFailureCount = 0,
    88. @JobPendingCount = 0
    89. -- Get completed as well as pending recipients
    90. INSERT #RecptResultSet(JobPrefixId, JobId, RecipientId, EventStatusId, EventDlvryECD, EventStopDt, EventFaxNumber,
    91. EventRecipientName, EventRecipientCompanyName, EventDlvryAttempts, ErrorSource, ErrorCode, ErrorClass,
    92. ErrorExtra, ErrorDesc, EventStatusDesc, EventSubmitDt, EventStartDt, EventDuration, EventDlvrySpeed,
    93. DlvryServerRecptRecvCSID, PageCt, CoverSubject, UserField1,EventDlvryCurrentPageCt,
    94. RecipTitle,RecipFirstName,RecipLastName,RecipAddress1,RecipAddress2,RecipCity,
    95. RecipState,RecipZip,RecipCountry,RecipVoice,
    96. RecipField1,RecipField2,RecipField3,RecipField4,RecipField5,RecipField6,
    97. RecipField7,RecipField8,RecipField9,RecipField10,RecipField11,RecipField12,
    98. SenderName,SenderCompany,SenderAddress1,
    99. SenderAddress2,SenderCity,SenderState,SenderZip,
    100. SenderCountry,SenderVoice,SenderFax)
    101. EXEC [dbo].[Recipient_EnumerateWorker] @Pending, @JobPrefixId, @JobId, @RecipientId, @FailedOnly,
    102. @RecordCount OUTPUT, @JobSuccessCount OUTPUT, @JobFailureCount OUTPUT, @JobPendingCount OUTPUT
    103. CREATE NONCLUSTERED INDEX [idx_RecipientJobId] ON #RecptResultSet
    104. (
    105. [JobPrefixId], [JobId], [RecipientId]
    106. )
    107. EXEC [dbo].[UTIL_LogSystemJobDetail]
    108. @JobId = @SystemJobId,
    109. @JobDetail = 'Adding EventFaxNumber details'
    110. -- Get the friendly description of the event status
    111. UPDATE r
    112. SET EventFaxNumberCountryCode = '1',
    113. EventFaxNumberCountryName = 'USA/Canada'
    114. FROM #RecptResultSet r
    115. WHERE LEFT(EventFaxNumber,1) = '1'
    116. UPDATE r
    117. SET EventFaxNumberCountryCode = idc.DialingCode,
    118. EventFaxNumberCountryName = idc.CountryName
    119. FROM #RecptResultSet r
    120. INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
    121. WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 6
    122. UPDATE r
    123. SET EventFaxNumberCountryCode = idc.DialingCode,
    124. EventFaxNumberCountryName = idc.CountryName
    125. FROM #RecptResultSet r
    126. INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
    127. WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 5
    128. UPDATE r
    129. SET EventFaxNumberCountryCode = idc.DialingCode,
    130. EventFaxNumberCountryName = idc.CountryName
    131. FROM #RecptResultSet r
    132. INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
    133. WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 4
    134. UPDATE r
    135. SET EventFaxNumberCountryCode = idc.DialingCode,
    136. EventFaxNumberCountryName = idc.CountryName
    137. FROM #RecptResultSet r
    138. INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
    139. WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 3
    140. UPDATE r
    141. SET EventFaxNumberCountryCode = idc.DialingCode,
    142. EventFaxNumberCountryName = idc.CountryName
    143. FROM #RecptResultSet r
    144. INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
    145. WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 2
    146. UPDATE r
    147. SET EventFaxNumberCountryCode = idc.DialingCode,
    148. EventFaxNumberCountryName = idc.CountryName
    149. FROM #RecptResultSet r
    150. INNER JOIN InternationalDialCodes idc ON SUBSTRING(EventFaxNumber,4,LEN(idc.DialingCode)) = idc.DialingCode
    151. WHERE LEFT(EventFaxNumber,3) = '011' AND LEN(idc.DIalingCode) = 1
    152. EXEC [dbo].[UTIL_LogSystemJobDetail]
    153. @JobId = @SystemJobId,
    154. @JobDetail = 'Added Failure details'
    155. UPDATE r
    156. SET FailureBRIPErrorDesc = 'Success',
    157. FailureBRIPErrorCode = 0,
    158. FailureReportLongString = 'Success'
    159. FROM #RecptResultSet r
    160. WHERE EventStatusId=1
    161. UPDATE r
    162. SET FailureBRIPErrorDesc = em.BRIPErrorDesc,
    163. FailureBRIPErrorCode = em.BRIPErrorCode,
    164. FailureReportLongString = em.ReportLongString,
    165. FailureErrorEmailNotification = em.ErrorEmailNotification,
    166. FailureErrorWSResponse = em.ErrorWSResponse
    167. FROM #RecptResultSet r
    168. INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
    169. r.ErrorClass=em.ECDErrortype AND
    170. r.ErrorCode=em.ECDErrorCode AND
    171. r.ErrorExtra=em.ECDErrorExtra
    172. WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
    173. UPDATE r
    174. SET FailureBRIPErrorDesc = em.BRIPErrorDesc,
    175. FailureBRIPErrorCode = em.BRIPErrorCode,
    176. FailureReportLongString = em.ReportLongString,
    177. FailureErrorEmailNotification = em.ErrorEmailNotification,
    178. FailureErrorWSResponse = em.ErrorWSResponse
    179. FROM #RecptResultSet r
    180. INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
    181. r.ErrorClass=em.ECDErrortype AND
    182. r.ErrorCode=em.ECDErrorCode AND
    183. 0=em.ECDErrorExtra
    184. WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
    185. UPDATE r
    186. SET FailureBRIPErrorDesc = em.BRIPErrorDesc,
    187. FailureBRIPErrorCode = em.BRIPErrorCode,
    188. FailureReportLongString = em.ReportLongString,
    189. FailureErrorEmailNotification = em.ErrorEmailNotification,
    190. FailureErrorWSResponse = em.ErrorWSResponse
    191. FROM #RecptResultSet r
    192. INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
    193. r.ErrorClass=em.ECDErrortype AND
    194. 0=em.ECDErrorCode AND
    195. 0=em.ECDErrorExtra
    196. WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
    197. UPDATE r
    198. SET FailureBRIPErrorDesc = em.BRIPErrorDesc,
    199. FailureBRIPErrorCode = em.BRIPErrorCode,
    200. FailureReportLongString = em.ReportLongString,
    201. FailureErrorEmailNotification = em.ErrorEmailNotification,
    202. FailureErrorWSResponse = em.ErrorWSResponse
    203. FROM #RecptResultSet r
    204. INNER JOIN ErrorMappings em ON r.ErrorSource=em.ECDErrorSource AND
    205. 0=em.ECDErrortype AND
    206. 0=em.ECDErrorCode AND
    207. 0=em.ECDErrorExtra
    208. WHERE EventStatusId=2 AND FailureBRIPErrorDesc IS NULL
    209. -- Return the summary totals for the Job
    210. SELECT @RecordCount AS [RecordCount],
    211. @JobSuccessCount AS [JobSuccessCount],
    212. @JobFailureCount AS [JobFailureCount],
    213. @JobPendingCount AS [JobPendingCount]
    214. EXEC [dbo].[UTIL_LogSystemJobDetail]
    215. @JobId = @SystemJobId,
    216. @JobDetail = 'Returning recipient list'
    217. -- Return the list of all recipients for the Job
    218. SELECT *
    219. FROM #RecptResultSet
    220. ORDER BY JobPrefixId,
    221. JobId,
    222. RecipientId
    223. EXEC [dbo].[UTIL_LogSystemJobDetail]
    224. @JobId = @SystemJobId,
    225. @JobDetail = 'Recipient list return complete'
    226. -- Destroy temporary objects
    227. DROP
    228. TABLE #RecptResultSet
    229. EXEC [dbo].[UTIL_EndSystemJob]
    230. @JobId = @SystemJobId
    231. SET NOCOUNT OFF
    232. RETURN @ReturnCode
    233. GO
    234. SET QUOTED_IDENTIFIER OFF
    235. GO
    236. SET ANSI_NULLS ON
    237. GO
    Alles anzeigen


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

    Quellcode

    1. <?php
    2. $Pending = "0";
    3. $Prefix = "101";
    4. $JobId = "315";
    5. $RecipientId = "0";
    6. $FailedOnly = "1";
    7. // Connect to MSSQL and select the database
    8. mssql_connect('expsq002', 'sa', 'MYPW!');
    9. mssql_select_db('db_data');
    10. // Create a new stored prodecure
    11. $stmt = mssql_init('Recipient_Enumerate');
    12. //mssql_bind($stmt, '@Pending',$Pending,SQLINT4,false,false,3);
    13. mssql_bind($stmt, '@JobPrefixId',$Prefix,SQLINT4,false,false,3);
    14. mssql_bind($stmt, '@JobId',$JobId,SQLINT4,false,false,3);
    15. //mssql_bind($stmt, '@RecipientId',$RecipientId,SQLINT4,false,false,3);
    16. //mssql_bind($stmt, '@FailedOnly',$FailedOnly,SQLINT4,false,false,3);
    17. //mssql_bind($stmt, "@ReturnCode", $lala, SQLVARCHAR, true, true);
    18. $result = mssql_execute($stmt);
    19. $numProds = mssql_num_rows($result);
    20. while($row = mssql_fetch_row($result))
    21. {
    22. var_dump($row);
    23. }
    24. ?>
    Alles anzeigen


    Ich bekomme leider als Antwort:

    Quellcode

    1. 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
    Das Leben ist binär - du bist eine 1, oder eine 0
  • 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.
  • 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
    Das Leben ist binär - du bist eine 1, oder eine 0
  • 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.