1.09 - API CURSORS and linked servers - Grrrrr!!

Like I always say; Every day is a school day.
A normally well performing system today suddenly started using 99% utilisation constantly. After taking a look I noticed that there were some strange requests on the server which all related to FETCH_APICURSORXXXXXXX. Now I could only see what these were by by querying the sys.dm_exec_cursors dmv. I then looked at profiler and saw the millions of sp_cursorfetch transactions and realised that there was a big issue. This was using a cursor because SQL was using an OLEDB connection on the linked server resulting in millions of rows individually returned  for a sizeable dataset.

After a bit of research OPENQUERY resolves this issue but it doesn't use parameters, so you need to use dynamic sql or call sp_executesql from the target server.

I hope this hopes anyone and reduces the the hours that it took me to sort out.

A good reference : http://www.sql-server-performance.com/2006/api-server-cursors/#comment-3847

1.08 - Intelllisense Refresh

To force a suggestion = CTRL+J   and to force an intellisense refresh = CTRL+SHIFT+R
- Who knew?

1.07 - SQL SERVER – How to Refresh SSMS Intellisense Cache to Update Schema Changes

Have you ever faced situation where you have just created or modified object but SSMS still shows the error. I quite often face this situation where I come across situation where my SSMS Intellisense Cache is not refreshed or updated. This is indeed very frustrating when you are presenting something on stage as the red underline means an error in graved in many people’s minds and it is hard for them to believe when the code with underline runs successfully.

Here is image of the recent situation. Where I had just dropped index but SSMS Intellisense was still showing that the index exists.

When I face this kind of situation, without wasting time I immediately do following : CTRL + SHIFT + R . This will clear the cache of the Intellisense and it removes the underline from the word. You can also refresh the Intellisense cache by using Edit -> Intellisense -> Refresh Local Cache.

Courtesy of Pinal Dave (http://blog.sqlauthority.com/2013/07/04/sql-server-how-to-refresh-ssms-intellisense-cache-to-update-schema-changes/)

1.06 - Old And Forgotten Database Recovery Options

I recently stumbled across some old commands to remove the db status from 'suspect' or 'restoring', although I would always recommend using restore database with recovery initially to remove the recovery issue.
These are :

1. sp_resetstatus
2. DBCC DBRECOVER (database) - Removed from SQL 2012

These should not be your first option but when all else fails...

1.05 - Bulk Insert Row Terminators Gotcha

I recently stumbled across this issue when a dev asked me to have a look at a file that he was attempting to import. Basically, the bulk import or the bcp statement was not recognising the row terminator and appending all the data beyond the last column into that very same last column. Annoying, to say the least.
After some researching, I found that this is quite a common problem and most people suggest dynamically building the statement and that seems to work.
e.g.
DECLARE @Cmd NVARCHAR(MAX) = 'BULK INSERT [TableName] FROM ''[InputFile]'' WITH (FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''' +CHAR(10) +''')'

EXEC SP_executeSQL @cmd

However, I am not a fan of dynamic sql and tried to find out if it can be done without and ....woohoo! It can be - You just have to use the hex values for the row terminator and then the World falls back into a sensible orbit and life makes sense again.
e.g.
BULK INSERT [TableName] FROM '[InputFile]' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0xa')

I hope this helps someone out there as it saved me a lot of time after I sussed it. 

1.04 - Xp_ReadErrorLog Parameters

These are always worth knowing about...
SQL Server: xp_readerrorlog parameter detail

Redgate - SQL In The City

Attended Redgate's SQL in the City last week in London for the second year running. Always worth a visit although it was more sales focused this year with a distinct lack of other vendors. A sign of the times I expect.
Quite a few of the sales pitches, ahem.. I mean informed lectures were discussing what is a nice GUI for what already exists, if you know where to look or you have taken the time to find out. Always good though to listen in to the guys (and girls) from SQLServerCentral (Steve Jones, Grant Finchley, Gail Shaw, etc.) as there is always a few golden nuggets of information that I have not heard or missed and they do seem to take time to speak to you if you are inclined to do so.
Well worth a visit for anyone who works with databases or is in charge of delivering solutions, if they are coming to a town near you.