D2K
Posts:7
 |
| 02-04-2010 11:13 AM |
|
I am trying to install User Directory from the Data Springs Collection 3.0 on DNN 04.09.03.
It blows up and I get this error... Any Suggestion on how to fix?
|
StartJob
|
Start Sql execution: 02.40.20.SqlDataProvider file
|
|
Failure
|
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Line 37: Incorrect syntax near 'Max'. Must declare the variable '@SQLToExecute'. Must declare the variable '@SQLToExecute'. at System.Data.SqlClient.SqlConnection.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.[DataSprings_RetrieveDNNUserProfileData3] @PortalID int, @RoleFilter nvarchar(1000), @LimitResults nvarchar(1000) AS Declare curDS Cursor FOR SELECT PropertyName, PropertyDefinitionID FROM dbo.[ProfilePropertyDefinition] where Visible=1 AND PortalID = @PortalID AND Deleted=0 AND PRopertyName <> 'FirstName' AND PropertyName <> 'LastName' AND PRopertyNAme <> 'E-Mail' AND PRopertyNAme <> 'Email' AND PRopertyNAme <> 'Username' AND PropertyNAme Not Like '%.%' AND PropertyNAme Not Like '%-%' --AND PropertyNAme Not Like '%/%' AND PropertyNAme Not Like '%#%' ORDER BY PropertyCategory, Vieworder FOR READ ONLY; Declare @PropertyName VarChar(500); Declare @PropertyDefinitionID Integer; Declare @SQLToUse VarChar(8000); Declare @SQLToUseJoin VarChar(8000); Declare @SQLToExecute nVarChar(Max); Declare @Alias VarChar(10); set @PropertyName = ''; set @SQLToUse = ''; set @SQLToUseJoin = ''; OPEN curDS; FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID; WHILE @@FETCH_STATUS = 0 BEGIN if DataLength(@SQLToUse) = 0 BEGIN set @SQLToUse = 'select ' + @LimitResults + ' UserID,Username,FirstName, LastName, Email, ' set @SQLToUseJoin = 'dbo.DSGetProfileElementByID(userID,''' + Convert(varchar(4), @PropertyDefinitionID) + ''') as [' + REPLACE(@PropertyName, '/', '') + ']' END ELSE BEGIN set @SQLToUseJoin = @SQLToUseJoin + ',dbo.DSGetProfileElementByID(userID,''' + Convert(varchar(4), @PropertyDefinitionID) + ''') as [' + REPLACE(@PropertyName, '/', '') + ']' END; FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID; END CLOSE CurDS; DEALLOCATE CurDS; set @SQLToUseJoin = @SQLToUseJoin + ' from dbo.[Users] Where UserID In (Select UserID from dbo.[UserPortals] where PortalID = ' + Convert(varchar(2), @PortalID) + ')' Set @SQLToExecute = 'Select * from (' + Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin) + ') AS A ' + Convert(varchar(8000), @RoleFilter) --print @SQLToExecute --EXEC(@SQLToExecute); --print @SQLToExecute; exec sp_executesql @SQLToExecute
|
|
EndJob
|
End Sql execution: 02.40.20.SqlDataProvider file
|
|
|
|
Info
|
Executing 02.50.00.SqlDataProvider
|
|
StartJob
|
Start Sql execution: 02.50.00.SqlDataProvider file
|
|
Failure
|
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Line 48: Incorrect syntax near 'Max'. Must declare the variable '@SQLToExecute'. Must declare the variable '@SQLToExecute'. at System.Data.SqlClient.SqlConnection.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.[DataSprings_RetrieveDNNUserProfileData3] @PortalID int, @RoleFilter nvarchar(1000), @LimitResults nvarchar(1000), @Authonly int AS Declare curDS Cursor FOR SELECT PropertyName, PropertyDefinitionID FROM dbo.[ProfilePropertyDefinition] where Visible=1 AND PortalID = @PortalID AND Deleted=0 AND PRopertyName <> 'FirstName' AND PropertyName <> 'LastName' AND PRopertyNAme <> 'E-Mail' AND PRopertyNAme <> 'Email' AND PRopertyNAme <> 'Username' AND PropertyNAme Not Like '%.%' AND PropertyNAme Not Like '%-%' --AND PropertyNAme Not Like '%/%' AND PropertyNAme Not Like '%#%' AND PropertyNAme Not Like '%)%' AND PropertyNAme Not Like '%(%' AND PropertyNAme Not Like '%^%' AND PropertyNAme Not Like '%&%' AND PropertyNAme Not Like '%[%' AND PropertyNAme Not Like '%]%' AND PropertyNAme Not Like '%@%' ORDER BY PropertyCategory, Vieworder FOR READ ONLY; Declare @PropertyName VarChar(500); Declare @PropertyDefinitionID Integer; Declare @SQLToUse VarChar(8000); Declare @SQLToUseJoin VarChar(8000); Declare @SQLToExecute nVarChar(Max); Declare @Alias VarChar(10); set @PropertyName = ''; set @SQLToUse = ''; set @SQLToUseJoin = ''; OPEN curDS; FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID; WHILE @@FETCH_STATUS = 0 BEGIN if DataLength(@SQLToUse) = 0 BEGIN set @SQLToUse = 'select ' + @LimitResults + ' U.UserID,U.Username,FirstName, LastName, U.Email, ' set @SQLToUseJoin = 'dbo.DSGetProfileElementByID(U.userID,''' + Convert(varchar(4), @PropertyDefinitionID) + ''') as [' + REPLACE(@PropertyName, '/', '') + ']' END ELSE BEGIN set @SQLToUseJoin = @SQLToUseJoin + ',dbo.DSGetProfileElementByID(U.userID,''' + Convert(varchar(4), @PropertyDefinitionID) + ''') as [' + REPLACE(@PropertyName, '/', '') + ']' END; FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID; END CLOSE CurDS; DEALLOCATE CurDS; set @SQLToUseJoin = @SQLToUseJoin + ' from dbo.[Users] U Inner Join dbo.[aspnet_users] as au on au.Username = U.UserName Inner Join dbo.[aspnet_membership] am on au.UserID = am.UserID WHERE (am.IsApproved =1 OR ' + Convert(varchar(2), @AuthOnly) + ' = 0) AND U.UserID In (Select UserID from dbo.[UserPortals] where PortalID = ' + Convert(varchar(2), @PortalID) + ')' Set @SQLToExecute = 'Select * from (' + Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin) + ') AS A ' + Convert(varchar(8000), @RoleFilter) --print @SQLToExecute --EXEC(@SQLToExecute); --print @SQLToExecute; exec sp_executesql @SQLToExecute
|
|
|
|
|
|
David To
river guide
Posts:2719
|
| 02-04-2010 01:12 PM |
|
Hi, are you running SQL Server 2000? -- David |
|
|
|
|
Dan Stauffer
going with the flow
Posts:68
|
| 04-03-2010 03:37 PM |
|
Any resolution on this?
I have a similar error installing on SQL Server 2005; DNN 4.9.4.
Using: 15668_10351_DataSprings_DynamicUserDirectory_30_0_DNN45PLUS_Enterprise
StartJob Start Sql execution: 02.50.60.SqlDataProvider file
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.[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.[Users] U inner join dbo.[userprofile] UP on U.userid = UP.userid inner join dbo.[profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from dbo.[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.[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.[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.[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 System.Data.SqlClient.SqlException: Invalid column name 'GO'. 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.[DataSprings_DynamicUserDirectory_AddField] @ID varchar(500), @FieldID varchar(500), @SearchLabel nvarchar(500), @DefaultValue varchar(50), @Condition varchar(15), @Searchable bit, @PreselectCriteriaID int, @InputType int, @InputSQL text, @SortOrder varchar(2), @DNNField bit, @DNNFieldName nvarchar(100), @ModuleID int, @PortalID int, @RequiredField bit, @RequiredFieldText nvarchar(200), @RequiredFieldErrorMessage nvarchar(1000) AS BEGIN if not exists(select ID from dbo.[DataSprings_DynamicUserDirectory_SearchFields] ds where ds.id=@ID) BEGIN insert into dbo.[DataSprings_DynamicUserDirectory_SearchFields] (FieldID,SearchLabel, DefaultValue,Condition,Searchable,Preselect,InputType, InputSQL, SortOrder, DNNField, DNNFieldName, ModuleID, PortalID, RequiredField, RequiredFieldText, RequiredFieldErrorMessage) values (@FieldID,@SearchLabel, @DefaultValue,@Condition,@Searchable,@PreselectCriteriaID,@InputType, @InputSQL, @SortOrder, @DNNField, @DNNFieldName, @ModuleID, @PortalID, @RequiredField, @RequiredFieldText, @RequiredFieldErrorMessage) --declare @insertedItemId int --select @insertedItemId = SCOPE_IDENTITY() Select SCOPE_IDENTITY() RETURN END ELSE BEGIN update dbo.[DataSprings_DynamicUserDirectory_SearchFields] set FieldID=@FieldID, SearchLabel=@SearchLabel,DefaultValue=@DefaultValue, Condition=@Condition,Searchable=@Searchable, Preselect=@PreselectCriteriaID, InputType = @InputType, InputSQL = @InputSQL, SortOrder=@SortOrder, DNNField = @DNNField, DNNFieldName = @DNNFieldName, ModuleID = @ModuleID, PortalID = @PortalID, RequiredField = @RequiredField, RequiredFieldText = @RequiredFieldText, RequiredFieldErrorMessage = @RequiredFieldErrorMessage WHERE ID=@ID Select @ID Return END END Return GO
EndJob End Sql execution: 02.50.60.SqlDataProvider file |
|
|
|
|
David To
river guide
Posts:2719
|
| 04-05-2010 07:44 AM |
|
HI, DUD requires the database to be set in SQL 2005 compatibility mode. So even if you're running SQL 2005, the database may be set in SQL 2000 mode. Please go to host / sql and execute the following:
declare @dbname nvarchar(200)
set @dbname = (select db_name())
EXEC sp_dbcmptlevel @dbname, 90;
Then do 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
GO
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
After wards, go to Host / Host Settings and click on "Restart Application". Thank you for your attention. -- David |
|
|
|
|
Dan Stauffer
going with the flow
Posts:68
|
| 04-05-2010 01:47 PM |
|
Thanks, David.
The first part went fine. The seconpart returned "There is an error in your query!"
Do I need to make changes to it to work?
I iterpreted your instructions as only 2 parts. Is that correct?
Do I need to uninstall DUD first?
Thanks, Dan |
|
|
|
|
David To
river guide
Posts:2719
|
| 04-06-2010 07:01 AM |
|
HI, no you do not need to uninstall DUD. On the 2nd part, when you execute the SQL, make sure to click on the checkbox "Execute as script". That way, it will tell you what the error is. Can you state the error here? It might be as simple as changing the "ALTER" to "CREATE" because I don't think it installed the stored procedure yet. -- David |
|
|
|
|
Dan Stauffer
going with the flow
Posts:68
|
| 04-06-2010 12:38 PM |
|
System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'CREATE'. Incorrect syntax near 'END'. 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.[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.[Users] U inner join dbo.[userprofile] UP on U.userid = UP.userid inner join dbo.[profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from dbo.[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.[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.[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 GO CREATE Procedure dbo.[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 |
|
|
|
|
David To
river guide
Posts:2719
|
| 04-06-2010 02:06 PM |
|
HI, is this a site we can have host access to? If so, can you use the Contact Us and shoot us an email, referencing this forum with the HOST login and the URL/TABID of your website? Thanks. -- David |
|
|
|
|
Dan Stauffer
going with the flow
Posts:68
|
| 04-06-2010 03:29 PM |
|
David,
Thanks. I sent the host information.
FYI ... I don't know if it matters. I ran the script contained in the module readme:
Instructions for Installing Dynamic User Directory 2.60.00 (I did this after a failed install attempt.)
This version uses a faster search method with the "PIVOT" SQL command which is only supported in SQL 2005 and databases.
To make sure your database is set for SQL 2000 compatability mode, please do the following before installing the program:
1) go to HOST / SQL
2) type in: sp_spaceused
3) this will return your database name
4) type in: EXEC sp_dbcmptlevel database_name, 90;
replacing database_name with your database_name.
Let me know if you need anything from me.
Thanks, Dan |
|
|
|
|
David To
river guide
Posts:2719
|
| 04-07-2010 07:23 AM |
|
HI Dan, you should be fine now. -- David |
|
|
|
|
ejbrewer
skipping stones
Posts:7
|
| 06-18-2010 01:24 PM |
|
I cant get this to install either
System.Data.SqlClient.SqlException: Invalid object name 'dbo.DataSprings_RetrieveDNNUserProfileData3'. 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.[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.[Users] U inner join dbo.[userprofile] UP on U.userid = UP.userid inner join dbo.[profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid --where U.userid In (Select userid from dbo.[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.[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.[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: There is already an object named 'DataSprings_RetrieveDNNUserProfileData3a' in the database. 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.[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 |
|
|
|
|
Candace
river guide
Posts:2431
|
| 06-23-2010 02:42 PM |
|
Hi ej,
Just wanted to verify that this is the same case that you and Chad are working on, right? Or is this is separate issue?
Candace |
|
|
|
|
Mip
going with the flow
Posts:37
|
| 08-03-2010 06:46 AM |
|
I dont know if this is similar or if i should be posting this somewhere else but here goes;
I tried installing DUD 3 on IIS7, SQL2008, DNN 5.04.04 and received this Warning:
Info Created - 02.50.60.SqlDataProvider
Info Executing 02.50.60.SqlDataProvider
Info Start Sql execution: 02.50.60.SqlDataProvider file
Warning SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Procedure or function 'AddSchedule' expects parameter '@FriendlyName', which was not supplied. 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) -- ADD DNN SCHEDULER ITEM if not exists (select TypeFullName from schedule where TypeFullName = 'DataSprings.DNN.Modules.DynamicUserDirectory.DUDSchedulerClient, DataSprings.DNN.Modules.DynamicUserDirectory') if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Schedule' and COLUMN_NAME = 'CreatedByUserID') execute AddSchedule 'DataSprings.DNN.Modules.DynamicUserDirectory.DUDSchedulerClient, DataSprings.DNN.Modules.DynamicUserDirectory',60,'m',30,'m',25,'',1,1,'', NULL,1 else execute AddSchedule 'DataSprings.DNN.Modules.DynamicUserDirectory.DUDSchedulerClient, DataSprings.DNN.Modules.DynamicUserDirectory',60,'m',30,'m',25,'',1,1,'', NULL
Info End Sql execution: 02.50.60.SqlDataProvider file
Info Created - Uninstall.SqlDataProvide |
|
|
|
|
Candace
river guide
Posts:2431
|
| 08-03-2010 06:57 AM |
|
Hi Mike,
It looks like the DUD scheduler is not getting added with DNN 5.4. There is an easy fix, though. In Host, SQL, please run this:
execute AddSchedule 'DataSprings.DNN.Modules.DynamicUserDirectory.DUDSchedulerClient, DataSprings.DNN.Modules.DynamicUserDirectory',60,'m',30,'m',25,'',1,1,'', NULL,0, 'Dynamic User Directory'
Thanks!
Candace |
|
|
|
|
Mip
going with the flow
Posts:37
|
| 08-03-2010 09:16 AM |
|
Ok Thanks,
I executed in the Host\SQL Connection: LocalMySqlServer and received a response back=
I installed again and no errors.
Thank you for the prompt response.
I greatly appreciate it.
Mike |
|
|
|
|
Mip
going with the flow
Posts:37
|
| 08-03-2010 01:34 PM |
|
I rebuilt my DNN site and installed a fresh copy of DNN5.4.4 then tried to run the SQL command you suggested and received an error. This was run on the same server setup.
execute AddSchedule 'DataSprings.DNN.Modules.DynamicUserDirectory.DUDSchedulerClient, DataSprings.DNN.Modules.DynamicUserDirectory',60,'m',30,'m',25,'',1,1,'', NULL,0, 'Dynamic User Directory'
execute:
There is an error in your query!
tried to install DUD 3 and still getting same error? |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 08-03-2010 02:11 PM |
|
Hello Mike,
Can you try the query again but like so:
execute {objectQualifier}AddSchedule 'DataSprings.DNN.Modules.DynamicUserDirectory.DUDSchedulerClient, DataSprings.DNN.Modules.DynamicUserDirectory',60,'m',30,'m',25,'',1,1,'', NULL,0, 'Dynamic User Directory'
This should help you.
Thanks,
Ryan |
|
|
|
|
Candace
river guide
Posts:2431
|
|
Mip
going with the flow
Posts:37
|
| 08-31-2010 08:43 AM |
|
Thanks,
Mike |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-03-2010 09:50 AM |
|
Hello Mike,
Please let us know if everything is working for you, just to follow up.
If you have any further questions please let us know.
Thanks,
Ryan |
|
|
|
|
Mip
going with the flow
Posts:37
|
| 09-16-2010 08:09 AM |
|
Thanks Ryan,
Everything with the Install is working perfectly.
Thanks,
Mike |
|
|
|
|
Ryan Bakerink
river guide
Posts:1900
|
| 09-16-2010 08:39 AM |
|
Hello Mip,
I'm glad to hear everything is working great for you.
If you ever have any questions please let us know.
Thanks,
Ryan |
|
|
|
|