Able to connect via local host but not server

I have a page that connects to SQL server. It works fine when running in local host but when I publish to the server, I get the following:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Any ideas on where to start?

Thanks,

Mark

Try these two blog posts:

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

AARGGHH Fulltext indexing woes...

We're feverishly in QA mode here getting ready to rollout production to
SQL2K5...
There is this one query which is returning the following error:
Msg 7617, Level 16, State 1, Line 1
Query does not reference the full-text indexed table or indexed view.
I'm really at a lost here. What is wrong with this first query'
I don't actually do select tblStock.* in the production version but it
succeeds/fails consistently either way so to shorten it up....
Here is the DDL used to create the catalog...
------
CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
AUTHORIZATION dbo;
CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc, strImagePath,
strSampleMedia, strCreators) KEY INDEX PK_tblStock;
ALTER FULLTEXT INDEX ON tblStock ENABLE;
------
Here is the query that does NOT work (followed by one that does...)
SELECT DISTINCT tblStock.*
FROM dbo.tblStock LEFT OUTER JOIN
dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK = dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
dbo.tblPriceFile ON dbo.tblStock.lngStockPK = dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
dbo.tblCategories ON
dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
LEFT OUTER JOIN
dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK = dbo.tblToolTypes.lngToolTypePK
WHERE (dbo.tblPriceFile.dtmDateApplied = (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
(dbo.tblStock.blnActiveItem = 1)
and freetext(tblStock.*,'marriage')
----
(the next one works)
select distinct * from tblStock where freetext(tblStock.*,'marriage')
----I've discovered that the joins are what cause this. As soon as I add any
join to the simple query below that is working, it then fails.
HELP!!! Why is this?
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
> We're feverishly in QA mode here getting ready to rollout production to
> SQL2K5...
> There is this one query which is returning the following error:
> Msg 7617, Level 16, State 1, Line 1
> Query does not reference the full-text indexed table or indexed view.
> I'm really at a lost here. What is wrong with this first query'
> I don't actually do select tblStock.* in the production version but it
> succeeds/fails consistently either way so to shorten it up....
> Here is the DDL used to create the catalog...
> ------
> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
> AUTHORIZATION dbo;
> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
> ALTER FULLTEXT INDEX ON tblStock ENABLE;
> ------
> Here is the query that does NOT work (followed by one that does...)
>
> SELECT DISTINCT tblStock.*
> FROM dbo.tblStock LEFT OUTER JOIN
> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK => dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
> dbo.tblPriceFile ON dbo.tblStock.lngStockPK => dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
> dbo.tblCategories ON
> dbo.tblCategorizedProducts.lngCategoryFK = dbo.tblCategories.lngCategoryPK
> LEFT OUTER JOIN
> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK => dbo.tblToolTypes.lngToolTypePK
> WHERE (dbo.tblPriceFile.dtmDateApplied => (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
> (dbo.tblStock.blnActiveItem = 1)
> and freetext(tblStock.*,'marriage')
> ----
> (the next one works)
> select distinct * from tblStock where freetext(tblStock.*,'marriage')
> ----
>
>
>|||Hmmm...we found changing the freetext(tblStock.* to be owner qualified
fixed the problem....
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23XNPPrXlGHA.3776@.TK2MSFTNGP03.phx.gbl...
> I've discovered that the joins are what cause this. As soon as I add any
> join to the simple query below that is working, it then fails.
> HELP!!! Why is this?
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uKsP6iXlGHA.4792@.TK2MSFTNGP02.phx.gbl...
>> We're feverishly in QA mode here getting ready to rollout production to
>> SQL2K5...
>> There is this one query which is returning the following error:
>> Msg 7617, Level 16, State 1, Line 1
>> Query does not reference the full-text indexed table or indexed view.
>> I'm really at a lost here. What is wrong with this first query'
>> I don't actually do select tblStock.* in the production version but it
>> succeeds/fails consistently either way so to shorten it up....
>> Here is the DDL used to create the catalog...
>> ------
>> CREATE FULLTEXT CATALOG ftStockSearch IN PATH 'c:\ftcatalogs' AS DEFAULT
>> AUTHORIZATION dbo;
>> CREATE FULLTEXT INDEX ON tblStock (strSKU, strTitle, strDesc,
>> strImagePath, strSampleMedia, strCreators) KEY INDEX PK_tblStock;
>> ALTER FULLTEXT INDEX ON tblStock ENABLE;
>> ------
>> Here is the query that does NOT work (followed by one that does...)
>>
>> SELECT DISTINCT tblStock.*
>> FROM dbo.tblStock LEFT OUTER JOIN
>> dbo.tblCategorizedProducts ON dbo.tblStock.lngStockPK =>> dbo.tblCategorizedProducts.lngStockFK LEFT OUTER JOIN
>> dbo.tblPriceFile ON dbo.tblStock.lngStockPK =>> dbo.tblPriceFile.lngStockFK LEFT OUTER JOIN
>> dbo.tblCategories ON
>> dbo.tblCategorizedProducts.lngCategoryFK =>> dbo.tblCategories.lngCategoryPK LEFT OUTER JOIN
>> dbo.tblToolTypes ON dbo.tblStock.lngToolTypeFK =>> dbo.tblToolTypes.lngToolTypePK
>> WHERE (dbo.tblPriceFile.dtmDateApplied =>> (SELECT MAX(dtmDateApplied) FROM tblPriceFile WHERE
>> lngSTockFK = lngStockPK AND getDate() >= dtmDateApplied)) AND
>> (dbo.tblStock.blnActiveItem = 1)
>> and freetext(tblStock.*,'marriage')
>> ----
>> (the next one works)
>> select distinct * from tblStock where freetext(tblStock.*,'marriage')
>> ----
>>
>>
>sql

A, B and C are the same but at least D or E are different (was "noob question")

hi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DBhi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DB

is this wat u wanted ?
Select A,B,C,D,E From Table1 Where Exists (Select 1 from Table1 Where (A=B and B=C) and (( A <>E) or (A <>D)))

Gurus, please comment|||I'm afraid your query won't work :o

Firstly, Exists isn't necessary. Secondly, if the condition is true for any of the rows it will display all the rows (the exists statement is not corrolated with the main query). Minor but SELECT * is recommended within an exists statement as the optimser will then chose the best index for the job (doubt it would make much difference here but I like to use it by habit).

Nearly there - a slight reworking:
Select A,B,C,D,E
From Table1
Where A=B and B=C and (A <> E or A <> D)|||Select A,B,C,D,E From Test
Where A=B
and A=C
and ((A=D or A=E)
or D<>E)

hope this works for u.|||Thanks for the suggessions|||Here's some NZDF code that will do what you've asked for:CREATE TABLE g11DB (
A INT
, B INT
, C INT
, D INT
, E INT
)

INSERT INTO g11DB (
A, B, C, D, E)
SELECT 1, 2, 3, 4, 5 UNION ALL
SELECT 1, 1, 1, 4, 5 UNION ALL
SELECT 1, 1, 1, 1, 5 UNION ALL
SELECT 1, 1, 1, 4, 1 UNION ALL
SELECT 1, 2, 1, 4, 1

SELECT *
FROM g11DB
WHERE 0 = A - B
AND A = C
AND A != D
UNION SELECT *
FROM g11DB
WHERE A = B
AND -A = -C
AND 0 != (A - E)-PatP|||Always the skeptic, Pat.|||Thanks guys but it seems i didn't properly state what I wanted... The code I was looking for is something like this (although i'm still wondering if there's some more efficient version...
SELECT IPA.*
FROM dbo.IRS_PAYMENT IPA INNER JOIN
dbo.IRS_PAYMENT IPB ON IPA.ip_employee_id = IPB.ip_employee_id
AND IPA.ip_paydate = IPB.ip_paydate
AND IPA.ip_pay_indicator = IPB.ip_pay_indicator
AND (NOT (IPA.ip_total_tax = IPB.ip_total_tax)
OR NOT (IPA.ip_net_pay = IPA.ip_net_pay))
ORDER BY IPA.ip_employee_id

what's NSDF stand for btw? :)|||How about:SELECT A.*
FROM dbo.IRS_PAYMENT AS A
INNER JOIN dbo.IRS_PAYMENT AS B
ON (B.ip_employee_id = A.ip_employee_id
AND B.ip_paydate = A.ip_paydate
AND B.ip_pay_indicator = A.ip_pay_indicator)
WHERE B.ip_total_tax != A.ip_total_tax
OR B.ip_net_pay != A.ip_net_pay
ORDER BY A.ip_employee_id, A.ip_paydate, A.ip_pay_indicator-PatP

a very very strange sql bug (?)

I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId = View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
FelixI often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause.
> Bojidar Alexandrov

A very beginer question: error in creating table

I am new in T-SQL. I have just create a empty database and would like to create some table in it. So I type the following in SQL Query Analyzer:

CREATE TABLE mydatabasename.mytablename (

some code here
)
But when execute, it give me an error from the first line "Specified owner name 'mydatabasename' either does not exist or you do not have permission to use it."

when I remove mydatabasename it inserts the table in to the master database, not in my database.

How can I insert the table into my database? Should I use SQL Query Analyzer or use others program? I 've tried to use the command "create table" in enterprise manager but it immediately give another error, some what like "could not use empty column name ... "
Seem to be too simple question, but I am really appreciate your helpWhen you use the SQL Query Analyzer do you choose from the dropdown your newly created Database?|||CREATE TABLE mydatabasename.mytablename

should be

CREATE TABLE mydatabasename.dbo.mytablename

A fully qualified name is
servername.databasename.owner.object

You can't skip one like you're doing. You can omit them from left to right, but can't skip a part.|||Thanks all. I will try both, select the newly database and use databasename.dbo.tablename.

Infact, I use to think about using "databasename.dbo.tablename" but then think it 's ridiculous for a try. What a mistake. It's my because my poor English make me sometime not understand the full syntax.

Thank you.

A very basic Q

This is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded
MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.

<%@. Import Namespace="System.Data.SqlClient" %
<Script Runat="Server"
Sub Page_Load
Dim conPubs As SqlConnection
conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" )
conPubs.Open()
End Sub
</Script>

Connection Opened!

Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?

Thanks for the help.when you installed your instance of msde- did you use the username SA or did you use the username webuser?

if you used the username webuser- then you have done just fine...

A truncation occurred during evaluation of the expression

First of all, I get the following error message for one of my packages which uses user variables:

SSIS package "UsageAnalysis.dtsx" starting.
Information: 0x4004300A at Perform xmlState Shredding, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001700E at UsageAnalysis: A truncation occurred during evaluation of the expression.
Error: 0xC0019004 at UsageAnalysis: The expression for variable "GetAnalysisData" failed evaluation. There was an error in the expression.
Error: 0xC02020E9 at Update Analysis Table, UsageAnalysis Source [1]: Accessing variable "User::GetAnalysisData" failed with error code 0xC001700E.
Error: 0xC0024107 at Update Analysis Table: There were errors during task validation.
Warning: 0x80019002 at Usage Analysis Process: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "UsageAnalysis.dtsx" finished: Failure.

Now my package has the following variables:

GetMaxUsageID: scope package level, type string, statement SELECT MAX(UsageID) AS MaxUsageID FROM XX.XXX

MaxUsageID: scope package level, type int32, default value 0, value get assigned from the following statement executed from sql task that runs GetMaxUsageID variable as above

GetAnalysisData: scope package level, type string, Evaluate as Expression

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 8, 1252) @.[User::MaxUsageID]

The package has worked fine until MaxUsageID value reached to 10,00,000 and since then I have been getting above mentioned error message. The problematic step is related to Data Flow task where I use GetAnalysisData. I have tried replacing user variable with literal as follows

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

the error message stays the same. Please note that package has worked fine before and it still works ok if I don't use user variables. Obviously, some of you would see eliminating user variables as workaround but I would appreciate if cause of that error message could be investigated.

Thanks,

Asaf

Are you sure MAX(UsageID) is 1,000,000 and not 100,000,000?

Try making your cast a longer length in the GetAnalysisData variable. Perhaps: (DT_STR,20,1252)@.[User::MaxUsageID]

|||

Hi Phil,

Thanks for your reply but I am afraid I already have tried the solution you suggested. Max(UsageID) is calculated against sql table and then stored to MaxUsageID user variable.

select max(usageID) from dbo.XXX

--1027005

So you could see value by no means is tens of millions as you anticipated. I also have tried changing DT_STR string length parameter to larger values as proposed

SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 20, 1252) @.[User::MaxUsageID]

Even when I have no casting involved such as

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

I still get the same truncation error message. I would appreciate if you could suggest something else which obviously I am missing here.

Thanks,

Asaf

|||

Phil,

I have worked it out.

It looks like one of the SSIS bugs. Even though I updated the expression by changing (DT_STR, 6, 1252) to (DT_STR, 12, 1252) so that I could avoid any obvious truncation issues for values above 1 million and after saving the package I rebuilt it but project would fail whenever I executed it.

When I opened the project again, I ended up having the old value for (DT_STR, 6, 1252) in xml code view. Somehow SSIS wouldn't save the updated expression. Hence I kept getting that error message even though I had reasons to believe I had done everything right.

To resolve that, I just decided to define a new variable with required DT_STR string length and my package works without any complain.

Thanks,

Asaf

|||

Sounds like you may have been trying to update the "Value" parameter, not the "Expression" parameter. In that case, it would revert back to whatever you had in the expression.

With that being said, if it is a bug and you can reproduce it in the future, please submit it at http://connect.microsoft.com/sqlserver/feedback and report back here with the link to your submission. But please only submit it if you can reproduce it.

Thanks,

Phil

sql

A truncation occurred during evaluation of the expression

First of all, I get the following error message for one of my packages which uses user variables:

SSIS package "UsageAnalysis.dtsx" starting.
Information: 0x4004300A at Perform xmlState Shredding, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001700E at UsageAnalysis: A truncation occurred during evaluation of the expression.
Error: 0xC0019004 at UsageAnalysis: The expression for variable "GetAnalysisData" failed evaluation. There was an error in the expression.
Error: 0xC02020E9 at Update Analysis Table, UsageAnalysis Source [1]: Accessing variable "User::GetAnalysisData" failed with error code 0xC001700E.
Error: 0xC0024107 at Update Analysis Table: There were errors during task validation.
Warning: 0x80019002 at Usage Analysis Process: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "UsageAnalysis.dtsx" finished: Failure.

Now my package has the following variables:

GetMaxUsageID: scope package level, type string, statement SELECT MAX(UsageID) AS MaxUsageID FROM XX.XXX

MaxUsageID: scope package level, type int32, default value 0, value get assigned from the following statement executed from sql task that runs GetMaxUsageID variable as above

GetAnalysisData: scope package level, type string, Evaluate as Expression

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 8, 1252) @.[User::MaxUsageID]

The package has worked fine until MaxUsageID value reached to 10,00,000 and since then I have been getting above mentioned error message. The problematic step is related to Data Flow task where I use GetAnalysisData. I have tried replacing user variable with literal as follows

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

the error message stays the same. Please note that package has worked fine before and it still works ok if I don't use user variables. Obviously, some of you would see eliminating user variables as workaround but I would appreciate if cause of that error message could be investigated.

Thanks,

Asaf

Are you sure MAX(UsageID) is 1,000,000 and not 100,000,000?

Try making your cast a longer length in the GetAnalysisData variable. Perhaps: (DT_STR,20,1252)@.[User::MaxUsageID]

|||

Hi Phil,

Thanks for your reply but I am afraid I already have tried the solution you suggested. Max(UsageID) is calculated against sql table and then stored to MaxUsageID user variable.

select max(usageID) from dbo.XXX

--1027005

So you could see value by no means is tens of millions as you anticipated. I also have tried changing DT_STR string length parameter to larger values as proposed

SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 20, 1252) @.[User::MaxUsageID]

Even when I have no casting involved such as

"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"

I still get the same truncation error message. I would appreciate if you could suggest something else which obviously I am missing here.

Thanks,

Asaf

|||

Phil,

I have worked it out.

It looks like one of the SSIS bugs. Even though I updated the expression by changing (DT_STR, 6, 1252) to (DT_STR, 12, 1252) so that I could avoid any obvious truncation issues for values above 1 million and after saving the package I rebuilt it but project would fail whenever I executed it.

When I opened the project again, I ended up having the old value for (DT_STR, 6, 1252) in xml code view. Somehow SSIS wouldn't save the updated expression. Hence I kept getting that error message even though I had reasons to believe I had done everything right.

To resolve that, I just decided to define a new variable with required DT_STR string length and my package works without any complain.

Thanks,

Asaf

|||

Sounds like you may have been trying to update the "Value" parameter, not the "Expression" parameter. In that case, it would revert back to whatever you had in the expression.

With that being said, if it is a bug and you can reproduce it in the future, please submit it at http://connect.microsoft.com/sqlserver/feedback and report back here with the link to your submission. But please only submit it if you can reproduce it.

Thanks,

Phil

a table/column to table/column data check (was "Help Please, SQL something Simpl

Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.

I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.

Easy Peasy for you guys, any help would be appreciated.

Welcome to the forum,ok here is your solution

CREATE TABLE t1(
col1 VARCHAR(20)
,col2 VARCHAR(20)
,col3 VARCHAR(20)
)

CREATE TABLE t2(
col4 VARCHAR(20)
,col5 VARCHAR(20)
)

The sql statement to check content of a col1 is in col4 or not...

SELECT * FROM
t1,t2
WHERE t1.col1=t2.col4|||Many thanks, will give it a try a wee bit later and post result.

Many thanks...|||Hi, this appears to work fine, however if I change the logic to check what is not in table2 it appears to go into a endless loop, any help appreciated.|||Hi, sorry after further testing, I may not have explained myself very well, apologies, I need to access row1 of table1 and then scan column2 in table2, the entire table, for the occurence of the data item in row1 table1.

Many thanks in anticipation.|||Hi, this appears to work fine, however if I change the logic to check what is not in table2 it appears to go into a endless loop, any help appreciated.
Test it ...

insert into t1 values('joy','datta','harry')
insert into t1 values('Pink','datta56','harry2')
insert into t1 values('Sheep','datta5','harry3')
insert into t1 values('Ghree','datta11','harry5')

insert into t2 values('Sheep','datta5')
insert into t2 values('Ghree','datta11')

SELECT * FROM
t1
WHERE t1.col1 NOT IN(SELECT t2.col5 FROM t2)|||Thanks sorted..

A stupid SQL RS question

Hello everyone,
I am newbie to SQL RS and just got it installed on my laptop. I wanted
to know if the following is possible with SQL RS or do I need to use a thrid
party tool. Here is what I want to do:
I have a search website where users can query certain (SQL) databases
and generate reports. Currently the reports are in HTML format and I using
ASP.NET to do all the searching and report displaying.
Is it possible to provide a report save option which will convert the
generated report into PDF/EXCEL/TEXT/etc? Can I make use of SQL RS or do I
have to use a thrid party software like ASP2PDF'
thank you all in advance,
BIYou have multiple options for rendering formats. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp
--
-- John H. Miller [MSFT]
-- SQL Server BI Product Group
----
--
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
----
--
"bhai" <bhai@.vt.edu> wrote in message
news:e3asQNadEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Hello everyone,
> I am newbie to SQL RS and just got it installed on my laptop. I wanted
> to know if the following is possible with SQL RS or do I need to use a
thrid
> party tool. Here is what I want to do:
> I have a search website where users can query certain (SQL) databases
> and generate reports. Currently the reports are in HTML format and I using
> ASP.NET to do all the searching and report displaying.
> Is it possible to provide a report save option which will convert the
> generated report into PDF/EXCEL/TEXT/etc? Can I make use of SQL RS or do
I
> have to use a thrid party software like ASP2PDF'
> thank you all in advance,
> BI
>|||Use the Reporting Services Web Service (SOAP API) for programmatic exporting
of reports. The method you want is "Render".
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"bhai" <bhai@.vt.edu> wrote in message
news:e3asQNadEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Hello everyone,
> I am newbie to SQL RS and just got it installed on my laptop. I wanted
> to know if the following is possible with SQL RS or do I need to use a
thrid
> party tool. Here is what I want to do:
> I have a search website where users can query certain (SQL) databases
> and generate reports. Currently the reports are in HTML format and I using
> ASP.NET to do all the searching and report displaying.
> Is it possible to provide a report save option which will convert the
> generated report into PDF/EXCEL/TEXT/etc? Can I make use of SQL RS or do
I
> have to use a thrid party software like ASP2PDF'
> thank you all in advance,
> BI
>

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.
> > >> >> >> >> >> >>
> > >> >> >> >> >> >
> > >> >> >> >> >> >
> > >> >> >> >> >> >.
> > >> >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >
> > >> >> >> >> >.
> > >> >> >> >> >
> > >> >> >> >
> > >> >> >> >
> > >> >> >> >.
> > >> >> >> >
> > >> >> >
> > >> >> >
> > >> >> >.
> > >> >> >
> > >> >
> > >> >
> > >> >.
> > >> >
> > >
> > >
> > >.
> > >
>