Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using text file to query SQL2005 db using vb.net

Posted on 2007-03-29
4
Medium Priority
?
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

618 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