June 28, 2007 at 2:21 pm
Hello,
how can I list all sql server instances SQL server 2005 local and the network usind SMO (from vb.net all vs2005).
Thank
October 5, 2007 at 12:28 pm
Use This code !
----------------------------------------------------------
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Dim dts As DataTable = SmoApplication.EnumAvailableSqlServers(False)
cmbSServers.DataSource = dts
cmbSServers.ValueMember = "Name"
-----------------------------------------------
Matt
March 3, 2008 at 12:35 pm
I am pretty new to this - what references do I need?
-- Cory
March 13, 2008 at 8:03 pm
You'll need references to Microsoft.SqlServer.SMO and (usually) Microsoft.SqlServer.ConnectionInfo.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
August 12, 2008 at 6:40 pm
Hey Tim,
Maybe you know how to get a list of databases on a particular server?
Regards
Leonard
August 14, 2008 at 4:32 am
Hi,
Its very simple!
Reference the following;
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
---------------------------------------------------------------------------
I'm getting all SQL server instances on the network;
'Getting all Servers on network and fill DataTable
Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)
' Databinding combobox
cmbSServers.DataSource = dt
cmbSServers.ValueMember = "Name"
If cmbSServers.Items.Count < 1 Then
cmbSServers.Text = " no servers found "
Else
cmbSServers.SelectedItem = 1
End If
-------------------------------------------------------
' Now im connecting to the selected Server as chosen by the user
Dim conSrc as ServerConnection = New ServerConnection(cmbSServers.Text)
'if you know from before hand what instance you want to connect to then instead use;
Dim conSrc as ServerConnection = New ServerConnection(InstanceName)
' if connecting to the selected instance requires a username and password then use
conSrc.Login = txtsuser.Text
conSrc.Password = txtspassword.Text
'if using windows integrated security use
conSrc.LoginSecure = True
'Connect to the instance
conSrc.Connect()
Dim svr as Server = New Server(conSrc)
'For all the databases found in the connected Server displayed them in combobox
For Each db As Database In svr.Databases
cmbDatabases.Items.Add(db.Name)
Next
If cmbDatabases.Items.Count < 1 Then
cmbDatabases.Text = "no databases found"
Else
cmbDatabases.SelectedItem = 1
End If
-----------------------------------------------------------------
Hope this help!
Regards,
Matt
August 14, 2008 at 6:33 am
HI Tim,
Thanks for your help. It works great. Now for my next question:
I see that the datareader cannot get a recordcount. Although I could use the old ADODB.Recordset object, I would really like to use ADO.NET, so is there any way to use a datareader object or another ADO.Net cursor, and have the luxury of a recordcount? I would really like to have an accurate progressbar in my app, but without a recordcount to set the maximum value, its looking a bit tricky.
Regards
Leonard
August 14, 2008 at 6:29 pm
Hi,
I wouldn't use the datareader of ADO.NET to get the record count!
If you want to get the number of found servers use;
// this continues from the code of the previous post
Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers(False)
dt.Rows.Count
If you want to find the number of found databases on the server istance use;
// this continues from the code of the previous post
srv.Databases.Count
once you retrieve the count set the progress bar max value to the count value.
Regards,
Matt
August 14, 2008 at 7:25 pm
Hi Matt,
Thanks for the reply. I am not trying to count the number of databases. I am fine with that part of the application.
What I was actually trying to do is to use a progressbar, to show the progress of retrieving data (not databases).
So I want to move through a recordset of data that has been returned by a stored procedure, and I need to get a recordcount (how many rows are in the recordset of data), to set the maximum value for my progressbar.
In ADODB, there was a recordcount property for the recordset object, I am looking for the equivalent thing in ADO.NET.
Regards
Leonard
August 15, 2008 at 3:28 am
Hi,
You want to execute a stored procedure and return the number of rows returned by the sp using ADO.NET?
If so then I created this function that generates the sp script and right after it executes the script storing the results (recordSet) in a DataSet;
Private Function GetStoreProcedures(ByVal db As Database) As ArrayList
Dim script As System.Collections.Specialized.StringCollection
Try
Dim sp As StoredProcedure = New StoredProcedure(db, "Your SP Name")
script = sp.Script()
Dim ds As New DataSet("Results")
ds = dbSobjects.ExecuteWithResults(script)
' Get Row count = ds.Tables(0).Rows.Count
Catch ex As FailedOperationException
Throw ex
End Try
End Function
If the code fails execution please let me know.
Regards,
Matt
August 15, 2008 at 4:16 pm
Hi Matt,
Thanks for all you help, considering that you don't even know me. I am sure that this code will work, however (and not to sound ungrateful, which I probably do...), my need is a bit more complex than just a simple scripting action.
My stored procedures have many parameters, and some of them are optional, which means that depending on how you call the proc, the rowcount will differ every time.
As well as that, I am actually trying to get the rowcount from the same object that returns the data (as you can in ADODB). I don't want the additional overhead of two calls to the database (one for data and one for a row count), as this seems totaly illogical to me.
Regards
Leonard
August 16, 2008 at 3:21 am
Hi,
Ok, if I understood you correctly you want to execute a stored procedure depending on certain parameters and then return the data + rowcount?
IF so there is no need of SMO.
I'm going to use instead ADO.NET objects instead;
Dim conn As New SqlConnection("Insert connection string")
conn.Open()
'Use sqlCommand to get the data
Dim myCommand As New SqlCommand("sp name", conn)
myCommand.CommandType = CommandType.StoredProcedure
'Define the parameters
myCommand.Parameters.Add("@para", SqlDbType.Int).Value = 10
myCommand.Parameters.Add("@para2",SqlDbType.Int).Value = 32
'Create a Datatable that stores the data from the executed sp
Dim dt as new Datatable("Results")
dt.Load(myCommand.ExecuteReader())
'For Row count
dt.Rows.Count
Hope to have understood you now
Regards,
Matt
August 19, 2008 at 3:22 pm
Hi Matt,
It's looking better now. Here is what I was trying to do:
The SMO stuff is for the login part of the app, so I want to give a list of servers on the network, and when a server is selected, then a dropdown for databases on that server, and then the login process.
Then I am also building a framework, with a central point for all data access in the application, so once the user is logged in the recordcount is required for each and every data call that needs to update a listview & a progressbar, so this is where I needed ADO.NET
So that is all working now. Then next part is to upgrade the old VB6 Crystal Report Engine to get the same functionality in VB.NET.
Thanks for all your help.
Regards
Leonard
August 25, 2008 at 9:04 pm
Hi Matt,
I have another question for you:
In ADODB, when you loop through a recordset, you would use the movenext method of the recordset, and you could access the data like this:
with rs
txtperson_name = rs!person_name
rs.movenext
end with
How do you achieve the same effect in ADO.NET with a DataTable?
Regards
Leonard
September 17, 2008 at 8:05 pm
Sample code:
For Each row As DataRow In myDataTable.Rows
txtperson_name = row!person_name.ToString()
Next
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply