Solved

Using text file to query SQL2005 db using vb.net

Posted on 2007-03-29
4
212 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 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

732 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