Post reply

Generate script for all objects in a database

  • January 8, 2010 at 2:41 am

    #74208

    There is one SP (SP_GenerateScript) available at the below URL:

    http://www.databasejournal.com/features/mssql/article.php/10894_3401081_1/Automating-Generate-SQL-Script.htm

    Explanation for the switches in the SP can be found at the below URL:

    http://www.sqlservercentral.com/articles/Administration/howtoscheduleasqlserverdatabasecreationscript/1834/

    When i execute the SP script files are created successfully. Separate script files each for Table, SP, Views, etc are created. But my requirement is to create a single file for all objects.

    There is an occurence in the SP like

    set @query = '"'+@applicationpath +'"'+ ' /s '+@@servername+ ' /d '+@dbname+ +' /F '+@destinationpath+ @switches

    As per the explanation in the Switches, /F means separate file for different category of objects.

    Hence is replaced /F with /f, which means all script in a single file. Now i get an error like the one below:

    Scripting failed.

    ERROR: Executing ScriptTransfer

    Error Code: 5

    Error Category: 0

    Source: Microsoft SQL-DMO

    Description: [SQL-DMO]CreateFile error on 'd:\pubs20100108'. Access is denied.

    NULL

    NULL

    Can anyone help me out on how to resolve this?

  • January 10, 2010 at 11:49 am

    #1101539

    Looks like a permissions issue to me. Is the directory/user/etc you used when the script executed successfully the same as the ones you used when it failed?

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply