How to diplay a dataset in a table

Hi all..
I have a dataset named "Primas" that returns 3 records with 3 fields each.
I want to display a list of those records with a header and a summarize row.
To do that, I placed a Table control into the layout. Assigned "Primas" as
the dataset and I placed a textbox control into it with the value of
=Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
When I build the report, I get the error:
"Expression value of object CODIGOPRIMA reference field CODIGOPRIMA. Report
element expressions can only reference a field in actual dataset scope or, if
they are inside an aggregate, the specified dataset scope"
(I translated the messaege from Spanish, so I'm not sure if it is accurate,
but that's the idea).
The question.. why I get that message although I have the dataset specified
for that table? When I go to a field property inside the table, under
expressions, system shows me only the fields from a dataset that is the
parent of the table (a List)
Any help will be greately appreciated,
Thanks
JaimeYou might check that the Dataset is aware of the field you are trying to
use. You can do this by clicking on the Refresh button on the Data tab with
that dataset selected. I get that same message if I've added or changed a
field in the underlying database and forget to refresh the dataset.
Jared
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:8C285D68-701D-451F-A2A8-363B80874025@.microsoft.com...
> Hi all..
> I have a dataset named "Primas" that returns 3 records with 3 fields each.
> I want to display a list of those records with a header and a summarize
> row.
> To do that, I placed a Table control into the layout. Assigned "Primas" as
> the dataset and I placed a textbox control into it with the value of
> =Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
> When I build the report, I get the error:
> "Expression value of object CODIGOPRIMA reference field CODIGOPRIMA.
> Report
> element expressions can only reference a field in actual dataset scope or,
> if
> they are inside an aggregate, the specified dataset scope"
> (I translated the messaege from Spanish, so I'm not sure if it is
> accurate,
> but that's the idea).
> The question.. why I get that message although I have the dataset
> specified
> for that table? When I go to a field property inside the table, under
> expressions, system shows me only the fields from a dataset that is the
> parent of the table (a List)
> Any help will be greately appreciated,
> Thanks
> Jaime|||Hi Tom...
I have done so but the same problem happens. And when I go to the field
value combobox, only dataset fields associated with the List are shown, not
table daaset fields.
Jaime
"Tom Rocco" wrote:
> You might check that the Dataset is aware of the field you are trying to
> use. You can do this by clicking on the Refresh button on the Data tab with
> that dataset selected. I get that same message if I've added or changed a
> field in the underlying database and forget to refresh the dataset.
> Jared
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:8C285D68-701D-451F-A2A8-363B80874025@.microsoft.com...
> > Hi all..
> >
> > I have a dataset named "Primas" that returns 3 records with 3 fields each.
> >
> > I want to display a list of those records with a header and a summarize
> > row.
> > To do that, I placed a Table control into the layout. Assigned "Primas" as
> > the dataset and I placed a textbox control into it with the value of
> > =Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
> >
> > When I build the report, I get the error:
> > "Expression value of object CODIGOPRIMA reference field CODIGOPRIMA.
> > Report
> > element expressions can only reference a field in actual dataset scope or,
> > if
> > they are inside an aggregate, the specified dataset scope"
> > (I translated the messaege from Spanish, so I'm not sure if it is
> > accurate,
> > but that's the idea).
> >
> > The question.. why I get that message although I have the dataset
> > specified
> > for that table? When I go to a field property inside the table, under
> > expressions, system shows me only the fields from a dataset that is the
> > parent of the table (a List)
> >
> > Any help will be greately appreciated,
> > Thanks
> > Jaime
>
>

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>|||I'm with Andrew, I vote Red Gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will bu
ild
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, fo
r
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:

> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
>

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > as subject.
> >
> > any freeware or done at sql level?
> >
> > thanks!
> >
> >
>|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
--
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>> What?
>>
>> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
>> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>> > as subject.
>> >
>> > any freeware or done at sql level?
>> >
>> > thanks!
>> >
>> >
>>
>|||I'm with Andrew, I vote Red Gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghostâ?¢ provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghostâ?¢ will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> > as subject.
> >
> > any freeware or done at sql level?
> >
> > thanks!
> >
> >
>
>

how to diff the data of same table of two sql servers

as subject.
any freeware or done at sql level?
thanks!
What?
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
|||say, i have a table Order at server A and B,
i want to diff the sql data at two servers.
"ChrisR" <chris@.noemail.com> wrote in message
news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
|||It's not free but it's real cheap. www.red-gate.com has a product called
data compare that will do what you ask.
Andrew J. Kelly SQL MVP
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:%23tl6F6FzEHA.1596@.TK2MSFTNGP10.phx.gbl...
> say, i have a table Order at server A and B,
> i want to diff the sql data at two servers.
> "ChrisR" <chris@.noemail.com> wrote in message
> news:OgXnH3FzEHA.1188@.tk2msftngp13.phx.gbl...
>
|||I'm with Andrew, I vote Red Gate
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
|||"Mullin Yu" <mullin_yu@.ctil.com> wrote in message
news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
> as subject.
> any freeware or done at sql level?
> thanks!
>
What about using a FULL OUTER JOIN and then pulling the values with NULL.
Those would be the difference records. The matches would be the matching
records.
Rick Sawtell
MCT, MCSD, MCDBA
|||Have a look at www.dbghost.com - why bother with a product that doesn't
always work?
DB Ghost? provides you with a fully automated BUILD, COMPARISON and
SYNCHRONIZATION capability for your SQL Server databases and is the only
product on the market that ensures database integrity as DB Ghost? will build
your database directly from your source control system. No other product in
the world does this. No other product can build, compare and synchronize a
target database making it match the source scripts precisely, every single
time, not just sometimes, but every single time. Try and prove us wrong.
Something else that might grab your interest is that an incredible 94% of
our clients (94%!!!) previously purchased our competitors products and soon
found that in the real world, these products let them down time after time.
Don't make the same mistake - why would you buy from our competitors who, for
similar money, can only offer you tools that don't build, and only compare
and sometimes synchronize...food for thought?
"ChrisR" wrote:

> What?
>
> "Mullin Yu" <mullin_yu@.ctil.com> wrote in message
> news:OREXwwFzEHA.1264@.TK2MSFTNGP12.phx.gbl...
>
>

How to develop a program which can monitor the change of a table content?

I need this exe program to monitor the insert and update action of a
table in SQL Server 2000 initiatively
How to delevop this program?
Who can provide me some advice or some information?
Thanks a lot.This is a multi-part message in MIME format.
--=_NextPart_000_0051_01C3CAB1.5A9963D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
See my reply in .programming.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Simon Peng" <pengxq@.hotmail.com> wrote in message
news:tpcluvo1vuac96vtrplv36sndhoiheg9ck@.4ax.com...
I need this exe program to monitor the insert and update action of a
table in SQL Server 2000 initiatively
How to delevop this program?
Who can provide me some advice or some information?
Thanks a lot.
--=_NextPart_000_0051_01C3CAB1.5A9963D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

See my reply in =.programming.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Simon Peng" wrote in =message news:tpcluvo1vua=c96vtrplv36sndhoiheg9ck@.4ax.com...I need this exe program to monitor the insert and update action of =atable in SQL Server 2000 initiativelyHow to delevop this program?Who can =provide me some advice or some information?Thanks a lot.

---

--=_NextPart_000_0051_01C3CAB1.5A9963D0--|||Why dont you use triggers to audit the table|||sysindexes table records all update, insert, delete activity for all tables. this may work for you. check it out.sql

How to determine when and if SQL Agent job will run again?

I need to determine when (maybe) and if (definitely) a SQL Agent job will run again. I need to maintain a table of the next pending execution for each job. I need to be able to update this table from within a SQL Agent job, but preferably from within an executing SSIS package in the job. Is this possible and if so, any suggestions on how?

Thanks

Hello, your question is really SQL agent related so you likely want to post in the mgt tools forum but I can say what I know. SQL agent jobs and job steps can be manipulate from TSQL/Stored procedures so in theory you could call those from an SSIS pacakge. For example, using the SSIS Execute SQL task.

The following looks like a good reference to SQL Agent SPs.

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

Hope that helps

|||

Here is the solution in case anyone else has the issue. Get the NextRunDate property of the Job object in the Microsoft.SqlServer.Management.Smo.Agent namespace.

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 the last time a table was accessed ?

I'm trying to do some housekeeping. I want to delete user tables from database(s) that have not had any activity...

I cannot seem to find a mechanism for accomplishing this. sysobjects only shows the createdate, not the last time a user table had a SELECT, INSERT, UPDATE or DELETE operation performed on it.

Anyone know how to do this ?

Thanks

(P.S. this is the second posting of this question today, as I went to my threads and I do not see the original post - sorry for the duplicate, but as I say, I do not see the original so am re-posting).

randyvol

SQL server doesn't store such information and all those process is logged in transction log, and you might need third party tools in this case to audit the events or run server side trace if you want to schedule such information for time being.|||

Satya -

First and foremost thank you for your reply.

Next - what?!!? WOW! I just naturally assumed that SQL Server would do this. I cannot imagine a product that is being touted as 'ready for prime time' does not provide such basic necessities. Don't get me wrong, I really like the product, especially the 2k5 instantiation, which is why I'm even more perplexed.

How is one supposed to know over time what tables one can delete with absolute safety? I understand that 3rd party tools provide this ability, but surely they leverage something (perhaps undocumented) in the basic system? Teradata, for instance provides this information - I know, I'm a certified Teradata Master and have used that system's entries on many occasions to ascertain whether or not a table was really 'stale' and could be dropped to free up disk. I would not think it is that big a deal (or that much overhead) to have one extra column, say in sysobjects, for instance, 'last updated'.

I just cannot believe MSFT overlooked this, or expects me to cough up dollars for a 3rd party tool to do this routine maintenance chore. This is something I'd expect to find in the sys tables for sure. Doesn't have to be elegant and exposed into Studio - just basic data I can fetch with a query would suffice.

As for the tranlog.. it is transient. I'm sure that there is data there to mine, but it doesn't help me on the 100's of tables already existent on our legacy system, that have been around for years.

I sure hope MSFT decides to provide this ability soon.

(It does explain why I cannot find any documentation on how to do this though ;-)

Oh well, I guess I'll have to go build my own stuff and let it cook for a couple of quarters to see if tables are stale or not.

Regards

randyvol

How To Determine the FileGroup for a Table

Is there a way to determine what filegroup a table belongs to by looking at
the
entry in the SysObjects or another system table? I found it easily enough f
or
indexes, but can't seem to find it for Table entries.
TIA,
-Steve-Never mind. I found it. I had to dig a little deeper into the system SP's.
Select s.groupname
From sysfilegroups s, sysindexes i
Where i.id = @.TableObjectID
And i.indid < 2
And i.groupid = s.groupid
-Steve-
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:eglD3J5UGHA.5248@.TK2MSFTNGP10.phx.gbl...
> Is there a way to determine what filegroup a table belongs to by looking a
t
> the entry in the SysObjects or another system table? I found it easily en
ough
> for indexes, but can't seem to find it for Table entries.
> TIA,
> -Steve-
>

how to determine the best timeout value

Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increas
e
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find th
e
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>

how to determine the best timeout value

Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increase
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find the
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!
Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>

How to determine table size?

Is there a stored procedure or command for determining the allocation size of
a table and related info?
thanks
Try,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:

> Is there a stored procedure or command for determining the allocation size of
> a table and related info?
> thanks

How to determine table size?

Is there a stored procedure or command for determining the allocation size o
f
a table and related info?
thanksTry,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:

> Is there a stored procedure or command for determining the allocation size
of
> a table and related info?
> thanks

How to determine table size?

Is there a stored procedure or command for determining the allocation size of
a table and related info?
thanksTry,
use northwind
go
exec sp_spaceused 'dbo.orders'
go
AMB
"Snake" wrote:
> Is there a stored procedure or command for determining the allocation size of
> a table and related info?
> thankssql

How to determine ROWCOUNT without executing the select statement

I'm selecting data from a large table in a paged manner by using the
ROW_NUMBER ranking function in a function like the one shown below... I
want the function to also return the total number of rows in the dataset
AFTER the primary select is issued but before the paged data is selected
out. In other words, if there are 10,000 rows, and 3500 of them get
selected by the where clause, but the paging only returns 101 .. 200, I
want the total rows output to be set to 3500.
The way this is written currently, I use @.@.ROWCOUNT, but only get the
page size (eg 100). Is there an easy way to get the primary data set
size without resorting to memory or temporary tables, and without
executing the main query twice'
-mdb
#############################
PROCEDURE [dbo].[GetTablePagedAndSorted]
(
@.tableName nvarchar(100),
@.columnList varchar(2000),
@.sortExpression nvarchar(100),
@.whereClause varchar(2000),
@.startRowIndex int,
@.maximumRows int,
@.totalRows int OUTPUT
) AS
IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
-- Issue query
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
SET @.sql = @.sql + ' FROM (
SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
@.sortExpression + ') AS RowRank
FROM ' + @.tableName + '
WHERE (' + @.whereClause + ')
) AS TableWithRowNumbers '
IF (@.maximumRows > 0)
BEGIN
SET @.sql = @.sql + '
WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
'
AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
END
-- Execute the SQL query
PRINT @.sql
EXEC sp_executesql @.sql
SET @.totalRows = @.@.ROWCOUNT
######################################You could put the result into a temp. table. Or you could create a SELECT
COUNT(*) to get the count before you do the actual SELECT. I guess the
question is how important is it to know the intermediate result set size -
is it worth the extra inefficiency?
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
> I'm selecting data from a large table in a paged manner by using the
> ROW_NUMBER ranking function in a function like the one shown below... I
> want the function to also return the total number of rows in the dataset
> AFTER the primary select is issued but before the paged data is selected
> out. In other words, if there are 10,000 rows, and 3500 of them get
> selected by the where clause, but the paging only returns 101 .. 200, I
> want the total rows output to be set to 3500.
> The way this is written currently, I use @.@.ROWCOUNT, but only get the
> page size (eg 100). Is there an easy way to get the primary data set
> size without resorting to memory or temporary tables, and without
> executing the main query twice'
> -mdb
> #############################
> PROCEDURE [dbo].[GetTablePagedAndSorted]
> (
> @.tableName nvarchar(100),
> @.columnList varchar(2000),
> @.sortExpression nvarchar(100),
> @.whereClause varchar(2000),
> @.startRowIndex int,
> @.maximumRows int,
> @.totalRows int OUTPUT
> ) AS
> IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
> -- Issue query
> DECLARE @.sql nvarchar(4000)
> SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
> SET @.sql = @.sql + ' FROM (
> SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
> @.sortExpression + ') AS RowRank
> FROM ' + @.tableName + '
> WHERE (' + @.whereClause + ')
> ) AS TableWithRowNumbers '
> IF (@.maximumRows > 0)
> BEGIN
> SET @.sql = @.sql + '
> WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
> '
> AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
> ' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
> END
> -- Execute the SQL query
> PRINT @.sql
> EXEC sp_executesql @.sql
> SET @.totalRows = @.@.ROWCOUNT
> ######################################
>|||"Mike C#" <xyz@.xyz.com> wrote in
news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
> You could put the result into a temp. table. Or you could create a
> SELECT COUNT(*) to get the count before you do the actual SELECT. I
> guess the question is how important is it to know the intermediate
> result set size - is it worth the extra inefficiency?
> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
Thanks, but as I said I do not want to use temporary tables, as this will
absolutely swamp my tempdb due to the potential size of the query results.
I considered your other suggestion previously, but then the question is how
do I get the results of an EXEC into a variable? Keep in mind that I have
to build the sql dynamically, and thus require sp_executesql. The
following syntax doesn't work:
SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
If I can find the solution to how to set a variable based on a dynamic sql
statement then I will be set.
-mdb|||Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
> Thanks, but as I said I do not want to use temporary tables, as this
> will absolutely swamp my tempdb due to the potential size of the query
> results. I considered your other suggestion previously, but then the
> question is how do I get the results of an EXEC into a variable? Keep
> in mind that I have to build the sql dynamically, and thus require
> sp_executesql. The following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic
> sql statement then I will be set.
>
OK I found the solution - sp_executesql can actually accept input and
output parameters!!
See the wonderful article at:
http://www.sommarskog.se/dynamic_sql.html
-mdb|||Oops, I see you already found that
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E26438875B7mbrayctiusacom@.207.46.248.16...
> Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
>> Thanks, but as I said I do not want to use temporary tables, as this
>> will absolutely swamp my tempdb due to the potential size of the query
>> results. I considered your other suggestion previously, but then the
>> question is how do I get the results of an EXEC into a variable? Keep
>> in mind that I have to build the sql dynamically, and thus require
>> sp_executesql. The following syntax doesn't work:
>> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
>> If I can find the solution to how to set a variable based on a dynamic
>> sql statement then I will be set.
> OK I found the solution - sp_executesql can actually accept input and
> output parameters!!
> See the wonderful article at:
> http://www.sommarskog.se/dynamic_sql.html
> -mdb|||sp_executesql does take output params
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16...
> "Mike C#" <xyz@.xyz.com> wrote in
> news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
>> You could put the result into a temp. table. Or you could create a
>> SELECT COUNT(*) to get the count before you do the actual SELECT. I
>> guess the question is how important is it to know the intermediate
>> result set size - is it worth the extra inefficiency?
>> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
>> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
> Thanks, but as I said I do not want to use temporary tables, as this will
> absolutely swamp my tempdb due to the potential size of the query results.
> I considered your other suggestion previously, but then the question is
> how
> do I get the results of an EXEC into a variable? Keep in mind that I have
> to build the sql dynamically, and thus require sp_executesql. The
> following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic sql
> statement then I will be set.
> -mdb

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 if data imported into database table

We have a SQL 2000 database that we import a csv file into. The csv file
contains specific metrics from our accounting system from the the prior day.
The data gets dumped out to the csv file sometime at night, and a DTS packag
e
imports it into SQL at 7:30 am.
I need to verify that the data populated into SQL every morning. I've been
manually querying the table, E1_Data, on one of three date columns, ENDED,
with the following script,
SELECT ENDED
FROM E12SQL.E1_DATA
WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
'2005')
If I get data back, I know yesterdays csv file imported into SQL.
Surely there is a better way to do this.
AlwaysLearningYou could create a stored proc and add it as a second step in your job to ru
n
the DTS package.
create proc as
declare @.myDate varchar(20)
set @.myDate = convert(varchar,dateadd(day,-1,getDate()),1) --yesterday's
date
if not exists(select Ended from E1_DATA where Ended > @.myDate) begin
raiserror('No imported records',16,1)
return 1
end
return 0
"AlwaysLearning" wrote:

> We have a SQL 2000 database that we import a csv file into. The csv file
> contains specific metrics from our accounting system from the the prior da
y.
> The data gets dumped out to the csv file sometime at night, and a DTS pack
age
> imports it into SQL at 7:30 am.
> I need to verify that the data populated into SQL every morning. I've bee
n
> manually querying the table, E1_Data, on one of three date columns, ENDED,
> with the following script,
> SELECT ENDED
> FROM E12SQL.E1_DATA
> WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
> '2005')
> If I get data back, I know yesterdays csv file imported into SQL.
> Surely there is a better way to do this.
>
> --
> AlwaysLearning|||The "yesterday's date" was all supposed to be a comment, it didnt' wrap well
.
Also, you may need to have the where clause be >= instead of >.
"Kathi Kellenberger" wrote:
> You could create a stored proc and add it as a second step in your job to
run
> the DTS package.
> create proc as
> declare @.myDate varchar(20)
> set @.myDate = convert(varchar,dateadd(day,-1,getDate()),1) --yesterday
's
> date
> if not exists(select Ended from E1_DATA where Ended > @.myDate) begin
> raiserror('No imported records',16,1)
> return 1
> end
> return 0
>
>
> "AlwaysLearning" wrote:
>|||So how do you want to verify the that the data was inserted. And why don't
you trust that the DTS package worked? Have there been cases where it
didn't but didn't raise any errors?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in message
news:D3420366-7668-49EB-B245-6A668FC6B552@.microsoft.com...
> We have a SQL 2000 database that we import a csv file into. The csv file
> contains specific metrics from our accounting system from the the prior
> day.
> The data gets dumped out to the csv file sometime at night, and a DTS
> package
> imports it into SQL at 7:30 am.
> I need to verify that the data populated into SQL every morning. I've
> been
> manually querying the table, E1_Data, on one of three date columns, ENDED,
> with the following script,
> SELECT ENDED
> FROM E12SQL.E1_DATA
> WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
> '2005')
> If I get data back, I know yesterdays csv file imported into SQL.
> Surely there is a better way to do this.
>
> --
> AlwaysLearning|||Louis, you ask, "...why don't you trust that the DTS package worked?"
Actually, the DTS package works just fine, it is that sometimes the
accounting system doesn't export its data to the csv file which results in
DTS pulling in the same data twice.
Regarding your first question, "...how do you want to verify the that the
data was inserted". If the data imported successfully, then the table,
E1_Data, will contain the priors day date in the ENDED column. It seems
logical to me that if I count the number of records in the table that have
yesterdays date and store that value somewhere, Excel or another SQL table,
then I can verify the data came over.
AlwaysLearning
"Louis Davidson" wrote:

> So how do you want to verify the that the data was inserted. And why don'
t
> you trust that the DTS package worked? Have there been cases where it
> didn't but didn't raise any errors?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in messa
ge
> news:D3420366-7668-49EB-B245-6A668FC6B552@.microsoft.com...
>
>|||That's . I am guessing that the other suggestion of having a step that
checks the table and raises an error if no data is in the table would
probably be a good idea.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in message
news:88D357A1-27B8-44C2-AE6B-E7CEF4767DA3@.microsoft.com...
> Louis, you ask, "...why don't you trust that the DTS package worked?"
> Actually, the DTS package works just fine, it is that sometimes the
> accounting system doesn't export its data to the csv file which results in
> DTS pulling in the same data twice.
> Regarding your first question, "...how do you want to verify the that the
> data was inserted". If the data imported successfully, then the table,
> E1_Data, will contain the priors day date in the ENDED column. It seems
> logical to me that if I count the number of records in the table that have
> yesterdays date and store that value somewhere, Excel or another SQL
> table,
> then I can verify the data came over.
> --
> AlwaysLearning
>
> "Louis Davidson" wrote:
>

How to determine actual constraint name by passing a column name

I need a query in which I can pass in a column name and table name and will
be provided all constraints ( all types ) for that given column in that
table. I've created and found several queries that work for various types
but have not found one that does what I need for Unique constraints. I have
the following for FKs, but it doesn't work for UQs
select db_name() as DATABASE_name
,t_obj.name as TABLE_NAME
,user_name(c_obj.uid) as OWNER
,c_obj.name as CONSTRAINT_NAME
,col.name as COLUMN_NAME
,col.colid as ORDINAL_POSITION
,c_obj.xtype as XTYPE
from
sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id and con.colid = col.colid
where
c_obj.xtype = 'F'
ORDER BY t_obj.name
If anyone can point out what I am missing, I'd really appreciate it.
Thanks
RachelDid you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
>I need a query in which I can pass in a column name and table name and will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Yes, but that does not provide information on Unique Constraints ( UQ)
"Tibor Karaszi" wrote:
> Did you try the INFORMATION_SCHEMA views? For instance CONSTRAINT_COLUMN_USAGE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> >I need a query in which I can pass in a column name and table name and will
> > be provided all constraints ( all types ) for that given column in that
> > table. I've created and found several queries that work for various types
> > but have not found one that does what I need for Unique constraints. I have
> > the following for FKs, but it doesn't work for UQs
> > select db_name() as DATABASE_name
> > ,t_obj.name as TABLE_NAME
> > ,user_name(c_obj.uid) as OWNER
> > ,c_obj.name as CONSTRAINT_NAME
> > ,col.name as COLUMN_NAME
> > ,col.colid as ORDINAL_POSITION
> > ,c_obj.xtype as XTYPE
> > from
> > sysobjects c_obj
> > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > join sysconstraints con on c_obj.id = con.constid
> > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > where
> > c_obj.xtype = 'F'
> > ORDER BY t_obj.name
> >
> > If anyone can point out what I am missing, I'd really appreciate it.
> > Thanks
> > Rachel
>
>|||> Yes, but that does not provide information on Unique Constraints ( UQ)
Really? Try this repro, and let us know how it works out for you.
USE tempdb
GO
CREATE TABLE dbo.foobar
(
foo INT NOT NULL UNIQUE,
bar VARCHAR(12)
)
ALTER TABLE dbo.foobar ADD CONSTRAINT UQ_Bar UNIQUE(bar)
GO
SELECT *
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME='foobar' AND COLUMN_NAME IN ('foo','bar')
GO
DROP TABLE dbo.foobar
GO
http://www.aspfaq.com/
(Reverse address to reply.)|||You need to do a little more work, but the starting point is the view
mentioned by Tibor. Logically, you need to determine if the column is
associated with a constraint and if the associated constraint is a unique
constraint. Constraint information can be found in the TABLE_CONSTRAINTS
view. hint - looks like a join is involved. BTW - what if unique-ness is
enforced with an index and not a constraint?
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:7CD67018-4028-4823-8320-26D90DB84B62@.microsoft.com...
> Yes, but that does not provide information on Unique Constraints ( UQ)
> "Tibor Karaszi" wrote:
> > Did you try the INFORMATION_SCHEMA views? For instance
CONSTRAINT_COLUMN_USAGE.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > http://www.sqlug.se/
> >
> >
> > "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> > news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> > >I need a query in which I can pass in a column name and table name and
will
> > > be provided all constraints ( all types ) for that given column in
that
> > > table. I've created and found several queries that work for various
types
> > > but have not found one that does what I need for Unique constraints.
I have
> > > the following for FKs, but it doesn't work for UQs
> > > select db_name() as DATABASE_name
> > > ,t_obj.name as TABLE_NAME
> > > ,user_name(c_obj.uid) as OWNER
> > > ,c_obj.name as CONSTRAINT_NAME
> > > ,col.name as COLUMN_NAME
> > > ,col.colid as ORDINAL_POSITION
> > > ,c_obj.xtype as XTYPE
> > > from
> > > sysobjects c_obj
> > > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > > join sysconstraints con on c_obj.id = con.constid
> > > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > > where
> > > c_obj.xtype = 'F'
> > > ORDER BY t_obj.name
> > >
> > > If anyone can point out what I am missing, I'd really appreciate it.
> > > Thanks
> > > Rachel
> >
> >
> >|||Ken,
Your query will not pick up unique and primary key constraints - they have
no data in syscolumns. To "fix" your query, make the join with syscolumns a
LEFT join:
> LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
Now, however, your 'Column' column will be null for PK and UQ constraints,
because they are implemented as indexes and their columns are stored in
sysindexes, not sysconstraints.
(BTW, the INFORMATION_SCHEMA views will not return any data regarding
default constraints, so I think you're on a better track by going straight
to the system tables.)
Basically, your start is correct in that
select *
from sysobjects
where parent_obj = object_id('<tablename>')
will return all of a table's constraints.
Since you want column information for each constraint, you're also going to
have problems when a constraint (PK, UQ, FK) covers more than one of a
table's columns. You need to decide whether to have multiple column
constraints come back as multiple rows or as a comma-delimited string (like
sp_helpconstraint or sp_helpindex do)
Do you like the output of sp_helpconstraint? If so, I would recommend you
just rewrite sp_helpconstraint, modifying it to take a table name and owner
as parameters, add the appropriate filter, and make it return only one
result set and just the resulting columns you want. Be sure to test on
multiple-column constraints.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> I need a query in which I can pass in a column name and table name and
will
> be provided all constraints ( all types ) for that given column in that
> table. I've created and found several queries that work for various types
> but have not found one that does what I need for Unique constraints. I
have
> the following for FKs, but it doesn't work for UQs
> select db_name() as DATABASE_name
> ,t_obj.name as TABLE_NAME
> ,user_name(c_obj.uid) as OWNER
> ,c_obj.name as CONSTRAINT_NAME
> ,col.name as COLUMN_NAME
> ,col.colid as ORDINAL_POSITION
> ,c_obj.xtype as XTYPE
> from
> sysobjects c_obj
> join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> join sysconstraints con on c_obj.id = con.constid
> join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> where
> c_obj.xtype = 'F'
> ORDER BY t_obj.name
> If anyone can point out what I am missing, I'd really appreciate it.
> Thanks
> Rachel|||Of course I meant rewrite sp_helpconstraint as a NEW stored procedure, with
a different name!
Ron
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:%23GUlV7U7EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Ken,
> Your query will not pick up unique and primary key constraints - they have
> no data in syscolumns. To "fix" your query, make the join with syscolumns
a
> LEFT join:
> > LEFT join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> Now, however, your 'Column' column will be null for PK and UQ constraints,
> because they are implemented as indexes and their columns are stored in
> sysindexes, not sysconstraints.
> (BTW, the INFORMATION_SCHEMA views will not return any data regarding
> default constraints, so I think you're on a better track by going straight
> to the system tables.)
> Basically, your start is correct in that
> select *
> from sysobjects
> where parent_obj = object_id('<tablename>')
> will return all of a table's constraints.
> Since you want column information for each constraint, you're also going
to
> have problems when a constraint (PK, UQ, FK) covers more than one of a
> table's columns. You need to decide whether to have multiple column
> constraints come back as multiple rows or as a comma-delimited string
(like
> sp_helpconstraint or sp_helpindex do)
> Do you like the output of sp_helpconstraint? If so, I would recommend you
> just rewrite sp_helpconstraint, modifying it to take a table name and
owner
> as parameters, add the appropriate filter, and make it return only one
> result set and just the resulting columns you want. Be sure to test on
> multiple-column constraints.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:BE32382B-7D00-43C7-A8C8-6645E3546CB5@.microsoft.com...
> > I need a query in which I can pass in a column name and table name and
> will
> > be provided all constraints ( all types ) for that given column in that
> > table. I've created and found several queries that work for various
types
> > but have not found one that does what I need for Unique constraints. I
> have
> > the following for FKs, but it doesn't work for UQs
> > select db_name() as DATABASE_name
> > ,t_obj.name as TABLE_NAME
> > ,user_name(c_obj.uid) as OWNER
> > ,c_obj.name as CONSTRAINT_NAME
> > ,col.name as COLUMN_NAME
> > ,col.colid as ORDINAL_POSITION
> > ,c_obj.xtype as XTYPE
> > from
> > sysobjects c_obj
> > join sysobjects t_obj on c_obj.parent_obj = t_obj.id
> > join sysconstraints con on c_obj.id = con.constid
> > join syscolumns col on t_obj.id = col.id and con.colid = col.colid
> > where
> > c_obj.xtype = 'F'
> > ORDER BY t_obj.name
> >
> > If anyone can point out what I am missing, I'd really appreciate it.
> > Thanks
> > Rachel
>