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.
mirik123Asked:
Who is Participating?
 
mirik123Connect With a Mentor Author Commented:
Hi,
This code works well from IS but it throws some unknown error in try/catch.

try
set conn = CoCreateObject("ADODB.Connection");
sConnString = "Provider=SQLOLEDB;User ID="+strusr+";password="+strpsw+";Data Source="+strsrv;
sExecString =
   "exec xp_cmdshell 'net use \\\\pcname\\v360_database_backup winpass /user:winuser';"+
   "use master;"+
   "RESTORE DATABASE v360_22 "+
   "FROM DISK = '\\\\itnvis\\v360_database_backup\\v360_233_06042010.bak' WITH RECOVERY,"+  
   "MOVE 'v360' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\v360_22.mdf',"+
   "MOVE 'v360_log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\v360_22_log.ldf';";
   res = conn.Open(sConnString);
      if (res < 0) then
         NumToStr(str2,res);
         MessageBox("Connection open failed: " + str2,MB_OK);
      endif;
   res = conn.execute(sExecString);
   conn.Close();
    set conn = NOTHING;
catch
       MessageBox("Error setting database", MB_OK);
 endcatch;
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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.
0
 
mirik123Author Commented:
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).
0
 
mirik123Author Commented:
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;";
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.