multi-value parameter in Master report passing to single param sub-report in a list.

Here's tricky one.

I have a fairly complex report that was given to me that was hard coded for single parameters. There is a dropdown for each market (created from a query in SSRS). The users have to run for each market each week.

Is there a way to use this report as a Sub-report inside a list of a master report and then use a mult-value parameter?

I want this multi-value parameter to build the values for the list and then run the "sub-report" for each value.

Essentially, I want to create a for each loop.

Any ideas?

I have the same problem where I need to run a subreport for each value in a multivalued paramter. Does anyone have a solution?
|||

Here is one way to accomplish this if your parameter values come from a query:

In your main report, add a list to the body bound to the same data set as the multi-value parameter.

On the list, add a Filter with the following properties:

Expression: The same as used to populate the parameter value.

Operator: In

Value: Use the parameter value (i.e. =Fields!MyMultiValueParam.Value) Because the parameter is a multi-value parameter, this will evaluate to an array.

sql

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

multi-thread tasks using a single connection object concurrently

is it possible to use a single connection object shared by several
tasks where each task is handled by a thread? these tasks call stored
procedures that return record sets, no editing, update or delete.

my objective is that db connection is expensive and each user can only
have 1 connection object. each user submits a request to the web
server, and the result web page construction consists of a number of
result sets obtained from several stored procedure from the same
database instance.

i could obtained my result sets by making each sp call one at a time
but was thinking whether i could instead break up the tasks, create a
thread for each task that shares the same db connection object
concurrently. the connection object will be alive until all tasks are
completed, that is if the final object ive could be achieved in a
shorter time. No connection pooling here.

i am wondering over questions like:

1. within the same db instance, if 2 stored procedures need to access
the same table, does SQLserver queue-up and service one request at a
time.

2. would there be any problem using the connection object this way,
sharing between multiple theads?

3. is it possible that a 'dead-lock' may occur within the DB?

of cos this whole idea can be absurd and into the trash if the
connection object doesn't support multi-thread and is queue based in
the first place.

pardon me if my SQL server basics is off track. been doing too many
other things.

thanks,
mengmeng (hui_km@.star-quest.com) writes:
> i could obtained my result sets by making each sp call one at a time
> but was thinking whether i could instead break up the tasks, create a
> thread for each task that shares the same db connection object
> concurrently. the connection object will be alive until all tasks are
> completed, that is if the final object ive could be achieved in a
> shorter time. No connection pooling here.

I honestly don't know if you can share connection objects between threads,
but in any case it seems like a pointless thing, because the connection
object would be a common resource. You cannot submit to queries on the
same connection object at the same time. ...ah, wait, actually with ADO
you can, but what happens is that ADO opens a new connection behind your
back for you.

> 1. within the same db instance, if 2 stored procedures need to access
> the same table, does SQLserver queue-up and service one request at a
> time.

Depends on how they procedures access the tables. For read operations,
the procedures may well execute in parallel. But if one process starts
to update, the other process will be blocked. (Unless it uses an
isoalation level of uncommitted.)

> 2. would there be any problem using the connection object this way,
> sharing between multiple theads?

As I said, it would be a pointless thing to do.

> 3. is it possible that a 'dead-lock' may occur within the DB?

Yes. That is not to say that you will experience dead-locks, only that
it could happen to you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||hi erland,

i was thinking the connection object is like a handle to a database
connection, but if has its own state, and if its not a thread-safe
object, then it would not work at all. even if i can wrap the connection
object in thread-safe codes, if it cannot handle thread context switch,
the idea is useless. the part about ado opening a connection on its own
accord is interesting though. i didn't know that at all.

the piece of info is helping decide how i should implement and write my
app server components. thanks for your response.

regards,
meng

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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-select Parameter - Single Quote Problem

Hi,
I am using SQL Server 2005 Reporting Services. I am trying to pass a
multi-select string value to my query but having difficulties with
single quotes. My query is:
SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
(@.CustomerType)
@.CustomerType is a multi-select parameter in my report. The value of
this parameter should be passed as 'Commercial','Residential' BUT I
think it is being passed as 'Commercial, Residential'
Please help.
Thanks
SajjadAre you putting in the single quotes in your selection? You shouldn't.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<sajjad.mussani@.gmail.com> wrote in message
news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
> Hi,
> I am using SQL Server 2005 Reporting Services. I am trying to pass a
> multi-select string value to my query but having difficulties with
> single quotes. My query is:
> SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> (@.CustomerType)
> @.CustomerType is a multi-select parameter in my report. The value of
> this parameter should be passed as 'Commercial','Residential' BUT I
> think it is being passed as 'Commercial, Residential'
> Please help.
> Thanks
> Sajjad
>|||I am not putting single quote in my selection. My selection is done
using a Multi-select drop down box. What I was trying to say is that,
I think the parameter should be passed with single quotes around each
selection, but it is not.
Sajjad
Bruce L-C [MVP] wrote:
> Are you putting in the single quotes in your selection? You shouldn't.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <sajjad.mussani@.gmail.com> wrote in message
> news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
> > Hi,
> >
> > I am using SQL Server 2005 Reporting Services. I am trying to pass a
> > multi-select string value to my query but having difficulties with
> > single quotes. My query is:
> >
> > SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> > (@.CustomerType)
> >
> > @.CustomerType is a multi-select parameter in my report. The value of
> > this parameter should be passed as 'Commercial','Residential' BUT I
> > think it is being passed as 'Commercial, Residential'
> >
> > Please help.
> > Thanks
> > Sajjad
> >|||I don't think that is the issue. I do this all the time. Is your query in a
stored procedure?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sajjad" <sajjad.mussani@.gmail.com> wrote in message
news:1158158039.291968.300150@.m73g2000cwd.googlegroups.com...
>I am not putting single quote in my selection. My selection is done
> using a Multi-select drop down box. What I was trying to say is that,
> I think the parameter should be passed with single quotes around each
> selection, but it is not.
> Sajjad
> Bruce L-C [MVP] wrote:
>> Are you putting in the single quotes in your selection? You shouldn't.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <sajjad.mussani@.gmail.com> wrote in message
>> news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
>> > Hi,
>> >
>> > I am using SQL Server 2005 Reporting Services. I am trying to pass a
>> > multi-select string value to my query but having difficulties with
>> > single quotes. My query is:
>> >
>> > SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
>> > (@.CustomerType)
>> >
>> > @.CustomerType is a multi-select parameter in my report. The value of
>> > this parameter should be passed as 'Commercial','Residential' BUT I
>> > think it is being passed as 'Commercial, Residential'
>> >
>> > Please help.
>> > Thanks
>> > Sajjad
>> >
>|||> SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> (@.CustomerType)
From SQL perspective, this won't work. You cannot pass such parameter. You
must either use dynamic sql ('... cust in ''' + @.cust ...) or parse this
multiparameter into table variable and use this table in subsequent query.
Radim Hampel

Multiples in a select statement

Is it possible to write a single select statement that would pull the following from a table named tblPayments.

I need the 'first payment date and amount', 'last payment date and amount', 'largest payment date and amount', and 'total payments' for each client by a date range.

tblPayments
ClientID
PaymentDate
PaymentAmount

Thanks for any suggestions.

create table p (ClientID int, PaymentDate datetime, PaymentAmount numeric(10,2))
insert into p
select 0,'1/1/2004',100 union all
select 0,'1/2/2004',80 union all
select 0,'1/3/2004',110 union all
select 1,'1/5/2004',87 union all
select 1,'1/12/2004',180 union all
select 1,'1/13/2004',10
select
d.clientID,
d.firstPmtDate,
p1.PaymentAmount,
d.lastPmtDate,
p2.PaymentAmount,
p3.PaymentDate maxPmtDate,
d.maxPmt,
d.ttlPmt
from
(
select
clientID,
min(paymentdate) firstPmtDate,
max(paymentDate) lastPmtDate,
max(PaymentAmount) maxPmt,
sum(PaymentAmount) ttlPmt
from p
--where PaymentDate between @.startDate and @.endDate
group by clientid
) d
join p p1 on p1.clientid = d.clientid and p1.paymentdate = d.firstPmtDate
join p p2 on p2.clientid = d.clientid and p2.paymentdate = d.lastPmtDate
join p p3 on p3.clientid = d.clientid and p3.PaymentAmount = d.maxPmt
--where p3.PaymentDate between @.startDate and @.endDate

|||Thank you, but this does not appear to return the amounts associated with each last and first payment dates. That is where I run into problems. I could be reading it bad?

Thanks again,|||Did you run the query in QA with the sample data?|||Ok, It worked in QA. I'm having some trouble understanding how you did this. How can I learn to understand this type of process?

Thanks for any suggestions,|||I'm having trouble converting this to my data. Is there anything specific I should know to do this?

Thanks again,|||What are you having trouble with specifically?|||Well, I get no errors but there is no data being returned.

Thanks,|||To demonstrate the query, I created a table called p, which represents your table tblPayments, it is an example. Then the table p is aliased as p1, p2,p3 where the joins are occuring to simplify the columns assignments. The alias d is used to declare the derived table which perform the aggregate functions to obtain max,min,etc...

If you want to use this query for you purposes you can change the table assignment of p in the from and join clauses to tblPayments.

Does that help to clarify?|||Yes, and that is what I did and I know there is data but none is being returned. This very puzzling.

Thanks again,|||I had not changed the table name in the joins and had listed my table AS p in the From clause. Now I get data, but I get many records for each client. I was hoping to end up with one row for each client. Any thoughts on this?

Thanks again,|||Can you post the revised query you are using..|||Here it is. Thanks,

select

d.Client_ID,

d.firstPmtDate,

p1.AmountPaid,

d.lastPmtDate,

p2.AmountPaid,

p3.PaymentDate maxPmtDate,

d.maxPmt,

d.ttlPmt

from

(

select

Client_ID,

min(paymentdate) firstPmtDate,

max(paymentDate) lastPmtDate,

max(AmountPaid) maxPmt,

sum(AmountPaid) ttlPmt

from tblPayments

--where PaymentDate between @.startDate and @.endDate

group by Client_ID

) d

join tblPayments p1 on p1.Client_ID = d.Client_ID and p1.paymentdate = d.firstPmtDate

join tblPayments p2 on p2.Client_ID = d.Client_ID and p2.paymentdate = d.lastPmtDate

join tblPayments p3 on p3.Client_ID = d.Client_ID and p3.AmountPaid = d.maxPmt

--where p3.PaymentDate between @.startDate and @.endDate|||It is somewhat difficult to fully understand the data behavior w/o seeing the data and variations that occur. If the rows are displaying duplicate data for each client you can modify the main select to select distinct which will limit dups from the results set.

Hope that helps.sql

Multiple view from a single select statement

Hi,
I want to write an SQL query which should return me 2 kinds of outputs depending on which condition is true. i.e.

the query should be something like

select someview if (condition1 = true)
else select someotherview if (condition2 = true)

where someview is a set of columns from one table only, and someotherview is a set of columns which is a superset of someview (& is generated from two tables, which have no common field)

Let me explain this further --
I want to select data from one table and a single field from other table, however if I the first table does not return any data, I still want data from the other table to be returned.
Can I write a SQL query to do this?

-- Amitdo a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?|||Originally posted by edwinjames
do a union query.

select * from table1
union
select 'ed' from table2;

if table 1 is return not rows table2 will still return data.

Does this answer your question?

I know, that I can use a union, however I want to support TimesTen & Oracle using thsame query...TT does not allow union while oracle does, can I write another query which emulates union?|||I am sorry I am not farmiliar with TT.

Does it support outer joins?|||Originally posted by edwinjames
I am sorry I am not farmiliar with TT.

Does it support outer joins?

Yes it dows, hence I am now using outer joins|||Glad to be of help

:)

multiple view create

Hi
How can I create multiple views in a single SQL script ?
(I get an error: 'CREATE VIEW' must be the first statement in a query
batch.)
thanksYou can use the batch terminator, GO, to seperate the batches:
CREATE VIEW xyz
AS
SELECT
..
GO
CREATE VIEW abc
AS
SELECT
..
GO
... etc
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"romy" <romy1000@.hotpop.com> wrote in message
news:e6ml7BM5FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I create multiple views in a single SQL script ?
> (I get an error: 'CREATE VIEW' must be the first statement in a query
> batch.)
>
> thanks
>sql

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
Leo
SELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>
|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>sql

multiple variables from query

Hi,
I want to assign multiple values from a query to local variables.
I can do it with a single one like:
SET @.Myvar = (SELECT FirstNameFROM People)
But if I want to select more then one field, I don't know how to do this.
SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't work.
I could do two queries, or write a cursor, but that sounds inefficient to
me,
Does anyone have a solution?
Thanks,
LeoSELECT @.myVar = FirstName, @.MyVar2 = LastName FROM People
--
Jacco Schalkwijk
SQL Server MVP
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Leo
Declare @.var1 INT, @.var2 INT
SELECT @.var1=col1,@.var2=col2 FROM Table
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Hi,
You can write the query like the below,
Select @.Myvar = FirstName, @.Myvar2 = LastName FROM People
Thanks
Hari
MCDBA
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>|||Thank you for all the replies.
It works now!
Leo
"Leo Muller" <leo-m@.keshet-i.com> wrote in message
news:c6039l$t2k$1@.news2.netvision.net.il...
> Hi,
> I want to assign multiple values from a query to local variables.
> I can do it with a single one like:
> SET @.Myvar = (SELECT FirstNameFROM People)
> But if I want to select more then one field, I don't know how to do this.
> SET @.Myvar, @.Myvar2 = (SELECT FirstName, LastName FROM People) doesn't
work.
> I could do two queries, or write a cursor, but that sounds inefficient to
> me,
> Does anyone have a solution?
> Thanks,
> Leo
>

Multiple Values to single parameter

Hi,
I have a situation where in I got to pass more than one value to a single parameter like I want to see all the employees of department 5,6 & 7 & so on, my query goes like this "Select firstname,lastname, title from employees where departmentid=@.departmentid". In this variable @.deparmentid I need to pass more than one value. is it possible? if it can, dude's pull it fast to me.

Hi adonis

It is possible I use SQL to check for a comma seperated list. Create this function and pass it a comma seperated list, it will return a table with the values. Change your SQL to something like this:
"Select firstname,lastname, title from employees where departmentid IN ( SELECT [ENTRY] FROM ListToTable(@.departmentid))". It should return the desired result. However you should add maybe an IF to check if the user want all employees and not just a few

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ListToTable') DROP FUNCTION ListToTable GO

CREATE FUNCTION ListToTable
(
/*
** Usage: select entry from listtotable('abc,def,ghi') order by entry desc
*/
@.mylist varchar(8000) )
RETURNS @.ListTable TABLE
(
seqid int not null,
entry varchar(255) not null
)

AS

BEGIN
DECLARE @.this varchar(255),
@.rest varchar(8000),
@.pos int,
@.seqid int

SET @.this = ' ' SET @.seqid = 1 SET @.rest = @.mylist SET @.pos = PATINDEX('%,%', @.rest) WHILE (@.pos > 0) BEGIN set @.this=substring(@.rest,1,@.pos-1) set @.rest=substring(@.rest,@.pos+1,len(@.rest)-@.pos) INSERT INTO @.ListTable (seqid,entry) VALUES (@.seqid,@.this) SET @.pos= PATINDEX('%,%', @.rest) SET @.seqid=@.seqid+1 END set @.this=@.rest INSERT INTO @.ListTable (seqid,entry) VALUES (@.seqid,@.this)
RETURN
END
, Hope this helps
l0n3i200n

|||

I really appriciate your effort. but dont you think it all a work around we are fiddling the query here, Insted I was looking for any option in the reporting services thru wich we can send multiple values. Your solution is feasible when you have a small query but you Iam playing with arount more that 25 tables the queries are so complex that if I fiddle those than Its gona suck me.

Any ways thanks and if U have any thing coming up please update me.

Multiple values into single cell

Taking the Northwind database as an example,
I have an order table: 'Orders'
a order details table: 'Order Details'
and a products table: 'Products'

For reasons best ignored, I want to produce a query which has columns:

Orders.OrderID, Products

which has results like:

10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella di
Giovanni'
10249, 'Tofu, Manjimup Dried Apples'

________
so for those who don't really know what I'm on about and haven't got
access to northwind, I want the second cell to contain the returned
column of a second query (but in text format)

could anyone suggest a way this could be done? am I entering the land
of cursors?

Many thanks,
NeilNAJH (neilonusenet@.yahoo.co.uk) writes:

Quote:

Originally Posted by

For reasons best ignored, I want to produce a query which has columns:
>
Orders.OrderID, Products
>
>
which has results like:
>
10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella di
Giovanni'
10249, 'Tofu, Manjimup Dried Apples'
>
________
so for those who don't really know what I'm on about and haven't got
access to northwind, I want the second cell to contain the returned
column of a second query (but in text format)
>
could anyone suggest a way this could be done? am I entering the land
of cursors?

If you are on SQL 2000, yes. SQL Server MVP Anith Sen covers the various
methods on http://www.projectdmx.com/tsql/rowconcatenate.aspx.

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

Quote:

Originally Posted by

Quote:

Originally Posted by

>For reasons best ignored, ... <<

Do not ignore the reasons; so, why do you want to write stinking bad
code?|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>For reasons best ignored, ... <<

>
Do not ignore the reasons; so, why do you want to write stinking bad
code?

At least he isn't talking about storing the data that way, merely
outputting it that way. That said, I would do it in a separate
reporting layer (e.g. Crystal Reports) if at all possible.|||On May 21, 3:27 am, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>For reasons best ignored, ... <<

>

Quote:

Originally Posted by

Do not ignore the reasons; so, why do you want to write stinking bad
code?

>
At least he isn't talking about storing the data that way, merely
outputting it that way. That said, I would do it in a separate
reporting layer (e.g. Crystal Reports) if at all possible.

I know it's bad practice to do it that way and a seperate reporting
layer like crystal reports etc would be lovely, but I'm in a situation
where I don't have those kind of facilities and budgets at my
disposal. It's just a means to an end. I'm certainly not holding data
like that. It's bad enough dealing with internal politics without
having to deal with it again on here! Thanks to you all for your kind
(or not so kind) input.

Neil

Multiple Values for single row

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..

now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...

so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int --> this is unique for user..i enabled automatic increment..and this is primary key..

so now every user must have a list of other userid's.. as contact list..

Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
THANK YOU !

if it helps..iam using sql server express edition..and iam accessing using asp.net/C#Hi,

Create a contact list table which includes columns,

UserId,
ContactId

Eralper

multiple values for single column in where clause

how does one specify multiple values for a single column in a where clause?

example:

SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')

if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id in ('1389', '0736')

See BOL for more.|||yup, thanks dudesql

multiple updates to table

Hello all,

I have a parallel process updating a single table from various sources using update table statements using a key column.

I'm afraid the process will fail when an update will occur to a record with the same key simultaneously.

Does anyone have a suggestion how to accomplish this? Is there a way timing the updates in queue?

Thanks.

There is no concurrent update in SQL Server, DML statement are queued and executed one by one (if they are not in a transaction). There are sure scenarios which would lead to concurrency conflicts, but this has to be handled by your frontedn application.

HTH, Jens Suessmeyer.'

http://www.sqlserver2005.de

|||

I assume you have multiple clients pumping data into a single table, right?

The next question is what you mean by key? Like an identity key? If so, that won't happen. SQL Server single threads identity key generation so that no two rows will get the same key.

Multiple Unique Keys

Hello everyone. I attempting to construct a table where a single row can be
individually identified by a unique value that is composed of more than one
column. How can define multiple unique keys for a table? Thanks to everyone
for being there to help.
Simply include all the appropriate columns in the PK definition:
CREATE TABLE [dbo].[Demo] (
[a] [int] NOT NULL ,
[b] [int] NOT NULL ,
[c] [int] NOT NULL ,
CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED
(
[a],
[b],
[c]
)
)
Roy Harvey
Beacon Falls, CT
On Wed, 29 Nov 2006 07:46:01 -0800, coenzyme
<coenzyme@.discussions.microsoft.com> wrote:

>Hello everyone. I attempting to construct a table where a single row can be
>individually identified by a unique value that is composed of more than one
>column. How can define multiple unique keys for a table? Thanks to everyone
>for being there to help.
|||Thanks for responding Roy.
"Roy Harvey" wrote:

> Simply include all the appropriate columns in the PK definition:
> CREATE TABLE [dbo].[Demo] (
> [a] [int] NOT NULL ,
> [b] [int] NOT NULL ,
> [c] [int] NOT NULL ,
> CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED
> (
> [a],
> [b],
> [c]
> )
> )
> Roy Harvey
> Beacon Falls, CT
> On Wed, 29 Nov 2006 07:46:01 -0800, coenzyme
> <coenzyme@.discussions.microsoft.com> wrote:
>

Multiple Unique Keys

Hello everyone. I attempting to construct a table where a single row can be
individually identified by a unique value that is composed of more than one
column. How can define multiple unique keys for a table? Thanks to everyone
for being there to help.Simply include all the appropriate columns in the PK definition:
CREATE TABLE [dbo].[Demo] (
[a] [int] NOT NULL ,
[b] [int] NOT NULL ,
[c] [int] NOT NULL ,
CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED
(
[a],
[b],
[c]
)
)
Roy Harvey
Beacon Falls, CT
On Wed, 29 Nov 2006 07:46:01 -0800, coenzyme
<coenzyme@.discussions.microsoft.com> wrote:

>Hello everyone. I attempting to construct a table where a single row can be
>individually identified by a unique value that is composed of more than one
>column. How can define multiple unique keys for a table? Thanks to everyone
>for being there to help.|||Thanks for responding Roy.
"Roy Harvey" wrote:

> Simply include all the appropriate columns in the PK definition:
> CREATE TABLE [dbo].[Demo] (
> [a] [int] NOT NULL ,
> [b] [int] NOT NULL ,
> [c] [int] NOT NULL ,
> CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED
> (
> [a],
> [b],
> [c]
> )
> )
> Roy Harvey
> Beacon Falls, CT
> On Wed, 29 Nov 2006 07:46:01 -0800, coenzyme
> <coenzyme@.discussions.microsoft.com> wrote:
>
>