OMG – MSIE 9 beta broke my SQL Management Studio

A nasty shock after installing Internet Explorer 9 beta:

Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.VisualStudio.OLE.Interop.IServiceProvider’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{6D5140C1-7436-11CE-8034-00AA006009FA}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0×80004002 (E_NOINTERFACE)). (Microsoft.VisualStudio.OLE.Interop)

Fortunately this has been seen before in other circumstances, and the soulution that worked immediately for me was as described in http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/d5d3e5fc-d8ce-4f42-b7ea-9bbbb7756a20/:

regsvr32 "C:\Program Files (x86)\Internet Explorer\ieproxy.dll"

(I’m running 64 bit)

An error was received from SQL Server while attempting to reverse engineer elements of type Microsoft.Data.Schema.Sql.SchemaModel.ISql100DatabaseEncryptionKey

Re my previous post on how not to be a sysadmin when comparing databases my joy has quickly been struck down by the above message!

Read more about Microsoft not acknowledging this to be a problem here Schema Compare permission error for Database Encryption Keys even though the type is set to be ignored

Blast! I can compare the schemas but not generate any script – even though cryptographic keys have nothing to do with it – because of this:

I’ve had success with the data compares, however, so a little progess was made after all.

Using Visual Studio 2010 Schema and Data Compare against a read-only login.

Today I wanted to do a schema and data-compare against a (production) server where I only have a basic db_datareader access.

Ususally I employ the RedGate tools for tasks like this, but in this case I only succeded in getting the message “The user does not have permission to perform this action”.

Having spent ½ a day on testing on a another server I gave up: I was only able to get the RedGate tool to work if I was an administrator on the SQL server – something that wouldn’t happen in my real world. RedGate’s documentation didn’t help, and as I’m running their version 7 (they are now on 8 ) and SQL 2008R2 I decided to try a different approach.

I fired up the VS2010 tools and initially got similar “access denied” messages again. However, the RedGate documentation hinted that using the SQL command VIEW DEFINITION might solve the problem. While it solved nothing for the RedGate tools it actually made the VS2010 tools work! They are definitely not as slick as RedGate’s tools, but I’m here to get the job done so good enough is good enough.

The syntax loks like this:

USE master

GRANT VIEW ANY DEFINITION TO <mylogin> (see [1] below)

GRANT VIEW ANY DEFINITION TO PUBLIC (see [2] below)

USE <databasename>

GRANT VIEW DEFINITION TO PUBLIC

GRANT VIEW DEFINITION TO <mylogin> (see [3] below)

For more examples look at this article: Granting View Definition Permission to a User or Role in SQL Server

If you want to use MSSQL Server Management Studio the various types of VIEW DEFINITION are hidden in various places:

1) Right click on the server object > Properties > Permissions (sets access by login)

2) Open Server Roles > Right click “public” > Permissions (sets access by role)

3) Right click on a database > Properties > Permissions > Select a user > Find View definition > check Grant (sets access by database/login)

Two ways to edit more than 200 rows in SQL Management Studio

Here are 2 different solutions to a common problem.

Solution 1, changing the setting of 200 rows permanently:

In Management Studio’s top menu select Tools -> Options -> SQL Server Object Explorer –> Commands and change the setting you see below.

 

Solution 2, changing the setting of 200 rows temporarily:

When the Edit window has been opened you right-click on it and select Pane –> Sql like this.

This will open a Query box where you can change the SELECT TOP(nnn) statement used for editing. After changing it, click on the red exclamation mark (!) to update the selection.

As an extra bonus this method also makes it possible to select a subset of rows to be edited by using a WHERE statement.

Build failed due to errors in [..] .dbml

What a drag. Increasingly my VS 2008 has begun issuing this error when I open a trustry old project from TFS and start working on it.

There are a couple of links for it on Google, and the solution that helped in my case is this:

1) Go to "c:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\"

2) Run the command devenv /resetskippkgs

Perhaps I’ve overloaded my VS with add-ons, plugins, uninstalls and what have you not. Anyway, today is VS 2010 RTM day so anytime soon now I’ll have a shiny new VS install where a lot of the stuff will be built in.

SQL 2008 Geo-location – an easy beginning

I was just playing around with SQL Server 2008’s, converting an old waypoint table with latitude/longitudes as columns. It was a lot easier than I thought, though the syntax seems a bit strange.

Here is my sample where I convert the old values to a new geography column, query it and calculate the distance between two points (in metres). The really weird thing is the magic number 4326, mening WGS84 – but more about that on Wikipedia.

-- SRID: 4326 = WGS84

UPDATE WPGEO
SET GEO =
geography::STPointFromText(
    'POINT('
    + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

SELECT geo.STAsText() FROM wpgeo WHERE Id =2166
SELECT geo.STAsText() FROM wpgeo WHERE Id =2167

DECLARE @geog1 GEOGRAPHY;
DECLARE @geog2 GEOGRAPHY;
DECLARE @result FLOAT;

SELECT @geog1 = Geo FROM wpgeo WHERE id = 2166;
SELECT @geog2 = Geo FROM wpgeo WHERE id = 2167;
SELECT @result = @geog1.STDistance(@geog2);
SELECT @result

The results look like this:

POINT (11.0802 55.5276)

POINT (11.0343 55.5578)

4438,54421068124

This is – in my opinion – pretty cool stuff…

Using SQL Extended Properties on a table.

This is a continuation of a previous post where I put a version number on the whole database.

To do the same thing on a particular table take a look at this sample:

USE sampledb

EXEC sys.sp_dropextendedproperty
@name = N'Version'
, @level0type = N'SCHEMA', @level0name = dbo
, @level1type = N'TABLE', @level1name = Table_1
;
GO

EXEC sys.sp_addextendedproperty
@name = N'Version'
, @value = N'test 01.02'
, @level0type = N'SCHEMA', @level0name = dbo
, @level1type = N'TABLE', @level1name = Table_1
;
GO

EXEC sys.sp_updateextendedproperty
@name = N'Version'
, @value = N'test 01.03'
, @level0type = N'SCHEMA', @level0name = dbo
, @level1type = N'TABLE', @level1name = Table_1
;
GO

The first time the above script is run, of course, the sp_dropextendedproperty will give an error of course.

The trick (in my case) was to get the SCHEMA name right – the rest should be a piece of cake!  Otherwise the MS documentation for the sproc’s is pretty clear – they just use a schema name that wasn’t immediately clear to me.

ORDER BY is undefined in SQL Server Views

Just found out (the hard way again, of course) that ORDER BY does not work with views, unless it is needed for selecting the TOP n records. As the documentation specifies:

“When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.”  (http://msdn.microsoft.com/en-us/library/ms188385.aspx)

For my code it meant that an .OrderBy() method had to be added to the LINQ that specified the data that was pulled from the view – in other words orderdering is never implicit.

Easily restore SQL Server Database with T-SQL script

Doing test driven development often requires you to reset your database to a well-defined state. For this purpose  I’ve come up with the following T-SQL that first clears all outstanding connections to the database, enabling the restore, then restores it from its backup set. Note that ‘WITH FILE=2’ points to the second backup set in my backup file, allowing me to choose from different bases (sets) in the same backup file.

——————————————————————————
– Kill all current connections
——————————————————————————
DECLARE @sqlcmd VARCHAR(8000);
SELECT @sqlcmd=COALESCE(@sqlcmd,”)+‘KILL ‘+CAST(spid AS VARCHAR(10))+‘; ‘
   FROM sys.sysprocesses
   WHERE DBID=DB_ID(‘OEDB’) AND HOSTNAME <>”
PRINT @sqlcmd
EXEC(@sqlcmd)
—————————————————————————-
– Restore test-data
—————————————————————————-
RESTORE DATABASE[OEDB]
    FROM  DISK=‘C:\OEDB.bak’
   WITH  FILE=2
—————————————————————————-

Of course you can execute this automatically from your test code – using SMO (Systems Management Objects) is another option for doing a programmatic restore. I’ve experimented with SMO, but the scripting seems so much easier.

Maintaining version number metadata in your SQL Database

When working with a SQL server database you may be in doubt as to what version etc you are using in your development, test, production. I suggest the use of SQL extended properties (though you might also just have a “Version” table) so for instance your tests always run on the expected data-platform! Here are the T-SQL commands to make use of the extended properties on the database level, though you can also use SMO:

EXEC sp_addextendedproperty
@name = 'Version',
@Value = '01.01'

SELECT name, value
FROM sys.extended_properties
WHERE class = 0

EXEC sp_updateextendedproperty
@name = 'Version',
@Value = '01.02'

SELECT name, value
FROM sys.extended_properties
WHERE class = 0

EXEC sp_dropextendedproperty
@name = 'Version'