FIX: Access Violation Occurs When You Call SQL Server Stored Procedure That Contains PRINT Statement (269412)


The information in this article applies to:

  • Microsoft OLE DB Provider for SQL Server 7.0
  • Microsoft OLE DB Provider for SQL Server 7.01
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 SP1
  • Microsoft Data Access Components 2.5 SP2
  • Microsoft Data Access Components 2.5 SP3
This article was previously published under Q269412

SYMPTOMS

An access violation occurs when you call a SQL Server stored procedure that contains a PRINT statement that displays a string containing approximately 700 characters.

This occurs with the Microsoft OLE DB Provider for SQL Server (SQLOLEDB).

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was corrected in MDAC 2.6.


You can download MDAC 2.6 or later from the following Web address:

http://msdn.microsoft.com/dataaccess

MORE INFORMATION

Steps to Reproduce Behavior

  1. Use the following SQL script to create a stored procedure that returns 700 bytes in a PRINT statement: 
    CREATE PROCEDURE sp_Test2
AS
set nocount on
declare @HTMLoutputx varchar(1000)
select @HTMLoutputx = ''

/* Create a text string containing more than 700 bytes. */ 
WHILE LEN(@HTMLoutputx) <= 700 
BEGIN
	select @HTMLoutputx = @HTMLoutputx + 'TOO MUCH TEXT IS BAD!'
END
PRINT @HTMLoutputx
  2. Create a new Standard EXE project in Visual Basic.
  3. Set a Project Reference to the Microsoft ActiveX Data Objects 2.5 Library.
  4. Add a command button named Command1 and insert the following code for the Click event handler:
    Private Sub Command1_Click()

On Error GoTo MAIN_ERROR

Dim adocnn As ADODB.Connection
Dim adocmd As ADODB.Command
Dim adorst As ADODB.Recordset
   
Set adocnn = New ADODB.Connection
adocnn.CursorLocation = adUseClient
adocnn.Open "Provider=SQLOLEDB;Data Source=myServer;Database=northwind;uid=sa;pwd=;"
  
Set adocmd = New ADODB.Command
With adocmd
    .CommandText = "sp_Test2"
    .CommandType = adCmdStoredProc
    .ActiveConnection = adocnn
End With

' Execute the command
adocmd.Execute , , adExecuteNoRecords

Set adocmd = Nothing
    
adocnn.Close
Set adocnn = Nothing

Exit Sub

MAIN_ERROR:
   MsgBox Err & " - " & Error$, vbCritical, "Error!"
   Exit Sub

End Sub
  5. Run the application. Click Command1 to see the access violation occur.

REFERENCES

For additional information about using PRINT statements and ADO, click the article number below to view the article in the Microsoft Knowledge Base:

194792 HOWTO: Retrieve Values in SQL Server Stored Procedures w/ ADO

Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbfix kbMDAC260fix kbProvider KB269412 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.