Hi.

1.  I haven't got a copy of Sql Server 7 handy here, otherwise I could
answer this myself:  Does the MS OLE DB Provider for ODBC support the
data types that were added to Sql Server in v.7?  I remember reading
that the BDE didn't support them because MS hadn't updated the
DBLibrary support or something, but don't know whether the same is
true of ODBC via ADO.

2.  Has anyone succeeded in getting meaningful <b>text</b> error
messages out of the Errors collection of an AdoConnection to the MS
OLE DB Provider for SQL Server?  I don't mean those damn OLE error
numbers*, but context-sensitive text like the ODBC provider would
produce ("syntax error near ...").  Is it possible to get this out of
the OLE DB layer when using ADO?

* nor the tabulation of what they mean that someone posted a while
ago.

Cheers, Martyn

Yes.  There was a bug in Mdac 2.6 that suppressed text error messages  when the
System Local doesn't match the  User Local.  This should be fixed in the
currently available version of Mdac 2.6
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Brian

Thanks.  Yes, I got SP1 for MDac 2.6 (upgrades the ADO DLLs to
2.61.7326.0) and that seems to report errors fine in textual form,
including syntax ones.

Now, the other thing that's bothering me is how to get the contents of
Sql PRINT statements out of the MS OLE DB Provider for Sql Server.  I
can get them from the ODBC provider fine, but not via the Sql Server
provider.  Any ideas?

Cheers, Martyn

news:35011ucd039j9dk1mruqbir12me5engpq1@4ax.com...

These two documents might give you some ideas:

Retrieve Values in SQL Server Stored Procedures with ADO
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;q194792
"PRINT statements in SQL Server can also populate the ADO errors collection.
However, PRINT statements are severity level zero (0) so, at least one
RAISERROR statement is required in the stored procedure to retrieve a PRINT
statement with ADO through the Errors collection."

HOWTO: Process Multiple Recordsets and Messages in ADO
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;q245179

Regards,
Andy Mackie.

Andy,

Thanks a lot.

I discovered after my previous post that if I set CursorLocation to
clUseServer - after the penny dropped that there wasn't much point
looking in a client-side construct for something like this - then I
get the contents of a direct PRINT statement (i.e. entered as the
CommandText) as both the contents of Exception(ExceptObject).Message
and as an ADO error, but I get neither from a stored proc, even if I
do a RAISERROR in it.  The copy of Q194792 I looked at was "Last
reviewed : October 14, 2000", so I'm wondering whether it's actually
fixed in MDac2.6Sp1, and maybe I'm just not doing all that's needed to
get at the text.  I'll take a look at the second article and see if
that throws any light on it.

Cheers, Martyn

Andy,

Aha.  No need for RAISERROR, apparently.  I defined spHello as

PRINT 'Hello'
PRINT 'World'

Then I do

  try
    R := AdoCommand1.Execute; {its sql.text = 'spHello'}
    while R.State = asStateExecuting do
      Application.ProcessMessages;
    while R <> Nil do begin
      R := R.NextRecordSet;
    end;
  except
    Memo1.Lines.Add(Exception(ExceptObject).Message);
  end;  

and get Hello \n World in Memo1.  Great!

Cheers, and thanks a lot for the pointers.

Martyn  

Andy,

Aha.  No need for RAISERROR, apparently.  I defined spHello as

PRINT 'Hello'
PRINT 'World'

Then I do

  try
    R := AdoCommand1.Execute; {its commandtext = 'spHello'}
    while R.State = asStateExecuting do
      Application.ProcessMessages;
    while R <> Nil do begin
      R := R.NextRecordSet(V);  { V is an OleVariant }
    end;
  except
    Memo1.Lines.Add(Exception(ExceptObject).Message);
  end;  

and get Hello \n World in Memo1.  Great!

Cheers, and thanks a lot for the pointers.

Martyn  

No.
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com