zimmer9
asked on
I received an error using SQL Server 2008. The error is Msg 102, Level 15. Do you know how I can resolve this error?
I try and execute the following stored procedure and receive the following error using SQL Server 2008.
The table layout follows.
Do you know how I can resolve this error?
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
SELECT DISTINCTROW ImportMonthlyChecksEntity. amt1,
Format$([ImportMonthlyChec ksEntity]. [dateFile] ,'mmmm yyyy') AS [dateFile By Month],
ImportMonthlyChecksEntity. branch, Sum(ImportMonthlyChecksEnt ity.amt2) AS [Sum Of amt2],
Count(*) AS [Count Of ImportMonthlyChecksEntity]
FROM ImportMonthlyChecksEntity
GROUP BY ImportMonthlyChecksEntity. amt1,
Format$([ImportMonthlyChec ksEntity]. [dateFile] ,'mmmm yyyy'),
ImportMonthlyChecksEntity. branch,
Year([ImportMonthlyChecksE ntity].[da teFile])*1 2+DatePart ('m',[Impo rtMonthlyC hecksEntit y].[dateFi le])-1;
ImportMonthlyChecksEntity
bankname varchar(53) allow nulls
banknumber varchar(19) allow nulls
amt1 varchar(6) allow nulls
amt2 nvarchar(MAX) allow nulls
datefile datetime allow nulls
dateimported datetime allow nulls
importedby varchar(50) allow nulls
branch varchar(50) allow nulls
The table layout follows.
Do you know how I can resolve this error?
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
SELECT DISTINCTROW ImportMonthlyChecksEntity.
Format$([ImportMonthlyChec
ImportMonthlyChecksEntity.
Count(*) AS [Count Of ImportMonthlyChecksEntity]
FROM ImportMonthlyChecksEntity
GROUP BY ImportMonthlyChecksEntity.
Format$([ImportMonthlyChec
ImportMonthlyChecksEntity.
Year([ImportMonthlyChecksE
ImportMonthlyChecksEntity
bankname varchar(53) allow nulls
banknumber varchar(19) allow nulls
amt1 varchar(6) allow nulls
amt2 nvarchar(MAX) allow nulls
datefile datetime allow nulls
dateimported datetime allow nulls
importedby varchar(50) allow nulls
branch varchar(50) allow nulls
By any chance did you copy this from Microsoft Access? If so check out Migrating your Access Queries to SQL Server Transact-SQL, which is a handy conversion article I wrote.
Things that are errors..
Things that are not errors and highly recommended
With all of the above..
Things that are errors..
- DISTINCTROW is not a valid T-SQL function in 2008. DISTINCT is.
- FORMAT$ is not a valid T-SQL function in 2008.
- DATEPART('m' remove the single quote marks
Things that are not errors and highly recommended
- Since this query only has one table name you can lose the tablename. reference in all of the column names.
- Using DISTINCT and GROUP BY serve the same purpose and is redundant. Recommend losing the DISTINCT.
- [dateFile By Month] --> Lose the spaces in column names, which force the developer to use square brackets [ ] to surround it, which is one thing to potentially forget and throw an error.
With all of the above..
SELECT DISTINCT
amt1,
DATENAME(month, datefile) + ' ' + YEAR(dateFile as char(4)) as dateFile_by_month,
branch,
Sum(amt2) AS count_amt2,
Count(*) AS count_all
FROM ImportMonthlyChecksEntity
ASKER
SELECT DISTINCT
amt1,
DATENAME(month, datefile) + ' ' + YEAR(dateFile as char(4)) as dateFile_by_month,
branch,
Sum(amt2) AS count_amt2,
Count(*) AS count_all
FROM ImportMonthlyChecksEntity
-------------------------- ---------- ---------- ---
Yes I did copy this from Microsoft Access and I will review your article St. Jimbo. :)
Msg 156, Level 15, State 10, Line 3
Incorrect syntax near the keyword 'AS'.
amt1,
DATENAME(month, datefile) + ' ' + YEAR(dateFile as char(4)) as dateFile_by_month,
branch,
Sum(amt2) AS count_amt2,
Count(*) AS count_all
FROM ImportMonthlyChecksEntity
--------------------------
Yes I did copy this from Microsoft Access and I will review your article St. Jimbo. :)
Msg 156, Level 15, State 10, Line 3
Incorrect syntax near the keyword 'AS'.
Typo, my bad, did not include the CAST( before the year.
SELECT DISTINCT
amt1,
DATENAME(month, datefile) + ' ' + CAST(YEAR(dateFile) as char(4)) as dateFile_by_month,
branch,
Sum(amt2) AS count_amt2,
Count(*) AS count_all
FROM ImportMonthlyChecksEntity
ASKER
Last follow up question Jim.
Do you know the best way to resolve the following error from my table structure:
Msg 8117, Level 16, State 1, Line 5
Operand data type nvarchar(max) is invalid for sum operator.
Do you know the best way to resolve the following error from my table structure:
Msg 8117, Level 16, State 1, Line 5
Operand data type nvarchar(max) is invalid for sum operator.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Looking..