Solved

Using text file to query SQL2005 db using vb.net

Posted on 2007-03-29
4
206 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now