Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQLCMD error: Msg 102, Level 15, State 1... Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 6:32 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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!
Post #1602119
Posted Monday, August 11, 2014 10:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:37 PM
Points: 2,413, Visits: 6,687
Quick thought, remove the GO as it not an TSQL command/operator.
Post #1602144
Posted Tuesday, August 12, 2014 1:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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.
Post #1602165
Posted Tuesday, August 12, 2014 6:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:37 PM
Points: 2,413, Visits: 6,687
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
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);

Post #1602237
Posted Tuesday, August 12, 2014 7:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 2,063, Visits: 3,592
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
Post #1602282
Posted Tuesday, August 12, 2014 8:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,923, Visits: 32,313
can you check the underlying tables for triggers? i'm thinking maybe a trigger is raising an error, maybe using dynamic SQL?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1602301
Posted Wednesday, August 13, 2014 10:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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!
Post #1602882
Posted Tuesday, August 26, 2014 1:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 17, 2014 5:02 PM
Points: 485, Visits: 1,368
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!
Post #1607361
Posted Tuesday, August 26, 2014 3:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 2,063, Visits: 3,592
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
Post #1607380
Posted Tuesday, August 26, 2014 3:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:43 PM
Points: 40,411, Visits: 36,861
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 2008, MVP
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

Post #1607382
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse