How to import this file

I have a MS SQL 2000 Server and i wanna run a chat script whitch need SQL. I have a File which is called db.schema! I don`t know how to import these file into the DB. Can someone help me?
thxbcp?
DTS?
BULK INSERT?

Check out Books Online..|||Sorry i`m newbie! I don`t know what you mean.|||Do you have SQL Server client installed on your machine?

If you do, goo look those things up in the menu item called books online (BOL)

It's the SQL Server reference manual|||No i have only installed SQL 2000 Server!
I will install the client if there is no other way.|||Is it a desktop edition?

Even if it is, you'd have books online in your start menu...

I never work on the server though, except to do the install and monkey around with services, ect...|||No its the MSDN SQL Enterprise Server edition.|||OK, did you find books online yet?

Do you know what Enterprise Manager is?

How about Query Analyzer?|||I know what Enterprise Manager is. I can creat db`s and users. But i don`t know many morn things in MSSQL!|||Well go to Enterprise manager...

Open up a server

Right click on data transformation services

click on all tasks

Click import data

the wizard should do the work for you...|||No it want, i still have tried these way but the extension .schema is not supported!|||What kind of file is it?

can you view it with notepad?

just change the extension to text|||yes i can. I try it tomorrow as a text file.|||It didn`t work after i rename this file to .txt!

I will post the code in the File here:

# db.schema
# SQL database schemas and initial test data
# $Id: db.schema,v 1.48.2.1 2003/08/06 10:41:26 letreo Exp $

#
# Dumping data for table 'poc_user_account'
#

DROP TABLE IF EXISTS poc_user_account;
CREATE TABLE poc_user_account (
USER varchar(255) NOT NULL,
PASSWORD varchar(255),
CONFIRM_CODE char(32),
DISABLED int NOT NULL DEFAULT '0',
PRIMARY KEY (USER)
) TYPE=MyISAM;
ALTER TABLE poc_user_account ADD PASSWORD_NEW varchar(255);

#
# Test users in alphabetical order of family names
#

INSERT INTO poc_user_account (USER,PASSWORD,PASSWORD_NEW,DISABLED) VALUES ('mirko','giese','30ebe4d47a2a1661f9d04f84d80466c1 ',0);
INSERT INTO poc_user_account (USER,PASSWORD,PASSWORD_NEW,DISABLED) VALUES ('frerk','meyer','b6273c0ba3ae37a4d3d1c6b084797f2e ',0);
INSERT INTO poc_user_account (USER,PASSWORD,PASSWORD_NEW,DISABLED) VALUES ('michael','oertel','fe0d21a59c0f5ba4e4860d83641c8 4e8',0);
INSERT INTO poc_user_account (USER,PASSWORD,DISABLED) VALUES ('operator','',0);

#
# Dumping data for table 'poc_user_groups'
#

DROP TABLE IF EXISTS poc_user_groups;
CREATE TABLE poc_user_groups (
NAME varchar(255) NOT NULL,
MEMBER text,
PRIMARY KEY (NAME)
) TYPE=MyISAM;

#
# Groups
#

INSERT INTO poc_user_groups VALUES ('chatter',NULL);
INSERT INTO poc_user_groups VALUES ('operator','a:2:{i:0;s:8:"operator";i:1;s:5:"Admin";}');
INSERT INTO poc_user_groups VALUES ('moderator',NULL);
INSERT INTO poc_user_groups VALUES ('vip',NULL);

#
# Dumping data for table 'poc_user_data'
#

DROP TABLE IF EXISTS poc_user_data;
CREATE TABLE poc_user_data (
NICK char(32) NOT NULL,
USER char(255) NOT NULL,
NAME char(255) NOT NULL,
THEME char(25) NOT NULL DEFAULT '',
BIRTHDAY date NOT NULL,
GENDER char(1) NOT NULL DEFAULT '',
EMAIL char(255),
PRIVATE_INVITED char(32) NOT NULL DEFAULT '',
HIDE_EMAIL char(1) NOT NULL DEFAULT '1',
PICTURE_URL varchar(255),
HOMEPAGE_URL varchar(255),
INTERESTS text,
MOTTO text,
ICQ_NUMBER int NOT NULL DEFAULT '0',
AIM_NICKNAME char(30) NOT NULL DEFAULT '',
YIM_NICKNAME char(30) NOT NULL DEFAULT '',
COLOR char(6) DEFAULT '000000',
ONLINE char(1),
LAST_CHANNEL char(32),
ADVICE char(5) default 'quiet',
SCROLLSPEED int,
FRIENDS text,
GRADE char(20) NOT NULL DEFAULT 'GRADE_ROOKIE',
REGTIME datetime,
LAST_ACTIVE_TIME datetime,
ONLINE_TIME int NOT NULL DEFAULT '0',
LINES_PER_DAY double,
LOGINS_PER_DAY double,
DAYS_REGISTERED integer,
LAST_HOST char(50),
LAST_IP char(15),
LAST_USER_AGENT char(100),
LAST_SESSIONID char(35),
LAST_REFERER char(100),
MISC text,
TMP_INSTANCE text NOT NULL DEFAULT '',
PRIMARY KEY (NICK),
KEY (USER)
) TYPE=MyISAM;

#
# Test users in alphabetical order of family names
#

INSERT INTO poc_user_data (NICK,USER,NAME,BIRTHDAY,EMAIL,COLOR,REGTIME) VALUES ("operator","operator","The Operator",'1969-03-24',"me@.here.net","003300","1999-12-31 23:59:59");
INSERT INTO poc_user_data (NICK,USER,NAME,BIRTHDAY,EMAIL,COLOR,REGTIME) VALUES ("superman","mirko","Mirko Giese",'1971-02-29',"mirko@.giese.de","66AACC","2000-12-31 23:59:59");
INSERT INTO poc_user_data (NICK,USER,NAME,BIRTHDAY,EMAIL,COLOR,REGTIME) VALUES ("tux","frerk","Frerk Meyer",'1967-06-24',"frerk@.meyer.de","AACC66","1998-12-31 23:59:59");
INSERT INTO poc_user_data (NICK,USER,NAME,BIRTHDAY,EMAIL,COLOR,REGTIME) VALUES ("micha","michael","Michael Oertel",'1969-03-24',"michael@.oertel.de","CCAA66","1999-12-31 23:59:59");

#
# Dumping data for table 'poc_user_notes'
#

DROP TABLE IF EXISTS poc_user_notes;
CREATE TABLE poc_user_notes (
NICKNAME varchar(32) NOT NULL,
ABOUT varchar(32) NOT NULL,
NOTE text,
PRIMARY KEY (NICKNAME,ABOUT),
KEY (NICKNAME,ABOUT)
) TYPE=MyISAM;

#
# Dumping data for table 'poc_channels'
#

DROP TABLE IF EXISTS poc_channels;
CREATE TABLE poc_channels (
NAME char(32) NOT NULL,
PASSWORD char(12),
MESSAGE char(255),
MAX_LINE_NUMBER int NOT NULL,
CUR_LINE_NUMBER int NOT NULL,
START datetime,
STOP datetime,
TYPE int NOT NULL DEFAULT '0',
INVITED text,
ORDER_IDX int,
PRIMARY KEY (NAME),
KEY (NAME)
) TYPE=MyISAM;

#
# Default channel data
#

INSERT INTO poc_channels(NAME,MAX_LINE_NUMBER,CUR_LINE_NUMBER, TYPE,ORDER_IDX) VALUES ('default',50,0,0,0);
INSERT INTO poc_channels(NAME,MAX_LINE_NUMBER,CUR_LINE_NUMBER, TYPE,ORDER_IDX) VALUES ('moderation',50,0,1,1);

#
# Dumping data for table 'poc_line_buffer'
#

DROP TABLE IF EXISTS poc_line_buffer;
CREATE TABLE poc_line_buffer (
NAME char(32) NOT NULL,
LINE_0 text,
LINE_1 text,
LINE_2 text,
LINE_3 text,
LINE_4 text,
LINE_5 text,
LINE_6 text,
LINE_7 text,
LINE_8 text,
LINE_9 text,
LINE_10 text,
LINE_11 text,
LINE_12 text,
LINE_13 text,
LINE_14 text,
LINE_15 text,
LINE_16 text,
LINE_17 text,
LINE_18 text,
LINE_19 text,
LINE_20 text,
LINE_21 text,
LINE_22 text,
LINE_23 text,
LINE_24 text,
LINE_25 text,
LINE_26 text,
LINE_27 text,
LINE_28 text,
LINE_29 text,
LINE_30 text,
LINE_31 text,
LINE_32 text,
LINE_33 text,
LINE_34 text,
LINE_35 text,
LINE_36 text,
LINE_37 text,
LINE_38 text,
LINE_39 text,
LINE_40 text,
LINE_41 text,
LINE_42 text,
LINE_43 text,
LINE_44 text,
LINE_45 text,
LINE_46 text,
LINE_47 text,
LINE_48 text,
LINE_49 text,
PRIMARY KEY (NAME),
KEY (NAME)
) TYPE=MyISAM;

#
# Default channel data
#

INSERT INTO poc_line_buffer (NAME) VALUES ('default');
INSERT INTO poc_line_buffer (NAME) VALUES ('moderation');

#
# Dumping data for table 'poc_mails'
#

DROP TABLE IF EXISTS poc_mails;
CREATE TABLE poc_mails (
SENDER char(25) NOT NULL,
TIME char(19) NOT NULL,
RECIPIENT char(25) NOT NULL,
MAIL text,
TRASHED_BY_SENDER int NOT NULL DEFAULT '0',
TRASHED_BY_RECIPIENT int NOT NULL DEFAULT '0',
PRIMARY KEY (SENDER,RECIPIENT,TIME),
KEY (SENDER),
KEY (RECIPIENT)
) TYPE=MyISAM;

#
# Dumping data for table 'poc_hits'
#

DROP TABLE IF EXISTS poc_hits;
CREATE TABLE poc_hits (
USER char(25) NOT NULL,
USERPAGE int NOT NULL DEFAULT '0',
LINE int NOT NULL DEFAULT '0',
BANN int NOT NULL DEFAULT '0',
LOGIN int NOT NULL DEFAULT '0',
PRIMARY KEY (USER),
KEY (USER)
) TYPE=MyISAM;

DROP TABLE IF EXISTS poc_banned_users;
CREATE TABLE poc_banned_users (
USER varchar(25) NOT NULL,
TIME_BANNED datetime NOT NULL DEFAULT '2000-12-31 23:59:59',
BANNED_FOR char(32) NOT NULL DEFAULT '',
PRIMARY KEY (USER,BANNED_FOR),
KEY (USER),
KEY (BANNED_FOR)
) TYPE=MyISAM;

DROP TABLE IF EXISTS poc_cache;
CREATE TABLE poc_cache (
ID char(255) NOT NULL,
MAX_AGE datetime,
CONTENT text,
PRIMARY KEY (ID),
KEY (ID)
) TYPE=MyISAM;

DROP TABLE IF EXISTS poc_guestbook;
CREATE TABLE poc_guestbook (
USER varchar(25) NOT NULL,
SENDER varchar(25) NOT NULL,
TIME int NOT NULL,
POST text,
PRIMARY KEY (SENDER,TIME),
KEY (USER,TIME)
) TYPE=MyISAM;|||This isn't something which you would import.

As its a script, you would open iSQL (Sql Query Analyser) onto the appropriate server/db. Open your script file (File/Open) and execute it. F5.

However, on a quick look at the script it doesn't look like MS SQL so immediately you're going to have problems.

You'll have to change the syntax from whatever SQL it is to that which MS SQL Server will understand.

Mark|||This script should work ond MSSQL 2000!|||Have you run it is query analyser ?

I did against SQL 2000 and it errors out all over the place.
For example the fieldname NAME is a keyword so its not allowed.

Anyhow, if what your saying is correct, then it should run, if its not,
what are the error messages you're getting ?

Where did you get the script from ?|||The script is form http://phpopenchat.org|||Its by default for MYSQL. It is open source.

You'll need to modify it for any other database, ie SQL Server.|||ooo this sucks. i can`t do that. but thx for your help|||try and change it to SQL Server, it won't be too difficult.

Run the script in pieces, this will make it easier to see the issues.

For example the drop table syntax is incorrect, so as you're trying to create the db object from scratch, you could just remove those lines.

Also the first table has a field called USER which SQL Server will not allow. However if you change it to USERNAME and do so with all of the other references to the field USER in the script, that error will go away.

Be careful though, as if you change the fieldnames, you'll also need to search through the PHP code for references there and change accordingly.

Below you'll find some work I did on the script for you. This script will run, but remember that USER is now USERNAME so this will require PHP code changes

I've changed the following:

- # to -- (comments)
- Commented out the DROP TABLES
- Rename USER to USERNAME
- Changed field types of double to integer (should be fine)
- Commented out script with KEY ( fieldname ) - note not PRIMARY KEY - (you need to look at creating new indexes in SQL Server for these)
- Double quotes with quotes (this may need to be changed back again by you, depending on how your SQL Server is setup)


-- db.schema
-- SQL database schemas and initial test data
-- $Id: db.schema,v 1.48.2.1 2003/08/06 10:41:26 letreo Exp $

--
-- Dumping data for table 'poc_user_account'
--

--DROP TABLE poc_user_account;
CREATE TABLE poc_user_account (
USERNAME varchar(255) NOT NULL,
PASSWORD varchar(255),
CONFIRM_CODE char(32),
DISABLED int NOT NULL DEFAULT '0',
PRIMARY KEY (USERNAME)
)
ALTER TABLE poc_user_account ADD PASSWORD_NEW varchar(255);

--
-- Test users in alphabetical order of family names
--

INSERT INTO poc_user_account (USERNAME,PASSWORD,PASSWORD_NEW,DISABLED) VALUES ('mirko','giese','30ebe4d47a2a1661f9d04f84d80466c1 ',0);
INSERT INTO poc_user_account (USERNAME,PASSWORD,PASSWORD_NEW,DISABLED) VALUES ('frerk','meyer','b6273c0ba3ae37a4d3d1c6b084797f2e ',0);
INSERT INTO poc_user_account (USERNAME,PASSWORD,PASSWORD_NEW,DISABLED) VALUES ('michael','oertel','fe0d21a59c0f5ba4e4860d83641c8 4e8',0);
INSERT INTO poc_user_account (USERNAME,PASSWORD,DISABLED) VALUES ('operator','',0);

--
-- Dumping data for table 'poc_user_groups'
--

--DROP TABLE poc_user_groups;
CREATE TABLE poc_user_groups (
NAME varchar(255) NOT NULL,
MEMBER text,
PRIMARY KEY (NAME)
)

--
-- Groups
--

INSERT INTO poc_user_groups VALUES ('chatter',NULL);
INSERT INTO poc_user_groups VALUES ('operator','a:2:{i:0;s:8:'operator';i:1;s:5:'Admi n';}');
INSERT INTO poc_user_groups VALUES ('moderator',NULL);
INSERT INTO poc_user_groups VALUES ('vip',NULL);

--
-- Dumping data for table 'poc_user_data'
--

--DROP TABLE poc_user_data;
CREATE TABLE poc_user_data (
NICK char(32) NOT NULL,
USERNAME char(255) NOT NULL,
NAME char(255) NOT NULL,
THEME char(25) NOT NULL DEFAULT '',
BIRTHDAY datetime NOT NULL,
GENDER char(1) NOT NULL DEFAULT '',
EMAIL char(255),
PRIVATE_INVITED char(32) NOT NULL DEFAULT '',
HIDE_EMAIL char(1) NOT NULL DEFAULT '1',
PICTURE_URL varchar(255),
HOMEPAGE_URL varchar(255),
INTERESTS text,
MOTTO text,
ICQ_NUMBER int NOT NULL DEFAULT '0',
AIM_NICKNAME char(30) NOT NULL DEFAULT '',
YIM_NICKNAME char(30) NOT NULL DEFAULT '',
COLOR char(6) DEFAULT '000000',
ONLINE char(1),
LAST_CHANNEL char(32),
ADVICE char(5) default 'quiet',
SCROLLSPEED int,
FRIENDS text,
GRADE char(20) NOT NULL DEFAULT 'GRADE_ROOKIE',
REGTIME datetime,
LAST_ACTIVE_TIME datetime,
ONLINE_TIME int NOT NULL DEFAULT '0',
LINES_PER_DAY integer,
LOGINS_PER_DAY integer,
DAYS_REGISTERED integer,
LAST_HOST char(50),
LAST_IP char(15),
LAST_USERNAME_AGENT char(100),
LAST_SESSIONID char(35),
LAST_REFERER char(100),
MISC text,
TMP_INSTANCE text NOT NULL DEFAULT '',
PRIMARY KEY (NICK)
--,
-- KEY (USERNAME)
)

--
-- Test users in alphabetical order of family names
--

INSERT INTO poc_user_data (NICK,USERNAME,NAME,BIRTHDAY,EMAIL,COLOR,REGTIME) VALUES ('operator','operator','The Operator','1969-03-24','me@.here.net','003300','1999-12-31 23:59:59');
INSERT INTO poc_user_data (NICK,USERNAME,NAME,BIRTHDAY,EMAIL,COLOR,REGTIME) VALUES ('superman','mirko','Mirko Giese','1967-06-24','mirko@.giese.de','66AACC','1998-12-31 23:59:59');
INSERT INTO poc_user_data (NICK,USERNAME,NAME,BIRTHDAY,EMAIL,COLOR,REGTIME) VALUES ('tux','frerk','Frerk Meyer','1967-06-24','frerk@.meyer.de','AACC66','1998-12-31 23:59:59');
INSERT INTO poc_user_data (NICK,USERNAME,NAME,BIRTHDAY,EMAIL,COLOR,REGTIME) VALUES ('micha','michael','Michael Oertel','1969-03-24','michael@.oertel.de','CCAA66','1999-12-31 23:59:59');

--
-- Dumping data for table 'poc_user_notes'
--

--DROP TABLE poc_user_notes;
CREATE TABLE poc_user_notes (
NICKNAME varchar(32) NOT NULL,
ABOUT varchar(32) NOT NULL,
NOTE text,
PRIMARY KEY (NICKNAME,ABOUT)
)

--
-- Dumping data for table 'poc_channels'
--

--DROP TABLE poc_channels;
CREATE TABLE poc_channels (
NAME char(32) NOT NULL,
PASSWORD char(12),
MESSAGE char(255),
MAX_LINE_NUMBER int NOT NULL,
CUR_LINE_NUMBER int NOT NULL,
START datetime,
STOP datetime,
TYPE int NOT NULL DEFAULT '0',
INVITED text,
ORDER_IDX int,
PRIMARY KEY (NAME)
)

--
-- Default channel data
--

INSERT INTO poc_channels(NAME,MAX_LINE_NUMBER,CUR_LINE_NUMBER, TYPE,ORDER_IDX) VALUES ('default',50,0,0,0);
INSERT INTO poc_channels(NAME,MAX_LINE_NUMBER,CUR_LINE_NUMBER, TYPE,ORDER_IDX) VALUES ('moderation',50,0,1,1);

--
-- Dumping data for table 'poc_line_buffer'
--

--DROP TABLE poc_line_buffer;
CREATE TABLE poc_line_buffer (
NAME char(32) NOT NULL,
LINE_0 text,
LINE_1 text,
LINE_2 text,
LINE_3 text,
LINE_4 text,
LINE_5 text,
LINE_6 text,
LINE_7 text,
LINE_8 text,
LINE_9 text,
LINE_10 text,
LINE_11 text,
LINE_12 text,
LINE_13 text,
LINE_14 text,
LINE_15 text,
LINE_16 text,
LINE_17 text,
LINE_18 text,
LINE_19 text,
LINE_20 text,
LINE_21 text,
LINE_22 text,
LINE_23 text,
LINE_24 text,
LINE_25 text,
LINE_26 text,
LINE_27 text,
LINE_28 text,
LINE_29 text,
LINE_30 text,
LINE_31 text,
LINE_32 text,
LINE_33 text,
LINE_34 text,
LINE_35 text,
LINE_36 text,
LINE_37 text,
LINE_38 text,
LINE_39 text,
LINE_40 text,
LINE_41 text,
LINE_42 text,
LINE_43 text,
LINE_44 text,
LINE_45 text,
LINE_46 text,
LINE_47 text,
LINE_48 text,
LINE_49 text,
PRIMARY KEY (NAME)
)

--
-- Default channel data
--

INSERT INTO poc_line_buffer (NAME) VALUES ('default');
INSERT INTO poc_line_buffer (NAME) VALUES ('moderation');

--
-- Dumping data for table 'poc_mails'
--

--DROP TABLE poc_mails;
CREATE TABLE poc_mails (
SENDER char(25) NOT NULL,
TIME char(19) NOT NULL,
RECIPIENT char(25) NOT NULL,
MAIL text,
TRASHED_BY_SENDER int NOT NULL DEFAULT '0',
TRASHED_BY_RECIPIENT int NOT NULL DEFAULT '0',
PRIMARY KEY (SENDER,RECIPIENT,TIME)
--,
-- KEY (SENDER),
-- KEY (RECIPIENT)
)

--
-- Dumping data for table 'poc_hits'
--

--DROP TABLE poc_hits;
CREATE TABLE poc_hits (
USERNAME char(25) NOT NULL,
USERPAGE int NOT NULL DEFAULT '0',
LINE int NOT NULL DEFAULT '0',
BANN int NOT NULL DEFAULT '0',
LOGIN int NOT NULL DEFAULT '0',
PRIMARY KEY (USERNAME)
)

--DROP TABLE poc_banned_users;
CREATE TABLE poc_banned_users (
USERNAME varchar(25) NOT NULL,
TIME_BANNED datetime NOT NULL DEFAULT '2000-12-31 23:59:59',
BANNED_FOR char(32) NOT NULL DEFAULT '',
PRIMARY KEY (USERNAME,BANNED_FOR)
--,
-- KEY (USERNAME),
-- KEY (BANNED_FOR)
)

--DROP TABLE poc_cache;
CREATE TABLE poc_cache (
ID char(255) NOT NULL,
MAX_AGE datetime,
CONTENT text,
PRIMARY KEY (ID)
)

--DROP TABLE poc_guestbook;
CREATE TABLE poc_guestbook (
USERNAME varchar(25) NOT NULL,
SENDER varchar(25) NOT NULL,
TIME int NOT NULL,
POST text,
PRIMARY KEY (SENDER,TIME)
--,
-- KEY (USERNAME,TIME)
)|||Thx a lot. But do you know a good chat script which use PHP and MSSQL? I think this would be much easier than your way :-)!|||afraid not :-)|||I use most of the time MySQL DB. Beceaus it`s much easier. But now i have the job to install a chat on a MSSQL DB and that sucks hard.|||The references to USER which need to be changed to USERNAME are most likely only going to be found in the .inc files and theres only a few of those.

If I was you, I'd spend just a couple of hours on this, run the sql script I gave you, then search and replace/change the USER PHP code references and then try the chat room :-)

How to import a .sql file in ms sql server database ?

Hi,
I am currently new to microsoft sql server, so following a book I need to
create a empty database (is ok), afterwards I need to run a script
(name.sql) against this created database.
The problem is that I don't know how to do this (after having struggled for
one hour with the import / export function).
Who can help me ?
Cheers, Jan
ps. the content of the .sql script :
CREATE TABLE Company(
CompanyID int IDENTITY(1,1),
CompanyName varchar(40) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
ZipCode varchar(10) NOT NULL,
Comments text NULL,
CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
)
CREATE TABLE Employee(
SSN char(11) NOT NULL,
CompanyID int NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Salary numeric(12, 2) NOT NULL,
DateOfBirth datetime NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
)
CREATE TABLE Temp(
SSN char(11) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
CompanyName varchar(40) NULL,
CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
)
ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
FOREIGN KEY (CompanyID)
REFERENCES Company(CompanyID)You can run the script in Query Analyzer (in your SQL Server program group).
Have a read of Books Online before you try that. Here's a link but it should
also be installed with the client tools on your machine:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/qryanlzr/qryanlzr_1zqq.asp?frame=true
--
David Portas
SQL Server MVP
--|||Hi,
Creating the Database:-
1. Open Enterprise Manager
2. Expand the SQL Server groups and select the server name
3. Expand the server and right click above database
4. Choose New database
5. Give the database name and path for MDF AND LDF
6. Click OK
7. Right click above the new database and select options...Choose the
recovery model and click ok. See the details of recovery model in books
online
Executing .SQL file
1. Login to Query Analyzer (Choose from SQL server program groups)
2. OPEN the .SQL file using File -- open
3. Choose the database created from database list
4. Press F5 button in keyboard to execute the script
Thanks
Hari
SQL Server Mvp
"Jan" <janjansenbe@.gmail.com> wrote in message
news:OWU50uRlFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am currently new to microsoft sql server, so following a book I need to
> create a empty database (is ok), afterwards I need to run a script
> (name.sql) against this created database.
> The problem is that I don't know how to do this (after having struggled
> for one hour with the import / export function).
> Who can help me ?
> Cheers, Jan
> ps. the content of the .sql script :
> CREATE TABLE Company(
> CompanyID int IDENTITY(1,1),
> CompanyName varchar(40) NOT NULL,
> Address varchar(30) NOT NULL,
> City varchar(20) NOT NULL,
> State char(2) NOT NULL,
> ZipCode varchar(10) NOT NULL,
> Comments text NULL,
> CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
> )
> CREATE TABLE Employee(
> SSN char(11) NOT NULL,
> CompanyID int NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> Salary numeric(12, 2) NOT NULL,
> DateOfBirth datetime NOT NULL,
> CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
> )
> CREATE TABLE Temp(
> SSN char(11) NOT NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> CompanyName varchar(40) NULL,
> CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
> )
>
> ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
> FOREIGN KEY (CompanyID)
> REFERENCES Company(CompanyID)
>
>

How to import a .sql file in ms sql server database ?

Hi,
I am currently new to microsoft sql server, so following a book I need to
create a empty database (is ok), afterwards I need to run a script
(name.sql) against this created database.
The problem is that I don't know how to do this (after having struggled for
one hour with the import / export function).
Who can help me ?
Cheers, Jan
ps. the content of the .sql script :
CREATE TABLE Company(
CompanyID int IDENTITY(1,1),
CompanyName varchar(40) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
ZipCode varchar(10) NOT NULL,
Comments text NULL,
CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
)
CREATE TABLE Employee(
SSN char(11) NOT NULL,
CompanyID int NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Salary numeric(12, 2) NOT NULL,
DateOfBirth datetime NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
)
CREATE TABLE Temp(
SSN char(11) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
CompanyName varchar(40) NULL,
CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
)
ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
FOREIGN KEY (CompanyID)
REFERENCES Company(CompanyID)You can run the script in Query Analyzer (in your SQL Server program group).
Have a read of Books Online before you try that. Here's a link but it should
also be installed with the client tools on your machine:
http://msdn.microsoft.com/library/d...asp?frame=true
David Portas
SQL Server MVP
--|||Hi,
Creating the Database:-
1. Open Enterprise Manager
2. Expand the SQL Server groups and select the server name
3. Expand the server and right click above database
4. Choose New database
5. Give the database name and path for MDF AND LDF
6. Click OK
7. Right click above the new database and select options...Choose the
recovery model and click ok. See the details of recovery model in books
online
Executing .SQL file
1. Login to Query Analyzer (Choose from SQL server program groups)
2. OPEN the .SQL file using File -- open
3. Choose the database created from database list
4. Press F5 button in keyboard to execute the script
Thanks
Hari
SQL Server Mvp
"Jan" <janjansenbe@.gmail.com> wrote in message
news:OWU50uRlFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am currently new to microsoft sql server, so following a book I need to
> create a empty database (is ok), afterwards I need to run a script
> (name.sql) against this created database.
> The problem is that I don't know how to do this (after having struggled
> for one hour with the import / export function).
> Who can help me ?
> Cheers, Jan
> ps. the content of the .sql script :
> CREATE TABLE Company(
> CompanyID int IDENTITY(1,1),
> CompanyName varchar(40) NOT NULL,
> Address varchar(30) NOT NULL,
> City varchar(20) NOT NULL,
> State char(2) NOT NULL,
> ZipCode varchar(10) NOT NULL,
> Comments text NULL,
> CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
> )
> CREATE TABLE Employee(
> SSN char(11) NOT NULL,
> CompanyID int NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> Salary numeric(12, 2) NOT NULL,
> DateOfBirth datetime NOT NULL,
> CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
> )
> CREATE TABLE Temp(
> SSN char(11) NOT NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> CompanyName varchar(40) NULL,
> CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
> )
>
> ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
> FOREIGN KEY (CompanyID)
> REFERENCES Company(CompanyID)
>
>

How to import a .sql file in ms sql server database ?

Hi,
I am currently new to microsoft sql server, so following a book I need to
create a empty database (is ok), afterwards I need to run a script
(name.sql) against this created database.
The problem is that I don't know how to do this (after having struggled for
one hour with the import / export function).
Who can help me ?
Cheers, Jan
ps. the content of the .sql script :
CREATE TABLE Company(
CompanyID int IDENTITY(1,1),
CompanyName varchar(40) NOT NULL,
Address varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
ZipCode varchar(10) NOT NULL,
Comments text NULL,
CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
)
CREATE TABLE Employee(
SSN char(11) NOT NULL,
CompanyID int NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Salary numeric(12, 2) NOT NULL,
DateOfBirth datetime NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
)
CREATE TABLE Temp(
SSN char(11) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
CompanyName varchar(40) NULL,
CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
)
ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
FOREIGN KEY (CompanyID)
REFERENCES Company(CompanyID)
You can run the script in Query Analyzer (in your SQL Server program group).
Have a read of Books Online before you try that. Here's a link but it should
also be installed with the client tools on your machine:
http://msdn.microsoft.com/library/de...asp?frame=true
David Portas
SQL Server MVP
|||Hi,
Creating the Database:-
1. Open Enterprise Manager
2. Expand the SQL Server groups and select the server name
3. Expand the server and right click above database
4. Choose New database
5. Give the database name and path for MDF AND LDF
6. Click OK
7. Right click above the new database and select options...Choose the
recovery model and click ok. See the details of recovery model in books
online
Executing .SQL file
1. Login to Query Analyzer (Choose from SQL server program groups)
2. OPEN the .SQL file using File -- open
3. Choose the database created from database list
4. Press F5 button in keyboard to execute the script
Thanks
Hari
SQL Server Mvp
"Jan" <janjansenbe@.gmail.com> wrote in message
news:OWU50uRlFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am currently new to microsoft sql server, so following a book I need to
> create a empty database (is ok), afterwards I need to run a script
> (name.sql) against this created database.
> The problem is that I don't know how to do this (after having struggled
> for one hour with the import / export function).
> Who can help me ?
> Cheers, Jan
> ps. the content of the .sql script :
> CREATE TABLE Company(
> CompanyID int IDENTITY(1,1),
> CompanyName varchar(40) NOT NULL,
> Address varchar(30) NOT NULL,
> City varchar(20) NOT NULL,
> State char(2) NOT NULL,
> ZipCode varchar(10) NOT NULL,
> Comments text NULL,
> CONSTRAINT PK_Company PRIMARY KEY NONCLUSTERED (CompanyID)
> )
> CREATE TABLE Employee(
> SSN char(11) NOT NULL,
> CompanyID int NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> Salary numeric(12, 2) NOT NULL,
> DateOfBirth datetime NOT NULL,
> CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (SSN)
> )
> CREATE TABLE Temp(
> SSN char(11) NOT NULL,
> Firstname varchar(20) NOT NULL,
> Lastname varchar(20) NOT NULL,
> CompanyName varchar(40) NULL,
> CONSTRAINT PK_Temp PRIMARY KEY NONCLUSTERED (SSN)
> )
>
> ALTER TABLE Employee ADD CONSTRAINT FK__Employee_Company
> FOREIGN KEY (CompanyID)
> REFERENCES Company(CompanyID)
>
>

How To Implement sql server database on the client's computer?

Hello,
I finished my vb.net application, the application uses sql server and run pretty well on my pc. Now I want to install this application on the customer's computer, and the issue I'm not sure about is, what do I need to install on the client's computer concerning the database?
Do I install SQL Server and restore the database there? how to secure the database and make sure nobody will be able to edit the data from Enterprise Manager. What if someone uninstalled SQL Server?

Please I need help in this issue since I'm a beginner.

Thanks.

Quote:

Originally Posted by Tea Maker

Hello,
I finished my vb.net application, the application uses sql server and run pretty well on my pc. Now I want to install this application on the customer's computer, and the issue I'm not sure about is, what do I need to install on the client's computer concerning the database?
Do I install SQL Server and restore the database there? how to secure the database and make sure nobody will be able to edit the data from Enterprise Manager. What if someone uninstalled SQL Server?

Please I need help in this issue since I'm a beginner.

Thanks.

You will need to install SQL Server on your customer's server (or just his/her computer if they're the only person that will be using it). As for security, you'll need to set up database users and set their permissions accordingly. Obviously you'll need to change your app to reflect the change of database location.|||

Quote:

Originally Posted by DonlonP

You will need to install SQL Server on your customer's server (or just his/her computer if they're the only person that will be using it). As for security, you'll need to set up database users and set their permissions accordingly. Obviously you'll need to change your app to reflect the change of database location.

If I set users permissions then nobody will be able to open the database? because I've tried this before, but anyone can access the database through Enterprise Manager without username and password? And what about if someone uninstalled sql server?
I'll be thanksfull if you provide me some links where I can read articles about this issue because I couldn't find any related articles.

Thanks|||

Quote:

Originally Posted by Tea Maker

If I set users permissions then nobody will be able to open the database? because I've tried this before, but anyone can access the database through Enterprise Manager without username and password? And what about if someone uninstalled sql server?
I'll be thanksfull if you provide me some links where I can read articles about this issue because I couldn't find any related articles.

Thanks

On second thoughts you don't even need to set user permissions if you install SQL Server 2000 desktop edition (which is free!) this doesn't come with enterprise manager anyway. Download from:
http://www.microsoft.com/downloads/...&displaylang=en
Obviously if someone uninstalled SQL Server then you wouldn't have a database and your app wouldn't work, and the person who uninstalled it would be a complete muppet!|||

Quote:

Originally Posted by DonlonP

On second thoughts you don't even need to set user permissions if you install SQL Server 2000 desktop edition (which is free!) this doesn't come with enterprise manager anyway. Download from:
http://www.microsoft.com/downloads/...&displaylang=en
Obviously if someone uninstalled SQL Server then you wouldn't have a database and your app wouldn't work, and the person who uninstalled it would be a complete muppet!

Wow, Thanks alot that helped me out so much.
Just one more question, How can I transfer the database from my computer to the other, do I create a backup and restore it there? and where do I place my databases.

How to identify fragmented index for rebuild

Hi,

I want to find out what is a good way to identify indexes that need
rebuilding. I tried to run DBCC showcontig and identify them based on
the scan density %. And according to a Microsoft Tech Net - All About
SQL Server Indexes discussion, the expert stated that based on the
Density (below 20%), I can pick out those indexes associated to the
table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to
rebuild all indexes associated to the specific table. The strange part
is that some tables, which have 20% or below scan density do not have
any indexes. So, I am confused!

Thanks in advance.Check out the white paper on SQL 2000 Index Defrag Best Practices:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

> The strange part
> is that some tables, which have 20% or below scan density do not have
> any indexes. So, I am confused!

You can create an clustered index on a heap (a table with no clustered
index) in order to reorg the table. You can drop it afterward if it is not
needed. It's usually a good idea to have a clustered index on every table
unless you have a specific reason not to.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"xo55ox" <xo55ox@.hotmail.com> wrote in message
news:abbcb3d7.0404291344.14a3a778@.posting.google.c om...
> Hi,
> I want to find out what is a good way to identify indexes that need
> rebuilding. I tried to run DBCC showcontig and identify them based on
> the scan density %. And according to a Microsoft Tech Net - All About
> SQL Server Indexes discussion, the expert stated that based on the
> Density (below 20%), I can pick out those indexes associated to the
> table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to
> rebuild all indexes associated to the specific table. The strange part
> is that some tables, which have 20% or below scan density do not have
> any indexes. So, I am confused!
> Thanks in advance.

How to I run a DTS package from a stored procedure? thank

I created a DTS package to transfer data from a remote database into my local database, I want to run this DTS in my stored procedure, can I do that?

Please help me, thanks a lot

Hi,

you have to call it via dtsrun on the command prompt, ousing xp_cmdshell.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens Suessmeyer,

could you be more specific? can you give me a example?

thanks

|||Sure, look the the DTSRUN syntax, you can start the dtsrun either with a GUID naming the package which is stored in SQL Server or by a structured storage file, using the XP_CMDSHELL 'DTSRUN SomePackage' will get you the package run.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||Another method would be to use the system OLE automation SPs and use the DTS object model to invoke the package. Please search the web for several examples. Btw, this question is more suited for the SQL Server Integration Services newsgroup so I will move the thread there so someone there can point you to appropriate resources/links.|||

Please read through the article on 'Data Transformation Services (DTS)' @. http://www.databasejournal.com/features/mssql/article.php/1459181
The example in this article demostrates the use of OLE stored procedures and its benefits.

Btw - This forum majorly deals with SQL Server 2005 - Integration Services.

For DTS (SQL Server 2000) related questions post @. http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&cat=en_US_2b8e81a3-be64-42fa-bd81-c6d41de5a219&lang=en&cr=US

Thanks,
Loonysan

|||I would keep away from using OLE stored procedures - it is a convinient way to do it, but you are running inside SQL Server process. Thus any problems with DTS package may affect the server. Running the package outside using DTSRUN utility is better, as the SQL Server and package run in isolated processes and don't affect each other.

If you mistyped DTS, but was really asking about SSIS (this is SSIS forum after all) - the recommended way is to create Agent Job with the package step, don't assing any schedule to this job, and then start this job from your SQL stored procedure by calling Agent's SP. This provides the isolation as with DTSRUN, but additionally you may specify user context for the SSIS package - so the package does not have to run under the same user as SQL Server.