14

I've been searching for a "definitive" solution to this for a couple of hours now and can't find out exactly what the problem is so am hoping someone can point me in the right direction.

I'm trying to create a script for a database (an MDF file DB) on a remote machine and use it to create the same database locally. I've got Visual Studio 2010 on both machines so used the Database Publishing Wizard to generate the script.

I next created a blank DB in Visual Studio locally (another MDF file DB) and selected to create a new query. I've pasted the copied script in but when I try and run it I get dozens of "incorrect syntax near GO" errors. I'm not sure how many in total as the alert pop-up goes off the bottom of my screen. It would seem to be a lot though.

I trimmed off all but the very top of the script and I still get the same errors with it. It now looks like this:

SET ANSI_NULLS ON

GO

I still get the "incorrect syntax near GO" error with this.

Is there something inherently wrong with the scripts this tool generates or am I doing something wrong with it?

The remote DB is 2008 R2 and my local is just plain 2008, hence why I don't just copy the MDF file across.

If anyone has any suggestions, they would be most welcome.

Kevin Wilson
  • 7,753
  • 8
  • 35
  • 39

2 Answers2

19

From the docs:

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

Either use such a tool to execute the script or omit the GO commands.

Hans Passant
  • 922,412
  • 146
  • 1,693
  • 2,536
  • 4
    Thanks for the info. I should probably have guessed it'd be something like that but I think I was stuck in the mindset that Microsoft wouldn't create a tool that generates plain text scripts that can't then be run as SQL scripts. Ah, Microsoft... – Kevin Wilson Nov 22 '10 at 23:30
  • The real irony is that VS2010's Database projects *do* support "GO" as the batch separator -- it's even in the Options dialog. Pretty sloppy of them, if you ask me. – McGuireV10 Jul 07 '12 at 14:48
  • Here I found a nice approach to a better understanding of the different usages of the GO keyword, I think it's worthwile to give it a look: [Fun with GO](http://beyondrelational.com/modules/2/blogs/70/posts/10842/fun-with-go.aspx). – Leonardo Allievi Nov 01 '12 at 14:57
8

I ran into the same problem recently and found the right answer to it (I think).

If you create a query in some random editor other than SQL Server Management Studio Code editor at the end of a line you may get a CARRIAGE RETURN (\r) character instead of a CARRIAGE RETURN + LINE FEED (\r\n). And even if you hit the DEL key at the end of the line and then Enter it doesn't change.

Use Notepad++ to replace CR (\r) for CRLF (\r\n) - keep the order, it's important!

Leonardo Allievi
  • 181
  • 1
  • 5
  • +1. Wow. I spent 30 minutes screwing around with the queries. Thanks for the info! – gh9 May 14 '13 at 20:13