December 5, 2017 at 2:21 pm
Can anyone help me? My requirement is to execute a MS Access db (w/ imbedded autoexec) as a 'step' from within a SQL Server Agent job. Any thought on this would be appreciated.
December 6, 2017 at 2:10 pm
And just what are you hoping to accomplish by doing that? It will happen on the same machine that the SQL Agent is running on, assuming that MS Access is indeed installed in the location you've specified, and typically, that's a server, which in most cases is not where you'd want that to happen, so I'll assume this is your local machine. Why do it? Not sure about setting options on that command line, but you'd want to probably use START command so that the agent job doesn't keep running until you close out of MS Access. Have I missed anything?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 6, 2017 at 2:16 pm
Doesn't Access want access to the desktop when it opens? Had this issue years ago at a previous employer. The fix was to have someone open the Access app and click a button to parse/cut itself to the minimum needed for reporting to a client.
December 7, 2017 at 7:10 am
Lynn Pettis - Wednesday, December 6, 2017 2:16 PMDoesn't Access want access to the desktop when it opens? Had this issue years ago at a previous employer. The fix was to have someone open the Access app and click a button to parse/cut itself to the minimum needed for reporting to a client.
That's what I was trying to get at... Whatever machine the SQL Agent is running on is where it will look in that C: drive location specified and try to run the specified program locally. As most SQL Agent machines are servers, save for the occasional developer laptop, it's not usually going to help anyone do anything to start MS Access on the server. However, that said, I don't think it's impossible to start the execution elsewhere. I think I remember seeing some kind of location parameter for a command line that could execute a program on another machine, and failing that, EXECUTE AT in T-SQL might work under the right circumstances, which including enabling xpcmdshell, although it seems unlikely a business would willingly sign up for such a scenario.
As to "The fix was to have someone open the Access app and click a button to parse/cut itself to the minimum needed for reporting to a client.", I'm not aware of any feature in MS Access that you can use after opening the application that would do something of that nature, but you CAN have a different installation of just the MS Access runtime code (or at least you used to be able to do that).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 7, 2017 at 7:23 am
MS Access and the Agent are on the same box. The Access app runs an Autoexec which does some required stuff then closes the app and Access. My desired next step is to run a Stored Proc from the agent.
Or...
A method of importing csv files into a SQL table where the file layouts do not match... Excel as well... just asking
December 7, 2017 at 7:33 am
Garth Zaleschuk - Thursday, December 7, 2017 7:23 AMMS Access and the Agent are on the same box. The Access app runs an Autoexec which does some required stuff then closes the app and Access. My desired next step is to run a Stored Proc from the agent.
Or...
A method of importing csv files into a SQL table where the file layouts do not match... Excel as well... just asking
If you're looking to import a CSV file into a SQL table where the file layouts do not match, SSIS is a rather handy tool. You can re-format data, change data type, and do pretty much whatever is needed, including logging error records (rows in the CSV file that for whatever reason don't meet specifications or might violate a table constraint), much of which would be difficult to control within MS Access, and requires VBA skills to accomplish. I'm guessing you or someone at your organization has (or had) those skills when this MS Access app was developed. It's usually much easier to re-create that app's functionality in SSIS than to mess with the consequences of running MS Access on a server. Not that you can't do it, but you may need to find a way to run that app in a way that it doesn't need to display itself on a desktop, as the SQL Agent execution context is the SQL Agent service account, which isn't going to have an interactive login context on the server from a Windows program execution perspective.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 7, 2017 at 7:33 am
Garth Zaleschuk - Thursday, December 7, 2017 7:23 AMA method of importing csv files into a SQL table where the file layouts do not match... Excel as well... just asking
Have you considered using SSIS?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 7, 2017 at 7:39 am
Hey thanks for the quick responses... yes direct from csv or Excel would be the preferred method, but I have searched but the methods described are a little to ... whatever, I am a newbie to this and would appreciate a simple sample of input file to insert into the SQL table.
Hope I am not asking for too much... all responses are helpful
December 7, 2017 at 7:49 am
Garth Zaleschuk - Thursday, December 7, 2017 7:39 AMHey thanks for the quick responses... yes direct from csv or Excel would be the preferred method, but I have searched but the methods described are a little to ... whatever, I am a newbie to this and would appreciate a simple sample of input file to insert into the SQL table.Hope I am not asking for too much... all responses are helpful
There needs to be someone with SSIS skills to make use of that tool, but it can be learned. Do you have any procedural language background? If you've used VBA, you can pick it up fairly quickly. At the least, take a look at the following video:
I can't vouch for the quality, but there are bound to be a LOT more of that kind of info out on the web. Google SSIS and import CSV file or Excel file and you're going to see a LOT of entries.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 7, 2017 at 8:36 am
sgmunson - Thursday, December 7, 2017 7:10 AMLynn Pettis - Wednesday, December 6, 2017 2:16 PMDoesn't Access want access to the desktop when it opens? Had this issue years ago at a previous employer. The fix was to have someone open the Access app and click a button to parse/cut itself to the minimum needed for reporting to a client.That's what I was trying to get at... Whatever machine the SQL Agent is running on is where it will look in that C: drive location specified and try to run the specified program locally. As most SQL Agent machines are servers, save for the occasional developer laptop, it's not usually going to help anyone do anything to start MS Access on the server. However, that said, I don't think it's impossible to start the execution elsewhere. I think I remember seeing some kind of location parameter for a command line that could execute a program on another machine, and failing that, EXECUTE AT in T-SQL might work under the right circumstances, which including enabling xpcmdshell, although it seems unlikely a business would willingly sign up for such a scenario.
As to "The fix was to have someone open the Access app and click a button to parse/cut itself to the minimum needed for reporting to a client.", I'm not aware of any feature in MS Access that you can use after opening the application that would do something of that nature, but you CAN have a different installation of just the MS Access runtime code (or at least you used to be able to do that).
You would be amazed what could be done. I didn't do it, but a co-worker wrote the VB code that ripped and consolidated the Access database to the bare minimum needed to provide the necessary reporting to the vendor.
December 7, 2017 at 9:23 am
Thanks for the responses... I am naturally lazy and was looking for the easy way out... just feed me 🙂 I don't want to fish 🙂
I am using the visual studio method (from the YouTube link) and working my way through. Removing Access from the process is a 'Best Practice' solution.
Again thanks and "I'll be back" I am certain with question on this 🙂
Garth
December 7, 2017 at 9:29 am
Garth Zaleschuk - Thursday, December 7, 2017 9:23 AMThanks for the responses... I am naturally lazy and was looking for the easy way out... just feed me 🙂 I don't want to fish 🙂I am using the visual studio method (from the YouTube link) and working my way through. Removing Access from the process is a 'Best Practice' solution.
Again thanks and "I'll be back" I am certain with question on this 🙂
Garth
Just remember, you have to support your solution so learn to fish. 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply