Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    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.

    Any ideas?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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
    FROM INFORMATION_SCHEMA.ROUTINES
    Paul

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yes, this works perfectly. Apparantly this should have been a tsql question...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Teddy

    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In case you need permissions:

    Code:
    SELECT O.name, U.name
    FROM syspermissions P INNER JOIN sysobjects O ON
      O.ID = P.id
     INNER JOIN sysusers U ON
      U.uid = P.grantee
    WHERE xtype = 'P'
    and category = 0
    Can't see an information_schema view that covers this. MS don't recommend querying system tables directly of course...
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •