Today I was setting up a linked server from a SQL Server 2008 machine connecting to a SQL 2000 machine using SQLOLEDB. After configuring the server in 2008 I tested it on SQL 2008 machine and everything seemed to be running fine. When I tested out the linked server in SQL 2000 though I got the following errors:

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’ . (Microsoft SQL Server, Error: 229) 

To fix this error I granted execute rights to the xp_prop_oledb_provider proc via the following script:

GRANT EXECUTE ON sys.xp_prop_oledb_provider TO [LOGIN]

After that I got the following error:

Message: Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server “[Linked Server Name]“.  The provider supports the interface, but returns a failure code when it is used.

To fix this error I found a Microsoft article that describes the needed steps to remedy

In Sql Server 2008, you may be unable to modify tables if they require a table rebuild.  The error you get is as follows:

Saving changes is not permitted.  The changes you have made require the following table to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be recreated.

In order to get around this error you have to click Tools–>Options–>Designers and uncheck the check box for Prevent saving changes that require table re-creation.

Today I was reading over a really great article online.  One of the examples as you could guess was based on one of the SQL Server sample databases.  I really wanted to play around with it a bit but wouldn’t you know it?  I didn’t have the sample dbs installed anywhere.  I typically do not install the sample databases anymore as I always think they just take up space and I will never really use them.  Isn’t that always the case?  Well I went searching on the internet and I found them here on Codeplex and I figured I’d share the link with everyone.  You can download the SQL Server sample databases here.

I was loading  a very large amount of data into a test machine today.  I had the machine completely to myself, I had a backup before the process began and I had a profiler trace running on another machine to monitor every statement processed.  The load was all part of a serialized transaction.  The machine was running SQL Server 2005 Standard edition on a dual core server with 16GB of memory.  It’s not the best machine but for testing environment its more then competant.  So we have a pretty isolated environment and you would think everything should be pretty straight forward. 

Wouldn’t you know it?  Halfway through the 8 hour process, the machine became unresponsive on me.   I couldn’t connect via ssms, DAC, SQLCMD, nothing.  The machine was pegged at 100% CPU, the memory was at 100% as well.  The machine was basically shot and rolling down a hill.  The last statement I saw from my profiler was 20 minutes ago…just got back from lunch by the way, and oh boy….what a day this was working out to be.  And my boss just sent me an email asking how things were going.  I am going to explain how I went about solving this issue but I just want to let you know that this is not something that you can always do, but when in a bind and during certain conditions it is a pretty nifty solution and is why I thought it would make for an interesting article.  I was in a bind and I was trying to perform the action in the fastest possible time because people were waiting for the server, and they really needed to start doing their testing.  Here is what I did:

Since I was unable to connect to the SQL Server instance whatsoever, I had to do some drastic measures.  I had a backup and I was the only one on the server so I was not concerned with data loss.  So I just decided to kill the SQL Server service from the services control panel.  Wouldn’t you know it this hung on me too.   So being the cowboy I am, I just went to task manager and killed the sqlserver.exe while I was at it.  Now, knowing that this is a completely abrupt and unorthodox thing to do, I knew all the dbs were going to have to be recovered by SQL Server when the server was restarted.  I didn’t feel like waiting so I decided to do some more unorthodox things.  I renamed one of my datafiles on the database that I was working on.  Then I restarted the service.  This of course brings the db up, but in suspect mode.  From here you can just romove the suspect db and restore from a backup.  Again this is an unorthodox way, but it works in certain situations and mine was one of them. 

So now where does this leave me.  I had my database back to square one, the server was stable again, but I still have to run this monster data load that needs to be run and figure out just what happened to cause this whole mess.  All my DMV’s are also now useless as the service was restarted and do not contain any of the information I would have had if the darn server wasn’t unresponsive and I couldn’t connect to it.  I opened up the error log on windows only to find that it contains nothing to help me.  The SQL Server error log show’d nada as well.  I did have my profiler trace and I knew the last statement that completed so I tried running the statement it failed on.  No problems…so I started thinking and finally realized the issue. 

Well, I was running a serialized transaction, and because of this it has to keep the entire transaction of updates in memory until the final commit is issued.  When the machine ran out of phicical memory it started to thrash out to disk causing the server to become unresponsive and new connections were unable to connect.  So the moral of the story is to be sure that when you are running a serialized transaction that you have enough memory to hold the entire transaction.

I received this error today while updating an extended property so I thought it would be a good little post to help others in the event that they come accross the same error as well.   Extended properties can be used for a great deal more then just for basic data dictionaries.  I’m currently working on a project where they are extensively using SMO for code generation and they store and engineer their systems information about the sql objects they generate on the extended properties of those sql objects.  While I was deploying a change today I got the following error

“Operand type clash: nvarchar(max) is incompatible with sql_variant ” 

This error seemed to have came about due to an upper size limit on the extended properties.   The default way of passing strings to the parameters in Unicode format as follows:

EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'AdventureWorks Sample OLTP Database';

While this is pretty much how you will see all of the examples, you can convert the variant to pretty much any datatype you like except N)TEXT, IMAGE, TIMESTAMP and SQL_VARIANT.  Since the extended property I was using was passed as Unicode or NVARCHAR(MAX)  it was doubling the size and I ran into the 7,500 byte limit which was 3750 characters while in UNICODE.  To solve the error I  shrunk down the extended property to  a smaller character limit.    So if you get this type of error while working with extended properties take a look at your variables and rememebr that SQL converts them to Unicode which doubles the size of your strings and there is a limit of 7500 bytes.

I recently came across an article by Becky Sweger   that reminded me of a situation I experienced myself a while ago where a transaction did not rollback as planned.  This caused quite a surprise to me when I experienced it myself, so I thought I would write a small blog about it to help others as well.  Say for instance, that you have code that is issuing two or more dml statements  and they are grouped inside of a single transaction.  I will use Becky’s example here for simplicity sake:

BEGIN TRANSACTION

INSERT INTO dbo.Table1 (some COLUMNS)
VALUES (some VALUES)

SELECT @error = @@error, @rowcount = @@rowcount

IF @error <> 0
BEGIN ROLLBACK TRANSACTION RAISERROR('error on the first insert!', 16, 1) RETURN END

[DO STUFF]

INSERT INTO dbo.Table2 (some COLUMNS)
VALUES (some VALUES)

SELECT @error = @@error, @rowcount = @@rowcount

IF @error <> 0
BEGIN ROLLBACK TRANSACTION RAISERROR('error on the second insert!', 16, 1) RETURN END

COMMIT TRANSACTION

One would believe the error handling would be sufficient enough and the transaction would rollback when an error occurs.  While most of the time this is true, there are some occasions where it will not.  For instance, if there is a run-time error on the second statement, the first statement statement would not rollback.  Runtime errors could be anything from a renamed object to a devide by zero error etc.  You would be surprised how often this can happen. 

To prevent this from happening to you try to always include a SET XACT_ABORT to ON statement with all your transactions.  This will specify that the current transaction will automatically be rolled back in the event of a run-time error.

Beware, though, that even when XACT_ABORT is ON, not all errors terminate the batch. Here are the exceptions I know of:

• Errors you raise yourself with RAISERROR.
• Compilation errors (which normally terminate the scope) do not terminate the batch.
• Error 266, Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.

So at a minimum you still need to check @@error after the execution of a stored procedure or a block of dynamic SQL even if you use XACT_ABORT ON.