SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Renato Buda / How SSIS and Other Tools Obtain Metadata / Latest Posts

RE: How SSIS and Other Tools Obtain Metadata

Mahmood.BilalB — Thu, 14 Jun 2012 17:27:53 GMT

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 Workselect * from BLA_BLAUse select col1, col1,... from BLA_BLA

RE: How SSIS and Other Tools Obtain Metadata

mishaluba — Sat, 08 Nov 2008 21:45:05 GMT

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

RE: How SSIS and Other Tools Obtain Metadata

Renato Buda-153382 — Fri, 07 Nov 2008 03:21:53 GMT

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

RE: How SSIS and Other Tools Obtain Metadata

Thomas Keller — Wed, 05 Nov 2008 16:27:17 GMT

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 intset @FmtOnlyTest = 0SELECT count(*) AS C FROM dbo.sysobjectsSELECT @FmtOnlyTest = count(*) FROM dbo.sysobjectsIF ( @FmtOnlyTest = 0 ) BEGIN SET FMTONLY OFF PRINT 'NOW OFF'END

RE: How SSIS and Other Tools Obtain Metadata

Andy Hilliard — Wed, 05 Nov 2008 06:32:18 GMT

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.ExportGraphicIndexASBEGIN DECLARE @FmtOnlyTest int; -- If this is null then FMTONLY was ON[b]SELECT @FmtOnlyTest = count(*) FROM sys.filegroups;[/b] IF @FmtOnlyTest IS NULL -- FMTONLY was ON SET FMTONLY OFF;

RE: How SSIS and Other Tools Obtain Metadata

Anipaul — Mon, 03 Nov 2008 18:43:47 GMT

Good article...

RE: How SSIS and Other Tools Obtain Metadata

cstrause — Mon, 03 Nov 2008 09:18:37 GMT

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.

RE: How SSIS and Other Tools Obtain Metadata

Thomas Keller — Mon, 03 Nov 2008 08:22:09 GMT

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.

RE: How SSIS and Other Tools Obtain Metadata

Carla Wilson-484785 — Mon, 03 Nov 2008 07:54:53 GMT

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!

How SSIS and Other Tools Obtain Metadata

Renato Buda-153382 — Sat, 01 Nov 2008 21:58:36 GMT

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