Literal value with "IN" clause

Howdy,
Is it okay to use a literal value with the IN clause. E.g.
SELECT somefield, anotherfield
...
WHERE ...etc.
AND 1234 IN (SELECT userid FROM tblUsers)
I was told it wasn't valid, but I'm pretty sure it worked for me. Just
sing clarification.
cheersYes, that is valid, but it seem to be a pretty meaningless operation to me.
The IN predicate will
not be dependent on the outer query, so it you get at least one match, then
the predicate is true
for all rows in the outer table:
USE pubs
SELECT * FROM authors WHERE 'White' IN (SELECT au_lname FROM authors)
SELECT * FROM authors WHERE 'GWhite' IN (SELECT au_lname FROM authors)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Smith" <genericemailaccount@.genericdomain.genericTLD> wrote in message
news:443cd3b9$0$7532$afc38c87@.news.optusnet.com.au...
> Howdy,
> Is it okay to use a literal value with the IN clause. E.g.
> SELECT somefield, anotherfield
> ...
> WHERE ...etc.
> AND 1234 IN (SELECT userid FROM tblUsers)
> I was told it wasn't valid, but I'm pretty sure it worked for me. Just
> sing clarification.
> cheers
>|||Yes, it's ok.
Or you could use:
WHERE EXISTS(SELECT * FROM tblUsers WHERE userid = 1234)
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"John Smith" <genericemailaccount@.genericdomain.genericTLD> wrote in message
news:443cd3b9$0$7532$afc38c87@.news.optusnet.com.au...
> Howdy,
> Is it okay to use a literal value with the IN clause. E.g.
> SELECT somefield, anotherfield
> ...
> WHERE ...etc.
> AND 1234 IN (SELECT userid FROM tblUsers)
> I was told it wasn't valid, but I'm pretty sure it worked for me. Just
> sing clarification.
> cheers
>

Literal value with "IN" clause

Howdy,

Is it okay to use a literal value with the IN clause. E.g.

SELECT somefield, anotherfield
....
WHERE ...etc.
AND 1234 IN (SELECT userid FROM tblUsers)

I was told it wasn't valid, but I'm pretty sure it worked for me. Just
seeking clarification.

cheers,John Smith (genericemailaccount@.genericdomain.genericTLD) writes:
> Is it okay to use a literal value with the IN clause. E.g.
> SELECT somefield, anotherfield
> ...
> WHERE ...etc.
> AND 1234 IN (SELECT userid FROM tblUsers)
> I was told it wasn't valid, but I'm pretty sure it worked for me. Just
> seeking clarification.

That should be OK. A bit unusual maybe, but certainly valid.

--
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|||>> I was told it wasn't valid, but I'm pretty sure it worked for me. <<

It is valid, Standad SQL and can be a useful trick to avoid OR-ed
predicates. The IN() list just has to be expressions that will cast
to the proper data type.

Listbox to only appear if there are records returned from the SQL select query

I would like to make a listbox only appear if there are results returned by the SQL select statement.

I want this to be assessed on a click event of a button before the listbox is rendered.

I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?

hi,

there must be datasource(dataset or reader) for that listbox i believe.

if its dataset then use dataset.tables[0].Rows.Count, if its reader then use reader.hasrows.

hope it helps.

regards,

satish.

|||

Thanks Satish,

I used an if statement to assess if rows > 0

Cheers,

Ben.

|||

cheers Ben.

satish.

sql

Listbox

I have a report with a single parameter, named param1. The parameter is
a list box that accepts multiple values.
When I select a single value from a listbox, the report works fine, But
when I select more than one value, the stored procedure call fails
saying '[Query execution failed for data set 'XXX' Must decalare the
variable '@.param1'.]'
I initially assumed that the multiple values would be passed to my SP
in the form of a single comma-delimited varchar, but this does not
seems to be the case. How can I set up the stored procedure call to
take multiple values from a listbox? Do I need to do something special
in the SP to process the multiple values?Hi,
you will have to write your query like this here:
WHERE SomeColumn IN (@.parametername)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||It is passed the way you suppose. But, try calling your stored procedure
yourself (not from Reporting Services). Manually pass it a comma separated
string for the parameter. It won't work. This is a stored procedure issue,
not a Reporting Services issue. If you have the query defined in RS you can
do like this: select * from sometable where somefield in (@.MyParam) but you
cannot do this if that statement is in a stored procedure.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
select * from sometable where somefield in (select str from
charlist_to_table(@.MyParam,Default))
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"melishbd" <melissa@.hbdc.com> wrote in message
news:1169581326.626144.35060@.v45g2000cwv.googlegroups.com...
>I have a report with a single parameter, named param1. The parameter is
> a list box that accepts multiple values.
> When I select a single value from a listbox, the report works fine, But
> when I select more than one value, the stored procedure call fails
> saying '[Query execution failed for data set 'XXX' Must decalare the
> variable '@.param1'.]'
> I initially assumed that the multiple values would be passed to my SP
> in the form of a single comma-delimited varchar, but this does not
> seems to be the case. How can I set up the stored procedure call to
> take multiple values from a listbox? Do I need to do something special
> in the SP to process the multiple values?
>

list schemas of a sql server

hello,

what is the sql server 6.5 equivalent for:

"select catalog_name from information_schema.schemata"

i would like to see a list of available schema's on a server
and this seems to work on 7.0 and newer. would i be able to do
it with the systables ? i have looked through the content of the
systables but i can's see a 'schema' column in any of them.

any help is appreciated.
thanks,
tomINFORMATION_SCHEMA.SCHEMATA shows all databases, not all tables. In
fact, it looks like the documentation is wrong here - according to BOL
2000, the view should list each database "that has permissions for the
current user", however in reality it returns all databases, whether or
not the user can access them. So in SQL 6.5 you can just do this:

select name
from master.dbo.sysdatabases

In MSSQL 2000, you could use HAS_DBACCESS() to show only the DBs which
the user can access, but I don't think there's an equivalent in SQL
6.5.

Simon|||thanks for that simon, it did the trick !!

list of users with db_datareader privledges

is there a way to select all users who have db_datareader privledges using
sql server code?
tia,
dkTry sp_helpgroup:
Execute sp_helpgroup 'db_datareader'
Or, stealing some code from sp_helpgroup:
select Group_name = substring(g.name, 1, 25), Group_id = g.uid,
Users_in_group = substring(u.name, 1, 25),
Userid = u.uid
from sysusers u, sysusers g, sysmembers m
where g.name = 'db_datareader'
and g.uid = m.groupuid
and (g.issqlrole = 1)
and u.uid = m.memberuid
order by 1, 2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"dk" <dk@.discussions.microsoft.com> wrote in message
news:5CD3F4A2-4A4E-4FA9-A1F9-2BE81B04CFD2@.microsoft.com...
> is there a way to select all users who have db_datareader privledges using
> sql server code?
> tia,
> dk

list of tables without indexes

Using SS2000 SP4. I found this code:
USE SMCLMS_Dev;
GO
SELECT*
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY table_name;
GO
but when I run it I get "Invalid object name 'sys.tables'."
Thanks,
--
Dan D.That example uses the sys.tables catalog view and is only valid for SQL
Server 2005. For an equivalent example in SQL Server 2000, try this:
SELECT *
FROM sysobjects
WHERE OBJECTPROPERTY(id,'IsIndexed') = 0 and xtype = 'U'
ORDER BY name;
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:266E5467-BFFF-4940-BEFE-4FF479BFCFCF@.microsoft.com...
> Using SS2000 SP4. I found this code:
> USE SMCLMS_Dev;
> GO
> SELECT*
> FROM sys.tables
> WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
> ORDER BY table_name;
> GO
> but when I run it I get "Invalid object name 'sys.tables'."
> Thanks,
> --
> Dan D.|||That worked. Thanks Gail.
--
Dan D.
"Gail Erickson [MS]" wrote:
> That example uses the sys.tables catalog view and is only valid for SQL
> Server 2005. For an equivalent example in SQL Server 2000, try this:
> SELECT *
> FROM sysobjects
> WHERE OBJECTPROPERTY(id,'IsIndexed') = 0 and xtype = 'U'
> ORDER BY name;
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:266E5467-BFFF-4940-BEFE-4FF479BFCFCF@.microsoft.com...
> > Using SS2000 SP4. I found this code:
> >
> > USE SMCLMS_Dev;
> > GO
> > SELECT*
> > FROM sys.tables
> > WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
> > ORDER BY table_name;
> > GO
> >
> > but when I run it I get "Invalid object name 'sys.tables'."
> >
> > Thanks,
> > --
> > Dan D.
>
>

List of tables used in query

Hi all,
Is there any way to get the list of the tables used
in the select statment ?
I want to create an application in which when user fires
any query i want to store tables used in that query in
other table.
for e.g if user execute
--
select * from table mytablea join mytableb on id = id
--
then i want to get mytablea and mytableb
as my list of tables.
thanks..
--
Thanks & Regards
MalkeshHi
Is it just simple a SELECT statement or a stored procedures,views as well?
"Malkesh" <Malkesh@.discussions.microsoft.com> wrote in message
news:0E9B0793-3202-4FBB-BC3B-C00F43829B09@.microsoft.com...
> Hi all,
> Is there any way to get the list of the tables used
> in the select statment ?
> I want to create an application in which when user fires
> any query i want to store tables used in that query in
> other table.
> for e.g if user execute
> --
> select * from table mytablea join mytableb on id = id
> --
> then i want to get mytablea and mytableb
> as my list of tables.
> thanks..
> --
> Thanks & Regards
> Malkesh|||Hi,
It's just a select statment, but may involve complex joins, subqueries etc..
.
--
Thanks & Regards
Malkesh
"Uri Dimant" wrote:

> Hi
> Is it just simple a SELECT statement or a stored procedures,views as well?
>
>
> "Malkesh" <Malkesh@.discussions.microsoft.com> wrote in message
> news:0E9B0793-3202-4FBB-BC3B-C00F43829B09@.microsoft.com...
>
>

list of tables and views in a database

Hi
Can someone tell me the best way to extract a list of table names and views
from a database in a select statement? I'm using SQL Server 2000.
Many thanks
Andrewhttp://www.aspfaq.com/search.asp?q=schema%3A&category=1
"J055" <j055@.newsgroups.nospam> wrote in message
news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
> Hi
> Can someone tell me the best way to extract a list of table names and
> views from a database in a select statement? I'm using SQL Server 2000.
> Many thanks
> Andrew
>|||That's a very useful link.
Thank you
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OPVwtCSSGHA.336@.TK2MSFTNGP12.phx.gbl...
> http://www.aspfaq.com/search.asp?q=schema%3A&category=1
>
>
>
> "J055" <j055@.newsgroups.nospam> wrote in message
> news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
>|||select * from information_schema.tables
"J055" <j055@.newsgroups.nospam> wrote in message
news:udiRk7RSGHA.3944@.TK2MSFTNGP10.phx.gbl...
> Hi
> Can someone tell me the best way to extract a list of table names and
> views from a database in a select statement? I'm using SQL Server 2000.
> Many thanks
> Andrew
>

List of past Dates

Hello,
I want to create a list op dates using the SELECT statement. The dates range
from the current date till the current date - 10 days
The resultset for today should be as follows.
20050221
20050220
20050219
20050218
20050217
20050216
20050215
20050214
20050213
20050212
20050211
How do I create this. The result must be used in an other view, so if
possible I want a view.
Thanks
BartDoes this work for you?
select getdate()
union all
select getdate()-1
union all
select getdate()-2
union all
select getdate()-3
union all
select getdate()-4
union all
select getdate()-5
union all
select getdate()-6
union all
select getdate()-7
union all
select getdate()-8
union all
select getdate()-9
Bojidar Alexandro
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||Hi,
Maybe this will help you.
Tomasz B.
use tempdb
go
create view ten_dates
As
select convert(varchar(8), getdate(), 112) d
union all
select convert(varchar(8), getdate()-1, 112)
union all
select convert(varchar(8), getdate()-2, 112)
union all
select convert(varchar(8), getdate()-3, 112)
union all
select convert(varchar(8), getdate()-4, 112)
union all
select convert(varchar(8), getdate()-5, 112)
union all
select convert(varchar(8), getdate()-6, 112)
union all
select convert(varchar(8), getdate()-7, 112)
union all
select convert(varchar(8), getdate()-8, 112)
union all
select convert(varchar(8), getdate()-9, 112)
union all
select convert(varchar(8), getdate()-10, 112)
"Bart Steur" wrote:

> Hello,
> I want to create a list op dates using the SELECT statement. The dates ran
ge
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>
>|||Bart
Look at the script written by Irzik Ben-Gan
CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
DECLARE @.rc AS INT
SET @.rc = 1
INSERT INTO @.Dates VALUES(@.from)
WHILE @.from + @.rc * 2 - 1 <= @.to
BEGIN
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
SET @.rc = @.rc * 2
END
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
WHERE dt + @.rc <= @.to
RETURN
END
GO
SELECT dt FROM fn_dates('20050211', '20050221')
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||SELECT dt
FROM Calendar
WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
http://www.aspfaq.com/show.asp?id=2519
David Portas
SQL Server MVP
--|||Thanks Guys,
It worked.
Bart
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3CD7AEB-9F22-435F-A569-32CE2D005F45@.microsoft.com...
> SELECT dt
> FROM Calendar
> WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
> http://www.aspfaq.com/show.asp?id=2519
> --
> David Portas
> SQL Server MVP
> --
>

List of backups

When I select to restore a database, on the window that pops up you can select from a list what backup you want to resore first. The list im talking about is the one where it says 'First backup to restore'.

Im my list I have the option to restore right back to the year 2003 when my database was first implemented. Now I havn't tried but I know this would not work as I have deleted the backup file associated with those backups in 2003.

Is there anyway I can remove those backups from this list and only show current ones that really exist?

Thanks

Neil.Try deleting it from backupset table from ur msdb database.|||Thanks this looks like the answer I am looking for.

Neil.

List DMVs

what is the query to list all DMVs in SQL 2005 ?
I tried select * from sysobjects where name like 'dm%'
and it returned them
However select * from sys.objects where name like 'dm%' returns no results
Both executed in master database
Anyways, what is the right SQL to obtain list of DMVs as sysobjects is a
deprecated system table
Hi Hassan
Try this:
SELECT * FROM sys.system_objects
WHERE name LIKE 'dm%'
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OpvHSd0FHHA.924@.TK2MSFTNGP02.phx.gbl...
> what is the query to list all DMVs in SQL 2005 ?
> I tried select * from sysobjects where name like 'dm%'
> and it returned them
> However select * from sys.objects where name like 'dm%' returns no results
> Both executed in master database
> Anyways, what is the right SQL to obtain list of DMVs as sysobjects is a
> deprecated system table
>

List DMVs

what is the query to list all DMVs in SQL 2005 ?
I tried select * from sysobjects where name like 'dm%'
and it returned them
However select * from sys.objects where name like 'dm%' returns no results
Both executed in master database
Anyways, what is the right SQL to obtain list of DMVs as sysobjects is a
deprecated system tableHi Hassan
Try this:
SELECT * FROM sys.system_objects
WHERE name LIKE 'dm%'
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OpvHSd0FHHA.924@.TK2MSFTNGP02.phx.gbl...
> what is the query to list all DMVs in SQL 2005 ?
> I tried select * from sysobjects where name like 'dm%'
> and it returned them
> However select * from sys.objects where name like 'dm%' returns no results
> Both executed in master database
> Anyways, what is the right SQL to obtain list of DMVs as sysobjects is a
> deprecated system table
>

List DMVs

what is the query to list all DMVs in SQL 2005 ?
I tried select * from sysobjects where name like 'dm%'
and it returned them
However select * from sys.objects where name like 'dm%' returns no results
Both executed in master database
Anyways, what is the right SQL to obtain list of DMVs as sysobjects is a
deprecated system tableHi Hassan
Try this:
SELECT * FROM sys.system_objects
WHERE name LIKE 'dm%'
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OpvHSd0FHHA.924@.TK2MSFTNGP02.phx.gbl...
> what is the query to list all DMVs in SQL 2005 ?
> I tried select * from sysobjects where name like 'dm%'
> and it returned them
> However select * from sys.objects where name like 'dm%' returns no results
> Both executed in master database
> Anyways, what is the right SQL to obtain list of DMVs as sysobjects is a
> deprecated system table
>

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
>

List availables tables

How to list available tables in a database using an sql
statement?
The following code does not work with ms-sql:
select table_name from user_tables;SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
"joey32" <joey32@.total.net> wrote in message
news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
> How to list available tables in a database using an sql
> statement?
> The following code does not work with ms-sql:
> select table_name from user_tables;
>|||i tried with your query, but i get 'table sysobjets not
recognized' from Access.
by the same time, i found a another query that looks like
the same style:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND
((Left([name],4))<>"Msys"))
ORDER BY MSysObjects.Name;
but again i get an error from ms-access:
>> Records can not be read, no read permission on
MSysObjects
by the way, i'm queying via odbc with sql statements, if
that might help you
>--Original Message--
>select name from sysobjects where type='U'. This will
>give you all the names of the tables present in the
>database. Make sure you are in the database in which you
>want to run the query.
>HTH
>>--Original Message--
>>How to list available tables in a database using an sql
>>statement?
>>The following code does not work with ms-sql:
>>select table_name from user_tables;
>>
>>.
>.
>|||When were you planning on mentioning you're using Access? You said sql,
ms-sql, etc.
Try SELECT * FROM MSysObjects or SELECT * FROM MSSysObjects (forget
which)...
"joey32" <joey32@.total.net> wrote in message
news:044301c34725$1c648e60$a301280a@.phx.gbl...
> i get this following message when executing the request:
> >> Could not find '...\INFORMATION_SCHEMA.mdb"
> and that's all i have.
> >--Original Message--
> >SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> >
> >
> >
> >"joey32" <joey32@.total.net> wrote in message
> >news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
> >> How to list available tables in a database using an sql
> >> statement?
> >>
> >> The following code does not work with ms-sql:
> >> select table_name from user_tables;
> >>
> >>
> >
> >
> >.
> >|||Well, look at the other thread in same post, information is
there, but i might be not very visible to you, sorry for
that mistake.
I runned the query and get this error:
>> no read access to 'MSysObjets' table
by the way, i am quering via odbc using sql statements on a
ms-access database, i think version is 2002 (xp).
so how to i get the MSysObjets table visible for read
access?
>--Original Message--
>When were you planning on mentioning you're using Access?
You said sql,
>ms-sql, etc.
>Try SELECT * FROM MSysObjects or SELECT * FROM
MSSysObjects (forget
>which)...
>
>
>
>"joey32" <joey32@.total.net> wrote in message
>news:044301c34725$1c648e60$a301280a@.phx.gbl...
>> i get this following message when executing the request:
>> >> Could not find '...\INFORMATION_SCHEMA.mdb"
>> and that's all i have.
>> >--Original Message--
>> >SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
>> >
>> >
>> >
>> >"joey32" <joey32@.total.net> wrote in message
>> >news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
>> >> How to list available tables in a database using an
sql
>> >> statement?
>> >>
>> >> The following code does not work with ms-sql:
>> >> select table_name from user_tables;
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I got this answer from another ms-access forum wich is
exactly what i was looking for.
It might help somebody.
Thanks for your help.
----
Look in the MSysObjects table (tools|options| check system
objects). You
don't have to unhide the table to run the query, but it
wouldn't hurt for
you to poke around those tables to see what info is
available. Native
access tables are type 1. Attached access tables are type
6.
Select name, type from msysobjects where type = 1 or type
= 6
Richard Bernstein
"swat42" <swat42@.bit.com> wrote in
news:ETjPa.16788$Tx.811910@.news20.bellglobal.com:
> How to list available tables in a db by their table name
with an sql
> query?
> The following piece of code don't work:
> select table_name from user_tables;
>|||Thanks a lot but it's not MS-Access forum (it's MS SQL Server one) so I
don't think this might help anyone here
"joey32" <joey32@.total.net> wrote in message
news:057d01c3473a$0fc65fc0$a301280a@.phx.gbl...
> I got this answer from another ms-access forum wich is
> exactly what i was looking for.
> It might help somebody.

List as report parameter

Any hint how to build a report that prompts user to select multiple values
from a lookup table and uses the multiple values in WHRERE myfield IN
(<user-selected-list>) to select the data?
ThanksReporting Services does not provide this functionality ... supposedly coming
in a future release.
For now, you can just make the parameter a text box so the user can type in
a comma separated list ... then parse the parameter in the filter.
--
Shaun Beane, MCT, MCDBA, MCDST
http://dbageek.blogspot.com
"TheTechie" <TheTechie@.discussions.microsoft.com> wrote in message
news:5398BCA1-4839-46FC-8D40-1C4B81EE8B9E@.microsoft.com...
> Any hint how to build a report that prompts user to select multiple values
> from a lookup table and uses the multiple values in WHRERE myfield IN
> (<user-selected-list>) to select the data?
> Thanks|||Basically you can't - multi value lists are not natively support in the
current version.
You can however roll it yourself, either by dynamically building the sql
string to use JobID In (@.somecommadelimitedlist) or if you have some SQL
skills you can create a function in SQL Server to take a comma separated
list and return a table for use in a query.
Check this out:
http://www.windowsitpro.com/Article/ArticleID/26244/26244.html?Ad=1
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"TheTechie" <TheTechie@.discussions.microsoft.com> wrote in message
news:5398BCA1-4839-46FC-8D40-1C4B81EE8B9E@.microsoft.com...
> Any hint how to build a report that prompts user to select multiple values
> from a lookup table and uses the multiple values in WHRERE myfield IN
> (<user-selected-list>) to select the data?
> Thanks|||Chapter 11 of the book "Hitchhiker's Guide to SQL Server 2000 Reporting
Services" provides a work-round to have a multi-select pick list in the
parameter area.
It is quite well explained, starting out with a comma-separated textbox, but
involves some careful editing...
HTH

List all your connection managers

Hello,
I'm building a custom task which has a property ConnectionManager which obviously allows you to select which connection manager you're goinng to use.

I know how to get the list of connection managers but how do I make them appear in a combo-box in the properties pane? Anyone got some code for that? Hopefully this is fairly trivial for you developer types out there.

Thanks
JamieI believe that the key is the TypeConverter property of property objects.
If you were talking about a custom property in the Data Flow (which I realize is not your question) whose values came from an enumeration, using TypeConverter would both constrain the value of the property to an enumeration value, and tell the editor to display available values from the enumeration. From BOL (again, on the Data Flow, but I see that TypeConverter is available on other SSIS property objects):

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C#
IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

TR>

Visual Basic
Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

|||

To get rich behaviour such as list of connections, with <New Connection> option that fires the new conn dialog when selected, you need to go for a UITypeEditor.

Editor(typeof(DtsInputTypeEditor), typeof(System.Drawing.Design.UITypeEditor))
public string MyConnection
{
blah blah
}

You would need to create the DtsInputTypeEditor class, inheriting from UITypeEditor.

public sealed class DtsInputTypeEditor : UITypeEditor, IDisposable
{
blah blah
}

Overriding EditValue of UITypeEditor allows you grab the IWindowsFormsEditorService and throw up your editor control. Mine is a simple control which inherits from ListBox. In the editor control you can then capture the click event and test for <New Conn> and do what you need to do.

You could probably use a type convereter for a simples list and no new functions. Some articles I had gathered to help me.

Getting the Most Out of the .NET Framework PropertyGrid Control
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/usingpropgrid.asp)

Make Your Components Really RAD with Visual Studio .NET Property Browser
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/vsnetpropbrow.asp)

Customized display of collection data in a PropertyGrid - The Code Project - C# Controls
(http://www.codeproject.com/cs/miscctrl/customizingcollectiondata.asp)

|||

If you just want to show list of existing connections, I think coding TypeConverter is much simpler than UITypeEditor. You just need to subclass TypeConverter and implement several functions: GetStandardValuesSupported() should return true, and GetStandardValues() should return list of connection manager names. Property panel will show list returned by GetStandardValues() in drop down box.

UITypeEditor gives you more control and more opportunity to implement custom behavior, but also requires more coding.

|||I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.|||

<BarefacedCheek>
Has anyone got the code that uses TypeConverter to provide a list of your connection managers in a combo box in the properties pane for a custom task? It'd save me an *awful* lot of work in working it out for myself if I could just nab someone else's!!

For example, in

GetStandardValues(...)

in my class that inherits from TypeConverter I need to get a list of connection managers in the task. How on earth do I do that?

Here's what I have already which is quite frankly nothing at all:

public class ConnectionManagerList : TypeConverter
{
public override bool GetStandardValuesSupported(ITypeDescriptorContext context)
{
return true;
}

public override StandardValuesCollection GetStandardValues(ITypeDescriptorContext context)
{
StandardValuesCollection connManList;

//What goes in here?

return connManList;
}
}

Thanks
Jamie
</BarefacedCheek>

|||

Use "context.Instance" to get your class for the property. From that you need to get the Connections collection, which depends on what the class is. Loop the connections and assign to the values collection. Job done. What your class is and how you expose the connections collection is the thinking bit.

|||I'm having some trouble with this. I have a breakpoint in the debugger, and when I view context.Instance, it is a Microsoft.DataTransformationServices.PipelineComponentMetadata class. I can't find any documentation on that class, and it doesn't seem to implement Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90, which is what I would have expected. What is context.Instance supposed to be?|||

The Instance will be whatever is parenting the grid. Looks like this is for a pipeline component, and that will differ from a task for example.

If you want a copy of IDTSComponentMetaData90, then PipelineComponentMetadata appears to have a property DtsComponentMetadata of type IDTSComponentMetaData90.

|||When I try to use the PipelineComponentMetadata object, it is giving me a compiler error, saying that the class is inaccessible :(|||

To get list of all connections managers, use IDtsConnectionService service. It is a public interface in Microsoft.SqlServer.Dts.Design and is documented in Books Online. It can be obtained from appropriate service provider.

PipelineComponentMetadata class is internal implementation details of the designer, don't use it.

|||Thanks Michael. Is there any way to get the output, input, and metadata collections? What I really need for the component I am working on is a list of the External Metadata columns for the first (only) input collection.|||

Where does your code live?

If this is UI Type Editor, you get ITypeDescriptorContext which gives you PropertyDescriptor. You can get the property value from it.

|||

Adam Tybor wrote:

I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.

Returning to this after a few months away...

Good stuff Adam. And that actually works really well for what I want to do.

However, I don't like the displaying of the ID rather than the name so I might decide to go down the UITypeEditor route instead.

-Thanks

Jamie

|||

Adam Tybor wrote:

I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.

Returning to this after a few months away...

Good stuff Adam. And that actually works really well for what I want to do.

However, I don't like the displaying of the ID rather than the name so I might decide to go down the UITypeEditor route instead.

-Thanks

Jamie

List all your connection managers

Hello,
I'm building a custom task which has a property ConnectionManager which obviously allows you to select which connection manager you're goinng to use.

I know how to get the list of connection managers but how do I make them appear in a combo-box in the properties pane? Anyone got some code for that? Hopefully this is fairly trivial for you developer types out there.

Thanks
JamieI believe that the key is the TypeConverter property of property objects.
If you were talking about a custom property in the Data Flow (which I realize is not your question) whose values came from an enumeration, using TypeConverter would both constrain the value of the property to an enumeration value, and tell the editor to display available values from the enumeration. From BOL (again, on the Data Flow, but I see that TypeConverter is available on other SSIS property objects):

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C#
IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

TR>

Visual Basic
Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

|||

To get rich behaviour such as list of connections, with <New Connection> option that fires the new conn dialog when selected, you need to go for a UITypeEditor.

Editor(typeof(DtsInputTypeEditor), typeof(System.Drawing.Design.UITypeEditor))
public string MyConnection
{
blah blah
}

You would need to create the DtsInputTypeEditor class, inheriting from UITypeEditor.

public sealed class DtsInputTypeEditor : UITypeEditor, IDisposable
{
blah blah
}

Overriding EditValue of UITypeEditor allows you grab the IWindowsFormsEditorService and throw up your editor control. Mine is a simple control which inherits from ListBox. In the editor control you can then capture the click event and test for <New Conn> and do what you need to do.

You could probably use a type convereter for a simples list and no new functions. Some articles I had gathered to help me.

Getting the Most Out of the .NET Framework PropertyGrid Control
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/usingpropgrid.asp)

Make Your Components Really RAD with Visual Studio .NET Property Browser
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/vsnetpropbrow.asp)

Customized display of collection data in a PropertyGrid - The Code Project - C# Controls
(http://www.codeproject.com/cs/miscctrl/customizingcollectiondata.asp)

|||

If you just want to show list of existing connections, I think coding TypeConverter is much simpler than UITypeEditor. You just need to subclass TypeConverter and implement several functions: GetStandardValuesSupported() should return true, and GetStandardValues() should return list of connection manager names. Property panel will show list returned by GetStandardValues() in drop down box.

UITypeEditor gives you more control and more opportunity to implement custom behavior, but also requires more coding.

|||I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.|||

<BarefacedCheek>
Has anyone got the code that uses TypeConverter to provide a list of your connection managers in a combo box in the properties pane for a custom task? It'd save me an *awful* lot of work in working it out for myself if I could just nab someone else's!!

For example, in

GetStandardValues(...)

in my class that inherits from TypeConverter I need to get a list of connection managers in the task. How on earth do I do that?

Here's what I have already which is quite frankly nothing at all:

public class ConnectionManagerList : TypeConverter
{
public override bool GetStandardValuesSupported(ITypeDescriptorContext context)
{
return true;
}

public override StandardValuesCollection GetStandardValues(ITypeDescriptorContext context)
{
StandardValuesCollection connManList;

//What goes in here?

return connManList;
}
}

Thanks
Jamie
</BarefacedCheek>

|||

Use "context.Instance" to get your class for the property. From that you need to get the Connections collection, which depends on what the class is. Loop the connections and assign to the values collection. Job done. What your class is and how you expose the connections collection is the thinking bit.

|||I'm having some trouble with this. I have a breakpoint in the debugger, and when I view context.Instance, it is a Microsoft.DataTransformationServices.PipelineComponentMetadata class. I can't find any documentation on that class, and it doesn't seem to implement Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90, which is what I would have expected. What is context.Instance supposed to be?|||

The Instance will be whatever is parenting the grid. Looks like this is for a pipeline component, and that will differ from a task for example.

If you want a copy of IDTSComponentMetaData90, then PipelineComponentMetadata appears to have a property DtsComponentMetadata of type IDTSComponentMetaData90.

|||When I try to use the PipelineComponentMetadata object, it is giving me a compiler error, saying that the class is inaccessible :(|||

To get list of all connections managers, use IDtsConnectionService service. It is a public interface in Microsoft.SqlServer.Dts.Design and is documented in Books Online. It can be obtained from appropriate service provider.

PipelineComponentMetadata class is internal implementation details of the designer, don't use it.

|||Thanks Michael. Is there any way to get the output, input, and metadata collections? What I really need for the component I am working on is a list of the External Metadata columns for the first (only) input collection.|||

Where does your code live?

If this is UI Type Editor, you get ITypeDescriptorContext which gives you PropertyDescriptor. You can get the property value from it.

|||

Adam Tybor wrote:

I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.

Returning to this after a few months away...

Good stuff Adam. And that actually works really well for what I want to do.

However, I don't like the displaying of the ID rather than the name so I might decide to go down the UITypeEditor route instead.

-Thanks

Jamie

|||

Adam Tybor wrote:

I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.

Returning to this after a few months away...

Good stuff Adam. And that actually works really well for what I want to do.

However, I don't like the displaying of the ID rather than the name so I might decide to go down the UITypeEditor route instead.

-Thanks

Jamie

List all your connection managers

Hello,
I'm building a custom task which has a property ConnectionManager which obviously allows you to select which connection manager you're goinng to use.

I know how to get the list of connection managers but how do I make them appear in a combo-box in the properties pane? Anyone got some code for that? Hopefully this is fairly trivial for you developer types out there.

Thanks
JamieI believe that the key is the TypeConverter property of property objects.
If you were talking about a custom property in the Data Flow (which I realize is not your question) whose values came from an enumeration, using TypeConverter would both constrain the value of the property to an enumeration value, and tell the editor to display available values from the enumeration. From BOL (again, on the Data Flow, but I see that TypeConverter is available on other SSIS property objects):

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C#
IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

TR>

Visual Basic
Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

|||

To get rich behaviour such as list of connections, with <New Connection> option that fires the new conn dialog when selected, you need to go for a UITypeEditor.

Editor(typeof(DtsInputTypeEditor), typeof(System.Drawing.Design.UITypeEditor))
public string MyConnection
{
blah blah
}

You would need to create the DtsInputTypeEditor class, inheriting from UITypeEditor.

public sealed class DtsInputTypeEditor : UITypeEditor, IDisposable
{
blah blah
}

Overriding EditValue of UITypeEditor allows you grab the IWindowsFormsEditorService and throw up your editor control. Mine is a simple control which inherits from ListBox. In the editor control you can then capture the click event and test for <New Conn> and do what you need to do.

You could probably use a type convereter for a simples list and no new functions. Some articles I had gathered to help me.

Getting the Most Out of the .NET Framework PropertyGrid Control
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/usingpropgrid.asp)

Make Your Components Really RAD with Visual Studio .NET Property Browser
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/vsnetpropbrow.asp)

Customized display of collection data in a PropertyGrid - The Code Project - C# Controls
(http://www.codeproject.com/cs/miscctrl/customizingcollectiondata.asp)

|||

If you just want to show list of existing connections, I think coding TypeConverter is much simpler than UITypeEditor. You just need to subclass TypeConverter and implement several functions: GetStandardValuesSupported() should return true, and GetStandardValues() should return list of connection manager names. Property panel will show list returned by GetStandardValues() in drop down box.

UITypeEditor gives you more control and more opportunity to implement custom behavior, but also requires more coding.

|||I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.|||

<BarefacedCheek>
Has anyone got the code that uses TypeConverter to provide a list of your connection managers in a combo box in the properties pane for a custom task? It'd save me an *awful* lot of work in working it out for myself if I could just nab someone else's!!

For example, in

GetStandardValues(...)

in my class that inherits from TypeConverter I need to get a list of connection managers in the task. How on earth do I do that?

Here's what I have already which is quite frankly nothing at all:

public class ConnectionManagerList : TypeConverter
{
public override bool GetStandardValuesSupported(ITypeDescriptorContext context)
{
return true;
}

public override StandardValuesCollection GetStandardValues(ITypeDescriptorContext context)
{
StandardValuesCollection connManList;

//What goes in here?

return connManList;
}
}

Thanks
Jamie
</BarefacedCheek>

|||

Use "context.Instance" to get your class for the property. From that you need to get the Connections collection, which depends on what the class is. Loop the connections and assign to the values collection. Job done. What your class is and how you expose the connections collection is the thinking bit.

|||I'm having some trouble with this. I have a breakpoint in the debugger, and when I view context.Instance, it is a Microsoft.DataTransformationServices.PipelineComponentMetadata class. I can't find any documentation on that class, and it doesn't seem to implement Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData90, which is what I would have expected. What is context.Instance supposed to be?|||

The Instance will be whatever is parenting the grid. Looks like this is for a pipeline component, and that will differ from a task for example.

If you want a copy of IDTSComponentMetaData90, then PipelineComponentMetadata appears to have a property DtsComponentMetadata of type IDTSComponentMetaData90.

|||When I try to use the PipelineComponentMetadata object, it is giving me a compiler error, saying that the class is inaccessible :(|||

To get list of all connections managers, use IDtsConnectionService service. It is a public interface in Microsoft.SqlServer.Dts.Design and is documented in Books Online. It can be obtained from appropriate service provider.

PipelineComponentMetadata class is internal implementation details of the designer, don't use it.

|||Thanks Michael. Is there any way to get the output, input, and metadata collections? What I really need for the component I am working on is a list of the External Metadata columns for the first (only) input collection.|||

Where does your code live?

If this is UI Type Editor, you get ITypeDescriptorContext which gives you PropertyDescriptor. You can get the property value from it.

|||

Adam Tybor wrote:

I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.

Returning to this after a few months away...

Good stuff Adam. And that actually works really well for what I want to do.

However, I don't like the displaying of the ID rather than the name so I might decide to go down the UITypeEditor route instead.

-Thanks

Jamie

|||

Adam Tybor wrote:

I don't know about dataflow components, but on a custom task I simply created a property of type ConnectionManager and it gave me a drop down list of all the connectionmanager's guid's in the package. The built in type converter has a nice drill down connection manager editor too so you can easily identify which connection you selected. Its worth a shot for almost no programming at all.

Returning to this after a few months away...

Good stuff Adam. And that actually works really well for what I want to do.

However, I don't like the displaying of the ID rather than the name so I might decide to go down the UITypeEditor route instead.

-Thanks

Jamie