Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...
Seven (se*****@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?
