Execute SQL Server scripts: Difference between revisions

From NSIS Wiki
Jump to navigationJump to search
m (Added category links.)
(Discussed SQL Server 2005 and later)
 
(8 intermediate revisions by 6 users not shown)
Line 1: Line 1:
{|align=right
{{PageAuthor|bazzargh}}
|<small>Author: [[{{ns:2}}:bazzargh|bazzargh]] ([[{{ns:3}}:bazzargh|talk]], [[{{ns:-1}}:Contributions/bazzargh|contrib]])</small>
 
|}
<br style="clear:both;">
== Description ==
== Description ==
With SQL Server 2000...a little bit untidy at present. This executes the script and dumps the output in the detail window. Todo: only dump the output if there's errors (and thats why I use '-b')
With SQL Server 2000...a little bit untidy at present. This executes the script and dumps the output in the detail window. Todo: only dump the output if there's errors (and thats why I use '-b')
Line 9: Line 7:
<highlight-nsis>
<highlight-nsis>
         ; path to the sql command line tools
         ; path to the sql command line tools
   ReadRegStr $R1 HKLM "SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup" "SQLPath"
   ReadRegStr $R1 HKLM "SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup"
                            "SQLPath"
         ; the computer name. obviously you can prompt for a host
         ; the computer name. obviously you can prompt for a host
   ReadRegStr $R2 HKLM "SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName" "ComputerName"
   ReadRegStr $R2 HKLM "SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName"
                            "ComputerName"
GetTempFileName $R0
GetTempFileName $R0
ExecWait '"$R1\Binn\isql.exe" -U "some-username" -P "some-password" -s "$R2" -d master -i "some-file.sql" -o "$R0" -b'
ExecWait '"$R1\Binn\isql.exe" -U "some-username" -P "some-password" -S "$R2" -d master  
                  -i "some-file.sql" -o "$R0" -b'
ClearErrors
ClearErrors
Line 33: Line 34:
</highlight-nsis>
</highlight-nsis>


[[{{ns:14}}:Database Functions]]
== SQL Server 2005 and later ==
 
SQL Server 2005 replaced isql and osql with sqlcmd. I discuss a solution for these versions of SQL Server on [http://stackoverflow.com/a/9841682/95195 this stackoverflow answer].
 
[[Category:Database Functions]]

Latest revision as of 15:16, 23 March 2012

Author: bazzargh (talk, contrib)


Description

With SQL Server 2000...a little bit untidy at present. This executes the script and dumps the output in the detail window. Todo: only dump the output if there's errors (and thats why I use '-b')

The Script

        ; path to the sql command line tools
  	ReadRegStr $R1 HKLM "SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup"
                            "SQLPath"
        ; the computer name. obviously you can prompt for a host
  	ReadRegStr $R2 HKLM "SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName"
                            "ComputerName"
	GetTempFileName $R0
	ExecWait '"$R1\Binn\isql.exe" -U "some-username" -P "some-password" -S  "$R2" -d master 
                  -i "some-file.sql" -o "$R0" -b'
 
	ClearErrors
        ; this dumps the logfile to the detail window
	FileOpen $R1 $R0 "r"
  	IfErrors FileOpenFailed
	MoreMessages:
		ClearErrors
		FileRead $R1 $R0
		IfErrors NoMoreMessages
		StrCpy $R0 $R0 -2 ; remove newline
		DetailPrint $R0
		Goto MoreMessages
	FileOpenFailed:
	DetailPrint "Can't read from $R0"
	NoMoreMessages:
	FileClose $R1
	ClearErrors

SQL Server 2005 and later

SQL Server 2005 replaced isql and osql with sqlcmd. I discuss a solution for these versions of SQL Server on this stackoverflow answer.