[RESOLVED] Help in getting list of MS SQL servers and Database

[daimous]

hi Guys! Can you help me on how can to get a list of MS SQL servers, regardless of version, present in my network or computer and the available database in that particular Server? Thanks in advance! Oh! I forgot SQLDMO is not working to me it does not get any list of available ms sql server, though im sure that some ms sql server is present in our network.

[RobDog888]

Although thi sis VB its still the same class in .NET.

http://vbforums.com/showpost.php?p=1928800&postcount=2

[daimous]

Thanks for that RobDog...but SQLDMO is not working to me. Is there any posible way aside from using SQLDMO?

[RobDog888]

You need to add a reference or import the namespace. I cant test it yet as I dont have sql installed.

[daimous]

Already done that..Actually i used SQLDMO to backup and restore my database but using it to get list of available ms sql server and database to that particular server is no luck..

[daimous]

Its now already working using SQLDMO...Thanks a lot!

[daimous]

Now it has an error again..hope this is not a major problem..I'va created a function that would return an array of string that contains the list of MS SQL server but when i call that function it has an error..can you help me to figure it out? Thanks!

        public static string[] GetSqlServerList()
        {
            string[] SqlServerList = null;
            try
            {
                SQLDMO.Application SqlApp = new SQLDMO.Application();
                SQLDMO.NameList SqlServers;
                SqlServers = SqlApp.ListAvailableSQLServers();
                for (int i = 0; i <= SqlServers.Count - 1; i++)
                {
                    if (SqlServers.Item(i) != null)
                    {
                        SqlServerList[i] = SqlServers.Item(i).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error getting SQL Server list!\r\nMessage:" + ex.Message, "RCPS [Server List]",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return SqlServerList;
        }

ERROR:

System.NullReferenceException was unhandled
Message="Object reference not set to an instance of an object."
Source="RCPSProject"
StackTrace:
at RCPSProject.CommonFunctions.GetSqlServerList() in C:\Documents and Settings\absalvamante\My Documents\Visual Studio 2005\Projects\RCPSProject\RCPSProject\Common\CommonFunctions.cs:line 182
at RCPSProject.RCPSIndex.BtnRenew_Click(Object sender, EventArgs e) in C:\Documents and Settings\absalvamante\My Documents\Visual Studio 2005\Projects\RCPSProject\RCPSProject\RCPSIndex.cs:line 62
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at RCPSProject.Program.Main() in C:\Documents and Settings\absalvamante\My Documents\Visual Studio 2005\Projects\RCPSProject\RCPSProject\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

[RobDog888]

Try placing the sercver count - 1 into a variable before hte loop.

integer iTemp = SqlServers.Count - 1

[daimous]

i got it working..Instead of array of string i just use List<string>..

        public static List<string> GetSqlServerList()
        {
            List<string> ServerList = new List<string>();
            try
            {
                SQLDMO.Application SqlApp = new SQLDMO.Application();
                SQLDMO.NameList SqlServers;
                SqlServers = SqlApp.ListAvailableSQLServers();
                for (int i = 0; i <= SqlServers.Count - 1; i++)
                {
                    if (SqlServers.Item(i) != null)
                    {
                        ServerList.Add(SqlServers.Item(i));
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error getting SQL Server list!\r\nMessage:" + ex.Message, "RCPS [Server List]",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            return ServerList;
        }

Anyway, Thanks for the inputs!

[daimous]

RobDog, From the link you gave me above..what if i want to use Windows Authentication instead of SQL Server Authentication how can I do that? Thanks!

[daimous]

I already got it..I just use

                    sqlServer.LoginSecure = true;
                    sqlServer.Connect(ServerName, "", "");