How to change the Identity value

I have a database table and it has a column called ttt_id which has an identity seed. Now the Id value is 2845746 and if I insert the new record the value would be 2845747. But I want to change that to 2865740 instead of 2845747. How can do that?
Thanks.Check out DBCC CHECKIDENT in BOL. It should give you everything you need.|||-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT <TableName> ON
GO

INSERT INTO <TableName> (<FieldName>) VALUES(2845747)
GO

How to capture an error message in T-SQL?

I need to capture an actual error message and place this record into a
log table.
I know that I can get error # from ERROR variable.
Where can I get the text, that consist information aboout objects that
caused problem?
Example
create procedure MyProc as
DECLARE Err int;
DECLARE ErrMsg varchar(256)
DELETE FROM ParentTable where ID=@.P_ID;
set @.Err=ERROR;
if @.Err!=0
begin
set @.ErrMsg=''?
insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
end;
go;
I expect to see in MyLog something like `Foreign key violation, child
record found, constraint "FK_ChildTable"`,
or "No permission to delete for table "ParentTable"`
or whatever I would see if I execute this TSQL in SQL Analyzer manually.
BTW, are all of such errors being written into some standard MSSQL log?
Is it SysLog or where?
Thanks,
MarkMark
Unfortunatly you cannot capture such kind of errors in current version.I
know with Yukon will introduce error handle like try--catch but cannot give
more details at this time.
On other hand look at my example helps you to solve the problem (Modify it
for your needs)
create table parent
(
col int not null primary key
)
go
insert into parent values (1)
go
create table child
(
col int not null primary key,
col1 int not null --references parent (col)
)
go
insert into child(col,col1) values (1,1)
insert into child(col,col1) values (2,1)
go
create trigger my_tr on parent for delete
as
if exists ( select * from child c join deleted d on c.col1=d.col)
raiserror ('Cannot delete',16,1)
go
delete parent where col=1
go
drop table child
drop table parent
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>|||> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot
give
> more details at this time.
See http://msdn.microsoft.com/msdnmag/i...on/default.aspx
for new Yukon enhancements in error handling with the TRY/CATCH construct
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
************************************
SQL Source Control 2003 - for
SQL Server Source Safe integration
and custom databases documentation|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot gi
ve
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>
>
>|||Sebastian, thanks,
It is good that Yukon can handle exceptions by TRY/CATCH. However I have
not found any Error Message Text capturing in examples. The error
messages are coded into the procedure, and are not actual messages from
Server. That unfortunate because in many cases the sole error code is
not enough, the object names associated with exception would be very
helpfull for further troubleshooting.
Mark
Sebastian K. Zaklada wrote:
> give
>
>
> See http://msdn.microsoft.com/msdnmag/i...ledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ************************************
> SQL Source Control 2003 - for
> SQL Server Source Safe integration
> and custom databases documentation
>|||Mark,

> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information about objects that
> caused problem?
You can't at the TSQL level.

> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
It depends whether the error is configured to. Check out the manage errors
dialog in EM and you'll see that checkbox. "Soft" errors are not written to
logs by default.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>

How to capture an error message in T-SQL?

I need to capture an actual error message and place this record into a
log table.
I know that I can get error # from ERROR variable.
Where can I get the text, that consist information aboout objects that
caused problem?
Example
create procedure MyProc as
DECLARE Err int;
DECLARE ErrMsg varchar(256)
DELETE FROM ParentTable where ID=@.P_ID;
set @.Err=ERROR;
if @.Err!=0
begin
set @.ErrMsg=''?
insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
end;
go;
I expect to see in MyLog something like `Foreign key violation, child
record found, constraint "FK_ChildTable"`,
or "No permission to delete for table "ParentTable"`
or whatever I would see if I execute this TSQL in SQL Analyzer manually.
BTW, are all of such errors being written into some standard MSSQL log?
Is it SysLog or where?
Thanks,
MarkMark
Unfortunatly you cannot capture such kind of errors in current version.I
know with Yukon will introduce error handle like try--catch but cannot give
more details at this time.
On other hand look at my example helps you to solve the problem (Modify it
for your needs)
create table parent
(
col int not null primary key
)
go
insert into parent values (1)
go
create table child
(
col int not null primary key,
col1 int not null --references parent (col)
)
go
insert into child(col,col1) values (1,1)
insert into child(col,col1) values (2,1)
go
create trigger my_tr on parent for delete
as
if exists ( select * from child c join deleted d on c.col1=d.col)
raiserror ('Cannot delete',16,1)
go
delete parent where col=1
go
drop table child
drop table parent
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>|||> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot
give
> more details at this time.
See http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx
for new Yukon enhancements in error handling with the TRY/CATCH construct
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.
************************************
SQL Source Control 2003 - for
SQL Server Source Safe integration
and custom databases documentation|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Uri, thank you.
I wrote FK code just for example.
Question was about error handling and capturing of error message text.
Mark
Uri Dimant wrote:
> Mark
> Unfortunatly you cannot capture such kind of errors in current version.I
> know with Yukon will introduce error handle like try--catch but cannot give
> more details at this time.
> On other hand look at my example helps you to solve the problem (Modify it
> for your needs)
> create table parent
> (
> col int not null primary key
> )
> go
> insert into parent values (1)
> go
> create table child
> (
> col int not null primary key,
> col1 int not null --references parent (col)
> )
> go
> insert into child(col,col1) values (1,1)
> insert into child(col,col1) values (2,1)
> go
> create trigger my_tr on parent for delete
> as
> if exists ( select * from child c join deleted d on c.col1=d.col)
> raiserror ('Cannot delete',16,1)
> go
> delete parent where col=1
> go
> drop table child
> drop table parent
>
>
>
> "Mark Malakanov" <markmal@.rogers.com> wrote in message
> news:402674CD.4080101@.rogers.com...
>>I need to capture an actual error message and place this record into a
>>log table.
>>I know that I can get error # from ERROR variable.
>>Where can I get the text, that consist information aboout objects that
>>caused problem?
>>Example
>>create procedure MyProc as
>>DECLARE Err int;
>>DECLARE ErrMsg varchar(256)
>>DELETE FROM ParentTable where ID=@.P_ID;
>>set @.Err=ERROR;
>>if @.Err!=0
>> begin
>> set @.ErrMsg=''?
>> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
>> end;
>>go;
>>I expect to see in MyLog something like `Foreign key violation, child
>>record found, constraint "FK_ChildTable"`,
>>or "No permission to delete for table "ParentTable"`
>>or whatever I would see if I execute this TSQL in SQL Analyzer manually.
>>BTW, are all of such errors being written into some standard MSSQL log?
>>Is it SysLog or where?
>>Thanks,
>>Mark
>
>|||Sebastian, thanks,
It is good that Yukon can handle exceptions by TRY/CATCH. However I have
not found any Error Message Text capturing in examples. The error
messages are coded into the procedure, and are not actual messages from
Server. That unfortunate because in many cases the sole error code is
not enough, the object names associated with exception would be very
helpfull for further troubleshooting.
Mark
Sebastian K. Zaklada wrote:
>>Unfortunatly you cannot capture such kind of errors in current version.I
>>know with Yukon will introduce error handle like try--catch but cannot
> give
>>more details at this time.
>
> See http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx
> for new Yukon enhancements in error handling with the TRY/CATCH construct
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ************************************
> SQL Source Control 2003 - for
> SQL Server Source Safe integration
> and custom databases documentation
>|||Mark,
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information about objects that
> caused problem?
You can't at the TSQL level.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
It depends whether the error is configured to. Check out the manage errors
dialog in EM and you'll see that checkbox. "Soft" errors are not written to
logs by default.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark Malakanov" <markmal@.rogers.com> wrote in message
news:402674CD.4080101@.rogers.com...
> I need to capture an actual error message and place this record into a
> log table.
> I know that I can get error # from ERROR variable.
> Where can I get the text, that consist information aboout objects that
> caused problem?
> Example
> create procedure MyProc as
> DECLARE Err int;
> DECLARE ErrMsg varchar(256)
> DELETE FROM ParentTable where ID=@.P_ID;
> set @.Err=ERROR;
> if @.Err!=0
> begin
> set @.ErrMsg=''?
> insert into MyLog values (getdate(),'MyProc',@.Err,@.ErrMsg);
> end;
> go;
> I expect to see in MyLog something like `Foreign key violation, child
> record found, constraint "FK_ChildTable"`,
> or "No permission to delete for table "ParentTable"`
> or whatever I would see if I execute this TSQL in SQL Analyzer manually.
> BTW, are all of such errors being written into some standard MSSQL log?
> Is it SysLog or where?
> Thanks,
> Mark
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
>> will record size be diferent if I save in each field value 0 versus if I
>> save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>>I should add that I am using SQL SErver 2000.
>> Thanks
>> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
>> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int.
>> Now, will record size be diferent if I save in each field value 0 versus
>> if I save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>>
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for help
I should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>
|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>
|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>

How to calculate a value which takes the prev rows value for calculation.

Hi All,

Please help me with this problem
I have two fields in my report- Production hours and Scheduled Start Time.

For the first record in the report the scheduled start time field will have the current date time field value ,but for consecutive records it is the sum of Prod-hrs+scheduled start time (of the Previous record).

I tried the following formula

if OnFirstRecord then
{@.CurrDateTime}
else
Previous({@.Schd_Start_Time})+{dpRptCSReport.prod_hrs}

but get the error ' A formula cannot refer to itself, either directly or indirectly'
Can you please suggest a way out??

Thanks in advance
RashmiHi All,

Please help me with this problem
I have two fields in my report- Production hours and Scheduled Start Time.

For the first record in the report the scheduled start time field will have the current date time field value ,but for consecutive records it is the sum of Prod-hrs+scheduled start time (of the Previous record).

I tried the following formula

if OnFirstRecord then
{@.CurrDateTime}
else
Previous({@.Schd_Start_Time})+{dpRptCSReport.prod_hrs}

but get the error ' A formula cannot refer to itself, either directly or indirectly'
Can you please suggest a way out??

Thanks in advance
Rashmi

Please can anyone suggest a solution? Please can you help Madhi?|||If OnFirstRecord = true Then ({@.CurrDateTime}; Global NumberVar Test :=
{@.Schd_Start_Time})+{dpRptCSReport.prod_hrs})
Else Previous({Test});
Global NumberVar Test :=
{@.Schd_Start_Time})+{dpRptCSReport.prod_hrs});|||this formula will help you
change currentdatetime with ur need.

global datetimevar z;
if OnFirstRecord then
(
currentdatetime;
z:=currentdatetime;
)
else
(
z+10;
z:=z+10
)

How to build an SQL-string from a record

Hi,
I need a solution for this in SQL Server, or in VB.NET, so for this reason I
posted it to the 2 newsgroups.
I need to build an SQL String from a given record.
For exemple: I have in my table tblMyCows this record:
CowID: 1
CowName: Bella (a typical Belgian cowname)
CowGender: Female
I should have something that generates me the Insert-statement for it: like
this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella',
'Female')".
If possible the same with an update an delete statement, and it would be
really nice if it could detect itself the primary keys, and use them for for
the update and Delete statements.
Anybody any idea?
Thanks a lot in advance,
PieterPieter,
Do you now need Bella because you was yesterday to much involved with
Stella.
I count 4 newsgroups, not 2.
However your problem sounds not difficult for me, what I not direct see, is
if the tblMycows is a datatable or that it is a table in a database?
Cor|||Hi Pieter,
maybe this can be of help (haven't tested it yet)
http://vyaskn.tripod.com/code.htm#inserts
btw I would rather be involved with Stella than with Heineken ;-)
hth Peter
"Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hehe it was Jupiler :-)
And tblMyCows is a table in a Database.
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hm thanks, it seems really nice, and just the thing I was looking for :-)
"Peter Proost" <pproost@.nospam.hotmail.com> wrote in message
news:OnQUGwZSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Hi Pieter,
> maybe this can be of help (haven't tested it yet)
> http://vyaskn.tripod.com/code.htm#inserts
> btw I would rather be involved with Stella than with Heineken ;-)
> hth Peter
>
>
> "Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
> news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> > Pieter,
> >
> > Do you now need Bella because you was yesterday to much involved with
> > Stella.
> > I count 4 newsgroups, not 2.
> >
> > However your problem sounds not difficult for me, what I not direct see,
> is
> > if the tblMycows is a datatable or that it is a table in a database?
> >
> > Cor
> >
> >
>|||Pieter,
> Hehe it was Jupiler :-)
You don't believe it, that was what I wrote first.
However Bella and Stella did sound better.
:-)
I have no sample at hand I will try to make it (I don't promish I succeed),
than probably I show it tomorrow. (I have the other way around).
Cor|||Pieter,
Wrong answer (not the jupiler), I have that part from the sample from which
in my opinion you should be able to do the rest yourself, when not, than
reply.
Have a look in this message.
http://groups-beta.google.com/group/microsoft.public.dotnet.languages.vb/msg/470d93378c5467f8?hl=en
Cor

How to build an SQL-string from a record

Hi,
I need a solution for this in SQL Server, or in VB.NET, so for this reason I
posted it to the 2 newsgroups.
I need to build an SQL String from a given record.
For exemple: I have in my table tblMyCows this record:
CowID: 1
CowName: Bella (a typical Belgian cowname)
CowGender: Female
I should have something that generates me the Insert-statement for it: like
this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella',
'Female')".
If possible the same with an update an delete statement, and it would be
really nice if it could detect itself the primary keys, and use them for for
the update and Delete statements.
Anybody any idea?
Thanks a lot in advance,
PieterPieter,
Do you now need Bella because you was yesterday to much involved with
Stella.
I count 4 newsgroups, not 2.
However your problem sounds not difficult for me, what I not direct see, is
if the tblMycows is a datatable or that it is a table in a database?
Cor|||Hi Pieter,
maybe this can be of help (haven't tested it yet)
http://vyaskn.tripod.com/code.htm#inserts
btw I would rather be involved with Stella than with Heineken ;-)
hth Peter
"Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hehe it was Jupiler :-)
And tblMyCows is a table in a Database.
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>|||Hm thanks, it seems really nice, and just the thing I was looking for :-)
"Peter Proost" <pproost@.nospam.hotmail.com> wrote in message
news:OnQUGwZSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Hi Pieter,
> maybe this can be of help (haven't tested it yet)
> http://vyaskn.tripod.com/code.htm#inserts
> btw I would rather be involved with Stella than with Heineken ;-)
> hth Peter
>
>
> "Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
> news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> is
>|||Pieter,

> Hehe it was Jupiler :-)
You don't believe it, that was what I wrote first.
However Bella and Stella did sound better.
:-)
I have no sample at hand I will try to make it (I don't promish I succeed),
than probably I show it tomorrow. (I have the other way around).
Cor|||Pieter,
Wrong answer (not the jupiler), I have that part from the sample from which
in my opinion you should be able to do the rest yourself, when not, than
reply.
Have a look in this message.
3378c5467f8?hl=en" target="_blank">http://groups-beta.google.com/group...78c5467f8?hl=en
Cor

How to build an SQL-string from a record

Hi,
I need a solution for this in SQL Server, or in VB.NET, so for this reason I
posted it to the 2 newsgroups.
I need to build an SQL String from a given record.
For exemple: I have in my table tblMyCows this record:
CowID: 1
CowName: Bella (a typical Belgian cowname)
CowGender: Female
I should have something that generates me the Insert-statement for it: like
this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella',
'Female')".
If possible the same with an update an delete statement, and it would be
really nice if it could detect itself the primary keys, and use them for for
the update and Delete statements.
Anybody any idea?
Thanks a lot in advance,
Pieter
Pieter,
Do you now need Bella because you was yesterday to much involved with
Stella.
I count 4 newsgroups, not 2.
However your problem sounds not difficult for me, what I not direct see, is
if the tblMycows is a datatable or that it is a table in a database?
Cor
|||Hi Pieter,
maybe this can be of help (haven't tested it yet)
http://vyaskn.tripod.com/code.htm#inserts
btw I would rather be involved with Stella than with Heineken ;-)
hth Peter
"Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>
|||Hehe it was Jupiler :-)
And tblMyCows is a table in a Database.
"Cor Ligthert" <notmyfirstname@.planet.nl> wrote in message
news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> Pieter,
> Do you now need Bella because you was yesterday to much involved with
> Stella.
> I count 4 newsgroups, not 2.
> However your problem sounds not difficult for me, what I not direct see,
is
> if the tblMycows is a datatable or that it is a table in a database?
> Cor
>
|||Hm thanks, it seems really nice, and just the thing I was looking for :-)
"Peter Proost" <pproost@.nospam.hotmail.com> wrote in message
news:OnQUGwZSFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Hi Pieter,
> maybe this can be of help (haven't tested it yet)
> http://vyaskn.tripod.com/code.htm#inserts
> btw I would rather be involved with Stella than with Heineken ;-)
> hth Peter
>
>
> "Cor Ligthert" <notmyfirstname@.planet.nl> schreef in bericht
> news:O4vtTtZSFHA.3052@.TK2MSFTNGP09.phx.gbl...
> is
>
|||Pieter,

> Hehe it was Jupiler :-)
You don't believe it, that was what I wrote first.
However Bella and Stella did sound better.
:-)
I have no sample at hand I will try to make it (I don't promish I succeed),
than probably I show it tomorrow. (I have the other way around).
Cor
|||Pieter,
Wrong answer (not the jupiler), I have that part from the sample from which
in my opinion you should be able to do the rest yourself, when not, than
reply.
Have a look in this message.
http://groups-beta.google.com/group/...8c5467f8?hl=en
Cor