 |
|
|
Joined: 2/1/2006
Posts: 190
|
|
|
I have a custom module and I'm trying to package it now. Everything works perfectly except the 01.00.00.SqlDataProvider file where the tables are created. I have a second file 01.00.01.SqlDataProvider to create the SP's and it works perfectly too.
I've found a couple of posts mentioning the encoding - which are all at UTF-8 with signature (VS2005). Still nothing works on creating the tables.
I've compared to the blog and other install files to see where it is different, but can't see it. When I remove {databaseOwner}{objectQualifier}, and check it in SQL Server (2005) it all checks out OK. The scripts were all created in SQL Server 2005 using 2000 format.
Here's an excerpt of the errors I keep getting:
| |
System.Data.SqlClient.SqlException: Incorrect syntax near '.'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) /** Create Tables and Constraints **/ IF NOT EXISTS (SELECT * FROM sysobjects WHERE object_id = OBJECT_ID(N'dbo.[ocps_pd_alerts]') AND type in (N'U')) BEGIN CREATE TABLE dbo.[ocps_pd_alerts]( [PortalId] [int] NOT NULL, [ItemID] [smallint] IDENTITY(0,1) NOT NULL, [ModuleID] [int] NOT NULL, [AlertName] [nvarchar](50) NOT NULL, [AlertBody] [nvarchar](200) NOT NULL, [AlertType] [smallint] NOT NULL, [Frequency] [tinyint] NOT NULL, [Expires] [smalldatetime] NOT NULL, [Publish] [bit] NOT NULL CONSTRAINT [DF_ocps_pd_alerts_Publish] DEFAULT ((1)), [CreatedByID] [int] NOT NULL, [Created] [smalldatetime] NOT NULL CONSTRAINT [DF__ocps_pd_a__Creat__4885B9BB] DEFAULT (getdate()), [ChangeByID] [int] NOT NULL, [Changed] [smalldatetime] NOT NULL CONSTRAINT [DF__ocps_pd_a__Chang__4979DDF4] DEFAULT (getdate()), CONSTRAINT dbo.[PK_ocps_pd_alerts] PRIMARY KEY CLUSTERED ( [ItemID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END System.Data.SqlClient.SqlException: Incorrect syntax near '.'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at |
|
|
|
|
I can't seem to figure out what '.' or 'with' it is not liking. I change 'sys.objects' to 'sysobjects', and 'sys.foreign_keys' to sysforeignkeys (as in blogs install) but no go. I have no more '.' in the code, but it keeps failing there.
Can anyone shed some light on this? TIA
|
| |
|
|
 | |
|
|
Vladan.Strigo.NET
Joined: 11/27/2003
Posts: 505
|
|
|
Try adding a couple of new lines to the end of that file - it breaks when the file ends with something SQL-ish... maybe that's your problem
HTH
|
| |
|
|
| |
|
|
Joined: 2/1/2006
Posts: 190
|
|
|
Nope - didn't help. I was hopeful though.
I recreated the same scripts, and before changing the [dbo]. to {databaseOwner}{objectQualifier} it runs fine. I don't see what I'm doing wrong - and it doesn't help that no line numbers are retrurned indicating where the problem is found. Not sure that can be provided in any way, but it sure would help.
|
| |
|
|
| |
|
|
Joined: 5/8/2003
Posts: 109
|
|
|
yshodul test yous scripts in the query editor to ensure tehy run beofre makign a package. These are the following errors I found in your script
1. Missign a space before the WITH statement
2. remove SCHEMA dbo FROM CONSTRAINT name
3. sysobjects should be sys.objects
Here is script that works
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[ocps_pd_alerts]') AND type in (N'U'))
BEGIN
CREATE TABLE dbo.[ocps_pd_alerts](
[PortalId] [int] NOT NULL,
[ItemID] [smallint] IDENTITY(0,1) NOT NULL,
[ModuleID] [int] NOT NULL,
[AlertName] [nvarchar](50) NOT NULL,
[AlertBody] [nvarchar](200) NOT NULL,
[AlertType] [smallint] NOT NULL,
[Frequency] [tinyint] NOT NULL,
[Expires] [smalldatetime] NOT NULL,
[Publish] [bit] NOT NULL CONSTRAINT [DF_ocps_pd_alerts_Publish] DEFAULT ((1)),
[CreatedByID] [int] NOT NULL,
[Created] [smalldatetime] NOT NULL CONSTRAINT [DF__ocps_pd_a__Creat__4885B9BB] DEFAULT (getdate()),
[ChangeByID] [int] NOT NULL,
[Changed] [smalldatetime] NOT NULL CONSTRAINT [DF__ocps_pd_a__Chang__4979DDF4] DEFAULT (getdate()),
CONSTRAINT [PK_ocps_pd_alerts] PRIMARY KEY CLUSTERED ( [ItemID] ASC ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
END
|
| |
|
|
| |
|
|
Joined: 2/1/2006
Posts: 190
|
|
|
I started with the editor and it ran fine there. I then copied to VS2005 page where I edited it for the DNN qualifiers - the text pasted here was teh return from DNN - so I think the spaces were in there before (#1) - I'll double check. You basically put it right back where it was without the {databaseowner}{objectqualifier} tags - which I thought were required. In that form, I can get it to work perfectly (at least on the SQL page in DNN).
#2 - that may help I will remove this
#3 - I've seen it both ways, so I'm unsure why this is a factor. I reference the blog SQL files for that change. I also tried it both ways wih neither making a difference. Maybe I'm not getting past other errors.
If following #1 - without qualifiers - I can make it work, is that a good thing or a bad thing, since the DNN documentation says we should be putting it there? Maybe if I leave off the {databaseowner} and put dbo. instead with the {objectqualifier}?
Thanks
|
| |
|
|
| |