Chris May

Last night I had another instance where doing a:

SELECT * FROM
TBL WHERE ID NOT IN (SELECT ID FROM TBL2)

would fail every time to return any rows.

After longer than it should have been, I realized that it was this stupid issue that I blogged about already (SQL "NOT IN" Will Fail If The List Contains A Null).

 

UPDATE 2:

Either this never worked in the first place as I wanted, or MS has changed how they are doing things in SSRS 2008.

I now don't see any of the errors in the tables I had been looking in, and in fact, the errors don't show up in any of their logs either.  This is crap.

If you are like me, you don't like the fact that the first time you realize that a data driven subscriptions in SQL Server Reporting Services has been failing is when someone comes up to you and says "Hey, I did XYZ and I never saw the server kick off an email." 

Really?  Let me check.... oh, looks like it has been broken for god knows how long.  What the heck?

Well, I wrote a script to notify you when there are errors on the report server.

First, you need to setup Database Mail.  Expand Management, and pick Database Mail.  For my script I used the name "Email Profile" for the "Email Profile" get it?

Then create a trigger on the ExecutionLogStorage table like this:

USE [ReportServer]
GO
/****** Object:  Trigger [dbo].[ExecutionLogStorage_Insert]    Script Date: 05/13/2009 10:20:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ExecutionLogStorage_Insert]
ON [dbo].[ExecutionLogStorage]
AFTER INSERT
AS 
BEGIN
    DECLARE @sStatus AS NVARCHAR(32)
    SET @sStatus = (SELECT Status FROM inserted)
    
    IF(@sStatus <> 'rsSuccess')
    BEGIN
        DECLARE @sReportName AS NVARCHAR(425)
        DECLARE @sPath AS NVARCHAR(425)
        DECLARE @ReportId AS UNIQUEIDENTIFIER 
        DECLARE @sUserName AS NVARCHAR(260)
        DECLARE @sParameters AS NVARCHAR(4000)
        DECLARE @dTimeEnd AS DATETIME 
        DECLARE @iTimeProcessing as int
        
        
        SELECT 
          @sReportName = Name,
          @sPath =PATH,
          @ReportId = ReportId,
          @sUserName = username,
          @sParameters = (select CAST(ISNULL(ELS.Parameters,'') AS NVARCHAR(4000)) from ExecutionLogStorage ELS Where LogEntryId = Inserted.LogEntryId),
          @dTimeEnd = TimeEnd,
          @iTimeProcessing = ISNULL(TimeProcessing,0)
        FROM inserted INNER JOIN [Catalog] 
        ON inserted.ReportId = CATALOG.[ItemID] 
        
        DECLARE @sBody VARCHAR(8000)
        SET @sBody = 'Error with SQL Report ' 
                    + CAST(@sReportName AS VARCHAR(100))
                    + ' at path ' 
                    + CAST (@sPath AS VARCHAR(250))
                    + ' with ReportId ' 
                    + CAST(@ReportId AS VARCHAR(100)) 
                    + ' and username ' 
                    + CAST(@sUserName AS VARCHAR(100))
                    + ' Parameters ' 
                    + CAST(@sParameters  AS VARCHAR(5000))
                    + ' End time ' 
                    + CAST(@dTimeEnd AS VARCHAR(50))
                    + ' Status ' 
                    + CAST(@sStatus AS VARCHAR(50))
                    + ' Time Processing '
                    + CAST(@iTimeProcessing AS VARCHAR(50));
        
        DECLARE @sSubject VARCHAR(500)
        SET @sSubject = 'Error With Report ' + @sReportName
        
        EXEC msdb.dbo.sp_send_dbmail 
         @profile_name = 'Email Profile', 
         @recipients = 'email@address.com', 
         @body =  @sBody  ,
         @subject = @sSubject;

    END
    
END

Presto!

 

UPDATE: Added logic to get the status and parameters.

 

I'm working on something where I wanted to know if some parameters to a SQL Server Stored Procedure were nullable (or, in otherwords, if they had a default value).

The API I was working with provided a way to find info about the SP, but the isNullable value was never accurate.

I tried working with with the MS DAABs which have a way to fetch SP parameter info, but this method also produced faulty information.

Looking into the code of the DAAB, it was internally calling:

Dim command As SqlCommand

SqlCommandBuilder.DeriveParameters(command)

Somewhere I came across some code for the DeriveParameters method (I think from the Mono project maybe?) which showed it calling this system SP:

sp_procedure_params_rowset

Which returns output fields,:

PARAMETER_HASDEFAULT

IS_NULLABLE

But, again, neither of these appear correctly.

However, I finally came across this thread where a MSFT poster indicates that this data is simply not available:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/900756fd-3980-48e3-ae59-a15d7fc15b4c/

Instead, you need to parse the SP definition to see what the default parameter value is, which is a pain, but at least I know why these other methods kept not working.

 

One of the challenges of using code generation is to avoid overwriting your custom changes when you re-generate the code later.

This is a trick I am using to avoid overwriting my stored procedures if I update them manually.  The trick is to include a comment indicating that the SP has been code generated, and then to remove that comment if you update the SP.  So lets say that you generate:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
-- CODE GENERATED
SELECT * FROM
OrderDetails
WHERE
OrderId = @OrderId

and you made some customizations to the SP so it now looks like this:

CREATE PROCEDURE OrderDetails_Get
@OrderId as int
AS
SELECT * FROM
OrderDetails
INNER JOIN
Orders ON
Orders.OrderId = OrderDetails.OrderId
WHERE
OrderId = @OrderId

The following code will help you when generating your SPs to avoid overwriting this change:

IF EXISTS(
  SELECT [definition] AS objectText 
  FROM sys.sql_modules 
  WHERE [object_id] = OBJECT_ID(N'OrderDetails_Get', 'P') 
  AND definition LIKE '%CODE GENERATED%')
BEGIN
   -- overwrite is ok
END

 

If you want to check and update the identity on tables in SQL Server, you can use the following code:

select ident_current('tablename') as ident
dbcc checkident('tablename',RESEED,100)
select ident_current('tablename') as newident

 

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

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)

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

The problem is that if you restore a sql server database on a different computer than the backup was created, you can have the problem that the usernames and permissions are somewhat lost.

Each username has an associated SID.  So even though you might have a user named 'Bill' and the restore database might have a user named 'Bill', they will have different SIDs and so the permissions will not be applied to the correct user.

To fix this problem you can follow the following steps:

1) run this TSQL:

sp_change_users_login 'report'

2) This will produce a table showing you Users that having issues.

3) run the following TSQL:

sp_change_users_login 'update_one', 'UserNameFromStep2Table','NameOfUserToUse'

In many cases the 2nd and 3rd parameters will be the same string.

More about this SP can be found here: http://doc.ddart.net/mssql/sql70/sp_ca-cz_4.htm

 

Many times you can have a database that has access to it restricted to SPs.  If the database is application specific, then there is a good chance that 1 user account will need to access all these SPs to run the application.

My friend Phil found and updated this script to run in SQL Server 2005 to generate the necessary code to grant execute rights to all SPs in a database for a given user.

SELECT 'GRANT EXECUTE ON ' + sysobjects.name + ' TO AccountName' + 

CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) 

FROM sysobjects 

WHERE type = 'P' and category = 0 AND name not like 'sp_%'

 

The following content has been cut and pasted from Kathy Kam's Blog. The article was Format String 101.


 

I see stuff like {0,-8:G2} passed in as a format string. What exactly does that do?" -- Very Confused String Formatter

The above format can be translated into this:

"{<argument index>[,<alignment>][:<formatString><zeros>]}"

argument index: This represent which argument goes into the string.

String.Format("first = {0};second = {1}", "apple", "orange");

String.Format("first = {1};second = {0}", "apple", "orange");

 

gives the following strings:

 

"first = apple;second = orange"

"first = orange;second = apple"

 

alignment (optional): This represent the minimal length of the string.

Postive values, the string argument will be right justified and if the string is not long enough, the string will be padded with spaces on the left.

Negative values, the string argument will be left justied and if the string is not long enough, the string will be padded with spaces on the right.

If this value was not specified, we will default to the length of the string argument.

 

String.Format("{0,-10}", "apple");      //"apple     "

String.Format("{0,10}", "apple");       //"     apple"

format string (optional): This represent the format code.

Numeric format specifier is available here. (e.g. C, G...etc.)
Datetime format specifier is available here.

Enumeration format specifier is available here.

Custom Numeric format specifier is available here. (e.g. 0. #...etc.)

 

Custom formatting is kinda hard to understand. The best way I know how to explain something is via code:

 

int pos = 10;

int neg = -10;

int bigpos = 123456;

int bigneg = -123456;

int zero = 0;

string strInt = "120ab";

 

String.Format("{0:00000}", pos);      //"00010"

String.Format("{0:00000}", neg);      //"-00010"

String.Format("{0:00000}", bigpos);   //"123456"

String.Format("{0:00000}", bigneg);   //"-123456"

String.Format("{0:00000}", zero);     //"00000"

String.Format("{0:00000}", strInt);   //"120ab"

String.Format("{0:#####}", pos);      //"10"

String.Format("{0:#####}", neg);      //"-10"

String.Format("{0:#####}", bigpos);   //"123456"

String.Format("{0:#####}", bigneg);   //"-123456"

String.Format("{0:#####}", zero);     //""

String.Format("{0:#####}", strInt);   //"120ab"

 

While playing around with this, I made an interesting observation:

 

String.Format("{0:X00000}", pos);      //"A"

String.Format("{0:X00000}", neg);      //"FFFFFFF6"

String.Format("{0:X#####}", pos);      //"X10"

String.Format("{0:X#####}", neg);      //"-X10"

 

The "0" specifier works well with other numeric specifier, but the "#" doesn't. Umm... I think the "Custom Numeric Format String" probably deserve a whole post of it's own. Since this is only the "101" post, I'll move on to the next argument in the format string.

 

 

zeros (optional): It actually has a different meaning depending on which numeric specifier you use.

 

int neg = -10;

int pos = 10;

 

// C or c (Currency): It represent how many decimal place of zeros to show.

String.Format("{0:C4}", pos);      //"$10.0000"

String.Format("{0:C4}", neg);      //"($10.0000)"

 

// D or d (Decimal): It represent leading zeros

String.Format("{0:D4}", pos);      //"0010"

String.Format("{0:D4}", neg);      //"-0010"

 

// E or e (Exponential): It represent how many decimal places of zeros to show.

String.Format("{0:E4}", pos);      //"1.0000E+001"

String.Format("{0:E4}", neg);      //"-1.0000E+001"

 

// F or f (Fixed-point): It represent how many decimal places of zeros to show.

String.Format("{0:F4}", pos);      //"10.0000"

String.Format("{0:F4}", neg);      //"-10.0000"

 

// G or g (General): This does nothing

String.Format("{0:G4}", pos);      //"10"

String.Format("{0:G4}", neg);      //"-10"

 

// N or n (Number): It represent how many decimal places of zeros to show.

String.Format("{0:N4}", pos);      //"10"

String.Format("{0:N4}", neg);      //"-10"

 

// P or p (Percent): It represent how many decimal places of zeros to show.

String.Format("{0:P4}", pos);      //"1,000.0000%"

String.Format("{0:P4}", neg);      //"-1,000.0000%"

 

// R or r (Round-Trip): This is invalid, FormatException is thrown.

String.Format("{0:R4}", pos);      //FormatException thrown

String.Format("{0:R4}", neg);      //FormatException thrown

 

// X or x (Hex): It represent leading zeros

String.Format("{0:X4}", pos);      //"000A"

String.Format("{0:X4}", neg);      //"FFFFFFF6"

 

// nothing: This is invalid, no exception is thrown.

String.Format("{0:4}", pos));      //"4"

String.Format("{0:4}", neg));      //"-4"

 

In summary, there are four types of behaviour when using this <zeros> specifier:

Leading Zeros: D, X

Trailing Zeros: C, E, F, N, P

Nothing: G

Invalid: R, <empty>

 

Now, that we've gone through the valid specifiers, you can actually use this in more than just String.Format(). For example, when using this with Byte.ToString():

 

Byte b = 10;

b.ToString("D4");      //"0010"

b.ToString("X4");      //"000A"

 

Wow... this was way longer than I expected. The BCL team is having blog day today, I need to get back to posting something for the BCLWeblog.

<Editorial Comment>

One of the lesson I learnt from an earlier post is that, readers are not interested in a post that doesn't give you more information than what MSDN provides. Instead, readers are more interested in seeing stuff that are not available on MSDN. So when I was doing research to post about this topic, I found that MSDN actually talks about exactly what the {0,-8:G2} format does. It is just not easy to find nor centrally located.

For example, in the ToString MSDN Doc, the "Remarks" section covered some basic rules on what a "format string" is. In the String.Format MSDN Doc, the "Remarks" section actually have a pretty detail explaination of what the above format does. Furthermore, MSDN provides a format string overview as well as a the table that specifies all the values that are allowed.

This puts me in an interesting position when writing about this topic. MSDN actually have lots of info that cover it. But since I have also heard more than one person being confused about this topic, I decided to post a summary of the documents and more examples. Do you think this is useful? Should I just stick to posting exclusively on non-MSDN topics?

</Editorial Comment>

To see the effects, just run this script against your database:

PRINT '1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>' +
CONVERT(CHAR(19),GETDATE()) 
PRINT '2) HERE IS MM-DD-YY FORMAT ==>' +
CONVERT(CHAR(8),GETDATE(),10) 
PRINT '3) HERE IS MM-DD-YYYY FORMAT ==>' +
CONVERT(CHAR(10),GETDATE(),110)
PRINT '4) HERE IS DD MON YYYY FORMAT ==>' +
CONVERT(CHAR(11),GETDATE(),106)
PRINT '5) HERE IS DD MON YY FORMAT ==>' +
CONVERT(CHAR(9),GETDATE(),6)
PRINT '6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>' +
CONVERT(CHAR(24),GETDATE(),113)

In case I haven't put this in here before.

Here and here are 2 articles from SQL Junkies.

The first deals with the .NET CLR and it's role in the upcoming version of SQL Server, called Yukon.

The second talks about a tool that you can use to run a stress test on a SQL Servers disk setup.

This article discusses a way to generating a list of create scripts that are in order of dependency, so you don't get all those errors in the QA when you run the script.

Originally posted on 2/4/04, I should revisit this.

Here is a pretty good white paper by a MSSQL MVP about using dynamic SQL in SPs.

This article shows how you can Add SQL Server 2000 Stored Procedures to Visual SourceSafe by Using Visual Studio .NET

Finally found this.

This link points to the getting started page. If you look down the tree on the left, you can see the TSQL Reference node.

SPInfoke is a tool that allows you to call SPs as static methods in a .NET class. I guess it won some awards and stuff, but I don't think (just from looking at the article) that is is nearly as good as the tools from Ellkey.com who's StoredProcToDotNet.htm wraps the SQLHelper classes in the MS Application Blocks for Data Access. They also offer a SP generator called the StoredProcBuilder.

Dynamic vs Static SQL