none
Command Parameters Refresh does not work for stored procedure

    Question

  • Hi,

    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")
    conn.Open "..."
    cmd.CommandText = "spr_ret_Personnel" 'Stored procedure with 2 input parameters
    cmd.CommandType = 4
    cmd.Activeconnection = conn
    cmd.Parameters.Refresh
    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:

    exec

     

    [database]..sp_procedure_params_rowset N'spr_ret_Personnel',1,NULL,NULL

    It executes fine on a client machine. The database does not have any synonysms.

    Please help.

    Thank you

    Dmitriy.

    Wednesday, March 31, 2010 12:45 PM

All replies

  • The ADO code seems to be correct.

     

    Could you use the "SQL Server Management Studio" to execute the TSQL:

    ..sp_procedure_params_rowset N'spr_ret_Personnel',1,NULL,NULL

    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.

    Thanks,

    Ming.

    WDAC Team, Microsoft.


    Pak-Ming Cheung - MSFT
    Thursday, April 01, 2010 10:26 AM
    Answerer
  • Are there CLR types for parameters to this procedure?  If so, SQL Server 2008 SP1 should have a fix for this.

    Hope that helps,

    John


    This post is provided 'as is' and confers no express or implied warranties or rights.
    Thursday, April 01, 2010 11:34 PM
    Moderator
  • 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

    hth


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Friday, April 02, 2010 12:30 AM
    Moderator