Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 262 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
ADO jumps back to default
Sunset
Member Posts: 200
Can someone please tell me how to force the ADO connection to stay on the instance?
Setup:
Server S004 is running 2 SQL servers. Vanilla SQL 2008, and SQL 2012 as instance SQL2012.
When connecting to the databases you point to s004 if you want bases on SQL2008, and s004\SQL2012 if you want bases on the SQL2012 instance. Connections are fine, service run without any problem; everybody is happy!
Well, everybody except me. On a different machine/setup I regularly need to get data on databases on s004\SQL2012 into a 2009R2 database. So instead of having to copy/paste I decided to make a little code snippet that would connect to the databases, and grab the data for me. But for some reason beyond me, the connection refuses to stick on the instance and will instead insist on going to the default installation.
For instance shoting off "Provider=SQLOLEDB;Data Source=S004\SQL2012,1433;Initial Catalog=LM;Trusted_Connection=Yes" will result in an error saying "Cannot open database LM requested by the login"
I am sysadmin and dbowner on all databases, and can access them all with Studio Management, so access isn't an issue
I tried replacing "Initial catalog" with Database, same error. I tried username/pw instead of Trusted_Connection, I tried "Integrated Security"; all with the same errormessage.
I then tried connecting to Master just to make sure I wasn't losing my marbles. That worked perfectly, so the connection happens
Cycling through the various thoughts, I at one point pointed to a database that exists on the default s004, and all of a sudden there was life on the other side :shock:
So now my question is, why does my connection go to the default instance on s004, and how do I stop it? The database i want is on the named instance, and I want my connction to stay there :x
Any help/hints are welcome.
Setup:
Server S004 is running 2 SQL servers. Vanilla SQL 2008, and SQL 2012 as instance SQL2012.
When connecting to the databases you point to s004 if you want bases on SQL2008, and s004\SQL2012 if you want bases on the SQL2012 instance. Connections are fine, service run without any problem; everybody is happy!
Well, everybody except me. On a different machine/setup I regularly need to get data on databases on s004\SQL2012 into a 2009R2 database. So instead of having to copy/paste I decided to make a little code snippet that would connect to the databases, and grab the data for me. But for some reason beyond me, the connection refuses to stick on the instance and will instead insist on going to the default installation.
For instance shoting off "Provider=SQLOLEDB;Data Source=S004\SQL2012,1433;Initial Catalog=LM;Trusted_Connection=Yes" will result in an error saying "Cannot open database LM requested by the login"
I am sysadmin and dbowner on all databases, and can access them all with Studio Management, so access isn't an issue
I tried replacing "Initial catalog" with Database, same error. I tried username/pw instead of Trusted_Connection, I tried "Integrated Security"; all with the same errormessage.
I then tried connecting to Master just to make sure I wasn't losing my marbles. That worked perfectly, so the connection happens
Cycling through the various thoughts, I at one point pointed to a database that exists on the default s004, and all of a sudden there was life on the other side :shock:
So now my question is, why does my connection go to the default instance on s004, and how do I stop it? The database i want is on the named instance, and I want my connction to stay there :x
Any help/hints are welcome.
Don't just take my word for it, test it yourself
0
Answers
"Provider=SQLOLEDB;Data Source=S004\SQL2012;Initial Catalog=LM;Trusted_Connection=Yes" did the trick
All other instances get a "random" port, which is only needed to be known by the SQL Browser service. (The SQL Browser uses port 1434 by the way.)
If you dont wish to have the SQL Browser service running, you can assign a specific port for your SQL instance with the SQL Configuration Mgr. Then you have to specify the port in the connection string, as you did. It just needs to be the right port :-)
Peter