Page 2 of 4 FirstFirst 1234 LastLast
Results 6 to 10 of 20

Thread: STRANGE: SQL Script not throwing any error

  1. #6
    Join Date
    Oct 2001
    Location
    Schaumburg, IL
    Posts
    2,190
    SQL scripting errors do not seem to get set to the SQL Server ODBC driver when you send all the script as a batch. To get it workaround, you need to change the script to have each block followed by a batch separator as follows:

    USE [master]
    GO

    IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'db1')
    BEGIN
    CREATE DATABASE [db1] ON PRIMARY
    ( NAME = N'db1', FILENAME = N'C:\Databases\Main\db1.mdf' , SIZE = 5000KB , FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = N'db1_log', FILENAME = N'C:\Databases\Main\db1_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

    EXEC dbo.sp_dbcmptlevel @dbname=N'db1', @new_cmptlevel=90

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [db1].[dbo].[sp_fulltext_database] @action = 'disable'
    end

    ALTER DATABASE [db1] SET ANSI_NULL_DEFAULT OFF

    ALTER DATABASE [db1] SET ANSI_NULLS OFF

    ALTER DATABASE [db1] SET ANSI_PADDING OFF

    ALTER DATABASE [db1] SET ANSI_WARNINGS OFF

    ALTER DATABASE [db1] SET ARITHABORT OFF

    ALTER DATABASE [db1] SET AUTO_CLOSE OFF

    ALTER DATABASE [db1] SET AUTO_CREATE_STATISTICS ON

    ALTER DATABASE [db1] SET AUTO_SHRINK OFF

    ALTER DATABASE [db1] SET AUTO_UPDATE_STATISTICS ON

    ALTER DATABASE [db1] SET CURSOR_CLOSE_ON_COMMIT OFF

    ALTER DATABASE [db1] SET CURSOR_DEFAULT GLOBAL

    ALTER DATABASE [db1] SET CONCAT_NULL_YIELDS_NULL OFF

    ALTER DATABASE [db1] SET NUMERIC_ROUNDABORT OFF

    ALTER DATABASE [db1] SET QUOTED_IDENTIFIER OFF

    ALTER DATABASE [db1] SET RECURSIVE_TRIGGERS OFF

    ALTER DATABASE [db1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

    ALTER DATABASE [db1] SET DATE_CORRELATION_OPTIMIZATION OFF

    ALTER DATABASE [db1] SET PARAMETERIZATION SIMPLE

    ALTER DATABASE [db1] SET READ_WRITE

    ALTER DATABASE [db1] SET RECOVERY FULL

    ALTER DATABASE [db1] SET MULTI_USER

    ALTER DATABASE [db1] SET PAGE_VERIFY CHECKSUM

    IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
    ALTER DATABASE [db1] MODIFY FILEGROUP [PRIMARY] DEFAULT

    USE [master]
    restore database db1 from disk = N'D:\Installer\DB\db1.bak' with replace
    END
    GO

    USE [master]
    GO

    /** db2 **/
    IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'db2')
    BEGIN
    CREATE DATABASE [db2] ON PRIMARY
    ( NAME = N'db2', FILENAME = N'C:\Databases\db2.mdf' , SIZE = 5000KB ,
    FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = N'db2_log', FILENAME = N'C:\Databases\db2_log.ldf' , SIZE = 1024KB ,
    FILEGROWTH = 10%)

    EXEC dbo.sp_dbcmptlevel @dbname=N'db2', @new_cmptlevel=90

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [db2].[dbo].[sp_fulltext_database] @action = 'disable'
    end

    ALTER DATABASE [db2] SET ANSI_NULL_DEFAULT OFF

    ALTER DATABASE [db2] SET ANSI_NULLS OFF

    ALTER DATABASE [db2] SET ANSI_PADDING OFF

    ALTER DATABASE [db2] SET ANSI_WARNINGS OFF

    ALTER DATABASE [db2] SET ARITHABORT OFF

    ALTER DATABASE [db2] SET AUTO_CLOSE OFF

    ALTER DATABASE [db2] SET AUTO_CREATE_STATISTICS ON

    ALTER DATABASE [db2] SET AUTO_SHRINK OFF

    ALTER DATABASE [db2] SET AUTO_UPDATE_STATISTICS ON

    ALTER DATABASE [db2] SET CURSOR_CLOSE_ON_COMMIT OFF

    ALTER DATABASE [db2] SET CURSOR_DEFAULT GLOBAL

    ALTER DATABASE [db2] SET CONCAT_NULL_YIELDS_NULL OFF

    ALTER DATABASE [db2] SET NUMERIC_ROUNDABORT OFF

    ALTER DATABASE [db2] SET QUOTED_IDENTIFIER OFF

    ALTER DATABASE [db2] SET RECURSIVE_TRIGGERS OFF

    ALTER DATABASE [db2] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

    ALTER DATABASE [db2] SET DATE_CORRELATION_OPTIMIZATION OFF

    ALTER DATABASE [db2] SET PARAMETERIZATION SIMPLE

    ALTER DATABASE [db2] SET READ_WRITE

    ALTER DATABASE [db2] SET RECOVERY FULL

    ALTER DATABASE [db2] SET MULTI_USER

    ALTER DATABASE [db2] SET PAGE_VERIFY CHECKSUM

    IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
    ALTER DATABASE [db2] MODIFY FILEGROUP [PRIMARY] DEFAULT

    USE [master]
    restore database db2 from disk = N'D:\Installer\DB\db2.bak' with replace

    END
    GO

    USE [master]
    GO

    /* db3 */
    IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'db3')
    BEGIN

    CREATE DATABASE [db3] ON PRIMARY
    ( NAME = N'db3', FILENAME = N'C:\Databases\db3.mdf' , SIZE = 5000KB , FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = N'db3_log', FILENAME = N'C:\Databases\db3_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

    EXEC dbo.sp_dbcmptlevel @dbname=N'db3', @new_cmptlevel=90

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [db3].[dbo].[sp_fulltext_database] @action = 'disable'
    end

    ALTER DATABASE [db3] SET ANSI_NULL_DEFAULT OFF

    ALTER DATABASE [db3] SET ANSI_NULLS OFF

    ALTER DATABASE [db3] SET ANSI_PADDING OFF

    ALTER DATABASE [db3] SET ANSI_WARNINGS OFF

    ALTER DATABASE [db3] SET ARITHABORT OFF

    ALTER DATABASE [db3] SET AUTO_CLOSE OFF

    ALTER DATABASE [db3] SET AUTO_CREATE_STATISTICS ON

    ALTER DATABASE [db3] SET AUTO_SHRINK OFF

    ALTER DATABASE [db3] SET AUTO_UPDATE_STATISTICS ON

    ALTER DATABASE [db3] SET CURSOR_CLOSE_ON_COMMIT OFF

    ALTER DATABASE [db3] SET CURSOR_DEFAULT GLOBAL

    ALTER DATABASE [db3] SET CONCAT_NULL_YIELDS_NULL OFF

    ALTER DATABASE [db3] SET NUMERIC_ROUNDABORT OFF

    ALTER DATABASE [db3] SET QUOTED_IDENTIFIER OFF

    ALTER DATABASE [db3] SET RECURSIVE_TRIGGERS OFF

    ALTER DATABASE [db3] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

    ALTER DATABASE [db3] SET DATE_CORRELATION_OPTIMIZATION OFF

    ALTER DATABASE [db3] SET PARAMETERIZATION SIMPLE

    ALTER DATABASE [db3] SET READ_WRITE

    ALTER DATABASE [db3] SET RECOVERY FULL

    ALTER DATABASE [db3] SET MULTI_USER

    ALTER DATABASE [db3] SET PAGE_VERIFY CHECKSUM

    IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
    ALTER DATABASE [db3] MODIFY FILEGROUP [PRIMARY] DEFAULT

    USE [master]
    restore database db3 from disk = N'D:\Installer\DB\db3.bak' with replace
    END
    GO

  2. #7
    Join Date
    Jan 2007
    Posts
    316
    Thanks for your reply. I'll try this out and let you know the results.

  3. #8
    Join Date
    Jan 2007
    Posts
    316
    I tried putting batch separators and found that most of the error messages are now presented to the user. I also tried using try, catch, raiserror etc.
    Well, but one problem still remains, restore database command still doesn't throw any error to installer if it is not able to open the backup file (say due to access permissions or so, i know it is not the usual case, but still assume the installer is being run from a network location and backup files are on 'Disk1'.. i.e. SETUPEXEDIR basically)
    This could be a problem with SQL Server again, which is not sending the appropriate message up till the application, but i still need to know is there any way that in case restore database command fails then installation should detect the error and abort.
    Any help will be appreciated.

  4. #9
    Join Date
    Oct 2001
    Location
    Schaumburg, IL
    Posts
    2,190
    I tried the RESTORE DATABASE statement with a path which does not exist on my system, and InstallShield recevied an error stating "RESTORE DATABASE is terminating abnormally." Could you try to create a new project and add a SQL script that only has a RESTORE DATABASE statement with an invalid path to see if you receive an error?

  5. #10
    Join Date
    Jan 2007
    Posts
    316
    Agree. An invalid path does throw an error. But a path to which you don't have an access (Operating System Error. Access is denied) doesn't show up in IS.
    This typically happens when my setup.exe and db1.bak is kept in a shared location and I launch the execute the setup directly from that network location. Now, the machine on which I am installing is running SQL Server service under local system account. Now, when the sql script tries to execute the restore database command with backup file path as \\server\share\db1.bak it fails because "local system" account (under which SQL server service is running) doesn't have even read access to \\server\share. However, if i change the sql server service to run under my own account it successfully restores the database.
    What I wanted is, this "access denied" error should be shown to the user too. I know I can do a workaround .. like checking in advance if its running from a network location or so and warning the user that setup must be run from local locations... but I still want to know the root cause of these error not showing up in IS.. because this workaround will be very much "problem specific" and it could happen in future that i start facing the problem with some other command. Once I know what prevents this error from showing up in IS, probably I'll avoid many other problems in future too.

Page 2 of 4 FirstFirst 1234 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •