Third Abnormal Form

3 things that really get on my nerves with using SQL Server

2012-01-06T16:28:00Z

Don't get me wrong I love SQL Server, I have been using it for many years in all its Windows favours...

SQL Server 4.21a, 6.0, 6.5, 7, 2000, 2005, 2008, 2008 R2 and 2012

If fact I trained under someone who used SQL Server 1 on OS/2 (there you go revealing my age)

However, there are number of things that get under my skin, and here are my top 3 favourites

1) Actual Execution Plan

   Why is this only displayed once the query has finished executing and returned all the results.

   This is particularly annoying if you have a never ending query, and the reason you are displaying
   the Actual Execution Plan is to endeavor to find the reason for your poor performance in the first
   place! A classic Catch 222 situation.

   I know you can display the Estimated Execution Plan, but this is only an estimation of what is
   going to happen not 100% what actually is happening or has happened.

   I don't know the internal mechanics of SQL Server enough, but there must be a point when the query
   engine has finished all its work and passing on its findings to the execution engine. Why can't
   the execution plan be displayed then!

2) Inconsistent (I believe) DDL Commands

While you can create a Table and an Index in another database. Syntax is

CREATE TABLE AnyDataBase.AnySchema.AnyTable

You can not create a View (or Procedure or Function) in another database!

   CREATE VIEW AnySchema.AnyView
   CREATE PROCEDURE AnySchema.AnyProcedure
   CREATE FUNCTION AnySchema.AnyFunction

I can't see any reason for the limit on this restriction other than maybe working out the metadata across databases
to ensure all objects bind correctly

Also while you always CREATE TABLE and DROP TABLE, CREATE VIEW DROP VIEW etc you

ALTER TABLE ADD COLUMN rather than ALTER TABLE CREATE COLUMN but you still ALTER TABLE DROP COLUMN

To be fair this is probably an ANSI Standard but it is inconsistent!

3) Inconsistent USE of the Command Termination ';' Semicolon Command

   BEGIN;
   PRINT 'Hello World';
   END;

is OK! However if you run:

USE AdventureWorks2008R2;
GO

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY;
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Results in

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'CATCH'.

But

USE AdventureWorks2008R2;
GO

BEGIN TRY;
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH;
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

works OK!

If you read this posting here this gives some explanation. It is partly a BUG, but also partly by design.

http://connect.microsoft.com/SQLServer/feedback/details/498085/msft-mso-end-try-throws-an-error-if-terminated-with-a-semicolon

But I just want consistency!

    Another one is that CTE insist that the previous line is correctly terminated, but (AFAIK) NO other
    function, command does!

    And before someone says it been rolled out incrementally via newer functionality, the even newer MERGE
    statement does NOT insist that the Termination character is present at the end of the previous statement!

I maybe returning to this issue again in the future

SQL Server Error 18452, "Login failed. The login is from an untrusted domain" Windows 7

2012-01-06T11:57:00Z

The full error message is SQL Server Error 18452, "Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication"

I have just got this error message again on a Windows 7 Workstation, that I am using SQL Server Management Studio on.

It reminded me that I wanted to blog about the resolution to this and also add how to solve this for Named Instances as well.

Firstly some background to the issue:

I have a Windows 7 Workstation that I use SQL Server Management Studio to connect to SQL Servers in both a Production (PROD) and TEST Domains. However, I log on to the Workstation using a Local Windows Account. I have to do this as the PROD and Test Domains are predominantly XP, and the Group Policy that was developed for XP is different to that used in Vista and Windows 7. Currently, we are not able to operate in a mixed mode domain as a result the current roaming profiles we have DO NOT WORK for Window 7. This is a major problem because it means whenever you log off you have to re-assign Outlook Exchange connections and you lose all your internet favourites etc.

So I have 3 Accounts all with the same Username

LOCAL\MyUser

PROD\MyUser

TEST\MyUser

I log onto my Windows WorkStation using LOCAL\MyUser and then want to connect seamlessly to both PROD and TEST SQL Servers using SQL Management Studio using Windows Authentication (Not SQL Authentication). I can either do one of three things:

1) I can attempt to use the Shift Key and then Right Click on SQL Management Studio and 'Run as Different User' and use either PROD or TEST Accounts

However, I cannot then intermix in SSMS Local SQL Server and different Domain SQL Servers - I am stuck with just those SQL Servers in the Domain I logged in on.....

2) I can attempt to use a Line Command as detailed by Jason Follas here:

http://www.jasonfollas.com/wiki/(X(1)S(zucey4jfr2sr4maocepgrn55))/Default.aspx?Page=SQL%20Management%20Studio%20on%20Vista&AspxAutoDetectCookieSupport=1

However, again I am limited to using just those SQL Servers in the one Domain, and secondly also I am prompted for the password each time as /savecred does not work with /netonly at the same time.

3) Finally I can attempt to use Windows Credential Manager to store the various Domain Credentials for me. However, when I did this (the first time) it did not work and I still had the SQL Server Error 18452 Message. I could not find out the answer until Google popped up the following link and Arsine's answer (thanks again Arsine!)

http://social.msdn.microsoft.com/Forums/hu-HU/sqlsecurity/thread/c05a90e4-cb16-46f6-9072-37083c65696d

I have to create a Windows Credential and add the SQL Server Port (1443) to the end of the Internet or Network Address in the form :<SQL_Port_Number>

So to seamlessly connect to SQL Servers in Any Domain from Windows 7 in SQL Management Studio you need to

•         Use Control Panel, User Accounts, Manage Your Credentials, Add a Windows Credential

•         Add all the SQL Servers you want by Fully Qualified Name but you must include the PORT Number as say :1433

•         Example:

•         ANYSERVER.ANYCOM.TEST:1433

•         Username: TEST\AnyUser
•         Password: TEST Password

•         Press Add Button

Note: Each SQL Server will have to be added separately

Note: If there are any Password changes then all references in Windows Credentials will need to be updated

This way you can control all SQL Servers in ALL Domains seamlessly from one instance of SQL Server Management Studio

I would like to add two additional points to Arsine's excellent posting

A) I find that it is best to Fully Qualified Name the ServerName in Windows Credentials and SQL Management Studio rather than just the Servername . So use ANYSERVER.ANYCOM.TEST rather than just ANYSERVER

B) If you have a NAMED INSTANCE of SQL Server you must create multiple entries in Windows Credentials, one using the default SQL Server Port 1433 and one for each Port allocated for the Named Instance such as 2335 so use

ANYSERVER.ANYCOM.TEST:1433

as well as

ANYSERVER.ANYCOM.TEST:2335

Note: Of course this will have to be defined as ANYSERVER.ANYCOM.TEST\NamedInstanceName in SQL Management Studio

To find out the Port that SQL is running on (there are many methods) I suggest you read the link below by Colin Stasiuk which contains by far the most comprehensive solutions I can find. I didn't like hunting throug Registry Entries and looking at the SQL Server Logs was by far the quickest solution (noting the 'possible gotcha' detailed )

http://sqlserverpedia.com/blog/sql-server-bloggers/what-tcp-port-is-sql-server-running-under/

Finally I know I could have used SQL Server Authentication without a problem to achive the same thing but I did not want to go through this less secure method.

PS. To avoid having to use the Shift Key when Right-Clicking for 'Run As Different User" in Windows 7:

Just go to registry run regedit.exe, search for “runasuser”, then under it you’ll find empty value REG_SZ named “Extended”.
Delete every “Extended” (there should be around 5 of them
Result is that there is no need to press shift key anymore, the option will ALWAYS appear when you Right-Click

A way to find the Real Total Lines of T-SQL Code in a SQL Database

2012-01-03T14:42:00Z

Version 2

In my previous post

http://consultingblogs.emc.com/paulmcmillan/archive/2012/01/03/find-the-total-lines-of-t-sql-code-in-a-sql-database.aspx

I said that the code was not perfect because it just counts the number of Line Feeds and you could have a number of
lines which do not have any text and as such these will be added to the count line total. Therefore you need to
eliminate any lines with just "whitespace" to get the "real" line count value from the actual line count. I said that perhaps I would have a go at doing this in the future...

Well I realised that this was quiet easy to do, by extending the logic

The logic of the code is to get the total length of text and also the total length of text with all the Line Feeds removed
NCHAR(10). The difference minus 1 is the Number of Lines in the Text.

To eliminate all Whitespace lines (those with no code in) which could consist of any or all of the following

Spaces Characters NCHAR (32)
Tab Characters NCHAR (9)
Carriage Returns NCHAR (13)
Corrupted Carriage Return and Line Feed Lines NCHAR (13) + NCHAR (10) + NCHAR (10) - I have seen instances of this

I would need to remove these instances of 'Noise' from the text first and then find the total length after removing the Line Feeds from what remained after the noise was removed.

I have also added in the Schema Name to the Object Name as the initial version did not have this information and I noticed
it was possible to skew the results if Objects had the same name but in different Schemas

The Version 2 of this code is below....

CREATE PROCEDURE [dbo].[esp_GetObjectNumberOfRows]

(

@DatabaseName SYSNAME = '',

@GroupingType NCHAR (1) = 'N', -- N = Object Name, T = Object Type, D = Database

@ExecuteSQL BIT = 1, -- Default Execute SQL On

@PrintSQL BIT = 0 -- Default Debug SQL Off

)

Paul McMillan

EMC Consulting 2012

Version 1

My Project Manager asked me to find out 'How many lines of code have we in the System'. I could not

believe that no one has ever had to do this before so I googled 'sql server stored procedure number of lines' and found

the following posting from Gordon Bell (many thanks!) on Stack Overflow in November 2008. See link below

http://stackoverflow.com/questions/291574/query-to-list-sql-server-stored-procedures-along-with-lines-of-code-for-each-pro

However, the solution Gordon provided did not allow you to pass in a DatabaseName as a Parameter (and I had to find this

out for all the databases in a system) - I could have run it in each database individually but I thought I would take

the base code and allow a DatabaseName to be passed in as a parameter.

Furthermore, I thought I would update the code to use the latest SQL sys.objects and sys.sql_modules system tables

rather than older sysobjects ans syscomments system tables.

Additionally, I thought I would add a Grouping Parameter, so you can return either the

1) Total Lines of Code for each Stored Procedure and each Function for any Database. GroupingType = 'N' - Default

2) Total Lines of Code for all Stored Procedures and all Functions for any Database. GroupingType = 'T' - Default

3) Total Lines of Code for all Stored Procedures and Functions for any Database . GroupingType = 'D'

Finally as this is written using Dynamic SQL I have a Switches for Print SQL (Debug) and Execute SQL

Version 2

I said that the code was not perfect because it just counts the number if newlines and you could have a number of

lines which do not have any text and as such these will be added to the count line total. Therefore you need to

eliminate any lines with just "whitespace" to get the "real" line count value. I said that prehaps I would

have a go at doing this in the future...

Well I realised that this was quiet easy to do, by extending the logic

The logic of the code is to get the total length of text and also the total length of text with all the Line Feeds removed

NCHAR(10). The difference minus 1 is the Number of Lines in the Text.

To eliminate all Whitespace lines (those with no code in) which could consist of any or all of the following

Spaces Characters NCHAR (32)

Tab Characters NCHAR (9)

Carriage Returns NCHAR (13)

Corrupted Carriage Return and Line Feed Lines NCHAR (13) + NCHAR (10) + NCHAR (10) - I have seen instances of this

I would need to remove these instances of 'Noise' from the text first and then find the total length after removing the Line Feeds from what remained after the noise was removed.

I have also added in the Schema Name to the Object Name as the initial version did not have this information and I noticed

it was possible to skew the results if Objects had the same name but in different Schemas

BEGIN;

IF @DatabaseName = ''

SET @DatabaseName = DB_NAME(); -- Default is current database

DECLARE @SQLMessage NVARCHAR (MAX);

-- Testing Code

--DECLARE @DatabaseName SYSNAME = 'master';

--DECLARE @GroupingType NCHAR (1) = 'T'; -- N = Object Name, T = Object Type, D = Database

SET @SQLMessage = '

SELECT

' + NCHAR (39) + @DatabaseName + NCHAR (39) + ' AS DatabaseName,

' +

CASE WHEN @GroupingType = 'N' THEN 'T.ObjectName, ' ELSE '' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN '

CASE WHEN T.ObjectDescription = ''Stored Procedure''

THEN SUM(T.ProcedureCount)

ELSE SUM(T.FunctionCount)

END AS ObjectCount,' ELSE 'SUM(T.ProcedureCount) + SUM(T.FunctionCount) AS ObjectCount, ' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'T.ObjectDescription, ' ELSE '' END + '

SUM(T.ActualLinesOfCode) - 1 AS TotalActualLinesOfCode,

SUM(T.RealLinesOfCode) - 1 AS TotalRealLinesOfCode

FROM

(

SELECT

CASE WHEN o.type = ''P'' THEN 1 ELSE 0 END AS ProcedureCount,

CASE WHEN o.type IN (''FN'', ''IF'', ''TF'') THEN 1 ELSE 0 END AS FunctionCount,

s.name + ''.'' + o.name AS ObjectName,

(LEN(C.definition) - LEN(REPLACE(C.definition, NCHAR(10), ''''))) AS ActualLinesOfCode,

(LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.definition, '' '', ''''), NCHAR(9), ''''), NCHAR(13), ''''), NCHAR(10) + NCHAR(10), NCHAR(10)), NCHAR(10) + NCHAR(10), NCHAR(10))) - LEN(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(C.definition, '' '', ''''), NCHAR(9), ''''), NCHAR(13), ''''), NCHAR(10) + NCHAR(10), NCHAR(10)), NCHAR(10) + NCHAR(10), NCHAR(10)), NCHAR(10), ''''))) AS RealLinesOfCode,

CASE WHEN o.type = ''P'' THEN ''Stored Procedure'' -- STORED_PROCEDURE

WHEN o.type IN (''FN'', ''IF'', ''TF'') THEN ''Function'' -- SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, TABLE_FUNCTION

END AS ObjectDescription

FROM ' + @DatabaseName + '.sys.objects o

INNER JOIN ' + @DatabaseName + '.sys.sql_modules c

ON c.object_id = o.object_id

INNER JOIN ' + @DatabaseName + '.sys.schemas s

ON o.schema_id = s.schema_id

WHERE o.type in (''P'', ''FN'', ''IF'', ''TF'')

) AS T

' +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'GROUP BY ' ELSE '' END +

CASE WHEN @GroupingType = 'N' THEN 'T.ObjectName, ' ELSE '' END +

CASE WHEN @GroupingType IN ('N', 'T') THEN 'T.ObjectDescription' ELSE '' END + '

ORDER BY 1' + CASE WHEN @GroupingType IN ('N', 'T') THEN ', T.ObjectDescription DESC' ELSE '' END

+ CASE WHEN @GroupingType IN ('N') THEN ', T.ObjectName ASC' ELSE '' END + ';';

IF @PrintSQL = 1

PRINT @SQLMessage;

IF @ExecuteSQL = 1

EXEC (@SQLMessage);

END;

Find the Total Lines of T-SQL Code in a SQL Database

2012-01-03T12:06:00Z

My Project Manager asked me to find out 'How many lines of code have we in the System'. I could not
believe that no one has ever had to do this before so I googled 'sql server stored procedure number of lines' and found
the following posting from Gordon Bell (many thanks!) on Stack Overflow in November 2008. See link below

http://stackoverflow.com/questions/291574/query-to-list-sql-server-stored-procedures-along-with-lines-of-code-for-each-pro

However, the solution Gordon provided did not allow you to pass in a DatabaseName as a Parameter (and I had to find this
out for all the databases in a system) - I could have run it in each database individually but I thought I would take
the base code and allow a DatabaseName to be passed in as a parameter.

Furthermore, I thought I would update the code to use the latest SQL sys.objects and sys.sql_modules system tables
rather than older sysobjects ans syscomments system tables.

Additionally, I thought I would add a Grouping Parameter, so you can return either the

1) Total Lines of Code for each Stored Procedure and each Function for any Database. GroupingType = 'N' - Default
2) Total Lines of Code for all Stored Procedures and all Functions for any Database. GroupingType = 'T' - Default
3) Total Lines of Code for all Stored Procedures and Functions for any Database . GroupingType = 'D'

Finally as this is written using Dynamic SQL I have a Switches for Print SQL (Debug) and Execute SQL

Note: The code is not perfect because it counts the number if newlines and you could have a number of these with no text on them and these will be added to the count line total. Therefore you need to eliminate any lines with just whitespace from them to get the "real" line count value. Perhaps I will have a go at doing this in the future.

The code to run this is below

and it is called via

EXEC dbo.esp_GetObjectNumberOfRows

@DatabaseName = 'master',

@GroupingType = 'N',

@ExecuteSQL = 1,

@PrintSQL = 0;

CREATE PROCEDURE dbo.esp_GetObjectNumberOfRows