I have an issue that I have seen posted all around newsgroups but I haven't yet come accross an adequate answer.
I am using VB6 (SP5), SQL Server 2000 and the latest SQl OLE DB driver (MSDAC 2.7).
In summary, if I use RAISERROR as the first line in a Stored Procedure then VB will respond to it, however if it occurs after a SELECT (or just about any other) statement then it becomes invisible to VB. The errors are reported correctly when the procedure is executed within Query Analyser.
I have tried changing the RAISERROR priority from 16 to 10 and I have tried the OLE ODBC driver - both with no success. I have been told that the SQL Server installation contains all of the latest patches.
I have included below a sample Stored Procedure followed by the VB code I am using. Please do not 'guess' at this one - only respond if you have come accross this issue before.
CREATE PROCEDURE [dbo].[uspRaiseTheError] AS
SET NOCOUNT ON
SELECT * FROM sysobjects
RAISERROR ('Talk to me',16,1)
SET NOCOUNT OFF
Private Sub Command1_Click()
On Error GoTo ErrHandler
Dim c As Connection
Set c = New Connection
c.CursorLocation = adUseClient
c.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=EDRMSX;Data Source=EDRMS"
'This stored procedure reports on objects not installed correctly by raising errors
c.Execute "EXEC dbo.uspRaiseTheError"
Set c = Nothing
MsgBox Err.Description, vbExclamation, "Error"