SQL Error upgrading to latest version
Last Post 04-14-2010 04:43 AM by Thomas Born. 3 Replies.
|
|
Thomas Born
going with the flow Posts:78
|
04-13-2010 04:25 AM |
|
Hi;
I just purchased the latest version of the collection and tried to install DUD, however the install process fails with the error message below. Note I am running SQL Server 2005 and I have also checked the compatibilty level of the database which is set to 2005.
Can you pls advise how I can complete the upgrade!
(I am also still running 4.9.2)
Failure |
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Incorrect syntax near '@LimitResults'. Incorrect syntax near '@LimitResults'. 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) ALTER Procedure dbo.[dnn_DataSprings_RetrieveDNNUserProfileData3] (@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000)) AS BEGIN if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') and (@PortalID = 999) begin drop table dbo.tempDUDProfile end if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999) begin drop table dbo.tempDUDPivot end -- generate tempDUDProfile table -- create table tempDUDProfile if not exists If not exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') BEGIN select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue, P.propertyname, P.PortalID into dbo.tempDUDProfile from dbo.[dnn_Users] U inner join dbo.[dnn_userprofile] UP on U.userid = UP.userid inner join dbo.[dnn_profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from dbo.[dnn_UserPortals] where PortalID = Convert(varchar(3), @PortalID)) where P.Visible=1 --AND P.PortalID = @PortalID AND P.Deleted=0 AND P.PRopertyName <> 'FirstName' AND P.PropertyName <> 'LastName' AND P.PRopertyNAme <> 'E-Mail' AND P.PRopertyNAme <> 'Email' AND P.PRopertyNAme <> 'Username' AND P.PRopertyNAme <> 'Displayname' AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!' exec dbo.[dnn_DataSprings_RetrieveDNNUserProfileData3a] END If (@PortalID <> 999) BEGIN Declare @MySQL nvarchar(max) -- check if DNN version is 5.0 or higher if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' @LimitResults ' A.* from dbo.tempDUDPivot AS A' ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' replace(@Filter,'[','A.[') replace(@RoleFilter,'[','A.[') ' AND ((C.IsApproved = 1) OR (' Cast(@AuthOnly as varchar(2)) ' = 0))' ' AND A.PortalID = ' cast(@PortalID as varchar(3)) ' AND A.userid not in (select userid from dbo.[dnn_userportals] where isdeleted = 1 AND portalID = ' cast(@PortalID as varchar(3)) ')' EXECUTE(@MySQL) END else -- if DNN version is lower than 5.0 BEGIN Set @MySQL = 'select ' @LimitResults ' A.* from dbo.tempDUDPivot AS A' ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' replace(@Filter,'[','A.[') replace(@RoleFilter,'[','A.[') ' AND ((C.IsApproved = 1) OR (' Cast(@AuthOnly as varchar(2)) ' = 0))' ' AND A.PortalID = ' cast(@PortalID as varchar(3)) '' EXECUTE(@MySQL) END --PRINT(@MySQL) END END System.Data.SqlClient.SqlException: Incorrect syntax near ',['. Incorrect syntax near '@columns'. 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.[dnn_DataSprings_RetrieveDNNUserProfileData3a] AS BEGIN DECLARE @columns VARCHAR(8000) SELECT @columns = COALESCE(@columns ',[' cast(propertyname as varchar) ']', '[' cast(propertyname as varchar) ']') FROM dbo.tempDUDProfile GROUP BY propertyname DECLARE @query VARCHAR(8000) SET @query = ' SELECT * INTO dbo.tempDUDPIVOT FROM dbo.tempDUDProfile PIVOT ( MAX(propertyvalue) FOR [propertyname] IN (' @columns ') ) AS p ' EXECUTE(@query) END |
Failure |
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Incorrect syntax near '@LimitResults'. Incorrect syntax near '@LimitResults'. 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) ALTER Procedure dbo.[dnn_DataSprings_RetrieveDNNUserProfileData3] (@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000)) AS BEGIN if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') and (@PortalID = 999) begin drop table dbo.tempDUDProfile end if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999) begin drop table dbo.tempDUDPivot end -- generate tempDUDProfile table -- create table tempDUDProfile if not exists If not exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') BEGIN select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue, P.propertyname, P.PortalID into dbo.tempDUDProfile from dbo.[dnn_Users] U inner join dbo.[dnn_userprofile] UP on U.userid = UP.userid inner join dbo.[dnn_profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from dbo.[dnn_UserPortals] where PortalID = Convert(varchar(3), @PortalID)) where P.Visible=1 --AND P.PortalID = @PortalID AND P.Deleted=0 AND P.PRopertyName <> 'FirstName' AND P.PropertyName <> 'LastName' AND P.PRopertyNAme <> 'E-Mail' AND P.PRopertyNAme <> 'Email' AND P.PRopertyNAme <> 'Username' AND P.PRopertyNAme <> 'Displayname' AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!' exec dbo.[dnn_DataSprings_RetrieveDNNUserProfileData3a] END If (@PortalID <> 999) BEGIN Declare @MySQL nvarchar(max) -- check if DNN version is 5.0 or higher if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' @LimitResults ' A.* from dbo.tempDUDPivot AS A' ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' replace(@Filter,'[','A.[') replace(@RoleFilter,'[','A.[') ' AND ((C.IsApproved = 1) OR (' Cast(@AuthOnly as varchar(2)) ' = 0))' ' AND A.PortalID = ' cast(@PortalID as varchar(3)) ' AND A.userid not in (select userid from dbo.[dnn_userportals] where isdeleted = 1 AND portalID = ' cast(@PortalID as varchar(3)) ')' EXECUTE(@MySQL) END else -- if DNN version is lower than 5.0 BEGIN Set @MySQL = 'select ' @LimitResults ' A.* from dbo.tempDUDPivot AS A' ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' replace(@Filter,'[','A.[') replace(@RoleFilter,'[','A.[') ' AND ((C.IsApproved = 1) OR (' Cast(@AuthOnly as varchar(2)) ' = 0))' ' AND A.PortalID = ' cast(@PortalID as varchar(3)) '' EXECUTE(@MySQL) END --PRINT(@MySQL) END END System.Data.SqlClient.SqlException: Incorrect syntax near ',['. Incorrect syntax near '@columns'. 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.[dnn_DataSprings_RetrieveDNNUserProfileData3a] AS BEGIN DECLARE @columns VARCHAR(8000) SELECT @columns = COALESCE(@columns ',[' cast(propertyname as varchar) ']', '[' cast(propertyname as varchar) ']') FROM dbo.tempDUDProfile GROUP BY propertyname DECLARE @query VARCHAR(8000) SET @query = ' SELECT * INTO dbo.tempDUDPIVOT FROM dbo.tempDUDProfile PIVOT ( MAX(propertyvalue) FOR [propertyname] IN (' @columns ') ) AS p ' EXECUTE(@query) END |
Failure |
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Incorrect syntax near '@LimitResults'. Incorrect syntax near '@LimitResults'. 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) ALTER Procedure dbo.[dnn_DataSprings_RetrieveDNNUserProfileData3] (@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000)) AS BEGIN if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') and (@PortalID = 999) begin drop table dbo.tempDUDProfile end if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999) begin drop table dbo.tempDUDPivot end -- generate tempDUDProfile table -- create table tempDUDProfile if not exists If not exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') BEGIN select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue, P.propertyname, P.PortalID into dbo.tempDUDProfile from dbo.[dnn_Users] U inner join dbo.[dnn_userprofile] UP on U.userid = UP.userid inner join dbo.[dnn_profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from dbo.[dnn_UserPortals] where PortalID = Convert(varchar(3), @PortalID)) where P.Visible=1 --AND P.PortalID = @PortalID AND P.Deleted=0 AND P.PRopertyName <> 'FirstName' AND P.PropertyName <> 'LastName' AND P.PRopertyNAme <> 'E-Mail' AND P.PRopertyNAme <> 'Email' AND P.PRopertyNAme <> 'Username' AND P.PRopertyNAme <> 'Displayname' AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!' exec dbo.[dnn_DataSprings_RetrieveDNNUserProfileData3a] END If (@PortalID <> 999) BEGIN Declare @MySQL nvarchar(max) -- check if DNN version is 5.0 or higher if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' @LimitResults ' A.* from dbo.tempDUDPivot AS A' ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' replace(@Filter,'[','A.[') replace(@RoleFilter,'[','A.[') ' AND ((C.IsApproved = 1) OR (' Cast(@AuthOnly as varchar(2)) ' = 0))' ' AND A.PortalID = ' cast(@PortalID as varchar(3)) ' AND A.userid not in (select userid from dbo.[dnn_userportals] where isdeleted = 1 AND portalID = ' cast(@PortalID as varchar(3)) ')' EXECUTE(@MySQL) END else -- if DNN version is lower than 5.0 BEGIN Set @MySQL = 'select ' @LimitResults ' A.* from dbo.tempDUDPivot AS A' ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' replace(@Filter,'[','A.[') replace(@RoleFilter,'[','A.[') ' AND ((C.IsApproved = 1) OR (' Cast(@AuthOnly as varchar(2)) ' = 0))' ' AND A.PortalID = ' cast(@PortalID as varchar(3)) '' EXECUTE(@MySQL) END --PRINT(@MySQL) END END System.Data.SqlClient.SqlException: Incorrect syntax near ',['. Incorrect syntax near '@columns'. 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.[dnn_DataSprings_RetrieveDNNUserProfileData3a] AS BEGIN DECLARE @columns VARCHAR(8000) SELECT @columns = COALESCE(@columns ',[' cast(propertyname as varchar) ']', '[' cast(propertyname as varchar) ']') FROM dbo.tempDUDProfile GROUP BY propertyname DECLARE @query VARCHAR(8000) SET @query = ' SELECT * INTO dbo.tempDUDPIVOT FROM dbo.tempDUDProfile PIVOT ( MAX(propertyvalue) FOR [propertyname] IN (' @columns ') ) AS p ' EXECUTE(@query) END |
Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Incorrect syntax near '@LimitResults'. Incorrect syntax near '@LimitResults'. 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) ALTER Procedure dbo.[dnn_DataSprings_RetrieveDNNUserProfileData3] (@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000)) AS BEGIN if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') and (@PortalID = 999) begin drop table dbo.tempDUDProfile end if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999) begin drop table dbo.tempDUDPivot end -- generate tempDUDProfile table -- create table tempDUDProfile if not exists If not exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') BEGIN select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue, P.propertyname, P.PortalID into dbo.tempDUDProfile from dbo.[dnn_Users] U inner join dbo.[dnn_userprofile] UP on U.userid = UP.userid inner join dbo.[dnn_profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from dbo.[dnn_UserPortals] where PortalID = Convert(varchar(3), @PortalID)) where P.Visible=1 --AND P.PortalID = @PortalID AND P.Deleted=0 AND P.PRopertyName <> 'FirstName' AND P.PropertyName <> 'LastName' AND P.PRopertyNAme <> 'E-Mail' AND P.PRopertyNAme <> 'Email' AND P.PRopertyNAme <> 'Username' AND P.PRopertyNAme <> 'Displayname' AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!' exec dbo.[dnn_DataSprings_RetrieveDNNUserProfileData3a] END If (@PortalID <> 999) BEGIN Declare @MySQL nvarchar(max) -- check if DNN version is 5.0 or higher if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' @LimitResults ' A.* from dbo.tempDUDPivot AS A' ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' replace(@Filter,'[','A.[') replace(@RoleFilter,'[','A.[') ' AND ((C.IsApproved = 1) OR (' Cast(@AuthOnly as varchar(2)) ' = 0))' ' AND A.PortalID = ' cast(@PortalID as varchar(3)) ' AND A.userid not in (select userid from dbo.[dnn_userportals] where isdeleted = 1 AND portalID = ' cast(@PortalID as varchar(3)) ')' EXECUTE(@MySQL) END else -- if DNN version is lower than 5.0 BEGIN Set @MySQL = 'select ' @LimitResults ' A.* from dbo.tempDUDPivot AS A' ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' replace(@Filter,'[','A.[') replace(@RoleFilter,'[','A.[') ' AND ((C.IsApproved = 1) OR (' Cast(@AuthOnly as varchar(2)) ' = 0))' ' AND A.PortalID = ' cast(@PortalID as varchar(3)) '' EXECUTE(@MySQL) END --PRINT(@MySQL) END END System.Data.SqlClient.SqlException: Incorrect syntax near ',['. Incorrect syntax near '@columns'. 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.[dnn_DataSprings_RetrieveDNNUserProfileData3a] AS BEGIN DECLARE @columns VARCHAR(8000) SELECT @columns = COALESCE(@columns ',[' cast(propertyname as varchar) ']', '[' cast(propertyname as varchar) ']') FROM dbo.tempDUDProfile GROUP BY propertyname DECLARE @query VARCHAR(8000) SET @query = ' SELECT * INTO dbo.tempDUDPIVOT FROM dbo.tempDUDProfile PIVOT ( MAX(propertyvalue) FOR [propertyname] IN (' @columns ') ) AS p ' EXECUTE(@query) END
Thanks for your help
Thomas |
|
|
|
|
David To
river guide Posts:2719
|
04-13-2010 07:47 AM |
|
HI, can you go to Host / SQL and execute the following: declare @dbname nvarchar(200) set @dbname = (select db_name()) EXEC sp_dbcmptlevel @dbname, 90; Then execute the following: ALTER Procedure {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3] (@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000)) AS BEGIN if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') and (@PortalID = 999) begin drop table {databaseOwner}tempDUDProfile end if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999) begin drop table {databaseOwner}tempDUDPivot end -- generate tempDUDProfile table -- create table tempDUDProfile if not exists If not exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDProfile') BEGIN select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,UP.propertyvalue, P.propertyname, P.PortalID into {databaseOwner}tempDUDProfile from {databaseOwner}[{objectQualifier}Users] U inner join {databaseOwner}[{objectQualifier}userprofile] UP on U.userid = UP.userid inner join {databaseOwner}[{objectQualifier}profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from {databaseOwner}[{objectQualifier}UserPortals] where PortalID = Convert(varchar(3), @PortalID)) where P.Visible=1 --AND P.PortalID = @PortalID AND P.Deleted=0 AND P.PRopertyName <> 'FirstName' AND P.PropertyName <> 'LastName' AND P.PRopertyNAme <> 'E-Mail' AND P.PRopertyNAme <> 'Email' AND P.PRopertyNAme <> 'Username' AND P.PRopertyNAme <> 'Displayname' AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!' exec {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3a] END If (@PortalID <> 999) BEGIN Declare @MySQL nvarchar(max) -- check if DNN version is 5.0 or higher if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted') BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) + ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = ' + cast(@PortalID as varchar(3)) + ')' EXECUTE(@MySQL) END else -- if DNN version is lower than 5.0 BEGIN Set @MySQL = 'select ' + @LimitResults + ' A.* from {databaseOwner}tempDUDPivot AS A' + ' inner join aspnet_users AS B on B.username = A.username inner join aspnet_membership C on B.userid = C.userid ' + replace(@Filter,'[','A.[') + replace(@RoleFilter,'[','A.[') + ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))' + ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) + '' EXECUTE(@MySQL) END --PRINT(@MySQL) END END Then execute the following: CREATE Procedure {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3a] AS BEGIN DECLARE @columns VARCHAR(8000) SELECT @columns = COALESCE(@columns + ',[' + cast(propertyname as varchar) + ']', '[' + cast(propertyname as varchar)+ ']') FROM {databaseOwner}tempDUDProfile GROUP BY propertyname DECLARE @query VARCHAR(8000) SET @query = ' SELECT * INTO {databaseOwner}tempDUDPIVOT FROM {databaseOwner}tempDUDProfile PIVOT ( MAX(propertyvalue) FOR [propertyname] IN (' + @columns + ') ) AS p ' EXECUTE(@query) END Then go to Host / Site Settings and restart the application. This should fix you up. -- David |
|
|
|
|
Thomas Born
going with the flow Posts:78
|
04-13-2010 02:38 PM |
|
Thanks David, this fixed the problem. Another question, I just test the upgrade on a new platform (all is part of upgrading DNN as well). I found another weird problem compared to the previous version. The first link below is the updated site and the second link our current live site, when you compare them you will see that the description in the listing on the upgraded site is longer than on the original site. It has the same data behind them, it just seems in the old version the number of characters displayed was limited. Can this be achieved in the new version of DUD? http://www64.clickalifecoach.com/Se...fault.aspx http://www.clickalifecoach.com/Sear...fault.aspx Regards Thomas |
|
|
|
|
Thomas Born
going with the flow Posts:78
|
04-14-2010 04:43 AM |
|
Sorry the top link www64 is currently not working. I am in the process of doing a test upgrade of DNN and cannot access the site. I am sure you know what I mean though by the post above. FYI the field that is displayed in the list allows more characters than displayed on the site, but for some reason version 2.5 cut if off after a certain number of characters and the new version doesn't |
|
|
|
|
|