September 22, 2004 at 2:48 pm
Hi all,
I did generate script for all tables into a file from one server, then i want to execute that file in another server but getting errors (could not create forign key), is there any way to execute that file.
Thanks,
September 22, 2004 at 4:09 pm
You need to create the table with the primary key first (the table that the foreign key references).
Steve
September 22, 2004 at 11:15 pm
Hi,
Did you tick Script Primary keys etc on the options tab when creating the script?
September 23, 2004 at 7:51 am
Thanks for your response,
There are 200 tables, when i generate script i chose include primary and forien keys. Is there any way to find which one has create first by script.
Thanks.
September 23, 2004 at 9:32 am
Redgate's SQL Compare is smart enough to script your tables (and other objects) in the right order, though I'm not sure what it would do if there were nasty circular references. Its scripts also have good error handling in them. You can get a trial from their website which is time limited I think - might get you out of this problem, in which case I highly recommend you buy the product. Total the cost of your time to figure this one out, and I think the product might pay for itself quite soon.
No connection, just happy customer.
Bill.
September 23, 2004 at 10:20 am
You might consider DTS for copying objects if you have connectivity.
September 23, 2004 at 12:36 pm
Well, you could script the tables without the foreign keys. Build the tables, then go back and script the foreign keys.
Steve
September 23, 2004 at 6:18 pm
I've run into this same problem using DTS create object; so I don't know if that's going to help you. As pointed out above, the order of table creation is key. scripting the tables and constraints separately is probably your best option, although I've never done this in EM.
cl
Signature is NULL
September 24, 2004 at 6:38 am
Hi
I just first you built the script with just a tables and primary keys. Create seperate script to link foreign constraints.
Run first script, make sure you have all the tables and primary keys.
than Run second script to build relationship foreign constraints.
try it...
rp
RJPATEL
September 24, 2004 at 7:19 am
I wrote a VBA program to handle this very issue...
here are excerpts of the SQL output of interest to you, for tblUSER... please remember the need to customize for your own issues
if NULLIF (object_id('dbo.cntUser_eq_UserType'),0) > 0 BEGIN
DROP PROCEDURE cntUser_eq_UserType
END
GO
if NULLIF (object_id('dbo.qryUser_eq_UserType'),0) > 0 BEGIN
DROP PROCEDURE qryUser_eq_UserType
END
if NULLIF (object_id('dbo.fkUSERUSTYID'),0) > 0 BEGIN
ALTER TABLE tblUser DROP CONSTRAINT fkUSERUSTYID
END
GO
if NULLIF (object_id('dbo.cntUser_eq_Company'),0) > 0 BEGIN
DROP PROCEDURE cntUser_eq_Company
END
GO
if NULLIF (object_id('dbo.qryUser_eq_Company'),0) > 0 BEGIN
DROP PROCEDURE qryUser_eq_Company
END
go
if NULLIF (object_id('dbo.fkUSERCOMPID'),0) > 0 BEGIN
ALTER TABLE tblUser DROP CONSTRAINT fkUSERCOMPID
END
GO
if NULLIF (object_id('dbo.cntIssue_eq_User'),0) > 0 BEGIN
DROP PROCEDURE cntIssue_eq_User
END
GO
if NULLIF (object_id('dbo.qryIssue_eq_User'),0) > 0 BEGIN
DROP PROCEDURE qryIssue_eq_User
END
go
if NULLIF (object_id('dbo.fkISSUUSERID'),0) > 0 BEGIN
ALTER TABLE tblIssue DROP CONSTRAINT fkISSUUSERID
END
GO
if NULLIF (object_id('dbo.fkISSUSERVID'),0) > 0 BEGIN
ALTER TABLE tblIssue DROP CONSTRAINT fkISSUSERVID
END
GO
if NULLIF (object_id('dbo.qryUserType'),0) > 0 BEGIN
DROP PROCEDURE qryUserType
END
GO
if exists (select * from sysobjects where id = object_id('dbo.tblUserType') and sysstat & 0xf = 3) BEGIN
DROP TABLE "dbo"."tblUserType"
END
GO
--
if NULLIF (object_id('dbo.qryUser'),0) > 0 BEGIN
DROP PROCEDURE qryUser
END
GO
if exists (select * from sysobjects where id = object_id('dbo.tblUser') and sysstat & 0xf = 3) BEGIN
DROP TABLE "dbo"."tblUser"
END
GO
CREATE TABLE "tblUser" (
"USERID" int IDENTITY(1, 1) NOT NULL,
"USERAbrv" varchar(6) NOT NULL,
"USERTitle" varchar(30) NOT NULL,
"USERDescription" varchar(200) NULL,
"USERRowHistory" text NULL,
"USERRowDisplay" int DEFAULT 999 NOT NULL,
"USERRowStatus" varchar(1) DEFAULT 'A' NOT NULL,
"USERRowCreated" datetime DEFAULT getdate() NOT NULL,
"USERRowUpdated" datetime DEFAULT getdate() NOT NULL )
GO
ALTER TABLE tblUser
ADD CONSTRAINT "pkUser" PRIMARY KEY CLUSTERED ("USERID")
GO
CREATE INDEX "USERAbrv" ON "dbo"."tblUser"("USERAbrv")
GO
CREATE INDEX "USERTitle" ON "dbo"."tblUser"("USERTitle")
GO
GRANT REFERENCES, SELECT, INSERT, DELETE, UPDATE ON "tblUser" TO "public"
GO
CREATE PROCEDURE qryUser AS
SELECT *
FROM tblUser
WHERE USERRowStatus = 'A'
ORDER BY USERRowDisplay, USERTitle
GO
SET IDENTITY_INSERT tblUser ON
GO
INSERT INTO
tblUser (USERID, USERRowDisplay, USERABRV, USERTITLE) VALUES (1, 999, 'UNK', 'Unknown')
GO
SET IDENTITY_INSERT tblUser OFF
GO
--
CREATE TABLE "tblUserType" (
"USTYID" int IDENTITY(1, 1) NOT NULL,
"USTYAbrv" varchar(6) NOT NULL,
"USTYTitle" varchar(30) NOT NULL,
"USTYDescription" varchar(200) NULL,
"USTYRowHistory" text NULL,
"USTYRowDisplay" int DEFAULT 999 NOT NULL,
"USTYRowStatus" varchar(1) DEFAULT 'A' NOT NULL,
"USTYRowCreated" datetime DEFAULT getdate() NOT NULL,
"USTYRowUpdated" datetime DEFAULT getdate() NOT NULL )
GO
ALTER TABLE tblUserType
ADD CONSTRAINT "pkUserType" PRIMARY KEY CLUSTERED ("USTYID")
GO
CREATE INDEX "USTYAbrv" ON "dbo"."tblUserType"("USTYAbrv")
GO
CREATE INDEX "USTYTitle" ON "dbo"."tblUserType"("USTYTitle")
GO
GRANT REFERENCES, SELECT, INSERT, DELETE, UPDATE ON "tblUserType" TO "public"
GO
CREATE PROCEDURE qryUserType AS
SELECT *
FROM tblUserType
WHERE USTYRowStatus = 'A'
ORDER BY USTYRowDisplay, USTYTitle
GO
SET IDENTITY_INSERT tblUserType ON
GO
INSERT INTO
tblUserType (USTYID, USTYRowDisplay, USTYABRV, USTYTITLE) VALUES (1, 999, 'UNK', 'Unknown')
GO
SET IDENTITY_INSERT tblUserType OFF
GO
--
--
ALTER TABLE tblIssue
ADD ISSUUSERID INT NOT NULL DEFAULT 1
GO
ALTER TABLE tblIssue
ADD CONSTRAINT fkISSUUSERID FOREIGN KEY (ISSUUSERID)
REFERENCES tblUser(USERID)
GO
CREATE PROCEDURE cntIssue_eq_User
@whatUser int = 1
AS
SELECT count(*) AS cntIssue
FROM tblIssue
where ISSUUSERID = @whatUser and ISSURowStatus = 'A'
GO
CREATE PROCEDURE qryIssue_eq_User
@whatUser int = 1
AS
SELECT *
FROM tblIssue INNER JOIN tblUser ON ISSUUSERID = USERID
where ISSUUSERID = @whatUser and ISSURowStatus = 'A'
ORDER BY ISSURowDisplay, ISSUTitle
GO
--
ALTER TABLE tblUser
ADD USERUSTYID INT NOT NULL DEFAULT 1
GO
ALTER TABLE tblUser
ADD CONSTRAINT fkUSERUSTYID FOREIGN KEY (USERUSTYID)
REFERENCES tblUserType(USTYID)
GO
-- IF "User" = "UserType" THEN BEGIN /*... self-referential table */
-- SELECT *
-- FROM dbo.tblUser INNER JOIN
-- dbo.tblUser tblUser_1 ON dbo.tblUser.USERID = tblUser_1.USERID
-- WHERE (dbo.tblUser.USERRowStatus = 'A')
-- ORDER BY dbo.tblUser.USERRowDisplay, dbo.tblUser.USERTitle
-- END
CREATE PROCEDURE cntUser_eq_UserType
@whatUserType int = 1
AS
SELECT count(*) AS cntUser
FROM tblUser
where USERUSTYID = @whatUserType and USERRowStatus = 'A'
GO
CREATE PROCEDURE qryUser_eq_UserType
@whatUserType int = 1
AS
SELECT *
FROM tblUser INNER JOIN tblUserType ON USERUSTYID = USTYID
where USERUSTYID = @whatUserType and USERRowStatus = 'A'
ORDER BY USERRowDisplay, USERTitle
GO
--
ALTER TABLE tblUser
ADD USERNameFirst varChar(20) NOT NULL DEFAULT '???'
GO
ALTER TABLE tblUser
ADD USERNameLast varChar(20) NOT NULL DEFAULT '???'
GO
ALTER TABLE tblUser
ADD USERLoginAccount varChar(20) NOT NULL DEFAULT '???'
GO
ALTER TABLE tblUser
ADD USERJobTitle varChar(20) NOT NULL DEFAULT '???'
GO
ALTER TABLE tblUser
ADD USERNameFirstProunce varChar(20) NOT NULL DEFAULT '???'
GO
ALTER TABLE tblUser
ADD USERNameLastProunce varChar(20) NOT NULL DEFAULT '???'
GO
ALTER TABLE tblUser
ADD USERPrimaryOfficePhone varChar(20) NOT NULL DEFAULT '???'
GO
SET IDENTITY_INSERT tblUserType ON
GO
INSERT INTO tblUserType (USTYID, USTYRowDisplay, USTYABRV, USTYTITLE) VALUES (3, 240, 'PRI_LI', 'primary liaison')
GO
SET IDENTITY_INSERT tblUserType OFF
GO
SET IDENTITY_INSERT tblUserType ON
GO
INSERT INTO tblUserType (USTYID, USTYRowDisplay, USTYABRV, USTYTITLE) VALUES (4, 190, 'USER_T', 'test user')
GO
SET IDENTITY_INSERT tblUserType OFF
GO
SET IDENTITY_INSERT tblUserType ON
GO
INSERT INTO tblUserType (USTYID, USTYRowDisplay, USTYABRV, USTYTITLE) VALUES (5, 110, 'USER_D', 'demo user')
GO
SET IDENTITY_INSERT tblUserType OFF
GO
SET IDENTITY_INSERT tblUserType ON
GO
INSERT INTO tblUserType (USTYID, USTYRowDisplay, USTYABRV, USTYTITLE) VALUES (6, 200, 'ADM_G', 'global admin')
GO
SET IDENTITY_INSERT tblUserType OFF
GO
SET IDENTITY_INSERT tblUserType ON
GO
INSERT INTO tblUserType (USTYID, USTYRowDisplay, USTYABRV, USTYTITLE) VALUES (7, 120, 'USER_B', 'basic user')
GO
SET IDENTITY_INSERT tblUserType OFF
GO
...ETC
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply