join or subselect: perform

Showing posts with label perform. Show all posts

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 in different databases

I'm working on an ASP project where the clients want to be able to
effectively perform SELECT queries joining tables from two different
databases (located on the same SQL-Server).

Does this involve creating virtual tables that link to another database, or
am I completely on the wrong track?

Any hints as to where I might find more information (buzz-words, etc.) would
be most appreciated.

ThanksTry 'four part names' :).
server.database.owner.table

"Captain Nemo" <nemo@.nospam.com> wrote in message
news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
> Hi
> I'm working on an ASP project where the clients want to be able to
> effectively perform SELECT queries joining tables from two different
> databases (located on the same SQL-Server).
> Does this involve creating virtual tables that link to another database,
> or
> am I completely on the wrong track?
> Any hints as to where I might find more information (buzz-words, etc.)
> would
> be most appreciated.
> Thanks|||Use 3-part naming:

select
*
from
dbo.MyTable l
join
OtherDB.dbo.OtherTable o on o.PK = l.PK

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Captain Nemo" <nemo@.nospam.com> wrote in message
news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
Hi

I'm working on an ASP project where the clients want to be able to
effectively perform SELECT queries joining tables from two different
databases (located on the same SQL-Server).

Does this involve creating virtual tables that link to another database, or
am I completely on the wrong track?

Any hints as to where I might find more information (buzz-words, etc.) would
be most appreciated.

Thanks|||The OP said the two DB's were on the same server. Therefore, 3-part naming
is sufficient.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:dr5cc8$bue$1@.magcargo.vodatel.hr...
Try 'four part names' :).
server.database.owner.table

It sure is! I've just tried it out. Where I went wrong was thinking that
2-part naming would do it (omitting the 'dbo').

Thanks, Tom|||Agreed, but why not provide a more complete info since theres a little
difference? He may need to pull data from two servers tomorrow...

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:LBqBf.4368$ft2.109590@.news20.bellglobal.com.. .
> The OP said the two DB's were on the same server. Therefore, 3-part
> naming
> is sufficient.
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:dr5cc8$bue$1@.magcargo.vodatel.hr...
> Try 'four part names' :).
> server.database.owner.table
>
> MC
>
> "Captain Nemo" <nemo@.nospam.com> wrote in message
> news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
>> Hi
>>
>> I'm working on an ASP project where the clients want to be able to
>> effectively perform SELECT queries joining tables from two different
>> databases (located on the same SQL-Server).
>>
>> Does this involve creating virtual tables that link to another database,
>> or
>> am I completely on the wrong track?
>>
>> Any hints as to where I might find more information (buzz-words, etc.)
>> would
>> be most appreciated.
>>
>> Thanks
>>
>>|||There's often a performance difference.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:dr5eu2$s5q$1@.magcargo.vodatel.hr...
Agreed, but why not provide a more complete info since theres a little
difference? He may need to pull data from two servers tomorrow...

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:dRsBf.4433$ft2.115520@.news20.bellglobal.com.. .
> There's often a performance difference.
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:dr5eu2$s5q$1@.magcargo.vodatel.hr...
> Agreed, but why not provide a more complete info since theres a little
> difference? He may need to pull data from two servers tomorrow...
>
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:LBqBf.4368$ft2.109590@.news20.bellglobal.com.. .
>> The OP said the two DB's were on the same server. Therefore, 3-part
>> naming
>> is sufficient.
>>
>> --
>> Tom
>>
>> ----------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>>
>> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
>> news:dr5cc8$bue$1@.magcargo.vodatel.hr...
>> Try 'four part names' :).
>> server.database.owner.table
>>
>>
>> MC
>>
>>
>> "Captain Nemo" <nemo@.nospam.com> wrote in message
>> news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
>>> Hi
>>>
>>> I'm working on an ASP project where the clients want to be able to
>>> effectively perform SELECT queries joining tables from two different
>>> databases (located on the same SQL-Server).
>>>
>>> Does this involve creating virtual tables that link to another database,
>>> or
>>> am I completely on the wrong track?
>>>
>>> Any hints as to where I might find more information (buzz-words, etc.)
>>> would
>>> be most appreciated.
>>>
>>> Thanks
>>>
>>>
>>
>>
>>
>|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Do you mean that actually specifing servername slows down the query? Could
> you explain why?

If the server name use is @.@.servername, SQL Server will shortcut, and
there is no overhead.

But if the linked server is defined a true loopback, so that there is a
new connection made, there is obviously an overhead, as data is first
passed to SQL Server, to the OLE DB provider on one connection, and then
the OLE DB provider passes the data back to another connection.

--
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|||Thank you for that, I assumed engine would 'optimize' that and never
checked. Just when I think I actually know something ;)....

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9756F1F84A0F4Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
>> Do you mean that actually specifing servername slows down the query?
>> Could
>> you explain why?
> If the server name use is @.@.servername, SQL Server will shortcut, and
> there is no overhead.
> But if the linked server is defined a true loopback, so that there is a
> new connection made, there is obviously an overhead, as data is first
> passed to SQL Server, to the OLE DB provider on one connection, and then
> the OLE DB provider passes the data back to another connection.
> --
> 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|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Thank you for that, I assumed engine would 'optimize' that and never
> checked. Just when I think I actually know something ;)....

As I mentioned, it does optimize when the server is @.@.servername, which
it can recognize. But it does not analyse connection strings.

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

Monday, March 19, 2012

Joining one database table with other database table in stroed procedures.

Hi All,
I need to perform a join on table1 of database1 with table2 or database2, in a stored procedure and return to my web application.. For this I'm providing execute permission on stored procedure, in database1 and "SELECT" permission on database2 table 2, to my database webuser.
Is there any way ( Another stored procedure in database2), i can get join on two tables without SELECT permission right on table2 ( or table1).
Purely using stored procedures. If so how?Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||Hi Paul,
Thanks, What is meant by security path? I didnot get ur point.
User have only public permission on both databases. Is it not enough? I do have execute permission on sp1 (Stored Procedure) in database1.
but in sp1 I have a select statement which is a join on database2 table-'table2'. When I tried to execute sp1 from web application only with execute permission on sp1-
I got an error as Webuser does not have 'SELECT' permission on table2 of database2. So I provided it and sp1 worked fine. But I don't want to use a SELECT permission- What is alternate, for the join.
sp1 has simple
SELECT *
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.col1 = t2.col1)

Originally posted by Paul Young
Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||by security path I was refering to how a user gets authenticated on server2 when making a connection from server1.

You might try using OPENQUERY to call a stored procedure on server2 and use the results to join to a table on server1. I haven't had the need for this in the past so I am working on theory here. Check BOL for usage on OPENQUERY, they have some good examples.|||When calling a stored proc from database 1, you need execute privs on the proc. You do not need to give the user privs on the table itself as long as it is in database 1, too. If database 2 is owned by the same user that is the owner of database 1, you do not need explicit permissions on the object in table 2 accessed by the proc. However, if the database owners are different for the two databases, the user calling the proc must have explicit permissions in the second database.

If need be, you can change the database owner by:

EXEC sp_changedbowner 'username'

Execute this in the database you want to change.|||Well Both databases are on same server. Is OPENQUERY solve in this case too?|||Originally posted by soumyag
Well Both databases are on same server.

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.|||Hi,
Both are on same server, but owned by different users. And I don't have any right to change the dbowners But how it will help in writing a join on tables. What is other alternative way to solve this problem.
Thanks.

Originally posted by bglass

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.

Joining on and Grouping by CASE function column alias (URGENT)

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
 CASE
 WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
 END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC RossYou could do it as a sub query

Select NewColumn from (Select case....) MySub group by MySub.NewColumn...etc, etc|||Not tested, but you should be able to do it like this:


SELECT NewColumn=
 CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END,
 Table2.SelectCol2
FROM Table1
JOIN Table2 ON CASE WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO' END = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I'm sure there's some rule, but I've never figured out when SQL lets you use an alias and when it doesn't. But, in this case, it apparently doesn't, so just use the CASE statement and you should be all set.|||I didn't know you could use a CASE in the JOIN syntax? Does it work?|||Works for me|||Cool I'll have to remember that one, top tip.

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

Friday, March 9, 2012

JOIN two Datasets?

I would like to perform an inner join on two seperate datasets. One dataset
gets its data from SQL server, the other gets its data from a XML web
service.Can't do this. You might consider using a sub report. A sub report can be
put into the table object cells.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jeff Richardson" <BobcatRidge@.newsgroups.nospam> wrote in message
news:O63%23UQuxGHA.3464@.TK2MSFTNGP03.phx.gbl...
>I would like to perform an inner join on two seperate datasets. One
>dataset gets its data from SQL server, the other gets its data from a XML
>web service.
>|||Thanks.
I have not tried the sub-report route because I assumed that you would get
all rows from the parent dataset even if the sub-report contained no rows.
Is there a way to that the parent rows are only displayed when the
sub-report has rows?
Thanks
Jeff.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u1qBZauxGHA.3456@.TK2MSFTNGP03.phx.gbl...
> Can't do this. You might consider using a sub report. A sub report can be
> put into the table object cells.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jeff Richardson" <BobcatRidge@.newsgroups.nospam> wrote in message
> news:O63%23UQuxGHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I would like to perform an inner join on two seperate datasets. One
>>dataset gets its data from SQL server, the other gets its data from a XML
>>web service.
>|||Hi Jeff,
Although you could not control the visibility of a textbox with the
Subreport, you could add another dataset to control the visibility.
The dataset should be the same as the the one you use in the sub report.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

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

Friday, March 23, 2012

Joins Across Servers/Databases

Wednesday, March 21, 2012

Joining tables in different databases

Monday, March 19, 2012

Joining one database table with other database table in stroed procedures.

Joining on and Grouping by CASE function column alias (URGENT)

Monday, March 12, 2012

Joining across databases

Joining across databases

Joining across databases

Friday, March 9, 2012

JOIN two Datasets?

Wednesday, March 7, 2012

Join tables across databases?

Join tables across databases?

Join tables across databases?

join or subselect

Blog Archive

About Me