If i am using SqlCommand Class and the CommandType of it is Text, then will it add "sp_exe

If i am using SqlCommand Class and the CommandType of it is Text, then will it add "sp_executesql N" in front of the sql command automatically in fact, just like SqlDataAdapter?If you are using a CommandType of Text, just send the SELECT, INSERT, UPDATE or DELETE command. If you are using CommandType of StoredProcedure, just send the name of the stored procedure. To see exactly what is sent to the SQL Server, use SQL Profiler, which will allow you to capture the exact code sent, which will vary based upon whether you are using parameters, etc.|||that's means i still need to add "sp_executesql N" when i use SqlCommand class and CommandType is Text if i want a better performance?|||As Douglas already suggested if you are not using a stored procedure then use command type text otherwise you should generally use command type stored procedure. You can however still use command type text to run a stored procedure whch does not return records, to do this it would be: "exec procname arg1, arg, arg..."

As for the sp_executesql this internal sql server procedure basically runs every single sql command received by the server. So yes, internally it is always added.

If exists command returning an error

Hello, can anyone see a problem with this T-SQL?

1set ANSI_NULLSON2set QUOTED_IDENTIFIERON3GO4ALTER PROCEDURE [dbo].[Logon_P]5@.User_IDVARCHAR(50),6@.User_PasswordVARCHAR(50)7AS89IFEXISTS(SELECT 110FROM [User]11WHERE [User_Name] = @.User_ID)12BEGIN13RETURN 114IF ((SELECT User_PasswordFROM dbo.[User]WHERE [User_Name]) = @.User_ID) = @.User_Password15BEGIN16RETURN 217END18END19ELSE20RETURN 021

Its returning the following error:

Msg 4145, Level 15, State 1, Procedure Logon_P, Line 11

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

You have a RETURN in your IF..EXISTS block. SQL will return from the block as soon as it sees the RETURN. So the SELECT you have after the RETURN will not be executed. And I dont understand what you are trying to do with your second IF statement inside the first IF block..

|||

Hello my friend,

I have it working on my system. I amended it as follows: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[Logon_P]
(
@.User_ID VARCHAR(50),
@.User_Password VARCHAR(50)
)
AS

-- 0 = no such user
-- 1 = user exists but password incorrect
-- 2 = both user and password match
DECLARE @.Outcome AS TINYINT

IF EXISTS(SELECT 1 FROM [User] WHERE [User_Name] = @.User_ID)
BEGIN
SET @.Outcome = 1

IF EXISTS(SELECT 1 FROM [User] WHERE [User_Name] = @.User_ID AND User_Password = @.User_Password)
BEGIN
SET @.Outcome = 2
END
END
ELSE BEGIN
SET @.Outcome = 0
END

SELECT @.Outcome

In my database, I had a user of scott and a password of blue, so I tested it with the following: -

exec [Logon_P] 'scott', 'blue' -- returns 2

exec [Logon_P] 'scott', 'blue2' -- returns 1

exec [Logon_P] 'scotty', 'blue' -- returns 0

Kind regards

Scotty

|||

Thank you scotty, thats been a massive help.

IF EXISTS

I wanna check the linked server is exist then do nothing and if not then
create linked server. Any command ?
like
if not exists()
begin
EXEC sp_addlinkedserver 'FASAT\FASATBASE'
EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
'testing'
end
Thanks
David
David,
You can try something like:
if not exists
(select * from sysservers where srvname like
'YourLinkedServerName')
-Sue
On Wed, 26 Jan 2005 11:51:32 -0500, "David"
<david@.north.com> wrote:

>I wanna check the linked server is exist then do nothing and if not then
>create linked server. Any command ?
>like
>if not exists()
>begin
> EXEC sp_addlinkedserver 'FASAT\FASATBASE'
> EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
>'testing'
>end
>
>Thanks
>David
>
|||IF NOT EXISTS (SELECT * FROM master..sysservers WHERE..)
Keith
"David" <david@.north.com> wrote in message
news:Om6ZLd8AFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I wanna check the linked server is exist then do nothing and if not then
> create linked server. Any command ?
> like
> if not exists()
> begin
> EXEC sp_addlinkedserver 'FASAT\FASATBASE'
> EXEC sp_addlinkedsrvlogin 'FASAT\FASATBASE', 'false', NULL, 'sa',
> 'testing'
> end
>
> Thanks
> David
>
|||Thanks sue
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bvkfv0putlakl00d3qsh4sf91msjhcntg4@.4ax.com...
> David,
> You can try something like:
> if not exists
> (select * from sysservers where srvname like
> 'YourLinkedServerName')
> -Sue
> On Wed, 26 Jan 2005 11:51:32 -0500, "David"
> <david@.north.com> wrote:
>
|||Howdy Yall,
ALA, like this:
<@.sServerName, @.sSrvProduct, @.sProviderString, @.sPathToMdb are passed-in>
--Kill the server IF it exists and get rid of the user logins too!
IF EXISTS(SELECT * FROM master..sysservers where srvname=@.sServerName)
EXEC sp_dropserver @.server = @.sServerName, @.droplogins = 'droplogins'
--This will create the LINK
EXEC sp_addlinkedserver @.server = @.sServerName,
@.srvproduct = @.sSrvProduct,
@.provider = @.sProviderString,
@.datasrc = @.sPathToMdb
IF @.@.ERROR=0
EXEC sp_addlinkedsrvlogin @.sServerName, 'false', NULL, NULL, NULL
Regards,
jetgeek
"Keith Kratochvil" wrote:

> IF NOT EXISTS (SELECT * FROM master..sysservers WHERE..)
>
> --
> Keith
>
> "David" <david@.north.com> wrote in message
> news:Om6ZLd8AFHA.2792@.TK2MSFTNGP15.phx.gbl...
>

if elseif .. in Store procedure:

Could anyone tell me the sytnax of IF command in store procedure ?
if ..
elseif ...
Thanks a lo.t
Does any tutorial web site teach about SP sytnax ?
There is no ElseIF but you can nest multiple Else's:
IF X = 1
...
ELSE
IF X = 2
...
ELSE
IF X = 3
...
Andrew J. Kelly SQL MVP
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:u9QY2UrDFHA.2632@.TK2MSFTNGP12.phx.gbl...
> Could anyone tell me the sytnax of IF command in store procedure ?
> if ..
> elseif ...
> Thanks a lo.t
> Does any tutorial web site teach about SP sytnax ?
>
|||You can make it a bit more like an elseif, if you put the ELSE and IF on the
same lines:
IF 1 = 0
PRINT 1
ELSE IF 1 = 2
PRINT 2
ELSE
PRINT 3
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:es4sKgrDFHA.2756@.TK2MSFTNGP15.phx.gbl...
> There is no ElseIF but you can nest multiple Else's:
>
> IF X = 1
> ...
> ELSE
> IF X = 2
> ...
> ELSE
> IF X = 3
> ...
> --
> Andrew J. Kelly SQL MVP
>
> "Agnes" <agnes@.dynamictech.com.hk> wrote in message
> news:u9QY2UrDFHA.2632@.TK2MSFTNGP12.phx.gbl...
>

IF Command

Hi All,

I am having a few problems writing a sp for the sales team, I have to display a target figure from a customer dates

start_date target_year1 target_year2 target_year3 target_year4 target_year5 01/01/2004 100 150 120 150 170 01/01/2003 90 20 30 30 30

the start date for each customer is different so the target year 1 ,2 ect will be different time periods.

i need to display the start_date and the target year which relates to todays date:

so no 1 would be year 3 and no2 will be year 4.

any help would be much needed!!

rich

Hi,

To do this you can use case statements.

try this:

select startdate, case datediff(yy, startdate, getdate())

when 0 then targetyear1

when 1 then targetyear 2

.

.

etc

from yourtable

Regards

|||thanks for your help, worked a treat!|||

JMattias

sorry the example was not very good the month my change,

so I would change the yy for month...the next problem is in the selecting between months

select startdate, case datediff(mm, startdate, getdate())

when <12 then targetyear1

when >= 12 and < 24 then targetyear 2

ect..

But this does not work ......

|||

use the datediff statement in every when

Like this

select startdate, case when datediff...... then when datediff..... then etc

Regards

|||thanks ...now that did do the job!!|||Good, I'm glad to help!

IE 7 RC 1 does not respond to Back history command while viewing a report

I have noticed a minor problem in IE 7 RC 1, while viewing a Microsoft
Reporting Services report the Back history command does not work.
Thanks,
Lonyes - I saw this too - I just now load RC 1 now thinking my older version
just had the problem.
Really bites - you think many would have noticed this on their end already.
"Lon" <lonuel@.gmail.com> wrote in message
news:1157478836.906147.116200@.d34g2000cwd.googlegroups.com...
>I have noticed a minor problem in IE 7 RC 1, while viewing a Microsoft
> Reporting Services report the Back history command does not work.
> Thanks,
> Lon
>sql

IDENTITY_INSERT, is it a good idea?

Hi,

Today I discovered this command completely by accident and thought that ther are several places which we could use it in our apps.

Talking with a colleague, he is not to sure as it new to him too.

By using this to recover lost identity values, would this have any possible adverse effects on the table, indexes etc.

I can see potential problems when constraints are set between tables/keys. Anyone with any experience using this good and bad would be useful to hear.

Thanks

Adam

One "gotcha" that can occur is that SET IDENTITY_INSERT requires the DDL_ADMIN privilege; this may not be a privilege that you want to give to rank-and-file users.|||

I have primarily used IDENTITY INSERT for DATA Import / Export jobs where i need to preserve the existing values of ID column and I do not want sql server to generate and I also want sql server to let me insert my own values in indentity columns.

|||

You can look at whether or not it is a good idea in two ways.

Technically: No problem whatsoever. If you know the value that was skipped and want to put a value there, it will not harm anything. All the identity property controls is a counter that adds some value to the previous value you used.

Logically: Bad idea. You should use identity valued columns as if you don't know, don't care the value. It would be better if they used a random number in some ways (though that would stink for building clustered indexes :) The idea behind using an identity value is as a surrogate for some value (as a key). Kind of like identifying you by your employee number. If you are employee 10 or employee 20 it will not change your pay. Either could be the CEO or the guy who sweeps up after the CEO.

So use identity_insert to load a table with existing values is perfectly acceptable, but once loaded, if you can accept gaps it is perfect. If not, then generate your own sequence values, looking for gaps.

IDENTITY_INSERT Problems

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8). (lines below with >>> are code lines. I am using Python, but the
syntax should be about the same as VBScript)
First, I create an ADO Connection and create my table.
[vbcol=seagreen]
[vbcol=seagreen]
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
[vbcol=seagreen]
This works fine. Then, I attempt to allow insertion into the ID_Field.
[vbcol=seagreen]
This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset
[vbcol=seagreen]
[vbcol=seagreen]
Last, I am attempt to add a record to the recordset with an explicit ID,
[vbcol=seagreen]
[vbcol=seagreen]
but this fails with the error of
"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."
Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.
I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.
There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/default...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?
Thanks for any help,
-d
> >>> r = win32com.client.Dispatch('ADODB.Recordset')[vbcol=seagreen]
Ugh, have you considered using an INSERT statement, or calling a stored
procedure that uses an INSERT statement?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hi drs,
I didn't check all your code, but I noticed that the Field_2 column you use
is NOT NULL, and in the code you post you don't insert a value in this
column. I.e. the code as you have posted it will fail because of this.
Jacco Schalkwijk
SQL Server MVP
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gasc37jmmb51e@.corp.supernews.com...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8). (lines below with >>> are code lines. I am using Python, but the
> syntax should be about the same as VBScript)
>
> First, I create an ADO Connection and create my table.
>
>
> Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
> Access Components;SERVER=SERVER\INSTANCE;"'
>
>
>
>
>
>
>
> This works fine. Then, I attempt to allow insertion into the ID_Field.
>
>
>
> This seems to work in that it does not throw an error and gives a return
> of -1. Then I open a Recordset
>
>
>
> Last, I am attempt to add a record to the recordset with an explicit ID,
>
>
>
> but this fails with the error of
>
> "Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done."
>
> Even worse, if I now try to set the identity field to allow inserts again,
> Updating() causes an error that I must use an explicit value for ID_Field,
> but if I try to give it one, it fails with the above error. I have to
> destroy the recordset object at this point to get any further.
>
> I am told that SET IDENTITY_INSERT only remains active for one statement
> and
> thus must be combined with the insert, but I do not know how to do this.
>
> There is a similar sounding bug w/ SQL 7
> (http://support.microsoft.com/default...b;EN-US;253157), but
> there
> is no indication that it affects newer versions of the DB. Does anyone
> have
> any suggestions or ideas?
>
> Thanks for any help,
>
> -d
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e8ssHj1cEHA.2616@.TK2MSFTNGP11.phx.gbl...
> Ugh, have you considered using an INSERT statement, or calling a stored
> procedure that uses an INSERT statement?
Yeah, using an INSERT statement failed in the same way.
-d
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:Oix2sq1cEHA.244@.TK2MSFTNGP12.phx.gbl...
> Hi drs,
> I didn't check all your code, but I noticed that the Field_2 column you
use
> is NOT NULL, and in the code you post you don't insert a value in this
> column. I.e. the code as you have posted it will fail because of this.
Just the example code I posted, not the real code that wouldn't run.
-d
|||Why would you post dummy code and not "the real code that wouldn't run"?
http://www.aspfaq.com/
(Reverse address to reply.)
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gb6057novl208@.corp.supernews.com...
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote
> in message news:Oix2sq1cEHA.244@.TK2MSFTNGP12.phx.gbl...
> use
> Just the example code I posted, not the real code that wouldn't run.
> -d
>
|||> Yeah, using an INSERT statement failed in the same way.
Can you show your new code that fails? The REAL code, not stuff you make up
on the fly?
http://www.aspfaq.com/
(Reverse address to reply.)
|||IDENTITY_INSERT is only active for particular transaction. Even if you turn
IDENTITY_INSERT ON it doesn't mean that you can then insert duplicate values
into the a IDENTITY column. If you have a constant need to insert values
into an IDENTITY column then I suggest that you re-think your database
design.
"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
message news:10gasc37jmmb51e@.corp.supernews.com...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8). (lines below with >>> are code lines. I am using Python, but the
> syntax should be about the same as VBScript)
>
> First, I create an ADO Connection and create my table.
>
>
> Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
> Access Components;SERVER=SERVER\INSTANCE;"'
>
>
>
>
>
>
>
> This works fine. Then, I attempt to allow insertion into the ID_Field.
>
>
>
> This seems to work in that it does not throw an error and gives a return
> of -1. Then I open a Recordset
>
>
>
> Last, I am attempt to add a record to the recordset with an explicit ID,
>
>
>
> but this fails with the error of
>
> "Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done."
>
> Even worse, if I now try to set the identity field to allow inserts again,
> Updating() causes an error that I must use an explicit value for ID_Field,
> but if I try to give it one, it fails with the above error. I have to
> destroy the recordset object at this point to get any further.
>
> I am told that SET IDENTITY_INSERT only remains active for one statement
and
> thus must be combined with the insert, but I do not know how to do this.
>
> There is a similar sounding bug w/ SQL 7
> (http://support.microsoft.com/default...b;EN-US;253157), but
there
> is no indication that it affects newer versions of the DB. Does anyone
have
> any suggestions or ideas?
>
> Thanks for any help,
>
> -d
>
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eNrAMv3cEHA.3128@.TK2MSFTNGP11.phx.gbl...
> Can you show your new code that fails? The REAL code, not stuff you make
up
> on the fly?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
Sure, after the line[vbcol=seagreen]
I tried
[vbcol=seagreen]
-d
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uNJuBv3cEHA.1888@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Why would you post dummy code and not "the real code that wouldn't run"?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in
> message news:10gb6057novl208@.corp.supernews.com...
By "dummy code" I mean that I left out the line where I added a value for
the NOT NULL field -- a perfectly reasonable thing to do as actually leaving
out this line would not cause an error until calling the Update() function.
Further, I changed the dsn to take out my real name, etc. Oh, and the
CREATE statement was shortened so you did not need to read about all of the
fields I was creating which had no relevance to this question, and there
were times when I tried a create statement which did not contain a field
which was NOT NULL. Last, since Python is interactive (the >>> prompts
indicate that I was doing this from the command line) I actually tried over
a hundred different things. What I posted was reasonable example code which
did indeed not work, and which was the closest I could come in a short
amount of space to demonstraiting what I though might work, but which
didn't. It really does not work. It does not fail, however, due to some
problem other than my lack of understanding how the IDENTITY_INSERT command
works. That is to say, the NOT NULLness of a field, or the altered dsn does
not make my question more difficult to understand. I am sorry that my short
response did not elucidate this point more clearly.
Really, what I am looking for is an example of something that does work. I
have been unable to find one online.
So far, no one seems to think my code should work, but why is still a
mystery to me.
-d

IDENTITY_INSERT Problem

Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
2.8) in that I cannot insert a specific value to an identity field. (lines
below with >>> are code lines. I am using Python, but the syntax should be
about the same as VBScript)

First, I create an ADO Connection and create my table.

>>> c = win32com.client.Dispatch('ADODB.Connection')

>>> dsn = 'DRIVER=SQL
Server;UID=myID;Trusted_Connection=Yes;Network=DBM SSOCN;APP=Microsoft Data
Access Components;SERVER=SERVER\INSTANCE;"'

>>> c.Open(dsn)

>>> sql = 'CREATE TABLE Table_Name ('

>>> sql += 'ID_Field INTEGER PRIMARY KEY IDENTITY(1,1), '

>>> sql += 'Field_2 nchar(50) NOT NULL, '

>>> sql += 'Field_3 FLOAT DEFAULT 0.0)'

>>> c.Execute(sql)

This works fine. Then, I attempt to allow insertion into the ID_Field.

>>> c.Execute("SET IDENTITY_INSERT Table_Name ON")

This seems to work in that it does not throw an error and gives a return
of -1. Then I open a Recordset

>>> r = win32com.client.Dispatch('ADODB.Recordset')

>>> r.Open('Table_Name', c, 2, 4)

Last, I am attempt to add a record to the recordset with an explicit ID,

>>> r.AddNew()

>>> r.Fields.Item('ID_Field').Value = 45

but this fails with the error of

"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."

Even worse, if I now try to set the identity field to allow inserts again,
Updating() causes an error that I must use an explicit value for ID_Field,
but if I try to give it one, it fails with the above error. I have to
destroy the recordset object at this point to get any further.

I am told that SET IDENTITY_INSERT only remains active for one statement and
thus must be combined with the insert, but I do not know how to do this.

There is a similar sounding bug w/ SQL 7
(http://support.microsoft.com/defaul...b;EN-US;253157), but there
is no indication that it affects newer versions of the DB. Does anyone have
any suggestions or ideas?

Thanks for any help,

-d"drs" <dsavitsk@.remove-and respell-to-send-mail-YAH-HEW.com> wrote in message news:<10gas90gh9n8j44@.corp.supernews.com>...
> Hi, I am having a problem with IDENTITY_INSERT command with MSDE 2000 (ADO
> 2.8) in that I cannot insert a specific value to an identity field. (lines
> below with >>> are code lines. I am using Python, but the syntax should be
> about the same as VBScript)

<snip
I haven't done much ADO programming, so I can't really say much about
the specific error, except to note that this KB article suggests
reviewing the connection string:

http://support.microsoft.com/defaul...kb;EN-US;269495

You might want to try connecting like this instead, to see if it makes
a difference:

c.Provider = 'sqloledb'
dsn = 'Server=MyServer;Database=MyDB;Trusted_Connection= Yes'
c.Open(dsn)

Apart from that, SET IDENTITY_INSERT remains on for your session until
you turn it off, and it can only be on for one table at a time. So I'm
not sure what you mean by putting it together with the INSERT.

One option to consider is to encapsulate your INSERT in a stored
procedure, then call the stored procedure rather than updating the
recordset directly. I don't know how well this fits with what you're
trying to do, but using stored procedures is good practice anyway:

create proc dbo.MyProc
@.ID_Field int,
@.Field_2 nchar(50),
@.Field_3 float
as
set nocount on
begin
set identity_insert dbo.Table_Name on
insert into dbo.Table_Name
(ID_Field, Field_2, Field_3)
values (@.ID_Field, @.Field_2, @.Field_3)
set identity_insert dbo.Table_Name off
end

Simon

Identity Column

Would like to know how to use SQL 2000 command to modify a IDENTITY column
previously defined as YES...and now need to change it to Yes (Not For
Replication).
Thanks
There is no simple SQL command for that. You have to drop and recreate the
column. If you use Enterprise Manager, you can change the property and then
generate a script (third button from the left) which you can save, study and
run alter.
Jacco Schalkwijk
SQL Server MVP
"CH" <CH@.discussions.microsoft.com> wrote in message
news:5AD5D775-0125-471D-B9ED-EC8A6428099A@.microsoft.com...
> Would like to know how to use SQL 2000 command to modify a IDENTITY column
> previously defined as YES...and now need to change it to Yes (Not For
> Replication).
> Thanks
|||Thanks for your reply. I believe that there's a command to change the
identity. Thanks again.
"Jacco Schalkwijk" wrote:

> There is no simple SQL command for that. You have to drop and recreate the
> column. If you use Enterprise Manager, you can change the property and then
> generate a script (third button from the left) which you can save, study and
> run alter.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "CH" <CH@.discussions.microsoft.com> wrote in message
> news:5AD5D775-0125-471D-B9ED-EC8A6428099A@.microsoft.com...
>
>

Identity Column

Would like to know how to use SQL 2000 command to modify a IDENTITY column
previously defined as YES...and now need to change it to Yes (Not For
Replication).
ThanksThere is no simple SQL command for that. You have to drop and recreate the
column. If you use Enterprise Manager, you can change the property and then
generate a script (third button from the left) which you can save, study and
run alter.
Jacco Schalkwijk
SQL Server MVP
"CH" <CH@.discussions.microsoft.com> wrote in message
news:5AD5D775-0125-471D-B9ED-EC8A6428099A@.microsoft.com...
> Would like to know how to use SQL 2000 command to modify a IDENTITY column
> previously defined as YES...and now need to change it to Yes (Not For
> Replication).
> Thanks|||Thanks for your reply. I believe that there's a command to change the
identity. Thanks again.
"Jacco Schalkwijk" wrote:

> There is no simple SQL command for that. You have to drop and recreate the
> column. If you use Enterprise Manager, you can change the property and the
n
> generate a script (third button from the left) which you can save, study a
nd
> run alter.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "CH" <CH@.discussions.microsoft.com> wrote in message
> news:5AD5D775-0125-471D-B9ED-EC8A6428099A@.microsoft.com...
>
>