Post reply

script generation to update in production, using batch file.

  • April 8, 2009 at 3:42 am

    #155531

    hi,

    i need some help about production script updation.

    at present i used to manually generate the script by using SMO, Then that script will be sent to production team. they will open that file in the SSMS/QUERY ANLYZER AND RUN THE SCRIPT. I heard we can do by creating batch files. how can i do?

    thanks,

    🙂

  • April 8, 2009 at 4:28 am

    #974132

    You can save your script in .sql and the production team use sqlcmd to execute.

    Lets say you have an update to do

    use foodatabase

    go

    update table1 set foo = 'another foo'

    then you save this script in .sql..something like foo.sql

    send to production team

    and they use in cmd prompt

    sqlcmd -S myServer\instanceName -i C:\foo.sql

    if want to save the output they can use

    sqlcmd - S myServer\instanceName -i C:\foo.sql - o C:\out.txt

    to know..Look for sqlcmd utitlity in msdn...its a very powerfull command.

    $hell your Experience !!![/url]

  • April 8, 2009 at 5:13 am

    #974157

    hi,

    thanks for ur reply,

    is it possible to call multiple script files using sqlcmd dynamically?

    thanks.

    🙂

  • April 8, 2009 at 9:07 am

    #974402

    I dont know very well sqlcmd utility, i think its not possible (i really dnt know..we have to read the documentation and do some test).

    But if not,

    You can put all .sql in one

    or

    i think you dont scape from a little programming.

    You can do a .exe (vb net, c#), or .vbs ..to read all .sql from a path and execute calling sqlcmd or executenonquery method from SMO.

    look this

    http://wardyit.com/blog/blog/archive/2006/11/24/169.aspx

    $hell your Experience !!![/url]

  • April 8, 2009 at 11:26 am

    #974540

    I did something in powershell to do this

    $ServerName = "Server1"

    $DatabaseName = "Testes"

    foreach ($path in Get-item "c:\projetos\teste\*.sql")

    {

    Invoke-Sqlcmd -ServerInstance $ServerName -Database $DatabaseName -InputFile $path

    }

    This script search all .sql files in c:\projetos\teste folder and execute them in server Server1 and Database Testes

    PS - to use this in Powershell shell have to load sql server snapins into profile. In SQLPS.exe (sql2k8) does not need.

    add-pssnapin SqlServerProviderSnapin100

    add-pssnapin SqlServerCmdletSnapin100

    you can transform this into a function and send servername and databasename..etc..many possibilities.

    $hell your Experience !!![/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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