Multi-Value parameter Select All

Is there a way to tell if a user has checked Select All for a multi-valued
parameter? I'd like my query to be different if they have.I recall from a previous post that this was not possible.
here is the link to the post.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?&query=tell+if+user+selected+all&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=e86a166d-61fa-44d8-867c-fc4f29781b5b&mid=e86a166d-61fa-44d8-867c-fc4f29781b5b
"Michelle" wrote:
> Is there a way to tell if a user has checked Select All for a multi-valued
> parameter? I'd like my query to be different if they have.

multi-value paramater from query: dynamically filtered

Hi,
I have a single value paramater that with a list of choices. I have a
second parameter, that is a mulit-value paramater with the choices derived
from a query. What I would lilke to do is filter the choice of the values
in the mult-value paramter by the value selected in the first single value
paramater. Is this possible. I know this can done on a web page, but so
far as I am aware the code behind these reports is XML and I don't know
enough about it to tell it to requery the multi-value parameter's query when
the sinlge value paramater's value is selected.
Can anyone explain how to do this, or let me know of any other way of
achieving this?
TIA,
JarrydHi Jarryd,
From your description, I understnad that you would like to implement
cascading parameters selection in your report.
If I have misunderstood, please let me know.
This seems to be a typical application scenario. I recommend that you refer
to this article to see if it helps:
Lesson 2: Adding Cascading Parameters to a Report
http://msdn2.microsoft.com/en-us/library/aa337426.aspx
If you have any other questioins or concerns, please feel free to let me
know. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
===========================================================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.
=========================================================|||Charles my man, you have done it again! Genius!
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:07X9SNFoIHA.10116@.TK2MSFTNGHUB02.phx.gbl...
> Hi Jarryd,
> From your description, I understnad that you would like to implement
> cascading parameters selection in your report.
> If I have misunderstood, please let me know.
> This seems to be a typical application scenario. I recommend that you
> refer
> to this article to see if it helps:
> Lesson 2: Adding Cascading Parameters to a Report
> http://msdn2.microsoft.com/en-us/library/aa337426.aspx
> If you have any other questioins or concerns, please feel free to let me
> know. Have a nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@.microsoft.com.
> ===========================================================> 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.
> =========================================================>|||:) My pleasure!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================sql

Multitable query

Hi there,

I have what seemed to look a simple qeury but I get double results or not enough.

There are 3 tables

ToolboxesToolsToolsInBoxes* ToolboxID* ToolID* ToolboxID* AllowedSizeID* SizeID* ToolID* Name* Name* Price1WhenPutInToolBox* #toolsInBox* PriceTool1* Price2WhenPutInToolBox* PriceBox1* PriceTool2* DateAdded* PriceBox2* InStock* DateRemoved* InStock,,,,,,,,,

some data for testing

Tools
ToolID SizeID Name PriceTool1 PriceTool2
1 2 NameTool1 4 5
2 2 NameTool2 2 3
3 2 NameTool3 22 25
4 3 NameTool4 9 14
5 2 NameTool5 33 36
6 2 NameTool6 23 27
7 3 NameTool7 7 7

ToolBoxToolboxIDAllowedSizeIDName#toolsInBoxPriceBox1PriceBox212ToolBox1210011023ToolBox2115016032ToolBox31200200

ToolsInBoxesToolboxIDToolIDPrice1WhenPutInToolBox Price2WhenPutInToolBox DateAddedDateRemoved11101511-11-2007n/a1281310-11-2007n/a15404512-10-200713-10-20072491409-09-2007n/a328813307-07-2007n/a

Now when I want to modify ToolBox1 (Size = 2) I would like to get a list of the Tools already in the toolbox AND the available tools that fit the AllowedSize for that toolbox in ONE table so it would be easy to add/remove tools from the toolbox

ToolboxID=1 (Size = 2)InToolBoxToolIDNamePrice1WhenPutInToolBox Price2WhenPutInToolBoxPriceTool1PriceTool2DateAddedDateRemovedYes1NameTool110154511-11-2007n/aYes2NameTool28132310-11-2007n/aavailable3NameTool3n/an/a2225n/an/aavailable5NameTool54045333612-10-200713-10-2007available6NameTool6n/an/a2327n/an/a

when a tool is removed from toolbox it becomes available again and keeps all information from that ToolBox

for ToolBoxID = 2 (Size = 3)InToolBoxToolIDNamePrice1WhenPutInToolBox Price2WhenPutInToolBoxPriceTool1PriceTool2DateAddedDateRemovedYes4NameTool4182891409-09-2007n/aavailable7NameTool7141477n/an/a

A tool can be put in many toolboxes

for ToolBoxID = 3 (Size = 2)InToolBoxToolIDNamePrice1WhenPutInToolBox Price2WhenPutInToolBoxPriceTool1PriceTool2DateAddedDateRemovedYes2NameTool2881332307-07-2007n/aavailable1NameTool1n/an/a45n/an/aavailable3NameTool3n/an/a2225n/an/aavailable5NameTool5n/an/a3336n/an/aavailable6NameTool6n/an/a2327n/an/a

I tried many, many queries, but not a good result.

I simple words it should be somthing like :

SELECT * FROM ToolsInBoxes WHERE ToolsInBoxes.ToolBoxID = (xx)

AND

( SELECT * FROM Tools WHERE Tools.Size = ToolBox(xx).Size

AND Tools NOT IN ToolsInBoxes WHERE ToolsInBoxes.ToolBoxID = (xx) )

I would appriciate any help

Best regards,

Bonaparte

1)Get a list of tools that already in the toobox

Select * from tools where ToolID IN(Select tooID from ToolsInBoxes where ToolBoxID=2)

2)Available Tools--
Select t.* from Tools t
inner join Toolbox tb ON tb.AllowedSizeID=t.SizeID AND AllowedSizeID=2
And t.ToolID NOT IN(Select tooID from ToolsInBoxes where ToolBoxID=2)

3) Since you want all the tools in one table you can create a field to differentiate the two queries and Union them

Select *, Flag="Tools Already in Box" from tools where ToolID IN(Select tooID from ToolsInBoxes where ToolBoxID=2)
UNION ALL
Select t.*,Flag="Available Tools" from Tools t
inner join Toolbox tb ON tb.AllowedSizeID=t.SizeID AND AllowedSizeID=2
And t.ToolID NOT IN(Select tooID from ToolsInBoxes where ToolBoxID=2)

Hope that helps and if that anwered your question please mark as answer.

|||

Thanks for your quick reply

3)Since you want all the tools in one table you can create a field to differentiate the two queries and Union them

So far so good, but I also want to have displayed the additional fields from ToolsInBox ( like Price1WhenPutInBox, DateAdded, ... ) in the same table (row).

like in the examples

and this can not be done with UNION

All queries combined using a UNION operator must have an equal number of expressions in their target list

Best regards,

Bonaparte

|||

This will give you something to start with although I was not able to understand your results in some cases..

SET dateformat dmygoDECLARE @.ToolsTABLE(ToolIDINT, SizeIDINT,Name VARCHAR(50), PriceTool1DECIMAL(10,2), PriceTool2DECIMAL(10,2))INSERT INTO @.ToolsSELECT 1, 2 ,'NameTool1' , 4 , 5UNIONALLSELECT 2, 2 ,'NameTool2' , 2 , 3UNIONALLSELECT 3, 2 ,'NameTool3' , 22 , 25UNIONALLSELECT 4, 3 ,'NameTool4' , 9 , 14UNIONALLSELECT 5, 2 ,'NameTool5' , 33 , 36UNIONALLSELECT 6, 2 ,'NameTool6' , 23 , 27UNIONALLSELECT 7, 3 ,'NameTool7' , 7 , 7DECLARE @.ToolBoxTABLE (ToolboxIDINT, AllowedSizeIDINT,Name VARCHAR(50), [#toolsInBox]INT, PriceBox1DECIMAL(10,2), PriceBox2DECIMAL(10,2))INSERT INTO @.ToolBoxSELECT 1, 2,'ToolBox1', 2, 100, 110UNIONALLSELECT 2, 3,'ToolBox2', 1, 150, 160UNIONALLSELECT 3, 2,'ToolBox3', 1, 200, 200DECLARE @.ToolsInBoxesTABLE (ToolboxIDINT,ToolIDINT, Price1WhenPutInToolBoxDECIMAL(10,2), Price2WhenPutInToolBoxDECIMAL(10,2), DateAddeddatetime,DateRemovedDATETIME)INSERT INTO @.ToolsInBoxesSELECT 1, 1, 10, 15,'11-11-2007',NULLUNIONALLSELECT 1, 2, 8, 13,'10-11-2007' ,NULLUNIONALLSELECT 1, 5, 40, 45,'12-10-2007','13-10-2007'UNIONALLSELECT 2, 4, 9, 14,'09-09-2007',NULLUNIONALLSELECT 3, 2, 88, 133,'07-07-2007',NULLDECLARE @.ToolBoxIDINT, @.SizeIDINTSELECT @.ToolBoxID = 1, @.SizeID=2SELECT T.toolid, T.Name, Price1WhenPutInToolBox,TIB.Price2WhenPutInToolBox, T.PriceTool1, T.PriceTool2, TIB.DateAdded, TIB.DateRemovedFROM @.Tools TJOIN @.ToolsInBoxes TIBON T.ToolID = TIB.ToolIDWHERE TIB.ToolboxID = @.ToolBoxIDUNION SELECT T.toolid, T.Name , TIB.Price1WhenPutInToolBox, TIB.Price2WhenPutInToolBox, T.PriceTool1, T.PriceTool2, TIB.DateAdded, TIB.DateRemovedFROM @.Tools TLEFTJOIN @.ToolsInBoxes TIBON T.ToolID = TIB.ToolIDWHERE T.SizeID = @.SizeIDAND NOT EXISTS(SELECT *FROM @.ToolsInBoxes T2WHERE T2.ToolId = T.ToolIdAND ToolboxID = @.ToolBoxID)

|||

Almost there ..

AND NOT EXISTS(SELECT *FROM @.ToolsInBoxes T2WHERE T2.ToolId = T.ToolIdAND ToolboxID = @.ToolBoxID)

displays 2 much,

the table : ToolsInBoxes can contain the ToolID more than ONES, from different ToolBoxes or deleted from ToolBox

pe. ToolID = 2 is used in ToolBox 1 and 3

The current UNION-part displays both.

I want ONLY the available TOOLS (with the good Size) that are NOT used yet.

I try to explain in plain text:

I have a number of Tools with Size, Price etc

I have a number of ToolBox, with Price, AllowedSize etc

I have a TABLE that contains de ToolsInBoxes, from ALL ToolBoxes, With ToolID, Price1WhenPutInToolBox, DateAdded etc

----------------------

Now lets say from the sapmle ToolboxID = 1:

In myToolBox are now 2 Tools from Size 2

1- Show me the ToolsInBox fromToolBox (that's OK, part 1 of the UNION)

a => ToolID = 1

b => ToolID = 2

c ToolID = 5 is removed so DO NOT SHOW, but the ToolID is back available to RE-use

2- Show me the Tools that are available for ToolBox

a => ToolID = 3

b => ToolID = 6

c => ToolID = 5 (was removed, so is available again)

--------------

As you can see ToolID = 2 is also in ToolBox 3 and there is where the problem is with the part 2 of the UNION.

Tools Size 2 (1,2,3,5,6)

Tools in ToolBox 1 (1,2)

SHOW ME

1- Tools in ToolBox = 1,2

2 - Tools available for THIS ToolBox = 3,5,6

That's all

Hope I explained it better this time

Best regards,

Bonaparte

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner jo
in
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get s
ome
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resourc
e
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/defaul...kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get
some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resou
rce
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, look
s
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was ab
le
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BT
W,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if thi
s
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However
if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveK
Could you please post your join query ?
-jens Suessmeyer.
|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.
|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default...b;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>
|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?
|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default.aspx?scid=kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:
> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:
> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004sql

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ? Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time?
Or does SQL do this automatically?
Thanks
Sean
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.c om...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ? Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time?
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

Multiselect Slicing MDX Query Question

Hello,

I am trying to understand MDX multiselect / slicing / axis restriction. And there may be some attribute hierarchy vs normal hierarchy input as well. Any thoughts would be very helpful

I've written a few queries from the Adventure Works cube.

Query 1: Multiselect slicing on the Date attribute hierarchy; Displaying the Fiscal hierarchy, Date level on the axis. Normal, expected results.

Code Snippet

select [Date].[Fiscal].[Date].Members on 0
from [Adventure Works]
where ({[Date].[Date].&[1000], [Date].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

March 26, 2004 July 4, 2004
$43,703.84 $1,301.33

Query 2: Multiselect slicing on the Date attribute hierarchy; Displaying the Fiscal hierarchy, Month level on the axis. I believe I am getting different results because the visual total is overwritten. Essentially, when I specify the Month on the axis, I get rid of my initial slice of two days, so I get the entire month's values. Is this correct?

Code Snippet

select [Date].[Fiscal].[Month].Members on 0
from [Adventure Works]
where ({[Date].[Date].&[1000], [Date].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

March 2004 July 2004
$1,480,905.18 $50,840.63

Query 3: Multiselect slicing on the Fiscal hierarchy, Date level; Displaying the [Month of year] attribute hierarchy on the avis. I have absolutely no idea why I am not getting anything back for July. Can anyone explain this to me?

Code Snippet

select [Date].[Month of Year].Members on 0
from [Adventure Works]
where ({[Date].[Fiscal].[Date].&[1000], [Date].[Fiscal].[Date].&[1100]}, [Measures].[Internet Sales Amount])

Results:

All Periods March July
$43,703.84 $43,703.84 (null)

I'm running SP2.

Thanks!
Jessica

If Query 3 is re-written using a subselect, data is returned for July:

Code Snippet

select [Date].[Month of Year].Members on 0

from (select

{[Date].[Fiscal].[Date].&[1000], [Date].[Fiscal].[Date].&[1100]} on 0

from [Adventure Works])

where ([Measures].[Internet Sales Amount])

All Periods March July
$45,005.17 $43,703.84 $1,301.33

Also, if the order of the selected dates is reversed, no data is returned for March instead:

Code Snippet

select [Date].[Month of Year].Members on 0

from [Adventure Works]

where ({[Date].[Fiscal].[Date].&[1100], [Date].[Fiscal].[Date].&[1000]},

[Measures].[Internet Sales Amount])

All Periods March July
$1,301.33 (null) $1,301.33

So, at first glance, this seems like a bug to me - but maybe someone else can explain the results differently?