How Build Web Application (asp.net + C# ) With Dynamic Database Schema?

Mar 26, 2008

Hi all,

I am doing an e-commerce project. This website will have a category of product. Each category will have sub-category. And sub-category may have another level of sub-category. This means the number of sub-category is not fixed. In the sub-category we will have products. Each product will have unpredicable number of properties (1, 2, 3 or many properties).

With the current requirment, I can know exactly the number of sub-category level and the number of properties. But the problem comes when later my boss add more category, sub-category ,... sub-category(more sub-category level), and product, as well as the products properties. At that time my database schema will not suitable for new category, products because the in can only design database with fixed number of sub-category level and fixed number of product properties(attributes or fiels in database).

Therefore, I want to ask all of you that
- Is there anyway to solve this problem?
- how to design database that meet extended requirements as I present above?

Thanks for all of your advices.

Quan.

View 17 Replies


ADVERTISEMENT

I've Build A Database Schema-Open For Suggestions

Apr 18, 2007

Hello Guys:I have included the schema that i build for sql serve 2005 below, My concern is with creating views and cursors.I open for any suggestions. Please try out the scheme;

/*
Created4/5/2007
Modified4/14/2007
Project
Model
Company
Author
Version
DatabaseMS SQL 2005
*/

use master
if exists (select name from sys.databases where name='clubDataBase')
drop database clubdataBase
go
create database clubDataBase
go
use clubDatabase

Create table [Role]
(
[Role_Id] Char(3) NOT NULL,
[Role_Type] Char(30) NOT NULL,
[Description] Char(30) NOT NULL,
Primary Key ([Role_Id])
)
go

Create table [Employee]
(
[employee_id] Char(20) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[Role_Id] Char(3) NOT NULL,
[Manager_Id] Char(20) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[HireDate] Datetime NOT NULL,
[PayRate] Real NOT NULL,
[Status] Varchar(30) NOT NULL,
[SSN] Char(12) NOT NULL,
[Fname] Char(30) NOT NULL,
[Lname] Char(30) NOT NULL,
[Gender] Char(1) NOT NULL,
[DOB] Datetime NOT NULL,
[LastUpdated] Datetime NOT NULL,
Primary Key ([employee_id])
)
go

Create table [Address]
(
[Add_Id] Char(10) NOT NULL,
[AddressLine1] Varchar(30) NOT NULL,
[AddressLine2] Varchar(30) NULL,
[City] Varchar(20) NOT NULL,
[State] Char(2) NOT NULL,
[ZipCode] Char(10) NOT NULL,
[Region] Char(20) NOT NULL,
Primary Key ([Add_Id])
)
go

Create table [Club]
(
[Club_Id] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[RegionId] Char(5) NOT NULL,
[ClubName] Varchar(30) NOT NULL,
[PhoneNumber] Char(13) NOT NULL,
[FaxNumber] Char(13) NULL,
[Email] Varchar(30) NOT NULL,
[WebSite] Varchar(30) NOT NULL,
[Description] Varchar(30) NOT NULL,
Primary Key ([Club_Id])
)
go

Create table [Equip_Inven]
(
[Equip_Inven_Id] Char(5) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Equip_Id] Char(5) NOT NULL,
[Purchase_Date] Datetime NOT NULL,
[Purchase_Cost] Money NULL,
Primary Key ([Equip_Inven_Id])
)
go

Create table [Equipment]
(
[Equip_Id] Char(5) NOT NULL, UNIQUE ([Equip_Id]),
[Equip_Name] Varchar(30) NOT NULL,
[Equip_Type] Varchar(20) NOT NULL,
[Equip_Desc] Varchar(30) NULL,
Primary Key ([Equip_Id])
)
go

Create table [CustomerClass]
(
[Club_Id] Char(5) NOT NULL,
[Class_Id] Char(5) NOT NULL,
[StartDate] Datetime NOT NULL,
[Member_Id] Char(10) NOT NULL,
[MemClassFees] Money NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Class_Id],[StartDate],[Member_Id])
)
go

Create table [Classes]
(
[Class_Id] Char(5) NOT NULL,
[ClassName] Varchar(30) NOT NULL,
[ClassDescription] Varchar(30) NOT NULL,
Primary Key ([Class_Id])
)
go

Create table [Member]
(
[Member_Id] Char(10) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Plan_Id] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[TotalFees] Money NOT NULL,
[MemberFname] Char(20) NOT NULL,
[MemberLname] Char(20) NOT NULL,
[Phonenumber] Char(12) NOT NULL,
[DOB] Datetime NOT NULL,
[Gender] Char(1) NOT NULL,
[Email] Char(20) NULL,
[LastUpdated] Datetime NOT NULL,
Primary Key ([Member_Id])
)
go

Create table [Health_Info]
(
[Member_Id] Char(10) NOT NULL,
[Doctor_Name] Varchar(20) NOT NULL,
[Allergy] Varchar(30) NULL,
[Diseases] Varchar(30) NULL,
[Emergency_Contact1] Char(20) NOT NULL,
[Emergency_Phone1] Char(12) NOT NULL,
[Contact_Relation1] Char(20) NOT NULL,
[Emergency_Contact2] Char(20) NULL,
[Emergency_Phone2] Char(12) NULL,
[Contact_Relation2] Char(20) NULL,
Primary Key ([Member_Id])
)
go

Create table [ClubClasses]
(
[StartDate] Datetime NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Class_Id] Char(5) NOT NULL,
[employee_id] Char(20) NOT NULL,
[ClassAgeRange] Char(10) NULL,
[ClubClassCost] Money NOT NULL,
[ClubClassSchedule] Varchar(30) NOT NULL,
Primary Key ([StartDate],[Club_Id],[Class_Id])
)
go

Create table [Plans]
(
[Plan_Id] Char(5) NOT NULL,
[SuggestedPrice] Money NOT NULL,
[PlanName] Varchar(30) NOT NULL,
[PlanDuration] Varchar(20) NOT NULL,
[Description] Varchar(30) NOT NULL,
Primary Key ([Plan_Id])
)
go

Create table [ClubPlans]
(
[Club_Id] Char(5) NOT NULL,
[Plan_Id] Char(5) NOT NULL,
[Discount] Numeric(2,2) NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Plan_Id])
)
go

Create table [MemberClubUsage]
(
[StartDate] Datetime NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[EndDate] Datetime NOT NULL,
Primary Key ([StartDate],[Club_Id],[Member_Id])
)
go

Create table [Facility]
(
[Facility_Id] Char(5) NOT NULL, UNIQUE ([Facility_Id]),
[FacilityName] Varchar(30) NOT NULL,
[FacilityType] Varchar(30) NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Facility_Id])
)
go

Create table [FacilityUsage]
(
[StartDate] Char(1) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Facility_Id] Char(5) NOT NULL,
[EndDate] Datetime NULL,
Primary Key ([StartDate],[Member_Id])
)
go

Create table [ClubFacilites]
(
[Club_Id] Char(5) NOT NULL,
[Facility_Id] Char(5) NOT NULL,
[OpenDate] Datetime NOT NULL,
[CloseDate] Datetime NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Facility_Id])
)
go

Create table [Equip_Maintanence]
(
[Equip_Maintanence_Id] Char(5) NOT NULL,
[Equip_Inven_Id] Char(5) NOT NULL,
[Main_Date] Datetime NULL,
[Main_Cost] Money NULL,
[Comment] Varchar(30) NULL,
Primary Key ([Equip_Maintanence_Id])
)
go

Create table [Guest]
(
[GuestId] Char(5) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[GuestName] Char(20) NOT NULL,
[GuestDOB] Datetime NOT NULL,
Primary Key ([GuestId])
)
go

Create table [RegionalOffice]
(
[RegionId] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[RegionName] Nvarchar(30) NOT NULL,
Primary Key ([RegionId])
)
go


Alter table [Employee] add foreign key([Role_Id]) references [Role] ([Role_Id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Manager_Id]) references [Employee] ([employee_id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([employee_id]) references [Employee] ([employee_id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Club] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Member] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Guest] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [RegionalOffice] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Equip_Inven] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubPlans] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [MemberClubUsage] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubFacilites] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [Equip_Maintanence] add foreign key([Equip_Inven_Id]) references [Equip_Inven] ([Equip_Inven_Id]) on update no action on delete no action
go
Alter table [Equip_Inven] add foreign key([Equip_Id]) references [Equipment] ([Equip_Id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([Class_Id]) references [Classes] ([Class_Id]) on update no action on delete no action
go
Alter table [CustomerClass] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [MemberClubUsage] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [FacilityUsage] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [Health_Info] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [Guest] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [CustomerClass] add foreign key([StartDate],[Club_Id],[Class_Id]) references [ClubClasses] ([StartDate],[Club_Id],[Class_Id]) on update no action on delete no action
go
Alter table [ClubPlans] add foreign key([Plan_Id]) references [Plans] ([Plan_Id]) on update no action on delete no action
go
Alter table [Member] add foreign key([Club_Id],[Plan_Id]) references [ClubPlans] ([Club_Id],[Plan_Id]) on update no action on delete no action
go
Alter table [ClubFacilites] add foreign key([Facility_Id]) references [Facility] ([Facility_Id]) on update no action on delete no action
go
Alter table [FacilityUsage] add foreign key([Club_Id],[Facility_Id]) references [ClubFacilites] ([Club_Id],[Facility_Id]) on update no action on delete no action
go
Alter table [Club] add foreign key([RegionId]) references [RegionalOffice] ([RegionId]) on update no action on delete no action
go

--create procedures and views
--the procedure to randomly add members visit the club
create procedure getMemberClubUsage
as
declare @memID char(10),
@clubID char(10)
declare @begDate datetime
declare mem_club_cursor cursor
--randamly to add the member to visit clubs
for
select member_ID, c.club_ID
from member, club c
open mem_club_cursor
fetch mem_club_cursor into
@memID, @clubID

while @@fetch_status=0
begin
set @begDate=dateadd(day, rand()*-365, getdate())
insert into memberClubUsage
select @begDate, @clubID, @memID, dateadd(minute, rand()*300, @begDate)
fetch mem_club_cursor into
@memID, @clubID
end

close mem_club_cursor
deallocate mem_club_cursor

go
--the procedure randomly add the member to classes
create procedure getMemberClubClass
as
insert into customerClass (startDate, club_ID, class_ID, Member_ID, memclassFees)
select startDate, c.club_ID, class_ID, Member_ID, 150
from member m, clubClasses c
go

create view MembershipClubRegion_VW
as
select member_ID, memberFName, memberLName, C.Club_ID, C.ClubName, c.RegionID, r.RegionName
from member m join club c on m.club_ID=c.Club_ID
join regionaloffice r on c.regionID=r.regionID
go

create view VistorbyMonthClub_VW
as
select clubName,
datename(month, startdate)+'-'+datename(year, startdate) 'Month',
count(*) TotalVistors
from club c join memberclubusage mc on c.club_ID=mc.club_ID
group by clubName, datename(month, startdate)+'-'+datename(year, startdate)

go

create view classClub_View
as
select c.class_ID,
className,
clubName,
startDate
from classes c join clubClasses cc on c.class_ID=cc.class_ID
join club cl on cc.club_ID=cl.club_ID
go
--add testing data into database
--insert data to table Address
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0001', '100 Washington St', 'Salem', 'NH', '01123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0002', '180 Porland Ave', 'Providence', 'RI', '03123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0003', '36 Huntington Ave', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0004', '45 Marine St', 'August', 'ME', '31245', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0005', '400 Tel St', 'Vermont', 'VT', '11234', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0006', '100 Abc Ave', 'George Town', 'CT', '1111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('B0001', '180 Matthew Ave', 'Worchest', 'NH', '01145', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('B0002', '1000 Elm St', 'Braintree', 'MA', '02184', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0001', '24 Edwin St', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0002', '57 Sterling Street', 'Malden', 'MA', '02148', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0003', '26 Royal St', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0004', '439 Newport Ave', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0005', '41 Old Brattleboro', 'August', 'ME', '31245', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0006', '110 N Central Ave', 'Montpelier', 'VT', '11234', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0007', '67 Marjoril Rd', 'George Town', 'CT', '1111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0008', '1 Holden Row #2', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0009', '67 Marjoril Rd', 'Braintree', 'MA', '02421', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0010', '253 Cambridge St Apt#2', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0011', '30 Hill St', 'Randolph', 'MA', '02134', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0012', '87 Ocean St', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0013', '7A Park Terrace', 'Boston', 'MA', '02112', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0014', '8 FoxRun #12', 'Boston', 'MA', '02113', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0015', '45 Upland Road', 'Boston', 'MA', '02114', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0016', '378 Medford St', 'Boston', 'MA', '02115', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0017', '46 Wheatland St', 'Boston', 'MA', '02116', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0018', '177 North Street', 'Boston', 'MA', '02117', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0019', '280 Breadway #4', 'Boston', 'MA', '02118', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0020', '106 Chester St', 'Boston', 'MA', '02119', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0021', '45 Upland Rd', 'Boston', 'MA', '02120', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0022', '47 Malvern St', 'Boston', 'MA', '02121', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0023', '49 Dwight St #1', 'Boston', 'MA', '02122', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0024', '97 LAWN STREET', 'Boston', 'MA', '02123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0025', '418 Revere Beach Pkwy #74', 'Boston', 'MA', '02124', 'NE')
go
--insert data to table RegionalOffice
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0001', 'C0001', 'New Hampshire')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0002', 'C0002', 'Rhode Island')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0003', 'C0003', 'Massachussetts')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0004', 'C0004', 'Marine')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0005', 'C0005', 'Vermont')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0006', 'C0006', 'Connecticut')
go
--insert data to table Club
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0001', 'C0001', 'R0001', 'NH CLub1', '8952645456', 'mail@ClubNH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0002', 'B0001', 'R0001', 'NH CLub2', '8953566889', 'mail@ClubNH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0003', 'C0003', 'R0003', 'MA Club1', '6174561787', 'mail@MANH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0004', 'B0002', 'R0003', 'MA Club2', '6175688989', 'mail@MANH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0005', 'C0002', 'R0002', 'RI Club1', '4264567898', 'mail@ClubRI', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0006', 'C0004', 'R0004', 'Marine Club 1', '8915678989', 'mail@clubME', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0007', 'C0005', 'R0005', 'Vermont Club1', '7325641234', 'mail@clubVT', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0008', 'C0006', 'R0006', 'Connecticut Club1', '7888989999', 'mail@clubCT', 'http://www.Club1NH.org', 'club')
go
--insert data to table Plans
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0001', 156.38, 'Family Plan', '1 year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0002', 90, 'Single Plan', '1 Year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0003', 70, 'Senior Plan', '1 Year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0004', 30, 'promote plan', '1 year', 'plan')
go
--insert data to table ClubPlans
insert into ClubPlans ( Club_id, Plan_id) values ('C0001', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0002', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0004', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0005', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0006', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0007', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0008', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0001', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0002', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0004', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0005', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0006', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0007', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0008', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0004')
go
--insert data to table Classes
insert into Classes ( Class_id, className, classDescription) values ('A0001', 'Beginning Yoga', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0002', 'Advance Yoga', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0003', 'Swimming', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0004', 'Dancing', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0005', 'Introduction to Tennis', 'desc')
go
--insert data to table Role
insert into Role ( Role_id, Role_Type, Description) values ('R01', 'Teacher', 'Class teacher')
insert into Role ( Role_id, Role_Type, Description) values ('R02', 'Manager', 'club manager')
go
--insert data to table Employee
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0001', 'A0001', 'R01', 'E0001', 'C0001', '1/1/1999', 15, ' ', '123456789', 'John', 'Joyce', 'M', '1/3/1968', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0002', 'A0002', 'R02', 'E0001', 'C0002', '12/1/2004', 25, ' ', '123456789', 'MacDonld', 'Harris', 'M', '10/12/1975', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0003', 'A0003', 'R02', 'E0001', 'C0003', '3/1/2006', 20, ' ', '123456789', 'Joane', 'Li', 'F', '11/5/1960', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0004', 'A0004', 'R02', 'E0001', 'C0004', '5/1/2006', 22, ' ', '123456789', 'Tony', 'Green', 'M', '8/12/1970', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0005', 'A0005', 'R02', 'E0001', 'C0005', '1/1/2007', 20, ' ', '123456789', 'Lisa', 'White', 'F', '11/10/1975', '4/18/2007')
go
--insert data to table ClubClasses
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('1/1/2007', 'C0001', 'A0001', 'E0002', 1500, 'Mon, Fri 7:30PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('2/1/2007', 'C0002', 'A0002', 'E0003', 1500, 'Mon, Fri 7:30PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('3/1/2007', 'C0003', 'A0003', 'E0004', 1500, 'Tue, Th 8:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('4/1/2007', 'C0004', 'A0004', 'E0005', 1500, 'Tue, Th 8:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('5/1/2007', 'C0005', 'A0005', 'E0002', 1500, 'Wed, Fri 7:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('4/5/2007', 'C0006', 'A0004', 'E0004', 1500, 'Sat, 8:00AM')
go
--insert data to table Member
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0001', 'C0001', 'P0001', 'A0006', 153.38, 'John', 'He', '7812671567', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0002', 'C0002', 'P0001', 'A0007', 153.38, 'Joane', 'Good', '5678945689', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0003', 'C0003', 'P0001', 'A0008', 153.38, 'Sharon', 'Spears', '8123456789', '1/17/1985', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0004', 'C0004', 'P0001', 'A0009', 153.38, 'Sandy', 'Green', '9874567898', '2/18/1965', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0005', 'C0005', 'P0001', 'A0010', 153.38, 'Lisa', 'White', '7185642356', '1/19/1967', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0006', 'C0006', 'P0001', 'A0011', 153.38, 'William', 'Black', '5689741235', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0007', 'C0007', 'P0001', 'A0012', 153.38, 'Ollie', 'Green', '7812671567', '1/21/1975', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0008', 'C0008', 'P0001', 'A0013', 153.38, 'Louis', 'Brien', '5678945689', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0009', 'C0001', 'P0002', 'A0014', 90, 'Barbara', 'Stone', '8123456789', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0010', 'C0002', 'P0002', 'A0015', 90, 'Jenat', 'Jean', '9874567898', '1/17/1985', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0011', 'C0003', 'P0002', 'A0016', 90, 'Orlande', 'Moore', '7185642356', '2/18/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0012', 'C0004', 'P0002', 'A0017', 90, 'Jerry', 'Dee', '5689741235', '1/19/1967', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0013', 'C0005', 'P0002', 'A0018', 90, 'Jim', 'Smith', '7812671567', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0014', 'C0006', 'P0002', 'A0019', 90, 'Jeff', 'Smith', '5678945689', '1/21/1975', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0015', 'C0007', 'P0002', 'A0020', 90, 'Michael', 'Sam', '8123456789', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0016', 'C0008', 'P0002', 'A0021', 90, 'Michelle', 'Lee', '9874567898', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0017', 'C0003', 'P0004', 'A0022', 30, 'Judy', 'Andy', '7185642356', '1/17/1985', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0018', 'C0001', 'P0001', 'A0023', 153.38, 'Pierre', 'Pierre', '5689741235', '2/18/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0019', 'C0002', 'P0001', 'A0024', 153.38, 'Tony', 'Kelliher', '7812671567', '1/19/1967', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0020', 'C0003', 'P0001', 'A0025', 153.38, 'Antonia', 'Sheeran', '5678945689', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0021', 'C0004', 'P0001', 'A0007', 153.38, 'Scott', 'Brown', '8123456789', '1/21/1975', 'M', '4/18/2007')
go

exec getMemberClubUsage
exec getMemberClubClass
Set quoted_identifier on
go


Set quoted_identifier off
go

View 5 Replies View Related

Build Database Application On Internet With ISP?

Jul 20, 2005

I have been building ASP, ASP.NET and Crystal Reports on SQL serverfor a few years now.I have build them mostly on an Intranet or with companies that havetheir own web servers.I now need to build an application for a company that doesn't not havea web server. Just a small shop that wants an internet application.How do I build an database application when the site will be host byan ISP? The database will be SQL Server 2000, but located locally.The site will be maintained by the ISP? How does the an applicationhosted by an ISP interact with a database located locally (client'ssite)?If someone could point me toward some tutorial or tell me how to dothis, I would appreciate it.

View 1 Replies View Related

Transact SQL :: How To Build Dynamic WHERE Clause In Openquery To Linked Database

Jul 17, 2015

I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.

SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011''  in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)

View 9 Replies View Related

Database Schema For HelpDesk Application

Nov 15, 2005

helloi have to make helpdesk application for teh IT Department of my company,so if anyone can help by supporting me by database schema for HelpDesk application.thank you for the help

View 1 Replies View Related

How Do You Build A Dynamic WHERE Statement?

Mar 9, 2006

I have 5 drop down lists and 1 text box, and I need to build the WHERE portion of my SELECT statment (stored procedure). the drop down lists are named client, ptype, apptdate, inspdate, state, and the textbox is named text. they all need to be this=something AND that=another AND...AND text LIKE mytext.
How would I go about building this efficiently?
Would I Declare a bit value in the sp called WhereSet = 0
IF @client IS NOT NULL     IF @WhereSet = 0          SET @Where = 'WHERE ClientID=@client'          SET @SetWhere = 1    ELSE          SET @Where = @Where + ' AND CleintID=@client'    .    .    ....
Or would this be a lot easier using adhoc SQL instead of a Stored Procedure?
(note: I am using a SQL DataSource)
Please help, I am going bald from pulling my hair our...

View 2 Replies View Related

Build Dynamic WHERE Clause

Apr 14, 2008

I have a stored procedure which expects one parameter @Company
The variable @Company holds pipe delimited value: "CNN|AOL|ABC"

I need to build a WHERE clause by parsing @Company value, so the select will look like below:

SELECT *
FROM Company
WHERE CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'CNN')
AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'AOL')
AND CompanyID IN (SELECT DISTINCT(CompanyID) FROM v_Company WHERE CompanyName = 'ABC')

Thanks for your help

View 2 Replies View Related

Build Dynamic WHERE Clause

Apr 14, 2008

Hello,

I have a stored procedure which expects one parameter @Company
The variable @Company holds pipe delimited value: "CNN|AOL|ABC"

I need to build a WHERE clause by parsing @Company value, so the select will look like below:

SELECT *
FROM Company
WHERE CompanyName = 'CNN'
AND CompanyName = 'AOL'
AND CompanyName = 'ABC'


P.S I know that above select doesn€™t really make sense , but I have a bigger query that would be hard to explain in this topic so I just simplified it.

Thank you

View 8 Replies View Related

Problems When Trying To Build Connection To SQL Server Express From ASP.NET Application

Aug 2, 2006

Hi,I have a problem, when I want to access to my SQL Server Expressdatabase from my ASP.NET application.My workstation ID is KITOLAP-HPMy username is user01Now I built the following connection String (VB.NET):Dim workstation As String =System.Environment.GetEnvironmentVariable("computername")connectionString = "workstation id=""" & workstation & """;packetsize=4096;user id=user01;integrated security=SSPI;data source=""" &workstation & "SQLExpress"";persist security info=True;initialcatalog=webshop"connection = New SqlConnection(connectionString)Although I put as user id "user01" the application makes an error whenI try to open the connection, saying that the login with user "aspnet"failed.How can I change it so that as login-user "user01" is taken and not"aspnet" or otherwise, how can I create a new user in the SQL Serverdatabase with which I could access the DB??thanks,kito

View 1 Replies View Related

Build An Application In C# With Using Mining Model For Viweing Prediction Value

Dec 28, 2007


HI
Thanks a lot for your answer
I say my request but I don€™t know why anybody don€™t answer me
I have a project about predicting a value about selling and buying of a good like t-shirts and I use data mining for my project ,so I should use time series algorithm ,that €˜s mean I have previous data about t-shirts for 11 months ago and now I should say for 12th month ,how many t-shirts are sale?
My tables saved on the excel file and it is problem, because how should I use this table for building model?
After building a model and structure and predicting the value of 12th month for this store in the mining model then I use this query in model in bi:
€œSELECT
PredictTimeSeries(amount)
From
[Forecasting]€?
This query showed a column and prediction value.
After all of that now I should show this value in the application so I use c# language for building it, so I use a form in c# then I add a button to form that with clicking on this button, I can connect to my structure and then show process of connecting in the panel, then with clicking on the other button I can use this query
(€œSELECT
PredictTimeSeries(amount)
From
[Forecasting]€?
) and after using I can see prediction value in the textbox, that€˜s mean the value of 12th month show on textbox.
Form has two buttons: one button is for connecting to mining structure and other button is for sending PredictTimeSeries query to structure, one textbox, which is for showing the predicting value for 12th month for selling t-shirts, one panel for viewing the lift chart.
Also you say
You are building an application that programmatically creates mining structure and a model, and then you want to train the model and display some results.
And I don€™t know how can I train my model, I should use a special code for it?
If your answer is say, please explain that and then say that codes are for training.
Please if you can send c# code for Sporadic of stage, please send that, I need to this code; my request is emergency for me.
Thanks a lot
i am very sad because any body don't answer me

View 1 Replies View Related

Where To Build Client Application For SQL 2005 Standard Edition

Jul 24, 2006

Hi,
I have a database working on SQL Server 2005 Standart Edition and i want to create a client windows form aplication.
My problem is where to do it? Im trying to use MS visual basic 2005 express edition but it doent support SQL Server 2005 Standart Edition, it only supports SQL 2005 express edition.
What should i do ?
And in the visual studio 2005 that comes with the package of SQL Server 2005 standart edition there is no project of a winodws form.
Please help

View 8 Replies View Related

Trying To Build Dynamic Stored Procedure

Jun 6, 2008

My existing ASP 1.0 site keeps getting hacked using SQL injections.  I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search.
I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck.  Getting either errors or double LIKE statements. Need some help.
string[] SqlKWSrch; 
SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ',');     int AStop = SqlKWSrch.Length;     int i = 0;        foreach( string a in SqlKWSearch )       {           if (i <= AStop)           {               SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND ";           }           else           {               SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' ";           }            i++;       }
1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure
GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion.
PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1
RETURN
 
Thank you
 

View 5 Replies View Related

Build Dynamic Query Using Sp_executesql

Sep 20, 2004

Hi there,

I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

EXEC master..sp_executesql @SQLString

And here's the error message that I get:

Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.

I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

Can anyone help?

Thanks

View 1 Replies View Related

Build SQL Query From Dynamic Checkbox List

Nov 26, 2003

Not sure if this is the place to post this, but here goes.

I need to setup an options screen where my customers can customize which locations will be stored for their user id when pulling reports. I have checkbox list that dynamically loads their locations. I need to store the selected checkbox items in my table and then each time they login in to run a report, it will use the stored Location values in my SQL query.

Synopsis:
Selected locations stored in table. When the report is ran, the location values are pulled and added to my queries WHERE clause.

Thanks.

View 1 Replies View Related

Build Dynamic Table Columns Issue

Jun 4, 2004

How I can build a dynamic temp table based upon the dynamic coulmn info from the other table? Please see my attached file as an example. Thanks!

J827

View 4 Replies View Related

SQL Server 2012 :: Build Search Condition Dynamic

Oct 6, 2015

IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)
INSERT INTO #test
SELECT '1'

[code]....

i am trying to build a dynamic where "or" clause finding difficulties.

View 7 Replies View Related

Dynamic Build SQL In Store Procedure Based On Select

Jul 23, 2005

I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL

View 3 Replies View Related

The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.

Sep 27, 2007

Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'.  However, the
current database schema is not compatible with this version.  You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!

View 4 Replies View Related

Dynamic Field DB Schema Brainstorming

Sep 6, 2005

Hello - Have a project where we are going to build a form creation application.  (ASP.NET).  This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.This is a fairly common thing.  Are there any resources out there as to where to start designing the DB schema?  I'm not looking to reinvent the wheel.  Here's the basic objects I'm seeing:TablesFormsFormFieldNamesFormFieldTypesFormFieldJSAny tips on the right direction to go?ThanksRob

View 4 Replies View Related

Dynamic XSD Schema Location For XML Source

Jul 14, 2006

I want to be able to programmatically set the schema location for an XML source. I first thought it would be a simple task using expressions and variables but it doesn't appear to allow anything in the way of setting it at runtime. Is this possible?

View 4 Replies View Related

Database Schema Compatible With Schema Version '1'

Apr 12, 2008

Hello everybody!I'm using ASP.NET  3.5,  MSSQL 2005I  bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?

View 2 Replies View Related

Problem With Using Dynamic SQL To CREATE XML SCHEMA COLLECTION In SQL 2005

Apr 8, 2008

This works in SQL Server 2005:CREATE XML SCHEMA COLLECTION Version2_1 AS'<xs:schema xmlns="http://www.icpsr.umich.edu/DDI"     xmlns:xs="http://www.w3.org/2001/XMLSchema"    xmlns:doc="http://www.icpsr.umich.edu/doc"     targetNamespace="http://www.icpsr.umich.edu/DDI"    elementFormDefault="qualified" attributeFormDefault="unqualified">    <xs:annotation>        <xs:documentation>            This is a w3c Schema "Technical Implementation" of the DDI Conceptual Specification.             This schema is intended for use in producing electronic versions of codebooks for quantitative social science data.            CVS Revision information: $Header: /cvsroot/ddi-alliance/ddi/w3c/Version2-1.xsd,v 1.10 2007/07/31 19:03:54 mdiggory Exp $         </xs:documentation>    </xs:annotation></xs:schema>'  This version, however, gives errors: DECLARE @sql VARCHAR(max)SET @sql ='CREATE XML SCHEMA COLLECTION Version2_1 AS <xs:schema xmlns="http://www.icpsr.umich.edu/DDI" xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:doc="http://www.icpsr.umich.edu/doc" targetNamespace="http://www.icpsr.umich.edu/DDI"elementFormDefault="qualified" attributeFormDefault="unqualified"><xs:annotation>    <xs:documentation>        This is a w3c Schema "Technical Implementation" of the DDI Conceptual Specification.         This schema is intended for use in producing electronic versions of codebooks for quantitative social science data.        CVS Revision information: $Header: /cvsroot/ddi-alliance/ddi/w3c/Version2-1.xsd,v 1.10 2007/07/31 19:03:54 mdiggory Exp $     </xs:documentation></xs:annotation></xs:schema>'EXEC (@sql)Ultimately, I am trying to create a stored procedure that will take as parameters the name of the new schema and the .xsd file it comes from, so that I can use it in code.  I am a novice, so all explanations would be greatly appreciated.Thanks,Ed Graham 

View 3 Replies View Related

Error On Build Solution, If SSIS Project Is Part Of Build

Mar 22, 2006

Hello,

we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..

C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild

Through build process we get a error:


External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):

We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?

with best regards

Anton Kalcik

View 5 Replies View Related

How Do You Build An Enterprise Database Solution?

Feb 1, 2008

Hi, I am noob, so excuse me if this is a stupid question, but I just bought a Learn HTML in 3 Hours book and a learn SQL in 10 minutes book and I was wondering how I would start to go about to build a super flexible enterprise wide web based database application, that is so flexible the users can put in any old crap in the front end and make up new fields as they go. Another one of my requirements is that it perform super fast and be distributed securely across my international multi billion dollar a year company.

So like I know Excel and a little VBA and I know how to use the query builder in Access and I landed this big old contract to revamp this old comapny's infrastructure. Should be pretty easy, right? Everyone is doing it.

So can you guys like help me out for free and stuff?

View 4 Replies View Related

Fastest Way To Build A Database Out Of SQL Scripts

Jul 23, 2005

What is the fastest way to generate an SQL 2000 databaseout of SQL scripts.The SQL scripts contain the create tables, views, storedprocedures, triggers, constraints, and the tables DATArecords.What are my options? isql? osql? are there other ways?Thank you

View 6 Replies View Related

DB Design :: Bat File To Build A Database?

Dec 1, 2015

I have a SQL script folder on my HDD. The folder contains SQL Scripts and subfolders with Constraints and Extended properties. I need a BAT file that builds a database on my SQL Server instance.

I have the following structure:

 Tables (folder)
  dbo.Customers.sql
  dbo.OrderLines.sql
  dbo.Orders.sql
  dbo.Products.sql
  Constraints (folder)
           Foreign Keys(folder)
dbo.OrderLines.FK.sql
dbo.Orders.FK.sql
  Extended Properties(folder)
     dbo.Customers.EXTPRP.sql
     dbo.Products.EXTPRP.sql

The Server name for the SQL CMD is PCMSSQL2014? user: sa pass is empty.

View 3 Replies View Related

I Need To Generate The Query To Build A Database I've Designed. Please Help..

Apr 3, 2007

I've registered with GoDaddy for my site. I've used Visual Web Dev 2005 with SQL Server Mgmt Express 2005 to create my database etc. Now with GoDaddy, I need to "recreate" the database on their server again, but I don't want to go throug the whole process again. I just want to use their query editor to generate the database online.GoDaddy mentions a "personal-sql" file that SQL Server 2000 generates, but I can't find anything like that with what I have. Thanks.

View 1 Replies View Related

Want To Run Stored Procedures To Build Maintenance Database

May 13, 2015

create all the maintenance scripts (SProcs) to my maintenance databases in an automated way?I have around 12 procedures which I don't want to consolidate in a single Proc and execute.Another problem which is coming to my mind is, I can';t use the below method to execute the proc, as I dont have RDP access to the servers.

sqlcmd -i C:MyFolderMyScript.sql -o C:MyFolderMyOutput.txt

I am thinking to ask for a central location where I can create a folder , dumping all my scripts and build the maintenance DB and creating all my procs, table, etc.

View 1 Replies View Related

How To Build Database To Support User-specified Attributes?

Jul 23, 2005

I have a database that tracks players for children's sports clubs. I haveincluded representative DDL for this database at the end of this post.A single instance of this database supports multiple clubs. I would like toadd support for letting each club define and store custom information abouttheir players. Basically, allows the clubs to define custom attributes forplayers (e.g. "height", "weight", "favourite sweet").A few constraints:1. Any attributes defined is "private" to the defining club. Other clubsaren't aware of it although they may define custom attributes of their ownwith the same name and type. [Perhaps there is a way to share definitions ofidentical attributes?]2. A club doesn't have to define any custom attributes.Has anyone done anything similar?. Any ideas on how it might be done?Kunle=================== BEGIN DDL ===================CREATE TABLE FootballClub (Club_ID int IDENTITY,Name char(80) NOT NULL,Area char(4) NOT NULL,League char(4) NOT NULL,City char(30) NOT NULL,PRIMARY KEY (Club_ID))goexec sp_primarykey FootballClub,Club_IDgoCREATE TABLE Player (Player_ID int IDENTITY,First_Name char(30) NOT NULL,Initials char(30) NULL,Last_Name char(30) NOT NULL,Date_Of_Birth datetime NOT NULL,Position char(4) NULL,Club_ID int NULL,PRIMARY KEY (Player_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey Player,Player_IDgoCREATE TABLE UserAccount (User_ID int IDENTITY,Club_ID int NOT NULL,FullName char(80) NOT NULL,Logon char(20) NOT NULL,PWD_Hash char(60) NOT NULL,PRIMARY KEY (User_ID, Club_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey UserAccount,User_ID,Club_IDgoexec sp_foreignkey Player, FootballClub,Club_IDgoexec sp_foreignkey UserAccount, FootballClub,Club_IDgo=================== END DDL ===================

View 3 Replies View Related

How To Build Database To Support User-specified Entities And Attributes?

Jul 23, 2005

I have a database that tracks players for children's sports clubs. I haveincluded representative DDL for this database at the end of this post.A single instance of this database supports multiple clubs. I would like toadd support for letting each club define and store custom information aboutarbitrary entities. Basically, allows the clubs to define custom entities(i.e tables) and associated custom attributes (i.e. fields) that may berelated to existing tables (such as Player and FootballClub) or existingentities. For instance, a club may define a PlayerAssessment entity thatrecords all player assessments.To do this, I plan to support the following use case:1. FootballClub admin creates a new entity and gives it a name anddescription (Entity is only accessible to this FootballClub).2. FootballClub admin indicates that the new entity has a M:1 relationshipwith the Player table (this will add Player_ID as a FK attribute).- {An entity may have no relationships.}- {Relationships are also supported to other entities.}3. FootballClub admin specifies the names and domain/types of any dataattributes (i.e. fields) of the entity.- {An attribute's type may be constrained to a few allowable types likeRelationship, Integer, Float, Currency, Date, Time, DateTime, Name,Description and Memo.}4. System creates entity as specified.A few constraints:1. Any entity defined is "private" to the defining club. Other clubs aren'taware of it although they may define custom entities of their ownwith the same name and attributes. [Perhaps there is a way to sharedefinitions of identical entities?]2. A club doesn't have to define any custom entities.Ideas I've considered:1. Generate DLL and create actual tables- Restrict such customizations such that while admin is setting up entities,no other user is allowed to use the system.- Once entity definition is complete, generate an actual table using DLL.Table and column names might be changed to enforce uniqueness/validityconstraints - this suggests a need for table/column name mapping.- PROS: Easy to implement.- CONS: Doesn't scale since only a limited number of tables can be created.DDL on a live, shared system?. Scary!!All users for all clubs will be locked out while entity iscreated.2. Generate DDL and create actual tables in secondary database(s)- Same as above except that the user tables are created in secondary [,shared] databases.- PROS: Reassurance that DDL is never run on the "core" dataAll users don't have to be locked out.- CONS: Doesn't scale since only a limited number of tables can be created.{ Unless I start creating additional databases too!. }Still needs to DDL on a live, shared system.Has anyone done anything similar?. Any ideas on how it might be done?. Inparticular, is this possible without having to execute DDL on the livedatabase?Kunle=================== BEGIN DDL ===================CREATE TABLE FootballClub (Club_ID int IDENTITY,Name char(80) NOT NULL,Area char(4) NOT NULL,League char(4) NOT NULL,City char(30) NOT NULL,PRIMARY KEY (Club_ID))goexec sp_primarykey FootballClub,Club_IDgoCREATE TABLE Player (Player_ID int IDENTITY,First_Name char(30) NOT NULL,Initials char(30) NULL,Last_Name char(30) NOT NULL,Date_Of_Birth datetime NOT NULL,Position char(4) NULL,Club_ID int NULL,PRIMARY KEY (Player_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey Player,Player_IDgoCREATE TABLE UserAccount (User_ID int IDENTITY,Club_ID int NOT NULL,FullName char(80) NOT NULL,Logon char(20) NOT NULL,PWD_Hash char(60) NOT NULL,PRIMARY KEY (User_ID, Club_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey UserAccount,User_ID,Club_IDgoexec sp_foreignkey Player, FootballClub,Club_IDgoexec sp_foreignkey UserAccount, FootballClub,Club_IDgo=================== END DDL ===================

View 3 Replies View Related

SQL Server 2005 Express: The Database Principal Owns A Schema In The Database, And Can Not Be Dropped.

Jan 11, 2006

I recently added a new user to my database.  Now I want to delete that user, but I keep getting the error above.  What do I need to do to delete my recently added user?

View 4 Replies View Related

SELECT Permission Denied On Object 'TableID', Database 'Database', Schema 'dbo'

Mar 21, 2007


The error message:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'TestID'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

The log file reads:

---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.

***Background***

General Users got an error message when trying to access any reports we have created.
All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors


I'll add this from the report logs...

w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed.
Login failed for user €˜DOMAINUsername€™.

The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.


I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)

View 3 Replies View Related

How To Export Whole Database Schema To Another Database But Not Include The Data?

Sep 21, 2007


I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data.
Does anyone know how to do that?
Many thanks for replying.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved