We have a legacy code that failed on a client machine. The Refresh method does not populates parameter collection of the command object for a given stored procedure. Here is a vb script that demonstrates the problem:
Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
cmd.CommandText = "spr_ret_Personnel" 'Stored procedure with 2 input parameters
cmd.CommandType = 4
cmd.Activeconnection = conn
MsgBox Cmd.Parameters.Count ' shows 0 (failed) instead of 3 (1 output + 2 input, succeeded)
I am trying to figure out what may cause it and how to fix it.
Environment: Windows 2003, SQL Server 2005, MDAC 2.8
SQL Profiler shows that calling Refresh executes the following procedure:
It executes fine on a client machine. The database does not have any synonysms.
The ADO code seems to be correct.
Could you use the "SQL Server Management Studio" to execute the TSQL:
And see what the result is?
Could you give us the definition of the SProc spr_ret_Personnel, so that we can take a look to see whether it is so special? If possible, you may try Windows Server 2008 or Windows Server 2008 R2, which may have already fixed the issue, if any.
WDAC Team, Microsoft.
Pak-Ming Cheung - MSFT
COM-based ADO and OLE DB to connect to SQL Server has had issues for some time when using the MDAC stack. The most current OLE DB provider for SQL Server is designed to connect to all previous and current versions of SQL Server including SQL Server Express. This driver can be downloaded from here: http://blogs.msdn.com/sqlnativeclient/archive/2009/11/03/download-locations-for-sql-server-native-client.aspx This new provider is far better and more stable than the old "MDAC stack" that we suffered with for years. The connection string looks like this:
Provider=SQLNCLI.1;Data Source=.\SQLEXPRESS;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW
Please click the Mark as Answer button if a post solves your problem!