writing database scripts (SQLServer)
got an email from someone reading the boards ...
I need to create an installer which deploys a web application and then runs a couple scripts on an SQL server 2000 database.
I’ve got the first part covered, but I really am no too sure how to run a script on the SQL server 2000 database.
Can you provide any assistance?
i've done this in my installer. i've set up a SQLServer database, logins, scheduled jobs, linked servers, created tables, stored procedures, etc. the best way to do this (IMHO) is to use the nsExec plugin.
after you've checked to make sure SQLServer and the SQLServer tools are installed on the destination server (see the archives for how to do this), you can use the <b>osql.exe</b> command to run scripts. here's an example:
... will allow you to execute a T-SQL script from the command line, using nsExec, all silently with output going to the details window.nsExec::Exec/TIMEOUT=20000'"$SQLTOOLS_PATH\osql.exe"-Usa
-P$SA_PWD-i"$INSTDIR\db\CREATE_DB.sql"-o"$INSTDIR\db\CREATE_DB.log"'
for one-off SQL statements, try:
please ignore the line breaks in the above examples. hope this helps ... and please post questions here, don't email me personally. :)nsExec::ExecToLog/TIMEOUT=20000`"$SQLTOOLS_PATH\osql.exe"-Usa
-P$SA_PWD-d"master"-Q
"IFEXISTS(SELECTsrvnameFROMmaster.dbo.sysservers
WHEREsrvname=N'MYREMOTESERVER'ANDisremote=1)
EXECsp_dropserver@server='MYREMOTESERVER',@droplogins='droplogins'"`