sql connection string multiple databases

[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.

[cj2]

Re: sql connection string multiple databases

I don't think I'm interested in that yet.

sloan wrote:

If you need dynamic building...you can consider the
>

http://www.google.com/search?hl=en&q=connectionstringbuilder+class&aq=2&oq=ConnectionStringBuilder

>
ConnectionStrin gBuilder.
>
>
>
>
>
>
>
"cj2" <cj2@nospam.nos pamwrote in message
news:e0eczaERJH A.4824@TK2MSFTN GP02.phx.gbl...

>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.

>
>

[cj2]

Re: sql connection string multiple databases

Well, actually from playing around I found I could do:

Using mySqlConnection As New SqlConnection(" packet
size=4096;..... .initial catalog=custome r")
MySqlReader = New SqlCommand("sel ect count(*) from
[inventory].[dbo].[orders] where ......", mySqlConnection ).ExecuteReader
If MySqlReader.Has Rows 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.



sloan wrote:

Yes, each connection string should be unique for each DataStore .
I use DataStore because a connection string can be setup for Databases as
well as TextFiles, ExcelFiles, etc, etc.
>
..........
>
>
>
>
"cj2" <cj2@nospam.nos pamwrote in message
news:e0eczaERJH A.4824@TK2MSFTN GP02.phx.gbl...

>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.

>
>

[\Ji Zhou [MSFT]\]

Re: sql connection string multiple databases

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 (v-jzho@online.mic rosoft.com, 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:
msdnmg@microsof t.com.

=============== =============== =============== =====
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#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/...tance&ln=en-us.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

[cj2]

Re: sql connection string multiple databases

I don't understand why you say it will not work. It does. My sql
connection string can say "initial catalog=custome r". 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.


v-jzho@online.mic rosoft.com wrote:

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 (v-jzho@online.mic rosoft.com, 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:
msdnmg@microsof t.com.
>
=============== =============== =============== =====
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#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/...tance&ln=en-us.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.
>

[Lloyd Sheen]

Re: sql connection string multiple databases


"cj2" <cj2@nospam.nos pamwrote in message
news:%23ig$7FOS JHA.5092@TK2MSF TNGP05.phx.gbl. ..

>I don't understand why you say it will not work. It does. My sql
>connection string can say "initial catalog=custome r". 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.
>
>
v-jzho@online.mic rosoft.com wrote:

>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 (v-jzho@online.mic rosoft.com, 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:
>msdnmg@microsof t.com.
>>
>============== =============== =============== ======
>Get notification to my posts through email? Please refer to
>http://msdn.microsoft.com/en-us/subs...#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/...tance&ln=en-us.
>============== =============== =============== ======
>This posting is provided "AS IS" with no warranties, and confers no
>rights.
>>

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

[cj2]

Re: sql connection string multiple databases

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 Sheen wrote:

>
"cj2" <cj2@nospam.nos pamwrote in message
news:%23ig$7FOS JHA.5092@TK2MSF TNGP05.phx.gbl. ..

>I don't understand why you say it will not work. It does. My sql
>connection string can say "initial catalog=custome r". 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.
>>
>>
>v-jzho@online.mic rosoft.com wrote:

>>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 (v-jzho@online.mic rosoft.com, 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: msdnmg@microsof t.com.
>>>
>>============= =============== =============== =======
>>Get notification to my posts through email? Please refer to
>>http://msdn.microsoft.com/en-us/subs...#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
>>investigati on 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/...tance&ln=en-us.
>>>
>>============= =============== =============== =======
>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>>

>
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