Entry point in sysdatabases

  • Newbie to this .. will learn .. am motivated.

    When I do a script for creating a new database I get this error: Could not locate entry in sysdatabases for database (the name of the one I wish to create.)

    I do not understand why I must have a name for my new database in sysdatabases before I have created it.

    The error is in the line starting with 'exec'

    Thanks

    PK

    This is my code:

    USE [master]
    GO
    /****** Object: Database [IBMStemplate] Script Date: 05-Apr-21 09:38:41 ******/
    CREATE DATABASE [IBMStemplate]
    CONTAINMENT = NONE
    ON PRIMARY
    ( NAME = N'IBMStemplate', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSVRDEV2012\MSSQL\DATA\IBMStemplate.mdf' , SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = N'IBMStemplate_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSVRDEV2012\MSSQL\DATA\IBMStemplate_log.ldf' , SIZE = 29504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    ALTER DATABASE [IBMStemplate] SET COMPATIBILITY_LEVEL = 110
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [IBMStemplate].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    ALTER DATABASE [IBMStemplate] SET ANSI_NULL_DEFAULT OFF

    Appreciating your contributions. Thanks.
    PK

  • What I would try first is run each bit of your code in chunks rather than the whole thing at once.

    Mind you, since you indicated that the problem is on the EXEC line, I'd be willing to bet the problem isn't with the EXEC part exactly. The problem is that the stored procedure "sp_fulltext_database" exists in master and it implicitly includes the database name.  So to run that, you'd need to change the code inside the BEGIN/END to something like:

    BEGIN
    USE [IBMStemplate]
    EXEC [sp_fulltext_database] @action = 'enable'
    USE [master]
    END

    This will switch your database context to your IBMStemplate database, then turn on the full text database.  My guess is the error is incorrect.

  • In general, you can exec the proc that way.

    However, since that proc does absolutely nothing, you'd be better just removing all the code that execs it.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks for your contributions Mr. Brian Gale and ScottPletcher.

    When I add the lines 'USE [IBMStemplate]' and  'Use [master]', the error stays the same and it also shows the same error then in the 'USE [IBMStemplate]' I inserted above it..

    In fact it also gives me the same error when I start to create the tables in the line: 'USE [IBMStemplate]' much lower down in the code..

    When I change the following setting when scripting from an existing database: 'Continue scripting on error as true' under advanced in the wizard, it creates the database correctly as far as I can establish. Even more confused now.

     

    • This reply was modified 4 days, 15 hours ago by  Peekay.

    Appreciating your contributions. Thanks.
    PK

  • SQL often "parses ahead" for errors.  SQL detects that the db doesn't yet exist and so can issue errors.

    I usually use dynamic SQL to get around these types of errors:

    USE [master]
    GO
    /****** Object: Database [IBMStemplate] Script Date: 05-Apr-21 09:38:41 ******/
    CREATE DATABASE [IBMStemplate]
    CONTAINMENT = NONE
    ON PRIMARY
    ( NAME = N'IBMStemplate', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSVRDEV2012\MSSQL\DATA\IBMStemplate.mdf' , SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = N'IBMStemplate_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSVRDEV2012\MSSQL\DATA\IBMStemplate_log.ldf' , SIZE = 29504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    EXEC('ALTER DATABASE [IBMStemplate] SET COMPATIBILITY_LEVEL = 110')
    GO
    /* again, all of this code is meaningless, does nothing, and should be removed */
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC('EXEC [IBMStemplate].[dbo].[sp_fulltext_database] @action = ''enable''')
    end
    GO
    EXEC('ALTER DATABASE [IBMStemplate] SET ANSI_NULL_DEFAULT OFF')

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Excellent thank you Scott.

    It works well, although, as you say, there may be errors referring to the name of the database.

    I do get this error when running the query and I am sure that it is not in line 1, which is the first line of the code you sent me (it apparently does not affect the creation of the database):

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'OFF'.

    I presume it could be in the dynamic code which I entered - see below.

    Edit inset: I have now established that the syntax error is in the first line of the code below.

    I have changed all the settings to dynamic as you suggested. Here they are (sorry, I do not know how to colour the words):

    EXEC('ALTER DATABASE [IBMStemplate] SET ANSI_NULL_DEFAULT OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET ANSI_NULLS OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET ANSI_PADDING OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET ANSI_WARNINGS OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET ARITHABORT OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET AUTO_CLOSE OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET AUTO_CREATE_STATISTICS ON')
    EXEC('ALTER DATABASE [IBMStemplate] SET AUTO_SHRINK OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET AUTO_UPDATE_STATISTICS ON')
    EXEC('ALTER DATABASE [IBMStemplate] SET CURSOR_CLOSE_ON_COMMIT OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET CURSOR_DEFAULT GLOBAL')
    EXEC('ALTER DATABASE [IBMStemplate] SET CONCAT_NULL_YIELDS_NULL OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET NUMERIC_ROUNDABORT OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET QUOTED_IDENTIFIER OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET RECURSIVE_TRIGGERS OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET DISABLE_BROKER')
    EXEC('ALTER DATABASE [IBMStemplate] SET AUTO_UPDATE_STATISTICS_ASYNC OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET DATE_CORRELATION_OPTIMIZATION OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET TRUSTWORTHY OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET ALLOW_SNAPSHOT_ISOLATION OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET PARAMETERIZATION SIMPLE')
    EXEC('ALTER DATABASE [IBMStemplate] SET READ_COMMITTED_SNAPSHOT OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET HONOR_BROKER_PRIORITY OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET RECOVERY FULL')
    EXEC('ALTER DATABASE [IBMStemplate] SET MULTI_USER')
    EXEC('ALTER DATABASE [IBMStemplate] SET PAGE_VERIFY CHECKSUM')
    EXEC('ALTER DATABASE [IBMStemplate] SET DB_CHAINING OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET FILESTREAM( NON_TRANSACTED_ACCESS OFF')
    EXEC('ALTER DATABASE [IBMStemplate] SET TARGET_RECOVERY_TIME = 0 SECONDS')

    • This reply was modified 4 days, 9 hours ago by  Peekay.

    Appreciating your contributions. Thanks.
    PK

  • I'm not getting any syntax errors on:

    ALTER DATABASE [IBMStemplate] SET ANSI_NULL_DEFAULT OFF

    Not sure what that's about.

    Btw, I just noticed that you're growing the log file by 10%.  NEVER use %, always use a fixed amount.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks so much for your help Scott!

    Appreciating your contributions. Thanks.
    PK

  • You're welcome!

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply