ADO Connection to SQL Server

I have an ADO connection to a SQL server database.
Physical Server Name = XYZ
SQL Server instance = XYZ\ABC
Database = MyDatabase
Table = tblMyTable

All connects fine with
Provider='SQLOLEDB';Server='XYZ';Data Source ='XYZ\ABC';
Initial Catalog='MyDatabase';

And any queries run OK with data returned to the spreadsheet.

However there are two methods of connecting, one using (as I understand
it) Dynamic ports and one using static ports. The machine (Client) that
it works on uses a static port for the XYZ machine.
(I can determine this from the ODBC connection even though I don't use
ODBC)
but a machine that uses dynamic ports on ODBC doesn't connect to the
server with the above connection string.
Error message: 'Specified SQL Server not found'
I have tried replacing the Server name with the IP address as
determined from using Ping XYZ (works fine).
I know the ODBC is a red herring but it seems to be the only thing that
I can think of that may give a clue.
Is there a different syntax that I should be using ?
The whole idea is to avoid having to configure other machines when
distributing the spreadsheet.

Thanks

Try connecting using MsQuery. If you can do that you can record the session
and obtain the correct syntax.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

<mal_k100@hotmail.com> wrote in message
news:1122987957.722860.33030@g47g2000cwa.googlegroups.com...
> I have an ADO connection to a SQL server database.
> Physical Server Name = XYZ
> SQL Server instance = XYZ\ABC
> Database = MyDatabase
> Table = tblMyTable
>
> All connects fine with
> Provider='SQLOLEDB';Server='XYZ';Data Source ='XYZ\ABC';
> Initial Catalog='MyDatabase';
>
> And any queries run OK with data returned to the spreadsheet.
>
> However there are two methods of connecting, one using (as I understand
> it) Dynamic ports and one using static ports. The machine (Client) that
> it works on uses a static port for the XYZ machine.
> (I can determine this from the ODBC connection even though I don't use
> ODBC)
> but a machine that uses dynamic ports on ODBC doesn't connect to the
> server with the above connection string.
> Error message: 'Specified SQL Server not found'
> I have tried replacing the Server name with the IP address as
> determined from using Ping XYZ (works fine).
> I know the ODBC is a red herring but it seems to be the only thing that
> I can think of that may give a clue.
> Is there a different syntax that I should be using ?
> The whole idea is to avoid having to configure other machines when
> distributing the spreadsheet.
>
> Thanks
>

Andy Wiggins wrote:
> Try connecting using MsQuery. If you can do that you can record the session
> and obtain the correct syntax.


MSQuery requires ODBC
I do NOT want to use anything that requires a client machine to be
configured. ADO is the correct way, I need the syntax for a connection
where the SQL Server instance name is different from the physical
server device and using the dynamic port seems to be a problemin this
instance. In the past I think I have always used a server instance and
server device with the same name i.e. physical server FRED has SQL
Server instance FRED running on it within which it is possible to
connect to one or more databases.

If anyone has actually achieved this and has the syntax it would be
appreciated.

Thanks

The solution !
There are two parameters for the Connection string. The one I had
missing was:
Network = xxxxxxx
Another that is relevant for dynamic port machines (?) seems to be the:
Address = yyyyyyy,port

Hope it works for others.

I have just run the spreadsheet from a machine that needed no
configuration of ODBC etc.