[Oddities] There's a limit to a SET assignment... Dynamic SQL, strings, and NVARCHAR

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]...'


Fix?  In this case, an easy one:

set @sql = N'insert into mytable ([myfielda],[myfieldb],..." + @mytable + N' on ...'

It appears that because it's all nvarchar-assigned, that it never runs into the limit.
Weird.

[Table Partitioning] what is your lock escalation?

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.


SELECT distinct tables.name,
lock_escalation_desc
FROM sys.partitions
inner join sys.tables
on tables.object_id = partitions.object_id
and partition_number >1
order by name

[Code] Using EXECUTE AS with a trigger to capture changes when a user doesn't have permissions.

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.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter trigger [dbo].[mytrigger]

on [dbo].[mytable] with execute as 'change_user'

after update

as

BEGIN

  SET NOCOUNT ON

  declare @current_user nvarchar(128)  --suser_sname() is nvarchar(128)

  execute as caller                    --caller is the original person running code

  set @current_user = SUSER_SNAME()    --get the name of the user

  --select @current_user               --returns my name when I run it

  revert                               --go back to the EXECUTE AS user, ch_user

  --Audit capture

      if exists (select name from sysobjects where name = 'change_mytable')

      begin

      --print suser_sname()                     --returns change_user

      insert into change_mytable(

      ID, name, updated_date, change_login, dml_action)

      SELECT

            ID, NAME, getdate(), @current_user, 'Update'

            FROM Deleted

      end

END

GO

Adding locations to SSMS "Open File"

Been looking like this for years.  Who knew, the right google and there it is.  I take no credit for this, simply sticking it in my blog so that I can find it next time.

http://www.sqlservercentral.com/Forums/Topic907188-391-1.aspx
Todd Engen

Here's where you'd find that for SSMS with 2005. \HKCU\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\Open Find\Places\UserDefinedPlaces Add subkeys Place0,Place1,Place(n) Under each subkey add two REG_SZ values Name = "Shortcut Name" Path = "Folder location"

[Network] Specified network name is no longer available

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.

Got this nugget courtesy of kgerde, which seems to be a near-magic-bullet for us.
http://www.sqlservercentral.com/Forums/Topic768429-391-1.aspx


HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters
Create a New DWORD value with the name: SessTimeout
set 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.)