SQL Server returns error during SQLCMD that doesn't fail when executed in SQL Management Studio

I've got a sql command file which executes without error when I run it inside Management Studio. However, when I run it using sqlcmd from a command prompt I get the following errors:

Msg 1023, Level 15, State 1, Server SQL1/SQLEXPRESS, Line 13
Invalid parameter 1 specified for datepart.
Msg 1023, Level 15, State 1, Server SQL1/SQLEXPRESS, Line 22
Invalid parameter 1 specified for datepart.

I have no idea why it would work in studio but not from sqlcmd - I've run similar commands without errors. Any ideas?

I'm posting the code for reference....

	(Region, EmployeeID, AsOfDate, Closed, Pending, Active, LateReport, AsOfWeek, EmpWk, YearWk)

-- All cases by region and employee when there is no BudgetID
-- Region set to 'TBD' during insert, to be updated after INSERT

-- CM (either the employee ID or the budget ID if a substitute is filling in on the case)
	case when (proj.budgetid is not null and exists (select 'X' from acm where proj.budgetid = acm.empid)) then proj.budgetid else ACM.EmpID end as CaseMgr, 

-- Record time stamp of the previous week's Sunday for AsOfDate
 as AsOfDate, /*assume job runs on Sunday for previous week*/

-- Accumulate case counts for closed, pending, active, late status
	sum (case when proj.projectother3 like 'clos%' then 1 else 0 end) Closed,
	sum (case when proj.projectother3 like 'pend%' then 1 else 0 end) Pending,
	sum (case when left(proj.projectother3,4) not in ('clos','pend') then 1 else 0 end) Active,
	sum (case when left(proj.projectother3,1) in ('0','1')
			case when left(proj.projectother3,5) < Left(convert(varchar,Getdate()-4,10),5)
				/*compare to 4 days earlier to determine late, gives a grace period to allow for reports that weren't processed until Mon/Tue for previous week */
				then 1 else 0 end
			else 0 
		end) LateReport,

-- Store week as a value (NOTE: this may not be needed for reporting since YearWk contains this data)

) as AsOfWeek,
	/*Using the week number of the beginning of the previous week since the job runs on Sunday of following week */

-- Build Primary Key field using the employeeID and YearWk fields concatenated
	(case when proj.budgetid is null then ACM.EmpID else proj.budgetid end)+ right(str(datepart("year",GetDate()-(datepart("weekday",GetDate()))),4),2) + '-' 
		+ left(convert(char,datepart("week",getdate()
),1),2) as EmpWk,

-- Build YearWk to create unique time records for each week of each year, not just the week)
	right(str(datepart("year",GetDate()-(datepart("weekday",GetDate()))),4),2) + '-' 
		+ left(convert(char,datepart("week",getdate()
),1),2) as YearWk

	left outer join proj on ACM.EmpID = proj.employeeid
WHERE proj.projectstatus = 'Active' and proj.budgetid is null
GROUP BY /*ACM.Region, */ACM.EmpID, proj.budgetid
ORDER BY /*ACM.Region, */ACM.EmpID

Open in new window

Shannon MollenhauerAsked:
Who is Participating?
HumpdyConnect With a Mentor Commented:
try get rid of your quotes in your datepart

Erick37Connect With a Mentor Commented:
Do ont enclose week, year, weekday, etc. in quotes...

Shannon MollenhauerAuthor Commented:
I'm splitting the points since you both posted at nearly the same time. Gee - I can't believe I couldn't find any answers like this with Google. Guess that's why I pay for EE every month! Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.