join or subselect: working

Showing posts with label working. Show all posts

Friday, March 30, 2012

Jump to URL was working - now not working

Hello. I have javascript in a bunch of my reports so that when jumping to a
url in the action property everything would go to a new browser window.
Everything is working just fine (after I upgraded the service pack so I could
do it) but now all of a sudden it doesn't work on any of the reports. Nothing
has changed on the box and none of the reports have been touched. Everything
else appears to be working as intended.
Any ideas? Any help would be very much appreciated. Thanks!Hi can i know what is the javascript you used? There is a some change in SP1
2005RS that made to do this. Few javascript code added to the RS report
manager has been changed to support and block some feature...
"dcray" wrote:
> Hello. I have javascript in a bunch of my reports so that when jumping to a
> url in the action property everything would go to a new browser window.
> Everything is working just fine (after I upgraded the service pack so I could
> do it) but now all of a sudden it doesn't work on any of the reports. Nothing
> has changed on the box and none of the reports have been touched. Everything
> else appears to be working as intended.
> Any ideas? Any help would be very much appreciated. Thanks!|||Hi, it wasn't until I upgraded the service pack that it would work at all,
and after applied worked fine just as anticipated. Nothing fancy, now all of
reports that use this js are broke and nothing has changed
="javascript:void(window.open('http://www.google.com'))"
I also tried specifiying the target window
="javascript:void(window.open('http://www.google.com','_blank'))"
still not working. These are the things that have been added to the box
reporting services is on since the reports were impliented and working with
the javascript in the action property:
Windows Malicious Software Removal Tool
Security Update for Microsoft Data Access Components 2.7 SP1
Cumulative Security Update for IE 6 SP1
Security Update for Windows 200
Cumulative Security Update for Outlook Express 6 SP1
just in case any of these might cause a prolblem...you never know.
thanks!!
"Bava Mani" wrote:
> Hi can i know what is the javascript you used? There is a some change in SP1
> 2005RS that made to do this. Few javascript code added to the RS report
> manager has been changed to support and block some feature...
> "dcray" wrote:
> > Hello. I have javascript in a bunch of my reports so that when jumping to a
> > url in the action property everything would go to a new browser window.
> > Everything is working just fine (after I upgraded the service pack so I could
> > do it) but now all of a sudden it doesn't work on any of the reports. Nothing
> > has changed on the box and none of the reports have been touched. Everything
> > else appears to be working as intended.
> >
> > Any ideas? Any help would be very much appreciated. Thanks!|||I have a report I designed in RS 2000 and converted to RS 2005. I just tried
it out and it worked fine:
= "javascript:void(window.open('http://www.google.com','_blank'))"
Looks exactly the same as below.
So, might be something else but it is not RS 2005. This requires RS 2000 SP1
or greater. I have all the latest security updates at the server and the
client (Server is 2003 SP1, client XP Pro SP2).
Anyway, not a solution but at least another data point for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dcray" <dcray@.discussions.microsoft.com> wrote in message
news:89F48B08-2279-4D1D-8FE6-FB1B98D431B8@.microsoft.com...
> Hi, it wasn't until I upgraded the service pack that it would work at all,
> and after applied worked fine just as anticipated. Nothing fancy, now all
> of
> reports that use this js are broke and nothing has changed
> ="javascript:void(window.open('http://www.google.com'))"
> I also tried specifiying the target window
> ="javascript:void(window.open('http://www.google.com','_blank'))"
> still not working. These are the things that have been added to the box
> reporting services is on since the reports were impliented and working
> with
> the javascript in the action property:
> Windows Malicious Software Removal Tool
> Security Update for Microsoft Data Access Components 2.7 SP1
> Cumulative Security Update for IE 6 SP1
> Security Update for Windows 200
> Cumulative Security Update for Outlook Express 6 SP1
> just in case any of these might cause a prolblem...you never know.
> thanks!!
>
> "Bava Mani" wrote:
>> Hi can i know what is the javascript you used? There is a some change in
>> SP1
>> 2005RS that made to do this. Few javascript code added to the RS report
>> manager has been changed to support and block some feature...
>> "dcray" wrote:
>> > Hello. I have javascript in a bunch of my reports so that when jumping
>> > to a
>> > url in the action property everything would go to a new browser window.
>> > Everything is working just fine (after I upgraded the service pack so I
>> > could
>> > do it) but now all of a sudden it doesn't work on any of the reports.
>> > Nothing
>> > has changed on the box and none of the reports have been touched.
>> > Everything
>> > else appears to be working as intended.
>> >
>> > Any ideas? Any help would be very much appreciated. Thanks!|||turns out the problem is that some of the urls to the reports that were
getting passed around used an alias for the machine that hasn't been set up
properly as one of the host headers
"Bruce L-C [MVP]" wrote:
> I have a report I designed in RS 2000 and converted to RS 2005. I just tried
> it out and it worked fine:
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> Looks exactly the same as below.
> So, might be something else but it is not RS 2005. This requires RS 2000 SP1
> or greater. I have all the latest security updates at the server and the
> client (Server is 2003 SP1, client XP Pro SP2).
> Anyway, not a solution but at least another data point for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "dcray" <dcray@.discussions.microsoft.com> wrote in message
> news:89F48B08-2279-4D1D-8FE6-FB1B98D431B8@.microsoft.com...
> >
> > Hi, it wasn't until I upgraded the service pack that it would work at all,
> > and after applied worked fine just as anticipated. Nothing fancy, now all
> > of
> > reports that use this js are broke and nothing has changed
> >
> > ="javascript:void(window.open('http://www.google.com'))"
> >
> > I also tried specifiying the target window
> >
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> >
> > still not working. These are the things that have been added to the box
> > reporting services is on since the reports were impliented and working
> > with
> > the javascript in the action property:
> >
> > Windows Malicious Software Removal Tool
> > Security Update for Microsoft Data Access Components 2.7 SP1
> > Cumulative Security Update for IE 6 SP1
> > Security Update for Windows 200
> > Cumulative Security Update for Outlook Express 6 SP1
> >
> > just in case any of these might cause a prolblem...you never know.
> > thanks!!
> >
> >
> >
> > "Bava Mani" wrote:
> >
> >> Hi can i know what is the javascript you used? There is a some change in
> >> SP1
> >> 2005RS that made to do this. Few javascript code added to the RS report
> >> manager has been changed to support and block some feature...
> >>
> >> "dcray" wrote:
> >>
> >> > Hello. I have javascript in a bunch of my reports so that when jumping
> >> > to a
> >> > url in the action property everything would go to a new browser window.
> >> > Everything is working just fine (after I upgraded the service pack so I
> >> > could
> >> > do it) but now all of a sudden it doesn't work on any of the reports.
> >> > Nothing
> >> > has changed on the box and none of the reports have been touched.
> >> > Everything
> >> > else appears to be working as intended.
> >> >
> >> > Any ideas? Any help would be very much appreciated. Thanks!
>
>

Jump To URL not working

Hi,
I'm using Reporting Services 2000 with the latest SP. The javascript code in
Jump To URL is giving a Page Cannot be Displayed message.
Here is the code:
="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
Globals!ReportFolder + "/Detail&Country_Code=" +
Parameters!Country_Code.Value + "&ITEMID=" + Fields!Parent_Product_Code.Value
+ "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
Parameters!Language.Value +
"&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width = 580,height=750,top=200'))"
Can someone plz help?The links work fine if i access the report from localhost/ReportServer
but not when i access from ReportManager.
Is this a known issue?
"Gaurav" wrote:
> Hi,
> I'm using Reporting Services 2000 with the latest SP. The javascript code in
> Jump To URL is giving a Page Cannot be Displayed message.
> Here is the code:
> ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> Globals!ReportFolder + "/Detail&Country_Code=" +
> Parameters!Country_Code.Value + "&ITEMID=" + Fields!Parent_Product_Code.Value
> + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> Parameters!Language.Value +
> "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width => 580,height=750,top=200'))"
> Can someone plz help?
>|||Get this complete link display on a text box (ofcourse without javascript
syntax) and see where you are going wrong.
Amarnath
"Gaurav" wrote:
> The links work fine if i access the report from localhost/ReportServer
> but not when i access from ReportManager.
> Is this a known issue?
>
> "Gaurav" wrote:
> > Hi,
> >
> > I'm using Reporting Services 2000 with the latest SP. The javascript code in
> > Jump To URL is giving a Page Cannot be Displayed message.
> >
> > Here is the code:
> > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> > Globals!ReportFolder + "/Detail&Country_Code=" +
> > Parameters!Country_Code.Value + "&ITEMID=" + Fields!Parent_Product_Code.Value
> > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> > Parameters!Language.Value +
> > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width => > 580,height=750,top=200'))"
> >
> > Can someone plz help?
> >|||I couldn't resolve this. Even if i put in a link to google.com it doesn't
work.
The following is not working:
="javascript:void(window.open('http://www.google.com','_blank'))"
Any other suggestions?
"Amarnath" wrote:
> Get this complete link display on a text box (ofcourse without javascript
> syntax) and see where you are going wrong.
> Amarnath
> "Gaurav" wrote:
> > The links work fine if i access the report from localhost/ReportServer
> > but not when i access from ReportManager.
> >
> > Is this a known issue?
> >
> >
> > "Gaurav" wrote:
> >
> > > Hi,
> > >
> > > I'm using Reporting Services 2000 with the latest SP. The javascript code in
> > > Jump To URL is giving a Page Cannot be Displayed message.
> > >
> > > Here is the code:
> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> > > Globals!ReportFolder + "/Detail&Country_Code=" +
> > > Parameters!Country_Code.Value + "&ITEMID=" + Fields!Parent_Product_Code.Value
> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> > > Parameters!Language.Value +
> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width => > > 580,height=750,top=200'))"
> > >
> > > Can someone plz help?
> > >|||This looks OK. What version are you one. The ability to do this was
introduced with SP1 of RS 2000. You need to be on SP1 or greater for RS 2000
and any version of RS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
news:E823758C-0A43-433F-B7C5-1EB1E9D98627@.microsoft.com...
>I couldn't resolve this. Even if i put in a link to google.com it doesn't
> work.
> The following is not working:
> ="javascript:void(window.open('http://www.google.com','_blank'))"
> Any other suggestions?
> "Amarnath" wrote:
>> Get this complete link display on a text box (ofcourse without javascript
>> syntax) and see where you are going wrong.
>> Amarnath
>> "Gaurav" wrote:
>> > The links work fine if i access the report from localhost/ReportServer
>> > but not when i access from ReportManager.
>> >
>> > Is this a known issue?
>> >
>> >
>> > "Gaurav" wrote:
>> >
>> > > Hi,
>> > >
>> > > I'm using Reporting Services 2000 with the latest SP. The javascript
>> > > code in
>> > > Jump To URL is giving a Page Cannot be Displayed message.
>> > >
>> > > Here is the code:
>> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
>> > > Globals!ReportFolder + "/Detail&Country_Code=" +
>> > > Parameters!Country_Code.Value + "&ITEMID=" +
>> > > Fields!Parent_Product_Code.Value
>> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
>> > > Parameters!Language.Value +
>> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width
>> > > =>> > > 580,height=750,top=200'))"
>> > >
>> > > Can someone plz help?
>> > >|||Thanks for the reply Bruce.
I am using Version 8.00.1038.00.
"Bruce L-C [MVP]" wrote:
> This looks OK. What version are you one. The ability to do this was
> introduced with SP1 of RS 2000. You need to be on SP1 or greater for RS 2000
> and any version of RS 2005.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
> news:E823758C-0A43-433F-B7C5-1EB1E9D98627@.microsoft.com...
> >I couldn't resolve this. Even if i put in a link to google.com it doesn't
> > work.
> >
> > The following is not working:
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> >
> > Any other suggestions?
> >
> > "Amarnath" wrote:
> >
> >> Get this complete link display on a text box (ofcourse without javascript
> >> syntax) and see where you are going wrong.
> >>
> >> Amarnath
> >>
> >> "Gaurav" wrote:
> >>
> >> > The links work fine if i access the report from localhost/ReportServer
> >> > but not when i access from ReportManager.
> >> >
> >> > Is this a known issue?
> >> >
> >> >
> >> > "Gaurav" wrote:
> >> >
> >> > > Hi,
> >> > >
> >> > > I'm using Reporting Services 2000 with the latest SP. The javascript
> >> > > code in
> >> > > Jump To URL is giving a Page Cannot be Displayed message.
> >> > >
> >> > > Here is the code:
> >> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> >> > > Globals!ReportFolder + "/Detail&Country_Code=" +
> >> > > Parameters!Country_Code.Value + "&ITEMID=" +
> >> > > Fields!Parent_Product_Code.Value
> >> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> >> > > Parameters!Language.Value +
> >> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width
> >> > > => >> > > 580,height=750,top=200'))"
> >> > >
> >> > > Can someone plz help?
> >> > >
>
>|||That should be SP2. It should be working for you.
Copy and paste this in:
= "javascript:void(window.open('http://www.google.com','_blank'))"
Also, do you have popup blocker running?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
news:DD368BFC-B8B5-4EC6-994D-F95E603FF9FD@.microsoft.com...
> Thanks for the reply Bruce.
> I am using Version 8.00.1038.00.
> "Bruce L-C [MVP]" wrote:
>> This looks OK. What version are you one. The ability to do this was
>> introduced with SP1 of RS 2000. You need to be on SP1 or greater for RS
>> 2000
>> and any version of RS 2005.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
>> news:E823758C-0A43-433F-B7C5-1EB1E9D98627@.microsoft.com...
>> >I couldn't resolve this. Even if i put in a link to google.com it
>> >doesn't
>> > work.
>> >
>> > The following is not working:
>> > ="javascript:void(window.open('http://www.google.com','_blank'))"
>> >
>> > Any other suggestions?
>> >
>> > "Amarnath" wrote:
>> >
>> >> Get this complete link display on a text box (ofcourse without
>> >> javascript
>> >> syntax) and see where you are going wrong.
>> >>
>> >> Amarnath
>> >>
>> >> "Gaurav" wrote:
>> >>
>> >> > The links work fine if i access the report from
>> >> > localhost/ReportServer
>> >> > but not when i access from ReportManager.
>> >> >
>> >> > Is this a known issue?
>> >> >
>> >> >
>> >> > "Gaurav" wrote:
>> >> >
>> >> > > Hi,
>> >> > >
>> >> > > I'm using Reporting Services 2000 with the latest SP. The
>> >> > > javascript
>> >> > > code in
>> >> > > Jump To URL is giving a Page Cannot be Displayed message.
>> >> > >
>> >> > > Here is the code:
>> >> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
>> >> > > Globals!ReportFolder + "/Detail&Country_Code=" +
>> >> > > Parameters!Country_Code.Value + "&ITEMID=" +
>> >> > > Fields!Parent_Product_Code.Value
>> >> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
>> >> > > Parameters!Language.Value +
>> >> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width
>> >> > > =>> >> > > 580,height=750,top=200'))"
>> >> > >
>> >> > > Can someone plz help?
>> >> > >
>>|||Even after disabling the popup bloker, its not running.
Any other thoughts?
"Bruce L-C [MVP]" wrote:
> That should be SP2. It should be working for you.
> Copy and paste this in:
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> Also, do you have popup blocker running?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
> news:DD368BFC-B8B5-4EC6-994D-F95E603FF9FD@.microsoft.com...
> > Thanks for the reply Bruce.
> > I am using Version 8.00.1038.00.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> This looks OK. What version are you one. The ability to do this was
> >> introduced with SP1 of RS 2000. You need to be on SP1 or greater for RS
> >> 2000
> >> and any version of RS 2005.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Gaurav" <Gaurav@.discussions.microsoft.com> wrote in message
> >> news:E823758C-0A43-433F-B7C5-1EB1E9D98627@.microsoft.com...
> >> >I couldn't resolve this. Even if i put in a link to google.com it
> >> >doesn't
> >> > work.
> >> >
> >> > The following is not working:
> >> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> >> >
> >> > Any other suggestions?
> >> >
> >> > "Amarnath" wrote:
> >> >
> >> >> Get this complete link display on a text box (ofcourse without
> >> >> javascript
> >> >> syntax) and see where you are going wrong.
> >> >>
> >> >> Amarnath
> >> >>
> >> >> "Gaurav" wrote:
> >> >>
> >> >> > The links work fine if i access the report from
> >> >> > localhost/ReportServer
> >> >> > but not when i access from ReportManager.
> >> >> >
> >> >> > Is this a known issue?
> >> >> >
> >> >> >
> >> >> > "Gaurav" wrote:
> >> >> >
> >> >> > > Hi,
> >> >> > >
> >> >> > > I'm using Reporting Services 2000 with the latest SP. The
> >> >> > > javascript
> >> >> > > code in
> >> >> > > Jump To URL is giving a Page Cannot be Displayed message.
> >> >> > >
> >> >> > > Here is the code:
> >> >> > > ="javascript:void(window.open('" + Globals!ReportServerUrl + "?"+
> >> >> > > Globals!ReportFolder + "/Detail&Country_Code=" +
> >> >> > > Parameters!Country_Code.Value + "&ITEMID=" +
> >> >> > > Fields!Parent_Product_Code.Value
> >> >> > > + "&NavCat=" + STR(Parameters!NavCat.Value) + "&Language=" +
> >> >> > > Parameters!Language.Value +
> >> >> > > "&IsParent=Y&rs:Command=Render&rc:Parameters=false','','_blank,width
> >> >> > > => >> >> > > 580,height=750,top=200'))"
> >> >> > >
> >> >> > > Can someone plz help?
> >> >> > >
> >>
> >>
> >>
>
>

Jump to URL not working

Hello .. I did exaclty what you said to display a HyperLink with the JUMP to
URL. The report works fine but no Hyperlink . Is there some other setup to do
so it can works .Is it that you are asking the underline like hyperlink ? then you need to
underline it to look like hyperlink if you are asking about the hyperlink
itself then you need to check for that particular textbox properties check
the "Action" and select Jump to URL. Moreover check the "Jump to URL" after
deploying on a server and test and not from preview
Amarnath
"DJ" wrote:
> Hello .. I did exaclty what you said to display a HyperLink with the JUMP to
> URL. The report works fine but no Hyperlink . Is there some other setup to do
> so it can works .

Jump To URL Non-Clickable(not working) on Production Server

Guys, I need your help here. I'd deployed a report(SQL 2005, VS2005)
it is working on my local server, but when I publish it in my
production server, all the hyperlinks aren't working anymore, what
could be the reason? (Both SQL 2005 already installed SP2)
I've checked the tags in local server which having the following
hyperlinks for example:
<a tabindex="1" href="http://links.10026.com/?link=javascript:void(window.open('../something.aspx?
vid=1&Language=en-
US','PopUp','width=520,height=300,scrollbars=yes,toolbar=no,status=no,menubar=no'))"
style="color:Blue" TARGET="_top">some values</a>
But those tags aren't exist in my production page, and the table only
shows values that can't be click(not a link, but blue colored and
underlined).
What happen?What you should do is just set the jump to URL to the expression:
="javascript:void(window.open('URL here')"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<heislyc@.gmail.com> wrote in message
news:1187245762.711978.161950@.i13g2000prf.googlegroups.com...
> Guys, I need your help here. I'd deployed a report(SQL 2005, VS2005)
> it is working on my local server, but when I publish it in my
> production server, all the hyperlinks aren't working anymore, what
> could be the reason? (Both SQL 2005 already installed SP2)
> I've checked the tags in local server which having the following
> hyperlinks for example:
> <a tabindex="1" href="http://links.10026.com/?link=javascript:void(window.open('../something.aspx?
> vid=1&Language=en-
> US','PopUp','width=520,height=300,scrollbars=yes,toolbar=no,status=no,menubar=no'))"
> style="color:Blue" TARGET="_top">some values</a>
> But those tags aren't exist in my production page, and the table only
> shows values that can't be click(not a link, but blue colored and
> underlined).
> What happen?
>

Friday, March 23, 2012

Joins

I am really stuck trying to get a join working.. I am not even sure it is
possible. I have 2 tables. One containing Purchase Orders and another
containing Vendor Transactions. I need to link both. The follow
Transact-SQL is an example of what I currently get.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[Purchase Orders]
GO
CREATE TABLE [dbo].[Purchase Orders] (
[po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no, Qty_Invoiced)
VALUES ('00000008', 'ABC', 'ITEM123', '40')
INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no, Qty_Invoiced)
VALUES ('00000008', 'ABC', 'ITEM123', '50')
INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no, Qty_Invoiced)
VALUES ('00000008', 'ABC', 'ITEM123', '60')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Vendor
Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Vendor Transactions]
GO
CREATE TABLE [dbo].[Vendor Transactions] (
[voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
VALUES ('0009', 'ABC', '00000008')
INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
VALUES ('0010', 'ABC', '00000008')
INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
VALUES ('0011', 'ABC', '00000008')
SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase Orders].vend_no,
dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
dbo.[Vendor Transactions].po_no AS EXPR1, dbo.[Vendor
Transactions].voucher_no
FROM dbo.[Vendor Transactions] INNER JOIN
dbo.[Purchase Orders] ON dbo.[Vendor
Transactions].po_no = dbo.[Purchase Orders].po_no AND
dbo.[Vendor Transactions].Vend_no = dbo.[Purchase
Orders].vend_no
Query Results are
00000008 ABC ITEM123 40 00000008 0009
00000008 ABC ITEM123 40 00000008 0010
00000008 ABC ITEM123 40 00000008 0011
00000008 ABC ITEM123 50 00000008 0009
00000008 ABC ITEM123 50 00000008 0010
00000008 ABC ITEM123 50 00000008 0011
00000008 ABC ITEM123 60 00000008 0009
00000008 ABC ITEM123 60 00000008 0010
00000008 ABC ITEM123 60 00000008 0011
What I actually want to see is
00000008 ABC ITEM123 40 00000008 0009
00000008 ABC ITEM123 50 00000008 0010
00000008 ABC ITEM123 60 00000008 0011
Any help gratefully received. Thanks SarahGoing from your data, I was trying to solve your problem,
Thought I had to eliminate the lower numbers. (Incorrect).
Why is the following correct ? :
00000008 ABC ITEM123 40 00000008 0009
00000008 ABC ITEM123 50 00000008 0010
00000008 ABC ITEM123 60 00000008 0011
Or is this result set also correct ? :
00000008 ABC ITEM123 40 00000008 0011
00000008 ABC ITEM123 50 00000008 0010
00000008 ABC ITEM123 60 00000008 0009
If you are only joining on po_no and vend_no, then you
get 9 result rows because all po_no's and vend_no's are
the same.
If you only want to join one [Purchase Orders] row with
one [Vendor Transactions] row you have to supply a criterium
on which the join should be made.
This question has to be answered before a solution can be given.
(A rough guess would be that voucher_no is missing in the [Purchase Orders]
table and this should be added to the on clause.).
I started of by shortening your code but got stuck on the above
question. I know it is a little impolite to rewrite somebody else's work,
but I did this for clearity for myself. (In our organisation we try to
avoid spaces in identifier names, because there are a lot of
systems which can not handle spaces in identifiers. In your
code the example was broken on several of those spaces for
example.)
SELECT
B.po_no,
B.vend_no,
B.Item_no,
B.Qty_Invoiced,
A.po_no ,
A.voucher_no
FROM
dbo.[Vendor Transactions] A
INNER JOIN
dbo.[Purchase Orders] B
ON
A.po_no = B.po_no AND
A.Vend_no = B.vend_no
ben brugman
"Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> I am really stuck trying to get a join working.. I am not even sure it is
> possible. I have 2 tables. One containing Purchase Orders and another
> containing Vendor Transactions. I need to link both. The follow
> Transact-SQL is an example of what I currently get.
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[Purchase Orders]
> GO
> CREATE TABLE [dbo].[Purchase Orders] (
> [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
Qty_Invoiced)
> VALUES ('00000008', 'ABC', 'ITEM123', '40')
> INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
Qty_Invoiced)
> VALUES ('00000008', 'ABC', 'ITEM123', '50')
> INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
Qty_Invoiced)
> VALUES ('00000008', 'ABC', 'ITEM123', '60')
> if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Vendor
> Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Vendor Transactions]
> GO
> CREATE TABLE [dbo].[Vendor Transactions] (
> [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> VALUES ('0009', 'ABC', '00000008')
> INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> VALUES ('0010', 'ABC', '00000008')
> INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> VALUES ('0011', 'ABC', '00000008')
> SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase Orders].vend_no,
> dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> dbo.[Vendor Transactions].po_no AS EXPR1,
dbo.[Vendor
> Transactions].voucher_no
> FROM dbo.[Vendor Transactions] INNER JOIN
> dbo.[Purchase Orders] ON dbo.[Vendor
> Transactions].po_no = dbo.[Purchase Orders].po_no AND
> dbo.[Vendor Transactions].Vend_no = dbo.[Purchase
> Orders].vend_no
> Query Results are
> 00000008 ABC ITEM123 40 00000008 0009
> 00000008 ABC ITEM123 40 00000008 0010
> 00000008 ABC ITEM123 40 00000008 0011
> 00000008 ABC ITEM123 50 00000008 0009
> 00000008 ABC ITEM123 50 00000008 0010
> 00000008 ABC ITEM123 50 00000008 0011
> 00000008 ABC ITEM123 60 00000008 0009
> 00000008 ABC ITEM123 60 00000008 0010
> 00000008 ABC ITEM123 60 00000008 0011
> What I actually want to see is
> 00000008 ABC ITEM123 40 00000008 0009
> 00000008 ABC ITEM123 50 00000008 0010
> 00000008 ABC ITEM123 60 00000008 0011
> Any help gratefully received. Thanks Sarah
>|||Ben
Thanks for looking at this.
The problem is the Voucher Number is not stored on the Purchase Order Table.
So the 9 records will be returned in my query. I don't think I can do this
without having the voucher number on the Purchase Order Table.
"ben brugman" <ben@.niethier.nl> wrote in message
news:uVyB0w6$DHA.1956@.TK2MSFTNGP10.phx.gbl...
> Going from your data, I was trying to solve your problem,
> Thought I had to eliminate the lower numbers. (Incorrect).
> Why is the following correct ? : THIS IS BECAUSE THE FIRST RECORD FOR
ORDER NUMBER 8 WILL ALWAYS RELATE TO THE LOWEST VOUCHER NUMBER. ETC ETC
> 00000008 ABC ITEM123 40 00000008 0009
> 00000008 ABC ITEM123 50 00000008 0010
> 00000008 ABC ITEM123 60 00000008 0011
> Or is this result set also correct ? : NO THIS IS NOT CORRECT
> 00000008 ABC ITEM123 40 00000008 0011
> 00000008 ABC ITEM123 50 00000008 0010
> 00000008 ABC ITEM123 60 00000008 0009
> If you are only joining on po_no and vend_no, then you
> get 9 result rows because all po_no's and vend_no's are
> the same.
> If you only want to join one [Purchase Orders] row with
> one [Vendor Transactions] row you have to supply a criterium
> on which the join should be made.
> This question has to be answered before a solution can be given.
> (A rough guess would be that voucher_no is missing in the [Purchase
Orders]
> table and this should be added to the on clause.).
> I started of by shortening your code but got stuck on the above
> question. I know it is a little impolite to rewrite somebody else's work,
> but I did this for clearity for myself. (In our organisation we try to
> avoid spaces in identifier names, because there are a lot of
> systems which can not handle spaces in identifiers. In your
> code the example was broken on several of those spaces for
> example.)
> SELECT
> B.po_no,
> B.vend_no,
> B.Item_no,
> B.Qty_Invoiced,
> A.po_no ,
> A.voucher_no
> FROM
> dbo.[Vendor Transactions] A
> INNER JOIN
> dbo.[Purchase Orders] B
> ON
> A.po_no = B.po_no AND
> A.Vend_no = B.vend_no
>
> ben brugman
>
>
> "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > I am really stuck trying to get a join working.. I am not even sure it
is
> > possible. I have 2 tables. One containing Purchase Orders and another
> > containing Vendor Transactions. I need to link both. The follow
> > Transact-SQL is an example of what I currently get.
> >
> > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
> N'IsUserTable')
> > = 1)
> > drop table [dbo].[Purchase Orders]
> > GO
> >
> > CREATE TABLE [dbo].[Purchase Orders] (
> > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> Qty_Invoiced)
> > VALUES ('00000008', 'ABC', 'ITEM123', '40')
> > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> Qty_Invoiced)
> > VALUES ('00000008', 'ABC', 'ITEM123', '50')
> > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> Qty_Invoiced)
> > VALUES ('00000008', 'ABC', 'ITEM123', '60')
> >
> > if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Vendor
> > Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[Vendor Transactions]
> > GO
> >
> > CREATE TABLE [dbo].[Vendor Transactions] (
> > [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > VALUES ('0009', 'ABC', '00000008')
> > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > VALUES ('0010', 'ABC', '00000008')
> > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > VALUES ('0011', 'ABC', '00000008')
> >
> > SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase Orders].vend_no,
> > dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> > dbo.[Vendor Transactions].po_no AS EXPR1,
> dbo.[Vendor
> > Transactions].voucher_no
> > FROM dbo.[Vendor Transactions] INNER JOIN
> > dbo.[Purchase Orders] ON dbo.[Vendor
> > Transactions].po_no = dbo.[Purchase Orders].po_no AND
> > dbo.[Vendor Transactions].Vend_no = dbo.[Purchase
> > Orders].vend_no
> >
> > Query Results are
> > 00000008 ABC ITEM123 40 00000008 0009
> > 00000008 ABC ITEM123 40 00000008 0010
> > 00000008 ABC ITEM123 40 00000008 0011
> > 00000008 ABC ITEM123 50 00000008 0009
> > 00000008 ABC ITEM123 50 00000008 0010
> > 00000008 ABC ITEM123 50 00000008 0011
> > 00000008 ABC ITEM123 60 00000008 0009
> > 00000008 ABC ITEM123 60 00000008 0010
> > 00000008 ABC ITEM123 60 00000008 0011
> >
> > What I actually want to see is
> >
> > 00000008 ABC ITEM123 40 00000008 0009
> > 00000008 ABC ITEM123 50 00000008 0010
> > 00000008 ABC ITEM123 60 00000008 0011
> >
> > Any help gratefully received. Thanks Sarah
> >
> >
>|||"Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
news:eDusoH7$DHA.4012@.tk2msftngp13.phx.gbl...
> Ben
> Thanks for looking at this.
> The problem is the Voucher Number is not stored on the Purchase Order
Table.
> So the 9 records will be returned in my query. I don't think I can do
this
> without having the voucher number on the Purchase Order Table.
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:uVyB0w6$DHA.1956@.TK2MSFTNGP10.phx.gbl...
> > Going from your data, I was trying to solve your problem,
> > Thought I had to eliminate the lower numbers. (Incorrect).
> >
> > Why is the following correct ? :
> THIS IS BECAUSE THE FIRST RECORD FOR
> ORDER NUMBER 8 WILL ALWAYS RELATE TO THE LOWEST VOUCHER NUMBER. ETC ETC
(If answering in line, please start on a new line, I almost missed this,
the capital writing made it visible).
Problem with databases there is no first or second rows, in
a database there is no order of rows. Although in
the implementation in a real database the database does
'order' the rows in the fysical table, this can not be used.
To get an order you have to order the fields yourself
by using 'order by', offcourse this requires a field on
which you want to order.
Now suppose you have ordered both tables. Then to
make a join on the table is quite complex, because you want
to join the first rows the second rows etc. But this join
depends on the rows allready joined or on the exact number
in the roworder.
If you have an order within the rows you could add an
extra column to signify the order of rows and use that
on both tables to perform the join on.
But I doubt that this will work in the end, because I still
think some information is lacking. If the number of row
is not equal what are you going to join. Then if the number
of rows is equal, why are they in sepparate tables.
ben brugman
> >
> > 00000008 ABC ITEM123 40 00000008 0009
> > 00000008 ABC ITEM123 50 00000008 0010
> > 00000008 ABC ITEM123 60 00000008 0011
> >
> > Or is this result set also correct ? : NO THIS IS NOT CORRECT
> > 00000008 ABC ITEM123 40 00000008 0011
> > 00000008 ABC ITEM123 50 00000008 0010
> > 00000008 ABC ITEM123 60 00000008 0009
> >
> > If you are only joining on po_no and vend_no, then you
> > get 9 result rows because all po_no's and vend_no's are
> > the same.
> >
> > If you only want to join one [Purchase Orders] row with
> > one [Vendor Transactions] row you have to supply a criterium
> > on which the join should be made.
> > This question has to be answered before a solution can be given.
> > (A rough guess would be that voucher_no is missing in the [Purchase
> Orders]
> > table and this should be added to the on clause.).
> >
> > I started of by shortening your code but got stuck on the above
> > question. I know it is a little impolite to rewrite somebody else's
work,
> > but I did this for clearity for myself. (In our organisation we try to
> > avoid spaces in identifier names, because there are a lot of
> > systems which can not handle spaces in identifiers. In your
> > code the example was broken on several of those spaces for
> > example.)
> >
> > SELECT
> > B.po_no,
> > B.vend_no,
> > B.Item_no,
> > B.Qty_Invoiced,
> > A.po_no ,
> > A.voucher_no
> > FROM
> > dbo.[Vendor Transactions] A
> > INNER JOIN
> > dbo.[Purchase Orders] B
> > ON
> > A.po_no = B.po_no AND
> > A.Vend_no = B.vend_no
> >
> >
> > ben brugman
> >
> >
> >
> >
> > "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> > news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > > I am really stuck trying to get a join working.. I am not even sure it
> is
> > > possible. I have 2 tables. One containing Purchase Orders and
another
> > > containing Vendor Transactions. I need to link both. The follow
> > > Transact-SQL is an example of what I currently get.
> > >
> > > if exists (select * from dbo.sysobjects where id => > > object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
> > N'IsUserTable')
> > > = 1)
> > > drop table [dbo].[Purchase Orders]
> > > GO
> > >
> > > CREATE TABLE [dbo].[Purchase Orders] (
> > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > Qty_Invoiced)
> > > VALUES ('00000008', 'ABC', 'ITEM123', '40')
> > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > Qty_Invoiced)
> > > VALUES ('00000008', 'ABC', 'ITEM123', '50')
> > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > Qty_Invoiced)
> > > VALUES ('00000008', 'ABC', 'ITEM123', '60')
> > >
> > > if exists (select * from dbo.sysobjects where id => > object_id(N'[dbo].[Vendor
> > > Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > > drop table [dbo].[Vendor Transactions]
> > > GO
> > >
> > > CREATE TABLE [dbo].[Vendor Transactions] (
> > > [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > VALUES ('0009', 'ABC', '00000008')
> > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > VALUES ('0010', 'ABC', '00000008')
> > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > VALUES ('0011', 'ABC', '00000008')
> > >
> > > SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase Orders].vend_no,
> > > dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> > > dbo.[Vendor Transactions].po_no AS EXPR1,
> > dbo.[Vendor
> > > Transactions].voucher_no
> > > FROM dbo.[Vendor Transactions] INNER JOIN
> > > dbo.[Purchase Orders] ON dbo.[Vendor
> > > Transactions].po_no = dbo.[Purchase Orders].po_no AND
> > > dbo.[Vendor Transactions].Vend_no =dbo.[Purchase
> > > Orders].vend_no
> > >
> > > Query Results are
> > > 00000008 ABC ITEM123 40 00000008 0009
> > > 00000008 ABC ITEM123 40 00000008 0010
> > > 00000008 ABC ITEM123 40 00000008 0011
> > > 00000008 ABC ITEM123 50 00000008 0009
> > > 00000008 ABC ITEM123 50 00000008 0010
> > > 00000008 ABC ITEM123 50 00000008 0011
> > > 00000008 ABC ITEM123 60 00000008 0009
> > > 00000008 ABC ITEM123 60 00000008 0010
> > > 00000008 ABC ITEM123 60 00000008 0011
> > >
> > > What I actually want to see is
> > >
> > > 00000008 ABC ITEM123 40 00000008 0009
> > > 00000008 ABC ITEM123 50 00000008 0010
> > > 00000008 ABC ITEM123 60 00000008 0011
> > >
> > > Any help gratefully received. Thanks Sarah
> > >
> > >
> >
> >
>|||For Oracle the following construct is possible :
SELECT
B.po_no,
B.vend_no,
B.Item_no,
B.Qty_Invoiced,
A.po_no ,
A.voucher_no
FROM
(select ..., rownum r1 from VendorTransactions) A
INNER JOIN
(select ..., rownum r2 from Purchase Orders) B
ON
R1 = R2
But here the other criteria are left out.
In SQL-server it would be easier to add a (temporary) extra column,
to both tables,
fill that with the required numbers and do the join on those numbers.
The filling of numbers offcourse depends on what you actually want.
Say the extra column is called X
alter table [vendor transactions] add x int
UPDATE [vendor transactions]
SET X = (select count(*) from [vendor transactions] as T2
where [vendor transactions].po_no = T2.po_no and
( [vendor transactions].voucher_no >= T2.voucher_no) )
select * from [vendor transactions]
-- will result in :
voucher_no Vend_no po_no x
-- -- -- --
0009 ABC 00000008 1
0010 ABC 00000008 2
0011 ABC 00000008 3
I do not know if the counting has to be done on Vend_no or po_no or both,
adjust
the example for that. The ordering here is done on the value of voucher_no.
For purchase orders the ordering probably has to be done on Qty_Invoiced,
this does not make sense to me, but it is the only column which
discriminates
in the example.
If the extra column is done on both tables, one can join on that column x.
(And afterwards drop the column x).
ben brugman
"ben brugman" <ben@.niethier.nl> wrote in message
news:c208fo$91$1@.reader08.wxs.nl...
> "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> news:eDusoH7$DHA.4012@.tk2msftngp13.phx.gbl...
> > Ben
> >
> > Thanks for looking at this.
> >
> > The problem is the Voucher Number is not stored on the Purchase Order
> Table.
> > So the 9 records will be returned in my query. I don't think I can do
> this
> > without having the voucher number on the Purchase Order Table.
> >
>
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:uVyB0w6$DHA.1956@.TK2MSFTNGP10.phx.gbl...
> > > Going from your data, I was trying to solve your problem,
> > > Thought I had to eliminate the lower numbers. (Incorrect).
> > >
> > > Why is the following correct ? :
> > THIS IS BECAUSE THE FIRST RECORD FOR
> > ORDER NUMBER 8 WILL ALWAYS RELATE TO THE LOWEST VOUCHER NUMBER. ETC ETC
> (If answering in line, please start on a new line, I almost missed this,
> the capital writing made it visible).
> Problem with databases there is no first or second rows, in
> a database there is no order of rows. Although in
> the implementation in a real database the database does
> 'order' the rows in the fysical table, this can not be used.
> To get an order you have to order the fields yourself
> by using 'order by', offcourse this requires a field on
> which you want to order.
> Now suppose you have ordered both tables. Then to
> make a join on the table is quite complex, because you want
> to join the first rows the second rows etc. But this join
> depends on the rows allready joined or on the exact number
> in the roworder.
> If you have an order within the rows you could add an
> extra column to signify the order of rows and use that
> on both tables to perform the join on.
> But I doubt that this will work in the end, because I still
> think some information is lacking. If the number of row
> is not equal what are you going to join. Then if the number
> of rows is equal, why are they in sepparate tables.
> ben brugman
>
> > >
> > > 00000008 ABC ITEM123 40 00000008 0009
> > > 00000008 ABC ITEM123 50 00000008 0010
> > > 00000008 ABC ITEM123 60 00000008 0011
> > >
> > > Or is this result set also correct ? : NO THIS IS NOT CORRECT
> > > 00000008 ABC ITEM123 40 00000008 0011
> > > 00000008 ABC ITEM123 50 00000008 0010
> > > 00000008 ABC ITEM123 60 00000008 0009
> > >
> > > If you are only joining on po_no and vend_no, then you
> > > get 9 result rows because all po_no's and vend_no's are
> > > the same.
> > >
> > > If you only want to join one [Purchase Orders] row with
> > > one [Vendor Transactions] row you have to supply a criterium
> > > on which the join should be made.
> > > This question has to be answered before a solution can be given.
> > > (A rough guess would be that voucher_no is missing in the [Purchase
> > Orders]
> > > table and this should be added to the on clause.).
> > >
> > > I started of by shortening your code but got stuck on the above
> > > question. I know it is a little impolite to rewrite somebody else's
> work,
> > > but I did this for clearity for myself. (In our organisation we try to
> > > avoid spaces in identifier names, because there are a lot of
> > > systems which can not handle spaces in identifiers. In your
> > > code the example was broken on several of those spaces for
> > > example.)
> > >
> > > SELECT
> > > B.po_no,
> > > B.vend_no,
> > > B.Item_no,
> > > B.Qty_Invoiced,
> > > A.po_no ,
> > > A.voucher_no
> > > FROM
> > > dbo.[Vendor Transactions] A
> > > INNER JOIN
> > > dbo.[Purchase Orders] B
> > > ON
> > > A.po_no = B.po_no AND
> > > A.Vend_no = B.vend_no
> > >
> > >
> > > ben brugman
> > >
> > >
> > >
> > >
> > > "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> > > news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > > > I am really stuck trying to get a join working.. I am not even sure
it
> > is
> > > > possible. I have 2 tables. One containing Purchase Orders and
> another
> > > > containing Vendor Transactions. I need to link both. The follow
> > > > Transact-SQL is an example of what I currently get.
> > > >
> > > > if exists (select * from dbo.sysobjects where id => > > > object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
> > > N'IsUserTable')
> > > > = 1)
> > > > drop table [dbo].[Purchase Orders]
> > > > GO
> > > >
> > > > CREATE TABLE [dbo].[Purchase Orders] (
> > > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > Qty_Invoiced)
> > > > VALUES ('00000008', 'ABC', 'ITEM123', '40')
> > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > Qty_Invoiced)
> > > > VALUES ('00000008', 'ABC', 'ITEM123', '50')
> > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > Qty_Invoiced)
> > > > VALUES ('00000008', 'ABC', 'ITEM123', '60')
> > > >
> > > > if exists (select * from dbo.sysobjects where id => > > object_id(N'[dbo].[Vendor
> > > > Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > > > drop table [dbo].[Vendor Transactions]
> > > > GO
> > > >
> > > > CREATE TABLE [dbo].[Vendor Transactions] (
> > > > [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO
> > > >
> > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > > VALUES ('0009', 'ABC', '00000008')
> > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > > VALUES ('0010', 'ABC', '00000008')
> > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no, po_no)
> > > > VALUES ('0011', 'ABC', '00000008')
> > > >
> > > > SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase
Orders].vend_no,
> > > > dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> > > > dbo.[Vendor Transactions].po_no AS EXPR1,
> > > dbo.[Vendor
> > > > Transactions].voucher_no
> > > > FROM dbo.[Vendor Transactions] INNER JOIN
> > > > dbo.[Purchase Orders] ON dbo.[Vendor
> > > > Transactions].po_no = dbo.[Purchase Orders].po_no AND
> > > > dbo.[Vendor Transactions].Vend_no => dbo.[Purchase
> > > > Orders].vend_no
> > > >
> > > > Query Results are
> > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > 00000008 ABC ITEM123 40 00000008 0010
> > > > 00000008 ABC ITEM123 40 00000008 0011
> > > > 00000008 ABC ITEM123 50 00000008 0009
> > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > 00000008 ABC ITEM123 50 00000008 0011
> > > > 00000008 ABC ITEM123 60 00000008 0009
> > > > 00000008 ABC ITEM123 60 00000008 0010
> > > > 00000008 ABC ITEM123 60 00000008 0011
> > > >
> > > > What I actually want to see is
> > > >
> > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > 00000008 ABC ITEM123 60 00000008 0011
> > > >
> > > > Any help gratefully received. Thanks Sarah
> > > >
> > > >
> > >
> > >
> >
> >
>|||Ben
Thanks again for your time and knowledge on this.. I haven't had a chance to
sit down and go through your postings. I am looking to do this over the
next few days.
Kind Regards Sarah
"ben brugman" <ben@.niethier.nl> wrote in message
news:OD2VYaFAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> For Oracle the following construct is possible :
> SELECT
> B.po_no,
> B.vend_no,
> B.Item_no,
> B.Qty_Invoiced,
> A.po_no ,
> A.voucher_no
> FROM
> (select ..., rownum r1 from VendorTransactions) A
> INNER JOIN
> (select ..., rownum r2 from Purchase Orders) B
> ON
> R1 = R2
> But here the other criteria are left out.
> In SQL-server it would be easier to add a (temporary) extra column,
> to both tables,
> fill that with the required numbers and do the join on those numbers.
> The filling of numbers offcourse depends on what you actually want.
> Say the extra column is called X
> alter table [vendor transactions] add x int
> UPDATE [vendor transactions]
> SET X = (select count(*) from [vendor transactions] as T2
> where [vendor transactions].po_no = T2.po_no and
> ( [vendor transactions].voucher_no >= T2.voucher_no) )
> select * from [vendor transactions]
> -- will result in :
> voucher_no Vend_no po_no x
> -- -- -- --
> 0009 ABC 00000008 1
> 0010 ABC 00000008 2
> 0011 ABC 00000008 3
> I do not know if the counting has to be done on Vend_no or po_no or both,
> adjust
> the example for that. The ordering here is done on the value of
voucher_no.
> For purchase orders the ordering probably has to be done on Qty_Invoiced,
> this does not make sense to me, but it is the only column which
> discriminates
> in the example.
> If the extra column is done on both tables, one can join on that column x.
> (And afterwards drop the column x).
> ben brugman
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:c208fo$91$1@.reader08.wxs.nl...
> >
> > "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> > news:eDusoH7$DHA.4012@.tk2msftngp13.phx.gbl...
> > > Ben
> > >
> > > Thanks for looking at this.
> > >
> > > The problem is the Voucher Number is not stored on the Purchase Order
> > Table.
> > > So the 9 records will be returned in my query. I don't think I can do
> > this
> > > without having the voucher number on the Purchase Order Table.
> > >
> >
> >
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:uVyB0w6$DHA.1956@.TK2MSFTNGP10.phx.gbl...
> > > > Going from your data, I was trying to solve your problem,
> > > > Thought I had to eliminate the lower numbers. (Incorrect).
> > > >
> > > > Why is the following correct ? :
> >
> > > THIS IS BECAUSE THE FIRST RECORD FOR
> > > ORDER NUMBER 8 WILL ALWAYS RELATE TO THE LOWEST VOUCHER NUMBER. ETC
ETC
> > (If answering in line, please start on a new line, I almost missed this,
> > the capital writing made it visible).
> >
> > Problem with databases there is no first or second rows, in
> > a database there is no order of rows. Although in
> > the implementation in a real database the database does
> > 'order' the rows in the fysical table, this can not be used.
> >
> > To get an order you have to order the fields yourself
> > by using 'order by', offcourse this requires a field on
> > which you want to order.
> >
> > Now suppose you have ordered both tables. Then to
> > make a join on the table is quite complex, because you want
> > to join the first rows the second rows etc. But this join
> > depends on the rows allready joined or on the exact number
> > in the roworder.
> >
> > If you have an order within the rows you could add an
> > extra column to signify the order of rows and use that
> > on both tables to perform the join on.
> >
> > But I doubt that this will work in the end, because I still
> > think some information is lacking. If the number of row
> > is not equal what are you going to join. Then if the number
> > of rows is equal, why are they in sepparate tables.
> >
> > ben brugman
> >
> >
> > > >
> > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > 00000008 ABC ITEM123 60 00000008 0011
> > > >
> > > > Or is this result set also correct ? : NO THIS IS NOT CORRECT
> > > > 00000008 ABC ITEM123 40 00000008 0011
> > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > 00000008 ABC ITEM123 60 00000008 0009
> > > >
> > > > If you are only joining on po_no and vend_no, then you
> > > > get 9 result rows because all po_no's and vend_no's are
> > > > the same.
> > > >
> > > > If you only want to join one [Purchase Orders] row with
> > > > one [Vendor Transactions] row you have to supply a criterium
> > > > on which the join should be made.
> > > > This question has to be answered before a solution can be given.
> > > > (A rough guess would be that voucher_no is missing in the [Purchase
> > > Orders]
> > > > table and this should be added to the on clause.).
> > > >
> > > > I started of by shortening your code but got stuck on the above
> > > > question. I know it is a little impolite to rewrite somebody else's
> > work,
> > > > but I did this for clearity for myself. (In our organisation we try
to
> > > > avoid spaces in identifier names, because there are a lot of
> > > > systems which can not handle spaces in identifiers. In your
> > > > code the example was broken on several of those spaces for
> > > > example.)
> > > >
> > > > SELECT
> > > > B.po_no,
> > > > B.vend_no,
> > > > B.Item_no,
> > > > B.Qty_Invoiced,
> > > > A.po_no ,
> > > > A.voucher_no
> > > > FROM
> > > > dbo.[Vendor Transactions] A
> > > > INNER JOIN
> > > > dbo.[Purchase Orders] B
> > > > ON
> > > > A.po_no = B.po_no AND
> > > > A.Vend_no = B.vend_no
> > > >
> > > >
> > > > ben brugman
> > > >
> > > >
> > > >
> > > >
> > > > "Sarah Kingswell" <sarah.kingswell@.ntlworld.com> wrote in message
> > > > news:#iEa7R6$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > > > > I am really stuck trying to get a join working.. I am not even
sure
> it
> > > is
> > > > > possible. I have 2 tables. One containing Purchase Orders and
> > another
> > > > > containing Vendor Transactions. I need to link both. The follow
> > > > > Transact-SQL is an example of what I currently get.
> > > > >
> > > > > if exists (select * from dbo.sysobjects where id => > > > > object_id(N'[dbo].[Purchase Orders]') and OBJECTPROPERTY(id,
> > > > N'IsUserTable')
> > > > > = 1)
> > > > > drop table [dbo].[Purchase Orders]
> > > > > GO
> > > > >
> > > > > CREATE TABLE [dbo].[Purchase Orders] (
> > > > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [Item_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [Qty_Invoiced] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > > > ) ON [PRIMARY]
> > > > > GO
> > > > >
> > > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > > Qty_Invoiced)
> > > > > VALUES ('00000008', 'ABC', 'ITEM123', '40')
> > > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > > Qty_Invoiced)
> > > > > VALUES ('00000008', 'ABC', 'ITEM123', '50')
> > > > > INSERT INTO [dbo].[Purchase Orders] (po_no, vend_no, Item_no,
> > > > Qty_Invoiced)
> > > > > VALUES ('00000008', 'ABC', 'ITEM123', '60')
> > > > >
> > > > > if exists (select * from dbo.sysobjects where id => > > > object_id(N'[dbo].[Vendor
> > > > > Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > > > > drop table [dbo].[Vendor Transactions]
> > > > > GO
> > > > >
> > > > > CREATE TABLE [dbo].[Vendor Transactions] (
> > > > > [voucher_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [Vend_no] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
> > > > > [po_no] [char] (10) COLLATE Latin1_General_CI_AS NULL
> > > > > ) ON [PRIMARY]
> > > > > GO
> > > > >
> > > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no,
po_no)
> > > > > VALUES ('0009', 'ABC', '00000008')
> > > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no,
po_no)
> > > > > VALUES ('0010', 'ABC', '00000008')
> > > > > INSERT INTO [dbo].[Vendor Transactions] (voucher_no, vend_no,
po_no)
> > > > > VALUES ('0011', 'ABC', '00000008')
> > > > >
> > > > > SELECT dbo.[Purchase Orders].po_no, dbo.[Purchase
> Orders].vend_no,
> > > > > dbo.[Purchase Orders].Item_no, dbo.[Purchase Orders].Qty_Invoiced,
> > > > > dbo.[Vendor Transactions].po_no AS EXPR1,
> > > > dbo.[Vendor
> > > > > Transactions].voucher_no
> > > > > FROM dbo.[Vendor Transactions] INNER JOIN
> > > > > dbo.[Purchase Orders] ON dbo.[Vendor
> > > > > Transactions].po_no = dbo.[Purchase Orders].po_no AND
> > > > > dbo.[Vendor Transactions].Vend_no => > dbo.[Purchase
> > > > > Orders].vend_no
> > > > >
> > > > > Query Results are
> > > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > > 00000008 ABC ITEM123 40 00000008 0010
> > > > > 00000008 ABC ITEM123 40 00000008 0011
> > > > > 00000008 ABC ITEM123 50 00000008 0009
> > > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > > 00000008 ABC ITEM123 50 00000008 0011
> > > > > 00000008 ABC ITEM123 60 00000008 0009
> > > > > 00000008 ABC ITEM123 60 00000008 0010
> > > > > 00000008 ABC ITEM123 60 00000008 0011
> > > > >
> > > > > What I actually want to see is
> > > > >
> > > > > 00000008 ABC ITEM123 40 00000008 0009
> > > > > 00000008 ABC ITEM123 50 00000008 0010
> > > > > 00000008 ABC ITEM123 60 00000008 0011
> > > > >
> > > > > Any help gratefully received. Thanks Sarah
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Joins

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

Friday, March 30, 2012

Jump to URL was working - now not working

Jump To URL not working

Jump to URL not working

Jump To URL Non-Clickable(not working) on Production Server

Friday, March 23, 2012

Joins

Joins

Wednesday, March 21, 2012

Joining tables in different databases

join or subselect

Blog Archive

About Me