-
Using osql, I'm getting a syntax error but don't know why
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
-
Try using -Q instead of < for script. The error may be on line 158 of the script.
-
Thank you for your reply, skhanal.
As soon as I typed this question, I found the answer about 5 minutes later, but I was not able to revoke the post because it was marked for approval status.
However, in case your curious, the answer to my issue was changing the encoding type of the sql file executed in the osql command. I changed the encoding type of the sql file to "Western European (Windows) - Codepage 1252" and it then the osql fired perfectly.
Below is the link where I stumbled upon the answer.
http://kiquenet.wordpress.com/2010/0...encoding-utf8/
Thanks again!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|