Hi All,
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Showing posts with label seperate. Show all posts
Showing posts with label seperate. Show all posts
Sunday, March 11, 2012
Creating seperate databases for acquired companies - best practice
Hi All,
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Creating seperate databases for acquired companies - best practice
Hi All,
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.
nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Our company has just acquired another company and I am in the
process of creating a database for the new company. The acquired
company is in the same area of business & require the exact same data
to be stored. However, We need to keep the data seperate between the
two companies for reporting purposes.
As I see it there are 2(?) options:
Add a CompanyID field to all tables to seperate the data. I.e. one
'big' database with several companies data in it.
Create seperate databases for each new company. I.e. many little
databases, each with the same structure.
We plan to acquire more companies in the future so this process may
well be required several times.
Our configuration is:
SQL server 2000 Standard edition running on Windows 2003 server
I realise that both options have their advantages & disadvantages. My
personal opinion is that a seperate DB for each company would be more
secure & easier to maintain.
However I would be interested to hear from any other people who have
been down this route & the experiences they gathered.
nwhitehead@.besconsulting.co.uk wrote:
> Hi All,
> Our company has just acquired another company and I am in the
> process of creating a database for the new company. The acquired
> company is in the same area of business & require the exact same data
> to be stored. However, We need to keep the data seperate between the
> two companies for reporting purposes.
> As I see it there are 2(?) options:
> Add a CompanyID field to all tables to seperate the data. I.e. one
> 'big' database with several companies data in it.
> Create seperate databases for each new company. I.e. many little
> databases, each with the same structure.
> We plan to acquire more companies in the future so this process may
> well be required several times.
> Our configuration is:
> SQL server 2000 Standard edition running on Windows 2003 server
> I realise that both options have their advantages & disadvantages. My
> personal opinion is that a seperate DB for each company would be more
> secure & easier to maintain.
> However I would be interested to hear from any other people who have
> been down this route & the experiences they gathered.
>
Hi,
I'd go for option 2 with having a separate DB for each new company.
Adding a CompanyID to all the tables is in my opinion not something you
"just" do. That will most likely require quite a re-write of the
application that are using the database so I doubt that's an option.
Regards
Steen Schlter Persson
Database Administrator / System Administrator
Creating select statement with seperate columns for different valu
I've been trying to figure out a select statement that would list a seperate
column with the count of a particular value for each value. Basically if I
have a table like this:
number value
-- --
10 good
10 bad
10 bad
12 good
14 bad
16 better
and I want to return all numbers with good or bad values and the totals for
those values, like this:
number good bad
-- -- --
10 1 2
12 1 0
14 0 1
How would I create the query?The best way to query this would be to use:
select num, count(value), value from checkcount
group by num, value
your result would be:
10 2 bad
14 1 bad
16 1 better
10 1 good
12 1 good
then you would want to create a user interface to format your result shown
in your example.
However, if you wanted SQL to bring your result back formatted as your
example, then you would want to inner join your table. hopefully, it is not
a large table. The following query would bring back your desired formatting:
select checkcount.num, isnull( thegood.good ,0) good, isnull( thebad.bad ,0)
bad from checkcount
left join (select num, count(value)as good from checkcount
where value = 'good'
group by num) theGood
on checkcount.num = thegood.num
left join (select num, count(value)as bad from checkcount
where value = 'bad'
group by num) thebad
on checkcount.num = thebad.num
where value in ('good','bad')
group by checkcount.num,thegood.good,thebad.bad
Thanks Kllyj64
"David Tilman" wrote:
> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||If you're lucky enough to have SQL Server 2005, try the new PIVOT operator:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( number INT, xvalue VARCHAR(10) )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 10, 'good' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 12, 'good' )
INSERT INTO #tmp VALUES ( 14, 'bad' )
INSERT INTO #tmp VALUES ( 16, 'better' )
SET NOCOUNT OFF
SELECT *
FROM #tmp AS t
PIVOT
(
COUNT(xvalue) FOR xvalue In ( [good], [bad], [better] )
) AS x
That is my first PIVOT query! That's going to be useful!
Let me know how you get on.
Damien
"David Tilman" wrote:
> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||Try,
select
number,
sum(case when value = 'good' then 1 else 0 end) as good,
sum(case when value = 'bad' then 1 else 0 end) as bad
from
t1
group by
number
go
How to rotate a table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
For SQL Server 2005, see PIVOT operator.
AMB
"David Tilman" wrote:
> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?
column with the count of a particular value for each value. Basically if I
have a table like this:
number value
-- --
10 good
10 bad
10 bad
12 good
14 bad
16 better
and I want to return all numbers with good or bad values and the totals for
those values, like this:
number good bad
-- -- --
10 1 2
12 1 0
14 0 1
How would I create the query?The best way to query this would be to use:
select num, count(value), value from checkcount
group by num, value
your result would be:
10 2 bad
14 1 bad
16 1 better
10 1 good
12 1 good
then you would want to create a user interface to format your result shown
in your example.
However, if you wanted SQL to bring your result back formatted as your
example, then you would want to inner join your table. hopefully, it is not
a large table. The following query would bring back your desired formatting:
select checkcount.num, isnull( thegood.good ,0) good, isnull( thebad.bad ,0)
bad from checkcount
left join (select num, count(value)as good from checkcount
where value = 'good'
group by num) theGood
on checkcount.num = thegood.num
left join (select num, count(value)as bad from checkcount
where value = 'bad'
group by num) thebad
on checkcount.num = thebad.num
where value in ('good','bad')
group by checkcount.num,thegood.good,thebad.bad
Thanks Kllyj64
"David Tilman" wrote:
> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||If you're lucky enough to have SQL Server 2005, try the new PIVOT operator:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( number INT, xvalue VARCHAR(10) )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 10, 'good' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 10, 'bad' )
INSERT INTO #tmp VALUES ( 12, 'good' )
INSERT INTO #tmp VALUES ( 14, 'bad' )
INSERT INTO #tmp VALUES ( 16, 'better' )
SET NOCOUNT OFF
SELECT *
FROM #tmp AS t
PIVOT
(
COUNT(xvalue) FOR xvalue In ( [good], [bad], [better] )
) AS x
That is my first PIVOT query! That's going to be useful!
Let me know how you get on.
Damien
"David Tilman" wrote:
> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?|||Try,
select
number,
sum(case when value = 'good' then 1 else 0 end) as good,
sum(case when value = 'bad' then 1 else 0 end) as bad
from
t1
group by
number
go
How to rotate a table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
For SQL Server 2005, see PIVOT operator.
AMB
"David Tilman" wrote:
> I've been trying to figure out a select statement that would list a sepera
te
> column with the count of a particular value for each value. Basically if I
> have a table like this:
> number value
> -- --
> 10 good
> 10 bad
> 10 bad
> 12 good
> 14 bad
> 16 better
> and I want to return all numbers with good or bad values and the totals fo
r
> those values, like this:
> number good bad
> -- -- --
> 10 1 2
> 12 1 0
> 14 0 1
> How would I create the query?
Wednesday, March 7, 2012
Creating ODBC connection
In my project, I have added a database called aspnetdb.mdf and I store all the required information here. Now I want to write a seperate Cryatl Report for my manager, that accesses this database and display required data. This report is not a part of the asp.net project. This is only for my manager. My database is not attached to any server. I just use the attachdb in the coonection string of my project to attach this database. no I want my crystal report to access this database. If I can create an ODBC connection to this database, it will solve my problem. Dows any body know how to access this database from Crystal with or without ODBC?
Any help is greatly appreciated.
i think crystal supports pretty much any type of data source, eg. odbc, oledb, etc.
Is the database SQL Express running on your own computer? I can find the connection string for you if this is the case.
Friday, February 24, 2012
Creating Linked Servers
I'm new to this aspect of SQL Server so bear with me here. I have 2 SQL Servers (2000) in seperate locations on separate IPs. From what I'm reading and from what I've tried using Enterprise manager, the only way I can link the other server is by using the IP address as the name. Is there a way that I can specify a "user-friendly" name which will be used to reference tables on the linked server in queries?
This is the first time I've run across something that none of my books, nor the books online seem very clear about. Keep that in mind when replying, please. I may need the remedial course for this. <grin>
Any help is appreciated.
Thanks.On the machine that you will be registrying the Linked Servers, if you can not ping the SQL Server name of the other 2 ?
c:\> ping MyServer
But you can ping the IP addresses
c:\> ping xxx.xxx.xxx.xxx
Then you can create "user friendly" names 2 ways.
1) Add the 2 servers to the LMHOST file of the server or
2) Us SQL Server Client Network Utility to define Alias for these 2 servers.
The second method is the easiest since it is straight forward and you are using SQL Server tools. The first way is more of a global definition, that defines an alias that can be used by all commands and tools (ie net use). Where as the second method is strickly SQL Server realm.
Make sure that you use TCP/IP as your Network Library when defining the aliases (method 2).
This is the first time I've run across something that none of my books, nor the books online seem very clear about. Keep that in mind when replying, please. I may need the remedial course for this. <grin>
Any help is appreciated.
Thanks.On the machine that you will be registrying the Linked Servers, if you can not ping the SQL Server name of the other 2 ?
c:\> ping MyServer
But you can ping the IP addresses
c:\> ping xxx.xxx.xxx.xxx
Then you can create "user friendly" names 2 ways.
1) Add the 2 servers to the LMHOST file of the server or
2) Us SQL Server Client Network Utility to define Alias for these 2 servers.
The second method is the easiest since it is straight forward and you are using SQL Server tools. The first way is more of a global definition, that defines an alias that can be used by all commands and tools (ie net use). Where as the second method is strickly SQL Server realm.
Make sure that you use TCP/IP as your Network Library when defining the aliases (method 2).
Subscribe to:
Posts (Atom)