|
 |  | |
| | |
| | |
| | |
 | | |
|
|
|
| SQL Problem - I don't get it... |
|
|
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 |
|
|
|
| |
|
|
| Re: SQL Problem - I don't get it... |
|
|
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
Thanks,
Vladan Strigo
NETMedia
My website: Vladan.Strigo.NET
Vladan.Strigo.NET: Projects
* Advanced VS2005 development approach - BlankModule
* DNN & Microsoft Ajax best practices guidance
Vladan.Strigo.NET: Resources
* Comprehensive list of DNN 4 Module development resources
|
|
|
|
| |
|
|
| Re: SQL Problem - I don't get it... |
|
|
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. |
|
|
|
| |
|
|
| Re: SQL Problem - I don't get it... |
|
|
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
|
|
|
|
| |
|
|
| Re: SQL Problem - I don't get it... |
|
|
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
|
|
|
|
| |
| | |
| | |
| | |
|  |
| |
|
|
|
These Discussion Forums are dedicated to the discussion of the DotNetNuke Web Application Framework.
For the benefit of the community and to protect the integrity of the project, 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 DotNetNuke.
2. Discussion or promotion of DotNetNuke product releases under a different brand name are strictly prohibited.
3. No Flaming or Trolling.
4. No Profanity, Racism, or Prejudice.
5. Site Moderators have the final word on approving/removing a thread or post or comment.
6. English language posting only, please.
|
| |
|
|