Helpful scripts for SQL Server

There is nothing new or revolutionary about the following scripts but I am compiling them here for anyone that these may help.

SQL Server database marked 'suspect'

There are various reasons that the DBMS may mark your database as suspect . Some of these are

• The database could have been corrupted
• Not enough space for the DBMS to run a recovery operation.
• OS locks on the files
• Unexpected sql server shut down.

To resolve this copy and paste the following script into SSMS. Replace [MyDatabaseName] to the actual database that you are trying to recover.

use master
go
EXEC sp_resetstatus [MyDatabaseName];
ALTER DATABASE [MyDatabaseName] SET EMERGENCY
DBCC checkdb(MyDatabaseName)
ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (MyDatabaseName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MyDatabaseName SET MULTI_USER

Get a list of all indexes on all tables in your database

There are instances when you need a list of all the tables, with index description and keys in your database. The following script lets you do just that.

DECLARE @IndexInfoTemp  TABLE (index_name         varchar(250)
                              ,index_description  varchar(250)
                              ,index_keys         varchar(250)
                              )

DECLARE @IndexInfo  TABLE (table_name         sysname
                          ,index_name         varchar(250)
                          ,index_description  varchar(250)
                          ,index_keys         varchar(250)
                          )

DECLARE @Tables Table (RowID       int not null identity(1,1)
                      ,TableName   sysname 
                      )
DECLARE @MaxRow       int
DECLARE @CurrentRow   int
DECLARE @CurrentTable sysname

INSERT INTO @Tables
    SELECT
        DISTINCT t.name
        FROM sys.indexes i
            INNER JOIN sys.tables t ON i.object_id = t.object_id
        WHERE i.Name IS NOT NULL
SELECT @MaxRow=@@ROWCOUNT,@CurrentRow=1

WHILE @CurrentRow<=@MaxRow
BEGIN

    SELECT @CurrentTable=TableName FROM @Tables WHERE RowID=@CurrentRow

    INSERT INTO @IndexInfoTemp
    exec sp_helpindex @CurrentTable

    INSERT INTO @IndexInfo
            (table_name   , index_name , index_description , index_keys)
        SELECT
            @CurrentTable , index_name , index_description , index_keys
        FROM @IndexInfoTemp

    DELETE FROM @IndexInfoTemp

    SET @CurrentRow=@CurrentRow+1

END --WHILE
SELECT * from @IndexInfo

Kill all sleeping processes in SQL Server

There may be occasions where you may want to get a list and subsequently kill all sleeping processes. Here is a script that lets you do that.

DECLARE @v_spid INT

DECLARE c_Users CURSOR

   FAST_FORWARD FOR

   SELECT SPID

   FROM master..sysprocesses (NOLOCK)

   WHERE spid>50 

   AND status='sleeping' 

   AND DATEDIFF(mi,last_batch,GETDATE())>=60

   AND spid<>@@spid

OPEN c_Users

FETCH NEXT FROM c_Users INTO @v_spid

WHILE (@@FETCH_STATUS=0)

BEGIN

  PRINT 'Exterminating '+CONVERT(VARCHAR,@v_spid)+'...'

  EXEC('KILL '+@v_spid)

  FETCH NEXT FROM c_Users INTO @v_spid

END

CLOSE c_Users

DEALLOCATE c_Users

VS 2012 - Some of the properties associated with the solution could not be read

Recently, I upgraded one of the team projects to VS2012. Post upgrade, whenever I tried opening the solution, VS would complain with the message, "Some of the properties associated with the solution could not be read".

I opened up the solution file that did not have this issue (the one in the dev branch) and compared it to the recently upgraded solution. When I compared the solution files the value for the "SccNumberOfProjects" was different. There were supposed to be only 20 projects part of the solution but for some odd reason the value for this attribute was 21 in the upgraded version.

I changed the value of this attribute to 20 in the upgraded solution file (had to changes this in a couple of spots in the solution file) and the problem went away.

Hope this helps.

Unexpected Error "12037"

When downloading something from a secure site you may sometimes get the following error

Error 12037 - SSL certificate date that was received from the server is bad. The certificate is expired.

If this is the case, then check your clock settings. Most often then not this is because of a wrong time/zone on your machine.

Hope this helps.

Http 500 Internal server error

Scenario: I deployed an application as a virtual directory under the default site and my application threw a HTTP 500 error.

Resolution: The reason was that the default site and my virtual directory that was configured as an application had common keys (keys with the same name). I removed the key from the application in the virtual directory and the problem went away. One way to find any config issues in IIS7 is to open the configuration editor in IIS7 for the site you are troubleshooting. The configuration editor will throw specific exceptions if it cannot successfully open the config which is how I found the issue with duplicate keys.

Error -2147024893 while uninstalling ss2k5 reporting services

While recently trying to uninstall reporting services 2k5 from my machine I ran into this nasty error. Researching this I found that the default site was running. Stopped the default site, re-ran the uninstallation and everything is hunky dory again.

Enable Session State in a WCF service

The concept of session in a WCF service is widely different from a typical ASP.NET application where the session is initiated and maintained on the server. Sessions in WCF are per call and are created in the context of the channel that received the service request. I will keep this simple. If you need access to Session data in a WCF service there are 2 things that need to happen. In the web.config of your service application, the aspnetcompatibility needs to be enabled like so....
1.
<system.serviceModel>
<serviceHostingEnvironment aspNetCompatibiltyEnabled="true"/>

2. Add the following line to the class that implements your service interface like so...

[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]

The AspNetCompatibilityRequirementsMode enum has three possible values, Allowed NotAllowed and Required. Set the value of this enum according to your needs.

System.Web.HttpContext unavailable in a service class library

I encountered this recently. I have a service that implements an aggregate interface. Each interface is implemented in a seperate class library. I needed access to System.Web.HttpContext in one of my projects. When I tried to add a reference to the System.Web namespace I did not see this namespace listed in the list of available namespaces. Intrigued I checked my project properties and saw that the project was referencing .Net Client Profile of .Netfrx40. I changed this to the full .Netfrx40 and tried adding the reference again and VOILA I was able to add the namespace reference to my project and now have access to HttpContext.

Happy Coding!

PFX also known as the Parallel framework

If you have ever written multi-threaded code and wished to take advantage of multi cores/processors on your machine without writing a lot of code to partition your data and managing concurrency then you gotta take a look at the new task parallelism constructs offered by .NetFrx40. Thread concurrency is when your sequential code executes simultaneously amongst the cores in parallel. Also known as parallel programming, this is achieved by using the new PFX library. Parallel LINQ, the parallel class, the task parallelism constructs and the concurrent collections are collectively known as the PFX. The parallel class together with the task construct is called the Task Parallel Library or TPL.

So, how can the Parallel class help? Imagine you have a list that represents a collection of objects that you want some db operation to be performed on. Moreover, you want to take advantage of your hardware that has two dual core processors. If your collection has say, for instance 4 items in it, your sequential code will iterate over this collection using the same thread.

 foreach(var m in list)                                                                                                                                      { do some db processing }

When you use the Parallel.Foreach construct you can then do the same work over 4 threads executing simultaneously, one on each core. Your code would look something like the following.

Parallel.Foreach(list, ()=>

{
Same code as above in sequential code.
});

The runtime manages the work and aggregates the result from the originating thread/processor.

 A few things to keep in mind. When using the task parallelism constructs remember you still have to manage access to critical sections within your thread, so you do have to lock access to critical sections within your threads.

I benchmarked the performance difference and it is in the order of magnitude in my project.                                                                               

Entity Framework: The ObjectContext instance has been disposed and can no longer be used for operations that require a connection

I had a piece of sequential code that executed in its own task (PFX). This was a sequential foreach loop. I changed this foreach to Parallel.Foreach and my code bombed. I do not think that the issue had anything to do with me trying to execute the foreach loop on all the cores at once. The actual problem was that I was retuning a value from within a using statement and EF did not like it.

Once the removed the return value from my method outside the using statement all is well.

log4net: No appenders could be found for logger

This is my initial foray into Log4Net for use with my WCF service. Since Log4Net is configuration based, if you do not see anything logged to your destination (in my case it is a database) then you have missed something in your config (that should not be any surprise, my point is to reinforce the reader to double check their config settings). In my particular case I am using the AdoNetAppender. I configured my database, installed and referenced the Log4net dll's and set about to log my first exception. After messing with it for a couple of hours I realized that I was missing the <appender-ref ref="AdoNetAppender" /> key in my config. The exception that was being thrown by Log4Net was

log4net: Logger: No appenders could be found for logger [PSLogger, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null] repository [log4net-default-repository]
log4net: Logger: Please initialize the log4net system properly.
log4net: Logger:    Current AppDomain context information:
log4net: Logger:       BaseDirectory   : C:\$\1.0\PSAuditService\PSAuditService\
log4net: Logger:       FriendlyName    : dbd5742-18-129488220968600299
log4net: Logger:       DynamicDirectory: C:\Windows\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files\root\ec718ec8\d90fe912

After adding the afore mentioned key all seems to be well.

Hope this helps someone.

This collection already contains an address with scheme Http. There can be at most one address per scheme in this collection

Recently, I deployed my fully tested WCF service to a shared hosting environment and I was greeted by this nasty message the first time I tried to test my service by entering the url in the address bar. Upon researching the issue I realized that my ISP had allowed my service to be accessible using multiple host names. Obviously, I did not have access to their IIS, so had to come up with an alternate solution. In my solution, I created a custom ServiceHostFactory and forced WCF to use this host factory.

1. Create a new class that inherits from the ServiceHostFactory.
2. Override the CreateServiceHost, create a new servicehost type with the Uri of that you want the service to bind to and return the new servicehost from the CreateServiceHost method.
 3. Change the .svc file of your service to use the new custom service host that you have created. This is done by providing the namespace name of your servicehost in the Factory attribute of the .svc file.


Hope this helps.

Error 500.19. MSSQL Server 2005 Reporting Services and Windows 7

I recently started working with SSRS once again after a sabbath of 4 years and tell you what....it was kinda difficult to recollect everything. Well for once I was working on a new OS with an old version of SQLServer. I installed SqlServer2005 and reporting services, the fired up the reporting services configuration editor to setup my reporting server. Following are the caveats that you need to remember for a sucessful install and initialization of reporting services.
Since SSRS uses .Net framework 2.0 do not use the default AppPool to run your reporting services (the default app pool in my case was using .Net framework 4) . It will make your life hell. To resolve this, open up IIS manager on your windows7 machine and click on your report server site. In the Actions pane click Basic Settings and then the Select button in the 'Edit Application' dialog box. This will open another dialog box that should let you change the app pool that your report server is using.
Since the report server virtual directory is installed under \%systemdrive%\Program Files (x86)\Microsoft SQL Server\MSSQL.3\Reporting Services, Windows7 will not let you run the site using the IUSR account. You will have to expressly grant the IUSR account read permission in the least. This was the reason in my case for Error 500.19.
Hope this helps. Enjoy!

An unknown error has occured in the WMI Provider. Error Code 8000000A

Recently while using reporting services configuration editor on a Windows 7 machine I ran into this error. On first look, I thought I was toast and would have to spend oodles of hours trying to fix it. The solution is pretty simple.....run the reporting services configuration editor as an administrator and this annoying error should go away. Would be helpful if Microsoft could put a little hint in the error thrown something like 'Are you running the configuration editor as an administrator?', kinda like the missing assembly reference hint on a typical .Net compile. Dont you agree?

Setting up a 'RAM Drive' and deploying your windows mobile app.

Recently I have started doing windows mobile development and to my dismay the storage space for programs on the emulator is limited to 32 MB by default. Well, that would obviously not work especially when you are deploying WCF related namespaces alongwith your other project dependent assemblies.
Luckily, there is a work around to this problem. You can setup a virtual storage card on your device that points to a local drive on your development PC. This is how you would do it. I am using a Windows Mobile 6 Professional Emulator

1. Fire up the emulator from within Visual Studio (Tools -> Device Emulator Manager).
2. Click File -> Configure from the menu.
3. On the General Tab, towards the bottom you will see a place holder for your Shared Folder.
4. Click browse [..] and select the folder on your local machine you would like to use as a virtual storage card.
You should now be able to see details of your storage card on your device.

DEPLOYING your windows mobile app to the device and debugging in VS (I am using VS 2008).

1. In VS, right click your project and select properties.
2. Click the devices tab.
3. Click on the ellisis [..] button beside the Output file folder.
4. Select Root folder from the drop down and specify a path (subdirectory) where you would like VS to deploy your application.
5. Select Ok and you should be in business.