Thanks to Michael:
When you don't have db_owner rights, all
SysObjects tables need the dbo owner prefix instead of
{databaseOwner} .
Michael Washington is aware of the issue, which will be fixed in version 04.00.30, thanks again Michael for your support!
Please substitute
03.03.00.SqlDataProvider with:
/************************************************************/
/*****
SqlDataProvider *****/
/*****
*****/
/*****
*****/
/***** Note: To
manually execute this script you must *****/
/***** perform a
search and replace operation *****/
/***** for
{databaseOwner} and {objectQualifier} *****/
/*****
*****/
/************************************************************/
/** Change
CreatedByUser column to an int for performance **/
ALTER TABLE
{databaseOwner}{objectQualifier}Surveys ADD
CreatedByUserID int NULL
GO
update
{databaseOwner}{objectQualifier}Surveys
set CreatedByUserID
= convert(int,CreatedByUser)
GO
ALTER TABLE
{databaseOwner}{objectQualifier}Surveys
DROP COLUMN
CreatedByUser
GO
ALTER TABLE
{databaseOwner}{objectQualifier}Surveys ADD
CreatedByUser int NOT NULL CONSTRAINT DF_{objectQualifier}Surveys_CreatedByUser
DEFAULT 0
GO
update
{databaseOwner}{objectQualifier}Surveys
set CreatedByUser =
CreatedByUserID
GO
ALTER TABLE
{databaseOwner}{objectQualifier}Surveys
DROP COLUMN
CreatedByUserID
GO
ALTER TABLE
{databaseOwner}{objectQualifier}Surveys
DROP
CONSTRAINT DF_{objectQualifier}Surveys_CreatedByUser
GO
/** Drop Existing
Stored Procedures **/
if exists (select *
from dbo.sysobjects where id =
object_id(N'{databaseOwner}[{objectQualifier}AddSurvey]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop
procedure {databaseOwner}{objectQualifier}AddSurvey
GO
if exists (select *
from dbo.sysobjects where id =
object_id(N'{databaseOwner}[{objectQualifier}GetSurvey]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop
procedure {databaseOwner}{objectQualifier}GetSurvey
GO
if exists (select *
from dbo.sysobjects where id =
object_id(N'{databaseOwner}[{objectQualifier}UpdateSurvey]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop
procedure {databaseOwner}{objectQualifier}UpdateSurvey
GO
/** Create Optimized
Stored Procedures **/
create procedure
{databaseOwner}{objectQualifier}AddSurvey
@ModuleID
int,
@Question
nvarchar(500),
@ViewOrder
int,
@OptionType
char(1),
@UserID
int
as
insert into
{objectQualifier}Surveys (
ModuleID,
Question,
ViewOrder,
OptionType,
CreatedByUser,
CreatedDate
)
values
(
@ModuleID,
@Question,
@ViewOrder,
@OptionType,
@UserID,
getdate()
)
select
SCOPE_IDENTITY()
GO
create procedure
{databaseOwner}{objectQualifier}GetSurvey
@SurveyID
int,
@ModuleID
int
as
select
SurveyID,
ModuleID,
Question,
ViewOrder,
OptionType,
CreatedByUser,
CreatedDate,
'Votes' = (
select sum(Votes) from {objectQualifier}SurveyOptions where
{objectQualifier}SurveyOptions.SurveyID = {objectQualifier}Surveys.SurveyID )
from
{objectQualifier}Surveys
where SurveyID =
@SurveyID
and ModuleID =
@ModuleID
GO
create procedure
{databaseOwner}{objectQualifier}UpdateSurvey
@SurveyID
int,
@Question
nvarchar(500),
@ViewOrder
int,
@OptionType
char(1),
@UserID
int
as
update
{objectQualifier}Surveys
set Question =
@Question,
ViewOrder =
@ViewOrder,
OptionType =
@OptionType,
CreatedByUser =
@UserID,
CreatedDate =
getdate()
where SurveyID =
@SurveyID
GO
ALTER TABLE
{databaseOwner}{objectQualifier}SurveyOptions ADD
IsCorrect
bit NOT NULL CONSTRAINT DF_{objectQualifier}SurveyOptions_IsCorrect DEFAULT
0
GO
if exists (select *
from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}GetSurveyOptions') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure
{databaseOwner}{objectQualifier}GetSurveyOptions
GO
if exists (select *
from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}AddSurveyOption') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure
{databaseOwner}{objectQualifier}AddSurveyOption
GO
if exists (select *
from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}UpdateSurveyOption') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure
{databaseOwner}{objectQualifier}UpdateSurveyOption
GO
create procedure
{databaseOwner}{objectQualifier}GetSurveyOptions
@SurveyID
int
as
select
SurveyOptionID,
ViewOrder,
OptionName,
Votes,
IsCorrect
from
{objectQualifier}SurveyOptions
where SurveyID =
@SurveyID
order by
ViewOrder
GO
create procedure
{databaseOwner}{objectQualifier}AddSurveyOption
@SurveyID
int,
@OptionName
nvarchar(500),
@ViewOrder
int,
@IsCorrect
bit
as
insert into
{objectQualifier}SurveyOptions (
SurveyID,
OptionName,
ViewOrder,
Votes,
IsCorrect
)
values
(
@SurveyID,
@OptionName,
@ViewOrder,
0,
@IsCorrect
)
select
SCOPE_IDENTITY()
GO
create procedure
{databaseOwner}{objectQualifier}UpdateSurveyOption
@SurveyOptionID
int,
@OptionName
nvarchar(500),
@ViewOrder
int,
@IsCorrect
bit
as
update
{objectQualifier}SurveyOptions
set OptionName =
@OptionName,
ViewOrder =
@ViewOrder,
IsCorrect =
@IsCorrect
where SurveyOptionID =
@SurveyOptionID
GO
if exists (select *
from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}AddSurveyResult') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure
{databaseOwner}{objectQualifier}AddSurveyResult
GO
if not exists (select *
from dbo.sysobjects where id =
object_id(N'{databaseOwner}{objectQualifier}SurveyResults') and
OBJECTPROPERTY(id, N'IsTable') = 1)
CREATE TABLE
{databaseOwner}{objectQualifier}SurveyResults (
[SurveyResultID] [int] IDENTITY (1, 1) NOT NULL ,
[SurveyOptionID] [int] NOT NULL ,
[UserID]
[int] NOT NULL
) ON
[PRIMARY]
GO
if not exists (select *
from dbo.sysobjects where id = object_id(N'PK_{objectQualifier}SurveyResults')
and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE
{databaseOwner}{objectQualifier}SurveyResults ADD CONSTRAINT
PK_{objectQualifier}SurveyResults PRIMARY KEY CLUSTERED
(
SurveyResultID
) ON
[PRIMARY]
GO
if not exists (select *
from dbo.sysobjects where id =
object_id(N'FK_{objectQualifier}SurveyResults_{objectQualifier}SurveyOptions')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE
{databaseOwner}{objectQualifier}SurveyResults ADD CONSTRAINT
FK_{objectQualifier}SurveyResults_{objectQualifier}SurveyOptions FOREIGN
KEY
(
SurveyOptionID
)
REFERENCES {databaseOwner}{objectQualifier}SurveyOptions
(
SurveyOptionID
) ON DELETE
CASCADE
NOT FOR
REPLICATION
GO
create procedure
{databaseOwner}{objectQualifier}AddSurveyResult
@SurveyOptionID
int,
@UserID
int
as
update
{objectQualifier}SurveyOptions
set Votes = Votes +
1
where SurveyOptionID =
@SurveyOptionID
insert into
{objectQualifier}SurveyResults ( SurveyOptionID, UserID )
values (
@SurveyOptionID, @UserID )
GO
/************************************************************/
/*****
SqlDataProvider *****/
/************************************************************/