TSQL Disk Usage

Only because I have a server with the Reports...Disk Usage doesn't like to work....

       
SELECT Name,  
  (CONVERT(FLOAT,size))  * (8192.0/1048576) File_Size,  
  (CONVERT(FLOAT,FILEPROPERTY(name,'SpaceUsed')))
                         * (8192.0/1048576) MB_Used,  
  ((CONVERT(FLOAT,size)) * (8192.0/1048576)
                         - (CONVERT(FLOAT,fileproperty(name,'SpaceUsed')))
                         * (8192.0/1048576)) MB_Free  
 FROM sysfiles  
ORDER BY FILEPROPERTY(name,'IsLogFile')  
 

This will show actual files sizes and MB used internally.

TFS2010 View and unsubscribe another users alerts

Unfortunately in TFS2010 there is on UI that allows you to view and remove another users post. The issue I had was that a user left, however emails were still being sent from TFS to his account.

The solution - a bit messy.

To view all alerts in a collection:

USE [Tfs_DefaultCollection]

-- Find all subscriptions
SELECT * FROM tbl_EventSubscription ORDER BY Address

*Note that the USE will need to reflect the correct collection database name


Next to unsubscribe. The follow SQL will create a command line to do that :

-- Create script to unsubscribe subscriptions
SELECT 'bissubscribe /unsubscribe /id ' + CAST(id as varchar(255))+ ' /collection http://tfs-server-name:8080/tfs/collection-name'
FROM dbo.tbl_EventSubscription
WHERE Address = 'someuser@mycompany.com'

*Note change tfs-server-name to your TFS server name, and collection-name to reflect the collection you are interested in.


Next, remote onto the TFS server. Open an elevated command line prompt. cd to the TFS installation folder \Tools.
Paste in the result line from the SQL above into the command line prompt and execute (or create a batch file, copy over to TFS server and execute it) .

Now the alert should be removed from the system.

Transactional replication, identity and NOT FOR REPLICATION

Ouch.

Here's the scenario. Have a database server (call it DBS-A, DB1) transactionally replicating to another server (DBS-B, DB1). The tables have identities on them as the unique index (to save space, and as DBS-B is readonly have no problems with duplicate data). All works fine.

So now have another server (DBS-C, DB2) replicating data into DBS-A (into another database DB2) using transactional replication. However, the replication is non standard, in that the sp_MSins_xxxx routine has been amended. This change calls a stored procedure which copies data in the DBS-A, DB1 database. This is a SELECT..INSERT type of affair which works perfectly when called from SSMS or via an application.

However, SQL Server thinks that this data is being created by replication, so obeys the NOT FOR REPLICATION flag on the IDENTITY field, and subsequently returns a not null error, expecting a value etc.

What is annoying is that when you add a table into a replication article, it automatically adds NOT FOR REPLICATION onto any IDENTITY fields and I can not see how to remove this via the UI.

After some investigation I found the following system stored procedure which will do this for you:

DECLARE @tableID INT


SELECT @tableID = object_id('myTable')
EXEC sys.sp_identitycolumnforreplication @tableID, 0

Nice !


Problem 2...
Now this seems to work perfectly on empty tables, however to my horror I found that when I tried it on a populated table (over 1million rows) that the log reader blew up with :

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: http://help/MSSQL_REPL20011

The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {000017d6:000016d7:00b9}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: http://help/18805

The process could not execute 'sp_replcmds' on 'mydatabase'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

Arrrrgggh !
This was traced to be definitely the table that I changed, as the error disappeared once the article was dropped.

After struggling through dropping and recreating the replication several times, the (unconfirmed as I don't want to try it again) solution appeared to be an order thing:

• Create an empty table with the same schema as the one you want to add to the replication
• Add empty table to replication
• Remove NOT FOR PUBLICATION using above stored procedure call
• Populate the data from full table to empty table
• Move any foreign keys over from referencing tables

That seems to work.

SSRS load image from external assembly

This has been a bit of and adventure to get this to work.

 

Scenario
I had a standalone C# assembly for creating data matrixes (2D barcodes) and it exported as a bitmap. This is really no different from loading a bmp from disk.

 

I wanted to create a report that displayed a data matrix per part in a table.

 

All sounds plausable!

 

Issues
There are a few issues with this:

 

1. The custom assemblies need to be installed and referenced. This needs to be set on the development PC and the server that the report is published to.

 

2. The Image control in SSRS (2005) needs to have the Value poperty set.

 

3. The Image Image expects a Byte array.

 

Configuration
Assuming that you have already created your assembly...

 

1. Copy the assembly to the following locations:
Development environment (may also be reporting server):
Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblie

 

Reporting Server:
Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

 

Note that these may be different depending on your installation.

 

2. Add reference to the assembly in your report.

• Open the report that will reference the custom assembly.
• On the Report menu, click Report Properties.
• In the Report Properties dialog box, click the References tab.
• Under References, click the ellipsis (...) button that is next to the Assembly name column header.
• In the Add References dialog box, click Browse. (In SQL Server 2005, click the Browse tab.)
• Locate and then click the custom assembly. Click Open. (In SQL Server 2005, click Add instead of Open.)
• In the Add References dialog box, click OK.
• In the Report Properties dialog box, click OK.

 

That's the reference installed.

 

3. Create custom code to use the assembly
This custom code will also need to convert the bitmap to a byte array...

• Right click the report and choose properties. Select the Code tab and enter code similar to the following :

Public Function GetMatrixImage(ByVal code As String) As Byte()

 

Dim photo as System.Drawing.Image
photo = myAssembly.GetTheImage(code)

 

Dim ms AS System.IO.MemoryStream = new System.IO.MemoryStream()
photo.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp)
Dim imagedata as byte()
imagedata = ms.GetBuffer()

 

return imagedata

 

End Function

 

4. Go to Layout tab and drop your Image control. Set it up as follows:

• Set Source = Database
• Set MIMEType = image/bmp (from the pulldown)
• Choose Expression from the Value drop down. In the Expression editor enter :

=Code.GetMatrixImage(Fields!ID.Value)

 

Where ID.Value is the key to creating/finding the image.

 

 

And that's it. Pretty easy really, however the documentation on how to do it is a bit thin. Hopefully this will help people out !

Solid State Drives for SQL Server

Interesting article on solid state drives and SQL Server.

SQL Server monitoring

Good article on SQL Server monitoring toolkit.

SSIS projects for monitoring your servers.

SQL Server Stats and Query Optimiser tips

Great article here with 13 Things You Should Know About Statistics and Query Optimiser. Covers a lot of ground and is worth a read.