Page 2 Problems: Invalid object name - Forum

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

Home All Forums

Problems: Invalid object name

9/8/2005 1:54 AM

Patrick Timmons

Joined: 10/15/2004

Posts: 15

Re: Problems: Invalid object name Modified By Patrick Timmons on 9/8/2005 2:49:58 PM

[EDIT: I am currently using version Forum/Blog (3.10.01) ]

I think I have found the culprit for the Invalid Object Name when using 2 separate connection strings, an object qualifier, and a custom dbo as above.

At first, it seemed to occur only when the stored procedure involved is creating a #temp table (Forum_ThreadGetAll for example). However, upon further investigation, it seems that every time a SQL string is created, then EXECuted, it will fail. (The EXEC runs under a new context of the user that was connected, not the Stored Procedure, so granting Execute right on the Stored Procedure is not enough)

So, the solution is to either remove/rewrite all the EXEC(sql) statements, or change how they reference the objects. You will also need to adjust the security permissions on some table to allow the connect user to SELECT from that table.

The following Stored Procedures need to be changed:

Forum_BlogGetAll
Forum_BlogGetCount
Forum_BlogGetArchive
Forum_BlogGetByCategory
Forum_BlogGetByModule
Forum_BlogGetBySubCategory
Forum_BlogSearch
Forum_ThreadGetAll
Forum_ThreadGetCount
Forum_UserGetAll
Forum_SearchGetResults
Forum_WhatsNewGetAll

SELECT permission for the low-level permission connection (the one listed as SiteSqlServer in the web.config) must be granted to:

Forum_Threads
Forum_Posts
Forum_Groups
Forum_Forums

Now, it would be very easy to just go through all 12 Stored Procedure and update the sql string that is being generated to include the {databaseOwner} in front of the {objectQualifier}; However, there is another problem that arises from the code itself. Any time there is a filter being generated & used by the Stored Procedure, it will cause a failure because the {databaseOwner} is not included. This is seen in most of the Search functions.

So, the quickest way I have found is to create a new function that is called in those stored procedures that looks for the {objectQualifier} and prepends the {databaseOwner}. The code for the function is pretty simple:

CREATE FUNCTION {databaseOwner}{objectQualifier}ForumSQLPatch
(
@oldSQL nvarchar(1000)
)
RETURNS nvarchar(1000) AS
BEGIN
DECLARE @newSQL nvarchar(1000)
SELECT @newSQL = REPLACE(@oldSQL, ' {objectQualifier}' , ' {databaseOwner}{objectQualifier}' )
RETURN (@newSQL)
END

Then all we need to do is run @sql through the filter before we EXECute it.

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

Problem is now solved.

9/8/2005 2:00 AM

Patrick Timmons

Joined: 10/15/2004

Posts: 15

Re: Problems: Invalid object name Modified By Patrick Timmons on 9/13/2005 10:08:32 AM

And here is the updated SQL script:

[EDIT: fixed the code to reflect the proper {databaseOwner} and {objectQualifier} for the ForumSQLPatch. (That' s what I get for posting it at 3am...)

/* Drop Old Stored Procedures */
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_BlogGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_BlogGetAll
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_BlogGetCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_BlogGetCount
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_BlogGetArchive') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_BlogGetArchive
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_BlogGetByCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_BlogGetByCategory
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_BlogGetByModule') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_BlogGetByModule
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_BlogGetBySubCategory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_BlogGetBySubCategory
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_BlogSearch') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_BlogSearch
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_ThreadGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_ThreadGetAll
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_ThreadGetCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_ThreadGetCount
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_UserGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_UserGetAll
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_SearchGetResults') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_SearchGetResults
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_WhatsNewGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}ForumSQLPatch') and xtype in (N'FN', N'IF', N'TF'))
drop function {databaseOwner}{objectQualifier}ForumSQLPatch
GO

/* SQL Patcher */
CREATE FUNCTION {databaseOwner}{objectQualifier}ForumSQLPatch
(
@oldSQL nvarchar(1000)
)
RETURNS nvarchar(1000) AS
BEGIN
	DECLARE @newSQL nvarchar(1000)
	SELECT @newSQL = REPLACE(@oldSQL, ' {objectQualifier}' , ' {databaseOwner}{objectQualifier}' )
	RETURN (@newSQL)
END
Go

/* Forum_BlogGetAll */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_BlogGetAll
(
@ModuleID int,
@PageSize int,
@PageIndex int
)
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)

-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql = 'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.ThreadID' +
		' FROM {objectQualifier}Forum_Groups (nolock), {objectQualifier}Forum_Forums (nolock), {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID' +
		' AND {objectQualifier}Forum_Forums.GroupID = {objectQualifier}Forum_Groups.GroupID' +
		' AND {objectQualifier}Forum_Groups.ModuleID = ' + CONVERT(varchar, @ModuleID) +
		' ORDER BY {objectQualifier}Forum_Posts.CreatedDate DESC'
--Print @sql
--Print @PageLowerBound
--Print @PageUpperBound

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	P.[Subject],
	P.[Body],
	P.[CreatedDate],
	P.[IsApproved],
	U.[Alias] AS StartedByAlias,
	U.[UserID] AS StartedByUserID,
	T.[ThreadID],
	T.[Replies],
	T.[ForumID],
	T.[Image],
	T.[PinnedDate],
	PageIndex.[IndexID],
	(SELECT Count([ThreadID]) FROM #PageIndex) As RecordCount,
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]) As RatingCount,
	ISNULL((SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As Rating
FROM
	{objectQualifier}Forum_Threads T (nolock),
	{objectQualifier}Forum_Posts P (nolock),
	{objectQualifier}Forum_Users U (nolock),
	#PageIndex PageIndex (nolock)
WHERE
		T.ThreadID				= PageIndex.ThreadID
	AND 	PageIndex.IndexID			> @PageLowerBound
	AND	PageIndex.IndexID			< @PageUpperBound
	AND	T.ThreadID				= P.PostID
	AND	P.UserID				= U.UserID
ORDER BY
	PageIndex.IndexID
GO

/* Forum_BlogGetCount */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_BlogGetCount
(
@Level		int,
@ObjectID	int,
@FromDate 	datetime,
@ToDate	datetime,
@Filter		nvarchar(500),
@Archive	bit
)
AS

CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)

-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
IF @Level =2 -- Get blog at forum level
BEGIN
SELECT  @sql = 	'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.[ThreadID] FROM {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Threads.ForumID = ' + CONVERT(varchar, @ObjectID) +
		@Filter
END
ELSE IF @Level = 1 -- Get blog at group level
BEGIN
SELECT  @sql = 	'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.[ThreadID] FROM {objectQualifier}Forum_Forums (nolock), {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID' +
		' AND {objectQualifier}Forum_Forums.GroupID = ' + CONVERT(varchar, @ObjectID) +
		@Filter
END
ELSE -- Get blog at module level
BEGIN
SELECT  @sql = 'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.[ThreadID] FROM {objectQualifier}Forum_Groups (nolock), {objectQualifier}Forum_Forums (nolock), {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID' +
		' AND {objectQualifier}Forum_Forums.GroupID = {objectQualifier}Forum_Groups.GroupID' +
		' AND {objectQualifier}Forum_Groups.ModuleID = ' + CONVERT(varchar, @ObjectID) +
		@Filter
END
print @sql
SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

IF @Archive = 1  --- if this view is from archive menu get all from selected date range
BEGIN
SELECT COUNT (PageIndex.ThreadID) FROM #PageIndex PageIndex (nolock), {objectQualifier}Forum_Threads T (nolock), {objectQualifier}Forum_Posts P (nolock)
WHERE
	PageIndex.ThreadID = T.ThreadID AND
	T.ThreadID = P.PostID AND
	P.CreatedDate > @FromDate AND
	P.CreatedDate < @ToDate
END
ELSE
BEGIN
SELECT COUNT (PageIndex.ThreadID) FROM #PageIndex PageIndex (nolock), {objectQualifier}Forum_Threads T (nolock), {objectQualifier}Forum_Posts P (nolock)
WHERE
	PageIndex.ThreadID = T.ThreadID AND
	T.PinnedDate >	getdate() AND
	T.ThreadID = P.PostID AND
	P.CreatedDate > @FromDate AND
	P.CreatedDate < @ToDate
END
GO


/* Forum_BlogGetArchive */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_BlogGetArchive
@ModuleID int,
@FromDate datetime,
@ToDate datetime,
@PageSize int,
@PageIndex int


AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

Print @FromDate
Print  @ToDate

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)

-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql = 'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.ThreadID' +
		' FROM {objectQualifier}Forum_Groups (nolock), {objectQualifier}Forum_Forums (nolock), {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID' +
		' AND {objectQualifier}Forum_Forums.GroupID = {objectQualifier}Forum_Groups.GroupID' +
		' AND {objectQualifier}Forum_Posts.CreatedDate > ''' + CONVERT(varchar, @FromDate) +
		''' AND {objectQualifier}Forum_Posts.CreatedDate < ''' + CONVERT(varchar, @ToDate) +
		''' AND {objectQualifier}Forum_Groups.ModuleID = ' + CONVERT(varchar, @ModuleID) +
		' ORDER BY {objectQualifier}Forum_Posts.CreatedDate DESC'
Print @sql
--Print @PageLowerBound
--Print @PageUpperBound

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	P.[Subject],
	P.[Body],
	P.[CreatedDate],
	P.[IsApproved],
	U.[Alias] AS StartedByAlias,
	U.[UserID] AS StartedByUserID,
	T.[ThreadID],
	T.[Replies],
	T.[ForumID],
	T.[Image],
	T.[PinnedDate],
	PageIndex.[IndexID],
	(SELECT Count([ThreadID]) FROM #PageIndex) As RecordCount,
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]) As RatingCount,
	ISNULL((SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As Rating
FROM
	{objectQualifier}Forum_Threads T (nolock) , {objectQualifier}Forum_Posts P (nolock) , {objectQualifier}Forum_Users U (nolock) , #PageIndex PageIndex (nolock)
WHERE
		T.ThreadID				= PageIndex.ThreadID
	AND 	PageIndex.IndexID			> @PageLowerBound
	AND	PageIndex.IndexID			< @PageUpperBound
	AND	T.ThreadID				= P.PostID
	AND	P.UserID				= U.UserID
ORDER BY
	PageIndex.IndexID
GO

/* Forum_BlogGetByCategory */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_BlogGetByCategory
(
@CategoryID int,
@PageSize int,
@PageIndex int
)
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)

-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql = 'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.ThreadID' +
		' FROM {objectQualifier}Forum_Forums (nolock), {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID' +
		' AND {objectQualifier}Forum_Threads.PinnedDate > ''' + CONVERT(varchar, getdate()) +
		''' AND {objectQualifier}Forum_Forums.GroupID = ' + CONVERT(varchar, @CategoryID) +
		' ORDER BY {objectQualifier}Forum_Posts.CreatedDate DESC'
--Print @sql
--Print @PageLowerBound
--Print @PageUpperBound

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	P.[Subject],
	P.[Body],
	P.[CreatedDate],
	P.[IsApproved],
	U.[Alias] AS StartedByAlias,
	U.[UserID] AS StartedByUserID,
	T.[ThreadID],
	T.[Replies],
	T.[ForumID],
	T.[Image],
	T.[PinnedDate],
	PageIndex.[IndexID],
	(SELECT Count([ThreadID]) FROM #PageIndex) As RecordCount,
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]) As RatingCount,
	ISNULL((SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As Rating
FROM
	{objectQualifier}Forum_Threads T (nolock),
	{objectQualifier}Forum_Posts P (nolock),
	{objectQualifier}Forum_Users U (nolock),
	#PageIndex PageIndex (nolock)
WHERE
		T.ThreadID				= PageIndex.ThreadID
	AND 	PageIndex.IndexID			> @PageLowerBound
	AND	PageIndex.IndexID			< @PageUpperBound
	AND	T.ThreadID				= P.PostID
	AND	P.UserID				= U.UserID
ORDER BY
	PageIndex.IndexID
GO

/* Forum_BlogGetByModule */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_BlogGetByModule
(
@ModuleID int,
@PageSize int,
@PageIndex int
)
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)

-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql = 'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.ThreadID' +
		' FROM {objectQualifier}Forum_Groups (nolock), {objectQualifier}Forum_Forums (nolock), {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID' +
		' AND {objectQualifier}Forum_Forums.GroupID = {objectQualifier}Forum_Groups.GroupID' +
		' AND {objectQualifier}Forum_Threads.PinnedDate > ''' + CONVERT(varchar, getdate()) +
		''' AND {objectQualifier}Forum_Groups.ModuleID = ' + CONVERT(varchar, @ModuleID) +
		' ORDER BY {objectQualifier}Forum_Posts.CreatedDate DESC'
--Print @sql
--Print @PageLowerBound
--Print @PageUpperBound

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	P.[Subject],
	P.[Body],
	P.[CreatedDate],
	P.[IsApproved],
	U.[Alias] AS StartedByAlias,
	U.[UserID] AS StartedByUserID,
	T.[ThreadID],
	T.[Replies],
	T.[ForumID],
	T.[Image],
	T.[PinnedDate],
	PageIndex.[IndexID],
	(SELECT Count([ThreadID]) FROM #PageIndex) As RecordCount,
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]) As RatingCount,
	ISNULL((SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As Rating
FROM
	{objectQualifier}Forum_Threads T (nolock) , {objectQualifier}Forum_Posts P (nolock) , {objectQualifier}Forum_Users U (nolock) , #PageIndex PageIndex (nolock)
WHERE
		T.ThreadID				= PageIndex.ThreadID
	AND 	PageIndex.IndexID			> @PageLowerBound
	AND	PageIndex.IndexID			< @PageUpperBound
	AND	T.ThreadID				= P.PostID
	AND	P.UserID				= U.UserID
ORDER BY
	PageIndex.IndexID
GO

/* Forum_BlogGetBySubCategory */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_BlogGetBySubCategory
(
@SubCategoryID int,
@PageSize int,
@PageIndex int
)
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)

-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql = 'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.ThreadID' +
		' FROM {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Threads.PinnedDate > ''' + CONVERT(varchar, getdate()) +
		''' AND {objectQualifier}Forum_Threads.ForumID = ' + CONVERT(varchar, @SubCategoryID) +
		' ORDER BY {objectQualifier}Forum_Posts.CreatedDate DESC'
Print @sql
--Print @PageLowerBound
--Print @PageUpperBound

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	P.[Subject],
	P.[Body],
	P.[CreatedDate],
	P.[IsApproved],
	U.[Alias] AS StartedByAlias,
	U.[UserID] AS StartedByUserID,
	T.[ThreadID],
	T.[Replies],
	T.[ForumID],
	T.[Image],
	T.[PinnedDate],
	PageIndex.[IndexID],
	(SELECT Count([ThreadID]) FROM #PageIndex) As RecordCount,
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]) As RatingCount,
	ISNULL((SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As Rating
FROM
	{objectQualifier}Forum_Threads T (nolock) , {objectQualifier}Forum_Posts P (nolock) , {objectQualifier}Forum_Users U (nolock) , #PageIndex PageIndex (nolock)
WHERE
		T.ThreadID				= PageIndex.ThreadID
	AND 	PageIndex.IndexID			> @PageLowerBound
	AND	PageIndex.IndexID			< @PageUpperBound
	AND	T.ThreadID				= P.PostID
	AND	P.UserID				= U.UserID
ORDER BY
	PageIndex.IndexID
GO

/* Forum_BlogSearch */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_BlogSearch
(
@ModuleID int,
@PageSize int,
@FromDate datetime,
@ToDate datetime,
@Filter nvarchar(500)
)
AS

-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	PostID	int
)
-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql = 'INSERT INTO #PageIndex (PostID) SELECT {objectQualifier}Forum_Posts.PostID' +
		' FROM {objectQualifier}Forum_Groups (nolock), {objectQualifier}Forum_Forums (nolock), {objectQualifier}Forum_Threads (nolock), {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.ThreadID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID' +
		' AND {objectQualifier}Forum_Forums.GroupID = {objectQualifier}Forum_Groups.GroupID' +
		' AND {objectQualifier}Forum_Groups.ModuleID = ' + CONVERT(varchar, @ModuleID) +
		@Filter +
		' ORDER BY {objectQualifier}Forum_Threads.IsPinned DESC, {objectQualifier}Forum_Posts.CreatedDate DESC'

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	C.[PostID],
	C.[Subject],
	C.[Body],
	C.[CreatedDate],
	C.[IsClosed],
	C.[IsApproved],
	C.[MediaURL],
	C.[MediaNAV],
	C.[UserID] AS CreatedByUser,
	A.[UserID] AS StartedByUserID,
	A.[Alias] AS StartedByAlias,
	T.[ThreadID],
	T.[Replies],
	T.[Views],
	T.[ForumID],
	T.[LastPostedPostId],
	T.[Image],
	T.[ObjectTypeCode],
	T.[ObjectID],
	T.[IsPinned],
	T.[PinnedDate],
	ISNULL((SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As RatingCount,
	ISNULL((SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As Rating
FROM
	{objectQualifier}Forum_Threads T (nolock),
	{objectQualifier}Forum_Posts C (nolock),
	{objectQualifier}Forum_Users A (nolock),
	#PageIndex PageIndex (nolock)
WHERE
	PageIndex.IndexID	< @PageSize + 1
AND	PageIndex.PostID	= C.PostID
AND	C.CreatedDate		> @FromDate
AND	C.CreatedDate		< @ToDate
AND	C.ThreadID 		= T.ThreadID
AND	C.UserID		= A.UserID

ORDER BY
	PageIndex.IndexID
GO

/* Forum_ThreadGetAll */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ThreadGetAll
(
@ForumID int,
@UserID int,
@PageSize int,
@PageIndex int,
@Filter nvarchar(500)
)
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)
-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql = 'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.ThreadID' +
		' FROM {objectQualifier}Forum_Threads, {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		--' AND {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.ThreadID' +
		' AND {objectQualifier}Forum_Threads.ForumID = ' + CONVERT(varchar, @ForumID) +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Posts.IsLocked = 0 ' +
		@Filter +
		' ORDER BY {objectQualifier}Forum_Threads.IsPinned DESC, {objectQualifier}Forum_Posts.CreatedDate DESC'

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

Print @PageLowerBound
Print @PageUpperBound

SELECT
	C.[Subject],
	C.[Body],
	C.[CreatedDate],
	A.[Alias] AS StartedByAlias,
	A.[UserID] AS StartedByUserID,
	B.[Alias] AS LastPostAlias,
	B.[UserID] As LastPostUserID,
	D.[CreatedDate] As LastPostedDate,
	T.[ThreadID],
	T.[Views],
	T.[ForumID],
	T.[LastPostedPostId],
	T.[Image],
	T.[ObjectTypeCode],
	T.[ObjectID],
	T.[IsPinned],
	T.[PinnedDate],
	C.[IsClosed],
	C.[MediaURL],
	C.[MediaNAV],
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_Posts (nolock) WHERE [ThreadID] = T.[ThreadID] AND [PostID] <> T.[ThreadID]) As Replies,
	--(SELECT Distinct Count(UserID) FROM {objectQualifier}Forum_ThreadsRead (nolock) WHERE ForumID = T.ForumID AND UserID = @UserID AND ReadThreads LIKE '%' + CONVERT(varchar, T.ThreadID) + ';%')  As IsRead,
	ISNULL((SELECT Max([ThreadID]) FROM #PageIndex  (nolock) WHERE [ThreadID] < T.[ThreadID]), 0) As PreviousThreadID,
	ISNULL((SELECT Min([ThreadID]) FROM #PageIndex  (nolock) WHERE [ThreadID] > T.[ThreadID]), 0) As NextThreadID,
	(SELECT Distinct  Count(UserID) FROM {objectQualifier}Forum_ThreadsRead (nolock) WHERE ForumID = T.ForumID AND UserID = @UserID AND  D.CreatedDate > LastPurseDate AND ReadThreads NOT LIKE '%' + CONVERT(varchar, T.ThreadID) + ';%')  As IsUnRead,
	(SELECT Distinct Count(UserID) FROM {objectQualifier}Forum_TrackedThreads (nolock) WHERE UserID = @UserID AND ThreadID = T.ThreadID) As IsTracked,
	ISNULL((SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As RatingCount,
	ISNULL((SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As Rating

FROM
	{objectQualifier}Forum_Threads T (nolock),
	{objectQualifier}Forum_Posts C (nolock),
	{objectQualifier}Forum_Posts D (nolock),
	{objectQualifier}Forum_Users A (nolock),
	{objectQualifier}Forum_Users B (nolock),
	#PageIndex PageIndex (nolock)
WHERE
	T.ThreadID				= PageIndex.ThreadID
AND 	PageIndex.IndexID			> @PageLowerBound
AND	PageIndex.IndexID			< @PageUpperBound
AND	T.ThreadID				= C.PostID
AND	C.ThreadID				= D.ThreadID
AND 	D.PostID				= T.ThreadID
AND	A.UserID				= C.UserID
AND	B.UserID				= D.UserID
ORDER BY
	PageIndex.IndexID
GO

/* Forum_ThreadGetCount */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ThreadGetCount
(
	@ForumID	int,
	@Filter		nvarchar(500)
)
AS
DECLARE @sql nvarchar(600)
SET  @sql = 	'SELECT COUNT (DISTINCT {objectQualifier}Forum_Threads.ThreadID) FROM {objectQualifier}Forum_Threads (nolock) , {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Threads.ThreadID = {objectQualifier}Forum_Posts.ThreadID' +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Posts.IsLocked = 0 ' +
		' AND ForumID = ' +
		CONVERT(varchar, @ForumID) +
		@Filter

SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)
GO

CREATE procedure {databaseOwner}{objectQualifier}Forum_UserGetAll
(
@PortalID int,
@Filter nvarchar(1024)
)
AS
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	UserID	int
)
-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
IF CHARINDEX('Alias', @Filter) = 0
	SELECT @sql = 	'INSERT INTO #PageIndex(UserID) SELECT {objectQualifier}Users.UserID' +
			' FROM {objectQualifier}Users (noLock), {objectQualifier}UserPortals (nolock) ' +
			' WHERE {objectQualifier}Users.UserID = {objectQualifier}UserPortals.UserID AND {objectQualifier}UserPortals.PortalID = ' +
			CONVERT(varchar, @PortalID) + @Filter
ELSE
	SELECT @sql = 	'INSERT INTO #PageIndex(UserID) SELECT {objectQualifier}Forum_Users.UserID' +
			' FROM {objectQualifier}Forum_Users (noLock), {objectQualifier}Users (noLock), {objectQualifier}UserPortals (nolock) ' +
			' WHERE {objectQualifier}Forum_Users.UserID = {objectQualifier}Users.UserID' +
			' AND {objectQualifier}Users.UserID = {objectQualifier}UserPortals.UserID AND {objectQualifier}UserPortals.PortalID = ' +
			CONVERT(varchar, @PortalID) + @Filter


SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	UR.*,
	FUR.*,
	--ISNULL((SELECT DISTINCT [UserID] FROM {objectQualifier}Forum_Moderators (nolock) WHERE {objectQualifier}Forum_Moderators.[UserID] = UR.[UserID]), 0) As IsModerator,
	--(SELECT COUNT(DISTINCT [UserID]) FROM {objectQualifier}Forum_Moderators (nolock) WHERE [UserID] = UR.[UserID]) As IsTrusted,
	ISNULL((SELECT NULLIF(COUNT(DISTINCT [UserID]), 0) FROM {objectQualifier}Forum_Moderators (nolock) WHERE [UserID] = UR.[UserID]), [IsTrusted]) AS IsTrusted,
	(SELECT COUNT(DISTINCT [UserID]) FROM {objectQualifier}Forum_Moderators (nolock) WHERE {objectQualifier}Forum_Moderators.[UserID] = UR.[UserID]) As IsModerator,
	(SELECT COUNT(DISTINCT [UserID]) FROM {objectQualifier}UsersOnline (nolock) WHERE {objectQualifier}UsersOnline.[UserID] = UR.[UserID] AND {objectQualifier}UsersOnline.[PortalID] = @PortalID) As IsOnline,
	ISNULL((SELECT SUM([PostsModerated]) FROM {objectQualifier}Forum_Moderators (nolock) WHERE {objectQualifier}Forum_Moderators.[UserID] = UR.[UserID]), 0) As PostsModerated

FROM
	{objectQualifier}Users UR (nolock), {objectQualifier}Forum_Users FUR (nolock), #PageIndex PageIndex (nolock)
WHERE
	FUR.[UserID] = UR.[UserID] AND
	UR.[UserID] = PageIndex.[UserID]
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_SearchGetResults
(
@Filter		nvarchar(500),
@PageIndex	int,
@PageSize	int,
@UserID		int,
@ModuleID	int,
@FromDate	datetime,
@ToDate	datetime
)
AS
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID	int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(1000)
SELECT  @sql =	'INSERT INTO #PageIndex(ThreadID) SELECT DISTINCT({objectQualifier}Forum_Posts.ThreadID) ' +
			'FROM {objectQualifier}Forum_Posts (nolock) , {objectQualifier}Forum_Threads (nolock) , {objectQualifier}Forum_Forums (nolock) , {objectQualifier}Forum_Groups (nolock) ' +
			'WHERE {objectQualifier}Forum_Posts.ThreadID = {objectQualifier}Forum_Threads.ThreadID AND ' +
			'{objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID AND ' +
			'{objectQualifier}Forum_Forums.GroupID = {objectQualifier}Forum_Groups.GroupID ' +
			'AND {objectQualifier}Forum_Posts.IsApproved = 1 ' +
			'AND {objectQualifier}Forum_Posts.IsLocked = 0 ' +
			'AND {objectQualifier}Forum_Groups.ModuleID = ' + CONVERT(varchar, @ModuleID) + ' ' +
			@Filter + ' ORDER BY {objectQualifier}Forum_Posts.ThreadID DESC'
Print @sql
SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

-- All of the rows are inserted into the table - now select the correct subset
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RecordCount	int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
SELECT @RecordCount = COUNT(*) FROM #PageIndex

Print @RecordCount
-- Select the data out of the temporary table
-- Select the data out of the temporary table
SELECT
	PageIndex.ThreadID,
	{objectQualifier}Forum_Posts.[Subject],
	{objectQualifier}Forum_Posts.[CreatedDate],
	{objectQualifier}Forum_Users.[UserID],
	{objectQualifier}Forum_Users.[Alias],
	{objectQualifier}Forum_Threads.[ForumID],
	{objectQualifier}Forum_Forums.[Name] As ForumName,
	RecordCount = @RecordCount,
	(SELECT Count({objectQualifier}Forum_Posts.PostID) FROM {objectQualifier}Forum_Posts (nolock) WHERE ThreadID = PageIndex.ThreadID) As PostCount,
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating  (nolock) WHERE ThreadID = PageIndex.ThreadID) As RatingCount,
	(SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating  (nolock) WHERE ThreadID = PageIndex.ThreadID) As Rating,
	(SELECT Distinct Count(UserID) FROM {objectQualifier}Forum_ThreadsRead (nolock) WHERE ForumID = {objectQualifier}Forum_Threads.[ForumID] AND UserID = {objectQualifier}Forum_Users.[UserID] AND {objectQualifier}Forum_Posts.[CreatedDate] > LastPurseDate AND ReadThreads NOT LIKE '%' + CONVERT(varchar, PageIndex.ThreadID) + ';%') As IsUnRead
FROM
	{objectQualifier}Forum_Users (nolock) , {objectQualifier}Forum_Forums (nolock) , {objectQualifier}Forum_Threads (nolock) , {objectQualifier}Forum_Posts (nolock) , #PageIndex PageIndex (nolock)
WHERE
	PageIndex.IndexID > @PageLowerBound AND
	PageIndex.IndexID < @PageUpperBound AND
	PageIndex.ThreadID = {objectQualifier}Forum_Posts.PostID AND
	{objectQualifier}Forum_Users.[UserID] = {objectQualifier}Forum_Posts.[UserID] AND
	{objectQualifier}Forum_Posts.[ThreadID] = {objectQualifier}Forum_Threads.[ThreadID] AND
	{objectQualifier}Forum_Posts.[CreatedDate] > @FromDate AND
	{objectQualifier}Forum_Posts.[CreatedDate] < @ToDate AND
	{objectQualifier}Forum_Threads.[ForumID] = {objectQualifier}Forum_Forums.[ForumID] AND
	({objectQualifier}Forum_Forums.ForumID NOT IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums) OR
	{objectQualifier}Forum_Forums.ForumID IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums (nolock) WHERE RoleID IN (SELECT RoleID FROM {objectQualifier}UserRoles (nolock) WHERE UserID = @UserID)))
ORDER BY
	PageIndex.IndexID
GO

CREATE procedure {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll
(
	@ModuleID	int,
	@NumberOfThread	int,
	@UserID		int,
	@FromDate		datetime,
	@ToDate		datetime,
	@Filter		nvarchar(500)
)
AS
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	PostID	int)
DECLARE @sql nvarchar(1000)
SELECT @sql = 	'INSERT INTO #PageIndex (PostID)' +
		' SELECT Max(PostID) As PostID' +
		' FROM {objectQualifier}Forum_Posts, {objectQualifier}Forum_Threads, {objectQualifier}Forum_Forums, {objectQualifier}Forum_Groups (nolock) ' +
		' WHERE ModuleID = ' +  CONVERT(varchar, @ModuleID) +
		' AND {objectQualifier}Forum_Groups.GroupID = {objectQualifier}Forum_Forums.GroupID' +
		' AND {objectQualifier}Forum_Forums.ForumID = {objectQualifier}Forum_Threads.ForumID' +
		' AND {objectQualifier}Forum_Threads.ThreadID = {objectQualifier}Forum_Posts.ThreadID' +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Posts.IsLocked = 0' +
		' AND ({objectQualifier}Forum_Threads.ForumID NOT IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums) OR' +
		' {objectQualifier}Forum_Threads.ForumID IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums (nolock) ' +
		' WHERE RoleID IN (SELECT RoleID FROM {objectQualifier}UserRoles (nolock) WHERE UserID =' + CONVERT(varchar, @UserID) +
		')))' +
		@Filter +
		' GROUP BY {objectQualifier}Forum_Posts.ThreadID'
Print @sql
SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	G.ModuleID,
	T.ForumID,
	F.Name AS ForumName,
	P1.ThreadID,
	P1.PostID As LastPostID,
	P2.UserID As StartedByUserID,
	U2.Alias As StartedByAlias,
	P2.Subject As StartedSubject,
	P1.Subject As LastSubject,
	P1.UserID As LastPostUserID,
	U1.Alias As LastPostAlias,
	P1.Body As LastPostBody,
	P1.CreatedDate As LastCreatedDate
FROM
	#PageIndex PageIndex (nolock),
	{objectQualifier}Forum_Posts P1 (nolock),
	{objectQualifier}Forum_Posts P2 (nolock),
	{objectQualifier}Forum_Threads T (nolock),
	{objectQualifier}Forum_Forums F (nolock),
	{objectQualifier}Forum_Groups G (nolock),
	{objectQualifier}Modules M (nolock),
	{objectQualifier}Forum_Users U1 (nolock),
	{objectQualifier}Forum_Users U2 (nolock)
WHERE
	P1.PostID = PageIndex.PostID AND
	P1.CreatedDate > @FromDate AND
	P1.CreatedDate < @ToDate AND
	P1.ThreadID = T.ThreadID AND
	P1.ThreadID = P2.PostID AND
	P1.UserID = U1.UserID AND
	P2.UserID = U2.UserID AND
	T.ForumID = F.ForumID AND
	F.GroupID = G.GroupID AND
	G.ModuleID = M.ModuleID	 AND
	PageIndex.IndexID <= @NumberOfThread
ORDER BY
	PageIndex.IndexID DESC
GO

GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_BlogGetAll TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_BlogGetCount TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_BlogGetArchive TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_BlogGetByCategory TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_BlogGetByModule TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_BlogGetBySubCategory TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_BlogSearch TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_ThreadGetAll TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_ThreadGetCount TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_UserGetAll TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_SearchGetResults TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll TO [public]

/*
You will have to run this section manually, or modify it directly on the SQL Server

GRANT SELECT ON {databaseOwner}{objectQualifier}Forum_Threads TO {databaselogin}
GRANT SELECT ON {databaseOwner}{objectQualifier}Forum_Posts   TO {databaselogin}
GRANT SELECT ON {databaseOwner}{objectQualifier}Forum_Groups  TO {databaselogin}
GRANT SELECT ON {databaseOwner}{objectQualifier}Forum_Forums  TO {databaselogin}

*/

9/12/2005 9:27 PM

Patrick Timmons

Joined: 10/15/2004

Posts: 15

Version 3.10.04: (was Problems: Invalid object name) Modified By Chris Paterra on 9/12/2005 9:36:54 PM

With the new version released, the first thing I did was check the code & SQL to see if this problem still exists. It does. The problem still exists.

***************
Note to all Developers:
Any time you EXECute SQL, it needs the correct security permissons in order to run the code. The SQL script does not run under the EXECUTE right of the Stored Procedure, it will run under the security conneciton of the connected user. Also, you need to ensure that the SQL can properly locate the required items, as the connection will automatically use the users default SQL login database (which may or may not be Master or the current database). Proper SQL scripting would be to include the {databaseowner} for every object that is created and referenced, however I do understand the need for simplicity as well.
***************

Here is the updated SQL script for the new version:

/* SQL Patcher */
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}ForumSQLPatch') and xtype in (N'FN', N'IF', N'TF'))
drop function {databaseOwner}{objectQualifier}ForumSQLPatch
GO

CREATE FUNCTION {databaseOwner}{objectQualifier}ForumSQLPatch
(
@oldSQL nvarchar(1000)
)
RETURNS nvarchar(1000) AS
BEGIN
	DECLARE @newSQL nvarchar(1000)
	SELECT @newSQL = REPLACE(@oldSQL, ' {objectQualifier}', ' {databaseOwner}{objectQualifier}' )
	RETURN (@newSQL)
END
GO


/* Drop Old Stored Procedures */
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_SearchGetResults') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_SearchGetResults
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_ThreadGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_ThreadGetAll
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_ThreadGetCount') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_ThreadGetCount
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_UserGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_UserGetAll
GO
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_WhatsNewGetAll') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll
GO

/* Add updated Procedures */

/* Forum_SearchGetResults */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_SearchGetResults
(
@Filter		nvarchar(500),
@PageIndex	int,
@PageSize	int,
@UserID		int,
@ModuleID	int,
@FromDate	datetime,
@ToDate	datetime
)
AS
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID	int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(2000)
SELECT  @sql =	'INSERT INTO #PageIndex(ThreadID) SELECT DISTINCT({objectQualifier}Forum_Posts.ThreadID) ' +
		'FROM {databaseOwner}{objectQualifier}Forum_Posts (nolock) , {databaseOwner}{objectQualifier}Forum_Threads (nolock) , {databaseOwner}{objectQualifier}Forum_Forums (nolock) , {databaseOwner}{objectQualifier}Forum_Groups (nolock) ' +
		'WHERE {objectQualifier}Forum_Posts.ThreadID = {objectQualifier}Forum_Threads.ThreadID AND ' +
		'{objectQualifier}Forum_Threads.ForumID = {objectQualifier}Forum_Forums.ForumID AND ' +
		'{objectQualifier}Forum_Forums.GroupID = {objectQualifier}Forum_Groups.GroupID ' +
		'AND {objectQualifier}Forum_Posts.[CreatedDate] >  ''' + CONVERT(varchar, @FromDate) + ''' ' +
		'AND {objectQualifier}Forum_Posts.[CreatedDate] < ''' + CONVERT(varchar, @ToDate) + ''' ' +
		'AND {objectQualifier}Forum_Posts.IsApproved = 1 ' +
		'AND {objectQualifier}Forum_Posts.IsLocked = 0 ' +
		'AND {objectQualifier}Forum_Groups.ModuleID = ' + CONVERT(varchar, @ModuleID) + ' AND ' +
		'( {objectQualifier}Forum_Forums.ForumID NOT IN (SELECT ForumID FROM  {objectQualifier}Forum_PrivateForums) OR ' +
		' {objectQualifier}Forum_Forums.ForumID IN ( ' +
		'SELECT ForumID  ' +
		'FROM  {objectQualifier}Forum_PrivateForums (nolock)  ' +
		'WHERE (RoleID IN (SELECT RoleID FROM  {objectQualifier}UserRoles (nolock) WHERE UserID = ' + CONVERT(varchar, @UserID) + ' ) ' +
		'OR EXISTS (SELECT TOP 1 1 FROM  {objectQualifier}Users WHERE UserID = ' + CONVERT(varchar, @UserID) + '  and IsSuperUser=1))))  ' +
		@Filter +
		' ORDER BY {objectQualifier}Forum_Posts.ThreadID DESC'
SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
Print @sql
EXEC(@sql)
-- All of the rows are inserted into the table - now select the correct subset
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RecordCount	int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
SELECT @RecordCount = COUNT(*) FROM #PageIndex

Print @RecordCount
-- Select the data out of the temporary table
-- Select the data out of the temporary table
SELECT
	PageIndex.ThreadID,
	{objectQualifier}Forum_Posts.[Subject],
	{objectQualifier}Forum_Posts.[CreatedDate],
	{objectQualifier}Forum_Users.[UserID],
	{objectQualifier}Forum_Users.[Alias],
	{objectQualifier}Forum_Threads.[ForumID],
	{objectQualifier}Forum_Threads.[Replies],
	{objectQualifier}Forum_Threads.[Views],
	{objectQualifier}Forum_Forums.[Name] As ForumName,
	RecordCount = @RecordCount,
	(SELECT Count([ThreadID]) FROM {databaseOwner}{objectQualifier}Forum_ThreadRating  (nolock) WHERE ThreadID = PageIndex.ThreadID) As RatingCount,
	(SELECT AVG([Rate]) FROM {databaseOwner}{objectQualifier}Forum_ThreadRating  (nolock) WHERE ThreadID = PageIndex.ThreadID) As Rating,
	(SELECT Distinct Count(UserID) FROM {databaseOwner}{objectQualifier}Forum_ThreadsRead (nolock) WHERE ForumID = {objectQualifier}Forum_Threads.[ForumID] AND UserID = 
{objectQualifier}Forum_Users.[UserID] AND {objectQualifier}Forum_Posts.[CreatedDate] > LastPurseDate AND ReadThreads NOT LIKE '%' + CONVERT(varchar, PageIndex.ThreadID) + ';%')  As IsUnRead
FROM
	{databaseOwner}{objectQualifier}Forum_Users (nolock),
	{databaseOwner}{objectQualifier}Forum_Forums (nolock),
	{databaseOwner}{objectQualifier}Forum_Threads (nolock),
	{databaseOwner}{objectQualifier}Forum_Posts (nolock),
	#PageIndex PageIndex (nolock)
WHERE
	PageIndex.IndexID > @PageLowerBound AND
	PageIndex.IndexID < @PageUpperBound AND
	PageIndex.ThreadID = {objectQualifier}Forum_Posts.ThreadID AND
	{objectQualifier}Forum_Users.[UserID] = {objectQualifier}Forum_Posts.[UserID] AND
	{objectQualifier}Forum_Posts.[ThreadID] = {objectQualifier}Forum_Threads.[ThreadID] AND
	{objectQualifier}Forum_Posts.[CreatedDate] > @FromDate AND
	{objectQualifier}Forum_Posts.[CreatedDate] < @ToDate AND
	{objectQualifier}Forum_Threads.[ForumID] = {objectQualifier}Forum_Forums.[ForumID] AND
	({objectQualifier}Forum_Posts.[CreatedDate] = (SELECT MAX([CreatedDate]) FROM {databaseOwner}{objectQualifier}Forum_Posts WHERE ThreadID = PageIndex.ThreadID)) AND
	({objectQualifier}Forum_Forums.ForumID NOT IN (SELECT ForumID FROM 
	{databaseOwner}{objectQualifier}Forum_PrivateForums) OR
	{objectQualifier}Forum_Forums.ForumID IN (
		SELECT ForumID
		FROM {databaseOwner}{objectQualifier}Forum_PrivateForums (nolock)
		WHERE (RoleID IN (SELECT RoleID FROM {databaseOwner}{objectQualifier}UserRoles (nolock) WHERE UserID = @UserID)
		OR EXISTS (SELECT TOP 1 1 FROM {databaseOwner}{objectQualifier}Users WHERE UserID = @UserID and IsSuperUser=1))))
ORDER BY
	PageIndex.IndexID
GO

/* Forum_ThreadGetAll */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ThreadGetAll
(
	@ForumID int,
	@UserID int,
	@PageSize int,
	@PageIndex int,
	@Filter nvarchar(500)
)
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	ThreadID	int
)
-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(2000)
SELECT  @sql =  'INSERT INTO #PageIndex (ThreadID) SELECT {objectQualifier}Forum_Threads.ThreadID' +
		' FROM {objectQualifier}Forum_Threads, {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Posts.PostID = {objectQualifier}Forum_Threads.LastPostedPostID' +
		' AND {objectQualifier}Forum_Threads.ForumID = ' + CONVERT(varchar, @ForumID) +
		@Filter +
		' ORDER BY {objectQualifier}Forum_Threads.IsPinned DESC, {objectQualifier}Forum_Posts.CreatedDate DESC'
SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)
Print @PageLowerBound
Print @PageUpperBound

SELECT
	FP.[Subject],
	FP.[Body],
	FP.[CreatedDate],
	FU.[Alias] AS StartedByAlias,
	FU.[UserID] AS StartedByUserID,
	LU.[Alias] AS LastPostAlias,
	LU.[UserID] As LastPostUserID,
	LP.[CreatedDate] As LastPostedDate,
	T.[ThreadID],
	T.[Views],
	T.[ForumID],
	T.[LastPostedPostId],
	T.[Image],
	T.[ObjectTypeCode],
	T.[ObjectID],
	T.[IsPinned],
	T.[PinnedDate],
	FP.[IsClosed],
	FP.[MediaURL],
	FP.[MediaNAV],
	(SELECT Count([ThreadID]) FROM {objectQualifier}Forum_Posts (nolock) WHERE [ThreadID] = T.[ThreadID] AND [PostID] <> T.[ThreadID]) As Replies,
	(SELECT Distinct  Count(UserID) FROM {objectQualifier}Forum_ThreadsRead (nolock) WHERE ForumID = T.ForumID AND UserID = 
@UserID AND  LP.CreatedDate > LastPurseDate AND ReadThreads NOT LIKE '%' + CONVERT(varchar, T.ThreadID) + ';%')  As IsUnRead,
	(SELECT Distinct Count(UserID) FROM {objectQualifier}Forum_TrackedThreads (nolock) WHERE UserID = @UserID AND ThreadID = T.ThreadID) As IsTracked,
	ISNULL((SELECT Count([ThreadID]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As RatingCount,
	ISNULL((SELECT AVG([Rate]) FROM {objectQualifier}Forum_ThreadRating TR (nolock) WHERE TR.[ThreadID] = T.[ThreadID]), 0) As Rating

FROM
	{objectQualifier}Forum_Threads T (nolock),
	{objectQualifier}Forum_Posts FP (nolock),
	{objectQualifier}Forum_Posts LP (nolock),
	{objectQualifier}Forum_Users FU (nolock),
	{objectQualifier}Forum_Users LU (nolock),
	#PageIndex PageIndex (nolock)
WHERE
	T.ThreadID				= PageIndex.ThreadID
	AND PageIndex.IndexID	> @PageLowerBound
	AND PageIndex.IndexID	< @PageUpperBound
	AND T.ThreadID		= FP.PostID
	AND FU.UserID		= FP.UserID
	AND T.LastPostedPostID	= LP.PostID
	AND LU.UserID		= LP.UserID
	AND FP.IsApproved	= 1
	AND FP.IsLocked		= 0
ORDER BY
	PageIndex.IndexID
GO

/* Forum_ThreadGetCount */
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ThreadGetCount
(
	@ForumID	int,
	@Filter		nvarchar(500)
)
AS
DECLARE @sql nvarchar(600)
SET  @sql = 'SELECT COUNT (DISTINCT {objectQualifier}Forum_Threads.ThreadID) FROM {objectQualifier}Forum_Threads (nolock) , {objectQualifier}Forum_Posts (nolock) ' +
		' WHERE {objectQualifier}Forum_Threads.ThreadID = {objectQualifier}Forum_Posts.ThreadID' +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Posts.IsLocked = 0 ' +
		' AND ForumID = ' +
		CONVERT(varchar, @ForumID) +
		@Filter
SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)
GO

/* Forum_UserGetAll */
CREATE procedure {databaseOwner}{objectQualifier}Forum_UserGetAll
(
	@PortalID int,
	@Filter nvarchar(1024),
	@PageSize int,
	@PageIndex int,
	@OrderBy varchar(20)
)
AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
CREATE TABLE #PageIndex
(
	IndexID		int IDENTITY (1, 1) NOT NULL,
	UserID	int
)
-- INSERT into the temp table
-- Create dynamic SQL to populate temporary table
DECLARE @sql nvarchar(2000)
SELECT @sql = 	'INSERT INTO #PageIndex(UserID) SELECT {objectQualifier}Forum_Users.UserID' +
		' FROM {databaseOwner}{objectQualifier}Forum_Users (noLock), {databaseOwner}{objectQualifier}Users (noLock), {databaseOwner}{objectQualifier}UserPortals (nolock) ' +
		' WHERE {objectQualifier}Forum_Users.UserID = {objectQualifier}Users.UserID' +
		' AND {objectQualifier}Users.UserID = {objectQualifier}UserPortals.UserID AND {objectQualifier}UserPortals.PortalID = ' +
		CONVERT(varchar, @PortalID) +
		@Filter	+
		' ORDER BY ' + @OrderBy
SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
EXEC(@sql)

SELECT
	UR.*,
	FUR.*,
	ISNULL((SELECT NULLIF(COUNT(DISTINCT [UserID]), 0) FROM {databaseOwner}{objectQualifier}Forum_Moderators (nolock) WHERE [UserID] = UR.[UserID]), [IsTrusted]) AS IsTrusted,
	(SELECT COUNT(DISTINCT [UserID]) FROM {databaseOwner}{objectQualifier}Forum_Moderators (nolock) WHERE {objectQualifier}Forum_Moderators.[UserID] = UR.[UserID]) As IsModerator,
	(SELECT COUNT(DISTINCT [UserID]) FROM {databaseOwner}{objectQualifier}UsersOnline (nolock) WHERE {objectQualifier}UsersOnline.[UserID] = UR.[UserID] AND 
{objectQualifier}UsersOnline.[PortalID] = @PortalID) As IsOnline,
	ISNULL((SELECT SUM([PostsModerated]) FROM {databaseOwner}{objectQualifier}Forum_Moderators (nolock) WHERE {objectQualifier}Forum_Moderators.[UserID] = UR.[UserID]), 0) As PostsModerated
FROM
	{databaseOwner}{objectQualifier}Users UR (nolock),
	{databaseOwner}{objectQualifier}Forum_Users FUR (nolock),
	#PageIndex PageIndex (nolock)
WHERE
	FUR.[UserID] = UR.[UserID]
	AND UR.[UserID] = PageIndex.[UserID]
	AND PageIndex.IndexID	> @PageLowerBound
	AND PageIndex.IndexID	< @PageUpperBound
	ORDER BY
		PageIndex.IndexID

SELECT
	COUNT(*) as TotalRecords
FROM #PageIndex

GO

/* Forum_WhatsNewGetAll */
CREATE procedure {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll
(
	@ModuleID	int,
	@NumberOfThread	int,
	@UserID		int,
	@FromDate		datetime,
	@ToDate		datetime,
	@Filter		nvarchar(500)
)
AS
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
	IndexID	int IDENTITY (1, 1) NOT NULL,
	PostID	int
)
DECLARE @sql nvarchar(1000)
SELECT @sql = 	'INSERT INTO #PageIndex (PostID)' +
		' SELECT Max(PostID) As PostID' +
		' FROM {objectQualifier}Forum_Posts, {objectQualifier}Forum_Threads, {objectQualifier}Forum_Forums, {objectQualifier}Forum_Groups (nolock) ' +
		' WHERE ModuleID = ' +  CONVERT(varchar, @ModuleID) +
		' AND {objectQualifier}Forum_Groups.GroupID = {objectQualifier}Forum_Forums.GroupID' +
		' AND {objectQualifier}Forum_Forums.ForumID = {objectQualifier}Forum_Threads.ForumID' +
		' AND {objectQualifier}Forum_Threads.ThreadID = {objectQualifier}Forum_Posts.ThreadID' +
		' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
		' AND {objectQualifier}Forum_Posts.IsLocked = 0' +
		' AND ({objectQualifier}Forum_Threads.ForumID NOT IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums) OR' +
		' {objectQualifier}Forum_Threads.ForumID IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums (nolock) ' +
		' WHERE RoleID IN (SELECT RoleID FROM {objectQualifier}UserRoles (nolock) WHERE UserID =' + CONVERT(varchar, @UserID) +
		')))' +
		@Filter +
		' GROUP BY {objectQualifier}Forum_Posts.ThreadID'
SELECT @sql = {databaseOwner}{objectQualifier}ForumSQLPatch(@sql)
Print @sql
EXEC(@sql)

SELECT
	G.ModuleID,
	T.ForumID,
	F.Name AS ForumName,
	P1.ThreadID,
	P1.PostID As LastPostID,
	P2.UserID As StartedByUserID,
	U2.Alias As StartedByAlias,
	P2.Subject As StartedSubject,
	P1.Subject As LastSubject,
	P1.UserID As LastPostUserID,
	U1.Alias As LastPostAlias,
	P1.Body As LastPostBody,
	P1.CreatedDate As LastCreatedDate
FROM
	#PageIndex PageIndex (nolock), 
	{objectQualifier}Forum_Posts P1 (nolock), 
	{objectQualifier}Forum_Posts P2 (nolock), 
	{objectQualifier}Forum_Threads T (nolock), 
	{objectQualifier}Forum_Forums F (nolock), 
	{objectQualifier}Forum_Groups G (nolock), 
	{objectQualifier}Modules M (nolock), 
	{objectQualifier}Forum_Users U1 (nolock), 
	{objectQualifier}Forum_Users U2 (nolock)
WHERE
	P1.PostID = PageIndex.PostID AND
	P1.CreatedDate > @FromDate AND
	P1.CreatedDate < @ToDate AND
	P1.ThreadID = T.ThreadID AND
	P1.ThreadID = P2.PostID AND
	P1.UserID = U1.UserID AND
	P2.UserID = U2.UserID AND
	T.ForumID = F.ForumID AND
	F.GroupID = G.GroupID AND
	G.ModuleID = M.ModuleID	 AND
	PageIndex.IndexID <= @NumberOfThread
ORDER BY
	PageIndex.IndexID DESC
GO

/* Security Fixes */

GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_SearchGetResults TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_ThreadGetAll TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_ThreadGetCount TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_UserGetAll TO [public]
GRANT EXECUTE ON {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll TO [public]

/*
You will have to run this section manually, or modify it directly on the SQL Server

GRANT SELECT ON {databaseOwner}{objectQualifier}Forum_Threads TO {databaselogin}
GRANT SELECT ON {databaseOwner}{objectQualifier}Forum_Posts   TO {databaselogin}
GRANT SELECT ON {databaseOwner}{objectQualifier}Forum_Groups  TO {databaselogin}
GRANT SELECT ON {databaseOwner}{objectQualifier}Forum_Forums  TO {databaselogin}

*/

9/12/2005 9:35 PM

Chris Paterra

Joined: 4/5/2003

Posts: 4377

Re: Version 3.10.04: (was Problems: Invalid object name) Modified By Chris Paterra on 9/12/2005 9:39:20 PM

First, please don't label a post a bug simply because it is not what you consider proper, this is misleading to other members of the community. This has functioned properly in many enviroments already.

I am not slamming what you are saying in anyway, but what you consider proper sql scripting is not how it is done in dotnetnuke core and this is a core module. As your note to developers says about objectqualifier, the current dataprovider script does.

Until the core decides that what you suggest is proper, if ever, we will continue to do things the way we have been doing them.

(Please don't take offense, I understand the desire for higher security)

Chris Paterra

Get direct answers to your questions in the Community Exchange.

9/12/2005 11:54 PM

Patrick Timmons

Joined: 10/15/2004

Posts: 15

Re: Version 3.10.04 bug: (was Problems: Invalid object name)

No offense is taken, however, this error is occuring using the security guidelines that the DNN documentation tells us to use for shared hosting environments (page 9 of the 3.1.0 documentation).

As for how the Core implementation is done in 3.1.1, the install script is run with the context that is provided in the upgradeConnectionString (if provided). The 03.01.01.SqlDataProvider uses the EXEC(@SQL), which works just fine, however nowhere in the schema does it use EXEC(@SQL).

As I stated, I do appreciate simplicity. I am making no recommendation to change every single stored procedure to make use of the {databaseowner} for every object, nor am I suggesting rebuilding the core. What I am stating is that if you plan to use inline SQL, then you must be aware of the security problems related to it, and what must be done to ensure that the code functions across multiple installations, using all accepted and documented connection methods. So, in that context, I call this a bug.

As per the SQL documentation:

Permissions

EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.

Page 2 of 2

Problems: Invalid object name

These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
No Flaming or Trolling.
No Profanity, Racism, or Prejudice.
Site Moderators have the final word on approving / removing a thread or post or comment.
English language posting only, please.

Products

Solutions

Resources

Partners

Community

Blog

About

QA

Ideas Test

New Community Website

DNN Corp. (DotNetNuke)