C# SQL-DMO ExecuteImmediate

Received by email from LineVoltageHalogen [tropicalfruitdrops@.yahoo.com]

> I am trying to run a sql script via SQL-DMO. The script just rebuilds
> some stored procs and I can get it to work, however it always runs against
> the "master" database. Could you show me how to specify which database?
> Here is what my code looks like:
> SQLDMO.SQLServer2 DMOSQLServerName = new SQLDMO.SQLServer2();
> SQLDMO.Database2 DMOPerStoreDbName = new SQLDMO.Database2();
> DMOSQLServerName.Connect(myGetConfigData.OlapServe rName,myGetConfigData.OlapUserLogin,OlapPass);
> DMOPerStoreDbName.Name =
> myConnectionData.OlapDatabaseName.ToString().Trim( );
> if (File.Exists(@."MyScript.sql"))
> {
> SR2=File.OpenText(@."MyScript.sql");
> S2=SR2.ReadLine();
> try
> {
> SQLScript = SR2.ReadToEnd();
> SR2.Close();
> DMOPerStoreDbName.ExecuteImmediate(SQLScript,SQLDM O.SQLDMO_EXEC_TYPE.SQLDMOExec_Default,
> null);
> }
>
> So as you can see the script runs but against the master database, I need
> it to run against a database I specify. Can you help?
>
> TFD

That's because you're setting the database name, instead of getting a
reference to an existing database from the server's Databases collection -
this code works for me:

SQLDMO.SQLServer2 srv = new SQLDMO.SQLServer2();
SQLDMO._Database db = new SQLDMO.Database();

srv.Name = "MyServer";
srv.LoginSecure = true;
srv.Connect(null,null,null);

db = srv.Databases.Item("MyDatabase", null);
db.ExecuteImmediate(" /* SQL or script contents go here */ ",
SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, null);

srv.DisConnect();

SimonThank You.

TFD