How SSIS and Other Tools Obtain Metadata

Question

How SSIS and Other Tools Obtain Metadata

R M Buda

SSCrazy

Points: 2225
More actions
November 1, 2008 at 9:58 pm

#200720

Comments posted to this topic are about the item How SSIS and Other Tools Obtain Metadata

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 1

Thanks for the heads-up on the potential metadata problem. I haven't stumbled on this problem yet, but it would have only been a matter of time. Clever work-around!

Thomas Keller Ten Centuries Points: 1246 More actions · Answer 2

Good to know, and the reverse will also be useful! When you have to use a front end tool such as Excel or Access, which does not use FMTONLY, temporarily adding it to the procedure will allow building a query without waiting for full execution.

cstrause Grasshopper Points: 24 More actions · Answer 3

This is also useful to know when using the wizard in Visual Studio 2005 for creating Typed data Sets.

The ugly work around is to create a table and select at the top of the stored procedure just to satisfy the tool, then comment it out and un-comment the realy code after that.

This looks like it would be a better way. And its something I always meant to trace using profiler and never had time. Thanks.

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Anipaul

SSC-Insane

Points: 24681

November 3, 2008 at 6:43 pm

#893363

Good article...

Andy Hilliard Old Hand Points: 344 More actions · Answer 5

Renato, this is a very useful and interesting article. Can you tell us how this code from your example works? It looks like you are evaluating the status of the FMTONLY option by doing a count from sys.filegroups. I don't understand why this would return the session status of FMTONLY.

CREATE PROCEDURE dbo.ExportGraphicIndex

AS

BEGIN

DECLARE @FmtOnlyTest int; -- If this is null then FMTONLY was ON

SELECT @FmtOnlyTest = count(*) FROM sys.filegroups;

IF @FmtOnlyTest IS NULL -- FMTONLY was ON

SET FMTONLY OFF;

Andy Hilliard
Owl Creek Consulting[/url]

Thomas Keller Ten Centuries Points: 1246 More actions · Answer 6

If FMTONLY is ON, all queries return zero rows, so a variable assignment in any select has no effect, and the variable keeps its previous value, which is NULL if it was never initialized. Here is another example which works in SQL2000, but at any rate you can query any table you have permission on.

DECLARE @FmtOnlyTest int

set @FmtOnlyTest = 0

SELECT count(*) AS C FROM dbo.sysobjects

SELECT @FmtOnlyTest = count(*) FROM dbo.sysobjects

IF ( @FmtOnlyTest = 0 ) BEGIN

SET FMTONLY OFF

PRINT 'NOW OFF'

END

R M Buda SSCrazy Points: 2225 More actions · Answer 7

Hi Andy,

Thomas Keller is absolutely right and explained it very well.

I chose SELECT COUNT(*) because that aggregate function will normally always return a row with a non-null value. That means that I could be sure that if the variable is null, the query did not return any rows, and FMTONLY was set.

The code is improved if @SetFmtOnly is explicitly initialised (as in Thomas's example)

Renato Buda

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 8

Thank you, this is excellent tip. Where were you 6 months ago when I spent days working around this limitation??!!! :w00t:

Mahmood.BilalB Ten Centuries Points: 1303 More actions · Answer 9

Excellent topic!

I just want to add one thing. If you use table variable or tmp table use all column names instead of just * in select statement.

Forexample:

Does not Work

select * from BLA_BLA

Use

select col1, col1,... from BLA_BLA

t.ovod-everett Default port Points: 1487 More actions · Answer 10

First off, I like this approach more than most of the alternatives, and the trick of using a query to interrogate the current status of FMTONLY is very clever. Having decided I liked it, I wanted to try to clean it up a little. I've been testing in SQL Server 2008 R2, and it appears that you can simplify this:

SELECT @FmtOnlyTest = 1;

No need to query a table. Also, in 2008 you can simplify it to:

DECLARE @NullIfFmtOnly int = (SELECT 1);

I prefer the variable name @NullIfFmtOnly because it makes the code a little more self explanatory.

Thomas Keller Ten Centuries Points: 1246 More actions · Answer 11

@t.ovod-everett good catch, selecting a constant works as well as selecting a count from a table (I checked it in SQL 2005).

R M Buda SSCrazy Points: 2225 More actions · Answer 12

@t.ovod-everett : good one.

I tested on SQL Server 2012 and the following works great

DECLARE @NullIfFmtOnly int = (SELECT 1);

but using a constant does not work i.e.

SET FMTONLY ON

SET @NullIfFmtOnly=1

gets the value of 1 even when SET FMTONLY ON.

t.ovod-everett Default port Points: 1487 More actions · Answer 13

Another hack I came up with involves using this approach to work with SELECT INTO #Temp (if you want to avoid the maintenance overhead of maintaining the CREATE TABLE statement separately).

DECLARE @NullIfFmtOnly int = (SELECT 1);

IF @NullIfFmtOnly IS NULL SET FMTONLY OFF;

DECLARE @TopPct int = ISNULL(@NullIfFmtOnly, 0) * 100;

SELECT TOP (@TopPct) PERCENT

Col1, Col2

INTO #Temp

FROM YourTable

OPTION (RECOMPILE);

IF @NullIfFmtOnly IS NULL SET FMTONLY ON;

I found that if I didn't use RECOMPILE, the query plan for the FMTONLY scenario still pulled all the records even if it didn't insert them into the temp table. When I used RECOMPILE (at least in 2008 R2), it intelligently used Constant Scan instead of accessing the underlying tables. One does pay the price of recompiling the query on every execution, but I'm guessing if you're going to all this trouble with Temp tables in SSIS that query compilation is not a significant cost.

Thomas Keller Ten Centuries Points: 1246 More actions · Answer 14

@Renato Buda-153382: It didn't work for you because you did SET instead of SELECT. That's why I was able to initialize my variable to 0 with SET, then check if SELECT changed it to 1.