1.23 - Service Account Naming

MSSQL$InstanceName is shorthand for: NT Service\MSSQL$InstanceName

1.22 - Identifying A Database From The Data File

A few times in the past I have had to deal with trying to identify a detached data file and try to work back as to what database the files belonged to.
After a lot of searching, I have long last found the solution and I hope it comes in helpful to those in a similar position to myself.

Enjoy!

Syntax:DBCC CHECKPRIMARYFILE ({'PhysicalFileName'} [,opt={0|1|2|3}])

PhysicalFileName is the full path for the primary database file.

opt=0 - checks if the file a primary database file.
opt=1 - returns name, size, maxsize, status and path of all files associated to the database.
opt=2 - returns the database name, version and collation.
opt=3 - returns name, status and path of all files associated with the database.

REM: You need the security on the folder/file to read the info!

1.21 - SQL Profiler Trace Duration Units

Always have to look this up so it must be useful:

• Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) 
• Beginning with SQL Server 2005, the server reports the duration of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second)
•  In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005 and later
• In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds. 

1.20 - SQL 2012 linked server query and loading a result set into an object

I recently upgraded an environment into a SQL 2012 environment from SQL 2008. Today one of the devs could not run a distributed query to one of the linked servers, but only if it returned a result set into a temporary table or table variable.
The query was running a Stored procedure that returned a small subset of data, It would run perfectly fine on its own but as soon as it ran with the additional task of adding the data into a temp table it blew up with the error
The partner transaction manager has disabled its support for remote/network transactions. and followed up with The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "[SERVERNAME]" was unable to begin a distributed transaction.

Odd.
I checked the configuration of the new servers and MS DTC was configured in the exact same way from the old ones but for some reason SQL 2012 seemed to be dealing with it differently (or at least the linked server provider was).
After I amended the local DTC config to accept network DTC access and for remote clients(inbound and outbound) all was well.

1.19 - Running a 32bit SSIS Package in a 64bit SQL Environment with the SQL Agent

I have always seen lots of issues with this whenever I have been performing these tasks and I have now found a method that ensures that it will work. This is as follows:

1. Change the SSIS package to run as 32bit
2. Import the SSIS package as a file system deployment
3. Create a credential on the SQL server
4. Create a Proxy for the above credential for the cmd.exec
5. Create a sql agent job to use a Operating System (Cmd Exec) in the job step
6. Set the SQL agent to use the proxy account that was created above
7. Now here's the part that forces it to use the 32bit  - Set the job step to call the 32bit version of the dtexec.exe (i.e. "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f  "[package file location and name]")

I hope this helps others as this is was a godsend for me

1.18 - SQL Aliases to Named Instances

This can be a tricky issue to resolve and there is not too much about it that I have found. However, there is a nice article here (setting-up-aliases-on-sql-server) that describes a nice way of sorting this. (use link for images)

Setting up aliases on a SQL Server with multiple instancesThis article will explain how to set up multiple aliases for a SQL Server running multiple instances. Aliases can be an important part of a company's disaster recovery plan as well as aiding in the replacement of an underlying physical or virtual server. By abstracting the name a user or application uses to connect to SQL Server, we gain the ability change the underlying hardware with a few changes to DNS.
At the end of this article, there will be 3 instances of SQL Server running that can accessed either by their SQL instance names or the 3 aliases we are going to create, SQLAlias1, SQLAlias2, SQLAlias3.
Our initial setup is a physical server named WinServer and 3 instances of SQL Server 2008. The first instance is the default instance, with the other two instances named Instance2 and Instance3. We can connect to all 3 instances by using the names WinServer, WinServer\Instance2, and WinServer\Instance3.
Once all 3 instances are setup, we need to add 2 more IP addresses to the WinServer. There is one IP address already assigned to WinServer, 192.168.1.1. By adding two new IP's, 192.168.1.2 and 192.168.1.3, WinServer can be accessed by any of the 3 IP's on the network. Below are screenshots on how to add the 2 new IP's. You may need to coordinate with your network administrator.
Add the IP's in the Local Area Connection Properties of the NIC.

Advanced

Add the 2 addresses

Uncheck Register this connection's addresses in DNS

Now that the IP's have been created, we need to move into DNS to configure our host names and aliases. Again, you may need to coordinate with your network administrator.
WinServer (the physical machine) must be setup as a static IP in DNS. In this instance, 192.168.1.1.SQLAlias1, which will point to the default instance on WinServer, will be setup as a DNS alias or CNAME. Basically, it is a pointer to WinServer.SQLAlias2, which will point to WinServer\Instance2, will be setup as a new Host (A) record in DNS with an address of 192.168.1.2.SQLAlias3, which will point to WinServer\Instance3, will be setup as a new Host (A) record in DNS with an address of 192.168.1.3.Once these are setup, you may have to flush your DNS cache and re-register. You can do this by running the following commands in the command prompt. ipconfig /flushdnsipconfig /registerdnsYou should now be able to ping all 3 aliases and each should resolve to their associated IP address:SQLAlias1: 192.168.1.1SQLAlias2: 192.168.1.2SQLAlias3: 192.168.1.3Now that everything has been completed on the DNS side, open up SQL Server Configuration Manager on WinServer. There, under SQL Server Network Configuration, you will see the protocols for the 3 instances of SQL Server.


Configure the protocols for each instance one at a time. First, the protocols for the default (MSSQLServer) instance.

Double click the TCP/IP protocol set Listen All to No.

Now, move to the IP Addresses tab and enter the 3 IP Addresses (192.168.1.1, 192.168.1.2, 192.168.1.3) all with the port of your choosing. I chose to keep the default port of 1433 for all three instances. Notice that on the first instance, the Active and Enabled is set to Yes on the IP address of 192.168.1.1 while the other two addresses have Active set to Yes, but Enabled is set to No. Dynamic Ports on the first instance is set to blank, not 0. 



Select OK. You will get a warning box that these changes will not take affect until the instance is restarted, but I chose to wait until I'd configured all 3 instances before restarting the 3 SQL services.
Now that the TCP/IP protocol is configured on the default instance, we will configure SQLInstance2 and SQLInstance3 in the same way, with a few subtle differences.
On the TCP/IP protocol setup of SQLInstance2, we will once again set the Listen All to No.

Inside the IP Addresses tab, enter your 3 IP Addresses just like on the first instance. The difference here is that all 3 will again be set to Active, but only 192.168.1.1 and 192.168.1.2 will be set to Enabled, with TCP Dynamic Ports set to 0 on the 192.168.1.1 address. This is done so we can still access the WinServer\Instance2 instance by that name and not just the SQLAlias2 alias.

For SQLIntance3, the TCP/IP protocol setup is basically the same as SQLInstance2, execpt for 1 difference. Listen All will still be set to No and the 192.168.1.1 address will still be set to Yes for Active and Enabled, along with TCP Dynamic Ports set to 0. The different is that on the 192.168.1.2 address, it will be set to No for Enabled, while Enabled is set to Yes for the 192.138.1.3 address.


Note: On all 3 instances, under the IPAll section of the IP Addresses, TCP Dynamic Ports and TCP Port will always be blank.
If you restart all 3 instances now, you will be able to connect to the default instance but will most likely get the following error message when connecting to SQLInstance2 and SQLInstance3. 
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)If that’s the case you will need to add a new DWORD registry entry under HKEY_LOCAL_MACHINE\SYSTSM\CurrentControlSet\Control\Lsa called DisableLoopbackCheck and set to 1.
Once that is set you should immediately be able to connect to all the instances on you server using either the SQL instance name or the new aliases setup in DNS.

1.17 - Run In The SSIS Deployment Utility On Machines With Multi Edition Instances

Run "C:\Program Files (x86)\Microsoft SQL Server\[SQL EDITION]\DTS\Binn\dtsinstall.exe" "[FileLocation.SSISDeploymentManifest]"

1.16 - SQLBits Registration

It's a shame that this is based in Telford, rather than London but ...SQLBits Registration now open and can be found at http://www.sqlbits.com/

1.15 - Linked Server Alias

I take no credit for this - Taken from http://alexpinsker.blogspot.co.uk/2007/08/how-to-give-alias-to-sql-linked-server.html , but very useful.

There are no problems to add SQL linked server to the specific host running it. But what if you need to give it an alias, rather than use hostname as linked server name?
Here is how to do it:
1) Step 1:
• In SQL Server Management Studio open Linked Servers and then 'New Linked Server'.
• Inside of appeared wizard – Select the General tab.
• Specify alias name in "Linked server" field.
• Select SQL Native Client as provider.
• Add sql_server in "Product Name" field (that's the magic).
• In "Data Source" – specify name of the host to be used as linked server.
2) Step 2:
• In Security tab – specify proper security options (e.g. security context)
3) Step 3:
• In Server Options tab – put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.
4) Step 4:
• Enjoy.

1.14 - Product Function In SQL

As some of you may be aware there is no PRODUCT function in SQL so we have to find a solution ourselves. I searched for a few hours and found various ways of doing it using LOG but that did not work fully as it did not take account of negative numbers. I eventually started thinking about using a recursive CTE, tried, failed and looked for an alternative, when I remembered that you could use a variable as a recursive solution and hey presto! It worked; details below:

DECLARE @a DECIMAL(37, 9) = 1.00; -- As 1 * x will always start us off
WITH    cte
          AS (
               SELECT
                    ROW_NUMBER() OVER ( ORDER BY (
                                                   SELECT
                                                        ( NULL )
                                                 ) ) [row]
                FROM
                    sys.tables
UNION ALL --Add random data for test
(SELECT -5 [Row])
UNION ALL --Add random data for test
(SELECT 20 [Row])
UNION ALL --Add random data for test
(SELECT 5.26566666 [Row])
UNION ALL --Add random data for test
(SELECT -3 [Row])
)

    SELECT
            @a = ( @a * [row] ) --Set recursion of the variable
        FROM
            cte


SELECT
CONVERT(VARCHAR, CONVERT(MONEY, @a), 1) --Select in a nice format

1.13 - SQL Relay - Coming to a town near you

http://www.sqlrelay.co.uk/

1.12 - Get SQL Service accounts TSql

/*SQL Service Account*/
DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',@ServiceAccountName OUTPUT, N'no_output'
SELECT @ServiceAccountName

/*SQL Agent Service Account*/
DECLARE @ServiceaccountName varchar(250)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLServeragent',
N'ObjectName',@ServiceAccountName OUTPUT, N'no_output'
SELECT @ServiceAccountName

1.11 - SSIS 2012 - Package Configurations Menu Option Missing

Useful Tip if you are developing BI in Visual Studio 2012 - http://dataqueen.unlimitedviz.com/2012/01/ssis-2012-package-configurations-menu-option-missing/

1.10 - Performance Of The IN Operator

I recently had to look at the performance of a query that was running with high CPU, and taking over 40+ seconds to run.
The scenario -
A stored proc used a view that joined to a table with a list of numbers from a table. This table was generated from a string that was passed to the stored proc as a string (possibly 10 values) separated by commas. This string was then split into a table via a UDF (table function) and this was then used in the IN operator to filter the number of results (it had to be dynamic). All indexes possible were made available, within the efficient running of the system.

If I changed the IN operator to a != or = this changed the query time to < 200ms from 40+Seconds but this was not fit for purpose as in this instance as the filter had to be dynamic. I then changed the IN statement that was contained in the WHERE clause to be part of a inner join and to join on the predicates that the IN operator was using ....and, wow! the number of logical reads on the table slumped from 4+million to 4 and the time down to 6 secs!! Not too bad as it was an offline, but crucial process.

So the long and the short is to remember; If you can join to it do so, as you may pay the price for utilisation of IN statements.

1.09 - API CURSORS and linked servers - Grrrrr!!

Like I always say; Every day is a school day.
A normally well performing system today suddenly started using 99% utilisation constantly. After taking a look I noticed that there were some strange requests on the server which all related to FETCH_APICURSORXXXXXXX. Now I could only see what these were by by querying the sys.dm_exec_cursors dmv. I then looked at profiler and saw the millions of sp_cursorfetch transactions and realised that there was a big issue. This was using a cursor because SQL was using an OLEDB connection on the linked server resulting in millions of rows individually returned  for a sizeable dataset.

After a bit of research OPENQUERY resolves this issue but it doesn't use parameters, so you need to use dynamic sql or call sp_executesql from the target server.

I hope this hopes anyone and reduces the the hours that it took me to sort out.

A good reference : http://www.sql-server-performance.com/2006/api-server-cursors/#comment-3847

1.08 - Intelllisense Refresh

To force a suggestion = CTRL+J   and to force an intellisense refresh = CTRL+SHIFT+R
- Who knew?

1.07 - SQL SERVER – How to Refresh SSMS Intellisense Cache to Update Schema Changes

Have you ever faced situation where you have just created or modified object but SSMS still shows the error. I quite often face this situation where I come across situation where my SSMS Intellisense Cache is not refreshed or updated. This is indeed very frustrating when you are presenting something on stage as the red underline means an error in graved in many people’s minds and it is hard for them to believe when the code with underline runs successfully.

Here is image of the recent situation. Where I had just dropped index but SSMS Intellisense was still showing that the index exists.

When I face this kind of situation, without wasting time I immediately do following : CTRL + SHIFT + R . This will clear the cache of the Intellisense and it removes the underline from the word. You can also refresh the Intellisense cache by using Edit -> Intellisense -> Refresh Local Cache.

Courtesy of Pinal Dave (http://blog.sqlauthority.com/2013/07/04/sql-server-how-to-refresh-ssms-intellisense-cache-to-update-schema-changes/)

1.06 - Old And Forgotten Database Recovery Options

I recently stumbled across some old commands to remove the db status from 'suspect' or 'restoring', although I would always recommend using restore database with recovery initially to remove the recovery issue.
These are :

1. sp_resetstatus
2. DBCC DBRECOVER (database) - Removed from SQL 2012

These should not be your first option but when all else fails...

1.05 - Bulk Insert Row Terminators Gotcha

I recently stumbled across this issue when a dev asked me to have a look at a file that he was attempting to import. Basically, the bulk import or the bcp statement was not recognising the row terminator and appending all the data beyond the last column into that very same last column. Annoying, to say the least.
After some researching, I found that this is quite a common problem and most people suggest dynamically building the statement and that seems to work.
e.g.
DECLARE @Cmd NVARCHAR(MAX) = 'BULK INSERT [TableName] FROM ''[InputFile]'' WITH (FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''' +CHAR(10) +''')'

EXEC SP_executeSQL @cmd

However, I am not a fan of dynamic sql and tried to find out if it can be done without and ....woohoo! It can be - You just have to use the hex values for the row terminator and then the World falls back into a sensible orbit and life makes sense again.
e.g.
BULK INSERT [TableName] FROM '[InputFile]' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0xa')

I hope this helps someone out there as it saved me a lot of time after I sussed it. 

1.04 - Xp_ReadErrorLog Parameters

These are always worth knowing about...
SQL Server: xp_readerrorlog parameter detail