There are 2 problems I am facing in the execution of SqlDataProvider script execution during installation of my custom modules:
1) The scripts have statements that add Extended Properties to Schema objects. e.g. consider:
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the course being offered.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'II_Course', @level2type=N'COLUMN',@level2name=N'Name'
The problem is that when I try to change @level0name=N'dbo' in the above statement to @level0name=N'{databaseowner}', while executing the script, it translates into dbo..ObjectName. And Sql Server complains that this object does not exist in the schema. The problem I guess, are 2 dots in the translated object name, one added by DNN while parsing {objectowner}, and the other by Sql Server, while executing the query. Is there any way to avoid the above problem, or would I need to hardcode the DB owner in the above statement, which would render it practically useless when the owner is not dbo.
2) I am getting errors in the execution of the script. A sample error is as follows:
Start Sql execution: 01.00.00.SqlDataProvider file
Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Incorrect syntax near 'Message: '. 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.DNNSqlDataProvider.ExecuteADOScript(String SQL) in G:\Projects\DNN\SqlDataProvider.vb:line 143 at DotNetNuke.Data.DNNSqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) in G:\Projects\DNN\SqlDataProvider.vb:line 437 ---- Here there is a Stored Procedure Create statement that I have shown below: |
The stored procedure Create statement, where this error originates is as follows:
|
-- Create the stored procedure to generate an error using
-- RAISERROR. The original error information is used to
-- construct the msg_str for RAISERROR.
CREATE PROCEDURE {databaseOwner}{objectQualifier}[II_RethrowError] AS
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
--Message: in the above line is where the error is being showed
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
|
I could not see any problem with this statement. What's more, the script file runs pefectly fine, when its content is copied & run thorugh "Sql" option in Host Menu.
I get many errors of this sort, but only when this script executes as a part of Module installation process. Am I missing something???