August 13, 2012 at 2:16 pm
I've created a number of scripts to create stored procedures, one per script file. I'm aware that I can use sqlcmd to execute these scripts with the :r feature. However, I'm having a few issues and I want to ask some questions.
First, in my research on how to do this I came across this article:
This article makes a statement that a USE statement needs to be put at the top of the script because the context of the sqlcmd will be "master." I do not want to do this if I don't have to because our development, test, and production dbs each have a different name (not my idea). Part of my scripts are running successfully and putting the sprocs into the right database (ie, not in master as the above statement implies), so I want to confirm what the author meant by that statement. I read it to say you cannot put a USE at the beginning of the sqlcmd script (which calls the sql scripts), which is what I have done.
A second question I have is regarding my view creation scripts within this same sqlcmd script. I'm getting errors ("Incorrect syntax near the keyword 'IF'.", for example) when I execute this part of the code. Each of these sql scripts do create their respective views successfully when executed on their own, so I'm wondering what the problem is here. I'll post my main script and one of the view creation scripts below for your review...
Thanks in advance for any help...
:On Error exit
PRINT 'Executing Startup Script...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_StartupScript.sql
PRINT 'Creating Views...'
PRINT 'EC_vwMaxDepID...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_vwMaxDepID.sql
:r d:\SQLDATA\Scripts\EssentialClient\EC_vwLastDepData.sql
:r d:\SQLDATA\Scripts\EssentialClient\EC_vwLastEmpData.sql
:r d:\SQLDATA\Scripts\EssentialClient\EC_vwMaxPayRollCert.sql
:r d:\SQLDATA\Scripts\EssentialClient\EC_vwMcDonaldGroups.sql
PRINT 'Creating Stored Procedures...'
PRINT 'GetFirstDayOfMonth...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_ufn_GetFirstDayOfMonth.sql
--:r d:\SQLDATA\Scripts\EssentialClient\EC_ufn_PADL.sql
PRINT 'EC_uspAdminEmailListAll...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspAdminEmailListAll.sql
PRINT 'EC_uspAdminEmailListPAI...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspAdminEmailListPAI.sql
PRINT 'EC_uspAuditSTD...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspAuditSTD.sql
PRINT 'EC_uspClientList...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspClientList.sql
PRINT 'EC_uspCltLocations...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspCltLocations.sql
PRINT 'EC_uspDepsOver17...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspDepsOver17.sql
PRINT 'EC_uspEligEmpCount...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspEligEmpCount.sql
PRINT 'EC_uspEligEmps...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspEligEmps.sql
PRINT 'EC_uspEmpBenefits...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspEmpBenefits.sql
PRINT 'EC_uspEmpsAged65...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspEmpsAged65.sql
PRINT 'EC_uspGetClientListing...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspGetClientListing.sql
PRINT 'EC_uspMcdClasses...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdClasses.sql
PRINT 'EC_uspMcdCountClasses...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdCountClasses.sql
PRINT 'EC_uspMcdEnrollCount...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdEnrollCount.sql
PRINT 'EC_uspMcdEnrolledByStatsCombined...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdEnrolledByStatsCombined.sql
PRINT 'EC_uspMcDonaldsAnnualEnrollment...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcDonaldsAnnualEnrollment.sql
PRINT 'EC_uspMcdPretax...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdPretax.sql
PRINT 'EC_uspMcdSubsidy...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdSubsidy.sql
PRINT 'EC_uspMcdSubsidyByClass...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdSubsidyByClass.sql
PRINT 'EC_uspMcdSubsidyMinimum...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdSubsidyMinimum.sql
PRINT 'EC_uspMcdSubsidyNone...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdSubsidyNone.sql
PRINT 'EC_uspMcdWaitPeriods...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMcdWaitPeriods.sql
PRINT 'EC_uspMisMatchPayFreq...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMisMatchPayFreq.sql
PRINT 'EC_uspMissingCltConfigData...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMissingCltConfigData.sql
PRINT 'EC_uspMonthlyRevenue...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspMonthlyRevenue.sql
PRINT 'EC_uspPayrollCertStatusNoLinks...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspPayrollCertStatusNoLinks.sql
PRINT 'EC_uspPayrollCertStatusWithLinks...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspPayrollCertStatusWithLinks.sql
PRINT 'EC_uspTransSourceList...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspTransSourceList.sql
PRINT 'EC_uspWorksiteProducts...'
:r d:\SQLDATA\Scripts\EssentialClient\EC_uspWorksiteProducts.sql
PRINT 'Stored Procedure Creation is completed.'
GO
Here is code I have that's failing when it's called from the above code to create a view:
IF object_id(N'Reporting.vwMaxDepID', 'V') IS NOT NULL
DROP VIEW Reporting.vwMaxDepID
GO
CREATE VIEW Reporting.vwMaxDepID AS
SELECT DepData.DepID, Max(DepData.AddDate) AS MaxOfAddDate
FROM Master.DepData
GROUP BY DepData.DepID;
August 14, 2012 at 8:14 am
The problem was as simple as not having a 'GO' at the end of each of my view scripts. I guess my template for stored proc creation had a GO at the end so that's why there were no problems with those.
As for the database context, the article was misleading because I did not have to put a USE statement in each script to make sure the objects were created in the right database. Rather, I could put one in my master script and all were created in that database.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply