For SQL Server 200, the master database holds the information for the metadata for databases on that particular server instance.
If you have access to this database, there should be some views pre-made by SQL Server that should show some of this information. Teddy ----- Original Message ----- From: Dusty Hale To: [email protected] Sent: Tuesday, June 05, 2007 4:41 PM Subject: RE: [ACFUG Discuss] OT: SQL help I'm afraid I don't have any experience using metadata. I looked at the metadata folder icon in enterprise manager but nothing obvious to me so I guess I would need to study on that a bit. For now I'm doing it one table at a time through the GUI unless someone could advise further. Thanks for the suggestion and I'll try to look into how to use metadata. Dusty ------------------------------------------------------------------------------ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dean H. Saxe Sent: Tuesday, June 05, 2007 4:23 PM To: [email protected] Subject: Re: [ACFUG Discuss] OT: SQL help Fair enough... Why not use the metadata in the DB to identify all of the FKs and then write a script to drop them? -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] "[U]nconstitutional behavior by the authorities is constrained only by the peoples' willingness to contest them" --John Perry Barlow On Jun 5, 2007, at 4:16 PM, Dusty Hale wrote: Normally I would agree ... but this is a judgment call for other reasons. Also this is not the real copy of the database and the data being served will not be affected by it. ---------------------------------------------------------------------------- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dean H. Saxe Sent: Tuesday, June 05, 2007 4:02 PM To: [email protected] Subject: Re: [ACFUG Discuss] OT: SQL help So you'd rather have no foreign key relationships and let the DB get all out of whack rather than figure out the DTS issues? I think you're looking at the wrong problem, I'd spend my time figuring out the DTS and making sure it works correctly. -dhs Dean H. Saxe, CISSP, CEH [EMAIL PROTECTED] "If liberty means anything at all, it means the right to tell people what they do not want to hear." -- George Orwell, 1945 On Jun 5, 2007, at 4:03 PM, Dusty Hale wrote: Speaking of OT SQL help ... If there are any database gurus out there that could throw me a bone and send me some sql code that would detect and remove foreign key relationships from tables (sql server 2000). If you're thinking "why would I want to do this?" it is because this database gets updated everyday with DTS and the if anything is out of whack of course it causes DTS errors when moving the data. I know I can remove them one at a time with enterprise manager but my remote connection seems to be incredibly slow doing it. Thanks, Dusty -------------------------------------------------------------------------- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Morton Sent: Tuesday, June 05, 2007 2:16 PM To: [email protected] Subject: RE: [ACFUG Discuss] OT: SQL help Teddy, Any idea why do I get an error in Query Analyzer : SQL Statement SELECT * FROM dbo.ORDER_ITEMS oi LEFT OUTER JOIN dbo.AP_translation tr ON tr.option_id IN (select convert(int,Value) from dbo.Split(oi.OptionIDs, ',')) WHERE (oi.Order_ID = 12387) Error received: Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '.'. Paul -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Teddy R Payne Sent: Tuesday, June 05, 2007 12:10 PM To: [email protected] Subject: Re: [ACFUG Discuss] OT: SQL help You use it in an adhoc query in query analyzer or SSMA. I have not tried it as an ad hoc query in CF as I myself tend not to use cfquery wherever I can. Teddy ----- Original Message ----- From: Paul Morton To: [email protected] Sent: Tuesday, June 05, 2007 1:58 PM Subject: RE: [ACFUG Discuss] OT: SQL help Without creating a SP, can I use this in a select statement? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Teddy R Payne Sent: Tuesday, June 05, 2007 11:45 AM To: [email protected] Subject: Re: [ACFUG Discuss] OT: SQL help Paul, There is already an open source function in SQL Server to do this for you: http://www.4guysfromrolla.com/webtech/031004-1.shtml They explain how to put it together. Here is the function creation for how I put it together from their explanation: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Split]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[Split] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE FUNCTION dbo.Split ( @List nvarchar(4000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(250) ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue ( value ) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue ( Value ) Select Value = ltrim(rtrim(@List)) Return END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO It is implemented as such: CREATE PROCEDURE getEmployeesByID(@employeeList nvarchar(1000)) AS Select employeeId, -- integer (int) FirstName, LastName from #myTable Where employeeId IN (Select convert(int,Value) from dbo.Split(@employeeList,',')) Cheers, Teddy ----- Original Message ----- From: Paul Morton To: [email protected] Sent: Tuesday, June 05, 2007 1:36 PM Subject: [ACFUG Discuss] OT: SQL help I apologize for the OT post, but I'm getting frustrated. I am attempting to use a UDF to split a varchar (comma delimited list of ints) in an IN clause in a SQL statement, and I'm not able to get the SQL statement to pass a Table field to the UDF. I'm hoping someone sees a glaring error, or can tell me I can't do this. MS SQL 2000 ________________________________________________________________ CREATE FUNCTION dbo.Split ( @List nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return END ________________________________________________________________________________ SQL Statement SELECT * FROM dbo.ORDER_ITEMS oi LEFT OUTER JOIN dbo.AP_translation tr ON tr.option_id IN (select convert(int,Value) from dbo.Split(oi.OptionIDs, ',')) WHERE (oi.Order_ID = 12387) _____________________________________________________________________________ Error received: Server: Msg 170, Level 15, State 1, Line 4 Line 4: Incorrect syntax near '.'. OptionIDs is a varchar(255) field with a comma delimited list of integers. It doesn't like the oi.OptionIDs. If I put in a list like '0,1,2' in it's place, the query works. Can I not pass a field into a UDF? Is there another way to skin this cat? Paul ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor - Figleaf Software To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
