Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection to SQL Server
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 - wrote in message ups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection to SQL Server
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection to SQL Server
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL Server Connection Pooling | Excel Programming | |||
ADO connection problem when using server | Excel Programming | |||
ado connection to access vs. sql server? | Excel Programming | |||
Check for valid SQL server connection | Excel Programming | |||
sql server connection | Excel Programming |