How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a numbe
r
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point an
d
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before addin
g
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
KeenerHi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
----
----
----
----
--
---
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
----
----
----
----
-- -- -- --
-- --
--
----
----
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
----
----
---
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
----
----
No rowguidcol column defined.
Data_located_on_filegroup
----
----
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:

> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database
.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "W
e
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will
be
> permitted to be added. For example, if I wish to add a field to CRM as te
xt
> and set it to have a limit of 6K, then CRM will only allow me to add a num
ber
> of fields adding up to 2K. The thing is also is that CRM will not allow y
ou
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point
and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before add
ing
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKI
NG
> CRM?
> Thanks for any help.
> Regards,
> Keener|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create a row that is greater than than 8060 characters, but you wi
ll
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDA
TE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ----
---
> ----
---
> --
> ---
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length P
rec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ----
---
> ----
---
> -- -- -- --
> -- --
> --
> ----
---
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replicatio
n
> ----
---
> ---
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ----
---
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ----
---
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
>|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:

> Keener wrote:
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>sql

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a number
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point and
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before adding
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
KeenerHi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
------
------
--
---
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
--
------
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
---
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "We
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will be
> permitted to be added. For example, if I wish to add a field to CRM as text
> and set it to have a limit of 6K, then CRM will only allow me to add a number
> of fields adding up to 2K. The thing is also is that CRM will not allow you
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before adding
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKING
> CRM?
> Thanks for any help.
> Regards,
> Keener|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
> Hi
> You can create a row that is greater than than 8060 characters, but you will
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ------
> ------
> --
> ---
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ------
> ------
> -- -- -- --
> -- --
> --
> ------
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replication
> ------
> ---
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ------
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ------
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
> > I have a question. A bit new to SQL Server, but how does one
> > determine the row size of a given table. Here is my issue:
> >
> > I am a CRM user and I understand that CRM uses SQL Server for its database.
> >
> > When using SQL Server on its own, it will only allow ~8k of DATA to enter
> > the database and will truncate the rest (from my understanding anyway).
> > However, CRM has set its own restrictions on the database and says that "We
> > will not allow
> > any data that will exceed SQL Server's limits of ~8k and therefore we will
> > calculate
> > the size of the fields in the table and determine if anymore columns will be
> > permitted to be added. For example, if I wish to add a field to CRM as text
> > and set it to have a limit of 6K, then CRM will only allow me to add a number
> > of fields adding up to 2K. The thing is also is that CRM will not allow you
> > to DELETE or reconfigure any columns, therefore, if I said to make the new
> > field to be 1K, it will not allow me. Basically CRM breaks at this point and
> > I can no longer add ANYTHING to this table, but I need to.
> >
> > Bearing this in mind, I wish to check and see what the table row size is
> > before hand so I can allocate certain fields more appropriately before adding
> > them to know the size of each field. Is there an store procedure to get
> > these values from SQL Server, so I can know if I am on the brink of BREAKING
> > CRM?
> >
> > Thanks for any help.
> >
> > Regards,
> > Keener|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:
> Keener wrote:
> > I have a question. A bit new to SQL Server, but how does one
> > determine the row size of a given table. Here is my issue:
> >
> > I am a CRM user and I understand that CRM uses SQL Server for its
> > database.
> >
> > When using SQL Server on its own, it will only allow ~8k of DATA to
> > enter the database and will truncate the rest (from my understanding
> > anyway). However, CRM has set its own restrictions on the database
> > and says that "We will not allow
> > any data that will exceed SQL Server's limits of ~8k and therefore we
> > will calculate
> > the size of the fields in the table and determine if anymore columns
> > will be permitted to be added. For example, if I wish to add a field
> > to CRM as text and set it to have a limit of 6K, then CRM will only
> > allow me to add a number of fields adding up to 2K. The thing is
> > also is that CRM will not allow you to DELETE or reconfigure any
> > columns, therefore, if I said to make the new field to be 1K, it will
> > not allow me. Basically CRM breaks at this point and I can no longer
> > add ANYTHING to this table, but I need to.
> >
> > Bearing this in mind, I wish to check and see what the table row size
> > is before hand so I can allocate certain fields more appropriately
> > before adding them to know the size of each field. Is there an store
> > procedure to get these values from SQL Server, so I can know if I am
> > on the brink of BREAKING CRM?
> >
> > Thanks for any help.
> >
> > Regards,
> > Keener
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

How to determine what the row size is.

I have a question. A bit new to SQL Server, but how does one
determine the row size of a given table. Here is my issue:
I am a CRM user and I understand that CRM uses SQL Server for its database.
When using SQL Server on its own, it will only allow ~8k of DATA to enter
the database and will truncate the rest (from my understanding anyway).
However, CRM has set its own restrictions on the database and says that "We
will not allow
any data that will exceed SQL Server's limits of ~8k and therefore we will
calculate
the size of the fields in the table and determine if anymore columns will be
permitted to be added. For example, if I wish to add a field to CRM as text
and set it to have a limit of 6K, then CRM will only allow me to add a number
of fields adding up to 2K. The thing is also is that CRM will not allow you
to DELETE or reconfigure any columns, therefore, if I said to make the new
field to be 1K, it will not allow me. Basically CRM breaks at this point and
I can no longer add ANYTHING to this table, but I need to.
Bearing this in mind, I wish to check and see what the table row size is
before hand so I can allocate certain fields more appropriately before adding
them to know the size of each field. Is there an store procedure to get
these values from SQL Server, so I can know if I am on the brink of BREAKING
CRM?
Thanks for any help.
Regards,
Keener
Hi
You can create a row that is greater than than 8060 characters, but you will
get an error when you insert data greater than that value. For example
(formatting may get messed up!):
create table mytab ( col1 varchar(8000), col2 varchar(8000) )
-- Warning: The table 'mytab' has been created but its maximum row size
(16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 16013 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
-- There is a certain amount of overhead!!!!
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
--Server: Msg 511, Level 16, State 1, Line 1
--Cannot create a row of size 8073 which is greater than the allowable
maximum of 8060.
--The statement has been terminated.
INSERT INTO mytab ( col1, col2 )
SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
To get column information try sp_help
sp_help Mytab
/*
Name
Owner
Type Created_datetime
------
------
mytab
dbo
user table 2005-05-31
18:23:48.873
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
------
col1
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
col2
varchar
no 8000
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
--- --
No identity column defined.
NULL
NULL NULL
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
*/
HTH
John
"Keener" wrote:

> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its database.
> When using SQL Server on its own, it will only allow ~8k of DATA to enter
> the database and will truncate the rest (from my understanding anyway).
> However, CRM has set its own restrictions on the database and says that "We
> will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we will
> calculate
> the size of the fields in the table and determine if anymore columns will be
> permitted to be added. For example, if I wish to add a field to CRM as text
> and set it to have a limit of 6K, then CRM will only allow me to add a number
> of fields adding up to 2K. The thing is also is that CRM will not allow you
> to DELETE or reconfigure any columns, therefore, if I said to make the new
> field to be 1K, it will not allow me. Basically CRM breaks at this point and
> I can no longer add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size is
> before hand so I can allocate certain fields more appropriately before adding
> them to know the size of each field. Is there an store procedure to get
> these values from SQL Server, so I can know if I am on the brink of BREAKING
> CRM?
> Thanks for any help.
> Regards,
> Keener
|||Keener wrote:
> I have a question. A bit new to SQL Server, but how does one
> determine the row size of a given table. Here is my issue:
> I am a CRM user and I understand that CRM uses SQL Server for its
> database.
> When using SQL Server on its own, it will only allow ~8k of DATA to
> enter the database and will truncate the rest (from my understanding
> anyway). However, CRM has set its own restrictions on the database
> and says that "We will not allow
> any data that will exceed SQL Server's limits of ~8k and therefore we
> will calculate
> the size of the fields in the table and determine if anymore columns
> will be permitted to be added. For example, if I wish to add a field
> to CRM as text and set it to have a limit of 6K, then CRM will only
> allow me to add a number of fields adding up to 2K. The thing is
> also is that CRM will not allow you to DELETE or reconfigure any
> columns, therefore, if I said to make the new field to be 1K, it will
> not allow me. Basically CRM breaks at this point and I can no longer
> add ANYTHING to this table, but I need to.
> Bearing this in mind, I wish to check and see what the table row size
> is before hand so I can allocate certain fields more appropriately
> before adding them to know the size of each field. Is there an store
> procedure to get these values from SQL Server, so I can know if I am
> on the brink of BREAKING CRM?
> Thanks for any help.
> Regards,
> Keener
You do not have to deal with the max row size (to a degree) if you use
TEXT/NTEXT/IMAGE data types. With those column data types, only a
16-byte pointer is stored in the row. If you are still worried a table
may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
somewhat accurate, measure of row size.
For example:
Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
for a more accurate measure that avoid varchar/nvarchar/varbinary
calculation issues, see "Estimating the Size of a Table" in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||John -- YOU DA MAN... DA MVP MAN!!!!!
I have to do a bit of math, but wow, that was what
I needed.
Thanks a bunch,
Keener
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create a row that is greater than than 8060 characters, but you will
> get an error when you insert data greater than that value. For example
> (formatting may get messed up!):
> create table mytab ( col1 varchar(8000), col2 varchar(8000) )
> -- Warning: The table 'mytab' has been created but its maximum row size
> (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 8000)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 16013 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> -- There is a certain amount of overhead!!!!
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 60)
> --Server: Msg 511, Level 16, State 1, Line 1
> --Cannot create a row of size 8073 which is greater than the allowable
> maximum of 8060.
> --The statement has been terminated.
> INSERT INTO mytab ( col1, col2 )
> SELECT REPLICATE ( 'A', 8000), REPLICATE ( 'A', 47)
> To get column information try sp_help
> sp_help Mytab
> /*
> Name
> Owner
> Type Created_datetime
> ------
> ------
> --
> mytab
> dbo
> user table 2005-05-31
> 18:23:48.873
>
> Column_name
> Type
> Computed Length Prec
> Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
>
> ------
> ------
> -- -- -- --
> -- --
> --
> ------
> col1
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
> col2
> varchar
> no 8000
> yes no
> no SQL_Latin1_General_CP1_CI_AS
>
> Identity
> Seed
> Increment Not For Replication
> ------
> --- --
> No identity column defined.
> NULL
> NULL NULL
>
> RowGuidCol
> ------
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> ------
> PRIMARY
>
> The object does not have any indexes.
> No constraints have been defined for this object.
> No foreign keys reference this table.
> No views with schema binding reference this table.
> */
> HTH
> John
> "Keener" wrote:
|||Thanks for the reply David.
I understand what you mean, but I need to know how
much the table is allocated for each row, not the actual size
of the data in the rows. I will use your method if/when I run
into the issues of managing data row sizes.
Thanks again,
Keener
"David Gugick" wrote:

> Keener wrote:
> You do not have to deal with the max row size (to a degree) if you use
> TEXT/NTEXT/IMAGE data types. With those column data types, only a
> 16-byte pointer is stored in the row. If you are still worried a table
> may break the 8060 byte row limit, you can use DATALENGTH() for a quick,
> somewhat accurate, measure of row size.
> For example:
> Select AVG(DATALENGTH(col1) + DATALENGTH(col2) + ...) from TableName
> for a more accurate measure that avoid varchar/nvarchar/varbinary
> calculation issues, see "Estimating the Size of a Table" in BOL.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

How to determine length of table row in sql 2005

How, other than by doing the addition on my own, can I detemrine the length of a row in a table?

TIA,

barkingdog

Do you mean the actual row (as of the inserted data) or by the defined data types and lengths.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

I mean "by the defined data types and lengths"

TIA,

Barkingdog

how to determine a cursor?

Can you elaborate a bit more? Are you trying to update every row in the
table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:TFOng.17571$sM4.70290@.weber.videotron.net...
I have this procedure that executes only once. How can I do so it does
update every lines in the database?
Thanks
declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')I have this procedure that executes only once. How can I do so it does
update every lines in the database?
Thanks
declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')|||Can you elaborate a bit more? Are you trying to update every row in the
table?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
news:TFOng.17571$sM4.70290@.weber.videotron.net...
I have this procedure that executes only once. How can I do so it does
update every lines in the database?
Thanks
declare @.tlongtextvar as varchar (8000),
@.tspecK as int
select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
@.tspecK = dbo.tLongTxt.k
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
UPDATE dbo.tLongTxt
SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
'</EntityDescription>'
FROM dbo.rTable INNER JOIN dbo.tLongTxt
ON dbo.rTable.K = dbo.tLongTxt.tSpecK
WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
(dbo.tLongTxt.tSpecConc = 22500) AND
(dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
(dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
(dbo.tLongTxt.K = @.tspecK)
AND (
dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
'%>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND dbo.tLongTxt.tLongTxt
not like '%</EDWLayerName>%' AND
dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND dbo.tLongTxt.tLongTxt
not like '%</MappingSource>%' AND
dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
(Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area Based
On IBF (Req)>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')|||I'm trying to update every row in one table with the appropriate contextual
update
Thanks
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> a crit dans le message de
news: OAcHH8QmGHA.3468@.TK2MSFTNGP03.phx.gbl...
> Can you elaborate a bit more? Are you trying to update every row in the
> table?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
> news:TFOng.17571$sM4.70290@.weber.videotron.net...
> I have this procedure that executes only once. How can I do so it does
> update every lines in the database?
> Thanks
>
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>|||I'm trying to update every row in one table with the appropriate contextual
update
Thanks
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> a crit dans le message de
news: OAcHH8QmGHA.3468@.TK2MSFTNGP03.phx.gbl...
> Can you elaborate a bit more? Are you trying to update every row in the
> table?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Fernand St-Georges" <Fernand St-Georges@.videotron.ca> wrote in message
> news:TFOng.17571$sM4.70290@.weber.videotron.net...
> I have this procedure that executes only once. How can I do so it does
> update every lines in the database?
> Thanks
>
> declare @.tlongtextvar as varchar (8000),
> @.tspecK as int
> select @.tlongtextvar = dbo.tLongTxt.tLongTxt,
> @.tspecK = dbo.tLongTxt.k
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>
> UPDATE dbo.tLongTxt
> SET dbo.tLongTxt.tLongTxt = '<EntityDescription>' + @.tlongtextvar +
> '</EntityDescription>'
> FROM dbo.rTable INNER JOIN dbo.tLongTxt
> ON dbo.rTable.K = dbo.tLongTxt.tSpecK
> WHERE (dbo.rTable.zStatus = 1) AND (dbo.rTable.zTransNo = 0) AND
> (dbo.tLongTxt.tSpecConc = 22500) AND
> (dbo.tLongTxt.zStatus = 1 OR dbo.tLongTxt.zStatus IS NULL) AND
> (dbo.tLongTxt.zTransNo = 0 OR dbo.tLongTxt.zTransNo IS NULL) AND
> (dbo.tLongTxt.K = @.tspecK)
> AND (
> dbo.tLongTxt.tLongTxt not like '%<%' AND dbo.tLongTxt.tLongTxt not like
> '%>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessPurpose>%' AND
> dbo.tLongTxt.tLongTxt not like '%<DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%</DataArchitectureFrameworkParent>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityAliasName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityTransformationNotes>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</EDWLayerName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<MappingSource>%' AND
> dbo.tLongTxt.tLongTxt
> not like '%</MappingSource>%' AND
> dbo.tLongTxt.tLongTxt not like '%<Entity''s Subject Area Based On IBF
> (Req)>%' AND dbo.tLongTxt.tLongTxt not like '%</Entity''s Subject Area
> Based
> On IBF (Req)>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityStandardAbbreviatedName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityDefinitionReuseIndicator>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofOrigin>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityRecordofReference>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateName>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityLifecycleStateDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%</EntityBusinessRuleDescription>%' AND
> dbo.tLongTxt.tLongTxt not like '%<SelectionCriteria>%' AND
> dbo.tLongTxt.tLongTxt not like '%</SelectionCriteria>%')
>
>

How to detect that a sqlserver table row has foreign keys and cannot be deleted?

What is the best way to detect that a sqlserver table row has foreign keys and cannot be deleted?

Thanks,

Keith

sp_table_constraints_rowset 'yourTableName'

How to detect IDENTITY_INSERT ON

I have an INSTEAD OF INSERT trigger on a table with an identity column. When
I insert the actual row in the trigger, I need to know if IDENTITY_INSERT has
been set for the table in order to issue the correct INSERT statement. Is
there a function that tells me if IDENTITY_INSERT is currently ON for a table?
Thanks,
Tom
Hi Tommy,
SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
Replace table with your table name.
Thanks
Yogish
|||That will only show whether the table *has* an identity column, not whether IDENTITY_INSERT is
turned on or not. AFAIK, this information is not exposed. I tried DBCC USEROPTIONS, but that doesn't
expose the information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:B091381A-3D58-4512-A364-754A4E0B8BD9@.microsoft.com...
> Hi Tommy,
> SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
> Replace table with your table name.
> --
> Thanks
> Yogish
|||Hi Tibor,
Yeah, you are right. I realised it after posting the message. And DBCC
USEROPTIONS doesn't give this option.
Thanks
Yogish
|||Hi Tommy,
Check out the remarks from BOL.
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a SET
IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
Server? returns an error message that states SET IDENTITY_INSERT is already
ON and reports the table it is set ON for.
Run the following...
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
SET IDENTITY_INSERT products_new ON
On the second statement,
IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
SET operation for table 'products_new'.
I hope this will answer your question in an indirect way.
Thanks
Yogish
|||It's true that I'll get an error if I try to set IDENTITY_INSERT on for
another table, but while I can capture the error code, I can't capture the
error message. So I know that IDENTITY_INSERT is on for another table, but I
don't know which table.
The closest solution I've found is to query the INSERTED pseudo-table. If
IDENTITY_INSERT is off, then the identity value will be zero for every row in
INSERTED. If IDENTITY_INSERT is on, then INSERTED will have other values,
unless the triggering statement is explicitly inserting zeroes.
"Yogish" wrote:

> Hi Tommy,
> Check out the remarks from BOL.
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a SET
> IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
> Server? returns an error message that states SET IDENTITY_INSERT is already
> ON and reports the table it is set ON for.
> Run the following...
> CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> SET IDENTITY_INSERT products ON
> GO
> SET IDENTITY_INSERT products_new ON
> On the second statement,
> IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
> SET operation for table 'products_new'.
> I hope this will answer your question in an indirect way.
> --
> Thanks
> Yogish
>

How to detect IDENTITY_INSERT ON

I have an INSTEAD OF INSERT trigger on a table with an identity column. Whe
n
I insert the actual row in the trigger, I need to know if IDENTITY_INSERT ha
s
been set for the table in order to issue the correct INSERT statement. Is
there a function that tells me if IDENTITY_INSERT is currently ON for a tabl
e?
Thanks,
TomHi Tommy,
SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
Replace table with your table name.
Thanks
Yogish|||That will only show whether the table *has* an identity column, not whether
IDENTITY_INSERT is
turned on or not. AFAIK, this information is not exposed. I tried DBCC USERO
PTIONS, but that doesn't
expose the information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:B091381A-3D58-4512-A364-754A4E0B8BD9@.microsoft.com...
> Hi Tommy,
> SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
> Replace table with your table name.
> --
> Thanks
> Yogish|||Hi Tibor,
Yeah, you are right. I realised it after posting the message. And DBCC
USEROPTIONS doesn't give this option.
Thanks
Yogish|||Hi Tommy,
Check out the remarks from BOL.
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a SE
T
IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
Server? returns an error message that states SET IDENTITY_INSERT is alread
y
ON and reports the table it is set ON for.
Run the following...
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
SET IDENTITY_INSERT products_new ON
On the second statement,
IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
SET operation for table 'products_new'.
I hope this will answer your question in an indirect way.
Thanks
Yogish|||It's true that I'll get an error if I try to set IDENTITY_INSERT on for
another table, but while I can capture the error code, I can't capture the
error message. So I know that IDENTITY_INSERT is on for another table, but
I
don't know which table.
The closest solution I've found is to query the INSERTED pseudo-table. If
IDENTITY_INSERT is off, then the identity value will be zero for every row i
n
INSERTED. If IDENTITY_INSERT is on, then INSERTED will have other values,
unless the triggering statement is explicitly inserting zeroes.
"Yogish" wrote:

> Hi Tommy,
> Check out the remarks from BOL.
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a
SET
> IDENTITY_INSERT ON statement is issued for another table, Microsoft? SQL
> Server? returns an error message that states SET IDENTITY_INSERT is alre
ady
> ON and reports the table it is set ON for.
> Run the following...
> CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40
))
> GO
> SET IDENTITY_INSERT products ON
> GO
> SET IDENTITY_INSERT products_new ON
> On the second statement,
> IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perfor
m
> SET operation for table 'products_new'.
> I hope this will answer your question in an indirect way.
> --
> Thanks
> Yogish
>

How to detect IDENTITY_INSERT ON

I have an INSTEAD OF INSERT trigger on a table with an identity column. When
I insert the actual row in the trigger, I need to know if IDENTITY_INSERT has
been set for the table in order to issue the correct INSERT statement. Is
there a function that tells me if IDENTITY_INSERT is currently ON for a table?
Thanks,
TomHi Tommy,
SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
Replace table with your table name.
--
Thanks
Yogish|||That will only show whether the table *has* an identity column, not whether IDENTITY_INSERT is
turned on or not. AFAIK, this information is not exposed. I tried DBCC USEROPTIONS, but that doesn't
expose the information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:B091381A-3D58-4512-A364-754A4E0B8BD9@.microsoft.com...
> Hi Tommy,
> SELECT OBJECTPROPERTY(OBJECT_ID('table'), 'TableHasIdentity')
> Replace table with your table name.
> --
> Thanks
> Yogish|||Hi Tibor,
Yeah, you are right. I realised it after posting the message. And DBCC
USEROPTIONS doesn't give this option.
--
Thanks
Yogish|||Hi Tommy,
Check out the remarks from BOL.
At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a SET
IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL
Serverâ?¢ returns an error message that states SET IDENTITY_INSERT is already
ON and reports the table it is set ON for.
Run the following...
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
SET IDENTITY_INSERT products ON
GO
SET IDENTITY_INSERT products_new ON
On the second statement,
IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
SET operation for table 'products_new'.
I hope this will answer your question in an indirect way.
--
Thanks
Yogish|||It's true that I'll get an error if I try to set IDENTITY_INSERT on for
another table, but while I can capture the error code, I can't capture the
error message. So I know that IDENTITY_INSERT is on for another table, but I
don't know which table.
The closest solution I've found is to query the INSERTED pseudo-table. If
IDENTITY_INSERT is off, then the identity value will be zero for every row in
INSERTED. If IDENTITY_INSERT is on, then INSERTED will have other values,
unless the triggering statement is explicitly inserting zeroes.
"Yogish" wrote:
> Hi Tommy,
> Check out the remarks from BOL.
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a SET
> IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL
> Serverâ?¢ returns an error message that states SET IDENTITY_INSERT is already
> ON and reports the table it is set ON for.
> Run the following...
> CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> CREATE TABLE products_new (id int IDENTITY PRIMARY KEY, product varchar(40))
> GO
> SET IDENTITY_INSERT products ON
> GO
> SET IDENTITY_INSERT products_new ON
> On the second statement,
> IDENTITY_INSERT is already ON for table 'pubs.dbo.products'. Cannot perform
> SET operation for table 'products_new'.
> I hope this will answer your question in an indirect way.
> --
> Thanks
> Yogish
>

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
Jim
DELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:

> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim
|||That should be:

> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?
|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?
|||You told that the rows are about THE SAME, you didn′t mentioned something of
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?
|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
JimDELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:
> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim|||That should be:
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü�meyer" wrote:
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
> > There are two columns in the table1,
> >
> > ID|AccountName | ContactName |
> > 1 | ebay.com | Alex
> > 2 | ebay.com |
> >
> >
> > Look the second row, it is reduplicate info which is absolutely same with
> > the first row. So I'd like to delete the second row. How to use SQL to do
> > that?
> >
> > Cheers,
> > Jim|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||You told that the rows are about THE SAME, you didn´t mentioned something of
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
JimDELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:

> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim|||That should be:

> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches t
he
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
>|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||You told that the rows are about THE SAME, you didn′t mentioned something o
f
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?