CODEWITHSUNDEEP
c#sql-serversmosql-scripts
Aditya
Aditya

Reputation: 65

How to run sql script with 'Go' statements from C#

I am trying to run SQL script which contains 'GO' statements as batch separators. I came across Jon Galloway's way to handle this type situation which makes use of SMO(nuget package Microsoft.SQlServer.SQLManagementObjects).

con = new SqlConnection(connectionString);//1
var serverConnection = new ServerConnection(con);//2
var server = new Server(serverConnection);//3
StreamReader streamReader = new StreamReader(@"D:\Scripts\Install-Northwind-Script.sql");//4
string script = streamReader.ReadToEnd();//5
server.ConnectionContext.ExecuteNonQuery(script);//6

The above code is throwing an exception at line 6.

System.IO.FileNotFoundException: 'Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=15.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.'

It is giving FileNotFound exception but it successfully read all the contents of file at line 5. I tried adding the Microsoft.SqlServer.BatchParser reference but could not find one.

Can anyone help me know what am I doing wrong here?

Upvotes: 0

Views: 1133

Answers (1)

Siavash Rostami
Siavash Rostami

Reputation: 1933

This error does not pick at your query but the fact that assemblies for the Sql Server BatchParser are missing. To fix that you have to install

x64\SharedManagementObjects.msi and
x64\SQLSysClrTypes.msi

With smo assemblies installed you should be good to go.
Yet better option is to use this Nuget package. It contains all you need for smo objects version 15.
Command to isntall (just enter this in nuget package manager console)

Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 150.18208.0

Upvotes: 3

Related Questions