use SQL script to create SQL server DB

Question

I have a very basic question about SQL server and Visual Studio 2010. I'm currently reading the book "C# 3.0 Unleashed with the .NET framework 3.5". I'm at the chapter where LINQ to SQL is explained. The writer asks to create a new database and use his script (given in the book) to create the tables and fill these with some data.

However, as I've never worked with SQL server before, this part is a bit confusing for me. The writer doesn't explain how to create the database. First I thought I could just right-click on Data Connections -> Create New SQL Server Database. However, when I tried that, I only got an error explaining no connection could be made.

When that didn't work, I added a new Service-based Database via Add -> New Item. This works (although I do not understand the difference between this and the previous thing I've tried. If someone could explain this for me, It'd be very appreciated), but now I'm supposed to use the script to create the tables and enter some data. I can right-click on the database and select New Query, but that window doesn't seem to accept such scripts.

First part of the script:

/****** Object: Table [dbo].[HospitalStaff]Script Date: 12/29/2007 21:42:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N’[HospitalStaff]’)
AND type in (N’U’))
BEGIN
CREATE TABLE [HospitalStaff](
[HospitalStaffID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Position] [varchar](50)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_HospitalStaff] PRIMARY KEY CLUSTERED
(
[HospitalStaffID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
)
END

I know it's a very basic question, but where can I execute this script? I've visited several websites with SQL server tutorials, but it's all pretty confusing.

Please don't prefix your titles with "Visual Studio 2010:" and such. That's what the tags are for. — John Saunders
– John Saunders, Commented Feb 7, 2012 at 17:21

Massimiliano Peluso · Accepted Answer · 2012-02-07 17:32:50Z

6

Add a new .sql file in your solution with .sql extension
Open that file
Right click
Execute script

enter image description here

more info at

http://msdn.microsoft.com/en-us/library/yea4bc1b(vs.80).aspx

answered Feb 7, 2012 at 17:32

Massimiliano Peluso

26.8k6 gold badges65 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bv202 Over a year ago

Thanks, but when I try that, I have to connect to the database engine which doesn't work as I'm getting the same error as the one I got when trying to create the new database.

Bv202 Over a year ago

This error: desmond.imageshack.us/Himg138/…

user1892410 Over a year ago

I get the insufficient memory exception message

aF. · Accepted Answer · 2012-02-07 17:29:47Z

2

You may use a program to access it.

For instance SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server.

Check more info HERE.

answered Feb 7, 2012 at 17:29

aF.

67k45 gold badges141 silver badges200 bronze badges

Comments

Nick Vaccaro · Accepted Answer · 2012-02-07 17:42:55Z

Right click on a database name in the Object Explorer in SSMS, go to Tasks -> Generate Scripts for all objects. Save it to a new query window, and you can see the text necessary for creating a new database from scratch and populating it with objects (tables, SPs, etc.)

Use this code to help you figure out how to generate a create string.

Example:

USE [master]
GO
/****** Object:  Database [DBA]    Script Date: 02/07/2012 11:40:09 ******/
CREATE DATABASE [DBA] ON  PRIMARY 
( NAME = N'DBA', FILENAME = N'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBA.mdf' , SIZE = 1303552KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'DBA_log', FILENAME = N'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DBA_log.ldf' , SIZE = 2052672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
...

keefdeveloper · Accepted Answer · 2012-10-18 15:17:10Z

1

Found an eloquent solution

Problem Database in Asp.NET web Visual Studio 2010 when running on PC with SQL Server 2008 Express I can use the interface to manually add tables but I wanted to run SQL code to create tables etc. You can't do this from the query interface (highlight database and from popup choose "New Query")

The idea stated above of using a script seemed like the way to go – but when I went to do that I couldn’t figure out how to connect to my database that was in the solution.

If I choose “Connection…” and the SQL Server Express 2008 that was running on my PC it would default to “master” and when I tried to browse to the database using [Options] it wasn’t in the list. It also went to “master” when I explicitly put the full path in C:\Users\keef.riffhard\Documents\Visual Studio 2010\WebSites\WebSite1\App_Data\MyAspDatabase.mdf

AGGGG!!

The trick was also in [Options] is “Additional Connection Parameters” with a note “Connection string parameters override graphical selections in other panels” So I highlighted the database in my solution and then went to properties and copied the connection string. I then pasted the connection string into the |Additional Connection Parameters| tab under [Options]

SUCCESS !!

answered Oct 18, 2012 at 15:17

keefdeveloper

111 bronze badge

1 Comment

Azulflame Over a year ago

please just post what works, most of us don't need/want to know everything you did to find it out.

Collectives™ on Stack Overflow

use SQL script to create SQL server DB

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related