Great Ideas. Always Flowing.

We are not happy until you are happy. Client satisfaction guaranteed. Whatever your needs and requirements, we have the skills and resources for the job!

Quick login...


Or... now make it easy with Facebook Integration
Connect via Facebook



Top Sellers

Frustrated over the lack of customization for your user's registration fields? Dynamically setup your DNN Portal with custom registration fields, layout, questions, and other core integration options......

Ultra Video Gallery is a brother product of Ultra Media Gallery, UVG allows you to upload videos in various format and automatically encode them to flv or H264 format, you also can add videos from internet or record live videos from your webcam.

Build high performance, completely customizable data-entry forms and views driven by your DNN and external databases. New built-in tools make it a snap to quickly create data entry forms, data views, and even database tables. Plus, add your own HTML, CSS, Javascript, SQL commands, stored procedures,

The most advanced DotNetNuke shopping cart on the planet. Easy to use e-Commerce, Secure Shopping Cart Software and SEO friendly. B2C / B2B Ecommerce Sites.

One stop solution for events calendar and events registration! FREE DOWNLOAD is available now!

SQL Error upgrading to latest version
Last Post 04-14-2010 04:43 AM by Thomas Born. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Thomas BornUser is Offline
going with the flow
going with the flow
Posts:78
Avatar

--
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 ToUser is Offline
    river guide
    river guide
    Posts:2719
    Avatar

    --
    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 BornUser is Offline
    going with the flow
    going with the flow
    Posts:78
    Avatar

    --
    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 BornUser is Offline
    going with the flow
    going with the flow
    Posts:78
    Avatar

    --
    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
    You are not authorized to post a reply.


     
     

    Join our mailing list...

    Get current news and events the easy way
     
     
       
    Subscribe Me

    Recent Blogs...

     
    Copyright 2005 - 2011 by Data Springs, Inc.
     
  • film izle
  • 720 izle
  • film
  • sinema izle
  • film makinesi
  • T�rk�e dublaj film
  • film izle
  • film izle
  • baglan film izle
  • sinema izle
  • 1080 film izle
  • film mercegi