2

I often find myself hacking together multiple .sql files for a project and calling them one after the other through Task Scheduler. Everything I know about SQL is self-taught, so I don't know anything about useful shortcuts and best practices. I imagine there has to be a more elegant way to do what I do.

Example

Right now I work on a project in which I map GPS data from my camera photos to the GPS data of some addresses which are stored on a different SQL Server:

  • Import all new Address-Strings from a different SQL Server on the network (insert into..select from)
  • Fix the spelling of those strings (stored procedure with 50+ SET @str = REPLACE(@str, yada, yada))
  • call wget http://maps.googleapis.com/maps/api/geocode/json?address= [adress string] with xp_cmdshell and store the result in a text file
  • read it with SELECT BulkColumn FROM OPENROWSET(BULK N'D:\resultfile.txt', SINGLE_CLOB) AS Contents) to later extract Latitude and Longitude and store them in the address table
  • run exiftools.exe against new photos to get their GPS data too, store results in csv, BULK INSERT this CSV file into the pictures table
  • map pictures to addresses
  • create directories and move photos with xp_cmdshell

These actions are all accompanied by many layers of if xyz is not null BEGIN END", REPLACE(xyz, CHAR(10), '') and so on.

I also have three views, which are used by xampp/mysql on a local website to

  • show all address-photo-mappings in a tree-structure, including links to those photos
  • show all photos which were taken without GPS data or couldn't be mapped to a known address
  • show all addresses which couldn't be resolved by Google

All in all, I have:

  • 6 .sql files
  • 6 .cmd files which all do sqlcmd -S server\instance -i yada.sql
  • a scheduled task which calls these 6 .cmd files
  • 3 views

Every little change I want to introduce makes me

  • re-check nearly all of these .sql files for compatibility
  • disable scheduled task while testing changes, reenable afterwards
  • alter view as.. because I can't just sync views via github

It is very tedious. Do you have any tips or something I should Google? I don't mind reading a bit on my own. Thanks!

closed as too broad by Justin Cave, Evan Carroll, McNets, James Anderson, Marian May 25 '17 at 12:12

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

You could create a script outside SQL Server (Powershell, VBScript, console app) which does away with your text files. The script would read input data from SQL, call the external utility and write the result directly back to the database.

If it was something written in C# then you have all the handy source control, automated tests stuff. Unfortunately, using SQL to perform external processes is possible, but painful. Sometimes you just need to chuck it in and write some middleware (in the middle of SQL and the external tools) to do the job for you.

For example, I wrote a VBScript that extracts a list of all tables from the DB then searches through .dtsx files to find them, then writes the result directly back to the database.

You may also be able to use SSIS and SQL Agent to simplify your process. SSIS is good for defining orchestration and data transfer in a visual way, though for more complex external things you often need to resort to C# script tasks. SQL Agent can be used to run SQL and command lines in order with basic workflow etc.


Community Wiki answer created from question comments by Nick McDermaid

0

So the simplest change you can make would be to use the SQL Agent instead of Windows Scheduler. Just make a single scheduled job that calls each of your steps in sequence (SQL scripts, command line scripts, etc). You can define the sequence of steps, and even branch to an error step if the prior one failed.

But beyond that, I think you should seriously investigate SSIS. You can create an SSIS "package", and run it from the SQL Agent either via a schedule or on-demand.

SSIS is very workflow-oriented, and can easily:

  1. Run SQL statement on multiple servers/databases
  2. Loop through objects/files and perform actions on each
  3. Move files around drives
  4. Import data into tables, applying transformations/lookups if necessary
  5. Pipe failed rows into a different destination table for error trapping/analysis
  6. Call scripts of any kind (C#, vbs, etc) to do nearly anything you want.
  7. High level of error logging

Plenty of tutorials out there, here is one created by Microsoft.

Not the answer you're looking for? Browse other questions tagged or ask your own question.