asked on

Cannot create database from InstallShield SQL script

exec xp_cmdshell 'net use \\\\pcname\\v360_database_backup localpass /user:domain\\localuser';

use master;
RESTORE DATABASE v360_22_t291
FROM DISK = '\\\\pcname\\v360_database_backup\\v360_233_06042010.bak' WITH RECOVERY,
MOVE 'v360' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\v360_22_t291.mdf',
MOVE 'v360_log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\v360_22_t291_log.ldf';

Hi all.
I can sucessfully run this script directly from SQL Server.
When I run it from InstallShield view "Server Configuration --> SQL Scripts" it gives no result -- no database created and no error shown.
If I run any other code from there like, create db user or update table it produces result correctly.

Raja Jegan R

>> SQL Scripts" it gives no result -- no database created and no error shown.

Definitely a permissions issue
Kindly check the credentials used for creating database using the above script.
Syntax of Above script is fine.

mirik123

ASKER

When I run it from command line it also works.
May be InstallShield uses its own SQL engine?

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>sqlcmd -S pcname01 -U sa -P sapass -i D:\sql.sql
output
-------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
---------------
The command completed successfully.
NULL
NULL
(0 rows affected)
Changed database context to 'master'.
(0 rows affected)
Processed 2144 pages for database 'v360_22', file 'v360' on file 1.
(0 rows affected)
Processed 8 pages for database 'v360_22', file 'v360_log' on file 1.
RESTORE DATABASE successfully processed 2152 pages in 10.297 seconds (1.711 MB/sec).

ASKER CERTIFIED SOLUTION

mirik123

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mirik123

ASKER

And this is also working :)

sExecString =
"declare @datalog varchar(250),@datamdf varchar(250), @data_path varchar(250);"+
"Select @data_path=Reverse(substring(ltrim(reverse([FileName])),charindex('\\',Ltrim(reverse([Filename]))),datalength(Ltrim(reverse([Filename]))))) From sysfiles Where groupid = 1;"+
"set @datamdf = @data_path + '"+strdb+".mdf';"+
"set @datalog = @data_path + '"+strdb+"_log.ldf';"+
"exec xp_cmdshell 'net use \\\\"+pcname+"\\nav360_database_backup "+strsharepass+" /user:"+strshareuser+"';"+
"use master;"+
"RESTORE DATABASE "+strdb+" "+
"FROM DISK = '\\\\"+pcname+"\\v360_database_backup\\"+strsharefile+"' WITH RECOVERY,"+
"MOVE 'nav360' TO @datamdf,"+
"MOVE 'nav360_log' TO @datalog;";