drop last bit

Hi,

I have an sql script, which updates some values by using BIT operations..

ex:

UPDATE table1

SET myValue = myValue & ~mask | (availability - mask)...

Problem is, this returns a value, when written to binary, it's one bit too long!

ex:

0 1 1 1 1 1

(= 62)

which should be:

0 1 1 1 1 0

(=31)

How can I "drop" this last BIT?

(Keep in mind that I store these values as LONGINT's)...

Been fighting with this one all day..

Try this...

Code Snippet

select cast(substring(rtrim(convert(char(19),<columnName>)),1,len(rtrim(convert(char(19),<columnName>)))-1) as bigint)

drop constraints

Hi,

I'm trying to update some tables, but there are constraints on them that need to be removed first. As I didn't create the DB and tables myself, I used the 'Generate SQL script' to get all constraints and their name.

I then had a look at the 'sysobject' table, and found some constraints (FK__ ...) listed in the script, but not all of them. Is there another way to to get all constraints on the DB ??

can you suggest the best way to drop the constrains? I was going to use something like:

declare @.mytest char(50)
set @.mytest=(select name from sysobjects where name like "FK__Item__attrib%")
EXEC( 'alter table item drop constraint '+@.mytest)

vincentI use this code to remove PRIMARY and FORIEGN KEY constraints plus drop all of my indexes, however I removed the DROP INDEX part
SET NOCOUNT ON
DECLARE @.SQLCmd varchar(255)

DECLARE DelCur CURSOR
FOR
SELECT CASE
WHEN OBJECTPROPERTY(OBJECT_ID(i.name), 'IsPrimaryKey') = 1
THEN 'ALTER TABLE ' + + o.name + ' DROP CONSTRAINT ' + i.name
WHEN OBJECTPROPERTY(OBJECT_ID(i.name), 'IsForiegnKey') = 1
THEN 'ALTER TABLE ' + + o.name + ' DROP CONSTRAINT ' + i.name
END
FROM sysindexes i,
sysobjects o
WHERE o.id = i.id
AND OBJECTPROPERTY(o.id, 'IsUserTable') = 1
AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
AND i.indid BETWEEN 1 AND 254
AND INDEXPROPERTY(o.id, i.name, 'IsStatistics') = 0
AND (
OBJECTPROPERTY(OBJECT_ID(i.name), 'IsPrimaryKey') = 1 OR
OBJECTPROPERTY(OBJECT_ID(i.name), 'IsForiegnKey') = 1
)

OPEN DelCur

FETCH DelCur INTO @.SQLCmd

WHILE (@.@.fetch_status = 0)
BEGIN
PRINT @.SQLCmd
EXEC (@.SQLCmd)
FETCH DelCur INTO @.SQLCmd
END

CLOSE DelCur
DEALLOCATE DelCur
GO

Drop all column extended propterties

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)

Drop all column extended propterties

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)