CODEWITHSUNDEEP
sql-serversql-server-2012scheduled-tasksssms
artemis
artemis

Reputation: 7281

Automate Running SQL Queries in SSMS

I have a SQL Server database set up that I manage using SQL Server Management Studio 17.

In that database, I have 27 tables that I maintain by running pretty simple OPENQUERY scripts every morning, something to the effect of:

DROP TABLE IF EXISTS [databasename].[dbo].[table27]
SELECT * INTO  [databasename].[dbo].[table27] FROM OPENQUERY(OracleInstance, '

SELECT
  table27.*

FROM
  table27
    INNER JOIN table26 ON table27.criteria = table26.criteria

WHERE
    < filter >
    < filter >

  ');

And this works great! But, it is cumbersome to every morning, sign into SSMS, and right click on my database and hit "New Query" and copy in 27 individual SQL scripts and run them. I am looking for a way to automate that. My directory that holds these scripts looks like this:
enter image description here

I don't know if this is achievable in SSMS or in like a batch script. I would imagine for the latter, some pseudocode looking like:

connect to sql server instance

given instance:
        for each sql_script in directory:
                sql_script.execute

I have tried creating a script in SSMS, by following: Tasks -> Script Database -> But there is no option to execute a .sql file on the tables in question.

I have tried looking at the following resources on using T-SQL to schedule nightly jobs, but have not had any luck conceiving of how to do so:

The expected result would be the ability to automatically run the 27 sql queries in the directory above to update the tables in SQL Server, once a day, preferably at 6:00 AM EST. My primary issue is that I cannot access anything but SQL Server Management Studio; I can't access the configuration manager to use things like SQL Server Agent. So if I am scheduling a task, I need to do so through SSMS.

Upvotes: 0

Views: 16660

Answers (4)

Zorkolot
Zorkolot

Reputation: 2027

You actually can't access the SQL Server Agent via Object Explorer? This is located below "Integration Services Catalog" See highlighted below:

enter image description here

You describe not being able to access that in the question for some reason. If you can't access that then something is wrong with SQL Server or perhaps you don't have admin rights to do things like schedule jobs (a guess there).

Upvotes: 2

Michael
Michael

Reputation: 869

In SSMS you would wnat to use Execute T-SQL Statement Task and write your delete statement in the SQL Statement field in the General Tab.

However, I would look at sqlcmd. Simply make a batch script and schedule it in Task Scheduler (if you're using windows). Or you could use

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G" pause

From this post. Run all SQL files in a directory

Upvotes: 1

Jonny
Jonny

Reputation: 1171

I suggest you add these scripts as jobs for the SQL Server Agent.

Upvotes: 0

Georges Khater
Georges Khater

Reputation: 74

So basically you have to create a Powershell script that calls and execute the sql scripts. After that you can add your Powrshell script to the Task Scheduler.

Upvotes: 0

Related Questions