Convert varbinary plan_handle to varchar

Here is how to do it in SQL 2008+

     SELECT cp.plan_handle
          , st.text
          , 'DBCC FREEPROCCACHE (' + convert(varchar(max), cp.plan_handle, 2) + ');'
       FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st



In SQL 2005

Could use undocumented function

master.dbo.fn_varbintohexstr

or XML

cast('' as xml).value('xs:hexBinary(sql:variable("column_or_variable"))', 'varchar(max)');

VEEAM database backups

VEEAM's vmware backup management software often touts how it can "backup SQL Server databases and truncate the transaction log".

This is probably significant for many corporations that do not have a DBA on staff and only have system administrators and helpdesk folks minding the database servers.

Out of the box, databases are not backed up unless someone does something to back them up.  That and the transaction logs for FULL and BULK_LOGGED recovery model databases continue to grow until they get backed up and then are automatically truncated once they are backed up.

This can result in poor performing databases that have disks filling up and transaction log files that are many times larger than the actual data files.

VEEAM allows system administrators a way to trigger native database and transaction log backups.   A sysadmin would probably think it is enough that now those unruly transaction logs aren't filling up disks anymore, pat themselves on the back, and think they are done.

Unfortunately, folks tend to stop short of asking questions "What are transaction logs, are they important?", "I wonder why transaction logs grow until they are backed up?', etc...

The trouble with this is that if database and transaction log backups are being performed by other processes, then if VEEAM initiates a backup, it can break the continuity of your backup chain and cripple your ability to do point in time restores to a point in time between full backups.

Having a mixture of tools managing database backups is a recipe for disaster.

If you have squirrelly sysadmins who insist on using VEEAM to back up databases, you will need to do one of two things:

1.  Tell them to perform only COPY-ONLY full backups and not to perform any transaction log backups.  Then assure them that you are performing your own full and transaction log backups using your own backup management tools and that the transaction logs are getting truncated.

-OR-

2.  Use VEEAM to manage ALL of your full and transaction log backups

linked server data integrations

While linked servers are a convenient feature that developers like because they are easy to implement, they are not very efficient at moving large amounts of data

General recommendations for any data integration include:

1. Do not “push” data over a link
- Do not perform inserts or updates over link

2. Use linked servers only for “pulling” small and medium amounts of data
- Simple select statement against a view in remote system -- Execute stored procedure in remote system that returns a data set
- This allows for remote system to optimize the query plan
- Alternative bulk copy methods should be used for really large amounts of data, remember that data pulled across linked servers will be eating up memory that your production SQL Server needs

3. The system that is being updated (written to) should perform a read-only pull
- this is better for security in that it only needs read-only connection to remote system
- DML is transactional and causes additional overhead that a select statement does not

4. Data pulls should be a single dataset
- There should not be “cross system” joins in the where clause
- Do not perform looping or cursors across a connection
- Either of these make it very difficult for query optimizer to develop query plan across systems
- If have to, it is better to pull more data than needed from remote system, and then filter it with join in local system than to try and join across systems

5. Also, did I mention “Do not perform inserts or updates over links”?

Published SSIS Package in SQL Server 2012

What happened to the export SSIS package?

Going from 2008R2 to 2012, SSIS packages began using the project deployment model.

The quick way to get a copy of the SSIS package out of SQL is to run SQL Server Management Studio as Administrator and expand the tree:

"Integration Services Catalogs" -> SSISDB -> ProjectFolderName -> Projects -> ProjectName

Then right-click the ProjectName and select "Export..."

Save and remember the name and path you save the *.ispac file.

Rename the ispac file with *.zip

Then can unzip the file.

Connecting with [NT AUTHORITY\SYSTEM]

Connecting with [NT AUTHORITY\SYSTEM] in older versions of SQL Server

D:\PSTools>psexec -i -s "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"


D:\PSTools>psexec -i -s "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.exe"

@@Servername is NULL

After applying CU8 for SQL 2012 SP2 @@Servername was null 

Somehow ended up in condition of no entry in sys.servers for server_id 0

select * from sys.servers where server_id=0

This returned 0 rows.  Can add the local server with:

sp_addserver '<servername>', local

Then had to restart the server for it to take effect

If it will be a while before can reboot, then suggest using 

serverproperty('ServerName')

The partner transaction manager has disabled its support for remote/network transactions

First verify the "Distribute Transaction Coordinator" Service is running on both database server computer and client computers

1.      Go to "Administrative Tools > Services"

2.      Turn on the "Distribute Transaction Coordinator" Service if it is not running


If it is running and client application is not on the same computer as the database server, on the computer running database server

1.      Go to "Administrative Tools > Component Services"

2.      On the left navigation tree, go to 

"Component Services > Computers > My Computer"  (you may need to double click and wait as some nodes need time to expand)

3.      Right click on "My Computer", select "Properties"

4.      Select "MSDTC" tab

5.      Click "Security Configuration"

6.      Make sure you check "Network DTC Access", "Allow Remote Client", "Allow Inbound/Outbound", "Enable TIP"  (Some option may not be necessary, have a try to get your configuration)

7.      The service will restart

8.      BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK

On your client computer use the same above procedure to open the "Security Configuration" setting, make sure you check "Network DTC Access", "Allow Inbound/Outbound" option, restart service and computer if necessary.

On you SQL server service manager, click "Service" dropdown, select "Distribute Transaction Coordinator", it should be also running on your server computer.

Oracle InstantClient error

The setup routines for the Oracle ODBC client in instantclient64_12_1 could not be loaded. System error code 126: The denoted module was not found. (C:\Program Files\Oracle\instantclient64_12_1\SQLORAS32.DLL).


Download and install the Microsoft Visual C++ 2010 Redistributable Package:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632

The problem is not with the SQLORA dlls, but the modules that those ddls refer to (missing from a default Windows installation).

http://serverfault.com/questions/555972/cannot-create-oracle-odbc-source-on-64bit-windows-7

Open cmd in folder

Had an issue where on some server clicking a shortcut to cmd did not open to a path specified in "Start in:" parameter.   Work around was to add parameter to command line.

cmd /K "cd /d d:\scripts\"

Agent Job Execution

AD User executing agent job needs to have SQLAgeUserRole in msdb and be owner of job.

https://technet.microsoft.com/en-US/library/ms188283(v=SQL.110).aspx

Unable to begin distributed transaction

Symptom: The transaction manager has disabled its support for remote/network transactions

Issue: Need to enable inbound/outbound transaction manager communication

Solution:
1. Open Component Services Snap-in (dcomcnfg)
2. Browse to Console->Component Services->Computers->My Computer->Distributed Transaction Coordinator
3. Right Click Local DTC and select properties
4. Select Securty tab
5. Check Network OTC Access, Check Allow Inbound, Check Allow Outbound
6. Click OK
7. DTC services should restart and then can give it a test

Additional info:
 http://sysadminwebsite.wordpress.com/2012/05/29/9/
 http://msdn.microsoft.com/en-us/library/aa561924.aspx 

all bout dat'base, no sql

Remove Foglight Performance Analyzer for SQL Server middle ware

REM  Run as administrator

"C:\Program Files\Quest Software\pa_agent\3566\agents\bin\quest_launcher.exe" 3566 "+C:\Program Files\Quest Software\pa_agent\3566\agents" -unregister




https://support.software.dell.com/performance-analysis-for-oracle/kb/44712

low free memory

Check your servers "free" memory, all the available memory may be tied up in cache due to copying files to/from your server

Create a file the size of your available memory or larger.

fsutil file createnew r:\backup\2gb.txt 2147483648

Then copy the file and then permanently delete both the original and the copy. (Delete from command line, use shift + delete, and/or empty the trash bin)

Now all available memory should be free :)

15404 Could not obtain information about Windows NT group/user

Error message:

03/03/2015 12:43:09,spid28s,Unknown,An exception occurred while enqueueing a message in the target queue. Error: 15404 State: 19. Could not obtain information about Windows NT group/user 'domain\svc_name' error code 0x5.
03/03/2015 12:43:09,spid28s,Unknown,Error: 28005 Severity: 16 State: 2.


Diagnosis:

Can confirm this by executing the following SQL:


xp_logininfo 'domain\svc_name'


Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 'domain\svc_name', error code 0x5.



Resolution:

A: Give the “Authenticated Users”  “Read Permissions” on the ADFS service account.

-or-

B: Add the SQL Server service account or "Authenticated Users" to the "BULTIIN\Windows Authorization Access Group" in AD for the domain

-or- 

C: Add the SQL Server service account or "Authenticated Users" to the "BULTIIN\Pre-Windows 2000 Compatible Access" in AD for the domain


References:

http://setspn.blogspot.com/2012/05/service-accounts-active-directory_6635.html

http://setspn.blogspot.com/2012/05/service-accounts-active-directory_4905.html

http://blog.matticus.net/2009/08/windows-2008-and-xplogininfo.html

SQL Server Default Trace 0

--To see if default trace is enabled
SELECT* FROM sys.configurations WHERE configuration_id = 1568


--To enable default trace
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO


--To query contents of default trace file
SELECT TE.*
     , T.*
  FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150)

       , ( SELECT TOP 1 f.[value]
             FROM sys.fn_trace_getinfo(NULL) f
            WHERE f.property = 2)), DEFAULT) T
  JOIN sys.trace_events TE 
    ON T.EventClass = TE.trace_event_id

More info:

http://www.databasejournal.com/features/mssql/a-few-cool-things-you-can-identify-using-the-default-trace.html

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

The Target Principal Name Is Incorrect. Cannot Generate SSPI Context

Setting up an instance of SQL Server 2008 R2 64bit Standard, got this error when trying to connect from off the server.

Still working to determine the cause, in the meantime was able to temporarily remedy by disabling names pipes and setting service account to "LocalSystem".

Connection String Variations

.\instance
(local)\instance
127.0.0.1\instance - loopback
servername\instance
ip\instance
ip,port
tcp:servername\instance - tcp/ip
lpc:servername\instance - shared memory
np:servername\instance - named pipes

SQL Server Network Client Utility 32/64bit

You can set up SQL Server aliases on a Windows Server or PC using the Network Client Utility even if they don't have the SQL Server Client tools installed.

The trick is to keep in mind that there are two different SQL Server Network Client Utility runtimes on a 64-bit machine.

If you are running a 32-bit Operating System, you will have only 32 bit drivers installed.

If you are using a 64 bit machine, the default utility will be for 64-bit data sources.

If you have a 64bit OS and are trying to access an alias in your 32bit application and receive the error check to see if you have an alias configured for the architecture of your application ( 32-bit / 64-bit ).

Use this utility to Review 64-Bit aliases and protocols
c:\windows\system32\cliconfg.exe

Use this utility to Review 32-bit aliases and protocols
c:\windows\sysWOW64\cliconfg.exe


Additional info:
http://geekswithblogs.net/twickers/archive/2009/12/08/136830.aspx

http://blogs.technet.com/b/meamcs/archive/2013/01/22/creating-a-sql-server-alias-using-the-sql-server-client-network-utility.aspx

http://www.sharepointassist.com/2010/02/02/configure-a-sql-server-alias-for-sharepoint-sql-server-2008/