A4 printing

Hi,
I have problems with A4 printing from the RSClientPrint activex.
I defined the size of my report to 21cm x 29,7cm (the A4 format) but
when printing the printer is set on a "personalized paper" format
instead of "A4" format.
So the user need to specify the A4 format each time he need to print a
report.
When I set the format to 8,5in x 11.5in the format is correctly
detected by the printer as a "letter(8,5"x11")". So if it's possible to
make understand to the printer that the format is "letter" it could be
possible to make it understand the A4 format, but I don't see how.
For me it's the last thing I need to make Reporting Services a valid
end user reporting tool.
I try this on several printers with several reports and they all do the
same thing.
Thanks for your responses.I don't think there is really much you can do here. The ActiveX control
passes the report sizes to the printer and let's the printer driver
determine what to do with these values. For some reason the driver is not
matching the values up with the A4 paper size, but it does for 8.5 X 11.
Does it work for other paper sizes, like legal, or 11 x 17?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sébastien Guillot" <s.guillot@.gmail.com> wrote in message
news:1126536856.799335.33940@.z14g2000cwz.googlegroups.com...
> Hi,
> I have problems with A4 printing from the RSClientPrint activex.
> I defined the size of my report to 21cm x 29,7cm (the A4 format) but
> when printing the printer is set on a "personalized paper" format
> instead of "A4" format.
> So the user need to specify the A4 format each time he need to print a
> report.
> When I set the format to 8,5in x 11.5in the format is correctly
> detected by the printer as a "letter(8,5"x11")". So if it's possible to
> make understand to the printer that the format is "letter" it could be
> possible to make it understand the A4 format, but I don't see how.
> For me it's the last thing I need to make Reporting Services a valid
> end user reporting tool.
> I try this on several printers with several reports and they all do the
> same thing.
> Thanks for your responses.
>|||Thanks for your response.
I tried 8.5 x 11, it takes me Legal (8,5" x 14"),
11"x17" -> Personalized paper format
8.5"x14" -> Personalized paper format
21cm x 29.7cm -> Personalized paper format
210mm x 297mm -> Personalized paper format
As you said it depend on how the printer interpret the values.
This problem appears most of time on multifunctionnal laser printers
how can receive multiple paper formats. On inkjet printer with only A4
paper it works better.
But, I think it's possible to make understand to the printer the right
format. All the rest of applications like Word, Excel,... managed to do
this so there's no good reason for the activex to not managed to do
this.
I see while searching answers thru several forums and newsgroup that
this problem occurs to a number a person all over the world.
So, it could be usefull to have a clear answer on how we need to set
page heigth and width for the A4 format.
(Sorry for my poor english, I tried to be as clear as possible)|||Yes, it would have been possible to do paper size detection in the print
control code. We sampled several printers and each printer handled
different paper sizes correctly. We have however noted the response from
customers regarding this issue and will consider adding it in a future
release.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sébastien Guillot" <s.guillot@.gmail.com> wrote in message
news:1126594138.934122.74600@.f14g2000cwb.googlegroups.com...
> Thanks for your response.
> I tried 8.5 x 11, it takes me Legal (8,5" x 14"),
> 11"x17" -> Personalized paper format
> 8.5"x14" -> Personalized paper format
> 21cm x 29.7cm -> Personalized paper format
> 210mm x 297mm -> Personalized paper format
>
> As you said it depend on how the printer interpret the values.
> This problem appears most of time on multifunctionnal laser printers
> how can receive multiple paper formats. On inkjet printer with only A4
> paper it works better.
> But, I think it's possible to make understand to the printer the right
> format. All the rest of applications like Word, Excel,... managed to do
> this so there's no good reason for the activex to not managed to do
> this.
> I see while searching answers thru several forums and newsgroup that
> this problem occurs to a number a person all over the world.
> So, it could be usefull to have a clear answer on how we need to set
> page heigth and width for the A4 format.
> (Sorry for my poor english, I tried to be as clear as possible)
>

A strange problem with updatable partitioned view.

Hi all,
I am working on a distributed database. I defined linked
servers, partitioned views etc. I can delete/insert/update
data from the view. Now the problem is if I add more ID
ranges to the partitioning column in the check, sometimes
it worked or sometimes it didn't. See the following sample
code:
-- Create linked server SERVER0,SERVER1 on two SQL
servers.
-- SERVER0 one one machine
exec sp_addlinkedserver 'SERVER0', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC OUT', @.optvalue='TRUE'
-- SERVER1 on another machine
exec sp_addlinkedserver 'SERVER1', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC OUT', @.optvalue='TRUE'
-- Create database Test_DB on each server.
-- ON SERVER1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
[ObjectID] <= 100)
GO
-- ON Server1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 )
GO
-- ON SERVER0: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
GO
--ON SERVER1: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
01-01',1)
It succeeds
-- ON both server, drop the checks
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 )
GO
-- On server1, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 )
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
01-01',1)
It succeeds
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
GO
-- On Server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
600)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
01-01',1)
It succeeds
-- On any server,
Delete vwtest
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
[ObjectID] between 40075 and 50074)
GO
-- On server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
[ObjectID] BETWEEN 50075 and 60074)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
01-01',1)
It will fail. the error message is "UNION ALL view vwtest
is not updatable becuase a partitioning column is not
found."
I am totally lost. Anyone knows how SQL server decides one
column is a partitioning or not. Here I used the same rule
but the result is different.
Any ideas? Thanks in advance.Peter,
did not go through your detailed post. However, I bet that you did your
modification with EM. It is known that when you do such changes in EM to
updateable partitioned view the EM does not do it right. Try use QA. If it
does not work, try recreate the view in QA.
HTH
Quentin
"Peter" <phe@.Visualplant.com> wrote in message
news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> Hi all,
> I am working on a distributed database. I defined linked
> servers, partitioned views etc. I can delete/insert/update
> data from the view. Now the problem is if I add more ID
> ranges to the partitioning column in the check, sometimes
> it worked or sometimes it didn't. See the following sample
> code:
> -- Create linked server SERVER0,SERVER1 on two SQL
> servers.
> -- SERVER0 one one machine
> exec sp_addlinkedserver 'SERVER0', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- SERVER1 on another machine
> exec sp_addlinkedserver 'SERVER1', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- Create database Test_DB on each server.
> -- ON SERVER1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> [ObjectID] <= 100)
> GO
> -- ON Server1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 )
> GO
>
> -- ON SERVER0: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> GO
> --ON SERVER1: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
> 01-01',1)
> It succeeds
> -- ON both server, drop the checks
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 )
> GO
> -- On server1, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 )
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
> 01-01',1)
> It succeeds
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> GO
> -- On Server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
> 600)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
> 01-01',1)
> It succeeds
> -- On any server,
> Delete vwtest
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
> [ObjectID] between 40075 and 50074)
> GO
> -- On server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
> [ObjectID] BETWEEN 50075 and 60074)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
> 01-01',1)
> It will fail. the error message is "UNION ALL view vwtest
> is not updatable becuase a partitioning column is not
> found."
> I am totally lost. Anyone knows how SQL server decides one
> column is a partitioning or not. Here I used the same rule
> but the result is different.
>
> Any ideas? Thanks in advance.
>|||Thanks for your reply.
However, I didn't change the constraint from EM. What I
did is that drop the constraint for all servers, then
create the constraint for all servers from QA. It worked
in some cases. It seems if I have more ID ranges or I have
ID ranges with negative value, it will fail. I tried to
recreate the view, it didn't work too.
The code I posted is exactly what I ran in QA.
>--Original Message--
>Peter,
>did not go through your detailed post. However, I bet
that you did your
>modification with EM. It is known that when you do such
changes in EM to
>updateable partitioned view the EM does not do it right.
Try use QA. If it
>does not work, try recreate the view in QA.
>HTH
>Quentin
>"Peter" <phe@.Visualplant.com> wrote in message
>news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> Hi all,
>> I am working on a distributed database. I defined
linked
>> servers, partitioned views etc. I can
delete/insert/update
>> data from the view. Now the problem is if I add more ID
>> ranges to the partitioning column in the check,
sometimes
>> it worked or sometimes it didn't. See the following
sample
>> code:
>> -- Create linked server SERVER0,SERVER1 on two SQL
>> servers.
>> -- SERVER0 one one machine
>> exec sp_addlinkedserver 'SERVER0', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- SERVER1 on another machine
>> exec sp_addlinkedserver 'SERVER1', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- Create database Test_DB on each server.
>> -- ON SERVER1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> [ObjectID] <= 100)
>> GO
>> -- ON Server1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 )
>> GO
>>
>> -- ON SERVER0: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> --ON SERVER1: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(10,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(110,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server, drop the checks
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 )
>> GO
>> -- On server1, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 )
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(11,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(111,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> GO
>> -- On Server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
>= -
>> 600)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(13,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(113,'2003-
>> 01-01',1)
>> It succeeds
>> -- On any server,
>> Delete vwtest
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
or
>> [ObjectID] between 40075 and 50074)
>> GO
>> -- On server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
OR
>> [ObjectID] BETWEEN 50075 and 60074)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(17,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(117,'2003-
>> 01-01',1)
>> It will fail. the error message is "UNION ALL view
vwtest
>> is not updatable becuase a partitioning column is not
>> found."
>> I am totally lost. Anyone knows how SQL server decides
one
>> column is a partitioning or not. Here I used the same
rule
>> but the result is different.
>>
>> Any ideas? Thanks in advance.
>
>.
>|||Peter,
Oops.
I saw you used Alter Table to add the constraint. Did you try to create the
constraint together with the table creation? Try that.
Quentin
"peter" <phe@.VisualPlant.com> wrote in message
news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
> Thanks for your reply.
> However, I didn't change the constraint from EM. What I
> did is that drop the constraint for all servers, then
> create the constraint for all servers from QA. It worked
> in some cases. It seems if I have more ID ranges or I have
> ID ranges with negative value, it will fail. I tried to
> recreate the view, it didn't work too.
> The code I posted is exactly what I ran in QA.
>
> >--Original Message--
> >Peter,
> >
> >did not go through your detailed post. However, I bet
> that you did your
> >modification with EM. It is known that when you do such
> changes in EM to
> >updateable partitioned view the EM does not do it right.
> Try use QA. If it
> >does not work, try recreate the view in QA.
> >
> >HTH
> >
> >Quentin
> >
> >"Peter" <phe@.Visualplant.com> wrote in message
> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> >> Hi all,
> >>
> >> I am working on a distributed database. I defined
> linked
> >> servers, partitioned views etc. I can
> delete/insert/update
> >> data from the view. Now the problem is if I add more ID
> >> ranges to the partitioning column in the check,
> sometimes
> >> it worked or sometimes it didn't. See the following
> sample
> >> code:
> >>
> >> -- Create linked server SERVER0,SERVER1 on two SQL
> >> servers.
> >> -- SERVER0 one one machine
> >> exec sp_addlinkedserver 'SERVER0', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >> -- SERVER1 on another machine
> >> exec sp_addlinkedserver 'SERVER1', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >>
> >> -- Create database Test_DB on each server.
> >> -- ON SERVER1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> >> [ObjectID] <= 100)
> >> GO
> >>
> >> -- ON Server1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 )
> >> GO
> >>
> >>
> >> -- ON SERVER0: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> --ON SERVER1: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (10,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (110,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server, drop the checks
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 )
> >> GO
> >> -- On server1, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 )
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (11,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (111,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> >> GO
> >> -- On Server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
> >= -
> >> 600)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (13,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (113,'2003-
> >> 01-01',1)
> >> It succeeds
> >>
> >> -- On any server,
> >> Delete vwtest
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
> or
> >> [ObjectID] between 40075 and 50074)
> >> GO
> >> -- On server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
> OR
> >> [ObjectID] BETWEEN 50075 and 60074)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (17,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (117,'2003-
> >> 01-01',1)
> >>
> >> It will fail. the error message is "UNION ALL view
> vwtest
> >> is not updatable becuase a partitioning column is not
> >> found."
> >>
> >> I am totally lost. Anyone knows how SQL server decides
> one
> >> column is a partitioning or not. Here I used the same
> rule
> >> but the result is different.
> >>
> >>
> >> Any ideas? Thanks in advance.
> >>
> >
> >
> >.
> >|||The result is the same. I have other aprtitioned tables
that work well. But the partitioned column of this table
has negative IDs. I think this is the reason.
>--Original Message--
>Peter,
>Oops.
>I saw you used Alter Table to add the constraint. Did
you try to create the
>constraint together with the table creation? Try that.
>Quentin
>
>"peter" <phe@.VisualPlant.com> wrote in message
>news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
>> Thanks for your reply.
>> However, I didn't change the constraint from EM. What I
>> did is that drop the constraint for all servers, then
>> create the constraint for all servers from QA. It worked
>> in some cases. It seems if I have more ID ranges or I
have
>> ID ranges with negative value, it will fail. I tried to
>> recreate the view, it didn't work too.
>> The code I posted is exactly what I ran in QA.
>>
>> >--Original Message--
>> >Peter,
>> >
>> >did not go through your detailed post. However, I bet
>> that you did your
>> >modification with EM. It is known that when you do
such
>> changes in EM to
>> >updateable partitioned view the EM does not do it
right.
>> Try use QA. If it
>> >does not work, try recreate the view in QA.
>> >
>> >HTH
>> >
>> >Quentin
>> >
>> >"Peter" <phe@.Visualplant.com> wrote in message
>> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> >> Hi all,
>> >>
>> >> I am working on a distributed database. I defined
>> linked
>> >> servers, partitioned views etc. I can
>> delete/insert/update
>> >> data from the view. Now the problem is if I add more
ID
>> >> ranges to the partitioning column in the check,
>> sometimes
>> >> it worked or sometimes it didn't. See the following
>> sample
>> >> code:
>> >>
>> >> -- Create linked server SERVER0,SERVER1 on two SQL
>> >> servers.
>> >> -- SERVER0 one one machine
>> >> exec sp_addlinkedserver 'SERVER0', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >> -- SERVER1 on another machine
>> >> exec sp_addlinkedserver 'SERVER1', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >>
>> >> -- Create database Test_DB on each server.
>> >> -- ON SERVER1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> >> [ObjectID] <= 100)
>> >> GO
>> >>
>> >> -- ON Server1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 )
>> >> GO
>> >>
>> >>
>> >> -- ON SERVER0: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> --ON SERVER1: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (10,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (110,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server, drop the checks
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 )
>> >> GO
>> >> -- On server1, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 )
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (11,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (111,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> >> GO
>> >> -- On Server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and
[ObjectID]
>> >= -
>> >> 600)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (13,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (113,'2003-
>> >> 01-01',1)
>> >> It succeeds
>> >>
>> >> -- On any server,
>> >> Delete vwtest
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
>> or
>> >> [ObjectID] between 40075 and 50074)
>> >> GO
>> >> -- On server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and
40074
>> OR
>> >> [ObjectID] BETWEEN 50075 and 60074)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (17,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (117,'2003-
>> >> 01-01',1)
>> >>
>> >> It will fail. the error message is "UNION ALL view
>> vwtest
>> >> is not updatable becuase a partitioning column is not
>> >> found."
>> >>
>> >> I am totally lost. Anyone knows how SQL server
decides
>> one
>> >> column is a partitioning or not. Here I used the same
>> rule
>> >> but the result is different.
>> >>
>> >>
>> >> Any ideas? Thanks in advance.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I have the same problem. My view is local and I can
insert using table names, but only read using view.

A SqlDataReader is returning an int, when it should be returning a tinyint

I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:

SELECT TreatmentStatusFROM vwReferralWithAdmissionDischarge
WHERE ClientNumber = 138238AND CaseNumber = 1AND ProviderNumber = 89

The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.

Why?

If you are just reteieving one value you might want to use ExecuteScalar which is faster and has less overhead than ExecuteReader.

|||

Try this:

int numericValue = System.Convert.ToInt32(yourDataReader.GetByte(0));

Cheers

|||

humormuch:

Try this:

int numericValue = System.Convert.ToInt32(yourDataReader.GetByte(0));

Cheers

I forgot to mention, in my original post, that I was using the GetByte() method of the SqlDataReader object, but that raised the following error message:

"Specified cast is not valid"

That's why I brought up the whole thing about knowing that the column was atinyint, but that the SqlDataReader in my ASP.NET 2.0 page is returning aint value instead, which I simply donot understand. Here is the relevant code snippet:

Dim sbAs StringBuilder =New StringBuilder("SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge ")sb.Append(String.Format("WHERE ClientNumber = {0} ", lClientNumber))sb.Append(String.Format("AND CaseNumber = {0} ", byCaseNumber))sb.Append(String.Format("AND ProviderNumber = {0}", nProviderNumber))Dim cmCheckTreatmentStatusAs SqlCommand =New SqlCommand(sb.ToString(), cn)cmCheckTreatmentStatus.CommandType = CommandType.TextDim sdrCheckTreatmentStatusAs SqlDataReader = cm.ExecuteReader()sdrCheckTreatmentStatus.Read()If sdrCheckTreatmentStatus.IsDBNull(0)Then'NOOPElseDim byTreatmentStatusAs Byte = sdrCheckTreatmentStatus.GetByte(0)'other stuff occurs hereEnd If

It is the line "Dim byTreatmentStatusAs Byte = sdrCheckTreatmentStatus.GetByte(0)" which is raising the error.

|||

Never mind, I found the mistake. It was a stupid mistake on my part. I had created a SqlCommand calledcmCheckTreatmentStatus, and then ran the ExecuteReader() method on another SqlCommand I defined earlier, calledcm.

I'm sorry everyone.

|||

DoctorWho:

Never mind, I found the mistake. It was a stupid mistake on my part. I had created a SqlCommand calledcmCheckTreatmentStatus, and then ran the ExecuteReader() method on another SqlCommand I defined earlier, calledcm.

I'm sorry everyone.

No problem. I remember I have done that quite a few times...

a SQL string

Hello,
I have the following SQL given by report designer in the
SQL pane. I know I have MYSET defined however this SQL
string does not bring anything, if I remove mySet filed it
is working fine.
SELECT myTable.*
FROM myTable
WHERE (DateTimeEntered BETWEEN @.StartDate AND
@.EndDate) AND (mySet = N'"
&System.Environment.GetEnvironmentVariable("MYSET") &"')
What is problem?
Thanks,
Jim.This has to be a query expression. Try:
="SELECT myTable.* FROM myTable WHERE (DateTimeEntered BETWEEN @.StartDate
AND @.EndDate) AND (mySet = N'" &
System.Environment.GetEnvironmentVariable("MYSET") & "')"
You'll need to first type in the static SQL query (in order to populate the
fields list in fields window), run it, and then replace it with the above
query expression.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> Hello,
> I have the following SQL given by report designer in the
> SQL pane. I know I have MYSET defined however this SQL
> string does not bring anything, if I remove mySet filed it
> is working fine.
> SELECT myTable.*
> FROM myTable
> WHERE (DateTimeEntered BETWEEN @.StartDate AND
> @.EndDate) AND (mySet = N'"
> &System.Environment.GetEnvironmentVariable("MYSET") &"')
> What is problem?
> Thanks,
> Jim.
>|||Ravi, you need to tell me what you mean by query
expression. I am completely new and did not get any
training on this system yet. I run my query without mySet
part, it runs fine, and I copy-paste your string to SQL
pane and I get unable to parse query text message. Can you
give me a link that explains this case.
>--Original Message--
>This has to be a query expression. Try:
>="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
BETWEEN @.StartDate
>AND @.EndDate) AND (mySet = N'" &
>System.Environment.GetEnvironmentVariable("MYSET") & "')"
>You'll need to first type in the static SQL query (in
order to populate the
>fields list in fields window), run it, and then replace
it with the above
>query expression.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
>> Hello,
>> I have the following SQL given by report designer in the
>> SQL pane. I know I have MYSET defined however this SQL
>> string does not bring anything, if I remove mySet filed
it
>> is working fine.
>> SELECT myTable.*
>> FROM myTable
>> WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> @.EndDate) AND (mySet = N'"
>> &System.Environment.GetEnvironmentVariable("MYSET") &"')
>> What is problem?
>> Thanks,
>> Jim.
>
>.
>|||Jim-
Here are the detailed steps:
Step 1. Create the relevant dataset in report designer.
Step 2. In the report designer Data tab, switch to Generic Query Designer
(this is the fourth button from the left after the Dataset dropdown - just
press this button if it isn't in the pressed state.)
Step 3. Type in the following query text: SELECT myTable.* FROM myTable
WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
Step 4. Run the query (enter relevant values for @.StartDate AND @.EndDate
when prompted).
Step 5. Switch to report designer Layout tab. This should populate the
fields windows with relevant fields, i.e., you will see the list of fields
in the fields window.
Step 6. Go back to report designer Data tab. Switch to Generic Query
Designer if you're not already there.
Step 7. Type in the following query text (which is a query expression):
="SELECT myTable.* FROM myTable WHERE (DateTimeEntered BETWEEN @.StartDate
AND @.EndDate) AND (mySet = N'" &
System.Environment.GetEnvironmentVariable("MYSET") & "')"
The Run button in the query designer should now be disabled. You will not be
able to execute the query.
Step 8. Switch to report designer Layout tab.
Step 9. You're now ready to use the fields in any of the data-bound controls
(table, matrix, list, chart).
Please let me know if you'd like further clarification.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> Ravi, you need to tell me what you mean by query
> expression. I am completely new and did not get any
> training on this system yet. I run my query without mySet
> part, it runs fine, and I copy-paste your string to SQL
> pane and I get unable to parse query text message. Can you
> give me a link that explains this case.
> >--Original Message--
> >This has to be a query expression. Try:
> >
> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
> BETWEEN @.StartDate
> >AND @.EndDate) AND (mySet = N'" &
> >System.Environment.GetEnvironmentVariable("MYSET") & "')"
> >
> >You'll need to first type in the static SQL query (in
> order to populate the
> >fields list in fields window), run it, and then replace
> it with the above
> >query expression.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> >> Hello,
> >> I have the following SQL given by report designer in the
> >> SQL pane. I know I have MYSET defined however this SQL
> >> string does not bring anything, if I remove mySet filed
> it
> >> is working fine.
> >>
> >> SELECT myTable.*
> >> FROM myTable
> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> @.EndDate) AND (mySet = N'"
> >> &System.Environment.GetEnvironmentVariable("MYSET") &"')
> >>
> >> What is problem?
> >> Thanks,
> >> Jim.
> >>
> >
> >
> >.
> >|||Thanks Ravi. That was my problem I did not know that there
is a generic query designer. I followed your steps run my
report with Debug->Start and got the following.
. An error has occurred during report processing.
(rsProcessingAborted) Get Online Help
o Cannot set the command text for data
set 'MySQLDB'. (rsErrorSettingCommandText) Get Online Help
 Error during processing of the CommandText
expression of dataset 'MySQLDB'.
(rsQueryCommandTextProcessingError) Get Online Hel
The report is running if I do not have mySet constraint.
Do you have any suggestions?
>--Original Message--
>Jim-
>Here are the detailed steps:
>Step 1. Create the relevant dataset in report designer.
>Step 2. In the report designer Data tab, switch to
Generic Query Designer
>(this is the fourth button from the left after the
Dataset dropdown - just
>press this button if it isn't in the pressed state.)
>Step 3. Type in the following query text: SELECT
myTable.* FROM myTable
>WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
>Step 4. Run the query (enter relevant values for
@.StartDate AND @.EndDate
>when prompted).
>Step 5. Switch to report designer Layout tab. This should
populate the
>fields windows with relevant fields, i.e., you will see
the list of fields
>in the fields window.
>Step 6. Go back to report designer Data tab. Switch to
Generic Query
>Designer if you're not already there.
>Step 7. Type in the following query text (which is a
query expression):
>="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
BETWEEN @.StartDate
>AND @.EndDate) AND (mySet = N'" &
>System.Environment.GetEnvironmentVariable("MYSET") & "')"
>The Run button in the query designer should now be
disabled. You will not be
>able to execute the query.
>Step 8. Switch to report designer Layout tab.
>Step 9. You're now ready to use the fields in any of the
data-bound controls
>(table, matrix, list, chart).
>Please let me know if you'd like further clarification.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2c25101c46945$74999a60$a501280a@.phx.gbl...
>> Ravi, you need to tell me what you mean by query
>> expression. I am completely new and did not get any
>> training on this system yet. I run my query without
mySet
>> part, it runs fine, and I copy-paste your string to SQL
>> pane and I get unable to parse query text message. Can
you
>> give me a link that explains this case.
>> >--Original Message--
>> >This has to be a query expression. Try:
>> >
>> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
>> BETWEEN @.StartDate
>> >AND @.EndDate) AND (mySet = N'" &
>> >System.Environment.GetEnvironmentVariable("MYSET")
& "')"
>> >
>> >You'll need to first type in the static SQL query (in
>> order to populate the
>> >fields list in fields window), run it, and then replace
>> it with the above
>> >query expression.
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
>> >> Hello,
>> >> I have the following SQL given by report designer in
the
>> >> SQL pane. I know I have MYSET defined however this
SQL
>> >> string does not bring anything, if I remove mySet
filed
>> it
>> >> is working fine.
>> >>
>> >> SELECT myTable.*
>> >> FROM myTable
>> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> >> @.EndDate) AND (mySet = N'"
>> >> &System.Environment.GetEnvironmentVariable("MYSET")
&"')
>> >>
>> >> What is problem?
>> >> Thanks,
>> >> Jim.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||What error do you see when you include it in the query expression?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> Thanks Ravi. That was my problem I did not know that there
> is a generic query designer. I followed your steps run my
> report with Debug->Start and got the following.
> . An error has occurred during report processing.
> (rsProcessingAborted) Get Online Help
> o Cannot set the command text for data
> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online Help
>  Error during processing of the CommandText
> expression of dataset 'MySQLDB'.
> (rsQueryCommandTextProcessingError) Get Online Hel
> The report is running if I do not have mySet constraint.
> Do you have any suggestions?
> >--Original Message--
> >Jim-
> >Here are the detailed steps:
> >
> >Step 1. Create the relevant dataset in report designer.
> >Step 2. In the report designer Data tab, switch to
> Generic Query Designer
> >(this is the fourth button from the left after the
> Dataset dropdown - just
> >press this button if it isn't in the pressed state.)
> >Step 3. Type in the following query text: SELECT
> myTable.* FROM myTable
> >WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
> >Step 4. Run the query (enter relevant values for
> @.StartDate AND @.EndDate
> >when prompted).
> >Step 5. Switch to report designer Layout tab. This should
> populate the
> >fields windows with relevant fields, i.e., you will see
> the list of fields
> >in the fields window.
> >Step 6. Go back to report designer Data tab. Switch to
> Generic Query
> >Designer if you're not already there.
> >Step 7. Type in the following query text (which is a
> query expression):
> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
> BETWEEN @.StartDate
> >AND @.EndDate) AND (mySet = N'" &
> >System.Environment.GetEnvironmentVariable("MYSET") & "')"
> >The Run button in the query designer should now be
> disabled. You will not be
> >able to execute the query.
> >Step 8. Switch to report designer Layout tab.
> >Step 9. You're now ready to use the fields in any of the
> data-bound controls
> >(table, matrix, list, chart).
> >
> >Please let me know if you'd like further clarification.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> >> Ravi, you need to tell me what you mean by query
> >> expression. I am completely new and did not get any
> >> training on this system yet. I run my query without
> mySet
> >> part, it runs fine, and I copy-paste your string to SQL
> >> pane and I get unable to parse query text message. Can
> you
> >> give me a link that explains this case.
> >>
> >> >--Original Message--
> >> >This has to be a query expression. Try:
> >> >
> >> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
> >> BETWEEN @.StartDate
> >> >AND @.EndDate) AND (mySet = N'" &
> >> >System.Environment.GetEnvironmentVariable("MYSET")
> & "')"
> >> >
> >> >You'll need to first type in the static SQL query (in
> >> order to populate the
> >> >fields list in fields window), run it, and then replace
> >> it with the above
> >> >query expression.
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> >> >> Hello,
> >> >> I have the following SQL given by report designer in
> the
> >> >> SQL pane. I know I have MYSET defined however this
> SQL
> >> >> string does not bring anything, if I remove mySet
> filed
> >> it
> >> >> is working fine.
> >> >>
> >> >> SELECT myTable.*
> >> >> FROM myTable
> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> >> @.EndDate) AND (mySet = N'"
> >> >> &System.Environment.GetEnvironmentVariable("MYSET")
> &"')
> >> >>
> >> >> What is problem?
> >> >> Thanks,
> >> >> Jim.
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Ravi,
Thansk for the reply. The error is listed below in the
text, I get it in the internet explorer, I do not see any
other error around. I have
=System.Environment.GetEnvironmentVariable("MYSET") in one
of the TextBox in the header section and it is giving
error in the internet explorer, but in design view in the
report designer I get MYSET value correct. I migth have
some setting problem with he internet explorer.
Thanks,
Jim.
>--Original Message--
>What error do you see when you include it in the query
expression?
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
>> Thanks Ravi. That was my problem I did not know that
there
>> is a generic query designer. I followed your steps run
my
>> report with Debug->Start and got the following.
>> . An error has occurred during report processing.
>> (rsProcessingAborted) Get Online Help
>> o Cannot set the command text for data
>> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online
Help
>>  Error during processing of the CommandText
>> expression of dataset 'MySQLDB'.
>> (rsQueryCommandTextProcessingError) Get Online Hel
>> The report is running if I do not have mySet constraint.
>> Do you have any suggestions?
>> >--Original Message--
>> >Jim-
>> >Here are the detailed steps:
>> >
>> >Step 1. Create the relevant dataset in report designer.
>> >Step 2. In the report designer Data tab, switch to
>> Generic Query Designer
>> >(this is the fourth button from the left after the
>> Dataset dropdown - just
>> >press this button if it isn't in the pressed state.)
>> >Step 3. Type in the following query text: SELECT
>> myTable.* FROM myTable
>> >WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
>> >Step 4. Run the query (enter relevant values for
>> @.StartDate AND @.EndDate
>> >when prompted).
>> >Step 5. Switch to report designer Layout tab. This
should
>> populate the
>> >fields windows with relevant fields, i.e., you will see
>> the list of fields
>> >in the fields window.
>> >Step 6. Go back to report designer Data tab. Switch to
>> Generic Query
>> >Designer if you're not already there.
>> >Step 7. Type in the following query text (which is a
>> query expression):
>> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
>> BETWEEN @.StartDate
>> >AND @.EndDate) AND (mySet = N'" &
>> >System.Environment.GetEnvironmentVariable("MYSET")
& "')"
>> >The Run button in the query designer should now be
>> disabled. You will not be
>> >able to execute the query.
>> >Step 8. Switch to report designer Layout tab.
>> >Step 9. You're now ready to use the fields in any of
the
>> data-bound controls
>> >(table, matrix, list, chart).
>> >
>> >Please let me know if you'd like further clarification.
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
>> >> Ravi, you need to tell me what you mean by query
>> >> expression. I am completely new and did not get any
>> >> training on this system yet. I run my query without
>> mySet
>> >> part, it runs fine, and I copy-paste your string to
SQL
>> >> pane and I get unable to parse query text message.
Can
>> you
>> >> give me a link that explains this case.
>> >>
>> >> >--Original Message--
>> >> >This has to be a query expression. Try:
>> >> >
>> >> >="SELECT myTable.* FROM myTable WHERE
(DateTimeEntered
>> >> BETWEEN @.StartDate
>> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >System.Environment.GetEnvironmentVariable("MYSET")
>> & "')"
>> >> >
>> >> >You'll need to first type in the static SQL query
(in
>> >> order to populate the
>> >> >fields list in fields window), run it, and then
replace
>> >> it with the above
>> >> >query expression.
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
>> >> >> Hello,
>> >> >> I have the following SQL given by report designer
in
>> the
>> >> >> SQL pane. I know I have MYSET defined however this
>> SQL
>> >> >> string does not bring anything, if I remove mySet
>> filed
>> >> it
>> >> >> is working fine.
>> >> >>
>> >> >> SELECT myTable.*
>> >> >> FROM myTable
>> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> >> >> @.EndDate) AND (mySet = N'"
>> >> >> &System.Environment.GetEnvironmentVariable
("MYSET")
>> &"')
>> >> >>
>> >> >> What is problem?
>> >> >> Thanks,
>> >> >> Jim.
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||This is because the Expression Host is assigned "Execution" permission set,
so when it runs from Report Manager, it will not have access to the
envrionment variables. If you change the permission set for expression host
to "FullTrust" in %PROGRAMFILES%\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\rssrvpolicy.config, it will allow you to read the
environment variables. However, giving the expression host "FullTrust"
privileges is a potential security risk and not recommended. See
"Expressions Used in Reports" topic in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
for details.
That said, you'd have to use some other method to figure out the current
user. Did you already consider using global collections, specifically
User!UserID, to get the current user name? Refer to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
for details.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> Ravi,
> Thansk for the reply. The error is listed below in the
> text, I get it in the internet explorer, I do not see any
> other error around. I have
> =System.Environment.GetEnvironmentVariable("MYSET") in one
> of the TextBox in the header section and it is giving
> error in the internet explorer, but in design view in the
> report designer I get MYSET value correct. I migth have
> some setting problem with he internet explorer.
> Thanks,
> Jim.
>
> >--Original Message--
> >What error do you see when you include it in the query
> expression?
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> >> Thanks Ravi. That was my problem I did not know that
> there
> >> is a generic query designer. I followed your steps run
> my
> >> report with Debug->Start and got the following.
> >>
> >> . An error has occurred during report processing.
> >> (rsProcessingAborted) Get Online Help
> >> o Cannot set the command text for data
> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online
> Help
> >>  Error during processing of the CommandText
> >> expression of dataset 'MySQLDB'.
> >> (rsQueryCommandTextProcessingError) Get Online Hel
> >>
> >> The report is running if I do not have mySet constraint.
> >> Do you have any suggestions?
> >>
> >> >--Original Message--
> >> >Jim-
> >> >Here are the detailed steps:
> >> >
> >> >Step 1. Create the relevant dataset in report designer.
> >> >Step 2. In the report designer Data tab, switch to
> >> Generic Query Designer
> >> >(this is the fourth button from the left after the
> >> Dataset dropdown - just
> >> >press this button if it isn't in the pressed state.)
> >> >Step 3. Type in the following query text: SELECT
> >> myTable.* FROM myTable
> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND @.EndDate)
> >> >Step 4. Run the query (enter relevant values for
> >> @.StartDate AND @.EndDate
> >> >when prompted).
> >> >Step 5. Switch to report designer Layout tab. This
> should
> >> populate the
> >> >fields windows with relevant fields, i.e., you will see
> >> the list of fields
> >> >in the fields window.
> >> >Step 6. Go back to report designer Data tab. Switch to
> >> Generic Query
> >> >Designer if you're not already there.
> >> >Step 7. Type in the following query text (which is a
> >> query expression):
> >> >="SELECT myTable.* FROM myTable WHERE (DateTimeEntered
> >> BETWEEN @.StartDate
> >> >AND @.EndDate) AND (mySet = N'" &
> >> >System.Environment.GetEnvironmentVariable("MYSET")
> & "')"
> >> >The Run button in the query designer should now be
> >> disabled. You will not be
> >> >able to execute the query.
> >> >Step 8. Switch to report designer Layout tab.
> >> >Step 9. You're now ready to use the fields in any of
> the
> >> data-bound controls
> >> >(table, matrix, list, chart).
> >> >
> >> >Please let me know if you'd like further clarification.
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> >> >> Ravi, you need to tell me what you mean by query
> >> >> expression. I am completely new and did not get any
> >> >> training on this system yet. I run my query without
> >> mySet
> >> >> part, it runs fine, and I copy-paste your string to
> SQL
> >> >> pane and I get unable to parse query text message.
> Can
> >> you
> >> >> give me a link that explains this case.
> >> >>
> >> >> >--Original Message--
> >> >> >This has to be a query expression. Try:
> >> >> >
> >> >> >="SELECT myTable.* FROM myTable WHERE
> (DateTimeEntered
> >> >> BETWEEN @.StartDate
> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >System.Environment.GetEnvironmentVariable("MYSET")
> >> & "')"
> >> >> >
> >> >> >You'll need to first type in the static SQL query
> (in
> >> >> order to populate the
> >> >> >fields list in fields window), run it, and then
> replace
> >> >> it with the above
> >> >> >query expression.
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> >> >> >> Hello,
> >> >> >> I have the following SQL given by report designer
> in
> >> the
> >> >> >> SQL pane. I know I have MYSET defined however this
> >> SQL
> >> >> >> string does not bring anything, if I remove mySet
> >> filed
> >> >> it
> >> >> >> is working fine.
> >> >> >>
> >> >> >> SELECT myTable.*
> >> >> >> FROM myTable
> >> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> >> >> @.EndDate) AND (mySet = N'"
> >> >> >> &System.Environment.GetEnvironmentVariable
> ("MYSET")
> >> &"')
> >> >> >>
> >> >> >> What is problem?
> >> >> >> Thanks,
> >> >> >> Jim.
> >> >> >>
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Thanks Ravi,
I set PermissionSetName="FullTrust" for
Name="Report_Expressions_Default_Permissions"
This time I do not see anything, not even #err. I still
see my environment variable in Preview screen.
>--Original Message--
>This is because the Expression Host is
assigned "Execution" permission set,
>so when it runs from Report Manager, it will not have
access to the
>envrionment variables. If you change the permission set
for expression host
>to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
Server\MSSQL\Reporting
>Services\ReportServer\rssrvpolicy.config, it will allow
you to read the
>environment variables. However, giving the expression
host "FullTrust"
>privileges is a potential security risk and not
recommended. See
>"Expressions Used in Reports" topic in
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
>for details.
>That said, you'd have to use some other method to figure
out the current
>user. Did you already consider using global collections,
specifically
>User!UserID, to get the current user name? Refer to
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
>for details.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
>> Ravi,
>> Thansk for the reply. The error is listed below in the
>> text, I get it in the internet explorer, I do not see
any
>> other error around. I have
>> =System.Environment.GetEnvironmentVariable("MYSET") in
one
>> of the TextBox in the header section and it is giving
>> error in the internet explorer, but in design view in
the
>> report designer I get MYSET value correct. I migth have
>> some setting problem with he internet explorer.
>> Thanks,
>> Jim.
>>
>> >--Original Message--
>> >What error do you see when you include it in the query
>> expression?
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
>> >> Thanks Ravi. That was my problem I did not know that
>> there
>> >> is a generic query designer. I followed your steps
run
>> my
>> >> report with Debug->Start and got the following.
>> >>
>> >> . An error has occurred during report processing.
>> >> (rsProcessingAborted) Get Online Help
>> >> o Cannot set the command text for data
>> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online
>> Help
>> >>  Error during processing of the CommandText
>> >> expression of dataset 'MySQLDB'.
>> >> (rsQueryCommandTextProcessingError) Get Online Hel
>> >>
>> >> The report is running if I do not have mySet
constraint.
>> >> Do you have any suggestions?
>> >>
>> >> >--Original Message--
>> >> >Jim-
>> >> >Here are the detailed steps:
>> >> >
>> >> >Step 1. Create the relevant dataset in report
designer.
>> >> >Step 2. In the report designer Data tab, switch to
>> >> Generic Query Designer
>> >> >(this is the fourth button from the left after the
>> >> Dataset dropdown - just
>> >> >press this button if it isn't in the pressed state.)
>> >> >Step 3. Type in the following query text: SELECT
>> >> myTable.* FROM myTable
>> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
@.EndDate)
>> >> >Step 4. Run the query (enter relevant values for
>> >> @.StartDate AND @.EndDate
>> >> >when prompted).
>> >> >Step 5. Switch to report designer Layout tab. This
>> should
>> >> populate the
>> >> >fields windows with relevant fields, i.e., you will
see
>> >> the list of fields
>> >> >in the fields window.
>> >> >Step 6. Go back to report designer Data tab. Switch
to
>> >> Generic Query
>> >> >Designer if you're not already there.
>> >> >Step 7. Type in the following query text (which is a
>> >> query expression):
>> >> >="SELECT myTable.* FROM myTable WHERE
(DateTimeEntered
>> >> BETWEEN @.StartDate
>> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >System.Environment.GetEnvironmentVariable("MYSET")
>> & "')"
>> >> >The Run button in the query designer should now be
>> >> disabled. You will not be
>> >> >able to execute the query.
>> >> >Step 8. Switch to report designer Layout tab.
>> >> >Step 9. You're now ready to use the fields in any of
>> the
>> >> data-bound controls
>> >> >(table, matrix, list, chart).
>> >> >
>> >> >Please let me know if you'd like further
clarification.
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
>> >> >> Ravi, you need to tell me what you mean by query
>> >> >> expression. I am completely new and did not get
any
>> >> >> training on this system yet. I run my query
without
>> >> mySet
>> >> >> part, it runs fine, and I copy-paste your string
to
>> SQL
>> >> >> pane and I get unable to parse query text message.
>> Can
>> >> you
>> >> >> give me a link that explains this case.
>> >> >>
>> >> >> >--Original Message--
>> >> >> >This has to be a query expression. Try:
>> >> >> >
>> >> >> >="SELECT myTable.* FROM myTable WHERE
>> (DateTimeEntered
>> >> >> BETWEEN @.StartDate
>> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >System.Environment.GetEnvironmentVariable
("MYSET")
>> >> & "')"
>> >> >> >
>> >> >> >You'll need to first type in the static SQL query
>> (in
>> >> >> order to populate the
>> >> >> >fields list in fields window), run it, and then
>> replace
>> >> >> it with the above
>> >> >> >query expression.
>> >> >> >
>> >> >> >--
>> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >SQL Server Reporting Services
>> >> >> >
>> >> >> >This posting is provided "AS IS" with no
warranties,
>> >> and
>> >> >> confers no rights.
>> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> wrote in
>> >> >> message
>> >> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
>> >> >> >> Hello,
>> >> >> >> I have the following SQL given by report
designer
>> in
>> >> the
>> >> >> >> SQL pane. I know I have MYSET defined however
this
>> >> SQL
>> >> >> >> string does not bring anything, if I remove
mySet
>> >> filed
>> >> >> it
>> >> >> >> is working fine.
>> >> >> >>
>> >> >> >> SELECT myTable.*
>> >> >> >> FROM myTable
>> >> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate
AND
>> >> >> >> @.EndDate) AND (mySet = N'"
>> >> >> >> &System.Environment.GetEnvironmentVariable
>> ("MYSET")
>> >> &"')
>> >> >> >>
>> >> >> >> What is problem?
>> >> >> >> Thanks,
>> >> >> >> Jim.
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||I tried it on both Win2k and Win2k3 and it seems to be working with RS 2000
SP1 installed. Can you try it with a new report or on another machine and
let me know?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
> Thanks Ravi,
> I set PermissionSetName="FullTrust" for
> Name="Report_Expressions_Default_Permissions"
> This time I do not see anything, not even #err. I still
> see my environment variable in Preview screen.
>
> >--Original Message--
> >This is because the Expression Host is
> assigned "Execution" permission set,
> >so when it runs from Report Manager, it will not have
> access to the
> >envrionment variables. If you change the permission set
> for expression host
> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
> Server\MSSQL\Reporting
> >Services\ReportServer\rssrvpolicy.config, it will allow
> you to read the
> >environment variables. However, giving the expression
> host "FullTrust"
> >privileges is a potential security risk and not
> recommended. See
> >"Expressions Used in Reports" topic in
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-
> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
> >for details.
> >
> >That said, you'd have to use some other method to figure
> out the current
> >user. Did you already consider using global collections,
> specifically
> >User!UserID, to get the current user name? Refer to
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-
> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >for details.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> >> Ravi,
> >> Thansk for the reply. The error is listed below in the
> >> text, I get it in the internet explorer, I do not see
> any
> >> other error around. I have
> >> =System.Environment.GetEnvironmentVariable("MYSET") in
> one
> >> of the TextBox in the header section and it is giving
> >> error in the internet explorer, but in design view in
> the
> >> report designer I get MYSET value correct. I migth have
> >> some setting problem with he internet explorer.
> >> Thanks,
> >> Jim.
> >>
> >>
> >> >--Original Message--
> >> >What error do you see when you include it in the query
> >> expression?
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> >> >> Thanks Ravi. That was my problem I did not know that
> >> there
> >> >> is a generic query designer. I followed your steps
> run
> >> my
> >> >> report with Debug->Start and got the following.
> >> >>
> >> >> . An error has occurred during report processing.
> >> >> (rsProcessingAborted) Get Online Help
> >> >> o Cannot set the command text for data
> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get Online
> >> Help
> >> >>  Error during processing of the CommandText
> >> >> expression of dataset 'MySQLDB'.
> >> >> (rsQueryCommandTextProcessingError) Get Online Hel
> >> >>
> >> >> The report is running if I do not have mySet
> constraint.
> >> >> Do you have any suggestions?
> >> >>
> >> >> >--Original Message--
> >> >> >Jim-
> >> >> >Here are the detailed steps:
> >> >> >
> >> >> >Step 1. Create the relevant dataset in report
> designer.
> >> >> >Step 2. In the report designer Data tab, switch to
> >> >> Generic Query Designer
> >> >> >(this is the fourth button from the left after the
> >> >> Dataset dropdown - just
> >> >> >press this button if it isn't in the pressed state.)
> >> >> >Step 3. Type in the following query text: SELECT
> >> >> myTable.* FROM myTable
> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
> @.EndDate)
> >> >> >Step 4. Run the query (enter relevant values for
> >> >> @.StartDate AND @.EndDate
> >> >> >when prompted).
> >> >> >Step 5. Switch to report designer Layout tab. This
> >> should
> >> >> populate the
> >> >> >fields windows with relevant fields, i.e., you will
> see
> >> >> the list of fields
> >> >> >in the fields window.
> >> >> >Step 6. Go back to report designer Data tab. Switch
> to
> >> >> Generic Query
> >> >> >Designer if you're not already there.
> >> >> >Step 7. Type in the following query text (which is a
> >> >> query expression):
> >> >> >="SELECT myTable.* FROM myTable WHERE
> (DateTimeEntered
> >> >> BETWEEN @.StartDate
> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >System.Environment.GetEnvironmentVariable("MYSET")
> >> & "')"
> >> >> >The Run button in the query designer should now be
> >> >> disabled. You will not be
> >> >> >able to execute the query.
> >> >> >Step 8. Switch to report designer Layout tab.
> >> >> >Step 9. You're now ready to use the fields in any of
> >> the
> >> >> data-bound controls
> >> >> >(table, matrix, list, chart).
> >> >> >
> >> >> >Please let me know if you'd like further
> clarification.
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> >> >> >> Ravi, you need to tell me what you mean by query
> >> >> >> expression. I am completely new and did not get
> any
> >> >> >> training on this system yet. I run my query
> without
> >> >> mySet
> >> >> >> part, it runs fine, and I copy-paste your string
> to
> >> SQL
> >> >> >> pane and I get unable to parse query text message.
> >> Can
> >> >> you
> >> >> >> give me a link that explains this case.
> >> >> >>
> >> >> >> >--Original Message--
> >> >> >> >This has to be a query expression. Try:
> >> >> >> >
> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> (DateTimeEntered
> >> >> >> BETWEEN @.StartDate
> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >System.Environment.GetEnvironmentVariable
> ("MYSET")
> >> >> & "')"
> >> >> >> >
> >> >> >> >You'll need to first type in the static SQL query
> >> (in
> >> >> >> order to populate the
> >> >> >> >fields list in fields window), run it, and then
> >> replace
> >> >> >> it with the above
> >> >> >> >query expression.
> >> >> >> >
> >> >> >> >--
> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >SQL Server Reporting Services
> >> >> >> >
> >> >> >> >This posting is provided "AS IS" with no
> warranties,
> >> >> and
> >> >> >> confers no rights.
> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> wrote in
> >> >> >> message
> >> >> >> >news:2c50601c46934$5bd8b080$a401280a@.phx.gbl...
> >> >> >> >> Hello,
> >> >> >> >> I have the following SQL given by report
> designer
> >> in
> >> >> the
> >> >> >> >> SQL pane. I know I have MYSET defined however
> this
> >> >> SQL
> >> >> >> >> string does not bring anything, if I remove
> mySet
> >> >> filed
> >> >> >> it
> >> >> >> >> is working fine.
> >> >> >> >>
> >> >> >> >> SELECT myTable.*
> >> >> >> >> FROM myTable
> >> >> >> >> WHERE (DateTimeEntered BETWEEN @.StartDate
> AND
> >> >> >> >> @.EndDate) AND (mySet = N'"
> >> >> >> >> &System.Environment.GetEnvironmentVariable
> >> ("MYSET")
> >> >> &"')
> >> >> >> >>
> >> >> >> >> What is problem?
> >> >> >> >> Thanks,
> >> >> >> >> Jim.
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >> >.
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Ravi,
We are using Win2003 (I have only one machine that has SQL
and RS installed), I installed SP1 for RS2000 and tried
it, it did not work. I also tried =User!UserID to see if
it is comping up in IE, that works fine. I could not get
environment variable displayed in IE.
Thanks,
Jim.
>--Original Message--
>I tried it on both Win2k and Win2k3 and it seems to be
working with RS 2000
>SP1 installed. Can you try it with a new report or on
another machine and
>let me know?
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
>> Thanks Ravi,
>> I set PermissionSetName="FullTrust" for
>> Name="Report_Expressions_Default_Permissions"
>> This time I do not see anything, not even #err. I still
>> see my environment variable in Preview screen.
>>
>> >--Original Message--
>> >This is because the Expression Host is
>> assigned "Execution" permission set,
>> >so when it runs from Report Manager, it will not have
>> access to the
>> >envrionment variables. If you change the permission set
>> for expression host
>> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
>> Server\MSSQL\Reporting
>> >Services\ReportServer\rssrvpolicy.config, it will allow
>> you to read the
>> >environment variables. However, giving the expression
>> host "FullTrust"
>> >privileges is a potential security risk and not
>> recommended. See
>> >"Expressions Used in Reports" topic in
>> >http://msdn.microsoft.com/library/default.asp?
>> url=/library/en-
>> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
>> >for details.
>> >
>> >That said, you'd have to use some other method to
figure
>> out the current
>> >user. Did you already consider using global
collections,
>> specifically
>> >User!UserID, to get the current user name? Refer to
>> >http://msdn.microsoft.com/library/default.asp?
>> url=/library/en-
>> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
>> >for details.
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
>> >> Ravi,
>> >> Thansk for the reply. The error is listed below in
the
>> >> text, I get it in the internet explorer, I do not see
>> any
>> >> other error around. I have
>> >> =System.Environment.GetEnvironmentVariable("MYSET")
in
>> one
>> >> of the TextBox in the header section and it is giving
>> >> error in the internet explorer, but in design view in
>> the
>> >> report designer I get MYSET value correct. I migth
have
>> >> some setting problem with he internet explorer.
>> >> Thanks,
>> >> Jim.
>> >>
>> >>
>> >> >--Original Message--
>> >> >What error do you see when you include it in the
query
>> >> expression?
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
>> >> >> Thanks Ravi. That was my problem I did not know
that
>> >> there
>> >> >> is a generic query designer. I followed your steps
>> run
>> >> my
>> >> >> report with Debug->Start and got the following.
>> >> >>
>> >> >> . An error has occurred during report processing.
>> >> >> (rsProcessingAborted) Get Online Help
>> >> >> o Cannot set the command text for data
>> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
Online
>> >> Help
>> >> >>  Error during processing of the CommandText
>> >> >> expression of dataset 'MySQLDB'.
>> >> >> (rsQueryCommandTextProcessingError) Get Online Hel
>> >> >>
>> >> >> The report is running if I do not have mySet
>> constraint.
>> >> >> Do you have any suggestions?
>> >> >>
>> >> >> >--Original Message--
>> >> >> >Jim-
>> >> >> >Here are the detailed steps:
>> >> >> >
>> >> >> >Step 1. Create the relevant dataset in report
>> designer.
>> >> >> >Step 2. In the report designer Data tab, switch
to
>> >> >> Generic Query Designer
>> >> >> >(this is the fourth button from the left after
the
>> >> >> Dataset dropdown - just
>> >> >> >press this button if it isn't in the pressed
state.)
>> >> >> >Step 3. Type in the following query text: SELECT
>> >> >> myTable.* FROM myTable
>> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> @.EndDate)
>> >> >> >Step 4. Run the query (enter relevant values for
>> >> >> @.StartDate AND @.EndDate
>> >> >> >when prompted).
>> >> >> >Step 5. Switch to report designer Layout tab.
This
>> >> should
>> >> >> populate the
>> >> >> >fields windows with relevant fields, i.e., you
will
>> see
>> >> >> the list of fields
>> >> >> >in the fields window.
>> >> >> >Step 6. Go back to report designer Data tab.
Switch
>> to
>> >> >> Generic Query
>> >> >> >Designer if you're not already there.
>> >> >> >Step 7. Type in the following query text (which
is a
>> >> >> query expression):
>> >> >> >="SELECT myTable.* FROM myTable WHERE
>> (DateTimeEntered
>> >> >> BETWEEN @.StartDate
>> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >System.Environment.GetEnvironmentVariable
("MYSET")
>> >> & "')"
>> >> >> >The Run button in the query designer should now
be
>> >> >> disabled. You will not be
>> >> >> >able to execute the query.
>> >> >> >Step 8. Switch to report designer Layout tab.
>> >> >> >Step 9. You're now ready to use the fields in
any of
>> >> the
>> >> >> data-bound controls
>> >> >> >(table, matrix, list, chart).
>> >> >> >
>> >> >> >Please let me know if you'd like further
>> clarification.
>> >> >> >
>> >> >> >--
>> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >SQL Server Reporting Services
>> >> >> >
>> >> >> >This posting is provided "AS IS" with no
warranties,
>> >> and
>> >> >> confers no rights.
>> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> wrote in
>> >> >> message
>> >> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
>> >> >> >> Ravi, you need to tell me what you mean by
query
>> >> >> >> expression. I am completely new and did not get
>> any
>> >> >> >> training on this system yet. I run my query
>> without
>> >> >> mySet
>> >> >> >> part, it runs fine, and I copy-paste your
string
>> to
>> >> SQL
>> >> >> >> pane and I get unable to parse query text
message.
>> >> Can
>> >> >> you
>> >> >> >> give me a link that explains this case.
>> >> >> >>
>> >> >> >> >--Original Message--
>> >> >> >> >This has to be a query expression. Try:
>> >> >> >> >
>> >> >> >> >="SELECT myTable.* FROM myTable WHERE
>> >> (DateTimeEntered
>> >> >> >> BETWEEN @.StartDate
>> >> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >> >System.Environment.GetEnvironmentVariable
>> ("MYSET")
>> >> >> & "')"
>> >> >> >> >
>> >> >> >> >You'll need to first type in the static SQL
query
>> >> (in
>> >> >> >> order to populate the
>> >> >> >> >fields list in fields window), run it, and
then
>> >> replace
>> >> >> >> it with the above
>> >> >> >> >query expression.
>> >> >> >> >
>> >> >> >> >--
>> >> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >> >SQL Server Reporting Services
>> >> >> >> >
>> >> >> >> >This posting is provided "AS IS" with no
>> warranties,
>> >> >> and
>> >> >> >> confers no rights.
>> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> >> wrote in
>> >> >> >> message
>> >> >> >> >news:2c50601c46934$5bd8b080
$a401280a@.phx.gbl...
>> >> >> >> >> Hello,
>> >> >> >> >> I have the following SQL given by report
>> designer
>> >> in
>> >> >> the
>> >> >> >> >> SQL pane. I know I have MYSET defined
however
>> this
>> >> >> SQL
>> >> >> >> >> string does not bring anything, if I remove
>> mySet
>> >> >> filed
>> >> >> >> it
>> >> >> >> >> is working fine.
>> >> >> >> >>
>> >> >> >> >> SELECT myTable.*
>> >> >> >> >> FROM myTable
>> >> >> >> >> WHERE (DateTimeEntered BETWEEN
@.StartDate
>> AND
>> >> >> >> >> @.EndDate) AND (mySet = N'"
>> >> >> >> >> &System.Environment.GetEnvironmentVariable
>> >> ("MYSET")
>> >> >> &"')
>> >> >> >> >>
>> >> >> >> >> What is problem?
>> >> >> >> >> Thanks,
>> >> >> >> >> Jim.
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >.
>> >> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||If =User!UserID works for you, that would be the recommended approach.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2dbc101c46aa1$8c871b40$a401280a@.phx.gbl...
> Ravi,
> We are using Win2003 (I have only one machine that has SQL
> and RS installed), I installed SP1 for RS2000 and tried
> it, it did not work. I also tried =User!UserID to see if
> it is comping up in IE, that works fine. I could not get
> environment variable displayed in IE.
> Thanks,
> Jim.
> >--Original Message--
> >I tried it on both Win2k and Win2k3 and it seems to be
> working with RS 2000
> >SP1 installed. Can you try it with a new report or on
> another machine and
> >let me know?
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
> >> Thanks Ravi,
> >> I set PermissionSetName="FullTrust" for
> >> Name="Report_Expressions_Default_Permissions"
> >> This time I do not see anything, not even #err. I still
> >> see my environment variable in Preview screen.
> >>
> >>
> >> >--Original Message--
> >> >This is because the Expression Host is
> >> assigned "Execution" permission set,
> >> >so when it runs from Report Manager, it will not have
> >> access to the
> >> >envrionment variables. If you change the permission set
> >> for expression host
> >> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
> >> Server\MSSQL\Reporting
> >> >Services\ReportServer\rssrvpolicy.config, it will allow
> >> you to read the
> >> >environment variables. However, giving the expression
> >> host "FullTrust"
> >> >privileges is a potential security risk and not
> >> recommended. See
> >> >"Expressions Used in Reports" topic in
> >> >http://msdn.microsoft.com/library/default.asp?
> >> url=/library/en-
> >> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
> >> >for details.
> >> >
> >> >That said, you'd have to use some other method to
> figure
> >> out the current
> >> >user. Did you already consider using global
> collections,
> >> specifically
> >> >User!UserID, to get the current user name? Refer to
> >> >http://msdn.microsoft.com/library/default.asp?
> >> url=/library/en-
> >> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >> >for details.
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> >> >> Ravi,
> >> >> Thansk for the reply. The error is listed below in
> the
> >> >> text, I get it in the internet explorer, I do not see
> >> any
> >> >> other error around. I have
> >> >> =System.Environment.GetEnvironmentVariable("MYSET")
> in
> >> one
> >> >> of the TextBox in the header section and it is giving
> >> >> error in the internet explorer, but in design view in
> >> the
> >> >> report designer I get MYSET value correct. I migth
> have
> >> >> some setting problem with he internet explorer.
> >> >> Thanks,
> >> >> Jim.
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >What error do you see when you include it in the
> query
> >> >> expression?
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> >> >> >> Thanks Ravi. That was my problem I did not know
> that
> >> >> there
> >> >> >> is a generic query designer. I followed your steps
> >> run
> >> >> my
> >> >> >> report with Debug->Start and got the following.
> >> >> >>
> >> >> >> . An error has occurred during report processing.
> >> >> >> (rsProcessingAborted) Get Online Help
> >> >> >> o Cannot set the command text for data
> >> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
> Online
> >> >> Help
> >> >> >>  Error during processing of the CommandText
> >> >> >> expression of dataset 'MySQLDB'.
> >> >> >> (rsQueryCommandTextProcessingError) Get Online Hel
> >> >> >>
> >> >> >> The report is running if I do not have mySet
> >> constraint.
> >> >> >> Do you have any suggestions?
> >> >> >>
> >> >> >> >--Original Message--
> >> >> >> >Jim-
> >> >> >> >Here are the detailed steps:
> >> >> >> >
> >> >> >> >Step 1. Create the relevant dataset in report
> >> designer.
> >> >> >> >Step 2. In the report designer Data tab, switch
> to
> >> >> >> Generic Query Designer
> >> >> >> >(this is the fourth button from the left after
> the
> >> >> >> Dataset dropdown - just
> >> >> >> >press this button if it isn't in the pressed
> state.)
> >> >> >> >Step 3. Type in the following query text: SELECT
> >> >> >> myTable.* FROM myTable
> >> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> @.EndDate)
> >> >> >> >Step 4. Run the query (enter relevant values for
> >> >> >> @.StartDate AND @.EndDate
> >> >> >> >when prompted).
> >> >> >> >Step 5. Switch to report designer Layout tab.
> This
> >> >> should
> >> >> >> populate the
> >> >> >> >fields windows with relevant fields, i.e., you
> will
> >> see
> >> >> >> the list of fields
> >> >> >> >in the fields window.
> >> >> >> >Step 6. Go back to report designer Data tab.
> Switch
> >> to
> >> >> >> Generic Query
> >> >> >> >Designer if you're not already there.
> >> >> >> >Step 7. Type in the following query text (which
> is a
> >> >> >> query expression):
> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> (DateTimeEntered
> >> >> >> BETWEEN @.StartDate
> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >System.Environment.GetEnvironmentVariable
> ("MYSET")
> >> >> & "')"
> >> >> >> >The Run button in the query designer should now
> be
> >> >> >> disabled. You will not be
> >> >> >> >able to execute the query.
> >> >> >> >Step 8. Switch to report designer Layout tab.
> >> >> >> >Step 9. You're now ready to use the fields in
> any of
> >> >> the
> >> >> >> data-bound controls
> >> >> >> >(table, matrix, list, chart).
> >> >> >> >
> >> >> >> >Please let me know if you'd like further
> >> clarification.
> >> >> >> >
> >> >> >> >--
> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >SQL Server Reporting Services
> >> >> >> >
> >> >> >> >This posting is provided "AS IS" with no
> warranties,
> >> >> and
> >> >> >> confers no rights.
> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> wrote in
> >> >> >> message
> >> >> >> >news:2c25101c46945$74999a60$a501280a@.phx.gbl...
> >> >> >> >> Ravi, you need to tell me what you mean by
> query
> >> >> >> >> expression. I am completely new and did not get
> >> any
> >> >> >> >> training on this system yet. I run my query
> >> without
> >> >> >> mySet
> >> >> >> >> part, it runs fine, and I copy-paste your
> string
> >> to
> >> >> SQL
> >> >> >> >> pane and I get unable to parse query text
> message.
> >> >> Can
> >> >> >> you
> >> >> >> >> give me a link that explains this case.
> >> >> >> >>
> >> >> >> >> >--Original Message--
> >> >> >> >> >This has to be a query expression. Try:
> >> >> >> >> >
> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> >> (DateTimeEntered
> >> >> >> >> BETWEEN @.StartDate
> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >> >System.Environment.GetEnvironmentVariable
> >> ("MYSET")
> >> >> >> & "')"
> >> >> >> >> >
> >> >> >> >> >You'll need to first type in the static SQL
> query
> >> >> (in
> >> >> >> >> order to populate the
> >> >> >> >> >fields list in fields window), run it, and
> then
> >> >> replace
> >> >> >> >> it with the above
> >> >> >> >> >query expression.
> >> >> >> >> >
> >> >> >> >> >--
> >> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >> >SQL Server Reporting Services
> >> >> >> >> >
> >> >> >> >> >This posting is provided "AS IS" with no
> >> warranties,
> >> >> >> and
> >> >> >> >> confers no rights.
> >> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> >> wrote in
> >> >> >> >> message
> >> >> >> >> >news:2c50601c46934$5bd8b080
> $a401280a@.phx.gbl...
> >> >> >> >> >> Hello,
> >> >> >> >> >> I have the following SQL given by report
> >> designer
> >> >> in
> >> >> >> the
> >> >> >> >> >> SQL pane. I know I have MYSET defined
> however
> >> this
> >> >> >> SQL
> >> >> >> >> >> string does not bring anything, if I remove
> >> mySet
> >> >> >> filed
> >> >> >> >> it
> >> >> >> >> >> is working fine.
> >> >> >> >> >>
> >> >> >> >> >> SELECT myTable.*
> >> >> >> >> >> FROM myTable
> >> >> >> >> >> WHERE (DateTimeEntered BETWEEN
> @.StartDate
> >> AND
> >> >> >> >> >> @.EndDate) AND (mySet = N'"
> >> >> >> >> >> &System.Environment.GetEnvironmentVariable
> >> >> ("MYSET")
> >> >> >> &"')
> >> >> >> >> >>
> >> >> >> >> >> What is problem?
> >> >> >> >> >> Thanks,
> >> >> >> >> >> Jim.
> >> >> >> >> >>
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >.
> >> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> >.
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||I actaully need =User!UserGroupNames, if availabe, not
UserID. I could not get Active directory connection
working, Group name is actaully an environment variable,
that is why I was trying to read it, but if I could list
of group names in another way that would still work for me.
>--Original Message--
>If =User!UserID works for you, that would be the
recommended approach.
>--
>Ravi Mumulla (Microsoft)
>SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
message
>news:2dbc101c46aa1$8c871b40$a401280a@.phx.gbl...
>> Ravi,
>> We are using Win2003 (I have only one machine that has
SQL
>> and RS installed), I installed SP1 for RS2000 and tried
>> it, it did not work. I also tried =User!UserID to see if
>> it is comping up in IE, that works fine. I could not get
>> environment variable displayed in IE.
>> Thanks,
>> Jim.
>> >--Original Message--
>> >I tried it on both Win2k and Win2k3 and it seems to be
>> working with RS 2000
>> >SP1 installed. Can you try it with a new report or on
>> another machine and
>> >let me know?
>> >
>> >--
>> >Ravi Mumulla (Microsoft)
>> >SQL Server Reporting Services
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
>> >> Thanks Ravi,
>> >> I set PermissionSetName="FullTrust" for
>> >> Name="Report_Expressions_Default_Permissions"
>> >> This time I do not see anything, not even #err. I
still
>> >> see my environment variable in Preview screen.
>> >>
>> >>
>> >> >--Original Message--
>> >> >This is because the Expression Host is
>> >> assigned "Execution" permission set,
>> >> >so when it runs from Report Manager, it will not
have
>> >> access to the
>> >> >envrionment variables. If you change the permission
set
>> >> for expression host
>> >> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
>> >> Server\MSSQL\Reporting
>> >> >Services\ReportServer\rssrvpolicy.config, it will
allow
>> >> you to read the
>> >> >environment variables. However, giving the
expression
>> >> host "FullTrust"
>> >> >privileges is a potential security risk and not
>> >> recommended. See
>> >> >"Expressions Used in Reports" topic in
>> >> >http://msdn.microsoft.com/library/default.asp?
>> >> url=/library/en-
>> >> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
>> >> >for details.
>> >> >
>> >> >That said, you'd have to use some other method to
>> figure
>> >> out the current
>> >> >user. Did you already consider using global
>> collections,
>> >> specifically
>> >> >User!UserID, to get the current user name? Refer to
>> >> >http://msdn.microsoft.com/library/default.asp?
>> >> url=/library/en-
>> >> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
>> >> >for details.
>> >> >
>> >> >--
>> >> >Ravi Mumulla (Microsoft)
>> >> >SQL Server Reporting Services
>> >> >
>> >> >This posting is provided "AS IS" with no warranties,
>> and
>> >> confers no rights.
>> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
wrote in
>> >> message
>> >> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
>> >> >> Ravi,
>> >> >> Thansk for the reply. The error is listed below in
>> the
>> >> >> text, I get it in the internet explorer, I do not
see
>> >> any
>> >> >> other error around. I have
>> >> >> =System.Environment.GetEnvironmentVariable
("MYSET")
>> in
>> >> one
>> >> >> of the TextBox in the header section and it is
giving
>> >> >> error in the internet explorer, but in design
view in
>> >> the
>> >> >> report designer I get MYSET value correct. I migth
>> have
>> >> >> some setting problem with he internet explorer.
>> >> >> Thanks,
>> >> >> Jim.
>> >> >>
>> >> >>
>> >> >> >--Original Message--
>> >> >> >What error do you see when you include it in the
>> query
>> >> >> expression?
>> >> >> >
>> >> >> >--
>> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >SQL Server Reporting Services
>> >> >> >
>> >> >> >This posting is provided "AS IS" with no
warranties,
>> >> and
>> >> >> confers no rights.
>> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> wrote in
>> >> >> message
>> >> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
>> >> >> >> Thanks Ravi. That was my problem I did not know
>> that
>> >> >> there
>> >> >> >> is a generic query designer. I followed your
steps
>> >> run
>> >> >> my
>> >> >> >> report with Debug->Start and got the following.
>> >> >> >>
>> >> >> >> . An error has occurred during report
processing.
>> >> >> >> (rsProcessingAborted) Get Online Help
>> >> >> >> o Cannot set the command text for data
>> >> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
>> Online
>> >> >> Help
>> >> >> >>  Error during processing of the CommandText
>> >> >> >> expression of dataset 'MySQLDB'.
>> >> >> >> (rsQueryCommandTextProcessingError) Get Online
Hel
>> >> >> >>
>> >> >> >> The report is running if I do not have mySet
>> >> constraint.
>> >> >> >> Do you have any suggestions?
>> >> >> >>
>> >> >> >> >--Original Message--
>> >> >> >> >Jim-
>> >> >> >> >Here are the detailed steps:
>> >> >> >> >
>> >> >> >> >Step 1. Create the relevant dataset in report
>> >> designer.
>> >> >> >> >Step 2. In the report designer Data tab,
switch
>> to
>> >> >> >> Generic Query Designer
>> >> >> >> >(this is the fourth button from the left after
>> the
>> >> >> >> Dataset dropdown - just
>> >> >> >> >press this button if it isn't in the pressed
>> state.)
>> >> >> >> >Step 3. Type in the following query text:
SELECT
>> >> >> >> myTable.* FROM myTable
>> >> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
>> >> @.EndDate)
>> >> >> >> >Step 4. Run the query (enter relevant values
for
>> >> >> >> @.StartDate AND @.EndDate
>> >> >> >> >when prompted).
>> >> >> >> >Step 5. Switch to report designer Layout tab.
>> This
>> >> >> should
>> >> >> >> populate the
>> >> >> >> >fields windows with relevant fields, i.e., you
>> will
>> >> see
>> >> >> >> the list of fields
>> >> >> >> >in the fields window.
>> >> >> >> >Step 6. Go back to report designer Data tab.
>> Switch
>> >> to
>> >> >> >> Generic Query
>> >> >> >> >Designer if you're not already there.
>> >> >> >> >Step 7. Type in the following query text
(which
>> is a
>> >> >> >> query expression):
>> >> >> >> >="SELECT myTable.* FROM myTable WHERE
>> >> (DateTimeEntered
>> >> >> >> BETWEEN @.StartDate
>> >> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >> >System.Environment.GetEnvironmentVariable
>> ("MYSET")
>> >> >> & "')"
>> >> >> >> >The Run button in the query designer should
now
>> be
>> >> >> >> disabled. You will not be
>> >> >> >> >able to execute the query.
>> >> >> >> >Step 8. Switch to report designer Layout tab.
>> >> >> >> >Step 9. You're now ready to use the fields in
>> any of
>> >> >> the
>> >> >> >> data-bound controls
>> >> >> >> >(table, matrix, list, chart).
>> >> >> >> >
>> >> >> >> >Please let me know if you'd like further
>> >> clarification.
>> >> >> >> >
>> >> >> >> >--
>> >> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >> >SQL Server Reporting Services
>> >> >> >> >
>> >> >> >> >This posting is provided "AS IS" with no
>> warranties,
>> >> >> and
>> >> >> >> confers no rights.
>> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
>> >> wrote in
>> >> >> >> message
>> >> >> >> >news:2c25101c46945$74999a60
$a501280a@.phx.gbl...
>> >> >> >> >> Ravi, you need to tell me what you mean by
>> query
>> >> >> >> >> expression. I am completely new and did not
get
>> >> any
>> >> >> >> >> training on this system yet. I run my query
>> >> without
>> >> >> >> mySet
>> >> >> >> >> part, it runs fine, and I copy-paste your
>> string
>> >> to
>> >> >> SQL
>> >> >> >> >> pane and I get unable to parse query text
>> message.
>> >> >> Can
>> >> >> >> you
>> >> >> >> >> give me a link that explains this case.
>> >> >> >> >>
>> >> >> >> >> >--Original Message--
>> >> >> >> >> >This has to be a query expression. Try:
>> >> >> >> >> >
>> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
>> >> >> (DateTimeEntered
>> >> >> >> >> BETWEEN @.StartDate
>> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
>> >> >> >> >> >System.Environment.GetEnvironmentVariable
>> >> ("MYSET")
>> >> >> >> & "')"
>> >> >> >> >> >
>> >> >> >> >> >You'll need to first type in the static SQL
>> query
>> >> >> (in
>> >> >> >> >> order to populate the
>> >> >> >> >> >fields list in fields window), run it, and
>> then
>> >> >> replace
>> >> >> >> >> it with the above
>> >> >> >> >> >query expression.
>> >> >> >> >> >
>> >> >> >> >> >--
>> >> >> >> >> >Ravi Mumulla (Microsoft)
>> >> >> >> >> >SQL Server Reporting Services
>> >> >> >> >> >
>> >> >> >> >> >This posting is provided "AS IS" with no
>> >> warranties,
>> >> >> >> and
>> >> >> >> >> confers no rights.
>> >> >> >> >> >"JIM.H."
<anonymous@.discussions.microsoft.com>
>> >> >> wrote in
>> >> >> >> >> message
>> >> >> >> >> >news:2c50601c46934$5bd8b080
>> $a401280a@.phx.gbl...
>> >> >> >> >> >> Hello,
>> >> >> >> >> >> I have the following SQL given by report
>> >> designer
>> >> >> in
>> >> >> >> the
>> >> >> >> >> >> SQL pane. I know I have MYSET defined
>> however
>> >> this
>> >> >> >> SQL
>> >> >> >> >> >> string does not bring anything, if I
remove
>> >> mySet
>> >> >> >> filed
>> >> >> >> >> it
>> >> >> >> >> >> is working fine.
>> >> >> >> >> >>
>> >> >> >> >> >> SELECT myTable.*
>> >> >> >> >> >> FROM myTable
>> >> >> >> >> >> WHERE (DateTimeEntered BETWEEN
>> @.StartDate
>> >> AND
>> >> >> >> >> >> @.EndDate) AND (mySet = N'"
>> >> >> >> >> >>
&System.Environment.GetEnvironmentVariable
>> >> >> ("MYSET")
>> >> >> >> &"')
>> >> >> >> >> >>
>> >> >> >> >> >> What is problem?
>> >> >> >> >> >> Thanks,
>> >> >> >> >> >> Jim.
>> >> >> >> >> >>
>> >> >> >> >> >
>> >> >> >> >> >
>> >> >> >> >> >.
>> >> >> >> >> >
>> >> >> >> >
>> >> >> >> >
>> >> >> >> >.
>> >> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >.
>> >> >> >
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||There's no =User!UserGroupNames collection. You can use LDAP provider with a
SQL query simlar to the following to get group names:
select name from 'LDAP://DC=DomainShortName,DC=com' where
objectCategory='group'
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
news:2e69d01c46acf$d7501220$a301280a@.phx.gbl...
> I actaully need =User!UserGroupNames, if availabe, not
> UserID. I could not get Active directory connection
> working, Group name is actaully an environment variable,
> that is why I was trying to read it, but if I could list
> of group names in another way that would still work for me.
> >--Original Message--
> >If =User!UserID works for you, that would be the
> recommended approach.
> >
> >--
> >Ravi Mumulla (Microsoft)
> >SQL Server Reporting Services
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2dbc101c46aa1$8c871b40$a401280a@.phx.gbl...
> >> Ravi,
> >> We are using Win2003 (I have only one machine that has
> SQL
> >> and RS installed), I installed SP1 for RS2000 and tried
> >> it, it did not work. I also tried =User!UserID to see if
> >> it is comping up in IE, that works fine. I could not get
> >> environment variable displayed in IE.
> >> Thanks,
> >> Jim.
> >>
> >> >--Original Message--
> >> >I tried it on both Win2k and Win2k3 and it seems to be
> >> working with RS 2000
> >> >SP1 installed. Can you try it with a new report or on
> >> another machine and
> >> >let me know?
> >> >
> >> >--
> >> >Ravi Mumulla (Microsoft)
> >> >SQL Server Reporting Services
> >> >
> >> >This posting is provided "AS IS" with no warranties,
> and
> >> confers no rights.
> >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
> >> >> Thanks Ravi,
> >> >> I set PermissionSetName="FullTrust" for
> >> >> Name="Report_Expressions_Default_Permissions"
> >> >> This time I do not see anything, not even #err. I
> still
> >> >> see my environment variable in Preview screen.
> >> >>
> >> >>
> >> >> >--Original Message--
> >> >> >This is because the Expression Host is
> >> >> assigned "Execution" permission set,
> >> >> >so when it runs from Report Manager, it will not
> have
> >> >> access to the
> >> >> >envrionment variables. If you change the permission
> set
> >> >> for expression host
> >> >> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
> >> >> Server\MSSQL\Reporting
> >> >> >Services\ReportServer\rssrvpolicy.config, it will
> allow
> >> >> you to read the
> >> >> >environment variables. However, giving the
> expression
> >> >> host "FullTrust"
> >> >> >privileges is a potential security risk and not
> >> >> recommended. See
> >> >> >"Expressions Used in Reports" topic in
> >> >> >http://msdn.microsoft.com/library/default.asp?
> >> >> url=/library/en-
> >> >> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
> >> >> >for details.
> >> >> >
> >> >> >That said, you'd have to use some other method to
> >> figure
> >> >> out the current
> >> >> >user. Did you already consider using global
> >> collections,
> >> >> specifically
> >> >> >User!UserID, to get the current user name? Refer to
> >> >> >http://msdn.microsoft.com/library/default.asp?
> >> >> url=/library/en-
> >> >> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >> >> >for details.
> >> >> >
> >> >> >--
> >> >> >Ravi Mumulla (Microsoft)
> >> >> >SQL Server Reporting Services
> >> >> >
> >> >> >This posting is provided "AS IS" with no warranties,
> >> and
> >> >> confers no rights.
> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> wrote in
> >> >> message
> >> >> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> >> >> >> Ravi,
> >> >> >> Thansk for the reply. The error is listed below in
> >> the
> >> >> >> text, I get it in the internet explorer, I do not
> see
> >> >> any
> >> >> >> other error around. I have
> >> >> >> =System.Environment.GetEnvironmentVariable
> ("MYSET")
> >> in
> >> >> one
> >> >> >> of the TextBox in the header section and it is
> giving
> >> >> >> error in the internet explorer, but in design
> view in
> >> >> the
> >> >> >> report designer I get MYSET value correct. I migth
> >> have
> >> >> >> some setting problem with he internet explorer.
> >> >> >> Thanks,
> >> >> >> Jim.
> >> >> >>
> >> >> >>
> >> >> >> >--Original Message--
> >> >> >> >What error do you see when you include it in the
> >> query
> >> >> >> expression?
> >> >> >> >
> >> >> >> >--
> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >SQL Server Reporting Services
> >> >> >> >
> >> >> >> >This posting is provided "AS IS" with no
> warranties,
> >> >> and
> >> >> >> confers no rights.
> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> wrote in
> >> >> >> message
> >> >> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> >> >> >> >> Thanks Ravi. That was my problem I did not know
> >> that
> >> >> >> there
> >> >> >> >> is a generic query designer. I followed your
> steps
> >> >> run
> >> >> >> my
> >> >> >> >> report with Debug->Start and got the following.
> >> >> >> >>
> >> >> >> >> . An error has occurred during report
> processing.
> >> >> >> >> (rsProcessingAborted) Get Online Help
> >> >> >> >> o Cannot set the command text for data
> >> >> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
> >> Online
> >> >> >> Help
> >> >> >> >>  Error during processing of the CommandText
> >> >> >> >> expression of dataset 'MySQLDB'.
> >> >> >> >> (rsQueryCommandTextProcessingError) Get Online
> Hel
> >> >> >> >>
> >> >> >> >> The report is running if I do not have mySet
> >> >> constraint.
> >> >> >> >> Do you have any suggestions?
> >> >> >> >>
> >> >> >> >> >--Original Message--
> >> >> >> >> >Jim-
> >> >> >> >> >Here are the detailed steps:
> >> >> >> >> >
> >> >> >> >> >Step 1. Create the relevant dataset in report
> >> >> designer.
> >> >> >> >> >Step 2. In the report designer Data tab,
> switch
> >> to
> >> >> >> >> Generic Query Designer
> >> >> >> >> >(this is the fourth button from the left after
> >> the
> >> >> >> >> Dataset dropdown - just
> >> >> >> >> >press this button if it isn't in the pressed
> >> state.)
> >> >> >> >> >Step 3. Type in the following query text:
> SELECT
> >> >> >> >> myTable.* FROM myTable
> >> >> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
> >> >> @.EndDate)
> >> >> >> >> >Step 4. Run the query (enter relevant values
> for
> >> >> >> >> @.StartDate AND @.EndDate
> >> >> >> >> >when prompted).
> >> >> >> >> >Step 5. Switch to report designer Layout tab.
> >> This
> >> >> >> should
> >> >> >> >> populate the
> >> >> >> >> >fields windows with relevant fields, i.e., you
> >> will
> >> >> see
> >> >> >> >> the list of fields
> >> >> >> >> >in the fields window.
> >> >> >> >> >Step 6. Go back to report designer Data tab.
> >> Switch
> >> >> to
> >> >> >> >> Generic Query
> >> >> >> >> >Designer if you're not already there.
> >> >> >> >> >Step 7. Type in the following query text
> (which
> >> is a
> >> >> >> >> query expression):
> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> >> (DateTimeEntered
> >> >> >> >> BETWEEN @.StartDate
> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >> >System.Environment.GetEnvironmentVariable
> >> ("MYSET")
> >> >> >> & "')"
> >> >> >> >> >The Run button in the query designer should
> now
> >> be
> >> >> >> >> disabled. You will not be
> >> >> >> >> >able to execute the query.
> >> >> >> >> >Step 8. Switch to report designer Layout tab.
> >> >> >> >> >Step 9. You're now ready to use the fields in
> >> any of
> >> >> >> the
> >> >> >> >> data-bound controls
> >> >> >> >> >(table, matrix, list, chart).
> >> >> >> >> >
> >> >> >> >> >Please let me know if you'd like further
> >> >> clarification.
> >> >> >> >> >
> >> >> >> >> >--
> >> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >> >SQL Server Reporting Services
> >> >> >> >> >
> >> >> >> >> >This posting is provided "AS IS" with no
> >> warranties,
> >> >> >> and
> >> >> >> >> confers no rights.
> >> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> >> >> wrote in
> >> >> >> >> message
> >> >> >> >> >news:2c25101c46945$74999a60
> $a501280a@.phx.gbl...
> >> >> >> >> >> Ravi, you need to tell me what you mean by
> >> query
> >> >> >> >> >> expression. I am completely new and did not
> get
> >> >> any
> >> >> >> >> >> training on this system yet. I run my query
> >> >> without
> >> >> >> >> mySet
> >> >> >> >> >> part, it runs fine, and I copy-paste your
> >> string
> >> >> to
> >> >> >> SQL
> >> >> >> >> >> pane and I get unable to parse query text
> >> message.
> >> >> >> Can
> >> >> >> >> you
> >> >> >> >> >> give me a link that explains this case.
> >> >> >> >> >>
> >> >> >> >> >> >--Original Message--
> >> >> >> >> >> >This has to be a query expression. Try:
> >> >> >> >> >> >
> >> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> >> >> >> (DateTimeEntered
> >> >> >> >> >> BETWEEN @.StartDate
> >> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> >> >> >> >> >> >System.Environment.GetEnvironmentVariable
> >> >> ("MYSET")
> >> >> >> >> & "')"
> >> >> >> >> >> >
> >> >> >> >> >> >You'll need to first type in the static SQL
> >> query
> >> >> >> (in
> >> >> >> >> >> order to populate the
> >> >> >> >> >> >fields list in fields window), run it, and
> >> then
> >> >> >> replace
> >> >> >> >> >> it with the above
> >> >> >> >> >> >query expression.
> >> >> >> >> >> >
> >> >> >> >> >> >--
> >> >> >> >> >> >Ravi Mumulla (Microsoft)
> >> >> >> >> >> >SQL Server Reporting Services
> >> >> >> >> >> >
> >> >> >> >> >> >This posting is provided "AS IS" with no
> >> >> warranties,
> >> >> >> >> and
> >> >> >> >> >> confers no rights.
> >> >> >> >> >> >"JIM.H."
> <anonymous@.discussions.microsoft.com>
> >> >> >> wrote in
> >> >> >> >> >> message
> >> >> >> >> >> >news:2c50601c46934$5bd8b080
> >> $a401280a@.phx.gbl...
> >> >> >> >> >> >> Hello,
> >> >> >> >> >> >> I have the following SQL given by report
> >> >> designer
> >> >> >> in
> >> >> >> >> the
> >> >> >> >> >> >> SQL pane. I know I have MYSET defined
> >> however
> >> >> this
> >> >> >> >> SQL
> >> >> >> >> >> >> string does not bring anything, if I
> remove
> >> >> mySet
> >> >> >> >> filed
> >> >> >> >> >> it
> >> >> >> >> >> >> is working fine.
> >> >> >> >> >> >>
> >> >> >> >> >> >> SELECT myTable.*
> >> >> >> >> >> >> FROM myTable
> >> >> >> >> >> >> WHERE (DateTimeEntered BETWEEN
> >> @.StartDate
> >> >> AND
> >> >> >> >> >> >> @.EndDate) AND (mySet = N'"
> >> >> >> >> >> >>
> &System.Environment.GetEnvironmentVariable
> >> >> >> ("MYSET")
> >> >> >> >> &"')
> >> >> >> >> >> >>
> >> >> >> >> >> >> What is problem?
> >> >> >> >> >> >> Thanks,
> >> >> >> >> >> >> Jim.
> >> >> >> >> >> >>
> >> >> >> >> >> >
> >> >> >> >> >> >
> >> >> >> >> >> >.
> >> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >
> >> >> >> >> >.
> >> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> >.
> >> >> >> >
> >> >> >
> >> >> >
> >> >> >.
> >> >> >
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||OK, I figured out a hack for you. Try this:
1. Pull up report designer.
2. Create a dataset pointing to the master database (or any other database)
on the server on which you want to read the environment variable.
3. Paste the following query in data view: master..xp_cmdshell 'echo
%MYSET%'
4. Run the query and verify that it is returning the expected value. The
column header will show up as "Output"
5. Go ot the fields window and create a database field (Fields Window ->
Right-click -> Add... button).
6. Type in "output" for Name and "output" for database field.
7. Go to layout view and drop a textbox on the design surface.
8. Type in the following expression in the textbox: =First(Fields!Output.Value, "master")
9. Preview the report. It should show you the value of %MYSET% env.
variable.
10. Deploy the report and view it in report manager. It should show you the
value of %MYSET% env. variable.
This does not require you to give the expression host FullTrust permisson
set.
Please let me know if this works for you.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23Fd7pPtaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> There's no =User!UserGroupNames collection. You can use LDAP provider with
a
> SQL query simlar to the following to get group names:
> select name from 'LDAP://DC=DomainShortName,DC=com' where
> objectCategory='group'
>
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "JIM.H." <anonymous@.discussions.microsoft.com> wrote in message
> news:2e69d01c46acf$d7501220$a301280a@.phx.gbl...
> >
> > I actaully need =User!UserGroupNames, if availabe, not
> > UserID. I could not get Active directory connection
> > working, Group name is actaully an environment variable,
> > that is why I was trying to read it, but if I could list
> > of group names in another way that would still work for me.
> >
> > >--Original Message--
> > >If =User!UserID works for you, that would be the
> > recommended approach.
> > >
> > >--
> > >Ravi Mumulla (Microsoft)
> > >SQL Server Reporting Services
> > >
> > >This posting is provided "AS IS" with no warranties, and
> > confers no rights.
> > >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:2dbc101c46aa1$8c871b40$a401280a@.phx.gbl...
> > >> Ravi,
> > >> We are using Win2003 (I have only one machine that has
> > SQL
> > >> and RS installed), I installed SP1 for RS2000 and tried
> > >> it, it did not work. I also tried =User!UserID to see if
> > >> it is comping up in IE, that works fine. I could not get
> > >> environment variable displayed in IE.
> > >> Thanks,
> > >> Jim.
> > >>
> > >> >--Original Message--
> > >> >I tried it on both Win2k and Win2k3 and it seems to be
> > >> working with RS 2000
> > >> >SP1 installed. Can you try it with a new report or on
> > >> another machine and
> > >> >let me know?
> > >> >
> > >> >--
> > >> >Ravi Mumulla (Microsoft)
> > >> >SQL Server Reporting Services
> > >> >
> > >> >This posting is provided "AS IS" with no warranties,
> > and
> > >> confers no rights.
> > >> >"JIM.H." <anonymous@.discussions.microsoft.com> wrote in
> > >> message
> > >> >news:2ceb701c46a83$7b33d130$a601280a@.phx.gbl...
> > >> >> Thanks Ravi,
> > >> >> I set PermissionSetName="FullTrust" for
> > >> >> Name="Report_Expressions_Default_Permissions"
> > >> >> This time I do not see anything, not even #err. I
> > still
> > >> >> see my environment variable in Preview screen.
> > >> >>
> > >> >>
> > >> >> >--Original Message--
> > >> >> >This is because the Expression Host is
> > >> >> assigned "Execution" permission set,
> > >> >> >so when it runs from Report Manager, it will not
> > have
> > >> >> access to the
> > >> >> >envrionment variables. If you change the permission
> > set
> > >> >> for expression host
> > >> >> >to "FullTrust" in %PROGRAMFILES%\Microsoft SQL
> > >> >> Server\MSSQL\Reporting
> > >> >> >Services\ReportServer\rssrvpolicy.config, it will
> > allow
> > >> >> you to read the
> > >> >> >environment variables. However, giving the
> > expression
> > >> >> host "FullTrust"
> > >> >> >privileges is a potential security risk and not
> > >> >> recommended. See
> > >> >> >"Expressions Used in Reports" topic in
> > >> >> >http://msdn.microsoft.com/library/default.asp?
> > >> >> url=/library/en-
> > >> >> us/RSPROG/htm/rsp_prog_extend_install_8j8x.asp
> > >> >> >for details.
> > >> >> >
> > >> >> >That said, you'd have to use some other method to
> > >> figure
> > >> >> out the current
> > >> >> >user. Did you already consider using global
> > >> collections,
> > >> >> specifically
> > >> >> >User!UserID, to get the current user name? Refer to
> > >> >> >http://msdn.microsoft.com/library/default.asp?
> > >> >> url=/library/en-
> > >> >> us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> > >> >> >for details.
> > >> >> >
> > >> >> >--
> > >> >> >Ravi Mumulla (Microsoft)
> > >> >> >SQL Server Reporting Services
> > >> >> >
> > >> >> >This posting is provided "AS IS" with no warranties,
> > >> and
> > >> >> confers no rights.
> > >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> > wrote in
> > >> >> message
> > >> >> >news:2cb5301c469ae$75bc7ed0$a401280a@.phx.gbl...
> > >> >> >> Ravi,
> > >> >> >> Thansk for the reply. The error is listed below in
> > >> the
> > >> >> >> text, I get it in the internet explorer, I do not
> > see
> > >> >> any
> > >> >> >> other error around. I have
> > >> >> >> =System.Environment.GetEnvironmentVariable
> > ("MYSET")
> > >> in
> > >> >> one
> > >> >> >> of the TextBox in the header section and it is
> > giving
> > >> >> >> error in the internet explorer, but in design
> > view in
> > >> >> the
> > >> >> >> report designer I get MYSET value correct. I migth
> > >> have
> > >> >> >> some setting problem with he internet explorer.
> > >> >> >> Thanks,
> > >> >> >> Jim.
> > >> >> >>
> > >> >> >>
> > >> >> >> >--Original Message--
> > >> >> >> >What error do you see when you include it in the
> > >> query
> > >> >> >> expression?
> > >> >> >> >
> > >> >> >> >--
> > >> >> >> >Ravi Mumulla (Microsoft)
> > >> >> >> >SQL Server Reporting Services
> > >> >> >> >
> > >> >> >> >This posting is provided "AS IS" with no
> > warranties,
> > >> >> and
> > >> >> >> confers no rights.
> > >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> > >> wrote in
> > >> >> >> message
> > >> >> >> >news:2cc6b01c46999$51fa2fc0$a501280a@.phx.gbl...
> > >> >> >> >> Thanks Ravi. That was my problem I did not know
> > >> that
> > >> >> >> there
> > >> >> >> >> is a generic query designer. I followed your
> > steps
> > >> >> run
> > >> >> >> my
> > >> >> >> >> report with Debug->Start and got the following.
> > >> >> >> >>
> > >> >> >> >> . An error has occurred during report
> > processing.
> > >> >> >> >> (rsProcessingAborted) Get Online Help
> > >> >> >> >> o Cannot set the command text for data
> > >> >> >> >> set 'MySQLDB'. (rsErrorSettingCommandText) Get
> > >> Online
> > >> >> >> Help
> > >> >> >> >>  Error during processing of the CommandText
> > >> >> >> >> expression of dataset 'MySQLDB'.
> > >> >> >> >> (rsQueryCommandTextProcessingError) Get Online
> > Hel
> > >> >> >> >>
> > >> >> >> >> The report is running if I do not have mySet
> > >> >> constraint.
> > >> >> >> >> Do you have any suggestions?
> > >> >> >> >>
> > >> >> >> >> >--Original Message--
> > >> >> >> >> >Jim-
> > >> >> >> >> >Here are the detailed steps:
> > >> >> >> >> >
> > >> >> >> >> >Step 1. Create the relevant dataset in report
> > >> >> designer.
> > >> >> >> >> >Step 2. In the report designer Data tab,
> > switch
> > >> to
> > >> >> >> >> Generic Query Designer
> > >> >> >> >> >(this is the fourth button from the left after
> > >> the
> > >> >> >> >> Dataset dropdown - just
> > >> >> >> >> >press this button if it isn't in the pressed
> > >> state.)
> > >> >> >> >> >Step 3. Type in the following query text:
> > SELECT
> > >> >> >> >> myTable.* FROM myTable
> > >> >> >> >> >WHERE (DateTimeEntered BETWEEN @.StartDate AND
> > >> >> @.EndDate)
> > >> >> >> >> >Step 4. Run the query (enter relevant values
> > for
> > >> >> >> >> @.StartDate AND @.EndDate
> > >> >> >> >> >when prompted).
> > >> >> >> >> >Step 5. Switch to report designer Layout tab.
> > >> This
> > >> >> >> should
> > >> >> >> >> populate the
> > >> >> >> >> >fields windows with relevant fields, i.e., you
> > >> will
> > >> >> see
> > >> >> >> >> the list of fields
> > >> >> >> >> >in the fields window.
> > >> >> >> >> >Step 6. Go back to report designer Data tab.
> > >> Switch
> > >> >> to
> > >> >> >> >> Generic Query
> > >> >> >> >> >Designer if you're not already there.
> > >> >> >> >> >Step 7. Type in the following query text
> > (which
> > >> is a
> > >> >> >> >> query expression):
> > >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> > >> >> (DateTimeEntered
> > >> >> >> >> BETWEEN @.StartDate
> > >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> > >> >> >> >> >System.Environment.GetEnvironmentVariable
> > >> ("MYSET")
> > >> >> >> & "')"
> > >> >> >> >> >The Run button in the query designer should
> > now
> > >> be
> > >> >> >> >> disabled. You will not be
> > >> >> >> >> >able to execute the query.
> > >> >> >> >> >Step 8. Switch to report designer Layout tab.
> > >> >> >> >> >Step 9. You're now ready to use the fields in
> > >> any of
> > >> >> >> the
> > >> >> >> >> data-bound controls
> > >> >> >> >> >(table, matrix, list, chart).
> > >> >> >> >> >
> > >> >> >> >> >Please let me know if you'd like further
> > >> >> clarification.
> > >> >> >> >> >
> > >> >> >> >> >--
> > >> >> >> >> >Ravi Mumulla (Microsoft)
> > >> >> >> >> >SQL Server Reporting Services
> > >> >> >> >> >
> > >> >> >> >> >This posting is provided "AS IS" with no
> > >> warranties,
> > >> >> >> and
> > >> >> >> >> confers no rights.
> > >> >> >> >> >"JIM.H." <anonymous@.discussions.microsoft.com>
> > >> >> wrote in
> > >> >> >> >> message
> > >> >> >> >> >news:2c25101c46945$74999a60
> > $a501280a@.phx.gbl...
> > >> >> >> >> >> Ravi, you need to tell me what you mean by
> > >> query
> > >> >> >> >> >> expression. I am completely new and did not
> > get
> > >> >> any
> > >> >> >> >> >> training on this system yet. I run my query
> > >> >> without
> > >> >> >> >> mySet
> > >> >> >> >> >> part, it runs fine, and I copy-paste your
> > >> string
> > >> >> to
> > >> >> >> SQL
> > >> >> >> >> >> pane and I get unable to parse query text
> > >> message.
> > >> >> >> Can
> > >> >> >> >> you
> > >> >> >> >> >> give me a link that explains this case.
> > >> >> >> >> >>
> > >> >> >> >> >> >--Original Message--
> > >> >> >> >> >> >This has to be a query expression. Try:
> > >> >> >> >> >> >
> > >> >> >> >> >> >="SELECT myTable.* FROM myTable WHERE
> > >> >> >> (DateTimeEntered
> > >> >> >> >> >> BETWEEN @.StartDate
> > >> >> >> >> >> >AND @.EndDate) AND (mySet = N'" &
> > >> >> >> >> >> >System.Environment.GetEnvironmentVariable
> > >> >> ("MYSET")
> > >> >> >> >> & "')"
> > >> >> >> >> >> >
> > >> >> >> >> >> >You'll need to first type in the static SQL
> > >> query
> > >> >> >> (in
> > >> >> >> >> >> order to populate the
> > >> >> >> >> >> >fields list in fields window), run it, and
> > >> then
> > >> >> >> replace
> > >> >> >> >> >> it with the above
> > >> >> >> >> >> >query expression.
> > >> >> >> >> >> >
> > >> >> >> >> >> >--
> > >> >> >> >> >> >Ravi Mumulla (Microsoft)
> > >> >> >> >> >> >SQL Server Reporting Services
> > >> >> >> >> >> >
> > >> >> >> >> >> >This posting is provided "AS IS" with no
> > >> >> warranties,
> > >> >> >> >> and
> > >> >> >> >> >> confers no rights.
> > >> >> >> >> >> >"JIM.H."
> > <anonymous@.discussions.microsoft.com>
> > >> >> >> wrote in
> > >> >> >> >> >> message
> > >> >> >> >> >> >news:2c50601c46934$5bd8b080
> > >> $a401280a@.phx.gbl...
> > >> >> >> >> >> >> Hello,
> > >> >> >> >> >> >> I have the following SQL given by report
> > >> >> designer
> > >> >> >> in
> > >> >> >> >> the
> > >> >> >> >> >> >> SQL pane. I know I have MYSET defined
> > >> however
> > >> >> this
> > >> >> >> >> SQL
> > >> >> >> >> >> >> string does not bring anything, if I
> > remove
> > >> >> mySet
> > >> >> >> >> filed
> > >> >> >> >> >> it
> > >> >> >> >> >> >> is working fine.
> > >> >> >> >> >> >>
> > >> >> >> >> >> >> SELECT myTable.*
> > >> >> >> >> >> >> FROM myTable
> > >> >> >> >> >> >> WHERE (DateTimeEntered BETWEEN
> > >> @.StartDate
> > >> >> AND
> > >> >> >> >> >> >> @.EndDate) AND (mySet = N'"
> > >> >> >> >> >> >>
> > &System.Environment.GetEnvironmentVariable
> > >> >> >> ("MYSET")
> > >> >> >> >> &"')
> > >> >> >> >> >> >>
> > >> >> >> >> >> >> What is problem?
> > >> >> >> >> >> >> Thanks,
> > >> >> >> >> >> >> Jim.
> > >> >> >> >> >> >>
> > >> >> >> >> >> >
> > >> >> >> >> >> >
> > >> >> >> >> >> >.
> > >> >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >.
> > >> >> >> >> >
> > >> >> >> >
> > >> >> >> >
> > >> >> >> >.
> > >> >> >> >
> > >> >> >
> > >> >> >
> > >> >> >.
> > >> >> >
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>