Check multiple records before inserting records

i have a table which is having size and pieces information

Size, pieces, sizeID, sizecombID

S, 1, 1, 1

M, 3, 2, 1

L, 3, 3, 1

XL, 1, 4, 1

I have two questions :-

1. If user enters the same information i.e. same sizes and same pieces then my stored procedure should not add these four records, instead it should return existing value of SizecombID(which stands for size combination ID)

2. If a totally new information is entered then that should be added in the table and sizecombID should be updated with highest value + 1. So for example if there is only one record in table as shown above then once a new combination is entered, my stored procedure should return 2..

can anybody help on this...

One possibility:

Code Snippet

SELECT SizeCombID

FROM MyTable

WHERE ( Col1 = NewValue1

AND Col2 = NewValue2

AND Col3 = NewValue3

)

IF ( @.@.ROWCOUNT = 0 )

INSERT INTO MyTable

( Col1,

Col2,

Col3

)

VALUES

( NewValue1,

NewValue2,

NewValue3

)

|||

Actually the code snippet is only checking 1 record which is not the case.

1. I will be sending all the entered records to my stored procedure, what is the parameter to be used for sending multiple records

2. All records entered by user are to be checked. In exmaple which was provided, user has entered four sizes so it will be four records. All four records are to be checked.

3. Can you also tell how i can increment the sizecombid automatically to generate next value. Before Insert statement, stored procedure should get the last sizecombID value, add 1 in it and then add that value also to SizecombID.

Pls advs..

|||

1.Passing Multiple Records:

Use XML string, you can get back those records in same structure you want.
There is no limitation since you can use either text or ntext as parameter datatype.

(Ex)

Code Snippet

Declare @.Param as Varchar(8000);
Select @.param = '<Root>
<Row Size="S" pieces="1" sizeID="1" sizecombID="1"/>
<Row Size="M" pieces="3" sizeID="2" sizecombID="1"/>
<Row Size="L" pieces="3" sizeID="3" sizecombID="1"/>
<Row Size="XL" pieces="1" sizeID="4" sizecombID="1"/>
</Root>'

Declare @.iDoc as Int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.param;

SELECT *
FROM OPENXML (@.idoc, '/Root/Row',1)
WITH (Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int)

EXEC sp_xml_removedocument @.iDoc;

2. You can store the xml result in Table variable and you can validate those with your main tables.

(ex)

Code Snippet

Declare @.Input table
(
Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int
);
insert Into @.Input
SELECT *
FROM OPENXML (@.idoc, '/Root/Row',1)
WITH (Size varchar(10),
pieces Int,
sizeID Int,
sizecombID Int)

3. This can be achived Max(id) + 1 but you have to take care the concurrent users issue.

|||

appreicate your reply on this, i have few more questions :-

1. Is xml the only way to acheive this, can't I pass a set of rows directly to stored procedure without using xml.

2. once all the records are available inside stored proc, do i have to loop through all records using cursor or temp table or i can get the results directly through a sql statement

3. I also thought of Max(id) but bcos of concurrency issues, i was hesitant. Can you suggest any other way ? i just want the value unique not necessarily it should be in sequence (i don't want to use guid)

4. Do you think that i should change my table structure to make it as a xml field for sizes instead of multiple rows, just wanted to know some suggestion on that..

5. how do u get these code snippet blocks in your post, these are very nice.

|||

1. No SQL Server wont support parameter as array value - it should be csv or xml

2. You can get the results directly from the SQL, most of the cursor used batches we can rewrite using SQL queries.

3. Then you can have a lookup table to fetch the unique id (SizeCombo Master table with Identity value) insert the new sizecombo on master table then use the @.@.IDENTITY / SCOPE_IDENTITY on your other detailed table.

4. Don't store your data as XML it will be very difficult to maintaine & manupulations , store the data as rows

5. Its there in the forum text editor itself (next to the Smily Icon with green color {})

|||

Hopefully last question..

Regarding answer to point 2, can u pls provide me the sql query which can search and return sizecombID from a table...

Check integrity

Hi,

What methods can we use to check the integrity problems? Some records in my
database are having Foreign Keys but the database doesn't have any related
records with required Primary Keys. I need to scan the whole database and
delete (maybe with backup, maybe not) all wrong records.

Who can I do that? Is it need to write my own application to do that or we
have some standard way to fix these problems? I'm not a database
administrator and don't know these ways (yet). Can somebody help me with
advice?

Thanks.

Dmitri ShvetsovHi

If you have a foreign key constraint enabled for the given relationships
then this should not occur.
If they were created with the NOCHECK option then existing data may violate
the constraint, but if you would not be able to create the FK if you this
was the case and NOCHECK was not specified.

The easiest way to check the integrity is probably to drop and reapply the
FKs. Another alternative would be to use a construct such as

SELECT * FROM Referencing C WHERE NOT EXISTS ( SELECT * FROM Referenced P
WHERE P.PK = C.FK )

But doing this for a large database may take some time.

John

"Dmitri Shvetsov" <dshvetsov@.cox.net> wrote in message
news:Xzlnb.87999$Ms2.54382@.fed1read03...
> Hi,
> What methods can we use to check the integrity problems? Some records in
my
> database are having Foreign Keys but the database doesn't have any related
> records with required Primary Keys. I need to scan the whole database and
> delete (maybe with backup, maybe not) all wrong records.
> Who can I do that? Is it need to write my own application to do that or we
> have some standard way to fix these problems? I'm not a database
> administrator and don't know these ways (yet). Can somebody help me with
> advice?
> Thanks.
> Dmitri Shvetsov|||Hi

If you have a foreign key constraint enabled for the given relationships
then this should not occur.
If they were created with the NOCHECK option then existing data may violate
the constraint, but if you would not be able to create the FK if you this
was the case and NOCHECK was not specified.

The easiest way to check the integrity is probably to drop and reapply the
FKs. Another alternative would be to use a construct such as

SELECT * FROM Referencing C WHERE NOT EXISTS ( SELECT * FROM Referenced P
WHERE P.PK = C.FK )

But doing this for a large database may take some time.

John

"Dmitri Shvetsov" <dshvetsov@.cox.net> wrote in message
news:Xzlnb.87999$Ms2.54382@.fed1read03...
> Hi,
> What methods can we use to check the integrity problems? Some records in
my
> database are having Foreign Keys but the database doesn't have any related
> records with required Primary Keys. I need to scan the whole database and
> delete (maybe with backup, maybe not) all wrong records.
> Who can I do that? Is it need to write my own application to do that or we
> have some standard way to fix these problems? I'm not a database
> administrator and don't know these ways (yet). Can somebody help me with
> advice?
> Thanks.
> Dmitri Shvetsov|||Try using dbcc checkconstraints with all_constraints . It should return all
'bad' data in the database...

MC

"Dmitri Shvetsov" <dshvetsov@.cox.net> wrote in message
news:Xzlnb.87999$Ms2.54382@.fed1read03...
> Hi,
> What methods can we use to check the integrity problems? Some records in
my
> database are having Foreign Keys but the database doesn't have any related
> records with required Primary Keys. I need to scan the whole database and
> delete (maybe with backup, maybe not) all wrong records.
> Who can I do that? Is it need to write my own application to do that or we
> have some standard way to fix these problems? I'm not a database
> administrator and don't know these ways (yet). Can somebody help me with
> advice?
> Thanks.
> Dmitri Shvetsov
>|||Try using dbcc checkconstraints with all_constraints . It should return all
'bad' data in the database...

MC

"Dmitri Shvetsov" <dshvetsov@.cox.net> wrote in message
news:Xzlnb.87999$Ms2.54382@.fed1read03...
> Hi,
> What methods can we use to check the integrity problems? Some records in
my
> database are having Foreign Keys but the database doesn't have any related
> records with required Primary Keys. I need to scan the whole database and
> delete (maybe with backup, maybe not) all wrong records.
> Who can I do that? Is it need to write my own application to do that or we
> have some standard way to fix these problems? I'm not a database
> administrator and don't know these ways (yet). Can somebody help me with
> advice?
> Thanks.
> Dmitri Shvetsov
>

Check for Duplicate Records Before Insertion

Hi,

Does anyone have any sugestions as to the best way to achieve the following?

I want to display a list of products in a GridView. Against each product I want to place a button which will add the product to a "wish list" table.

I need to be able to check that the product has not already been inserted into the wish list.

Either suggested methods or links to tutorials will be appreciated.

Thanks in advance.

Just do a;

SELECT ProductID FROM WishListTable WHERE ProductID = <selected Product ID>

If you don't get an empty result set back you know the product is already there. Or if you are using SQL Server Stored Procedures;

IF NOT EXISTS (SELECT 1 FROM WishListTable WHERE ProductID = <selected Product ID>) INSERT INTO WishListTable ...

|||

Thanks for your answer.

I started searching for a tutorial on this and found one athttp://aspalliance.com/687

I've incorporated your suggestion and the tutorial into my system and it works a treat.

Thanks again

Check and send data with xp_sendmail

How yould i loop trought all the records in a table and fetching a specic record that is flagged and sending for each record found a email with that records data to a mail recipient. this should be part of a step in a sql job. PLZ HELPHad a little problem in understanding your phrases. You may check cursor in BOL for looping actions.|||create proc abc as
declare c1 cursor as select primary key from table name where condition
open c1
while (@.@.fetch_status =0)

begin
exec xp_sendmail ....
fetch next
end

something like this and schedule as job !!!

charts

I've created a stacked bar chart using reporting services. I'm
building the chart from 20 records that include amounts, years, ect.
The problem is whenever there are two amounts that are the same the
chart only shows one of the amounts. I tried to find out if there was
a property that was hiding duplicates with no luck.
thanksYou need to create a group for the chart that will identify the unique
records.
Andy Potter

char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

go

create table table2

(

col2 char(2)

)

go

I add some records to two tables after createing operation completed.

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

-

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.

Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.

If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply

char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

go

create table table2

(

col2 char(2)

)

go

I add some records to two tables after createing operation completed.

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

-

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.

Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.

If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply

Char Trim

Hi,

I imported records from text file using BCP, in one column I am seeing blank space in starting as well in end. I printed the ASCII code it turn out as 32. But when I tried to trim with LTRIM and RTRIM it didn't work. I tried to replace the character using REPLACE witn no avail.

Please help.

Thanks.

You could try the following:

RIGHT(LEFT(ColumnName, LEN(ColumnName) - 1), LEN(ColumnName) - 2)

This will trim the leading and trailing characters.

Is there a chance you could you check the results of the following just to confirm that the ASCII value of the character is 32 ? :

SELECT TOP 1 ASCII(LEFT(ColumnName, 1))
FROM MyTable

Thanks

Chris

|||

I confirmed that the char is 32. I am still seeing the blanks before and after the values.

|||Is the column CHAR or VARCHAR or NVARCHAR?|||

As Dan states, the datatype is interesting here, but if you are using rtrim and ltrim, it shouldn't matter... Please post some sample code like this so we can get an idea of what you are really wanting...

drop table test
go
create table test
(
charValue char(10),
varcharValue varchar(10)
)
go
insert into test
select 'Test' + char(32) ,
'Test' + char(32)
go
select '*' + charValue + '*' as char1,
'*' + ltrim(rtrim(charValue)) + '*' as char2,
'*' + varcharValue + '*' as varchar1,
'*' + ltrim(rtrim(varcharValue)) + '*' as varchar2
from test
go
char1 char2 varchar1 varchar2
*Test * *Test* *Test * *Test*

|||

Louis,

I was surprised to see that your varcharValue included the trailing space. I thought VARCHAR columns removed trailing spaces. So then I went to study the references and found that it depends on ANSI_PADDING in the database.

http://msdn2.microsoft.com/en-us/library/ms175055.aspx

The varchar data type is a variable-length data type. Values shorter than the size of the column are not right-padded to the size of the column. If the ANSI_PADDING option was set to OFF when the column was created, any trailing blanks are truncated from character values stored in the column. If ANSI_PADDING was set ON when the column was created, trailing blanks are not truncated.

Thanks for the education! ;-)

|||Thanks for the added info...|||

Hi,

Thaks for your help.

Column is Varchar(50) also I am seeing following property of column (please note I am using SQLServer 2005 SP1).

TrimTrailingBlank =0

FixedLenNullinSource=0

This column is contains database sizes like 2000 MB

I need to remove the MB thing which I am doing

Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize)))

then I have to convert to Numeric type but when I am trying

Cast(ltrim(rtrim(Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize))))) as Numeric(20,2))

getting error

Msg 8114, Level 16, State 5, Line 3

Error converting data type varchar to numeric.

Thanks

|||

I think the computation for the third parameter of SUBSTRING is not correct. I think you just want "(CharIndex('M',DatabaseSize)-1)".

declare @.DatabaseSize varchar(50)
set @.DatabaseSize = ' 2000 MB '

print ' ' + cast(LEN(@.DatabaseSize) as varchar)
print ' ' + cast(CharIndex('M',@.DatabaseSize) as varchar)

print ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as Numeric(20,2))
print cast(len(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))) as varchar)
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as binary)

Dan

|||

Thanks a lot,

Reason I put Len() function cause we do have some rows where MB was missing so what I did I concanated 'MB' and use ChartIdex -1 and it is working now !!!! All along I was looking at wrong place to resolve the problem.

Cheers.

Changing year in field

Hi all:
I need to change the year to many records that, by mistake, were put with a 2007 date, but they should be still with 2006, can anyone help me ?
I use Sql Server 2000 and the field is of type datetime
Thanks:eek:first make a backup.

then write a select statement for the rows that need to be updated, and make sure it's correct (that is, it contains all the rows that need fixing, and no others):

select * from mytable where <some criteria>

then you can apply an update.

update mytable
set mycol=dateadd(year, -1, mycol)
where <some criteria>