.NET SMO and Connecting

Question

According to the .NET documentation on SMO (SQL Server Management Objects) you can create a connection using an ADO.NET connection see (https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.common.serverconnection?f1url=%3FappId%3DDev16IDEF1%26l%3DEN-US%26k%3Dk(Microsoft.SqlServer.Management.Common.ServerConnection);k(TargetFrameworkMoniker-.NETFramework,Version%253Dv4.8);k(DevLang-VB)%26rd%3Dtrue&view=sql-smo-160)

However when I do it I get an error. Is the documentation wrong?

Code...
Dim cnADO As New SqlClient.SqlConnection(sPkgConn)
Dim cnSMO As New Microsoft.SqlServer.Management.Common.ServerConnection(cnADO)

sPkgConn is a Connection string

My application is already running using a connection string to query data etc. I am trying to connect with SMO to get information about Table, View, and stored procedures to manage them. I do not want to have to re-prompt the user of the old style Server, UserId, Password information.

Answer 1

Something here may help.
https://social.technet.microsoft.com/wiki/contents/articles/52098.smosql-server-vb-net-part-1.aspx

I'd also try asking for help here in dedicated forum.
https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vbgeneral

--please don't forget to Accept as answer if the reply is helpful--

Answer 2

You won't use SqlClient.SqlConnection as the SMO connection.

You will need to use Microsoft.SqlServer.Management.Smo.Server and pass the ServerConnection object you created in.

Then you can use server.Databases["databasename"].Tables["tablename","schemaname"]

Answer 3

Hi @Lee Taylor

Quote from this doc.: connecting-to-an-instance-of-sql-server-by-using-sql-server-authentication-in-visual-c

When you connect to an instance of SQL Server by using SQL Server Authentication, you must specify the authentication type. This example demonstrates the alternative method of declaring a ServerConnection object variable, which enables the connection information to be reused.

The example is Visual C# .NET code that demonstrates how to connect to the remote and vPassword contain the logon and password.

// compile with:     
// /r:Microsoft.SqlServer.Smo.dll    
// /r:Microsoft.SqlServer.ConnectionInfo.dll    
// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll     
    
using System;    
using Microsoft.SqlServer.Management.Smo;    
using Microsoft.SqlServer.Management.Common;    
    
public class A {    
   public static void Main() {     
      String sqlServerLogin = "user_id";    
      String password = "pwd";    
      String instanceName = "instance_name";    
      String remoteSvrName = "remote_server_name";    
    
      // Connecting to an instance of SQL Server using SQL Server Authentication    
      Server srv1 = new Server();   // connects to default instance    
      srv1.ConnectionContext.LoginSecure = false;   // set to true for Windows Authentication    
      srv1.ConnectionContext.Login = sqlServerLogin;    
      srv1.ConnectionContext.Password = password;    
      Console.WriteLine(srv1.Information.Version);   // connection is established    
    
      // Connecting to a named instance of SQL Server with SQL Server Authentication using ServerConnection    
      ServerConnection srvConn = new ServerConnection();    
      srvConn.ServerInstance = @".\" + instanceName;   // connects to named instance    
      srvConn.LoginSecure = false;   // set to true for Windows Authentication    
      srvConn.Login = sqlServerLogin;    
      srvConn.Password = password;    
      Server srv2 = new Server(srvConn);    
      Console.WriteLine(srv2.Information.Version);   // connection is established    
    
      // For remote connection, remote server name / ServerInstance needs to be specified    
      ServerConnection srvConn2 = new ServerConnection(remoteSvrName);    
      srvConn2.LoginSecure = false;    
      srvConn2.Login = sqlServerLogin;    
      srvConn2.Password = password;    
      Server srv3 = new Server(srvConn2);    
      Console.WriteLine(srv3.Information.Version);   // connection is established    
   }    
}  

More examples: connecting-to-an-instance-of-sql-server

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

Hi @Lee Taylor ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.