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


ADVERTISEMENT

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

Cannot Build SQL Server Mobile Solution For Windows Mobile 5

May 18, 2006

Using MS VS 2005 (incl SQL Server Mobile)
MS Pocket PC 2005 SDK

I am working on a project that builds for Pocket PC on both Mobile 2003 and Mobile 5. The project uses/will use SQL Server Mobile to store local data.

Project created from new with support for both platforms. I include required header files ssceerr30.h and ssceoledb30.h.

Project builds fine in WM2003 configuration, release and debug.

When I build for WM5 the compiler cannot find the header file transact.h. This is included from within ssceoledb30.h. Same as under WM2003.

In WM2003 configuration if I highlight the ssceoledb30.h include in Visual Studio and open the header, it takes me to <Visual Studio dir>SmartDevicesSDKSQL ServerMobilev3.0. I then locate the include for transact.h and do the same, which takes me <Visual Studio dir>SDKPocketPC2003include. The file exists.

If I repeat the above 'browsing' under the WM5 configuration, ssceoledb30.h takes me to a different copy in the WM5 SDK directory. There is no diff between the file here and the other copy used by WM2003. If I attempt to open transact.h - file does not exist.

Fix (which I'm not too sure about, i.e. is it OK?) - If I copy transact.h to the WM5 SDK directory, the project builds.

Why has transact.h disappeared from WM5 SDK?
I can find no ref's to this problem anywhere. Is my installation of the WM5 SDK corrupt? What else could I be missing?
Is there a sample for SQL Server mobile (like the NorthwindOLEDB sample) that comes configured to build for WM5?

View 1 Replies View Related

Add New Database To Solution

Mar 20, 2006

I'm running VS 2005 Enterprise, and SQL Server 2005 Developer / workgroup edition.I'm trying to follow a few online examples to play around, and some of these examples require that you add a SQL DB to your solution by going:-right click on website-Add new Item-Add SQL DataBase.WHen I do that, I get the error that SQL Express isn't installed.  Now the question is, why would I want SQL Express installed when I have SQL server 2005 already installed? My Machine can support it, but it's sort of a waste when I need to run SQL Server 2000, SQL Server 2005 and then SQL Server express all at the same time.Any advice?

View 3 Replies View Related

Help With Database Solution

Aug 4, 2006

I volunteer for a non-for profit group and they have alot of names in amultiple databases. the problem is that some people are in multipledatabases. and if they send out a mailing from multiple databases somepeople get 2 and 3 of the same thing. my idea was to either make or find aprogram that has everyone is 1 database and then make the people part ofmultiple groups. (ex: bill is part of group a and b and im doing a mailingfrom both group i only want 1 piece of mail to go to him).It can be a separate program or with access. that use access now but i cantfigure out how to make my idea work.any question please ask.tiaStephen

View 2 Replies View Related

Large Database Solution

Jun 15, 2000

Hi Everybody:

We now host 200 G data in SQL Server 7.0 in MSCS. The data will be 1 terabyte at end of the year. The possible solution to host large data with good performance is to migrate data to Oracle or UDB in Unix platform. But we would like to try any possibility in Microsoft world before we decide to migrate data to the other databases.

I need your experience and recommendation about hosting large database in SQL Server with good web performance. Somebody mentions Microsoft tera server. Who has experience using tera server which is still in the beta release? I would like to hear some successful story to handle the large data and understand precisely hardware, software, performance moitoring requirement.

Thank you very much.

Rich

View 2 Replies View Related

What Is The Best Solution For A Report Database

Apr 18, 2008



Hi

I have been asked to create an archive database which we can use for reporting purpuse . The idea is to have a seperate data base which is an exact copy of the current production database , Once we setup the archive DB . we will remove all 40 days old data from Production database and form that day on should have archive DB with full data and production DB with 40 days old.
I thought go going log shipping or back up restor or snapshortor something but the problem is with all these technology the both data base will come in sync . If i delete data from production that will reflect in arvhive also .

I thought of keeping the 40 days only in production and the rest in archive . But they want to wiew the both data together in one aspx page . so i had think of some joint view . But when the archive data gets grow this would be the main perfomence bottlentck

Can any one help me to select a good solution for this, thanks in advance



P M Varughese

View 8 Replies View Related

Embedded Database Or Runtime Solution

Jul 28, 2006

Hello,
Im doing research for my company for a project we are about to start, but the more I find, the more Im confusing myself. Maybe someone would be so nice to help me a little.

We need a database solution, either licensing (ISV) or i think maybe an embedded database. if its licensing, its not a problem, but after research, im thinking licensing would be a waste of time or too much and theire not enough information to go by just that.

Problem: Need to develop software for a client where there will be around 300 users using the system (not all at once neccisarrily). We are creating this software for them and they want to re-sell it after completion, but they do not want to make their customers purchase a database for the purchase of their software. (because of all types of licensing). We need the database RUNTIME to run on the customers machine to make the software work.

Is there a RUNTIME license for Independant-Software-Vendor(IVS) for redistribution? or would it make sense to embed a database ( for example, the free edition of sql server;embeeded feature :), or maybe firebird embedded database?) We are developing in Visual Studio 2005.

This might sound confusing, thats where im at. For those of you with more experience, hopefully you understand what im talking about.

Thank you. I have 2 days to find a solution. Ill continue looking and post in thread if i find anything new or clearify my problem.

View 1 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

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

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

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 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

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

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

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

Opening Solution Creates Extraneous Xx.database Files In SSIS Project

Feb 9, 2007

I have a solution with a couple of ssis projects in it.  Everytime I open the solution, Visual Studio creates an extra .database file for the project's existing xxx.database file.  The solution is under VSS control and VS2005 checks out the project and shows the file as a newly added file.

What causes this and how can I prevent this from occurring?

The files contain this:

<Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="d3ce9653-3ac5-4ee5-85c3-7d60b2e5f109" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ID>Publish</ID>
  <Name>Publish</Name>
  <CreatedTimestamp>0001-01-01T00:00:00Z</CreatedTimestamp>
  <LastSchemaUpdate>0001-01-01T00:00:00Z</LastSchemaUpdate>
  <LastProcessed>0001-01-01T00:00:00Z</LastProcessed>
  <State>Unprocessed</State>
  <LastUpdate>0001-01-01T00:00:00Z</LastUpdate>
  <DataSourceImpersonationInfo>
    <ImpersonationMode>Default</ImpersonationMode>
    <ImpersonationInfoSecurity>Unchanged</ImpersonationInfoSecurity>
  </DataSourceImpersonationInfo>
</Database>
 

There are 2 of us working on the solution and the other fellow does not see this behavior.

The files do not show up in the VS2005 solution explorer.  If they are user specific as I suspect (impersonation info?), then they should not be added to the .dtproj project file.

Thanks!

View 3 Replies View Related

SOLUTION! - VB.NET 2005 Express And SQL Server 2005 - NOT Saving Updates To DB - SOLUTION!

Nov 30, 2006

VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!

-----------------------------------

The following article is bogus and confusing:

How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

Do not copy








The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

-----------------------------------

The above article is bogus and confusing.

This is rediculous!

This is the most vague and convoluted bunch of nonsince I've ever come accross!

Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.

--------------------------

I've tried it and it doesn't work for me.

Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.



If you have a step-by-step procedure that can be reproduced this properly please PM me.

I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.

Many thanks.



This is the exact procedure I have come up with:

NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.

NOTE 2: DO NOT hand copy the database to a folder/directory in your project.

Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.

Create a folder for your data following NOTE 2.

Copy your data to that folder. * mine was C:mydatamyData.mdf



Create a NEW project.

Remove any Data Connections. ( no matter what)

Save it.

Data | View Data Sources

Add New Data Source

select NEW CONNECTION ( No Matter what, do it!

Select the database. * again mine was C:mydatamyData.mdf

Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.



To Test ----------

From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.

The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.

Save it.

1) Run the app.

Add one database record to the database by pressing the Add(+) icon

Just add some quick junk data that you don't mind getting lost if it doesn't save.

YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Now exit the application.

2) Run the app again.

And verify there is one record already there.

Now add a second database record to the database by pressing the Add (+) icon.

NOW add some quick junk data that you WILL intentionally loose.

*** DO NOT *** press the save icon.

Just Exit the app.

3) Again, Run the app.

Verify that the first record is still there.

Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.

Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.

As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.

Whewh!

I hope this saves me some time the next time I want to start a new database project.

Oh, and uh, for anyone else reading this post.

Thanks,
Barry G. Sumpter

Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express

Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.

View 17 Replies View Related

How To Build A Package To Ftp Files From BLOBs With FTP Connection Info Also Held In The Database

Apr 24, 2008

I have a table with embedded files in it and also ftp server/user/pwd fields so that each record has all the data and information required and need to configure an SSIS package to to FTP them to the destination as specified in the record within the databaseAt the moment I have a data flow task which uses a Derived Column and an Export Column transformation which creates the files to a temp folder then it runs a ForEach loop container back on the control flow to FTP each file and to then move each file to an archive folder.

All I think I need to know how to do is to somehow control the FTP connection settings with the fields from the database within this foreach loop, but it is a for each file collection. I also need to update each record saying it has been FTP'd as well
Do I need to have an initial query outside of this foreach loop to populate an array which can be used for the 4 FTP connection fields (server/user/pwd/directory), if so how?

Any ideas as to how to correctly finish this package.


Thanks

View 2 Replies View Related

Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database

Feb 5, 2007

From Newbie to Newbie,



Add reference to:

'Microsoft ActiveX Data Objects 2.8 Library

'Microsoft ADO Ext.2.8 for DDL and Security

'Microsoft Jet and Replication Objects 2.6 Library

--------------------------------------------------------

Imports System.IO

Imports System.IO.File





Code Snippet

'BACKUP DATABASE

Public Shared Sub Restart()

End Sub



'You have to have a BackUps folder included into your release!

Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo


addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"


'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""



Try

File.Copy(z, f)

Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)

End Try



MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub






Code Snippet

'RESTORE DATABASE

Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String


With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName



'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With


'Notify user
MsgBox("Data restored successfully")


Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub








Code Snippet

'CREATE NEW DATABASE

Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;

Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table



'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet

OLEDB:Engine Type=5")


'Open the Catalog
Cat.ActiveConnection = Cn



'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"


'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable



Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")


'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)



'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)



'User notification )
MsgBox("A new empty database was created successfully")


'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing


'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If

End Sub








Code Snippet



'COMPACT DATABASE

Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine


'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program

FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")


'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")


'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")


'User notification
MsgBox("The database was compacted successfully")

End Sub

End Class

View 1 Replies View Related

Backup SQL Database Without Using Enterprise Manager?

May 2, 2004

Hello,

Is there a way where I can backup SQL database without using Enterprise Manager? If there is, may I know how?

View 3 Replies View Related

Database Stats In Enterprise Manager

Mar 1, 1999

We recently upgraded to Version 7.0 and the Enterprise Manager on the client is not showing the database size or the information in regards to the last backups.
Has anyone else experienced this that can offer a solution?

View 2 Replies View Related

I Can't Connect To My SQL Database Using Enterprise Manager :(

Sep 9, 2004

Hello,

I am trying to solve this problem for the past 1.5 weeks and it is driving me crazy :(

I can't connect to my SQL database using Enterprise Manager, Query Analyzer, Visual Studio 2003 .NET or a .aspx that I created for testing.. it runs perfectly on my domain but it doesnt work on my computer, on the browser I get this message: SQL Server unavailable or access denied. It is the same message I get when I try connecting to my db using EM, QA, VS.NET etc :(

I can ping my sql domain, so I know I have access to it and it is working.. I can successfully connect to my personal SQL server running on my machine. (SQL Server Personal Edition running on Windows XP )

I tried doing everything I could,

I already selected TCP/IP as my client only protocol;
I tried installing the lastest MDAC (v 2.8);
I tried every other port on my TCP/IP client setup like 14333 or 11433 or 11143 etc;
I tried every other port on the alias I set for my sql address;
I tried downloading the evaluation version of MS SQL2000 and updating it to SP3;
I tried reinstalling several times my MS SQL2000 (I use the personal edition on my windows XP) and updating it to SP3;
I tried connecting to my sql address domain and directly to its IP;
And I tried a lot more resolutions that I can't remember right now, but it didn't work :(

I've been searching all forums/google etc looking for someone with my problem but I still didnt, everyone who had a problem connecting to their DB using EM or QA resolved it by doing "this or that", I did exactly the same "this or that" on my computer/config and it didn't resolve my problem. :(

What am I doing wrong? I can't connect to my database from my personal computer :(

I have never used enterprise manager before, so I don't know if it is a problem on my personal computer or on my sql server address on internet.. Is there anyway to find a free test ms sql database with like 1mb just to try and find out if I can connect to a sql database at all using EM or if the problem is here on my computer?

Thanks in advance for any help,
Xande

View 3 Replies View Related

Cannot Connect To Database Using Enterprise Manager

Jul 4, 2007

I cannot seem to connect to this Instance locally which was i able toin the past. I can connect to the instance from out side. The instanceis running and the DBs in the instance are available and usedsuccessfully by the applications but i cannot connect to the instanceusing the enterprise manager.the error i get isSQL Server does not exist or access deniedrubeel

View 1 Replies View Related

Upgrading SQL 2005 Enterprise Evaluation To Enterprise Full Not Supported On Win XP Professional

Jul 11, 2006

This may be a stupid question, but can someone from MS SQL Server Team kind enough to confirm it, please?

View 3 Replies View Related

Can't Restore Database From .a .bak File In Enterprise Manager

May 29, 2006

Hi Everybody,I use MS SQL server 2000 enterprise manager on a windows server 2003.I created a new database in enterprise manager, and try to restore the new database from a .bak file. I went to ---->restore database. I checked "from device" and  select the right path to the .bak file.Then I click ok, but there is an error pop up." tend to override existing database", so I went to --->option, and checked "force override existing database". then I click ok, but get an error look like this      http://ewwa.lo7.net/error.JPGI thought it was a problem with my .bak file. So I try to use another .bak file which I am sure it is 100% good.  It prompt the restore is sucessfully. But when I checked the db, there nothing there(No tables).I think there must be something wrong with my enterprise manager's configuration.Do you know what happen with my enterprise manager? Thank you a lot in advance:)!!

View 2 Replies View Related

Create UTF-8 Database Using Enterprise Manager Sqlserver2000

Mar 10, 2004

Friends

I know how to create a database using enterprise manager in sql server 7 or 2000.
But i want to create a database where i have to set the unicode as UTF8 for the whole database.
is it possible?
if yes, how can i do that.

thanks
sathyguy

View 3 Replies View Related

Viewing Database Via Query Analyzer And/or Enterprise Manager

Sep 20, 2004

We are running SQL 2000 SP3 on a Win2K server with multiple user databases. Our current problem is the ability of users to view the database objects via the native tools.

Example: User Bob
Bob has the following rights:
DB A – datareader/datawriter
DB B – datareader/datawriter
DB C – datareader
DB D – datareader
DB E – datareader/datawriter

The user has no builder rights or server roles.

In Enterprise Manager, the user can not see any databases. In Query Analyzer, the user can only see DB A, B, and C. Anyone seen this problem before and/or have suggestions?

Thanks in advance!

View 3 Replies View Related

Connecting To Online Database Using Local Enterprise Manager

Jul 23, 2005

I have Enterprise Manager on my local machine. For the last twelvemonths it has been connecting without problem to my online SQL Serverdatabase provided by my ISP.Three weeks ago the ISP applied some sort of extra securityarrangements to their SQL Server to allow access only through port1433. they have told me to configure an alias using Network Client andto register this alias in the usual way using my Enterprise Manager.My problem is that despite creating the alias in exactly the wayinstructed I just cannot connect to online database. I keep gettingthe SQL Server does not exist or access denied when I try to connect.As a result of advice obtained I have tried using the lite version ofthe 'littleADmin' tool available fromhttp://www.mylittletools.net/scripts/en/mla_sql/Using this useful looking software I find I cannot connect to theonline SQL database when it is installed on my own local machine butif I install it on my website it does connect without problem to myonline SQL. Server databaseHowever I need to use my more powerful Enterprise Manager to connect,my question (at last!) is:1. Is there likely to be a problem trying to connect to my onlinedatabase from Enterprise Manager on my local machine as a result ofthe new security arrangements. In other words, is there somethinganalagous to what is happening with the 'littleAdmin tool' wherebyusing Enterprise Manager I cannot connect from my local machine forsome reason obvious to everyone except myself??or2. Is it possible that there is some problem arising from Net Client?(the aliases appear in Enterprise Manager OK)I know my ISP should be answering this sort of question but they justdon't seem to know. I need to connect even if only to download mydatabase and take it to another ISP,Best wishes, John Morgan

View 4 Replies View Related

Negative File Size On Database Device In Enterprise Manager

Apr 28, 1999

I have several clients who are reporting a negative file size on their database devices in Enterprise Manager. The sp_helpdevice procedure reports the size correctly. Any suggestions?

View 1 Replies View Related

Problem COnnecting Remote Database Server Thru Enterprise Manager

Sep 6, 2003

Hello Guys,

I have just put up an Webserver with SQL Server 2000. Everything is working fine on the remote machine Thru TS .. i can do every administrative things .. But i m facing problem to connect my remote databse server thru the Enterprise Manager .. It Gives me Cant Connect Database Server " Connection Failiure " when i try to connect my remote database server thru Ip address ,.. Please some one help me out to make connecting database server thru Enterprise Manager ..

Thanks

View 10 Replies View Related







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