Unanswered: Pull qry/sp command text into access from mssql through dao
This one has been baking my noodle for a while now...
I've been tasked with creating "alternative" backup solutions using an access adp against MSSQL.
Basically, I need to backup the table structures and data (done), and I need to backup all current stored procedures and views (tricky).
To backup the tables, I simply iterated through the allTables collection and looked for anything that didn't meet the profile of a system table. Then I fed that to OutputTo, no problems
I'm not sure how to pull the TEXT for all of the stored procedures and queries. I can pull the data they return without an issue, but not the commandtext itself. I tried hitting it with DAO and dynamically assigning a command object but didn't have much luck.
I have to run, but this works from QA to get the name and the actual text from every proc and function in the selected db. You'd need to tweak it to get all db's, but at least it gives you somewhere to start. If you don't get it resolved, I can play later when I get back.
SELECT Specific_name, routine_definition
Teddy, I got a quick chance to try this before I left work, and I was able to open an ADO recordset in Access that returned the text of the SP's for the db named in the connection string. I assume from there you can store it wherever you wanted. Let me know if that helps.
Beware a minor gotcha on this - the INFORMATION_SCHEMA.ROUTINES view is based on the syscomments table. The column in syscomments that contains the text as data is limited to 8000 chars. If the actual chars is >8000 then the sproc text is returned in 8000/ (No of chars in sproc) rows - rounded up of course.
Caught me out once...it was carnage...O the horror...I swore it would never happen again...
Anyhoo - aren't these things backed up in their normal backup routine? A backup of the db or even just syscomments should cover this.
In theory there is a backup plan in place for the entire sql server. Due to a highly compartmentalized organization structure, my direct client has requested "alternative" backups that can be managed locally.