Query across servers

I'm new to SQL and I'm trying to write a stored procdeure in my current database that queries a different database on a different server. What is the best way to do this?
Thanks and sorry if it's too remedial a question.you will need to set up a linked server. there is a great product documentation tool called Books Online under Start\Programs\Micorsoft SQL Server. Use the search feature for "linked server". You can then reference your the linked server using openquery or you can create a view that created with openquery. again see books online about openquery and views.|||You can use OpenDataSource. Check that from BOL.

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories

Query Across Servers

Thanks! I'll try again.

quote:

---

>--Original Message--
>Yes it is possible. You will need to fully qualify your
>select path. For example
>SELECT A.TESTDATA1, B.TESTDATA2
> FROM <Server1>.<Database>.dbo.<Table> A,
> <Server2>.<Database>.dbo.<Table> B
>WHERE A.TESTDATA1 = B.TESTDATA2
>As long as the User account you are using has access to
>both database (Trusted Connection) it should work fine.
>
data[QUOTE]
>.
>

---

you can indeed do joins across tables between sqlservers. However watch out
for performance. If you want it done more flexiibly & faster or want to do
updates, check out my company.
Regards
David Penney - MetaMatrix
http://www.metamatrix.com
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:8de901c3ea6d$a19c8470$a101280a@.phx.gbl...
> Thanks! I'll try again.
>
> datasql

Query Across Servers

Hi,
Is it possible to execute a query asking for joined data
from different tables on two different servers and
databases?
SQLServer1 SQLServer2
-- --
09.0.0.30\TestData 198.160.30.20\TestData2
I need to run a query against tables in the TestData &
TestData2 databases located at the I.P. locations
simulated above. Joins are necessary.
Thanks in advance.You can use linked servers and then fully qualify database objects, for
example:
SELECT * FROM SQLServer1.database.dbo.tablename -- from server2
You can find more info about linked servers in books online...
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"DBa" <anonymous@.discussions.microsoft.com> wrote in message
news:639101c3e5cb$e41662e0$a601280a@.phx.gbl...

quote:

---

> Hi,
> Is it possible to execute a query asking for joined data
> from different tables on two different servers and
> databases?
> SQLServer1 SQLServer2
> -- --
> 09.0.0.30\TestData 198.160.30.20\TestData2
> I need to run a query against tables in the TestData &
> TestData2 databases located at the I.P. locations
> simulated above. Joins are necessary.
> Thanks in advance.

---

|||alternate solution is to use OPENROWSET if you dont have permissions to
create linked server
Ex:
SELECT a.*
FROM OPENROWSET('SQLOLEDB','server1';'user1';
'password1',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
FROM OPENROWSET('SQLOLEDB','server2';'user1';
'password1',
'SELECT * FROM pubs.dbo.titleauthor') AS b
where a.au_id = b.au_id
GO
SQL Booksonline has more samples on OPENROWSET
Sethu Srinivasan
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:O2EjQ6h5DHA.2496@.TK2MSFTNGP09.phx.gbl...

quote:

---

> You can use linked servers and then fully qualify database objects, for
> example:
> SELECT * FROM SQLServer1.database.dbo.tablename -- from server2
> You can find more info about linked servers in books online...
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "DBa" <anonymous@.discussions.microsoft.com> wrote in message
> news:639101c3e5cb$e41662e0$a601280a@.phx.gbl...
>

---

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardF
Yes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_nam e
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF
sql

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF

Query across multiple databases/SQL Servers

Is it possible to run a single query that pulls data from multiple
tables in multiple databases, perhaps also from multiple SQL Servers?
Any input would be appreciated.
Thanks!
RichardFYes. Specify the server and database:
select Select_Column_List
from Server_Name.DB_Name.Object_owner.Table_or_view_name
If from another server, you need to set up a linked server. See Linked
Server in Books Online.
"RichardF" <no.one@.no.where.com> wrote in message
news:41acf571.7680223@.msnews.microsoft.com...
> Is it possible to run a single query that pulls data from multiple
> tables in multiple databases, perhaps also from multiple SQL Servers?
> Any input would be appreciated.
> Thanks!
> RichardF

Query accross servers

Hi,
Please could someone provide me with a best example of how to query records
from different tables in different databases, where the databases are
located on different servers on the same network.
Your assistance is much appreciated.If you want to use different servers you have to build up some linked
servers (look in the BOL). There you can go with the four-point name to
query them.
Select * from [Servername].[Databasename].[owner].[Objectname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"11Oppidan" <11Oppidan@.community.nospam> schrieb im Newsbeitrag
news:us3gSr6WFHA.3092@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Please could someone provide me with a best example of how to query
> records from different tables in different databases, where the databases
> are located on different servers on the same network.
> Your assistance is much appreciated.
>|||You may also want to check out OPENQUERY in BOL. Sometimes it yields
better performance than four-part names.
Here's an excerpt from BOL:
This example creates a linked server named OracleSvr against an Oracle
database using the Microsoft OLE DB Provider for Oracle. Then this
example uses a pass-through query against this linked server.
Note This example assumes that an Oracle database alias called ORCLDB
has been created.
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
HTH...
--
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Wed, 18 May 2005 15:13:15 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>If you want to use different servers you have to build up some linked
>servers (look in the BOL). There you can go with the four-point name to
>query them.
>Select * from [Servername].[Databasename].[owner].[Objectname]|||Thanks guys!
"Joe Webb" <joew@.webbtechsolutions.com> wrote in message
news:67gm815emd4m6g2vb7dr70tsn01vsciaon@.
4ax.com...
> You may also want to check out OPENQUERY in BOL. Sometimes it yields
> better performance than four-part names.
> Here's an excerpt from BOL:
> This example creates a linked server named OracleSvr against an Oracle
> database using the Microsoft OLE DB Provider for Oracle. Then this
> example uses a pass-through query against this linked server.
> Note This example assumes that an Oracle database alias called ORCLDB
> has been created.
>
> EXEC sp_addlinkedserver 'OracleSvr',
> 'Oracle 7.3',
> 'MSDAORA',
> 'ORCLDB'
> GO
> SELECT *
> FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
> GO
>
> HTH...
> --
> Joe Webb
> SQL Server MVP
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
> On Wed, 18 May 2005 15:13:15 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>

Queries between servers

This has happened multiple times but I will only mention one story to lay the
groundwork.
One of our data servers crashed. We built a new box from scratch (the only
difference is that it's x64 OS instead of x32), and I got the databases back
online from the system dbs and the SAN.
We have had a problem when DTS packages run from a remote server that the
time it takes to run has increased over time. The servers are linked, and I
guess they were before I got here so I am not sure about settings and such.
I just realized that in one of the instances the service packs differ b/t 3
and 4, but there are other DTS jobs that go from an SP4 box to an SP4 box and
it still happens.
Where should I begin to look to see how to fix or address this slowness? Any
tips are appreciated.
ThanksThere is a HUGE range of things that could be causing your issue. someone
modified a setting or structure, san setting changes or workload, service
pack diffs, 32/64bit driver diffs, os file fragmentation, internal database
data/index fragmentation, etc, etc. You could spend days or even weeks
going back and forth on this forum answering follow-on questions from
posters to try to isolate the problem (if there even is one) and get no
where. Or you could call microsoft or get with a consultant and pay a
professional to isolate the situation for you. My bet is that the latter
will provide a much better ROI.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:75BA70E2-2C0E-407F-BBDD-7C8C9481193C@.microsoft.com...
> This has happened multiple times but I will only mention one story to lay
> the
> groundwork.
> One of our data servers crashed. We built a new box from scratch (the only
> difference is that it's x64 OS instead of x32), and I got the databases
> back
> online from the system dbs and the SAN.
> We have had a problem when DTS packages run from a remote server that the
> time it takes to run has increased over time. The servers are linked, and
> I
> guess they were before I got here so I am not sure about settings and
> such.
> I just realized that in one of the instances the service packs differ b/t
> 3
> and 4, but there are other DTS jobs that go from an SP4 box to an SP4 box
> and
> it still happens.
> Where should I begin to look to see how to fix or address this slowness?
> Any
> tips are appreciated.
> Thanks

Queries between servers

This has happened multiple times but I will only mention one story to lay th
e
groundwork.
One of our data servers crashed. We built a new box from scratch (the only
difference is that it's x64 OS instead of x32), and I got the databases back
online from the system dbs and the SAN.
We have had a problem when DTS packages run from a remote server that the
time it takes to run has increased over time. The servers are linked, and I
guess they were before I got here so I am not sure about settings and such.
I just realized that in one of the instances the service packs differ b/t 3
and 4, but there are other DTS jobs that go from an SP4 box to an SP4 box an
d
it still happens.
Where should I begin to look to see how to fix or address this slowness? Any
tips are appreciated.
ThanksThere is a HUGE range of things that could be causing your issue. someone
modified a setting or structure, san setting changes or workload, service
pack diffs, 32/64bit driver diffs, os file fragmentation, internal database
data/index fragmentation, etc, etc. You could spend days or even weeks
going back and forth on this forum answering follow-on questions from
posters to try to isolate the problem (if there even is one) and get no
where. Or you could call microsoft or get with a consultant and pay a
professional to isolate the situation for you. My bet is that the latter
will provide a much better ROI.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:75BA70E2-2C0E-407F-BBDD-7C8C9481193C@.microsoft.com...
> This has happened multiple times but I will only mention one story to lay
> the
> groundwork.
> One of our data servers crashed. We built a new box from scratch (the only
> difference is that it's x64 OS instead of x32), and I got the databases
> back
> online from the system dbs and the SAN.
> We have had a problem when DTS packages run from a remote server that the
> time it takes to run has increased over time. The servers are linked, and
> I
> guess they were before I got here so I am not sure about settings and
> such.
> I just realized that in one of the instances the service packs differ b/t
> 3
> and 4, but there are other DTS jobs that go from an SP4 box to an SP4 box
> and
> it still happens.
> Where should I begin to look to see how to fix or address this slowness?
> Any
> tips are appreciated.
> Thanks

Queries between servers

This has happened multiple times but I will only mention one story to lay the
groundwork.
One of our data servers crashed. We built a new box from scratch (the only
difference is that it's x64 OS instead of x32), and I got the databases back
online from the system dbs and the SAN.
We have had a problem when DTS packages run from a remote server that the
time it takes to run has increased over time. The servers are linked, and I
guess they were before I got here so I am not sure about settings and such.
I just realized that in one of the instances the service packs differ b/t 3
and 4, but there are other DTS jobs that go from an SP4 box to an SP4 box and
it still happens.
Where should I begin to look to see how to fix or address this slowness? Any
tips are appreciated.
Thanks
There is a HUGE range of things that could be causing your issue. someone
modified a setting or structure, san setting changes or workload, service
pack diffs, 32/64bit driver diffs, os file fragmentation, internal database
data/index fragmentation, etc, etc. You could spend days or even weeks
going back and forth on this forum answering follow-on questions from
posters to try to isolate the problem (if there even is one) and get no
where. Or you could call microsoft or get with a consultant and pay a
professional to isolate the situation for you. My bet is that the latter
will provide a much better ROI.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:75BA70E2-2C0E-407F-BBDD-7C8C9481193C@.microsoft.com...
> This has happened multiple times but I will only mention one story to lay
> the
> groundwork.
> One of our data servers crashed. We built a new box from scratch (the only
> difference is that it's x64 OS instead of x32), and I got the databases
> back
> online from the system dbs and the SAN.
> We have had a problem when DTS packages run from a remote server that the
> time it takes to run has increased over time. The servers are linked, and
> I
> guess they were before I got here so I am not sure about settings and
> such.
> I just realized that in one of the instances the service packs differ b/t
> 3
> and 4, but there are other DTS jobs that go from an SP4 box to an SP4 box
> and
> it still happens.
> Where should I begin to look to see how to fix or address this slowness?
> Any
> tips are appreciated.
> Thanks

Queries between databases

We have some diffrent databases on our servers and we sometimes want to
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?
You can add views to your database which include the database name for the
external objects then you can just access the view name.
It means releasing different views in the different envronments but the rest
of the code will stay the same.
"stuckish@.gmail.com" wrote:

> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>
|||What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegrou ps.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>
|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegrou ps.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>
|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
[vbcol=seagreen]
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegrou ps.com...
|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegro ups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>

Queries between databases

We have some diffrent databases on our servers and we sometimes want to
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?You can add views to your database which include the database name for the
external objects then you can just access the view name.
It means releasing different views in the different envronments but the rest
of the code will stay the same.
"stuckish@.gmail.com" wrote:

> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
[vbcol=seagreen]
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegroups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>
>

Queries between databases

We have some diffrent databases on our servers and we sometimes want to
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> > We have some diffrent databases on our servers and we sometimes want to
> > make queries between them. The problem is that we have diffrent names
> > for the databases. In the dev enviroment the databases is called
> > database1_dev and in stage it's called database2_stage and so on.
> >
> > What is the best way to create some sort of alias or something so that
> > i can add a query to dev that i don't have to change when i move it to
> > stage.
> >
> > For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> >
> > As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> > database2_dev.table1 ..
> >
> > And i have to change it on deployment. There must be some good solution
> > for this right?
> >|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegroups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>> What is the version are you using?
>>
>> <stuckish@.gmail.com> wrote in message
>> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
>> > We have some diffrent databases on our servers and we sometimes want to
>> > make queries between them. The problem is that we have diffrent names
>> > for the databases. In the dev enviroment the databases is called
>> > database1_dev and in stage it's called database2_stage and so on.
>> >
>> > What is the best way to create some sort of alias or something so that
>> > i can add a query to dev that i don't have to change when i move it to
>> > stage.
>> >
>> > For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
>> >
>> > As it is now i must have SELECT * FROM database1_dev.table1 JOIN
>> > database2_dev.table1 ..
>> >
>> > And i have to change it on deployment. There must be some good solution
>> > for this right?
>> >
>

Queries Across Servers

Hey all,

Is there a way to qualify a table name or whatever with the SQL name as well? We have serveral SQL Servers and I want to be able to update/query multiple SQL Servers from one statement.

For example:

select * from "is-dbdev".master.dbo.sysfiles

where "is-dbdev" is the name of the server. Can you do this? I have not had any luck thus far...and I get errors when I try to add the other SQL Server as a linked server.

Can anyone help?

Thank you.You have to add the remote server as a linked server, you also have to have a linked server login. Lookup sp_addlinkedserver in BOL.

Once added the syntax is

servername.databasename.ownername.tablename

HTH

QA green button grayed out

I'm running QA on my local PC and going through an ODBC
connection, I've been able to connect to SQL servers on
our network and execute TSQL statements.
Suddenly, last week, the green button is grayed out. No
matter what server. Everything else looks ok. All
features in EM seem to work. In QA, I can connect to the
correct DB and that seems ok. I can type in a command.
But, no green execute button!
I'm running SQL 2000 on my PC. I'm attaching to SQL 2000
and SQL 7.0 servers. The local OS is XP.
Any ideas?
Thanks,
DonHi
And does Ctrl+E work?
John
"Don" <ddachner@.hotmail.com> wrote in message
news:00ad01c3cfd7$463e4470$a501280a@.phx.gbl...
> I'm running QA on my local PC and going through an ODBC
> connection, I've been able to connect to SQL servers on
> our network and execute TSQL statements.
> Suddenly, last week, the green button is grayed out. No
> matter what server. Everything else looks ok. All
> features in EM seem to work. In QA, I can connect to the
> correct DB and that seems ok. I can type in a command.
> But, no green execute button!
> I'm running SQL 2000 on my PC. I'm attaching to SQL 2000
> and SQL 7.0 servers. The local OS is XP.
> Any ideas?
> Thanks,
> Don
>

Q186726 Error 80004005 Occurs When Retrieving Data from SQL Server

Does anyone know if this article is valid for connection between servers.
I'm getting an intermittent
|308|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired when a
website running on a windows 2000sp3 tries to write data to sql 2000 server
running on windows 2003 server. Could this be the cause? Is there anything
else that could be causing this problem?
Hi
Run sp_who2 and look for blocking processes when this occurs. Intermittent
issues like this point to row blocking issues in the database
One process might update a row, hold the locks, whilst a second process
tries to do the same. The 2nd process waits until process 1 is finished and
usually after 90 seconds, the MDAC stack gives up and returns the error.
Regards
Mike
"shalafi" wrote:

> Does anyone know if this article is valid for connection between servers.
> I'm getting an intermittent
> |308|80004005|[Microsoft][ODBC_SQL_Server_Driver]Timeout_expired when a
> website running on a windows 2000sp3 tries to write data to sql 2000 server
> running on windows 2003 server. Could this be the cause? Is there anything
> else that could be causing this problem?

Q186726 Error 80004005 Occurs When Retrieving Data from SQL Server

Does anyone know if this article is valid for connection between servers.
I'm getting an intermittent
|308|80004005|[Microsoft]& #91;ODBC_SQL_Server_Driver]Timeout_expir
ed whe
n a
website running on a windows 2000sp3 tries to write data to sql 2000 server
running on windows 2003 server. Could this be the cause? Is there anything
else that could be causing this problem?Hi
Run sp_who2 and look for blocking processes when this occurs. Intermittent
issues like this point to row blocking issues in the database
One process might update a row, hold the locks, whilst a second process
tries to do the same. The 2nd process waits until process 1 is finished and
usually after 90 seconds, the MDAC stack gives up and returns the error.
Regards
Mike
"shalafi" wrote:

> Does anyone know if this article is valid for connection between servers.
> I'm getting an intermittent
> |308|80004005|[Microsoft]& #91;ODBC_SQL_Server_Driver]Timeout_expir
ed w
hen a
> website running on a windows 2000sp3 tries to write data to sql 2000 serve
r
> running on windows 2003 server. Could this be the cause? Is there anythin
g
> else that could be causing this problem?