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 :-)
Dev2624 wrote:
, but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)