Hi Lowell...thanks for your reply. I had to do some research on the BCP part but got it to work. Thanks!
One thing...it does output the specs, but it's not exactly the same as running it manually. It looks like it puts everything together without an sp_executesql statement. For example:
This is the code generated from one particular function that is generated out of the Generate Scripts Function:
/****** Object: UserDefinedFunction [dbo].[KLLfn_Get_Week_Start_Date] Script Date: 11/22/2010 16:26:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KLLfn_Get_Week_Start_Date]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:Tony Schmitt
-- Create date: 03/15/2010
-- Description:Return the starting day of a given week.
-- =============================================
CREATE FUNCTION [dbo].[KLLfn_Get_Week_Start_Date]
(
-- Add the parameters for the function here
@ParmDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @WkStartDate DateTime
-- Add the T-SQL statements to compute the return value here
SET @WkStartDate = (SELECT DATEADD(wk, DATEDIFF(wk, 6, @ParmDate), 6))
-- Return the result of the function
RETURN @WkStartDate
END
'
END
GO
This is from the process you provided me with:
-- =============================================
-- Author:Tony Schmitt
-- Create date: 03/15/2010
-- Description:Return the starting day of a given week.
-- =============================================
CREATE FUNCTION [dbo].[KLLfn_Get_Week_Start_Date]
(
-- Add the parameters for the function here
@ParmDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @WkStartDate DateTime
-- Add the T-SQL statements to compute the return value here
SET @WkStartDate = (SELECT DATEADD(wk, DATEDIFF(wk, 6, @ParmDate), 6))
-- Return the result of the function
RETURN @WkStartDate
END
It then goes to the next Create Function statement.
Basically, it looks like it's missing the step checking if it exists, then the execute statement. When I try running the new version, I get loads and loads of errors about variables etc not being defined.
Any ideas?