Running a database mirror setup with the SQLBrowser service off may produce unexpected results.

I had an interesting case the other day which I thought I’d share some information on here.Basically the setup was a SQL Server 2008 Database Mirror setup and the connectionstring looked something like this: @”Data Source=SERVER_A\INSTANCE_A,12345;Failover Partner=SERVER_B\INSTANCE_B,54321;Initial Catalog=MirroredDatabase;”; Perhaps you’ll notice that the connectionstring contains both server and instance and port number.In this case the…

3

Clarification on the Failover Partner in the connectionstring in Database Mirror setup.

There is a common misunderstanding on how the Failover Partner keyword in the connectionstring works in a database mirror setup.This is my attempt to clarify a bit on this. From the documentation we can see the following: “Failover Partner – The name of the failover partner server where database mirroring is configured.”This then suggests that…

9

How to consume a web service from within SQL Server using SQL CLR

Today I’ll show how you can consume a Web Service from within SQL Server using the SQL Server CLR.   DISCLAIMER: This is just intended as proof of concept. I do feel that if you wish to consume web services from SQL Server, then you should probably put this code in an assembly outside SQL…

6

Error : The OutputPath property is not set for project ‘xxxxx.dbproj’.

I had an interesting case the other day and I thought I’d share how we managed to get around the problem. This post will not discuss why this happens or if this is the correct behavior. It will just show a way to build your project.   So, the scenario is as follows. In Visual…

0

Simplified steps for creating BID / ETW traces for ADO.Net and SQLNCLI

In order to create BID traces for ADO.Net and/or SQLNCLI/10 follow these simplified steps:   #1 On the machine where client code runs, create a new directory called C:\BID #2 Locate the appropriate diagnostic dll.   ADO.Net: For .Net 2.0, default location is: C:\Windows\Microsoft.NET\Framework\v2.0.50727\AdoNetDiag.dll For .Net 4.0, default location is: C:\Windows\Microsoft.NET\Framework\v4.0.30319\AdoNetDiag.dll   SQLNCLI an SQLNCLI10…

0

Invalid object name ‘MSysConf’. (State 42S02) (Code 208)

Sometimes customers call in wondering why they have the following Exception and User Error Message in their SQL Profiler log:   Exception: Error: 208, Severity: 16, State: 1 User Error Message: Invalid object name ‘MSysConf’.   or, if they replay a SQL profiler trace, it may be a Replay Provider Error:   [Microsoft][SQL Server Native…

0

"Error Locating Server/Instance Specified [xFFFFFFFF]". SQLSRV32 to the rescue.

When connecting to SQL Server and when the connection fails the reason for the error may sometimes be a bit unclear.   For this example I have a SQL Server 2008 instance called Spike2008 running on a machine called SpikeSrv2008 (running on Windows Server 2008). So the full name of the server is SpikeSrv2008\Spike2008.  …

2

Registry keys for ODBC connections using sqlsrv32, sqlncli and sqlncli10

  Here is the list of the registry keys used for ODBC connections when using SQLSRV32.dll / SQLNCLI.dll / SQLNCLI10.dll respectively. Obviously keys for sqlsrv32 exist for SQLNCLI.dll and SQLNCLI10.dll for backwards compatibility. And subsequently all keys for SQLNCLI.dll exists for SQLNCLI10.dll for the same reason.   The keys below are when using “User DSN”,…

1

Simplified steps to create BID / ETW tracefiles.

  Sometimes we in support need you to take a BID (Built In Diagnostics) or ETW (Event Tracing for Windows) trace in order to troubleshoot a problem.   There are a few documents out there on how to create these, but they are somewhat verbose for the purpose of just creating the .etl file. So…

0

How to find the culprit and a blocking scenario.

  A post on how to troubleshoot blocking clients.   The scenario is that you have many users that complain that all of a sudden the applications hang and no data is returned. The setup for demoing this scenario:   Machine 1: This is the SQL Server 2008 machine, called MIKESPIKE\SPIKE2008 in this case. Machine…

0