How to define an SQL Server function withing DDT?

Edhy Rijo — Tue, 03 Nov 2009 13:58:49 GMT

I have a SQL Server function which I have not problem creating it in SQL Server Manager Studio, but when try to create it in the DDT I always get an error when try to deploy the UDF.

Here the code I have in the DDT, also look at the udfSample.png image:
[codesnippet]
(@FK_Customers INT)
RETURNS varchar(20)

AS
BEGIN
DECLARE @ParkingNumbers varchar(20)
DECLARE @IsInactive BIT

SET @ParkingNumbers = ''
SET @IsInactive = 0

SELECT @ParkingNumbers = @ParkingNumbers + LTRIM(cast(v.ParkingNumber as varchar(3))) + ','
FROM
dbo.Vehicles V
WHERE
(V.FK_Customers = @FK_Customers AND
V.IsInactive = @IsInactive)
ORDER BY
v.ParkingNumber

RETURN @ParkingNumbers
END
[/codesnippet]

Here is the code I use in Manager Studio with not problem:
[codesnippet]
CREATE FUNCTION dbo.udf_GetParkingNumbers (@FK_Customers INT)
RETURNS varchar(20)

AS
BEGIN
DECLARE @ParkingNumbers varchar(20)
DECLARE @IsInactive BIT

SET @ParkingNumbers = ''
SET @IsInactive = 0

SELECT @ParkingNumbers = @ParkingNumbers + LTRIM(cast(v.ParkingNumber as varchar(3))) + ','
FROM
dbo.Vehicles V
WHERE
(V.FK_Customers = @FK_Customers AND
V.IsInactive = @IsInactive)
ORDER BY
v.ParkingNumber

-- SELECT @ParkingNumbers

RETURN @ParkingNumbers

END
GO
[/codesnippet]

The error I get from DDT when try to deploy the UDF is:
[codesnippet]
X 11/1/2009 11:09:09 PM-> An error occurred while creating the stored procedure [dbo].[spGetParkingNumbers].
X 11/1/2009 11:09:09 PM-> SqlException
X 11/1/2009 11:09:09 PM-> Incorrect syntax near the keyword 'DECLARE'.
X 11/1/2009 11:09:09 PM-> Incorrect syntax near the keyword 'AS'.
X 11/1/2009 11:09:09 PM-> Must declare the scalar variable "@ParkingNumbers".
X 11/1/2009 11:09:09 PM-> Must declare the scalar variable "@ParkingNumbers".
X 11/1/2009 11:09:09 PM->
X 11/1/2009 11:09:09 PM-> Source : .Net SqlClient Data Provider
X 11/1/2009 11:09:09 PM->
X 11/1/2009 11:09:09 PM-> Stack Trace:
X 11/1/2009 11:09:09 PM-> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
X 11/1/2009 11:09:09 PM-> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
X 11/1/2009 11:09:09 PM-> at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
X 11/1/2009 11:09:09 PM-> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
X 11/1/2009 11:09:09 PM-> at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
X 11/1/2009 11:09:09 PM-> at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
X 11/1/2009 11:09:09 PM-> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
X 11/1/2009 11:09:09 PM-> at MicroFour.StrataFrame.DBEngine.SQL.DatabaseMigrator.DeploySprocsOfPriority(Database parent, DatabaseInfo dbInfo, Int32 priority)

[/codesnippet]

What am I doing wrong in DDT to get this error, while the same code works just fine in Manager Studio?

RE: How to define an SQL Server function withing DDT?

Edhy Rijo — Tue, 03 Nov 2009 13:58:49 GMT

Hi Dustin,

Thanks for the confirmation. I deleted my code from DDT and copied again and now it works. I guess some invisible character may have been there before.

RE: How to define an SQL Server function withing DDT?

Dustin Taylor — Tue, 03 Nov 2009 08:11:06 GMT

This is probably a silly question, but you are creating this as a User Defined Function rather than a Stored Procedure in the DDT, correct?

I just gave this a shot on a test database, and the following worked fine as a UDF deployed to a new database:

[code](@FK_Customers INT)
RETURNS varchar(20)

AS
BEGIN
DECLARE @ParkingNumbers varchar(20)
DECLARE @IsInactive BIT

SET @ParkingNumbers = ''
SET @IsInactive = 0

SELECT @ParkingNumbers = @ParkingNumbers + LTRIM(cast(v.ParkingNumber as varchar(3))) + ','
FROM
dbo.Vehicles V
WHERE
(V.FK_Customers = @FK_Customers AND
V.IsInactive = @IsInactive)
ORDER BY
v.ParkingNumber

-- SELECT @ParkingNumbers

RETURN @ParkingNumbers

END[/code]

That above was in the "Procedure Code" section of hte User-Defined Function Properties, while the "Pre-Create/Environment Code" was left as the default:

[code]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO[/code]

StrataFrame Forum » StrataFrame Database Deployment Toolkit » Database Deployment Toolkit (How do I?) » How to define an SQL Server function withing DDT?

How to define an SQL Server function withing DDT?

RE: How to define an SQL Server function withing DDT?

RE: How to define an SQL Server function withing DDT?