How to identify IDENTITY column on a linked server table?

Using SQL2000 (latest SP and patches)...
I'm trying to identify the IDENTITY column of a table in a database on a
linked server. Ordinarily I'd just use sp_columns, but since it's a linked
database I have to use sp_columns_ex, and it doesn't return the IDENTITY
column identifier!
Help!! :) Any ideas? Thanks!
JackHave you tried
exec linkerserver.db.dbo.sp_help tablename
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jack Black" <jackblackisback@.hotmail.com> wrote in message
news:%23e438$y0DHA.404@.tk2msftngp13.phx.gbl...
> Using SQL2000 (latest SP and patches)...
> I'm trying to identify the IDENTITY column of a table in a database on a
> linked server. Ordinarily I'd just use sp_columns, but since it's a
linked
> database I have to use sp_columns_ex, and it doesn't return the IDENTITY
> column identifier!
> Help!! :) Any ideas? Thanks!
> Jack
>|||Thanks for responding! :)
Yeah, I've tried that... Suffice to say the scripting language I'm using
(ColdFusion) doesn't support multiple resultsets without using their stored
procedure tag (CFSTOREDPROC), so I would have to build the query into a
stored proc. Normally not a problem, but CFSTOREDPROC is hardwired to
execute all sp's with a call command, so the syntax executed would be "CALL
EXEC ...", resulting in a failed call. And since CALL doesn't work with
linked server syntax, I can't use it.
I'm trying to avoid requiring a local DSN pointing to the remote SQL server,
but I think it's coming down to needing that because of CF's shortcomings...
Any other thoughts welcome!! :)
Jack
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:u2sBBC40DHA.716@.TK2MSFTNGP12.phx.gbl...
> Have you tried
> exec linkerserver.db.dbo.sp_help tablename
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.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
>
> "Jack Black" <jackblackisback@.hotmail.com> wrote in message
> news:%23e438$y0DHA.404@.tk2msftngp13.phx.gbl...
> > Using SQL2000 (latest SP and patches)...
> >
> > I'm trying to identify the IDENTITY column of a table in a database on a
> > linked server. Ordinarily I'd just use sp_columns, but since it's a
> linked
> > database I have to use sp_columns_ex, and it doesn't return the IDENTITY
> > column identifier!
> >
> > Help!! :) Any ideas? Thanks!
> > Jack
> >
> >
>

How to Identify Hot Tables

Does anyone know how to identify the hottest, most active tables in a
database?

We have hundreds of users hitting a PeopleSoft database with hundreds
of tables. We are I/O bound on our SAN, and are thinking of putting
the hottest tables on a solid state (RAM) drive for improved
performance. Problem is: which are the hottest tables? Would like to
do this based on hard data instead of developer/vendor guesses.

Any suggestions are much appreciated.Hi

You could profile system usage, this will not give tables if you are using
stored procedures, but you could work it out knowing which procedures are
being used. Knowing the procedures being called also helps to put things
into context and allows you to examine query plans.

John

"Jeff Roughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:b8143af9.0407071314.5bcc4ae3@.posting.google.c om...
> Does anyone know how to identify the hottest, most active tables in a
> database?
> We have hundreds of users hitting a PeopleSoft database with hundreds
> of tables. We are I/O bound on our SAN, and are thinking of putting
> the hottest tables on a solid state (RAM) drive for improved
> performance. Problem is: which are the hottest tables? Would like to
> do this based on hard data instead of developer/vendor guesses.
> Any suggestions are much appreciated.|||My immediate thought was "dbcc memusage" but I looked in the documentation
and it's now obsolete. Too bad. I think that it showed the top objects in
memory, which is almost exactly what you seek.

If I had to approach this myself, I might give some thought to evaluating
the locks taken out. They should give you at least some clue as to what
objects are important to SQL Server (but not all objects locked may be in
memory). I suppose I'd snapshot the lock list periodically and then develop
some processes to evaluate what was locked and summarize the objects locked
and sort of estimate how much storage of each is locked (you have table,
extend, page and row locks to consider). I don't think this will tell you
much about logs, though, which may be critical.

"Jeff Roughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:b8143af9.0407071314.5bcc4ae3@.posting.google.c om...
> Does anyone know how to identify the hottest, most active tables in a
> database?
> We have hundreds of users hitting a PeopleSoft database with hundreds
> of tables. We are I/O bound on our SAN, and are thinking of putting
> the hottest tables on a solid state (RAM) drive for improved
> performance. Problem is: which are the hottest tables? Would like to
> do this based on hard data instead of developer/vendor guesses.
> Any suggestions are much appreciated.

How to identify fragmented index for rebuild

Hi,

I want to find out what is a good way to identify indexes that need
rebuilding. I tried to run DBCC showcontig and identify them based on
the scan density %. And according to a Microsoft Tech Net - All About
SQL Server Indexes discussion, the expert stated that based on the
Density (below 20%), I can pick out those indexes associated to the
table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to
rebuild all indexes associated to the specific table. The strange part
is that some tables, which have 20% or below scan density do not have
any indexes. So, I am confused!

Thanks in advance.Check out the white paper on SQL 2000 Index Defrag Best Practices:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

> The strange part
> is that some tables, which have 20% or below scan density do not have
> any indexes. So, I am confused!

You can create an clustered index on a heap (a table with no clustered
index) in order to reorg the table. You can drop it afterward if it is not
needed. It's usually a good idea to have a clustered index on every table
unless you have a specific reason not to.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"xo55ox" <xo55ox@.hotmail.com> wrote in message
news:abbcb3d7.0404291344.14a3a778@.posting.google.c om...
> Hi,
> I want to find out what is a good way to identify indexes that need
> rebuilding. I tried to run DBCC showcontig and identify them based on
> the scan density %. And according to a Microsoft Tech Net - All About
> SQL Server Indexes discussion, the expert stated that based on the
> Density (below 20%), I can pick out those indexes associated to the
> table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to
> rebuild all indexes associated to the specific table. The strange part
> is that some tables, which have 20% or below scan density do not have
> any indexes. So, I am confused!
> Thanks in advance.

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,
Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:

> Is there a way to identify a specific query - such as by begin time or user
> account - and then be able to kill the query programaticaly. We have a web
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table). I
> would like a way a user can kill their query.
> Thanks,
|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
[vbcol=seagreen]
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement .
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas

"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:

> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
..[vbcol=seagreen]
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
user[vbcol=seagreen]
web[vbcol=seagreen]
table). I[vbcol=seagreen]

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:
> Is there a way to identify a specific query - such as by begin time or user
> account - and then be able to kill the query programaticaly. We have a web
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table). I
> would like a way a user can kill their query.
> Thanks,|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement .
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
> > Is there a way to identify a specific query - such as by begin time or user
> > account - and then be able to kill the query programaticaly. We have a web
> > based application the issues SQL queries. If a user formulates a query
> > poorly it can take quite a long time to run (40M+ records for one table). I
> > would like a way a user can kill their query.
> >
> > Thanks,|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
> > Is there a way to identify a specific query - such as by begin time or
user
> > account - and then be able to kill the query programaticaly. We have a
web
> > based application the issues SQL queries. If a user formulates a query
> > poorly it can take quite a long time to run (40M+ records for one
table). I
> > would like a way a user can kill their query.
> >
> > Thanks,

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:

> Is there a way to identify a specific query - such as by begin time or use
r
> account - and then be able to kill the query programaticaly. We have a w
eb
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table).
I
> would like a way a user can kill their query.
> Thanks,|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done b
y
issuing queries/commands to the SQL via a Java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
[vbcol=seagreen]
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
>|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a Java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:

> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.[vbcol=seagreen]
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
>
user[vbcol=seagreen]
web[vbcol=seagreen]
table). I[vbcol=seagreen]

How to Identify an Array

I have seen several examples explaining the fact that a table
containing a field for each day of the week is for the most part an
array. An specific example is where data representing worked hours is
stored in a table.

CREATE TABLE [hoursWorked] (
[id] [int] NOT NULL ,
[location_id] [tinyint] NOT NULL,
[sunday] [int] NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL
)

I had to work with a table with a similar structure about 7 years ago
and I remember that writing code against the table was pretty close to
Hell on earth.

I am now looking at a table that is similar in nature - but different.

CREATE TABLE [blah] (
[concat_1_id] [int] NOT NULL ,
[concat_2_id] [int] NOT NULL ,
[code_1] [varchar] (30) NOT NULL ,
[code_2] [varchar] (20) NULL ,
[code_3] [varchar] (20) NULL ,
[some_flg] [char] (1) NOT NULL
) ON [PRIMARY]

The value for code_2 and code_3 will be dependently null and they will
represent similar data in both records (i.e. the value "abc" can exist
in both fields) . For example if code_2 contains data then code_3 will
probably not contain data.

I do not think that this is an array. But with so many rows where
code_2 and code_3 will be NULL something just does not feel right.

I will appreciate your input.rm wrote:

Quote:

Originally Posted by

I have seen several examples explaining the fact that a table
containing a field for each day of the week is for the most part an
array. An specific example is where data representing worked hours is
stored in a table.
>
CREATE TABLE [hoursWorked] (
[id] [int] NOT NULL ,
[location_id] [tinyint] NOT NULL,
[sunday] [int] NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL
)
>
I had to work with a table with a similar structure about 7 years ago
and I remember that writing code against the table was pretty close to
Hell on earth.
>
I am now looking at a table that is similar in nature - but different.
>
CREATE TABLE [blah] (
[concat_1_id] [int] NOT NULL ,
[concat_2_id] [int] NOT NULL ,
[code_1] [varchar] (30) NOT NULL ,
[code_2] [varchar] (20) NULL ,
[code_3] [varchar] (20) NULL ,
[some_flg] [char] (1) NOT NULL
) ON [PRIMARY]
>
The value for code_2 and code_3 will be dependently null and they will
represent similar data in both records (i.e. the value "abc" can exist
in both fields) . For example if code_2 contains data then code_3 will
probably not contain data.
>
I do not think that this is an array. But with so many rows where
code_2 and code_3 will be NULL something just does not feel right.
>
I will appreciate your input.

A table is not an array. It is a relation. Unlike arrays, relations are
not addressable by an index structure but only by the values they
contain. A relation with N attributes is N-dimensional but that doesn't
make it an N-dimensional array.

I have only your column names to go on. Your HoursWorked structure is
surely very impractical, not least because of the difficulty of
aggregating data across multiple days. The second case is trickier to
interpret. At the very least it seems probable that it isn't normalized
appropriately because of what you have said about the dependencies.
Think Fifth Normal Form and satisfy yourself about the appropriateness
of the design. Design by newsgroup is really not much more than
guesswork.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--