locked
SQL express server 2005 how to create new database from sql script file. RRS feed

  • Question

  • Hi All,

    I'm new SQL user, i spent half day for only creating new database in SQL Server 2005 express from a scipt file. And keep receiving the same error - Could not locate entry in sysdatabases for database named "helpdesk".

    My script file is

    USE master
    GO

    CREATE DATABASE HelpDesk
      ON
      (
        NAME = HelpDesk,
        FILENAME = 'c:\mssql\data\helpdesk.mdf',
        SIZE = 5,
        FILEGROWTH = 5
      )
      LOG ON
      (
        NAME = 'HelpDesk_log',
        FILENAME = 'c:\mssql\data\helpdesk_log.ldf',
        SIZE = 5MB,
        FILEGROWTH = 5MB
      )
    GO

    USE HelpDesk
    GO

    CREATE TABLE [dbo].[categories] (
        [category_id] [int] NOT NULL ,
        [cname] [varchar] (50) NULL ,
        [rep_id] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[db_keys] (
        [problems] [int] NULL ,
        [departments] [int] NULL ,
        [categories] [int] NULL ,
        [users] [int] NULL ,
        [Lang] [int] NULL
    ) ON [PRIMARY]
    GO

    .......

    Does the problem relate to the limitation of SQL express.

    Any help will be appreciated.

    Thanks All,

    Tuesday, June 22, 2010 4:24 AM

Answers

  • There is no problem with your script i have tried the same on my local machine and it is perfect .. can you try this out once . Can you check out the path 'c:\mssql\data\ ? dose the mentioned path exists on your machine ?

    Execute the below script ...

    USE master
    GO

    CREATE DATABASE HelpDesk
      ON
      (
        NAME = HelpDesk,
        FILENAME = 'c:\helpdesk.mdf',
        SIZE = 5,
        FILEGROWTH = 5
      )
      LOG ON
      (
        NAME = 'HelpDesk_log',
        FILENAME = 'c:\helpdesk_log.ldf',
        SIZE = 5MB,
        FILEGROWTH = 5MB
      )
    GO

    USE HelpDesk
    GO

    CREATE TABLE [helpdesk]. [dbo].[categories] (
        [category_id] [int] NOT NULL ,
        [cname] [varchar] (50) NULL ,
        [rep_id] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [helpdesk].[dbo].[db_keys] (
        [problems] [int] NULL ,
        [departments] [int] NULL ,
        [categories] [int] NULL ,
        [users] [int] NULL ,
        [Lang] [int] NULL
    ) ON [PRIMARY]
    GO

    • Marked as answer by dudetrance Tuesday, June 22, 2010 6:53 AM
    Tuesday, June 22, 2010 6:36 AM

All replies

  •  

    Use three part name in create table statement .. that will fix the issue

    USE master
    GO

    CREATE DATABASE HelpDesk
      ON
      (
        NAME = HelpDesk,
        FILENAME = 'c:\mssql\data\helpdesk.mdf',
        SIZE = 5,
        FILEGROWTH = 5
      )
      LOG ON
      (
        NAME = 'HelpDesk_log',
        FILENAME = 'c:\mssql\data\helpdesk_log.ldf',
        SIZE = 5MB,
        FILEGROWTH = 5MB
      )
    GO

    USE HelpDesk
    GO

    CREATE TABLE [helpdesk]. [dbo].[categories] (
        [category_id] [int] NOT NULL ,
        [cname] [varchar] (50) NULL ,
        [rep_id] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [helpdesk].[dbo].[db_keys] (
        [problems] [int] NULL ,
        [departments] [int] NULL ,
        [categories] [int] NULL ,
        [users] [int] NULL ,
        [Lang] [int] NULL
    ) ON [PRIMARY]
    GO
    ------------
    Thanks,Suhas V

    Tuesday, June 22, 2010 5:24 AM
  • Sorry but i'm not sure what you mean.

    Could you edit my script specifically?

    Thank a lot,

    Tuesday, June 22, 2010 6:14 AM
  • There is no problem with your script i have tried the same on my local machine and it is perfect .. can you try this out once . Can you check out the path 'c:\mssql\data\ ? dose the mentioned path exists on your machine ?

    Execute the below script ...

    USE master
    GO

    CREATE DATABASE HelpDesk
      ON
      (
        NAME = HelpDesk,
        FILENAME = 'c:\helpdesk.mdf',
        SIZE = 5,
        FILEGROWTH = 5
      )
      LOG ON
      (
        NAME = 'HelpDesk_log',
        FILENAME = 'c:\helpdesk_log.ldf',
        SIZE = 5MB,
        FILEGROWTH = 5MB
      )
    GO

    USE HelpDesk
    GO

    CREATE TABLE [helpdesk]. [dbo].[categories] (
        [category_id] [int] NOT NULL ,
        [cname] [varchar] (50) NULL ,
        [rep_id] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [helpdesk].[dbo].[db_keys] (
        [problems] [int] NULL ,
        [departments] [int] NULL ,
        [categories] [int] NULL ,
        [users] [int] NULL ,
        [Lang] [int] NULL
    ) ON [PRIMARY]
    GO

    • Marked as answer by dudetrance Tuesday, June 22, 2010 6:53 AM
    Tuesday, June 22, 2010 6:36 AM
  • Wow, i'm so dumb because of the warning which give me the wrong direction.

    Thanks,

    Have a nice day!

    Tuesday, June 22, 2010 6:53 AM