Determine slow running reports

Take a look at the Execution Log topics in Books Online. All of the informaiton you want is stored in the ReportServer database. There is even a DTS package to extract the data and a set of reports which show you statistics.
Brian Welcker
Group Program Manager
SQL Server Reporting Services
--Original Message--
From: Randy Knight
Posted At: Thursday, October 06, 2005 2:53 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Determine slow running reports
Subject: Re: Determine slow running reports
That would be great if I had control of the reports but as I said, the reports are published by analysts from all over the company. I just wondered if RS put some statistics or anything like that in the ReportServer database. It would seem to be useful ... how many times report is run, avg execution times, etc.
Guess I'll resort to good ol' SQL Profiler :)Just what I was looking for. Thanks.

Determine Quarter End and Beginning Dates

Hello,

I have a query that I would like to schedule in DTS. The criteria of
this query checks for records in the table that are within the current
quarter. Here is what I have.

WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'

I would like to dynamically generate the Quarter End and Quarter
Beginning dates within my where clause based on the date that DTWS
package is being executed on. Can anyone show me how this can be
accomplished?

Thank You."Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1112196440.142834.300110@.z14g2000cwz.googlegr oups.com...
> Hello,
> I have a query that I would like to schedule in DTS. The criteria of
> this query checks for records in the table that are within the current
> quarter. Here is what I have.
> WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
> I would like to dynamically generate the Quarter End and Quarter
> Beginning dates within my where clause based on the date that DTWS
> package is being executed on. Can anyone show me how this can be
> accomplished?
>
> Thank You.

Quick and dirty solution - see DATEPART in Books Online.

The longer answer is that using DATEPART might not be good for performance
(applying a function to a column prevents MSSQL using an index on that
column), so you may need another approach. One would be to write a stored
proc to return the first and last days of the current quarter, so you can
put them in variables and use them in your query; another would be to create
a calendar table (which is very useful anyway) and join on it in your query.

A couple of other small points - BETWEEN with datetime columns can give you
unexpected results if you don't allow for the time portion. In your case,
this is probably safer:

where submit_date >= '20050101' and submit_date < '20050401'

Also, try to use the YYYYMMDD date format if possible - it will always be
interpreted correctly by MSSQL regardless of client or server settings. More
information here:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||> WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
> I would like to dynamically generate the Quarter End and Quarter
> Beginning dates within my where clause based on the date that DTWS
> package is being executed on. Can anyone show me how this can be
> accomplished?

The easy way to do this is to set up a dates table that has columns for
quarter and year and then join
e.g. If you have a table: dates
D as datetime, Year as integer, Quarter as integer
20050101, 2005, 1
20050102, 2005, 1
...
20051231, 2005, 4

And then in your query:
Join (select Quarter, Year from dates where d = CONVERT(getdate(), datetime,
112) as t
Join dates on submitdate = d
where d.Quarter = t.Quarter and d.year = t.Year

The Hard Way is to calculate it in line:

If you want current quarter then:
WHERE submit_date BETWEEN
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST((Month(GetDate())-1) / 3 * 3 + 1 as varchar(2)),2) + '01' AND
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST((Month(GetDate())-1) / 3 * 3 + 4 as varchar(2)), 2) + '01'

Last quarter is harder:
WHERE submit_Date BETWEEN
CASE WHEN Month(GetDate()) < 4 THEN
CAST(Year(GetDate()) - 1 as varchar(4)) + '0901'
ELSE
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST(Month(GetDate()-1) / 3 * 3 - 2 as varchar(2)),2) + '01'
END
AND
CASE WHEN Month(GetDate()) < 4 THEN
CAST(Year(GetDate()) as varchar(4)) + '0101'
ELSE
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST(Month(GetDate()) / 3 * 3 + 1 as varchar(2)), 2) + '01'
END|||On 30 Mar 2005 07:27:20 -0800, Matt wrote:

>Hello,
>I have a query that I would like to schedule in DTS. The criteria of
>this query checks for records in the table that are within the current
>quarter. Here is what I have.
>WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
>I would like to dynamically generate the Quarter End and Quarter
>Beginning dates within my where clause based on the date that DTWS
>package is being executed on. Can anyone show me how this can be
>accomplished?
>
>Thank You.

Hi Matt,

In addition to the answers Simon and James gave, here's a quick formula
to calculate the first and last date of the quarter:

declare @.test datetime
set @.test = '20051201'
SELECT DATEADD(quarter, DATEDIFF(quarter, '20000101', @.test),
'20000101'),
DATEADD(quarter, DATEDIFF(quarter, '20000101', @.test) + 1,
'19991231')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Destination Spreadsheets in SSIS

Hello -

I am dealing with SSIS in VS 2005... Trying to convert all my DTS packages... So, basically all my packages will extract some information from a database and load the results into a spreadsheet.

To start I am trying to do a TOP 1 returning a string from the db... The first row has column names but mysteriously the package will start to write the expected results in the third row instead of the second one. The second row will remain blank and if I do a preview against the destination spreadsheet within the pkg I will see a NULL value in the second row and then in the third row I will see the string I was expecting.

Tried the following with no success:

Regedit.exe, in Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ do TypeGuessRows=8, ImportMixedTypes=Text, AppendBlankRows=0, FirstRowHasNames=Yes

Any help would be really appreciated.

I noticed some people are looking at this topic but no answers up to now... Should I add more information so that maybe you can help?

Thank you.

|||

I just tried it and it works for me. Header in row 1 and data starts and ends in row 2.

Please post more info.

|||I have not seen that behavir either. Perhaps is something in the source table that is causing that. Have you tried deleting the excel file and the connection manager and creating them back?|||

I am still having issues... I’ve created a new package using table pubs.dbo.jobs. I added a "data flow task" to the "control flow" pane. After that, I went to "data flow" pane and added a "OLE DB source" which has the sql query "select top 1 job_id from pubs.dbo.jobs", returning 1.

I also added an "Excel Destination" which points to a spreadsheet with a column header called "ID". All cells in this xls are defined as numbers... After that, I tied both components in the "data flow" pane and ran the package (in the connections managers I have the OLE DB and the excel connections).

SSIS succeeded and loaded 1 into the destination spreadsheet, in the third row instead of the second one. When I go to the "Excel Destination", hit edit and do preview, I see ID in the first row (which is the column name), NULL in the second row (unexpected) and 1 in third row (results from sql query).

Please, advise. Thanks in advance.

|||I follow the same steps you described and everything looks right. So, How are you creating the excel file? I created a connection manager to an unexisting excel file; the in the Excel destination componnet I use the New button in the 'name of the excel sheet' to create it as suggested by SSIS. Perhaps you are pointing to an existing file that has something in it it that causes the behaivor you see.|||

Rafael -

This does solve the problem. I really use an existing spreadsheet so I believe something wrong was happening while trying to match data types (sql query X existing destination sheet).

However, I really need to use this existing spreadsheet... This is because when my package is executed, the destination file is overwritten by a copy of the empty template file with column headers only. So the template file should be used... This is the way I always did for DTS packages and I'm wondering if you have another way to append the destination file in the second row always (otherwise file will keep growing).

BTW, these conversion data type "issues" in SSIS are a pain... I just don't understand why Microsoft eliminated implicit conversions (we have to convert explicitly in the sql query or either add a Data Conversion transform task mostly when we deal with strings - more to be done in addition to all that we have on our plate already). I miss DTS packages...

Please, reply with any comment on how to deal with the problem I have using an existing excel sheet. When I create a new one as suggested by SSIS, it does solve the NULL issue but I need to use a template file to assure I am starting in the second row always.

Thanks for your help.

|||

Gabriel Souza wrote:

BTW, these conversion data type "issues" in SSIS are a pain... I just don't understand why Microsoft eliminated implicit conversions (we have to convert explicitly in the sql query or either add a Data Conversion transform task mostly when we deal with strings - more to be done in addition to all that we have on our plate already). I miss DTS packages...

They may create a little more work upfront, but I'll readily trade that for not having to deal with implicit conversions. I've seen too many tools that use implicit conversions guess wrong about the datatypes, and you get no errors, no warnings. I've seen input files get columns re-ordered, but due to implicit conversions, the import process didn't throw any errors and the wrong data was imported for two weeks before a user questioned the values they were getting.

An explicit conversion is a lot safer for a production quality application.

|||

If you use an existing excel, the Excel destination will APPEND to the file. So if it detects that the original excel has 3 rows, it will write from the 4th row.

Even if your rows 2,3 look empty to you in the excel, they may have been used previously, and may have formatting/comments etc.Excel will not see these rows as empty.

One good way to check is to open the excel, and press CTRL_Home and then CTRL_Shift_End. If rows 2 and three are being marked, then that means excel detected three rows.

To solve this problem, mark A2 to Ctrl_Shift_End and delete cells (move cells up), then save the excel. Then check again to see if row 2 and 3 are being marked.

I had this problem, and it got solved when I edited the excel properly.

|||

Karfast -

Thanks for the reply but this does not help... The template file is in the correct format and we can't have manual work here...

The packages are supposed to run daily and they can't keep appending because they will grow indefinitely. This is why we use the template file and copy it to overwrite the destination file. The file will then be empty and the package will start in the second row again (at least this is the way we always did for dts's).

The problem I have is.... When this template file is copied the NULL issue happens again and it looks like SSIS gets lost with the data types because I did not create the excel sheet by clicking the "new" button in the "excel destination editor" as Rafael suggested (since I am dealing with the new template file by the time package runs).

Rafael suggestion does solve the problem with the unexpected NULL (because the destination sheet is created as SSIS suggests when we click the "new" button) but when the template file is copied the issue is raised again... This process of appending the file in the second row always should be automatic and we can't rely on manual deletions. As I said, the template file is formatted correctly.

Let me know your thoughts.

Thanks a lot.

|||

Gabriel on a recent project we ran into a nearly identical issue. At the end of updating one of our dimensions we needed to output the results to reflect any changes so our Excel and database table were completely in sync. We solved the issue using a template Excel file, but the template followed these standards:

All cells formatted as text

No forumulas

Header row only

All rows below header deleted initially to avoid any previous entries

Once the template was created it was untouched. Within our SSIS package we used a File System Task to copy the template over the existing file then in our data flow we took our source then ran it through data conversion (unicode string 255 or double precision floats) and output it to our new excel file. We used an Excel destination to map to our overwritten file and for the file task we had a before (template) and after (destion).

I can send you the package and template if you are still having difficulty but the lessons we learned were the formatting of the Excel template and matching the data types from the database to the Excel destination.

Hope this helps! Good luck!

|||

Hi Gabriel,

I meant that the template file should be correct, and that is the only manual operation.

Now if the template has col headers in row 1, and data in its second and third rows, all destination files will also have it. And hence, your first real row will be in the 4th row.

Please check your template file.

I had exactly the same scenario, and same problem, and it got solved.

HTH

Kar

|||

ADMariner -

Your suggestions are pretty good and I see you truly understood the issue I was having. I tried to apply these steps but unfortunately I was still having the issue... Even making sure all the 60,000 rows after the first row were being deleted properly, the NULL value would still show up.

Only thing that solved the problem was to create the "destination" sheet by clicking the "new button" in the "excel destination editor" as Rafael suggested. As soon as I applied this same trick to the template file and pointed the SSIS package back to the destination spreadsheet, the issue went away.

I really appreciate all the help from everybody. Thanks a lot

|||

ADMariner wrote:

We solved the issue using a template Excel file, but the template followed these standards:

All cells formatted as text

No forumulas

Header row only

All rows below header deleted initially to avoid any previous entries

Once the template was created it was untouched. Within our SSIS package we used a File System Task to copy the template over the existing file then in our data flow we took our source then ran it through data conversion (unicode string 255 or double precision floats) and output it to our new excel file. We used an Excel destination to map to our overwritten file and for the file task we had a before (template) and after (destion).

After you wrote to the template and then opened it in Excel, were your floats represented as numbers or text? I haven't been able to get it to write floats to an existing file (header row only) that remain formatted as numbers in Excel. Instead, they're text with the related side-effects (left justified, green triangle in upper left warning about a possible number in a text field, conditional formatting doesn't work correctly, etc.) I tried having the Excel destination create the spreadsheet, and then it DID write the floats as numbers, and would continue appending numbers on subsequent runs, but when I removed the data from that file (leaving the header) and tried to write to it again, the floats were then written as text.

Design Opinions Please

I have written a DTS package to import a table from Oracle into SQL Server.
This table is defined as Data_Group (which is an employee id), Element_Code
(a numberic code that tells what data this row has, ie. Name, Address,
Status, etc), and then the Value. There are several elements that I will no
t
use, but I import them anyway for later use. My next step is to take these
data elements at build 1 record in my SQL Server table.
I am thinking about creating a SP that accepts the employee id and then
builds the record by using a bunch of SQL Update statements. Then write
another SP that opens a cursur on my input table (SELECT DISTINCT Data_Group
From T1) and call the SP from within the loop.
Is this a good approach or is there a better way? Thanks for your advice.insert newtbl
select id ,
(select s.name from staging s where s.id = t.id) ,
(select s.address from staging s where s.id = t.id) ,
...
from (select distinct id from staging) t
"Phill" wrote:

> I have written a DTS package to import a table from Oracle into SQL Server
.
> This table is defined as Data_Group (which is an employee id), Element_Cod
e
> (a numberic code that tells what data this row has, ie. Name, Address,
> Status, etc), and then the Value. There are several elements that I will
not
> use, but I import them anyway for later use. My next step is to take thes
e
> data elements at build 1 record in my SQL Server table.
> I am thinking about creating a SP that accepts the employee id and then
> builds the record by using a bunch of SQL Update statements. Then write
> another SP that opens a cursur on my input table (SELECT DISTINCT Data_Gro
up
> From T1) and call the SP from within the loop.
> Is this a good approach or is there a better way? Thanks for your advice.|||oops
insert newtbl (id, name, address, ...)
select id ,
name = (select s.value from staging s where s.id = t.id and s.code = 1) ,
address = (select s.value from staging s where s.id = t.id and s.code = 2) ,
...
from (select distinct id from staging) t
"Phill" wrote:

> I have written a DTS package to import a table from Oracle into SQL Server
.
> This table is defined as Data_Group (which is an employee id), Element_Cod
e
> (a numberic code that tells what data this row has, ie. Name, Address,
> Status, etc), and then the Value. There are several elements that I will
not
> use, but I import them anyway for later use. My next step is to take thes
e
> data elements at build 1 record in my SQL Server table.
> I am thinking about creating a SP that accepts the employee id and then
> builds the record by using a bunch of SQL Update statements. Then write
> another SP that opens a cursur on my input table (SELECT DISTINCT Data_Gro
up
> From T1) and call the SP from within the loop.
> Is this a good approach or is there a better way? Thanks for your advice.

Design Choices

I have 20-30 action queries which will be executed nightly. Is it better to place them in a DTS package and call the package, or place them in a stored procedure and call the stored procedure?If you are using a Access dtabase on a web site and using an inc file for your data connections I can down load the database and crack security. As for the performance of Acess it sucks over 1,000,000,000 records.|||This is for a SQL Server database. I'm not looking for your opinion on Access|||depends on what your action queries do. If you need to move data then DTS is the way to go. If you need to work with data inside SQL server then I would faver stored procedures.