Problem discovering local SQL Server

I found that SMO sometimes does not return the correct list of available SQL servers.

1. I have SQL development installed in my machine. When I use SMO to retrieve list of server, it does not return anything. But when I use "OSQL -L" command, it returns (local) server.

This is my code.

Cursor.Current = Cursors.WaitCursor

cmbServerName.Items.Clear()

Dim dtSQLServers As DataTable = SmoApplication.EnumAvailableSqlServers()

Dim I As Integer

For I = 0 To dtSQLServers.Rows.Count - 1

Dim ServerName As String = dtSQLServers.Rows(I)("Server").ToString()

If (dtSQLServers.Rows(I)("Instance") IsNot Nothing And dtSQLServers.Rows(I)("Instance").ToString().Length > 0) Then

ServerName = ServerName + "\" + dtSQLServers.Rows(I)("Instance").ToString()

cmbServerName.Items.Add(ServerName)

End If

Next I

Cursor.Current = Cursors.Default

2. When I install SQL 2005 Express in any machine, I cannot retreive its existance via SMO with the same code (on local and remote machine).

Anyone experiencing this situation before? Is there something wrong in my code? How can I fix this?

Thank you,

Hi,

consider the differences with Server and InstanceName, I once wrote an Article about that:

http://sqlserver2005.de/Articles/1/

I saw that you wanted to add the data of ServerName to instance name which would be wrong in your case.

2. Is SQL Server brwoser running ? Otherwise it could be that instances running on other server can′t be retrieved if hosted on different ports.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Problem copy a table from SQLServer 2000 data base to SQLExpress database using SMO.

I am trying to to copy a table from a SQLserver 2000 database to a new table in as SQLExpress database. The following is the code I used.

Private Sub btnCopyTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyTable.Click

Try
mFromDatabase = New Database(mFromSrv, Me.cboSelectFromDatabase.Text)
mToDatabase = New Database(mFromSrv, Me.cboSelectToDatabase.Text)
'mToDatabase.Create()
Dim xfr As New Transfer
xfr.CopyAllTables = False
'xfr.Options.WithDependencies = False
'xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = Me.cboSelectToDatabase.Text
xfr.DestinationServer = mFromSrv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
xfr.TransferData()
Catch ex As Exception
Dim errMsg As String
errMsg = ex.ToString
Throw
End Try

End Sub

When I do this I get the following error on the xfr.TransferData line.

Microsoft.SqlServer.Management.Smo.SmoException: The Integration Services component is not installed or you do not have permission to use it. > System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.SqlServer.Management.Smo.Transfer.CreateDtsTransferProvider()

=== Pre-bind state information ===
LOG: User = ACER-1\phowatt
LOG: DisplayName = Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
(Fully-specified)
LOG: Appbase = file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/
LOG: Initial PrivatePath = NULL
Calling assembly : Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91.
===
LOG: This bind starts in default load context.
LOG: Using application configuration file: D:\VS2005_Projects\AnalyzeProjects\Forms\bin\Forms.vshost.exe.config
LOG: Using machine configuration file from C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Post-policy reference: Microsoft.SqlServer.DtsTransferProvider, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider.DLL.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider/Microsoft.SqlServer.DtsTransferProvider.DLL.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider.EXE.
LOG: Attempting download of new URL file:///D:/VS2005_Projects/AnalyzeProjects/Forms/bin/Microsoft.SqlServer.DtsTransferProvider/Microsoft.SqlServer.DtsTransferProvider.EXE.

End of inner exception stack trace
at Microsoft.SqlServer.Management.Smo.Transfer.CreateDtsTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.GetTransferProvider()
at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
at Forms.frmTransfer.btnCopyTable_Click(Object sender, EventArgs e) in D:\VS2005_Projects\AnalyzeProjects\Forms\frmTransfer.vb:line 149

I used the following references with this code.

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SmoEnum

Microsoft.SqlServer.SqlEnum

SMO uses SSIS to perform data transfer operations, but SQL Server Express doesn't install the SSIS engine, so the transfer operation fails because the service SMO relies on to perform the operation doesn't exist on the machine you're running on. On this page of my blog - http://sqljunkies.com/WebLog/marathonsqlguy/archive/2006/10/31/24756.aspx - I described how to build a table from an existing one. Perhaps you can use this code to build your destination tables, and then use Transact-SQL to copy the data from the source to the destination database.|||I also have SQL Server 2005. If I install it will that install SSIS? Is there another way to install the SSIS engine? Or maybe your recommended approach is the better approach in any case.|||The full versions (Developer, Standard, Enterprise) of SQL Server allow you to install SSIS, which I believe will solve your problem (based on the error messages in your first post.)|||I have written myself a little application that enables me to set the various copy properties and option properties and then display the resulting scripts so that I can see how the various copy and option property settings affect the outcome. Since there is little documentation about SMO you cannot depend on your intuition about how these different property settings might affect the resulting script. Also there is IMO a shortcomming in the scripting of views. The problem occurs when there are views that are based on other views. If the underlying views are not created first then there will be an error when using the script when trying to create a view that uses another view and that other view has not yet been created. You might think that setting the CopyAllViews to True and setting the Options.WithDependencies to True would be how to deal with that but it appears that the Options.WithDependencies only affects scripts for tables. It appears that if you try to copy a database from one server to another and there are views like I have described above the Transfer will fail and it seems that there is no way to set the options to avoid this problem. So it appears that copying a database using Transfer will work only if there are no conditions in the source database that Transfer cannot handle.