December 9, 2016 at 7:20 pm
I would bet this is the type of situation where the script is easy and fast to run and the fix is complex to where they keep putting it off or are afraid of touching something that some one else built a long time ago. They may fear breaking something else. This is where time in your new position and the growing familiarity that comes with it will at some point in the future allow you to convey with confidence what the whole problem is and what can be done about it. I agree that you should express your thoughts, though you may be over ruled on the motion to try to fix/improve this situation right now. 🙂
----------------------------------------------------
February 28, 2017 at 3:06 am
Hello,
I was looking on how normally people would create a job and run SQL tasks, I will have to check the SSIS option. I am still surprised how many times the answer is to create a Windows script or Powershell. What I usually do is create one master script where I can send a aa.sql script name and execute it. In the case here I need to generate a commas delimited .CSV file that is sent elsewhere. It also creates daily log files, so to avoid any clutter that is left over unattended. The script is called runovernightjob-out.cmd:
REM USAGE : runovernightjob-out.cmd <script name> <db name> <out file>
REM
set PROGDIR=D:\Application\Scripts
set LOGDIR=%PROGDIR%\logs
set OUTDIR=%PROGDIR%\Out
set SCRIPT=%1%
set DB=%2%
set OPT=%3%
set RETURN=0
FOR /F "TOKENS=1 DELIMS=/ " %%A IN ('echo %date:~-4,4%%date:~-7,2%%date:~-10,2%') DO set RUNDATE=%%A
FOR /F "TOKENS=1 DELIMS=/ " %%A IN ('echo %date:~0,3%') DO set WEEKD=%%A
set LOGFILE=%LOGDIR%\%SCRIPT%.%WEEKD%.log
-- the -b otpon is to return errorlevel 1 correctly
sqlcmd -b -s , -i %PROGDIR%\%SCRIPT% -d %DB% -o %OUTDIR%\%OPT%
if ERRORLEVEL 1 (
set RETURN=1
goto script_error
) else (
set RETURN=0
goto end
)
REM :script_error
REM @echo "Script %SCRIPT% executed at %date% %time% Retcode = %RETURN%" >>%LOGFILE%
:end
@echo "=================================================================" >>%LOGFILE%
@echo "Script %SCRIPT% executed at %date% %time% Retcode = %RETURN%" >>%LOGFILE%
exit /B %RETURN%
Then I can schedule this to run my SQL using the Windows Scheduler. As you can see using SQLCMD you can add parameters like the database you need to run the script against, so the call is simple:
runovernightjob-out.cmd aaa.sql MyDB out1.txt
For each script you just repeat the task. Your scheduled tasks will decide which script needs to run at different times. You can built a wrapper script to run all your scripts in one go:
runovernightjob-out.cmd a1.sql MyDB out1.txt
runovernightjob-out.cmd a2.sql MyDB out2.txt
runovernightjob-out.cmd a2.sql MyDB out2.txt
You can also collect any evidence of failures:
sqlcmd -s , -i %PROGDIR%\%SCRIPT% -d %DB% -o %OUTDIR%\%OPT% >>%LOGFILE% 2>&1
I hope this helps. And not very complex too.
February 28, 2017 at 3:12 am
Ignore me here, sorry.
February 28, 2017 at 3:17 am
WebTechie - Thursday, December 1, 2016 8:12 AMI would like to have an app built also.However, as I mentioned, I am the new guy on the block. Senior guy, but still the new guy.I don't want to upset everyone when management believes this is routine and ok.I am leaning toward the SSIS. I just need to figure out how to read a sql file into SSIS and then execute the commands.Thanks.
I think it's the correct approach. We should not immediately dismiss the work of Developers. Sometimes a system requires to run daily scheduled updates.
February 28, 2017 at 8:09 am
In the past, I maintained a database containing TB sized tables and billions of rows where occasionally once every one or two weeks) the data analysts needed to perform mass updates on subsets of data, which would typically be 10s of millions of rows. What I had was a table containing something like the following columns and a simple in-house developed app where users could submit request, and I would then use the same for reviewing and approving the request for deployment. For auditing and production control process reasons, the users were still required to submit their ad-hoc DML script requests through the support desk system and enter the change order number.
I had a job polling the AdHocRequest table every 1 minute, executing the TOP 1 request where StatusCode = Pending and ScheduledTime > getdate(). The job also updates the StartedTime, DeployedTime, and StatusCode columns upon start and completion. It's important to set StatusCode = Processing in the first step, so the same request doesn't get executed repeatedly. So you're essentially looking at a single table, an app that any developer could throw together in a day or two, and a scheduled job. It's nothing fancy, but it covers all the bases and was extensively used for a long time.
[ChangeOrderID] (links back to IT ServiceNow support desk system)
[SubmissionTime] (when the users requested the deployment)
[RequestTime] (when the users initially requested deployment to occur)
[ApprovedTime] (when I reviewed and approved and deployment)
[ScheduleTime] (when I scheduled the deployment to occur)
[StartedTime] (when the deployment actually started)
[CompletionTime] (when the deployment actually completed)
[StatusCode] (pending, processing, success, failed, denied request, etc.)
[SQLText] (actual text of T-SQL batch)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply