Post reply

Could not find server - on ADODB recordset .Update

  • January 19, 2012 at 10:10 am

    #104109

    Sorry -- Accidentally posted this long before it was ready!

    We have a front end db in MS Access which connects to clients' SQL Server data.

    Most of our clients have no problems with this code, but one of our clients does.

    Dim cnSQL As ADODB.Connection

    Dim rstSQL As New ADODB.Recordset

    Set cnSQL = New ADODB.Connection

    cnSQL.ConnectionString = strSQLConnString

    cnSQL.Open

    rstSQL.Open "Exec usp_SavePrepFormConfig VariablesHere....", cnSQL, adOpenKeyset, adLockOptimistic

    rstSQL!FieldName = BlahBlah

    rstSQL.Update

    It's on the .Update that the code fails for the client.

    They get this Error:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'CorrectServerName\CorrectInstanceName' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

    An interesting thing is that for this client, strSQLConnString = "driver={SQL Server};SERVER='CorrectServerName\CorrectInstanceName' ,Port;Trusted_Connectio=Yes;DATABASE=mts7"

    But after I set the cnSQL.ConnectionString = strSQLConnString, and I then ask for what the value of cnSQL.ConnectionString IS, then I get

    "Provider=MSDASQL.1;Extended Properties="DRIVER=SQL Server; SERVER='CorrectServerName\CorrectInstanceName' ,Port;UID=;APP=2007 Microsoft Office system;WSID=UserNameHere;DATABASE=mts7;Trusted_Connection=Yes"

    (For other clients, if I ask for the cnSQL.ConnectionString after I set it, the ConnectionString is what I told it to be.)

    Anyone know why the client might be getting this Error?

    Their data is in SQL Server 2005, but its Compatibility Level is SQL Server 2000, if that matters. (Though others clients have had the same config.)

    To get more specific configuration data, I'll have to log on with the client again.

    I've seen this question asked a couple of times, but I haven't found an answer.

    Thanks!

  • January 25, 2012 at 9:23 am

    #1438278

    For future reference:

    The answer was out there in cyberland. I just hadn't understood it.

    Looks like the computer name of the machine housing the SQL Server was changed. The metadata within the SQL Server had not been changed. This method of connecting to the data touched on that metadata and couldn't find the server name.

    I requested the client update the Server Name as here:

    http://msdn.microsoft.com/en-us/library/ms143799.aspx

    I'm hopeful this will resolve the situation.

    Thanks!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply