SQL Error - can't see anything wrong!

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
ScuzzyJoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterSenior Software DeveloperCommented:
I don't think you want to be using a semicolon and then a GO statement. You should choose one or the other.
Jim HornMicrosoft SQL Server Data DudeCommented:
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.
ScuzzyJoAuthor Commented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ScuzzyJoAuthor Commented:
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
Jim HornMicrosoft SQL Server Data DudeCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScuzzyJoAuthor Commented:
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.
Jim HornMicrosoft SQL Server Data DudeCommented:
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
ScuzzyJoAuthor Commented:
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.
Jim HornMicrosoft SQL Server Data DudeCommented:
No prob.  Dynamic SQL is levels harder to build and support then regular SQL, and should be avoided if at all possible.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
ScuzzyJoAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Just wanted to try to find a logical explanation for the error message.
Looks like you just found another Microsoft "feature" ;)
Cheers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.