Hi-
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>
Friday, March 30, 2012
How do I move a web assistant job?
Hi-
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>sql
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>sql
How do I move a project between machines?
Hi,
I'm an SQL Server novice.
After developing some websites (VS 2005 VWD), my machine started to crumble. (e.g, SQL Server won't uninstall and lots of other software problems)
So I bought a new machine and set up a clean installation of SQL Server 2005 Standard and Visual Studio 2005 standard.
I copied my old "projects" folder to the new machine but I cannot use it.
1: When I try to import I get a "cannot import external files" error.
2: When I click on a project, it launches VS 2005 and I can edit etc.
However, when I try to run the website in VS 2005, I get the following error.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) an error
Please can someone let me know how I can get my "old" projects running in my new environment?
Thanks
Ken
Has your sql server name or instance changed if so you will have to change your dataset to represent the new server.|||
Hi Ray,
Thanks for the reply.
Yes - I'm using a completely new setup. New server name and new instance name.
But I'm still struggling to get to grips with the management console. I bought several books on SQL Server 2005 and spent lots of time ploughing through the zillions of help files but I have not yet found a "How to" that meets my level of need to understand what I'm doing with SQL Serverr Management Studio.
So, when you say "change the dataset" I don't know what steps to take to do that. If you have a minute, I would very much appreciate some basic guide that shows me how to "change the dataset to represent the new server".
Its hard being a novice!
Ken
|||In your visual studios project you should have set up your project to connect to the database this is a dataset. It is a setup for a connection to the database from visual studios. You should look in your project, and should be somewhere in your solution depending on which type you are creating.|||Hi Ray,
Thanks for your comment.
One of the problems I'm experienceing is that most of the well intended advice is couched in metaphorical terms rather than in "Press this then click that" terms.
Let's use the sample SQL Express personal website starter kit as an example.
Which DB?: In the App_Data folder thare are two .mdf files: ASPNETDB.MDF and Personal.mdf I know that the application needs both of these DB's. If I change the connection string, do I have to do it for both of these DB's ?
Connection String?: I have been floundering about in the help files without finding anything that I have found to be helpful. Where do I find the "connection string" to these DB's? what does a connection string look like? , How do I change it? What effect does it have on the rest of the project? Do I have to do anything else to make the app run under SQL Server Standard?
If I can get answers to these questions it will help me to get moving forward.
Thanks for your help.
Ken
How do I move a project between machines?
Hi,
I'm an SQL Server novice.
After developing some websites (VS 2005 VWD), my machine started to crumble. (e.g, SQL Server won't uninstall and lots of other software problems)
So I bought a new machine and set up a clean installation of SQL Server 2005 Standard and Visual Studio 2005 standard.
I copied my old "projects" folder to the new machine but I cannot use it.
1: When I try to import I get a "cannot import external files" error.
2: When I click on a project, it launches VS 2005 and I can edit etc.
However, when I try to run the website in VS 2005, I get the following error.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) an error
Please can someone let me know how I can get my "old" projects running in my new environment?
Thanks
Ken
Has your sql server name or instance changed if so you will have to change your dataset to represent the new server.|||
Hi Ray,
Thanks for the reply.
Yes - I'm using a completely new setup. New server name and new instance name.
But I'm still struggling to get to grips with the management console. I bought several books on SQL Server 2005 and spent lots of time ploughing through the zillions of help files but I have not yet found a "How to" that meets my level of need to understand what I'm doing with SQL Serverr Management Studio.
So, when you say "change the dataset" I don't know what steps to take to do that. If you have a minute, I would very much appreciate some basic guide that shows me how to "change the dataset to represent the new server".
Its hard being a novice!
Ken
|||In your visual studios project you should have set up your project to connect to the database this is a dataset. It is a setup for a connection to the database from visual studios. You should look in your project, and should be somewhere in your solution depending on which type you are creating.|||Hi Ray,
Thanks for your comment.
One of the problems I'm experienceing is that most of the well intended advice is couched in metaphorical terms rather than in "Press this then click that" terms.
Let's use the sample SQL Express personal website starter kit as an example.
Which DB?: In the App_Data folder thare are two .mdf files: ASPNETDB.MDF and Personal.mdf I know that the application needs both of these DB's. If I change the connection string, do I have to do it for both of these DB's ?
Connection String?: I have been floundering about in the help files without finding anything that I have found to be helpful. Where do I find the "connection string" to these DB's? what does a connection string look like? , How do I change it? What effect does it have on the rest of the project? Do I have to do anything else to make the app run under SQL Server Standard?
If I can get answers to these questions it will help me to get moving forward.
Thanks for your help.
Ken
How do I move a database I have developed on my local server (2005) to my client?
Hi all. Probably a simple thing to do but I need to know how to do this. I
have to move the database I created in 2005 Enterprise to 2005 Express on my
client site. Do I just do a back up and burn to a CD and after installing
2005 Express restore the backup? Don't I need the .mdf and the .ldf files
or does the backup procedure in the Studio backup both? I set it to backup
the Database.
Regards,
RobertYou can A) drop the database, copy the mdf and ldf files to CD or
somewhere that you can move them and at the client site attach them or
b) do a backup of the database and restore at the client site. Did you
use any Enterprise features that Express may not support?
Robert Johnson wrote:
> Hi all. Probably a simple thing to do but I need to know how to do this. I
> have to move the database I created in 2005 Enterprise to 2005 Express on my
> client site. Do I just do a back up and burn to a CD and after installing
> 2005 Express restore the backup? Don't I need the .mdf and the .ldf files
> or does the backup procedure in the Studio backup both? I set it to backup
> the Database.
> Regards,
> Robert
>|||I don't believe so.. Just added tables and one view. No triggers, functions
or stuff like that. Relationships between tables and constraints are in
place. The client has less than 5 concurent users so Express should work
fine for him. It's a very small client and small database needs.
Interesting idea about detaching and copy the mdf and ldf to CD. Didn't
think that would work. I'll give that a shot.
Regards and Thanks
Robert
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
> You can A) drop the database, copy the mdf and ldf files to CD or
> somewhere that you can move them and at the client site attach them or b)
> do a backup of the database and restore at the client site. Did you use
> any Enterprise features that Express may not support?
>
> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert|||I had to ask <G>. I use the attach/detach all the time. After I've
copied the ldf and mdf I reattach to the original db. That's how I move
from our test to production server.
Robert Johnson wrote:
> I don't believe so.. Just added tables and one view. No triggers, functions
> or stuff like that. Relationships between tables and constraints are in
> place. The client has less than 5 concurent users so Express should work
> fine for him. It's a very small client and small database needs.
> Interesting idea about detaching and copy the mdf and ldf to CD. Didn't
> think that would work. I'll give that a shot.
> Regards and Thanks
> Robert
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
>> You can A) drop the database, copy the mdf and ldf files to CD or
>> somewhere that you can move them and at the client site attach them or b)
>> do a backup of the database and restore at the client site. Did you use
>> any Enterprise features that Express may not support?
>>
>> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert
>|||Thanks again Brett. I'll test it out tomorrow on site. If your ears are
burning...lol.
Robert
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:eKtpB4UBIHA.4836@.TK2MSFTNGP06.phx.gbl...
>I had to ask <G>. I use the attach/detach all the time. After I've copied
>the ldf and mdf I reattach to the original db. That's how I move from our
>test to production server.
> Robert Johnson wrote:
>> I don't believe so.. Just added tables and one view. No triggers,
>> functions or stuff like that. Relationships between tables and
>> constraints are in place. The client has less than 5 concurent users so
>> Express should work fine for him. It's a very small client and small
>> database needs. Interesting idea about detaching and copy the mdf and ldf
>> to CD. Didn't think that would work. I'll give that a shot.
>> Regards and Thanks
>> Robert
>> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
>> news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
>> You can A) drop the database, copy the mdf and ldf files to CD or
>> somewhere that you can move them and at the client site attach them or
>> b) do a backup of the database and restore at the client site. Did you
>> use any Enterprise features that Express may not support?
>>
>> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do
>> this. I have to move the database I created in 2005 Enterprise to 2005
>> Express on my client site. Do I just do a back up and burn to a CD and
>> after installing 2005 Express restore the backup? Don't I need the
>> .mdf and the .ldf files or does the backup procedure in the Studio
>> backup both? I set it to backup the Database.
>> Regards,
>> Robert
>>|||Hello Robert,
When you take a backup of a database, you do not need mdf or ldf files of
your database. *.BAK files (which is the backup file) contains all that
files.
This is a comman way used to copy databases or as Brett mentioned, you can
attach\detach your database files.
For additional info: Attaching and Detaching a database is the fastest
method to copy or move a database. However, it takes your database offline
and this would not be good for production environments. So, backing up a
database does not cause a problem like this.
Ekrem Önsoy
"Robert Johnson" <johnson_r@.sbcglobal.net> wrote in message
news:%23FPMBlUBIHA.4836@.TK2MSFTNGP06.phx.gbl...
> Hi all. Probably a simple thing to do but I need to know how to do this. I
> have to move the database I created in 2005 Enterprise to 2005 Express on
> my client site. Do I just do a back up and burn to a CD and after
> installing 2005 Express restore the backup? Don't I need the .mdf and the
> .ldf files or does the backup procedure in the Studio backup both? I set
> it to backup the Database.
> Regards,
> Robert
>|||Thanks Ekrem. I wasn't sure if it had all that I needed.
Regards,
Robert
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:F98D9090-F8AE-4DF2-AEFC-30898DE47197@.microsoft.com...
> Hello Robert,
>
> When you take a backup of a database, you do not need mdf or ldf files of
> your database. *.BAK files (which is the backup file) contains all that
> files.
> This is a comman way used to copy databases or as Brett mentioned, you can
> attach\detach your database files.
> For additional info: Attaching and Detaching a database is the fastest
> method to copy or move a database. However, it takes your database offline
> and this would not be good for production environments. So, backing up a
> database does not cause a problem like this.
>
> --
> Ekrem Önsoy
>
> "Robert Johnson" <johnson_r@.sbcglobal.net> wrote in message
> news:%23FPMBlUBIHA.4836@.TK2MSFTNGP06.phx.gbl...
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert
>>
>
have to move the database I created in 2005 Enterprise to 2005 Express on my
client site. Do I just do a back up and burn to a CD and after installing
2005 Express restore the backup? Don't I need the .mdf and the .ldf files
or does the backup procedure in the Studio backup both? I set it to backup
the Database.
Regards,
RobertYou can A) drop the database, copy the mdf and ldf files to CD or
somewhere that you can move them and at the client site attach them or
b) do a backup of the database and restore at the client site. Did you
use any Enterprise features that Express may not support?
Robert Johnson wrote:
> Hi all. Probably a simple thing to do but I need to know how to do this. I
> have to move the database I created in 2005 Enterprise to 2005 Express on my
> client site. Do I just do a back up and burn to a CD and after installing
> 2005 Express restore the backup? Don't I need the .mdf and the .ldf files
> or does the backup procedure in the Studio backup both? I set it to backup
> the Database.
> Regards,
> Robert
>|||I don't believe so.. Just added tables and one view. No triggers, functions
or stuff like that. Relationships between tables and constraints are in
place. The client has less than 5 concurent users so Express should work
fine for him. It's a very small client and small database needs.
Interesting idea about detaching and copy the mdf and ldf to CD. Didn't
think that would work. I'll give that a shot.
Regards and Thanks
Robert
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
> You can A) drop the database, copy the mdf and ldf files to CD or
> somewhere that you can move them and at the client site attach them or b)
> do a backup of the database and restore at the client site. Did you use
> any Enterprise features that Express may not support?
>
> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert|||I had to ask <G>. I use the attach/detach all the time. After I've
copied the ldf and mdf I reattach to the original db. That's how I move
from our test to production server.
Robert Johnson wrote:
> I don't believe so.. Just added tables and one view. No triggers, functions
> or stuff like that. Relationships between tables and constraints are in
> place. The client has less than 5 concurent users so Express should work
> fine for him. It's a very small client and small database needs.
> Interesting idea about detaching and copy the mdf and ldf to CD. Didn't
> think that would work. I'll give that a shot.
> Regards and Thanks
> Robert
> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
> news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
>> You can A) drop the database, copy the mdf and ldf files to CD or
>> somewhere that you can move them and at the client site attach them or b)
>> do a backup of the database and restore at the client site. Did you use
>> any Enterprise features that Express may not support?
>>
>> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert
>|||Thanks again Brett. I'll test it out tomorrow on site. If your ears are
burning...lol.
Robert
"Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
news:eKtpB4UBIHA.4836@.TK2MSFTNGP06.phx.gbl...
>I had to ask <G>. I use the attach/detach all the time. After I've copied
>the ldf and mdf I reattach to the original db. That's how I move from our
>test to production server.
> Robert Johnson wrote:
>> I don't believe so.. Just added tables and one view. No triggers,
>> functions or stuff like that. Relationships between tables and
>> constraints are in place. The client has less than 5 concurent users so
>> Express should work fine for him. It's a very small client and small
>> database needs. Interesting idea about detaching and copy the mdf and ldf
>> to CD. Didn't think that would work. I'll give that a shot.
>> Regards and Thanks
>> Robert
>> "Brett I. Holcomb" <brettholcomb@.bellsouth.net> wrote in message
>> news:unSxUqUBIHA.3780@.TK2MSFTNGP05.phx.gbl...
>> You can A) drop the database, copy the mdf and ldf files to CD or
>> somewhere that you can move them and at the client site attach them or
>> b) do a backup of the database and restore at the client site. Did you
>> use any Enterprise features that Express may not support?
>>
>> Robert Johnson wrote:
>> Hi all. Probably a simple thing to do but I need to know how to do
>> this. I have to move the database I created in 2005 Enterprise to 2005
>> Express on my client site. Do I just do a back up and burn to a CD and
>> after installing 2005 Express restore the backup? Don't I need the
>> .mdf and the .ldf files or does the backup procedure in the Studio
>> backup both? I set it to backup the Database.
>> Regards,
>> Robert
>>|||Hello Robert,
When you take a backup of a database, you do not need mdf or ldf files of
your database. *.BAK files (which is the backup file) contains all that
files.
This is a comman way used to copy databases or as Brett mentioned, you can
attach\detach your database files.
For additional info: Attaching and Detaching a database is the fastest
method to copy or move a database. However, it takes your database offline
and this would not be good for production environments. So, backing up a
database does not cause a problem like this.
Ekrem Önsoy
"Robert Johnson" <johnson_r@.sbcglobal.net> wrote in message
news:%23FPMBlUBIHA.4836@.TK2MSFTNGP06.phx.gbl...
> Hi all. Probably a simple thing to do but I need to know how to do this. I
> have to move the database I created in 2005 Enterprise to 2005 Express on
> my client site. Do I just do a back up and burn to a CD and after
> installing 2005 Express restore the backup? Don't I need the .mdf and the
> .ldf files or does the backup procedure in the Studio backup both? I set
> it to backup the Database.
> Regards,
> Robert
>|||Thanks Ekrem. I wasn't sure if it had all that I needed.
Regards,
Robert
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:F98D9090-F8AE-4DF2-AEFC-30898DE47197@.microsoft.com...
> Hello Robert,
>
> When you take a backup of a database, you do not need mdf or ldf files of
> your database. *.BAK files (which is the backup file) contains all that
> files.
> This is a comman way used to copy databases or as Brett mentioned, you can
> attach\detach your database files.
> For additional info: Attaching and Detaching a database is the fastest
> method to copy or move a database. However, it takes your database offline
> and this would not be good for production environments. So, backing up a
> database does not cause a problem like this.
>
> --
> Ekrem Önsoy
>
> "Robert Johnson" <johnson_r@.sbcglobal.net> wrote in message
> news:%23FPMBlUBIHA.4836@.TK2MSFTNGP06.phx.gbl...
>> Hi all. Probably a simple thing to do but I need to know how to do this.
>> I have to move the database I created in 2005 Enterprise to 2005 Express
>> on my client site. Do I just do a back up and burn to a CD and after
>> installing 2005 Express restore the backup? Don't I need the .mdf and
>> the .ldf files or does the backup procedure in the Studio backup both? I
>> set it to backup the Database.
>> Regards,
>> Robert
>>
>
How do I monitor my SQL JOB?
Hullo. I have a SQL SCHEDULER Job that keeps hanging.
1) How do I monitor this? I would like to have another Sql Job monitor the first one hourly, and send me an email if it is hanging. Isn't there a system table that tells me a job status?
1a) Once I find the job, how can I "Stop it" automatically?
2) Is there a way to have the Job stop itself it it runs for more than 20 minutes?
Thanks.
~LeNo one Knows...?
~Le
"No one knows? No one Knows if the Hot Tea is Hot or Cold...?"
"Is it Iced Tea?"
"NO!"
"Well then, I have no idea."|||Well, I have a suspision that most dba's would probably want to spend their time investigating the cause of a 'hanging' job rather than developing other jobs to manage such a situation. I think it's more correct to say that a job step stays 'in-progress'. The only time I have seen this is in relation to replication jobs that seem to have a tendency to report being continiously 'in-progress'. Apart from that 'feature', I can't remember a situation where a job would routinely misbehave without a good reason.
Have you looked at the job history to see which step the job is failing on? I know it's not much use if it's a one step job but I thought I would mention it anyway. Either way, look at the code in the step that is staying 'in-progress' and see if you can isolate it further. Perhaps add a few audit steps to write out to a table (like print statements if you were running code via QA). The objective should be to keep drilling down until you've found the cause.
Perhaps post more detail about the code that is staying in-progress and more useful feedback will come your way.
Clive|||Well, I have a suspision that most dba's would probably want to spend their time investigating the cause of a 'hanging' job rather than developing other jobs to manage such a situation. I think it's more correct to say that a job step stays 'in-progress'. The only time I have seen this is in relation to replication jobs that seem to have a tendency to report being continiously 'in-progress'. Apart from that 'feature', I can't remember a situation where a job would routinely misbehave without a good reason.
Have you looked at the job history to see which step the job is failing on? I know it's not much use if it's a one step job but I thought I would mention it anyway. Either way, look at the code in the step that is staying 'in-progress' and see if you can isolate it further. Perhaps add a few audit steps to write out to a table (like print statements if you were running code via QA). The objective should be to keep drilling down until you've found the cause.
Perhaps post more detail about the code that is staying in-progress and more useful feedback will come your way.
Clive
LOL. You are right, the better thing to do would be to stop it from hanging in the first place! The SQL Agent fires off a VB App that I wrote. The VB App is hanging on the the custom PGP command. But it does not happen often. The PGP 8 Service itself seems to be hanging about every 13-14 days, which in turn causes my VB App to hang, which causes the SQL Agent to hang.
Since there is a code freeze, I cannot edit the VB app to trap the error at the moment. So I just want to monitor the SQL Agent jobs, and if possible stop it if it is hanging.
`Le|||You can find what jobs are running by looking in the sysprocesses table for jobs running:
select *
from master..sysprocesses
where program_name like 'SQLAgent%Job%'
The problem is that the job_id you get: 0xC778DE2759DD354B9C219B301886EA43
equates to the binary version of the job_id stored in the sysjobs table.
Still if you know you shouldn't have any jobs running at all except the one you are checking on to see if it hung, you should be able to use this select to get the SPID of the hanging job and kill it if need be until you can figure out what's causing it to fail.
1) How do I monitor this? I would like to have another Sql Job monitor the first one hourly, and send me an email if it is hanging. Isn't there a system table that tells me a job status?
1a) Once I find the job, how can I "Stop it" automatically?
2) Is there a way to have the Job stop itself it it runs for more than 20 minutes?
Thanks.
~LeNo one Knows...?
~Le
"No one knows? No one Knows if the Hot Tea is Hot or Cold...?"
"Is it Iced Tea?"
"NO!"
"Well then, I have no idea."|||Well, I have a suspision that most dba's would probably want to spend their time investigating the cause of a 'hanging' job rather than developing other jobs to manage such a situation. I think it's more correct to say that a job step stays 'in-progress'. The only time I have seen this is in relation to replication jobs that seem to have a tendency to report being continiously 'in-progress'. Apart from that 'feature', I can't remember a situation where a job would routinely misbehave without a good reason.
Have you looked at the job history to see which step the job is failing on? I know it's not much use if it's a one step job but I thought I would mention it anyway. Either way, look at the code in the step that is staying 'in-progress' and see if you can isolate it further. Perhaps add a few audit steps to write out to a table (like print statements if you were running code via QA). The objective should be to keep drilling down until you've found the cause.
Perhaps post more detail about the code that is staying in-progress and more useful feedback will come your way.
Clive|||Well, I have a suspision that most dba's would probably want to spend their time investigating the cause of a 'hanging' job rather than developing other jobs to manage such a situation. I think it's more correct to say that a job step stays 'in-progress'. The only time I have seen this is in relation to replication jobs that seem to have a tendency to report being continiously 'in-progress'. Apart from that 'feature', I can't remember a situation where a job would routinely misbehave without a good reason.
Have you looked at the job history to see which step the job is failing on? I know it's not much use if it's a one step job but I thought I would mention it anyway. Either way, look at the code in the step that is staying 'in-progress' and see if you can isolate it further. Perhaps add a few audit steps to write out to a table (like print statements if you were running code via QA). The objective should be to keep drilling down until you've found the cause.
Perhaps post more detail about the code that is staying in-progress and more useful feedback will come your way.
Clive
LOL. You are right, the better thing to do would be to stop it from hanging in the first place! The SQL Agent fires off a VB App that I wrote. The VB App is hanging on the the custom PGP command. But it does not happen often. The PGP 8 Service itself seems to be hanging about every 13-14 days, which in turn causes my VB App to hang, which causes the SQL Agent to hang.
Since there is a code freeze, I cannot edit the VB app to trap the error at the moment. So I just want to monitor the SQL Agent jobs, and if possible stop it if it is hanging.
`Le|||You can find what jobs are running by looking in the sysprocesses table for jobs running:
select *
from master..sysprocesses
where program_name like 'SQLAgent%Job%'
The problem is that the job_id you get: 0xC778DE2759DD354B9C219B301886EA43
equates to the binary version of the job_id stored in the sysjobs table.
Still if you know you shouldn't have any jobs running at all except the one you are checking on to see if it hung, you should be able to use this select to get the SPID of the hanging job and kill it if need be until you can figure out what's causing it to fail.
How do I monitor MSDE performance
Hi there,
How can I monitor the MSDE performance. I read about it
on the MSDN site but was not clear what tool to use.
Can u help?
Thanks,
FP
hi FP,
FP wrote:
> Hi there,
> How can I monitor the MSDE performance. I read about it
> on the MSDN site but was not clear what tool to use.
> Can u help?
depending on what you want to monitor, you can even use the System Monitor,
adding your preferred counters, like
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
How can I monitor the MSDE performance. I read about it
on the MSDN site but was not clear what tool to use.
Can u help?
Thanks,
FP
hi FP,
FP wrote:
> Hi there,
> How can I monitor the MSDE performance. I read about it
> on the MSDN site but was not clear what tool to use.
> Can u help?
depending on what you want to monitor, you can even use the System Monitor,
adding your preferred counters, like
http://www.windowsitpro.com/Articles...layTab=Article
http://www.windowsitpro.com/Articles...layTab=Article
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
Subscribe to:
Posts (Atom)