Cannot execute MSSQL script to create many stored procedures

[ptzedakis]

Hi,

I have created an installer where I use dotConnect Universal 3.20.50 to execute a script file with stored procedure create statements. If the script contains just one CREATE PROCEDURE statement everything works find. However, if the script contains MORE THAN ONE stored procedure CREATE statements, I always get the following error (pls find the total script contents after the error).

The MSSQL script file is syntactically correct, so there must be an issue with dotConnect Universal.

Can anyone help ?

Event Type: Error
Event Source: MsiInstaller
Event Category: None
Event ID: 11001
Date: 24/5/2011
Time: 12:44:08 μμ
User: HPNW9440\Administrator
Computer: HPNW9440
Description:
The description for Event ID ( 11001 ) in Source ( MsiInstaller ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Product: NetHabilis DesktopSecure -- Error 1001. Error 1001. Within CreateDB(), a Database Exception occurred:

Message: Incorrect syntax near the keyword 'PROCEDURE'.
Incorrect syntax near the keyword 'PROCEDURE'.
Incorrect syntax near the keyword 'PROCEDURE'.
Must declare the scalar variable "@ApplMessage".

StackTrace: at Devart.Data.Universal.UniCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3, Boolean A_4)
at Devart.Data.Universal.UniCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at NHDTSECUDBCustomAction.DeployInstaller.ExecuteSql(String DatabaseName, String SQL)
at NHDTSECUDBCustomAction.DeployInstaller.CreateDB(SupportedDatabases SelectedDB, String DBAddress, String DBPort, String DBUsername, String DBPassword) --> Incorrect syntax near the keyword 'PROCEDURE'.
Incorrect syntax near the keyword 'PROCEDURE'.
Incorrect syntax near the keyword 'PROCEDURE'.
Must declare the scalar variable "@ApplMessage". --> Incorrect syntax near the keyword 'PROCEDURE'.
Incorrect syntax near the keyword 'PROCEDURE'.
Incorrect syntax near the keyword 'PROCEDURE'.
Must declare the scalar variable "@ApplMessage".; (NULL); (NULL); (NULL).
Data:
0000: 7b 33 33 34 36 45 35 32 {3346E52
0008: 36 2d 41 34 34 44 2d 34 6-A44D-4
0010: 31 31 42 2d 41 42 36 42 11B-AB6B
0018: 2d 32 36 39 35 33 38 36 -2695386
0020: 33 41 42 38 36 7d 3AB86}


### MS SQL STORED PROCEDURE CREATE STATEMENTS ###

CREATE PROCEDURE [dbo].[stp_Insert_UserAudit]
(
@UserID NVARCHAR(64),
@WorkstationID NVARCHAR(256),
@AgreedToPolicy BIT
)
AS

INSERT INTO
tb_UserAudits (uaUserID, uaWorkstationID, uaAgreedToPolicy)
VALUES (@UserID, @WorkstationID, @AgreedToPolicy)

;


CREATE PROCEDURE [dbo].[stp_RetrieveUserAudits]
AS

SELECT
uaUserID as [Login],
uaWorkstationID as [Workstation],
uaAgreedToPolicy as [Policy Agreement],
uaRecordedAt as [Recorded Date]
FROM
tb_UserAudits
ORDER BY
uaRecordedAt DESC

;



CREATE PROCEDURE [dbo].[stp_RetrieveAppMessages]
AS

SELECT
uaUserID as [Login],
uaWorkstationID as [Workstation],
amApplMessage as [Application Message],
amExcType as [Exception],
amExcMessage as [Exception Message],
amExcStackTrace as [Exception StackTrace],
amRecordedAt as [Recorded Date],
amHandled as [Handled]
FROM
tb_AppMessages
WHERE
amHandled = 0
ORDER BY
amRecordedAt DESC

;



CREATE PROCEDURE [dbo].[stp_Insert_AppMessage]
(
@UserID NVARCHAR(64), @WorkstationID NVARCHAR(256), @ApplMessage NVARCHAR(1024), @ExcType NVARCHAR(128), @ExcMessage NVARCHAR(1024), @ExcStackTrace NVARCHAR(4000)
)
AS

INSERT INTO
tb_AppMessages (uaUserID, uaWorkstationID, amApplMessage, amExcType, amExcMessage, amExcStackTrace)
VALUES (@UserID, @WorkstationID, @ApplMessage, @ExcType, @ExcMessage, @ExcStackTrace)

;

[Shalex]

Please put the GO command after every CREATE PROCEDURE statement: http://msdn.microsoft.com/en-us/library/ms188037.aspx.

[ptzedakis]

Hello,

Your suggestion is not accurate, as I keep getting error "Incorrect syntax near 'GO'. The GO statement is only recognised by the sqlcmd and osql utilities and the MS SQL Management Studio Code Editor, as it is clearly mentioned in the article you referred me to. Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server.

If I execute my script within MS SQL MANAGEMENT STUDIO, using the GO command at the end of each CREATE PROCEDURE STATEMENT everything works fine. If I execute the same script using a DML UniCommand, it always fails with the error I mentioned in my last e-mail.

EDIT: UPDATE -> The UniScript component fails to execute the script as well, with or without the GO statement.

Your examples do not show how to execute a script with at least stored procedures for MS SQL Server which would be quite useful.

Please help.

[Shalex]

You are right concerning the GO command within MS SQL MANAGEMENT STUDIO.

Please put a slash symbol "/" after each CREATE PROCEDURE statement when using the UniScript component with SQL Server. Notify us about the results.

[ptzedakis]

Using UniScript, it now works if I put a / after each CREATE PROCEDURE statement.

Thank you.