Using adodb command - output parameter problem- HELP!

[Sandie Towers]

We use a number of similar databases and frequently create a new
database using a backup restore of another similar database. We try to
keep changes between databases in _Additional tables - like Account
Additional, Sale_Additional so most tables stay the same. The latest
restored database (I'll call it DBaseA) is behaving differently in VB6
code and I need help trying to make it work.

I have been using use an ADODB.Command to execute a stored procedure
using adCmdStoredProc and Parameters.Refresh to get an output parameter
value which is an integer. Here is a sample of the VB:

Public Function UnlockAccount(Account_ID As String) As Boolean
Dim LCmd As ADODB.Command
On Error GoTo ERROR_UnlockAccount
UnlockAccount = True
If Account_ID <> "" Then
If DBConnect() Then
Set LCmd = New ADODB.Command
LCmd.ActiveConnection = CN(0)
LCmd.CommandTimeout = 0
LCmd.CommandType = adCmdStoredProc
LCmd.CommandText = "Unlock_Account"
LCmd.Parameters.Refresh
LCmd.Parameters("@Account_ID").VALUE = Account_ID

LCmd.Execute , , adExecuteNoRecords

If LCmd.Parameters("@Status") <> 0 Then
UnlockAccount = False
End If
Else
msgbox "UnlockAccount: DBConnect Failed", "UnlockAccount"
End If
End If
Exit Function
ERROR_UnlockAccount:
UnlockAccount = False
msgbox "UnlockAccount: App Error: " & Err & " " & _
Err.Description, "UnlockAccount"
DBConnect True
Exit Function
Resume 'for debugging
End Function

and this is one of the sps that fails - can't be more simple!
Create PROCEDURE Unlock_Account
( @Account_ID UNIQUEIDENTIFIER,
@Status INTEGER =null OUTPUT) AS
/* Strip functionality */
SET @Status = 0
go

This code is still working in at least 4 other databases. It fails in
database DBaseA with the error:
Invalid character for cast conversion
We are talking about the exact same table definition and the exact same
stored procedure just in different databases.

I fumbled around on the Internet and found a mention in a PowerBuilder
web site of additional parameters in the connect string which seems to
fix the problem in SOME of the stored procs - but not all.
The added parameters are:
DelimitIdentifier='No';MsgTerse='Yes';
CallEscape='No';FormatArgsAsExp='N'
They are not documented in MS but are in Sybase?? No idea why, but some
of the stored proc functions work when I add this to the connect string.

The complete connect string is:
Provider=SQLOLEDB.1;Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=DBaseA;
Data Source=PHASE2-S500,1433;
Network Library=DBMSSOCN;
DelimitIdentifier='No';MsgTerse='Yes';
CallEscape='No';FormatArgsAsExp='N'

The databases are on different servers - but they are running the same
version of SQL2000, the same version of Windows. I see no differences
in options set in the different servers or between databases that work
and this one. And dbcc checkdb runs clean.

The compiled code that fails, fails across the board - not just on my
(developer) PC. The same source code works on the same tables in other
databases, including the one we copied....

The final kicker - if I build a SQL statement string and get the
recordset instead, it works like a charm. But I have to fix about 20 VB
functions and check that I do have a recordset and SET NOCOUNT ON in all
20 sps.

I feel this is either something so obvious I will kick myself or so
serious Microsoft will be digging into it.

Any ideas anyone??

Sandie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Sandie Towers (st*****@phase2solutions.com) writes:

I have been using use an ADODB.Command to execute a stored procedure
using adCmdStoredProc and Parameters.Refresh to get an output parameter
value which is an integer. Here is a sample of the VB:
...
and this is one of the sps that fails - can't be more simple!
Create PROCEDURE Unlock_Account
( @Account_ID UNIQUEIDENTIFIER,
@Status INTEGER =null OUTPUT) AS
/* Strip functionality */
SET @Status = 0
go

This code is still working in at least 4 other databases. It fails in
database DBaseA with the error:
Invalid character for cast conversion
We are talking about the exact same table definition and the exact same
stored procedure just in different databases.
The first thing to check is of course that you have the right procedure.

Next is to try to narrow down exactly which value that might be causing
the problem. Since you sett @Status to anything, it seems that
@Account_ID is the likely culprit. Using the VB debugger, check how
the .Parameters array looks like. If there is bad version of the stored
procedure that SQLOLEDB uses to get the parameters, this could be an
explanation.
The added parameters are:
DelimitIdentifier='No';MsgTerse='Yes';
CallEscape='No';FormatArgsAsExp='N'
They are not documented in MS but are in Sybase??
When I tried these in a test app that I have, SQLOLEDB barfed at the
format. When I removed the single quotes, and tried them one by one,
I kept getting "Invaliid connection attribute", so I suspect the reason
that MS has not documented these, is because they have no meaning with
the OLE DB provider for SQL Server.
The complete connect string is:
Provider=SQLOLEDB.1;Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=DBaseA;
Data Source=PHASE2-S500,1433;
Network Library=DBMSSOCN;
DelimitIdentifier='No';MsgTerse='Yes';
CallEscape='No';FormatArgsAsExp='N'

I would suggest that you beside the incorrect parameters also drop
Network Library. The default network library is called DBNETLIB, I
believe. DBMSSOCN is an old and obsolete one.


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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