Trouble Running Sql Script from Code

[perlmunger]

I have created a deployment application that has the user provide db credentials. It then creates the database and then runs a .sql script to create all of the db objects. The script runs fine in Query Analyzer, but won't work in my deployment project. I get the following error message: 'CREATE VIEW' must be the first statement in a query batch. 'CREATE VIEW' must be the first statement in a query batch. 'CREATE VIEW' must be the first statement in a query batch. 'CREATE VIEW' must be the first statement in a query batch. 'CREATE VIEW' must be the first statement in a query batch. What does this mean and why do I only get it in my deployment app and not in query analyzer? Thanks. -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall

[Jeff Varszegi]

That's a great signature quote. Try putting GO on its own line after each CREATE VIEW. Did this solve your problem? How is this script being run, anyway? Thank you. Jeff Varszegi

[perlmunger]

From what I understand, "GO" isn't part of the T-SQL language. Frankly, if that's the case, I'm not sure what in the world it's there for except as a delimiter (maybe?). I've tried to run the script with GO(s) in and I get a syntax error at the first one it comes to. I then just ignore all of the GO(s) when I'm reading the script and I get the error I mentioned in my first message. Thanks for your help. -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall

[Jeff Varszegi]

Well, part of the problem is that I don't know what you mean by "running a script". I mean, are you using osql or something? If not, you will probably have to separate each one of the CREATE statements, etc. into a separate call; each one has to be in a separate "batch". In some contexts, you separate batches with GO; in others, you just execute them separately. Are you just executing the .sql file using a shell command or something? Try separating each CREATE statement into a separate file-- what happens? Thank you. Jeff Varszegi

[perlmunger]

I see what you are saying. What I am doing is opening a .sql file that I created using the "Generate SQL Script" command from Enterprise Manager. I script all of it to a single file and then read the entire contents into a string in my application. I am then setting the command text of my sql command to that string and running ExecuteNonQuery. How would I separate the create view calls out? Is there any way other than actually parsing the script or puting each create view into its own file? Thanks. -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall

[Jeff Varszegi]

Well, you could always copy the statements into your source code. I can't think of any other way. It's an interesting situation. Regards, Jeff Varszegi

[perlmunger]

Your suggestion has helped me to figure it out. Instead of stripping out the GO statements, I am using them as a delimiter for each statement. This appears to be working, however, I am finding that some of the stored procedures I created had some improper syntax that (for some stupid only-microsoft-knows-why reason) Enterprise Manager had allowed. Now that I'm running it using C#, however, it is holding me to a more perfect syntax. Now that I've fixed those things in my script, everything seems to be working fine. I really appreciate your help. It was exactly what I needed. I'm doing this for an installer project. I'm just amazed that nobody else (at least that I can find with google) has ever done this before. It's crazy!! ;-) Thanks a heap. -Matt ------------------------------------------ The 3 great virtues of a programmer: Laziness, Impatience, and Hubris. --Larry Wall