Professional Web Applications Themes

Automating SQL Script Generation - Microsoft SQL / MS SQL Server

Hello, I was wondering if anyone know where I can find a proc or something that will automate my sql script generation? Instead of manually right clicking on your database and selecting generate sql script, I want to be able to schedule a job or something that runs a proc to script all the objects in the database. I hope I explained myself well enough. Thanks all....

  1. #1

    Default Automating SQL Script Generation

    Hello,

    I was wondering if anyone know where I can find a proc or
    something that will automate my sql script generation?
    Instead of manually right clicking on your database and
    selecting generate sql script, I want to be able to
    schedule a job or something that runs a proc to script all
    the objects in the database. I hope I explained myself
    well enough. Thanks all.
    Eli Guest

  2. #2

    Default Re: Automating SQL Script Generation

    Scripting is done by DMO (Distributed Management Objects). You can create a
    scheduled job with VB Script task that uses DMO. Here is code example:

    Dim oSS As SQLDMO.SQLServer
    Dim oDb As SQLDMO.Database
    Dim oT As SQLDMO.Transfer
    Dim sS As String

    Sub Script()
    Set oSS = New SQLDMO.SQLServer
    Set oT = New SQLDMO.Transfer
    oSS.Connect "server", "login", "password" 'Connect to the server
    Set oDb = oSS.Databases("pubs")
    oT.CopyAllTables = True
    oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
    End Sub

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org

    "Eli" <com> wrote in message
    news:059601c36b3e$016d4ab0$gbl... 


    Dejan Guest

  3. #3

    Default Re: Automating SQL Script Generation

    I modified the example to make it work for VBScript. Just
    cut and past the text into a .vbs file


    Dim oSS
    Dim oDb
    Dim oT

    Set oSS = CreateObject("SQLDMO.SQLServer")
    Set oDb = CreateObject("SQLDMO.Database")
    Set oT = CreateObject("SQLDMO.Transfer")

    oSS.Connect "[server_name]", "[username]", "[password]" 'C
    onnect to the server
    Set oDb = oSS.Databases("pubs")
    oT.CopyAllTables = True
    oDb.ScriptTransfer oT,2,"C:\pubs.sql"


    Edgardo Valdez
    MCSD, MCDBA, MCSE, MCP+I
    http://www.edgardovaldez.us/
     
    Objects). You can create a 
    code example: 
    to the server 
    SQLDMOXfrFile_SingleFile, "C:\pubs.sql" [/ref]
    or [/ref]
    all 
    >
    >
    >.
    >[/ref]
    Edgardo Guest

  4. #4

    Default Re: Automating SQL Script Generation

    If somebody wants to use the same example as a job in SQL 2000
    the example with 3 added lines at the bottom should look like this :

    Dim oSS
    Dim oDb
    Dim oT
    Set oSS = CreateObject("SQLDMO.SQLServer")
    Set oDb = CreateObject("SQLDMO.Database")
    Set oT = CreateObject("SQLDMO.Transfer")
    oSS.Connect "SERVER", "User", "Pass"
    Set oDb = oSS.Databases("pubs")
    oT.CopyAllTables = True
    oDb.ScriptTransfer oT,2,"C:\pubs.sql"
    Set oT = Nothing
    Set oDB = Nothing
    Set oSS = Nothing

    AT




    "Edgardo Valdez" <com> wrote in message
    news:037901c36b52$c977ae60$gbl... 
    > Objects). You can create a 
    > code example: 
    > to the server 
    > SQLDMOXfrFile_SingleFile, "C:\pubs.sql" [/ref]
    > or [/ref]
    > all 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Aleksandar Guest

Similar Threads

  1. HTML Generation (Next Generation CGI)
    By John W. Long in forum Ruby
    Replies: 4
    Last Post: November 24th, 04:24 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139