May 8, 2020 at 7:28 pm
Hello All,
So I have a VB Script written that moves some files to different folders, connects to my SQL DB, deletes some views, creates some views, and runs an SSIS Package to export said views. It does this in a loop over 30 times creating different views, and exporting them to excel files. Running it from the command line using 'cscript' works exactly as intended
Running it from SQL Server as job, using a proxy, it starts out well -- moving files to folders as intended -- but then it just hangs -- I *think* its getting hung up in the code once in starts to connect to SQL.
I'm connecting to SQL using an ADODB.Connection string, I've tried connecting using my own Windows Authentication or using our 'sa' account with a password. In both instances it works from the command line, but not as a SQL Job.
Any thoughts or suggestions would be greatly appreciated. Thanks!
May 8, 2020 at 9:40 pm
Without seeing the script, that is a difficult thing to debug.
My guess is it is not hanging connecting to SQL as that should time out, not just sit hung.
First thing I'd do is try to determine if it is hanging or crashing. If it is indeed hanging, put some logging into the script (write to a log file before the line you think is causing the hang and after).
If it is crashing, logging is a good idea too.
My guess as to what is happening is a permission issue or missing drive letter problem. If you have any hard-coded drive letters in there (other than local disks), I would replace those with UNC paths.
Since it runs with cscript, it means YOU have the proper drive letters and permissions, but does your SQL Agent account have appropriate access?
Since it works with both your account and the sa account when run manually, I am strongly leaning towards it being an issue with UNC paths vs drive letters.
My first step would be to drop some logging into the vbscript to write to a file to log what steps complete and where it is getting stuck.
You could also check the SQL logs to see if maybe something got blocked connecting for some reason, and/or check extended events/profiler to see if SQL queries are being run. Could be a long running query or blocking preventing the script from finishing at the time you scheduled it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 9, 2020 at 2:01 am
Hi Brian,
Thank You! Your advice helped immensely. I wrote to a log file and eventually realized what was happening:
For anyone else who might need this:
My vbs code was indeed not connecting to SQL Server, but because I had an 'On Error Resume Next' Error Handler, the code kept running past the connection into a 'do...loop' I had written. Because my recordsets weren't populating with any data, the loops kept running in perpetuity, and therefore the code was just hanging.
After removing the error handler, I realized I was getting a DSN Error, and after some digging and experimenting, using the 'SQL Server Native Client 11.0' Driver as opposed to the 'ODBC Driver 17 for SQL Server' did the trick. I couldn't exactly tell you why, but regardless, it's working now.
Thanks Again.
May 9, 2020 at 2:51 am
Thanks for the feedback on your problem.
I'll admit, though, that I'm still trying to figure out why you need a VBScript to do this. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2020 at 11:43 am
Hi Jeff,
This process was originally written in VBA, in an MS Access Application. We have moved our back end to Azure SQL Server, so it was easiest for me to write this using VB, but I'm curious if there is a preferred way of doing this (I'll have to write more of these types of scripts, so if there's a better way, I'm all ears)
This job is scheduled to run once a day, and the point of it is to loop through a list of our company's vendors, and for each vendor create 5 different reports based on the last 24 hours of activity. Those 5 Reports are exported to a single excel file per vendor, titled with the vendor's name and current date, and then sent to said vendor.
From my understanding, the only way to export to excel would be through an SSIS Package, which I have set up, and execute through my vbscript.
May 9, 2020 at 12:33 pm
if you are working with SQL 2019 and get R configured and up and running you can write Excel files with it - and probably easier than with SSIS
There was an article here last few days about this https://www.mssqltips.com/sqlservertip/6396/export-sql-server-tables-to-excel/
regarding using vbscript - for scripting it is better if you start looking at powershell as it is the way going forward for most MS related stuff
May 9, 2020 at 3:36 pm
If you already have an SSIS package setup to build the Excel files - then you should be able to put everything else in the script directly into SSIS. It would really depend on what the script is doing...
The idea that you have to create a view - then select from the view - then drop the view leads me to believe a stored procedure may be a better option, but we would need to see the views and how they are different for each vendor.
I may also just be a matter of creating a single view for each report and modifying the SSIS package to use a parameter that uses that view - again, depends on how the view is changed for each vendor.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 9, 2020 at 5:57 pm
Thanks for the tips guys -- I'll definitely look into these options moving forward.
May 10, 2020 at 12:26 am
Hi Jeff,
This process was originally written in VBA, in an MS Access Application. We have moved our back end to Azure SQL Server, so it was easiest for me to write this using VB, but I'm curious if there is a preferred way of doing this (I'll have to write more of these types of scripts, so if there's a better way, I'm all ears)
This job is scheduled to run once a day, and the point of it is to loop through a list of our company's vendors, and for each vendor create 5 different reports based on the last 24 hours of activity. Those 5 Reports are exported to a single excel file per vendor, titled with the vendor's name and current date, and then sent to said vendor.
From my understanding, the only way to export to excel would be through an SSIS Package, which I have set up, and execute through my vbscript.
Thank you for the feedback. See Frederico's post about using "R" to do this.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply