Tuesday, February 19, 2013

How to log value in SQL trace

Previous post explains how to check OUTPUT value of stored procedure in SQL Profiler. But, if you have nested stored procedure and want to see output parameter value, it's not easy. This is becasue the nested stored procedure is not RPC call and so is not captured in RPC:Completed event. Nested SP will be captured in SP:Staring and SP:Complated events. So even if you check RPC Output Parameter, the value will not be shown in SQL trace.

So how can we see the output value? If you use interactive tools such as SSMS, SQLCMD, this task is trivial. You can simply add PRINT or SELECT statement. But the problem is this statement does not print the value to SQL trace.
Obviously there is no standard trace event class that captures this kind of output, but we can use user defined event, so-called UserConfigurable event class, to get around this problem.

To create this event data from TSQL statement, we use sp_trace_generateevent which generates trace event with user defined text value (and binary value if specified).

The below GetNext SP has nested SP called GetNextNestedSP. Once the nest SP is called, we want to display OUTPUT parameter value to SQL trace.

( @p1 NVARCHAR(32), @p2 NVARCHAR(32))
    DECLARE @text nvarchar(128)  

    EXEC [GetNextNestedSP] @p1, @p2, @out OUTPUT
    SET @text = CONVERT(nvarchar(32), @out)                           
    EXEC master..sp_trace_generateevent  82, @text
    -- cut --

sp_trace_generateevent takes 3 parameters. First parameter is eventId for SQL trace. As MSDN says, this value is predefined and must be one of the event numbers from 82 through 91. ID 82 corresponds to [UserConfigurable:0] in SQL Profiler event class. 2nd parameter is text string that we want to send to SQL trace. This can be any string but there is a string length limitation (MSDN: 128 chars). 3rd parameter is optional binary data if needed. So when this SP (GetNext) is called from client application, how can we capture user defined event in SQL Profiler?

How to check SP output value in Profiler

When stored procedure is called from client application, the call is typically made by using RPC call. So in order to capture SP traces from Profiler, typically RPC:Starting event and RPC:Completed event are checked and those settings are enough in most times. However, sometimes there are chances you want to check out output value of the stored procedure call. Especially where many concurrent calls occur in production server.

To capture the return value of SP, we need to enable RPC Output Parameter event.

1) Run Profiler.exe
2) Connect to SQL Server
3) In Trace Properties dialog, select TSQL_Replay template (or other appropriate for your purpose) in [Use this template].
4) Goto [Event Selection] tabe
5) Make sure [RPC Output Parameter] is selected.

6) Start trace.

The result of OUTPUT parameter will be shown in RPC:Completed event. When the row for RPC:Completed is selected in trace, the bottom pane show TextData and you will see output parameter value before SP call. In the example below, output @p3 is set to 562.

Thursday, February 7, 2013

SQL Backup Error : Cannot open backup device NT AUTHORITY\SYSTEM

Even if backing up database directly to remote server, some people often do this because they worry about the backup file loss in case of the DB machine crash. Sure better approach will be to backup DB to local disk and copy the .bak file to remote file share.
Anyway, the topic of this post is about database backup error 3201. SQL Server service was running as NT AUTHORITY\SYSTEM account and daily backup job was scheduled as SQL Agent job. Backup job directly backs up database to remote file share. When the job was run, the following error occurred.

Message Executed as user: NT AUTHORITY\SYSTEM. Backing up DB to \\BAKSERVER\DBBackups\DB_FULL_Daily_2013_02_01_14_00_00.bak... [SQLSTATE 01000] (Message 0) Cannot open backup device '\\BAKSERVER\DBBackups\DB_FULL_Daily_2013_02_01_14_00_00.bak'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)Unable to open Step output file. The step failed.

The error message is clear - access denied. The machine cannot access the remote file share.
This is because the BACKUP statement is run by SQL Server service account which is SYSTEM account and the SYSTEM cannot access remote fileshare. Even if SYSTEM account can do everything in local machine, it does not have permission to other machines.

So the possible solution might be (1) change SQL Server service account to other domain account (2) add machine account to a domain (or machine) group and give share permission to the group.

How to add machine account manually

1) Open Computer Management
2) Goto [Local Users and Groups] - [Groups]
3) Doubleclick a group you want to use
4) By default, [Computer] object type is unchecked, so you have enable the [Type].
5) Click [Object Types] button
6) Check [Computer] and OK

7) And then add machine name

How to add machine account by using script

The following is a VBScript example that adds a machine account to local Administrators group.
Set Netw = WScript.CreateObject("WScript.Network")
Local = Netw.ComputerName
DomainName = "MyDomain"
MachineAccount = "DBSERVER1"
Set Group1 = GetObject("WinNT://"& local &"/Administrators")
Group1.Add "WinNT://"& DomainName &"/"& MachineAccount &"$"