Query/Report processing time

I am having a frustrating problem with a report. It is pulling data from a
stored procedure, and I have tested it through query analyzer and i get
performance times of 4-5 seconds. However, when I select this same sproc
through Reporting Services during report building time, and hit the "!" to
pull the data, punch in the exact same parameters, it is taking upward of
30-40 seconds to pull the same data. What is the deal? It makes no rational
sense.How much data are we talking about? In QA, enable Client Statistics and what are the # of rows
affected and # of bytes sent?
Also, which query designer are you using? Does it have 4 panes and show tables or is it the 2-pane
textbox/grid designer? QA and the designers all use different underlying mechanisms for querying
the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses the SQL Managed provider.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> Well, that is just it. It's NOT during the actual report processing that I
> am having this problem, it is when I am loading the dataset under the "Data"
> tab. When I hit the "!" and enter in my params, the thing is taking forever.
> But when I run the same sproc in Query Analyzer, it is taking 4-5 seconds.
> So, this has nothing to do with actual report processing, it is something it
> is doing when it is grabbing the data that is f'ed up (if you know what I
> mean). Hence, when I deploy this thing (although the report is really
> simple) since it is taking it forever to grab data that should take a few
> seconds, it is taking like 45 seconds to render this report. It is a real
> pain in the ass.
> "Andrew Byrne" wrote:
> > My guess is that in design time you are running the query through the .NET
> > IDE so I presume there is a bigger memory footprint required.
> >
> > That lag experienced is probably due to RS not caching the report between
> > runs, something which was improved in SP1.
> >
> > Again, I would say .NET is involved if you experience lag the first time (in
> > a session) you run a report - the CLR needs to load assemblies, everything is
> > running under IIS etc.
> >
> > Don't get me wrong - I agree that RS could be a lot faster - just merely
> > trying to come up with plausible reasons for the reduction in speed !
> >
> > "Dan Fell" wrote:
> >
> > >
> > > I too suffer from similar problems. I think the problem seems to lie in the
> > > rendering part of the reporting. The queries themselves take relatively
> > > little time but the report can take ages to appear on screen.
> > >
> > > Not only that but it can be quite erratic. There a lag when the Report
> > > Manager's pages and reports are first rendered and then they seem to render
> > > much quicker afterwards. Is this normal?
> > >
> > > DF
> > >
> > > "isamu" wrote:
> > >
> > > > I am having a frustrating problem with a report. It is pulling data from a
> > > > stored procedure, and I have tested it through query analyzer and i get
> > > > performance times of 4-5 seconds. However, when I select this same sproc
> > > > through Reporting Services during report building time, and hit the "!" to
> > > > pull the data, punch in the exact same parameters, it is taking upward of
> > > > 30-40 seconds to pull the same data. What is the deal? It makes no rational
> > > > sense.|||I have experienced the same thing, even though I am designing/running
directly on the server hosting RS.
It seems to be intermittent. I have had a report take 2-3 minutes
everytime. This could go on for hours, even after a reboot. Then, out of
nowhere it takes 3 seconds. FYI: The same query always took about 1
second through query analyzer. This seems to be an RS rendering issue...
Dan
___________________________
"isamu" <isamu@.discussions.microsoft.com> wrote in message
news:36026EB9-5AD5-4355-9C15-B7573A5DAB56@.microsoft.com...
> Argh ... well, this is quite odd, after working on this for about 2 hours
> yesterday and even running it a few times today, it appears to have fixed
> itself ... I am not sure, but perhaps this was some sort of Network issue
at
> my client's site. Thanks a lot for your time, and sorry to trouble you.
I
> guess if this issue ever comes up again, have the network guys check their
> latencies.
> "Donovan R. Smith [MSFT]" wrote:
> > How much data are we talking about? In QA, enable Client Statistics and
what are the # of rows
> > affected and # of bytes sent?
> >
> > Also, which query designer are you using? Does it have 4 panes and show
tables or is it the 2-pane
> > textbox/grid designer? QA and the designers all use different
underlying mechanisms for querying
> > the database. The VDT (4-pane) uses OLE-DB and the GQD (2-pane) uses
the SQL Managed provider.
> >
> > --
> > Thanks.
> >
> > Donovan R. Smith
> > Software Test Lead
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "isamu" <isamu@.discussions.microsoft.com> wrote in message
> > news:A7571FD3-FE20-4BE7-A2B2-6E11C0ACCBE0@.microsoft.com...
> > > Well, that is just it. It's NOT during the actual report processing
that I
> > > am having this problem, it is when I am loading the dataset under the
"Data"
> > > tab. When I hit the "!" and enter in my params, the thing is taking
forever.
> > > But when I run the same sproc in Query Analyzer, it is taking 4-5
seconds.
> > > So, this has nothing to do with actual report processing, it is
something it
> > > is doing when it is grabbing the data that is f'ed up (if you know
what I
> > > mean). Hence, when I deploy this thing (although the report is really
> > > simple) since it is taking it forever to grab data that should take a
few
> > > seconds, it is taking like 45 seconds to render this report. It is a
real
> > > pain in the ass.
> > >
> > > "Andrew Byrne" wrote:
> > >
> > > > My guess is that in design time you are running the query through
the .NET
> > > > IDE so I presume there is a bigger memory footprint required.
> > > >
> > > > That lag experienced is probably due to RS not caching the report
between
> > > > runs, something which was improved in SP1.
> > > >
> > > > Again, I would say .NET is involved if you experience lag the first
time (in
> > > > a session) you run a report - the CLR needs to load assemblies,
everything is
> > > > running under IIS etc.
> > > >
> > > > Don't get me wrong - I agree that RS could be a lot faster - just
merely
> > > > trying to come up with plausible reasons for the reduction in speed
!
> > > >
> > > > "Dan Fell" wrote:
> > > >
> > > > >
> > > > > I too suffer from similar problems. I think the problem seems to
lie in the
> > > > > rendering part of the reporting. The queries themselves take
relatively
> > > > > little time but the report can take ages to appear on screen.
> > > > >
> > > > > Not only that but it can be quite erratic. There a lag when the
Report
> > > > > Manager's pages and reports are first rendered and then they seem
to render
> > > > > much quicker afterwards. Is this normal?
> > > > >
> > > > > DF
> > > > >
> > > > > "isamu" wrote:
> > > > >
> > > > > > I am having a frustrating problem with a report. It is pulling
data from a
> > > > > > stored procedure, and I have tested it through query analyzer
and i get
> > > > > > performance times of 4-5 seconds. However, when I select this
same sproc
> > > > > > through Reporting Services during report building time, and hit
the "!" to
> > > > > > pull the data, punch in the exact same parameters, it is taking
upward of
> > > > > > 30-40 seconds to pull the same data. What is the deal? It
makes no rational
> > > > > > sense.
> >
> >
> >

Query/Report Help needed

Hello all,

I need to do the following task repeatedly. I have been doing it
manually until now and I am thinking if there is any way I could
automate the whole process.

Here is the task:
For a selected group of tables, I need to create an Excel file with one
worksheet per table. The worksheet contains information about the
table.

Here is an Example of Person table with fields PersonId, LastName,
FirstName ...

Column Name -> PersonId LastName FirstName ...
Type -> char char char ...
Length -> 6 50 50
Data -> P00001 Smith John ...
. P00002 Smith Joan ...You can do this from within an ActiveX Script in DTS. Select the
necessary information from INFORMATION_SCHEMA.COLUMNS, then use the
Excel automation objects to put the data into the proper cells. You can
then do the same for the actual data, using the column information that
you selected to generate a SQL statement if your tables are constantly
changing.

You should be able to find information on using the Excel automation
objects on the Microsoft website.

HTH,
-Tom.sql

Query/Report for Owner of all SQL Jobs

Is there a query/report that I can run against all our SQL servers to see who the 'owner' of each job is?
If so, can this be run in SQL Server Enterprise Manager 8, SQL Server Management Studio or Hyena?Usual caveats re querying system tables (run at own risk etc):
SELECT J.name AS JobName
, L.name AS JobOwner
FROM msdb.dbo.sysjobs_view J
INNER JOIN
master.dbo.syslogins L
ON J.owner_sid = L.sid
HTH|||Thank you very much! I'm just a newbie DBA admin :)

Query!

Hi All,

Reporting service provides export to excel functionality at report level.

I want to provide the exprot to excel functionality at line item level.

Regards

Abdul

Hello,

Do yow want this functionality on your web page??

|||

I want to provide the exprot to excel functionality at line item level.

NO, the build-in exporting function doesn't support what you want.

Query XML datasource

I have an XML file that I receive from a vendor and would like to report
from this data every morning. Can anyone send me a link where I can do
this? I do not have a schema at this time but I think I can create one if
needed.
Thanks
Scottsee the thread
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d8d798424508e0e8/c4ceb1e126528133?lnk=raot#c4ceb1e126528133
I think your solution will also depend on
[1] how do you receive the XML file from the vendor?
[2] how do you like the report every morning? (schedule,
auto-delivery?)
Scott M wrote:
> I have an XML file that I receive from a vendor and would like to report
> from this data every morning. Can anyone send me a link where I can do
> this? I do not have a schema at this time but I think I can create one if
> needed.
> Thanks
> Scott|||Will this also work in RS 2000?
<siva.jasthi@.gmail.com> wrote in message
news:1134501012.316584.125040@.g44g2000cwa.googlegroups.com...
> see the thread
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/d8d798424508e0e8/c4ceb1e126528133?lnk=raot#c4ceb1e126528133
>
> I think your solution will also depend on
> [1] how do you receive the XML file from the vendor?
> [2] how do you like the report every morning? (schedule,
> auto-delivery?)
>
>
> Scott M wrote:
>> I have an XML file that I receive from a vendor and would like to report
>> from this data every morning. Can anyone send me a link where I can do
>> this? I do not have a schema at this time but I think I can create one
>> if
>> needed.
>> Thanks
>> Scott
>|||Hi Scott,
Unfortunately, it will not work in SQL Server 2000 Reporting Services.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Is there an online tutorial for how to create a data extension for that?
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:YUstdbIAGHA.832@.TK2MSFTNGXA02.phx.gbl...
> Hi Scott,
> Unfortunately, it will not work in SQL Server 2000 Reporting Services.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Scott,
Thanks for the respond.
I believe the following articles might help
Build an XML Data Extension for SQL Server Reporting Services
http://www.devx.com/dbzone/Article/21214
Tutorial: Using XML Data in a Report
http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I am not understanding from the MSDN article in this post where do you put
the XML file to connect to for your database.
"Michael Cheng [MSFT]" wrote:
> Hi Scott,
> Thanks for the respond.
> I believe the following articles might help
> Build an XML Data Extension for SQL Server Reporting Services
> http://www.devx.com/dbzone/Article/21214
> Tutorial: Using XML Data in a Report
> http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
> Hope this helps.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||That first article looks like it might be what I need. I'll let you guys
know.
Thanks VERY much!!
Scott
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:ktNF%23pVAGHA.1236@.TK2MSFTNGXA02.phx.gbl...
> Hi Scott,
> Thanks for the respond.
> I believe the following articles might help
> Build an XML Data Extension for SQL Server Reporting Services
> http://www.devx.com/dbzone/Article/21214
> Tutorial: Using XML Data in a Report
> http://msdn2.microsoft.com/en-us/library/ms345334(en-US,SQL.90).aspx
> Hope this helps.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Scott,
You are welcome :) If you have any questions or concerns next time, don't
hesitate to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Query written in CODE part is not working

Hi
I have a report in which the Dataset is filled using MDX query and for every row in the DataSet the Function in the CODE is called, which in turn Connects to the DB and Selects a particular value. The data set is getting filled but the CODE (which contains the SQL Select query) is not getting executed.
I have references System.data, System.data.SQLClient, System.data.Xml dlls for a RDL and have written a connection string with UID and Pwd and the userid has sufficient permissions.
When i Preview it(using visual studio), the value(O/P, the expected value) is shown in the report but i dont see any query executed in the SQL Profiler. Also when i preview this report through the report viewer control from an Window Application, i dont get the output. I dont seem to figure out what the problem is. Please HELP!!

Thanking you in advance.
Regards
Sai
Not sure if I undestand your scenario well. Do you have a VB.NET function embedded in the report? If so, most likely the function is erroring out or you are facing a security issue. What I'd suggest is moving the code to an external .NET assembly. Then, set your report as a startup item on the project properties. Put a breakpoint in the custom function and hit F5 to load the report in the Report Host. When the report is run, the breakpoint should be hit from the first dataset row and you should be able to troubleshoot what's wrong.sql

Query written in CODE part is not working

Hi
I have a report in which the Dataset is filled using MDX query and for every row in the DataSet the Function in the CODE is called, which in turn Connects to the DB and Selects a particular value. The data set is getting filled but the CODE (which contains the SQL Select query) is not getting executed.

I have references System.data, System.data.SQLClient, System.data.Xml

dlls for a RDL and have written a connection string with UID and Pwd

and the userid has sufficient permissions.
When i Preview it(using visual studio), the value(O/P, the expected value) is shown in the report but i dont see any query executed in the SQL Profiler. Also when i preview this report through the report viewer control from an Window Application, i dont get the output. I dont seem to figure out what the problem is. Please HELP!!

Thanking you in advance.
Regards
SaiNot sure if I undestand your scenario well. Do you have a VB.NET function embedded in the report? If so, most likely the function is erroring out or you are facing a security issue. What I'd suggest is moving the code to an external .NET assembly. Then, set your report as a startup item on the project properties. Put a breakpoint in the custom function and hit F5 to load the report in the Report Host. When the report is run, the breakpoint should be hit from the first dataset row and you should be able to troubleshoot what's wrong.

QUERY WITH COUNT

I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below

I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4

STATUS

* 0 1 2 3 4

+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)

region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)

-region 8

+school count(id) count(id) count(id) count(id) count(id) cnt(id)

The fields are in the same table

employee ID region status

A 1 1

B 1 0

C 2 3

Please help

THANKS

This should do, just expand out to 8 status:

create table matrixThang

(

employeeId char(1),

region int,

status int

)

insert into matrixThang

select 'A',1,1

union all

select 'B',1,0

union all

select 'C',2,3

go

select region, count(*) as ,

sum(case when status = 0 then 1 else 0 end) as [0],

sum(case when status = 1 then 1 else 0 end) as [1],

sum(case when status = 2 then 1 else 0 end) as [2],

sum(case when status = 3 then 1 else 0 end) as [3]

from matrixThang

group by region

Returns:

region * 0 1 2 3
-- -- -- -- -- --
1 2 1 1 0 0
2 1 0 0 0 1

|||

hi

Sorry , I am a little confused

does this give the count of employees for diffetent status?

I need to display count(employee id) for each region for each status

also i will need the count at each row level for each branch the employees attend

thanks

|||

Yes, the SUM is a trick to eliminate NULLs, as well as give you a lot of power over the types of aggregations you want. If you need to do something with duplicate values and count distincts like this, you can do something along these lines:

select region, count(*) as ,

count(distinct case when status = 0 then employeeId else NULL end) as [0],

count(distinct case when status = 1 then employeeId else NULL end) as [1],

count(distinct case when status = 2 then employeeId else NULL end) as [2],

count(distinct case when status = 3 then employeeId else NULL end) as [3]

from matrixThang

group by region

The NULL values will give you this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If you understand why you are getting this error message, you can use:

SET ANSI_WARNINGS OFF

to turn them off. Note that it obviscates problems, but the warning messages aren't a big deal either.

QUERY WITH COUNT

I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below

I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4

STATUS

* 0 1 2 3 4

+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)

region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)

-region 8

+school count(id) count(id) count(id) count(id) count(id) cnt(id)

The fields are in the same table

employee ID region status

A 1 1

B 1 0

C 2 3

Please help

THANKS

This should do, just expand out to 8 status:

create table matrixThang

(

employeeId char(1),

region int,

status int

)

insert into matrixThang

select 'A',1,1

union all

select 'B',1,0

union all

select 'C',2,3

go

select region, count(*) as ,

sum(case when status = 0 then 1 else 0 end) as [0],

sum(case when status = 1 then 1 else 0 end) as [1],

sum(case when status = 2 then 1 else 0 end) as [2],

sum(case when status = 3 then 1 else 0 end) as [3]

from matrixThang

group by region

Returns:

region * 0 1 2 3
-- -- -- -- -- --
1 2 1 1 0 0
2 1 0 0 0 1

|||

hi

Sorry , I am a little confused

does this give the count of employees for diffetent status?

I need to display count(employee id) for each region for each status

also i will need the count at each row level for each branch the employees attend

thanks

|||

Yes, the SUM is a trick to eliminate NULLs, as well as give you a lot of power over the types of aggregations you want. If you need to do something with duplicate values and count distincts like this, you can do something along these lines:

select region, count(*) as ,

count(distinct case when status = 0 then employeeId else NULL end) as [0],

count(distinct case when status = 1 then employeeId else NULL end) as [1],

count(distinct case when status = 2 then employeeId else NULL end) as [2],

count(distinct case when status = 3 then employeeId else NULL end) as [3]

from matrixThang

group by region

The NULL values will give you this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If you understand why you are getting this error message, you can use:

SET ANSI_WARNINGS OFF

to turn them off. Note that it obviscates problems, but the warning messages aren't a big deal either.

Query using DATEPART

Hello.

The purpose of the code below is to produce graphs in a report (thus the SELECT DATEPART so the month and year appear on the graphs using COUNT in the report.)I have two challenges:

1. How should the code be modified so that it returns the previous two months at the start of a new year?(I.e. In Jan. 2008, returning Nov. & Dec. 2007, and in Feb. 2008, returning Dec. 2007 & Jan 2008, etc.)

2. How should the code be modified so that the DatePart and DateName data are treated as dates yet still appear as the Month Name and Year (June 2007)? Currently it is treating them as alphanumeric so that on the chart, July is coming before June.

Thank you.

SELECT DISTINCT TBL_01.id, TBL_01.url, TBL_01.source, Tbl_02.type, TBL_01.time, Tbl_03.time, DATENAME(month, TBL_01.time) + ' ' + DATENAME(year,TBL_01.time) as Month_Year

FROMTbl_03 INNER JOIN

Tbl_04 ON Tbl_03.id = Tbl_04.endsTBL_01 LEFT OUTER JOIN

Tbl_02 ON Tbl_04.endsTBL_01 = Tbl_02.id LEFT OUTER JOIN

Tbl_05 ON Tbl_04.ursid = Tbl_05.SubId INNER JOIN

Tbl_03 AS TBL_01 ON TBL_01.id = Tbl_04. EFP

WHERE(Tbl_04. EFP IS NOT NULL) AND (Tbl_05.grade IS NULL OR

Tbl_05.grade = 'NC')

AND (DATEPART(month, TBL_01.time) < (SELECT DATEPART(month, getutcdate()))

AND DATEPART(month, TBL_01.time) > (SELECT DATEPART(month, getutcdate()))-3

AND DATEPART(year, TBL_01.time) = (SELECT DATEPART(year, getutcdate()))

AND (Tbl_02.type IS NULL OR Tbl_02.type = 'CC'

OR Tbl_02.type = 'MK'))

ORDER BY Tbl_02.Type, Tbl_03.time DESC

For Q2, you might be able to fix the ordering with a tiny change to the 'ORDER BY ' clause:

ORDER BY TBL_01.time desc, Tbl_02.Type, Tbl_03.time

You don't always have to have the <ORDER BY> field in the select list.

|||

For Q1, I would do something like this:

Code Snippet

declare @.dtStartOfThisMonth datetime, @.dtStartOfTimePeriod datetime

set @.dtStartOfThisMonth = ('01 ' + right(convert(varchar, getdate(),106),9))

set @.dtStartOfTimePeriod = Dateadd(m,-2, @.dtStartOfThisMonth)

THE REST OF THE QUERY HERE

Then, in the WHERE clause put something like:

Code Snippet

AND Tbl_01.Time >= @.dtStartOfTimePeriod and Tbl_01.Time < @.dtStartOfThisMonth

|||

This worked in putting things in the proper order. I just took out the "desc" and everything fell into place. The one field that was in the select list that didn't need to be was merely a leftover from when I was experimenting with an aggregate. I had forgotten to take it out.

Thank you so much.

|||

This worked beautifully! Thank you so much!

query troubles

I used a query in VB with an access form as the front end to create a report...I have modified it to try to make it work in SQL Server...it is returning too many fields though currently....

Select dev.KinderNumber,dev.Client,dev.MFG,dev.FAMILY,dev .Intro_Date,dev.FIXTURE,dev.Dimensions_HWD,
dev.MFG_Price,dev.Item_Number,dp.picname from Development
as dev, DevelopmentPictures as dp
RIGHT JOIN Development ON dp.[KinderNum] = Development.[KinderNumber]
WHERE dp.[Revision_Letter]
IN(select max([Revision_Letter]) from DevelopmentPictures where DevelopmentPictures.KinderNum = dp.KinderNum);

this returns about 57000 rows I usually have about 350-400..

also : IN(select max([Revision_Letter]) from DevelopmentPictures where DevelopmentPictures.KinderNum = KinderNum);

if I take out the dp from dp.kindernum it only retunrs the row headings??

Please help!

thanks in advance,

JohnWhat does Development as dev join to? May be a cross join there...|||Since you are comparing the results from two different databases, VB and SQL Server, I'd suggest first running a quick check on the tables to see if you've got the same number of rows in both.|||jfouse

You didn't say what your query should return so this is a bit of a guess.

Select dev.KinderNumber
, dev.Client
, dev.MFG
, dev.FAMILY
, dev.Intro_Date
, dev.FIXTURE
, dev.Dimensions_HWD
, dev.MFG_Price
, dev.Item_Number
, dp.picname
from Development as dev
left join DevelopmentPictures as dp on dev.KinderNumber = dp.KinderNum
where dp.Revision_Letter = (select max(Revision_Letter)
from DevelopmentPictures
where DevelopmentPictures.KinderNum = dp.KinderNum)sql

Query to report database user role membership

Hi All,

I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.

If anyone has a query like this can they please help me?

thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:

select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R

Query to report database user role membership

Hi All,

I using sql server 2005 sp2. I'm trying to construct a query that'll show me all roles and the users that are in the roles. I'm having trouble figuring how sql server stores this information in the system tables. I think it's based around the database_principals system tables but haven't been able to display the info i need.

If anyone has a query like this can they please help me?

thanks,
Dave
I figured it out. I found the sys.database_role_members which had the info i was after:

select distinct c.name, b.name from sys.database_role_members a
inner join sys.database_principals b on b.principal_id = a.member_principal_id
inner join sys.database_principals c on c.principal_id = a.role_principal_id
and b.type <> 'R

Query timeouts

I've created a report in RS 2006 that uses an XML data source. The source
uses a web service that takes sufficiently long to return that my query times
out.
can anyone tell me how can I increase the timeout value of the query?Click on the ... of the dataset. The timeout is on that page.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"ekkis" <ekkis@.discussions.microsoft.com> wrote in message
news:CC99FCCF-D3B5-4AB6-AD31-BE35A002748E@.microsoft.com...
> I've created a report in RS 2006 that uses an XML data source. The source
> uses a web service that takes sufficiently long to return that my query
> times
> out.
> can anyone tell me how can I increase the timeout value of the query?
>

Query Time Out

Hi,

I have a report with query timeout set to 1 sec (I want this for producing timeout exception). When I view it from Business Intelligence Dev. studio's preview tab, it gives me "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" exception.

This is fine..but when i deploy it on report server it doesn't give this exception and opens the report without giving any exception.

in DatabaseQueryTimeout in rsreportserver.config is set to 900sec.

Can someone help me out if I am doing anything wrong or missing something?

And I'will be thankfull to you in advance.

-Thanks

Sounds like more a reporting services issue. Try that group.

Query String Programmatically

Is any way to Modified The Query String Programmatically for a Report ?
I want to be able to Change the WHERE Clause without using Parameter..
e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report I
want something like WHERE DeliveryDate < '2005-04-04'.
Thank. Any Idea I would appreciated.
--
Message posted via http://www.sqlmonster.comGot from BOL.
Passing a Report Parameter on a URL
You can pass report parameters to a report by including them in a URL. These
URL parameters are not prefixed, because they are passed directly to the
report processing engine. For more about report parameters, see Running a
Parameterized Report.
Example
The following example uses the report parameter EmployeeID to render the
specified report:
http://server/reportserver?/Sales/Northwest/Employee Sales
Report&rs:Command=Render&EmployeeID=1234See Also
The operator cant be changed unless you use dynamic SQL.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Edgar Mantilla via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:000edb152d5c494b8854daa6aaf009ff@.SQLMonster.com...
> Is any way to Modified The Query String Programmatically for a Report ?
> I want to be able to Change the WHERE Clause without using Parameter..
> e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report
> I
> want something like WHERE DeliveryDate < '2005-04-04'.
> Thank. Any Idea I would appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Thank you,
I am working in a windows form that selects the report from the Report
Server and send it as .PDF to a specific location, Passing a Report
Parameter on a URL, it works well, but I want to have more flexibility in
building my query string, so I worked with a RDL Generator (Form BOL
Walkthrough ? Generating RDL Using the .NET Framework), which work fine
when you are creating a report from scratch.
Is any way to change the CommandTex or Query string of an existing report
programmatically?
--
Message posted via http://www.sqlmonster.com|||Edgar,
AFAIK you can't change the query text at report run time. There are
solutions to this type of complex querying, and let me say up front that I
recommend you put your query in a stored procedure instead of trying to put
this all into the report itself.
As Jens said, you can use dynamic SQL. This involves building your query as
a string and then calling EXEC (string). If you only have a limited number
of choices (e.g. only <, =, or >) then you can simply use IF statements and
hard code the queries for each branch.
Note: if you go the dynamic SQL route, beware of SQL injection attacks.
That's a whole subject, and too much to discuss here in one post.
Ted
"Edgar Mantilla via SQLMonster.com" wrote:
> Thank you,
> I am working in a windows form that selects the report from the Report
> Server and send it as .PDF to a specific location, Passing a Report
> Parameter on a URL, it works well, but I want to have more flexibility in
> building my query string, so I worked with a RDL Generator (Form BOL
> Walkthrough â' Generating RDL Using the .NET Framework), which work fine
> when you are creating a report from scratch.
> Is any way to change the CommandTex or Query string of an existing report
> programmatically?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi, in the Generic Query Designer you can use some like this:
="SELECT " & Parameters!Field1.Value
" FROM " &
Parameters!Table.Value
"Edgar Mantilla via SQLMonster.com" wrote:
> Is any way to Modified The Query String Programmatically for a Report ?
> I want to be able to Change the WHERE Clause without using Parameter..
> e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report I
> want something like WHERE DeliveryDate < '2005-04-04'.
> Thank. Any Idea I would appreciated.
> --
> Message posted via http://www.sqlmonster.com
>