Showing posts with label located. Show all posts
Showing posts with label located. Show all posts

Monday, March 12, 2012

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||Użytkownik Tibor Karaszi napisaÅ?:
> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> Użytkownik Tibor Karaszi napisaÅ?:
>> 1 is same as 2. The optimizer has all the information and can process the query the same whether
>> the tables are in the same database or different database. For 3 and 4, the query is optimized
>> locally and parts of the query is passed onto the linked server. This limits the flexibility that
>> the optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test
>> with your data, schema, queries etc.
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinIt is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||1 is same as 2. The optimizer has all the information and can process the qu
ery the same whether the
tables are in the same database or different database. For 3 and 4, the quer
y is optimized locally
and parts of the query is passed onto the linked server. This limits the fle
xibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, yo
u need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Serve
r (linked servers) when
> these instances are located on different physical machines and these machi
nes contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin|||
> It is really opene-end question , because only you do know about your tabl
es
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin|||> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small
, medium, big)
> Merlin
>

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
That really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:

>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
Merlin
It is really opene-end question , because only you do know about your tables
structure, indexes and amount of data.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on the same
> physical machine
> 4. from different databases located on different instances of MS SQL
> Server (linked servers) when these instances are located on different
> physical machines and these machines contact each other through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but
> if (and how big) are there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||1 is same as 2. The optimizer has all the information and can process the query the same whether the
tables are in the same database or different database. For 3 and 4, the query is optimized locally
and parts of the query is passed onto the linked server. This limits the flexibility that the
optimizer otherwise has. 4 is obviously worse than 3. For quantification, you need to test with your
data, schema, queries etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:u5qDn7WAGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Hello,
>
> What is performance difference between joining tables
> 1. from the same database
> 2. from different databases located on the same instance of MS SQL Server
> 3. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on the same physical machine
> 4. from different databases located on different instances of MS SQL Server (linked servers) when
> these instances are located on different physical machines and these machines contact each other
> through LAN
> I guess, performance or variant 1 is the best and for 4 is the worst, but if (and how big) are
> there differences between: 1 and 2, 2 and 3.
>
> Thanks a lot.
> Merlin
|||
> It is really opene-end question , because only you do know about your tables
> structure, indexes and amount of data.
Structure of these tables is the same in all variants.
I don't expect exact answers, because it is impossible without exact
info, but I think it is possible to point at mainspriongs which affect
performance.
Difference between variants 1 and 4 is obvious.
What about difference between 1 and 2, 2 and 3 it's not obvious for me.
Merlin
|||U?ytkownik Tibor Karaszi napisa?:

> 1 is same as 2. The optimizer has all the information and can process
> the query the same whether the tables are in the same database or
> different database. For 3 and 4, the query is optimized locally and
> parts of the query is passed onto the linked server. This limits the
> flexibility that the optimizer otherwise has. 4 is obviously worse than
> 3. For quantification, you need to test with your data, schema, queries
> etc.
Thanks, this is the info what I've expcected.
Do you have feeling what difference can be between variants 2 and 3
(small, medium, big)
Merlin
|||> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
I'd say medium to big. But you can always find exceptions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:43A160FA.6060206@.NOSPAM_poczta.onet.pl...
> U?ytkownik Tibor Karaszi napisa?:
>
> Thanks, this is the info what I've expcected.
> Do you have feeling what difference can be between variants 2 and 3 (small, medium, big)
> Merlin
>

Friday, March 9, 2012

Joing tables from different databases - performance issues

Hello,
What is performance difference between joining tables
1. from the same database
2. from different databases located on the same instance of MS SQL Server
3. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on the same
physical machine
4. from different databases located on different instances of MS SQL
Server (linked servers) when these instances are located on different
physical machines and these machines contact each other through LAN
I guess, performance or variant 1 is the best and for 4 is the worst,
but if (and how big) are there differences between: 1 and 2, 2 and 3.
Thanks a lot.
MerlinThat really depends on a lot of things such as the queries
themselves and on your network configuration - issues such
as are the servers on the same switch. There is no black and
white percentage to give you. If you are concerned about
performance across servers, you may want to check the
performance tuning tips in the following article:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Thu, 15 Dec 2005 13:00:15 +0100, MerlinXP
<MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote:

>Hello,
>
>What is performance difference between joining tables
>1. from the same database
>2. from different databases located on the same instance of MS SQL Server
>3. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on the same
>physical machine
>4. from different databases located on different instances of MS SQL
>Server (linked servers) when these instances are located on different
>physical machines and these machines contact each other through LAN
>I guess, performance or variant 1 is the best and for 4 is the worst,
>but if (and how big) are there differences between: 1 and 2, 2 and 3.
>
>Thanks a lot.
>Merlin

Wednesday, March 7, 2012

Join tables across databases?

In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1datetime=null,
@.Date2datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND (dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
Try these examples:
http://vyaskn.tripod.com/programming_faq.htm#q13
http://vyaskn.tripod.com/programming_faq.htm#q14
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"j c via droptable.com" <forum@.droptable.com> wrote in message
news:514309300509D@.droptable.com...
In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH
pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads
me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||> pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in
the
> pubs.city_zipcodes.CITY --from the zipcode
tables
The selected columns

> FROM
> INNER JOIN city_zipcodes ON dbo.customer.postalcode =
> city_zipcodes.zipcode --this is the join I am trying to accomplish
from the selected table.
You made two mistakes. First, you did not qualify the table correctly in
the join. Second, you did not qualify the appopriate columns correctly
within the select list. Your select list should qualify the columns using
the same table qualification used in the join(s) - these should always match
EXACTLY. Often, it is best to use an alias for the table (as Vyas has done
in his examples). This lets you change (or correct) the selected table
without having to change the associated columns in the remainder of the
statement.

> where
> (dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
> service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
This logic should NOT be needed. The arguments are already datetime
variables - converting them to the same datatype does nothing beneficial.
If record_date is a datetime, then nothing is gained with the CONVERT
statement. If record_date is char or varchar, then your logic is incorrect
since you did not avoid the implicit conversion (which is what you were
apparently trying to do with convert).
|||Thanks for the link. That cleared everything up!
j
Message posted via http://www.droptable.com

Join tables across databases?

In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH pub
s.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads m
e
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND (dbo
.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1Try these examples:
http://vyaskn.tripod.com/programming_faq.htm#q13
http://vyaskn.tripod.com/programming_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"j c via droptable.com" <forum@.droptable.com> wrote in message
news:514309300509D@.droptable.com...
In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH
pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads
me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||> pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in
the
> pubs.city_zipcodes.CITY --from the zipcode
tables
The selected columns

> FROM
> INNER JOIN city_zipcodes ON dbo.customer.postalcode =
> city_zipcodes.zipcode --this is the join I am trying to accomplish
from the selected table.
You made two mistakes. First, you did not qualify the table correctly in
the join. Second, you did not qualify the appopriate columns correctly
within the select list. Your select list should qualify the columns using
the same table qualification used in the join(s) - these should always match
EXACTLY. Often, it is best to use an alias for the table (as Vyas has done
in his examples). This lets you change (or correct) the selected table
without having to change the associated columns in the remainder of the
statement.

> where
> (dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
> service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
This logic should NOT be needed. The arguments are already datetime
variables - converting them to the same datatype does nothing beneficial.
If record_date is a datetime, then nothing is gained with the CONVERT
statement. If record_date is char or varchar, then your logic is incorrect
since you did not avoid the implicit conversion (which is what you were
apparently trying to do with convert).|||Thanks for the link. That cleared everything up!
j
Message posted via http://www.droptable.com

Join tables across databases?

In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode = city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND (dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1Try these examples:
http://vyaskn.tripod.com/programming_faq.htm#q13
http://vyaskn.tripod.com/programming_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"j c via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:514309300509D@.SQLMonster.com...
In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH
pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads
me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||> pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in
the
> pubs.city_zipcodes.CITY --from the zipcode
tables
The selected columns
> FROM
> INNER JOIN city_zipcodes ON dbo.customer.postalcode => city_zipcodes.zipcode --this is the join I am trying to accomplish
from the selected table.
You made two mistakes. First, you did not qualify the table correctly in
the join. Second, you did not qualify the appopriate columns correctly
within the select list. Your select list should qualify the columns using
the same table qualification used in the join(s) - these should always match
EXACTLY. Often, it is best to use an alias for the table (as Vyas has done
in his examples). This lets you change (or correct) the selected table
without having to change the associated columns in the remainder of the
statement.
> where
> (dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
> service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
This logic should NOT be needed. The arguments are already datetime
variables - converting them to the same datatype does nothing beneficial.
If record_date is a datetime, then nothing is gained with the CONVERT
statement. If record_date is char or varchar, then your logic is incorrect
since you did not avoid the implicit conversion (which is what you were
apparently trying to do with convert).|||Thanks for the link. That cleared everything up!
j
Message posted via http://www.sqlmonster.com