I am running the SQL express advanced on the same PC that also hosts my Visual Basic Express application. I have user instances turned off and full-text enabled and working from within a SQL query window. However, I can not create a working connection to the database using the data source wizard from the server explorer within VB.NET. Since this is on the same PC, do I need to enable anything other than shared memory? I get the error: Provider SQL Network Interfaces, error 26 Error locating server/instance.
DeBug
hi DeBug,
if you are using VBExpress, I've heard the data source wizard should not be able to use connections other than user instances.. you should modify the connection context in your code, pointing to the "shared" SQLExpress data source..
personally I'm not able to help more, as I only own the full VS edition..
regards
|||Hi,
when you say you turned user instances off, do you mean that you installed the default instance and not a named instance?
What Data source, Servername and log on type did you choose when you tried to add the SQL server to the server explorer?
Are you running VB.Net in the same windows account you installed SQL Express with?
--
SvenC
I believe that user instances are disabled for use with full-text search so I set the User Instance to False. The following connection string both fail:
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SGExpress.mdf";Integrated Security=True;Connect Timeout=30;User Instance=False
Data Source=.\MSSQLSERVER;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SGExpress.mdf";Integrated Security=True;Connect Timeout=30;User Instance=False
I installed VB and SQL Express using my administrative user account logged on locally to the PC. I'm am not being logged into any domain server but only to the local PC.
DeBug
|||Update: I can connect to the database using the following code in a VB.NET application but not with the Data Source Configuration Wizard. The wizard will not allow me to enter ".\" where it assigns the ".\SQLExpress" and other instances.
Dim conn As New SqlConnection()
conn.ConnectionString = "Data Source=.\;Initial Catalog=SGExpress;Integrated Security=True;Integrated Security=True"
conn.Open()
Me.Text = conn.ServerVersion
conn.Close()
The Microsoft SQL Server Reporting Service wizard allows me to produce and connect the following xml DataSource.rds file:
<?xml version="1.0" encoding="utf-8"?>
<RptDataSource xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Name>DataSourcePlats</Name>
<DataSourceID>cf8ea427-70f2-4a10-8989-61ed5f9bd74c</DataSourceID>
<ConnectionProperties>
<Extension>SQL</Extension>
<ConnectString>Data Source=.\;Initial Catalog=SGExpress</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</RptDataSource>
However, even though I saved the above data source as Shared, it does not show up in VB.NET.
How can I manually create a DataSource for my Visual Basic Express applications? I believe this problem is related to the fact that my SQL Express was installed to allow for full-text indexing and does not have indivual instances.
DeBug
|||Did you try to use only . instead of .\
You might also try your real computer name.
How did you install SQL Server? Did you specify an instance name? Did you install more than one instance? What services do you see in service manager (Start->Run : services.msc) which start with "SQL Server". Please post the exact names.
--
SvenC
The wizard will only let me select an instance from the drop down box. I can not edit that property to key in: ".", " .\", or my computer name.
I installed SQL Server Express Advanced using the EXE downloaded from the MSDN web site. It created a directory like "C:\d3705b3534f0937859a" and installs from there. I do not recall specifing a instance name on the last intall as per the full-text requirements. I have however used the SQL Express (not advanced) with an instance name on this PC. That version of SQL Express was removed using the ADD/Remove and the newer SQL Express with full-text search was then installed.
Service Manager:
SQL Server FullText Search (SQLEXPRESS) - stopped, auto
SQL Server (MSSQLSERVER) - started, auto
SQL Server Active Directory Helper - stopped, disabled
SQL Server Browser - started, auto
SQL Server FullText Search (MSSQLSERVER) -started, auto
SQL Server VSS Writer - stopped, manual
I can connect using ADO.NET in Visual Basic Express using just code but fail at creating a data connection via the wizard.
DeBug
|||Hmm, the way you use the server explorer in VB.Net Express seems to be different to the full version of Visual Studio 2005 I use. In the server explorer I see a top node "Data Connections" where I can right click to add a connection. In the following dialog I can enter the computer name where SQL server is running. The data source field is a bit misleading as I can only choose which provider to use. I have a few screenshots how it looks on my machine:
http://i112.photobucket.com/albums/n193/Sven_Carstensen/vs_Server1.gif
http://i112.photobucket.com/albums/n193/Sven_Carstensen/vs_Server2.gif
http://i112.photobucket.com/albums/n193/Sven_Carstensen/vs_Server3.gif
Is that comparable to your server explorer UI? Otherwise I hope someone with VB.Net Express can help.
--
SvenC
Visual Basic Express has a "Database Explorer" which contains a collection of Data Connections. I can right click the Database Explorer pane and choose Add Connection. This launches the Add Connection wizard which allows me to select the data provider, the database file, and other connection parameters. When I click the Test Connection button, it always fails. However, if I code a connection with ADO.NET as noted in my previous posts, I can in fact connect and display a query in a DataGridView control. I've used the wizard in the past to create connections and I'm puzzled why it is failing now. The only thing I changed when removing and then reinstalling SQL Express with Advanced Services, is installing using the default instance and disabling user instances as per the full-text documentation. If I right click in the code window of Visual Basic Express and insert the snippet "create a local SQL connection to SQL Express" it also fails with what I think should be a correct connection string. If I insert the snippet "create a server based connection SQL Server" then it works correctly. I think the wizard is trying to create the same type as the local SQL connection to SQL Express and fails for the same reason. I'm struggling to discover that reason.
DeBug
|||That sounds like a limitiation in the Express edition of VB. The database explorer seems to always expect the named instance SQLExpress which you now no longer have.
You might need to use SQL Server Management Studio Express instead of the build in Database Explorer to manage your SQL server. That should give you full control to what server and instance you want to connect.
Maybe some Microsoft person has better details about using the Database Explorer with other instance names as SQLExpress?
--
SvenC
Hi Doug,
Andrea is right, VB Express uses User Instances by default, although I would have expected a different error that your getting.
If you're trying to create a database directly in your project (i.e. Right-click in the project folder and use the Add New Item | Database functionality) you will have to use User Instance, that's the only way it works.
If you've already created a database on the parent instance of your server, then you can make a connection to it in the Database Manager, but you'll need to go into the Advanced properties and change User Instance to false. You will have to point at the actual database file, even though it is already attached, because that's just the way VB Express is designed to work. (In short, both VB Express and C# Express were designed with the assumption that they would be used only to create single user applications with data stored locally by using User Instances. Because of this, the connections work in a specific way.)
Another possibility given the error, is that you've installed your Instance of SQL Express to a non-default Instance Name. (something other than SQLEXPRESS) If that is the case, you'll need to modify the properties of your VB Express installation to use your Instance Name. By default, VB Express is configured to use SQLEXPRESS as the Name Instance. You can do this on the Tools | Properties menus. Select the check box to show all properties in the dialog and then look in the Data properties.
Mike
No comments:
Post a Comment