April 8, 2009 at 3:42 am
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
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.
April 8, 2009 at 5:13 am
hi,
thanks for ur reply,
is it possible to call multiple script files using sqlcmd dynamically?
thanks.
🙂
April 8, 2009 at 9:07 am
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
April 8, 2009 at 11:26 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply