DB connection query via notepad
Hello all, I have a very big query to be fetched from a notepad/sql file. Instead of passing an inline DB query like: strSQLQuery = "INSERT INTO " &strTblNM& " (" &strCols& ") VALUES (" &strVals& ...
Forum Discussion
Is the query in your notepad file a single line? I mean, it may seem that it is when viewing it, but I'd be curious as to what the value of "s" is at the point in your script when you exit your while loop. Do you have the full query or only part?
Additionally, if your SQL query contains special characters like quotes, slashes, etc, those could be misinterpreted when passing to the query.
Suggestion: Rather than reading the stuff from a text file into a variable and executing it, have you considered opening a shell and then executing the sqlcmd command line syntax? Something on the lines of
Sys.OleObject('WScript.Shell').Exec('sqlcmd -S MyServer -i C:\MySQLScript.sql')
Seems that this would be a lot easier than trying to read the file into memory.
