SQL in the Wild » Blog Archive » Are all updates split into delete-insert?

SQL in the Wild » Blog Archive » Are all updates split into delete-insert?

Interesting post that debunks the myth that ALL updates are split into delete-insert pair.

Closed a SQL query by accident? There is help

Have a look at this post: Oh ****! | Home Of The Scary DBA

And this one:

Recover Backed-up query files in SQL Server Management Studio

AuthorizationManager check failed - SOLUTION!

I found a solution to the above problem first mentioned here today by pure chance. Since PowerShell is part of SQL Server 2008 (I think!), I decided to restart SQL Server 2008 on my machine when the error surfaced yet again today, as I was not in the mood for a reboot (too many open files & sessions).

Guess what, the error disappeared! So there must be some tight integration between SQL Server and PowerShell for this to be the solution. 

Please see this post for a repeatable solution.

Giving IIS 7 AppPoolIdentity account access to SQL Server

IIS 7 has a nifty new feature whereby you can set the identity of the application pool to ApplicationPoolIdentity. It then creates a virtual user with the same name as the name of the application pool, i.e. if you named your application pool Bozo, it will create a virtual user called Bozo, to which you can assign rights.

To allow the virtual user access to SQL Server, do the following:

• In SQL Server Management Studio, open the Security folder, right-click the Logins folder, and click New Login...
• Enter IIS AppPool\{apppoolname}, replacing {apppoolname} with the name of your application pool - DO NOT USE THE SEARCH FUNCTIONALITY
• Assign the necessary database level privledges and click Ok

This has been tested on SQL Server 2005, I would imagine it works in SQL2008 too.

Source: http://efreedom.com/Question/1-1933134/Add-IIS-AppPool-Identities-SQL-Server-Logons

To umlaut or not

A recent project required the bulk insert of data into a table in SQL Server. The file contained data with umlaut (ü) and accent (É) characters.

The vanilla BULK INSERT did not work very well with this, changing it to other characters. After quite a bit of googling, I found the solution was to add

WITH (codepage = '1252')

to the BULK INSERT statement. The default codepage on most Windows computers appear to be 437. The lowercase umlaut's code is 252 in the ANSI table. Character 252 in codepage 437 is 'n', so that's what you'll end up with in your data rather than the umlaut.

Locked out of SQL Server

Our active directory was recently migrated to a new domain, and because of this, I could not log in to SQL Server with my new user account. Problem is, I also forgot my sa password, so I could not add my new user account.

This is where single user mode comes in. You restart SQL Server in single-user mode, add the new user or reset the sa password, and then restart it in normal mode.

What do you need to do to start it in single user mode?

Open SQL Server Configuration Manager and click SQL Server Services
Right-click on the instance and click Properties.
Click the Advanced tab, and type the startup parameters into the Startup Parameters box (the parameters are semi-colon delimited, so add -m; to the front.
Click OK
Restart the database engine.

Remember to remove the single user startup option and restart the database after resetting the password or adding the user!

Reference: MSDN

SQL: Back to basics

Today's lesson might be common knowledge for you, but for me it was a good back to basics again. It concerns the calling of scalar functions from the WHERE clause of a SELECT statement.

One could expect the following statement to execute the function only once, but, then, you'd be wrong.

select * from sys.objects
where dbo.myfunc(1) > 1

SQL server executes the function once for every row in sys.objects, even though it is called with a constant parameter and will never return a different result. Typically this is something you would not pick up in development with a small dataset, but performance will decrease dramatically as the dataset size increases.

So, the right way to write the above SQL statement would be something like

declare @num int
set @num=dbo.myfunc(1)
select * from sys.objects
where @num > 1

Be aware, be very aware!

CLR Procedure, next iteration

We have code that uses an old Extended Stored Procedure written in Delphi that is giving some problems, so I decided to rewrite the procedure in C# as a CLR procedure.

The code has to download emails from a POP mailbox and process the attachments. For the POP code I found a nice POP library: http://dotnetctrlext.sourceforge.net/smtpop.htm

I had to exclude the MessageBuilder class from being compiled, as it was referencing System.Drawing, with which SQL Server has some issues, and I did not want to delve into them forever.

The first challenge was to register the CLR procedures. The database needs TRUSTWORTHY property set to ON, because the CLR procedure needs to be created with UNSAFE permissions as it accesses the network for the POP mail download.

This is the error it gives:

CREATE ASSEMBLY for assembly 'IntRegPopUtil' failed because assembly 'IntRegPopUtil' is not authorized for PERMISSION_SET = UNSAFE.  
The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the 
TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. 
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. 
If not, use sp_changedbowner to fix the problem.

This is the change necessary:

ALTER DATABASE dbname SET TRUSTWORTHY ON

Next is creating the assembly:

create assembly IntRegPopUtil from 'C:\work\sandbox\IntRegPopUtil\bin\Debug\IntRegPopUtil.dll' with permission_set = UNSAFE

And the CLR Procedure:

create procedure IntRegPopProcessMail as external name IntRegPopUtil.[IntRegPopUtil.IntRegBulkMail].ProcessMail

Now we are ready to execute the procedure.

If you get an error like the following when executing your procedure, you need to create the assembly with UNSAFE permissions, as above.

A .NET Framework error occurred during execution of user-defined routine or aggregate "IntRegPopProcessMail": 
System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at SmtPop.POP3Client.Open(String pop3host, Int32 port, String user, String pwd)
   at IntRegPopUtil.IntRegBulkMail.ProcessMail()

The next challenge was to get my code to be able to read its appSettings from sqlservr.exe.config in the SQL Server Binn folder.

You have to restart SQL after creating the sqlservr.exe.config in the same folder as sqlservr.exe. Then the fun starts.

It kept on giving error like the following:

"The value of the property 'key' cannot be parsed.
* The error is: Request failed. (sqlservr.exe.Config line X)".

This seems to be a bug from SQL2005 beta days, the solution is to add a dummy call to the ConnectionStrings property, this causes it to initialize properly. I found the solution here.

int i = ConfigurationManager.ConnectionStrings.Count;

That's it, now the procedure can connect to the POP server to download the mail, and it can use settings from the <appSettings> section in the sqlservr.exe.config.

To update the assembly when you've made changes to the dll, use the following syntax:

alter assembly IntRegPopUtil from 'C:\work\sandbox\IntRegPopUtil\bin\Debug\IntRegPopUtil.dll'

MyTechEd #16 - Inside T-SQL: Enhancements, Techniques, Tips & Tricks

This was a very informative session.

Keyword from the session: Insert statement multiple values, Common Table Expression, Using Hierarchy functionality for sorting, Inline functions, OUTPUT clause for INSERT, UPDATE, DELETE.

Link to presentation: download here

MyTechEd #7 - Guiding Your Query Plans in Microsoft SQL Server for Improved Query Performance

This was a VERY informative session. The presenter knows her stuff. This is also where I picked up on the NOLOCK issue I posted about previously.

With SQL2008 you can now give hints via SQL Management Studio, instead of having to code them into your SQL. This allows for easier application tuning, as no build-test-deploy of the application is needed.

Link to presentation: download here

MyTechEd #5 - SQL 2008: Best Practices and lessons learned

The presentation/presenter was a bit boring. There might be some value in the slides though.

Link to presentation: download here

Common Table Expression (CTE)

Do you know what a Common Table Expression is? It is a temporary named result set that can make your SQL easier to read and maintain.

Where can I use it? Often one encounters a summary screen that has some basic information, along with a sum or count. One example is for instance details about an employee, plus a count of the number of people reporting to him. This can lead to some nasty SQL if you write it all in one statement. This is where CTE steps in. You can create a CTE for the number of people reporting to an employee, and join that with the employees table like it was a normal table.

In the example below the CTE is a result set with a ManagerID and the number of people reporting to that manager. It is then joined to the Employee table. Do you think it makes the SQL cleaner and easier to read?

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS
(
 SELECT ManagerID, COUNT(*)
 FROM HumanResources.Employee AS e
 WHERE ManagerID IS NOT NULL
 GROUP BY ManagerID
)
SELECT e.EmployeeID, e.BirthDate, ISNULL(DirectReports, 0) as DirectReports
FROM DirReps as d right outer join HumanResources.Employee as e on d.ManagerID = e.EmployeeID
ORDER BY e.EmployeeID;
GO

You can find some additional info here.

My TechEd #1 - Dashboards & Scorecards

Presenter: Gavin Russell-Rockliff

This was an overview session to introduce the dashboarding and scorecarding capabilities available in the Microsoft platform.

The central component is PerformancePoint Server 2007, which now comes with MOSS Enterprise. A variety of datasources can be integrated into dashboard/scorecard, including SQL Services Analysis Services, Excel 2007, Excel Services and Relational Tables.

Link to the presentation: download here

PerformancePoint Team Blog

WITH NOLOCK = dirty rotten scoundrels

A comment by a presenter at a SQL Server tuning session at TechEd Africa 2009 sparked my interest, so I decided to investigate.

Do you use WITH (NOLOCK) with each SELECT statement? Do you know what it does?

Let me fill you in. It allows your query to read uncommitted data, i.e. deleted or inserted rows that might still be rolled back. Even worse is that it can double read rows, or even misread rows. Imagine the impact it might have on financial systems...

This might be ok in a data warehouse environment (when the data is not being updated), but certainly is not ok in a transactional database.

If you still need convincing not to to it, look here.

How many rows in all the tables in a database?

Often one needs to know the number of rows in all tables in a database, like when you are writing or executing tests, and need to know if you have test data for a table.

I found a nice query, and adapted it slightly for my use.

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]
WHERE sysobjects.xtype = 'U'
GROUP BY sysobjects.[name]
ORDER BY name

You can find the original here.

What SQL is currently executing?

Have you wondered what SQL is currently executing on SQL Server, or which statement in a stored procedure is currently executing? Below is a script to create the sp_currentsql stored procedure.

I got this contribution from a colleague, thanks J.

CREATE  PROCEDURE [dbo].[sp_currentsql]
AS

SET NOCOUNT ON

CREATE TABLE #SP_WHO2 (
 SPID  SMALLINT,
 Status  NCHAR(30),
 Login  NCHAR(128),
 HostName  NCHAR(128),
 BlkBy  CHAR(5),
 DBName  NCHAR(128),
 Command  NCHAR(16),
 CPUTime  INTEGER,
 DiskIO  INTEGER,
 LastBatch  VARCHAR(20),
 ProgramName NCHAR(128),
 SPID_2 SMALLINT,
 RequestID int
)

INSERT INTO #SP_WHO2
exec sp_who2 active

DECLARE @SPID int
DECLARE @sql_handle binary(20), @handle_found bit
DECLARE @stmt_start int, @stmt_end int
DECLARE @line varchar(8000), @wait_str varchar(8)

DECLARE curSPID CURSOR FOR
 SELECT DISTINCT SPID
 FROM #SP_WHO2
 WHERE Status != 'BACKGROUND' AND HostName != '  .' AND SPID != @@spid
OPEN curSPID


FETCH NEXT FROM curSPID INTO @SPID

WHILE @@FETCH_STATUS = 0 BEGIN
 SELECT @sql_handle = sql_handle,
 @stmt_start = stmt_start/2,
 @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
 FROM master.dbo.sysprocesses
 WHERE spid = @SPID
 AND ecid = 0

 SELECT  @line=SUBSTRING( text,
    COALESCE(NULLIF(@stmt_start, 0), 1),
    CASE @stmt_end
     WHEN -1
      THEN DATALENGTH(text)
     ELSE
      (@stmt_end - @stmt_start)
     END
   )
  FROM ::fn_get_sql(@sql_handle)
 SELECT @SPID SPID, @line SQL

 FETCH NEXT FROM curSPID INTO @SPID
END

CLOSE curSPID
DEALLOCATE curSPID

DROP TABLE #SP_WHO2

GO

Creating a SQL Server CLR Procedure

I ventured into unchartered territory (for myself) and created a SQL Server CLR Procedure today. It is actually very easy.

First, create a class library project in VS 2005/2008. Create a class with a static method that will be the CLR Procedure

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace SQLCLRTest
{
public class SQLCLRDemo
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
   using (SqlConnection conn =
        new SqlConnection("context connection = true"))
   {
       conn.Open();
       SqlCommand cmd = new SqlCommand();
       cmd.Connection = conn;
       cmd.CommandText = "SELECT 'Hello World!' AS Greeting";
       SqlContext.Pipe.ExecuteAndSend(cmd);
   }
}
}
}

Then you have to register the assembly in SQL Server

CREATE ASSEMBLY SQLCLRTest FROM 'C:\work\sandbox\SQLCLRTest\SQLCLRTest\bin\Debug\SQLCLRTest.dll'

Then you create the procedure

CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME SQLCLRTest.[SQLCLRTest.SQLCLRDemo].HelloWorld

You might have to enable CLR execution in SQL2005

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

Then you can appreciate the fruits of your labour

exec HelloWorld

Greeting
------------
Hello World!

(1 row(s) affected)

Now that was easy!

MSDN reference here

Error when creating CLR procedure here

Enabling CLR in SQL Server here

How to connect to SQL Server, VS TFS, etc using Windows Authentication when computer is not on Active Directory Domain

I often move around between different clients, and never join their domains, simply because my machine would get cluttered with policies and logon scripts, so I've encountered this problem before.

To access a fileshare, you just map using

net use \\machinename\ipc$ /user:domain\user

This does not work for connecting to SQL Server with Windows Authentication though. I found this neat little trick to accomplish this:

runas.exe /netonly /user: "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\ide\SqlWb.exe"

This runs Management Studio as the domain user, which can then authenticate to SQL Server using Windows Authentication.

The original article can be found here

SQL Server 2008 Spatial, Google MAPS API

At a recent TechDays event I was introduced to the spatial features in the latest version of SQL Server, 2008. Spatial apps has interested me for a while, but I never really got a chance to do anything, I suppose in a way due to the preceived complexity.

I wondered what I could to with the new sptial features. A farmer on one of the forums I frequent recently suggested that the fellow forumites acquire rainmeteres and report daily rainfall figures, as rainfall often differs sunstantially between areas just a few km apart. Well I thought, why not write an application allowing users to register their geographical locations and record their rainfall, and display the data on a map?

This is where Google Maps API comes in. It allows you to populate a map on your own website with your own objects. I was amazed at the amount of free custom controls available for the Google Maps API, and quickly put together the site.

You can see the end result here.