Having decided not to use the undocumented checksum in SQL 7, we are
now using SP_TABLE_VALIDATION to work out the checksum of a table.

We have an issue in that we are calling it from a VB app and we get
the message back stating how many rows were counted etc. but it also
returns an error code with the message and stops the VB app getting
back the OUTPUT parameters that we actually want (rowcount and
checksum).

Has anyone any similar experiences?
Any ideas ?

Thanks

S.

Seven (sevincs@yahoo.com) writes:
> Having decided not to use the undocumented checksum in SQL 7, we are
> now using SP_TABLE_VALIDATION to work out the checksum of a table.
>
> We have an issue in that we are calling it from a VB app and we get
> the message back stating how many rows were counted etc. but it also
> returns an error code with the message and stops the VB app getting
> back the OUTPUT parameters that we actually want (rowcount and
> checksum).

Looking at the source code for sp_table_validation, I see that it
raises some informational messages with severity 10. ADO usually don't
regard these messages as errors, and sometimes it is difficult to
receive these messages in ADO at all.

Then again, you did not say that are using ADO. If you are, I suggest
that you try using a client-side cursor. If this does not work out,
maybe you could post the VB code?


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93E6ED621700FYazorman@127.0.0.1>...
> Seven (sevincs@yahoo.com) writes:
> > Having decided not to use the undocumented checksum in SQL 7, we are
> > now using SP_TABLE_VALIDATION to work out the checksum of a table.
> >
> > We have an issue in that we are calling it from a VB app and we get
> > the message back stating how many rows were counted etc. but it also
> > returns an error code with the message and stops the VB app getting
> > back the OUTPUT parameters that we actually want (rowcount and
> > checksum).
>
> Looking at the source code for sp_table_validation, I see that it
> raises some informational messages with severity 10. ADO usually don't
> regard these messages as errors, and sometimes it is difficult to
> receive these messages in ADO at all.
>
> Then again, you did not say that are using ADO. If you are, I suggest
> that you try using a client-side cursor. If this does not work out,
> maybe you could post the VB code?

Is there an ADO setting then that can be changed to only read messages
of a certain severity and above?

The code for the stored proc is below:

CREATE PROCEDURE pr_getProgrammesCheckSum
@expected_rowcount int OUTPUT,
@expected_checksum numeric OUTPUT
AS
/**
Usage:
Creates a unique ID for programmes table using checksum. FOR Version
7 SQL
IN Parameter:
None
OUT Parameter:
@expected_rowcount int ,
@expected_checksum numeric ,
RETURN:
0 : Success
-1: Error
All else: Fail
**/
DECLARE
@table sysname ,
@rowcount_only bit,
@owner nvarchar(50),
-- @expected_rowcount int ,
-- @expected_checksum numeric,
@full_or_fast tinyint,
@shutdown_agent bit,
@table_name sysname,
@result int;

BEGIN
SET @result = -1;
SELECT
@table = 'Programmes',
@expected_rowcount =null ,
@expected_checksum =null ,
@rowcount_only = 0,
@owner = NULL,
@full_or_fast = 1,
@shutdown_agent = 0,
@table_name = ''

EXEC sp_table_validation @table,
@expected_rowcount OUTPUT,
@expected_checksum OUTPUT,
@rowcount_only ,
@owner ,
@full_or_fast ,
@shutdown_agent,
@table_name


SET @result = @@ERROR
RETURN @result
END

We are attempting to execute this proc using the following VB code:

Dim rsGetProgrammesUniqueKey As Recordset
Dim prmReturn As Parameter
Dim prmRowcount As Parameter
Dim prmCheckSum As Parameter
Dim prmEntity As Parameter
Dim comGetProgrammesUniqueIDCommand As New Command

On Error GoTo GetProgrammesUniqueKey_Error

comGetProgrammesUniqueIDCommand.ActiveConnection =
GVADatabaseConnection

comGetProgrammesUniqueIDCommand.CommandText =
"pr_getProgrammesCheckSum"

comGetProgrammesUniqueIDCommand.CommandType = adCmdStoredProc

'Add return parameter
Set prmReturn = comGetProgrammesUniqueIDCommand.CreateParameter("R eturn",
adInteger, adParamReturnValue)
comGetProgrammesUniqueIDCommand.Parameters.Append prmReturn

'Add output parameters
Set prmRowcount = comGetProgrammesUniqueIDCommand.CreateParameter("R owcount",
adInteger, adParamOutput)
comGetProgrammesUniqueIDCommand.Parameters.Append prmRowcount

Set prmCheckSum = comGetProgrammesUniqueIDCommand.CreateParameter("C heckSum",
adInteger, adParamOutput)
comGetProgrammesUniqueIDCommand.Parameters.Append prmCheckSum

'Execute
Set rsGetProgrammesUniqueKey =
comGetProgrammesUniqueIDCommand.Execute(, , adExecuteNoRecords)


'Examine return code
If comGetProgrammesUniqueIDCommand("Return") <> 0 Then
GetProgrammesUniqueKey = -1
GoTo GetProgrammesUniqueKey_Exit
End If

'get output
GetProgrammesUniqueKey = comGetProgrammesUniqueIDCommand("Checksum")


However as soon as we attempt to execute the stored proc the following
error occurs:
err.number = -2147217913
err.description = [Microsoft][ODBC SQL Server Driver][SQL
Server]Generated expected rowcount value of 36 and expected checksum
value of 14428475765 for .


We have tried several ways of turning this message off from the SQL
server side but there seems to be no way of doing this within SQL
server.
Do you know of anyway we can make ADO ignore this message?

[posted and mailed, please reply in news]

Seven (sevincs@yahoo.com) writes:
> Is there an ADO setting then that can be changed to only read messages
> of a certain severity and above?

No. Your control over errror-handing in ADO is poor. Your problem is
that an informational message is presented as an error. At other
occasions you may find that you cannot retrieve the informational
messages you actually want at all.

I tried your procedure from a small test app I have for ADO, and I while
I did get the message, there was no error raised.

I can see two possibilities:
1) When you run the procedure, there is actually some error occurring.
In this case, ADO would present you both with the error and any
other messages. You would have to iterate over the .Errors collection
to find them all.
2) You are using a version of ADO which is stupid enough to interpret
severity 10 as an error.

Since you mentioned you are using SQL7, I would not be surprised if you
have ADO 2.5 or somesuch which makes the second option the most likely.
You might try download a service pack for the MDAC you have. Or install
MDAC 2.7 or 2.8. Note that you need probably need to change the project to
use the new ADO version.

I also note that you are using MSDASQL, that is OLE DB over ODBC. You
might also try to switch to SQLOLEDB, as this provider is generally
the recommended one.

You cursor location was not clear from the script, but client-side
cursor is usually better than the default server-side cursor.

If nothing of this helps - the workaround would be in your error handler
to check Err.Number for the messages that sp_table_validation can raise,
and if it is any of these number say "Resume Next". Or simply have an
On Error Resume Next before the call to the procedure, and manually
check the .Errors collection.

After all, you are in Visual Basic, so you have full control of which
errors that get presented and which are dropped on the floor.

One final note on your procedure:

> EXEC sp_table_validation @table,
> @expected_rowcount OUTPUT,
> @expected_checksum OUTPUT,
> @rowcount_only ,
> @owner ,
> @full_or_fast ,
> @shutdown_agent,
> @table_name
>
>
> SET @result = @@ERROR
> RETURN @result

When checking a call to a stored procedure, you should in most cases
check both the return value and @@error. Since @@error is set after
each statement, it may be 0 when you come back from the procedure
even if there was an error.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp