Multi-Value parameter syntax error

I am new to reporting services...
I've created a report that uses a multi-value parameter based ona dataset
usign a simple select statement. It works fine when a single value is
selected, but when multiple values are selected returns the error Incorrect
Syntax near ','.
How can the report code be modified to pass multiple values with the correct
syntax?See my response to your other posting (which is basically, show us what you
did). My guess is you have an incorrect SQL statement OR you are not going
against SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:7342117C-9715-477F-A9F4-E748C2EA3A77@.microsoft.com...
>I am new to reporting services...
> I've created a report that uses a multi-value parameter based ona dataset
> usign a simple select statement. It works fine when a single value is
> selected, but when multiple values are selected returns the error
> Incorrect
> Syntax near ','.
> How can the report code be modified to pass multiple values with the
> correct
> syntax?
>

Multi-value parameter returns syntax error

I've created a report that includes a multi-value parameter, however when
more than one value is seelcted from the list the report produces an error
that references "Incorrect Syntax near ",".
How do I correct this?Show how you are using the parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:A4B88054-4FAE-4D62-BEF0-8B282C787E9E@.microsoft.com...
> I've created a report that includes a multi-value parameter, however when
> more than one value is seelcted from the list the report produces an error
> that references "Incorrect Syntax near ",".
> How do I correct this?
>|||Send the Query where you are using this parametre.For Multivalue you
should use "in" keyword instead of "="
hope this might be your problem
Bruce L-C [MVP] wrote:
> Show how you are using the parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "StMaas" <StMaas@.discussions.microsoft.com> wrote in message
> news:A4B88054-4FAE-4D62-BEF0-8B282C787E9E@.microsoft.com...
> > I've created a report that includes a multi-value parameter, however when
> > more than one value is seelcted from the list the report produces an error
> > that references "Incorrect Syntax near ",".
> > How do I correct this?
> >sql

Multi-Value Parameter

Hi
I have created a very simple report in SSRS, which counts the number of
Supplier transactions.
I have added a Multi-Value parameter with two values, (I or C) to filter
either Invoices or Credits or both.
If I query Invoice only, I get the correct result.
If I query Credit only, I get the correct result.
If I select all, the result is the same as Invoice. i.e. it is not adding
Invoice and Credit together.
Can anyone please help?
Thanks
ShodmanSo how you have given in your query to "select all" ?
Amarnath
"Shodman" wrote:
> Hi
> I have created a very simple report in SSRS, which counts the number of
> Supplier transactions.
> I have added a Multi-Value parameter with two values, (I or C) to filter
> either Invoices or Credits or both.
> If I query Invoice only, I get the correct result.
> If I query Credit only, I get the correct result.
> If I select all, the result is the same as Invoice. i.e. it is not adding
> Invoice and Credit together.
> Can anyone please help?
> Thanks
> Shodman|||Please note that I am not using any syntax or trying to write code - I am not
that clever!!
From the layout tab, I am adding a parameter through Report > Report
Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
When I Preview the report, the parameter shows "select all" as on option.
Thanks for your time
Shodman
"Amarnath" wrote:
> So how you have given in your query to "select all" ?
> Amarnath
> "Shodman" wrote:
> > Hi
> >
> > I have created a very simple report in SSRS, which counts the number of
> > Supplier transactions.
> >
> > I have added a Multi-Value parameter with two values, (I or C) to filter
> > either Invoices or Credits or both.
> >
> > If I query Invoice only, I get the correct result.
> > If I query Credit only, I get the correct result.
> >
> > If I select all, the result is the same as Invoice. i.e. it is not adding
> > Invoice and Credit together.
> >
> > Can anyone please help?
> >
> > Thanks
> > Shodman|||Hey I am asking whether you have given any if condition in your data tab,
otherwise how you intend to display and select the correct query to display
your results. In otherwords you need to tell RS that when you select "Select
All" what should be done to display the correct results.
Amarnath
"Shodman" wrote:
> Please note that I am not using any syntax or trying to write code - I am not
> that clever!!
> From the layout tab, I am adding a parameter through Report > Report
> Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> When I Preview the report, the parameter shows "select all" as on option.
> Thanks for your time
> Shodman
>
> "Amarnath" wrote:
> > So how you have given in your query to "select all" ?
> >
> > Amarnath
> >
> > "Shodman" wrote:
> >
> > > Hi
> > >
> > > I have created a very simple report in SSRS, which counts the number of
> > > Supplier transactions.
> > >
> > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > either Invoices or Credits or both.
> > >
> > > If I query Invoice only, I get the correct result.
> > > If I query Credit only, I get the correct result.
> > >
> > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > Invoice and Credit together.
> > >
> > > Can anyone please help?
> > >
> > > Thanks
> > > Shodman|||I believe that Amarnath is saying that you have to provide some code in your
query to tell the database what to do when the "Select All" option is
selected.
So for example:
Select c.customer_name, p.product_type
From customer c, product p
Where c.customer_id = p.customer_id
and (p.product_type IN(@.param_product_type))
So in your report parameters you check the Multi-Value check box for the
"param_product_type" parameter. So in the above code, if you select 1 type,
the SQL query will return the correct type and if you select ALL, then the
query will return ALL product types.
Hope this helps.
Rob Cuscaden
"Amarnath" wrote:
> Hey I am asking whether you have given any if condition in your data tab,
> otherwise how you intend to display and select the correct query to display
> your results. In otherwords you need to tell RS that when you select "Select
> All" what should be done to display the correct results.
> Amarnath
> "Shodman" wrote:
> > Please note that I am not using any syntax or trying to write code - I am not
> > that clever!!
> >
> > From the layout tab, I am adding a parameter through Report > Report
> > Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> >
> > When I Preview the report, the parameter shows "select all" as on option.
> >
> > Thanks for your time
> > Shodman
> >
> >
> > "Amarnath" wrote:
> >
> > > So how you have given in your query to "select all" ?
> > >
> > > Amarnath
> > >
> > > "Shodman" wrote:
> > >
> > > > Hi
> > > >
> > > > I have created a very simple report in SSRS, which counts the number of
> > > > Supplier transactions.
> > > >
> > > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > > either Invoices or Credits or both.
> > > >
> > > > If I query Invoice only, I get the correct result.
> > > > If I query Credit only, I get the correct result.
> > > >
> > > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > > Invoice and Credit together.
> > > >
> > > > Can anyone please help?
> > > >
> > > > Thanks
> > > > Shodman|||You are right, I am asking exactly the same.
Amarnath
"Rob" wrote:
> I believe that Amarnath is saying that you have to provide some code in your
> query to tell the database what to do when the "Select All" option is
> selected.
> So for example:
> Select c.customer_name, p.product_type
> From customer c, product p
> Where c.customer_id = p.customer_id
> and (p.product_type IN(@.param_product_type))
> So in your report parameters you check the Multi-Value check box for the
> "param_product_type" parameter. So in the above code, if you select 1 type,
> the SQL query will return the correct type and if you select ALL, then the
> query will return ALL product types.
> Hope this helps.
> Rob Cuscaden
>
> "Amarnath" wrote:
> > Hey I am asking whether you have given any if condition in your data tab,
> > otherwise how you intend to display and select the correct query to display
> > your results. In otherwords you need to tell RS that when you select "Select
> > All" what should be done to display the correct results.
> >
> > Amarnath
> >
> > "Shodman" wrote:
> >
> > > Please note that I am not using any syntax or trying to write code - I am not
> > > that clever!!
> > >
> > > From the layout tab, I am adding a parameter through Report > Report
> > > Parameters, and selecting Multi-Value. Values = I and C, (non-queried).
> > >
> > > When I Preview the report, the parameter shows "select all" as on option.
> > >
> > > Thanks for your time
> > > Shodman
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > So how you have given in your query to "select all" ?
> > > >
> > > > Amarnath
> > > >
> > > > "Shodman" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > I have created a very simple report in SSRS, which counts the number of
> > > > > Supplier transactions.
> > > > >
> > > > > I have added a Multi-Value parameter with two values, (I or C) to filter
> > > > > either Invoices or Credits or both.
> > > > >
> > > > > If I query Invoice only, I get the correct result.
> > > > > If I query Credit only, I get the correct result.
> > > > >
> > > > > If I select all, the result is the same as Invoice. i.e. it is not adding
> > > > > Invoice and Credit together.
> > > > >
> > > > > Can anyone please help?
> > > > >
> > > > > Thanks
> > > > > Shodman

multithreading = sqlserver deadlocks

I created a .net program which uses multithreading. The threads
concurrently access the same db resources. This has generated no end
of sql server deadlocks, which our dba is struggling to resolve.
I am very close to giving up and going ahead and serializing all
database access for the application. This will reduce performance
quite a bit, although it will eliminate all the deadlocks.
Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
mine for building an app that hits the db so hard, but we are unable
to solve all the deadlocking issues using the errorlog and profiling
combo that has been suggested. We solve one deadlock issue, only to
see another one revealed later, perhaps on different resources,
perhaps not.
I have heard there are many applications that use the Singleton
pattern to serialize db access accross the board, just to avoid
situations like this.
Any feedback welcome.Deadlocks are usually caused by 2 or more users attempting to update the
same set of tables in opposite orders. If you have one sp that updates
Orders and then Details while another updates details first and then orders
this will eventually cause a dead lock. If they are both in the same order
you most likely get only blocking which is normal and can be controlled. Do
you have an example of 2 procedures that deadlock on each other and the
associated DDL for those tables? Deadlocking is almost always a result of a
poor schema or more likely poor data access methods. Serialization is not
the way to go, you should do it right and find the cause so you can fix it.
Maybe these will help:
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
--
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402170756.4a53601f@.posting.google.com...
> I created a .net program which uses multithreading. The threads
> concurrently access the same db resources. This has generated no end
> of sql server deadlocks, which our dba is struggling to resolve.
> I am very close to giving up and going ahead and serializing all
> database access for the application. This will reduce performance
> quite a bit, although it will eliminate all the deadlocks.
> Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> mine for building an app that hits the db so hard, but we are unable
> to solve all the deadlocking issues using the errorlog and profiling
> combo that has been suggested. We solve one deadlock issue, only to
> see another one revealed later, perhaps on different resources,
> perhaps not.
> I have heard there are many applications that use the Singleton
> pattern to serialize db access accross the board, just to avoid
> situations like this.
> Any feedback welcome.|||Can't deadlocking also happen for locks other than tables? It can happen
for RID or KEY locks as well within the same table, can't it?
Christian Smith
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Deadlocks are usually caused by 2 or more users attempting to update the
> same set of tables in opposite orders. If you have one sp that updates
> Orders and then Details while another updates details first and then
orders
> this will eventually cause a dead lock. If they are both in the same
order
> you most likely get only blocking which is normal and can be controlled.
Do
> you have an example of 2 procedures that deadlock on each other and the
> associated DDL for those tables? Deadlocking is almost always a result of
a
> poor schema or more likely poor data access methods. Serialization is not
> the way to go, you should do it right and find the cause so you can fix
it.
> Maybe these will help:
>
> http://www.support.microsoft.com/?id=224453 Blocking Problems
> http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> Blocking
>
>
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > I created a .net program which uses multithreading. The threads
> > concurrently access the same db resources. This has generated no end
> > of sql server deadlocks, which our dba is struggling to resolve.
> >
> > I am very close to giving up and going ahead and serializing all
> > database access for the application. This will reduce performance
> > quite a bit, although it will eliminate all the deadlocks.
> >
> > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > mine for building an app that hits the db so hard, but we are unable
> > to solve all the deadlocking issues using the errorlog and profiling
> > combo that has been suggested. We solve one deadlock issue, only to
> > see another one revealed later, perhaps on different resources,
> > perhaps not.
> >
> > I have heard there are many applications that use the Singleton
> > pattern to serialize db access accross the board, just to avoid
> > situations like this.
> >
> > Any feedback welcome.
>|||Yes there are other reasons why dead locks occur but the main reason is as I
posted. Without more details it's all a guess so the most logical or common
reason is what I posted.
--
Andrew J. Kelly
SQL Server MVP
"Christian Smith" <csmith@.digex.com> wrote in message
news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Can't deadlocking also happen for locks other than tables? It can happen
> for RID or KEY locks as well within the same table, can't it?
> Christian Smith
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > Deadlocks are usually caused by 2 or more users attempting to update the
> > same set of tables in opposite orders. If you have one sp that updates
> > Orders and then Details while another updates details first and then
> orders
> > this will eventually cause a dead lock. If they are both in the same
> order
> > you most likely get only blocking which is normal and can be controlled.
> Do
> > you have an example of 2 procedures that deadlock on each other and the
> > associated DDL for those tables? Deadlocking is almost always a result
of
> a
> > poor schema or more likely poor data access methods. Serialization is
not
> > the way to go, you should do it right and find the cause so you can fix
> it.
> > Maybe these will help:
> >
> >
> > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > Blocking
> >
> >
> >
>
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > I created a .net program which uses multithreading. The threads
> > > concurrently access the same db resources. This has generated no end
> > > of sql server deadlocks, which our dba is struggling to resolve.
> > >
> > > I am very close to giving up and going ahead and serializing all
> > > database access for the application. This will reduce performance
> > > quite a bit, although it will eliminate all the deadlocks.
> > >
> > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > mine for building an app that hits the db so hard, but we are unable
> > > to solve all the deadlocking issues using the errorlog and profiling
> > > combo that has been suggested. We solve one deadlock issue, only to
> > > see another one revealed later, perhaps on different resources,
> > > perhaps not.
> > >
> > > I have heard there are many applications that use the Singleton
> > > pattern to serialize db access accross the board, just to avoid
> > > situations like this.
> > >
> > > Any feedback welcome.
> >
> >
>|||One deadlocking situation is fairly simple:
node 1: Select from some_table where rownum = 42
node 2: update some_table with (TABLOCKX) where rownum = 42
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as I
> posted. Without more details it's all a guess so the most logical or common
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Can't deadlocking also happen for locks other than tables? It can happen
> > for RID or KEY locks as well within the same table, can't it?
> >
> > Christian Smith
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > Deadlocks are usually caused by 2 or more users attempting to update the
> > > same set of tables in opposite orders. If you have one sp that updates
> > > Orders and then Details while another updates details first and then
> orders
> > > this will eventually cause a dead lock. If they are both in the same
> order
> > > you most likely get only blocking which is normal and can be controlled.
> Do
> > > you have an example of 2 procedures that deadlock on each other and the
> > > associated DDL for those tables? Deadlocking is almost always a result
> of
> a
> > > poor schema or more likely poor data access methods. Serialization is
> not
> > > the way to go, you should do it right and find the cause so you can fix
> it.
> > > Maybe these will help:
> > >
> > >
> > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > > Blocking
> > >
> > >
> > >
> >
> http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > I created a .net program which uses multithreading. The threads
> > > > concurrently access the same db resources. This has generated no end
> > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > >
> > > > I am very close to giving up and going ahead and serializing all
> > > > database access for the application. This will reduce performance
> > > > quite a bit, although it will eliminate all the deadlocks.
> > > >
> > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > > mine for building an app that hits the db so hard, but we are unable
> > > > to solve all the deadlocking issues using the errorlog and profiling
> > > > combo that has been suggested. We solve one deadlock issue, only to
> > > > see another one revealed later, perhaps on different resources,
> > > > perhaps not.
> > > >
> > > > I have heard there are many applications that use the Singleton
> > > > pattern to serialize db access accross the board, just to avoid
> > > > situations like this.
> > > >
> > > > Any feedback welcome.
> > >
> > >
> >
> >|||Here is the deadlock graph of the previous post:
Wait-for graph
2004-02-18 12:06:37.50 spid4
2004-02-18 12:06:37.50 spid4 Node:1
2004-02-18 12:06:37.51 spid4 KEY: 26:1124251110:2 (130092d8179c)
CleanCnt:1 Mode: U Flags: 0x0
2004-02-18 12:06:37.51 spid4 Grant List 0::
2004-02-18 12:06:37.51 spid4 Owner:0x4fce9ac0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:73 ECID:0
2004-02-18 12:06:37.51 spid4 SPID: 73 ECID: 0 Statement Type:
SELECT Line #: 46
2004-02-18 12:06:37.51 spid4 Input Buf: RPC Event:
S_BPI_BY_BPIID_XML;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:57 ECID:0 Ec:(0x53915568) Value:0x558fa140 Cost:(0/3C)
2004-02-18 12:06:37.53 spid4
2004-02-18 12:06:37.53 spid4 Node:2
2004-02-18 12:06:37.53 spid4 KEY: 26:1124251110:1 (4a004cee256c)
CleanCnt:1 Mode: X Flags: 0x0
2004-02-18 12:06:37.53 spid4 Grant List 0::
2004-02-18 12:06:37.53 spid4 Owner:0x4edb17a0 Mode: X
Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0
2004-02-18 12:06:37.53 spid4 SPID: 57 ECID: 0 Statement Type:
UPDATE Line #: 347
2004-02-18 12:06:37.53 spid4 Input Buf: RPC Event:
U_FAILED_ELI_CT;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:73 ECID:0 Ec:(0x4872B568) Value:0x48b1e3e0 Cost:(0/0)
2004-02-18 12:06:37.53 spid4 Victim Resource Owner:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:73 ECID:0 Ec:(0x4872B568) Value:0x48b1e3e0 Cost:(0/0)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as I
> posted. Without more details it's all a guess so the most logical or common
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > Can't deadlocking also happen for locks other than tables? It can happen
> > for RID or KEY locks as well within the same table, can't it?
> >
> > Christian Smith
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > Deadlocks are usually caused by 2 or more users attempting to update the
> > > same set of tables in opposite orders. If you have one sp that updates
> > > Orders and then Details while another updates details first and then
> orders
> > > this will eventually cause a dead lock. If they are both in the same
> order
> > > you most likely get only blocking which is normal and can be controlled.
> Do
> > > you have an example of 2 procedures that deadlock on each other and the
> > > associated DDL for those tables? Deadlocking is almost always a result
> of
> a
> > > poor schema or more likely poor data access methods. Serialization is
> not
> > > the way to go, you should do it right and find the cause so you can fix
> it.
> > > Maybe these will help:
> > >
> > >
> > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> > > Blocking
> > >
> > >
> > >
> >
> http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > I created a .net program which uses multithreading. The threads
> > > > concurrently access the same db resources. This has generated no end
> > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > >
> > > > I am very close to giving up and going ahead and serializing all
> > > > database access for the application. This will reduce performance
> > > > quite a bit, although it will eliminate all the deadlocks.
> > > >
> > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> > > > mine for building an app that hits the db so hard, but we are unable
> > > > to solve all the deadlocking issues using the errorlog and profiling
> > > > combo that has been suggested. We solve one deadlock issue, only to
> > > > see another one revealed later, perhaps on different resources,
> > > > perhaps not.
> > > >
> > > > I have heard there are many applications that use the Singleton
> > > > pattern to serialize db access accross the board, just to avoid
> > > > situations like this.
> > > >
> > > > Any feedback welcome.
> > >
> > >
> >
> >|||Not sure I follow what Node1 & 2 are. Are they 2 different users? Do they
both issue a select and an update? Sometimes it can be more confusing to
only give partial details. There must be more to it than what you show.
Are they wrapped in a transaction and what other statements are between the
begin and commit? But the real question is why on earth are you using a
TABLOCKX?
--
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402201155.64b1af2@.posting.google.com...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
> > Yes there are other reasons why dead locks occur but the main reason is
as I
> > posted. Without more details it's all a guess so the most logical or
common
> > reason is what I posted.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Christian Smith" <csmith@.digex.com> wrote in message
> > news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> > > Can't deadlocking also happen for locks other than tables? It can
happen
> > > for RID or KEY locks as well within the same table, can't it?
> > >
> > > Christian Smith
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> > > > Deadlocks are usually caused by 2 or more users attempting to update
the
> > > > same set of tables in opposite orders. If you have one sp that
updates
> > > > Orders and then Details while another updates details first and then
> > orders
> > > > this will eventually cause a dead lock. If they are both in the
same
> > order
> > > > you most likely get only blocking which is normal and can be
controlled.
> > Do
> > > > you have an example of 2 procedures that deadlock on each other and
the
> > > > associated DDL for those tables? Deadlocking is almost always a
result
> > of
> > a
> > > > poor schema or more likely poor data access methods. Serialization
is
> > not
> > > > the way to go, you should do it right and find the cause so you can
fix
> > it.
> > > > Maybe these will help:
> > > >
> > > >
> > > > http://www.support.microsoft.com/?id=224453 Blocking Problems
> > > > http://www.support.microsoft.com/?id=271509 How to monitor SQL
2000
> > > > Blocking
> > > >
> > > >
> > > >
> > >
> >
http://www.amazon.com/exec/obidos/tg/detail/-/B0000W86FY/qid=1077039326//ref=sr_8_xs_ap_i1_xgl14/104-0688407-7232719?v=glance&s=books&n=507846
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> > > > news:1faa8782.0402170756.4a53601f@.posting.google.com...
> > > > > I created a .net program which uses multithreading. The threads
> > > > > concurrently access the same db resources. This has generated no
end
> > > > > of sql server deadlocks, which our dba is struggling to resolve.
> > > > >
> > > > > I am very close to giving up and going ahead and serializing all
> > > > > database access for the application. This will reduce performance
> > > > > quite a bit, although it will eliminate all the deadlocks.
> > > > >
> > > > > Maybe it's our dba's fault, maybe it's sql server's fault, maybe
it's
> > > > > mine for building an app that hits the db so hard, but we are
unable
> > > > > to solve all the deadlocking issues using the errorlog and
profiling
> > > > > combo that has been suggested. We solve one deadlock issue, only
to
> > > > > see another one revealed later, perhaps on different resources,
> > > > > perhaps not.
> > > > >
> > > > > I have heard there are many applications that use the Singleton
> > > > > pattern to serialize db access accross the board, just to avoid
> > > > > situations like this.
> > > > >
> > > > > Any feedback welcome.
> > > >
> > > >
> > >
> > >|||In article <1faa8782.0402201155.64b1af2@.posting.google.com>,
n_o_s_p_a__m@.mail.com said...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
Actually both would error out because they're not valid SQL statements ;)
Seriously though, why would that deadlock? Whichever grabbed the table
first would finish its work and release it for the other.

multithreading = sqlserver deadlocks

I created a .net program which uses multithreading. The threads
concurrently access the same db resources. This has generated no end
of sql server deadlocks, which our dba is struggling to resolve.
I am very close to giving up and going ahead and serializing all
database access for the application. This will reduce performance
quite a bit, although it will eliminate all the deadlocks.
Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
mine for building an app that hits the db so hard, but we are unable
to solve all the deadlocking issues using the errorlog and profiling
combo that has been suggested. We solve one deadlock issue, only to
see another one revealed later, perhaps on different resources,
perhaps not.
I have heard there are many applications that use the Singleton
pattern to serialize db access accross the board, just to avoid
situations like this.
Any feedback welcome.Deadlocks are usually caused by 2 or more users attempting to update the
same set of tables in opposite orders. If you have one sp that updates
Orders and then Details while another updates details first and then orders
this will eventually cause a dead lock. If they are both in the same order
you most likely get only blocking which is normal and can be controlled. Do
you have an example of 2 procedures that deadlock on each other and the
associated DDL for those tables? Deadlocking is almost always a result of a
poor schema or more likely poor data access methods. Serialization is not
the way to go, you should do it right and find the cause so you can fix it.
Maybe these will help:
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
http://www.amazon.com/exec/obidos/t...=books&n=507846
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402170756.4a53601f@.posting.google.com...
> I created a .net program which uses multithreading. The threads
> concurrently access the same db resources. This has generated no end
> of sql server deadlocks, which our dba is struggling to resolve.
> I am very close to giving up and going ahead and serializing all
> database access for the application. This will reduce performance
> quite a bit, although it will eliminate all the deadlocks.
> Maybe it's our dba's fault, maybe it's sql server's fault, maybe it's
> mine for building an app that hits the db so hard, but we are unable
> to solve all the deadlocking issues using the errorlog and profiling
> combo that has been suggested. We solve one deadlock issue, only to
> see another one revealed later, perhaps on different resources,
> perhaps not.
> I have heard there are many applications that use the Singleton
> pattern to serialize db access accross the board, just to avoid
> situations like this.
> Any feedback welcome.|||Can't deadlocking also happen for locks other than tables? It can happen
for RID or KEY locks as well within the same table, can't it?
Christian Smith
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Deadlocks are usually caused by 2 or more users attempting to update the
> same set of tables in opposite orders. If you have one sp that updates
> Orders and then Details while another updates details first and then
orders
> this will eventually cause a dead lock. If they are both in the same
order
> you most likely get only blocking which is normal and can be controlled.
Do
> you have an example of 2 procedures that deadlock on each other and the
> associated DDL for those tables? Deadlocking is almost always a result of
a
> poor schema or more likely poor data access methods. Serialization is not
> the way to go, you should do it right and find the cause so you can fix
it.
> Maybe these will help:
>
> http://www.support.microsoft.com/?id=224453 Blocking Problems
> http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
> Blocking
>
>
http://www.amazon.com/exec/obidos/t...=books&n=507846
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0402170756.4a53601f@.posting.google.com...
>|||Yes there are other reasons why dead locks occur but the main reason is as I
posted. Without more details it's all a guess so the most logical or common
reason is what I posted.
Andrew J. Kelly
SQL Server MVP
"Christian Smith" <csmith@.digex.com> wrote in message
news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> Can't deadlocking also happen for locks other than tables? It can happen
> for RID or KEY locks as well within the same table, can't it?
> Christian Smith
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OkcJl6X9DHA.548@.TK2MSFTNGP11.phx.gbl...
> orders
> order
> Do
of
> a
not
> it.
>
http://www.amazon.com/exec/obidos/t...=books&n=507846
>|||One deadlocking situation is fairly simple:
node 1: Select from some_table where rownum = 42
node 2: update some_table with (TABLOCKX) where rownum = 42
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.
TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as
I
> posted. Without more details it's all a guess so the most logical or comm
on
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> orders
> order
> Do
> of
> a
> not
> it.
> http://www.amazon.com/exec/obidos/t...=books&n=507846|||Here is the deadlock graph of the previous post:
Wait-for graph
2004-02-18 12:06:37.50 spid4
2004-02-18 12:06:37.50 spid4 Node:1
2004-02-18 12:06:37.51 spid4 KEY: 26:1124251110:2 (130092d8179c)
CleanCnt:1 Mode: U Flags: 0x0
2004-02-18 12:06:37.51 spid4 Grant List 0::
2004-02-18 12:06:37.51 spid4 Owner:0x4fce9ac0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:73 ECID:0
2004-02-18 12:06:37.51 spid4 SPID: 73 ECID: 0 Statement Type:
SELECT Line #: 46
2004-02-18 12:06:37.51 spid4 Input Buf: RPC Event:
S_BPI_BY_BPIID_XML;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:57 ECID:0 Ec0x53915568) Value:0x558fa140 Cost0/3C)
2004-02-18 12:06:37.53 spid4
2004-02-18 12:06:37.53 spid4 Node:2
2004-02-18 12:06:37.53 spid4 KEY: 26:1124251110:1 (4a004cee256c)
CleanCnt:1 Mode: X Flags: 0x0
2004-02-18 12:06:37.53 spid4 Grant List 0::
2004-02-18 12:06:37.53 spid4 Owner:0x4edb17a0 Mode: X
Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0
2004-02-18 12:06:37.53 spid4 SPID: 57 ECID: 0 Statement Type:
UPDATE Line #: 347
2004-02-18 12:06:37.53 spid4 Input Buf: RPC Event:
U_FAILED_ELI_CT;1
2004-02-18 12:06:37.53 spid4 Requested By:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:73 ECID:0 Ec0x4872B568) Value:0x48b1e3e0 Cost0/0)
2004-02-18 12:06:37.53 spid4 Victim Resource Owner:
2004-02-18 12:06:37.53 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:73 ECID:0 Ec0x4872B568) Value:0x48b1e3e0 Cost0/0)
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:<O4PueDZ9DHA.2432@.
TK2MSFTNGP09.phx.gbl>...
> Yes there are other reasons why dead locks occur but the main reason is as
I
> posted. Without more details it's all a guess so the most logical or comm
on
> reason is what I posted.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uxC%23TGY9DHA.3404@.TK2MSFTNGP09.phx.gbl...
> orders
> order
> Do
> of
> a
> not
> it.
> http://www.amazon.com/exec/obidos/t...=books&n=507846|||Not sure I follow what Node1 & 2 are. Are they 2 different users? Do they
both issue a select and an update? Sometimes it can be more confusing to
only give partial details. There must be more to it than what you show.
Are they wrapped in a transaction and what other statements are between the
begin and commit? But the real question is why on earth are you using a
TABLOCKX?
Andrew J. Kelly
SQL Server MVP
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0402201155.64b1af2@.posting.google.com...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:<O4PueDZ9DHA.2432@.TK2MSFTNGP09.phx.gbl>...
as I
common
happen
the
updates
same
controlled.
the
result
is
fix
2000
http://www.amazon.com/exec/obidos/t...=books&n=507846
end
it's
unable
profiling
to|||In article <1faa8782.0402201155.64b1af2@.posting.google.com>,
n_o_s_p_a__m@.mail.com said...
> One deadlocking situation is fairly simple:
> node 1: Select from some_table where rownum = 42
> node 2: update some_table with (TABLOCKX) where rownum = 42
Actually both would error out because they're not valid SQL statements ;)
Seriously though, why would that deadlock? Whichever grabbed the table
first would finish its work and release it for the other.

Multi-Table IDENTITY

Is there a way to associate IDENTITY columns from two different tables, so that new records created in either table will have mutually unique values?

That is, a new record in table A will be given value 1, and then a new record created in table B will be given value 2 (because value 1 was already used by the IDENTITY column in table A). Can this be done?

No, identity fields are only unique within the table.

Multi-select parameter in SQL Server Rptg Svcs 2005

I am using a beta version of reporting services 2005.
I have created my report parameters as multi-select. However, I'm having
trouble having the query parameters accept the list that the report
parameters passes. (below) I tried replacing the = with in and between and
neither work. It doesn't like the comma between the multiple values. I
can't imagine that they've allowed multi-select report parameters without a
way to pass them cleanly to the query parameters.
Any help would be greatly appreciated. Thank you in advance for any help.
Machelle
Select * from candidate_info
where (recruiting_year in @.p_Year) and
(candidate_type_display in @.p_candidate_type) and
(degree_level in @.p_degree) and
(owner_site_code in @.p_site_owner) and
(offer_job_grade in @.p_grade)Try putting parenthese around the parameter like you normally would if you
were providing a hardcoded list ie
Select * from candidate_info
> where (recruiting_year in (@.p_Year)) and
> (candidate_type_display in (@.p_candidate_type)) and
> (degree_level in (@.p_degree)) and
> (owner_site_code in (@.p_site_owner)) and
> (offer_job_grade in (@.p_grade))
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:3EF55E31-2626-4B24-86FD-97E087999FF8@.microsoft.com...
>I am using a beta version of reporting services 2005.
> I have created my report parameters as multi-select. However, I'm having
> trouble having the query parameters accept the list that the report
> parameters passes. (below) I tried replacing the = with in and between and
> neither work. It doesn't like the comma between the multiple values. I
> can't imagine that they've allowed multi-select report parameters without
> a
> way to pass them cleanly to the query parameters.
> Any help would be greatly appreciated. Thank you in advance for any help.
> Machelle
> Select * from candidate_info
> where (recruiting_year in @.p_Year) and
> (candidate_type_display in @.p_candidate_type) and
> (degree_level in @.p_degree) and
> (owner_site_code in @.p_site_owner) and
> (offer_job_grade in @.p_grade)
>|||I love it when it's a simple answer - thanks Wayne I really appreciate your
taking the time to answer my question.
"Wayne Snyder" wrote:
> Try putting parenthese around the parameter like you normally would if you
> were providing a hardcoded list ie
> Select * from candidate_info
> > where (recruiting_year in (@.p_Year)) and
> > (candidate_type_display in (@.p_candidate_type)) and
> > (degree_level in (@.p_degree)) and
> > (owner_site_code in (@.p_site_owner)) and
> > (offer_job_grade in (@.p_grade))
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Machelle" <Machelle@.discussions.microsoft.com> wrote in message
> news:3EF55E31-2626-4B24-86FD-97E087999FF8@.microsoft.com...
> >I am using a beta version of reporting services 2005.
> >
> > I have created my report parameters as multi-select. However, I'm having
> > trouble having the query parameters accept the list that the report
> > parameters passes. (below) I tried replacing the = with in and between and
> > neither work. It doesn't like the comma between the multiple values. I
> > can't imagine that they've allowed multi-select report parameters without
> > a
> > way to pass them cleanly to the query parameters.
> >
> > Any help would be greatly appreciated. Thank you in advance for any help.
> >
> > Machelle
> >
> > Select * from candidate_info
> > where (recruiting_year in @.p_Year) and
> > (candidate_type_display in @.p_candidate_type) and
> > (degree_level in @.p_degree) and
> > (owner_site_code in @.p_site_owner) and
> > (offer_job_grade in @.p_grade)
> >
>
>

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273033&SiteID=1

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multi-Row update trigger

Hi,

I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.

DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product

DeliveryNote has the ReceivedDate
DeliveryNoteDetail has the ReceivedQty

I made the following trigger for handling single row updates, which works fine.

UPDATE Purchasing.Product
SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
FROM Purchasing.DeliveryNote dn INNER JOIN
Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID

Now I don't know how to handle multi-row situations when the same product is updated.
Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).Subqueries, perhaps...
UPDATE Purchasing.Product
SET LastReceivedQty = subquery.ReceivedQty,
LastReceivedDate = subquery.ReceivedDate
from Purchasing.DeliveryNote dn
inner join --Subquery
(SELECT dnd.DeliveryNoteID,
sum(i.ReceivedQty) ReceivedQty,
max(dn.ReceivedDate) RecievedDate
FROM Purchasing.DeliveryNoteDetail dnd
INNER JOIN inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID
INNER JOIN Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
INNER JOIN Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID
group by dnd.DeliveryNoteID) Subquery
on dn.DeliveryNoteID = Subquery.DeliveryNoteIDsql

multiple values inside textbox

Hi,
I have created a function that will accept multiple values for a parameter.
I have added a textbox to the body where I want to display the values of the
parameter but for some reason it only shows the first value.
Why and how can I resolve it?
Thanksim also facing the same prob...
Did u get any solution for this...
Thanks,
"collie" wrote:
> Hi,
> I have created a function that will accept multiple values for a parameter.
> I have added a textbox to the body where I want to display the values of the
> parameter but for some reason it only shows the first value.
> Why and how can I resolve it?
> Thanks|||What kind of function is it? Are you storing comma separated values for that
parameter?
"Chandra" wrote:
> im also facing the same prob...
> Did u get any solution for this...
> Thanks,
>
> "collie" wrote:
> > Hi,
> >
> > I have created a function that will accept multiple values for a parameter.
> > I have added a textbox to the body where I want to display the values of the
> > parameter but for some reason it only shows the first value.
> > Why and how can I resolve it?
> >
> > Thanks|||Thanks for the response. Below is the function I am using.
CREATE FUNCTION dbo.UDF_CharCommaSeparatedListToTable
(
@.CommaSeparatedList VARCHAR(8000)
)
RETURNS @.ParsedTableValue TABLE (TableValue VARCHAR(1000))
AS
BEGIN
DECLARE @.TableValue VARCHAR(1000)
DECLARE @.Pos INT
SET @.CommaSeparatedList = LTRIM(RTRIM(@.CommaSeparatedList))+ ','
SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
IF REPLACE(@.CommaSeparatedList, ',', '') <> ''
BEGIN
WHILE @.Pos > 0
BEGIN
SET @.TableValue = LTRIM(RTRIM(LEFT(@.CommaSeparatedList, @.Pos - 1)))
IF @.TableValue <> ''
BEGIN
INSERT INTO @.ParsedTableValue (TableValue)
VALUES (RTRIM(@.TableValue))
END
SET @.CommaSeparatedList = RIGHT(@.CommaSeparatedList,
LEN(@.CommaSeparatedList) - @.Pos)
SET @.Pos = CHARINDEX(',', @.CommaSeparatedList, 1)
END
END
RETURN
END
In the sp I am using it as follows:
--
where
(@.Paramregion IS NULL OR t.region in (Select stringval from dbo.CSVTABLE
(@.Paramregion)))
"Vipul Shah" wrote:
> What kind of function is it? Are you storing comma separated values for that
> parameter?
> "Chandra" wrote:
> > im also facing the same prob...
> > Did u get any solution for this...
> > Thanks,
> >
> >
> > "collie" wrote:
> >
> > > Hi,
> > >
> > > I have created a function that will accept multiple values for a parameter.
> > > I have added a textbox to the body where I want to display the values of the
> > > parameter but for some reason it only shows the first value.
> > > Why and how can I resolve it?
> > >
> > > Thanks|||Didn't find a solution. Did u?
"Chandra" wrote:
> im also facing the same prob...
> Did u get any solution for this...
> Thanks,
>
> "collie" wrote:
> > Hi,
> >
> > I have created a function that will accept multiple values for a parameter.
> > I have added a textbox to the body where I want to display the values of the
> > parameter but for some reason it only shows the first value.
> > Why and how can I resolve it?
> >
> > Thanks

Multiple users, SourceSafe, and Reporting Services

I am new to the .NET world. I have created a Solution with some Projects in
them for Reporting Services. I have successfually integrated SourceSafe with
my environment and Added the solutions to Sourcesafe.
Now I have a co-worker who needs to begin sharing my solution and Projects.
I have him set up in SourceSafe but can not seem to find a way to get his
environment to "check out" my solution and bind his environment into
SourceSafe like I have mine bound.In the VS IDE, try File>Source Control>Open From Source Control
HTH
"mlapoint" wrote:
> I am new to the .NET world. I have created a Solution with some Projects in
> them for Reporting Services. I have successfually integrated SourceSafe with
> my environment and Added the solutions to Sourcesafe.
> Now I have a co-worker who needs to begin sharing my solution and Projects.
> I have him set up in SourceSafe but can not seem to find a way to get his
> environment to "check out" my solution and bind his environment into
> SourceSafe like I have mine bound.|||Worked like a charm. Thanks!
"Alien2_51" wrote:
> In the VS IDE, try File>Source Control>Open From Source Control
> HTH
> "mlapoint" wrote:
> > I am new to the .NET world. I have created a Solution with some Projects in
> > them for Reporting Services. I have successfually integrated SourceSafe with
> > my environment and Added the solutions to Sourcesafe.
> >
> > Now I have a co-worker who needs to begin sharing my solution and Projects.
> > I have him set up in SourceSafe but can not seem to find a way to get his
> > environment to "check out" my solution and bind his environment into
> > SourceSafe like I have mine bound.

Multiple Updatable Publication on the same table!!

Hi,

I have created a publication for sql server 2005 Mobile Edition subscriber,

I need to add another Publication/merge replication on the same tables,

Scenario:

-Server A and mobile devices (publication for sql server 2005 Mobile Edition subscriber)

-The same Server A send data "1 way direction" to Server B by merge replication publication.

NB: My Tables are updatble in both publications.

Any idea?

Thanks,

Tarek Ghazali

SQL Server MVP

Are you getting an error that's preventing this from happening?

Multiple Updatable Publication on the same table!!

Hi,

I have created a publication for sql server 2005 Mobile Edition subscriber,

I need to add another Publication/merge replication on the same tables,

Scenario:

-Server A and mobile devices (publication for sql server 2005 Mobile Edition subscriber)

-The same Server A send data "1 way direction" to Server B by merge replication publication.

NB: My Tables are updatble in both publications.

Any idea?

Thanks,

Tarek Ghazali

SQL Server MVP

Are you getting an error that's preventing this from happening?

Multiple subreports in a report

I'm trying to generate two reports from one reporting services URL, so
I created a "master report" that simploy contains two sub-reports.
When I run it in the preview browser, one subreport runs, and not the
2nd.
In the RS Manager, both subreports say nothing but "Error: Subreport
could not be shown".
I wonder if it's a datasource problem, or something else?
Thanks,
JackI imagine that it is a datasource issue, if it runs fine in report designer
preview.
I have a report that has 5 subreports and the only problem/change that I had
to make was to make sure that the data source is set properly for all the
subreports. For some reason it looses it occasionally when the reports are
deployed.
"jackfreud" wrote:
> I'm trying to generate two reports from one reporting services URL, so
> I created a "master report" that simploy contains two sub-reports.
> When I run it in the preview browser, one subreport runs, and not the
> 2nd.
> In the RS Manager, both subreports say nothing but "Error: Subreport
> could not be shown".
> I wonder if it's a datasource problem, or something else?
> Thanks,
> Jack
>|||Just had similar issue. Make sure the subreport operates standalone, with the
correct datasource, and proper parameters are passed to the sub report
property on the main report.
"jackfreud" wrote:
> I'm trying to generate two reports from one reporting services URL, so
> I created a "master report" that simploy contains two sub-reports.
> When I run it in the preview browser, one subreport runs, and not the
> 2nd.
> In the RS Manager, both subreports say nothing but "Error: Subreport
> could not be shown".
> I wonder if it's a datasource problem, or something else?
> Thanks,
> Jack
>

Multiple roles?

Hello. I was wondering if someone could help me understand better about
assigning a user to multiple roles. I created a role for some developers
from another group to be able to to DTS over some data (select, insert,
delete, update) into our local tables from their sql server. I also
assigned them to the db_ddladmin role. They tried to DTS our new data and
looked like they were able to delete the data in our local tables but their
bulk insert command to insert the data wasn't successful. I read and
thought that when assigning a user multiple roles the role with the most
priveleges takes in effect?
Thanks in advance.Yes, rights of several roles accumulate for a user, but db_ddladmin does not
grant BULK INSERT rights.
They need to be members of the bulkadmin server role in order to run BULK
INSERT in SS2000. In SS2005 you need ADMINISTER BULK OPERATIONS, plus
insert rights to the table and you may need ALTER TABLE permissions in some
cases.
See the Permissions subheading on the BULK INSERT command in either version
of Books Online.
RLF
"zz12" <IDontLikeSpam@.Nowhere.com> wrote in message
news:uaHlkYSfHHA.444@.TK2MSFTNGP04.phx.gbl...
> Hello. I was wondering if someone could help me understand better about
> assigning a user to multiple roles. I created a role for some developers
> from another group to be able to to DTS over some data (select, insert,
> delete, update) into our local tables from their sql server. I also
> assigned them to the db_ddladmin role. They tried to DTS our new data and
> looked like they were able to delete the data in our local tables but
> their bulk insert command to insert the data wasn't successful. I read
> and thought that when assigning a user multiple roles the role with the
> most priveleges takes in effect?
> Thanks in advance.
>|||zz12 (IDontLikeSpam@.Nowhere.com) writes:
> Hello. I was wondering if someone could help me understand better about
> assigning a user to multiple roles. I created a role for some
> developers from another group to be able to to DTS over some data
> (select, insert, delete, update) into our local tables from their sql
> server. I also assigned them to the db_ddladmin role. They tried to
> DTS our new data and looked like they were able to delete the data in
> our local tables but their bulk insert command to insert the data wasn't
> successful. I read and thought that when assigning a user multiple
> roles the role with the most priveleges takes in effect?
I would guess that rather all privs are unioned together, and DENY
takes precendence over GRANT.
In any case, BULK INSERT requires membership in the bulkadmin server role,
which you don't seemed to have assign to your users.
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|||Will look into it further. Thanks a bunch for both of your replies.
Totally appreciate it.
Take care.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99116B91E56CYazorman@.127.0.0.1...
> zz12 (IDontLikeSpam@.Nowhere.com) writes:
> I would guess that rather all privs are unioned together, and DENY
> takes precendence over GRANT.
> In any case, BULK INSERT requires membership in the bulkadmin server role,
> which you don't seemed to have assign to your users.
>
> --
> 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