Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDevelopment and...Development and...Getting StartedGetting StartedSqlDataProvider SQL script problemSqlDataProvider SQL script problem
Previous
 
Next
New Post
10/14/2013 12:17 PM
 

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.

 
New Post
10/14/2013 1:07 PM
 
What version of SQL server are you trying to run this?

Chris Hammond
Former DNN Corp Employee, MVP, Core Team Member, Trustee
Christoc.com Software Solutions DotNetNuke Module Development, Upgrades and consulting.
dnnCHAT.com a chat room for DotNetNuke discussions
 
New Post
10/14/2013 7:23 PM
 
you should remove all target specifications prior to running the script in a different environment. please use existing DNN core or module scripts as templates.

Cheers from Germany,
Sebastian Leupold

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
10/15/2013 2:21 AM
 
There are several points:

1) Why do you create the table only if it does not exist? Where is the guarantee that it is the correct version of the table etc.? I would first check if it exists, if yes delete it and then create it.
2) As Sebastian said, but to make it more clear: avoid constructs like ON [PRIMARY] etc. Remove as much code as possible from your SQL script that it still works (so it will use default settings from the server where it is running). Also avoid the SET statements etc.
3) Also avoid the square brackets whenever possible - e.g. use "CREATE TABLE {databaseOwner}{objectQualifier}OO_Users_Stock_Items_Templates(" instead of "CREATE TABLE {databaseOwner}[{objectQualifier}OO_Users_Stock_Items_Templates](".
4) I am not sure if this is valid anymore, but somewhen it was necessary to have a blank line after each GO (not for SQL Server, but for DNN). Please try that as well.

Best wishes
Michael

Michael Tobisch
DNN★MVP

dnn-Connect.org - The most vibrant community around the DNN-platform
 
New Post
10/15/2013 5:44 AM
 

 Thank you all for your pointers. 

 It is SQLExpress 2008 R2.

 I will simplify the script, remove the set statements and remove the [ brackets.

I want the table left to stop someone else accidentally removing the module and destroying the data. Structure updates  can be handled by module update sqlprovider scripts.


 

 

 
Previous
 
Next
HomeHomeDevelopment and...Development and...Getting StartedGetting StartedSqlDataProvider SQL script problemSqlDataProvider SQL script problem


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out