October 2005 - Posts

Today has broken my record for visitors, not quite getting to 200, which I'm quite chuffed at given the amount of effort I put in.

I have been looking back at some old posts and was trying to see if any had received more than 1000 web visits i.e. not rss and was suprised to see that my post on user defined function performance has top 3300 views thats about 4 times more than the average post. Just goes to show what people are interested in.

Here's the post if you interested http://www.sqljunkies.com/WebLog/simons/archive/2005/01/17/6468.aspx

Posted by simonsabin | with no comments
Filed under: ,

So you want to setup a cluster on your laptop. well before getting the men in white costs to come and pick you up I will let you read this post http://sqladvice.com/blogs/repeatableread/archive/2005/08/01/4273.aspx on how to do it with VMware.

Personally this looks worse than the war and peace size book required to install clustering in SQL Server 7.


Posted by simonsabin | with no comments
Filed under:

If you want to include a date literal i.e. '1 oct 2005' in an indexed view then it needs to be converted to a precise deterministic date.

BOL does include the information you need to do this but it's not obvious.

You need to do the following,

  1. use convert
  2. specify a style (see BOL)
  3. do not use character months i.e. oct, jan, april. Only use the number representation
  4. use 4 character years

i.e. convert (datetime, '30/1/2005',103) for us brits and convert(datetime, '1/30/2005',101) for those over the pond

To follow up on Adams comments, this is not about the storage of dates but the use of a literal date in an indexed view. The reason I refer to a precise date is because the error raised says that the date needs to be precise.

i.e. the following is not allowed

create view vw_MyIndexedView with schemabinding as select col1, col2, col3 from dbo.mytable where col2 < '1 oct 2005'

and should be converted into,

create view vw_MyIndexedView with schemabinding as select col1, col2, col3 from dbo.mytable where col2 < convert(datetime, '1/10/2005',103).

The reason I posted this was that it stumped me for a while as it's obviously nice to use "friendly" dates, that are easily understood when read.

Posted by simonsabin | with no comments

I am continuing to find great features in SQL Server 2005. The latest is the log viewer. You now have the power to display logs from SQL Server, Event log, Database Mail and SQL Agent all in on view, merged based on time.

This means that you don't have to flip back and fourth between event log and Enterprise manager to find what was going on in SQL Server when something happened to your server.

In addition you can filter your results by date range, user, computer, source and also some text that the message contains.

As with the changes to profiler that enable you to combine perfmon and profiler output to compare the two, the ability to look at all your system logs files at one time just simplifies your job as a dba.

ps did I say that you can add ANY log file to the same viewer as long as it is in the correct format, a comma delimited file with columns date, source, severity and message.


Posted by simonsabin | with no comments
Filed under:

It was almost 2 months ago that I was fortunate to go to the PDC. As always there was much being given away, ranging from pens to tablet pcs with lots of t-shirts to boot.

Of those that I picked up, good was the luggage tag from AMD but the best so far has to be from MaximumASP. They gave out floppy frisbies, providing hours of fun in the park. The great thing is that unlike normal frisbies where you occasionally catch it awkwardly, these are soft and never hurt :)


Posted by simonsabin | with no comments

Browsing around and found Chris's blog http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!350.entry

Also intrigued cos he's got a high blogshare value compared to mine :)


Posted by simonsabin | with no comments

If you've been playing building serious applications with SQL Server and its CLR integrtation you may have come across error 6551 or 6552. These relate to types not matching up between the function/type/procedure... declaration in TSQL to that in your CLR code.

The basics are that certain TSQL types map to certain SQLCLR types.

What can get annoying (if you didn't read the manual like me!) is that you may create your CLR code and TSQL code and can't for love nor money find whats wrong. You've got 1 parameter that is a CLR SqlString and your TSQL is declared as a varchar. Surely they are the same.

Well they could be but they're not. SQLCLR requires the use of the unicode data types nchar and nvarchar.

For a mapping of the types look for "SQL Server Data Types and Their .NET Framework Equivalents" in BOL

So if you're getting error 6551 or 6552 check to see if you have your chars declared properly as nvarchar or nchar.

Posted by simonsabin | 1 comment(s)
Filed under: ,

I have come across this a number of times before and always found it very annoying.

When running performance monitor you can look at counters for the performance of the Microsoft Search engine MSSearch, which is what full text uses.

This all works fine when using system monitor and displaying the results in real time, you can get the query rate, the filtering rate, the waiting documents etc. However when you try and setup a background counter log the counters come back as empty as those the counter didn't exist.

Well to get these logging correctly you need to have the counter log running as a user that has access to the counters. I assume it's some dcom issue ? Anyway I set it running as the same account as my SQL Server and I get the results recorded correctly. This can be done on a per counter log basis in the properties of the log, you can probably set it elsewhere in a service but I've stuck to keeping it simple and doing in the counter log.

So if you are missing your full text performance counters then make sure you are running as a user that has access.

There are KB articles about the counters actually not being installed correctly, I will leave you to find them.

p.s. this only applies to SQL Server 2000 (and probably 7). 2005 has a new set of performance counters and I don't know if this still applies.


Posted by simonsabin | with no comments

So we've all come across the need to delete 10 million records. however we all no that this won't be quick and will result in a large log file and as we get nearer deleting the 10 millionth row the process is going very slowly.

Well the standard way around this is to run the command in batches, this way our transaction is never very big. So you can write a while loop and check an iterator, but first you need to get into the loop so you need to store the iteration in a variable and have something like this

set rowcount 10000
declare @rc int
set @rc =0
while @rc < 1000 
  --Do my update/delete etc 
  delete from mytable where ....
 @rc = @rc+1

Well in SQL 2005 in SQLCMD mode and the new TOP clause in an update/delete you can do the following

--your update statement
delete top (10000) from mytable where ....
:go 1000

which of these looks easier to you. I vote for number 2.

Posted by simonsabin | with no comments

You've created you first SSIS package and performance sucks. Well all might not be lost, if the performance you are referring to is the one when run in debug mode in the Visual Studio IDE by pressing F5 then yes it is likely to not be the best.

The guys at SQLIS.com have looked at the performance of running in different environments, VS, VS non debug and DTExec. The results are quite something. I had noticed an improvment in using DTEXEC but not to this degree


Posted by simonsabin | with no comments
More Posts Next page »