9 Replies - 280 Views - Last Post: 18 May 2013 - 02:18 PM Rate Topic: -----

#1 Black_Lion  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 31-March 13

No new tables in database after executed sql query

Posted 10 May 2013 - 03:59 PM

Hi all. I am using Microsoft SQL Server 2008 Management Studio. I have been trying to create a database (containing tables etc) from a query file (Delivery.sql).

In the Object explorer I right-clicked Database folder and created the "Delivery database". I then clicked New Query and pasted the contents of Delivery.sql (from notepad) into the new query.

Now when I execute this code, it says it has completed successfully, but in the Object Explorer the "Delivery database" does not contain any tables :dontgetit: . I am confused and am in need of help . Thanks.

Here is the image of the object explorer:

Posted Image

Here is the code of the query file (Delivery.sql):


USE [Delivery database]
GO

/****** Object:  Database [Delivery]    Script Date: 05/09/2013 19:23:43 ******/
CREATE DATABASE [Delivery] ON  PRIMARY 
( NAME = N'Delivery', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Delivery.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Delivery_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Delivery_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Delivery] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Delivery database].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [Delivery] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [Delivery] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [Delivery] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [Delivery] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [Delivery] SET ARITHABORT OFF 
GO

ALTER DATABASE [Delivery] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [Delivery] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [Delivery] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [Delivery] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [Delivery] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [Delivery] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [Delivery] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [Delivery] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [Delivery] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [Delivery] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [Delivery] SET  DISABLE_BROKER 
GO

ALTER DATABASE [Delivery] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [Delivery] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [Delivery] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [Delivery] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [Delivery] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [Delivery] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [Delivery] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [Delivery] SET  READ_WRITE 
GO

ALTER DATABASE [Delivery] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [Delivery] SET  MULTI_USER 
GO

ALTER DATABASE [Delivery] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [Delivery] SET DB_CHAINING OFF 
GO





Is This A Good Question/Topic? 0
  • +

Replies To: No new tables in database after executed sql query

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: No new tables in database after executed sql query

Posted 10 May 2013 - 04:06 PM

Right-click the Tables folder and choose Refresh, or the Databases folder.

Mm, doesn't ON PRIMARY define it as a system database? I haven't checked this but worth looking in the System Databases folder. Edited: No, I don't think this is correct, it refers to System Tables.

Quote

PRIMARY
Specifies that the associated <filespec> list defines the primary file. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user filegroups. The first <filespec> entry in the primary filegroup becomes the primary file, which is the file containing the logical start of the database and its system tables. A database can have only one primary file. If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

This post has been edited by andrewsw: 10 May 2013 - 04:09 PM

Was This Post Helpful? 0
  • +
  • -

#3 Black_Lion  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 31-March 13

Re: No new tables in database after executed sql query

Posted 10 May 2013 - 04:52 PM

I refreshed the table and delivery database but still didnt appear (note: dont confuse 'Delivery' with 'Delivery Database', they are 2 different databases, which I forgot to mention earlier).

Im curious...

At first I changed the code on line no. 001 to USE [Delivery] since this database is the desired database to be created to contain table. Logically it makes sense to me. But when query executed it said "Database 'Delivery' already exists" :dozingoff:

Why did this error occur? Thanks
Was This Post Helpful? 0
  • +
  • -

#4 Black_Lion  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 23
  • Joined: 31-March 13

Re: No new tables in database after executed sql query

Posted 11 May 2013 - 09:00 AM

I realised that the sql script I used didnt create any due to the ommission of "CREATE TABLE". I got the database to display its tables, by using a different script with "CREATE TABLES" code in it. Thanks for assistance :)
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: No new tables in database after executed sql query

Posted 11 May 2013 - 09:13 AM

View PostBlack_Lion, on 11 May 2013 - 04:00 PM, said:

I realised that the sql script I used didnt create any due to the ommission of "CREATE TABLE". I got the database to display its tables, by using a different script with "CREATE TABLES" code in it. Thanks for assistance :)/>

Of course, it lacks tables!? :whistling: :rolleyes:

This post has been edited by andrewsw: 11 May 2013 - 09:14 AM

Was This Post Helpful? 1
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: No new tables in database after executed sql query

Posted 18 May 2013 - 11:30 AM

FYI, ON PRIMARY simply means to put the table in the primary data file. When you create a database, you start with one data file, the primary one. You can add more if you need them. If you haven't created mulitple files in a "filegroup" then you are always creating a table on primary, and you don't need to specify this.

This doesn't come up unless you want to have multiple filegroups, generally for huge databases that won't fit in one physical location or for load balancing purposes to spread your db over multiple physical locations to improve performance. For more info, see http://msdn.microsof...0(SQL.80).aspx.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: No new tables in database after executed sql query

Posted 18 May 2013 - 11:42 AM

Thanks @Bob. Yes, I recall now - like for partitioning.

http://msdn.microsof...v=sql.100).aspx

Your link includes the dot . at the end.

This post has been edited by andrewsw: 18 May 2013 - 11:45 AM

Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: No new tables in database after executed sql query

Posted 18 May 2013 - 11:58 AM

I hate it when that happens. :) Thanks for fixing it.

This post has been edited by BobRodes: 18 May 2013 - 11:59 AM

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2890
  • View blog
  • Posts: 9,597
  • Joined: 12-December 12

Re: No new tables in database after executed sql query

Posted 18 May 2013 - 12:08 PM

View PostBobRodes, on 18 May 2013 - 06:58 PM, said:

I hate it when that happens. :) Thanks for fixing it.

No worries but, I didn't fix it :) - I provided a different link. Here's yours though:

http://msdn.microsof...10(SQL.80).aspx
Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: No new tables in database after executed sql query

Posted 18 May 2013 - 02:18 PM

Maybe it's my turn to read more carefully. :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1