How to get sql server name with a vb-script?

Is there anyone who knows how to get sql server name using a vb-script?

I have tryed this:

bKey = WshShell.RegRead("HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL")

But it cant open it for read.

Someone who knows a better way?

Regards
SW-engineer

I have a script in vb .net but it doesn't target your request

Imports Microsoft.SqlServer.Management.Smo

Dim dt As DataTable

dt = SmoApplication.EnumAvailableSqlServers

For Each dr As DataRow In dt.Rows

MsgBox(dr(0).ToString)

Next

|||You don't specify which machine name you are looking for, but, if you want the name where the package is actually running you can use the SSIS system varaiable MachineName|||

I am not looking for the machine name! I am looking for the name of the sql-server (mssqlserver or sqlexpress)

I need the name when my script creating a database.

What is the "SSIS system varaiable MachineName "?

How work this script in vb.net versus usual vb-script?

Imports Microsoft.SqlServer.Management.Smo

Dim dt As DataTable

dt = SmoApplication.EnumAvailableSqlServers

For Each dr As DataRow In dt.Rows

MsgBox(dr(0).ToString)

Next

|||

That's not the name of the sql-server it's the product version. You need to know whether you are connection to Sql Server of Sql Express? Or are you thinking of the instance name in the case where you have both running on the same machine?

What exactly are you trying to accomplish? With more information we may be able to answer your question.

The system variable MachineName is easy to find. Double-Click a DerivedColumn Shape, and look at the Variables folder in the top left window.

|||

You want the SQL Server instance name you say, not the machine name. Ok, slight problem though since SSIS is not part of the SQL Server instance, so this is a moot point, there is no SQL Server instance associated with SSIS. I could have 10 instances of SQL on a box, but this still has no relationship to SSIS.

SSIS is really just an object model, a set of client-side libraries if you like. There is a SSIS service, but this is not really required, and again has no relationship to any SQL Server instances that may, or may not be installed on the machine as well.

|||

"You need to know whether you are connection to Sql Server of Sql Express? " Yes!

My application can be installed on a machine with mssqlserver or sqlexpress. I need the name when i my script is creating my database.

Here is the code: sqlcmd -S " & WshNetwork.ComputerName & "\sqlexpress -E -i dropp2.sql -i CreateP2DB.sql -i P2T.SQL

As you se here, the name of the sqlserver (here sqlexpress) is hardcoded. I dont want to have the name hardcoded because the machine maybe have mssqlserver installed instead of sqlexpress. I wolud like to read from the machine wich server instance is installed and then choose one of them.

Hope I have made myself clear?

|||

What do you expect to happen if there is an instance of SQLExpress and an instance of SQL Server?

What do you expect to happen if there is more than one instance of SQL Server?

|||

If mssqlserver exist i will choose that server, if only sqlexpress exist i choose that server.

Look here, I would do something like this:

IF(sqlservernnqme == mssqlserver)
...
sqlcmd -S " & WshNetwork.ComputerName & "\mssqlserver -E -i dropp2.sql -i CreateP2DB.sql -i P2T.SQL

else

sqlcmd -S " & WshNetwork.ComputerName & "\sqlexpress -E -i dropp2.sql -i CreateP2DB.sql -i P2T.SQL

|||

You are missing the point I am trying to make. Your simple logic of use SQL but if it is not there use express, will only work in very simple scenario. It may be the most common, but you should not rely on it.

So choosing SQL Server or SQL Express, it is not an either or situation. You can have BOTH SQL Server and SQL Express on the same box. You can have up 50 instances of SQL, be that SQL Server or SQL Express on one machine.

Quite often you can have instances you do not even realise are there as some products install a black box instance that you never see, but any code method will enumerate this instance.

|||

Ok I know what you mean!

My plan is to use either mssqlserver or sqlexpress. If not any of these to instances exist my install project will install sqlexpress and then use sqlexpress.

Is this a bad way to this?

|||

I think you should ask the user. Personally I like to have control of these things. I may have a SQL Server installed, but for a specific app, which I may not want mixed up at all with yours. Similarly I may have this with SQL Express. Detecting what is there and making some recommendations would be good, but I'd always want the choice. I maybe overly pedantic since I obviously I explicitly use SQL.

You could perhaps take the approach of just installing your own named instance of SQLExpress. You can have multiple instances of express if you want, just call yours something other than "SQLExpress" the default name.

|||

That was god arguments!

This application is only installed at computers with me as admin, so I decide which instance the application should use :)

So are there a way to find out the name of the instance?

|||

Try these-

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=337144&SiteId=1

http://www.sqlserver2005.de/articles/1/

|||

Is it not possible to get the name easily with a vb-script?

For example:

to get the windows\system32 directory I just use:

fso.GetSpecialFolder(1)

Is there nothing like this, fso.Getsqlservername(...)

I have been surfing for this many hours now, puh!