bug

You are currently browsing articles tagged bug.

Once again I’ve been wasting some time because of a silly bug.  This time it was due to the OLE DB Source component and the way it works with parameters.  If you are in a situation where you know your query is working fine and yet no records are going down the data flow, here’s a possible solution!

Disclaimer: this issue exists up until SQL Server 2008 R2.  Read on for details!

Update: after being advised to do so by several people, including Jamie Thomson, I’ve filed a bug at MS Connect: SSIS OLE DB Source incorrectly returns zero records in combination with parameter and comment

The Situation

I had a Data Flow with an OLE DB Source that uses one parameter, for instance:

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

I knew the query was working fine because when executed through SSMS and with the question mark replaced with ‘blue’, it would return 28 rows:

28 records in Management Studio

But when executed in BIDS, through either Execute Package or Execute Task, it would return zero records:

Zero records, zilch, nada, niente, none at all!

So I thought something must be going wrong with the package variable that gets passed into the source parameter, somehow.  I’m not going into details on what I tried out in my attempt to get this working, but I can tell you that I started to get really irritated.  My colleague Koen Verbeeck (b|t) can confirm this because I called him over to my desk to help me think! (thanks btw!) Smile

After some further tinkering with the data flow, we had our smart moment of the day and decided to launch SQL Server Profiler to see what BIDS was sending to the server!  I’m not sure if you’re aware of this but BIDS is doing some metadata-related stuff when preparing queries.  As far as I can tell, it also tries to determine the parameter type by running the following query:

 set fmtonly on select Color from  dbo.DimProduct
--some really smart comment goes here where 1=2 set fmtonly off

When creating this statement, it seems to use the whole FROM clause of the original query, including any trailing comments.  It combines that with a SELECT statement that contains the field that gets filtered and it appends " where 1=2 set fmtonly off".

But alas, apparently it’s not aware that lines can be commented out by using a double dash.  So part of its generated statement is commented out.  What it should have done is used some CRLFs, especially in front of the WHERE clause.  But it didn’t.

So, as a result of that, FMTONLY remains on while the SELECT statement gets executed, resulting in zero records!

For those unfamiliar with the FMTONLY setting:

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

And I can actually confirm what I’m stating here by changing the query to the following:

set fmtonly off;
select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

28 records down the pipe!

We've got data!

But this hack is a little too dirty to put in production.  So what else can we do?  Well, use block-style comments instead and we won’t face the issue!

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
/* some even smarter comment goes here */
where Color = ?

So, as I mentioned at the start of the post, this behavior can be reproduced using SSIS versions prior to 2012.  What about 2012 then?  Here’s the result of the Data Flow using the first query mentioned above:

SSIS 2012: we've got data, even with the "faulty" query!

Alright, that works better!  Now let’s use Profiler to check what’s going on here.  This is the first statement that gets executed:

exec [sys].sp_describe_undeclared_parameters N'select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = @P1'

Further down, I also see this one:

exec [sys].sp_describe_first_result_set N'select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = @P1',N'@P1 nvarchar(15)',1

It is using an entirely different approach, no longer using the FMTONLY setting!  Hang on, this rings a bell!  Look what the BOL page for SET FMTONLY (2012 version) specifies:

Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).

Cool stuff!

Conclusion

If you’re not on SQL Server 2012 yet, be careful with comments in OLE DB Sources in the SSIS Data Flow!  Ow, and get the SQL Server Profiler off its dusty shelf now and then!

Have fun!

Valentino.

Share

Tags: , , , ,

Here’s a nice error message for a Friday afternoon:

image

Ouch, catastrophic failure!  Hang on, “catastrophic’”, really??  My computer didn’t explode, I’m still alive, I can even click the OK button to make the catastrophe go away.  Okay, what I was trying to achieve wasn’t possible for some reason, but to call it catastrophic is a little exaggerated.

But I guess it beats the useless “an unexpected error has occurred” message any time.  Because, an error, isn’t that by definition unexpected?  Unless you have an application with a button called Generate Error!

I had the pleasure of encountering this message while attempting to add a new Analysis Services connection to my SSIS project.  For some reason, probably a DLL that didn’t load as usual, not all controls were available and when I specified the server name, I got that message after clicking the OK button.

Here’s another variation, received through another button on that same screen:

image

So now it’s catastrophic and unexpected, sounds more like it! Smile We even got the error number: 0x8000FFFF, well of course!

Most important: I was able to “fix” this issue by restarting my BIDS (2008).

Have fun!

Valentino.

Share

Tags: , ,

I’ve been a user of the Microsoft Connect site for several years now.  A regular user who now and then casts an Up vote or clicks the “I can reproduce it!” link.  Not as a content poster.  Well, I did post some comments in the past, but I never filed an issue or suggestion.

So, a couple of weeks ago I decided to post my first bug to Microsoft Connect, an issue which I had discovered in SQL Server 2012 RC0.  Because this process took me more time than I’d expected, I‘m now blogging about my experience.

The hardest part about the whole experience was finding the actual page that needs to be used to enter and submit the content.

Submitting Content To Microsoft Connect

Are you in for a ride on the Connect site?  Here we go!

Step 1: Search For Your Product on The Connect Home Page

Enter a search term on Microsoft Connect to locate your favorite product

On the Home page, enter a search term with which you can locate your favorite product.  If you’re not signed in yet, do that first.

Step 2: Click To Participate

On the search results, click the Participate link next to your favorite product.

Once the search engine has produced the output, locate your favorite product and click the Participate action next to it.

Step 3: Submit Product Feedback

Click Submit Product Feedback to submit product feedback.

After clicking Participate, the following overwhelming screen appears.  The link that you’re looking for is called Submit Product Feedback.

Step 4: The Feedback Center

After clicking Submit Product Feedback, you end up at the Feedback Center.

Do not use the Submit link, use Search first to check if your issue already exists.

Looking at the screen above, you’d think that the yellow Submit Feedback button can be used to open the page where you’d need to enter the bug details.  Well, it doesn’t…

You need to search for your issue first, to ensure it doesn’t exist already.

Step 5: The Submit Feedback Button Has Been Found, Eureka!

The Submit Feedback button on the Product Search Results screen.

After you’ve performed the search for your issue, you get to the following page above.  Finally, on that page you can click a real Submit Feedback button that will bring you to the Select Feedback Form page.

Step 6: Select Your Feedback Form Of Choice

Select SQL Server Bug Form to file a SQL Server issue, or SQL Server Suggestion Form for a suggestion.

On the Select Feedback Form page, select SQL Server Bug Form to file a SQL Server issue, or select SQL Server Suggestion Form to enter a suggestion.

Step 7: Fill Out The Textboxes

The "Submit A Bug" Form for SQL Server.

Finally, the page we’ve been trying to locate when we started the adventure, quest accomplished!  With this new knowledge, we can now skip directly to step 6 through the following link: Select Feedback Form

Disclaimer: please ensure that you’re not creating duplicate bugs or suggestions, but use whatever search engine your prefer to achieve that goal!

So, here’s my New Year’s present to all of you!  And remember: have fun!

Valentino.

References

Microsoft Connect

Feedback Center

Select Feedback Form

Share

Tags: , ,

Ever since I upgraded to SQL Server 2008 Service Pack 1 I noticed that the Management Studio was reporting incorrect version numbers when connected to Integration or Reporting Services.  This incorrect version number is located to the right of the server instance in the Object Explorer.

As usual, a picture says so much more than … :

Object Explorer showing wrong version numbers

As I have posted earlier, 10.0.2531 is the version number for SP1, while 10.0.1600 is the original RTM version number.

I never really spent time looking for an answer to this.  It was obviously a bug but I could live with it and someone else would probably already have filed it as being a bug.  So recently I came across a post by Phil Brammer that mentioned this issue.  This post got a comment from Matt Masson, a developer on the SSIS team.  Have a look at the comment but in short: the version numbers that are being shown in the Object Explorer are actually the version numbers of the service’s .exe file!  And SSMS is now showing the wrong number because these files didn’t get an update in SP1.

After a little search I found the bug report on Microsoft Connect, reported on March 11, 2009, by Dan English.  Its status is Fixed but it seems that it isn’t.  At least, looking at the comments, CU5 (Cumulative Update) for SQL Server 2008 SP1 is still showing the problem.  So I guess you could go over to the Connect page and click on that Yes button if you’re interested in seeing this fixed.  After all, it could be quite misleading to novice DB guys and gals…

On this same subject, there’s another interesting post by Adam W. Saxton, a member of the Microsoft SQL Server Escalation Services Team.  In this post he takes a closer look at the SQL Server 2008 Reporting Services version number after having installed CU2.

Conclusion: if you need to find out what version your server is running, do not rely on the version numbers that you see in the Object Explorer.  As Adam explained, one way is to look at the version numbers of the files that were included in the upgrade.  But that may a bit of an overkill.  My favorite way, assuming that all components of the SQL Server installation have been upgraded to the same version, is to use the following query:

SELECT @@VERSION;

 

On my machine that comes back with the following result:

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

And remember, have fun!

Share

Tags: , , , , , , , ,

I came across an issue when playing around with Report Builder 2.0.  I had created a report using an embedded data source.  Once I’d published the report to the report server, I couldn’t get it to run anymore.  Instead it gave me the following error:

This report cannot be run in report builder because it contains one or more embedded data sources with credential options that are not supported.  Instead of embedded data sources use shared data sources or save and view the report on the server.

Okay, no problem I thought, let’s just create a shared data source and switch to that one then.  So I opened up the Data Source Properties in Report Builder and selected the Use a shared connection or report model radio button.

Unfortunately, when running the report it threw me that same error?!  And when I open the Data Source properties again, my change was undone!  It was still using the embedded data source.

As far as I’m concerned that should be a bug.

The only way that I could switch my data source to a shared connection was by creating a new data source, which means you also need to move all datasets connected to the original data source.

Quick tip: if you first rename the original data source and datasets to something like srcMyDataset_OLD, you can give the correct name to the new one straightaway.

So I guess that’s another workaround on my list :-)

This issue was encountered while using Report Builder 2.0 (10.0.2531.0).  I tried to reproduce it using Report Builder 3.0 (10.50.1092.20 – that’s the version of the SQL Server 2008 R2 August CTP) and I couldn’t.  Which means it has been fixed.  Good on you Microsoft!

Share

Tags: , , , , , ,

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved