Using text file to query SQL2005 db using vb.net

I'm writing an app in vb.net to read a text file containing an sql query.

I've tried accessing it directly

Public sqlfirstDbScript As String = "C:\scripts\First_db_script.sql"
            Dim commandFirstdbscript As New SqlClient.SqlCommand("EXEC sp_configure 'clr enabled', 1 RECONFIGURE", sqlconnection)
            commandFirstdbscript.CommandType = CommandType.Text
            commandFirstdbscript.CommandText = sqlfirstDbScript
            commandFirstdbscript.ExecuteNonQuery()

But I get error in syntax near "\"
I have also tried using a StreamReader

        Dim StreamfirstText As StreamReader
        StreamfirstText = File.OpenText(sqlfirstDbScript)
        'sqlstringStreamfirstText.Read()
        Dim sqlstring As String = StreamfirstText.ReadToEnd

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

nmcdermaidCommented:
Is it a VB.Net error or a SQL error. What happens if you run the text query directtly in SQL Serve Management Studio, does it work?

You know you are opening yourself up for whats generally termed an 'injection' attack here. Someone could put 'TRUNCATE TABLE ImportantTable' in the text file and run it.
0
mrwarejrAuthor Commented:
The query runs fine from QueryAnalyzer.  I understand about the 'injection' attack.  Here's the process.

Database Primary-A is the main db for a web app.
Nightly a database backup set NewDb-B.bak  the transferred to the server containing updated data for Primary-A. NewDb-B is from another Corporate app. There are to sql scripts that must be run to create  storedprocs needed by the application that will actually process and update the records. These must added nightly since the NewDB.bak that is transferred doesn't contin them.

They are pretty long so I didn't want to tspend the time of breaking down each line to put it into a string.
0
mrwarejrAuthor Commented:
I should have added. The app I'm currently will
1. unzip the downloaded file  <--works
2. Restore it to the SqlServer instance  <--works
3. Add a user  <--works
4. Run 2 sql scripts to create sp's <---Busted part
5. Run update program
0
nmcdermaidCommented:
Its easier to use SQLCMD to run a SQL script rather then writing a special VB.Net app to run it. Why don't you try that. Look up SQLCMD in the SQL Server help. If it doesn't make sense let me know.
0

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
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
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.