0

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.

artemis
  • 6,857
  • 11
  • 46
  • 99
  • Schedule your query in `SQL Server Agent` is the easiest. You mention you have issue on that, maybe you can let us know what is your issue with scheduling it – Squirrel Apr 01 '19 at 13:17
  • Hi @Squirrel, good call, I tried to be as detailed as possible in my post and left out a crucial detail. Updated now. I cannot use the Configuration Manager to access `SQL Server Agent`, I only have access to the `SSMS app`. – artemis Apr 01 '19 at 13:20
  • You managed the `SQL Server Agent` using `SSMS`. Not the `Configuration Manager` – Squirrel Apr 01 '19 at 13:21

4 Answers4

2

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).

Zorkolot
  • 1,899
  • 1
  • 11
  • 8
1

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

Michael
  • 749
  • 1
  • 8
  • 22
0

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.

Georges Khater
  • 74
  • 4
0

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

Jonny
  • 1,037
  • 7
  • 15