Remove identity

Hi all,

Is there a way to remove the identity from a column
by using alter table?

thanx

Hi,

identity is no property, you have to create another column, copya the values to the new column and drop the old one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Hi,

In the SQL Server Management Studio Express, when I right click in table, and I choice the modify option, bellow appear the column properties where I set the Identity Specification to No or Yes.

This option disables/enable the identity?

But is there a way to disable the identity via command line?

thanx|||

Hi,
"disabling" the property does the things behind the scenes I mentioned in my earlier post. In SQL 2k (I didn't check that in SQL2k5 right now) it did a creation of a new table, copy the data to the new table, and renamed the table vice versa to the new / old name.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank u very much for your help

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

sql

Remove FileGroup

I have removed all the files from a filegroup ...when I do sp_helpdb I don't see the filegroup... but when i try to remove the filegroup using ALTER DATABASE <DB> REMOVE FILEGROUP, it says The filegroup 'XXX' cannot be removed because it is not empty

any idea...

Thanks

Ranga

Hi

I've encountered the same issue.

Hi guys, is there a solution for this problem.

Below is other post for the same issue.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=446565&SiteID=1

thanks,
Larry

|||

Hi Ranga,

Maybe a partition scheme is still mapped to the filegroups.

Try dropping the partition scheme before removing the filegroups

|||

We are having this same problem. We want to drop old files and filegroups from our partitioned database. We can drop the files. However, there does not seem to be anyway to remove a filegroup from an existing partition, even if the filegroup is empty. Dropping the partition is NOT an option in our case, as the partitioned table contain 2 billion+ records and to rebuild the partition would likely take 2 weeks or longer.

Q to Microsoft support: How do we remove a Filegroup from a partition? Once a FG is added to a partition, are we stuck with it being there indefinately?

Thanks.

remove cursor

hi below is my procedure..i don't want to use cursor....than what should be my query?

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

DECLARE @.Bank_Name VARCHAR(50)
DECLARE @.MICR_CMSCode varchar(50),@.MICR_SchemeGroup varchar(50)

--BANK CURSOR
DECLARE CUR_BANK CURSOR FOR
SELECT Bank_Name FROM Tbl_BankMst where Bank_isactive=1

OPEN CUR_BANK

FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
WHILE @.@.FETCH_STATUS=0
BEGIN

--print(@.Bank_Name)
--MICR CURSOR
DECLARE CUR_MICR cursor for
--Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_BankName='ICICI BANK LTD'
Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_AuthStatus =2 and MICR_Optype =0 and MICR_BankName=rtrim(ltrim(@.Bank_Name))

Open CUR_MICR

Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup
while @.@.fetch_status = 0
begin
update Tbl_CMS_UploadDetails set CMS_SchemeGroup =@.MICR_SchemeGroup Where Scheme_Code=rtrim(ltrim(@.MICR_CMSCode))

--print(@.MICR_SchemeGroup)--@.MICR_CMSCode
Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup

end
close CUR_MICR
deallocate CUR_MICR

- update compare status and maker-

declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName
--select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name='ICICI BANK LTD'

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
-
FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
END

close CUR_BANK
deallocate CUR_BANK

hi try this..

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1
--and a.MICR_BankName = rtrim(ltrim(@.Bank_Name))
) d
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

END
GO|||oops, i forgot to add the on clause..

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1
--and a.MICR_BankName = rtrim(ltrim(@.Bank_Name))
) d ON Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

END
GO|||thanx let me try i will be back.sql

Remove a constraint

I executed the following sp several times

ALTER PROCEDURE [dbo].[sp_GetItemsByCategoryOnPage]

AS

BEGIN

CREATE TABLE #TemporaryItems

(

ID int IDENTITY PRIMARY KEY,

...

[auto] [bit] NULL CONSTRAINT [DF_CostupdateItems_auto] DEFAULT ((1)),

...

)

...

END

Suddlenly I got a message:

The constraint [DF_CostupdateItems_auto] already exists. How can I remove it from DB?

Hi there,

You can use such a statement :

alter table YourTableNameWithThisConstraint drop constraint [DF_CostupdateItems_auto]

The constraints store in each Table's structures not in global.

__
May the God blessings be.

LotraSoft Ltd.

|||You will get the error message if multiple connections call the SP simultaneously. Even though the temporary table name is uniquefied automatically by SQL Server since you have a named constraint for the default you can't only execute the SP serially. So either remove the constraint name from the column definition so that SQL Server can automatically generate a unique name or don't call the SP concurrently.