Connection to multiple databases
I have a server with multiple ODBC connections.
I want to create a connection string that will connect to all those ODBC (each for one database), so that when I run a query it will query all databases.
This is my current connection script
In the page I have an include file:
The myconnection.asp script is:
The above will allow me to connect to the first database via ODBC. the first odbc is named: odbc1 then I list the username and password for that ODBC connection.
How can I add code so that it also connects to odbc2, odbc3, odbc4, etc.
As an example we can assume user2, user3, user4 are the users for each ODBC and same for passwords, password2, password3, etc.
Then If I run a query for example:
select * from contacts
Will it run it against ALL the databases included in the connection ? (This is the goal)
I want to create a connection string that will connect to all those ODBC (each for one database), so that when I run a query it will query all databases.
This is my current connection script
In the page I have an include file:
<!--#include file="../Connections/myconnection.asp" -->The myconnection.asp script is:
<%
' FileName="Connection_odbc_conn_dsn.htm"
' Type="ADO"
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_bluedot_STRING
MM_bluedot_STRING = "dsn=odbc1;uid=user1;pwd=password1;"
%>The above will allow me to connect to the first database via ODBC. the first odbc is named: odbc1 then I list the username and password for that ODBC connection.
How can I add code so that it also connects to odbc2, odbc3, odbc4, etc.
As an example we can assume user2, user3, user4 are the users for each ODBC and same for passwords, password2, password3, etc.
Then If I run a query for example:
select * from contacts
Will it run it against ALL the databases included in the connection ? (This is the goal)
Big Monty
You cannot do this, you would need to create linked tables throughout your databases, and I believe that's only good for Access. For sql server you need to have a separate connection object for each database you want to connect to.
It's there a specific reason why you only want one connection object?
ASKER
If i create a connection object for each database it will query all databases with the same query ?
And I guess I can simply include the connection string for that database like I did for the first one, correct ?
And I guess I can simply include the connection string for that database like I did for the first one, correct ?
And, why would you want to connect to multiple databases? You should run the query against the database that has the data you need. If you have multiple databases with the data you need, you need to loop through and connect to each, or handle this with linked servers and views in SQL.
ASKER
Ok. I created two connection objects and included them both on my page. But the SQL only queries one.
Below is my code, how do I change it so that it queries all the connections ?
Below is my code, how do I change it so that it queries all the connections ?
Dim rs_tickets
Dim rs_tickets_cmd
Dim rs_tickets_numRows
Set rs_tickets_cmd = Server.CreateObject ("ADODB.Command")
rs_tickets_cmd.ActiveConnection = MM_odbc1_STRING
rs_tickets_cmd.CommandText = "SELECT a.id , a.firmid , a.startedby , a.startedon , a.ticket_status , a.subject , a.cc , a.browser, a.bug_type, a.ticket_type , a.form_name , a.form_page , a.form_section , a.form_question , a.ticket_description , b.MaidenNm + ' ' + b.MailStr AS attyname FROM support_tickets a INNER JOIN Users AS b ON b.UserId = a.startedby WHERE a.firmid = ? order by startedon desc"
rs_tickets_cmd.Prepared = true
rs_tickets_cmd.Parameters.Append rs_tickets_cmd.CreateParameter("param1", 5, 1, -1, rs_tickets__mmcolparam) ' adDouble
Set rs_tickets = rs_tickets_cmd.Execute
rs_tickets_numRows = 0ASKER
For the views option. How can I create a view for multiple databases ?
I am querying to see which users logged in for each database on a certain day, this is why I need to query each database.
I am querying to see which users logged in for each database on a certain day, this is why I need to query each database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How about creating just one connection. And the user created in MS SQL with permissions to read all databases.
Then my query could specify.
Select * from database1.contacts
Union
Select * from database2.contacts
Would that work ? One single query, one single connection querying all databases.
Then my query could specify.
Select * from database1.contacts
Union
Select * from database2.contacts
Would that work ? One single query, one single connection querying all databases.
ASKER
I tested and it worked. The only problem is every time I create a new datbase I need to update the query on my page.
Perhaps I can create a view and I update the view and leave the ASP alone. That is probably easier.
I was able to retrieve results from two databases by doing the union as stated above, with one ODBC and one connection string.
Also, every time i add a new database I need to provide my ODBC user permissions to read that new database, that should not be a big issue.
Perhaps I can create a view and I update the view and leave the ASP alone. That is probably easier.
I was able to retrieve results from two databases by doing the union as stated above, with one ODBC and one connection string.
Also, every time i add a new database I need to provide my ODBC user permissions to read that new database, that should not be a big issue.
side note: UNION ALL is probably what you want, and not UNION.
https://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
https://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
ASKER
Right.
ASKER
Thanks to both.