Hi,
I am getting a weird error message when i try to install my custom module to my 04.06.02 installation.
In "module definitons" i click the "install new module" then I browse for the zip file and choose "Install New Module".
Everythings working fine except the "SQL Execution".
The Error is: (commands causing errors are underlined by me)
| StartJob |
Begin Sql execution |
| Info |
Executing 01.00.00.SqlDataProvider |
| StartJob |
Start Sql execution: 01.00.00.SqlDataProvider file |
| Failure |
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Line 11: Incorrect syntax near '...'. Line 12: Incorrect syntax near ')'. Incorrect syntax near the keyword 'AS'. 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.[RLicencer_ResellerCustomers_GetCustomersByReseller] ( @ResellerUserID int ) AS SELECT RC.ResellerUserID, RC.CustomerUserID, U.UserName AS CustomerUserName, (SELECT CASE WHEN LEN(PropertyValue) <= 20 THEN PropertyValue WHEN LEN(PropertyValue) > 20 THEN SUBSTRING(PropertyValue,1,20)'...' END FROM dbo.[UserProfile] UP WHERE (UP.UserID = U.UserID) AND (PropertyDefinitionID = (SELECT PropertyDefinitionID FROM dbo.[ProfilePropertyDefinition] WHERE (PortalID = 0) AND (PropertyName = 'CompanyName')))) AS CustomerCompanyName, (U.FirstName ' ' U.LastName) AS CustomerFullUserName, U.Email AS CustomerEMail, (SELECT PropertyValue FROM dbo.[UserProfile] UP WHERE (UP.UserID = U.UserID) AND (PropertyDefinitionID = (SELECT PropertyDefinitionID FROM dbo.[ProfilePropertyDefinition] WHERE (PortalID = 0) AND (PropertyName = 'Telephone')))) AS CustomerTelephone FROM dbo.[Users] U RIGHT OUTER JOIN dbo.[RLicencer_ResellerCustomers] RC ON U.UserID = RC.CustomerUserID WHERE (RC.ResellerUserID = @ResellerUserID) ORDER BY U.UserName RETURN |
| EndJob |
End Sql execution: 01.00.00.SqlDataProvider file |
| |
| EndJob |
Finished Sql execution |
however, SUBSTRING(PropertyValue,1,20)'...' is not equal to the text in the "01.00.00.SqlDataProvider" file. Content of file is:
CREATE PROCEDURE {databaseOwner}[{objectQualifier}RLicencer_ResellerCustomers_GetCustomersByReseller]
(
@ResellerUserID int
)
AS
SELECT RC.ResellerUserID, RC.CustomerUserID,
U.UserName AS CustomerUserName,
(SELECT CASE WHEN LEN(PropertyValue) <= 20 THEN PropertyValue
WHEN LEN(PropertyValue) > 20 THEN SUBSTRING(PropertyValue,1,20)+'...' END
FROM {databaseOwner}[{objectQualifier}UserProfile] UP WHERE (UP.UserID = U.UserID) AND (PropertyDefinitionID = (SELECT PropertyDefinitionID FROM {databaseOwner}[{objectQualifier}ProfilePropertyDefinition] WHERE (PortalID = 0) AND (PropertyName = 'CompanyName')))) AS CustomerCompanyName,
(U.FirstName + ' ' + U.LastName) AS CustomerFullUserName,
U.Email AS CustomerEMail,
(SELECT PropertyValue FROM {databaseOwner}[{objectQualifier}UserProfile] UP WHERE (UP.UserID = U.UserID) AND (PropertyDefinitionID = (SELECT PropertyDefinitionID FROM {databaseOwner}[{objectQualifier}ProfilePropertyDefinition] WHERE (PortalID = 0) AND (PropertyName = 'Telephone')))) AS CustomerTelephone
FROM {databaseOwner}[{objectQualifier}Users] U RIGHT OUTER JOIN {databaseOwner}[{objectQualifier}RLicencer_ResellerCustomers] RC ON U.UserID = RC.CustomerUserID
WHERE (RC.ResellerUserID = @ResellerUserID)
ORDER BY U.UserName
RETURN
as you may noticed, there is a + (plus) sign between SUBSTRING(PropertyValue,1,20) and '...' but module install engine does not see it.
What may be the problem?