I need to load data from a table in sqlserver to a table
in an instance of the same server. The query need to be
run in the SQL server itself not in the instance. So...
insert into [servername\instancename].[dbname].
[dbo].table1 (
col1,
col2)
select
t2.col1,
t2.col2
from table2 as t2
I keep getting this message:
Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
Thanks in advance for you help.This is a multi-part message in MIME format.
--=_NextPart_000_038B_01C3D5F9.27781520
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In Enterprise Mgr, right click on the linked server - CNS-SFO-A46\QA. Bring
up the properties, click on Server Options, then check the Data Access box.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
I need to load data from a table in sqlserver to a table
in an instance of the same server. The query need to be
run in the SQL server itself not in the instance. So...
insert into [servername\instancename].[dbname].
[dbo].table1 (
col1,
col2)
select
t2.col1,
t2.col2
from table2 as t2
I keep getting this message:
Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
Thanks in advance for you help.
--=_NextPart_000_038B_01C3D5F9.27781520
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
In Enterprise Mgr, right click on the =linked server - CNS-SFO-A46\QA. Bring up the properties, =click on Server Options, then check the Data Access box.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Sandra" wrote in message news:00db01c3d620$c2=708f40$a601280a@.phx.gbl...I need to load data from a table in sqlserver to a table in an =instance of the same server. The query need to be run in the SQL server itself not =in the instance. So...insert into [servername\instancename].[dbname].[dbo].table1 (col1,col2)selectt2.col1,t2.col2from table2 as =t2 I keep getting this message:Server 'CNS-SFO-A46\QA' is =not configured for DATA ACCESS.Thanks in advance for you =help.
--=_NextPart_000_038B_01C3D5F9.27781520--|||I'm affraid I don't see a check box for Data Access. Is it
possible I'm looking in the wrong place?
>--Original Message--
>In Enterprise Mgr, right click on the linked server - CNS-
SFO-A46\QA. Bring
>up the properties, click on Server Options, then check
the Data Access box.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>I need to load data from a table in sqlserver to a table
>in an instance of the same server. The query need to be
>run in the SQL server itself not in the instance. So...
>insert into [servername\instancename].[dbname].
>[dbo].table1 (
>col1,
>col2)
>select
>t2.col1,
>t2.col2
>from table2 as t2
>I keep getting this message:
>Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
>Thanks in advance for you help.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0038_01C3D60C.8C53A730
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Did you find the linked server under Security->Linked Servers?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Sandra" <anonymous@.discussions.microsoft.com> wrote in message
news:058101c3d631$7e232580$a101280a@.phx.gbl...
I'm affraid I don't see a check box for Data Access. Is it
possible I'm looking in the wrong place?
>--Original Message--
>In Enterprise Mgr, right click on the linked server - CNS-
SFO-A46\QA. Bring
>up the properties, click on Server Options, then check
the Data Access box.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>I need to load data from a table in sqlserver to a table
>in an instance of the same server. The query need to be
>run in the SQL server itself not in the instance. So...
>insert into [servername\instancename].[dbname].
>[dbo].table1 (
>col1,
>col2)
>select
>t2.col1,
>t2.col2
>from table2 as t2
>I keep getting this message:
>Server 'CNS-SFO-A46\QA' is not configured for DATA ACCESS.
>Thanks in advance for you help.
>
--=_NextPart_000_0038_01C3D60C.8C53A730
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Did you find the linked server under Security->Linked Servers?
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Sandra" wrote in message news:058101c3d631$7e=232580$a101280a@.phx.gbl...I'm affraid I don't see a check box for Data Access. Is it possible I'm =looking in the wrong place?>--Original Message-->In =Enterprise Mgr, right click on the linked server - CNS-SFO-A46\QA. Bring>up the properties, click on Server Options, then check =the Data Access box.>>-->Tom>>--=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Sand=ra" wrote in message>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...>=I need to load data from a table in sqlserver to a table>in an instance =of the same server. The query need to be>run in the SQL server itself =not in the instance. So...>>insert into [servername\instancename].[dbname].>[dbo].table1 (>col1,>col2)>select>t2.col1,>t2.col2>from table2 as t2>>I keep getting this =message:>>Server 'CNS-SFO-A46\QA' is not configured for DATA =ACCESS.>>Thanks in advance for you help.>
--=_NextPart_000_0038_01C3D60C.8C53A730--|||Yes, I did and I don't find the name of the server listed
but when I try to create New Linked Server it says the
server already exist. The same response I get when I try
to run EXEC sp_addlinkedserver
@.server = 'servername\instancename'
Also, I see the name of the server in the sysservers table.
>--Original Message--
>Did you find the linked server under Security->Linked
Servers?
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
message
>news:058101c3d631$7e232580$a101280a@.phx.gbl...
>I'm affraid I don't see a check box for Data Access. Is it
>possible I'm looking in the wrong place?
>>--Original Message--
>>In Enterprise Mgr, right click on the linked server -
CNS-
>SFO-A46\QA. Bring
>>up the properties, click on Server Options, then check
>the Data Access box.
>>--
>>Tom
>>----
-
>--
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>
>>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
>>I need to load data from a table in sqlserver to a table
>>in an instance of the same server. The query need to be
>>run in the SQL server itself not in the instance. So...
>>insert into [servername\instancename].[dbname].
>>[dbo].table1 (
>>col1,
>>col2)
>>select
>>t2.col1,
>>t2.col2
>>from table2 as t2
>>I keep getting this message:
>>Server 'CNS-SFO-A46\QA' is not configured for DATA
ACCESS.
>>Thanks in advance for you help.
>|||The server might already be listed under "remote servers". This would happen
for example if you were doing replication from one instance to another. You
can allow data access by running the procedure
sp_serveroption 'server' ,
'data access',
'true'
Hope this helps,
Joe Lax
<anonymous@.discussions.microsoft.com> wrote in message
news:06be01c3d641$bbad9740$a101280a@.phx.gbl...
> Yes, I did and I don't find the name of the server listed
> but when I try to create New Linked Server it says the
> server already exist. The same response I get when I try
> to run EXEC sp_addlinkedserver
> @.server = 'servername\instancename'
> Also, I see the name of the server in the sysservers table.
>
>
> >--Original Message--
> >Did you find the linked server under Security->Linked
> Servers?
> >
> >--
> > Tom
> >
> >----
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional
> >Toronto, ON Canada
> >www.pinnaclepublishing.com/sql
> >..
> >"Sandra" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:058101c3d631$7e232580$a101280a@.phx.gbl...
> >I'm affraid I don't see a check box for Data Access. Is it
> >possible I'm looking in the wrong place?
> >
> >>--Original Message--
> >>In Enterprise Mgr, right click on the linked server -
> CNS-
> >SFO-A46\QA. Bring
> >>up the properties, click on Server Options, then check
> >the Data Access box.
> >>
> >>--
> >>Tom
> >>
> >>----
> -
> >--
> >>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >>SQL Server MVP
> >>Columnist, SQL Server Professional
> >>Toronto, ON Canada
> >>www.pinnaclepublishing.com/sql
> >>
> >>
> >>"Sandra" <anonymous@.discussions.microsoft.com> wrote in
> >message
> >>news:00db01c3d620$c2708f40$a601280a@.phx.gbl...
> >>I need to load data from a table in sqlserver to a table
> >>in an instance of the same server. The query need to be
> >>run in the SQL server itself not in the instance. So...
> >>
> >>insert into [servername\instancename].[dbname].
> >>[dbo].table1 (
> >>col1,
> >>col2)
> >>select
> >>t2.col1,
> >>t2.col2
> >>from table2 as t2
> >>
> >>I keep getting this message:
> >>
> >>Server 'CNS-SFO-A46\QA' is not configured for DATA
> ACCESS.
> >>
> >>Thanks in advance for you help.
> >>
> >sql
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
load all data without knowing old one was load in the previous time?
I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run?
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Thank
Hi Cao Van,
Ofcourse when you run the package every time it means you are inserting duplicate rows. To avoid this, I have two familiar methods
1) Truncate the destination table, everytime when you load the data from source and populate the detination table. (This is always time consuming process)
2) Use Look up concept to filter out the duplicates.
Thanks
Subhash Subramanyam
|||In other words, you are the responsible of putting enough logic to avoid loading duplicate rows in your destination. Subhassh suggestions are very valid. For the first one you can use an execute sql task in control flow, right before the data flow to perform the TRUNCATE. For the second option, make sure you search the forum; there have been many discussions around that, actually in the first page of the forum, there is a sticky post that talks about it.
sqlFriday, March 23, 2012
Listing tables in a database
Hello Group,
how can I run a query in the QA to list the table names in a particular
database?
RichRich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
> > Hello Group,
> >
> > how can I run a query in the QA to list the table names in a particular
> > database?
> >
> > Rich|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
> > Rich,
> >
> > Use view INFORMATION_SCHEMA.TABLES
> >
> > use northwind
> > go
> >
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where TABLE_TYPE = 'BASE TABLE'
> > go
> >
> >
> > AMB
> >
> >
> > "Rich" wrote:
> >
> > > Hello Group,
> > >
> > > how can I run a query in the QA to list the table names in a particular
> > > database?
> > >
> > > Rich|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'
how can I run a query in the QA to list the table names in a particular
database?
RichRich,
Use view INFORMATION_SCHEMA.TABLES
use northwind
go
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
go
AMB
"Rich" wrote:
> Hello Group,
> how can I run a query in the QA to list the table names in a particular
> database?
> Rich|||Hello Mesa,
how can I add the creation date to the list?
Rich
"Alejandro Mesa" wrote:
> Rich,
> Use view INFORMATION_SCHEMA.TABLES
> use northwind
> go
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where TABLE_TYPE = 'BASE TABLE'
> go
>
> AMB
>
> "Rich" wrote:
> > Hello Group,
> >
> > how can I run a query in the QA to list the table names in a particular
> > database?
> >
> > Rich|||Rich
To retrieve the create date of a table you will need to query the sysobjects
system table. The following example illustrates a query that returns the
table name and the creation date of the table:
USE northwind
GO
SELECT name, crdate
FROM dbo.sysobjects
WHERE xtype = 'U' -- User table
HTH
- Peter Ward
WARDY IT Solutions
"Rich" wrote:
> Hello Mesa,
> how can I add the creation date to the list?
> Rich
> "Alejandro Mesa" wrote:
> > Rich,
> >
> > Use view INFORMATION_SCHEMA.TABLES
> >
> > use northwind
> > go
> >
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where TABLE_TYPE = 'BASE TABLE'
> > go
> >
> >
> > AMB
> >
> >
> > "Rich" wrote:
> >
> > > Hello Group,
> > >
> > > how can I run a query in the QA to list the table names in a particular
> > > database?
> > >
> > > Rich|||Or, if you're using SQL 2005, then also using the sys.objects Catalog
View
SELECT name, create_date
FROM sys.objects
WHERE type = 'U'
Wednesday, March 21, 2012
Listing all tables and their columns?
Hey all. I apologize, but I'm a developer, not a DBA. I need to run a query that will list each table in a DB as well as the columns i nthose tables.
I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.
Any help would be greatly appreciated. Thanks!Select Column_name,table_name From Information_schema.columns|||excellent, thanks!sql
I know that you can use: EXEC sp_help 'table_name' to get a description, but I'm not sure how to set up a cursor to substitute the table names, or where to get the tables names.
Any help would be greatly appreciated. Thanks!Select Column_name,table_name From Information_schema.columns|||excellent, thanks!sql
Monday, March 12, 2012
List of Store Procedures Called
Hi
I want to know the Store Procedures called when i run my application.We want to disable / drop the store procedures that are not called. Is there any Trace Template available to do so . We ar e using Sql Server 2000
thanks
You can run the Profiler for that:
File->New->Trace, then in Events tab choose "Stored procedure" event classes, then select only "SP
After a period of time->Stop selected trace (red button)
Friday, February 24, 2012
List box parameters
Can some one help me. I know you can create a drop down list box for
the user to select a value from and run a report on that value.
But Ineed my drop downlist to be mulit-select. ie the user must be able
to select one or more items in the list box
I have read that some people have managaged to make this work but the
only examples i could find did not infact work. Perhaps it is possible
to modify the xml code behind the report to make the drop down list
mulit-select. Again i was not able to find anyway of doing this.
building asn asp front end to call the report is not an option for me.
Thanks for the helpThere is no way with the Report Manager to have multi-select. This will be
supported with the next version (out this year).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Josef.Szeliga@.nrm.qld.gov.au> wrote in message
news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
> Can some one help me. I know you can create a drop down list box for
> the user to select a value from and run a report on that value.
> But Ineed my drop downlist to be mulit-select. ie the user must be able
> to select one or more items in the list box
> I have read that some people have managaged to make this work but the
> only examples i could find did not infact work. Perhaps it is possible
> to modify the xml code behind the report to make the drop down list
> mulit-select. Again i was not able to find anyway of doing this.
> building asn asp front end to call the report is not an option for me.
> Thanks for the help
>|||If this is not supported with in the Report Manager, is it supported in a
different method? If so, can an example/link be provided? If it is not
supported, then why is the 'multi-value' allowed on prompts? (I am using
Visual Studio and the June CTP)
Thanks!
"Bruce L-C [MVP]" wrote:
> There is no way with the Report Manager to have multi-select. This will be
> supported with the next version (out this year).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
> > Can some one help me. I know you can create a drop down list box for
> > the user to select a value from and run a report on that value.
> > But Ineed my drop downlist to be mulit-select. ie the user must be able
> > to select one or more items in the list box
> >
> > I have read that some people have managaged to make this work but the
> > only examples i could find did not infact work. Perhaps it is possible
> > to modify the xml code behind the report to make the drop down list
> > mulit-select. Again i was not able to find anyway of doing this.
> > building asn asp front end to call the report is not an option for me.
> >
> > Thanks for the help
> >
>
>|||As I said, this is supported with next release (RS 2005). In the June CTP
(note that this is a beta and betas are not complete by definition) the
multi-select is not working.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
> If this is not supported with in the Report Manager, is it supported in a
> different method? If so, can an example/link be provided? If it is not
> supported, then why is the 'multi-value' allowed on prompts? (I am using
> Visual Studio and the June CTP)
> Thanks!
> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This will
>> be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but the
>> > only examples i could find did not infact work. Perhaps it is possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for me.
>> >
>> > Thanks for the help
>> >
>>|||I was just looking at the same problem and found the following in the
documentation:
[Parameters with multiple values are specified by repeating the parameter
name; for example,
http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
This shows some promise as a workaround using the current version. I
haven't had a chance to look into it. Any insight would be appreciated..
Reid
"Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
> If this is not supported with in the Report Manager, is it supported in a
> different method? If so, can an example/link be provided? If it is not
> supported, then why is the 'multi-value' allowed on prompts? (I am using
> Visual Studio and the June CTP)
> Thanks!
> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This will
>> be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but the
>> > only examples i could find did not infact work. Perhaps it is possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for me.
>> >
>> > Thanks for the help
>> >
>>|||What documentation did you find that. I know for sure that multi-select
parameters do not work in RS 2000.
It has been a big deal here and discussed many times.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Reid" <warrex@.cox.net> wrote in message
news:eXBJ$p2dFHA.720@.TK2MSFTNGP15.phx.gbl...
>I was just looking at the same problem and found the following in the
>documentation:
> [Parameters with multiple values are specified by repeating the parameter
> name; for example,
> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
> This shows some promise as a workaround using the current version. I
> haven't had a chance to look into it. Any insight would be appreciated..
> Reid
> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
>> If this is not supported with in the Report Manager, is it supported in a
>> different method? If so, can an example/link be provided? If it is not
>> supported, then why is the 'multi-value' allowed on prompts? (I am using
>> Visual Studio and the June CTP)
>> Thanks!
>> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This will
>> be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be
>> > able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but the
>> > only examples i could find did not infact work. Perhaps it is
>> > possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for me.
>> >
>> > Thanks for the help
>> >
>>
>|||Reid,
Where did you find the original doc?
Thanks!
"Reid" wrote:
> I was just looking at the same problem and found the following in the
> documentation:
> [Parameters with multiple values are specified by repeating the parameter
> name; for example,
> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
> This shows some promise as a workaround using the current version. I
> haven't had a chance to look into it. Any insight would be appreciated..
> Reid
> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
> > If this is not supported with in the Report Manager, is it supported in a
> > different method? If so, can an example/link be provided? If it is not
> > supported, then why is the 'multi-value' allowed on prompts? (I am using
> > Visual Studio and the June CTP)
> >
> > Thanks!
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> There is no way with the Report Manager to have multi-select. This will
> >> be
> >> supported with the next version (out this year).
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
> >> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
> >> > Can some one help me. I know you can create a drop down list box for
> >> > the user to select a value from and run a report on that value.
> >> > But Ineed my drop downlist to be mulit-select. ie the user must be able
> >> > to select one or more items in the list box
> >> >
> >> > I have read that some people have managaged to make this work but the
> >> > only examples i could find did not infact work. Perhaps it is possible
> >> > to modify the xml code behind the report to make the drop down list
> >> > mulit-select. Again i was not able to find anyway of doing this.
> >> > building asn asp front end to call the report is not an option for me.
> >> >
> >> > Thanks for the help
> >> >
> >>
> >>
> >>
>
>|||Reporting Services Books Online - Running a Parameterized Report
I did a search on "multiple values" (no quotes) and it came up 3rd in the
list.
I'm testing the feature, but it doesn't seem to work
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OBxGaz2dFHA.584@.TK2MSFTNGP15.phx.gbl...
> What documentation did you find that. I know for sure that multi-select
> parameters do not work in RS 2000.
> It has been a big deal here and discussed many times.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Reid" <warrex@.cox.net> wrote in message
> news:eXBJ$p2dFHA.720@.TK2MSFTNGP15.phx.gbl...
>>I was just looking at the same problem and found the following in the
>>documentation:
>> [Parameters with multiple values are specified by repeating the parameter
>> name; for example,
>> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
>> This shows some promise as a workaround using the current version. I
>> haven't had a chance to look into it. Any insight would be appreciated..
>> Reid
>> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
>> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
>> If this is not supported with in the Report Manager, is it supported in
>> a
>> different method? If so, can an example/link be provided? If it is not
>> supported, then why is the 'multi-value' allowed on prompts? (I am
>> using
>> Visual Studio and the June CTP)
>> Thanks!
>> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This will
>> be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be
>> > able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but the
>> > only examples i could find did not infact work. Perhaps it is
>> > possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for
>> > me.
>> >
>> > Thanks for the help
>> >
>>
>>
>|||Hmm. The books online (RS 2000) I have installed does not have anything like
that.
"Reid" <warrex@.cox.net> wrote in message
news:%2324J0W3dFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Reporting Services Books Online - Running a Parameterized Report
> I did a search on "multiple values" (no quotes) and it came up 3rd in the
> list.
> I'm testing the feature, but it doesn't seem to work
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OBxGaz2dFHA.584@.TK2MSFTNGP15.phx.gbl...
>> What documentation did you find that. I know for sure that multi-select
>> parameters do not work in RS 2000.
>> It has been a big deal here and discussed many times.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Reid" <warrex@.cox.net> wrote in message
>> news:eXBJ$p2dFHA.720@.TK2MSFTNGP15.phx.gbl...
>>I was just looking at the same problem and found the following in the
>>documentation:
>> [Parameters with multiple values are specified by repeating the
>> parameter name; for example,
>> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
>> This shows some promise as a workaround using the current version. I
>> haven't had a chance to look into it. Any insight would be
>> appreciated..
>> Reid
>> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
>> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
>> If this is not supported with in the Report Manager, is it supported in
>> a
>> different method? If so, can an example/link be provided? If it is
>> not
>> supported, then why is the 'multi-value' allowed on prompts? (I am
>> using
>> Visual Studio and the June CTP)
>> Thanks!
>> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This
>> will be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be
>> > able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but
>> > the
>> > only examples i could find did not infact work. Perhaps it is
>> > possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for
>> > me.
>> >
>> > Thanks for the help
>> >
>>
>>
>>
>|||I've been through several updates since last Summer, including the SP2 beta.
I don't know if that updated the help files or not. The only help file
version for books online I can find is on the about box, v8.01, but that's
probably the books online software, not the reporting services helpfile.
Also there are 35 help files in the MSSQL\Reporting Services\Help\1033
folder. The file version for the .hxs files (first 2 at least) is 9.0.0.1.
The feature doesn't seem to work yet. If there's a "sweet" combination of
parameter definitions and url syntax and sql query parameter specification
that works, I didn't stumble across it. I guess we'll have to wait for the
next release.
"Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
news:0841F61A-F513-4A32-A970-09E5213765DB@.microsoft.com...
> Reid,
> Where did you find the original doc?
> Thanks!
>
> "Reid" wrote:
>> I was just looking at the same problem and found the following in the
>> documentation:
>> [Parameters with multiple values are specified by repeating the parameter
>> name; for example,
>> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
>> This shows some promise as a workaround using the current version. I
>> haven't had a chance to look into it. Any insight would be appreciated..
>> Reid
>> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
>> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
>> > If this is not supported with in the Report Manager, is it supported in
>> > a
>> > different method? If so, can an example/link be provided? If it is
>> > not
>> > supported, then why is the 'multi-value' allowed on prompts? (I am
>> > using
>> > Visual Studio and the June CTP)
>> >
>> > Thanks!
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> There is no way with the Report Manager to have multi-select. This
>> >> will
>> >> be
>> >> supported with the next version (out this year).
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> >> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> >> > Can some one help me. I know you can create a drop down list box for
>> >> > the user to select a value from and run a report on that value.
>> >> > But Ineed my drop downlist to be mulit-select. ie the user must be
>> >> > able
>> >> > to select one or more items in the list box
>> >> >
>> >> > I have read that some people have managaged to make this work but
>> >> > the
>> >> > only examples i could find did not infact work. Perhaps it is
>> >> > possible
>> >> > to modify the xml code behind the report to make the drop down list
>> >> > mulit-select. Again i was not able to find anyway of doing this.
>> >> > building asn asp front end to call the report is not an option for
>> >> > me.
>> >> >
>> >> > Thanks for the help
>> >> >
>> >>
>> >>
>> >>
>>|||Hi,
I am new to RS and the very first project that I had to implement was to
enhance a report which was taking a single param into multi value(not
multiple parameters). I bought 3 books and all were useless. I designed the
application and it works absolutely marvellous..
I will be posting the details by COB today so that everybody can see how it
works..
"Josef.Szeliga@.nrm.qld.gov.au" wrote:
> Can some one help me. I know you can create a drop down list box for
> the user to select a value from and run a report on that value.
> But Ineed my drop downlist to be mulit-select. ie the user must be able
> to select one or more items in the list box
> I have read that some people have managaged to make this work but the
> only examples i could find did not infact work. Perhaps it is possible
> to modify the xml code behind the report to make the drop down list
> mulit-select. Again i was not able to find anyway of doing this.
> building asn asp front end to call the report is not an option for me.
> Thanks for the help
>
the user to select a value from and run a report on that value.
But Ineed my drop downlist to be mulit-select. ie the user must be able
to select one or more items in the list box
I have read that some people have managaged to make this work but the
only examples i could find did not infact work. Perhaps it is possible
to modify the xml code behind the report to make the drop down list
mulit-select. Again i was not able to find anyway of doing this.
building asn asp front end to call the report is not an option for me.
Thanks for the helpThere is no way with the Report Manager to have multi-select. This will be
supported with the next version (out this year).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Josef.Szeliga@.nrm.qld.gov.au> wrote in message
news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
> Can some one help me. I know you can create a drop down list box for
> the user to select a value from and run a report on that value.
> But Ineed my drop downlist to be mulit-select. ie the user must be able
> to select one or more items in the list box
> I have read that some people have managaged to make this work but the
> only examples i could find did not infact work. Perhaps it is possible
> to modify the xml code behind the report to make the drop down list
> mulit-select. Again i was not able to find anyway of doing this.
> building asn asp front end to call the report is not an option for me.
> Thanks for the help
>|||If this is not supported with in the Report Manager, is it supported in a
different method? If so, can an example/link be provided? If it is not
supported, then why is the 'multi-value' allowed on prompts? (I am using
Visual Studio and the June CTP)
Thanks!
"Bruce L-C [MVP]" wrote:
> There is no way with the Report Manager to have multi-select. This will be
> supported with the next version (out this year).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
> > Can some one help me. I know you can create a drop down list box for
> > the user to select a value from and run a report on that value.
> > But Ineed my drop downlist to be mulit-select. ie the user must be able
> > to select one or more items in the list box
> >
> > I have read that some people have managaged to make this work but the
> > only examples i could find did not infact work. Perhaps it is possible
> > to modify the xml code behind the report to make the drop down list
> > mulit-select. Again i was not able to find anyway of doing this.
> > building asn asp front end to call the report is not an option for me.
> >
> > Thanks for the help
> >
>
>|||As I said, this is supported with next release (RS 2005). In the June CTP
(note that this is a beta and betas are not complete by definition) the
multi-select is not working.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
> If this is not supported with in the Report Manager, is it supported in a
> different method? If so, can an example/link be provided? If it is not
> supported, then why is the 'multi-value' allowed on prompts? (I am using
> Visual Studio and the June CTP)
> Thanks!
> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This will
>> be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but the
>> > only examples i could find did not infact work. Perhaps it is possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for me.
>> >
>> > Thanks for the help
>> >
>>|||I was just looking at the same problem and found the following in the
documentation:
[Parameters with multiple values are specified by repeating the parameter
name; for example,
http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
This shows some promise as a workaround using the current version. I
haven't had a chance to look into it. Any insight would be appreciated..
Reid
"Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
> If this is not supported with in the Report Manager, is it supported in a
> different method? If so, can an example/link be provided? If it is not
> supported, then why is the 'multi-value' allowed on prompts? (I am using
> Visual Studio and the June CTP)
> Thanks!
> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This will
>> be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but the
>> > only examples i could find did not infact work. Perhaps it is possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for me.
>> >
>> > Thanks for the help
>> >
>>|||What documentation did you find that. I know for sure that multi-select
parameters do not work in RS 2000.
It has been a big deal here and discussed many times.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Reid" <warrex@.cox.net> wrote in message
news:eXBJ$p2dFHA.720@.TK2MSFTNGP15.phx.gbl...
>I was just looking at the same problem and found the following in the
>documentation:
> [Parameters with multiple values are specified by repeating the parameter
> name; for example,
> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
> This shows some promise as a workaround using the current version. I
> haven't had a chance to look into it. Any insight would be appreciated..
> Reid
> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
>> If this is not supported with in the Report Manager, is it supported in a
>> different method? If so, can an example/link be provided? If it is not
>> supported, then why is the 'multi-value' allowed on prompts? (I am using
>> Visual Studio and the June CTP)
>> Thanks!
>> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This will
>> be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be
>> > able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but the
>> > only examples i could find did not infact work. Perhaps it is
>> > possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for me.
>> >
>> > Thanks for the help
>> >
>>
>|||Reid,
Where did you find the original doc?
Thanks!
"Reid" wrote:
> I was just looking at the same problem and found the following in the
> documentation:
> [Parameters with multiple values are specified by repeating the parameter
> name; for example,
> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
> This shows some promise as a workaround using the current version. I
> haven't had a chance to look into it. Any insight would be appreciated..
> Reid
> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
> > If this is not supported with in the Report Manager, is it supported in a
> > different method? If so, can an example/link be provided? If it is not
> > supported, then why is the 'multi-value' allowed on prompts? (I am using
> > Visual Studio and the June CTP)
> >
> > Thanks!
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> There is no way with the Report Manager to have multi-select. This will
> >> be
> >> supported with the next version (out this year).
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
> >> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
> >> > Can some one help me. I know you can create a drop down list box for
> >> > the user to select a value from and run a report on that value.
> >> > But Ineed my drop downlist to be mulit-select. ie the user must be able
> >> > to select one or more items in the list box
> >> >
> >> > I have read that some people have managaged to make this work but the
> >> > only examples i could find did not infact work. Perhaps it is possible
> >> > to modify the xml code behind the report to make the drop down list
> >> > mulit-select. Again i was not able to find anyway of doing this.
> >> > building asn asp front end to call the report is not an option for me.
> >> >
> >> > Thanks for the help
> >> >
> >>
> >>
> >>
>
>|||Reporting Services Books Online - Running a Parameterized Report
I did a search on "multiple values" (no quotes) and it came up 3rd in the
list.
I'm testing the feature, but it doesn't seem to work
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OBxGaz2dFHA.584@.TK2MSFTNGP15.phx.gbl...
> What documentation did you find that. I know for sure that multi-select
> parameters do not work in RS 2000.
> It has been a big deal here and discussed many times.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Reid" <warrex@.cox.net> wrote in message
> news:eXBJ$p2dFHA.720@.TK2MSFTNGP15.phx.gbl...
>>I was just looking at the same problem and found the following in the
>>documentation:
>> [Parameters with multiple values are specified by repeating the parameter
>> name; for example,
>> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
>> This shows some promise as a workaround using the current version. I
>> haven't had a chance to look into it. Any insight would be appreciated..
>> Reid
>> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
>> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
>> If this is not supported with in the Report Manager, is it supported in
>> a
>> different method? If so, can an example/link be provided? If it is not
>> supported, then why is the 'multi-value' allowed on prompts? (I am
>> using
>> Visual Studio and the June CTP)
>> Thanks!
>> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This will
>> be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be
>> > able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but the
>> > only examples i could find did not infact work. Perhaps it is
>> > possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for
>> > me.
>> >
>> > Thanks for the help
>> >
>>
>>
>|||Hmm. The books online (RS 2000) I have installed does not have anything like
that.
"Reid" <warrex@.cox.net> wrote in message
news:%2324J0W3dFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Reporting Services Books Online - Running a Parameterized Report
> I did a search on "multiple values" (no quotes) and it came up 3rd in the
> list.
> I'm testing the feature, but it doesn't seem to work
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OBxGaz2dFHA.584@.TK2MSFTNGP15.phx.gbl...
>> What documentation did you find that. I know for sure that multi-select
>> parameters do not work in RS 2000.
>> It has been a big deal here and discussed many times.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Reid" <warrex@.cox.net> wrote in message
>> news:eXBJ$p2dFHA.720@.TK2MSFTNGP15.phx.gbl...
>>I was just looking at the same problem and found the following in the
>>documentation:
>> [Parameters with multiple values are specified by repeating the
>> parameter name; for example,
>> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
>> This shows some promise as a workaround using the current version. I
>> haven't had a chance to look into it. Any insight would be
>> appreciated..
>> Reid
>> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
>> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
>> If this is not supported with in the Report Manager, is it supported in
>> a
>> different method? If so, can an example/link be provided? If it is
>> not
>> supported, then why is the 'multi-value' allowed on prompts? (I am
>> using
>> Visual Studio and the June CTP)
>> Thanks!
>> "Bruce L-C [MVP]" wrote:
>> There is no way with the Report Manager to have multi-select. This
>> will be
>> supported with the next version (out this year).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> > Can some one help me. I know you can create a drop down list box for
>> > the user to select a value from and run a report on that value.
>> > But Ineed my drop downlist to be mulit-select. ie the user must be
>> > able
>> > to select one or more items in the list box
>> >
>> > I have read that some people have managaged to make this work but
>> > the
>> > only examples i could find did not infact work. Perhaps it is
>> > possible
>> > to modify the xml code behind the report to make the drop down list
>> > mulit-select. Again i was not able to find anyway of doing this.
>> > building asn asp front end to call the report is not an option for
>> > me.
>> >
>> > Thanks for the help
>> >
>>
>>
>>
>|||I've been through several updates since last Summer, including the SP2 beta.
I don't know if that updated the help files or not. The only help file
version for books online I can find is on the about box, v8.01, but that's
probably the books online software, not the reporting services helpfile.
Also there are 35 help files in the MSSQL\Reporting Services\Help\1033
folder. The file version for the .hxs files (first 2 at least) is 9.0.0.1.
The feature doesn't seem to work yet. If there's a "sweet" combination of
parameter definitions and url syntax and sql query parameter specification
that works, I didn't stumble across it. I guess we'll have to wait for the
next release.
"Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
news:0841F61A-F513-4A32-A970-09E5213765DB@.microsoft.com...
> Reid,
> Where did you find the original doc?
> Thanks!
>
> "Reid" wrote:
>> I was just looking at the same problem and found the following in the
>> documentation:
>> [Parameters with multiple values are specified by repeating the parameter
>> name; for example,
>> http://exampleWebServerName/reportserver?/foldercontainingreports/orders®ion=east®ion=west ]
>> This shows some promise as a workaround using the current version. I
>> haven't had a chance to look into it. Any insight would be appreciated..
>> Reid
>> "Borris Clash" <BorrisClash@.discussions.microsoft.com> wrote in message
>> news:878012D9-AE3F-4BB4-BD3A-D9AA3B1C2F23@.microsoft.com...
>> > If this is not supported with in the Report Manager, is it supported in
>> > a
>> > different method? If so, can an example/link be provided? If it is
>> > not
>> > supported, then why is the 'multi-value' allowed on prompts? (I am
>> > using
>> > Visual Studio and the June CTP)
>> >
>> > Thanks!
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> There is no way with the Report Manager to have multi-select. This
>> >> will
>> >> be
>> >> supported with the next version (out this year).
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> <Josef.Szeliga@.nrm.qld.gov.au> wrote in message
>> >> news:1117695896.503904.161470@.g49g2000cwa.googlegroups.com...
>> >> > Can some one help me. I know you can create a drop down list box for
>> >> > the user to select a value from and run a report on that value.
>> >> > But Ineed my drop downlist to be mulit-select. ie the user must be
>> >> > able
>> >> > to select one or more items in the list box
>> >> >
>> >> > I have read that some people have managaged to make this work but
>> >> > the
>> >> > only examples i could find did not infact work. Perhaps it is
>> >> > possible
>> >> > to modify the xml code behind the report to make the drop down list
>> >> > mulit-select. Again i was not able to find anyway of doing this.
>> >> > building asn asp front end to call the report is not an option for
>> >> > me.
>> >> >
>> >> > Thanks for the help
>> >> >
>> >>
>> >>
>> >>
>>|||Hi,
I am new to RS and the very first project that I had to implement was to
enhance a report which was taking a single param into multi value(not
multiple parameters). I bought 3 books and all were useless. I designed the
application and it works absolutely marvellous..
I will be posting the details by COB today so that everybody can see how it
works..
"Josef.Szeliga@.nrm.qld.gov.au" wrote:
> Can some one help me. I know you can create a drop down list box for
> the user to select a value from and run a report on that value.
> But Ineed my drop downlist to be mulit-select. ie the user must be able
> to select one or more items in the list box
> I have read that some people have managaged to make this work but the
> only examples i could find did not infact work. Perhaps it is possible
> to modify the xml code behind the report to make the drop down list
> mulit-select. Again i was not able to find anyway of doing this.
> building asn asp front end to call the report is not an option for me.
> Thanks for the help
>
Monday, February 20, 2012
list all employees that didnt take courses in 2005 (was "SQL statment")
I need help on the SQL statment.
This SQL statment is extreamly slow and i don't know how to make it run faster. I have a tblTrainingStudents table where it keeps all the employees trainings and tblSHPEmployee is the employee roster and tblBasicSchool is the table where we track all the cadets who graduate from the basic school (from cadets to troopers) in each year. What i want the result to be is list all employees that didn't take courses in say year 2005. New troopers don't have to take any extra courses for year 2005 since they took these course when they were in basic school. I really need someone can help me speed up my SQL statment ASAP.
Thanks.
************************************************** **
SELECT *
FROM
(SELECT *
FROM tblTrainingCourses C, tblSHPEmployee
WHERE year = '#currentYear#'
AND (reg_no LIKE '1%'
OR reg_no LIKE '2%'
OR reg_no LIKE '3%')
AND troop = '#troop#'
AND NOT EXISTS
(SELECT *
FROM tblTrainingStudents S
WHERE C.trainingType = S.trainingType
AND C.courseID = S.courseID
AND year = '#currentYear#'
AND reg_no = regNo)) as employee
WHERE employee.reg_no NOT IN
(SELECT regNo
FROM tblBasicschool
WHERE employee.reg_no = regNo
AND left(enddate,4) = '#currentYear#'
AND (rank='TRP' OR left(regNo, 1) = '3'))
************************************************** *********
ORDER BY troop, lname, fname, minit, trainingType, courseIDI don(t see any need for the "SELECT FROM SELECT", so what about:SELECT *
FROM tblTrainingCourses as C, tblSHPEmployee
WHERE year = '#currentYear#'
AND (reg_no LIKE '1%' OR reg_no LIKE '2%' OR reg_no LIKE '3%')
AND troop = '#troop#'
AND NOT EXISTS
(SELECT 1
FROM tblTrainingStudents
WHERE C.trainingType = trainingType
AND C.courseID = courseID
AND year = '#currentYear#'
AND reg_no = regNo)
AND reg_no NOT IN
(SELECT regNo
FROM tblBasicschool
WHERE left(enddate,4) = '#currentYear#'
AND (rank='TRP' OR regNo LIKE '3%')
)
ORDER BY ...Clearly the "NOT EXISTS" and "NOT IN" will potentially slow down the query (since no index can be used for resolving these conditions).
Similarly the use of left(enddate,4) may slow down things; you should (if possible) replace it with someting like "enddate LIKE '2006%'".|||Peter, thanks for your post. It is still so slow. It takes about 3 mins to run the query. Is there any other way to speed this up a lot more? The tblTrainingStudents is kind of big. I really appreciate your help.|||It looks like your main query produces a cartesian product for I don't see a join-clause between 'tblTrainingCourses' and 'tblSHPEmployee'... or did I miss something?
Grts|||What about the following query?
I've removed tblTrainingCourses, and also removed the redundant condition reg_no = regNo in the second subquery.
You will maybe have to re-insert your conditions on year and troop -- I've no idea in which tables they belong.
Also, for the time being, I replaced the "in current year" condition by ">= '2006-01-01'"; adapt this if necessary.
The conditions on reg_no (with "BETWEEN" and ">= '3'") are more performant than a LIKE; but they assume reg_no to be all digits, and moreover assume an ASCII ordering.
Again, adapt if necessary.SELECT *
FROM tblSHPEmployee AS e
WHERE reg_no BETWEEN '1' AND '39999999'
AND NOT EXISTS
( SELECT regNo
FROM tblTrainingStudents AS s INNER JOIN tblTrainingCourses AS c
ON s.CourseID = c.CourseID
WHERE regNo = e.reg_No
AND year = '2006'
)
AND reg_no NOT IN
( SELECT regNo
FROM tblBasicschool
WHERE endDate >= '2006-01-01'
AND (rank = 'TRP' OR regNo >= '3')
)You may replace the "NOT EXISTS" by a "NOT IN" and vice versa; that could give performance differences (depending on the size of the tables), so try out all four possibilities. Don't forget to remove the condition "regNo = e.reg_No" in the "NOT IN" and to add it in the "NOT EXISTS".|||'LIKE' keywords also slow down queries since they force a table scan. Also you're selecting * when you potentially could not be.|||'LIKE' keywords also slow down queries since they force a table scan.Not necessarily: most RDBMS' will use a matching index scan for "LIKE '3%'". But you're right in the case of a "%" at the beginning of the string.
Also you're selecting * when you potentially could not be.This is less of a performance problem, it's at most a data transmission bottleneck (over a slow communication line).
Except in the case where you would only SELECT a column which happens to be an indexed column (or column combination), since in that case the query *could* be performed with an index-only scan (but that also depends on the WHERE condition of course).|||Not necessarily: most RDBMS' will use a matching index scan for "LIKE '3%'". But you're right in the case of a "%" at the beginning of the string.
This is less of a performance problem, it's at most a data transmission bottleneck (over a slow communication line).
Except in the case where you would only SELECT a column which happens to be an indexed column (or column combination), since in that case the query *could* be performed with an index-only scan (but that also depends on the WHERE condition of course).
I defer to your superior knowledge Peter!
Our DBAs here refuse to allow us to use LIKE in code because most of the time it does a table scan. 'SELECT *' is also banned since not only is it wasteful, it has a tendency to stop working if the table schema changes. :D|||'SELECT *' is also banned since not only is it wasteful, it has a tendency to stop working if the table schema changes.I fully agree with that: it's *always* safer to list all required columns explicitly, even it turns out to be all table columns.
So indeed "SELECT *" should be banned!|||I fully agree with that: it's *always* safer to list all required columns explicitly, even it turns out to be all table columns.
So indeed "SELECT *" should be banned!
ITA Peter...we've still got some 'select *' stuff hanging around but it is slowly being re-written to explicitly list all the columns.|||Or if you want to list all courses together with all employees who did not take that course:SELECT c.CourseName, e.EmployeeName
FROM
tblSHPEmployee AS e
INNER JOIN
tblTrainingCourses AS c
ON NOT EXISTS
( SELECT 1
FROM tblTrainingStudents AS s
WHERE s.CourseID = c.CourseID
AND e.reg_No = s.regNo
)
WHERE
reg_no BETWEEN '1' AND '39999999'
ORDER BY 1, 2Mind you that this may again run a lot slower!|||[Non-ANSI Comment]Assuming this is SQL Server then SELECT * within an exists statement is the one time SELECT * is recommended - the optimiser then selects optimal index.[/Non-ANSI Comment]|||SELECT * within an exists statement is recommended - the optimiser then selects optimal index.Are you sure that SQLServer would not do that with "SELECT 1" ?
I know DB2 will typically do an index scan without data lookup when using "SELECT 1".|||You know - I have lost my original MS link but coincidently today I've read something elsewhere (by the bloomin head of SQL Server Optimisation no less) that contradicts what I posted. So disregard :rolleyes:|||Anyway, in an EXISTS, "SELECT 1" is better than "SELECT *" since the former does not require data access (when an index is available on the column(s) specified in the WHERE condition) while the latter may need data access (unless the optimizer is clever enough to see that the two are equivalent :-)
This SQL statment is extreamly slow and i don't know how to make it run faster. I have a tblTrainingStudents table where it keeps all the employees trainings and tblSHPEmployee is the employee roster and tblBasicSchool is the table where we track all the cadets who graduate from the basic school (from cadets to troopers) in each year. What i want the result to be is list all employees that didn't take courses in say year 2005. New troopers don't have to take any extra courses for year 2005 since they took these course when they were in basic school. I really need someone can help me speed up my SQL statment ASAP.
Thanks.
************************************************** **
SELECT *
FROM
(SELECT *
FROM tblTrainingCourses C, tblSHPEmployee
WHERE year = '#currentYear#'
AND (reg_no LIKE '1%'
OR reg_no LIKE '2%'
OR reg_no LIKE '3%')
AND troop = '#troop#'
AND NOT EXISTS
(SELECT *
FROM tblTrainingStudents S
WHERE C.trainingType = S.trainingType
AND C.courseID = S.courseID
AND year = '#currentYear#'
AND reg_no = regNo)) as employee
WHERE employee.reg_no NOT IN
(SELECT regNo
FROM tblBasicschool
WHERE employee.reg_no = regNo
AND left(enddate,4) = '#currentYear#'
AND (rank='TRP' OR left(regNo, 1) = '3'))
************************************************** *********
ORDER BY troop, lname, fname, minit, trainingType, courseIDI don(t see any need for the "SELECT FROM SELECT", so what about:SELECT *
FROM tblTrainingCourses as C, tblSHPEmployee
WHERE year = '#currentYear#'
AND (reg_no LIKE '1%' OR reg_no LIKE '2%' OR reg_no LIKE '3%')
AND troop = '#troop#'
AND NOT EXISTS
(SELECT 1
FROM tblTrainingStudents
WHERE C.trainingType = trainingType
AND C.courseID = courseID
AND year = '#currentYear#'
AND reg_no = regNo)
AND reg_no NOT IN
(SELECT regNo
FROM tblBasicschool
WHERE left(enddate,4) = '#currentYear#'
AND (rank='TRP' OR regNo LIKE '3%')
)
ORDER BY ...Clearly the "NOT EXISTS" and "NOT IN" will potentially slow down the query (since no index can be used for resolving these conditions).
Similarly the use of left(enddate,4) may slow down things; you should (if possible) replace it with someting like "enddate LIKE '2006%'".|||Peter, thanks for your post. It is still so slow. It takes about 3 mins to run the query. Is there any other way to speed this up a lot more? The tblTrainingStudents is kind of big. I really appreciate your help.|||It looks like your main query produces a cartesian product for I don't see a join-clause between 'tblTrainingCourses' and 'tblSHPEmployee'... or did I miss something?
Grts|||What about the following query?
I've removed tblTrainingCourses, and also removed the redundant condition reg_no = regNo in the second subquery.
You will maybe have to re-insert your conditions on year and troop -- I've no idea in which tables they belong.
Also, for the time being, I replaced the "in current year" condition by ">= '2006-01-01'"; adapt this if necessary.
The conditions on reg_no (with "BETWEEN" and ">= '3'") are more performant than a LIKE; but they assume reg_no to be all digits, and moreover assume an ASCII ordering.
Again, adapt if necessary.SELECT *
FROM tblSHPEmployee AS e
WHERE reg_no BETWEEN '1' AND '39999999'
AND NOT EXISTS
( SELECT regNo
FROM tblTrainingStudents AS s INNER JOIN tblTrainingCourses AS c
ON s.CourseID = c.CourseID
WHERE regNo = e.reg_No
AND year = '2006'
)
AND reg_no NOT IN
( SELECT regNo
FROM tblBasicschool
WHERE endDate >= '2006-01-01'
AND (rank = 'TRP' OR regNo >= '3')
)You may replace the "NOT EXISTS" by a "NOT IN" and vice versa; that could give performance differences (depending on the size of the tables), so try out all four possibilities. Don't forget to remove the condition "regNo = e.reg_No" in the "NOT IN" and to add it in the "NOT EXISTS".|||'LIKE' keywords also slow down queries since they force a table scan. Also you're selecting * when you potentially could not be.|||'LIKE' keywords also slow down queries since they force a table scan.Not necessarily: most RDBMS' will use a matching index scan for "LIKE '3%'". But you're right in the case of a "%" at the beginning of the string.
Also you're selecting * when you potentially could not be.This is less of a performance problem, it's at most a data transmission bottleneck (over a slow communication line).
Except in the case where you would only SELECT a column which happens to be an indexed column (or column combination), since in that case the query *could* be performed with an index-only scan (but that also depends on the WHERE condition of course).|||Not necessarily: most RDBMS' will use a matching index scan for "LIKE '3%'". But you're right in the case of a "%" at the beginning of the string.
This is less of a performance problem, it's at most a data transmission bottleneck (over a slow communication line).
Except in the case where you would only SELECT a column which happens to be an indexed column (or column combination), since in that case the query *could* be performed with an index-only scan (but that also depends on the WHERE condition of course).
I defer to your superior knowledge Peter!
Our DBAs here refuse to allow us to use LIKE in code because most of the time it does a table scan. 'SELECT *' is also banned since not only is it wasteful, it has a tendency to stop working if the table schema changes. :D|||'SELECT *' is also banned since not only is it wasteful, it has a tendency to stop working if the table schema changes.I fully agree with that: it's *always* safer to list all required columns explicitly, even it turns out to be all table columns.
So indeed "SELECT *" should be banned!|||I fully agree with that: it's *always* safer to list all required columns explicitly, even it turns out to be all table columns.
So indeed "SELECT *" should be banned!
ITA Peter...we've still got some 'select *' stuff hanging around but it is slowly being re-written to explicitly list all the columns.|||Or if you want to list all courses together with all employees who did not take that course:SELECT c.CourseName, e.EmployeeName
FROM
tblSHPEmployee AS e
INNER JOIN
tblTrainingCourses AS c
ON NOT EXISTS
( SELECT 1
FROM tblTrainingStudents AS s
WHERE s.CourseID = c.CourseID
AND e.reg_No = s.regNo
)
WHERE
reg_no BETWEEN '1' AND '39999999'
ORDER BY 1, 2Mind you that this may again run a lot slower!|||[Non-ANSI Comment]Assuming this is SQL Server then SELECT * within an exists statement is the one time SELECT * is recommended - the optimiser then selects optimal index.[/Non-ANSI Comment]|||SELECT * within an exists statement is recommended - the optimiser then selects optimal index.Are you sure that SQLServer would not do that with "SELECT 1" ?
I know DB2 will typically do an index scan without data lookup when using "SELECT 1".|||You know - I have lost my original MS link but coincidently today I've read something elsewhere (by the bloomin head of SQL Server Optimisation no less) that contradicts what I posted. So disregard :rolleyes:|||Anyway, in an EXISTS, "SELECT 1" is better than "SELECT *" since the former does not require data access (when an index is available on the column(s) specified in the WHERE condition) while the latter may need data access (unless the optimizer is clever enough to see that the two are equivalent :-)
Subscribe to:
Posts (Atom)