We've moved to http://sqlforge.us/blog/

Thank you for visiting!

Please find our latest posts and service offerings here: 

http://sqlforge.us/blog/

Instant PRINTs

SQL “PRINT” statements getting buffered and not displaying until buffer is flushed with batch is done or gets full.

Using a RAISERROR with severity of 0 and “WITH NOWAIT” will not interrupt the batch, but will immediately display the output.

Here’s an example keeping it to one line and including a timestamp…


--for first msg in batch
DECLARE @msg nvarchar(2044) = convert(varchar(20),current_timestamp,120) + ' - Your First Message Here'; RAISERROR(@msg, 0, 1) WITH NOWAIT;

--for rest of msgs in batch
SET @msg = convert(varchar(20),current_timestamp,120) + ' - Subsequent Messages Here'; RAISERROR(@msg, 0, 1) WITH NOWAIT;

GO

SSRS Temp files filling drive

If you have SSRS files filling your temp drive, look for the RSTempFiles.

Default folder:

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\RSTempFiles

You can move this folder to a different drive by copying the RSTempFiles folder to the target drive (for this example "R:\RSTempFiles").

Make sure that the SSRS service account alias has full permissions on the folder

Default local machine service account alias:

[NT SERVICE\ReportServer]



Locate the rsreportserver.config file

Default folder:
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer


In this file Located the <Service> tag and add entry for FileShareStorageLocation with new path:


<Service>
  <FileShareStorageLocation>
    <Path>R:\RSTempFiles</Path>
  </FileShareStorageLocation>

...
</Service>


After saving the file restart the SSRS instance.  It should start using the new location.



For more info about the files stored in this folder:

https://blogs.msdn.microsoft.com/jgalla/2008/06/30/all-those-temporary-files-rstempfiles/

sp_hadr_isprimary

USE master;
GO

IF OBJECT_ID('dbo.sp_hadr_isprimary','P') IS NULL
   EXEC ('CREATE PROCEDURE dbo.sp_hadr_isprimary AS PRINT 1;');
GO

--return 1=current instance is primary, 0=current not primary
ALTER PROCEDURE dbo.sp_hadr_isprimary
( @agname sysname = 'AG_GROUP_NAME' )
AS
   DECLARE @PrimaryReplica sysname
         , @ThisReplica sysname
, @retval int = 0;
BEGIN
   SET NOCOUNT ON;
   SET @ThisReplica = cast(ServerProperty('ServerName') as sysname);

          SELECT @PrimaryReplica = hags.primary_replica
            FROM sys.dm_hadr_availability_group_states hags
      INNER JOIN sys.availability_groups ag
         ON ag.group_id = hags.group_id
           WHERE ag.name = @agname;

   IF UPPER(@PrimaryReplica) =  UPPER(@ThisReplica)
    BEGIN
      SET @retval = 1
    END

          SELECT @retval "IsPrimary"
               , @agname "AG_Name"
               , @PrimaryReplica "PrimaryReplica"
               , @ThisReplica "ThisReplica"
   RETURN @retval
END;
GO



Example Usage:

declare @retval int

exec @retval = master.dbo.sp_hadr_isprimary

SELECT @retval "return_value"

bare bones

To start SQL Server and have it only recover the master database and leave all others offline, can use this command:

SQLServr.exe -T3608 -T3609

Also, if you have a named instance, will need to include the -s parameter with the instance name.

SQLServr.exe -T3608 -T3609 -sINSTNAME

various security functions

--execute as login = 'sa';
--execute as user = 'guest';

SELECT @@version
     --current context / execute as
     , user "user" --same as user_name()
     , user_name() "user_name" 
     , current_user "current_user"  
     , session_user "session_user" 

     --current login unless execute as
     , system_user "system_user" 
     , suser_name() "suser_name"  

     --original context
     , ORIGINAL_LOGIN() "original_login" 

GO

Example checking for DML type of trigger

Most of the time it is better to have separate insert/update/delete triggers if you need to do different things based on the DML type.  If you did want to have common code then could call a stored procedure with the DML type as a parameter.

In the rare instances where it may be more practical to have one trigger to rule them all, here is an example of how to check for the type of DML activity of the transaction executing the trigger.


ALTER TRIGGER dbo.tr_test_iud
   ON  dbo.test
   AFTER INSERT,UPDATE,DELETE
AS 
   DECLARE @trgtype varchar(10)
BEGIN
   SET NOCOUNT ON;

   IF EXISTS (SELECT TOP 1 1 FROM inserted)
      IF EXISTS (SELECT TOP 1 1 FROM deleted) SET @trgtype = 'UPDATE'
      ELSE SET @trgtype = 'INSERT'
   ELSE SET @trgtype = 'DELETE'

   PRINT @trgtype
END
GO


Keep in mind also, it is best practice to always code for there being more than one record involved in the transaction resulting in multiple records in the insert/deleted tables.

Server not configured for DATA ACCESS

Error: Server 'SERVER_NAME' is not configured for DATA ACCESS.


Resolution:
exec sp_serveroption 'SERVER_NAME', 'data access', 'true'
go

SQL Configuration Manager Cannot connect to WMI provider

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]

This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.


To work around this problem, open a command prompt, type the following command, and then press ENTER:

Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"

The value of number depends on the version of SQL Server:nnn

Microsoft SQL Server 2016        130
Microsoft SQL Server 2014        120
Microsoft SQL Server 2012        110
Microsoft SQL Server 2008 R2   100
Microsoft SQL Server 2008        100
Microsoft SQL Server 2005        90


https://support.microsoft.com/en-us/kb/956013

super trim

CREATE FUNCTION dbo.trim( @val nvarchar(max) )
RETURNS nvarchar(max)
BEGIN
   DECLARE @tab nchar(1)
         , @lf nchar(1)
         , @cr nchar(1)
         , @crlf nchar(2)

   SET @tab = char(9)
   SET @lf = char(10)
   SET @cr = char(13)
   SET @crlf = char(13) + char(10)

   RETURN ( replace(replace(replace(replace(ltrim(rtrim(@val)), @tab, ''), @crlf, ''), @lf, ''), @cr, '' ) )

END  --trim()

AFTER clause

ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK AFTER 120 SECONDS

a call to LogonUserW failed

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

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.
 
Open "Log on as a batch job" and add the user assigned as the xp_cmdshell proxy account

SSMS connecting to Availability Groups

Connecting to Always On High Availability Group using SQL Server Management Studio (SSMS)

1. In SQL Server Management Studio, enter the High Availability Group Listener name.
Then click on the [Options >>] button.

2. On the “Connection Properties” tab, increase the "Connection time-out:"
Default setting is 15 seconds.
Microsoft recommends setting it to 21 seconds per subnet for availability groups.
This setting is available SSMS versions 2005 and up.

3. Click on the “Additional Connection Parameters” tab.
This tab is available on SSMS versions 2012 and up and this is parameter available in SQL Native Clients based on .NET framework 4 and up.
Add an entry for:

MultiSubnetFailover=True

References:
https://msdn.microsoft.com/en-us/library/gg471494(v=sql.110).aspx https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.100).aspx

Client Recovery Latency During Failover

A multi-subnet FCI by default enables the RegisterAllProvidersIP cluster resource for its network name. In a multi-subnet configuration, both the online and offline IP addresses of the network name will be registered at the DNS server. The client application then retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in multi-subnet failovers no longer depend on DNS update latencies. By default, the client tries the IP addresses in order. When the client uses the new optional MultiSubnetFailover=True parameter in its connection string, it will instead try the IP addresses simultaneously and connects to the first server that responds. This can help minimize the client recovery latency when failovers occur. For more information, see AlwaysOn Client Connectivity (SQL Server) and Create or Configure an Availability Group Listener (SQL Server).

With legacy client libraries or third party data providers, you cannot use the MultiSubnetFailover parameter in your connection string. To help ensure that your client application works optimally with multi-subnet FCI in SQL Server 2012, try to adjust the connection timeout in the client connection string by 21 seconds for each additional IP address. This ensures that the client’s reconnection attempt does not timeout before it is able to cycle through all IP addresses in your multi-subnet FCI.
The default client connection time-out period for SQL Server Management Studio and sqlcmd is 15 seconds.

https://msdn.microsoft.com/en-us/library/ff878716(v=sql.110).aspx

Linked Servers/OPENROWSET use OLE DB and OLE DB in the SQL Server Native Client does not support the MultiSubnetFailover keyword.

https://msdn.microsoft.com/en-us/library/gg471494(v=sql.110).aspx

Try a System DSN using ODBC with MultiSubnetFailover specified and then creating the linked server to use this .

Other option is to increase the timeout of the linked server to give it time to work around all the registered IP addresses.

Use the following guidelines to connect to a server in an availability group or SQL Server 2012 Failover Cluster Instance:

- Use the MultiSubnetFailover=True connection property when connecting to a single subnet or multi-subnet; it will improve performance for both.

- To connect to an availability group, specify the availability group listener of the availability group as the server in your connection string.

- Connecting to a SQL Server instance configured with more than 64 IP addresses will cause a connection failure.

- Behavior of an application that uses the MultiSubnetFailover connection property is not affected based on the type of authentication: SQL Server Authentication, Kerberos Authentication, or Windows Authentication.

- Increase the value of Connect Timeout to accommodate for failover time and reduce application connection retry attempts.

- Distributed transactions are not supported.


If read-only routing is not in effect, connecting to a secondary replica location will fail in the following situations:

- If the secondary replica location is not configured to accept connections.

- If an application uses ApplicationIntent=ReadWrite (discussed below) and the secondary replica location is configured for read-only access.

https://msdn.microsoft.com/en-us/library/hh205662(v=vs.110).aspx


Bottom Line:

1. Upgrade clients where possible
2. Set the multisubnetfailover option if using SQLClient in .NET 4.0 and above client libraries and applications
3. Extend connection timeouts to 21 seconds per subnet for legacy connections that cannot be upgraded
4. If the application uses a library that cannot be upgraded, but supports ODBC, then upgrade ODBC and create an ODBC connection that uses the MultiSubmetFailover=True and have application connect using the ODBC connection.
5. If the application uses SQL Client, then can create a SQL Native Client alias that points to primary node (can use a powershell script to change)
6. If all else fails, connect the application to the primary node (will have to be manually changed on failover)
7. Not recommended, but can set RegisterAllProvidersIP to off for the cluster (this could cause some clients to not be able to connect for 20 minutes depending on HostRecordTTL setting)


Additional References:

http://www.madeiradata.com/make-sure-clients-can-connect-multi-subnet-cluster/
http://johnlouros.com/blog/leveraging-multi-subnet-failover
https://blogs.technet.microsoft.com/sqlpfeil/2014/03/31/sql-alias-powershell-script/
http://sqlperformance.com/2013/11/system-configuration/ag-connectivity

@@SERVERNAME returns name of old server

-- in the case where a server has been cloned or renamed 
-- and @@SERVERNAME still returns name of old server

use master
go

SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
GO


SELECT [server_id]
      ,[name]
      ,[product]
      ,[provider]
      ,[data_source]
  FROM [master].[sys].[servers]
GO

sp_dropserver 'OLDSERVER\OLDINSTANCE';
GO
sp_addserver 'NEWSERVER\NEWINSTANCE', local;
GO

-- has been updated in servers table
SELECT [server_id]
      ,[name]
      ,[product]
      ,[provider]
      ,[data_source]
  FROM [master].[sys].[servers]
GO

-- may need to restart instance 
-- for it to take effect for @@SERVERNAME

SELECT @@SERVERNAME, SERVERPROPERTY('MachineName')
GO

Installing SQL Server 2000 Desktop Edition

Download MSDE 2000 Release A

This will download a compress executable file MSDE2000A which is SQL Server 2000 Desktop Engine with SP3a (8.00.760)

Executing this will expand to a folder C:\MSDERalA\

From a command line navigate to this folder and execute on of the following:

To install a default instance configured to use the default Windows Authentication Mode, just pass in the SAPWD="AStrongSAPwd" switch where AStrongSAPwd is a strong password for the "sa" account.

C:\MSDERalA\>setup SAPWD="AStrongSAPwd"

To install a named instance include the INSTANCENAME="InstanceName" switch.  To use mixed mode Windows Authentication and SQL authentication use the SECURITYMODE=SQL switch.

C:\MSDERalA\>setup INSTANCENAME="MyInstance" SECURITYMODE=SQL SAPWD="AStrongSAPwd"


To start SQL Server, go to Start->Run and type sqlmangr.exe to bring up the sql server service manager.  You can start the service using it and set it to start when OS starts.

Now open a command prompt and attempt connecting using osql

C:\>osql -E


Resources:
How to obtain and install SQL Server 2000 Desktop Engine (MSDE 2000)
https://support.microsoft.com/en-us/kb/324998
http://support.microsoft.com/default.aspx?scid=kb;en-us;810826
http://support.microsoft.com/kb/322336/EN-US/

what version of .NET?

To find .NET Framework versions by viewing the registry (.NET Framework 1-4)

1. On the Start menu, choose Run.
2. In the Open box, enter regedit.exe.
3. In the Registry Editor, open the following subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP


To find .NET Framework versions by viewing the registry (.NET Framework 4.5 and later)

1. On the Start menu, choose Run.
2. In the Open box, enter regedit.exe.
3. In the Registry Editor, open the following subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full


https://msdn.microsoft.com/en-us/library/hh925568(v=vs.110).aspx#net_a

when were databases last restored

    SELECT A.restore_history_id
         , A.restore_date  --date database was restored
         , A.destination_database_name
         , A.user_name
         , A.restore_type
         , B.backup_finish_date --date of backup used to restore database
         , C.physical_device_name  --backup file used to restore database
         , A.stop_at
         , A.stop_at_mark_name
         , A.stop_before
      FROM msdb.dbo.restorehistory A
 LEFT JOIN msdb.[dbo].[backupset] B
        ON A.backup_set_id = B.backup_set_id
 LEFT JOIN msdb.[dbo].[backupmediafamily] C
        ON B.[media_set_id] = C.[media_set_id]
     WHERE A.restore_date 
         = ( SELECT MAX(A1.restore_date)
               FROM msdb.dbo.restorehistory A1
              WHERE A1.destination_database_name = A.destination_database_name )

declare cursor local fast forward

Execute ps1 files at command line

1. Open regedit.exe

2. Export copy of the registry

3. Navigate to

HKEY_CLASSES_ROOT\Microsoft.PowerShellScript.1\Shell

4.  Change the default from 'Open' to '0'

5. Could change default parameters to powershell.exe in HKEY_CLASSES_ROOT\Microsoft.PowerShellScript.1\Shell\0\Command


Another option:
3. Navigate in registry to HKEY_CLASSES_ROOT\Microsoft.PowerShellScript.1\Shell\Open\Command\

4. Change Old Value:
"C:\Windows\System32\notepad.exe" "%1"

to New Value:
"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -NoLogo -NoProfile -NonInteractive -ExecutionPolicy "Bypass" -File "%1"


Why not do this:

This makes it easy for dangerous .ps1 files to be executed.  With great power comes great responsibility.