Solved

SQL Error - can't see anything wrong!

Posted on 2016-09-15
12
33 Views
Last Modified: 2016-09-16
Hi

I hope someone can help.  I'm getting an error on the following code:

SELECT DISTINCT old_ssn
INTO LFRD_Old_SSN_Cnt
FROM LFRD;
--
--
ALTER TABLE LFRD_Old_SSN_Cnt ADD SSN_SSNs nvarchar (255);
GO
--
--
UPDATE A SET SSN_SSNs = B.SSN
FROM LFRD_Old_SSN_Cnt A LEFT OUTER JOIN LFRD B ON A.old_ssn = B.old_ssn;
--
--

The message is:

Msg 102, Level 15, State 1, Line 83
Incorrect syntax near ')'.

Line 83 is just after GO on the ALTER TABLE bit.

I can't for the life of me see what's wrong with the code.  Please help.

Thanks
Sarah
0
Comment
Question by:ScuzzyJo
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41799943
I don't think you want to be using a semicolon and then a GO statement. You should choose one or the other.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41799947
Looks correct to me.  Wild guesses..
  • Is the table name spelled correctly?
  • Do you need to add the schema name to the ALTER TABLE?
  • Is the column added already in the table?
  • Might need a GO between the SELECT .. INTO that creates the table and the ALTER TABLE that changes it.
0
 

Author Comment

by:ScuzzyJo
ID: 41799970
Hi Russ

Thanks, but I don't think that's the problem.  I always write it like that and I've never had a problem with it before.

Thanks
Sarah
0
 

Author Comment

by:ScuzzyJo
ID: 41799988
Hi Jim

It's driving me mad!

The table name must be correct as I copied and pasted it from the line where the table was created to be sure.

I'm not sure why I would need to add the schema name or even what that is!  Is it the bit with dbo in front of it?  I've tried dragging that across but it didn't make any difference.

The table is dropped and is then added as part of the SELECT DISTINCT  bit above.

I tried adding a GO, but it didn't make any difference.

It's very strange and I'm at a complete loss!

Thanks
Sarah
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41799992
Post the entire SQL, with what was dropped out with the ..., into a code block.
Then run, note the line that throws the error, and reference it in the code pasted in the code block.

Might be something else that is just cascading down to the ).  Microsoft error messages are not abundantly intuitive to what the actual error is.
0
 

Author Comment

by:ScuzzyJo
ID: 41800003
Thank you.  I've found it!  It was bracket in some dynamic SQL on Line 131!!!!!!!

I cut and pasted everything after that line into Notepad and then ran it.  It was fine, so I gradually added bits back in until it fell over again and I found it.

You're right about the error messages not being very helpful.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 41800011
Yeah alot of times when you get an ambiguous error message you have to start there and read up to flush out the actual error.

And if you're working in dynamic SQL then you're playing with fire.  It's often helpful to add a line SELECT @sql (or whatever your dynamic SQL variable is) to your script just to review and make sure it is correct.

Thanks for the accept, good luck with your project.  -Jim
0
 

Author Comment

by:ScuzzyJo
ID: 41800017
Thanks, Jim.  I'm pretty new to dynamic SQL, so there will no doubt be a question on that coming up either this afternoon or maybe in the morning as I'm in the UK, so finish work in about half an hour.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41800033
No prob.  Dynamic SQL is levels harder to build and support then regular SQL, and should be avoided if at all possible.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41801092
I've found it!  It was bracket in some dynamic SQL on Line 131!!!!!!!
Would be possible the error referencing the Line 83 of the dynamic SQL? Or isn't that big dynamic SQL?
0
 

Author Comment

by:ScuzzyJo
ID: 41801255
Hi Vitor

The dynamic SQL bit is tiny and starts way lower down.  Line 83 seems to have nothing to do with that bit.

Thanks
Sarah
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41801289
Ok. Just wanted to try to find a logical explanation for the error message.
Looks like you just found another Microsoft "feature" ;)
Cheers
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 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

11 Experts available now in Live!

Get 1:1 Help Now