Solved

SQL server 2005 Create Scheduled Job

Posted on 2011-02-20
6
452 Views
Last Modified: 2012-05-11
In SQL server 2005, I have two queries that I need to run as scheduled job with daily refresh.

These are the queries:

DELETE OPENQUERY ([MYLinkedServer], 'select * from Target_tab');

INSERT INTO OPENQUERY([MYLinkedServer], 'select fid, FName from Target_tab')
select TNumber, FName from dbo.Source_tab;

The above queries run well on the SQL server 2005. The target is an Oracle database.

How do I create a scheduled job for these two queries on the SQL server?
0
Comment
Question by:toooki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 6

Accepted Solution

by:
AkAlan earned 450 total points
ID: 34938881
SQL Server Agent
Right Click on Jobs and select New Job.
0
 

Author Comment

by:toooki
ID: 34939183
Thank you. I created (tried to create) this procedure:

I get error when I run the CREATE PROCEDURE command:

Msg 262, Level 14, State 1, Procedure MyProc1, Line 14
CREATE PROCEDURE permission denied in database 'myDbname'.

What privileges are needed to create  PROCEDURE and create job?

Where do I find SQL server Agent? (I do not see under Programmability or Security tab)
USE [myDbname]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MyProc1] 
AS

BEGIN

SET NOCOUNT ON;
DELETE OPENQUERY ([MYLinkedServer], 'select * from Target_tab')

INSERT INTO OPENQUERY([MYLinkedServer], 'select fid, FName from Target_tab')
select TNumber, FName from dbo.Source_tab


END
END

Open in new window

0
 
LVL 6

Expert Comment

by:AkAlan
ID: 34939214
SQL Server Agent is part of SQL Management Studio. If you don't see it you need to do some research on how to install it. It is what allows you to create scheduled jobs, backups, view event logs and so forth. If you aren't an administrator on the sql server then you need that person to give you permissions to create stored procedures. If you are an administrator but are logged in with a non admin account, you need to give that account permissions.
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

Author Comment

by:toooki
ID: 34939264
Thank you. I am not the administrator of the database. I only have one login to use in that SQL server. And I am using SQL server management studio express.

I will ask the administrator to give me:
1. permissions to create stored procedures.

Do I need any other permission to create scheduled job?
I attached the screen-shot of the SQL server management studio (everything that shows under the database name). Was the the "SQL Server Agent" part supposed to show up here? sql server management studio screen
0
 
LVL 6

Expert Comment

by:AkAlan
ID: 34939295
I think you will have to also get permissions to create jobs. Here is a screen shot of what sql agent will look like if you had it.
SqlServerImage.docx
0
 

Author Comment

by:toooki
ID: 34990076
I found it later. I was using SQL server management studio "Express". The free one does not have the Agent. The licensed one has...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question