Info!

Hi All, 2 doubts
1. How can I retrieve the exact command (T-SQL) being executed by an SPID on
the query Analyzer. You can get the same info using Ent. Mgr and right click
on SPID.
2. What's the best technic (light weight method) to log errors from a Stored
Proc? I don't wanna use xp_cmdshell...
TIA1) DBCC INPUTBUFFER (SPID) capped at 255 I believe, or use
fn_get_sql() SP3 + on SQl server 2000 take a look here
(http://vyaskn.tripod.com/fn_get_sql.htm)
2) log the errors in a ErrorLog table with procname, date, any other
info (parameters maybe) etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||DBCC INPUTBUFFER (SPID)
note - it will truncate the statement after a particular length.
"Vai2000" <nospam@.microsoft.com> wrote in message
news:e9STBJddGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Hi All, 2 doubts
> 1. How can I retrieve the exact command (T-SQL) being executed by an SPID
> on
> the query Analyzer. You can get the same info using Ent. Mgr and right
> click
> on SPID.
> 2. What's the best technic (light weight method) to log errors from a
> Stored
> Proc? I don't wanna use xp_cmdshell...
> TIA
>|||> 2. What's the best technic (light weight method) to log errors from a Storedd">
> Proc? I don't wanna use xp_cmdshell...
Where do you want these errors to be logged? RAISERROR and xp_logevent can b
e options, but hard to
tell without more info.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vai2000" <nospam@.microsoft.com> wrote in message news:e9STBJddGHA.1204@.TK2MSFTNGP02.phx.gb
l...
> Hi All, 2 doubts
> 1. How can I retrieve the exact command (T-SQL) being executed by an SPID
on
> the query Analyzer. You can get the same info using Ent. Mgr and right cli
ck
> on SPID.
> 2. What's the best technic (light weight method) to log errors from a Stor
ed
> Proc? I don't wanna use xp_cmdshell...
> TIA
>sql

Info about a running job step

Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
QuentinYou can use sp_help_job e.g.
exec msdb..sp_help_job
@.job_name = 'jobname ,
@.job_aspect = 'JOB'
Look at the current_execution_status and current_execution_step columns
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message
news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
Hi group,
I need to retrieve information about a running step, specifically its
status. When a step is being executed, the EM always knows and shows so
(under status showing Executing Job Step 'n (step name)'). However, I can
not find such information in the system tables. where does EM get the info
from? How can I access this info with a query, or better yet, within a
stored proc? Help is appreciated.
Quentin|||Thanks Mr. Dentist.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:efTLfj4VDHA.1816@.TK2MSFTNGP09.phx.gbl...
> You can use sp_help_job e.g.
> exec msdb..sp_help_job
> @.job_name = 'jobname ,
> @.job_aspect = 'JOB'
> Look at the current_execution_status and current_execution_step columns
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxXqrA3VDHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi group,
> I need to retrieve information about a running step, specifically its
> status. When a step is being executed, the EM always knows and shows so
> (under status showing Executing Job Step 'n (step name)'). However, I can
> not find such information in the system tables. where does EM get the
info
> from? How can I access this info with a query, or better yet, within a
> stored proc? Help is appreciated.
> Quentin
>

Indexing problem and slow data retrieve

I have two tables that stores company information in a database. Since
retrieving data from the tables is too slow, the structure need to be
improved. The current structure of the tables is as follows:
First table contains
table1
companyNo : unique field that is created by sequence
companyTaxNumber:have a unique index
companyName : have an index, but not unique
companyAddress
companyCountry
timestamp
trigger on table1:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
Second Table Contains
table2
Up_companyNo2 : unique field that is created by sequence
Up_companyTaxNumber2:have a unique index
Up_companyName2 : have an index, but not unique
Up_companyAddress2
Up_companyCountry2
timestamp
trigger on table2:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber 2
and timestamp=1
the problem is the application uses table1 details, and give reference to
"companyTaxNumber" in forms. When the company information is updated (rows
are not updated, inserted with a new companyNo via sequence) and inserted
into table1 from table2, the new forms will use the updated information.
However, old forms cannot use updated ones, since reports cannot be changed.
in conclusion,
table2 contains last updated company information, the initial information
before updates are not stored in table2
table 1 contains all the updates as new entries since forms that use these
entries cannot change the references. So, table1 gets bigger and datas
retrieved slowly.
Can you give me clear advices about the improvement techniques about indexes
and triggers? How can I handle this situation?
It is still not clear as to the logical seperation or business uses between
the two tables. Some clarification would be helpful.
Nevertheless, in both tables, I would use a UNIQUE CONSTRAINT for the
companyTaxNumber and Up_companyTaxNumber2 attributes instead of just a
UNIQUE INDEX. Now, all SQL Server tables should have a CLUSTERED INDEX
defined. Normally, I see users choose these IDENTITY surrogate primary
keys, or none at all. Both are bad choices. You have three candidates, or
a combination, that would server you better and, perhaps, assist you in your
querying issues: companyTaxNumber (my choice), companyName, or timestamp.
The other thing I noticed was your use of triggers. I would be careful
about this; I think I understand why you went this route instead of the
timestamp datatype, but it is serving the same purpose, just at the record
instead of the table level. That being said, you need to be careful when
using triggers because they execute T-SQL statements, that is, they are
SET-BASED, NOT ROW-BASED. A trigger is fired--ONECE--whenever an indicated
CRUD statement type is executed regardless of the number of rows impacted.
This means that your INSERTED and/or DELETED tables could be NULL, 1, or
many records with each trigger exectution. DO NOT ASSUME 1 RECORD.
In the trigger on Table2, the timestamp field is ambiguous as you are
joining Table1 and Table2, each with its own timestamp fields.
With the number of fields you have, and if you add the Clustered Index to
the correct attribute, I could see these sets of tables being able to
service several millions of records each with negligble query reporting
duration, dependent on your hardware, of course.
Are you joining these tables to others in your queries that would be
affecting your performance? Could you provide an example of a badly
performing query with the associated shcemea of any referenced tables.
Hope this helps.
Sincerely,
Anthony Thomas

"Sinem" <Sinem@.discussions.microsoft.com> wrote in message
news:2095306F-37BF-43F4-9ECC-C873E71ACAEF@.microsoft.com...
I have two tables that stores company information in a database. Since
retrieving data from the tables is too slow, the structure need to be
improved. The current structure of the tables is as follows:
First table contains
table1
companyNo : unique field that is created by sequence
companyTaxNumber:have a unique index
companyName : have an index, but not unique
companyAddress
companyCountry
timestamp
trigger on table1:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
Second Table Contains
table2
Up_companyNo2 : unique field that is created by sequence
Up_companyTaxNumber2:have a unique index
Up_companyName2 : have an index, but not unique
Up_companyAddress2
Up_companyCountry2
timestamp
trigger on table2:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber 2
and timestamp=1
the problem is the application uses table1 details, and give reference to
"companyTaxNumber" in forms. When the company information is updated (rows
are not updated, inserted with a new companyNo via sequence) and inserted
into table1 from table2, the new forms will use the updated information.
However, old forms cannot use updated ones, since reports cannot be changed.
in conclusion,
table2 contains last updated company information, the initial information
before updates are not stored in table2
table 1 contains all the updates as new entries since forms that use these
entries cannot change the references. So, table1 gets bigger and datas
retrieved slowly.
Can you give me clear advices about the improvement techniques about indexes
and triggers? How can I handle this situation?
|||Hi,
As I mentioned in the problem, I have two tables called table1 and table2. In
the application, there are numerous references in the fields belong to
table1,
while creating a report. After a report have been created, no changes can be
done on the reports. However, details on table1 can be changed and updated
occasionally. On each change, a new row inserted into table1. Old rows never
changes. By the way, while viewing old reports, references are not lost. This
results in decrease of the performance. Since table1 gets larger.
There is a problem with performance not the query. The real problem is how to
design these two tables in oracle database and find an appropriate solution?
Thanks for your convenience..
I'll post a piece of code in an hour..
sozmen
"AnthonyThomas" wrote:

> It is still not clear as to the logical seperation or business uses between
> the two tables. Some clarification would be helpful.
> Nevertheless, in both tables, I would use a UNIQUE CONSTRAINT for the
> companyTaxNumber and Up_companyTaxNumber2 attributes instead of just a
> UNIQUE INDEX. Now, all SQL Server tables should have a CLUSTERED INDEX
> defined. Normally, I see users choose these IDENTITY surrogate primary
> keys, or none at all. Both are bad choices. You have three candidates, or
> a combination, that would server you better and, perhaps, assist you in your
> querying issues: companyTaxNumber (my choice), companyName, or timestamp.
> The other thing I noticed was your use of triggers. I would be careful
> about this; I think I understand why you went this route instead of the
> timestamp datatype, but it is serving the same purpose, just at the record
> instead of the table level. That being said, you need to be careful when
> using triggers because they execute T-SQL statements, that is, they are
> SET-BASED, NOT ROW-BASED. A trigger is fired--ONECE--whenever an indicated
> CRUD statement type is executed regardless of the number of rows impacted.
> This means that your INSERTED and/or DELETED tables could be NULL, 1, or
> many records with each trigger exectution. DO NOT ASSUME 1 RECORD.
> In the trigger on Table2, the timestamp field is ambiguous as you are
> joining Table1 and Table2, each with its own timestamp fields.
> With the number of fields you have, and if you add the Clustered Index to
> the correct attribute, I could see these sets of tables being able to
> service several millions of records each with negligble query reporting
> duration, dependent on your hardware, of course.
> Are you joining these tables to others in your queries that would be
> affecting your performance? Could you provide an example of a badly
> performing query with the associated shcemea of any referenced tables.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Sinem" <Sinem@.discussions.microsoft.com> wrote in message
> news:2095306F-37BF-43F4-9ECC-C873E71ACAEF@.microsoft.com...
> I have two tables that stores company information in a database. Since
> retrieving data from the tables is too slow, the structure need to be
> improved. The current structure of the tables is as follows:
> First table contains
> table1
> companyNo : unique field that is created by sequence
> companyTaxNumber:have a unique index
> companyName : have an index, but not unique
> companyAddress
> companyCountry
> timestamp
> trigger on table1:
> if insertion occurs: timestamp:0
> if update occurs : increment timestamp by 1
> Second Table Contains
> table2
> Up_companyNo2 : unique field that is created by sequence
> Up_companyTaxNumber2:have a unique index
> Up_companyName2 : have an index, but not unique
> Up_companyAddress2
> Up_companyCountry2
> timestamp
> trigger on table2:
> if insertion occurs: timestamp:0
> if update occurs : increment timestamp by 1
> update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber 2
> and timestamp=1
> the problem is the application uses table1 details, and give reference to
> "companyTaxNumber" in forms. When the company information is updated (rows
> are not updated, inserted with a new companyNo via sequence) and inserted
> into table1 from table2, the new forms will use the updated information.
> However, old forms cannot use updated ones, since reports cannot be changed.
> in conclusion,
> table2 contains last updated company information, the initial information
> before updates are not stored in table2
> table 1 contains all the updates as new entries since forms that use these
> entries cannot change the references. So, table1 gets bigger and datas
> retrieved slowly.
>
> Can you give me clear advices about the improvement techniques about indexes
> and triggers? How can I handle this situation?
>
>

Indexing problem and slow data retrieve

I have two tables that stores company information in a database. Since
retrieving data from the tables is too slow, the structure need to be
improved. The current structure of the tables is as follows:
First table contains
table1
companyNo : unique field that is created by sequence
companyTaxNumber:have a unique index
companyName : have an index, but not unique
companyAddress
companyCountry
timestamp
trigger on table1:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
Second Table Contains
table2
Up_companyNo2 : unique field that is created by sequence
Up_companyTaxNumber2:have a unique index
Up_companyName2 : have an index, but not unique
Up_companyAddress2
Up_companyCountry2
timestamp
trigger on table2:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber2
and timestamp=1
the problem is the application uses table1 details, and give reference to
"companyTaxNumber" in forms. When the company information is updated (rows
are not updated, inserted with a new companyNo via sequence) and inserted
into table1 from table2, the new forms will use the updated information.
However, old forms cannot use updated ones, since reports cannot be changed.
in conclusion,
table2 contains last updated company information, the initial information
before updates are not stored in table2
table 1 contains all the updates as new entries since forms that use these
entries cannot change the references. So, table1 gets bigger and datas
retrieved slowly.
Can you give me clear advices about the improvement techniques about indexes
and triggers? How can I handle this situation?It is still not clear as to the logical seperation or business uses between
the two tables. Some clarification would be helpful.
Nevertheless, in both tables, I would use a UNIQUE CONSTRAINT for the
companyTaxNumber and Up_companyTaxNumber2 attributes instead of just a
UNIQUE INDEX. Now, all SQL Server tables should have a CLUSTERED INDEX
defined. Normally, I see users choose these IDENTITY surrogate primary
keys, or none at all. Both are bad choices. You have three candidates, or
a combination, that would server you better and, perhaps, assist you in your
querying issues: companyTaxNumber (my choice), companyName, or timestamp.
The other thing I noticed was your use of triggers. I would be careful
about this; I think I understand why you went this route instead of the
timestamp datatype, but it is serving the same purpose, just at the record
instead of the table level. That being said, you need to be careful when
using triggers because they execute T-SQL statements, that is, they are
SET-BASED, NOT ROW-BASED. A trigger is fired--ONECE--whenever an indicated
CRUD statement type is executed regardless of the number of rows impacted.
This means that your INSERTED and/or DELETED tables could be NULL, 1, or
many records with each trigger exectution. DO NOT ASSUME 1 RECORD.
In the trigger on Table2, the timestamp field is ambiguous as you are
joining Table1 and Table2, each with its own timestamp fields.
With the number of fields you have, and if you add the Clustered Index to
the correct attribute, I could see these sets of tables being able to
service several millions of records each with negligble query reporting
duration, dependent on your hardware, of course.
Are you joining these tables to others in your queries that would be
affecting your performance? Could you provide an example of a badly
performing query with the associated shcemea of any referenced tables.
Hope this helps.
Sincerely,
Anthony Thomas
"Sinem" <Sinem@.discussions.microsoft.com> wrote in message
news:2095306F-37BF-43F4-9ECC-C873E71ACAEF@.microsoft.com...
I have two tables that stores company information in a database. Since
retrieving data from the tables is too slow, the structure need to be
improved. The current structure of the tables is as follows:
First table contains
table1
companyNo : unique field that is created by sequence
companyTaxNumber:have a unique index
companyName : have an index, but not unique
companyAddress
companyCountry
timestamp
trigger on table1:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
Second Table Contains
table2
Up_companyNo2 : unique field that is created by sequence
Up_companyTaxNumber2:have a unique index
Up_companyName2 : have an index, but not unique
Up_companyAddress2
Up_companyCountry2
timestamp
trigger on table2:
if insertion occurs: timestamp:0
if update occurs : increment timestamp by 1
update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber2
and timestamp=1
the problem is the application uses table1 details, and give reference to
"companyTaxNumber" in forms. When the company information is updated (rows
are not updated, inserted with a new companyNo via sequence) and inserted
into table1 from table2, the new forms will use the updated information.
However, old forms cannot use updated ones, since reports cannot be changed.
in conclusion,
table2 contains last updated company information, the initial information
before updates are not stored in table2
table 1 contains all the updates as new entries since forms that use these
entries cannot change the references. So, table1 gets bigger and datas
retrieved slowly.
Can you give me clear advices about the improvement techniques about indexes
and triggers? How can I handle this situation?|||Hi,
As I mentioned in the problem, I have two tables called table1 and table2. In
the application, there are numerous references in the fields belong to
table1,
while creating a report. After a report have been created, no changes can be
done on the reports. However, details on table1 can be changed and updated
occasionally. On each change, a new row inserted into table1. Old rows never
changes. By the way, while viewing old reports, references are not lost. This
results in decrease of the performance. Since table1 gets larger.
There is a problem with performance not the query. The real problem is how to
design these two tables in oracle database and find an appropriate solution?
Thanks for your convenience..
I'll post a piece of code in an hour..
sozmen
"AnthonyThomas" wrote:
> It is still not clear as to the logical seperation or business uses between
> the two tables. Some clarification would be helpful.
> Nevertheless, in both tables, I would use a UNIQUE CONSTRAINT for the
> companyTaxNumber and Up_companyTaxNumber2 attributes instead of just a
> UNIQUE INDEX. Now, all SQL Server tables should have a CLUSTERED INDEX
> defined. Normally, I see users choose these IDENTITY surrogate primary
> keys, or none at all. Both are bad choices. You have three candidates, or
> a combination, that would server you better and, perhaps, assist you in your
> querying issues: companyTaxNumber (my choice), companyName, or timestamp.
> The other thing I noticed was your use of triggers. I would be careful
> about this; I think I understand why you went this route instead of the
> timestamp datatype, but it is serving the same purpose, just at the record
> instead of the table level. That being said, you need to be careful when
> using triggers because they execute T-SQL statements, that is, they are
> SET-BASED, NOT ROW-BASED. A trigger is fired--ONECE--whenever an indicated
> CRUD statement type is executed regardless of the number of rows impacted.
> This means that your INSERTED and/or DELETED tables could be NULL, 1, or
> many records with each trigger exectution. DO NOT ASSUME 1 RECORD.
> In the trigger on Table2, the timestamp field is ambiguous as you are
> joining Table1 and Table2, each with its own timestamp fields.
> With the number of fields you have, and if you add the Clustered Index to
> the correct attribute, I could see these sets of tables being able to
> service several millions of records each with negligble query reporting
> duration, dependent on your hardware, of course.
> Are you joining these tables to others in your queries that would be
> affecting your performance? Could you provide an example of a badly
> performing query with the associated shcemea of any referenced tables.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Sinem" <Sinem@.discussions.microsoft.com> wrote in message
> news:2095306F-37BF-43F4-9ECC-C873E71ACAEF@.microsoft.com...
> I have two tables that stores company information in a database. Since
> retrieving data from the tables is too slow, the structure need to be
> improved. The current structure of the tables is as follows:
> First table contains
> table1
> companyNo : unique field that is created by sequence
> companyTaxNumber:have a unique index
> companyName : have an index, but not unique
> companyAddress
> companyCountry
> timestamp
> trigger on table1:
> if insertion occurs: timestamp:0
> if update occurs : increment timestamp by 1
> Second Table Contains
> table2
> Up_companyNo2 : unique field that is created by sequence
> Up_companyTaxNumber2:have a unique index
> Up_companyName2 : have an index, but not unique
> Up_companyAddress2
> Up_companyCountry2
> timestamp
> trigger on table2:
> if insertion occurs: timestamp:0
> if update occurs : increment timestamp by 1
> update table1 where table1.companyTaxNumber=table2.Up_companyTaxNumber2
> and timestamp=1
> the problem is the application uses table1 details, and give reference to
> "companyTaxNumber" in forms. When the company information is updated (rows
> are not updated, inserted with a new companyNo via sequence) and inserted
> into table1 from table2, the new forms will use the updated information.
> However, old forms cannot use updated ones, since reports cannot be changed.
> in conclusion,
> table2 contains last updated company information, the initial information
> before updates are not stored in table2
> table 1 contains all the updates as new entries since forms that use these
> entries cannot change the references. So, table1 gets bigger and datas
> retrieved slowly.
>
> Can you give me clear advices about the improvement techniques about indexes
> and triggers? How can I handle this situation?
>
>