Creation of Jobs...

I wanted to created a few jobs in my database.
How do i create it using the Batch files???
Thanks,
Sandu_BangaloreLook up sp_add_job in BOL...

Or better yet...create one manually in EM and then script it ( I always like the easy way out)

Creating SQLServer Jobs in Enterprise Manager

I would like to create a job in Enterprise manager that runs sp_who2
and write the output to a file. The filename would need to be appended
with a timestamp that makes the file unique. I can then write another
job that will cleanup the directory by deleting files that are older
than a specified date. This is a very simple process in unix platform.
I am hoping that it can be done in Windows but I don't know how?

Any help will be applicicated..You can send the output of a job step directly to a file (see the
Advanced tab), but there's no easy way to set the file name
dynamically. You might be able to use sp_update_jobstep to set it at
runtime, but then you would still have to do the cleanup part, which
would mean using xp_cmdshell.

Personally, I would use a script in a langauge like VBScript, Perl or
Python to do this:

1. Work out the correct filename
2. Call osql.exe to get the output into the file (see the -Q and -o
options)
3. Clean up any old files

You can then call the script from a job step - it's much easier to
manipulate files outside the database.

Simon

Creating process doing specific job periodically

Hi, can I create a background process in SQL Server doing specific jobs periodically? Stored procedure is the only solution?

hi,

remedios_ wrote:

Hi, can I create a background process in SQL Server doing specific jobs periodically? Stored procedure is the only solution?

SQLExpress does not provide the SQL Server Agent, the native SQL Server scheduler where specific jobs can be scheduled to execute specific "task", such as Transact-SQL statements, CmdExec OS commands and the like..
obviously the full blown SQL Server 2005 editions provides this component..
this is not as "creating a background process", as the Agent runs a proprietary process and related threads..

a solution can even be to schedule, via the native OS scheduler (AT, SCHTASKS) the required tasks.. this is usualy the solution for scheduled backups in SQLExpress scenarios...

now the question is about what you have to do and on which "platform", SQLExpress or full blown SQL Server editions..

regards

|||

Hi, Andrea.

I'm using SQL 2005 Express edition.
There is a table which saves data from clients every other minute. This table keeps only one hour's data before from current time. So I must delete the expired data. This is the task that I want to schedule.

I don't exactly know which is efficiant that doing in my application or let SQL Server does.

I'll visit the site you commented and thank you very much.

|||

Hi,

I'd go for an OS scheduled task, running SQLCmd.exe to execute the desired Transact-SQL statement...

something like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.History (

Id int NOT NULL IDENTITY,

data varchar(25) DEFAULT 'will be removed',

Registered datetime NOT NULL DEFAULT DATEADD(Hour, -1, GETDATE())

);

GO

WHILE (SELECT COUNT(*) FROM dbo.History) < 10 BEGIN

INSERT INTO dbo.History DEFAULT VALUES;

END;

INSERT INTO dbo.History VALUES ( 'this will not be removed', GETDATE());

GO

SELECT * FROM dbo.History;

GO

PRINT 'actual statement to be scheduled every hour';

DELETE FROM dbo.History

WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;

SELECT * FROM dbo.History;

GO

DROP TABLE dbo.History;

--<--

Id data Registered

-- - --

1 will be removed 2006-12-09 12:03:35.843

2 will be removed 2006-12-09 12:03:35.860

3 will be removed 2006-12-09 12:03:35.860

4 will be removed 2006-12-09 12:03:35.860

5 will be removed 2006-12-09 12:03:35.860

6 will be removed 2006-12-09 12:03:35.860

7 will be removed 2006-12-09 12:03:35.860

8 will be removed 2006-12-09 12:03:35.860

9 will be removed 2006-12-09 12:03:35.860

10 will be removed 2006-12-09 12:03:35.860

11 this will not be removed 2006-12-09 13:03:35.860

actual statement to be scheduled every hour

Id data Registered

-- - --

11 this will not be removed 2006-12-09 13:03:35.860

the actual "cleaning" statement should then be included in a cmd file like

SQLCmd -S(Local) -E -dTheDatabase -Q"DELETE FROM dbo.History WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;" >c:\MyLog\ScheduledCleanup.txt

schedule it as desired..

regards

Creating process doing specific job periodically

Hi, can I create a background process in SQL Server doing specific jobs periodically? Stored procedure is the only solution?

hi,

remedios_ wrote:

Hi, can I create a background process in SQL Server doing specific jobs periodically? Stored procedure is the only solution?

SQLExpress does not provide the SQL Server Agent, the native SQL Server scheduler where specific jobs can be scheduled to execute specific "task", such as Transact-SQL statements, CmdExec OS commands and the like..
obviously the full blown SQL Server 2005 editions provides this component..
this is not as "creating a background process", as the Agent runs a proprietary process and related threads..

a solution can even be to schedule, via the native OS scheduler (AT, SCHTASKS) the required tasks.. this is usualy the solution for scheduled backups in SQLExpress scenarios...

now the question is about what you have to do and on which "platform", SQLExpress or full blown SQL Server editions..

regards

|||

Hi, Andrea.

I'm using SQL 2005 Express edition.
There is a table which saves data from clients every other minute. This table keeps only one hour's data before from current time. So I must delete the expired data. This is the task that I want to schedule.

I don't exactly know which is efficiant that doing in my application or let SQL Server does.

I'll visit the site you commented and thank you very much.

|||

Hi,

I'd go for an OS scheduled task, running SQLCmd.exe to execute the desired Transact-SQL statement...

something like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.History (

Id int NOT NULL IDENTITY,

data varchar(25) DEFAULT 'will be removed',

Registered datetime NOT NULL DEFAULT DATEADD(Hour, -1, GETDATE())

);

GO

WHILE (SELECT COUNT(*) FROM dbo.History) < 10 BEGIN

INSERT INTO dbo.History DEFAULT VALUES;

END;

INSERT INTO dbo.History VALUES ( 'this will not be removed', GETDATE());

GO

SELECT * FROM dbo.History;

GO

PRINT 'actual statement to be scheduled every hour';

DELETE FROM dbo.History

WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;

SELECT * FROM dbo.History;

GO

DROP TABLE dbo.History;

--<--

Id data Registered

-- - --

1 will be removed 2006-12-09 12:03:35.843

2 will be removed 2006-12-09 12:03:35.860

3 will be removed 2006-12-09 12:03:35.860

4 will be removed 2006-12-09 12:03:35.860

5 will be removed 2006-12-09 12:03:35.860

6 will be removed 2006-12-09 12:03:35.860

7 will be removed 2006-12-09 12:03:35.860

8 will be removed 2006-12-09 12:03:35.860

9 will be removed 2006-12-09 12:03:35.860

10 will be removed 2006-12-09 12:03:35.860

11 this will not be removed 2006-12-09 13:03:35.860

actual statement to be scheduled every hour

Id data Registered

-- - --

11 this will not be removed 2006-12-09 13:03:35.860

the actual "cleaning" statement should then be included in a cmd file like

SQLCmd -S(Local) -E -dTheDatabase -Q"DELETE FROM dbo.History WHERE DATEDIFF(Hour, Registered, GETDATE()) >= 1;" >c:\MyLog\ScheduledCleanup.txt

schedule it as desired..

regards

Creating maintenance plans in MSDE

I can create a maintenance plan in Enterprisemanager using the maintenance plan wizard. It automatically creates 4 different jobs. I can generate a script for those jobs and run using a batch file in MSDE. Is there another way to do the same thing. How c
an i create maintenance plans in MSDE
Hi,
You need to just run the same types of scripts against the MSDE. Use OSQL or
a 3rd party tool.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"hp" <anonymous@.discussions.microsoft.com> wrote in message
news:E4C10AAD-2B43-4BDE-B1B1-C23D326CAE19@.microsoft.com...
> I can create a maintenance plan in Enterprisemanager using the maintenance
plan wizard. It automatically creates 4 different jobs. I can generate a
script for those jobs and run using a batch file in MSDE. Is there another
way to do the same thing. How can i create maintenance plans in MSDE
|||These articles may help you get started:
241397 HOWTO: Back Up a Microsoft Data Engine Database with Transact-SQL
http://support.microsoft.com/?id=241397
328747 INF: Recovery Planning for Microsoft SQL Server 2000 Desktop Engine
http://support.microsoft.com/?id=328747
325003 HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the
http://support.microsoft.com/?id=325003
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

creating jobs programmatically causes deadlocks

I'm using the transactional sproc below to create jobs in an automated
fashion that will run cmdexecs after a configurable pause. This is
potentially HIGH VOLUME! The sproc is run from a VB.NET EXE using the
ADO.NET command.executenonquery method during the processing of text files to
determine what the job should do.
I have two essentially identical servers (4 X 3.2GHz CPU, 3GB RAM). On one
server, dropping 30 trigger files results in ~150 jobs being created in ~30
seconds (I have a thread.sleep(200) between each job creation due to another
issue). On another server dropping just 10 of the trigger files causes
deadlocks consistently. Both servers are running SQL 2000 SP3 on WinServer
2003 Standard edition and
the assembly is written against DNF 1.1.
Is the logic in the sproc faulty? The transaction is necessary since the
job needs all the properties to do it's job.
What server configurations affect deadlock timeouts, etc. Any help
would be appreciated.
Sproc follows:
--creates single use, self deleting job
CREATE PROCEDURE usp_RunCmdJobSoon (
@.JobName as varchar(50),
@.CmdText as varchar(1000),
@.DelaySeconds as int = 30,
@.RunOnIdle as bit = 0,
@.DeleteWhenDone as bit = 0
)
AS
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
declare @.Date as datetime
declare @.NewDate as int
declare @.NewTime as int
DECLARE @.String char(30)
SET @.String = 'sa'
/*
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @.JobName)
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR ('Unable to import job %s since there is already a
multi-server job with this name.', 16, 1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
ELSE
RAISERROR ('Unable to create job %s since it already exists.', 16,
1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
*/
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = @.JobName, @.owner_login_name = @.String, @.description = N'Automated
Job Run', @.category_name = N'ODS Automated', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0,
@.notify_level_eventlog = 3, @.delete_level= @.DeleteWhenDone
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = @.JobName, @.command = @.CmdText, @.database_name = N'', @.server
= N'', @.database_user_name = N'', @.subsystem = N'CmdExec',
@.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts = 2, @.retry_interval =
5, @.output_file_name = N'', @.on_success_step_id = 0, @.on_success_action = 1,
@.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
--in case of restart
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on start', @.enabled = 1, @.freq_type = 64
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
--in case of Idle condition
IF (@.RunOnIdle = 1)
BEGIN
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on Idle', @.enabled = 1, @.freq_type = 128
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
-- Delay start
SET @.String = 'Run job soon'
set @.Date = dateadd(ss,@.DelaySeconds,Getdate())
set @.NewDate = cast(convert(varchar(8), @.Date, 112) as int)
set @.NewTime = cast(replace(convert(varchar(8), @.Date, 108), ':', '') as
int)
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= @.String, @.enabled = 1, @.freq_type = 1, @.active_start_date = @.NewDate,
@.active_start_time = @.NewTime
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
RAISERROR ('Error %s while creating JobName= %s ', 16, 1,@.@.Error,
@.JobName) WITH LOG
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Hi,
Thanks for your post and I will take ownership of this thread now and help
you with this issue.
From your descriptions, I understood that your stored procedures goes
smoothly in one machine but it will be easily hangs in another machine.
Have I understood you? Correct me if I was wrong.
Generally, performance issues can be caused by various factors, and it is
difficult to locate the root cause in a newsgroup thread. If the issue
still exists after you have used the troubleshooting steps above, to
efficiently troubleshoot a performance issue, we recommend that you contact
Microsoft Product Support Services and open a support incident and work
with a dedicated Support Professional. Please be advised that contacting
phone support will be a charged call. To obtain the phone numbers for
specific technology request please take a look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you'd still like to continue working via the newsgroup, I want to set
your expectations that the issue might take a long time to narrow down.
During the course of troubleshooting, we may redirect you to PSS if
required.
For now, I would love to collect more information about your issue
1. Any Error Message given by SQL Server 2000?
2. Collect the information from sp_blocker_pss80 as the following KB
insturcted
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Moreover, here are some documents for your reference to find what is
blocked
Resolving blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/?id=323630
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
I already have a blocker output, but it's pretty straight forward. The ONLY
thing happening on the system is the creation of jobs through this sproc.
The sproc is blocking itself from another instance doing the exact same thing
at the same time. That's why I included the sproc in my first post, it's
blocking itself.
How can I get more blocker output to you? Maybe I can email them to you?
blocker output excerpt:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 8 No Waittypes: 2004-09-30 15:39:06.890 0
AAG0J6DW06
8 No Waittypes: 2004-09-30 15:39:21.907 0 AAG0J6DW06
8.2 Start time: 2004-09-30 15:39:36.920 0
SYSPROCESSES AAG0J6DW06 134218488
spid status blocked open_tran waitresource
waittype waittime cmd lastwaittype cpu
physical_io memusage last_batch
login_time net_address net_library dbid ecid kpid
hostname
hostprocess loginame
program_name
nt_domain
nt_username
uid sid
sql_handle
stmt_start stmt_end
-- -- -- --
-----------
-
-- -- -- --
-- -- -- --
-- -- -- -- -- --
------
------
------
------
------
--------
--- -- --
51 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4547 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:32.360
2004-09-30 15:39:31.610 000E7FB55C16 Named Pipes 4 0 1008
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
61 sleeping 64 1 KEY: 4:2089058478:1
(bf013f652931)
0x0003 5281 CONDITIONAL LCK_M_S
0 0 18 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5700
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
64 sleeping 61 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5406 CONDITIONAL LCK_M_S
0 0 17 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 4136
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
65 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4672 CONDITIONAL LCK_M_S
0 0 0 2004-09-30 15:39:32.360
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5616
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
66 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 11 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3308
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
67 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3284
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
68 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 2064
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
69 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 5780
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
70 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 1204
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
ESP 110
SYSPROC FIRST PASS
spid ecid waittype
-- -- --
64 0 0x0003
51 0 0x0003
61 0 0x0003
65 0 0x0003
66 0 0x0003
67 0 0x0003
68 0 0x0003
69 0 0x0003
70 0 0x0003
Blocking via locks at 2004-09-30 15:39:36.920
SPIDs at the head of blocking chains
spid
SYSLOCKINFO
spid ecid dbid ObjId IndId Type Resource Mode
Status TransID TransUOW
-- -- -- -- -- -- -- --
-- -- --
51 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
51 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:484 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (bf013f652931) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (bf013f652931) S
WAIT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 PAG 1:4701 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:4886 IS
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:4886 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 KEY (d001dca5d2ee) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 KEY (2302a3aeaeb3) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 0 TAB IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 0 TAB IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 0 TAB IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 0 TAB IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 0 TAB IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 0 TAB IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 0 TAB IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 KEY (850364070d60) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 KEY (a2045bef58ed) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 KEY (d001b54b3a2e) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 KEY (3302eb1fa7ce) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 KEY (c0019414db93) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 KEY (a90444d60925) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 KEY (a904a60ed3b4) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 KEY (95032cb6041d) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 KEY (a204cbc41ffd) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 PAG 1:11483 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 KEY (c0018819704b) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (cf0177d4204c) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171280 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171394 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171417 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171440 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171486 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171525 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171532 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 PAG 1:24427 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 PAG 1:44674 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
ESL 0
|||Hi,
Thanks for your prompt updates and information!
Yes, it will be highly appreciated if you could provide me the information
by means of email. Here are some steps to collect the information I want
this time
1. Create a script file named 'queryprocess.sql' with the following query
to run commands in a loop.
WHILE 1=1
BEGIN
Select * from master.dbo.sysprocesses
WAITFOR DELAY '00:00:05'
END
GO
2. Run the script file created in step d from either Isql.exe or the
Osql.exe query tool in a Windows command prompt on the computer that is
running SQL Server. For example,
osql -E -Sserver_name -iqueryprocess.sql -oqueryprocess.out -w2000
3. Send the information to us for reviewing and my email is
v-mingqc@.online.microsoft.com (remove 'online' as it is only for SPAM)
In the meanwhile, from syslockinfo, I found many sp are waiting for an
object 2089058478. Is this the stored procedure itself? Please use the
following command to check the object name: object_name( 2089058478)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I am just checking on your progress regarding the information that was sent
you! Have you tried the steps I provided to you? I wonder how the testing
is going. If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

creating jobs in SSIS

what is the equiv to dtsrunui to create the operating system (CmdExec)...
anyone have a good site suggestions for ssis outside of bolMaybe someone can help you with this, but I can't understand what you are asking.

-PatP|||I appreciate it ... i found what the equivalent is...
dtsrunui = dtexecui

creating jobs in SQL Server 2005

Hello

I am using SQL Server 2005
I want to delete the data from my table after specific number of days.

This should happen automatically.

After the days I specified the data from the table should automatically get deleted. Can anyone let me know how to achieve this.

If anyone can give an example for this it will be great.

Thanks in advance.

Hi,
As the title of your post suggests you've already identified that you need a job running on the server to facilitate this.

SQL Server Agent will allow jobs to be scheduled and run - read up about this feature of SQL Server if unsure.

As for how to add a job...

In SQL Server Management Studio connect to the server.

creating jobs in EM via terminal services

I created a maintenance plan on a server to backup the database and logs. I
created the plan through EM on my PC. The plan works ok. The problem is that
when I log on to the server via terminal services and start EM on the server
the jobs are not there. I've created jobs this way many times before and I'v
e
always been able to see them when logged on to the server.
Any idea why I can't see these jobs? Does it have anything to do with a
maintenance plan?
Thanks,
--
Dan D.Hi Dan,
Do you use trusted connection to login to sql server in both solutions?
Yours truly,
Tomasz B.
"Dan D." wrote:

> I created a maintenance plan on a server to backup the database and logs.
I
> created the plan through EM on my PC. The plan works ok. The problem is th
at
> when I log on to the server via terminal services and start EM on the serv
er
> the jobs are not there. I've created jobs this way many times before and I
've
> always been able to see them when logged on to the server.
> Any idea why I can't see these jobs? Does it have anything to do with a
> maintenance plan?
> Thanks,
> --
> Dan D.|||I figured it out. I have the users set up differently on that box. When I
logged on with my user logon, I could see the jobs. Thanks.
"Tomasz Borawski" wrote:
[vbcol=seagreen]
> Hi Dan,
> Do you use trusted connection to login to sql server in both solutions?
> Yours truly,
> Tomasz B.
> "Dan D." wrote:
>

creating jobs in EM via terminal services

I created a maintenance plan on a server to backup the database and logs. I
created the plan through EM on my PC. The plan works ok. The problem is that
when I log on to the server via terminal services and start EM on the server
the jobs are not there. I've created jobs this way many times before and I've
always been able to see them when logged on to the server.
Any idea why I can't see these jobs? Does it have anything to do with a
maintenance plan?
Thanks,
Dan D.
Hi Dan,
Do you use trusted connection to login to sql server in both solutions?
Yours truly,
Tomasz B.
"Dan D." wrote:

> I created a maintenance plan on a server to backup the database and logs. I
> created the plan through EM on my PC. The plan works ok. The problem is that
> when I log on to the server via terminal services and start EM on the server
> the jobs are not there. I've created jobs this way many times before and I've
> always been able to see them when logged on to the server.
> Any idea why I can't see these jobs? Does it have anything to do with a
> maintenance plan?
> Thanks,
> --
> Dan D.
|||I figured it out. I have the users set up differently on that box. When I
logged on with my user logon, I could see the jobs. Thanks.
"Tomasz Borawski" wrote:
[vbcol=seagreen]
> Hi Dan,
> Do you use trusted connection to login to sql server in both solutions?
> Yours truly,
> Tomasz B.
> "Dan D." wrote:

creating jobs in EM via terminal services

I created a maintenance plan on a server to backup the database and logs. I
created the plan through EM on my PC. The plan works ok. The problem is that
when I log on to the server via terminal services and start EM on the server
the jobs are not there. I've created jobs this way many times before and I've
always been able to see them when logged on to the server.
Any idea why I can't see these jobs? Does it have anything to do with a
maintenance plan?
Thanks,
--
Dan D.Hi Dan,
Do you use trusted connection to login to sql server in both solutions?
Yours truly,
Tomasz B.
"Dan D." wrote:
> I created a maintenance plan on a server to backup the database and logs. I
> created the plan through EM on my PC. The plan works ok. The problem is that
> when I log on to the server via terminal services and start EM on the server
> the jobs are not there. I've created jobs this way many times before and I've
> always been able to see them when logged on to the server.
> Any idea why I can't see these jobs? Does it have anything to do with a
> maintenance plan?
> Thanks,
> --
> Dan D.|||I figured it out. I have the users set up differently on that box. When I
logged on with my user logon, I could see the jobs. Thanks.
"Tomasz Borawski" wrote:
> Hi Dan,
> Do you use trusted connection to login to sql server in both solutions?
> Yours truly,
> Tomasz B.
> "Dan D." wrote:
> > I created a maintenance plan on a server to backup the database and logs. I
> > created the plan through EM on my PC. The plan works ok. The problem is that
> > when I log on to the server via terminal services and start EM on the server
> > the jobs are not there. I've created jobs this way many times before and I've
> > always been able to see them when logged on to the server.
> >
> > Any idea why I can't see these jobs? Does it have anything to do with a
> > maintenance plan?
> >
> > Thanks,
> > --
> > Dan D.