script in removing primary key..

help, I need it now, I need a script for removing a primary key constraint. thanks
? ALTER TABLE Tbl DROP Constraint_Name -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <yngielkoh@.discussions..microsoft.com> wrote in message news:a9440570-7c86-4f01-a3b8-6e5926206112@.discussions.microsoft.com...help, I need it now, I need a script for removing a primary key constraint. thanks|||I tried this one but not working..

What do mean by this Constraint_Name? whats the name stands for?
|||? The name of the constraint -- every primary key is named. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <yngielkoh@.discussions..microsoft.com> wrote in message news:effa9b68-2bab-4afb-a96b-0e2c18627229@.discussions.microsoft.com...I tried this one but not working..What do mean by this Constraint_Name? whats the name stands for?

Script

I have a foreign key. I want to script how to create it without using
the management studio i.e. from the query analyser in SQL 2005. Is
there a system stored procedure or something that does this for you.
thanks for your help
NewishNo, you will have to build it like
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
from..
Read up this article
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e960df1a-13fc-43ee-ba91-34c1b719ac2c.htm
"Newish" <ahussain3@.gmail.com> wrote in message
news:1175352912.176014.48640@.q75g2000hsh.googlegroups.com...
>I have a foreign key. I want to script how to create it without using
> the management studio i.e. from the query analyser in SQL 2005. Is
> there a system stored procedure or something that does this for you.
> thanks for your help
> Newish
>|||Here's an example from BOL:
USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;
If you want to get the syntax 'for free' you can create the FK in EM and
rather than actually save the change, simply take a look at the script that
is generated. Cheers, Paul Ibison SQL Server MVP,
www.replicationanswers.com

Script

I have a foreign key. I want to script how to create it without using
the management studio i.e. from the query analyser in SQL 2005. Is
there a system stored procedure or something that does this for you.
thanks for your help
NewishNo, you will have to build it like
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint
+ ']')
from..
Read up this article
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e960df1a-13fc-43ee-ba91-
34c1b719ac2c.htm
"Newish" <ahussain3@.gmail.com> wrote in message
news:1175352912.176014.48640@.q75g2000hsh.googlegroups.com...
>I have a foreign key. I want to script how to create it without using
> the management studio i.e. from the query analyser in SQL 2005. Is
> there a system stored procedure or something that does this for you.
> thanks for your help
> Newish
>|||Here's an example from BOL:
USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;
If you want to get the syntax 'for free' you can create the FK in EM and
rather than actually save the change, simply take a look at the script that
is generated. Cheers, Paul Ibison SQL Server MVP,
www.replicationanswers.com

Script

I have a foreign key. I want to script how to create it without using
the management studio i.e. from the query analyser in SQL 2005. Is
there a system stored procedure or something that does this for you.
thanks for your help
Newish
No, you will have to build it like
exec ('alter table [' + @.table + '] drop constraint [' + @.constraint + ']')
from..
Read up this article
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e960df1a-13fc-43ee-ba91-34c1b719ac2c.htm
"Newish" <ahussain3@.gmail.com> wrote in message
news:1175352912.176014.48640@.q75g2000hsh.googlegro ups.com...
>I have a foreign key. I want to script how to create it without using
> the management studio i.e. from the query analyser in SQL 2005. Is
> there a system stored procedure or something that does this for you.
> thanks for your help
> Newish
>

scope_identity()

I have an ASP front end on SQL 2000 database. I have a form that submits to
an insert query. The entry field is an "identity" and the primary key. I
have used scope_identity() to display the entry# of the record just entered
on the confirmation page. Now I need to insert the entry into another
table. This is my query:

SET NOCOUNT ON
INSERT wo_main
(site_id, customer, po_number)
VALUES ('::site_id::', '::customer::', '::po_number::')
SELECT scope_identity() AS entry
INSERT INTO wo_combo_body
(entry) VALUES ('::entry::')
SET nocount off

This query displays the entry number of the record just entered, but inserts
a 0 in to entry field of the 2nd table. Any help would be great.

Thanks,
Darren>SELECT scope_identity() AS entry
does not assign the identity to a variable named entry, it just
returns a recordset like any other select

either
declare @.Entry int
set @.Entry = (select scope_identity())
insert into table (field) values (@.Entry)

or
insert int table (field) values (select scope_identity())

also move the set nocount off to the top

On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
<celtics@.lan-specialist.com> wrote:

>I have an ASP front end on SQL 2000 database. I have a form that submits to
>an insert query. The entry field is an "identity" and the primary key. I
>have used scope_identity() to display the entry# of the record just entered
>on the confirmation page. Now I need to insert the entry into another
>table. This is my query:
>SET NOCOUNT ON
>INSERT wo_main
> (site_id, customer, po_number)
>VALUES ('::site_id::', '::customer::', '::po_number::')
>SELECT scope_identity() AS entry
>INSERT INTO wo_combo_body
>(entry) VALUES ('::entry::')
>SET nocount off
>This query displays the entry number of the record just entered, but inserts
>a 0 in to entry field of the 2nd table. Any help would be great.
>Thanks,
>Darren|||Hi

If you can use a local variable to hold what is returned by SCOPE_IDENTITY.
This variable can then be used in the second insert statement. You may also
want to add some error checking! Rather than returning a result set it may
also be better(faster) to return the value as a parameter

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:aptZb.32366$um1.10431@.twister.nyroc.rr.com...
> I have an ASP front end on SQL 2000 database. I have a form that submits
to
> an insert query. The entry field is an "identity" and the primary key. I
> have used scope_identity() to display the entry# of the record just
entered
> on the confirmation page. Now I need to insert the entry into another
> table. This is my query:
> SET NOCOUNT ON
> INSERT wo_main
> (site_id, customer, po_number)
> VALUES ('::site_id::', '::customer::', '::po_number::')
> SELECT scope_identity() AS entry
> INSERT INTO wo_combo_body
> (entry) VALUES ('::entry::')
> SET nocount off
> This query displays the entry number of the record just entered, but
inserts
> a 0 in to entry field of the 2nd table. Any help would be great.
> Thanks,
> Darren|||Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
any pitfalls with doing it with a trigger?

Also...

On the same confirmation page I want to diplay links to a page for each
table. Basically I need to select the entry field from each table that I
have inserted to with the trigger. I can then use this as a hyperlink to
the each page. Any ideas on this?

"Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> >SELECT scope_identity() AS entry
> does not assign the identity to a variable named entry, it just
> returns a recordset like any other select
> either
> declare @.Entry int
> set @.Entry = (select scope_identity())
> insert into table (field) values (@.Entry)
> or
> insert int table (field) values (select scope_identity())
> also move the set nocount off to the top
> On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> <celtics@.lan-specialist.com> wrote:
> >I have an ASP front end on SQL 2000 database. I have a form that submits
to
> >an insert query. The entry field is an "identity" and the primary key.
I
> >have used scope_identity() to display the entry# of the record just
entered
> >on the confirmation page. Now I need to insert the entry into another
> >table. This is my query:
> >SET NOCOUNT ON
> >INSERT wo_main
> > (site_id, customer, po_number)
> >VALUES ('::site_id::', '::customer::', '::po_number::')
> >SELECT scope_identity() AS entry
> >INSERT INTO wo_combo_body
> >(entry) VALUES ('::entry::')
> >SET nocount off
> >This query displays the entry number of the record just entered, but
inserts
> >a 0 in to entry field of the 2nd table. Any help would be great.
> >Thanks,
> >Darren|||Hi

You have very little scope to insert new records in a secondary table if you
use a trigger, as you can not pass parameters to it. If there is only one
column in the second table it should be redundant. If there are other
columns then you should write a stored procedure and use a transaction to
maintain consistency see books online :
BEGIN TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ba-bz_96zy.htm
ROLLBACK TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ra-rz_471q.htm
COMMIT TRANSACTION:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_ca-co_7w6m.htm

You should be able to identify the values inserted in a session by including
identifying data in the table such as Username, Datatime, SessionId etc.

John

"Scrappy" <celtics@.lan-specialist.com> wrote in message
news:bnxZb.71491$n62.1826@.twister.nyroc.rr.com...
> Thanks! I used a trigger to accomplish this. I am new to SQL. Are there
> any pitfalls with doing it with a trigger?
> Also...
> On the same confirmation page I want to diplay links to a page for each
> table. Basically I need to select the entry field from each table that I
> have inserted to with the trigger. I can then use this as a hyperlink to
> the each page. Any ideas on this?
>
> "Bruce Loving" <BRUCE@.LOVINGSCENTS.COM> wrote in message
> news:tbtc30lq99c2h3lvjgrh93nt9hmqk1hisc@.4ax.com...
> > >SELECT scope_identity() AS entry
> > does not assign the identity to a variable named entry, it just
> > returns a recordset like any other select
> > either
> > declare @.Entry int
> > set @.Entry = (select scope_identity())
> > insert into table (field) values (@.Entry)
> > or
> > insert int table (field) values (select scope_identity())
> > also move the set nocount off to the top
> > On Fri, 20 Feb 2004 19:40:54 GMT, "Scrappy"
> > <celtics@.lan-specialist.com> wrote:
> > >I have an ASP front end on SQL 2000 database. I have a form that
submits
> to
> > >an insert query. The entry field is an "identity" and the primary key.
> I
> > >have used scope_identity() to display the entry# of the record just
> entered
> > >on the confirmation page. Now I need to insert the entry into another
> > >table. This is my query:
> > > >SET NOCOUNT ON
> > >INSERT wo_main
> > > (site_id, customer, po_number)
> > >VALUES ('::site_id::', '::customer::', '::po_number::')
> > >SELECT scope_identity() AS entry
> > >INSERT INTO wo_combo_body
> > >(entry) VALUES ('::entry::')
> > >SET nocount off
> > > >This query displays the entry number of the record just entered, but
> inserts
> > >a 0 in to entry field of the 2nd table. Any help would be great.
> > > >Thanks,
> > >Darren
>

SCOPE_IDENTITY with ASP

I am seeing a problem with an ASP application, where I have 2 tables.
In the first table, the ASP inserts just 1 row and retrieves the
primary key of the new row using SCOPE_IDENTITY. It then uses that
primary key in the column of a second table (foreign key) to insert
many rows.

What I'm seeing is an intermittent problem where the foreign key in the
second table is not what it should be. I think the problem may be due
to the fact that the insert into the first table and the calling of
SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
in between.

Is it possible that 2 users may be calling my ASP page at the same time
and causing a concurrency problem due to the INSERT and the
SCOPE_IDENTITY being done in 2 different SQL statements? I read that
SCOPE_IDENTITY always returns the last identity value generated from
"the current connection", so I thought that would mean that it wouldn't
get messed up by another ASP request. But now I'm thinking that
perhaps ASP uses connection pooling which could mean that 2 users could
be sharing the same connection which would cause this concurrency
issue.

Does anyone know if my theory of what's wrong is plausible?hmm.. you got me here

Cowly the Game player

Please click on my links
http://spacefed.com
http://gc.gamestotal.org
http://uc.gamestotal.org
http://aw.gamestotal.org
http://www.gamestotal.org
http://3700ad.gamestotal.com
http://www.spacefederation.net
http://www.gamestotal.org/news/
http://ballmonster.gamestotal.com
http://www.spacefederation.net/manual/
http://gc.gamestotal.org/i.cfm?p=aboutgc
http://uc.gamestotal.org/i.cfm?p=aboutgc
http://www.gamestotal.org/corp/
http://www.gamestotal.org/strategygames/|||"Larry" <larry_grant_dc@.hotmail.com> wrote in message
news:1114610604.033965.138570@.o13g2000cwo.googlegr oups.com...
>I am seeing a problem with an ASP application, where I have 2 tables.
> In the first table, the ASP inserts just 1 row and retrieves the
> primary key of the new row using SCOPE_IDENTITY. It then uses that
> primary key in the column of a second table (foreign key) to insert
> many rows.
> What I'm seeing is an intermittent problem where the foreign key in the
> second table is not what it should be. I think the problem may be due
> to the fact that the insert into the first table and the calling of
> SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
> in between.
> Is it possible that 2 users may be calling my ASP page at the same time
> and causing a concurrency problem due to the INSERT and the
> SCOPE_IDENTITY being done in 2 different SQL statements? I read that
> SCOPE_IDENTITY always returns the last identity value generated from
> "the current connection", so I thought that would mean that it wouldn't
> get messed up by another ASP request. But now I'm thinking that
> perhaps ASP uses connection pooling which could mean that 2 users could
> be sharing the same connection which would cause this concurrency
> issue.
> Does anyone know if my theory of what's wrong is plausible?

You don't mention if you're using stored procedures, but your description
seems to suggest you aren't. SCOPE_IDENTITY() returns the last value
inserted "within the current scope" - if you do your INSERTs in a stored
proc, then the proc itself is the scope, so there is no problem with
concurrency. But if you're executing each SQL statement directly, then there
could be a concurrency issue because all statements using the same
connection would share the same scope.

Apart from this issue, using stored procedures is generally a good idea, for
a number of security and performance reasons:

http://www.sommarskog.se/dynamic_sql.html#Why_SP

Simon|||I am not using stored procs.

I still have a question about your statement "there could be a
concurrency issue because all statements using the same connection
would share the same scope".

The connection is created within my ASP page. If several users call
the ASP page at the same time, is that considered the "same connection"
or different connections?|||"Larry" <larry_grant_dc@.hotmail.com> wrote in message
news:1114627612.035136.42880@.z14g2000cwz.googlegro ups.com...
>I am not using stored procs.
> I still have a question about your statement "there could be a
> concurrency issue because all statements using the same connection
> would share the same scope".
> The connection is created within my ASP page. If several users call
> the ASP page at the same time, is that considered the "same connection"
> or different connections?

If IIS opens only one connection to the server, then yes, that would all be
in the same scope. If each execution of the ASP page opens a new connection
to MSSQL (which seems unlikely to me, but I know almost nothing about ASP),
then they would be in different scopes. You can use sp_who2 to view the
current connections, and also see sysprocesses and @.@.SPID in Books Online.

If you're unsure about how ASP is managing connections, you'll probably get
better feedback in an ASP forum, although from a purely SQL perspective, I
suspect that using a stored proc should solve the issue anyway.

Simon|||Larry (larry_grant_dc@.hotmail.com) writes:
> I am seeing a problem with an ASP application, where I have 2 tables.
> In the first table, the ASP inserts just 1 row and retrieves the
> primary key of the new row using SCOPE_IDENTITY. It then uses that
> primary key in the column of a second table (foreign key) to insert
> many rows.
> What I'm seeing is an intermittent problem where the foreign key in the
> second table is not what it should be. I think the problem may be due
> to the fact that the insert into the first table and the calling of
> SCOPE_IDENTITY are done in 2 separate ASP statements with some ASP code
> in between.
> Is it possible that 2 users may be calling my ASP page at the same time
> and causing a concurrency problem due to the INSERT and the
> SCOPE_IDENTITY being done in 2 different SQL statements? I read that
> SCOPE_IDENTITY always returns the last identity value generated from
> "the current connection", so I thought that would mean that it wouldn't
> get messed up by another ASP request. But now I'm thinking that
> perhaps ASP uses connection pooling which could mean that 2 users could
> be sharing the same connection which would cause this concurrency
> issue.

I don't know ASP, but what is important is that you cannot use a
model where you connect for each query here, but you must use the
same connection for the two queries, so that you retain scope.

But it may be easier to send the SELECT satement as part of the
INSERT batch to save a round trip.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp