A blog about one man's journey through code… and some pictures of the Peak District

Category Archives: sql

Based on this earlier post, I returned to my test project to find that it didn’t actually pass anymore. The first issue was that the test couldn’t locate Sqllite3.dll.

Thanks to this question on every programmers favourite web site, I realised that I needed to add an extension for SQLite.

The next issue was when I tried to add a test to create a complex table:

Don’t know about [EntityName].

What I mean by a complex table, and the thing that CreateTable doesn’t like, is an entity that references another entity (the same is true if you try to reference the same entity).

Finally, you’re not allowed to use the System.Object type:

A fix

It is my understanding that, if you can get it to work with SQLite, Entity Framework will solve this problem for you. I took more of a roll-your-own approach. The key issue here is that you want SQLite to not try to serialise the second object; and for this, you can use the [Ignore] attribute:

So, that stops it crashing. The next part is largely dependent on your data: if you have a basic primary / foreign key one-to-n mapping then this will work; however, anything more complex, and you’ll probably have to write a custom abstraction for the data. All that said, my solution starts with a base entity class:

The PropertyChanged goes without saying (it’s like the roads), but the interesting part is the Key. Based on the code above, each entity needs a uniquely identifiable key; in this case, it’s a string; however, this can be any primitive type. In the derived class, you’ll need to map the type to the data; for example:

As you can see, for each reference, it simply recursively calls itself to add that, too. This will spectacularly fall down if you have a circular reference (so don’t do that, or don’t use this if you do).

I’ve recently started investigating the use of SQLite for the purpose of storing data in my UWP application. Before I start, the following are the best tutorials that I could find on the net to get started with SQLite:

Finally, the nuget package to integrate SQLIte with your UWP app is here.

I didn’t find too much information on checking if a table exists, and I think it’s quite a key thing to be able to do, as you can’t really have a deployment script that runs when you deploy your UWP app. As a result I create a base data access class. Let’s start with the interface:

That’s it; I’m still not sure how I feel about SQLite. For a small, local storage, it feels like it could be an XML file; but I suppose it comes into its own when we have a potentially large local storage.

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

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:

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:

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:

Now, make your changes:

And then, export the file:

File -> Export Template

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

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:

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

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:

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.

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:

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:

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:

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:

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:

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:

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.

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:

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.