We help IT Professionals succeed at work.

Connection string for multiple databases

LZ1
LZ1 asked
on
Hey Experts!!

I have a Classic ASP/VBScript application I'm building that requires a connection to more than 1 database at the same time.

I'm not sure how I would setup the connection string in my connection to do this.
The current connection string is below.

Any help is appreciated.
PROVIDER=SQLOLEDB;SERVER=TMCFILE;UID=username;PWD=password;DATABASE=DB1

Open in new window

Comment
Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Knock yourself out -->  http://www.connectionstrings.com/
LZ1
Top Expert 2011

Author

Commented:
Thanks Jim. But I couldn't find how to connect to more than 1 database.  Would I do it like this?
PROVIDER=SQLOLEDB;SERVER=TMCFILE;UID=username;PWD=password;DATABASE=DB1
PROVIDER=SQLOLEDB;SERVER=TMCFILE;UID=username;PWD=password;DATABASE=DB2

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you can either connect to each db with a dedicated connection (and it's connection string),
or plainly connect to the master db, and use the db name when calling tables, views, procedure:

select t.* from db1.dbo.tablename t where t.pk = 1

exec db2.dbo.myproc @id = 2
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Each connection (ADODB, DAO, whatever) can have one and only one connection string.
If you have two data sources, then you need two connections, each with it's own connection string.
LZ1
Top Expert 2011

Author

Commented:
Thanks for the answers guys.  If I use 2 data sources then, can I still use regular SQL statements that grab tables from both db's?

Or if I use 2 different connection strings how would that look? Just like I posted the code above?
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>Thanks for the answers guys.  If I use 2 data sources then, can I still use regular SQL statements that grab tables from both db's?
No.  Each connection is for one db, and afaik all SQL you run off of a connection is for that connection only; it is not possible to do cross-database SQL.

If you require cross-database SQL then you're going to have to code that in a view/SP spelling out [database].[schema].table everywhere, then call that in a single connection.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
I agree with that.
LZ1
Top Expert 2011

Author

Commented:
Ok, so just to recap then:

I have a cross-database SELECT statement that requires 2 connections. So I would need to do a view or something else on the server side and then calling the connection from that?
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:

>I have a cross-database SELECT statement
Fine, but you'll need to create a view on the server to do cross-select.

>that requires 2 connections.
No, any SQL statment is run off of one and only one connection

>So I would need to do a view or something else on the server side
Yes

>... and then calling the connection from that?
You use the connection to execute the view (or something)
LZ1
Top Expert 2011

Author

Commented:
Thanks guys!
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade.  Good luck with your project.  -Jim