@@error for dynamic SQL

Does anyone know of a simple, correct way to retrieve the value of @.@.error from dynamic SQL? I can't use:SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''

EXECUTE (@.cCmd)
SELECT @.@.error -- only shows locally reported error

EXECUTE sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported errorI can get the error from a local procedure (one run on this box) using the first example, or on a remote server (via RPC) using the second, but not both. Any suggestions?

-PatPI'm confused as to what your expected output should be since I'm getting errors all over the place (typical results for me).

QUERY:
Set Concat_Null_Yields_Null Off

Declare @.cCmd nvarchar(4000)
Declare @.cServer nvarchar, @.cLogin nvarchar
, @.iError int

SELECT @.cCmd = 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''junque'', '''
+ @.cLogin + ''''

Print @.cCmd + N' << The Command' + nchar(10)

EXECUTE (@.cCmd)
SELECT @.@.error As 'Error' -- only shows locally reported error

EXECUTE @.iError = sp_executesql @.cCmd, N'@.i INT OUTPUT'
, @.iError OUTPUT -- Only shows remotely reported error

Select @.iError

RESULTS
EXECUTE @.i = [].master.dbo.sp_password NULL, 'junque', '' << The Command

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Error
----
1038

(1 row(s) affected)

Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.

----
170

(1 row(s) affected)

What value do you expect for the error codes?|||I'm attempting to dynamically build a password change request, that will be executed on some arbitrary number of servers determined by values in a table. The code that I posted assumes that your current login has the ability to dynamically execute sp_password for a login on both your local server and all of the remote servers.

When it executes sp_password on the local server, the first example works well and the error code is available via the SELECT. When it executes sp_password on a remote server (via RPC, Remote Procedure Call), then the second example works well, but I haven't found any reliable way to retrive the @.@.error result.

At the moment, I'm thinking that I'll just use the 0/1 return code from sp_password and wait until I have time to create an extended stored procedure (or the problem goes away) for a more elegant fix.

-PatP|||Thanks - now I get it.

The only way I could find around this is to put a wrapper around sp_password by creating my_sp_password via hacking sp_password - one of the flaws with this approach is that when sp_password changes, you have to also potentially change the wrapper (my_sp_password).

If you choose to use this hack, you may want to comment out/remove the RAISERRORs. Note: I did not test this on linked servers.

WRAPPER
create procedure my_sp_password
@.old sysname = NULL, -- the old (current) password
@.new sysname, -- the new password
@.loginame sysname = NULL -- user to change password on
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @.self int, @.error int
select @.self = CASE WHEN @.loginame is null THEN 1 ELSE 2 END
, @.error = -1

-- RESOLVE LOGIN NAME
if @.loginame is null
select @.loginame = suser_sname()

-- CHECK PERMISSIONS (SecurityAdmin per Richard Waymire) --
IF (not is_srvrolemember('securityadmin') = 1)
AND not @.self = 1
begin
dbcc auditevent (107, @.self, 0, @.loginame, NULL, NULL, NULL)
raiserror(15210,-1,-1)
select @.error = 15210
return (@.error)
end
ELSE
begin
dbcc auditevent (107, @.self, 1, @.loginame, NULL, NULL, NULL)
end

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
IF (@.@.trancount > 0)
begin
raiserror(15002,-1,-1,'sp_password')
select @.error = 15002
return (@.error)
end

-- RESOLVE LOGIN NAME (disallows nt names)
if not exists (select * from master.dbo.syslogins where
loginname = @.loginame and isntname = 0)
begin
raiserror(15007,-1,-1,@.loginame)
select @.error = 15007
return (@.error)
end

-- IF non-SYSADMIN ATTEMPTING CHANGE TO SYSADMIN, REQUIRE PASSWORD (218078) --
if (@.self <> 1 AND is_srvrolemember('sysadmin') = 0 AND exists
(SELECT * FROM master.dbo.syslogins WHERE loginname = @.loginame and isntname = 0
AND sysadmin = 1) )
SELECT @.self = 1

-- CHECK OLD PASSWORD IF NEEDED --
if (@.self = 1 or @.old is not null)
if not exists (select * from master.dbo.sysxlogins
where srvid IS NULL and
name = @.loginame and
( (@.old is null and password is null) or
(pwdcompare(@.old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1) ) )
begin
raiserror(15211,-1,-1)
select @.error = 15211
return (@.error)
end

-- CHANGE THE PASSWORD --
exec sp_configure 'allow', 1
Reconfigure
set @.error = @.@.error

if @.error = 0
begin
-- update master.dbo.sysxlogins
-- set password = convert(varbinary(256), pwdencrypt(@.new)), xdate2 = getdate(), xstatus = xstatus & (~2048)
-- where name = @.loginame and srvid IS NULL
exec @.error = sp_password @.old, @.new, @.loginame
end

-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
if @.error = 0
begin
exec('use master grant all to null')
set @.error = @.@.error
end

-- FINALIZATION: RETURN SUCCESS/FAILURE --
if @.error <> 0
return (@.error)
raiserror(15478,-1,-1)
set @.error = 15478
return (@.error) -- sp_password

EXECUTION
Declare @.error int
Select @.error = -1
exec @.error = my_sp_password 'fred', 'ted', 'sam'
Select @.error|||Or

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE ProcessLog(Spid int, ProcessName varchar(255), Row_Count int, Error int, Return_Code int, Add_Dt datetime DEFAULT GetDate())
GO

DECLARE @.cCmd varchar(8000), @.cServer sysname, @.cLogin sysname
SELECT @.cServer = @.@.SERVERNAME, @.cLogin = SYSTEM_USER

SELECT @.cCmd = 'DECLARE @.i int' + CHAR(13)
+ 'EXECUTE @.i = [' + @.cServer
+ '].master.dbo.sp_password NULL, ''test'', '''
+ @.cLogin + ''''+ CHAR(13)
+ 'INSERT INTO ProcessLog(Spid, ProcessName, Row_Count, Error, Return_Code)' + CHAR(13)
+ 'SELECT ' + CONVERT(varchar(15),@.@.SPID) + ', ''sp_password'''+', @.@.ROWCOUNT, @.@.ERROR, @.i' + CHAR(13)

SELECT @.cCmd

EXECUTE (@.cCmd)

SELECT * FROM ProcessLog o
WHERE Spid = @.@.SPID
AND Add_Dt IN ( SELECT MAX(Add_Dt)
FROM ProcessLog i
WHERE i.Spid = o.Spid)
GO

SET NOCOUNT OFF
DROP TABLE ProcessLog
GO|||Brett's example works like my example 1 for the local server, and would make Rube Goldberg giddy with glee in the process. I really want something that works equally well for both local and remote without a whole lot of work to make it fly.

-PatP|||How does your first one work?

Doesn't it return an error?

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@.i'.

?

I didn't test it remotely, are you saying it won't work?|||What I posted is actually an excerpt from a much larger script, only to show the general idea of what I'm doing... I didn't intend for anyone to execute it "as is", but I should have been more clear about that.

The first bit of logic works nicely when the code is being executed on the local server (in other words, when @.cServer = @.@.servername like in your example). The second bit of code works when the dynamic SQL generates an RPC, and it can be elaborated on to return the actual @.@.error value by following the SELECT with a SET command in the dynamic SQL. Because the code is trapped at two different layers within the SQL Engine, and is reported differently depending on who catches what, I can't think of a single, coherant way to trap the actual error code (15087 if I remember correctly) for both cases unless I construct wildly different bits of SQL and test to see which SQL I should use.

As I said, I think this is a design issue in SQL Server that I can't readily fix. I think that the proper answer is to just break down and code it as an extended stored procedure, where I can "put a leash on the monster" and stop fighting it. I just don't have the luxury of the time (probably around 8 hours to write and test, without the enhancements I'd really like to add) needed for that kind of fix at the moment.

-PatP|||DECLARE @.j INT
CREATE TABLE #temp ( id INT IDENTITY (1,1), result VARCHAR(255) )
INSERT INTO #temp (result)
EXEC @.j = master.dbo.xp_cmdshell 'osql -E -Q "EXIT(EXECUTE master.dbo.sp_password NULL, ''junque'', ''test3''; )"'
SELECT @.j
IF @.j = -100
BEGIN
SELECT SUBSTRING(result, CHARINDEX('Msg ', result) + 4, (CHARINDEX(' ',result,CHARINDEX('Msg ', result) + 5) - CHARINDEX('Msg ', result) - 5))
FROM #temp
WHERE id = 1
END

DROP TABLE #temp

NOTE: I don't have a remote server to test it with at home.

Tim S|||I don't think that the query from TimS will get me much further than what I've got right now, but it gives me an idea! I think he's resolved the issue of how to work around the distinctly different way that MS-SQL handles errors on the local SQL Server versus remote SQL servers by using osql.exe to put all servers on a local footing. I'll have to experiment a bit when I get back to a machine with MS-SQL available, but I think it might work to do something like:SET @.cmd = 'osql -E -Q "EXECUTE sp_password NULL, ''abcde'', '''
+ @.cLogin + ''' EXIT(SELECT @.@.error)" -s ' + @.cServer'

EXECUTE @.iRC = master.dbo.xp_cmdshell @.cCmdTo simulate the remote server, just create a second instance of MS-SQL on your machine. As long as the network isn't specifically a part of your test, a second instance works nicely for whatever I've needed to test.

-PatP

:new.<variable_name>

I am trying to write an 'after insert' trigger for a very dynamic database that inserts all the new values put into a table into an audit table. To do this, I have to write :new.col1, :new.col2, :new.col3 etc into the audit table. I have a loop that puts ['col1','col2','col3', etc.] into a variable named column_name, but I don't seem to be able to dynamically generate the variable name. I guess I am looking for something like an eval function in PL/SQL that could do

stmt := 'new_value := :new.'||column_name;
eval(stmt);
insert into audit_table (:new.id,column_name,new_value,SYSDATE);

But I haven't found it yet, and I can't select column_name from table_name because table 'table_name' is mutating and Oracle won't let you select on a table that is changing. If anyone can help, it would be greatly appreciated. Thanks in advance.Hello,

I dont know such a command in PL/SQL ... but what do you think about dynamic SQL. Use the package methods DMBS_SQL.

I hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com|||Hey, I can use dynamic SQL for something like the following

sql_stmt = 'select :col_name from :table_name where id=:id';
execute immediate into new_value using col_name, table_name, id

But this is an actual PL/SQL statement that I need to build on the fly like this

stmt := 'new_value=:new.'||col_name;
eval(stmt);
insert into audit_table values(:new.id,new_value,SYSDATE,col_name);

Any thoughts would be greatly appreciated. Thanks.|||Hello,

whats about

DECLARE
cVar VARCHAR2(500) := 0;
BEGIN
cVar := 'DECLARE ' ||
' cThis VARCHAR2(200) := ''' ||
'BEGIN ' ||
' cThis := :new.' || column_name ||
' INSERT INTO and so on and so on ' ||
'END;';

.
.
.
do the dynamic stuff
.
.
.

END;

Is this what you want to do ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

'.bcp file is corrupt' with dynamic snapshots

I have merge replication set up between sql mobile and sql 2005 (just recently upgraded from sql 2000). My publication has around 40 published tables, about half of them are filtered. The other day I noticed that the initial replication to get the entire database was downloading the same tables multiple times. The last test was each table was downloaded 10 times. So I did some research which brought me to the following fix, which was to implement dynamic snapshots.

However, when I try to replicate, it generates a dynamic snapshot, but it fails with the following error…

An error occurred while reading the .bcp data file for the 'MSmerge_rowtrack' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber.(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199428)

I haven’t changed the snapshot locations, and if I don’t use dynamic snapshots it works fine. I’ve tweaked the permissions (in development) to the point where everyone has full permission on the IIS share, and snapshot folder with no luck.

I would appreciate any advice. Thank you!

Not sure if you'll find an answer here.

I've been a part of this thread

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

without any real resolution so far.

We can re-run the snapshot when it happens and that seems to resolve it for the user who is affected, but if it's a permissoins problem why doesn;t it happen to everyone at once?

**Dynamic SQL**

Hi
I'm working with SQL 2000 and I have a table with following struncture and I
want to get the result as I described below ,but I don't know how?
PAP (PART1 NUMERIC(3) ,PART2 NUMERIC(3) ,QTY NUMERIC(2))
Primary Key (part1+part2)
part1 part2 qty
-- -- --
100 205 5
100 204 6
101 203 7
205 107 1
107 116 2
Result:
I want to show following result when we select data for part1=100 and
part2=205:
part1 part2 qty finalqty
-- -- -- --
100 205 5 2
I used following code to get the result and it was successfull
select a.par1,a.part2,a.qty,c.qty2
from pap a,pap b,pap c
where a.part2=b.part1
and b.part2=c.part1
but what I really s for,is the number of join
is not static,because if a new row inserted in the table the result would
be different for example:
new row : 116 114 3
then
the result:
part1 part2 qty finalqty
-- -- -- --
100 205 5 3
I want a sql statement to fetch the result witjout dependecy to the special
number of joins.
Thanks alot
new row:The very first question you'll have to answer is how do you know which row
is the "new" row. Once that is clear you can do something like this.
e.g.
create table pap (part1 numeric(3) ,part2 numeric(3) ,qty numeric(2),
primary key (part1,part2)
)
go
insert pap (part1,part2,qty)
select 100, 205, 5
union all select 100, 204, 6
union all select 101, 203, 7
union all select 205, 107, 1
union all select 107, 116, 2
go
--pick the last qty
select *,(select top 1 qty from pap order by part1 desc,part2 desc) lastqty
from pap
where part1=100 and part2=205
go
drop table pap
go
-oj
"M R" <rezvani@.parskhazar.net> wrote in message
news:eCGJAfbZFHA.1412@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'm working with SQL 2000 and I have a table with following struncture and
> I
> want to get the result as I described below ,but I don't know how?
> PAP (PART1 NUMERIC(3) ,PART2 NUMERIC(3) ,QTY NUMERIC(2))
> Primary Key (part1+part2)
> part1 part2 qty
> -- -- --
> 100 205 5
> 100 204 6
> 101 203 7
> 205 107 1
> 107 116 2
> Result:
> I want to show following result when we select data for part1=100 and
> part2=205:
> part1 part2 qty finalqty
> -- -- -- --
> 100 205 5 2
> I used following code to get the result and it was successfull
> select a.par1,a.part2,a.qty,c.qty2
> from pap a,pap b,pap c
> where a.part2=b.part1
> and b.part2=c.part1
> but what I really s for,is the number of join
> is not static,because if a new row inserted in the table the result would
> be different for example:
> new row : 116 114 3
> then
> the result:
> part1 part2 qty finalqty
> -- -- -- --
> 100 205 5 3
> I want a sql statement to fetch the result witjout dependecy to the
> special
> number of joins.
> Thanks alot
> new row:
>
>|||Get a copy of TREES & HIERARCHIES IN SQL for details, but for now,
Google up the "Nested set model". Here is how to travel to the leaf
nodes from any point in the tree.
SELECT @.my_part, T1.qty
FROM Tree AS T1, Tree AS T2
WHERE T1.rgt-T1.lft =1 -- leaf node
AND T1.lft BETWEEN T2.lft AND T2.rgt
AND T2.part = @.my_part;|||It doesn't matter to recognize the new record ,because if we have a correct
sql statement it automatically will be fetched in the result,and your sql
statement is not suitable
select *,(select top 1 qty from pap order by part1 desc,part2 desc) lastqty
from pap
where part1=100 and part2=205
cause the last qty is not the qty with the greatest part1 and part2,we
select the qty as a last one by joining the part2 with part1 in this table
and repeat this action to find the ast one.
"oj" <nospam_ojngo@.home.com> wrote in message
news:#CR18acZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> The very first question you'll have to answer is how do you know which row
> is the "new" row. Once that is clear you can do something like this.
> e.g.
> create table pap (part1 numeric(3) ,part2 numeric(3) ,qty numeric(2),
> primary key (part1,part2)
> )
> go
> insert pap (part1,part2,qty)
> select 100, 205, 5
> union all select 100, 204, 6
> union all select 101, 203, 7
> union all select 205, 107, 1
> union all select 107, 116, 2
> go
> --pick the last qty
> select *,(select top 1 qty from pap order by part1 desc,part2 desc)
lastqty
> from pap
> where part1=100 and part2=205
> go
> drop table pap
> go
>
> --
> -oj
>
> "M R" <rezvani@.parskhazar.net> wrote in message
> news:eCGJAfbZFHA.1412@.TK2MSFTNGP12.phx.gbl...
and
would
>|||Well, you didn't mention the child/parent relationship in your post and I
didn't bother reading each value to see the trend. So, what you're after is
the hierachy problem. Y
You want to take a look at the example in book online or use Celko's or
this.
--adding another left join
--if you need to go level deeper.
select p1.*,coalesce(p3.qty,p2.qty,p1.qty) last_qty
from pap p1 left join pap p2 on p1.part2=p2.part1
left join pap p3 on p2.part2=p3.part1
where p1.part1=100 and p1.part2=205
-oj
"M R" <rezvani@.parskhazar.net> wrote in message
news:ulcBPvlZFHA.3732@.TK2MSFTNGP10.phx.gbl...
> It doesn't matter to recognize the new record ,because if we have a
> correct
> sql statement it automatically will be fetched in the result,and your sql
> statement is not suitable
> select *,(select top 1 qty from pap order by part1 desc,part2 desc)
> lastqty
> from pap
> where part1=100 and part2=205
> cause the last qty is not the qty with the greatest part1 and part2,we
> select the qty as a last one by joining the part2 with part1 in this table
> and repeat this action to find the ast one.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:#CR18acZFHA.4088@.TK2MSFTNGP15.phx.gbl...
> lastqty
> and
> would
>

**Dynamic SQL**

Hi
1)I'm using SQl server 2000,how can I use dynamic sql to pass different
tables' name in a sql statement,I'd be thankful if you give an example.
2)I want to use the calculate field to join some columns instead of
concatination of them in different views,at first: is it a good idea?,
second: does it reduce the speed of fetching data when I'm selecting from
that table?
example: emp(emp_no,emp_name , emp_family, emp_desc)
insted of selecting emp_no + emp_name ,make emp_no + emp_name as a
calculated field.
Thanks,> 1)I'm using SQl server 2000,how can I use dynamic sql to pass different
> tables' name in a sql statement,I'd be thankful if you give an example.
Check out http://www.sommarskog.se/dynamic_sql.html before you go down that
slippery slope.

> 2)I want to use the calculate field to join some columns instead of
> concatination of them in different views,at first: is it a good idea?,
> second: does it reduce the speed of fetching data when I'm selecting from
> that table?
> example: emp(emp_no,emp_name , emp_family, emp_desc)
> insted of selecting emp_no + emp_name ,make emp_no + emp_name as a
> calculated field.
It's generally best to request columns individually and format data in the
presentation layer. This will make your application more scalable.
Hope this helps.
Dan Guzman
SQL Server MVP
"maryam rezvani" <rezvani@.parskhazar.net> wrote in message
news:%23$qrByADFHA.3688@.TK2MSFTNGP14.phx.gbl...
> Hi
> 1)I'm using SQl server 2000,how can I use dynamic sql to pass different
> tables' name in a sql statement,I'd be thankful if you give an example.
> 2)I want to use the calculate field to join some columns instead of
> concatination of them in different views,at first: is it a good idea?,
> second: does it reduce the speed of fetching data when I'm selecting from
> that table?
> example: emp(emp_no,emp_name , emp_family, emp_desc)
> insted of selecting emp_no + emp_name ,make emp_no + emp_name as a
> calculated field.
> Thanks,
>

(Select All) Checkbox As Default

All,

When creating a dynamic parameter list from a query, Reporting Services graciously provides a "(Select All)" checkbox. How can I select this checkbox by default so that when a user generates the report, all of the values are selected?

Thanks

The "select all" item is just a UI concept at this point. You could setup a default value for the report parameter that contains all values of the valid values list - but in that case all the checkboxes for individual items would be pre-selected, but the "select all" would still not be pre-selected.

-- Robert

|||Thanks for the reply. I actually tried your approach before posting my question. The problem that I am facing when using this approach is that the pre-selection is always static so this will not work when my checkbox list is populated from a query. Since the results of the query are dynamic, I can't setup a default list with any degree of accuracy.|||

Robert,

Is there any way to tell when the Select All option is selected?

R

|||

I don't know if this works

But if my dropdown source is Query, and with "Multi-Value items" checkbox checked

Under Default Values, If I choose "Query", pick the Dataset, put in the value field -> it will select all rows in the query by default

When I go to Preview, all items from the query ARE checked in the dropdown. Even the "ALL" option I created myself

(now the problem is, how do you uncheck them all with ease...)

I hope that's what you're looking for, I wish there's easy way to post pictures

|||

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

(Select All) Checkbox As Default

All,

When creating a dynamic parameter list from a query, Reporting Services graciously provides a "(Select All)" checkbox. How can I select this checkbox by default so that when a user generates the report, all of the values are selected?

Thanks

The "select all" item is just a UI concept at this point. You could setup a default value for the report parameter that contains all values of the valid values list - but in that case all the checkboxes for individual items would be pre-selected, but the "select all" would still not be pre-selected.

-- Robert

|||Thanks for the reply. I actually tried your approach before posting my question. The problem that I am facing when using this approach is that the pre-selection is always static so this will not work when my checkbox list is populated from a query. Since the results of the query are dynamic, I can't setup a default list with any degree of accuracy.|||

Robert,

Is there any way to tell when the Select All option is selected?

R

|||

I don't know if this works

But if my dropdown source is Query, and with "Multi-Value items" checkbox checked

Under Default Values, If I choose "Query", pick the Dataset, put in the value field -> it will select all rows in the query by default

When I go to Preview, all items from the query ARE checked in the dropdown. Even the "ALL" option I created myself

(now the problem is, how do you uncheck them all with ease...)

I hope that's what you're looking for, I wish there's easy way to post pictures

|||

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert

(Select All) Checkbox As Default

All,

When creating a dynamic parameter list from a query, Reporting Services graciously provides a "(Select All)" checkbox. How can I select this checkbox by default so that when a user generates the report, all of the values are selected?

Thanks

The "select all" item is just a UI concept at this point. You could setup a default value for the report parameter that contains all values of the valid values list - but in that case all the checkboxes for individual items would be pre-selected, but the "select all" would still not be pre-selected.

-- Robert

|||Thanks for the reply. I actually tried your approach before posting my question. The problem that I am facing when using this approach is that the pre-selection is always static so this will not work when my checkbox list is populated from a query. Since the results of the query are dynamic, I can't setup a default list with any degree of accuracy.|||

Robert,

Is there any way to tell when the Select All option is selected?

R

|||

I don't know if this works

But if my dropdown source is Query, and with "Multi-Value items" checkbox checked

Under Default Values, If I choose "Query", pick the Dataset, put in the value field -> it will select all rows in the query by default

When I go to Preview, all items from the query ARE checked in the dropdown. Even the "ALL" option I created myself

(now the problem is, how do you uncheck them all with ease...)

I hope that's what you're looking for, I wish there's easy way to post pictures

|||

The closest you can get is to compare the number of rows in the dataset used for the valid values list (e.g. =CountRows("ParameterDataSetName")) with the number of selected parameter values (e.g. =Parameters!P1.Count)

-- Robert