piątek, 7 grudnia 2012

End of a day with T-SQL

How to get begin/end of a day with T-SQL?

  1. SELECT GETDATE() -- current date and time [2012-12-07 15:04:34.680]
  2. SELECT CAST(GETDATE() AS DATE) -- cropped to date part only [2012-12-07]
  3. SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME) -- back do DATETIME - begin of a day [2012-12-07 00:00:00.000]
  4. SELECT DATEADD(day,+1,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) -- begin of a next day [2012-12-08 00:00:00.000]
  5. SELECT DATEADD(ms,-3,DATEADD(day,+1,CAST(CAST(GETDATE() AS DATE) AS DATETIME))) -- end of a day [2012-12-07 23:59:59.997] -- Accurancy of DATETIME is rounded to increments of .000, .003, or .007 seconds

Solution above works with SQL Server 2008 and above as DATE datatype was introduced then.

wtorek, 27 listopada 2012

Check your internet domain with intoDNS.com

IntoDNS.com is my favorite on-line tool for checking internet domain configuration integrity. It also checks if domain passes some good practices. It's very clear in reading and gives rich descriptions of diagnosed problems.

I suggest you to use intoDNS.com after any internet domain reconfiguration (especially if you edit your zone files with vi ;)

Tip: Assume your domain is contoso.com (I know it's not yours :)
You can query your domain with HTTP GET query like:

piątek, 23 listopada 2012

Does trigger has a schema? (Microsoft SQL Server)

I was wondering if triggers have (or belongs to) schemas.

According to MSDN syntax for CREATE TRIGGER is:

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

But when you expand triggers node under table node in SQL Server Management Studio (SSMS) trigger schema is not shown. Also if you query sys.triggers view there is no schema_id column. But when you query for your trigger name sys.objects views there IS schema_id column. So where is the truth?

So in my lab I made a simple test. I tried to add to one table two triggers with the same name but different schemas, and it occures that:

Msg 2103, Level 15, State 1, Procedure MyNewTrigger, Line 14 Cannot create trigger 'schema2.MyNewTrigger' because its schema is different from the schema of the target table or view.

Conclusion: Trigger has a schema but it has to be the same schema as schema of the table. So it's some way safe to avoid giving schema names together with trigger name in scripts.

czwartek, 22 listopada 2012

Slow death .. of SPID

Just killed long-time SQL process..

kill 57

And now I have to wait till it die for good..

Message says:

SPID 57: transaction rollback in progress. Estimated rollback completion: 84%. Estimated time remaining: 113 seconds.

Conclusion: killing process makes it to rollback uncommitted transactions which may be time consuming. It's not a point-in-time operation as we may used to it.

Tip: Add a shortcut to SSMS SQL templates folder into your SQL templates SVN repository

As my workspace is distributed thru multiple remote desktops (sql servers, admin machines, testing machines) I quite often need to checkout my SQL templates from SVN repository and add them to SQL Server Management Studio (SSMS). But I always forget where to place it. So my trick is to checkout my SQL Templates first - to folder where I put other projects. And then to use (windows explorer) shortcut for quick relocate to SSMS templates folder I make a copy (ctrl+c, ctrl+v) of my SQL templates working directory.

I previous post I gave you same tips where to find SQL templates folder.
Now make use of it and create shortcut.

Ready. In new workspace just checkout your SQL templates project from SVN, double click on shortcut ti relocate to SMSS templates folder and you know where to move your project - move it!. Another tip is to leave the shortcut in folder with other projects for future use, when you need to make some updates to your templates.

poniedziałek, 12 listopada 2012

Find first non-empty, not-null string | NULLIF matrix

If you're MS SQL Server developer, you probably know some of following functions:
  • ISNULL(expr1, expr2) - returns expr2 if expr1 IS NULL; returns expr1 if it IS NOT NULL
  • COALESCE(expr1, expr2, ...) - returns first NOT NULL expression; can have more then 2 expressions; returns NULL if all expressions IS NULL
  • NULLIF(expr1, expr2) - returns NULL if expr1 and expr2 are equal; returns expr1 if expr1 and expr2 are not equal
Now, have some fun with NULLIF..

NULLIF matrix

First not-null and non-empty string in-line function

Now mixing NULLIF with COALESCE you may construct safe in-line function that returns first not-null and non-empty string or empty string of all strings are empty or nulls. You don't have to build CASE statement or use LEN function (to check if string is not emtpy). See this:

NULLIF (with empty string as second param) changes any empty string into NULL so COALESCE won't return empty text. Last param of COALESCE (empty stirings) protects us from returing NULL if all strings are empty or nulls. Of course you may remove last param (empty string) if you prefer NULL if there is no result.

wtorek, 6 listopada 2012

How is SPN related to Kerberos?

Kerberos is authentication protocol. If UserA wants to talk with ServiceB and ServiceB wants to know who is talking with, UserA must be authenticated. Kerberos works in this way: UserA is authenticated in secure way by kerberos server. Then UserA is given a ticket for ServiceB. UserA show ticket to ServiceB. Now ServiceB is sure that UserA is UserA because it trusts Kerberos server and all information written on tickets are secured and verifiable.

So kerberos ticket is a message from UserA to ServiceB. Now, as you know every Windows/Unix service is run on user account. On this scenario user account is not dedicated for human but technically is the same account type as for human user. Well, in fact kerberos ticket is a secure message from UserA to UserB. Secure - in cryptographic manner - it use pair of public/private keys (all messages to UserB are encrypted with his public key and decryptable only with UserB private key; all messages from UserA are signed with his private key and verifiable with UserA public key).

So if UserA wants to talk with SQL Server called SQL1.mycompany.com or HTTP Server called WWW1, how do we know that kerberos ticket is issued to UserS (SQL1) or UserW (WWW1)? This is where SPN appears (Service Princial Name). It connects service names with user account names, so Kerberos knows if UserA asks for a ticket for WWW1 service, ticket must be generated using UserW public key.

In Windows Server SPN are stored in Active Directory as user account attributes. One user account may have many SPN registered (for example: one user account runs multiple instances of SQL Server or one SQL Server is accessed by "short" NetBIOS name (without domain name) or "full" FQDN name (including domain name) - so SQL is registered under two SPNs). SPN is easily registered, listed, unregistered with SETSPN command (built-in since Windows Server 2008).

If you have Kerberos related problem start investigation from checking if user accounts have SPN registered for services they run. Windows Services MMC mangement console is a place where you setup an account for running service, but Kerberos does not know anything about it. SPN is second place where user account to service relationship is registered and the only place where Kerberos reads it. So, all noise about Kerberos and SPN is so simple - Kerberos just needs to know who is hidden under service (who serves a service) to generate ticket that user show to service to say "hello, that's really me, this is my ticket issued by Kerberos - you trust him". And SPN translates service address (SQL Server instance, server name, IP port or URL of webapp) to user name - "target" of kerberos ticket.

niedziela, 4 listopada 2012

What about unhandled exception in catch block?

I was wondering this morning if I have to care about possible unhandled exceptions in catch block. So I made a simple test in C# console project.

Yes, you have to care about possible exceptions even in catch block.

czwartek, 4 października 2012

Disable Skype API and get rid of trojan addons

One of our employee reported me that his Skype is sending itself some dangerous links to his folks.

It occures that some unwanted addons was installed and enabled in his Skype installation. It also occures that other employees opened link retreived from "him" and now they have the same "addon" installed.

The fastest way to get rid of unwanted addons was to disable Skype API at all, by adding one entry to Windows registers.

Distribute this following entry with Group Preferences (Computer Configuration, not User Configuration). Once added to Group Policy, users' computers MUST be restarted while group policies are processed on Windows startup.


środa, 3 października 2012

Where are my SQL templates?

Looking for SQL Server Management Studio templates folder?

Try here..

SQL Server 2012

"%APPDATA%\Microsoft\SQL Server Management Studio\11.0\Templates\Sql"

SQL Server 2008

"%APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql"

SQL Server 2005

"%APPDATA%\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql"