NSIS execute *.bat or *.exe

H-Block
15th May 2009 15:01 UTC

NSIS execute *.bat or *.exe
Hallo,
i try to explain my problem.
I want to execute a .bat file from to installer, that attach a database to a SQLEXPRESS 2005. The Same with the .exe file.

Here is the .bat content:
sqlcmd -U user -P pw -S %computername%\test -Q "sp_attach_db 'test','%Programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf','%Programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_log.ldf'"

Now my problem when I execute the .bat or exe file self it works. When I execute it from the installer not.

Help me please.

jpderuiter
15th May 2009 17:29 UTC

How do you call the bat file?

You can use the MSSQL OLEDB plug-in to attach the database as well (usefull if you have to do more than just attaching).
http://nsis.sourceforge.net/MSSQL_OLEDB_plug-in

H-Block
18th May 2009 06:52 UTC

Okay i tried it with MSSQL OLEDB.
Login into SQLEXPRESS Works but then came "Quary Failed".Why?

Here the script:
NSIS
SetOutPath "$Temp"
File "D:\db_connect.sql"
ReadRegStr $R3 HKLM "SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName" "ComputerName"
MSSQL_OLEDB::SQL_Logon "$R3\SQLEXPRESS" "SA" "TEST"
MSSQL_OLEDB::SQL_Execute "$Temp\db_connect.sql"
MSSQL_OLEDB::SQL_Logout

db_connect.sql
sp_attach_db 'test','%Programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf','%Programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_log.ldf'

Thank you for your help

Okay I found my problem i used MSSQL_OLEDB::SQL_Execute and not MSSQL_OLEDB::SQL_ExecuteScript. But now came "Error initializing script". Why?

jpderuiter
18th May 2009 07:21 UTC

Try following as your sql script

USE [master]
GO
CREATE DATABASE [test] ON
( FILENAME = N'%Programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test.mdf' ),
( FILENAME = N'%Programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_log.ldf' )
FOR ATTACH
GO

H-Block
18th May 2009 08:34 UTC

Thank you for your fast answer.
I tried your code but it doesn't work.
There is the same error. Error initializing script.
When I testing it with the Example from MSSQL_OLEDB the Error is:
Extract: test.sql
Loggin on to SQL server Notebook\test
0
Logon successfull
0
SQL State: 0x00000000 - Native: 0 - Message:
1
Query Failed
0
SQL State: 0x80040e14 - Native: 0 - Message: Wrong Syntax in the near of '\'.
1
Error getting rowset
1
Error getting rowset
0
SQL State: 0x00000000 - Native: 0 - Message:
0
Script executed successfully
0
SQL State: 0x00000000 - Native: 0 - Message:
0
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

2
No more data
0
SQL State: 0x00000000 - Native: 0 - Message:
Completed

When I connect to the SQL server over cmd--> sqlcmd
and paste your code it works.

H-Block
18th May 2009 09:38 UTC

Okay I found my problem and fixed it.
Now I have another Problem. I want to create a user with this script:
MSSQL_OLEDB::SQL_Execute "CREATE LOGIN test WITH PASSWORD = '123456';USE testdb;CREATE USER test FOR LOGIN test WITH DEFAULT_SCHEMA = dbo;Exec sp_addrolemember'db_owner','test'"

Login into server is okay. Have I anything vorgotten?

jpderuiter
18th May 2009 09:50 UTC

Okay I found my problem and fixed it.

How did you solve it?

Now I have another Problem. I want to create a user with this script: MSSQL_OLEDB::SQL_Execute "CREATE LOGIN test WITH PASSWORD = '123456';USE testdb;CREATE USER test FOR LOGIN test WITH DEFAULT_SCHEMA = dbo;Exec sp_addrolemember'db_owner','test'" Login into server is okay. Have I anything vorgotten?

You can pass only one SQL command to MSSQL_OLEDB::SQL_Execute.
Use this instead:

MSSQL_OLEDB::SQL_Execute /NOUNLOAD "CREATE LOGIN test WITH PASSWORD = '123456'"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "USE testdb"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "CREATE USER test FOR LOGIN test WITH DEFAULT_SCHEMA = dbo"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "Exec sp_addrolemember 'db_owner','test'"

H-Block
18th May 2009 10:37 UTC

Okay Thank you it Works.
Here is the code I used.

For SQLEXPRESS install:
Function ExistsMSDE
ExecWait "$Temp\SQL.EXE -q /norebootchk /qb reboot=ReallySuppress addlocal=all instancename=SQLEXPRESS SCCCHECKLEVEL=IncompatibleComponents:1;MDAC25Version:0 ERRORREPORTING=1 SQLAUTOSTART=1 SAPWD=test SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0"
Sleep 10000 ; wait for a 10 seconds
FunctionEnd

For Database:
ReadRegStr $R3 HKLM "SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName" "ComputerName"
MSSQL_OLEDB::SQL_Logon "$R3\SQLEXPRESS" "sa" "test"
MSSQL_OLEDB::SQL_Execute "CREATE DATABASE [DB_name] ON ( FILENAME = N'$Programfiles\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB.mdf' ),( FILENAME = N'$Programfiles\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_log.ldf' ) FOR ATTACH GO"
MSSQL_OLEDB::SQL_Logout

For User:
ReadRegStr $R3 HKLM "SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName" "ComputerName" MSSQL_OLEDB::SQL_Logon "$R3\SQLEXPRESS" "sa" "test"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "CREATE LOGIN user WITH PASSWORD = 'PW'"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "USE DB_name"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "CREATE USER user FOR LOGIN user WITH DEFAULT_SCHEMA= dbo_owner"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "Exec sp_addrolemember 'db_owner', 'user'"
MSSQL_OLEDB::SQL_Logout

Thank you for your fast Help.