Hi,
I am trying to write a sql provider script to create a table but only if it doesn't exist. I have copied and pasted it from the generated script in SQL Management Studio and replaced the dbo. with the {databaseOwner} & {objectQualifier} tokens.
When I try installing my module I get the following error :-
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'ON'. at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:2e489b8b-6db2-4978-8b9c-532f9e273c8b /************************************************************/ /***** SqlDataProvider *****/ /***** *****/ /***** *****/ /***** Note: To manually execute this script you must *****/ /***** perform a search and replace operation *****/ /***** for dbo. and *****/ /***** *****/ /************************************************************/ IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[OO_Users_Stock_Items_Templates]') AND type in (N'U')) BEGIN /****** Object: Table dbo.[OO_Users_Stock_Items_Templates] Script Date: 10/14/2013 17:03:02 ******/ SET ANSI_NULLS ON System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'END'. at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script) ClientConnectionId:ae52cfc4-e096-4154-8288-d4b3badb4609 END /************************************************************/ /***** SqlDataProvider *****/ /************************************************************/
The sql code is as follows :-
/************************************************************/
/***** SqlDataProvider *****/
/***** *****/
/***** *****/
/***** Note: To manually execute this script you must *****/
/***** perform a search and replace operation *****/
/***** for {databaseOwner} and {objectQualifier} *****/
/***** *****/
/************************************************************/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{databaseOwner}[{objectQualifier}OO_Users_Stock_Items_Templates]') AND type in (N'U'))
BEGIN
/****** Object: Table {databaseOwner}[{objectQualifier}OO_Users_Stock_Items_Templates] Script Date: 10/14/2013 17:03:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE {databaseOwner}[{objectQualifier}OO_Users_Stock_Items_Templates](
[Users_Stock_Items_TemplatesID] [int] IDENTITY(1,1) NOT NULL,
[PortalID] [int] NOT NULL,
[Account_Short_Code] [char](14) NOT NULL,
[User_Name] [nvarchar](100) NOT NULL,
[Item_Number] [char](20) NOT NULL,
[Template_Name] [char](50) NOT NULL,
CONSTRAINT [PK_OO_Users_Stock_Items_Templates] PRIMARY KEY CLUSTERED
(
[Users_Stock_Items_TemplatesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [PORTALID] ON {databaseOwner}[{objectQualifier}OO_Users_Stock_Items_Templates]
(
[PortalID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [USERSSTOCKITEMSTEMPLATES_ACCOUNTFM_NOTEMPLATE_NAMEUSER_NAME] ON {databaseOwner}[{objectQualifier}OO_Users_Stock_Items_Templates]
(
[Account_Short_Code] ASC,
[Item_Number] ASC,
[Template_Name] ASC,
[User_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
END
/************************************************************/
/***** SqlDataProvider *****/
/************************************************************/
Can anyone see what I am doing wrong?
I am using DNN 7.