Problem:
I have a MySql database that houses status information that is linked
by a primary key from a SQL2005 database
Using Report Designer in VS2005, I have created 3 datasets pointing to
the 2 datasources. I need to create a relationship between the MySql
based datasets and a dataset that is based on a SQL2005 datasource, so
that I can have all of the data available in one table...
MySQL Table1
DocID, JobID, FKeyToSQL
MySQL Table2
JobID,Status
SQL2005 Data
PKeyToMySQL,Description,etc.
I would like to be able to see a status report that shows...
PKeyToMySQL,Description,JobID,Status
Thanks for any help.Can you try adding MySQL database as a LinkedServer?
"Kevin.Ailes@.gmail.com" wrote:
> Problem:
> I have a MySql database that houses status information that is linked
> by a primary key from a SQL2005 database
> Using Report Designer in VS2005, I have created 3 datasets pointing to
> the 2 datasources. I need to create a relationship between the MySql
> based datasets and a dataset that is based on a SQL2005 datasource, so
> that I can have all of the data available in one table...
> MySQL Table1
> DocID, JobID, FKeyToSQL
> MySQL Table2
> JobID,Status
> SQL2005 Data
> PKeyToMySQL,Description,etc.
> I would like to be able to see a status report that shows...
> PKeyToMySQL,Description,JobID,Status
> Thanks for any help.
>|||Jaraba wrote:
> Can you try adding MySQL database as a LinkedServer?
>
> "Kevin.Ailes@.gmail.com" wrote:
> > Problem:
> > I have a MySql database that houses status information that is linked
> > by a primary key from a SQL2005 database
> >
> > Using Report Designer in VS2005, I have created 3 datasets pointing to
> > the 2 datasources. I need to create a relationship between the MySql
> > based datasets and a dataset that is based on a SQL2005 datasource, so
> > that I can have all of the data available in one table...
> >
> > MySQL Table1
> > DocID, JobID, FKeyToSQL
> > MySQL Table2
> > JobID,Status
> >
> > SQL2005 Data
> > PKeyToMySQL,Description,etc.
> >
> > I would like to be able to see a status report that shows...
> >
> > PKeyToMySQL,Description,JobID,Status
> >
> > Thanks for any help.
> >
> >
I was able to embed a sub-report and submit a parameter to it and it
seems to have accomplished what I wanted. It seems like a round about
way to do it though. I would have thought there would be some way to
build relationships between datasets since you can't combine data from
2 different datasources into 1 dataset. Oh well, now if I could just
get this sub-report to be centered in the column.(TextAlign=center on
column properties doesn't seem to be working)
Showing posts with label primary. Show all posts
Showing posts with label primary. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Combined Primary Key - Why?
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen>> If (b), then how do I ensure that a combination of ProductID and OrderID
In t-SQL, you'd use a NOT NULL UNIQUE CONSTRAINT on those values.
Anith|||I would normally go with ProductID, OrderID for primary key, as that is the
natural key. If I have to go with a surrogate key, then I'd make sure
there's a unique index/constraint on ProductID, OrderID combination.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
In a many to many relationship, say Product to Orders with ProductOrders
being the associative entity, what is the most commonly used definition for
Primary keys (PK) in the associative entity and why?
a)ProductOrders: ProductID - PK, OrderID - PK
OR
b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
OrderID not (PK).
If (b), then how do I ensure that a combination of ProductID and OrderID are
unique?
Thanks,
Naveen|||You can create a UNIQUE constraint on the combination of ProductID and
OrderID. Although my preference is to have a Primary Key on ProductID and
OrderID, and, if I think a single column (Identity) key is useful, to have
the UNIQUE constraint on the Identity column. In this scenario there is
usually not much need to have an Identity column on the ProductOrders table
though.
Jacco Schalkwijk
SQL Server MVP
"Naveen" <Naveen@.discussions.microsoft.com> wrote in message
news:C88C1636-C31F-4C63-8DAB-248C4D4C10B3@.microsoft.com...
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition
> for
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID
> are
> unique?
> Thanks,
> Naveen|||"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||I wasn't talking about the order of columns within the compound key - but
yes, that's a good point.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23NHEglEPFHA.2520@.tk2msftngp13.phx.gbl...
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OjnP5cEPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>I would normally go with ProductID, OrderID for primary key, as that is the
> natural key. If I have to go with a surrogate key, then I'd make sure
> there's a unique index/constraint on ProductID, OrderID combination.
> --
Me to. Although I would pick (OrderId, ProductID), if order-wise access is
a often a more common access path than product-wise access. Whichever
column leads your clustered index will have the cheapest access path.
And put a non-clustered index on whichever column is not the leading column
in the clustered index (OrderID). Foreign keys should normally be supported
by an index.
create table ProductOrders
(
ProductID int not null references Products,
OrderID int not null refereneces Orders on delete cascade,
constraint pk_ProductOrders primary key(OrderID,ProductID)
)
create index ix_ProductOrdersProduct on ProductOrders(ProductId)
David|||There is only one key, (order_id, product_id) by definition. This is
basic RDBMS; a key must be made up of attributes that exist in the data
model. There is no ProductOrderID(mostly identity) in the real world;
it is derived from the internal state of the hardware at insertion
time:
CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Inventory(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
order_id INTEGER NOT NULL
REFERENCES Orders(order_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (order_id, product_id));
Newbies screw up and do it the other way. They can then get duplicates
and destroy the data integrity of the schema.|||This Product Orders table is just a OrderDetails, OrderItems or
InvoiceDetails table..
If you are going have any other tables "hanging off" this table, where you
will need to have a FK referring back to the ProductOrders (I'd call it
OrderDetails)
Then I'd go with b) strictly to avoid having to use composite FKs in other
table(s).
In that event, add another "Alternate" key (using Unique Index, or Unique
Constraint, on (OrderID, ProductID) to ensure uniqueness.
Regardless of whether you also have a single column surrogate key, make the
Composite key the Clustered Index, and if more queries will select data by
OrderID than by ProductID from this table, (THis is normally true for such
tables) use (OrderID, ProductID) as the sequence. But also put another inde
x
on ProductID by itself, for Joins back to Product Table
"Naveen" wrote:
> In a many to many relationship, say Product to Orders with ProductOrders
> being the associative entity, what is the most commonly used definition fo
r
> Primary keys (PK) in the associative entity and why?
> a)ProductOrders: ProductID - PK, OrderID - PK
> OR
> b) ProductOrders: ProductOrderID(PK, mostly identity), ProductID (not PK),
> OrderID not (PK).
> If (b), then how do I ensure that a combination of ProductID and OrderID a
re
> unique?
> Thanks,
> Naveensqlsql
Combined Index not using in SQL 7.0 SP4
Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binusqlsql
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binusqlsql
Tuesday, March 20, 2012
Combine two int columns into one bigint column
Hello
I have created a database that uses an int for the table primary keys.
This database is being deployed at several sites. Each site has a
unique site ID which is less than 100.
There is another database that acts as a master viewer. This database
holds a copy of all the site databases to do reports across all the
sites. Basically each site database gets 'merged' with the master
database once a month.
The master viewer database uses exactly the same structure as the site
database, but needs to store the site ID with every record. I have
currently achieved this by changing the table primary key to be a
bigint and store the site ID in 30 bits and the actual site record
primary in the remaining 34 bits (ok I could have used 32/32). The
reason for not adding another primary key column is because I use the
exact same sql queries in the master database and site databases, and
adding another column would mean creating two seperate queries when
joining tables (one to work on the master and one to work on the sites
- more work and difficult to maintain).
It is relatively simple to extract the site ID and record primary key
from the bigint using bitwise operations and bit shifting.
Unfortunately sql does not support bit shifting and I use division for
the same affect. The only downside I see is the performance issue when
extracting the site primary key in a sql query. If I want to test the
site primary key I use "WHERE ((MaintenanceTransaction_PRK &
-1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
1073741824. If the table has tens of thousands/millions of records I
can see this taking a while.
Does anyone have any other suggestions that I could use?
Many thanks
PaulChange your database in the master and client sites to include site ID as
part of the key in all of them. Then you can write one SQL that will run
against all of your databases equally. This will server you better in the
long run.
Or you can simply multiply the PK by 10000 and add the site ID to it to get
the new ID. This will give you a new combined ID without all that screwing
around with bits. Much simpler and you will have room for 10000 customers
before you run out of site IDs. This really is a kludge, however, and not
the best way to solve the problem.
<kerplunkwhoops@.yahoo.co.uk> wrote in message
news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...
> Hello
> I have created a database that uses an int for the table primary keys.
> This database is being deployed at several sites. Each site has a
> unique site ID which is less than 100.
> There is another database that acts as a master viewer. This database
> holds a copy of all the site databases to do reports across all the
> sites. Basically each site database gets 'merged' with the master
> database once a month.
> The master viewer database uses exactly the same structure as the site
> database, but needs to store the site ID with every record. I have
> currently achieved this by changing the table primary key to be a
> bigint and store the site ID in 30 bits and the actual site record
> primary in the remaining 34 bits (ok I could have used 32/32). The
> reason for not adding another primary key column is because I use the
> exact same sql queries in the master database and site databases, and
> adding another column would mean creating two seperate queries when
> joining tables (one to work on the master and one to work on the sites
> - more work and difficult to maintain).
> It is relatively simple to extract the site ID and record primary key
> from the bigint using bitwise operations and bit shifting.
> Unfortunately sql does not support bit shifting and I use division for
> the same affect. The only downside I see is the performance issue when
> extracting the site primary key in a sql query. If I want to test the
> site primary key I use "WHERE ((MaintenanceTransaction_PRK &
> -1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
> MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
> 1073741824. If the table has tens of thousands/millions of records I
> can see this taking a while.
> Does anyone have any other suggestions that I could use?
> Many thanks
> Paul
>|||Hello
I did consider changing the whole of the project to include the site ID
as a seperate column in the site database, but that would be a huge
amount of work and the master viewer is a special case for one
customer. I was trying to make it as simple as possible. It all works
correctly now, but I am just concerned about the performance.
Your solution about mutliplying by 10000 is the same concept to what I
do currently. It still has the problem of division to extract the
primary key.
Thanks
Paul
Jim Underwood wrote:
> Change your database in the master and client sites to include site ID as
> part of the key in all of them. Then you can write one SQL that will run
> against all of your databases equally. This will server you better in the
> long run.
> Or you can simply multiply the PK by 10000 and add the site ID to it to ge
t
> the new ID. This will give you a new combined ID without all that screwin
g
> around with bits. Much simpler and you will have room for 10000 customers
> before you run out of site IDs. This really is a kludge, however, and not
> the best way to solve the problem.
> <kerplunkwhoops@.yahoo.co.uk> wrote in message
> news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...|||I am not sure if you will be able to get the Site Id by using multiplication
and division.
Consider the following scenario
Site Id Other Id
10 1000 = 10X1000 = 10000
20 500 = 20X500 = 10000
You will not be able to find out the site Id using division as both the
multiplication
results in the same value.
You could try changing the datatype of Master database's Id col to VARCHAR
and have Id values as
10 concatenated with 00001000 as 1000001000
20 concatenated with 00000500 as 1000000500.
I hope doing this will not affect your Queries as there are not new column
and only a DataTypeChange. You could also extract the site id by using
substring (first 2 chrs) functions.
- Sha Anand
"kerplunkwhoops@.yahoo.co.uk" wrote:
> Hello
> I did consider changing the whole of the project to include the site ID
> as a seperate column in the site database, but that would be a huge
> amount of work and the master viewer is a special case for one
> customer. I was trying to make it as simple as possible. It all works
> correctly now, but I am just concerned about the performance.
> Your solution about mutliplying by 10000 is the same concept to what I
> do currently. It still has the problem of division to extract the
> primary key.
> Thanks
> Paul
>
> Jim Underwood wrote:
>
I have created a database that uses an int for the table primary keys.
This database is being deployed at several sites. Each site has a
unique site ID which is less than 100.
There is another database that acts as a master viewer. This database
holds a copy of all the site databases to do reports across all the
sites. Basically each site database gets 'merged' with the master
database once a month.
The master viewer database uses exactly the same structure as the site
database, but needs to store the site ID with every record. I have
currently achieved this by changing the table primary key to be a
bigint and store the site ID in 30 bits and the actual site record
primary in the remaining 34 bits (ok I could have used 32/32). The
reason for not adding another primary key column is because I use the
exact same sql queries in the master database and site databases, and
adding another column would mean creating two seperate queries when
joining tables (one to work on the master and one to work on the sites
- more work and difficult to maintain).
It is relatively simple to extract the site ID and record primary key
from the bigint using bitwise operations and bit shifting.
Unfortunately sql does not support bit shifting and I use division for
the same affect. The only downside I see is the performance issue when
extracting the site primary key in a sql query. If I want to test the
site primary key I use "WHERE ((MaintenanceTransaction_PRK &
-1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
1073741824. If the table has tens of thousands/millions of records I
can see this taking a while.
Does anyone have any other suggestions that I could use?
Many thanks
PaulChange your database in the master and client sites to include site ID as
part of the key in all of them. Then you can write one SQL that will run
against all of your databases equally. This will server you better in the
long run.
Or you can simply multiply the PK by 10000 and add the site ID to it to get
the new ID. This will give you a new combined ID without all that screwing
around with bits. Much simpler and you will have room for 10000 customers
before you run out of site IDs. This really is a kludge, however, and not
the best way to solve the problem.
<kerplunkwhoops@.yahoo.co.uk> wrote in message
news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...
> Hello
> I have created a database that uses an int for the table primary keys.
> This database is being deployed at several sites. Each site has a
> unique site ID which is less than 100.
> There is another database that acts as a master viewer. This database
> holds a copy of all the site databases to do reports across all the
> sites. Basically each site database gets 'merged' with the master
> database once a month.
> The master viewer database uses exactly the same structure as the site
> database, but needs to store the site ID with every record. I have
> currently achieved this by changing the table primary key to be a
> bigint and store the site ID in 30 bits and the actual site record
> primary in the remaining 34 bits (ok I could have used 32/32). The
> reason for not adding another primary key column is because I use the
> exact same sql queries in the master database and site databases, and
> adding another column would mean creating two seperate queries when
> joining tables (one to work on the master and one to work on the sites
> - more work and difficult to maintain).
> It is relatively simple to extract the site ID and record primary key
> from the bigint using bitwise operations and bit shifting.
> Unfortunately sql does not support bit shifting and I use division for
> the same affect. The only downside I see is the performance issue when
> extracting the site primary key in a sql query. If I want to test the
> site primary key I use "WHERE ((MaintenanceTransaction_PRK &
> -1073741824) / 1073741824) = 1" for example (I use "WHERE (1073741823 &
> MaintenanceTransaction_PRK) = 1" to test the site ID) where 2^30 =
> 1073741824. If the table has tens of thousands/millions of records I
> can see this taking a while.
> Does anyone have any other suggestions that I could use?
> Many thanks
> Paul
>|||Hello
I did consider changing the whole of the project to include the site ID
as a seperate column in the site database, but that would be a huge
amount of work and the master viewer is a special case for one
customer. I was trying to make it as simple as possible. It all works
correctly now, but I am just concerned about the performance.
Your solution about mutliplying by 10000 is the same concept to what I
do currently. It still has the problem of division to extract the
primary key.
Thanks
Paul
Jim Underwood wrote:
> Change your database in the master and client sites to include site ID as
> part of the key in all of them. Then you can write one SQL that will run
> against all of your databases equally. This will server you better in the
> long run.
> Or you can simply multiply the PK by 10000 and add the site ID to it to ge
t
> the new ID. This will give you a new combined ID without all that screwin
g
> around with bits. Much simpler and you will have room for 10000 customers
> before you run out of site IDs. This really is a kludge, however, and not
> the best way to solve the problem.
> <kerplunkwhoops@.yahoo.co.uk> wrote in message
> news:1149083975.233210.304140@.i40g2000cwc.googlegroups.com...|||I am not sure if you will be able to get the Site Id by using multiplication
and division.
Consider the following scenario
Site Id Other Id
10 1000 = 10X1000 = 10000
20 500 = 20X500 = 10000
You will not be able to find out the site Id using division as both the
multiplication
results in the same value.
You could try changing the datatype of Master database's Id col to VARCHAR
and have Id values as
10 concatenated with 00001000 as 1000001000
20 concatenated with 00000500 as 1000000500.
I hope doing this will not affect your Queries as there are not new column
and only a DataTypeChange. You could also extract the site id by using
substring (first 2 chrs) functions.
- Sha Anand
"kerplunkwhoops@.yahoo.co.uk" wrote:
> Hello
> I did consider changing the whole of the project to include the site ID
> as a seperate column in the site database, but that would be a huge
> amount of work and the master viewer is a special case for one
> customer. I was trying to make it as simple as possible. It all works
> correctly now, but I am just concerned about the performance.
> Your solution about mutliplying by 10000 is the same concept to what I
> do currently. It still has the problem of division to extract the
> primary key.
> Thanks
> Paul
>
> Jim Underwood wrote:
>
Sunday, March 11, 2012
COM objects under 64-bit?
Hi everyone,
Primary platform is Framework 2.0 under 32-bit
Production platform will be Framework 2.0 under 64-bit.
I'm concerned about what object should I use in order to launch DTS 2000/7.0 from our .Net service.
Currently it tested fine from a 32-bit environment (sql server 2005 as backend).
Reference: "Microsoft DTSPackage Object Library"
Physical file: dtspkg.dll
My wonder, is there any limitation when it's gonna to work on 64-bit?
Generally speaking, any limitation/issue/drawback for COM objects under 64 bit?
In my project properties appears Any CPU, x86, x64. I've got now Any CPU.
Thanks for your advices,
Well, anyway, in any case. I assume that it'd provoke lots of pitfalls, drawbacks and so on.
So then, could I use assemblies in order to launch DTS 2000?
(Microsoft.SqlServer.Dts.Runtime.Application)
Thanks again,
|||Do you mean Microsoft.SqlServer.Dts.Runtime namespace from SQL Server 2005? If yes, it can't run DTS 2000 packages, it can only run SSIS packages.The DTS 2000 only exists in 32-bit version, you can use it on 64-bit OS from 32-bit processes run in WOW64.|||
Hi Michael,
Thanks a lot for your information.
So that, could I deduce that such COM component will work fine on our Window Server 2003 Enterprise x64 Edition?
Thanks again.
|||
hi,
is it so?
|||They will work on x64, but your application has to be 32-bit.So instead of Any CPU, compile it for x86 - it will then run in WOW64 and be able to access DTS COM objects (provided they are installed of course).|||
No, my project got a "ANYCPU". Which is the problem?
Upload dtspkg.dll to the cluster. Or anything else?
|||If you compile for AnyCPU, the executable will run as 64-bit executable on x64 machine. You can only access 32-bit DLLs from 32-bit processes, so you would not have access to DTS objects if executable is compiled for AnyCPU. Your executable has to be compiled for x86 - then it will run in WOW64 on x64 machine (i.e. as 32-bit process, with access to 32-bit COM objects).I think it is more than one DLL, see redistributable docs in SQL 2000 docs for list of files that are might and should be copied, and proper installation instructions.|||
Thanks for that. I'll keep on mind.
So that, leaving as 'Any CPU' it'd caused a pitfall..
But gosh, I understand that my service will run as * in my Task Manager, 32 bit... It would not take advantatge of 64 bit at all...
What a pity!! What could I do in order to fix that?
AFAIK is just a dtspkg.dll, at least when I run my project from VS. VS take dtspkg.dll and it's created (suppose) a RCW called
as "Interop.DTS.dll"
Thanks again.
|||
enric vives wrote:
But gosh, I understand that my service will run as * in my Task Manager, 32 bit... It would not take advantatge of 64 bit at all...
What a pity!! What could I do in order to fix that?
Move to SSIS of course!
|||Ok, Michael, I'm totally agree with you, but listen to me:
We've got around 500 dts "live" up and just ten SSIS running on-production in order to cover our business as usual. As time goes by dts will be transformed into .DTSX, of course!
Currently have two services: one which throws exclusively dts 2000 and the new one which is that we're discussing at (both ssis and dts 2000)
But, thanks indeed.
|||
DTS comming from SQL Server 2000, never mind 7.0, has never been supported on 64-bit. The initial engine support for 64-bit (Liberty?) only came along after the SQL Server 2000 had been relased, and clearly decsion was made that only certain parts of the product would be ported. Forward compatability on hardware is rather a challenge I'd imagine, and going back to add new hardware support for old components like DTS would clearly have impacted the delivery of SQL 2005. MS have been clear about this strategy, so that seems fair to me, you cannot expect old products to be constantly updated to keep pace with new hardware or operating systems. I like my Windows 2003 64-bit over NT4 thanks.
Also think about what adding 64-bit support means to something like a data engine, you need to do some fairly major work around memory managent to allow it to scale over all that nice memory 64-bit gives us. They did that work, and found there was no realistic way forward to make the orginal code base scale, so they wrote the SSIS pipeline. You will often hear variations on that point when speaking to team members at conferences and the like.
Every few genartions in software refresh cycles you get a step change like this. The best thing that has happend is that DTS is still there and supported, so DTS just keeps on working. If your DTS solutions no longer scale, then it seems fair to accept that some re-work will be required, although I would agree that this step change is quite significant, but then so are the benefits and that seems like the trade off.
|||hi Darren,
No, no I'm not complaining about Microsoft's philosophy or regarding ssis at all. I'm complaining about tight dates and about the fact that I'll have to run my service as 32 bit only for keep in mind old ETL.
SSIS is a very good tool!
Columns with multiple values ?
Hi,
The values I need to store in the table are
Student ID
Student Name
Subjects
The "Student ID" is the primary key.
A student can take more than 1 subject.
For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math
How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?
My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.
Or I can create a new field called "RowNumber" and keep that the primary key..
For example:
Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math
If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?...If this is the only way to store the multiple sibjects...It isn't. I'm afraid that your design is really flawed. You would want to be certain that your design is correct before you even start thinking about SQL.
Have a read of this and then come back and let us know what you think your design should now be
http://r937.com/relational.html
EDIT: Oops - first post - welcome to the forum :D
HTH|||Pay particular attention to the section on many-to-many relationships, which I think is applicable to your situation.|||Table # 1: Student Details
---------
Student ID (primary key)
Student Name
Table # 2: Subjects
-------
RowID (primary key)
Student ID
Subject
For example:
We have 2 students, Kelly and Mary.
Kelly's subjects are History and Math
Mary's subjects are Math and English
Table # 1
----
Student ID Student Name
100 Kelly
200 Mary
Table # 2:
----
RowID StudentID Subject
1 100 History
2 100 Math
3 200 Math
4 200 English
When I want to display Kelly's details:
select * from 'Student Details' where StudentID = '100' UNION select * from Subjects where StudentID = '100'
The SQL query might not be of the right syntax, but is this idea right?
I believe this is a one-to-many relationship..|||No. You do not need a UNION query to do this, and it won't even work unless the designs of the two tables are identical.
Again, your database design is flawed. Please read the section on how to create many-to-many relationships.|||The SQL query might not be of the right syntax, but is this idea right?You've got a few details left to resolve, but you've definitely got the right idea. You want to use a JOIN instead of a UNION, but you're headed the right way now.
-PatP
The values I need to store in the table are
Student ID
Student Name
Subjects
The "Student ID" is the primary key.
A student can take more than 1 subject.
For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math
How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?
My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.
Or I can create a new field called "RowNumber" and keep that the primary key..
For example:
Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math
If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?...If this is the only way to store the multiple sibjects...It isn't. I'm afraid that your design is really flawed. You would want to be certain that your design is correct before you even start thinking about SQL.
Have a read of this and then come back and let us know what you think your design should now be
http://r937.com/relational.html
EDIT: Oops - first post - welcome to the forum :D
HTH|||Pay particular attention to the section on many-to-many relationships, which I think is applicable to your situation.|||Table # 1: Student Details
---------
Student ID (primary key)
Student Name
Table # 2: Subjects
-------
RowID (primary key)
Student ID
Subject
For example:
We have 2 students, Kelly and Mary.
Kelly's subjects are History and Math
Mary's subjects are Math and English
Table # 1
----
Student ID Student Name
100 Kelly
200 Mary
Table # 2:
----
RowID StudentID Subject
1 100 History
2 100 Math
3 200 Math
4 200 English
When I want to display Kelly's details:
select * from 'Student Details' where StudentID = '100' UNION select * from Subjects where StudentID = '100'
The SQL query might not be of the right syntax, but is this idea right?
I believe this is a one-to-many relationship..|||No. You do not need a UNION query to do this, and it won't even work unless the designs of the two tables are identical.
Again, your database design is flawed. Please read the section on how to create many-to-many relationships.|||The SQL query might not be of the right syntax, but is this idea right?You've got a few details left to resolve, but you've definitely got the right idea. You want to use a JOIN instead of a UNION, but you're headed the right way now.
-PatP
Thursday, March 8, 2012
Columns IDENTITY property
Hi all.
I have inherited some table full of data. It has a primary key [int]
clustered on one column. But this column was not created with IDENTITY. Now
I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
loosing the data but can't figure out the syntax of proper script :( I know
that EM allows you to do just that but I need a script).
Could anyone help me with that, please? Is it possible at all? Books Online
doc is not clear about this situation.
Thanks!!Here is the script that EM generates to add Identity to a column:
As you can see, you must create a new table,
copy the data from the old table to the new,
drop the old table and rename the new one.
This is a very simple example.
For a table with constraints, triggers, foreign keys etc., the script gets
longer.
On another table I have, the same operation generates about 300 lines of
code.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_numbers
(
number int NOT NULL,
test int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_numbers ON
GO
IF EXISTS(SELECT * FROM dbo.numbers)
EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
SELECT number, test FROM dbo.numbers TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_numbers OFF
GO
DROP TABLE dbo.numbers
GO
EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
GO
COMMIT
"Kikoz" <kikoz@.hotmail.com> wrote in message
news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY.
Now
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I
know
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books
Online
> doc is not clear about this situation.
> Thanks!!
>
>|||You can not do it using ALTER TABLE. Set the identity property in EM and
click "Save change script" button (third from left to rigth).
AMB
"Kikoz" wrote:
> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY. No
w
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I kno
w
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books Onlin
e
> doc is not clear about this situation.
> Thanks!!
>
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>
I have inherited some table full of data. It has a primary key [int]
clustered on one column. But this column was not created with IDENTITY. Now
I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
loosing the data but can't figure out the syntax of proper script :( I know
that EM allows you to do just that but I need a script).
Could anyone help me with that, please? Is it possible at all? Books Online
doc is not clear about this situation.
Thanks!!Here is the script that EM generates to add Identity to a column:
As you can see, you must create a new table,
copy the data from the old table to the new,
drop the old table and rename the new one.
This is a very simple example.
For a table with constraints, triggers, foreign keys etc., the script gets
longer.
On another table I have, the same operation generates about 300 lines of
code.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_numbers
(
number int NOT NULL,
test int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_numbers ON
GO
IF EXISTS(SELECT * FROM dbo.numbers)
EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
SELECT number, test FROM dbo.numbers TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_numbers OFF
GO
DROP TABLE dbo.numbers
GO
EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
GO
COMMIT
"Kikoz" <kikoz@.hotmail.com> wrote in message
news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY.
Now
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I
know
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books
Online
> doc is not clear about this situation.
> Thanks!!
>
>|||You can not do it using ALTER TABLE. Set the identity property in EM and
click "Save change script" button (third from left to rigth).
AMB
"Kikoz" wrote:
> Hi all.
> I have inherited some table full of data. It has a primary key [int]
> clustered on one column. But this column was not created with IDENTITY. No
w
> I need to add IDENTITY to this column (ALTER COLUMN, I guess) without
> loosing the data but can't figure out the syntax of proper script :( I kno
w
> that EM allows you to do just that but I need a script).
> Could anyone help me with that, please? Is it possible at all? Books Onlin
e
> doc is not clear about this situation.
> Thanks!!
>
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>|||That's exactly what I was looking for. Thanx, man!!
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:up6OjnICFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Here is the script that EM generates to add Identity to a column:
> As you can see, you must create a new table,
> copy the data from the old table to the new,
> drop the old table and rename the new one.
> This is a very simple example.
> For a table with constraints, triggers, foreign keys etc., the script gets
> longer.
> On another table I have, the same operation generates about 300 lines of
> code.
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_numbers
> (
> number int NOT NULL,
> test int NOT NULL IDENTITY (1, 1)
> ) ON [PRIMARY]
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers ON
> GO
> IF EXISTS(SELECT * FROM dbo.numbers)
> EXEC('INSERT INTO dbo.Tmp_numbers (number, test)
> SELECT number, test FROM dbo.numbers TABLOCKX')
> GO
> SET IDENTITY_INSERT dbo.Tmp_numbers OFF
> GO
> DROP TABLE dbo.numbers
> GO
> EXECUTE sp_rename N'dbo.Tmp_numbers', N'numbers', 'OBJECT'
> GO
> COMMIT
> "Kikoz" <kikoz@.hotmail.com> wrote in message
> news:ubdqHSICFHA.1564@.TK2MSFTNGP09.phx.gbl...
> Now
> know
> Online
>
Saturday, February 25, 2012
column position sql table
There are about 500 tables in one particular datbase. There areforeign keys set on most of the tables. I want to change the positionof the primary key column in all those tables. How can I do thatprogramatically?
What do you mean by "position" of the PK column?
|||PK = primary key
|||Let me rephrase my question:
What do you mean by"position" of the PK column?
|||Hi dilbert1947,
Based on my understanding, if you mean "the column sequence number" for "the position of the primary key column", then i think there is nothing you have to worry about. In my opinion, changing the column sequence of your primary key won't affect anything in your database schema.
Hope my suggestion can help
|||
Ok let me put it this way. I had to add a primary key column (programmatically) to all the tables in a database. Now when you right click and view the table, you see the newly added primary key column as the right most column. After the primary key column was added, many foreign keys were also added in many of the tables. Now one way to change the column "position" is to manually right click on each table and click modify and then manually move the column to the top, so that every time you view the table, the primary key column appears on the left. Myself being a lazy person, I'd lprefer to write code that would do this in all the tables rather than manually modifying all the tables.
I hope that makes things clear.
|||Do it manually in one of the tables. Then before you save the changes, click the button that says "Generate change SQL file" or something like that. Then read the file and see what the tool was going to execute.
Column Name in a Primary key constraint
Hi all
Would there be a easy way to find the column name(s) which constitute
a Primary constraint for a table through navigating the system
catalogs.
I found that the PK Constraint object in syscontraints is showing the
colid = 0.
TIA
Norman"Norman Leung" <normanl@.interlog.com> wrote in message
news:3d8f97f8.0312031321.685fd5b7@.posting.google.c om...
> Hi all
> Would there be a easy way to find the column name(s) which constitute
> a Primary constraint for a table through navigating the system
> catalogs.
> I found that the PK Constraint object in syscontraints is showing the
> colid = 0.
colid = 0 indicates that it's a composite PK.
To find the columns of the PK, just use the system stored procedure
"sp_pkeys", for example,
sp_pkeys sometablename
However, if you insist on using the system tables to do this, have a look at
the source code for the sp_pkeys procedure. Apparently, you need to join
the syscolumns table with the sysindexes table, filter with a 0x800 mask (I
assume this selects for a primary key index), then select for columns based
on column name using the procedure index_col().
- Dave
...
from
sysindexes i, syscolumns c, sysobjects o
where
o.id = @.table_id
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@.full_table_name, i.indid, c1.colid)
and (c.name = index_col (@.full_table_name, i.indid, 1) or
c.name = index_col (@.full_table_name, i.indid, 2) or
c.name = index_col (@.full_table_name, i.indid, 3) or
c.name = index_col (@.full_table_name, i.indid, 4) or
c.name = index_col (@.full_table_name, i.indid, 5) or
c.name = index_col (@.full_table_name, i.indid, 6) or
c.name = index_col (@.full_table_name, i.indid, 7) or
c.name = index_col (@.full_table_name, i.indid, 8) or
c.name = index_col (@.full_table_name, i.indid, 9) or
c.name = index_col (@.full_table_name, i.indid, 10) or
c.name = index_col (@.full_table_name, i.indid, 11) or
c.name = index_col (@.full_table_name, i.indid, 12) or
c.name = index_col (@.full_table_name, i.indid, 13) or
c.name = index_col (@.full_table_name, i.indid, 14) or
c.name = index_col (@.full_table_name, i.indid, 15) or
c.name = index_col (@.full_table_name, i.indid, 16)
)
> TIA
> Norman|||normanl@.interlog.com (Norman Leung) wrote in message news:<3d8f97f8.0312031321.685fd5b7@.posting.google.com>...
> Hi all
> Would there be a easy way to find the column name(s) which constitute
> a Primary constraint for a table through navigating the system
> catalogs.
> I found that the PK Constraint object in syscontraints is showing the
> colid = 0.
> TIA
> Norman
SELECT
KCU.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON KCU.TABLE_NAME = CCU.TABLE_NAME AND
KCU.COLUMN_NAME = CCU.COLUMN_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
KCU.TABLE_NAME = 'MyTable' AND
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
Simon
Friday, February 24, 2012
Column is constrained to be unique. Value 123 already
I'm using sqlDataAdpater to call a stored procedure for insert
statements.
The stored procedure inserts into all columns except primary key, which
it then selects back at end.
The program does a number of these inserts and then reads back from
database, this all works okay. Later when I go to do more inserts I
get the error message
Column is constrained to be unique. Value 123 already
Below is the stored procedure.
Any help would be greatly appreciated.
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int =null output, @.SelectionOrig_ID int =null,
@.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
=null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
=null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
@.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
)
as
insert into [dbo].[Selection]
(
SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
string2, string3, string4, string5, string6, string7, Hidden, NameLong,
Team_ID, Percentage
)
values
(
@.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
@.Hidden, @.NameLong, @.Team_ID, @.Percentage
)
select * From Selection Where Selection_ID = @.@.IDENTITY
GOChange to this:
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- --
select @.Selection_ID Selection_ID
Return(0)
-- ---
but also consider the following, you can use the SP to do both Inserts and
Updates, switching based on whether or not you pass in a null or non-null
@.Selection_ID as follows
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
-- ---
This has the benefiy of ONLY Updating the columns for which you actually
pass non-null values to the SP.
"dermot" wrote:
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>
"dermot" wrote:
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Missed one word in Raiserror statement
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has deleted or removed Selection %d.',
16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
"CBretana" wrote:
> Change to this:
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- --
> select @.Selection_ID Selection_ID
> Return(0)
> -- ---
> but also consider the following, you can use the SP to do both Inserts and
> Updates, switching based on whether or not you pass in a null or non-null
> @.Selection_ID as follows
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> If @.Selection_ID Is Null
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- ---
> Else If Exists (Select * From [dbo].[Selection]
> Where Selection_ID = @.Selection_ID)
> Update [dbo].[Selection] Set
> SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
> SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
> Event_ID = IsNull(@.Event_ID, Event_ID),
> Name = IsNull(@.Name, Name),
> Type = IsNull(@.Type, Type),
> Odd = IsNull(@.Odd, Odd)
> string1 = IsNull(@.string1, string1),
> string2 = IsNull(@.string2, string2),
> string3 = IsNull(@.string3, string3),
> string4 = IsNull(@.string4, string4),
> string5 = IsNull(@.string5, string5),
> string6 = IsNull(@.string6, string6),
> string7 = IsNull(@.string7, string7),
> Hidden = IsNull(@.Hidden, Hidden),
> NameLong = IsNull(@.NameLong, NameLong),
> Team_ID = IsNull(@.Team_ID, Team_ID),
> Percentage = IsNull(@.Percentage, Percentage)
> Where Selection_ID = @.Selection_ID
> -- ---
> Else
> Begin
> Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
> Return(-1)
> End
>
> Select @.Selection_ID Selection_ID
>
> Return(0)
> -- ---
> This has the benefiy of ONLY Updating the columns for which you actually
> pass non-null values to the SP.
> "dermot" wrote:
>
> "dermot" wrote:
>|||Script out the entire table if you could, just to make sure. I assume there
is no triggers, and that you know where the value 123 comes from?
If you can repeat the operation, use profiler and capture a trace of it
failing. Post that and we can see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1109971489.057346.206560@.l41g2000cwc.googlegroups.com...
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.
statements.
The stored procedure inserts into all columns except primary key, which
it then selects back at end.
The program does a number of these inserts and then reads back from
database, this all works okay. Later when I go to do more inserts I
get the error message
Column is constrained to be unique. Value 123 already
Below is the stored procedure.
Any help would be greatly appreciated.
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int =null output, @.SelectionOrig_ID int =null,
@.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
=null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
=null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
@.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
)
as
insert into [dbo].[Selection]
(
SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
string2, string3, string4, string5, string6, string7, Hidden, NameLong,
Team_ID, Percentage
)
values
(
@.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
@.Hidden, @.NameLong, @.Team_ID, @.Percentage
)
select * From Selection Where Selection_ID = @.@.IDENTITY
GOChange to this:
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- --
select @.Selection_ID Selection_ID
Return(0)
-- ---
but also consider the following, you can use the SP to do both Inserts and
Updates, switching based on whether or not you pass in a null or non-null
@.Selection_ID as follows
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
-- ---
This has the benefiy of ONLY Updating the columns for which you actually
pass non-null values to the SP.
"dermot" wrote:
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>
"dermot" wrote:
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Missed one word in Raiserror statement
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has deleted or removed Selection %d.',
16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
"CBretana" wrote:
> Change to this:
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- --
> select @.Selection_ID Selection_ID
> Return(0)
> -- ---
> but also consider the following, you can use the SP to do both Inserts and
> Updates, switching based on whether or not you pass in a null or non-null
> @.Selection_ID as follows
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> If @.Selection_ID Is Null
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- ---
> Else If Exists (Select * From [dbo].[Selection]
> Where Selection_ID = @.Selection_ID)
> Update [dbo].[Selection] Set
> SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
> SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
> Event_ID = IsNull(@.Event_ID, Event_ID),
> Name = IsNull(@.Name, Name),
> Type = IsNull(@.Type, Type),
> Odd = IsNull(@.Odd, Odd)
> string1 = IsNull(@.string1, string1),
> string2 = IsNull(@.string2, string2),
> string3 = IsNull(@.string3, string3),
> string4 = IsNull(@.string4, string4),
> string5 = IsNull(@.string5, string5),
> string6 = IsNull(@.string6, string6),
> string7 = IsNull(@.string7, string7),
> Hidden = IsNull(@.Hidden, Hidden),
> NameLong = IsNull(@.NameLong, NameLong),
> Team_ID = IsNull(@.Team_ID, Team_ID),
> Percentage = IsNull(@.Percentage, Percentage)
> Where Selection_ID = @.Selection_ID
> -- ---
> Else
> Begin
> Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
> Return(-1)
> End
>
> Select @.Selection_ID Selection_ID
>
> Return(0)
> -- ---
> This has the benefiy of ONLY Updating the columns for which you actually
> pass non-null values to the SP.
> "dermot" wrote:
>
> "dermot" wrote:
>|||Script out the entire table if you could, just to make sure. I assume there
is no triggers, and that you know where the value 123 comes from?
If you can repeat the operation, use profiler and capture a trace of it
failing. Post that and we can see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1109971489.057346.206560@.l41g2000cwc.googlegroups.com...
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.
Subscribe to:
Posts (Atom)