SQL ERROR: ‘JOBID’ scripting variable not defined.

\ by

I often run scripts, especially SQL Agent job scripts using SQLCMD. Moving to SQL 2014 I ran into this error: ‘JOBID’ scripting variable not defined.

I see it is because I also use a token variable in the last job step. A quick solution for me was to add the -x parameter to SQLCMD ( -x (disable variable substitution) ) as suggested as one of the workarounds here.

 

Posted in: Files, SQL, SQLAgent

SQL Server: List Service Accounts’ Owners

\ by

Here’s a couple of ways to get it via code.

/*

http://sqlandme.com/2013/08/20/sql-service-get-sql-server-service-account-using-t-sql/
*/
DECLARE @DBEngineLogin VARCHAR(100)
DECLARE @AgentLogin VARCHAR(100)

EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name = N'ObjectName',
@value = @DBEngineLogin OUTPUT

EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEM\CurrentControlSet\Services\SQLServerAgent',
@value_name = N'ObjectName',
@value = @AgentLogin OUTPUT

SELECT [DBEngineLogin] = @DBEngineLogin,
[AgentLogin] = @AgentLogin
GO

SELECT [servicename],
[service_account]
FROM [sys].[dm_server_services]
GO
Posted in: SQL, SQL Server

SQL ERROR: SSPI handshake failed with error code

\ by 
DESCRIPTION: SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.  [CLIENT: 10.12.23.345].

This can be related to an AD login account expiring and the user leaving SSMS or something running on their PC. The account eventually locks out and SQL sees this message until the user resets their password.

Posted in: ERRORs, Permissions, SQL Server, Windows

SELECT TOP n rows

\ by

I love how top can accept a variable. You could pass it into a procedure.

DECLARE @vcOut VARCHAR(MAX),
    @nTop INT

SELECT  @nTop = 5
SELECT TOP (@nTop)
        @vcOut = COALESCE(@vcOut, '') + NAME + CHAR(10)
FROM    sys.sysdatabases

PRINT @vcOut
--
Posted in: Coding, SQL

SQL Processes, sys.dm_exec_connections and last sql statement

\ by

Last SPIDs, their statement, network packet size.

SELECT  c.session_id,
        s.host_name,
        s.login_name,
        [LastStatement].[[text]],
        c.net_packet_size,
        s.login_time,
        s.status,
        d.name
FROM    sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s
ON      c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS LastStatement
LEFT OUTER JOIN
	sys.databases d on d.database_id = [LastStatement].dbid
ORDER BY s.[status],
        s.login_time DESC,
        s.[host_name]
  
--

Source

Posted in: SQL

xp_cmdshell error: ‘C:\Program’ is not recognized as an internal or external command,

\ by

Apparently in my 64-bit environment I can’t execute the following command.

DECLARE @vcCmd VARCHAR(8000)
SELECT @vcCmd = '"C:\Program Files\Winzip\WZZIP.exe" -es -a "E:\DATA\test.zip" "E:\DATA\test.dat"'
EXEC   master..xp_cmdshell @vcCmd = @vcCmd
/*
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
*/

The solution is I can execute other DOS commands, so let’s concatenate them.

DECLARE @vcCmd VARCHAR(8000)
SELECT @vcCmd = 'C: && CD "C:\Program Files\Winzip\" && WZZIP.exe -es -a "E:\DATA\test.zip" "E:\DATA\test1.dat"'
EXEC   master..xp_cmdshell @vcCmd = @vcCmd
Posted in: DOS, SQL