August 2007 Blog Posts
An interesting topic came up a few days ago in the forums about database compatibility.
It turns out you can still run CLR functions, sprocs etc when changing the compatibility level from 90 to 80.
Of course i had to test this behaviour because i had a hard time believing it.
i used my high precision timer for performace measurements which is written in C# as a test bed.
You can find the whole script in that post.
When i created the database and all CLR functions I changed the compatiblity level to 80 with:
EXEC sp_dbcmptlevel testCLR, 80;
Then I tried using the CLR functions and they worked just...
A negative Width value??
I can hear it now:
Are you insane Mladen?? Do you want our apps going around looking ugly??
Well... of course I do! It means i can go around fixing them for big bucks :))
But joking aside, -1 and -2 width values are very usefull.
Setting the width to be -1 means that the column will have the width of the longest item in it.
Setting the width to be -2 means that the column will have the width of the column heading.
Pretty cool, no?
In a lengthy article about WebDAV i've written a long while ago I've explained how and what WebDAV is and what are the problems you might encounter. Suggesting by the comments (over 200) it's a pretty popular topic but there's no help on the net for anything more complex than simple operations because MSDN suggest that WebDAV is totally unsupported. So anything you do with it you're on your own. Having said that let's see how to do those 2 things from the title: Cancel a Meeting Request 1. get the Meeting Request URI from the inbox 2. get the...
I've written two more articles on SQLTeam about Service Broker.
The first one shows how to deploy a One to Many scenario over multiple servers. I've shown how to implement auditing from many child servers to one central storage server that holds all data. This is also applicable in a reverse situation of One to Many
The second one explains how and where to start looking when you have to troubleshoot and debug it.
Go and have a read.
Service broker is fun stuff!
And no, you shouldn't look at the back of it. :)
Whenever I talk to anyone about looking at the computer monitor for a longer period of time everyone thinks i'm nuts when i say i can look at it for 12 hours straight with no problem at all.
After that statement I get the usual dose of: "You're such a geek!", "Sure way to ruin your eyesight!" and "Don't you have a life?" replies.
Just to set things straight: "Yes I'm a geek with a touch of Nerd and proud of it!", "Not really.", "Yes I do have a life....
Recently i'm seeing a lot of questions about how to modify data in the tables which are part of the multi-join select statement.
And i keep wondering how can this be so hard to remember. The methodology is quite simple.
More here.
This is acctually quite simple. There is 'startup' option that you can set to the procedure.
There are a few limitations though:
- your stored pricedure must reside in the [master] database
- it's owner must be dbo
- it musn't have any input or output parameters
Here is some sample code:
USE master;
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
IF OBJECT_ID('spTest') IS NOT NULL
DROP PROC spTest
GO
-- crate a test stored procedure
CREATE PROC spTest
AS
-- just create a sample database
EXEC('CREATE database db1')
GO
-- set it...
Some time ago i've written about the TableDiff.exe utility. It's a great tool for quick table comparisons and i've grown tired of typing it in the command line.
That's why I created this simple TableDiff GUI to help me along and i've decided to share it with the world. :)
You can download it here.
Hope you find it usefull.
In a previous post I've shown what locks are held when doing "update if exists, insert if not" problem in 2 different ways. Of course i had to see how MERGE statement (new in SQL Server 2008) behaves in this matter.
More here.
DBCC FREEPROCCACHE; and DBCC DROPCLEANBUFFERS; are commands that clean the cached execution plans from memory.
The consequence of running those statements is that all your objects (sprocs, functions, views) must be recompiled which can be quite costly.
There is no way around that. And if you want to recompile 100 sprocs you have to execute each one and each one returns data.
I didn't want this so i created this script that uses a not well know SET option called
SET FMTONLY ON
which returns only schema and no data to the client. Since returning data can take a long time this thing is pretty...