Deleting a Windows Service

While I typically stick with Microsoft products there are times that I must drift. I'm currently working with a client that is using Cognos for their metadata and presentation (reporting and analysis) layers. I ran into an issue this morning while trying to upgrade their version of Cognos. Unfortunately there is no automated upgrade process between the version they have and the one they want to upgrade to. This meant uninstalling the old and installing the new. In the process I could not start the Cognos service because the previous version already had a service registered with the same name. I tried uninstalling and reintsalling a number of times but to no avail. Eventually I decided to manually delete the service. I did this with the following commands (entered at a command prompt):

This command lists all the registered services on the machine
sc query state= all findstr "SERVICE_NAME"

This command deletes the service
sc delete service_name

If necessary you can also delete the service from the registry. You should be able to find the key in the following location:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

SSRS Rendering Extension - Pipe Delimited

It's very easy to leverage the existing Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering type to "create" a new rendering option in SSRS that uses a delimiter other than a comma. Below is an example of adding a "CSV" renderer that uses a pipe ( | ).

Simply add the following to the Render section of your rsreportserver.config file to add the option for rendering a pipe delimited file.


<Extension Name="PIPE" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
    <OverrideNames>
        <Name Language="en-US">CSV (pipe delimited)</Name>
    </OverrideNames>
    <Configuration>
        <DeviceInfo>
            <FieldDelimiter>|</FieldDelimiter>
            <NoHeader>false</NoHeader>
        </DeviceInfo>
    </Configuration>
</Extension>
   
Note the FieldDelimiter tag. This is where you can set your delimiter to the character of your choice.

Be sure to include the OverrideNames tag but feel free to change the value. Once you have completed the change, restart the Reporting Services service to reload the newly modified config file.

SSAS Deployment Error: No mapping between account names and security IDs was done. .

Check to make sure all the Active Directory ids in your role objects are valid. Most likely this error is being caused by a user or group that has been removed from your Active Directory.

SQL Server Management Studio Default Isolation Level Can Cause Locking/Blocking

Many unsofisticated and/or underfunded organizations have people executing ad hoc queries against production SQL Server instances with little or no oversight. I am currently in an organization where employees on the operations team as well as the marketing and business intelligence teams have the ability to execute queries via SQL Server Management Studio, directly against production OLTP databases. If a query takes an hour to run, so be it. They need their data and they will wait. These users know nothing about locks, blocking, resource contention, IO bottlenecks, etc and therefore have no idea what problems they might be causing.

Unfortunately, the default behavior for SQL Server Management Studio is to default each connection's Isolation Level to Read Committed. This can cause blocking under a number of different scenarios. I won't detail them here but believe me, it can be a problem.
However, this problem can be avoided. SSMS provides the ability to set the default Isolation Level for all queries. The option can be found by navigating to Tools -> Options. From there, Query Execution -> SQL Server -> Advanced.

I suggest setting the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED and the DEADLOCK_PRIORITY to Low. Setting the DEADLOCK_PRIORITY to Low will mean that, in the case of a deadlock, the SSMS query will most likely be aborted. Also note the LOCK TIMEOUT value of 30,000 milliseconds. This is the equivalent of 30 seconds and means that if an SSMS query encounters a lock for more than 30 seconds, the query will be aborted.
Note that you can also set the Isolation Level for the active connection by issuing the SET TRANSACTION ISOLATION LEVEL command. For example:
     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

This command can also be used in stored procedures instead of using NOLOCK hints on all tables.

**Keep in mind that using this transaction isolation level can lead to inconsistent data in your result set since you can read uncommitted data. 
 

Below are the descriptions of the different Isolation Levels from SQL Server BOL.

READ COMMITTED
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.


READ UNCOMMITTED
Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.


REPEATABLE READ
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.


SERIALIZABLE
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Linked Server NT AUTHORITY\ANONYMOUS LOGON Error - SPN Issue

I was recently struggling with a couple of linked servers. Every time I tried testing them or running a query that referenced the linked server I received the following error:
       Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON
The environment I was operating in had an overly complex network configuration that I won't begin to attempt to describe. Anyhow, I was convinced that this had something to do with Kerberos authentication. It turns out it does although not the way I had originally thought.


The SQL Server had recently been migrated to a new domain. In the process, an SPN (Service Principal Name) had not been modified accordingly. By using setspn -L servername I was able to see that the SPN referenced the old domain name.

To resolve the problem the SPN was deleted using setspn -D.

Column Headers in Matrix

In SSRS for SQL Server 2005, the matrix component does not allow for out of the box row group column headers. The default behavior is for the matrix to have one cell that spans all row group columns.

Often, when we have only one row group it is obvious to the end user what the data represents (e.g. store name, state, etc.). However, when we we have multiple row groups, it is less obvious what is represented in each column. Hence, the need for column headers. Don't fret. It is possible to create column headers.

Here's how . . .
In the empty cell above the rows add a Rectangle and size it to fit the entire cell. Now add Textboxes for each column you want a header for. Size the Textboxes to be aligned to your data cells. You now have placeholders for your column header text. The key to making this work is properly sizing your columns and headers and setting the CanGrow and CanShrink properties to false.

Top Clause - Using Dynamic Values

Prior to SQL Server 2005, the value in the TOP clause was limited to literal values which meant that variables could not be used. Hence, the number of rows returned could not be variable/dynamic. Below is an example:

SELECT TOP 10 * FROM table1

New to SQL Server 2005 is the ability to use a variable in the TOP clause.

DECLARE @top INT
SET @top = 10
SELECT TOP (@top) *
FROM table1

This is pretty useful when there is a requirement to have a user determine the number of rows returned in a result set. Note that the variable must be between parantheses.