I'm fairly new to sql and am having a problem with keywords causing havoc in my sql script. I'm trying to execute a list of premade .sql script files in C#. I'm currently reading the file to a string and executing it with command.ExecuteNonQuery(). This works great for most of the scripts, but I'm running into one that inadvertently contains a keyword:
INSERT INTO [thetable]
SELECT '123123', 'abcabc', 'I WANT TO GO TO BED'
UNION ALL
SELECT '123124', 'abcdef', 'SOOO TIRED'
Essentially, when it hits that GO the command fails.
I am in charge of creating these insert files, so if I need to reformat them in some way then that is possible; however, the data in them is non negotiable. Also, since I am loading them from a file with many rows, parameterization to avoid this stuff doesn't seem feasible either.
Any help would be greatly appreciated at this point. Thanks a lot!
EDIT to add info:
To clarify, the actual string is something more like 'ASVFDS4+23eF3da34sddsdf3d3t4g...100charslater...sd5OAyGOsiISIssdsd/sNUIGsdisd354f'. When I try and execute the command, I catch the exception, which says:
"Unclosed quotation mark after character string 'ASVFDS4+23eF3da34sddsdf3d3t4g...100charslater...sd5OAy'
Note that 5OAy is immediately followed by GOsiIS..., which lead me to believe that the GO is actually being read as a command, causing it to expect an end to the string before that command.
Running .NET 3.5
EDIT 2 I also should clarify, I am currently splitting on the actual GO statements and executing the commands individually.
i.e.
USE MyDatabase
GO
INSERT INTO [thetable]
SELECT '123123', 'abcabc', 'I WANT TO GO TO BED'
UNION ALL
SELECT '123124', 'abcdef', 'SOOO TIRED'
UNION ALL
...
SELECT '123189', 'abcabc', 'HAD SOME SLEEP'
GO
splits, so I execute
USE MyDatabase
and
INSERT INTO [thetable]
SELECT '123123', 'abcabc', 'I WANT TO GO TO BED'
UNION ALL
SELECT '123124', 'abcdef', 'SOOO TIRED'
UNION ALL
...
SELECT '123189', 'abcabc', 'HAD SOME SLEEP'
separately. So my issue isn't with actual GO statements, it's with the characters 'GO' appearing in the data string.
ANSWER: The problem is that I've made a terribly dumb mistake. I split on "GO", which splits the command string right in the middle of that parameter where the letters GO appear.
/facepalm
Thanks for all of the help!
GO
is used as a batch separator. Don't think thatcommand.ExecuteNonQuery()
recognizesGO
. You probably have some code in C# that parses your SQL scripts and executes one batch at a time right? There is where you need to look for a way to fix this. If you have coded that yourself and have control over the generation of the script files you can change your code to only trigger a new batch whenGO
is by itself on a single line. – Farrel