August 11, 2014 at 6:32 pm
Hello -
I am currently putting together multiple SQL files that perform a 7-step set of tasks to recreated a table with new Filegroups. I am trying to simplify my rebuild process as I go, by creating a batch file that puts me back to where I was the day before (so I do not have to do this manually each time). I am doing it this way for 2 reasons.
1. I am not a programmer, and as such I am keeping this very simple for me to do, and
2. The process works very well, allows me to keep the eventual process fairly well contained for memory utilization purposes (being split up into multiple files - 243 by the end to be exact), and again because I am not a programmer.
So the issue I have is that as I am trying to execute my batch file to make this work, I receive the following error.
Msg 102, Level 15, State 1, Server CA9762-O7010, Line 3
Incorrect syntax near 'CarePro_MSCRM'.
There is a main file that calls all the other .sql files, and is using SQLCMD functions / DOS functions to execute and eventually pause and exit the script. It looks like this:
/* SCRIPT: ExecuteALL.sql */
/* MODIFY CRM DATABASE */
--:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\Create-Filegroups.sql
--:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\Create-Files.sql
-- This is the main caller for each script.
SET NOCOUNT ON
GO
PRINT 'MODIFY CRM DATABASE'
:On Error exit
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_regimen_carepro_icdExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_regimen_carepro_payerExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_scopeaction_carepro_reasoncodeExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_servicerequest_carepro_icdExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_servicerequest_carepro_servicegExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_specialty_carepro_networkExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carrierExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_catalogExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_claimplaceofserviceExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_claimplaceofservicenetworkExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_classificationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_clinicalinformationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\Create-Filegroups.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\Create-Files.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\AccountExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\BusinessUnitExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\CampaignExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_account_contactExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_actiondestinationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_additionalinformationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_affiliationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_affiliationprogramExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_affiliationrequestExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_answerdetailExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_answerExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_answertypedetailExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_answertypeExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_attributelogExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_auditlogExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authorizationrequestcreatemessageExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authorizationrequestreorderabilityExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authorizationrequestreorderabilityidExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authrequestExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authrequestlogExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authrequestreorderabilitytermreasonExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authrequestreporttrackingExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_batchservicenetworkExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_bsacalculatorformedicationspecialtyExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_cardiosubcategoryaffiliationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_cardiosubcategoryExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_directions_carepro_optionmeddosExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_directions_carepro_requirmeddosExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_emrsystem_accountExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_group_contactExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_medication_carepro_icdExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_netopscontacttracking_carepro_nExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_network_accountExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_patient_contactExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_payer_carepro_networkExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_payergroup_carepro_programExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_procedure_carepro_icdExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_procedure_carepro_medicationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_program_carepro_groupExtensionBase.sql
PRINT 'CRM DATABASE IS COMPLETE'
GO
It is executed from the command line by way of a batch file (but I would like to have it execute from a SQL Agent job after I eventually get this resolved) in the following fashion.
SQLCMD -E -dCarePro_MSCRM -iC:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\executeALL.sql
Just as an additional example - here is what is being called (the 7-step process...It simply goes and creates a temp table, copies the data from the Live table over, drops the live table, then recreates it with a new Filegroup, and copies the data back in. It then adds all the constraints back in, and lastly - drops the temp table).
USE [CarePro_MSCRM]
GO
-- 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
GO
SET QUOTED_IDENTIFIER ON
GO
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]
GO
-- 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
GO
ALTER TABLE [dbo].[carepro_carepro_regimen_carepro_icdExtensionBase] CHECK CONSTRAINT [FK_carepro_carepro_regimen_carepro_icdExtensionBase_carepro_carepro_regimen_carepro_icdBase]
GO
-- 7. DROP TEMP TABLE.
DROP TABLE #CCRCICDEB
Any help would be greatly appreciated.
Thank you!
August 11, 2014 at 10:48 pm
Quick thought, remove the GO as it not an TSQL command/operator.
😎
August 12, 2014 at 1:36 am
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.
August 12, 2014 at 6:09 am
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);
August 12, 2014 at 7:28 am
GO should be fine in script files when executed via sqlcmd.
I'll be damned if I can see where the problem is though if it isn't that.
Do you get the same error if it's executed in SSMS using SQLCMD mode (Query menu -> SQLCMD Mode)?
Thanks
August 12, 2014 at 8:21 am
can you check the underlying tables for triggers? i'm thinking maybe a trigger is raising an error, maybe using dynamic SQL?
Lowell
August 13, 2014 at 10:54 am
Thanks to all of you for your suggestions here. I wound up going with a combination of suggestions from Eirikur Eiriksson, but also made sure that there were no Triggers in any of the tables, and did everything I could to keep the 'GO' keyword in place, but ultimately - that was what was causing all the trouble (and I believe I understand why now). In straight T-SQL / ANSI based commands - this is just a batch operations terminator. That party I already knew, but what I didn't realize is when you drop down into the SQLCMD layer, that command causes all sorts of issues (that part I am sure is due to how I was using it. 'The GO command may be used without preface, or preceded by !!:', but that is not how I was using it. This was taken from this link...http://msdn.microsoft.com/en-us/library/ms174187(v=sql.105).aspx).
So here is how it looks now.
1. Individual table reconstruction (7-step process):
DECLARE @SQL_STR NVARCHAR(MAX) = N'
USE CarePro_MSCRM;
-- 1. Create TEMP TABLE.
CREATE TABLE #AEB
([AccountId] [uniqueidentifier] NOT NULL,
[carepro_EndTime] [int] NULL,
[carepro_InOfficeBilling] [bit] NULL,
[carepro_Integration_Location_Key] [nvarchar](100) NULL,
[carepro_location_id] [nvarchar](100) NULL,
[carepro_NPI] [nvarchar](100) NULL,
[carepro_StartTime] [int] NULL,
[carepro_TaxID] [nvarchar](100) NULL,
[carepro_NetworkId] [uniqueidentifier] NULL,
[carepro_FacilityCannotAutoApprove] [bit] NULL,
[carepro_TimeZone] [int] NULL,
[carepro_Classification] [int] NULL,
[carepro_ContactRequestGuidsforBatch] [nvarchar](max) NULL,
[carepro_EffectiveFrom] [datetime] NULL,
[carepro_EffectiveTo] [datetime] NULL,
[carepro_HasPendingContactRequest] [bit] NULL,
[carepro_HasPendingPracticeRequest] [bit] NULL,
[carepro_PracticeRequestGuidsForBatch] [nvarchar](max) NULL,
[carepro_ProfileBatchStatus] [int] NULL,
[carepro_ReasonCodeGuidsForBatch] [nvarchar](max) NULL,
[carepro_AffiliationProgramId] [uniqueidentifier] NULL,
[carepro_ParentPracticeRequestid] [uniqueidentifier] NULL,
[carepro_ServiceNetworkId] [uniqueidentifier] NULL)
-- 2. Copy Data into TEMP TABLE.
INSERT INTO #AEB ([AccountId], [carepro_EndTime], [carepro_InOfficeBilling], [carepro_Integration_Location_Key], [carepro_location_id], [carepro_NPI], [carepro_StartTime], [carepro_TaxID], [carepro_NetworkId], [carepro_FacilityCannotAutoApprove], [carepro_TimeZone], [carepro_Classification], [carepro_ContactRequestGuidsforBatch], [carepro_EffectiveFrom], [carepro_EffectiveTo], [carepro_HasPendingContactRequest], [carepro_HasPendingPracticeRequest], [carepro_PracticeRequestGuidsForBatch], [carepro_ProfileBatchStatus], [carepro_ReasonCodeGuidsForBatch], [carepro_AffiliationProgramId], [carepro_ParentPracticeRequestid], [carepro_ServiceNetworkId])
SELECT AccountId, carepro_EndTime, carepro_InOfficeBilling, carepro_Integration_Location_Key, carepro_location_id, carepro_NPI, carepro_StartTime, carepro_TaxID, carepro_NetworkId, carepro_FacilityCannotAutoApprove, carepro_TimeZone, carepro_Classification, carepro_ContactRequestGuidsforBatch, carepro_EffectiveFrom, carepro_EffectiveTo, carepro_HasPendingContactRequest, carepro_HasPendingPracticeRequest, carepro_PracticeRequestGuidsForBatch, carepro_ProfileBatchStatus, carepro_ReasonCodeGuidsForBatch, carepro_AffiliationProgramId, carepro_ParentPracticeRequestid, carepro_ServiceNetworkId
FROM dbo.AccountExtensionBase
-- 3. DROP original Table.
DROP TABLE dbo.AccountExtensionBase
-- 4. Create new Table using new Filegroup.
CREATE TABLE [dbo].[AccountExtensionBase](
[AccountId] [uniqueidentifier] NOT NULL,
[carepro_EndTime] [int] NULL,
[carepro_InOfficeBilling] [bit] NULL,
[carepro_Integration_Location_Key] [nvarchar](100) NULL,
[carepro_location_id] [nvarchar](100) NULL,
[carepro_NPI] [nvarchar](100) NULL,
[carepro_StartTime] [int] NULL,
[carepro_TaxID] [nvarchar](100) NULL,
[carepro_NetworkId] [uniqueidentifier] NULL,
[carepro_FacilityCannotAutoApprove] [bit] NULL,
[carepro_TimeZone] [int] NULL,
[carepro_Classification] [int] NULL,
[carepro_ContactRequestGuidsforBatch] [nvarchar](max) NULL,
[carepro_EffectiveFrom] [datetime] NULL,
[carepro_EffectiveTo] [datetime] NULL,
[carepro_HasPendingContactRequest] [bit] NULL,
[carepro_HasPendingPracticeRequest] [bit] NULL,
[carepro_PracticeRequestGuidsForBatch] [nvarchar](max) NULL,
[carepro_ProfileBatchStatus] [int] NULL,
[carepro_ReasonCodeGuidsForBatch] [nvarchar](max) NULL,
[carepro_AffiliationProgramId] [uniqueidentifier] NULL,
[carepro_ParentPracticeRequestid] [uniqueidentifier] NULL,
[carepro_ServiceNetworkId] [uniqueidentifier] NULL,
CONSTRAINT [PK_AccountExtensionBase] PRIMARY KEY CLUSTERED
(
[AccountId] 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] TEXTIMAGE_ON [FG2]
-- 5. Copy data into new Table.
INSERT INTO dbo.AccountExtensionBase ([AccountId], [carepro_EndTime], [carepro_InOfficeBilling], [carepro_Integration_Location_Key], [carepro_location_id], [carepro_NPI], [carepro_StartTime], [carepro_TaxID], [carepro_NetworkId], [carepro_FacilityCannotAutoApprove], [carepro_TimeZone], [carepro_Classification], [carepro_ContactRequestGuidsforBatch], [carepro_EffectiveFrom], [carepro_EffectiveTo], [carepro_HasPendingContactRequest], [carepro_HasPendingPracticeRequest], [carepro_PracticeRequestGuidsForBatch], [carepro_ProfileBatchStatus], [carepro_ReasonCodeGuidsForBatch], [carepro_AffiliationProgramId], [carepro_ParentPracticeRequestid], [carepro_ServiceNetworkId])
SELECT AccountId, carepro_EndTime, carepro_InOfficeBilling, carepro_Integration_Location_Key, carepro_location_id, carepro_NPI, carepro_StartTime, carepro_TaxID, carepro_NetworkId, carepro_FacilityCannotAutoApprove, carepro_TimeZone, carepro_Classification, carepro_ContactRequestGuidsforBatch, carepro_EffectiveFrom, carepro_EffectiveTo, carepro_HasPendingContactRequest, carepro_HasPendingPracticeRequest, carepro_PracticeRequestGuidsForBatch, carepro_ProfileBatchStatus, carepro_ReasonCodeGuidsForBatch, carepro_AffiliationProgramId, carepro_ParentPracticeRequestid, carepro_ServiceNetworkId
FROM #AEB
-- 6. Add Constraints back in place.
ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD CONSTRAINT [carepro_carepro_affiliationprogram_account_AffiliationProgramId] FOREIGN KEY([carepro_AffiliationProgramId])
REFERENCES [dbo].[carepro_affiliationprogramBase] ([carepro_affiliationprogramId])
NOT FOR REPLICATION
ALTER TABLE [dbo].[AccountExtensionBase] CHECK CONSTRAINT [carepro_carepro_affiliationprogram_account_AffiliationProgramId]
ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD CONSTRAINT [carepro_carepro_practicerequest_account_ParentPracticeRequestid] FOREIGN KEY([carepro_ParentPracticeRequestid])
REFERENCES [dbo].[carepro_practicerequestBase] ([carepro_practicerequestId])
NOT FOR REPLICATION
ALTER TABLE [dbo].[AccountExtensionBase] CHECK CONSTRAINT [carepro_carepro_practicerequest_account_ParentPracticeRequestid]
ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD CONSTRAINT [carepro_carepro_servicenetwork_account_ServiceNetwork] FOREIGN KEY([carepro_ServiceNetworkId])
REFERENCES [dbo].[carepro_servicenetworkBase] ([carepro_servicenetworkId])
NOT FOR REPLICATION
ALTER TABLE [dbo].[AccountExtensionBase] CHECK CONSTRAINT [carepro_carepro_servicenetwork_account_ServiceNetwork]
ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD CONSTRAINT [carepro_network_account] FOREIGN KEY([carepro_NetworkId])
REFERENCES [dbo].[carepro_networkBase] ([carepro_networkId])
NOT FOR REPLICATION
ALTER TABLE [dbo].[AccountExtensionBase] CHECK CONSTRAINT [carepro_network_account]
ALTER TABLE [dbo].[AccountExtensionBase] WITH NOCHECK ADD CONSTRAINT [FK_AccountExtensionBase_AccountBase] FOREIGN KEY([AccountId])
REFERENCES [dbo].[AccountBase] ([AccountId])
NOT FOR REPLICATION
ALTER TABLE [dbo].[AccountExtensionBase] CHECK CONSTRAINT [FK_AccountExtensionBase_AccountBase]
/****** Object: Index [ndx_SystemManaged_Account] Script Date: 08/04/2014 17:28:33 ******/
CREATE NONCLUSTERED INDEX [ndx_SystemManaged_Account] ON [dbo].[AccountExtensionBase]
(
[carepro_TaxID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [NC1]
-- 7. DROP TEMP TABLE.
DROP TABLE #AEB'
EXEC (@SQL_STR);
2. Batch file that calls each piece of code (forgive me - I'm using the term 'code' loosely here):
/* SCRIPT: ExecuteALL.sql */
/* MODIFY CRM DATABASE */
-- This is the main caller for each script.
SET NOCOUNT ON
GO
PRINT 'MODIFYING CRM DATABASE'
:On Error exit
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\Create-Files.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_regimen_carepro_icdExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_regimen_carepro_payerExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_scopeaction_carepro_reasoncodeExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_servicerequest_carepro_icdExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_servicerequest_carepro_servicegExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_specialty_carepro_networkExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carrierExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_catalogExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_claimplaceofserviceExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_claimplaceofservicenetworkExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_classificationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_clinicalinformationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\AccountExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\BusinessUnitExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\CampaignExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_account_contactExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_actiondestinationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_additionalinformationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_affiliationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_affiliationprogramExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_affiliationrequestExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_answerdetailExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_answerExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_answertypedetailExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_answertypeExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_attributelogExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_auditlogExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authorizationrequestcreatemessageExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authorizationrequestreorderabilityExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authorizationrequestreorderabilityidExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authrequestExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authrequestlogExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authrequestreorderabilitytermreasonExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_authrequestreporttrackingExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_batchservicenetworkExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_bsacalculatorformedicationspecialtyExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_cardiosubcategoryaffiliationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_cardiosubcategoryExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_directions_carepro_optionmeddosExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_directions_carepro_requirmeddosExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_emrsystem_accountExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_group_contactExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_medication_carepro_icdExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_netopscontacttracking_carepro_nExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_network_accountExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_patient_contactExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_payer_carepro_networkExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_payergroup_carepro_programExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_procedure_carepro_icdExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_procedure_carepro_medicationExtensionBase.sql
:r C:\SQL\SQLCORECA1\CarePro_MSCRM\T-SQL\Filegroups\carepro_carepro_program_carepro_groupExtensionBase.sql
PRINT 'CRM DATABASE UPDATE IS COMPLETE'
GO
One other thing is that with this approach, I needed to create unique parameters for each one of the files (no big deal, but it will not work using the same parameter. There may be a programmatic way around this that I am not aware of - like some sort of flushing of memory / garbage collection type feature that I could use, but for now I am going with this approach...@SQL_STR through @SQL_STR240).
My next steps will be to simply run the SQLCMD part in a SQL Agent Job. This will work (have not tried it yet, but researched it) by setting up a job type of Operating system (CmdExec).
Thanks again to each of you for your direction and support!
August 26, 2014 at 1:38 am
Resurrecting this thread since this is where my quest started. . .
I have been successfully rebuilding my database design (I think). I say I think because as I have been creating the new design, I have been building as I go, and - I have noticed something a little concerning. My new files in my Filegroups are growing just fine, but my main .MDF file is not shrinking. Now - I have done a couple of things to see if perhaps the disk space usage is just not being reported correctly, but thus far - no changes:
- Ran DBCC CHECKDB against the database in question - no issues there.
- Ran DBCC UPDATEUSAGE to see if perhaps this wasn't needed after all the moving of data to new files/Filegroups, but again - no change.
- Backed up and restored the copy of the database, but again - no change.
- Started from scratch with a backup of the current database in our PROD environment. Ran my scripting to the point I am at, but again - no change.
There should be a current difference of nearly 30gb of data that has been redistributed to the new files/Filegroups, but my main .MDF is still the same size.
I've tried researching this, but the only things I could find were the ideas that I have tried to date (listed above).
Any insight into this would be really appreciated. This is currently being constructed on a 2008 R2 SQL Server Developer Edition install, but will eventually be in a Standard Edition Version of the environment (all of which are 64-bit if that make any difference).
Thank you all in advance!
August 26, 2014 at 3:11 am
Hi,
I can't see any explicit shrink commands in the queries you've posted, what are you running?
SQL doesn't automatically release space to the OS unless you've got AutoShrink enabled on the database (not recommended).
Thanks
August 26, 2014 at 3:17 am
SQL doesn't automatically reduce the file sizes. Unless autoshrink is on (very bad idea) or manual shrinks are done, there's nothing that will reduce the size of the files.
Bear in mind that shrink fragments indexes and you'll need to rebuild them afterwards, which may grow the files again.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2014 at 10:48 am
Thank you Gail and Gazareth...I do not like shrinking files, and avoid doing that as often as physically possible (just didn't know if the disk space shouldn't show less being in the main .MDF).
Many thanks, and I will carry on!
August 27, 2014 at 2:41 am
No problem, shrinks are fine if you've just deleted/moved a load of data like you have here.
It's a good idea to run an index defrag/rebuild afterwards though.
Don't know how you do it, but DBCC SHRINKFILE is preferred over DBCC SHRINKDATABASE.
Leave yourself some empty space in the file so the defrag/rebuild doesn't cause the file to grow again.
Hope that helps!
Cheers
Gaz
August 27, 2014 at 4:07 am
Forgot to add this for the free space in your files:
use yourdb
go
select file_id, type, name, cast(size/128.00 as decimal(19,2)) AS [Size(MB)], cast((size-FILEPROPERTY(name, 'spaceused'))/128.00 as decimal(19,2)) AS [Free Space(MB)]
from sys.database_files
August 27, 2014 at 2:07 pm
Thank you, Gazareth!
August 27, 2014 at 2:17 pm
Gazareth (8/27/2014)
No problem, shrinks are fine if you've just deleted/moved a load of data like you have here.It's a good idea to run an index defrag/rebuild afterwards though.
Don't know how you do it, but DBCC SHRINKFILE is preferred over DBCC SHRINKDATABASE.
Leave yourself some empty space in the file so the defrag/rebuild doesn't cause the file to grow again.
Hope that helps!
Cheers
Gaz
DBCC SHRINKDATABASE is the same as calling DBCC SHRINKFILE for all files in the database the only real difference is that the former respects the Minimum size property of the target database while the latter goes all the way down (but no further) to the actual data size.
😎
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply