Category Archives: SQL Server

Custom Templates for SQL Database Files

Templates in SSMS work really well; if you select to create a new stored procedure, you’ll get a skeleton procedure, and then placeholders, which you can populate by pressing Ctrl-Shift-M

SQLTemplate1

But how can we replicate this same behaviour in a VS DB project?

Out of the box, creating a stored procedure will give you this:

SQLTemplate2

The key to changing the above template is to create your own template. The templates in VS are stored wherever you tell them to be:

SQLTemplate3

In my case:

C:\Users\Paul\Documents\Visual Studio 14\Templates\ItemTemplates

Export Template

So, how to create a custom template for, say, a stored procedure? First, create your template. Typically, this will be from an existing VS template:

SQLTemplate4

Now, make your changes:

SQLTemplate5

And then, export the file:

File -> Export Template

SQLTemplate6

You’ll then be asked which item in your project to export:

SQLTemplate7

It treats DB templates like any other, so the next question relates to dependencies. Finally, it asks what you would like to call your template:

SQLTemplate8

If you select to automatically import into VS, then after restarting VS, you should see your new template:

SQLTemplate9

Using Common Templates with Source Control

You obviously can mail this around to the team, and they can all import it. However, another option is that you can source control it.

If you have a look at the directory above, you’ll see that it has created a file there:

SQLTemplate10

Like any other file, you can simply add this to source control. If you do, then you can change the template directories described above to point at the source control folder. That way, everyone on the team can point to up to date source controlled templates.

Notes

Everything in this article applies equally well to other files – it’s just that it is very well documented for this, but not so well for SQL DB projects.

SQL Server quick insert test data

I recently came across a little known feature of SSMS that made me think that, for small amounts of data, it may be possible to create a procedure to insert test data into a table. Let’s start with the table:

CREATE TABLE [dbo].[Audit](
	[AuditDate] [datetime] NOT NULL,
	[AuditText] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

So, let’s create a statement that will inset data into this table:

INSERT INTO DBO.[Audit]
(AuditDate, AuditText)
VALUES
(GETDATE(), 'The sky is blue')

GO

sql1

Okay, so here’s the thing; try this:

INSERT INTO DBO.[Audit]
(AuditDate, AuditText)
VALUES
(GETDATE(), 'The sky is blue')

GO 10

sql2

So, that’s good, but what if you want some actual kosher data?

Randomising a date is relatively easy, using a combination of the RAND() function and the DATEADD() functions, you can do this in a single line. However, randomising text is more complex. If you just want random strings of letters, then you could try something like this. However, if you want something more sensible, then you can use a lookup table:

DECLARE @lookupTable TABLE (TextLookup VARCHAR(255))
DECLARE @lookupTableSize INT

INSERT INTO @lookupTable
(TextLookup)
VALUES
('red'), ('blue'), ('green'), ('orange'), ('purple')

SELECT @lookupTableSize = COUNT(1) FROM @lookupTable

INSERT INTO dbo.[Audit]
(AuditDate, AuditText)
VALUES
(DATEADD(day, -ABS(CHECKSUM(NewId())) % 30000, GETDATE()),
(SELECT TOP 1 TextLookup FROM (
    SELECT TextLookup, ROW_NUMBER() OVER (ORDER BY TextLookup) AS RowNum
    FROM @lookupTable
) AS DerivedAudit
WHERE DerivedAudit.RowNum >= (ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % @lookupTableSize) + 1))

GO 100


SELECT *
FROM dbo.Audit

It’s worth noting that GO in this case executes the entire script, not just the bit you want; however, I’m not proposing you use this for any serious volume – just for add a quick few records.

SQL Server xp_sprintf and xp_sscanf

I learned about these two functions accidentally last year, and I wanted to try them both out to see what they do. A long time ago, I used to be a C programmer, so the concepts of the two functions are familiar to me (assuming they are similar to their C cousins).

xp_sprintf

This is kind of like the C function sprintf… except it isn’t. Firstly, it only supports the %s placeholder. This means that you can’t represent, or format a float, like you can in C. Secondly, it looks like it only works with strings of 255 characters or less. Here’s an example:

DECLARE @myString varchar (255)
EXEC xp_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', '3', 'TestTable'

SELECT @myString

sql1

Admittedly, that seems a bit pointless; but what if we do this:


DECLARE @colCount INT
DECLARE @colCountStr varchar(255)
DECLARE @myString varchar(255)
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT @colCount = COUNT(COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'TestDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tableName

SELECT @colCountStr = CONVERT(varchar, @colCount)

EXEC xp_sprintf @myString OUTPUT, 
    'There are %s fields in the table %s', @colCountStr, @tableName

SELECT @myString

sql2

It makes a bit more sense. However, there are a number of restrictions with the procedure. There is the 255 characters, you can only substitute strings and, because it’s a procedure, you can’t include it in a query.

A better way: FORMATMESSSAGE

FORMATMESSAGE is a function, but it supports all the standard placeholders:


DECLARE @colCount INT
DECLARE @tableName varchar(255) 

set @tableName = 'Audit'

SELECT FORMATMESSAGE('There are %d fields in the table %s', COUNT(COLUMN_NAME), @tableName) 
FROM INFORMATION_SCHEMA.COLUMNS WHERE 
TABLE_CATALOG = 'TestDB' AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = @tableName

sql2

xp_sscanf

This looks like a far more useful function; as it allows parsing of a string. Whether or not you believe you should be storing your data in a manner that requires parsing, one day, you will need to do it. Consider this table:

scanf

So, I want to analyse this data; how about:

select *
from dbo.[Audit]

DECLARE @colour varchar(255)
DECLARE @text varchar(255)

SELECT @text = AuditText from dbo.[Audit]

EXEC xp_sscanf @text, 'The sky is %s today', @colour OUTPUT

select @colour

sql3

The eagle eyed amongst you might notice a slight issue here – because it’s a procedure, it can’t be used inside a query. It’s also hampered by the same restrictions of 255 characters, etc… I couldn’t find an xp_scanf equivalent of FORMATMESSAGE, so I rolled my own:

CREATE FUNCTION ScanFWrapper 
(	
	@inputText varchar(255),
	@formatText varchar(255)
)
RETURNS varchar(255)
AS
BEGIN	
	DECLARE @Result varchar(255)

	EXEC xp_sscanf @inputText, @formatText, @Result OUTPUT
		
	RETURN @Result

END
GO

Admittedly, it’s not very generic, but you can call it like this:

SELECT dbo.ScanFWrapper(AuditText, 'The sky is %s today') 
FROM dbo.[Audit]

sql4

Conclusion

There are a number of xp_* methods, and they seem to be one-off procedures, so I’m probably being unfair on them in trying to compare them to their C equivalents.

List the Installed Instances of SQL Server

The following code will list all the SQL Server instances on the local machine.

            RegistryView rv = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;

            using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, rv))
            {
                RegistryKey rk = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
                if (rk == null) return;

                foreach(var v in rk.GetValueNames())
                {
                    Console.WriteLine($"{v} : {rk.GetValue(v)}");
                }
            }

            Console.ReadLine();

… assuming the registry is correct

Get the executable location of SQLPackage

In my recent escapades into the murky world of deploying dacpac files into a SQL DB, it occurred to me that the location of the SQLPackage file has largely been a cut-and-paste from the web. Apparently, the registry key that holds this information is: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Data-Tier Application Framework\InstallLocation. Here’s some code to retrieve it using C#:

        private static void GetSQLDacTools()
        {
            using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, _registryView))
            {
                RegistryKey rk = hklm.OpenSubKey(
                    @"SOFTWARE\Microsoft\Microsoft SQL Server\Data-Tier Application Framework\InstallLocation");
                if (rk == null)
                {
                    Console.WriteLine("Sql Data Tools Not Installed");
                    return;
                }

                string path = rk.GetValue(string.Empty).ToString();

                Console.WriteLine(path);
            }            
        }

Insert a Stream into SQL

I have written a couple of articles around this; relating to transmitting large files over WCF and enabling filestream in SQL. This article deals with actually inserting one of those large files into the DB and retrieving it back out again.

The following method does not use FILESTREAM; that requires a slightly different syntax.

The Database

If you have a look at the linked articles, you’ll already have seen how the data that I’m dealing with is arranged; however, here’s a create statement for the table; just in case you want to try this:

CREATE TABLE [dbo].[BinaryDataTest](
	[ROWGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[DataName] [nchar](10) NOT NULL,
	[Data] [varbinary](max) FILESTREAM  NULL,
	[Data2] [varbinary](max) NULL,
UNIQUE NONCLUSTERED 
(
	[ROWGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [fs_fg_filestream]

For completeness, my DB is called TestDB.

You’ll notice that `Data` uses FILESTREAM. However, I won’t cover that in this post.

The Service

Here’s an example of how you would write the insert statement in your service (the same method should work whether or not a service is used):

        public void InsertData(Stream value)
        {            
            string connectionString = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;
            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cn.Open();                

                cmd.CommandText = $"INSERT INTO [dbo].[BinaryDataTest] (" +
                    "[ROWGUID],[DataName],[Data2] ) " +
                    "VALUES (NEWID(), 'test', @DataVarBinary)";
                cmd.CommandType = System.Data.CommandType.Text;

                MemoryStream newStream = new MemoryStream();
                value.CopyTo(newStream);
                SqlParameter sqlParameterBin = new
                    SqlParameter("@DataVarBinary", SqlDbType.VarBinary);
                sqlParameterBin.Value = new SqlBytes(newStream);
                cmd.Parameters.Add(sqlParameterBin);

                cmd.ExecuteNonQuery();
            }
        }

As you can see, I have a connection string called “TestDB”; other than that, I think the only remarkable thing (that is: thing worthy of remark – not astounding) is the SqlParameter set-up. Use the VarBinary SQL type, and the ADO.NET SQL function SqlBytes(), and you’re good to go.

Next, there’s the data retrieval:


        public Stream GetData(string dataName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;
            using (SqlConnection cn = new SqlConnection(connectionString))
            using (SqlCommand cmd = cn.CreateCommand())
            {
                cn.Open();
                cmd.CommandText = "SELECT [ROWGUID],[DataName],[Data],[Data2]" +
                    " FROM [dbo].[BinaryDataTest]" +
                    " WHERE DataName = @DataName";
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("DataName", dataName);
                using (var rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Stream str = rdr.GetStream(rdr.GetOrdinal("Data2"));

                        return str;
                    }
                }
            }

            throw new Exception("Invalid data");
        }

A familiar looking idea. You’ll see that I’m only returning that `Data2` as stated earlier, and am using the SqlDataReader.GetStream() function.

The Client

I’m deliberately missing out the configuration that enables you to send these files, and which is documented here.

Here’s the Main() function of a client console app:


        static void Main(string[] args)
        {
            ServiceReference1.Service1Client svc = new ServiceReference1.Service1Client();
            Stream stream = File.OpenRead(@"c:\tmp\test.bmp");

            svc.InsertData(stream);

            Stream strDest = File.OpenWrite(@"c:\tmp2\testdestination.bmp");
            Stream str2 = svc.GetData("test");                        
            str2.CopyTo(strDest);

        }

Summary

So, we’re reading a file from c:\tmp into a stream, and sending that, via WCF into the SQL DB. Then, we’re reading that back out of the SQL DB, and sending it back over to the client. The client then writes this out to a file.

I fully intend to cover how this differs in a FILESTREAM column in a later post.

Setting up SQL Server to use the FILESTREAM feature

Whilst playing about with this feature of SQL Server, I encountered the above error. This post should lead you around the error. It does not make any claims as to whether using the FILESTREAM feature is a good, or bad idea.

The error:

Msg 1969, Level 16, State 1, Line 14
Default FILESTREAM filegroup is not available in database ‘TestDB’.

The table create statement that caused this was:

CREATE TABLE [dbo].[BinaryDataTest2](
	[DataName] [nchar](10) NOT NULL,
	[Data] [varbinary](max) FILESTREAM NULL,
	[Data2] [nchar](10) NULL
) ON [PRIMARY] 

I have to be honest, and say that I did, initially try to create this through the UI designer. this thread put me straight on that.

So, the next stage was to create a filegroup with the FILESTREAM enabled, but if you do that now, you’ll (likely) get the following error:

Msg 5591, Level 16, State 3, Line 1
FILESTREAM feature is disabled.

This is a property of the SQL Server instance, not the DB:

filestream1

https://msdn.microsoft.com/en-us/library/cc645923.aspx

Next, run SQL Configuration Manager and enable FILESTREAM here as well.

filestream2

(found here on MSDN)

Finally, add a file group with FILESTREAM enabled:

ALTER DATABASE TestDB
ADD FILEGROUP fs_fg_filestream CONTAINS FILESTREAM  
GO  
ALTER DATABASE TestDB
ADD FILE  
(  
    NAME= 'filestream',  
    FILENAME = 'C:\db\fs' 
)  
TO FILEGROUP fs_fg_filestream  
GO  

Obviously, replace “C:\db\fs” with an actual location on your hard-drive.

The next error I got was:

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Okay, so you need to assign a field at a unique identifier:

CREATE TABLE [dbo].[BinaryDataTest](
	[ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
	[DataName] [nchar](10) NOT NULL,
	[Data] [varbinary](max) FILESTREAM NULL,
	[Data2] [nchar](10) NULL
) ON [PRIMARY]

Finally, insert some data into your table:

INSERT INTO BinaryDataTest(ROWGUID, DataName, Data, Data2) 
Values (NEWID()
      , 'test'
      , Convert(varbinary,'test')
	  , null
      );

If you have a look at your (equivalent of) “c:\db”, you’ll see exactly what the effect of this was:

filestream3

Basically, you are now storing the data from the DB in the file system. I re-iterate, I make no claims that this is a good or bad thing, just that it is a thing.

Mock Current Date and Time in SQL Server

Occasionally, if you’re especially lucky, you’ll get into a situation where you have SQL procedures and functions that are so complicated that they require unit tests all of their own. They’ll have business logic embedded in them, and not testing them will leave a massive hole in your test coverage.

In this blog post I’m not going to describe how to do that – SSDT are quite well documented anyway. This is about how to deal with dates and times in SQL Server.

A new function

You’ll probably have a few places in your SQL script that call the following:

SELECT GETUTCDATE()

Or, you may even have the following:

SELECT GETDATE()

Which will presumably work well for what you want. Of course, the problem that you have here is, that for unit tests, this presents a variable factor in your test; that is, you’re not always testing the same procedure. Take the following segments of SQL for example:


PROCEDURE MYPROC
AS
BEGIN
	DECLARE @today DATETIME
	DECLARE @hasEntriesAfterToday INT
	
	SET @today = GETUTCDATE()

	SELECT @hasEntriesAfterToday = COUNT(*)
	FROM dbo.MyTable t
	WHERE t.Col1 > @today

	IF (@hasEntriesAfterToday > 0) 	
		select 'test'
END
GO

MyTable contains many entries after today, and my test checks that it returns ‘test’, so the test works, the code works and I’m going to bed.

But what happens in a year’s time?

Let’s say that the last entity in that table is 01/01/2015 (that way the post works in the US, too). As I write this, it is mid-way through June. So, I need to know what will happen on 2nd January 2015. If I do nothing then when it is 2nd January 2015 the test will start to fail, and I won’t know why.

Abstract the date

When faced with this problem, my initial fix was as follows:

CREATE FUNCTION dbo.MyGetDate()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATETIME
	SET @today = GETUTCDATE()

	RETURN @today
END

And then simply change the above procedure to call this. That certainly works; however, as soon as you start to reference this function (for example, you set a default value for a date in a table), you’ll find that you’ll get stuck when you try to mock it out; consequently, you need a double layer:

CREATE FUNCTION dbo.MyGetDate2()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATETIME	
	SET @today = GETUTCDATE()

	RETURN @today
END
GO

CREATE FUNCTION dbo.MyGetDate()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATE	
	SET @today = dbo.MyGetDate2()

	RETURN @today
END
GO

What this then allows you to do is to replace the function of MyGetDate2 without affecting MyGetDate. This is a wrapper function to replace the DateTime:

internal static void OverrideDateTimeTest(SqlConnection cn, SqlTransaction tr, string newDateTime)
{
    string sql =
        "ALTER FUNCTION dbo.MyGetDate2(	" +
        ") RETURNS datetime " +
        "AS " +
        "BEGIN " +
        "DECLARE @value datetime " +
        "SET @value = convert(datetime, '" + newDateTime + "') "  +
        "RETURN @value " +
        "END";
 
    using (SqlCommand cmd = new SqlCommand(sql, cn, tr))
    {
        cmd.Connection = cn;
        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
    }
}

And here’s the test:

[TestMethod]
public void MyTest()
{
    DBWrapper.OverrideDateTimeTest(cn, tr, "2014-06-10 22:30:00.000");
    Assert.AreEqual( …

The best part about this is that IN SQL SERVER DDL STATEMENTS CAN BE ROLLED BACK! Look at the following test:


-- 1
BEGIN TRAN
GO

-- 2
SELECT dbo.MyGetDate()
GO

-- 3
ALTER FUNCTION [dbo].[MyGetDate2]()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATETIME
	SET @today = GETUTCDATE()

	RETURN @today
END
GO

-- 4
SELECT dbo.MyGetDate()
GO

-- 5
ALTER FUNCTION [dbo].[MyGetDate2]()
RETURNS DATETIME
AS
BEGIN
	DECLARE @today DATETIME
	SET @today = CONVERT(DATETIME, '2014-06-10 22:30:00.000')

	RETURN @today
END
GO

-- 6
SELECT dbo.MyGetDate()
GO

-- 7
ROLLBACK TRAN
GO

-- 8
SELECT dbo.MyGetDate()
GO

Okay – there’s a fair amount of code, but the stages are as follows (numbered):

1. Start the transaction.
2. Show the existing implementation of MyGetDate2 (in case it’s not what it should be).
3. Change MyGetDate2 to use GetUTCDate(), so it should be the same as before.
4. Check again – should still return the same as 2.
5. Change MgGetDate2 to return hard coded date.
6. Check that it now returns a hard coded date.
7. Rollback the transaction.
8. The transaction is rolled back, and so the function behaves as in 1.

Conclusion

So, we can include a date mock in our test and, should there be a problem, or when we’re finished, it all gets rolled back. Just because I’m always cautious about such things, I’ve created a test that checks that the default implementation returns the current date, but you shouldn’t need this.

The server principal “server” is not able to access the database “DBName” under the current security context.

I got this error recently – seemingly for no reason. I thought it might be an idea to share the workaround, as I didn’t find it anywhere when searching (although I did find a mass of other potential solutions – so this is the post I wanted to find while searching).

The Problem

I started getting the error:

The server principal “server” is not able to access the database “DBName” under the current security context.

This occurred after recreating a database and trying to run a stored procedure. It’s obviously a security error; but it appeared that the security was sufficient to execute the SP in question.

Attempt 1

The first thing I came across was this:

SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME,
DP.PRINCIPAL_ID,
DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,
P.CLASS_DESC,
OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME,
P.PERMISSION_NAME,
P.STATE_DESC AS PERMISSION_STATE_DESC
FROM SYS.DATABASE_PERMISSIONS P
INNER JOIN SYS.DATABASE_PRINCIPALS DP
ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID
WHERE P.STATE_DESC = 'DENY'

That showed nothing; i.e. there was no explicit “deny”.

Attempt 2

Next, I came across something that suggested using the following to explicitly grant connect to the current user:

use msdb
grant connect to [domaincurrentuser]

Again, this didn’t work for me.

Attempt 3

I then came across a raft of suggestions to do the following:

1. Log in with Microsoft SQL Server Management Studio and click OK when the error appears.
2. Press F7 to open Object Explorer Details.
3. Right click on the column header at the top of the new window and uncheck Collation
4. In the left column, right-click Databases and click Refresh
5. Find your database and click the + sign to expand it.

It didn’t work (and I couldn’t really work out why it would); but based on the quantity of suggestions and positive response – try it).

Attempt 4

Try running the SP as “sa”. If it still doesn’t work then:

ALTER DATABASE DBName SET TRUSTWORTHY ON

sp_changedbowner 'sa'

If you’re still reading then you may be in the same boat as me; try this:

Attempt 5 – success


USE DBName
GO
EXECUTE sp_grantdbaccess guest

/*
REVOKE CONNECT FROM guest
GO
*/

This WORKED, and I didn’t know why. Firstly, if this IS your problem; don’t leave guest enabled – it’s a LARGE security hole. What it likely means is that your SP is executing as a user that may not exist in your DB. Have a look for a command such as the following somewhere in the SQL:

EXECUTE AS 'SomeUser'

Where ‘SomeUser’ isn’t in your DB. The reason that enabling ‘Guest’ works is that it acts as a fallback; if you try to do something as a user with no permission, it will use Guest if it can’t execute.