Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Optimise

Author  Topic 

kingjes
Starting Member

7 Posts
Posted - 2006-06-02 : 15:02:15
Hello All,
I have a query that I could use some help with. I would like to see how I could make this query more optimised. I am not hte best at Joins so if someone could help me with this that would be great. Right now its taking about 24-30 secs to run which is a no go. Im wondering if some kind of join would work better?
thank you much
-jes

SELECT recipes_signed_up_for.user_id,
recipes_signed_up_for.recipe_id,
recipes_signed_up_for.use_in_lesson_plan AS use_in_lesson_plan,
recipes_signed_up_for.attached_lesson_plan AS attached_lesson_plan,
recipes_signed_up_for.participate,
recipes_signed_up_for.authorized AS authorized,
recipes_signed_up_for.date_signed_up_for AS date_signed_up_for,
users.user_id,
users.f_name AS f_name,
users.l_name AS l_name,
users.email_address AS email_address,
users.primary_phone AS primary_phone,
recipes.recipe_name AS recipe_name,
recipes.recipe_id AS recipe_id,
recipes.number_served AS number_served,
recipes.last_day_to_sign_up_for AS last_day_to_sign_up_for,
recipes.recipe_instructions AS recipe_instructions,
recipes.active_recipe,
recipe_ingredients.recipe_ingredient_id,
recipe_ingredients.recipe_id,
recipe_ingredients.ingredient_id,
recipe_ingredients.ingredient_quantity AS ingredient_quantity,
recipe_ingredients.ingredient_unit,
recipe_ingredients.active_ingredient AS active_ingredient,
ingredients.ingredient_id,
ingredients.ingredient AS ingredient_name,
recipes_signed_up_for_ingredients_needed.ingredient_id,
recipes_signed_up_for_ingredients_needed.ingredient_needed AS ingredient_needed
FROM recipes, recipe_ingredients, ingredients, recipes_signed_up_for_ingredients_needed, recipes_signed_up_for, users, locations, regions
WHERE recipes.recipe_id = recipe_ingredients.recipe_id
AND recipe_ingredients.ingredient_id = ingredients.ingredient_id
AND recipes_signed_up_for_ingredients_needed.user_id = #url.user_id#
AND recipes_signed_up_for_ingredients_needed.ingredient_id = recipe_ingredients.ingredient_id
AND recipes_signed_up_for_ingredients_needed.ingredient_needed = 1
AND recipes.recipe_id = recipes_signed_up_for.recipe_id
AND recipes_signed_up_for.user_id = users.user_id
AND recipes_signed_up_for.user_id = #URL.user_id#
AND recipes_signed_up_for.participate = 1
AND locations.region_id = regions.region_id
AND recipes_signed_up_for.recipe_id IN (SELECT recipe_id
FROM recipes
WHERE active_recipe = 1)
ORDER BY recipes.recipe_name

kingjes
Starting Member

7 Posts
Posted - 2006-06-02 : 15:12:44
By the way, if you need a copy of the dB here is a link to dl the zip..

http://www.mercedspca.com/temp/mydatabase.zip

this is just a copy and anything important has been removed.. thanx for your help.. this is a freeby website for the SPCA and I am kinda stuck on it..
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2006-06-02 : 15:13:24
Well we would need more to go, like the indexes, table DDL, sample data, ect

Read the hint link in my sig and post what it asks for.

Also I would reccomend that you use ANSI Syntax for the Joins



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

kingjes
Starting Member

7 Posts
Posted - 2006-06-02 : 15:19:21
I have included a link to the dB itself.. I hope this will be helpful!
-jes
Go to Top of Page

kingjes
Starting Member

7 Posts
Posted - 2006-06-02 : 15:26:26
Is this what you needed?


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_addtosourcecontrol]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_addtosourcecontrol]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_addtosourcecontrol_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_addtosourcecontrol_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_adduserobject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_adduserobject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_adduserobject_vcs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_adduserobject_vcs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_checkinobject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_checkinobject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_checkinobject_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_checkinobject_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_checkoutobject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_checkoutobject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_checkoutobject_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_checkoutobject_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_displayoaerror]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_displayoaerror]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_displayoaerror_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_displayoaerror_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_droppropertiesbyid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_droppropertiesbyid]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_dropuserobjectbyid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_dropuserobjectbyid]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_generateansiname]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_generateansiname]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_getobjwithprop]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_getobjwithprop]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_getobjwithprop_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_getobjwithprop_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_getpropertiesbyid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_getpropertiesbyid]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_getpropertiesbyid_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_getpropertiesbyid_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_getpropertiesbyid_vcs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_getpropertiesbyid_vcs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_getpropertiesbyid_vcs_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_getpropertiesbyid_vcs_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_isundersourcecontrol]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_isundersourcecontrol]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_isundersourcecontrol_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_isundersourcecontrol_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_removefromsourcecontrol]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_removefromsourcecontrol]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_setpropertybyid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_setpropertybyid]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_setpropertybyid_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_setpropertybyid_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_validateloginparams]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_validateloginparams]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_validateloginparams_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_validateloginparams_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_vcsenabled]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_vcsenabled]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_verstamp006]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_verstamp006]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_whocheckedout]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_whocheckedout]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_whocheckedout_u]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_whocheckedout_u]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BACKUP_units]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BACKUP_units]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Content]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Content]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Current_News]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Current_News]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Documents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Documents]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Editor_Info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Editor_Info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FeedBack]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FeedBack]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Message_Board]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Message_Board]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Policies]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Policies]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sections]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Sections]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[approved_ingredients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[approved_ingredients]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[curriculum]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[curriculum]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dtproperties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dtproperties]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[grades]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[grades]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotm]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hotm]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotm_categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hotm_categories]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ingredients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ingredients]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[job_titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[job_titles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[locations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[locations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[meetings]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[meetings]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[recipe_ingredients]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[recipe_ingredients]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[recipes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[recipes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[recipes_signed_up_for]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[recipes_signed_up_for]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[recipes_signed_up_for_ingredients_needed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[recipes_signed_up_for_ingredients_needed]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[regions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[regions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[site_access_levels]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[site_access_levels]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[units]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[units]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[users]
GO

CREATE TABLE [dbo].[BACKUP_units] (
[unit_id] [int] NOT NULL ,
[unit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_unit] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Content] (
[Page_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Page_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Page_Content] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Created] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Update_Date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Editor_ID] [int] NULL ,
[Section_ID] [int] NULL ,
[Default_Page] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Current_News] (
[Cur_News_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Cur_News_Date_Created] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cur_News_Short_Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cur_News_Short_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cur_News_Content] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Cur_News_Editor_ID] [int] NULL ,
[Section_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Documents] (
[Doc_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Doc_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doc_Desc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doc_Format] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doc_Path] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doc_File_Size] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doc_Create_Date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doc_Edit_Date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Doc_Editor_ID] [int] NULL ,
[Section_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Editor_Info] (
[Editor_ID] [int] IDENTITY (1, 1) NOT NULL ,
[F_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email_Addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[User_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Access_Level] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[FeedBack] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[F_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[L_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email_Addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FeedBack_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Submitted] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Section_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Message_Board] (
[Msg_Brd_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Msg_Brd_Date_Created] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Msg_Brd_Short_Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Msg_Brd_Short_Desc] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Msg_Brd_Long_Title] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Msg_Brd_Content] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Msg_Brd_Editor_ID] [int] NULL ,
[Section_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Policies] (
[Pol_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Pol_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pol_Desc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pol_Format] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pol_Path] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pol_File_Size] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pol_Create_Date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pol_Edit_Date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pol_Editor_ID] [int] NULL ,
[Section_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Sections] (
[Section_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Section_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Section_Desc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[approved_ingredients] (
[Approved_ingredients_id] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [int] NULL ,
[region_id] [int] NULL ,
[recipe_id] [int] NULL ,
[ingredient_id] [int] NULL ,
[ingredient_quantity] [int] NULL ,
[ingredient_unit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[curriculum] (
[curriculum_id] [int] IDENTITY (1, 1) NOT NULL ,
[date_uploaded] [smalldatetime] NULL ,
[curriculum_title] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_desc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_grade] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment6] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment7] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment8] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment9] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[curriculum_attachment10] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_curriculum] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[dtproperties] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[objectid] [int] NULL ,
[property] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uvalue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lvalue] [image] NULL ,
[version] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[grades] (
[grade_id] [int] IDENTITY (1, 1) NOT NULL ,
[grade] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[hotm] (
[hotm_id] [int] IDENTITY (1, 1) NOT NULL ,
[date_uploaded] [smalldatetime] NULL ,
[hotm_month] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hotm_year] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hotm_category] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hotm_title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hotm_notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[attached_file] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_hotm] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[hotm_categories] (
[hotm_cat_id] [int] IDENTITY (1, 1) NOT NULL ,
[hotm_category] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ingredients] (
[ingredient_id] [int] IDENTITY (1, 1) NOT NULL ,
[ingredient] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_ingredient] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[job_titles] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[job_title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_job_title] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[locations] (
[location_id] [int] IDENTITY (1, 1) NOT NULL ,
[location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[region_id] [int] NULL ,
[active_location] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[meetings] (
[meeting_id] [int] IDENTITY (1, 1) NOT NULL ,
[date_uploaded] [smalldatetime] NULL ,
[next_meeting_date] [smalldatetime] NULL ,
[meeting_title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[meeting_desc] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[meeting_agenda] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[meeting_mins] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[meeting_contacts] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_meeting] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[recipe_ingredients] (
[recipe_ingredient_id] [int] IDENTITY (1, 1) NOT NULL ,
[recipe_id] [int] NULL ,
[ingredient_id] [int] NULL ,
[ingredient_quantity] [float] NULL ,
[ingredient_unit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_ingredient] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[recipes] (
[recipe_id] [int] IDENTITY (1, 1) NOT NULL ,
[recipe_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[number_served] [int] NULL ,
[last_day_to_sign_up_for] [smalldatetime] NULL ,
[recipe_instructions] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[taste_test] [bit] NOT NULL ,
[active_recipe] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[recipes_signed_up_for] (
[signed_up_for_id] [int] IDENTITY (1, 1) NOT NULL ,
[date_signed_up_for] [smalldatetime] NULL ,
[user_id] [int] NULL ,
[recipe_id] [int] NULL ,
[use_in_lesson_plan] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[attached_lesson_plan] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[participate] [bit] NOT NULL ,
[authorized] [bit] NOT NULL ,
[authorized_date] [smalldatetime] NULL ,
[authorized_by_user_id] [int] NULL ,
[authorized_notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shopping_compleate] [bit] NOT NULL ,
[shopping_compleate_date] [smalldatetime] NULL ,
[receipts_received] [bit] NOT NULL ,
[delivered_to_location] [bit] NOT NULL ,
[receipts_turn_in] [bit] NOT NULL ,
[shopper_notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[recipes_signed_up_for_ingredients_needed] (
[ingredients_needed_id] [int] IDENTITY (1, 1) NOT NULL ,
[user_id] [int] NULL ,
[recipe_id] [int] NULL ,
[ingredient_id] [int] NULL ,
[ingredient_needed] [bit] NOT NULL ,
[approved_ingredient] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[regions] (
[region_id] [int] IDENTITY (1, 1) NOT NULL ,
[region] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delivery_person_user_id] [int] NULL ,
[save_mart_card_digits] [int] NULL ,
[active_region] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[site_access_levels] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[site_access_level] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[units] (
[unit_id] [int] IDENTITY (1, 1) NOT NULL ,
[unit] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[active_unit] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
[user_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[l_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email_address] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[primary_phone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[job_title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[salary_rate] [money] NULL ,
[site_access_level] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[region_id] [int] NULL ,
[location_id] [int] NULL ,
[grade] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[class_room] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[number_students] [int] NULL ,
[active_user] [bit] NOT NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_addtosourcecontrol
@vchSourceSafeINI varchar(255) = '',
@vchProjectName varchar(255) ='',
@vchComment varchar(255) ='',
@vchLoginName varchar(255) ='',
@vchPassword varchar(255) =''

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @vchDatabaseName varchar(255)
select @vchDatabaseName = db_name()

declare @iReturnValue int
select @iReturnValue = 0

declare @iPropertyObjectId int
declare @vchParentId varchar(255)

declare @iObjectCount int
select @iObjectCount = 0

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError


/* Create Project in SS */
exec @iReturn = sp_OAMethod @iObjectId,
'AddProjectToSourceSafe',
NULL,
@vchSourceSafeINI,
@vchProjectName output,
@@SERVERNAME,
@vchDatabaseName,
@vchLoginName,
@vchPassword,
@vchComment


if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

/* Set Database Properties */

begin tran SetProperties

/* add high level object */

exec @iPropertyObjectId = dbo.dt_adduserobject_vcs 'VCSProjectID'

select @vchParentId = CONVERT(varchar(255),@iPropertyObjectId)

exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSProjectID', @vchParentId , NULL
exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSProject' , @vchProjectName , NULL
exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSourceSafeINI' , @vchSourceSafeINI , NULL
exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSQLServer', @@SERVERNAME, NULL
exec dbo.dt_setpropertybyid @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName, NULL

if @@error <> 0 GOTO E_General_Error

commit tran SetProperties

declare cursorProcNames cursor for
select convert(varchar(255), name) from sysobjects where type = 'P' and name not like 'dt_%'
open cursorProcNames

while 1 = 1
begin
declare @vchProcName varchar(255)
fetch next from cursorProcNames into @vchProcName
if @@fetch_status <> 0
break

select colid, text into #ProcLines
from syscomments
where id = object_id(@vchProcName)
order by colid

declare @iCurProcLine int
declare @iProcLines int
select @iCurProcLine = 1
select @iProcLines = (select count(*) from #ProcLines)
while @iCurProcLine <= @iProcLines
begin
declare @pos int
select @pos = 1
declare @iCurLineSize int
select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine))
while @pos <= @iCurLineSize
begin
declare @vchProcLinePiece varchar(255)
select @vchProcLinePiece = convert(varchar(255),
substring((select text from #ProcLines where colid = @iCurProcLine),
@pos, 255 ))
exec @iReturn = sp_OAMethod @iStreamObjectId, 'AddStream', @iReturnValue OUT, @vchProcLinePiece
if @iReturn <> 0 GOTO E_OAError
select @pos = @pos + 255
end
select @iCurProcLine = @iCurProcLine + 1
end
drop table #ProcLines

exec @iReturn = sp_OAMethod @iObjectId,
'CheckIn_StoredProcedure',
NULL,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sServerName = @@SERVERNAME,
@sDatabaseName = @vchDatabaseName,
@sObjectName = @vchProcName,
@sComment = @vchComment,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword,
@iVCSFlags = 0,
@iActionFlag = 0,
@sStream = ''

if @iReturn = 0 select @iObjectCount = @iObjectCount + 1

end

CleanUp:
close cursorProcNames
deallocate cursorProcNames
select @vchProjectName
select @iObjectCount
return

E_General_Error:
/* this is an all or nothing. No specific error messages */
goto CleanUp

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
goto CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_addtosourcecontrol_u
@vchSourceSafeINI nvarchar(255) = '',
@vchProjectName nvarchar(255) ='',
@vchComment nvarchar(255) ='',
@vchLoginName nvarchar(255) ='',
@vchPassword nvarchar(255) =''

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @vchDatabaseName varchar(255)
select @vchDatabaseName = db_name()

declare @iReturnValue int
select @iReturnValue = 0

declare @iPropertyObjectId int
declare @vchParentId nvarchar(255)

declare @iObjectCount int
select @iObjectCount = 0

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError


/* Create Project in SS */
exec @iReturn = sp_OAMethod @iObjectId,
'AddProjectToSourceSafe',
NULL,
@vchSourceSafeINI,
@vchProjectName output,
@@SERVERNAME,
@vchDatabaseName,
@vchLoginName,
@vchPassword,
@vchComment


if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

/* Set Database Properties */

begin tran SetProperties

/* add high level object */

exec @iPropertyObjectId = dbo.dt_adduserobject_vcs 'VCSProjectID'

select @vchParentId = CONVERT(nvarchar(255),@iPropertyObjectId)

exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSProjectID', @vchParentId , NULL
exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSProject' , @vchProjectName , NULL
exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSourceSafeINI' , @vchSourceSafeINI , NULL
exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSQLServer', @@SERVERNAME, NULL
exec dbo.dt_setpropertybyid_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName, NULL

if @@error <> 0 GOTO E_General_Error

commit tran SetProperties

declare cursorProcNames cursor for
select convert(nvarchar(255), name) from sysobjects where type = N'P' and name not like N'dt_%'
open cursorProcNames

while 1 = 1
begin
declare @vchProcName nvarchar(255)
fetch next from cursorProcNames into @vchProcName
if @@fetch_status <> 0
break

select colid, text into #ProcLines
from syscomments
where id = object_id(@vchProcName)
order by colid

declare @iCurProcLine int
declare @iProcLines int
select @iCurProcLine = 1
select @iProcLines = (select count(*) from #ProcLines)
while @iCurProcLine <= @iProcLines
begin
declare @pos int
select @pos = 1
declare @iCurLineSize int
select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine))
while @pos <= @iCurLineSize
begin
declare @vchProcLinePiece nvarchar(255)
select @vchProcLinePiece = convert(nvarchar(255),
substring((select text from #ProcLines where colid = @iCurProcLine),
@pos, 255 ))
exec @iReturn = sp_OAMethod @iStreamObjectId, N'AddStream', @iReturnValue OUT, @vchProcLinePiece
if @iReturn <> 0 GOTO E_OAError
select @pos = @pos + 255
end
select @iCurProcLine = @iCurProcLine + 1
end
drop table #ProcLines

exec @iReturn = sp_OAMethod @iObjectId,
'CheckIn_StoredProcedure',
NULL,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sServerName = @@SERVERNAME,
@sDatabaseName = @vchDatabaseName,
@sObjectName = @vchProcName,
@sComment = @vchComment,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword,
@iVCSFlags = 0,
@iActionFlag = 0,
@sStream = ''

if @iReturn = 0 select @iObjectCount = @iObjectCount + 1

end

CleanUp:
close cursorProcNames
deallocate cursorProcNames
select @vchProjectName
select @iObjectCount
return

E_General_Error:
/* this is an all or nothing. No specific error messages */
goto CleanUp

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
goto CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Add an object to the dtproperties table
*/
create procedure dbo.dt_adduserobject
as
set nocount on
/*
** Create the user object if it does not exist already
*/
begin transaction
insert dbo.dtproperties (property) VALUES ('DtgSchemaOBJECT')
update dbo.dtproperties set objectid=@@identity
where id=@@identity and property='DtgSchemaOBJECT'
commit
return @@identity

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create procedure dbo.dt_adduserobject_vcs
@vchProperty varchar(64)

as

set nocount on

declare @iReturn int
/*
** Create the user object if it does not exist already
*/
begin transaction
select @iReturn = objectid from dbo.dtproperties where property = @vchProperty
if @iReturn IS NULL
begin
insert dbo.dtproperties (property) VALUES (@vchProperty)
update dbo.dtproperties set objectid=@@identity
where id=@@identity and property=@vchProperty
select @iReturn = @@identity
end
commit
return @iReturn



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_checkinobject
@chObjectType char(4),
@vchObjectName varchar(255),
@vchComment varchar(255)='',
@vchLoginName varchar(255),
@vchPassword varchar(255)='',
@iVCSFlags int = 0,
@iActionFlag int = 0, /* 0 => AddFile, 1 => CheckIn */
@txStream1 Text = '', /* There is a bug that if items are NULL they do not pass to OLE servers */
@txStream2 Text = '',
@txStream3 Text = ''


as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'


declare @iPropertyObjectId int
select @iPropertyObjectId = 0

select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchProjectName varchar(255)
declare @vchSourceSafeINI varchar(255)
declare @vchServerName varchar(255)
declare @vchDatabaseName varchar(255)
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject', @vchProjectName OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT

if @chObjectType = 'PROC'
begin
if @iActionFlag = 1
begin
/* Procedure Can have up to three streams
Drop Stream, Create Stream, GRANT stream */

begin tran compile_all

/* try to compile the streams */
exec (@txStream1)
if @@error <> 0 GOTO E_Compile_Fail

exec (@txStream2)
if @@error <> 0 GOTO E_Compile_Fail

exec (@txStream3)
if @@error <> 0 GOTO E_Compile_Fail
end

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

if @iActionFlag = 1
begin
exec @iReturn = sp_OAMethod @iObjectId,
'CheckIn_StoredProcedure',
NULL,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sServerName = @vchServerName,
@sDatabaseName = @vchDatabaseName,
@sObjectName = @vchObjectName,
@sComment = @vchComment,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword,
@iVCSFlags = @iVCSFlags,
@iActionFlag = @iActionFlag,
@sStream = @txStream2
end
else
begin
declare @iStreamObjectId int
declare @iReturnValue int

exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT
if @iReturn <> 0 GOTO E_OAError

select colid, text into #ProcLines
from syscomments
where id = object_id(@vchObjectName)
order by colid

declare @iCurProcLine int
declare @iProcLines int
select @iCurProcLine = 1
select @iProcLines = (select count(*) from #ProcLines)
while @iCurProcLine <= @iProcLines
begin
declare @pos int
select @pos = 1
declare @iCurLineSize int
select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine))
while @pos <= @iCurLineSize
begin
declare @vchProcLinePiece varchar(255)
select @vchProcLinePiece = convert(varchar(255),
substring((select text from #ProcLines where colid = @iCurProcLine),
@pos, 255 ))
exec @iReturn = sp_OAMethod @iStreamObjectId, 'AddStream', @iReturnValue OUT, @vchProcLinePiece
if @iReturn <> 0 GOTO E_OAError
select @pos = @pos + 255
end
select @iCurProcLine = @iCurProcLine + 1
end
drop table #ProcLines

exec @iReturn = sp_OAMethod @iObjectId,
'CheckIn_StoredProcedure',
NULL,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sServerName = @vchServerName,
@sDatabaseName = @vchDatabaseName,
@sObjectName = @vchObjectName,
@sComment = @vchComment,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword,
@iVCSFlags = @iVCSFlags,
@iActionFlag = @iActionFlag,
@sStream = ''
end

if @iReturn <> 0 GOTO E_OAError

if @iActionFlag = 1
begin
commit tran compile_all
if @@error <> 0 GOTO E_Compile_Fail
end

end

CleanUp:
return

E_Compile_Fail:
declare @lerror int
select @lerror = @@error
rollback tran compile_all
RAISERROR (@lerror,16,-1)
goto CleanUp

E_OAError:
if @iActionFlag = 1 rollback tran compile_all
exec dbo.dt_displayoaerror @iObjectId, @iReturn
goto CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_checkinobject_u
@chObjectType char(4),
@vchObjectName nvarchar(255),
@vchComment nvarchar(255)='',
@vchLoginName nvarchar(255),
@vchPassword nvarchar(255)='',
@iVCSFlags int = 0,
@iActionFlag int = 0, /* 0 => AddFile, 1 => CheckIn */
@txStream1 Text = '', /* There is a bug that if items are NULL they do not pass to OLE servers */
@txStream2 Text = '',
@txStream3 Text = ''


as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'


declare @iPropertyObjectId int
select @iPropertyObjectId = 0

select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchProjectName nvarchar(255)
declare @vchSourceSafeINI nvarchar(255)
declare @vchServerName nvarchar(255)
declare @vchDatabaseName nvarchar(255)
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject', @vchProjectName OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT

if @chObjectType = 'PROC'
begin
if @iActionFlag = 1
begin
/* Procedure Can have up to three streams
Drop Stream, Create Stream, GRANT stream */

begin tran compile_all

/* try to compile the streams */
exec (@txStream1)
if @@error <> 0 GOTO E_Compile_Fail

exec (@txStream2)
if @@error <> 0 GOTO E_Compile_Fail

exec (@txStream3)
if @@error <> 0 GOTO E_Compile_Fail
end

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

if @iActionFlag = 1
begin
exec @iReturn = sp_OAMethod @iObjectId,
N'CheckIn_StoredProcedure',
NULL,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sServerName = @vchServerName,
@sDatabaseName = @vchDatabaseName,
@sObjectName = @vchObjectName,
@sComment = @vchComment,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword,
@iVCSFlags = @iVCSFlags,
@iActionFlag = @iActionFlag,
@sStream = @txStream2
end
else
begin
declare @iStreamObjectId int
declare @iReturnValue int

exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT
if @iReturn <> 0 GOTO E_OAError

select colid, text into #ProcLines
from syscomments
where id = object_id(@vchObjectName)
order by colid

declare @iCurProcLine int
declare @iProcLines int
select @iCurProcLine = 1
select @iProcLines = (select count(*) from #ProcLines)
while @iCurProcLine <= @iProcLines
begin
declare @pos int
select @pos = 1
declare @iCurLineSize int
select @iCurLineSize = len((select text from #ProcLines where colid = @iCurProcLine))
while @pos <= @iCurLineSize
begin
declare @vchProcLinePiece nvarchar(255)
select @vchProcLinePiece = convert(nvarchar(255),
substring((select text from #ProcLines where colid = @iCurProcLine),
@pos, 255 ))
exec @iReturn = sp_OAMethod @iStreamObjectId, N'AddStream', @iReturnValue OUT, @vchProcLinePiece
if @iReturn <> 0 GOTO E_OAError
select @pos = @pos + 255
end
select @iCurProcLine = @iCurProcLine + 1
end
drop table #ProcLines

exec @iReturn = sp_OAMethod @iObjectId,
N'CheckIn_StoredProcedure',
NULL,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sServerName = @vchServerName,
@sDatabaseName = @vchDatabaseName,
@sObjectName = @vchObjectName,
@sComment = @vchComment,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword,
@iVCSFlags = @iVCSFlags,
@iActionFlag = @iActionFlag,
@sStream = ''
end

if @iReturn <> 0 GOTO E_OAError

if @iActionFlag = 1
begin
commit tran compile_all
if @@error <> 0 GOTO E_Compile_Fail
end

end

CleanUp:
return

E_Compile_Fail:
declare @lerror int
select @lerror = @@error
rollback tran compile_all
RAISERROR (@lerror,16,-1)
goto CleanUp

E_OAError:
if @iActionFlag = 1 rollback tran compile_all
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
goto CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_checkoutobject
@chObjectType char(4),
@vchObjectName varchar(255),
@vchComment varchar(255),
@vchLoginName varchar(255),
@vchPassword varchar(255),
@iVCSFlags int = 0,
@iActionFlag int = 0/* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iReturnValue int
select @iReturnValue = 0

declare @vchTempText varchar(255)

/* this is for our strings */
declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchProjectName varchar(255)
declare @vchSourceSafeINI varchar(255)
declare @vchServerName varchar(255)
declare @vchDatabaseName varchar(255)
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject', @vchProjectName OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT

if @chObjectType = 'PROC'
begin
/* Procedure Can have up to three streams
Drop Stream, Create Stream, GRANT stream */

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAMethod @iObjectId,
'CheckOut_StoredProcedure',
NULL,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sObjectName = @vchObjectName,
@sServerName = @vchServerName,
@sDatabaseName = @vchDatabaseName,
@sComment = @vchComment,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword,
@iVCSFlags = @iVCSFlags,
@iActionFlag = @iActionFlag

if @iReturn <> 0 GOTO E_OAError


exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

create table #commenttext (id int identity, sourcecode varchar(255))


select @vchTempText = 'STUB'
while @vchTempText IS NOT NULL
begin
exec @iReturn = sp_OAMethod @iStreamObjectId, 'GetStream', @iReturnValue OUT, @vchTempText OUT
if @iReturn <> 0 GOTO E_OAError

if (@vchTempText IS NOT NULL) insert into #commenttext (sourcecode) select @vchTempText
end

select 'VCS'=sourcecode from #commenttext order by id
select 'SQL'=text from syscomments where id = object_id(@vchObjectName) order by colid

end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
GOTO CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_checkoutobject_u
@chObjectType char(4),
@vchObjectName nvarchar(255),
@vchComment nvarchar(255),
@vchLoginName nvarchar(255),
@vchPassword nvarchar(255),
@iVCSFlags int = 0,
@iActionFlag int = 0/* 0 => Checkout, 1 => GetLatest, 2 => UndoCheckOut */

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iReturnValue int
select @iReturnValue = 0

declare @vchTempText nvarchar(255)

/* this is for our strings */
declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchProjectName nvarchar(255)
declare @vchSourceSafeINI nvarchar(255)
declare @vchServerName nvarchar(255)
declare @vchDatabaseName nvarchar(255)
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject', @vchProjectName OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT

if @chObjectType = 'PROC'
begin
/* Procedure Can have up to three streams
Drop Stream, Create Stream, GRANT stream */

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAMethod @iObjectId,
N'CheckOut_StoredProcedure',
NULL,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sObjectName = @vchObjectName,
@sServerName = @vchServerName,
@sDatabaseName = @vchDatabaseName,
@sComment = @vchComment,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword,
@iVCSFlags = @iVCSFlags,
@iActionFlag = @iActionFlag

if @iReturn <> 0 GOTO E_OAError


exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

create table #commenttext (id int identity, sourcecode nvarchar(255))


select @vchTempText = N'STUB'
while @vchTempText IS NOT NULL
begin
exec @iReturn = sp_OAMethod @iStreamObjectId, N'GetStream', @iReturnValue OUT, @vchTempText OUT
if @iReturn <> 0 GOTO E_OAError

if (@vchTempText IS NOT NULL) insert into #commenttext (sourcecode) select @vchTempText
end

select N'VCS'=sourcecode from #commenttext order by id
select N'SQL'=text from syscomments where id = object_id(@vchObjectName) order by colid

end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
GOTO CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.dt_displayoaerror
@iObject int,
@iresult int
as

set nocount on

declare @vchOutput varchar(255)
declare @hr int
declare @vchSource varchar(255)
declare @vchDescription varchar(255)

exec @hr = sp_OAGetErrorInfo @iObject, @vchSource OUT, @vchDescription OUT

select @vchOutput = @vchSource + ': ' + @vchDescription
raiserror (@vchOutput,16,-1)

return


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.dt_displayoaerror_u
@iObject int,
@iresult int
as

set nocount on

declare @vchOutput nvarchar(255)
declare @hr int
declare @vchSource nvarchar(255)
declare @vchDescription nvarchar(255)

exec @hr = sp_OAGetErrorInfo @iObject, @vchSource OUT, @vchDescription OUT

select @vchOutput = @vchSource + ': ' + @vchDescription
raiserror (@vchOutput,16,-1)

return


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Drop one or all the associated properties of an object or an attribute
**
** dt_dropproperties objid, null or '' -- drop all properties of the object itself
** dt_dropproperties objid, property -- drop the property
*/
create procedure dbo.dt_droppropertiesbyid
@id int,
@property varchar(64)
as
set nocount on

if (@property is null) or (@property = '')
delete from dbo.dtproperties where objectid=@id
else
delete from dbo.dtproperties
where objectid=@id and property=@property


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Drop an object from the dbo.dtproperties table
*/
create procedure dbo.dt_dropuserobjectbyid
@id int
as
set nocount on
delete from dbo.dtproperties where objectid=@id

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Generate an ansi name that is unique in the dtproperties.value column
*/
create procedure dbo.dt_generateansiname(@name varchar(255) output)
as
declare @prologue varchar(20)
declare @indexstring varchar(20)
declare @index integer

set @prologue = 'MSDT-A-'
set @index = 1

while 1 = 1
begin
set @indexstring = cast(@index as varchar(20))
set @name = @prologue + @indexstring
if not exists (select value from dtproperties where value = @name)
break

set @index = @index + 1

if (@index = 10000)
goto TooMany
end

Leave:

return

TooMany:

set @name = 'DIAGRAM'
goto Leave

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Retrieve the owner object(s) of a given property
*/
create procedure dbo.dt_getobjwithprop
@property varchar(30),
@value varchar(255)
as
set nocount on

if (@property is null) or (@property = '')
begin
raiserror('Must specify a property name.',-1,-1)
return (1)
end

if (@value is null)
select objectid id from dbo.dtproperties
where property=@property

else
select objectid id from dbo.dtproperties
where property=@property and value=@value

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Retrieve the owner object(s) of a given property
*/
create procedure dbo.dt_getobjwithprop_u
@property varchar(30),
@uvalue nvarchar(255)
as
set nocount on

if (@property is null) or (@property = '')
begin
raiserror('Must specify a property name.',-1,-1)
return (1)
end

if (@uvalue is null)
select objectid id from dbo.dtproperties
where property=@property

else
select objectid id from dbo.dtproperties
where property=@property and uvalue=@uvalue

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Retrieve properties by id's
**
** dt_getproperties objid, null or '' -- retrieve all properties of the object itself
** dt_getproperties objid, property -- retrieve the property specified
*/
create procedure dbo.dt_getpropertiesbyid
@id int,
@property varchar(64)
as
set nocount on

if (@property is null) or (@property = '')
select property, version, value, lvalue
from dbo.dtproperties
where @id=objectid
else
select property, version, value, lvalue
from dbo.dtproperties
where @id=objectid and @property=property

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** Retrieve properties by id's
**
** dt_getproperties objid, null or '' -- retrieve all properties of the object itself
** dt_getproperties objid, property -- retrieve the property specified
*/
create procedure dbo.dt_getpropertiesbyid_u
@id int,
@property varchar(64)
as
set nocount on

if (@property is null) or (@property = '')
select property, version, uvalue, lvalue
from dbo.dtproperties
where @id=objectid
else
select property, version, uvalue, lvalue
from dbo.dtproperties
where @id=objectid and @property=property

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create procedure dbo.dt_getpropertiesbyid_vcs
@id int,
@property varchar(64),
@value varchar(255) = NULL OUT

as

set nocount on

select @value = (
select value
from dbo.dtproperties
where @id=objectid and @property=property
)


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create procedure dbo.dt_getpropertiesbyid_vcs_u
@id int,
@property varchar(64),
@value nvarchar(255) = NULL OUT

as

set nocount on

select @value = (
select uvalue
from dbo.dtproperties
where @id=objectid and @property=property
)


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_isundersourcecontrol
@vchLoginName varchar(255) = '',
@vchPassword varchar(255) = '',
@iWhoToo int = 0 /* 0 => Just check project; 1 => get list of objs */

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iReturnValue int
select @iReturnValue = 0

declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @vchTempText varchar(255)

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchProjectName varchar(255)
declare @vchSourceSafeINI varchar(255)
declare @vchServerName varchar(255)
declare @vchDatabaseName varchar(255)
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject', @vchProjectName OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT

if (@vchProjectName IS NULL) or (@vchSourceSafeINI IS NULL) or (@vchServerName IS NULL) or (@vchDatabaseName IS NULL)
begin
RAISERROR('Not Under Source Control',16,-1)
return
end

if @iWhoToo = 1
begin

/* Get List of Procs in the project */
exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAMethod @iObjectId,
'GetListOfObjects',
NULL,
@vchProjectName,
@vchSourceSafeINI,
@vchServerName,
@vchDatabaseName,
@vchLoginName,
@vchPassword

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, 'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

create table #ObjectList (id int identity, vchObjectlist varchar(255))

select @vchTempText = 'STUB'
while @vchTempText IS NOT NULL
begin
exec @iReturn = sp_OAMethod @iStreamObjectId, 'GetStream', @iReturnValue OUT, @vchTempText OUT
if @iReturn <> 0 GOTO E_OAError

if (@vchTempText IS NOT NULL) insert into #ObjectList (vchObjectlist ) select @vchTempText
end

select vchObjectlist from #ObjectList order by id
end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
goto CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_isundersourcecontrol_u
@vchLoginName nvarchar(255) = '',
@vchPassword nvarchar(255) = '',
@iWhoToo int = 0 /* 0 => Just check project; 1 => get list of objs */

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iReturnValue int
select @iReturnValue = 0

declare @iStreamObjectId int
select @iStreamObjectId = 0

declare @vchTempText nvarchar(255)

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchProjectName nvarchar(255)
declare @vchSourceSafeINI nvarchar(255)
declare @vchServerName nvarchar(255)
declare @vchDatabaseName nvarchar(255)
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject', @vchProjectName OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT

if (@vchProjectName IS NULL) or (@vchSourceSafeINI IS NULL) or (@vchServerName IS NULL) or (@vchDatabaseName IS NULL)
begin
RAISERROR(N'Not Under Source Control',16,-1)
return
end

if @iWhoToo = 1
begin

/* Get List of Procs in the project */
exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAMethod @iObjectId,
N'GetListOfObjects',
NULL,
@vchProjectName,
@vchSourceSafeINI,
@vchServerName,
@vchDatabaseName,
@vchLoginName,
@vchPassword

if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAGetProperty @iObjectId, N'GetStreamObject', @iStreamObjectId OUT

if @iReturn <> 0 GOTO E_OAError

create table #ObjectList (id int identity, vchObjectlist nvarchar(255))

select @vchTempText = N'STUB'
while @vchTempText IS NOT NULL
begin
exec @iReturn = sp_OAMethod @iStreamObjectId, N'GetStream', @iReturnValue OUT, @vchTempText OUT
if @iReturn <> 0 GOTO E_OAError

if (@vchTempText IS NOT NULL) insert into #ObjectList (vchObjectlist ) select @vchTempText
end

select vchObjectlist from #ObjectList order by id
end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
goto CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create procedure dbo.dt_removefromsourcecontrol

as

set nocount on

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

exec dbo.dt_droppropertiesbyid @iPropertyObjectId, null

/* -1 is returned by dt_droppopertiesbyid */
if @@error <> 0 and @@error <> -1 return 1

return 0



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** If the property already exists, reset the value; otherwise add property
** id -- the id in sysobjects of the object
** property -- the name of the property
** value -- the text value of the property
** lvalue -- the binary value of the property (image)
*/
create procedure dbo.dt_setpropertybyid
@id int,
@property varchar(64),
@value varchar(255),
@lvalue image
as
set nocount on
declare @uvalue nvarchar(255)
set @uvalue = convert(nvarchar(255), @value)
if exists (select * from dbo.dtproperties
where objectid=@id and property=@property)
begin
--
-- bump the version count for this row as we update it
--
update dbo.dtproperties set value=@value, uvalue=@uvalue, lvalue=@lvalue, version=version+1
where objectid=@id and property=@property
end
else
begin
--
-- version count is auto-set to 0 on initial insert
--
insert dbo.dtproperties (property, objectid, value, uvalue, lvalue)
values (@property, @id, @value, @uvalue, @lvalue)
end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** If the property already exists, reset the value; otherwise add property
** id -- the id in sysobjects of the object
** property -- the name of the property
** uvalue -- the text value of the property
** lvalue -- the binary value of the property (image)
*/
create procedure dbo.dt_setpropertybyid_u
@id int,
@property varchar(64),
@uvalue nvarchar(255),
@lvalue image
as
set nocount on
--
-- If we are writing the name property, find the ansi equivalent.
-- If there is no lossless translation, generate an ansi name.
--
declare @avalue varchar(255)
set @avalue = null
if (@uvalue is not null)
begin
if (convert(nvarchar(255), convert(varchar(255), @uvalue)) = @uvalue)
begin
set @avalue = convert(varchar(255), @uvalue)
end
else
begin
if 'DtgSchemaNAME' = @property
begin
exec dbo.dt_generateansiname @avalue output
end
end
end
if exists (select * from dbo.dtproperties
where objectid=@id and property=@property)
begin
--
-- bump the version count for this row as we update it
--
update dbo.dtproperties set value=@avalue, uvalue=@uvalue, lvalue=@lvalue, version=version+1
where objectid=@id and property=@property
end
else
begin
--
-- version count is auto-set to 0 on initial insert
--
insert dbo.dtproperties (property, objectid, value, uvalue, lvalue)
values (@property, @id, @avalue, @uvalue, @lvalue)
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_validateloginparams
@vchLoginName varchar(255),
@vchPassword varchar(255)
as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchSourceSafeINI varchar(255)
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAMethod @iObjectId,
'ValidateLoginParams',
NULL,
@sSourceSafeINI = @vchSourceSafeINI,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword
if @iReturn <> 0 GOTO E_OAError

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
GOTO CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_validateloginparams_u
@vchLoginName nvarchar(255),
@vchPassword nvarchar(255)
as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int
select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchSourceSafeINI nvarchar(255)
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT

exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 GOTO E_OAError

exec @iReturn = sp_OAMethod @iObjectId,
N'ValidateLoginParams',
NULL,
@sSourceSafeINI = @vchSourceSafeINI,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword
if @iReturn <> 0 GOTO E_OAError

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
GOTO CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_vcsenabled

as

set nocount on

declare @iObjectId int
select @iObjectId = 0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iReturn int
exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT
if @iReturn <> 0 raiserror('', 16, -1) /* Can't Load Helper DLLC */



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
** This procedure returns the version number of the stored
** procedures used by the Microsoft Visual Database Tools.
** Current version is 7.0.00.
*/
create procedure dbo.dt_verstamp006
as
select 7000

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_whocheckedout
@chObjectType char(4),
@vchObjectName varchar(255),
@vchLoginName varchar(255),
@vchPassword varchar(255)

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID varchar(100)
select @VSSGUID = 'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int

select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchProjectName varchar(255)
declare @vchSourceSafeINI varchar(255)
declare @vchServerName varchar(255)
declare @vchDatabaseName varchar(255)
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSProject', @vchProjectName OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT
exec dbo.dt_getpropertiesbyid_vcs @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT

if @chObjectType = 'PROC'
begin
exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT

if @iReturn <> 0 GOTO E_OAError

declare @vchReturnValue varchar(255)
select @vchReturnValue = ''

exec @iReturn = sp_OAMethod @iObjectId,
'WhoCheckedOut',
@vchReturnValue OUT,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sObjectName = @vchObjectName,
@sServerName = @vchServerName,
@sDatabaseName = @vchDatabaseName,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword

if @iReturn <> 0 GOTO E_OAError

select @vchReturnValue

end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror @iObjectId, @iReturn
GOTO CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc dbo.dt_whocheckedout_u
@chObjectType char(4),
@vchObjectName nvarchar(255),
@vchLoginName nvarchar(255),
@vchPassword nvarchar(255)

as

set nocount on

declare @iReturn int
declare @iObjectId int
select @iObjectId =0

declare @VSSGUID nvarchar(100)
select @VSSGUID = N'SQLVersionControl.VCS_SQL'

declare @iPropertyObjectId int

select @iPropertyObjectId = (select objectid from dbo.dtproperties where property = 'VCSProjectID')

declare @vchProjectName nvarchar(255)
declare @vchSourceSafeINI nvarchar(255)
declare @vchServerName nvarchar(255)
declare @vchDatabaseName nvarchar(255)
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSProject', @vchProjectName OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSourceSafeINI', @vchSourceSafeINI OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLServer', @vchServerName OUT
exec dbo.dt_getpropertiesbyid_vcs_u @iPropertyObjectId, 'VCSSQLDatabase', @vchDatabaseName OUT

if @chObjectType = 'PROC'
begin
exec @iReturn = sp_OACreate @VSSGUID, @iObjectId OUT

if @iReturn <> 0 GOTO E_OAError

declare @vchReturnValue nvarchar(255)
select @vchReturnValue = ''

exec @iReturn = sp_OAMethod @iObjectId,
N'WhoCheckedOut',
@vchReturnValue OUT,
@sProjectName = @vchProjectName,
@sSourceSafeINI = @vchSourceSafeINI,
@sObjectName = @vchObjectName,
@sServerName = @vchServerName,
@sDatabaseName = @vchDatabaseName,
@sLoginName = @vchLoginName,
@sPassword = @vchPassword

if @iReturn <> 0 GOTO E_OAError

select @vchReturnValue

end

CleanUp:
return

E_OAError:
exec dbo.dt_displayoaerror_u @iObjectId, @iReturn
GOTO CleanUp



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-06-02 : 15:27:02
I expect your problem (or at least the first one!!) is that every single [recipes] record is joined to every single [users] record, and they are joined to every single [regions] record because there is no constraint between those three tables.

Here's how I see your query (with JOINs instead of the WHERE style you are using):

SELECT	RSUF.user_id,
	RSUF.recipe_id,
	RSUF.use_in_lesson_plan AS use_in_lesson_plan,
	RSUF.attached_lesson_plan AS attached_lesson_plan,
	RSUF.participate,
	RSUF.authorized AS authorized,
	RSUF.date_signed_up_for AS date_signed_up_for,
	U.user_id,
	U.f_name AS f_name,
	U.l_name AS l_name,
	U.email_address AS email_address,
	U.primary_phone AS primary_phone, 
	R.recipe_name AS recipe_name,
	R.recipe_id AS recipe_id,
	R.number_served AS number_served,
	R.last_day_to_sign_up_for AS last_day_to_sign_up_for,
	R.recipe_instructions AS recipe_instructions,
	R.active_recipe,
	RI.recipe_ingredient_id,
	RI.recipe_id,
	RI.ingredient_id,
	RI.ingredient_quantity AS ingredient_quantity,
	RI.ingredient_unit,
	RI.active_ingredient AS active_ingredient,
	I.ingredient_id,
	I.ingredient AS ingredient_name,
	RSUFIN.ingredient_id,
	RSUFIN.ingredient_needed AS ingredient_needed
FROM	dbo.recipes AS R
	JOIN dbo.recipe_ingredients AS RI
		 ON RI.recipe_id = R.recipe_id
	JOIN dbo.ingredients AS I
		 ON I.ingredient_id = RI.ingredient_id
	JOIN users AS U
		 ON U.??? = XXX.???
	JOIN dbo.recipes_signed_up_for_ingredients_needed AS RSUFIN
		ON RSUFIN.user_id = #url.user_id#
		AND RSUFIN.ingredient_id = RI.ingredient_id
		AND RSUFIN.ingredient_needed = 1
	JOIN dbo.recipes_signed_up_for AS RSUF
		 ON RSUF.recipe_id = R.recipe_id
		AND RSUF.user_id = U.user_id 
		AND RSUF.user_id = #URL.user_id# 
		AND RSUF.participate = 1
	JOIN dbo.recipes AS RSUFR
		 ON RSUFR.recipe_id = RSUF.recipe_id
		AND RSUFR.active_recipe = 1
	JOIN dbo.regions AS REG
		 ON R.??? = XXX.??
	JOIN dbo.locations AS L
		 ON L.region_id = REG.region_id
ORDER BY R.recipe_name

Kristen
Go to Top of Page

kingjes
Starting Member

7 Posts
Posted - 2006-06-02 : 15:28:16
How could I create one BIG arse query that will join all of the tables? My thought is that Icould run this query, cache it and just runa "query of queries" form the one large cache.. crazy I know but just tossing it out there..
thank you much
-jes
Go to Top of Page

kingjes
Starting Member

7 Posts
Posted - 2006-06-02 : 15:30:29
thank you very much for your help.. could you please explain "U.??? = XXX.???"
do I need to pass in the recipe id?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2006-06-02 : 15:41:16
You need to provide criteria that will limit the number of records from those two tables!

I don't know what the relevant columns are in the other tables (although I would guess that "#url.user_id#" is probably relevant for the [users] table, but I have no idea what is relevant for the [regions] table)

Kristen
Go to Top of Page

kingjes
Starting Member

7 Posts
Posted - 2006-06-02 : 15:47:42
the regions table has a region id. each user us assigned to a region.
the users record has a regioin id column that holds the regiion id.
-jes
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site