Hi!
I have the following master-destails table in my database:
www.virtuasoft.com.br/help.JPG
Please help in the following sqls:
1. How can i create a SQL that returns all the field from both tables? (i
have tried INNER JOIN, but, since Person and Company have different fields,
it doesnt work)
2. How can i create a SQL search both tables for all fields and add a column
telling which table it got the records form (
something like: SELECT *, 'Client' As Type FROM Client INNER JOIN
Person ON Client.PKid = Person.PKid
SELECT *, 'Company' As Type FROM Company
INNER JOIN Person ON Company.PKid = Person.PKid
)
Thanks a lot, Im having a really hard time trying to do it!
Bruno NOn Sat, 26 Feb 2005 16:12:04 -0300, Bruno N wrote:
>Hi!
>I have the following master-destails table in my database:
>www.virtuasoft.com.br/help.JPG
>Please help in the following sqls:
(snip)
Hi Bruno,
Has someone already answered your questions? I don't see any replies,
even though your post is already two days old, but maybe my newsreader
missed some messages?
Annyway, I don't really understand your data and requested output. I
think I'd understand better if you post the CREATE TABLE statements to
create your tables on my system, the INSERT statements to fill them with
some sample data and the output you'd like to get, based on that sample
data. Check out www.aspfaq.com/5006 for more info on how to present your
problem.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Most of us will not jump to a URL provided by a
stranger. And most of us would rather work with DDL instead of a .jpg.|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:v44721h7dkd7u38on929be9sjhe576uq2h@.
4ax.com...
> Has someone already answered your questions? I don't see any replies,
> even though your post is already two days old, but maybe my newsreader
> missed some messages?
No, this was posted on the 28th but it seems that the poster has a clock
problem.
Showing posts with label hii. Show all posts
Showing posts with label hii. Show all posts
Saturday, February 25, 2012
Monday, February 20, 2012
master..sysmessages
Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job will
be fired?
What table stores this information, so that I can run the query against it?
Thanks
When the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job will
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks
|||No I do not want to create alerts, I just want to find out where and in which
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in which
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that requires
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job will
be fired?
What table stores this information, so that I can run the query against it?
Thanks
When the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job will
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks
|||No I do not want to create alerts, I just want to find out where and in which
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in which
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>
|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that requires
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
master..sysmessages
Hi
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job wil
l
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job wi
ll
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in whic
h
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in whi
ch
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that require
s
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>
I am trying to create a job to monitor the error log in sql server. I
understand that the error types with their severity are in the table
master..sysmessages.
However, which table to I frequently query or monitor so that whenever the
error which I am looking for with specific severity, when occurs the job wil
l
be fired?
What table stores this information, so that I can run the query against it?
ThanksWhen the error occurs, you want it to be logged. That's how
you know the error happened. Otherwise, they aren't really
stored anywhere.
You can create an alert that will watch for the error and in
response to the error, it can execute a job. That's sounds
like what you are looking for. You can find more information
on alerts in SQL Server books online.
-Sue
On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
>Hi
>I am trying to create a job to monitor the error log in sql server. I
>understand that the error types with their severity are in the table
>master..sysmessages.
>However, which table to I frequently query or monitor so that whenever the
>error which I am looking for with specific severity, when occurs the job wi
ll
>be fired?
>What table stores this information, so that I can run the query against it?
>Thanks|||No I do not want to create alerts, I just want to find out where and in whic
h
table sql server stores the errors that occur?
"Sue Hoegemeier" wrote:
> When the error occurs, you want it to be logged. That's how
> you know the error happened. Otherwise, they aren't really
> stored anywhere.
> You can create an alert that will watch for the error and in
> response to the error, it can execute a job. That's sounds
> like what you are looking for. You can find more information
> on alerts in SQL Server books online.
> -Sue
> On Tue, 22 Feb 2005 14:03:09 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Not stored in any table.
-Sue
On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>No I do not want to create alerts, I just want to find out where and in whi
ch
>table sql server stores the errors that occur?
>
>"Sue Hoegemeier" wrote:
>|||I am using smtp and I cannot really use inbuilt sql alerts, as that requires
sql mail usage. Is there a way I can query the errors that occur so that I
can send out smtp e-mails?
Thanks
Paresh.
"Sue Hoegemeier" wrote:
> Not stored in any table.
> -Sue
> On Tue, 22 Feb 2005 14:57:08 -0800, "Pari"
> <Pari@.discussions.microsoft.com> wrote:
>
>|||Alerts don't require SQL mail - I've often used them with
smtp mail. You don't need to use the notification part of
the alert and can just setup the response to be executing a
job. Alerts require SQL Agent to be running but not SQL
Mail. You can also download an smtp alerter for SQL Server
from:
http://www.dbmaint.com/SmtpAlerter.asp
But if you just don't want to use alerts, you essentially
would need to do the same thing setting up an alert would do
- you'd have to set the error up to be logged and then read
the log to find the error. If you find the error in the log,
then send out the email.
To log the error, you would use sp_altermessage.
For example, If I wanted the error for invalid object name
to be logged (error 208), I would use:
sp_altermessage 208, 'WITH_LOG', 'true'
If you query sysmessages before and after executing this,
you'll notice the dlevel value will change to reflect the
logging of this error.
Then whenever that error occurs, it will be logged. So you
would write or use a utility to read the log and search for
the string: Error: 208
in the SQL Server log or in the description in the
Application Event log. You can then send out the mail,
execute the job to email if you find the string.
And in terms of if you define an alert, the occurrence is of
the error is indicated in msdb..sysalerts. Last occurrence
and occurrence count are stored in sysalerts. You could also
work with this table but that would require setting up an
alert. You could use sp_update_alert to reset any of the
values for the occurrence of the alert.
I can't think of what else you would have for options in how
to do this but that hopefully covers most of the ways or
gives you enough information on how you could handle it.
-Sue
On Wed, 23 Feb 2005 06:07:07 -0800, "Pari"
<Pari@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am using smtp and I cannot really use inbuilt sql alerts, as that require
s
>sql mail usage. Is there a way I can query the errors that occur so that I
>can send out smtp e-mails?
>Thanks
>Paresh.
>"Sue Hoegemeier" wrote:
>
Subscribe to:
Comments (Atom)