I started receiving the error the full-text query parameter for fulltext query string is not valid after upgrading to .NET 4 when calling a function in SQL Server 2008 from Linq to SQL.   It seems LTS is sending the string as nvarchar(4000) event though I had defined the paramater to the function with a length of nvarchar(200).

The work around that I put in place as we define the parameter to my function as 4000.  Not ideal, but it resolved the error.

Experienced a very strang issue with a query after upgrading to SQL Server 2008 from 2005.  A query that took less than one second on SQL 2005 ended up taking 27 seconds on SQL Server 2008. 

Exact scenario:

Query that selects data and one of the columns in the select list belonged to a full text index.  If this column is not in the select list, the query runs in under one second.
Removing the full text index did not make a difference
Changing the data type from nvarchar(max) to nvarchar(3000) did not make a difference
adding a where filter of column != '' worked

Not sure why this happened and only in SQL 2008 but not in 2005 but if you are seeing the same issue, I hope this article helps.

Recently ran into an issue with long running sql queries that used TCP to communicate between different SQL Server servers.  We sporatically noticed the below errors and SQL job failures for jobs that ran long running sql queries.

Error: 2009-09-28 08:43:40.62     Code: 0xC0202009     Source:      Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Protocol error in TDS stream".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Communication link failure".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "TCP Provider: An existing connection was forcibly closed by the remote host.  ".  An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Communication link failure".  An OLE DB record is ...  The package execution fa...  The step failed.

After some research, the below KB article outlines that TCP Chimney may be the cause.  Turns out, TCP Chimney may require an update the your NIC drivers.  

http://support.microsoft.com/kb/945977

We have not had the chance to upgrade our drivers, but we did disable TCP Chimney and that eliminated the error. To disable, issue the below statement at the command prompt (does not require a restart):

Netsh int ip set chimney DISABLED;

Need a developer, architect or manager? I am available - email me at [email protected]

SQL Server full-text search filters out certain noise words by default.  If you want to include some of these noise words in your full text search, you will need to do the following.

1. Locate the noise file for your language.  For English, this would be noiseENU.txt and is located in the SQL Server install directory.  For example C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData (Your location may vary).  You can also use the following query to help determine the location:
   
   select * from sys.fulltext_catalogs
   
   
2. Edit this file.  You can add or remove noise words/numbers, etc. from this file.
   
   
3. The last thing you must do is rebuild the catalog (*Note, if you have a large catalog, do this off-hours as it will impact performance):
   
   ALTER FULLTEXT CATALOG YourCatalogNameHere
   REBUILD WITH ACCENT_SENSITIVITY = OFF

Problem: you want to create a comma seperated list of values from a column in a database table.  You constraints are that you want to create the comma seperated list at the database level and you can not use a cursor or while loop.

For example, if you have a table called "Category" with a column "Name" that contains the values:

 Name
Breakfast
Lunch
Dinner

and you want to return Breakfast, Lunch, Dinner.

The key is to initialize the string var to an empty string so the concatenation works as expected. .

Below is a list of software development podcasts, IT Podcasts, and general computer related podcasts. Please add commnets on podcasts you enjoy...

.NET Rocks - This is a really good (quality and content) podcast that focuses on .NET development.  While the main focus is .NET there is a fair amount of content that applies to general computing and development such as design patterns, agilie practices, and more.

Software Engineerig Radio - This podcast provides an excellent variaty of topics that span technologies, languages, platforms, methodologies, etc. The shows can be rather technical and geared more toward knowledgable developers, but still offer a wealth of knowledge for beginners through experts.

Hansel Minutes - This is a weekly podcast that is primarily focused on Microsoft technologies but also includes other technologies and topics from time to time (for example digital photography).  While Scott is a Microsoft employee, the cast is rather open to non MS technologies and techniques such as TDD, ALT.NET, etc.

The Java Posse - This is an excellent podcast focused on the Java language, platform, and related technologies.  It provides both technical information as well as news and events within the Java community.

Buzz Out Loud - This is a daily podcast that is a summary of news and events relating to technology.  The personalities of the hosts really help to seperate this podcast from the usually news recap type shows...certainly worth checking out...

Polymorphic Podcast - A good podcast focusing on development and .NET.  The podcasts sometimes include screencasts and overall provides a lot of good content.

SSWUG - A database centric podcast dealing mainly with SQL Server but does include topics on other RBDMS as general database best practices.

Slashdot Review -  While at times biased, this can be a quick and informative cast.

Google Developer Podcast - Hopefully this will be a good resource to learn more about the technolgy offerings available through google.

Windows Weekly Podcast - Interesting take on the windows world.

ALT.NET Podcasts - A really good podcast that covers topics that all developrs should familiarize themselves with.

Deep Fried Bytes Podcast - Good podcast focusing on mainly Microsoft development

Thirsty Developer - Another good developer podcast focusing mainly on .NET

herdingcode.com - Good podcast relating to development

WebDevRadio - Covers web development

eCorner - Stanford entreprenure podcast 

Stackoverflow - development podcast 

ThoughtWorks - Business and technology topics

Agile Toolkit - topics relating to Agile development 

Udi Dahan - SOA podcast 

Endpoint.tv - New show hosted by Ron Jacobs on REST, SOA, web services, etc. 

http://pixel8.infragistics.com/default.aspx - UI, UX, RIA info

Finacial Physician

Railscast - webcasts on Ruby on Rails

Rails Podcast - podcasts on RoR

FLOSS - Weekly podcast on free and open source software

GiaOM - weekly show about technology and business 

Pragmatic Programmers - Interesting tech podcasts

Rails Envy - Regular podcast on Ruby on Rails

The Start Up Success Podcast - Name says it all...

This Week in Start Ups - Podcast on business and technology

Mobil Orchard - iPhone development topics

Stuff You Should Know - Variety of interesting topics from science to general knowledge....quite entertaining....

One of the nice tools that is provided with SQL Server 2005 is the TableDiff.exe table comparison tool.  It allows you to compare the data in the same table on different servers or instances.  Below is an expample that compares the data in "table1" and places the results in the table "Table1DifferencesServer1Server2".  This command needs to be run from the command line, in the directory where tablediff.exe is located.

C:\Program Files\Microsoft SQL Server\90\COM> tablediff -sourceserver "server1" -sourcedatabase "database1" -sourcetable "table1" -destinationserver "server2" -destinationdatabase "database1" -destinationtable "table1" -et Table1DifferencesServer1Server2

 More info can be found on BOL http://msdn2.microsoft.com/en-us/library/ms162843.aspx

If you create a named instance in SQL Server 2005 and you receive the message below you should try the following:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

1. Verify that remote connections are enabled.  You can right click on the instance from SSMS and check the connections section to make sure remote connections are enabled or use the surface area configuration tool.

2. If after verifying that remote connections are enabled, you still can not connect, you should enable and start the SQL Server Browser service using the Surface Area Configuration tool.  Once this service starts, you should be able to connect to the named instance(s). 

I have encountered an issue when running the database tuning advisor against SQL Server 2005 that if the advisor errors out while performing its analysis, it will leave the hypothetical stats/indexes in your database.  These should be removed as they may result in additional processing for SQL Server.