Tuesday, January 31, 2012

I had a query against a table that would change based on the day (mytable_20120131). Copied and pasted it into a nvarchar(max) variable, tried to get it to run multiple times without luck. Odd error messages - number of selects doesn't match number of inserts, "incorrect syntax near the keyword 'on'" (just means there's a problem somewhere in the code), problem near a comma, etc. Was using nvarchar(max) since I'm plugging it into sp_executesql. Oh, and it's an explicit insert with over 140 fields.

Coworker had the bright idea to look at where the dynamic table name was added. Did a "select @sql", copied and pasted into notepad.

declare @sql nvarchar(max), @mytable nvarchar(max)
set @mytable = 'dbo.mytable'
set @sql = 'insert into mytable ([myfielda],[myfieldb],..." + @mytable + ' on ...'
select @sql
Here's what it looked like around dbo.mytable when I did the select:
"myfieldbo.mytable"
Huh?

Turns out, there's a length to the string assignment: 4000 characters. I normally don't run into it because my code normally looks like this when I need dynamic sql, but I didn't want to have to deal with splitting the 30 lines I'd need:
set @sql = 'insert into mytable ([myfielda],[myfieldb],.."
+ '[myfieldg],[myfieldh]...'
+ '[myfieldy],[myfieldz]...'

Monday, January 30, 2012

Bare bones query. This will give you the lock escalation for your partitioned tables. In our experience we've had good luck with AUTO (which is _not_ the default!), but the reason it's not the default is because some MS customers had deadlock issues with it enabled. For us, it prevents issues with queries blocking inserts.

Also, let me put a suggestion in your ear: don't go with just one table for inserts & queries. Create a partitioned table to catch live data, keep a week's worth of data. Create a mechanism to copy that into your history (what gets queried) table. Make the history table AUTO, and experiment with your live table. Compress the history. Make a view between the two if you need. Like much on this blog, let my pain be your gain.

Wednesday, January 25, 2012

Had to build this to keep track of changes on certain tables in our environment. Due to limited permissions, we wound up with people hitting the table but the trigger not working right, because they hadn't been granted permissions. So the obvious solution is to grant permissions to both tables to a group, then add people into that group. Not an option for this particular scenario. Hence, my code.

Note that THIS WILL NOT WORK across databases, unless it’s
marked as TRUSTWORTHY. Planned
functionality, not a bug. Within a database it works.

New code bolded. I
created a user Change_User, a change-tracking table named change_mytable and gave Change_User select/insert permissions ONLY into change_mytable.

Tuesday, January 17, 2012

We've seen this in our environment for a while. Tried moving backup times around, that helped some but not in every case. TCP Chimney Offload didn't seem to be our issue either, though it has been for others.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parametersCreate a New DWORD value with the name: SessTimeoutset the value: 360 keep it Hexadecimal(This value might not work for your backup but it was high enough for mine. If this doesn't work increase the value and try again.)