| Author | Messages | |
r_honey
Regular User
Posts:73
 | | 06/02/2008 10:49 AM |
| 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??? | | | |
| Mitchel Sellers
Site Admin/Owner
Guru
Posts:5607
| | 06/02/2008 11:20 AM |
| There error is actually due to the single quote.
WHen saving the .SqlDataProvider file, be sure to save it in UTF-8 format! | |
-Mitchel Sellers
MCITP, MCPD, MCTS
Director of Development
IowaComputerGurus Inc.
3Essentials is my recommended Shared Hosting Provider
This site is hosted on a VPS from HostMySIte.com
Mosso is my recommended cloud computing provider. Use reference code REF-ICG to get $100 off your second month!
To get Guaranteed DNN Support check out our affordable DNN Technical Support Programs | |
| r_honey
Regular User
Posts:73
| | 06/03/2008 5:17 AM |
| Posted By Mitchel Sellers on 06/02/2008 11:20 AM
WHen saving the .SqlDataProvider file, be sure to save it in UTF-8 format!
What code are you poitning to Mitch with the above remark???
And Thanx again Man!!! Yes, the SqlDataProvider needs to be i UTF-8 format to be parsed correctly by DNN. So, my second problem is solved. But the first one remain. How can I have Extended Properties in my SqlDataProvider files, without hard-coding the Database owner??? | | | |
| r_honey
Regular User
Posts:73
| | 06/03/2008 5:18 AM |
| I was pointing to your remark about "Missing Quote". Mitch, you should enable your members to be able to edit their own posts. Just a suggestion. | | | |
| Mitchel Sellers
Site Admin/Owner
Guru
Posts:5607
| | 06/05/2008 12:29 AM |
| Inside your SQL script you are using the ' character. Once saved as UTF-8, your odd problem with the dynamic loaded database owner should go away.
As for editing in the forum, I'll have to work on editing that. | |
-Mitchel Sellers
MCITP, MCPD, MCTS
Director of Development
IowaComputerGurus Inc.
3Essentials is my recommended Shared Hosting Provider
This site is hosted on a VPS from HostMySIte.com
Mosso is my recommended cloud computing provider. Use reference code REF-ICG to get $100 off your second month!
To get Guaranteed DNN Support check out our affordable DNN Technical Support Programs | |
| r_honey
Regular User
Posts:73
| | 06/05/2008 3:05 AM |
| No benefit Mich. After saving in UTF-8, as I said the second problem went away, but the first one persisted. And I reason could also be seen. After parsing of DataProvider by DNN, DNN replaces {databaseowner} with say "dbo.". So, level0name now contains "dbo." That . is where the problem lies. Now, Sql Server combines this name with the object name to end up as dbo..Object (where the second dot has been inserted by Sql Server). And then, it can not find any object with this name, as is obvious!!! | | | |
| Mitchel Sellers
Site Admin/Owner
Guru
Posts:5607
| | 06/06/2008 8:23 AM |
| Ah yes...now I see it..
This is due to the way that DNN puts in the object qualifer...you might be able to do something like the following...it isn't elegant, but I believe it is valid
@level0name=SUBSTRING(N'{databaseowner}', 0, LEN('N'{databaseowner}') -1)
This will trim off the . that is always there with DNN | |
-Mitchel Sellers
MCITP, MCPD, MCTS
Director of Development
IowaComputerGurus Inc.
3Essentials is my recommended Shared Hosting Provider
This site is hosted on a VPS from HostMySIte.com
Mosso is my recommended cloud computing provider. Use reference code REF-ICG to get $100 off your second month!
To get Guaranteed DNN Support check out our affordable DNN Technical Support Programs | |
| r_honey
Regular User
Posts:73
| | 06/06/2008 10:33 AM |
| Your solution did not work directly, although a work-around to it did. You cannot speicfy SQL function or anything like that in a call to a SP. The only things allowed are simple variables (but not even CAST(@var as nvarchar)) or literals. So, I used the following code to achieve the same effect: DECLARE @owner nvarchar(MAX);
set @owner = SUBSTRING(N'{databaseowner}', 0, LEN(N'{databaseowner}') -1)
@level0name=@owner This way it worked. Thanx for all your replies!!! | | | |
|
|