can anyone help me with a script that the help desk can
run or execute that will page me if there is a problem
with one of the databases that I support. We are trying to
use the help desk as the middle man between the DBA and
the users. Instead of the users calling the DBA direct
they will have to open a ticket with the help desk which
will automatically page the DBA on call.
Thanks for any help.Have you looked at Alerts in SQL Server. These can mail you (If you setup mail) when certain things go wrong. As for user problems. You could have a ticket type of DB Problem and when the database receives that after being inputted by your help
desk it would trigger a mail. This could be through SQL Mail or perhaps your Helpdesk software has this capability.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.orgsql
Showing posts with label script. Show all posts
Showing posts with label script. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
Aggregate/Concatenate Strings in a Script Component?
Hi--
I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.
I have data coming from an Excel Spreadsheet that looks like this:
CustNumb Invoice
1 a
1 b
2 c
3 d
3 e
3 f
I would like an output that looks like this:
CustNumb Invoice
1 a, b
2 c
3 d, e, f
I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.
Thanks for any help or ideas...
Hilary
If you know how the maximum number of values you might have, you might be able to do this in a pivot transform. However, I would probably do it in a script. You'd need to set up the output as asynchronous (make the SynchronousOutputID = None on the output). In the ProcessInputRow method, you'll need to out a row on the async output each time the CustNumb changes from the previous value using the Output0Buffer.AddRow method.
|||Thank you for your reply. When I was trying to do this in script earlier, that was one of the things I had trouble understanding--how to find out when the CustNumb changed. I thought that ProcessInputRow was looking at one row at a time, so I tried grabbing the customer number and then checking the next row to see if it matched... something like:
Code Snippet
Dim intCustNumber as Integer
intCustNumber = Row.CustNumber
With Row.NextRow
Dim intCustNumber1 as Integer
intCustNumber1 = Row.CustNumber
If intCustNumber = intCustNumber1 then
Etc.... not surprisingly, I lost all the rows that didn't have a repetition of the customer number. How do I look at each row and know when the number changes?
Thank you,
Hilary
|||ProcessInputRow does look at a row each time. You need to declare a variable at the instance level (or a static variable, but I prefer instance) to hold the customer number. I freehanded the code below, so it may not compile, but hopefully it gives you an idea. You'll need to make sure it handles the first row and last row properly - I haven't tested it.
Code Snippet
Public Class ScriptMain
Inherits UserComponent
Private intCustomer As Integer
Private strString As String
Private blnFirstRow As Boolean = True
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.CustNumber = intCustomer Then
'Same customer
strString = strString + Row.ColumnVal
Else
If blnFirstRow Then
blnFirstRow = False
Else
'new customer - output the current values
Output0Buffer.AddRow()
Output0Buffer.CustNumber = intCustomer
Output0Buffer.NewCol = strString
End If
'now reset the variables
intCustomer = Row.CustNumber
strString = Row.ColumnVal
End If
End Sub
Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)
MyBase.Input0_ProcessInput(Buffer)
If Row.EndOfRowset Then
Output0Buffer.AddRow()
Output0Buffer.CustNumber = intCustomer
Output0Buffer.NewCol = strString
Output0Buffer.SetEndOfRowSet()
End If
End Sub
|||Thank you, that is just what I needed-- a bit of a map for how to approach it.
Hilary
|||There was a bug in the script above, where the last row could be skipped. I found it when when I was writing this problem up to post on my blog. Here's the full sample:
http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx
I've also edited the script above to correct the issue.
|||Thank you so very much. That is just above and beyond, you know? I really appreciate the help.
Hilary
Thursday, March 8, 2012
Aggregate/Concatenate Strings in a Script Component?
Hi--
I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.
I have data coming from an Excel Spreadsheet that looks like this:
CustNumb Invoice
1 a
1 b
2 c
3 d
3 e
3 f
I would like an output that looks like this:
CustNumb Invoice
1 a, b
2 c
3 d, e, f
I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.
Thanks for any help or ideas...
Hilary
If you know how the maximum number of values you might have, you might be able to do this in a pivot transform. However, I would probably do it in a script. You'd need to set up the output as asynchronous (make the SynchronousOutputID = None on the output). In the ProcessInputRow method, you'll need to out a row on the async output each time the CustNumb changes from the previous value using the Output0Buffer.AddRow method.
|||Thank you for your reply. When I was trying to do this in script earlier, that was one of the things I had trouble understanding--how to find out when the CustNumb changed. I thought that ProcessInputRow was looking at one row at a time, so I tried grabbing the customer number and then checking the next row to see if it matched... something like:
Code Snippet
Dim intCustNumber as Integer
intCustNumber = Row.CustNumber
With Row.NextRow
Dim intCustNumber1 as Integer
intCustNumber1 = Row.CustNumber
If intCustNumber = intCustNumber1 then
Etc.... not surprisingly, I lost all the rows that didn't have a repetition of the customer number. How do I look at each row and know when the number changes?
Thank you,
Hilary
|||ProcessInputRow does look at a row each time. You need to declare a variable at the instance level (or a static variable, but I prefer instance) to hold the customer number. I freehanded the code below, so it may not compile, but hopefully it gives you an idea. You'll need to make sure it handles the first row and last row properly - I haven't tested it.
Code Snippet
Public Class ScriptMain
Inherits UserComponent
Private intCustomer As Integer
Private strString As String
Private blnFirstRow As Boolean = True
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.CustNumber = intCustomer Then
'Same customer
strString = strString + Row.ColumnVal
Else
If blnFirstRow Then
blnFirstRow = False
Else
'new customer - output the current values
Output0Buffer.AddRow()
Output0Buffer.CustNumber = intCustomer
Output0Buffer.NewCol = strString
End If
'now reset the variables
intCustomer = Row.CustNumber
strString = Row.ColumnVal
End If
End Sub
Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)
MyBase.Input0_ProcessInput(Buffer)
If Row.EndOfRowset Then
Output0Buffer.AddRow()
Output0Buffer.CustNumber = intCustomer
Output0Buffer.NewCol = strString
Output0Buffer.SetEndOfRowSet()
End If
End Sub
|||Thank you, that is just what I needed-- a bit of a map for how to approach it.
Hilary
|||There was a bug in the script above, where the last row could be skipped. I found it when when I was writing this problem up to post on my blog. Here's the full sample:
http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx
I've also edited the script above to correct the issue.
|||Thank you so very much. That is just above and beyond, you know? I really appreciate the help.
Hilary
Tuesday, March 6, 2012
Aggregate function is missing when scripting database objects (SQL2005)
I installed the 'StringUtilities' assembly using the Microsoft clr integration sample. The functions work fine, however, when I script out all the objects in the database, the Aggregate function is not scripted and does not appear in any of the selection lists. The other functions (Scalar and Table-valued) scripted fine. I can right click on the Aggregate function and script it out by itself, I was just wondering why it doesn't appear when I script all the database objects or just the user-defined function objects. Thanks.
What do you do to script all objects?|||In SSMS I right-click the database in the object explorer. Then I choose Tasks > Generate Script. Then I click on "Select All" on each window. When I review the generated script the Aggregate function i.e. dbo.Concatenate() is missing. I have also found the same problem if I just try to script out the Functions. The aggregate function does not appear in the pick list of Functions to be scripted. However, if I check the 'Script all objects in the selected database' checkbox on the first screen then it does generate script for the Aggregate function.Aggregate function is missing when scripting database objects (SQL2005)
I installed the 'StringUtilities' assembly using the Microsoft clr integration sample. The functions work fine, however, when I script out all the objects in the database, the Aggregate function is not scripted and does not appear in any of the selection lists. The other functions (Scalar and Table-valued) scripted fine. I can right click on the Aggregate function and script it out by itself, I was just wondering why it doesn't appear when I script all the database objects or just the user-defined function objects. Thanks.
What do you do to script all objects?|||In SSMS I right-click the database in the object explorer. Then I choose Tasks > Generate Script. Then I click on "Select All" on each window. When I review the generated script the Aggregate function i.e. dbo.Concatenate() is missing. I have also found the same problem if I just try to script out the Functions. The aggregate function does not appear in the pick list of Functions to be scripted. However, if I check the 'Script all objects in the selected database' checkbox on the first screen then it does generate script for the Aggregate function.Friday, February 24, 2012
Ageing from system date by number of days
Can anyone help with how I write a script that takes a balance and then age
it by its transaction dates into 30, 60 90 days etc?
My script is as follows and the "AS2 on the 2nd line is coming out as
incorrect syntax:
SELECT
GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN 1
AND 30) AND
SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between 31
and 60) AND
SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
61 and 90)AND
SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
FROM PLPostedSupplierTran
"WendyUK" wrote:
> Can anyone help with how I write a script that takes a balance and then age
> it by its transaction dates into 30, 60 90 days etc?
|||Wendy,
A couple of things:
1 - DATEDIFF (MONTH... ) does not give you what you want since February 28
and March 1 are one month apart. Use DAY instead of MONTH.
2 - WHERE appears after the FROM and only 1 per query. For your columns,
read about the CASE statement. You will need something like the following
untested code:
SELECT GoodsValueInAccountCurrency, TransactionDate,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(0) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS THIRTY,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(1) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS SIXTY,
. . . etc
RLF
"WendyUK" <WendyUK@.discussions.microsoft.com> wrote in message
news:681A829D-F604-41D0-A485-B9B92132180C@.microsoft.com...[vbcol=seagreen]
> My script is as follows and the "AS2 on the 2nd line is coming out as
> incorrect syntax:
> SELECT
> GoodsValueInAccountCurrency, TransactionDate,
> DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
> SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN
> 1
> AND 30) AND
> SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between
> 31
> and 60) AND
> SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
> 61 and 90)AND
> SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
>
> FROM PLPostedSupplierTran
>
> "WendyUK" wrote:
it by its transaction dates into 30, 60 90 days etc?
My script is as follows and the "AS2 on the 2nd line is coming out as
incorrect syntax:
SELECT
GoodsValueInAccountCurrency, TransactionDate,
DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN 1
AND 30) AND
SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between 31
and 60) AND
SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
61 and 90)AND
SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
FROM PLPostedSupplierTran
"WendyUK" wrote:
> Can anyone help with how I write a script that takes a balance and then age
> it by its transaction dates into 30, 60 90 days etc?
|||Wendy,
A couple of things:
1 - DATEDIFF (MONTH... ) does not give you what you want since February 28
and March 1 are one month apart. Use DAY instead of MONTH.
2 - WHERE appears after the FROM and only 1 per query. For your columns,
read about the CASE statement. You will need something like the following
untested code:
SELECT GoodsValueInAccountCurrency, TransactionDate,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(0) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS THIRTY,
SUM (CASE ((DATEDIFF(DAY,TransactionDate, GETDATE()) - 1) / 30)
WHEN(1) THEN GoodsValueInAccountCurrency
ELSE 0
END ) AS SIXTY,
. . . etc
RLF
"WendyUK" <WendyUK@.discussions.microsoft.com> wrote in message
news:681A829D-F604-41D0-A485-B9B92132180C@.microsoft.com...[vbcol=seagreen]
> My script is as follows and the "AS2 on the 2nd line is coming out as
> incorrect syntax:
> SELECT
> GoodsValueInAccountCurrency, TransactionDate,
> DATEDIFF(MONTH, TransactionDate, GETDATE()) AS no_of_days_since,
> SUM(GoodsValueInAccountCurrency) AS THIRTY WHERE (no_of_days_since BETWEEN
> 1
> AND 30) AND
> SUM(GoodsValueInAccountCurrency) AS SIXTY WHERE (no_of_days_since between
> 31
> and 60) AND
> SUM(GoodsValueInAccountCurrency) AS NINETY WHERE (no_of_days_since between
> 61 and 90)AND
> SUM(GoodsValueInAccountCurrency) AS MORE WHERE (no_of_days_since > 90)
>
> FROM PLPostedSupplierTran
>
> "WendyUK" wrote:
Sunday, February 12, 2012
After creating database, running script to create tables?
I am in my apps master database and I have a Proc to create a project database. However, once that database is created, I have a long script that I need to run to create the tables, indexes, and views.
What is the best way to run this?
I'd intended to just make this part of my stored proc -- create database, then tables, then views. However, to simply create the database, I had to build a string to concatenate the passed in value of the new database. I'd hate to have to do this for every line of this script.
Should I simply take this script as is and create it as a stored proc in the new database and then run that proc?Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)
Is this supposed to be part of an automated process?
blindman|||Originally posted by blindman
Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)
Is this supposed to be part of an automated process?
blindman
Yes. I'm trying to allow admin users on our web app the ability to create new projects on the fly. Part of this is creating the new database.|||Originally posted by blindman
Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)
Is this supposed to be part of an automated process?
blindman
Bummer: Server: Msg 154, Level 15, State 1, Procedure CreateclaimDexDB_SP, Line 22
a USE database statement is not allowed in a procedure or trigger.|||No, USE is not allowed in a procedure or trigger. I've gotten around that in the past by creating dynamic SQL statements, but I imagine your code is rather long for that.
Personally, I think this is only one of the drawbacks you will encounter by creating separate databases for each project. By far the easiest way to handle an application like this is to add scalability to your database to allow it to handle multiple projects. This will make it much faster and easier to add, delete, and manage projects. It will also prevent a lot of duplicated information between databases which are sure to get our of synch, and will allow for powerful comparison analysis between projects.
I think you are heading into an administrative nightmare by creating separate databases, and this problem that you have now is just the tip of the iceberg.
It is also not a good idea to be storing procedures like this in the master database. If you must, then create a separate database to store the procedures involved in creating project databases.
Other options include using SQL to load a template database from a backup file. The template database could have all your objects already stored.
...or your front end application could switch focus to the new database before executing the procedure for creating all the objects.
blindman|||Originally posted by blindman
No, USE is not allowed in a procedure or trigger. I've gotten around that in the past by creating dynamic SQL statements, but I imagine your code is rather long for that.
Personally, I think this is only one of the drawbacks you will encounter by creating separate databases for each project. By far the easiest way to handle an application like this is to add scalability to your database to allow it to handle multiple projects. This will make it much faster and easier to add, delete, and manage projects. It will also prevent a lot of duplicated information between databases which are sure to get our of synch, and will allow for powerful comparison analysis between projects.
I think you are heading into an administrative nightmare by creating separate databases, and this problem that you have now is just the tip of the iceberg.
It is also not a good idea to be storing procedures like this in the master database. If you must, then create a separate database to store the procedures involved in creating project databases.
Other options include using SQL to load a template database from a backup file. The template database could have all your objects already stored.
...or your front end application could switch focus to the new database before executing the procedure for creating all the objects.
blindman
I appreciate your comments, but in this case, separate databases is the right way to go. They truly are for distinct projects, clients, etc. and CAN be scaled across different servers if need be. In addition, some tables will start out the same, but can be altered by each client independently. This is tying into another existing application.
When I said master DB, I forgot there was a SQL database called master. I meant the main application database, which keeps track of the different projects, users, clients, etc.
As a general rule, I would agree with you, but in this case, we're doing the right thing.
What is the best way to run this?
I'd intended to just make this part of my stored proc -- create database, then tables, then views. However, to simply create the database, I had to build a string to concatenate the passed in value of the new database. I'd hate to have to do this for every line of this script.
Should I simply take this script as is and create it as a stored proc in the new database and then run that proc?Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)
Is this supposed to be part of an automated process?
blindman|||Originally posted by blindman
Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)
Is this supposed to be part of an automated process?
blindman
Yes. I'm trying to allow admin users on our web app the ability to create new projects on the fly. Part of this is creating the new database.|||Originally posted by blindman
Well, you don't need to store it as a stored procedure in order to run it. You could just switch to the new database and run it through Query Analyzer. Alternatively, you could put a USE database statement at the beginning of the script, and that would switch the database focus for you. (You'll want to make sure it succeeds though, because otherwise you could end up creating all your objects in the Master database.)
Is this supposed to be part of an automated process?
blindman
Bummer: Server: Msg 154, Level 15, State 1, Procedure CreateclaimDexDB_SP, Line 22
a USE database statement is not allowed in a procedure or trigger.|||No, USE is not allowed in a procedure or trigger. I've gotten around that in the past by creating dynamic SQL statements, but I imagine your code is rather long for that.
Personally, I think this is only one of the drawbacks you will encounter by creating separate databases for each project. By far the easiest way to handle an application like this is to add scalability to your database to allow it to handle multiple projects. This will make it much faster and easier to add, delete, and manage projects. It will also prevent a lot of duplicated information between databases which are sure to get our of synch, and will allow for powerful comparison analysis between projects.
I think you are heading into an administrative nightmare by creating separate databases, and this problem that you have now is just the tip of the iceberg.
It is also not a good idea to be storing procedures like this in the master database. If you must, then create a separate database to store the procedures involved in creating project databases.
Other options include using SQL to load a template database from a backup file. The template database could have all your objects already stored.
...or your front end application could switch focus to the new database before executing the procedure for creating all the objects.
blindman|||Originally posted by blindman
No, USE is not allowed in a procedure or trigger. I've gotten around that in the past by creating dynamic SQL statements, but I imagine your code is rather long for that.
Personally, I think this is only one of the drawbacks you will encounter by creating separate databases for each project. By far the easiest way to handle an application like this is to add scalability to your database to allow it to handle multiple projects. This will make it much faster and easier to add, delete, and manage projects. It will also prevent a lot of duplicated information between databases which are sure to get our of synch, and will allow for powerful comparison analysis between projects.
I think you are heading into an administrative nightmare by creating separate databases, and this problem that you have now is just the tip of the iceberg.
It is also not a good idea to be storing procedures like this in the master database. If you must, then create a separate database to store the procedures involved in creating project databases.
Other options include using SQL to load a template database from a backup file. The template database could have all your objects already stored.
...or your front end application could switch focus to the new database before executing the procedure for creating all the objects.
blindman
I appreciate your comments, but in this case, separate databases is the right way to go. They truly are for distinct projects, clients, etc. and CAN be scaled across different servers if need be. In addition, some tables will start out the same, but can be altered by each client independently. This is tying into another existing application.
When I said master DB, I forgot there was a SQL database called master. I meant the main application database, which keeps track of the different projects, users, clients, etc.
As a general rule, I would agree with you, but in this case, we're doing the right thing.
after create database, parser cannot filnd new database for 'use'
Hello,
I have a script that creates a new database table called MYDATABAE, then it
will use USE [MYDATABASE]
to create tables.
Well.. It doesn't even get past the parser. It stops saying:
Could not locate entry in sysdatabases for database 'MYDATABASE'
Below you will see some of the script I am attempting to run, by the way,
this was created using SqlServer express management studio. At the bottom
of the script, see where the parser gives the error.
USE [master]
GO
/****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:24
******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
BEGIN
CREATE DATABASE [MYDATABASE] ON PRIMARY
( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable'
end
GO
ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
GO
ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
GO
ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
GO
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MYDATABASE] SET READ_WRITE
GO
ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MYDATABASE] SET MULTI_USER
GO
ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
GO
USE [MYDATABASE] > this is where error occurs!
GO
... More sql ...
I am not sure what happended. This was working earlier today, but not
anymore...
Any and help would be appreciated!
Thanks,
BryanI ran the script you posted without error. Perhaps there is a
non-displayable character in your actual script. You might try re-typing
the limes immediately preceding and following the statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bryan" <BryanZM@.nospam.nospam> wrote in message
news:efZc8hvyGHA.4648@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a script that creates a new database table called MYDATABAE, then
> it will use USE [MYDATABASE]
> to create tables.
> Well.. It doesn't even get past the parser. It stops saying:
> Could not locate entry in sysdatabases for database 'MYDATABASE'
>
> Below you will see some of the script I am attempting to run, by the way,
> this was created using SqlServer express management studio. At the bottom
> of the script, see where the parser gives the error.
> USE [master]
> GO
> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:24
> ******/
> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
> BEGIN
> CREATE DATABASE [MYDATABASE] ON PRIMARY
> ( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE => UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE => 2048GB , FILEGROWTH = 10%)
> END
> GO
> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
> GO
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
> begin
> EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable'
> end
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
> GO
> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
> GO
> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
> GO
> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
> GO
> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
> GO
> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
> GO
> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
> GO
> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
> GO
> ALTER DATABASE [MYDATABASE] SET READ_WRITE
> GO
> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
> GO
> ALTER DATABASE [MYDATABASE] SET MULTI_USER
> GO
> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
> GO
> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
> GO
> USE [MYDATABASE] > this is where error occurs!
> GO
> ... More sql ...
>
> I am not sure what happended. This was working earlier today, but not
> anymore...
> Any and help would be appreciated!
> Thanks,
> Bryan
>
>|||Hello Bryan,
I've also tried the script you pasted and it runs corretly on my side
also(except that I change the database file path according to my local
machine).
As for the error you mentioned, does it only occur when you use "Parse"
action to check the syntax? If so, the parser in management studio will
report error against the non-existing database since it hasn't been created
at parse time. However, the script is still able to be executed without any
problem.
Please feel free to post here if there is anything we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Ok, for some miraculous rerason it works now. But it created another
problem. I am now trying ot execute the same script in c# with a connection
to master. It does not work!!! I get an error like this:
Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near 'GO'.\r\nCould
not locate entry in sysdatabases for database 'GOIDB'. No entry found with
that name. Make sure that the name is entered correctly."
I can remove everything but the foloowing and it will execute fine:
>> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
>> BEGIN
>> CREATE DATABASE [MYDATABASE] ON PRIMARY
>> ( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =>> UNLIMITED, FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE
>> = 2048GB , FILEGROWTH = 10%)
>> END
>> GO
It creates the Database without a hitch...
Any more suggestions?
Thanks!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u8jKcWwyGHA.1292@.TK2MSFTNGP03.phx.gbl...
>I ran the script you posted without error. Perhaps there is a
>non-displayable character in your actual script. You might try re-typing
>the limes immediately preceding and following the statement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <BryanZM@.nospam.nospam> wrote in message
> news:efZc8hvyGHA.4648@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I have a script that creates a new database table called MYDATABAE, then
>> it will use USE [MYDATABASE]
>> to create tables.
>> Well.. It doesn't even get past the parser. It stops saying:
>> Could not locate entry in sysdatabases for database 'MYDATABASE'
>>
>> Below you will see some of the script I am attempting to run, by the way,
>> this was created using SqlServer express management studio. At the
>> bottom of the script, see where the parser gives the error.
>> USE [master]
>> GO
>> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006
>> 10:18:24 ******/
>> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
>> BEGIN
>> CREATE DATABASE [MYDATABASE] ON PRIMARY
>> ( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =>> UNLIMITED, FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE
>> = 2048GB , FILEGROWTH = 10%)
>> END
>> GO
>> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
>> GO
>> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
>> begin
>> EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable'
>> end
>> GO
>> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
>> GO
>> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
>> GO
>> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
>> GO
>> ALTER DATABASE [MYDATABASE] SET READ_WRITE
>> GO
>> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
>> GO
>> ALTER DATABASE [MYDATABASE] SET MULTI_USER
>> GO
>> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
>> GO
>> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
>> GO
>> USE [MYDATABASE] > this is where error occurs!
>> GO
>> ... More sql ...
>>
>> I am not sure what happended. This was working earlier today, but not
>> anymore...
>> Any and help would be appreciated!
>> Thanks,
>> Bryan
>>
>|||Bryan wrote:
> Ok, for some miraculous rerason it works now. But it created another
> problem. I am now trying ot execute the same script in c# with a connection
> to master. It does not work!!! I get an error like this:
> Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
> 'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
> 'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near 'GO'.\r\nCould
> not locate entry in sysdatabases for database 'GOIDB'. No entry found with
> that name. Make sure that the name is entered correctly."
>
"GO" is not a SQL command, it's a batch seperator used inside a script
file that is executed within QA/Management Studio/ISQL
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks,
I replaced the GO with ";" and it appears I am almost there, now I get this
error:
Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
found with that name. Make sure that the name is entered correctly.
when the execution gets here:
USE [MYDATABASE]
Shouldn't it be creating my database, and not throw this error? I am
stumped!
Here is the script:
USE [master]
/****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:24
******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
BEGIN
CREATE DATABASE [MYDATABASE] ON PRIMARY
( NAME = N'MYDATABASE', FILENAME = N'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MYDATABASE_log', FILENAME = N'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE
= 2048GB , FILEGROWTH = 10%)
END
;
EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
;
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC
[MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable' end
;
ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
;
ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
;
ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
;
ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
;
ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
;
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
;
ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
;
ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
;
ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
;
ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
;
ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
;
ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
;
ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
;
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
;
ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
;
ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
;
ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
;
ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
;
ALTER DATABASE [MYDATABASE] SET READ_WRITE
;
ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
;
ALTER DATABASE [MYDATABASE] SET MULTI_USER
;
ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
;
ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
;
USE [MYDATABASE]
more stuff...
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F45F39.80506@.realsqlguy.com...
> Bryan wrote:
>> Ok, for some miraculous rerason it works now. But it created another
>> problem. I am now trying ot execute the same script in c# with a
>> connection to master. It does not work!!! I get an error like this:
>> Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
>> 'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the
>> keyword 'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nCould not locate entry in sysdatabases for database 'GOIDB'. No
>> entry found with that name. Make sure that the name is entered
>> correctly."
> "GO" is not a SQL command, it's a batch seperator used inside a script
> file that is executed within QA/Management Studio/ISQL
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||The issue you're running into is that the parser parses a complete batch
before it executes it. When you used the script with a GO after each line,
each line was sent to the parser as a separate batch. Now, you are sending
all the lines to the parser in a single batch. When it gets to the USE
statement, the database doesn't exist because the batch hasn't been
executed. You need to split the commands into separate batches - execute
everything up to the USE as one batch and then do the USE as a separate
batch.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bryan" <BryanZM@.nospam.nospam> wrote in message
news:%23BaPDf4yGHA.4232@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> I replaced the GO with ";" and it appears I am almost there, now I get
> this error:
> Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
> found with that name. Make sure that the name is entered correctly.
>
> when the execution gets here:
> USE [MYDATABASE]
>
> Shouldn't it be creating my database, and not throw this error? I am
> stumped!
> Here is the script:
> USE [master]
> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:24
> ******/
> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
> BEGIN
> CREATE DATABASE [MYDATABASE] ON PRIMARY
> ( NAME = N'MYDATABASE', FILENAME = N'c:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE => UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE_log.LDF' , SIZE = 26816KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> END
> ;
>
> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
> ;
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC
> [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable' end
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
> ;
> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
> ;
> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
> ;
> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
> ;
> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
> ;
> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
> ;
> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
> ;
> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
> ;
> ALTER DATABASE [MYDATABASE] SET READ_WRITE
> ;
> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
> ;
> ALTER DATABASE [MYDATABASE] SET MULTI_USER
> ;
> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
> ;
> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
> ;
> USE [MYDATABASE]
>
> more stuff...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44F45F39.80506@.realsqlguy.com...
>> Bryan wrote:
>> Ok, for some miraculous rerason it works now. But it created another
>> problem. I am now trying ot execute the same script in c# with a
>> connection to master. It does not work!!! I get an error like this:
>> Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
>> 'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the
>> keyword 'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nCould not locate entry in sysdatabases for database 'GOIDB'. No
>> entry found with that name. Make sure that the name is entered
>> correctly."
>>
>> "GO" is not a SQL command, it's a batch seperator used inside a script
>> file that is executed within QA/Management Studio/ISQL
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Bryan wrote:
> Thanks,
> I replaced the GO with ";" and it appears I am almost there, now I get this
> error:
> Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
> found with that name. Make sure that the name is entered correctly.
>
> when the execution gets here:
> USE [MYDATABASE]
>
> Shouldn't it be creating my database, and not throw this error? I am
> stumped!
>
Think about what's happening here. You're issuing a series of commands
to SQL as a single batch. That entire batch is compiled, and then
executed. At the time of compilation, the database doesn't exist, thus
an error is thrown.
In your original attempt, you were seperating commands into seperate
batches using the "GO" seperator. That, unfortunately, doesn't work
outside of a script file that is run through QA or ISQL.
It's a bit unusual for an application to create a database on the fly
like this. If you really must create this database this way, you're
going to have to do it in two steps. The first step will create the
database. The second, seperate, step will run the rest of the operation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for Roger and Tracy's input.
Hi Bryan,
I agree with Roger that when you send all the T-SQL block through one C#
net sqlcommand, it is just as you execute them in a single batch and the
T-SQL engine will report the error against the use statement on a
non-existing database. I suggest you separate the script into to parts,
create database and alter database and execute them in a separate
SqlCommand respectively.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for all the help, I was able to get it to work.
Bryan .
I have a script that creates a new database table called MYDATABAE, then it
will use USE [MYDATABASE]
to create tables.
Well.. It doesn't even get past the parser. It stops saying:
Could not locate entry in sysdatabases for database 'MYDATABASE'
Below you will see some of the script I am attempting to run, by the way,
this was created using SqlServer express management studio. At the bottom
of the script, see where the parser gives the error.
USE [master]
GO
/****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:24
******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
BEGIN
CREATE DATABASE [MYDATABASE] ON PRIMARY
( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable'
end
GO
ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
GO
ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
GO
ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
GO
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MYDATABASE] SET READ_WRITE
GO
ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MYDATABASE] SET MULTI_USER
GO
ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
GO
USE [MYDATABASE] > this is where error occurs!
GO
... More sql ...
I am not sure what happended. This was working earlier today, but not
anymore...
Any and help would be appreciated!
Thanks,
BryanI ran the script you posted without error. Perhaps there is a
non-displayable character in your actual script. You might try re-typing
the limes immediately preceding and following the statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bryan" <BryanZM@.nospam.nospam> wrote in message
news:efZc8hvyGHA.4648@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a script that creates a new database table called MYDATABAE, then
> it will use USE [MYDATABASE]
> to create tables.
> Well.. It doesn't even get past the parser. It stops saying:
> Could not locate entry in sysdatabases for database 'MYDATABASE'
>
> Below you will see some of the script I am attempting to run, by the way,
> this was created using SqlServer express management studio. At the bottom
> of the script, see where the parser gives the error.
> USE [master]
> GO
> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:24
> ******/
> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
> BEGIN
> CREATE DATABASE [MYDATABASE] ON PRIMARY
> ( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE => UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE => 2048GB , FILEGROWTH = 10%)
> END
> GO
> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
> GO
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
> begin
> EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable'
> end
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
> GO
> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
> GO
> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
> GO
> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
> GO
> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
> GO
> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
> GO
> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
> GO
> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
> GO
> ALTER DATABASE [MYDATABASE] SET READ_WRITE
> GO
> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
> GO
> ALTER DATABASE [MYDATABASE] SET MULTI_USER
> GO
> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
> GO
> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
> GO
> USE [MYDATABASE] > this is where error occurs!
> GO
> ... More sql ...
>
> I am not sure what happended. This was working earlier today, but not
> anymore...
> Any and help would be appreciated!
> Thanks,
> Bryan
>
>|||Hello Bryan,
I've also tried the script you pasted and it runs corretly on my side
also(except that I change the database file path according to my local
machine).
As for the error you mentioned, does it only occur when you use "Parse"
action to check the syntax? If so, the parser in management studio will
report error against the non-existing database since it hasn't been created
at parse time. However, the script is still able to be executed without any
problem.
Please feel free to post here if there is anything we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Ok, for some miraculous rerason it works now. But it created another
problem. I am now trying ot execute the same script in c# with a connection
to master. It does not work!!! I get an error like this:
Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near 'GO'.\r\nCould
not locate entry in sysdatabases for database 'GOIDB'. No entry found with
that name. Make sure that the name is entered correctly."
I can remove everything but the foloowing and it will execute fine:
>> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
>> BEGIN
>> CREATE DATABASE [MYDATABASE] ON PRIMARY
>> ( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =>> UNLIMITED, FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE
>> = 2048GB , FILEGROWTH = 10%)
>> END
>> GO
It creates the Database without a hitch...
Any more suggestions?
Thanks!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u8jKcWwyGHA.1292@.TK2MSFTNGP03.phx.gbl...
>I ran the script you posted without error. Perhaps there is a
>non-displayable character in your actual script. You might try re-typing
>the limes immediately preceding and following the statement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <BryanZM@.nospam.nospam> wrote in message
> news:efZc8hvyGHA.4648@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> I have a script that creates a new database table called MYDATABAE, then
>> it will use USE [MYDATABASE]
>> to create tables.
>> Well.. It doesn't even get past the parser. It stops saying:
>> Could not locate entry in sysdatabases for database 'MYDATABASE'
>>
>> Below you will see some of the script I am attempting to run, by the way,
>> this was created using SqlServer express management studio. At the
>> bottom of the script, see where the parser gives the error.
>> USE [master]
>> GO
>> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006
>> 10:18:24 ******/
>> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
>> BEGIN
>> CREATE DATABASE [MYDATABASE] ON PRIMARY
>> ( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =>> UNLIMITED, FILEGROWTH = 1024KB )
>> LOG ON
>> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
>> Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE
>> = 2048GB , FILEGROWTH = 10%)
>> END
>> GO
>> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
>> GO
>> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
>> begin
>> EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable'
>> end
>> GO
>> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
>> GO
>> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
>> GO
>> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
>> GO
>> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
>> GO
>> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
>> GO
>> ALTER DATABASE [MYDATABASE] SET READ_WRITE
>> GO
>> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
>> GO
>> ALTER DATABASE [MYDATABASE] SET MULTI_USER
>> GO
>> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
>> GO
>> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
>> GO
>> USE [MYDATABASE] > this is where error occurs!
>> GO
>> ... More sql ...
>>
>> I am not sure what happended. This was working earlier today, but not
>> anymore...
>> Any and help would be appreciated!
>> Thanks,
>> Bryan
>>
>|||Bryan wrote:
> Ok, for some miraculous rerason it works now. But it created another
> problem. I am now trying ot execute the same script in c# with a connection
> to master. It does not work!!! I get an error like this:
> Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
> 'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
> 'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near 'GO'.\r\nCould
> not locate entry in sysdatabases for database 'GOIDB'. No entry found with
> that name. Make sure that the name is entered correctly."
>
"GO" is not a SQL command, it's a batch seperator used inside a script
file that is executed within QA/Management Studio/ISQL
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks,
I replaced the GO with ";" and it appears I am almost there, now I get this
error:
Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
found with that name. Make sure that the name is entered correctly.
when the execution gets here:
USE [MYDATABASE]
Shouldn't it be creating my database, and not throw this error? I am
stumped!
Here is the script:
USE [master]
/****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:24
******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
BEGIN
CREATE DATABASE [MYDATABASE] ON PRIMARY
( NAME = N'MYDATABASE', FILENAME = N'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MYDATABASE_log', FILENAME = N'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE
= 2048GB , FILEGROWTH = 10%)
END
;
EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
;
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC
[MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable' end
;
ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
;
ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
;
ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
;
ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
;
ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
;
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
;
ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
;
ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
;
ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
;
ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
;
ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
;
ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
;
ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
;
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
;
ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
;
ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
;
ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
;
ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
;
ALTER DATABASE [MYDATABASE] SET READ_WRITE
;
ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
;
ALTER DATABASE [MYDATABASE] SET MULTI_USER
;
ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
;
ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
;
USE [MYDATABASE]
more stuff...
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F45F39.80506@.realsqlguy.com...
> Bryan wrote:
>> Ok, for some miraculous rerason it works now. But it created another
>> problem. I am now trying ot execute the same script in c# with a
>> connection to master. It does not work!!! I get an error like this:
>> Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
>> 'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the
>> keyword 'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nCould not locate entry in sysdatabases for database 'GOIDB'. No
>> entry found with that name. Make sure that the name is entered
>> correctly."
> "GO" is not a SQL command, it's a batch seperator used inside a script
> file that is executed within QA/Management Studio/ISQL
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||The issue you're running into is that the parser parses a complete batch
before it executes it. When you used the script with a GO after each line,
each line was sent to the parser as a separate batch. Now, you are sending
all the lines to the parser in a single batch. When it gets to the USE
statement, the database doesn't exist because the batch hasn't been
executed. You need to split the commands into separate batches - execute
everything up to the USE as one batch and then do the USE as a separate
batch.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bryan" <BryanZM@.nospam.nospam> wrote in message
news:%23BaPDf4yGHA.4232@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> I replaced the GO with ";" and it appears I am almost there, now I get
> this error:
> Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
> found with that name. Make sure that the name is entered correctly.
>
> when the execution gets here:
> USE [MYDATABASE]
>
> Shouldn't it be creating my database, and not throw this error? I am
> stumped!
> Here is the script:
> USE [master]
> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:24
> ******/
> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
> BEGIN
> CREATE DATABASE [MYDATABASE] ON PRIMARY
> ( NAME = N'MYDATABASE', FILENAME = N'c:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE => UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE_log.LDF' , SIZE = 26816KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> END
> ;
>
> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
> ;
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC
> [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable' end
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
> ;
> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
> ;
> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
> ;
> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
> ;
> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
> ;
> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
> ;
> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
> ;
> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
> ;
> ALTER DATABASE [MYDATABASE] SET READ_WRITE
> ;
> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
> ;
> ALTER DATABASE [MYDATABASE] SET MULTI_USER
> ;
> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
> ;
> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
> ;
> USE [MYDATABASE]
>
> more stuff...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44F45F39.80506@.realsqlguy.com...
>> Bryan wrote:
>> Ok, for some miraculous rerason it works now. But it created another
>> problem. I am now trying ot execute the same script in c# with a
>> connection to master. It does not work!!! I get an error like this:
>> Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
>> 'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the
>> keyword 'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
>> 'GO'.\r\nCould not locate entry in sysdatabases for database 'GOIDB'. No
>> entry found with that name. Make sure that the name is entered
>> correctly."
>>
>> "GO" is not a SQL command, it's a batch seperator used inside a script
>> file that is executed within QA/Management Studio/ISQL
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Bryan wrote:
> Thanks,
> I replaced the GO with ";" and it appears I am almost there, now I get this
> error:
> Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
> found with that name. Make sure that the name is entered correctly.
>
> when the execution gets here:
> USE [MYDATABASE]
>
> Shouldn't it be creating my database, and not throw this error? I am
> stumped!
>
Think about what's happening here. You're issuing a series of commands
to SQL as a single batch. That entire batch is compiled, and then
executed. At the time of compilation, the database doesn't exist, thus
an error is thrown.
In your original attempt, you were seperating commands into seperate
batches using the "GO" seperator. That, unfortunately, doesn't work
outside of a script file that is run through QA or ISQL.
It's a bit unusual for an application to create a database on the fly
like this. If you really must create this database this way, you're
going to have to do it in two steps. The first step will create the
database. The second, seperate, step will run the rest of the operation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for Roger and Tracy's input.
Hi Bryan,
I agree with Roger that when you send all the T-SQL block through one C#
net sqlcommand, it is just as you execute them in a single batch and the
T-SQL engine will report the error against the use statement on a
non-existing database. I suggest you separate the script into to parts,
create database and alter database and execute them in a separate
SqlCommand respectively.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for all the help, I was able to get it to work.
Bryan .
after create database, parser cannot filnd new database for 'use'
Hello,
I have a script that creates a new database table called MYDATABAE, then it
will use USE [MYDATABASE]
to create tables.
Well.. It doesn't even get past the parser. It stops saying:
Could not locate entry in sysdatabases for database 'MYDATABASE'
Below you will see some of the script I am attempting to run, by the way,
this was created using SqlServer express management studio. At the bottom
of the script, see where the parser gives the error.
USE [master]
GO
/****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:
24
******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
BEGIN
CREATE DATABASE [MYDATABASE] ON PRIMARY
( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
END
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInsta
lled'))
begin
EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable
'
end
GO
ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
GO
ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
GO
ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
GO
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MYDATABASE] SET READ_WRITE
GO
ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MYDATABASE] SET MULTI_USER
GO
ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
GO
USE [MYDATABASE] > this is where error occurs!
GO
... More sql ...
I am not sure what happended. This was working earlier today, but not
anymore...
Any and help would be appreciated!
Thanks,
BryanI ran the script you posted without error. Perhaps there is a
non-displayable character in your actual script. You might try re-typing
the limes immediately preceding and following the statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bryan" <BryanZM@.nospam.nospam> wrote in message
news:efZc8hvyGHA.4648@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a script that creates a new database table called MYDATABAE, then
> it will use USE [MYDATABASE]
> to create tables.
> Well.. It doesn't even get past the parser. It stops saying:
> Could not locate entry in sysdatabases for database 'MYDATABASE'
>
> Below you will see some of the script I am attempting to run, by the way,
> this was created using SqlServer express management studio. At the bottom
> of the script, see where the parser gives the error.
> USE [master]
> GO
> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:1
8:24
> ******/
> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
> BEGIN
> CREATE DATABASE [MYDATABASE] ON PRIMARY
> ( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE =
> 2048GB , FILEGROWTH = 10%)
> END
> GO
> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
> GO
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInsta
lled'))
> begin
> EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enab
le'
> end
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
> GO
> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
> GO
> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
> GO
> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
> GO
> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
> GO
> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
> GO
> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
> GO
> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
> GO
> ALTER DATABASE [MYDATABASE] SET READ_WRITE
> GO
> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
> GO
> ALTER DATABASE [MYDATABASE] SET MULTI_USER
> GO
> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
> GO
> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
> GO
> USE [MYDATABASE] > this is where error occurs!
> GO
> ... More sql ...
>
> I am not sure what happended. This was working earlier today, but not
> anymore...
> Any and help would be appreciated!
> Thanks,
> Bryan
>
>|||Hello Bryan,
I've also tried the script you pasted and it runs corretly on my side
also(except that I change the database file path according to my local
machine).
As for the error you mentioned, does it only occur when you use "Parse"
action to check the syntax? If so, the parser in management studio will
report error against the non-existing database since it hasn't been created
at parse time. However, the script is still able to be executed without any
problem.
Please feel free to post here if there is anything we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Ok, for some miraculous rerason it works now. But it created another
problem. I am now trying ot execute the same script in c# with a connection
to master. It does not work!!! I get an error like this:
Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near 'GO'.\r\nCould
not locate entry in sysdatabases for database 'GOIDB'. No entry found with
that name. Make sure that the name is entered correctly."
I can remove everything but the foloowing and it will execute fine:
It creates the Database without a hitch...
Any more suggestions?
Thanks!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u8jKcWwyGHA.1292@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
>I ran the script you posted without error. Perhaps there is a
>non-displayable character in your actual script. You might try re-typing
>the limes immediately preceding and following the statement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <BryanZM@.nospam.nospam> wrote in message
> news:efZc8hvyGHA.4648@.TK2MSFTNGP04.phx.gbl...
>|||Bryan wrote:
> Ok, for some miraculous rerason it works now. But it created another
> problem. I am now trying ot execute the same script in c# with a connectio
n
> to master. It does not work!!! I get an error like this:
> Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
> 'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the keywor
d
> 'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near 'GO'.\r\nCou
ld
> not locate entry in sysdatabases for database 'GOIDB'. No entry found with
> that name. Make sure that the name is entered correctly."
>
"GO" is not a SQL command, it's a batch seperator used inside a script
file that is executed within QA/Management Studio/ISQL
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks,
I replaced the GO with ";" and it appears I am almost there, now I get this
error:
Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
found with that name. Make sure that the name is entered correctly.
when the execution gets here:
USE [MYDATABASE]
Shouldn't it be creating my database, and not throw this error? I am
stumped!
Here is the script:
USE [master]
/****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:
24
******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
BEGIN
CREATE DATABASE [MYDATABASE] ON PRIMARY
( NAME = N'MYDATABASE', FILENAME = N'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MYDATABASE_log', FILENAME = N'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE
= 2048GB , FILEGROWTH = 10%)
END
;
EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
;
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInsta
lled')) begin EXEC
[MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable' end
;
ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
;
ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
;
ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
;
ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
;
ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
;
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
;
ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
;
ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
;
ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
;
ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
;
ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
;
ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
;
ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
;
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
;
ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
;
ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
;
ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
;
ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
;
ALTER DATABASE [MYDATABASE] SET READ_WRITE
;
ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
;
ALTER DATABASE [MYDATABASE] SET MULTI_USER
;
ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
;
ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
;
USE [MYDATABASE]
more stuff...
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F45F39.80506@.realsqlguy.com...
> Bryan wrote:
> "GO" is not a SQL command, it's a batch seperator used inside a script
> file that is executed within QA/Management Studio/ISQL
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||The issue you're running into is that the parser parses a complete batch
before it executes it. When you used the script with a GO after each line,
each line was sent to the parser as a separate batch. Now, you are sending
all the lines to the parser in a single batch. When it gets to the USE
statement, the database doesn't exist because the batch hasn't been
executed. You need to split the commands into separate batches - execute
everything up to the USE as one batch and then do the USE as a separate
batch.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bryan" <BryanZM@.nospam.nospam> wrote in message
news:%23BaPDf4yGHA.4232@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> I replaced the GO with ";" and it appears I am almost there, now I get
> this error:
> Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
> found with that name. Make sure that the name is entered correctly.
>
> when the execution gets here:
> USE [MYDATABASE]
>
> Shouldn't it be creating my database, and not throw this error? I am
> stumped!
> Here is the script:
> USE [master]
> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:1
8:24
> ******/
> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
> BEGIN
> CREATE DATABASE [MYDATABASE] ON PRIMARY
> ( NAME = N'MYDATABASE', FILENAME = N'c:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE_log.LDF' , SIZE = 26816KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> END
> ;
>
> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
> ;
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInsta
lled')) begin EXEC
> [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable' e
nd
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
> ;
> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
> ;
> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
> ;
> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
> ;
> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
> ;
> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
> ;
> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
> ;
> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
> ;
> ALTER DATABASE [MYDATABASE] SET READ_WRITE
> ;
> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
> ;
> ALTER DATABASE [MYDATABASE] SET MULTI_USER
> ;
> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
> ;
> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
> ;
> USE [MYDATABASE]
>
> more stuff...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44F45F39.80506@.realsqlguy.com...
>|||Bryan wrote:
> Thanks,
> I replaced the GO with ";" and it appears I am almost there, now I get thi
s
> error:
> Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
> found with that name. Make sure that the name is entered correctly.
>
> when the execution gets here:
> USE [MYDATABASE]
>
> Shouldn't it be creating my database, and not throw this error? I am
> stumped!
>
Think about what's happening here. You're issuing a series of commands
to SQL as a single batch. That entire batch is compiled, and then
executed. At the time of compilation, the database doesn't exist, thus
an error is thrown.
In your original attempt, you were seperating commands into seperate
batches using the "GO" seperator. That, unfortunately, doesn't work
outside of a script file that is run through QA or ISQL.
It's a bit unusual for an application to create a database on the fly
like this. If you really must create this database this way, you're
going to have to do it in two steps. The first step will create the
database. The second, seperate, step will run the rest of the operation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for Roger and Tracy's input.
Hi Bryan,
I agree with Roger that when you send all the T-SQL block through one C#
net sqlcommand, it is just as you execute them in a single batch and the
T-SQL engine will report the error against the use statement on a
non-existing database. I suggest you separate the script into to parts,
create database and alter database and execute them in a separate
SqlCommand respectively.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for all the help, I was able to get it to work.
Bryan .
I have a script that creates a new database table called MYDATABAE, then it
will use USE [MYDATABASE]
to create tables.
Well.. It doesn't even get past the parser. It stops saying:
Could not locate entry in sysdatabases for database 'MYDATABASE'
Below you will see some of the script I am attempting to run, by the way,
this was created using SqlServer express management studio. At the bottom
of the script, see where the parser gives the error.
USE [master]
GO
/****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:
24
******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
BEGIN
CREATE DATABASE [MYDATABASE] ON PRIMARY
( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
END
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInsta
lled'))
begin
EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable
'
end
GO
ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
GO
ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
GO
ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
GO
ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
GO
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [MYDATABASE] SET READ_WRITE
GO
ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
GO
ALTER DATABASE [MYDATABASE] SET MULTI_USER
GO
ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
GO
USE [MYDATABASE] > this is where error occurs!
GO
... More sql ...
I am not sure what happended. This was working earlier today, but not
anymore...
Any and help would be appreciated!
Thanks,
BryanI ran the script you posted without error. Perhaps there is a
non-displayable character in your actual script. You might try re-typing
the limes immediately preceding and following the statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bryan" <BryanZM@.nospam.nospam> wrote in message
news:efZc8hvyGHA.4648@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a script that creates a new database table called MYDATABAE, then
> it will use USE [MYDATABASE]
> to create tables.
> Well.. It doesn't even get past the parser. It stops saying:
> Could not locate entry in sysdatabases for database 'MYDATABASE'
>
> Below you will see some of the script I am attempting to run, by the way,
> this was created using SqlServer express management studio. At the bottom
> of the script, see where the parser gives the error.
> USE [master]
> GO
> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:1
8:24
> ******/
> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
> BEGIN
> CREATE DATABASE [MYDATABASE] ON PRIMARY
> ( NAME = N'MYDATABASE', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE =
> 2048GB , FILEGROWTH = 10%)
> END
> GO
> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
> GO
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInsta
lled'))
> begin
> EXEC [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enab
le'
> end
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
> GO
> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
> GO
> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
> GO
> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
> GO
> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
> GO
> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
> GO
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
> GO
> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
> GO
> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
> GO
> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
> GO
> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
> GO
> ALTER DATABASE [MYDATABASE] SET READ_WRITE
> GO
> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
> GO
> ALTER DATABASE [MYDATABASE] SET MULTI_USER
> GO
> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
> GO
> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
> GO
> USE [MYDATABASE] > this is where error occurs!
> GO
> ... More sql ...
>
> I am not sure what happended. This was working earlier today, but not
> anymore...
> Any and help would be appreciated!
> Thanks,
> Bryan
>
>|||Hello Bryan,
I've also tried the script you pasted and it runs corretly on my side
also(except that I change the database file path according to my local
machine).
As for the error you mentioned, does it only occur when you use "Parse"
action to check the syntax? If so, the parser in management studio will
report error against the non-existing database since it hasn't been created
at parse time. However, the script is still able to be executed without any
problem.
Please feel free to post here if there is anything we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Ok, for some miraculous rerason it works now. But it created another
problem. I am now trying ot execute the same script in c# with a connection
to master. It does not work!!! I get an error like this:
Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near 'GO'.\r\nCould
not locate entry in sysdatabases for database 'GOIDB'. No entry found with
that name. Make sure that the name is entered correctly."
I can remove everything but the foloowing and it will execute fine:
It creates the Database without a hitch...
Any more suggestions?
Thanks!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u8jKcWwyGHA.1292@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
>I ran the script you posted without error. Perhaps there is a
>non-displayable character in your actual script. You might try re-typing
>the limes immediately preceding and following the statement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bryan" <BryanZM@.nospam.nospam> wrote in message
> news:efZc8hvyGHA.4648@.TK2MSFTNGP04.phx.gbl...
>|||Bryan wrote:
> Ok, for some miraculous rerason it works now. But it created another
> problem. I am now trying ot execute the same script in c# with a connectio
n
> to master. It does not work!!! I get an error like this:
> Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword
> 'EXEC'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near the keywor
d
> 'ALTER'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near
> 'GO'.\r\nIncorrect syntax near 'GO'.\r\nIncorrect syntax near 'GO'.\r\nCou
ld
> not locate entry in sysdatabases for database 'GOIDB'. No entry found with
> that name. Make sure that the name is entered correctly."
>
"GO" is not a SQL command, it's a batch seperator used inside a script
file that is executed within QA/Management Studio/ISQL
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks,
I replaced the GO with ";" and it appears I am almost there, now I get this
error:
Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
found with that name. Make sure that the name is entered correctly.
when the execution gets here:
USE [MYDATABASE]
Shouldn't it be creating my database, and not throw this error? I am
stumped!
Here is the script:
USE [master]
/****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:18:
24
******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
BEGIN
CREATE DATABASE [MYDATABASE] ON PRIMARY
( NAME = N'MYDATABASE', FILENAME = N'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MYDATABASE_log', FILENAME = N'c:\\Program Files\\Microsoft SQL
Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE_log.LDF' , SIZE = 26816KB , MAXSIZE
= 2048GB , FILEGROWTH = 10%)
END
;
EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
;
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInsta
lled')) begin EXEC
[MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable' end
;
ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
;
ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
;
ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
;
ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
;
ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
;
ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
;
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
;
ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
;
ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
;
ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
;
ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
;
ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
;
ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
;
ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
;
ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
;
ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
;
ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
;
ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
;
ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
;
ALTER DATABASE [MYDATABASE] SET READ_WRITE
;
ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
;
ALTER DATABASE [MYDATABASE] SET MULTI_USER
;
ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
;
ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
;
USE [MYDATABASE]
more stuff...
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F45F39.80506@.realsqlguy.com...
> Bryan wrote:
> "GO" is not a SQL command, it's a batch seperator used inside a script
> file that is executed within QA/Management Studio/ISQL
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||The issue you're running into is that the parser parses a complete batch
before it executes it. When you used the script with a GO after each line,
each line was sent to the parser as a separate batch. Now, you are sending
all the lines to the parser in a single batch. When it gets to the USE
statement, the database doesn't exist because the batch hasn't been
executed. You need to split the commands into separate batches - execute
everything up to the USE as one batch and then do the USE as a separate
batch.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bryan" <BryanZM@.nospam.nospam> wrote in message
news:%23BaPDf4yGHA.4232@.TK2MSFTNGP05.phx.gbl...
> Thanks,
> I replaced the GO with ";" and it appears I am almost there, now I get
> this error:
> Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
> found with that name. Make sure that the name is entered correctly.
>
> when the execution gets here:
> USE [MYDATABASE]
>
> Shouldn't it be creating my database, and not throw this error? I am
> stumped!
> Here is the script:
> USE [master]
> /****** Object: Database [MYDATABASE] Script Date: 08/28/2006 10:1
8:24
> ******/
> IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'MYDATABASE')
> BEGIN
> CREATE DATABASE [MYDATABASE] ON PRIMARY
> ( NAME = N'MYDATABASE', FILENAME = N'c:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE.mdf' , SIZE = 11456KB , MAXSIZE =
> UNLIMITED, FILEGROWTH = 1024KB )
> LOG ON
> ( NAME = N'MYDATABASE_log', FILENAME = N'c:\\Program Files\\Microsoft SQL
> Server\\MSSQL.1\\MSSQL\\DATA\\MYDATABASE_log.LDF' , SIZE = 26816KB ,
> MAXSIZE = 2048GB , FILEGROWTH = 10%)
> END
> ;
>
> EXEC dbo.sp_dbcmptlevel @.dbname=N'MYDATABASE', @.new_cmptlevel=90
> ;
> IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInsta
lled')) begin EXEC
> [MYDATABASE].[dbo].[sp_fulltext_database] @.action = 'enable' e
nd
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_NULL_DEFAULT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_NULLS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_PADDING OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ANSI_WARNINGS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ARITHABORT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_CLOSE ON
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_CREATE_STATISTICS ON
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_SHRINK OFF
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS ON
> ;
> ALTER DATABASE [MYDATABASE] SET CURSOR_CLOSE_ON_COMMIT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET CURSOR_DEFAULT GLOBAL
> ;
> ALTER DATABASE [MYDATABASE] SET CONCAT_NULL_YIELDS_NULL OFF
> ;
> ALTER DATABASE [MYDATABASE] SET NUMERIC_ROUNDABORT OFF
> ;
> ALTER DATABASE [MYDATABASE] SET QUOTED_IDENTIFIER OFF
> ;
> ALTER DATABASE [MYDATABASE] SET RECURSIVE_TRIGGERS OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ENABLE_BROKER
> ;
> ALTER DATABASE [MYDATABASE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
> ;
> ALTER DATABASE [MYDATABASE] SET DATE_CORRELATION_OPTIMIZATION OFF
> ;
> ALTER DATABASE [MYDATABASE] SET TRUSTWORTHY OFF
> ;
> ALTER DATABASE [MYDATABASE] SET ALLOW_SNAPSHOT_ISOLATION OFF
> ;
> ALTER DATABASE [MYDATABASE] SET PARAMETERIZATION SIMPLE
> ;
> ALTER DATABASE [MYDATABASE] SET READ_WRITE
> ;
> ALTER DATABASE [MYDATABASE] SET RECOVERY SIMPLE
> ;
> ALTER DATABASE [MYDATABASE] SET MULTI_USER
> ;
> ALTER DATABASE [MYDATABASE] SET PAGE_VERIFY CHECKSUM
> ;
> ALTER DATABASE [MYDATABASE] SET DB_CHAINING OFF
> ;
> USE [MYDATABASE]
>
> more stuff...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44F45F39.80506@.realsqlguy.com...
>|||Bryan wrote:
> Thanks,
> I replaced the GO with ";" and it appears I am almost there, now I get thi
s
> error:
> Could not locate entry in sysdatabases for database 'MYDATABASE'. No entry
> found with that name. Make sure that the name is entered correctly.
>
> when the execution gets here:
> USE [MYDATABASE]
>
> Shouldn't it be creating my database, and not throw this error? I am
> stumped!
>
Think about what's happening here. You're issuing a series of commands
to SQL as a single batch. That entire batch is compiled, and then
executed. At the time of compilation, the database doesn't exist, thus
an error is thrown.
In your original attempt, you were seperating commands into seperate
batches using the "GO" seperator. That, unfortunately, doesn't work
outside of a script file that is run through QA or ISQL.
It's a bit unusual for an application to create a database on the fly
like this. If you really must create this database this way, you're
going to have to do it in two steps. The first step will create the
database. The second, seperate, step will run the rest of the operation.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks for Roger and Tracy's input.
Hi Bryan,
I agree with Roger that when you send all the T-SQL block through one C#
net sqlcommand, it is just as you execute them in a single batch and the
T-SQL engine will report the error against the use statement on a
non-existing database. I suggest you separate the script into to parts,
create database and alter database and execute them in a separate
SqlCommand respectively.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for all the help, I was able to get it to work.
Bryan .
Subscribe to:
Posts (Atom)