April 8, 2021 at 6:25 am
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
April 8, 2021 at 6:48 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 8, 2021 at 8:29 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
April 9, 2021 at 5:14 am
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.
Appreciating your contributions. Thanks.
PK
April 9, 2021 at 6:16 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
April 9, 2021 at 7:47 am
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')Appreciating your contributions. Thanks.
PK
April 9, 2021 at 3:30 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
April 10, 2021 at 8:00 am
Thanks so much for your help Scott!
Appreciating your contributions. Thanks.
PK
April 10, 2021 at 7:08 pm
You're welcome!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply