Use a user data type (UDT) in temp table

Every now and then someone wants to use a user-defined data type (UDT) from a database in a temp table.

Initially trying it gives the error:
CREATE TABLE #Test (TestField GrumpyDBA)
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type GrumpyDBA

To enable the datatypes in temp tables, create them in TEMPDB ... and assuming it's not just for a one off script, also in MODEL (so they are rebuilt in tempdb on each startup).

CREATE TYPE GrumpyDBA FROM INT NOT NULL

So far, so obvious, but the issue that now crops up is the following error when a user tries to create the table again:

Msg 15247, Level 16, State 4, Line 2
User does not have permission to perform this action.

It's quite easy to give up at this point, but it's actually an easy update to fix this as well, just run this in TEMPDB (and Model)

GRANT REFERENCES ON TYPE::dbo.GrumpyDBA TO public

And you should now find you can use the UDT in temp tables from the original database.

Net Send becomes MSG

OK, so it's not news, but NET SEND no longer is the favoured way to send messages to machines, and MSG is the new method - unfortunately it's not as easy to use, and by default doesn't even work!

NET SEND is built into SQL which is a problem when that's used as an alternative to email for getting alerts.  Getting it to use MSG instead can raise other problems too.

Basic syntax to use MSG to send a message to a user / workstation :- the main issue here is you need to know what machine the user is on, but unlike netsend, you can at least configure it to auto close, so you don't come in at 8am and find 5000 popups to click close on.

MSG * /SERVER:workstation001 /TIME:60 Hello I'm testing

Next issue is you are likely to get this error:

Error opening Terminal server Workstation001
Error [1722]:The RPC server is unavailable.

It seems that the latest versions of Windows, by default, do not allow the relevant RPC call

To fix, you need to open regedit, navigate to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TerminalServer

Open the AllowRemoteRPC registry entry, and change the value to 1

You should now be able to use the MSG command.

You can use this to get SQL alerts as follows:
Create a file (e.g. msg.cmd file) with entries for each persons machine you want notified, such as:
MSG * /SERVER:workstation001 /TIME:60 %1
MSG * /SERVER:workstation002 /TIME:60 %1

Create a SQL agent job that calls that file via a CmdExec type step, along with [A-MSG], such as
\\network\DBA\msg.cmd "$(ESCAPE_NONE(A-MSG)) FROM $(ESCAPE_NONE(SRVR)) "

I've then configured alerts (e.g. backupfailed, replication issues etc.) on failure response to execute this job.

SQL 2008 - adding a column to a replicated table fails

There seems to be an issue in certain situations with adding columns to replicated tables in SQL 2008 where the publication is set to include schema updates.

The first error we saw was:

Msg 650, Level 16, State 1, Procedure sp_MSfixup_base_columns, Line 40
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

I'm also aware of a similar error mentioning a different system proc:

Msg 650, Level 16, State 1, Procedure sp_MSreinit_article, Line 66
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

It doesn't look like there is currently any supported workaround for it, but I managed to get it work by doing the following.

Step 1 - In the database where you are trying to change the table, script out the database trigger called "tr_MStran_altertable" as a create statement

Step 2 - Give the trigger a new name in this script, e.g. tr_MStran_altertable_ReadCom.  
Also, in the code you will see it has several SET statements, and to this section add:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Step 3 - run this script to create a new trigger.  

Note the reason for creating a new trigger is that you will get an error if you try to just ALTER the existing trigger to add this setting saying:

Msg 21598, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 60

Modifying DDL triggers created by replication is disallowed since these are required to track DDL changes.

Step 4 - disable the original trigger

DISABLE TRIGGER [tr_MStran_altertable] ON DATABASE

You should now find the original ALTER TABLE statement completes without an error, and the new field is also added to the replication.

Check which node a clustered SQL Instance is on

Sometimes it is useful to know which physical node a SQL instance is running on in a cluster - e.g. for performance monitoring to check CPU attributable to an instance.

You can check this via the cluster failover manager, but you can also run the following SQL statement on the instance in question:

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

This will  return the netbios name of the server the node is currently active on.

I've used this in conjunction with CPU monitors, and setting processor affinity by NUMA node to track CPU activity by instance. 
The assigned NUMA node can be obtained from sys.dm_os_nodes [update] if you are on SQL 2008 R2 such as:

SELECT memory_node_id FROM sys.dm_os_nodes WHERE cpu_affinity_mask = online_scheduler_mask AND node_state_desc = 'ONLINE'

Identify SQL Agent job that runs a Reporting Services subscription

So you want to manually kick off a Reporting Services subscription outside of its usual schedule ... easy you think, I'll just run the SQL Agent job.

Then you find this ...

For some reason, reporting services wants to keep it a secret which job runs which subscription, by using easy to remember GUID based job names.

Fear not ... this SQL will give you a list of the job names against the actual reports

SELECT ScheduleID, Path, Name, s.Description
FROM ReportServer.dbo.Catalog c
JOIN ReportServer.dbo.Subscriptions s ON c.ItemID = s.Report_OID
JOIN ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

Just find the job with the same name as the ScheduleId of the report subscription you want, and you can manually start it whenever you like.

Truncate table DOES rollback

The post on table variables not being affected by transactions reminds me of another DBA interview question that I very rarely hear people answer correctly.

What is the difference between DELETE and TRUNCATE?

The standard answer trotted out is that DELETE is logged and can be rolled back, whereas TRUNCATE can't.

Really?

OK, try this:

CREATE TABLE #Trunc (MyId INT)
INSERT #Trunc SELECT 1 UNION SELECT 2


SELECT * FROM #Trunc 
-- returns 2 rows


BEGIN TRAN
  TRUNCATE TABLE #Trunc
  SELECT * FROM #Trunc 
-- returns 0 rows - we have truncated the table
ROLLBACK TRAN


SELECT * FROM #Trunc

Now if TRUNCATE was indeed non-logged and cannot be rolled back, you would expect the final SELECT statement to also return 0 records, and then laugh at you as you realised you had lost all your data ...

But it doesn't!  It returns the original 2 rows that were there before you issued the TRUNCATE statement ... if you don't believe me, give it a try ...

Table variables don't roll back

A standard question asked at DBA interviews is what is the difference between a temp table and a table variable.

Aside from the usual incorrect answer of one being a physical table in tempdb and the other being just held in memory, and the less often heard but correct fact of one taking the collation of tempdb and the other taking the collation of the current database, a little known aspect is that one of them is totally unaffected by transactions.

So what I hear you say ...

Combine this with TRY and CATCH error logic, and you can capture the data causing transactions to roll back and write it somewhere else after the transaction has failed.

Don't believe me??  OK, have a look at this:

DECLARE @Data TABLE (MyID INT)


BEGIN TRANSACTION 
INSERT @Data VALUES (1)
ROLLBACK TRANSACATION

Now in a normal, or even temp, table you would get no records back from a subsequent SELECT statement, but run SELECT * FROM @Data after the ROLLBACK statement in this situation, and you will see it returns the data added within the transaction.

Obviously this is a very basic example, but using this within complicated transactions, and the possibilities for auditing and error handling are endless!  Well ... nearly endless ... well ...

Remote access - requires Network Level Authentication

I recently had the following error while trying to connect to a Windows 2008 server while installing SQL 2008, by remote desktop.

The remote computer requires Network Level Authentication, which your computer does not support. 
For assistance, contact your system administrator or technical support.

This seems to be applicable to Windows XP SP3, and the fix is a couple of registry updates.

• Open regedit, and find subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
• Right click "SecurityPackages" and select modify
• Add the value "tspkg" (don't delete any of the existing ones)
• find subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders
• Right click "SecurityProviders" and select modify
• Add the value "credssp.dll"
• Reboot

This should now allow you to RDP onto the Windows 2008 Server.

UPDATE - Microsoft have released an automated FIX IT link for this: Microsoft Support 951608

xp_cmdshell raises error "a call to LogonUserW failed with error code 1385"

xp_cmdshell requires a couple of permissions to enable non-sysadmin users to run it, and even when you think you have enabled it in surface area config, and assigned an account to the ##xp_cmdshell_proxy_account##, you will frequently find you still encounter the following error:

An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.

In order to fix this you need to open the Local Security Settings on the host machine.
 
Navigate to Security Settings -> Local Policies -> User Rights Assignment.
 
Now open "Log on as a batch job" and add the user you assigned to the xp_cmdshell proxy account - and you should find it runs fine now.

Error using linked server from 64bit to 32bit SQL

A colleague came across an error using a linked server connection to retrieve data from another server, which gave an error claming:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator. Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "". The provider supports the interface, but returns a failure code when it is used.

We knew the table did exist, and the user had permissions.  The same statement also worked from another server, so it wasn't the syntax either.  We assumed it was some issue going from SQL 2008 to SQL 2000, but it seems there is a small issue with using linked servers between 64-bit and 32-bit versions due to the way it queries meta data.

The Microsoft line on fixing this is "To resolve this problem, manually run the Instcat.sql script that is included with SQL Server SP" - which begs the question, why doesn't the SP do this automatically?!

Our fix though was to manually create "sp_tables_info_rowset_64" which the 64-bit server tries to run on the 32-bit box.  There are several blogs that script out the full code for doing this, but this code is almost exactly the same as the proc that already exists on the 32-bit version, namely "sp_tables_info_rowset" - so we found the quickest and tidiest solution to be just to use that, as follows:

USE master GO CREATE PROCEDURE sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null AS DECLARE @Result INT SET @Result = 0 EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type GO GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

You should now find the error magically goes away.