SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLCMD error: Msg 102, Level 15, State 1...


SQLCMD error: Msg 102, Level 15, State 1...

Author
Message
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 1595
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!
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15017 Visits: 18593
Quick thought, remove the GO as it not an TSQL command/operator.
Cool
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 1595
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.
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15017 Visits: 18593
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.
Cool

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);


Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4000 Visits: 5813
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28080 Visits: 39927
can you check the underlying tables for triggers? i'm thinking maybe a trigger is raising an error, maybe using dynamic SQL?

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 1595
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!
SQL_ME_RICH
SQL_ME_RICH
SSC Eights!
SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)SSC Eights! (928 reputation)

Group: General Forum Members
Points: 928 Visits: 1595
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!
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4000 Visits: 5813
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86897 Visits: 45266
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search