Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How SSIS and Other Tools Obtain Metadata Expand / Collapse
Author
Message
Posted Saturday, November 1, 2008 9:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:02 AM
Points: 146, Visits: 238
Comments posted to this topic are about the item How SSIS and Other Tools Obtain Metadata
Post #595515
Posted Monday, November 3, 2008 7:54 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:34 AM
Points: 1,566, Visits: 1,851
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
Posted Monday, November 3, 2008 8:22 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 AM
Points: 32, Visits: 121
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
Posted Monday, November 3, 2008 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 5, 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
Posted Monday, November 3, 2008 6:43 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 5,353, Visits: 1,389
Good article...


Post #596246
Posted Wednesday, November 5, 2008 6:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 5:12 PM
Points: 40, Visits: 32
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
Posted Wednesday, November 5, 2008 4:27 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 AM
Points: 32, Visits: 121
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
Posted Friday, November 7, 2008 3:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:02 AM
Points: 146, Visits: 238
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
Posted Saturday, November 8, 2008 9:45 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:45 AM
Points: 536, Visits: 758
Thank you, this is excellent tip. Where were you 6 months ago when I spent days working around this limitation??!!!


Post #599515
Posted Thursday, June 14, 2012 5:27 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:37 PM
Points: 627, Visits: 137
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 »

Add to briefcase

Permissions Expand / Collapse