sql connection string multiple databases

  • Thread starter Thread starter cj2
  • Start date Start date
C

cj2

If from withing one program I want to access multiple databases on a sql
server do I need to make multiple connections? The connection string
says initial catalog. I'd assume I could denote the database/catalog in
the query but I'm not sure the notation to use.
 
Well, actually from playing around I found I could do:

Using mySqlConnection As New SqlConnection("packet
size=4096;......initial catalog=customer")
MySqlReader = New SqlCommand("select count(*) from
[inventory].[dbo].[orders] where ......", mySqlConnection).ExecuteReader
If MySqlReader.HasRows Then
........

So my connection string says the initial catalogue will be the customer
database but the query is actually from the inventory database. This is
what I was asking. I'm open to comments from anyone who wants to share.
 
Hello Chris,

Glad to see you again! Actually, one SqlConnection can only target at one
server database. That is to say, after specifying the database using
Initial catalog in the connection string, the target database is already
determined and cannot be changed. If we want to query from another
database, we have to build another connection string for that new database.

I can understand your concern that you just want to initial the connection
only for the first time, and then you are convenient to query all database
by specifying the database name in query command like we do in the
management studio. But the Visual Studio programming modal is just not
designed to be that way. Different from inside the SQL Server Management
Studio, it is the programmer's duty to navigate to other database by
creating new connection string. And then the query to the new database can
be performed.

Please let me know if you have any future concerns or questions on this,
and I will try my best to follow up.

Have a nice day, Chris!


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
I don't understand why you say it will not work. It does. My sql
connection string can say "initial catalog=customer". The I run the
command "select count(*) from [inventory].[dbo].[orders]" which has
nothing to do with the customer database. It is the inventory database.
So in essence I have found I can make one sql connection and reference
any database on that sql server I want using that same connection.
 
cj2 said:
I don't understand why you say it will not work. It does. My sql
connection string can say "initial catalog=customer". The I run the
command "select count(*) from [inventory].[dbo].[orders]" which has nothing
to do with the customer database. It is the inventory database. So in
essence I have found I can make one sql connection and reference any
database on that sql server I want using that same connection.


Hello Chris, Glad to see you again! Actually, one SqlConnection can only
target at one server database. That is to say, after specifying the
database using Initial catalog in the connection string, the target
database is already determined and cannot be changed. If we want to query
from another database, we have to build another connection string for
that new database.

I can understand your concern that you just want to initial the
connection only for the first time, and then you are convenient to query
all database by specifying the database name in query command like we do
in the management studio. But the Visual Studio programming modal is just
not designed to be that way. Different from inside the SQL Server
Management Studio, it is the programmer's duty to navigate to other
database by creating new connection string. And then the query to the new
database can be performed. Please let me know if you have any future
concerns or questions on this, and I will try my best to follow up.

Have a nice day, Chris!


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you.
Please feel free to let my manager know what you think of the level of
service provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues where an initial response from the community or a Microsoft
Support Engineer within 1 business day is acceptable. Please note that
each follow up response may take approximately 2 business days as the
support professional working with you may need further investigation to
reach the most efficient resolution. The offering is not appropriate for
situations that require urgent, real-time or phone-based interactions or
complex project analysis and dump analysis issues. Issues of this nature
are best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
Click to expand...

This is (has been) what you could do in SQL Server from v4.2. What you are
finding is that you are using a user account that allows access to both
databases. Hey you can add a linked server and get data from another
database server entirely.

LS
 
Yes, the user name as far as I'm concerned is for the server as a whole
and is good for any database on the server. I hadn't thought about it,
but of course there might be places that give out user names for
individual databases and I can see where that would require a separate
connection per database. Thanks for the clarification.


Lloyd said:
cj2 said:
I don't understand why you say it will not work. It does. My sql
connection string can say "initial catalog=customer". The I run the
command "select count(*) from [inventory].[dbo].[orders]" which has
nothing to do with the customer database. It is the inventory
database. So in essence I have found I can make one sql connection and
reference any database on that sql server I want using that same
connection.


Hello Chris, Glad to see you again! Actually, one SqlConnection can
only target at one server database. That is to say, after specifying
the database using Initial catalog in the connection string, the
target database is already determined and cannot be changed. If we
want to query from another database, we have to build another
connection string for that new database.

I can understand your concern that you just want to initial the
connection only for the first time, and then you are convenient to
query all database by specifying the database name in query command
like we do in the management studio. But the Visual Studio
programming modal is just not designed to be that way. Different
from inside the SQL Server Management Studio, it is the programmer's
duty to navigate to other database by creating new connection string.
And then the query to the new database can be performed. Please let
me know if you have any future concerns or questions on this, and I
will try my best to follow up.

Have a nice day, Chris!


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments
and suggestions about how we can improve the support we provide to
you. Please feel free to let my manager know what you think of the
level of service provided. You can send feedback directly to my
manager at: (e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.


Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues where an initial response from the community or a Microsoft
Support Engineer within 1 business day is acceptable. Please note
that each follow up response may take approximately 2 business days
as the support professional working with you may need further
investigation to reach the most efficient resolution. The offering is
not appropriate for situations that require urgent, real-time or
phone-based interactions or complex project analysis and dump
analysis issues. Issues of this nature are best handled working with
a dedicated Microsoft Support Engineer by contacting Microsoft
Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.

==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
Click to expand...

This is (has been) what you could do in SQL Server from v4.2. What you
are finding is that you are using a user account that allows access to
both databases. Hey you can add a linked server and get data from
another database server entirely.

LS
Click to expand...
 
Back
Top