Products

Evoq Content Overview Content Creation Workflow Asset Management Mobile Responsive Personalization Content Analytics SEO Integrations Security Website PerformanceEvoq Engage Overview Community Management  Dashboard  Analytics  Member Profile Gamification Advocate Marketing Community Engagement  Ideas  Answers  Discussions  Groups  Wikis  Events Mobile ReadyDNN Support PackagesEvoq: CMS FeaturesEvoq OnDemandCompare DNN's CMS Products

Solutions

Content Management SystemMulti-Channel PublishingDigital MarketingOnline Community ManagementIntranet Software SolutionOur CustomersPrime

Resources

Test DrivesSchedule A DemoDocumentation CenterWebinarsWhite PapersProduct ManualsRequest PricingData SheetsCase StudiesEvoq Preferred Products Content Management Ecommerce Forms Identity Management and Authentication Site Management Social Themes

Partners

DNN Partners Partner Directory Become a DNN Partner Partner Program Overview  Hosting  Implementation  ISV  Training  Competencies

Community

Participate Ask a Question Forums Community Showcase DNN MVP  Program Overview  Lifetime MVPs  Honorary MVPs Subscribe to DNN Digest Advisory Groups  Awareness Advisory Group  Developer Advisory Group  Partner Advisory Group  Technology Advisory GroupLearn Documentation Wiki Community Blog Video Library Project History Development RoadmapDownload DNN Store Language Packs Manuals Nightly BuildsSecurity Policies Security Center

Blog

About

News Press Releases News Coverage Press KitCareersContact DNN

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
HomeHomeArchived Discus...Archived Discus...Developing Under Previous Versions of .NETDeveloping Under Previous Versions of .NETASP.Net 2.0ASP.Net 2.0SQL error when packaging the moduleSQL error when packaging the module
Previous
 
Next
New Post
5/4/2007 3:27 AM
 
Jimmy Singal

Joined: 10/12/2005
Posts: 71

Hi All,

I got problem when trying to package the stored procedure below in my module.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}C360_GetOpenCalls') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure {databaseOwner}{objectQualifier}C360_GetOpenCalls

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}[C360_GetOpenCalls]

@ModuleId int,

@StatusId int,

@PageIndex int,

@NumRows int,

@RecordCount int OUTPUT

AS

DECLARE @startRowIndex int;

SET @startRowIndex = @PageIndex * @NumRows + 1;

DECLARE @total int;

SET @total = @startRowIndex + @NumRows - 1;

SELECT @RecordCount=(SELECT COUNT(*) FROM C360_OpenCallInfo WHERE ModuleId = @ModuleId AND StatusId = @StatusId);

WITH OpenCallEntries AS (

SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS Row, OpenCallId, Title, Description,

Deadline, Email, Contact, Disciplines, ThematicAreas, ThumbnailId, CreatedBy, CreatedDate

FROM C360_OpenCallInfo WHERE ModuleId = @ModuleId AND StatusId = @StatusId

)

SELECT *

FROM OpenCallEntries

WHERE (Row > @startRowIndex) AND (Row < @total)

ORDER BY CreatedDate DESC;

-----------------------------------------------------------------------------------------------------------------------------------------------------------

I always got "incorrect syntax near '1'", i think the error could be in the SET statement but I can't think why. Because when I tried to run this script manually using the Host -> SQL. it runs succesfully and the stored procedure was created.

 

DotNetNuke Newbie!
 
New Post
5/4/2007 6:08 AM
 
Jimmy Singal

Joined: 10/12/2005
Posts: 71

Here is the error from the log.

 

Install Module:: C360_OpenCall
Info:: Starting Installation
Info:: Reading files
Info:: Loading AddOpenCall.ascx.resx
Info:: File AddOpenCall.ascx.resx read successfully
Info:: Loading EditOpenCall.ascx.resx
Info:: File EditOpenCall.ascx.resx read successfully
Info:: Loading OpenCallList.ascx.resx
Info:: File OpenCallList.ascx.resx read successfully
Info:: Loading Microsoft.Practices.EnterpriseLibrary.Data.dll
Info:: File Microsoft.Practices.EnterpriseLibrary.Data.dll read successfully
Info:: Loading Microsoft.Practices.ObjectBuilder.dll
Info:: File Microsoft.Practices.ObjectBuilder.dll read successfully
Info:: Loading C360.OpenCall.dll
Info:: File C360.OpenCall.dll read successfully
Info:: Loading Microsoft.Practices.EnterpriseLibrary.Common.dll
Info:: File Microsoft.Practices.EnterpriseLibrary.Common.dll read successfully
Info:: Loading calendar_view_month.png
Info:: File calendar_view_month.png read successfully
Info:: Loading version.txt
Info:: File version.txt read successfully
Info:: Loading AddOpenCall.ascx
Info:: File AddOpenCall.ascx read successfully
Info:: Loading EditOpenCall.ascx
Info:: File EditOpenCall.ascx read successfully
Info:: Loading OpenCallDetail.ascx
Info:: File OpenCallDetail.ascx read successfully
Info:: Loading OpenCallList.ascx
Info:: File OpenCallList.ascx read successfully
Info:: Loading OpenCallSettings.ascx
Info:: File OpenCallSettings.ascx read successfully
Info:: Loading module.css
Info:: File module.css read successfully
Info:: Loading C360_OpenCall.dnn
Info:: File C360_OpenCall.dnn read successfully
Info:: Loading 01.00.00.SqlDataProvider
Info:: File 01.00.00.SqlDataProvider read successfully
Info:: Loading Uninstall.SqlDataProvider
Info:: File Uninstall.SqlDataProvider read successfully
Info:: Loading 01.01.00.SqlDataProvider
Info:: File 01.01.00.SqlDataProvider read successfully
Info:: Loading eWorld.UI.dll
Info:: File eWorld.UI.dll read successfully
Info:: Loading 01.02.00.SqlDataProvider
Info:: File 01.02.00.SqlDataProvider read successfully
Info:: Reading files done.
Info:: Reading DNN file
Info:: DNN file is in valid 3.0 format.
Info:: xml loaded.
Info:: Loading files info
Info:: Found valid path (images) for calendar_view_month.png.
Info:: Found valid path (App_LocalResources) for OpenCallList.ascx.resx.
Info:: Found valid path (App_LocalResources) for AddOpenCall.ascx.resx.
Info:: Found valid path (App_LocalResources) for EditOpenCall.ascx.resx.
Info:: Loading Modules info
Info:: Loading Control info for 'Open Call List' module
Info:: Dnn load finished successfully
Info:: Begin Sql execution
Info:: Executing 01.01.00.SqlDataProvider
Info:: Start Sql execution: 01.01.00.SqlDataProvider file
Info:: SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Incorrect syntax near '1'. Incorrect syntax near '@NumRows'. 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 DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo.[C360_GetOpenCalls] @ModuleId int, @StatusId int, @PageIndex int, @NumRows int, @RecordCount int OUTPUT AS DECLARE @startRowIndex int; SET @startRowIndex = @PageIndex * @NumRows 1; DECLARE @total int; SET @total = @startRowIndex @NumRows - 1; SELECT @RecordCount=(SELECT COUNT(*) FROM C360_OpenCallInfo WHERE ModuleId = @ModuleId AND StatusId = @StatusId); WITH OpenCallEntries AS ( SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS Row, OpenCallId, Title, Description, Deadline, Email, Contact, Disciplines, ThematicAreas, ThumbnailId, CreatedBy, CreatedDate FROM C360_OpenCallInfo WHERE ModuleId = @ModuleId AND StatusId = @StatusId ) SELECT * FROM OpenCallEntries WHERE (Row > @startRowIndex) AND (Row < @total) ORDER BY CreatedDate DESC;
Info:: End Sql execution: 01.01.00.SqlDataProvider file
Info:: Finished Sql execution
Info:: Creating files
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\module.css
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\OpenCallList.ascx
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\OpenCallDetail.ascx
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\AddOpenCall.ascx
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\EditOpenCall.ascx
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\OpenCallSettings.ascx
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\version.txt
Info:: Created D:\Sites\Culture360Dev\bin\C360.OpenCall.dll
Info:: Created D:\Sites\Culture360Dev\bin\Microsoft.Practices.EnterpriseLibrary.Common.dll
Info:: Created D:\Sites\Culture360Dev\bin\Microsoft.Practices.EnterpriseLibrary.Data.dll
Info:: Created D:\Sites\Culture360Dev\bin\Microsoft.Practices.ObjectBuilder.dll
Info:: Created D:\Sites\Culture360Dev\bin\eWorld.UI.dll
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\images\calendar_view_month.png
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\App_LocalResources\OpenCallList.ascx.resx
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\App_LocalResources\AddOpenCall.ascx.resx
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\App_LocalResources\EditOpenCall.ascx.resx
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\Providers\DataProviders\SqlDataProvider\01.00.00.SqlDataProvider
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\Providers\DataProviders\SqlDataProvider\01.01.00.SqlDataProvider
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\Uninstall.SqlDataProvider
Info:: Created D:\Sites\Culture360Dev\DesktopModules\C360.OpenCall\C360_OpenCall.dnn.config
Info:: Files created
Info:: Registering DesktopModule
Info:: Registering Definitions
Info:: Registering Controls
Info:: Registering finished
Info:: Installation successful.

DotNetNuke Newbie!
 
New Post
5/4/2007 6:19 AM
 
Uri Nizan

www.seamlessmodules.com
Joined: 3/30/2004
Posts: 27

Try dropping the semi-colons (;)
 
New Post
5/4/2007 7:14 AM
 
Chris Smith


www.nddllc.com
Joined: 1/25/2003
Posts: 681

The error is occuring at the '+' symbols. Be certain that your saving your SQL Script in UTF-8 format.
 
New Post
5/4/2007 8:22 AM
 
Babar Javaid

Joined: 1/14/2005
Posts: 142

Your script seems to be correct.

Please make sure that you have saved the script file in UTF-8 Format.

Just open your file in notepad and choose save as from there choose the format and save the file.

"Knowledge Sharing Matters!"
 
 Page 1 of 2
12Next 
Previous
 
Next
HomeHomeArchived Discus...Archived Discus...Developing Under Previous Versions of .NETDeveloping Under Previous Versions of .NETASP.Net 2.0ASP.Net 2.0SQL error when packaging the moduleSQL error when packaging the module


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