Month / May 2010

by Arvind Shyamsundar

Web installer for obtaining / installing debugging tools (WinDbg)

Recently there has been a change to the way you obtain downloads of the WinDbg family of Debugging Tools for Windows. The classic link for the direct download of these installers was http://www.microsoft.com/whdc/devtools/debugging/default.mspx. Unfortunately, that link only directly makes available the older versions of the debugger (circa March 2009.) For the latest debugger it recommends you download the Windows Development Kit, which is around 700MB in size.

Relief is at hand, though: there is a Web installer for the WinSDK which allows you to just select those items of choice, such as the Debugging Tools for Windows. The link for the SDK is at http://msdn.microsoft.com/en-us/windows/bb980924.aspx

Do note that, this will INSTALL the debugging tools (which is not a bad thing, you can later XCOPY it to your target server) to the default location such as “C:Program FilesDebugging Tools for Windows (x64)” (in my case this is the path, as I’m using x64 OS.) But it does help save some download time.

Advertisement
by Arvind Shyamsundar

Programmatically Getting version of loaded assembly

I recently had to determine at runtime the version of the assembly containing a particular type. After some searching I hit upon this:

System.Reflection.Assembly.GetAssembly(typeof(MyNamespace.MyType)).GetName().Version.ToString()

Do note that this will only work if the assembly containing the referenced type is already loaded.

Updated based on a comment, this also does the job equally well:

typeof(MyNamespace.MyType).Assembly.GetName().Version.ToString()

Thank you!

by Arvind Shyamsundar

Reportviewer and drillthrough

I was doing some testing the other day with a ReportViewer control hosted in a WinForms application to do local mode report processing. As some of my reports had a drillthrough / navigation option set, I had setup a set of DrillthroughEventHandler to ensure that the right datasources are bound to the report when the drillthrough reports are invoked.

The problem in my case was that on specific reports being invoked, the drillthrough would cause an exception inside ReportViewer and it would display:

An error occurred during rendering of the report.
Object reference not set to an instance of an object.

If this issue was random I would have suspected issues like the one in http://support.microsoft.com/kb/959595 but I was able to get this error consistently. On later troubleshooting it was clear that:

  • I had two DrillthroughEventHandler setup, but only one for each report should have been ‘active’.
  • Unfortunately the way you setup these handlers is you use the following construct:

reportViewer1.Drillthrough += new DrillthroughEventHandler(CorrectDrillthroughEventHandler)

  • Due to the way my code was structured, it turned out I would add the 2nd DrillthroughEventHandler to a report which never really needed it. So I ended up making sure that the unwanted handlers were registered first, just before the call to the registration of the correct one.

reportViewer1.Drillthrough -= new DrillthroughEventHandler(UnwantedDrillthroughEventHandler);

Doing this got rid of the exception and things worked fine for me. Of course, be sure that this is not the ONLY reason for the above exception. This was a specific case and I hope it might prove useful for someone who is using Drillthrough and multiple DrillthroughEventHandler.

If you found this useful, please do leave a comment! And if you are logged in, please do rate the post as well.

by Arvind Shyamsundar

Replication and Linked Servers

Problem

I recently hit upon an issue with trying to setup a linked server to an instance which was already a subscriber to a publication. When replication is setup, it actually creates a remote server for the subscriber. However that ‘remote server’ is not configured for data access. So if you try to use that server, you would end up with:

Msg 7411, Level 16, State 1, Line 1
Server ‘foosub’ is not configured for DATA ACCESS.

Also, any attempt to add a similarly named linked server would fail with the error message below:

EXEC master.dbo.sp_addlinkedserver @server = N’foosub’, @srvproduct=N’SQL Server’

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82
The server ‘foosub’ already exists.

Troubleshooting

Next, I tried to add a linked server (using the SQL Native Client) but with a different name (MYSRV) but pointing to the right server (foosub). My initial attempt yielded the following error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

Solution

The problem turned out that I had not used the right ‘provider’ string. Here is the script which finally worked for me:

EXEC master.dbo.sp_addlinkedserver @server = N’MYSVR’, @srvproduct=N’foosub’, @provider=N’SQLNCLI10′, @provstr=N’Server=foosub;Database=master;Trusted_Connection=yes’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MYSVR’, @locallogin = NULL , @useself = N’True’
GO

Or if you are more comfortable using the SQLOLEDB provider, here’s a sample:

EXEC master.dbo.sp_addlinkedserver @server = N’MYSRV’, @srvproduct=N’SQLOLEDB’, @provider=N’SQLOLEDB’, @datasrc=N’foosub’, @provstr=N’Data Source=foosub;Initial Catalog=master’, @catalog=N’master’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’MYSRV’, @locallogin = NULL , @useself = N’True’
GO

You can also do this from the SSMS GUI by using ‘‘SQLNCI10’ or ‘SQLOLEDB’ as the Provider.

With this, I can subsequently access remote tables as such:

SELECT * FROM MYSVR.master.sys.tables

Hope this is useful! Please leave a comment if you find it useful.

by Arvind Shyamsundar

My Favorite SQL Server Blogs

At our workshops and during other customer interactions, we are usually asked for links to good blogs and reading materials. Here is a list of my favorite SQL-related blogs, arranged in no specific order.

http://blogs.msdn.com/craigfr: Craig Freedman on Query Processing (QP)

http://blogs.msdn.com/sqlprogrammability: Plan cache, parameterization etc.

http://blogs.msdn.com/sqlserverstorageengine: Storage Engine

http://blogs.msdn.com/repltalk: Replication support team

http://blogs.msdn.com/psssql: CSS SQL Support team

http://www.sqlskills.com/blogs/PAUL: Paul Randal with lots of tips

http://blogs.msdn.com/sqlqueryprocessing/default.aspx: Query Processing again

http://blogs.msdn.com/sqlserverfaq: Other CSS SQL Support team members

http://blogs.msdn.com/davidlean: Good series of posts on SQL Spatial features

http://blogs.msdn.com/sql_pfe_blog: Our global SQL PFE team blog

http://blogs.msdn.com/robertbruckner: Good information on SSRS

http://www.sqlcat.com: The SQL Server Customer Advisory Team (SQLCAT)

http://blogs.msdn.com/sqlreleaseservices: Learn about the latest releases (Service Pack / Cumulative Update) from SQL product team

I will be updating this list as and when I remember / discover other useful blogs. I hope this is useful for the community!