Solved

Using text file to query SQL2005 db using vb.net

Posted on 2007-03-29
4
208 Views
Last Modified: 2010-08-05
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

0
Comment
Question by:mrwarejr
  • 2
  • 2
4 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 18821031
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
 

Author Comment

by:mrwarejr
ID: 18821215
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
 

Author Comment

by:mrwarejr
ID: 18821249
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 50 total points
ID: 18827781
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question