Showing posts with label across. Show all posts
Showing posts with label across. Show all posts

Monday, March 26, 2012

Joins Vs Where clause - Performance Query

Hi There !!

To finetune performance for some of our queries,

I have come across suggestions to use

- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases

Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ??

Also,

I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )

USING A WHERE CLAUSE and Alias
--------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null

USING A JOIN
------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL

******

I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:

Any comments/ suggestions.

Thanks,

Have a great time
-Ranjit.

------------
It pays to be honest to your DBAmy experience is that 99% of the time, the optimizer is smart enough to generate the same plan regardless of whether you use the ansi join syntax or not. I prefer the ansi syntax just for purity's sake however.

If you haven't already, you should measure first (using profiler) to find where the bottlenecks are. Only after you have measured can you begin to address perf issues.

Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.|||Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.Agreed - ANSI syntax is merely convention (although of course you can do more than an inner join with ANSI).

and avoid using AliasesNope again - this is just a convention too. Some people think aliases make code easier to read, blindman does not. :)

I don't know where you stand in performance tuning experience but everyone of any level can find something of use here:
http://www.sql-server-performance.com/articles_performance.asp

HTH|||avoiding aliases is not "reasonable"

:)|||Some people think aliases make code easier to read, blindman does not. :)
My reputation preceeds me.
But even I don't claim the aliases hurt performance.

Friday, March 23, 2012

Joins Across Servers/Databases

I am trying to determine how best to create a query that will allow me to perform 'joins across servers'.

I want to create a single join that will join my production database (Oracle) with my Land Database (MSSQL) on a common key.

From what I have gathered on other sites, it appears that SQLServer can perform this function, but I am not that technical and I am not a SQL programmer (eventhough I have access to one), and I am hoping there are some point and click apps that will let me perform this function.

Any thoughts ?Hello,

the best way to join two databases for example from Oracle to MSSQL is to use ADBC. ADBC is a software package that expands the Oracle database. Once installed, you can select the MSSQL tables that should be visible in Oracle.

If you like, we can assist you installing ADBC and developing the necessary functions for MSSQL to Oracle access.

Please send me a mail to m.peter@.alligatorsql.com if you need further help in this way.

Hope that helps ?

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Wednesday, March 21, 2012

Joining Tables Across Multiple Datasets

I am trying to join two tables from two different datasets in SQL
Server 2005 Reporting Services. How would one go about doing this?
Since there are technically two spots to have SQL queries (one for
each dataset), how does one go about doing SQL? Is joining across
multiple datasets even possible? If so, are there clever workarounds?You cannot join datasets in RS. You either need to do this in a stored
procedure OR you should think about subreports. Usually what you need to do
can be accomplished with subreports. For a 1:1 or 1:M relationship
subreports will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<rdracer58@.gmail.com> wrote in message
news:1183746250.948449.38960@.o61g2000hsh.googlegroups.com...
>I am trying to join two tables from two different datasets in SQL
> Server 2005 Reporting Services. How would one go about doing this?
> Since there are technically two spots to have SQL queries (one for
> each dataset), how does one go about doing SQL? Is joining across
> multiple datasets even possible? If so, are there clever workarounds?
>sql

Monday, March 12, 2012

Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon

Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon

Joining across databases

Hi there,
Can anyone tell me if its possible to perform a join on two tables that
are resident in different databases - both databases are running on the
same machine and under the same SQL Server 2005 instance. If anyone
could tell me roughly how to do this then I'd be very greatful.
If it is possible, could I also as - is it necessary to define
relationships between the tables in order for the join to work, and is
that even possible?
Many thanks for anyone who can advise
Kindest Regards
SimonYou can reference objects in other databases on the same server by using
3-part names: <database>.<schema>.<object>
SELECT a.Col1, b.Col2
FROM Schema1.Table1 a
JOIN OtherDatabase.Schema2.Table2 b ON
a.Table1Col = b.Table2Col
Cross-database joins are like any other joins; no constrains are required.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Simon
Have you tried?
select <columns> from db1.dbo.table1 t1 join db2.dbo.table1 t1
on t1.col=t2.col
--or
create a view that contains a select statement from requierd database and
join the view with a "source" database
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:e7pIq5lfGHA.4276@.TK2MSFTNGP03.phx.gbl...
> Hi there,
> Can anyone tell me if its possible to perform a join on two tables that
> are resident in different databases - both databases are running on the
> same machine and under the same SQL Server 2005 instance. If anyone could
> tell me roughly how to do this then I'd be very greatful.
> If it is possible, could I also as - is it necessary to define
> relationships between the tables in order for the join to work, and is
> that even possible?
> Many thanks for anyone who can advise
> Kindest Regards
> Simon|||Many thanks Guys
Simon

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