Store and run SQL Server 2005 scripts from MS Access

Situation: I'm in charge of decommissioning a SW product with the Back-End in a SQL Server database. We approximately 90 clients that we will have to pull multiple tables down into a combo of CSV and XML files on disk for them to reference once the SW is gone. Each client has its own separate SQL DB.

I have many of the SQL Scripts and SP's written or mostly written to dump the data to tables in the SQL BE. I can automate the linking to the tables from an Access DB to read and export to the appropriate files.

The databases are all SQL 2005 Standard edition and it is an Access 2003 front-end.

The Issue: My T-SQL scripts and custom SP's to extract the data to the tables runs into several thousand lines of code already. I'm going to have "novices" in SQL that would have to run the scripts for each SQL DB and then fire up the Access to run the exports. I'd like to automate the process of running the SQL Scripts from the Access DB. The steps as I envision them:


They fire up Access -- it creates a DSN and auto links to the master.sys.database table.


Then a form pops up saying "this is your choice of databases/clients.


They pick the DB and then Access runs the T-SQL scripts against that database.


Then Access links to the export tables and will start dumping data to disk.
Step 3 is where I'm getting stuck trying to think around the corner. I want to make it easy for the techs running this to "click" and go. I'm trying to avoid having them do too many steps.

I don't want to bother with SSIS because of the limited amount of time to build the packages and the "one-time" use of this package.

Any thoughts on how to do this?
LVL 38
Jim P.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Not sure if i understand the problem, but I'll give it a try.

After picking a DB in step 3, I suppose you also have a connection string to that db. You can use that to create an "ADODB.Connection" object and the run the TSQL quesries using that object.

You need some VBA to do that.

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sDSN
oConn.Close: Set oConn = Nothing

Open in new window

Jim P.Author Commented:
The problem is that one stored proc alone is about 700 lines of T-SQL. Then I have the scripts which actually run the stored proc which runs into about 300 lines. And that is just getting one of the tables. I'm going to have to extract about 15 tables and run about 2-300 lines of code per table. Trying to make one massive script is impractical. I'm getting lost in my own coding now -- even with comments.

I don't want to have to do the export work by myself. I want the other techs to be able to run it.

Going with ADODB solution, wouldn't I have to recode all my TSQL into strings in the DB?
Hmm. Not sure how you get so many lines of code to get things out of a database. I know things can get complicated, but this sounds a little too much.

But anyway, I suppose that the stored procedures are in the customer databases, and you can just call them by name.

To be honest, I have no idea what an "SW product" is.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim P.Author Commented:
Software Product. My company does software for the healthcare industry. I'm trying to be generic so if someone is looking to export for the auto, accounting, etc. industry they can use a similar solution.
Jim P.Author Commented:
This is what I came up with.

I ended up making a table in the DB that holds the scripts in a memo field. I then use the following function to run the scripts via the sqlcmd function.

Public Function Run_SQL_Scripts(ServerName As String, FacilityDB As String)

Dim SQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Dim FileName As String
Dim FileNum As Integer
Dim OutputLine As String
Dim Directory As String

Dim RunScriptCmd As String

SQL = "SELECT SQL_Script_Name, SQL_Script_Text " & _
    "FROM SQL_Scripts_Tbl " & _
    "WHERE FinalExportScript = True " & _
    "ORDER BY Order_ID"
'SQL_Script_Name SQL_Script_Text Order_ID    IDNum   FinalExportScript

'Open Scripts table
Set DB = CurrentDb
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = False Then
    MsgBox "No Data", vbExclamation, "Exiting Function"
    Set RS = Nothing
    Set DB = Nothing
    Exit Function
End If

'Make a folder to hold the scripts
Directory = Left(CurrentProject.FullName, InStrRev(CurrentProject.FullName, "\")) & "SQLScripts\"
If Len(Dir(Directory, vbDirectory)) = 0 Then
    MkDir Directory
End If

'Actually export the scripts.
Do While RS.EOF = False
    FileNum = FreeFile()
    Open Directory & RS!SQL_Script_Name For Output Access Write Lock Write As FileNum
    Print #FileNum, RS!SQL_Script_Text
    Close #FileNum

'This function writes a 64K text file to make sure the files are _
 actually written to disk


'Run the scripts in specified order with a 5 second delay between each. _
 The first two scripts are custom SPs and functions to make _
 the rest of the stuff work.
Do While RS.EOF = False
    RunScriptCmd = "sqlcmd -S " & ServerName & " -E -d " & FacilityDB & " -i " & Chr(34) & Directory & RS!SQL_Script_Name & Chr(34)
    Call Shell(RunScriptCmd, vbNormalFocus)
    Sleep (5000) ' Waits for 5 seconds

Set RS = Nothing
Set DB = Nothing

End Function

Open in new window

Not pretty, but it works.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Author Commented:
Anthony PerkinsCommented:
Another approach you can take, provided this is not SQL Server Express, is to run the script(s)/Stored Procedure(s) as a SQL Server Agent job.  The big advantage here is that it run asynchronously.
Jim P.Author Commented:

These are going to be one off jobs. We're shutting the SW and have to provide the data to the customers for retention.  There is no really good way to create generic jobs that will get around this either.

Or am I missing something?
Anthony PerkinsCommented:
>>Or am I missing something? <<
Maybe.  If you can encapsulate all the code in Stored Procedure(s), you can then call the same Stored Procedure(s) from a job.  Alternatively you can create a job with all the SQL script(s)/Stored Procedure(s) and delete it on the fly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.