Hi,
I need to export my reports to Excel, and I've encountered strange layout problems, as below.
Problem 1: Looks ok in report, looks crazy in Excel
----
I understand that data regions within table and matrices are not supported (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp).. so I have a matrix in a rectangle(instead of a table), and this rectangle within a list. Report generates this fine,.. nice and neat.., butonce exported to Excel, the layout is messy and unintelligible. One report column can be represented by 1 and some even 10 cells. Does anyone know what is the cause of this? Perhaps the use of lists?
Problem 2: What's #NAME?
--
I have a column X in report that a calculated value, and formula is
=(ReportItems!textbox213.Value / ReportItems!textbox211.Value) ...
textbox213 and textbox211 both have values from sums of other field items. So column X has a proper value when generated, but once exported, it says #NAME in the Excel column (error i suppose). When I click on #NAME, it says =(_146/_144) <-- what does this mean?
I would really appreciate anyone's help on this, since i've spend loads of time (too much!) on this.. Seems like what I see in the report is not what I get in Excel! Anyway, thank you in advance.
Best regards,
Julie
--
Message posted via http://www.sqlmonster.comIt's recommended to use tables rather than rectangles and lists when
exporting to Excel. As the link you provided describes, you get
unpredictable results when using anything other than tables or matrixes.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JTay via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d285b020b8244ddeb4394efec2c3fb23@.SQLMonster.com...
> Hi,
> I need to export my reports to Excel, and I've encountered strange layout
> problems, as below.
> Problem 1: Looks ok in report, looks crazy in Excel
> ----
> I understand that data regions within table and matrices are not supported
> (see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp)..
> so I have a matrix in a rectangle(instead of a table), and this rectangle
> within a list. Report generates this fine,.. nice and neat.., butonce
> exported to Excel, the layout is messy and unintelligible. One report
> column can be represented by 1 and some even 10 cells. Does anyone know
> what is the cause of this? Perhaps the use of lists?
> Problem 2: What's #NAME?
> --
> I have a column X in report that a calculated value, and formula is
> =(ReportItems!textbox213.Value / ReportItems!textbox211.Value) ...
> textbox213 and textbox211 both have values from sums of other field items.
> So column X has a proper value when generated, but once exported, it says
> #NAME in the Excel column (error i suppose). When I click on #NAME, it
> says =(_146/_144) <-- what does this mean?
> I would really appreciate anyone's help on this, since i've spend loads of
> time (too much!) on this.. Seems like what I see in the report is not what
> I get in Excel! Anyway, thank you in advance.
> Best regards,
> Julie
> --
> Message posted via http://www.sqlmonster.com|||I can't use tables to encapsulate the matrix. If I do put the matrix within the table, it would say "Data Regions within table/matrix cells are ignored" on Excel when exported. This is a well known issue and is currently not supported, even in SP1.
However, I managed to get it to look slightly better in Excel, but after *much* manipulation on the alignment of the matrices and lists...
--
Message posted via http://www.sqlmonster.com|||Okay, I get it, you're right, there's no easy answer -- just lots of
tweaking layout.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JTay via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7e935d41037d4da694ce1277e681894a@.SQLMonster.com...
>I can't use tables to encapsulate the matrix. If I do put the matrix within
>the table, it would say "Data Regions within table/matrix cells are
>ignored" on Excel when exported. This is a well known issue and is
>currently not supported, even in SP1.
> However, I managed to get it to look slightly better in Excel, but after
> *much* manipulation on the alignment of the matrices and lists...
> --
> Message posted via http://www.sqlmonster.com
Showing posts with label export. Show all posts
Showing posts with label export. Show all posts
Wednesday, March 21, 2012
Monday, March 19, 2012
HELP! HOW TO CALL A DTS PACKAGE FROM A STORED PROC
I'm a rookie with MSSQL. I need to run a DTS package to export a result set
to an MX Excel spread sheet. I need to call the DTS from a stored procedure
and pass it three values, depending on the input parameters to the stored
proc.
to an MX Excel spread sheet. I need to call the DTS from a stored procedure
and pass it three values, depending on the input parameters to the stored
proc.
DTS package is no problem. Pretty easy with the DTS wizard. My problem is
that I can't figure out how to instansiate the DTS package object from a
stored proc and pass the three values as parameters to the DTS package so
they can populate the parameters I created in it.
I found an article related to it, but I'm too much of a rookie to grasp it.
It showed how to do this from a stored procedure:
EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
'MyGVValue'
IF @.hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
END
I have three values and tree global variables to populate. Do I need to do
the above 3 times?
How do I instantiate the package object? I've read up some on sp_OACreate,
but I don't get it, yet.
How do I initiate the variable @.oPKG?
It contains the name of the sp_OACreate string, right? How do I address a
DTS package in the sp_OACreate string?
I would really appreciate just writing out the sp_OACreate string and how I
pass values for three existing global variables to a DTS package named
"DTS_1".
I'm under some real pressure to get this done.
Thanks for any help I can get.
GunnyHi
Check out
http://www.databasejournal.com/feat...cle.php/1459181
A good resource for everything DTS are books online and
http://www.sqldts.com
John
"Cox News Server" <cfgunny@.cox.net> wrote in message
news:RJClb.97602$AH4.96936@.lakeread06...
> I'm a rookie with MSSQL. I need to run a DTS package to export a result
set
> to an MX Excel spread sheet. I need to call the DTS from a stored
procedure
> and pass it three values, depending on the input parameters to the stored
> proc.
> DTS package is no problem. Pretty easy with the DTS wizard. My problem is
> that I can't figure out how to instansiate the DTS package object from a
> stored proc and pass the three values as parameters to the DTS package so
> they can populate the parameters I created in it.
> I found an article related to it, but I'm too much of a rookie to grasp
it.
> It showed how to do this from a stored procedure:
> EXEC @.hr = sp_OASetProperty @.oPKG, 'GlobalVariables("MyGVName").Value,
> 'MyGVValue'
> IF @.hr <> 0
> BEGIN
> PRINT '*** GlobalVariable Assignment Failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> END
> I have three values and tree global variables to populate. Do I need to do
> the above 3 times?
> How do I instantiate the package object? I've read up some on sp_OACreate,
> but I don't get it, yet.
> How do I initiate the variable @.oPKG?
> It contains the name of the sp_OACreate string, right? How do I address a
> DTS package in the sp_OACreate string?
> I would really appreciate just writing out the sp_OACreate string and how
I
> pass values for three existing global variables to a DTS package named
> "DTS_1".
> I'm under some real pressure to get this done.
> Thanks for any help I can get.
>
> Gunny
Wednesday, March 7, 2012
HELP! From Job tasks to ASP
Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
HELP! From Job tasks to ASP
Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
HELP! From Job tasks to ASP
Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-Rsegis bata wrote:
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with status
,
> last run, etc.) from SQL Server to a table (in the same SQL Server) so I c
an
> create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of th
e
> tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
Hi,
Try to take a look at sysjobhistory table in the MSDB database. That
should get you started.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-Rsegis bata wrote:
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with status
,
> last run, etc.) from SQL Server to a table (in the same SQL Server) so I c
an
> create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of th
e
> tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
Hi,
Try to take a look at sysjobhistory table in the MSDB database. That
should get you started.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
HELP! From Job tasks to ASP
Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-R
Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Helps if I add the tables for you too!
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too!
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>
|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>
|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =
Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
HELP! From Job tasks to ASP
Hello everyone,
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-Rsegis bata wrote:
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with status,
> last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
> create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of the
> tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
Hi,
Try to take a look at sysjobhistory table in the MSDB database. That
should get you started.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Helps if I add the tables for you too! :)
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too! :)
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>> Hello everyone,
>> Is it possible (and if so, how?) to export the list job tasks (with
>> status, last run, etc.) from SQL Server to a table (in the same SQL
>> Server) so I can create a simple ASP file showing the results?
>> Also, that export process should be running periodically because I don't
>> want just a snapshot of sometime in the past but a recurrent display of
>> the tasks in SQL with their particular information.
>> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
>> Thanks in advance for all your help...
>> SB-R
>|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>> Helps if I add the tables for you too! :)
>> look at msdb..sysjobhistory and msdb..sysjobs
>> Immy
>> "segis bata" <segisbata@.hotmail.com> wrote in message
>> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>> Hello everyone,
>> Is it possible (and if so, how?) to export the list job tasks (with
>> status, last run, etc.) from SQL Server to a table (in the same SQL
>> Server) so I can create a simple ASP file showing the results?
>> Also, that export process should be running periodically because I don't
>> want just a snapshot of sometime in the past but a recurrent display of
>> the tasks in SQL with their particular information.
>> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
>> Thanks in advance for all your help...
>> SB-R
>>
>|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
Is it possible (and if so, how?) to export the list job tasks (with status,
last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
create a simple ASP file showing the results?
Also, that export process should be running periodically because I don't
want just a snapshot of sometime in the past but a recurrent display of the
tasks in SQL with their particular information.
FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
Thanks in advance for all your help...
SB-Rsegis bata wrote:
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with status,
> last run, etc.) from SQL Server to a table (in the same SQL Server) so I can
> create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of the
> tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>
Hi,
Try to take a look at sysjobhistory table in the MSDB database. That
should get you started.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Why reinvent the wheel?
Just query the tables directly via a stored procedure and call the procedure
when requiring an update?
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Helps if I add the tables for you too! :)
look at msdb..sysjobhistory and msdb..sysjobs
Immy
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> Is it possible (and if so, how?) to export the list job tasks (with
> status, last run, etc.) from SQL Server to a table (in the same SQL
> Server) so I can create a simple ASP file showing the results?
> Also, that export process should be running periodically because I don't
> want just a snapshot of sometime in the past but a recurrent display of
> the tasks in SQL with their particular information.
> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
> Thanks in advance for all your help...
> SB-R
>|||Hello,
Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
MSDB (I only see sysjobs_view)
SB-R
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Helps if I add the tables for you too! :)
> look at msdb..sysjobhistory and msdb..sysjobs
> Immy
> "segis bata" <segisbata@.hotmail.com> wrote in message
> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>> Hello everyone,
>> Is it possible (and if so, how?) to export the list job tasks (with
>> status, last run, etc.) from SQL Server to a table (in the same SQL
>> Server) so I can create a simple ASP file showing the results?
>> Also, that export process should be running periodically because I don't
>> want just a snapshot of sometime in the past but a recurrent display of
>> the tasks in SQL with their particular information.
>> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
>> Thanks in advance for all your help...
>> SB-R
>|||Sorry,
I was looking at the views instead of the tables
Thanks again!
SB-R
"segis bata" <segisbata@.hotmail.com> wrote in message
news:eZGgw%23jaHHA.4000@.TK2MSFTNGP02.phx.gbl...
> Hello,
> Thanks for the info, but I can't see sysjobhistory and sysjobs tables in
> MSDB (I only see sysjobs_view)
> SB-R
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:u2QC12jaHHA.1244@.TK2MSFTNGP04.phx.gbl...
>> Helps if I add the tables for you too! :)
>> look at msdb..sysjobhistory and msdb..sysjobs
>> Immy
>> "segis bata" <segisbata@.hotmail.com> wrote in message
>> news:eF%23AVrjaHHA.2448@.TK2MSFTNGP02.phx.gbl...
>> Hello everyone,
>> Is it possible (and if so, how?) to export the list job tasks (with
>> status, last run, etc.) from SQL Server to a table (in the same SQL
>> Server) so I can create a simple ASP file showing the results?
>> Also, that export process should be running periodically because I don't
>> want just a snapshot of sometime in the past but a recurrent display of
>> the tasks in SQL with their particular information.
>> FYI, I'm running SQL Server 2000 in a Win2003 SP1 box
>> Thanks in advance for all your help...
>> SB-R
>>
>|||Pls have a look in the system tables folder for dbo.sysjobhistory.
For a more immediate view of job states you can use this code:
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL) -- 1 = Executing, 2 = Waiting For Thread, 3 =Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =PerformingCompletionActions
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT sysjobs_view.name, #xp_results.* FROM #xp_results inner join
msdb.dbo.sysjobs_view sysjobs_view
on #xp_results.job_id = sysjobs_view.job_id
DROP TABLE #xp_results
Rgds,
Paul Ibison
Monday, February 27, 2012
Help!
Hello,
I am trying to copy user logins from one database to another on the same
instance of SQL.
I used DTS to export the data to the new database, when I should have used
the backup option.
I'm in a bet of trouble. Now the users can not access the copied database.
I followed the suggestion below:
But I get the error when I run the script. Server: Msg 195, Level 15, State
10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
How to transfer logins and passwords between instances of SQL Server
View products that this article applies to.
Article ID : 246133
Last Review : April 12, 2006
Revision : 5.0
Any help would appreciated. My neck is on the line
TerrySearch the archives for the name of the procedures. The KB by mistake includ
es code that only run on
2005, but there has been several postings with the original proc, that runs
on 2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terry" <tgwillett@.cox.net> wrote in message news:pgc2g.56484$gE.47798@.dukeread06...eagreen">
> Hello,
> I am trying to copy user logins from one database to another on the same
> instance of SQL.
> I used DTS to export the data to the new database, when I should have used
> the backup option.
> I'm in a bet of trouble. Now the users can not access the copied database.
> I followed the suggestion below:
> But I get the error when I run the script. Server: Msg 195, Level 15, Stat
e
> 10, Procedure sp_help_revlogin, Line 52
> 'LOGINPROPERTY' is not a recognized function name.
> How to transfer logins and passwords between instances of SQL Server
> View products that this article applies to.
> Article ID : 246133
> Last Review : April 12, 2006
> Revision : 5.0
> Any help would appreciated. My neck is on the line
> Terry
>|||Thank You
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O7%23iSQeZGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Search the archives for the name of the procedures. The KB by mistake
> includes code that only run on 2005, but there has been several postings
> with the original proc, that runs on 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:pgc2g.56484$gE.47798@.dukeread06...
>
I am trying to copy user logins from one database to another on the same
instance of SQL.
I used DTS to export the data to the new database, when I should have used
the backup option.
I'm in a bet of trouble. Now the users can not access the copied database.
I followed the suggestion below:
But I get the error when I run the script. Server: Msg 195, Level 15, State
10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
How to transfer logins and passwords between instances of SQL Server
View products that this article applies to.
Article ID : 246133
Last Review : April 12, 2006
Revision : 5.0
Any help would appreciated. My neck is on the line
TerrySearch the archives for the name of the procedures. The KB by mistake includ
es code that only run on
2005, but there has been several postings with the original proc, that runs
on 2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terry" <tgwillett@.cox.net> wrote in message news:pgc2g.56484$gE.47798@.dukeread06...eagreen">
> Hello,
> I am trying to copy user logins from one database to another on the same
> instance of SQL.
> I used DTS to export the data to the new database, when I should have used
> the backup option.
> I'm in a bet of trouble. Now the users can not access the copied database.
> I followed the suggestion below:
> But I get the error when I run the script. Server: Msg 195, Level 15, Stat
e
> 10, Procedure sp_help_revlogin, Line 52
> 'LOGINPROPERTY' is not a recognized function name.
> How to transfer logins and passwords between instances of SQL Server
> View products that this article applies to.
> Article ID : 246133
> Last Review : April 12, 2006
> Revision : 5.0
> Any help would appreciated. My neck is on the line
> Terry
>|||Thank You
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O7%23iSQeZGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Search the archives for the name of the procedures. The KB by mistake
> includes code that only run on 2005, but there has been several postings
> with the original proc, that runs on 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:pgc2g.56484$gE.47798@.dukeread06...
>
Help!
Hello,
I am trying to copy user logins from one database to another on the same
instance of SQL.
I used DTS to export the data to the new database, when I should have used
the backup option.
I'm in a bet of trouble. Now the users can not access the copied database.
I followed the suggestion below:
But I get the error when I run the script. Server: Msg 195, Level 15, State
10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
How to transfer logins and passwords between instances of SQL Server
View products that this article applies to.
Article ID : 246133
Last Review : April 12, 2006
Revision : 5.0
Any help would appreciated. My neck is on the line :)
TerrySearch the archives for the name of the procedures. The KB by mistake includes code that only run on
2005, but there has been several postings with the original proc, that runs on 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terry" <tgwillett@.cox.net> wrote in message news:pgc2g.56484$gE.47798@.dukeread06...
> Hello,
> I am trying to copy user logins from one database to another on the same
> instance of SQL.
> I used DTS to export the data to the new database, when I should have used
> the backup option.
> I'm in a bet of trouble. Now the users can not access the copied database.
> I followed the suggestion below:
> But I get the error when I run the script. Server: Msg 195, Level 15, State
> 10, Procedure sp_help_revlogin, Line 52
> 'LOGINPROPERTY' is not a recognized function name.
> How to transfer logins and passwords between instances of SQL Server
> View products that this article applies to.
> Article ID : 246133
> Last Review : April 12, 2006
> Revision : 5.0
> Any help would appreciated. My neck is on the line :)
> Terry
>|||Thank You
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O7%23iSQeZGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Search the archives for the name of the procedures. The KB by mistake
> includes code that only run on 2005, but there has been several postings
> with the original proc, that runs on 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:pgc2g.56484$gE.47798@.dukeread06...
>> Hello,
>> I am trying to copy user logins from one database to another on the same
>> instance of SQL.
>> I used DTS to export the data to the new database, when I should have
>> used
>> the backup option.
>> I'm in a bet of trouble. Now the users can not access the copied
>> database.
>> I followed the suggestion below:
>> But I get the error when I run the script. Server: Msg 195, Level 15,
>> State
>> 10, Procedure sp_help_revlogin, Line 52
>> 'LOGINPROPERTY' is not a recognized function name.
>> How to transfer logins and passwords between instances of SQL Server
>> View products that this article applies to.
>> Article ID : 246133
>> Last Review : April 12, 2006
>> Revision : 5.0
>> Any help would appreciated. My neck is on the line :)
>> Terry
>>
>
I am trying to copy user logins from one database to another on the same
instance of SQL.
I used DTS to export the data to the new database, when I should have used
the backup option.
I'm in a bet of trouble. Now the users can not access the copied database.
I followed the suggestion below:
But I get the error when I run the script. Server: Msg 195, Level 15, State
10, Procedure sp_help_revlogin, Line 52
'LOGINPROPERTY' is not a recognized function name.
How to transfer logins and passwords between instances of SQL Server
View products that this article applies to.
Article ID : 246133
Last Review : April 12, 2006
Revision : 5.0
Any help would appreciated. My neck is on the line :)
TerrySearch the archives for the name of the procedures. The KB by mistake includes code that only run on
2005, but there has been several postings with the original proc, that runs on 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terry" <tgwillett@.cox.net> wrote in message news:pgc2g.56484$gE.47798@.dukeread06...
> Hello,
> I am trying to copy user logins from one database to another on the same
> instance of SQL.
> I used DTS to export the data to the new database, when I should have used
> the backup option.
> I'm in a bet of trouble. Now the users can not access the copied database.
> I followed the suggestion below:
> But I get the error when I run the script. Server: Msg 195, Level 15, State
> 10, Procedure sp_help_revlogin, Line 52
> 'LOGINPROPERTY' is not a recognized function name.
> How to transfer logins and passwords between instances of SQL Server
> View products that this article applies to.
> Article ID : 246133
> Last Review : April 12, 2006
> Revision : 5.0
> Any help would appreciated. My neck is on the line :)
> Terry
>|||Thank You
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O7%23iSQeZGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Search the archives for the name of the procedures. The KB by mistake
> includes code that only run on 2005, but there has been several postings
> with the original proc, that runs on 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terry" <tgwillett@.cox.net> wrote in message
> news:pgc2g.56484$gE.47798@.dukeread06...
>> Hello,
>> I am trying to copy user logins from one database to another on the same
>> instance of SQL.
>> I used DTS to export the data to the new database, when I should have
>> used
>> the backup option.
>> I'm in a bet of trouble. Now the users can not access the copied
>> database.
>> I followed the suggestion below:
>> But I get the error when I run the script. Server: Msg 195, Level 15,
>> State
>> 10, Procedure sp_help_revlogin, Line 52
>> 'LOGINPROPERTY' is not a recognized function name.
>> How to transfer logins and passwords between instances of SQL Server
>> View products that this article applies to.
>> Article ID : 246133
>> Last Review : April 12, 2006
>> Revision : 5.0
>> Any help would appreciated. My neck is on the line :)
>> Terry
>>
>
Subscribe to:
Posts (Atom)