Running sql script from Setup deployment .NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Running sql script from Setup deployment .NET

Author

Topic

sudhapec
Starting Member

4 Posts

Posted - 2004-04-21 : 09:26:34

Friends

I want to run sql script for creating of database which
contains tables and stored procedures by setup and deployment project
in VS.NET. I have created customaction and passing the string
containing script to the ExecuteNonQuery() procedure to run the script

The problem is where ever GO statement is there in the script it
is giving problem. I read somewhere that

" Your script must not include any go statements. These are used only by Query Analyzer or the osql.exe utility. If they are present in your script when using it with ADO.NET (as in this demo), then the ExecuteNonQuery will fail."

If i remove Go from the script then it is giving exception again
telling that "Incorrect syntax near the keyword Procedure"
where ever create Procedure statement is there

Please help me in this regard.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-21 : 19:47:31

When you run a script inside Query Analyzer, it uses GO to split a script up into smaller batches.

You can do the same thing in your VB.NET code. Read the script file, then break it up using GO and run each part separately.

Here is a bit of C# code that will return an array of batches, I'm sure you know how to convert it to VB.NET.



private string[] ParseSqlString(string sql)
{
	string[] batches;

	string pattern = @"\nGO\s*";

	batches = Regex.Split(sql, pattern);

	return batches;

}

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-04-21 : 19:55:02

You can use osql which execute files and process the "GO" keyword.. the Process class in the Diagnostic namespace makes this a trivial exercise....

DavidM

"If you are not my family or friend, then I will discriminate against you"

sudhapec
Starting Member

4 Posts

Posted - 2004-04-22 : 03:37:45

Sir,

If i use OSQL.exe how can i know the path where OSQL.exe is there
also the path may vary from machine to machine based on where the sqlserver is insallled
or it must be there in the path environment variable of DOS ( which may not be case always)
Please reply to this query

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-22 : 03:45:57

OSQL is in the path on a standard install. Or you can do it my way

Damian

sudhapec
Starting Member

4 Posts

Posted - 2004-04-23 : 04:32:46

Thank you very much Byrmol and Merkin
I have used OSQL for creating the database
and it is working fine

Wishing you all the best
Keep in touch

sudhakar.kasina@tcs.com
Tata Consultancy Services, mumbai

sudhapec
Starting Member

4 Posts

Posted - 2004-04-30 : 05:35:52

we can invoke OSQL command from your vb.net code
you need not mention the path for osql.exe for this.

// file containing your script
string strFilepath = "c:\myfolder\myscript.sql";

// append double codes to string at both ends
string strFilepathWithQuotes = "\"" + strFilepath + "\"";

// form argument string for osql
string strOsqlArg = string.Format("-S {0} -U {1} -P {2} -i {3} ,strDBSource,strDBUser,strDBPassword,strFilepathWithQuotes );

ProcessStartInfo psi =new ProcessStartInfo("osql.exe ",strOsqlArg);
Process p = Process.Start( psi );
p.WaitForExit();

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources