script generation to update in production, using batch file.

  • 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,

    🙂

  • 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]

  • hi,

    thanks for ur reply,

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

    thanks.

    🙂

  • 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]

  • 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