Multiple Instances Question

We have a customer that refuses to accept the idea of multiple instances. I've told them that there are no hidden problems with this. On the contrary there are a number of advantages if you're going to consolidate a huge number of servers with databases that have different requirements. Interesting to hear some comments about the subject!?Hi Folke,
apart from a bit of wasted space caused throug multiple server-engines i
dont see any problems doing so.
Micha
"Folke" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8B57E51F-1A7F-4D2C-83F2-2B01F915DADC@.microsoft.com...
> We have a customer that refuses to accept the idea of multiple instances.
I've told them that there are no hidden problems with this. On the contrary
there are a number of advantages if you're going to consolidate a huge
number of servers with databases that have different requirements.
Interesting to hear some comments about the subject!?|||It's best to run a single instance on a server unless you have a specific
reason to do otherwise. This will make the best use of available hardware
resources. Consolidating many databases onto a single instance is possible
as long as you don't have conflicting application requirements like
different default instance collations.
See the Books Online <architec.chm::/8_ar_cs_5upf.htm> for some guidelines.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Folke" <anonymous@.discussions.microsoft.com> wrote in message
news:8B57E51F-1A7F-4D2C-83F2-2B01F915DADC@.microsoft.com...
> We have a customer that refuses to accept the idea of multiple instances.
I've told them that there are no hidden problems with this. On the contrary
there are a number of advantages if you're going to consolidate a huge
number of servers with databases that have different requirements.
Interesting to hear some comments about the subject!?

Multiple Instances Question

We have a customer that refuses to accept the idea of multiple instances. I've told them that there are no hidden problems with this. On the contrary there are a number of advantages if you're going to consolidate a huge number of servers with databases t
hat have different requirements. Interesting to hear some comments about the subject!?
Hi Folke,
apart from a bit of wasted space caused throug multiple server-engines i
dont see any problems doing so.
Micha
"Folke" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8B57E51F-1A7F-4D2C-83F2-2B01F915DADC@.microsoft.com...
> We have a customer that refuses to accept the idea of multiple instances.
I've told them that there are no hidden problems with this. On the contrary
there are a number of advantages if you're going to consolidate a huge
number of servers with databases that have different requirements.
Interesting to hear some comments about the subject!?
|||It's best to run a single instance on a server unless you have a specific
reason to do otherwise. This will make the best use of available hardware
resources. Consolidating many databases onto a single instance is possible
as long as you don't have conflicting application requirements like
different default instance collations.
See the Books Online <architec.chm::/8_ar_cs_5upf.htm> for some guidelines.
Hope this helps.
Dan Guzman
SQL Server MVP
"Folke" <anonymous@.discussions.microsoft.com> wrote in message
news:8B57E51F-1A7F-4D2C-83F2-2B01F915DADC@.microsoft.com...
> We have a customer that refuses to accept the idea of multiple instances.
I've told them that there are no hidden problems with this. On the contrary
there are a number of advantages if you're going to consolidate a huge
number of servers with databases that have different requirements.
Interesting to hear some comments about the subject!?

Multiple Instances Question

We have a customer that refuses to accept the idea of multiple instances. I'
ve told them that there are no hidden problems with this. On the contrary th
ere are a number of advantages if you're going to consolidate a huge number
of servers with databases t
hat have different requirements. Interesting to hear some comments about the
subject!?Hi Folke,
apart from a bit of wasted space caused throug multiple server-engines i
dont see any problems doing so.
Micha
"Folke" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:8B57E51F-1A7F-4D2C-83F2-2B01F915DADC@.microsoft.com...
> We have a customer that refuses to accept the idea of multiple instances.
I've told them that there are no hidden problems with this. On the contrary
there are a number of advantages if you're going to consolidate a huge
number of servers with databases that have different requirements.
Interesting to hear some comments about the subject!?|||It's best to run a single instance on a server unless you have a specific
reason to do otherwise. This will make the best use of available hardware
resources. Consolidating many databases onto a single instance is possible
as long as you don't have conflicting application requirements like
different default instance collations.
See the Books Online <architec.chm::/8_ar_cs_5upf.htm> for some guidelines.
Hope this helps.
Dan Guzman
SQL Server MVP
"Folke" <anonymous@.discussions.microsoft.com> wrote in message
news:8B57E51F-1A7F-4D2C-83F2-2B01F915DADC@.microsoft.com...
> We have a customer that refuses to accept the idea of multiple instances.
I've told them that there are no hidden problems with this. On the contrary
there are a number of advantages if you're going to consolidate a huge
number of servers with databases that have different requirements.
Interesting to hear some comments about the subject!?

Multiple FK or "TableName" column?

Hi!
I have a table "addresses". Address can be associate to a customer, an
employee, a supplier or a consultant. Every of these 5 have a table with an
identity primary key. My question is simple: Which of the 2 ways is the best
to link the "Addresses" table to other tables?
A- By adding 5 foreign keys, assuming that only one will be NOT NULL by reco
rd
B- Adding a column Varchar "ParentTable" and a Int column "ParentID" to make
the relation programmatically?
I need the best way to be faster and also easier to implement...
Thanks in Advance!David Parenteau wrote:
> Hi!
> I have a table "addresses". Address can be associate to a customer, an
> employee, a supplier or a consultant. Every of these 5 have a table
> with an identity primary key. My question is simple: Which of the 2
> ways is the best to link the "Addresses" table to other tables?
> A- By adding 5 foreign keys, assuming that only one will be NOT NULL
> by record B- Adding a column Varchar "ParentTable" and a Int column
> "ParentID" to make the relation programmatically?
> I need the best way to be faster and also easier to implement...
> Thanks in Advance!
Or C - Create 5 tables for CustomerAddress, EmployeeAddress, etc.
Or D - Add an AddressID to the Customer, Employee, etc. tables if they
can only have one address (probably not the case)
Or E - Create a generic Person table and a PersonAddress table. Relate
Person to Customer, Employee, etc.
I don't like option A and option B is not relational.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||First of all, an IDENTITY cannot ever be a key by definition, so the
schema is screwed up from the start. Ignoring that fundamental
problem, start by asking the basic questions:
1) Is an address an attribute of a customer, an employee, a supplier
and a consultant? In which case you need a SAN (Standard Address
Number) or equivalent in a column in those tables. I am assuming that
people fall into one and only one of these roles and that they are
logically different. Otherwise, there would be a People table.
2) Is there a one-to-many relationship between an address and a
customer, an employee, a supplier or a consultant? That case, you will
need tables to model the customer-addresses, the employee-addresses,
the supplier-addresses and the consultant-addresses relationships. I
would also add type codes to the addresses (ship-to, bill-to, etc.).|||Wow... suggestion!
Yes, more than one adress per customers, etc...
For E, does the Person table has 5 FK, one for each table or you mean 5
Person table, one for customer, one for employee, and so on?
Addresses should be the same columns for each 5 entities, so Solution C
seems to be not the quickest way to choose if a change must occur in the
schema... WHat do you think about this?
"David Gugick" wrote:

> David Parenteau wrote:
> Or C - Create 5 tables for CustomerAddress, EmployeeAddress, etc.
> Or D - Add an AddressID to the Customer, Employee, etc. tables if they
> can only have one address (probably not the case)
> Or E - Create a generic Person table and a PersonAddress table. Relate
> Person to Customer, Employee, etc.
> I don't like option A and option B is not relational.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||First create and load this table. might want to look at Melissa Data
software to edit the addresses,
CREATE TABLE Addresses
(address_nbr INTEGER NOT NULL PRIMARY KEY,
street1 CHAR(35) NOT NULL,
street2 CHAR(35),
street3 CHAR(35),
city_name CHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip4 CHAR(10) NOT NULL
CHECK (zip4 LIKE '[0-9][0-9][0-9][0-9][0-9]'
OR zip4 LIKE
'[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
verification_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);
Next, create each of the tables with the proper key for that kind of
entity. For suppliers, the Dun & Bradsteet codes are common; employees
better have an SSN, etc.
CREATE TABLE Suppliers
(duns_nbr CHAR(9) NOT NULL PRIMARY KEY,
supplier_name CHAR(35) NOT NULL,
.);
Now create a relationship table between each of the entities and the
addresses:
CREATE TABLE SupplierAddresses
(duns_nbr CHAR(9) NOT NULL
REFERENCES Suppliers(duns_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
address_nbr INTEGER NOT NULL,
REFERENCES Addresses(address_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
supplier_address_type CHAR(5) INTEGER NOT NULL
CHECK (supplier_address_type IN ('ship', 'bill', 'HQ', 'serv',
...)),
PRIMARY KEY (duns_nbr, address_nbr));
Make heavy use of DRI actions to maintian data integrity. Take time to
invent good address type codes for each logical entity (i.e. a customer
does not have a service department or a headquarters).|||About the Identity that cannot be a Key, do you mean a "Natural key"? If yes
,
I'm agree.
I used to set a FieldID INT Identity has a Primary key in my tables, to
avoid multiple-field primary keys. This seems to be easier to locate a recor
d
from a web application or application... Does this a very bad thing to do? I
f
yes, why?
David
"--CELKO--" wrote:

> First of all, an IDENTITY cannot ever be a key by definition, so the
> schema is screwed up from the start. Ignoring that fundamental
> problem, start by asking the basic questions:
> 1) Is an address an attribute of a customer, an employee, a supplier
> and a consultant? In which case you need a SAN (Standard Address
> Number) or equivalent in a column in those tables. I am assuming that
> people fall into one and only one of these roles and that they are
> logically different. Otherwise, there would be a People table.
> 2) Is there a one-to-many relationship between an address and a
> customer, an employee, a supplier or a consultant? That case, you will
> need tables to model the customer-addresses, the employee-addresses,
> the supplier-addresses and the consultant-addresses relationships. I
> would also add type codes to the addresses (ship-to, bill-to, etc.).
>|||Celko,
I now have a new example: A table activity. But the "activities" will need
to be linked to all 150 tables in my database... So, is it a reason to avoid
creating 150 link tables and keep the table name in a column of the
"Activity" table?
Thanks!!
"--CELKO--" wrote:

> First of all, an IDENTITY cannot ever be a key by definition, so the
> schema is screwed up from the start. Ignoring that fundamental
> problem, start by asking the basic questions:
> 1) Is an address an attribute of a customer, an employee, a supplier
> and a consultant? In which case you need a SAN (Standard Address
> Number) or equivalent in a column in those tables. I am assuming that
> people fall into one and only one of these roles and that they are
> logically different. Otherwise, there would be a People table.
> 2) Is there a one-to-many relationship between an address and a
> customer, an employee, a supplier or a consultant? That case, you will
> need tables to model the customer-addresses, the employee-addresses,
> the supplier-addresses and the consultant-addresses relationships. I
> would also add type codes to the addresses (ship-to, bill-to, etc.).
>

Multiple Dynamic Insert Statements in one stored procedure?

Hello, I'm having a little trouble, and need a little direction. I

have a stored procedure with a transaction that creates a contract for

a customer in a Contract table. After the contract record is created, I need

to create the contract items/products with their properties (price,

notes, etc), which belong in a linked table, so that they're created

all within the same transaction (the contract, and the products), so

that if either the contract or any of the items cannot be inserted into

their correct tables, it will rollback and undo the creation of the

contract. Now, I understand transactions and how they work, but my

trouble is that I'm not sure if I can use a dynamic insert

statement to create the contract items/products, within the same

transaction where the contract is created.

I'm new to sp_executesql, which seems to be the recommended method for dynamic sql in a stored proc, but if I understand it correctly, I'd have to call a stored proc for each item i wanted to insert something into the ContractItem table with a dynamic insert statement. Is there any way to have multiple dynamically created insert statements in one stored procedure? Help! I hope I explained my problem well enough. thanks in advance!

You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.

Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?

Chris

|||

Chris Howarth wrote:

You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.

I understand this, I guess my problem really is this: the number of rows I will have to insert into the ContractItem table will vary each time this stored procedure to create a Contract and it's ContractItems is called. So how do I pass that (variable amount of) data into the stored prodedure to create the dynamic insert statements?

Chris Howarth wrote:

Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?

Not really, I'm still sort of new to stored procedures though, if that is any kind of an excuse :) If this would be a better way to do it, then my plan should probably change slightly...

|||

How you choose to do this very much depends on your application's architecture.

If you have a web server or application server that is executing your stored procedures then it would probably make sense to create a single INSERT stored proc (designed to insert one row at a time) per table and then get your web/app server to execute the stored procedure once per row, passing in the parameters as required. Any iteration that needs to take place can then be performed by the web/app server.

You can start a transaction at the connection level, so as long as your web/app server uses the same connection to execute all stored procs that are part of the 'business transaction' then all of the changes will be rolled back should an error be raised.

Does this fit your scenario?

Chris

|||Chris,

This does fit my scenario... a transaction at the connection level sound just like what I need to get this to work properly. Now I just have to go dig up how to do that. Thank you so much for your help!

multiple database publications

I'm using merge replication with sql2000 and sqlserverce. All is working
well for now.
My customer now wants my ppc app to utilize data from multiple databases. Is
it possible for a single publication to contain data from multiple databases
and if so will it merge correctly?
This is probably a stupid question, but it would be nice if it works
I'd have to try this out, but I don't think so. You replicate to SQL CE
using an ActiveX control on the PPC which will pull the subscription to the
PPC.
One option you may have is to use RDA to do parts of your sync, and a merge
publication to do other tables.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:OOYLbvPzEHA.804@.TK2MSFTNGP12.phx.gbl...
> I'm using merge replication with sql2000 and sqlserverce. All is working
> well for now.
> My customer now wants my ppc app to utilize data from multiple databases.
> Is
> it possible for a single publication to contain data from multiple
> databases
> and if so will it merge correctly?
> This is probably a stupid question, but it would be nice if it works
>

Multipe Inserts Causes Error - Please Help

I am currently using ADO to create a recordset, once I have selected my data (1 column called customer - datatype int - about 50,000 of them), I then post a transaction to each cutomer using a stored procedure. I am using a disconnect recordset for the initial ADO recordset and after each run of the store procedure I set everything to closed then nothing. I have plenty of resources available but I still get the following error.

17066 :
SQL Server Assertion: File: <S:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.

Can anyone help, sorry if this is not infomation I can post more if needed only to be honest I don't what to extra to post.

As a matter of interest I have also had this error when importing 1.7 millon records with DTS!!Have since found out this was due to a hardware issue, it seems that it relates to CPU clocking speeds and or Video cards.

Have replaced the cpu and motherboard in my case and the problem has not returned, and I have run extensive test and re-tests