Solved

I received an error using SQL Server 2008. The error is Msg 102, Level 15. Do you know how I can resolve this error?

Posted on 2016-08-21
6
25 Views
Last Modified: 2016-08-21
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$([ImportMonthlyChecksEntity].[dateFile],'mmmm yyyy') AS [dateFile By Month],
ImportMonthlyChecksEntity.branch, Sum(ImportMonthlyChecksEntity.amt2) AS [Sum Of amt2],
Count(*) AS [Count Of ImportMonthlyChecksEntity]
FROM ImportMonthlyChecksEntity
GROUP BY ImportMonthlyChecksEntity.amt1,
Format$([ImportMonthlyChecksEntity].[dateFile],'mmmm yyyy'),
ImportMonthlyChecksEntity.branch,
Year([ImportMonthlyChecksEntity].[dateFile])*12+DatePart('m',[ImportMonthlyChecksEntity].[dateFile])-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
0
Comment
Question by:zimmer9
  • 4
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41764437
For starters, in these situations it would greatly help if you could place your code in a CODE block (comment toolbar), and when an error occurs double-click on the error, watch the cursor jump to the offending line, and tell us what line that is.

Looking..
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41764439
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..
  • 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

Open in new window

0
 

Author Comment

by:zimmer9
ID: 41764440
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'.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:Jim Horn
ID: 41764444
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

Open in new window

0
 

Author Comment

by:zimmer9
ID: 41764446
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.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41764452
nvarchar is a Unicode character.  Think all of those Swedish letters that are not in the English A-Z alphabet.

Sum means you are trying to add a column of numbers.

Obviously you can't sum a bunch of characters.   SUM ( A + Z + WAHOO) = ??

If we're talking the Sum(amt2), and it is nvarchar, the obvious knee-jerk reaction is that if these are numbers (amt would imply amount) where you would want to do math operations, then the column should be some kind of numeric data type and not character data type.  Recommend changing the column data type.

If you can't do that then you could always SUM(CAST(amt2 as decimal(9,2))), but I reeeeeeeeeeeeeeeeeeeeeeeally recommend against that as any value that cannot be converted to a number will throw an error.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now