How to set the server name using SMO?

Question

0.00/5 (No votes)

See more:

I have just started using SMO.

The full name of the MSSQL server on my PC is <machineident>\SQLExpress.

In the project I am working on:

Dim srv As New Microsoft.SqlServer.Management.Smo.Server

When running the code using the above, srv.name only returns the <machineident> portion of the name. It ignores the "\SQLExpress" portion of the name.

As a result of misidentifying the server, subsequent SMO calls do not work correctly. For example:

Dim db As Database = srv.Databases(mDataBaseName)

throws an error.

What I have tried:

1. Searched for reporting of this issue.
2. Looked to the list of methods in .Server object to see if there was a method to correctly set the server.

Posted 30-Jan-21 3:41am

Kevin Brady

Updated 30-Jan-21 5:22am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

FranzBe · Accepted Answer · 2021-01-30T05:22:00

Solution 1

The

\SQLExpress

part of the name is the instance the SQL Server runs on. You will get it with

srv.InstanceName

here ist the microsoft doc for this property (well, not that verbose and not very helpful) perhaps this getting started might help you a bit.

Here is a sample that worked for me

VB

Dim serverName As String = "yourMachine"
Dim instanceName As String = "yourInstance"
Dim srv As New Microsoft.SqlServer.Management.Smo.Server(serverName + "\" + instanceName)
Dim serverNameFromSMO = srv.Name
Dim instanceNameFromSMO = srv.InstanceName

Posted 30-Jan-21 5:22am

FranzBe

Updated 30-Jan-21 8:37am

v2

Comments

Kevin Brady 30-Jan-21 12:51pm

When I try to get the InstanceName, an error is thrown. "Failed to connect to server"

It seems that SMO is not correctly finding the SQL server.

FranzBe 30-Jan-21 14:40pm

Hi Kevin, it is difficult so see what your problem is from the one line of code you are posting. I added some lines to the solution that are working on my system. In the getting started link I mentioned there are samples that show you how to connect to a SQL Server (in c# but that might still be helpful). You should use 'Improve Question' and post the relevant part of your code.

Kevin Brady 31-Jan-21 9:59am

Setting the full name of the dataserver as you detailed works. Thank you very much.
To do this, I had to add a reference to Microsoft.SqlServer.ConnectionInfo.