SQL_ME_RICH (8/12/2014)
You mean where I have it in the script(s) I am trying to call, listed as:
USE [CarePro_MSCRM];GO
I should have mentioned that I tried changing that 6 different ways, but the error is the same with each:
USE CarePro_MSCRM;
USE CarePro_MSCRM;
GO
USE CarePro_MSCRM
GO
USE [CarePro_MSCRM];
USE [CarePro_MSCRM];
GO
USE [CarePro_MSCRM]
GO
None of these approaches makes any difference.
First of all, sorry for the previous incomplete answer, battling the network connection at the beach:-P
The way to do this is to construct a string variable and use execute or sp_executesql, something like the example below. This allows you to change the database in scope of the execution.
😎
DECLARE @SQL_STR NVARCHAR(MAX) = N'
USE [CarePro_MSCRM];
-- 1. Create TEMP TABLE.
CREATE TABLE #CCRCICDEB
([carepro_carepro_regimen_carepro_icdId] [uniqueidentifier] NOT NULL);
-- 2. Copy Data into TEMP TABLE
INSERT INTO #CCRCICDEB ([carepro_carepro_regimen_carepro_icdId])
SELECT carepro_carepro_regimen_carepro_icdId
FROM dbo.carepro_carepro_regimen_carepro_icdExtensionBase;
-- 3. DROP original Table.
DROP TABLE dbo.carepro_carepro_regimen_carepro_icdExtensionBase;
-- 4. Create new Table using new Filegroup
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
CREATE TABLE [dbo].[carepro_carepro_regimen_carepro_icdExtensionBase](
[carepro_carepro_regimen_carepro_icdId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_carepro_carepro_regimen_carepro_icdExtensionBase] PRIMARY KEY CLUSTERED
(
[carepro_carepro_regimen_carepro_icdId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [FG1]
) ON [FG1];
-- 5. Copy data into new Table.
INSERT INTO dbo.carepro_carepro_regimen_carepro_icdExtensionBase ([carepro_carepro_regimen_carepro_icdId])
SELECT carepro_carepro_regimen_carepro_icdId
FROM #CCRCICDEB;
-- 6. Add Constraints back in place.
ALTER TABLE [dbo].[carepro_carepro_regimen_carepro_icdExtensionBase] WITH NOCHECK ADD CONSTRAINT [FK_carepro_carepro_regimen_carepro_icdExtensionBase_carepro_carepro_regimen_carepro_icdBase] FOREIGN KEY([carepro_carepro_regimen_carepro_icdId])
REFERENCES [dbo].[carepro_carepro_regimen_carepro_icdBase] ([carepro_carepro_regimen_carepro_icdId])
NOT FOR REPLICATION ;
ALTER TABLE [dbo].[carepro_carepro_regimen_carepro_icdExtensionBase] CHECK CONSTRAINT [FK_carepro_carepro_regimen_carepro_icdExtensionBase_carepro_carepro_regimen_carepro_icdBase];
-- 7. DROP TEMP TABLE.
DROP TABLE #CCRCICDEB;
'
EXEC (@SQL_STR);