Remove Identity seed w/ T-SQL

Does anyone know how to remove the Identity attribute of a column using T-SQL. I have created the Identity attribute with the following. I know I can do it with a script created through Enterprise Manager (which is really long and hard to re-create with Dynamic SQL), but I am looking for a one line opposite to the following statement:

IF (SELECT COL_LENGTH('[dbo].tablename', 'UserID')) IS NULL
ALTER TABLE [dbo].tablename ADD UserID INT NOT NULL IDENTITY (1, 1)

something like:
ALTER TABLE [dbo].tablename ALTER COLUMN UserID"REMOVE IDENTITY"

Thanks,
JoshThere is not a simple way to accomplish this. You need basically to drop the column altogether and then add it back to the table. as a non-identity column.

So you could add a new non-identity column to the table (UserIDNEW), copy your UserID data into it, drop the UserID column, then rename your UserIDNEW column back to UserID.

Wish I had better news.

I looked upthis article to explain more concretely what I am talking about.

Terri|||Terri,
I too found the same article. I didn't think there was a simple way to do it, but I thought I would ask.

Thanks,
Josh

Remove identity property of a primary key

Hi,
I have a table with a column named ID as primary key, this column has
the identity property. This ID is referenced by some other tables as foreign
key.
Is there a way, I can use "alter table alter ID int not null...." TSQL
to remove this identity property?
Thanks!
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours Hardy1. drop FK
2. sp_rename table with identity to some other name
3. create table new with the same name without identity
4. insert new select * from old
5. create FK
6 drop old
"Hardy Wang" <hardywang@.hotmail.com> wrote in message
news:O7AqRnXrFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as
> foreign key.
> Is there a way, I can use "alter table alter ID int not null...." TSQL
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>|||The only way is dropping the column. Try using EM if you really want to do
this. In EM, before saving changes, press button "save change script", third
from left to right in the tool bar. You will see what really EM does in orde
r
to accomplish this tak.
Example: (from northwind.orders)
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
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
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
DECLARE @.v sql_variant
SET @.v = N''
EXECUTE sp_addextendedproperty N'MS_Description', @.v, N'user', N'dbo',
N'table', N'Tmp_Orders', N'column', N'OrderID'
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID,
OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry FROM dbo.Orders (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT
AMB
"Hardy Wang" wrote:

> Hi,
> I have a table with a column named ID as primary key, this column has
> the identity property. This ID is referenced by some other tables as forei
gn
> key.
> Is there a way, I can use "alter table alter ID int not null...." TSQ
L
> to remove this identity property?
> Thanks!
> --
> WWW: http://hardywang.1accesshost.com
> ICQ: 3359839
> yours Hardy
>
>

Remove Identity column constraint/mgmt

Hello...
We have some tables that had been using Identity columns as a Primary
Key...but we abandoned that approach a few weeks ago and adopted GUIDs
instead.
These tables are included in Publications that were originally on SQL
2000...but we upgraded to SQL 2005.
Is there a way I can remove the Identity constraint from the server
pub...does the Not for Replication handle this?
Or...would be better to Drop the column and reinitialize the subscribers?
thanks for any help
- will
You would be best to drop the identity column, NFR will not drop the
identity column but will not enforce the identity property if the insert is
caused by a replication process.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
> Hello...
> We have some tables that had been using Identity columns as a Primary
> Key...but we abandoned that approach a few weeks ago and adopted GUIDs
> instead.
> These tables are included in Publications that were originally on SQL
> 2000...but we upgraded to SQL 2005.
> Is there a way I can remove the Identity constraint from the server
> pub...does the Not for Replication handle this?
> Or...would be better to Drop the column and reinitialize the subscribers?
> thanks for any help
> - will
|||Thanks for the help. I may test out going the NFR route...just so I don't
have to mess with the table schema too much. Will changing to NFR force a
Re-Init of for the subscribers?
"Hilary Cotter" wrote:

> You would be best to drop the identity column, NFR will not drop the
> identity column but will not enforce the identity property if the insert is
> caused by a replication process.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
>
>
|||This is something you do on the subscriber - so for transactional
replication it will have no impact on reinitialization. For merge and
updateable subscribers it will.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:C109A5B7-07DE-4E85-88C5-6BA71CBCCEFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the help. I may test out going the NFR route...just so I
> don't
> have to mess with the table schema too much. Will changing to NFR force a
> Re-Init of for the subscribers?
> "Hilary Cotter" wrote:
sql

Remove Identity

I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi.
You can use something as this:
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_city_desc] [varchar] (50) DEFAULT ('')
) ON [PRIMARY]
GO
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NULL
Hermilson T.
****************************************
**********************
I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
Toeen|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> ALTER
for
> rights.
>

Remove Identity

I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
ToeenHi
You can use something as this
Create tha table with Identity...
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL
[f_city_desc] [varchar] (50) DEFAULT (''
) ON [PRIMARY
G
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NUL
Hermilson T
*************************************************************
I have created an identity column in a table.Now i want to remove th
identity property from the column by using T-SQL script
Can anyone tell How
Thanking you
Toee|||> You can use something as this:
> Create tha table with Identity...
> CREATE TABLE [City]
> ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> [f_city_desc] [varchar] (50) DEFAULT ('')
> ) ON [PRIMARY]
> GO
> And to Modify the column, erasing the property of the Identity.
> Alter TABLE City
> Alter column f_city_id [int] NOT NULL
You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > You can use something as this:
> >
> > Create tha table with Identity...
> >
> > CREATE TABLE [City]
> > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [f_city_desc] [varchar] (50) DEFAULT ('')
> > ) ON [PRIMARY]
> > GO
> >
> > And to Modify the column, erasing the property of the Identity.
> >
> > Alter TABLE City
> > Alter column f_city_id [int] NOT NULL
> You can't clear the IDENTITY property from the table column using the
ALTER
> TABLE statement. You have to re-create the database and move the data, for
> example:
> BEGIN TRANSACTION
> CREATE TABLE dbo.TemporaryTable
> (
> id int NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.YourTable)
> EXEC('INSERT INTO dbo.TemporaryTable(id)
> SELECT id FROM dbo.YourTable TABLOCKX')
> GO
> DROP TABLE dbo.YourTable
> GO
> EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> GO
> COMMIT
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing
set identity insert <tablename> on
Then you can insert your values. When you are done, set identity insert to
off.
Eric
"Toeen" <masha@.brain.net.pk> wrote in message
news:uMU5qUJ8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> I have created an identity column in a table.Now i want to remove the
> identity property from the column by using T-SQL script.
> Can anyone tell How?
> Thanking you!
> Toeen
>|||> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.
SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dandy WEYN" <no_spam_info@.dandyman.net> wrote in message
news:402aaa65$0$13253$ba620e4c@.news.skynet.be...
> Or you can use a select into statement
>
> select * into newtable from sourcetablewithidentity
> drop table sourcetablewithidentity
> exec sp_rename dbo.TemporaryTable', 'YourTable'
> --
> Dandy Weyn, Belgium
> MCSE, MCSA, MCDBA, MCT
> http://www.dandyman.net
> Check my SQL Server resource pages (currently under construction)
> http://www.dandyman.net/sql
>
> "Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
> in message news:usLELMK8DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > > You can use something as this:
> > >
> > > Create tha table with Identity...
> > >
> > > CREATE TABLE [City]
> > > ( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
> > > [f_city_desc] [varchar] (50) DEFAULT ('')
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > And to Modify the column, erasing the property of the Identity.
> > >
> > > Alter TABLE City
> > > Alter column f_city_id [int] NOT NULL
> >
> > You can't clear the IDENTITY property from the table column using the
> ALTER
> > TABLE statement. You have to re-create the database and move the data,
for
> > example:
> >
> > BEGIN TRANSACTION
> > CREATE TABLE dbo.TemporaryTable
> > (
> > id int NOT NULL
> > ) ON [PRIMARY]
> > GO
> > IF EXISTS(SELECT * FROM dbo.YourTable)
> > EXEC('INSERT INTO dbo.TemporaryTable(id)
> > SELECT id FROM dbo.YourTable TABLOCKX')
> > GO
> > DROP TABLE dbo.YourTable
> > GO
> > EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
> > GO
> > COMMIT
> >
> > sincerely,
> > --
> > Sebastian K. Zaklada
> > Skilled Software
> > http://www.skilledsoftware.com
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
>

Remove Duplicate value from One Cloumn Table

I am working SQL Server 2005 and One Table Which contain only one column without primary key
Now I want to remove all duplicate value from that table with only single query
Here are a couple options:
http://www.sqlteam.com/item.asp?ItemID=3331
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
|||

Thank

Here happens like that

1) Select Distinct row from the original table and store it into tempory table

2) delete all rows from original table

3) copy the all rows from tempory table (where distinct rows inserted)

But I want to do this thing using single Delete Query
Can we do this by using only single delete query

sql

Remove duplicate rows from table

I have a table with one column, and i want to remove those records from the table which are duplicate i meant if i have a records rakesh in table two time then one records should be remove...
my tables is like that

Names
----
Rakesh
Rakesh
Rakesh Kumar Sharma
Rakesh Kumar Sharma
Baburaj
Raghu
Raghu

and Output of query should be like that
Names
----
Rakesh
Rakesh Kumar Sharma
Baburaj
Raghu

Thanks in advanceSELECT * FROM table1
UNION
SELECT * FROM table1
or
SELECT distinct name FROM table1

...;)

Plz give the whole structure of your table,then only we can help you.Plz read the sticky above in this forum.|||Hi,

You can find three methods with samples for removing dublicated records from tables at article http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

One method is using "SET ROWCOUNT", an other method uses the "TOP". And the last way of solving this problem is getting use of a temporary "IDENTITY" column.

I hope you find it useful

Eralper
http://www.kodyaz.com|||As always, make a back-up first!
select distinct *
into #temptable
from yourtable

delete from yourtable

insert into yourtable
select *
from #temptable

drop table #temptable

Remove CRLF

I have a column in a data flow task which contains carriage return/line breaks. Is it possible to remove them with a derived column - or is there a better way to do this?

thanks in advance

Yeah, it should be possible. Use the REPLACE function. "\r\n" indicates a CRLF

-Jamie

remove columns created by replication process

One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :

select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'

Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :

...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?I'd use:DECLARE @.crlf CHAR(2)
SET @.crlf = Char(13) + Char(10)-PatP|||Could you give me more explanation (why : char(13)+char(10))
I tried only char(13) only and I noticed the result (space in the beginning of the line).|||You really want the history lesson?

Ok, back in the days of CP/M, there was hot debate as to what constituted a "line end". The Unix crew wanted Line Feed (0x0a). The OASIS crew wanted Carriage Return (0x0d). Nobody would budge.

Teletypes needed both, and CR took longer to execute than LF did, so it was always sent first. Since nobody could make a "command decision" Gary Kildall made the call that they'd use what the teletypes wanted, to make it easier to print files and vex both of the software camps!

MS-DOS basically picked up where CP/M left off, so it followed the same convention. Windoze is the GUI that was later bolted on to MS-DOS, so it used the same convention... You see where we are headed here, right?

Anywho, the short answer boils down to Transact SQL sees a "line end" as being a carriage return followed by a line feed, aka Char(13) + Char(10) to us hydro-carbon based types.

-PatP

remove chr(10) char with Tsql?

Hello,
I am writing data to a table using a DTS package from a VB
app. One of the columns, nvarchar column, is getting
carriage/return char appended to the data.
Select '*' + fld1 + '*' From tbl1
fld1 contains digits. I get this
* 10*
If I do this:
Declare @.s varchar(10)
Select @.s = fld1 From tbl1
Print '*' + @.s + '*'
I get this:
*
10*
If I say Update tbl1 Set fld1 = Ltrim(fld1)
I still get * 10*. But if I say Update tbl1 Set fld1 = 10
Now I get *10*
How can I remove these chr(10) chars with Tsql? Should I
loop through a cursor and use Substring?
declare @.s varchar(20)
declare @.t varchar(20)
select @.s = cn from tblincidentsn where rownum = 1
print '*' + @.s + '*'
set @.t = substring(@.s,3, len(@.s) - 2)
print ''
print '*' + @.t + '*'
gives me
*
02*
*02* <-- correct format
Thanks in advance for any suggestions.
RonAre you sure you don't want both CHAR(13) and CHAR(10) (CR and LF,
respectively)?
SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||SET @.s = REPLACE(REPLACE(@.s, CHAR(10), ''), CHAR(13), '')
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||> SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
I usually replace them separately, for two reasons:
(a) not all programs behave well. Some only include 10, some only include
13.
(b) not all programs behave well. Many include these in the wrong order.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePxgmo2IFHA.3484@.TK2MSFTNGP12.phx.gbl...
> (a) not all programs behave well. Some only include 10, some only include
> 13.
> (b) not all programs behave well. Many include these in the wrong order.
Both are excellent points!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

remove all double quotes from column values using t-sql

Hi,
I am having problem withj double quotes being inserted automatically when i
am inserting data using a CSV file.I am using a C# program to insert the
values.
My coulmn is called whereClause and it is a varchar(50) e.g
'ISNULL(salary,2000)=2000'
but what gets inserted is "'ISNULL(salary,2000)=2000'"
I am using this value in dynamic sql to generate a query such as
exec 'select salary from employee where '+@.whereClause and I get error
because of the automatic double quote insertion.
The CSV file does not have double quotes so I need a solution that will do
either 1 of the following
1)suppress insertion of double quotes in the table in C# OR
2)write query to remove the double quotes using T-SQL
Solution 2 is preferable.
Your help is highly appreciated.
Thanks.if I understood the question correctly, this should work..
declare @.a varchar(30)
set @.a = '"ISNULL(salary,2000)=2000"'
select replace(@.a,'"','')|||Thans for the reply.
But the problem is I have about a thousand rows and each condition is
different and having double quotes eg
id whereClause
1 "ISNULL(salary,5000)=5000"
2 "ISNULL(bonus,400)>600"
3 "IN(600,40)"
I want to remove all double quotes and update the table with proper values i
e
if table contains "ISNULL(salary,5000)=5000" I want to update it as
ISNULL(salary,5000)=5000
Thanks.
"Omnibuzz" wrote:

> if I understood the question correctly, this should work..
> declare @.a varchar(30)
> set @.a = '"ISNULL(salary,2000)=2000"'
> select replace(@.a,'"','')|||Use REPLACE:
create table #x (s varchar(30))
INSERT #x values ('abcdefg')
INSERT #x values ('"hijklmn"')
INSERT #x values ('o"p"q"r"s"t"u')
select * from #x
UPDATE #x
SET S = REPLACE(S,'"','')
select * from #x
Roy Harvey
Beacon Falls, CT
On Wed, 5 Apr 2006 11:18:02 -0700, tech77
<tech77@.discussions.microsoft.com> wrote:

>Hi,
>I am having problem withj double quotes being inserted automatically when i
>am inserting data using a CSV file.I am using a C# program to insert the
>values.
>My coulmn is called whereClause and it is a varchar(50) e.g
>'ISNULL(salary,2000)=2000'
>but what gets inserted is "'ISNULL(salary,2000)=2000'"
>I am using this value in dynamic sql to generate a query such as
>exec 'select salary from employee where '+@.whereClause and I get error
>because of the automatic double quote insertion.
>The CSV file does not have double quotes so I need a solution that will do
>either 1 of the following
>1)suppress insertion of double quotes in the table in C# OR
>2)write query to remove the double quotes using T-SQL
>Solution 2 is preferable.
>Your help is highly appreciated.
>Thanks.|||Thanks.That worked like a charm!!
"Roy Harvey" wrote:

> Use REPLACE:
> create table #x (s varchar(30))
> INSERT #x values ('abcdefg')
> INSERT #x values ('"hijklmn"')
> INSERT #x values ('o"p"q"r"s"t"u')
> select * from #x
> UPDATE #x
> SET S = REPLACE(S,'"','')
> select * from #x
> Roy Harvey
> Beacon Falls, CT
> On Wed, 5 Apr 2006 11:18:02 -0700, tech77
> <tech77@.discussions.microsoft.com> wrote:
>
>sql