Simons SQL Blog : Bugs

Do you use schemas? would you like keyboard shortcuts in SSMS to work with them

simonsabin — Wed, 01 Jun 2011 08:16:49 GMT

If you use schemas then you will know that keyboard shortcuts don’t work with them due to the period (.) in between the schema and the object name. If you would like the SQL team to fix this, then vote on this connect item https://connect.microsoft.com...(read more)

FTP doesn’t work with proxy accounts in SQL Agent – calloc error

simonsabin — Mon, 08 Nov 2010 13:42:18 GMT

Not sure this is a windows server 2008 R2 feature or more wide spread, I would guess that it applies to all windows operating systems. If you try and user a proxy account for a sql agent job that does an FTP using the windows FTP.exe , whether through...(read more)

SQL Intellisense doesn’t work with CAST

simonsabin — Sat, 16 Oct 2010 07:12:47 GMT

If you find this behaviour very frustrating then please vote https://connect.microsoft.com/SQLServer/feedback/details/614130/intellisense-doesnt-work-with-cast The problem is that CAST invokes the intellisense drop down when you type AS, when it shouldn...(read more)

Error: The type of the value being assigned to variable ?? differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object

simonsabin — Tue, 19 Jan 2010 19:05:14 GMT

If you get the above error and you have no idea why (i.e you haven't changed the data type of a variable) then it is likely due to a data type inference issue with SSIS. You might think that SQL is returning a value of one type when in fact it isn't its returning something else.

This occurs with a number of functions in SQL, the key is that you think your SQL is returning an integer when in fact its returning a decimal or a data type that SQL Server Integration services maps to a string.

Two fiunctions I've encountered this are row_number and scope_identity.

The solution is to explicitly cast your columns in your query you return to SQL Server Integration services i.e. cast(scope_identity() as int) or cast(row_number() over (order by somecolumn) as int)

If you encounter this you can check [BOL] to see what data type is returned by the function.

The issue is documented on the SSIS team blog Why can’t I store my BIGINT result in an Int64 .... This is the same issue.

User defined function performance is awful, Micorosft please sort this out

simonsabin — Thu, 14 Jan 2010 19:21:13 GMT

User defined functions looked like something great when they were introduced in SQL 2005. They are a very logical step to make more code reuseable by wrapping common code in functions. It is an approach that is in development 101 and something everyone does and looks to doing.

So what is the problem with SQL Server.

Well the issue is that whilst there are great from a reducation of duplicated code the performance sucks, and sucks big time. I wrote about this a year ago TSQL Scalar functions are evil and have spoke about it at many usergroups over the years.

Almost every client I've seen over the past 2 years has implemented functions because it is the logical thing to do. However they've then been bitten big style by the performance issue. To make the matter worse use of scalar functions also gets in the way of supporting code, try and do a stmt level profile or something I found this week try and capture query plans in profiler. Because each of these is considered a block of code it results in stmtCompeleted and starting events to fire and for query plan event to fire.

At a recent client I was able to tune a query from 3 minutes to 3 seconds using the tricks in "TSQL Scalar functions are evil"

Scalar user defined functions are the worst thing in SQL Server and something needs to be done about them. I feel this is a bug in the product and have posted as such here https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273443

If you have been bitten by this bug please vote for the item on connect go here "Vote for changing user defined functions so they perform better "

[SS 2010/01/19 I've been told my connect item is a duplicate and so has been closed. SO please vote on this item instead https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273443 The links above now point to this connect item]

"unable to switch the encoding" when deploying XML Schemas using Database Professional

simonsabin — Thu, 14 Jan 2010 17:14:33 GMT

I've just blogeed about how to use XML Schema Collections in Visual Studio Database Professional Project

I mentioned there is one gotcha about. The gotcha is if you have UTF-8 encoded schemas then they fail to be deployed. This is because they get deployed using

CREATE XML SCHEMA COLLECTION [dbo].[LogiPointRec]
AS N'<?xml version="1.0" encoding="UTF-8"?>

Note the N' this results in the following error

XML parsing: line 1, character 38, unable to switch the encoding

To get round this I haven't found a way for Database Professional to not use the unicode prefix, so you have to change to UTF-16 encoding of you schema. I've reported this here https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=524977 so please vote if you have the same problem.

A significant part of sql server process memory has been paged out

simonsabin — Tue, 10 Mar 2009 21:52:46 GMT

Do you have the following occuring on your SQL Server system.

If so it is likely you are running SQL Server 2005 or SQL Server 2008 Standard edition on x64 hardware.

It will be causing you serious performance issues when it occurs, as all your database pages will be removed from memory and thus SQL will have to read them again from disk. The MVP community and especially Maciej Pilecki is trying to get Microsoft to do something more proactive about it.

The problem is due to bugs in third party drivers and OS bugs.

According to Microsoft all known bugs have been fixed by the relevant vendors and so you shouldn't be experiencing this issue, if you update your system

If you are still experiencing this, which we believ many people still are, then you must contact customer support (https://support.microsoft.com/oas/default.aspx?iid=246306&iguid=bd81e653-64b3-4bf7-bec9-798309406d4d_2_1&ln=en-gb&prid=9455&gprid=347865) to get the to provide you with details of the updates that you will need to apply to your system. My understanding is that this will not cost you anything, as it is a known bug. (Some are detailed here http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

If after contacting CSS you are unable to get an immediate fix, or they want you to pay please comment and vote on this connect item

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=422322

Or send me email using this form http://sqlblogcasts.com/blogs/simons/contact.aspx

Gotchas in Management Studio 2008

simonsabin — Mon, 03 Nov 2008 23:09:48 GMT

If you script objects in management studio 2008 be careful its a bit forgetful. If you are using a case insensitive collation and you have objects that refer to other objects but use different case for the object names then when you try and script objects some might not be included. I found this when trying to script a function and two tables. The function referred to one of the tables but with a different case. Oddly this table didn't get scripted.

This has been fixed and will be available in an upcoming cumulative update.

The next gotcha is using the debugging in SSMS 2008. You have to be careful if you cancel your debug session because the database you are connected to might change

If you cancel a query that is in debug mode, the query window will be connected to the database selected when the query window was opened. i.e. if you change the database before you start debugging the database will not be the same before and after cancelling the query,

To repro.

Open a new window this is likely to start in master
change the database from the drop down say tempdb
type wait for delay '00:00:10'
start debugging
cancel debugging
you will now be connected to master and not tempdb.

Be warned.

What application doesn't have UNDO in today's world

simonsabin — Tue, 06 Nov 2007 14:43:36 GMT

Well the first that is close to my heart is Integration Services and its really annoying.

I tried to track down a connect item for this but couldn't find a specific one. So I raised one https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=308794.

We've been told that undo didn't make the Katmai triage bar. Personally I think that's just rediculous. If you agree make sure you vote on it.

Next in the list is the reporting services dataset designer. There is an undo feature for the report, however it is inconsistent and can cause you to loose lots of changes if you hit undo whilst in the dataset designer. I know I've lost hours of work in doing this. I couldn't find a connect item when I searched on "report designer undo" so I raised on.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=308790. However when I further investigated this blog post I found this item https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299431 which describes the situation as worse than mine. This one has been closed (by design) which seems an odd closure. However to make sure this is fixed you have to still vote on it.

They are many blog posts that support this http://blogs.conchango.com/jamiethomson/archive/2007/07/27/SSIS_3A00_-The-backlash-continues.aspx, http://blogs.conchango.com/jamiethomson/archive/2005/05/09/1398.aspx, http://www.ayende.com/Blog/archive/2007/07/15/SSIS-15-Faults.aspx, http://sqlblogcasts.com/blogs/simons/638.aspx

Full text thesaurus files bug in documentation

simonsabin — Sat, 22 Sep 2007 22:00:54 GMT

I don't normally blog about documentation errors generally because they're often not that major however this one is fundamental what is being explained.

This is the page online http://msdn2.microsoft.com/en-us/library/7f5424a4-a72c-44dc-b455-786a12767c99.aspx It talks about creating new thesaurus files. Unfortunately the XML example is wrong. When I looked at it intially I was suprised because the diacritics element wasn't valid XML. Well looking at the examples in the <SQL install folder>/FTData/ts????.xml you can see that the example in the documentation is wrong.

The diacritics element should be

<diacritics_sensitive>0</diacritics_sensitive>

Being a good SQL Server citzen I have reported this via the feedback in BOL so I hope it should be fixed.