Hello,

I'm attempting to deploy an alter procedure script across databases. However, I can't figure out what the problem is with the syntax error.

Thank you for your assistance,
Drumsticks

I have replaced my username and password with dummy credentials in the osql script.

osql -Umyusername -Pmypassword -SCOMPANY1\DB1 -dCCS < "c:\users\Drumsticks\My Documents\Business\compa\alter_pClassEvent.sql"

The result is the following:
1> 2> 3> ... 158> Msg 102, Level 15, State 1, Server COMPANY1\DB1, Line 1
Incorrect syntax near ' '.

Is this error due to something within the sql file or does this indicate something wrong with the actual osql script?

Below is the script contained in the file:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pClassEvent]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--Get Beginning and Ending dates for the entire academic school year.
DECLARE @dtYearStart DATETIME,
@dtYearEnd DATETIME,
@yearID INT,
@dayRotCount INT,
@dayRotStart INT,
@weekDayStart INT,
@weekDayEnd INT,
@getNow DATETIME;

-- ADJUSTABLE NUMBERS --
SET @dayRotStart = 1; -- First Day of School Rotation Day.
SET @weekDayStart = 2; -- Starting Day of week. (ie. 1 = Sunday, 7 = Saturday).
SET @weekDayEnd = 6; -- End Day of week. (ie. 1 = Sunday, 7 = Saturday).
-- /ADJUSTABLE NUMBERS --

-- FIXED NUMBERS --
SET @dayRotCount = 1; -- Default number. This will automatically change and does not need to be adjusted.
-- /FIXED NUMBER --

-- Freeze the time for rendering.
SET @getNow = GETDATE();

-- Truncate classEvent table.
TRUNCATE TABLE tClassEvent

-- Get Academic School Year Dates.
SELECT TOP 1
@dtYearStart = dtAcademicYearStart,
@dtYearEnd = dtAcademicYearEnd,
@yearID = skAcademicYearID
FROM ltAcademicYear
WHERE CONVERT(DATE, @getNow) BETWEEN dtAcademicYearStart AND dtAcademicYearEnd -- Check if current date is between dates.
OR (CONVERT(VARCHAR(4),YEAR(@getNow)) + '-' + CONVERT(VARCHAR(4), YEAR(@getNow) + 1) = academicYear
AND CONVERT(DATE, @getNow) < dtAcademicYearStart) -- Check if the current date is between school years. If so, create next school year.
ORDER BY academicYear;

-- Get the max dayTypeOrder number to loop 1 through the max dayTypeOrder to each school day date.
SELECT @dayRotCount = MAX(dayTypeOrder)
FROM ltSchedule s
INNER JOIN ltBlockType bt
ON bt.skBlockTypeID = s.fkBlockTypeID
AND bt.isActive = 1
INNER JOIN ltAcademicYear ay
ON ay.skAcademicYearID = @yearID
AND ay.skAcademicYearID = bt.fkAcademicYearID;

-- CTEs
WITH getAllClass (fkClassID, fkScheduleID, fkTermID, dayTypeOrder, timeTypeOrder, fkBlockTypeID, isAttendance, dtStartTime, dtEndTime, maxDayRotation, skSessionID, dtStart, dtEnd) AS (
-- Get all active classes for this or upcoming academic school year.
SELECT TOP 99999
ISNULL(sd.fkClassID, 0) AS fkClassID,
sd.fkScheduleID,
c.fkTermID,
s.dayTypeOrder,
s.timeTypeOrder,
s.fkBlockTypeID,
s.isAttendance,
s.dtStartTime,
s.dtEndTime,
ISNULL((SELECT COUNT(DISTINCT s1.dayTypeOrder)
FROM ltSchedule s1
WHERE s1.fkBlockTypeID = s.fkBlockTypeID), 0) AS maxDayRotation,
0 AS skSessionID,
t.dtStart,
t.dtEnd
FROM tClass c
INNER JOIN xtScheduleDays sd
ON sd.fkClassID = c.skClassID
INNER JOIN ltSchedule s
ON s.skScheduleID = sd.fkScheduleID
INNER JOIN ltBlockType bt
ON bt.skBlockTypeID = c.fkBlockTypeID
AND bt.isActive = 1
INNER JOIN ltAcademicYear ay
ON ay.skAcademicYearID = @yearID
AND ay.skAcademicYearID = bt.fkAcademicYearID
INNER JOIN ltTerm t
ON t.skTermID = c.fkTermID
/*WHERE c.isHistory = 0
AND c.isActive = 1
AND c.isDeleted = 0*/
ORDER BY t.dtStart,
s.fkBlockTypeID,
c.fkTermID,
s.dayTypeOrder,
s.timeTypeOrder
),
getAllDate (dayDate, dayNumber) AS (
-- Create all school day dates and associate day rotations to them.
SELECT @dtYearStart AS dayDate,
CASE
WHEN DATEPART(dw, @dtYearStart) BETWEEN @weekDayStart AND @weekDayEnd THEN
@dayRotStart
END AS dayNumber
UNION ALL
SELECT DATEADD(dd, 1, dayDate) AS dayDate,
CASE
WHEN DATEPART(dw, DATEADD(dd, 1, dayDate)) BETWEEN @weekDayStart AND @weekDayEnd AND dayNumber = @dayRotCount THEN
1
WHEN DATEPART(dw, DATEADD(dd, 1, dayDate)) BETWEEN @weekDayStart AND @weekDayEnd THEN
dayNumber + 1
ELSE
dayNumber
END AS dayNumber
FROM getAllDate
WHERE dayDate <= @dtYearEnd
),
getAllExDate (skEventID, exDateStart, exDateEnd, whosNotShowingUp) AS (
-- Get all dates that school is out of session.
SELECT ed.skEventID,
ed.exDateStart,
ed.exDateEnd,
ns.whosNotShowingUp
FROM ltExcludedDates ed
INNER JOIN ltExDateNoShow ns
ON ns.skWhosNoShow = ed.fkWhosNoShow
WHERE ed.exDateStart BETWEEN @dtYearStart AND @dtYearEnd
)
-- Combine all queries to populate the school year for all classes on every school day and insert them into tClassEvent table.
INSERT
INTO tClassEvent (
fkClassID,
fkScheduleID,
dtStartTime,
dtEndTime,
isAttendance
)
SELECT ac.fkClassID,
ac.fkScheduleID,
CONVERT(DATETIME, CONVERT(VARCHAR(10), ad.dayDate, 101) + ' ' + CONVERT(VARCHAR(8), ac.dtStartTime, 108)) AS dtStartTime,
CONVERT(DATETIME, CONVERT(VARCHAR(10), ad.dayDate, 101) + ' ' + CONVERT(VARCHAR(8), ac.dtEndTime, 108)) AS dtEndTime,
ac.isAttendance
FROM getAllClass ac
INNER JOIN getAllDate ad
ON ad.dayNumber = ac.dayTypeOrder
WHERE DATEPART(DW, dayDate) BETWEEN @weekDayStart AND @weekDayEnd
AND NOT EXISTS (SELECT *
FROM getAllExDate aed
WHERE ad.dayDate BETWEEN aed.exDateStart AND aed.exDateEnd)
ORDER BY CONVERT(DATETIME, CONVERT(VARCHAR(10), ad.dayDate, 101) + ' ' + CONVERT(VARCHAR(8), ac.dtStartTime, 108)),
ac.fkClassID
OPTION (maxRecursion 365) -- max is 100 by default.
END