How SSIS and Other Tools Obtain Metadata

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Renato Buda » How SSIS and Other Tools Obtain Metadata

How SSIS and Other Tools Obtain Metadata

Rate Topic

Display Mode

Topic Options

Author

Message

Renato Buda-153382

Posted Saturday, November 01, 2008 9:58 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 139, Visits: 215

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

Post #595515

Carla Wilson-484785

Posted Monday, November 03, 2008 7:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:39 PM
Points: 1,355, Visits: 1,740

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!

Post #595819

Thomas Keller

Posted Monday, November 03, 2008 8:22 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 14, 2013 2:54 AM
Points: 23, Visits: 97

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.

Post #595849

cstrause

Posted Monday, November 03, 2008 9:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 11:33 AM
Points: 4, Visits: 43

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.

Post #595896

Anipaul

Posted Monday, November 03, 2008 6:43 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:35 AM
Points: 4,789, Visits: 1,336

Good article...

Post #596246

Andy Hilliard

Posted Wednesday, November 05, 2008 6:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 17, 2011 10:15 PM
Points: 40, Visits: 30

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

Post #597282

Thomas Keller

Posted Wednesday, November 05, 2008 4:27 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, January 14, 2013 2:54 AM
Points: 23, Visits: 97

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

Post #597764

Renato Buda-153382

Posted Friday, November 07, 2008 3:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 139, Visits: 215

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

Post #598760

mishaluba

Posted Saturday, November 08, 2008 9:45 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525, Visits: 617

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

Post #599515

Mahmood.BilalB

Posted Thursday, June 14, 2012 5:27 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:56 PM
Points: 328, Visits: 125

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

Post #1316247

« Prev Topic | Next Topic »

Permissions