Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    25

    Unanswered: Executing Dynamic SQL on a different DB

    Hi,

    Using SQL Server 2000, I am trying to copy the table relationships from the source DB 'LIVE_CONTROL' to a copy of the DB. The tables are an exact match.

    I'm using @RIScript to store my executable string and as far as I can see this is fine however. I don't think my 'USE[LIVE_CONTROL_COPY19052009]' bolted onto the front is having the affect I desired.


    The copy database for the purpose of this description starts off with no table relationships, just tables, data, keys and indexes. When I first ran the below code it did not have the 4096 on the end of the scripttype variable. (this 4096 tells the script to include 'if exists then don't bother' statements) The script failed because not only does it try to create constraints but indexes and keys aswell and the indexes already existed in the copy DB.


    Take 2 (with 4096 if exists statements included) and the script appeared to run fine however.... Still no constraints on my Copy DB. I believe that when the dynamic script is being executed it is doing so on the LIVE_CONTROL DB.

    The whole script is being run in a connection to LIVE_CONTROL. If that makes sense to anyone are you aware of how to get the dynamic SQL to execute against a specific DB.

    Thanks in advance

    Chris




    Code:
    SET NOCOUNT ON;
      
    
    --Creates Scripts To @T(I)ScriptLocation
    
    DECLARE @oServer			AS INT
    DECLARE @method			AS VARCHAR(300)
    DECLARE @TSQL				AS VARCHAR(4000)
    DECLARE @ScriptType			AS INT
    DECLARE @TName			AS VARCHAR (100)
    DECLARE @RIScript		AS VARCHAR (MAX)
    
    --Initialisation
    SET @TSQL =''
    SET @TName =''
    SET @RIScript = ''
    SET @ScriptType = 131072|1073741824|256|520093696|4096
    
    /*
    131072		iSQLDMOScript_IncludeHeaders
    1073741824	iSQLDMOScript_NoIdentity
    256			SQLDMOScript_AppendToFile
    520093696
    */
    
    
    --Preparation
    EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
    EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'
    EXEC sp_OAMethod @oServer, 'Connect', NULL, "CHRIS-H-XP"
    
    
    
    --Create Tables Script
    DECLARE TablesToCreate CURSOR
    	FOR SELECT [name] FROM LIVE_CONTROL..sysobjects WHERE xtype = 'U'
    OPEN TablesToCreate
    	FETCH NEXT FROM TablesToCreate INTO @TName
    	WHILE @@FETCH_STATUS = 0
    	
    
    	BEGIN
    	SET @method = 'Databases("LIVE_CONTROL").Tables("'+@TName+'").Script (' + CAST (@ScriptType AS CHAR)+')'
    	EXEC sp_OAMethod @oServer, @method ,                           
    							@TSQL OUTPUT
    	
    
    	SET @RIScript = @RIScript + '		' +@TSQL
    
    	FETCH NEXT FROM TablesToCreate INTO @TName
    	END
    
    
    CLOSE TablesToCreate
    DEALLOCATE TablesToCreate 
    
    EXEC sp_OADestroy @oServer
    
    SET @RIScript = 'USE[LIVE_CONTROL_COPY19052009]			'+REPLACE(@RIScript,'GO','')
    EXEC(@RIScript)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you PRINT @RIScript and post here
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    25
    This is @RIScript at time of execution

    Code:
    USE[LIVE_CONTROL_COPY20052009]					/****** Object:  Table [dbo].[CONTROL_TablePrimaryColumns]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_TablePrimaryColumns]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_TablePrimaryColumns] ADD 
    	CONSTRAINT [DF__CONTROL_T__dDate__0A7378A9] DEFAULT (getdate()) FOR [dDate],
    	CONSTRAINT [DF__CONTROL_T__sHost__0B679CE2] DEFAULT (host_name()) FOR [sHost],
    	CONSTRAINT [PK_CONTROL_TablePrimaryColumns] PRIMARY KEY  CLUSTERED 
    	(
    		[iPrimaryColumnID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    END
    
    
    
    
    		/****** Object:  Table [dbo].[CONTROL_Tables]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_Tables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_Tables] ADD 
    	CONSTRAINT [DF__CONTROL_T__dDate__0D4FE554] DEFAULT (getdate()) FOR [dDate],
    	CONSTRAINT [DF__CONTROL_T__sHost__0E44098D] DEFAULT (host_name()) FOR [sHost],
    	CONSTRAINT [PK_CONTROL_Tables] PRIMARY KEY  CLUSTERED 
    	(
    		[uTableGUID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
    	CONSTRAINT [FK_CONTROL_Tables_CONTROL_TablePrimaryColumns_1] FOREIGN KEY 
    	(
    		[iPrimaryColumnID]
    	) REFERENCES [CONTROL_TablePrimaryColumns] (
    		[iPrimaryColumnID]
    	),
    	CONSTRAINT [FK_CONTROL_Tables_CONTROL_TablesLockType_1] FOREIGN KEY 
    	(
    		[iLockTypeID]
    	) REFERENCES [CONTROL_TablesLockType] (
    		[iLockTypeID]
    	)
    END
    
    
    
    
    		/****** Object:  Table [dbo].[CONTROL_TablesLockType]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_TablesLockType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_TablesLockType] ADD 
    	CONSTRAINT [DF__CONTROL_T__dDate__102C51FF] DEFAULT (getdate()) FOR [dDate],
    	CONSTRAINT [DF__CONTROL_T__sHost__11207638] DEFAULT (host_name()) FOR [sHost],
    	CONSTRAINT [PK_CONTROL_TablesLockType] PRIMARY KEY  CLUSTERED 
    	(
    		[iLockTypeID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    END
    
    
    
    
    		
    		
    		
    		
    		
    		/****** Object:  Table [dbo].[CONTROL_User]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_User] ADD 
    	CONSTRAINT [IX_CONTROL_User_sNTLogin] UNIQUE  CLUSTERED 
    	(
    		[sNTLogin]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    END
    
    
    
    
    		
    		
    		
    		/****** Object:  Table [dbo].[CONTROL_Module]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_Module]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_Module] ADD 
    	CONSTRAINT [IX_CONTROL_Module] UNIQUE  NONCLUSTERED 
    	(
    		[uModuleGUID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    END
    
    
    
    
    		
    		
    		
    		
    		/****** Object:  Table [dbo].[CONTROL_RoleAccess]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_RoleAccess]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_RoleAccess] ADD 
    	CONSTRAINT [PK_CONTROL_RoleAccess] PRIMARY KEY  CLUSTERED 
    	(
    		[iRoleID],
    		[uModuleGUID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    END
    
    
    
    
    		
    		
    		
    		
    		/****** Object:  Table [dbo].[CONTROL_HelpFileVersions]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_HelpFileVersions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_HelpFileVersions] ADD 
    	CONSTRAINT [DF_CONTROL_HelpFileVersions_dDateLoaded] DEFAULT (getdate()) FOR [dDateLoaded]
    END
    
    
    
    
    		
    		/****** Object:  Table [dbo].[CONTROL_UseCaseRole]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_UseCaseRole]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_UseCaseRole] ADD 
    	CONSTRAINT [DF__CONTROL_U__uGUID__6DD739FB] DEFAULT (newid()) FOR [uGUID],
    	CONSTRAINT [DF__CONTROL_U__dDate__6ECB5E34] DEFAULT (getdate()) FOR [dDate],
    	CONSTRAINT [DF__CONTROL_U__sHost__6FBF826D] DEFAULT (host_name()) FOR [sHost],
    	CONSTRAINT [PK_CONTROL_UseCaseRole] PRIMARY KEY  CLUSTERED 
    	(
    		[iUseCaseID],
    		[iRoleID],
    		[iUserID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    END
    
    
    
    
    		/****** Object:  Table [dbo].[CONTROL_UseCase]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_UseCase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_UseCase] ADD 
    	CONSTRAINT [DF__CONTROL_U__uGUID__71A7CADF] DEFAULT (newid()) FOR [uGUID],
    	CONSTRAINT [DF__CONTROL_U__dDate__729BEF18] DEFAULT (getdate()) FOR [dDate],
    	CONSTRAINT [DF__CONTROL_U__sHost__73901351] DEFAULT (host_name()) FOR [sHost],
    	CONSTRAINT [PK_CONTROL_UseCase] PRIMARY KEY  CLUSTERED 
    	(
    		[iUseCaseID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    END
    
    
    
    
    		/****** Object:  Table [dbo].[CONTROL_Lock]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_Lock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_Lock] ADD 
    	CONSTRAINT [DF__CONTROL_L__dDate__7E0DA1C4] DEFAULT (getdate()) FOR [dDate],
    	CONSTRAINT [DF__CONTROL_L__sHost__7F01C5FD] DEFAULT (host_name()) FOR [sHost],
    	CONSTRAINT [FK_CONTROL_Lock_CONTROL_LockDataSet_1] FOREIGN KEY 
    	(
    		[iLockDataSetID]
    	) REFERENCES [CONTROL_LockDataSet] (
    		[iLockDataSetID]
    	),
    	CONSTRAINT [FK_CONTROL_Lock_CONTROL_Tables_1] FOREIGN KEY 
    	(
    		[uTableGUID]
    	) REFERENCES [CONTROL_Tables] (
    		[uTableGUID]
    	)
    END
    
    
    
    
    		/****** Object:  Table [dbo].[CONTROL_LockDataSet]    Script Date: 20/05/2009 11:12:03 ******/
    if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CONTROL_LockDataSet]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
    ALTER TABLE [CONTROL_LockDataSet] ADD 
    	CONSTRAINT [DF__CONTROL_L__dDate__00EA0E6F] DEFAULT (getdate()) FOR [dDate],
    	CONSTRAINT [DF__CONTROL_L__sHost__01DE32A8] DEFAULT (host_name()) FOR [sHost],
    	CONSTRAINT [PK_CONTROL_LockDataSet] PRIMARY KEY  CLUSTERED 
    	(
    		[iLockDataSetID]
    	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
    END

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The code in post #1 is SQL 2005, not SQL 2000.

    I think you might want to examine your exists statements in @RIScript
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •