Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:
> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>

Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, reneHi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:

> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like t
o
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_nam
e'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get t
he
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related t
o
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>

Check Job-Status

Hi NG,
I defined a database maintenance task as a job. The job will normally be
executed by the SQL-Server according it's schedule. Furthermore I'd like to
start the job on demand.
Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
I have to check if it is already running. This could be checked with
sp_help_job.
The stored procedure sp_help_job returns multiple rowsets, how can I found
out if the job is still running or if it is idle?
DECLARE @.retval int
EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
@.execution_status=4 returns 4 rowsets. If a job is already running I get the
following error:
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
sa) refused because the job already has a pending request from User sa.
I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
the last rowset of SP sp_help_job .
Thanks for suggestions, rene
Hi
The column msdb.dbo.sysjobhistory.run_status will give you the status of the
job.
Integer
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Regards
Mike
"Re Fo" wrote:

> Hi NG,
> I defined a database maintenance task as a job. The job will normally be
> executed by the SQL-Server according it's schedule. Furthermore I'd like to
> start the job on demand.
> Before I execute my job with EXEC msdb..sp_start_job @.job_name='my_job_name'
> I have to check if it is already running. This could be checked with
> sp_help_job.
> The stored procedure sp_help_job returns multiple rowsets, how can I found
> out if the job is still running or if it is idle?
> DECLARE @.retval int
> EXEC @.retval = msdb..sp_help_job @.job_name='my_job_name',
> @.execution_status=4 returns 4 rowsets. If a job is already running I get the
> following error:
> Server: Msg 22022, Level 16, State 1, Line 0
> SQLServerAgent Error: Request to run job Job for 'my_job_name' (from User
> sa) refused because the job already has a pending request from User sa.
> I tried to check on @.@.ERROR or @.@.ROWCOUNT but both variables are related to
> the last rowset of SP sp_help_job .
> Thanks for suggestions, rene
>
>
>
>
sql

Check Constraint

I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.

What did I do wrong. Thanks for your help.Don't have access to bol and my machine...I'll figure it out tomorrow, unless you get it tonight

BUT

Look at LIKE [0-9][0-9][0-9][0-9][0-9]|||I guess u r trying to check a single digit as zip.
existing data in the table conflicts with the constraint u r trying to add
so try to alter table with nocheck option.
or else drop the column and add it in which ever way u want.
------------------------
alter table test
with nocheck
add constraint ck_test
check (zip between '0' and '9')
------------------------

Originally posted by ttkh
I have an existing table with field ZIPCODE defined as VARCHAR(5).
I want to add a check constraint to allow only number from 0 to 9.

This is what I did but it gave me error:

alter table test
with check
add constraint ck_test
check (zip between '0' and '9')

error:

ALTER TABLE statement conflicted with COLUMN CHECK constraint 'ck_test'.
The conflict occurred in database 'lahdProperty', table 'test', column 'Zip'.

What did I do wrong. Thanks for your help.

Charting...jump to report

I have a chart with a number of categories, and a Jump To Report action
defined. The Jump to report is a table with details of the data.
I would like to set it up sothat the user can click on a category, and only
the details of that category are returned in the Jump To Report. Is there
any way of setting it up like this.
I have everything working, except I am getting all records in the detail
report, not only records from the selected category.
Thanks in advance
AndreYou will need to add a parameter on the detail report and provide the
current category id on the JumpToReport parameter page of the parent report.
Also, in the detail report you will need to have a parameterized query to
only retrieve the data based on the category id.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:E49EB95A-156A-47A8-AE60-D898B8D43F93@.microsoft.com...
>I have a chart with a number of categories, and a Jump To Report action
> defined. The Jump to report is a table with details of the data.
> I would like to set it up sothat the user can click on a category, and
> only
> the details of that category are returned in the Jump To Report. Is there
> any way of setting it up like this.
> I have everything working, except I am getting all records in the detail
> report, not only records from the selected category.
> Thanks in advance
> Andre

chart and localization

I'm not able to define the chart y-axis maximum and minimun to be fixed
0,2 and -0,2. I have defined the report language as finnish and in
editor the everything works fine but after deploying and in preview mode
reporting services changes the limits to -15 and 15. I suppose the
problem is the the ',' decimal separator. I have set IE and asp.net
local to finnish and the OS (XP PRO EN) default locals to the use
finnish number formats. All other formattings in the report are just
fine, so the only problem is the chart. Does anyone know how to fix this
problem?I know this might be a stupid question but have you tried setting it to
0.2 instead of 0,2?
I had a few issues with number formatting in 2000 too, when trying to
use Norwegian formats. I ended up using US/UK numbers then applying
format codes for displaying.
There are also significant issues in 2005 data and preview tab with
European date formats (dd/mm/yyyy). It works fine when deployed.
The point I am aiming at is that the preview tab is not that good an
indicator of how the report may behave when deployed, primarily because
there are a number of issues when using non-US locales.
Regards
Chris
Pyry Grönholm wrote:
> I'm not able to define the chart y-axis maximum and minimun to be
> fixed 0,2 and -0,2. I have defined the report language as finnish and
> in editor the everything works fine but after deploying and in
> preview mode reporting services changes the limits to -15 and 15. I
> suppose the problem is the the ',' decimal separator. I have set IE
> and asp.net local to finnish and the OS (XP PRO EN) default locals to
> the use finnish number formats. All other formattings in the report
> are just fine, so the only problem is the chart. Does anyone know how
> to fix this problem?|||Yes I have.
The behavior is quite strange that way as well.
I'd like to have the scale between 0.2 and -0.2. If I set these values
the scale end up to be 0.2 and -1.0. So the negative side somehow fails.
If I set range from 0,2 to -0,2 I'll get a range from 2 to -2 so
probably the '.' is anyway the corrent decimal separator.
Does anyone have idea why I'm not able to set the negative decimal value
to minimun?
-Pyry GRönholm
Chris wrote:
> I know this might be a stupid question but have you tried setting it to
> 0.2 instead of 0,2?
> I had a few issues with number formatting in 2000 too, when trying to
> use Norwegian formats. I ended up using US/UK numbers then applying
> format codes for displaying.
> There are also significant issues in 2005 data and preview tab with
> European date formats (dd/mm/yyyy). It works fine when deployed.
> The point I am aiming at is that the preview tab is not that good an
> indicator of how the report may behave when deployed, primarily because
> there are a number of issues when using non-US locales.
> Regards
> Chris
>
> Pyry Grönholm wrote:
>
>>I'm not able to define the chart y-axis maximum and minimun to be
>>fixed 0,2 and -0,2. I have defined the report language as finnish and
>>in editor the everything works fine but after deploying and in
>>preview mode reporting services changes the limits to -15 and 15. I
>>suppose the problem is the the ',' decimal separator. I have set IE
>>and asp.net local to finnish and the OS (XP PRO EN) default locals to
>>the use finnish number formats. All other formattings in the report
>>are just fine, so the only problem is the chart. Does anyone know how
>>to fix this problem?
>
>

Characters might change in text field values?

I am using a SQL Server 2000 database as the base of my application.
One of the tables stores saved user defined query information. I then use the values stored here to recreates user queries for data.
The problem is that when I get back to the table to retrieve data some characters are changed which leads to some application errors.
For example let's say my user wants to serach information based on info location. So he chosses to see all the results from distinct cities (Lisboa, Faro, Santarém and Setúbal). He does and he wants to retrieve the same info again when he returns to the
app, so he saves the query (and my application stores the select statement on a table field (this field is a text field)). When he returns to the app, he retrieves his sabed query and I get an SQL error, stating problems in the SQL statement. When I get
there to check for the statement I get a couple of '&' characters on strange places.
I am shure that is not a programming syntax error since I check the select statement just before it is stored.
Any idea what might be causing this?
I am using SQL Server 2000 SP3 english settings, Windows xp sp1 for the client app english version with portuguese locale settings.
What is the datatype for the column in which you are storing these values?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Hi. It's using the char datatype. The size is growing as needed.
"Vikram Jayaram [MS]" wrote:

> What is the datatype for the column in which you are storing these values?
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>
|||Have you tried using nvarchar or nchar? Same results?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Same results.
"Vikram Jayaram [MS]" wrote:

> Have you tried using nvarchar or nchar? Same results?
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>

Characters might change in text field values?

I am using a SQL Server 2000 database as the base of my application.
One of the tables stores saved user defined query information. I then use th
e values stored here to recreates user queries for data.
The problem is that when I get back to the table to retrieve data some chara
cters are changed which leads to some application errors.
For example let's say my user wants to serach information based on info loca
tion. So he chosses to see all the results from distinct cities (Lisboa, Far
o, Santarém and Setúbal). He does and he wants to retrieve the same info a
gain when he returns to the
app, so he saves the query (and my application stores the select statement o
n a table field (this field is a text field)). When he returns to the app, h
e retrieves his sabed query and I get an SQL error, stating problems in the
SQL statement. When I get
there to check for the statement I get a couple of '&' characters on strange
places.
I am shure that is not a programming syntax error since I check the select s
tatement just before it is stored.
Any idea what might be causing this?
I am using SQL Server 2000 SP3 english settings, Windows XP sp1 for the clie
nt app english version with portuguese locale settings.What is the datatype for the column in which you are storing these values?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Hi. It's using the char datatype. The size is growing as needed.
"Vikram Jayaram [MS]" wrote:

> What is the datatype for the column in which you are storing these values?
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>|||Have you tried using nvarchar or nchar? Same results?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Same results.
"Vikram Jayaram [MS]" wrote:

> Have you tried using nvarchar or nchar? Same results?
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
>

Changing user defined data type's data type

Hi,

I have a user defined data type which is called DEmployeeName,

it's length is varchar(20), how do i change it into varchar(30) without droping it?

I'm using SQL server 2005.

Thanks in advance.. You will have to remove all references to change it.

HTH; jens Suessmeyer.

http://www.sqlserver2005.de