Chris May

We utilize data driven subscriptions in SQL Server Reporting Services (SSRS) to automate several reports and their distribution to a group of people.  For example maybe when a work order is created in the database a report with info about that order is emailed to everyone who will have to be involved in fulfiling the order. 

We realized that some of these reports were not going out.  By looking at the log files (located at ) it became clear that we were hitting a permissions issue:

ReportingServicesService!library!4!09/25/2008-08:15:04:: 
e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: 
The permissions granted to user 'MYDOMAIN\someuser' are insufficient for performing this operation., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: 
The permissions granted to user 'MYDOMAIN\someuser' are insufficient for performing this operation.

Ok that seemed to make sense.  The user "someuser" had left our company and so I'm sure his account was disabled.  After looking around at the report, it's definition, the subscriptions, the data access, nothing was tied to this old employee. 

But.... the subscription itself still is.

The downside of this is that there is no way to change who "owns" the subscription.

However, you can make the changes manually in the database with the following code:

DECLARE @OldUserID uniqueidentifier
DECLARE @NewUserID uniqueidentifier 

SELECT @OldUserID = UserID FROM dbo.Users 
WHERE UserName = 'MYDOMAIN\someuser'

SELECT @NewUserID = UserID FROM dbo.Users 
WHERE UserName = 'MYDOMAIN\newuserhere'

UPDATE dbo.Subscriptions 
SET OwnerID = @NewUserID 
WHERE OwnerID = @OldUserID

Presto, your subscription has a new owner and will once again start running correctly.

UPDATE: I am going to try to work on something that will monitor the subscriptions and notify me if one of them fail.  Check back later.

Has anyone been getting these errors when serving up SQL Reporting Services Reports?

A connection that was expected to be kept alive was closed by the server

All of a sudden we have been getting a lot of these, and I am not sure at the moment what is causing them.

We have 2 webservers that are hosting the reportviewer control, which loads up reports from a single reporting server.

This guy suggested that this problem was related to some 10 minute timeout, but I haven't been able to replicate his 10 minute problem.

Some other people are talking about overrideing the webservice proxy classes to set KeepAlive = false, but we have no webservice proxy to override as we are using the reportviewer.

Update:

This guy is talking about changing the SSRS session timeout.

Could "rc:Toolbar=false" be the problem?

Update 2:
Looks like this was all caused by the introduction of an IDP by our network guys.

 

Recently I wanted to find the time between 2 datetimes, but I wanted to exclude weekends.  To further complicate matters, I wanted to know hours, not just days.

The "standard" way to do stuff like this is to build out a calendar table that has every date for the next 50 years, along with info like "IsWeekend" or "IsHoliday".

But I found some snippets around that I hoped to use to my advantage, and what I came up with works very well:

DECLARE @start DATETIME
DECLARE @end DATETIME

SET @start = '3/7/08 12:50 pm'
SET @end = '3/11/08 1:50 am'

SELECT (DateDiff(hh, @start, @end) - DateDiff(ww, @start, @end)*2*24)/24.0
       

I won't spend a lot of time explaining why it works, but it basically counts the number of weekend "jumps" that are crossed between the 2 dates, and subtracts accordingly.

 

This is some useful stuff from 4guysfromrolla.com. 

BEGIN TRY
   BEGIN TRANSACTION    -- Start the transaction

   -- Your code here

   -- If we reach here, success!
   COMMIT
END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

We recently ran into a problem with SSRS where our email subscriptions were processing, but no emails were going out.

SSRS doesn't really give you any info in the web interface to give you any clue that something isn't work right, let alone help you figure out what the problem is.

I was able to solve the problem by looking at the various log files that SSRS creates:  ReportServerWebApp__X, ReportServerService__X, and ReportServer__X.

In these config files I was able to find some errors coming back from our mail server. 

This lead me to discover that the problem was with how the email addresses were formatted. 

The issue was that we recently changed which mail server we used for sending out these emails, and we have moved from an Exchange 2003 server to an Exchange 2007 server.

Exchange 2003 would accept domain accounts as destinations for emails, so you could send an email to "cmay" and it would be delivered.  It seems that Exchange 2007 requires that you provide the full email address: cmay@company.com. 

After changing some of the SPs that served up the data, all worked once again.

 

Every now and then I go to my blog to lookup something I wrote before only to find that I never posted it.

I would have bet someone $20 that I had posted something on how to strip off the time from a DateTime in SQL.  Why would you do this?

Well, lets say that have some autogenerated DateTime field (CreatedOn?) that you later want to join with another field, but the other field only knows the DATE when something happened, not the exact Date and Time.

So, you can use the following code to just get the DATE and zero out the time component:

SELECT CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME)

If you are using the NOT IN clause with a SQL Select statement with SQL Server it will not work if there is a null value in the list.  Say for example you want to do:

SELECT * FROM 
Employees 
Where 
Employee.Id NOT IN 
(  
   Select EmployeeId from 
   Salaries 
   where Salary < 100000
)

If the inner select returns a null value then the outer select statement will return no rows.

To try it, just run these 2 SQL statments against any table in your DB.

-- will return rows
SELECT * FROM 
sometable
WHERE ID NOT IN (1,2,3)

-- will return nothing
SELECT * FROM 
sometable
WHERE ID NOT IN (1,2,3, null)

When working with a SSRS report, the report framework will already "Denull" everything.  But in some cases, for example when you are working with numbers, you might want to display a value in lue of the "" that is displayed for nulls.

Now you would THINK, that they would just use the same ISNULL syntax that everyone knows from sql programming.

HAHAHHAHA... of course they didn't do that.

Instead you have to use a combo of IIF and IsNothing like so:

=IIF(IsNothing(Fields!Quantity.Value),0,Fields!Quantity.Value)

It isn't THAT big of a pain, but I wish MS would sometimes just realize how much easier it would be for them to just create an IsNull function in their own codebase than make their users write this out.

Oh well.  I guess when it comes to problems with SSRS, this is waaaaaaayyyyy down my list.

Speaking of "my problems with SSRS"... when are you guys going to support TBLR text??  Drives me nuts.  Everyone does "vertical text" in TBLR format, for pretty much every application, but now in SSRS you are forced to use TBRL.  When you give these reports to engineers that is the very first thing they say "You need to turn this text around the other way".... yes I know, I got my degree in Civil Engineering with a focus on structure design, so when you come from a background of seeing all vertical text (like on any plans) as ALWAYS, by requirement, aligned in a TBLR manner, and then you are forced to cock your head the other way to read it, it is really a pain.

But I guess I would still like to see MS fix their *terrible* PDF rendering first.  I just LOVE watching my reports take 4x as long as in CR, and end up 100x bigger in file size than they are after being saved in acrobat (15 MB for a 1 page PDF, open in Acrobat, save the file again, down to 150KB, with no visible change in appearance or quality).

 

Someone asked me how to do this the other day, and I couldn't find a simple way to do it in the SQL Server Management Studio, but the script is pretty simple:

ALTER TABLE tablename
ALTER COLUMUN
   columnname varchar(50) NOT NULL
GO


ALTER TABLE tablename
ADD CONSTRAINT constraintname UNIQUE( columnname ) 

GO

You just screwed up the database, and now you need to make some surgical repairs to the database.

Or maybe you have data in development and production tables and you want to move some data changes from one to the other.

Basically the problem is that you have 2 tables with the same schema where 1 column has some different values in one table when compared with the other.

You can't do an update on an inner join statement, but you can create an updatable view containing the fields you need and then run an update statement on it.

A view is updatable if it meets the following conditions:

A view is updatable if:

• select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION, or DISTINCT clauses. Aggregate functions can be used in a in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.
• select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.
• The FROM clause in the select_statement references at least one table. select_statement must have more than nontabular expressions, which are expressions that are not derived from a table. For example, this view is not updatable:

CREATE VIEW NoTable AS 
 SELECT GETDATE() AS CurrentDate, 
 @@LANGUAGE AS CurrentLanguage, 
 CURRENT_USER AS CurrentUser 

SQL Servers' GETDATE() function will return the current DateTime.  But what if you just want the current date.

CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)

or

CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME)

See this page for even more options.

Or, if you want to get just the current time out of GETDATE or any datetime for that matter, check out this page (it is one of the FAQs).

 

When you are trying to get the last Identity value in SQL Server, the old way was to use the @@IDENTITY variable.

This, however, can easily give you the wrong answer back.  For example, if you have a trigger inserting a history record that also contains an identity column.

Here is an article that discusses why you should probably stick to SCOPE_IDENTITY().

I was having problems with getting RS to work correctly on a windows 2000 server.  I would browse to the page, and it would prompt me for my credentials.  I would use the Admin loign, and it wouldn't take, finally giving me an error like this:

Server Error in '/Reports' Application.
--------------------------------------------------------------------------------

Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\bin" is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.UnauthorizedAccessException: Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\bin" is denied.

ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.

I found someone who figured out the impersonation problem:

The problem happens when installing RS on Windows 2000 Server that is a domain controller.  RS doesn't do the setup correctly.  The basic steps to fix the problem are

1. Grant impersonate privilege to IWAM_<machine> account (see knowledge base article 824308).  Go to domain controller security policy  in administrative tools. Click security settings -> Click local policies -> click user right assignment.  In the right pane, double click impersonate a client after authentication.  In security policy setting window, click define these policy settings.  Click add -> click browse.  In select users or groups window, select IWAM account name and click add.  Then, click Ok -> Click OK -> Click OK.  At command prompt, type the following command: secedit /refreshpolicy machine_policy /enforce.  Then, type iisreset.

2.  Remove IWAM_<machine> account from guest group.  Go to active directory users and computers in administrative tools.  Open users folder.  In right pane, double click IWAM_<machine>.  Select member of tab.  Remove guest.  Click OK.

3.  Reboot.

4.  Run rsactivate.  From command prompt, change directory to C:\Program Files\Microsoft SQL Server\80\Tools\Binn.  The run following command: RSActivate.exe -c "c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config" -t.

See the following link for more details http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSINSTALL/htm/gs_installingrs_v1_8k82.asp .

For now, this seems to have fixed this problem.

I just came across this nice tip:

One change with SQL Server 2005 is a new option to copy column headers along with the data results.  This option gets set using the query options setting.  To access this setting from SQL Server Management Studio, select Query | Query Options from the menus

You can then paste the columns into excel and you will get the headers as well.

http://www.mssqltips.com/tip.asp?tip=1107

 

Microsoft is releasing "Visual Studio Team Edition 2005 for Database Professionals" (who on Earth comes up with these names????).

MS is holding some launch events all over where you can come (for free) and learn a little about this product, which from what I saw looks pretty slick.

http://www.teams-deliver.com

Red Gate has a pretty interesting tool called SQL Refactor:

http://www.red-gate.com/products/sql_refactor/index.htm

 

The "Layout Code" feature looks awesome.  It basically takes a terrible looking select statement and turns it into a nicely formatted block.

 

Here is a code project article that shows some of these features:

http://www.codeproject.com/showcase/SQLRefactor.asp

 

SQL Server Reporting Services 2005 allows you to connect with a web based ReportViewer control.

This control needs to provide credentials for the reporting server to authenticate the user requesting the report.  By default, the account running the asp.net worker process is used, but you can use the code in this forum posting to create you own custom credentials.

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

 

When you try to assign the web reportviewer control to a height of 100%, it displays as a very short (height wise) format.  For me, adding the style attributes to the form did the trick.

The solution to this problem was found on this page: http://msdn2.microsoft.com/en-us/library/ms252090.aspx.

Considerations for XHTML and ReportViewer Web Server Control

When dealing with money, or numbers, sql server provides a few different datatypes: Decimal, Money, Float among others.

The main difference is that Decimal and Money datatypes are "exact", as opposed to Float which is a "Floating Point Representation".

When dealing with Floats, you can get some odd mathematical results:
http://www.chrismay.org/2005/09/29/BinaryRepresentationOfDecimalValues.aspx

e.g.
0.4 - 0.1 = 0.30000000000000000000004
1.4-1.1 = 0.29999999999999999999998

So when a number is going to represent money, you should use the Decimal or Money datatypes.  Money is (19,4) which means 19 total digits, 4 of which are right of the decimal point.  Decimal can be setup however you want, like Decimal(10,2).

SmallMoney can also be used, which can represent money values under 1,000,000 w/ 4 decimal places.

 

One of the challenges in doing merge replication in SQL Server is dealing with IDENTITY columns.

One of the tricks you can use is "automatic identity range management". 

Basically you tell DB 1 to start at seed 1 and DB 2 to seed at 20,000, and when DB 1 reaches 16,000 or DB 2 reaches 36,000 it will reset those seeds to 40,000 and 60,000.

http://msdn2.microsoft.com/en-us/library/ms152543.aspx

http://msdn2.microsoft.com/en-us/library/ms146907.aspx

It appears to work well.