От: | akasoft | ||
Дата: | 30.10.05 08:29 | ||
Оценка: | |||
#Имя: | FAQ.janus.jet2sql |
Для служебного пользования.
Переход на MS SQL.
1. Создайте базу данных с именем Janus.
2. В Sql Server Enterprise Manager произведите миграцию базы из Access в SQL Server.
Data Transformation Services -> правый мышь -> All Tasks -> Export Data.
В качестве целевой БД выбирете только что созданную Janus.
3. Для восстановления индексов выполните следующий скрипт на БД Janus:
BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_download_topics ( id int NOT NULL IDENTITY (1, 1), source nvarchar(32) NULL, messageid int NOT NULL, hint nvarchar(128) NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_download_topics ON GO IF EXISTS(SELECT * FROM dbo.download_topics) EXEC('INSERT INTO dbo.Tmp_download_topics (id, source, messageid, hint) SELECT id, source, messageid, hint FROM dbo.download_topics TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_download_topics OFF GO DROP TABLE dbo.download_topics GO EXECUTE sp_rename N'dbo.Tmp_download_topics', N'download_topics', 'OBJECT' GO ALTER TABLE dbo.download_topics ADD CONSTRAINT PK_download_topics PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_favorites ( id int NOT NULL IDENTITY (1, 1), mid int NOT NULL, fid int NOT NULL, comment nvarchar(255) NULL, url ntext NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_favorites ON GO IF EXISTS(SELECT * FROM dbo.favorites) EXEC('INSERT INTO dbo.Tmp_favorites (id, mid, fid, comment, url) SELECT id, mid, fid, comment, url FROM dbo.favorites TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_favorites OFF GO DROP TABLE dbo.favorites GO EXECUTE sp_rename N'dbo.Tmp_favorites', N'favorites', 'OBJECT' GO ALTER TABLE dbo.favorites ADD CONSTRAINT PK_favorites PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_favorites_folders ( id int NOT NULL IDENTITY (1, 1), name nvarchar(100) NULL, pid int NOT NULL, comment nvarchar(255) NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_favorites_folders ON GO IF EXISTS(SELECT * FROM dbo.favorites_folders) EXEC('INSERT INTO dbo.Tmp_favorites_folders (id, name, pid, comment) SELECT id, name, pid, comment FROM dbo.favorites_folders TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_favorites_folders OFF GO DROP TABLE dbo.favorites_folders GO EXECUTE sp_rename N'dbo.Tmp_favorites_folders', N'favorites_folders', 'OBJECT' GO ALTER TABLE dbo.favorites_folders ADD CONSTRAINT PK_favorites_folders PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_marks_outbox ( id int NOT NULL IDENTITY (1, 1), mark int NOT NULL, mid int NOT NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_marks_outbox ON GO IF EXISTS(SELECT * FROM dbo.marks_outbox) EXEC('INSERT INTO dbo.Tmp_marks_outbox (id, mark, mid) SELECT id, mark, mid FROM dbo.marks_outbox TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_marks_outbox OFF GO DROP TABLE dbo.marks_outbox GO EXECUTE sp_rename N'dbo.Tmp_marks_outbox', N'marks_outbox', 'OBJECT' GO ALTER TABLE dbo.marks_outbox ADD CONSTRAINT PK_marks_outbox PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION ALTER TABLE dbo.messages ADD CONSTRAINT DF_messages_ismarked DEFAULT 0 FOR ismarked GO ALTER TABLE dbo.messages ADD CONSTRAINT PK_messages PRIMARY KEY CLUSTERED ( mid ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_messages_dte ON dbo.messages ( dte ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_messages_gid ON dbo.messages ( gid ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_messages_pid ON dbo.messages ( pid ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_messages_tid ON dbo.messages ( tid ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_messages_uid ON dbo.messages ( uid ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_messages_outbox ( dte datetime NOT NULL, gid int NULL, hold bit NOT NULL, message ntext NULL, mid int NOT NULL IDENTITY (1, 1), reply int NOT NULL, subject nvarchar(128) NULL, tagline nvarchar(128) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_messages_outbox ON GO IF EXISTS(SELECT * FROM dbo.messages_outbox) EXEC('INSERT INTO dbo.Tmp_messages_outbox (dte, gid, hold, message, mid, reply, subject, tagline) SELECT dte, gid, hold, message, mid, reply, subject, tagline FROM dbo.messages_outbox TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_messages_outbox OFF GO DROP TABLE dbo.messages_outbox GO EXECUTE sp_rename N'dbo.Tmp_messages_outbox', N'messages_outbox', 'OBJECT' GO ALTER TABLE dbo.messages_outbox ADD CONSTRAINT PK_messages_outbox PRIMARY KEY CLUSTERED ( mid ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_rate_outbox ( id int NOT NULL IDENTITY (1, 1), mid int NOT NULL, rate int NOT NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_rate_outbox ON GO IF EXISTS(SELECT * FROM dbo.rate_outbox) EXEC('INSERT INTO dbo.Tmp_rate_outbox (id, mid, rate) SELECT id, mid, rate FROM dbo.rate_outbox TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_rate_outbox OFF GO DROP TABLE dbo.rate_outbox GO EXECUTE sp_rename N'dbo.Tmp_rate_outbox', N'rate_outbox', 'OBJECT' GO ALTER TABLE dbo.rate_outbox ADD CONSTRAINT PK_rate_outbox PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_rating ( dte datetime NOT NULL, id int NOT NULL IDENTITY (1, 1), mid int NOT NULL, rate smallint NOT NULL, rby smallint NOT NULL, tid int NOT NULL, uid int NOT NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT dbo.Tmp_rating ON GO IF EXISTS(SELECT * FROM dbo.rating) EXEC('INSERT INTO dbo.Tmp_rating (dte, id, mid, rate, rby, tid, uid) SELECT dte, id, mid, rate, rby, tid, uid FROM dbo.rating TABLOCKX') GO SET IDENTITY_INSERT dbo.Tmp_rating OFF GO DROP TABLE dbo.rating GO EXECUTE sp_rename N'dbo.Tmp_rating', N'rating', 'OBJECT' GO ALTER TABLE dbo.rating ADD CONSTRAINT PK_rating PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_rating_mid ON dbo.rating ( mid ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_rating_mid_rate ON dbo.rating ( mid, rate ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_rating_rate ON dbo.rating ( rate ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_rating_tid ON dbo.rating ( tid ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_rating_uid ON dbo.rating ( uid ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION ALTER TABLE dbo.server_forums ADD CONSTRAINT PK_server_forums PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_server_forums_name ON dbo.server_forums ( name ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION ALTER TABLE dbo.subscribed_forums ADD CONSTRAINT PK_subscribed_forums PRIMARY KEY CLUSTERED ( id ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_subscribed_forums_name ON dbo.subscribed_forums ( name ) ON [PRIMARY] GO ALTER TABLE dbo.subscribed_forums ADD CONSTRAINT DF_subscribed_forums_issync DEFAULT 0 FOR issync GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_topic_info ( mid int NOT NULL, answers_count smallint NULL, answers_unread smallint NULL, answers_rate smallint NULL, answers_smile smallint NULL, answers_agree smallint NULL, answers_disagree smallint NULL, answers_me_unread smallint NULL, answers_marked smallint NULL, answers_last_update_date datetime NULL, this_rate smallint NULL, this_smile smallint NULL, this_agree smallint NULL, this_disagree smallint NULL, gid int NULL ) ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.topic_info) EXEC('INSERT INTO dbo.Tmp_topic_info (mid, answers_count, answers_unread, answers_rate, answers_smile, answers_agree, answers_disagree, answers_me_unread, answers_marked, answers_last_update_date, this_rate, this_smile, this_agree, this_disagree, gid) SELECT mid, answers_count, answers_unread, answers_rate, answers_smile, answers_agree, answers_disagree, answers_me_unread, answers_marked, answers_last_update_date, this_rate, this_smile, this_agree, this_disagree, gid FROM dbo.topic_info TABLOCKX') GO DROP TABLE dbo.topic_info GO EXECUTE sp_rename N'dbo.Tmp_topic_info', N'topic_info', 'OBJECT' GO ALTER TABLE dbo.topic_info ADD CONSTRAINT PK_topic_info PRIMARY KEY CLUSTERED ( mid ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_topic_info_gid ON dbo.topic_info ( gid ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX IX_topic_info_answers_last_update_date ON dbo.topic_info ( answers_last_update_date ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION ALTER TABLE dbo.users ADD CONSTRAINT PK_users PRIMARY KEY CLUSTERED ( uid ) ON [PRIMARY] GO COMMIT BEGIN TRANSACTION CREATE TABLE dbo.Tmp_vars ( name nvarchar(24) NOT NULL, varvalue nvarchar(128) NULL ) ON [PRIMARY] GO IF EXISTS(SELECT * FROM dbo.vars) EXEC('INSERT INTO dbo.Tmp_vars (name, varvalue) SELECT name, varvalue FROM dbo.vars TABLOCKX') GO DROP TABLE dbo.vars GO EXECUTE sp_rename N'dbo.Tmp_vars', N'vars', 'OBJECT' GO ALTER TABLE dbo.vars ADD CONSTRAINT PK_vars PRIMARY KEY CLUSTERED ( name ) ON [PRIMARY] GO COMMIT
4. Создайте в каталоге, в котором лежит аксессовская база файл db_config.xml следующего содержания:
<Config> <ConfigurationString>.Sql</ConfigurationString> <ConnectionString>Server=(local);Database=Janus;Integrated Security=SSPI</ConnectionString> </Config>
5. Кто не спрятался, я не виноват.