Query across multiple db's

I have two databases on one server. I want do a query across the databases. I
was moving the data and out of 370K records, 24 did not come across. How do I
query to find out which records did not make it across.
Hi,
select * from DB1..tablename where id not in(select id from DB2..tablename)
Thanks
Hari
MCDBA
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> I have two databases on one server. I want do a query across the
databases. I
> was moving the data and out of 370K records, 24 did not come across. How
do I
> query to find out which records did not make it across.
|||Did not work. I think because the PK is across 5 fields
"Hari Prasad" wrote:

> Hi,
> select * from DB1..tablename where id not in(select id from DB2..tablename)
>
> Thanks
> Hari
> MCDBA
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> databases. I
> do I
>
>
|||One way to finese this problem is:
SELECT * FROM DB1..tablename
WHERE CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
NOT IN
(SELECT
CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
FROM DB2..tablename)
That assumes that CHAR(10) will hold the conversion of your identifying
columns. Adjust appropriately.
Russell Fields
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:8655C387-155D-4AE6-BFAF-7DB3DFD50593@.microsoft.com...[vbcol=seagreen]
> Did not work. I think because the PK is across 5 fields
> "Hari Prasad" wrote:
DB2..tablename)[vbcol=seagreen]
How[vbcol=seagreen]
|||Or you could use NOT EXISTS, which in my opinion reads a bit better:
SELECT *
FROM DB1..tablename T1
WHERE NOT EXISTS
(SELECT *
FROM DB2..tablename T2
WHERE T2.IDCol1 = T1.IDCol1
AND T2.IDCol2 = T2.IDCol2
AND T2.IDCol3 = T2.IDCol3
AND T2.IDCol4 = T2.IDCol4
AND T2.IDCol5 = T2.IDCol5)
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23hli%23S7hEHA.3536@.TK2MSFTNGP12.phx.gbl...
> One way to finese this problem is:
> SELECT * FROM DB1..tablename
> WHERE CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> NOT IN
> (SELECT
> CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> FROM DB2..tablename)
> That assumes that CHAR(10) will hold the conversion of your identifying
> columns. Adjust appropriately.
> Russell Fields

Query across multiple db's

I have two databases on one server. I want do a query across the databases.
I
was moving the data and out of 370K records, 24 did not come across. How do
I
query to find out which records did not make it across.Hi,
select * from DB1..tablename where id not in(select id from DB2..tablename)
Thanks
Hari
MCDBA
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> I have two databases on one server. I want do a query across the
databases. I
> was moving the data and out of 370K records, 24 did not come across. How
do I
> query to find out which records did not make it across.|||Did not work. I think because the PK is across 5 fields
"Hari Prasad" wrote:

> Hi,
> select * from DB1..tablename where id not in(select id from DB2..tablename
)
>
> Thanks
> Hari
> MCDBA
>
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:12D870F3-4DE5-44CA-89BC-9BD387517EFC@.microsoft.com...
> databases. I
> do I
>
>|||One way to finese this problem is:
SELECT * FROM DB1..tablename
WHERE CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
NOT IN
(SELECT
CONVERT(CHAR(10), IDCol1) +
CONVERT(CHAR(10), IDCol2) +
CONVERT(CHAR(10), IDCol3) +
CONVERT(CHAR(10), IDCol4) +
CONVERT(CHAR(10), IDCol5)
FROM DB2..tablename)
That assumes that CHAR(10) will hold the conversion of your identifying
columns. Adjust appropriately.
Russell Fields
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:8655C387-155D-4AE6-BFAF-7DB3DFD50593@.microsoft.com...[vbcol=seagreen]
> Did not work. I think because the PK is across 5 fields
> "Hari Prasad" wrote:
>
DB2..tablename)[vbcol=seagreen]
How[vbcol=seagreen]|||Or you could use NOT EXISTS, which in my opinion reads a bit better:
SELECT *
FROM DB1..tablename T1
WHERE NOT EXISTS
(SELECT *
FROM DB2..tablename T2
WHERE T2.IDCol1 = T1.IDCol1
AND T2.IDCol2 = T2.IDCol2
AND T2.IDCol3 = T2.IDCol3
AND T2.IDCol4 = T2.IDCol4
AND T2.IDCol5 = T2.IDCol5)
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23hli%23S7hEHA.3536@.TK2MSFTNGP12.phx.gbl...
> One way to finese this problem is:
> SELECT * FROM DB1..tablename
> WHERE CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> NOT IN
> (SELECT
> CONVERT(CHAR(10), IDCol1) +
> CONVERT(CHAR(10), IDCol2) +
> CONVERT(CHAR(10), IDCol3) +
> CONVERT(CHAR(10), IDCol4) +
> CONVERT(CHAR(10), IDCol5)
> FROM DB2..tablename)
> That assumes that CHAR(10) will hold the conversion of your identifying
> columns. Adjust appropriately.
> Russell Fields

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

Query 2 tables - each in a different database

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

That is no problem.

SELECT a.Field, b.Field
FROM dbo.myTable a
JOIN OtherDatabase.dbo.myTable b ON a.myKey = b.myKey

|||

The select code is clear but the connection to the two databases is not when using ADO. Is one connection object used somehow or are the databases linked?

|||

WeslyB is right.

But your user has to have select rights on the other database.

You don't have to handle the rights in ADO.

Regards

Query 2 table in different databases

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

You need to use a 3-part name to reference the table from one database to another. The 3-part name consists of <database>.<schema>.<object>. The connection is only to the server and you can access any database to which you have permissions. You can either switch database context using USE <database> statement or use the 3-part name to reference objects.

Query 2 databases

Hi all,
Does anyone know how can I select data across 2 databases in SQL ? I want
to do something like this:
Select A.SentDate from dbo.DBA.TblA A, dbo.DBB.TblB B
Where B.FileName = '01012005.txt'
And A.AKey = B.BKey
But it doesn't work and give me an error "Invalid object name dbo.DBA.TblA",
guessing could be syntax error (?)... any idea?
Thanks !!
K.K>It should be
Database.owner.objectname as in
DBA.dbo.TblA
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"K.K." <someone@.microsoft.com> wrote in message
news:%23F$cuW6IFHA.4060@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> Does anyone know how can I select data across 2 databases in SQL ? I want
> to do something like this:
> Select A.SentDate from dbo.DBA.TblA A, dbo.DBB.TblB B
> Where B.FileName = '01012005.txt'
> And A.AKey = B.BKey
> But it doesn't work and give me an error "Invalid object name
> dbo.DBA.TblA", guessing could be syntax error (?)... any idea?
> Thanks !!
> K.K>
>
>

Query : Running a job/step in a loop for ALL databases

Hi,

I've written a job to export user and database permissions for all
d/b's on a server. As you can see below, the T-SQL commands are the
same for each d/b. Can anyone assist with regard to re-writing this so
that any new d/b's added do not require ammending the job (loop)?

Thx,

GC.

use master
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use msdb
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test1
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'
use test2
go
SELECT db_name()
EXEC sp_helpuser
EXEC sp_helprotect NULL, NULL, NULL, 'o s'"Garry Clarke" <gclarke@.euro.banta.com> wrote in message
news:fed38413.0310240324.77f4ce60@.posting.google.c om...
> Hi,
> I've written a job to export user and database permissions for all
> d/b's on a server. As you can see below, the T-SQL commands are the
> same for each d/b. Can anyone assist with regard to re-writing this so
> that any new d/b's added do not require ammending the job (loop)?
> Thx,
> GC.
> use master
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use msdb
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test1
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'
> use test2
> go
> SELECT db_name()
> EXEC sp_helpuser
> EXEC sp_helprotect NULL, NULL, NULL, 'o s'

A cursor is one way to do this (cursors are usually a bad idea in
application code, but can be useful for admin scripts):

declare @.db sysname

declare cur_dbs cursor fast_forward
for select name from master..sysdatabases
order by name

open cur_dbs

fetch next from cur_dbs into @.db

while @.@.fetch_status = 0
begin
select @.db
exec('exec ' + @.db + '..sp_helpuser')
exec('exec ' +@.db + '..sp_helprotect NULL, NULL, NULL, ''os''')
fetch next from cur_dbs into @.db
end

close cur_dbs
deallocate cur_dbs

Simon

query

hi,

i have a problem as a sql developer.i have never used DTS packages for
sending data in SQL databases. but i used SSIS which is a new feature
in SQL server 2005.
so do any of you think SSIS is better now a days and mostly used
compared to DTS.

jittu..On Feb 18, 7:51 pm, rrgh...@.gmail.com wrote:

Quote:

Originally Posted by

hi,
>
i have a problem as a sql developer.i have never used DTS packages for
sending data in SQL databases. but i used SSIS which is a new feature
in SQL server 2005.
so do any of you think SSIS is better now a days and mostly used
compared to DTS.
>
jittu..

I believe that SSIS in SQL 2005 is the replacement for DTS in SQL 2000
and older. I myself have not yet had the opportunity to use SSIS as
we do not yet have any production instances of SQL 2005, but from what
I have heard SSIS is more robust then DST was.

Queries using tables from diffrent Databases or SQL instances

Hello,

I am new in SSIS.

I am using an OLEDB source and setted as SQL Command.

The Query is a JOIN between different databases.

How can I make the QUERY with different source (different databases or SQL Servers)?

I mean, any solution is OK, the important is to make queries against different databases with SSIS.

Thank

You can always use two or more OLE DB sources and then use a union all or Merge Join transformations.|||

As Phil wrote in the previous post, you can add several OLEDB sources or others sources and join the data adding a UNION ALL or MERGE JOIN.

In merge Join the input data must be sorted and could ne joined by LEFT or RIGHT.

Regards!

|||

Thank|||

If you have to use an Execute SQL Task, you can do this by creating Linked servers, it can be SQL server or not.

Then use fully qualified names.

I use this to compare tables content side by side accross similar servers.

i.e.

Select s1.ColumnA as Server1_Status, s2.ColumnA as Server2_Status

From Server1 s1

Join Server2 s2 on s1.Pkey = s2.Pkey

Where Server2 is an entry in Server Objects, Linked Servers

For everything else, I use the other options like multiple pumps and Union All.

It performs better, especially when pulling from non-SQL databases.

Regards,

Philippe

Queries for Back up and restore the databases

Hi,
I would like to know what are the queries for back up and restore of
the DB in SQL Server.
Kindly help. Thanks in advance.
urs,
Sundar The Great!Good ol' Books Online says:
http://msdn.microsoft.com/library/d...br />
35ww.asp
http://msdn.microsoft.com/library/d...br />
25rm.asp
Only *you* know what you actually need. Unless you tell us.
ML
http://milambda.blogspot.com/

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

Qualify query with table name owner

All of the sudden when I do queries on my databases I have to include the ta
ble owner name
Before - Select * from people
After - Select * from carl.people
Don't know what changed... Haven't changed any table owners, config options
for the server or query tool.
Anyone know why?
Thanks,
CarlYou probably don't operate under the user name "carl", quite simply. Execute
SELECT SESSTION_USER and see what it returns.
Btw, it is a good practice to *always* owner-qualify in production code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Carl at pnm" <anonymous@.discussions.microsoft.com> wrote in message
news:EC65D713-D380-4A63-A367-E72CBB18FC6E@.microsoft.com...
> All of the sudden when I do queries on my databases I have to include the
table owner name
> Before - Select * from people
> After - Select * from carl.people
> Don't know what changed... Haven't changed any table owners, config
options for the server or query tool.
> Anyone know why?
> Thanks,
> Carl

Qualifing table names with dbo

if dbo exists then the sql server resolves the object immeidately
otherwise it needs to search the databases one by one in the server.
Basically its a performance issue related.
HTH
Rajesh Peddireddy.
"Jim Abel" wrote:

> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does the
dbo
> do anything at all, especially if the user that request the query to execu
te
> is not the dbo simply a user with read only permissions?
>"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:3931CB08-2AE8-4D0C-AC07-E5037BB45D92@.microsoft.com...
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
No it's not. SQL Server will never have to resolve the object name outside
of the database, and when it does this happens on at query comiliation time.
Once the query is compiled it should be reused, so this is not a performance
issue.
Prefixing with DBO is unnecesary and should be avoided in the case where all
the objects in a database reside in the DBO schema. In addition prefixing
is necessary to create schema-bound views and functions.
David|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> Prefixing with DBO is unnecesary and should be avoided in the case where
> all the objects in a database reside in the DBO schema. In addition
> prefixing is necessary to create schema-bound views and functions.
I have had some discussions with the SQL Server team on that one...
In SQL 2000, if a plain user issues a SELECT statement or calls a
stored procedure with qualifying the name with dbo, there is a cost.
Say that user fred issues:
SELECT ... FROM tbl
EXEC some_sp
SQL Server will first have to check whether there is a fred.tbl or a
fred.some_sp. There is a cost for this.
In SQL 2005, this may be different. This is because in SQL 2005 owner
and schema are different. In SQL 2000 fred's default schema is "fred"
by necessity. In SQL 2005, fred may have dbo as his default schema, and
in this case, it should not matter whether you say tbl or dbo.tbl.
However, this depends on how the user fred was created. If the database
was carried over from SQL 2000, or ir the user was created with
sp_adduser out of habit, it will be as on SQL 2000.
It's a different issue inside of a stored procedure, and this is where I
don't agree with some of the SQL Server folks. In a stored procedure
owned by dbo, "SELECT ... FROM tbl" is unambiguous, and adding "dbo."
is just noise for the human reader. However, the SQL Server team
claims there still is a cost in this case. I tempted to say that in
such case this is a bug, but I have not looked at the actual code.
In any case, Microsoft's recommendation is that you should always
specify dbo. also inside stored procedures.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Q: view over databases

Hello,
How can I write a view that gets data from different database in the same
Database server?You can qualify object names with the database name:
CREATE VIEW dbo.MyView
AS
SELECT
t1.Col1,
t2.Col2
FROM Database1.dbo.Table1 t1
JOIN Database2.dbo.Table2 t2 ON
t1.Col3 = t2.Col4
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:92B7EB48-1A26-467F-97DB-8350669FDAF0@.microsoft.com...
> Hello,
> How can I write a view that gets data from different database in the same
> Database server?
>|||Qualify object names with the database name:
SELECT count(*)
FROM master.dbo.sysobjects as A
JOIN model.dbo.sysobjects as B
ON A.id = B.id
WHERE A.name <> B.name
Roy
On Wed, 22 Feb 2006 12:56:28 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Hello,
>How can I write a view that gets data from different database in the same
>Database server?
>|||Is this possible if the database on different server? I could not get it
working.
"Dan Guzman" wrote:
> You can qualify object names with the database name:
> CREATE VIEW dbo.MyView
> AS
> SELECT
> t1.Col1,
> t2.Col2
> FROM Database1.dbo.Table1 t1
> JOIN Database2.dbo.Table2 t2 ON
> t1.Col3 = t2.Col4
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:92B7EB48-1A26-467F-97DB-8350669FDAF0@.microsoft.com...
> > Hello,
> > How can I write a view that gets data from different database in the same
> > Database server?
> >
> >
>
>|||You can use linked serves.
Look in BOL for linked servers
Regards
Amish Shah|||As Amish mentioned, you can create linked servers and then use 4-part names
to qualify objects: on different servers:
CREATE VIEW dbo.MyView
AS
SELECT
t1.Col1,
t2.Col2
FROM Database1.dbo.Table1 t1
JOIN OtherServer.Database2.dbo.Table2 t2 ON
t1.Col3 = t2.Col4
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0D8CD62D-1258-49BE-B1C0-76F14235D931@.microsoft.com...
> Is this possible if the database on different server? I could not get it
> working.
> "Dan Guzman" wrote:
>> You can qualify object names with the database name:
>> CREATE VIEW dbo.MyView
>> AS
>> SELECT
>> t1.Col1,
>> t2.Col2
>> FROM Database1.dbo.Table1 t1
>> JOIN Database2.dbo.Table2 t2 ON
>> t1.Col3 = t2.Col4
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:92B7EB48-1A26-467F-97DB-8350669FDAF0@.microsoft.com...
>> > Hello,
>> > How can I write a view that gets data from different database in the
>> > same
>> > Database server?
>> >
>> >
>>

Q: view over databases

Hello,
How can I write a view that gets data from different database in the same
Database server?Qualify object names with the database name:
SELECT count(*)
FROM master.dbo.sysobjects as A
JOIN model.dbo.sysobjects as B
ON A.id = B.id
WHERE A.name <> B.name
Roy
On Wed, 22 Feb 2006 12:56:28 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:

>Hello,
>How can I write a view that gets data from different database in the same
>Database server?
>