SQL Server 2012 Express LocalDB 解决provider: SQL Network Interfaces error: 52
来源:互联网 发布:淘宝天猫历史价格 编辑:程序博客网 时间:2024/05/18 15:07
原文连接:http://www.mssqltips.com/sqlservertip/2694/getting-started-with-sql-server-2012-express-localdb/
Problem
Developers using SQL Server Express face a few challenges in their day to day work. One is that setting up and maintaining Express can be a daunting task. Another is that using the "User Instances" feature (which has been deprecated) leads to a lot of confusion - developers connect to one instance of the database through their tools, and another instance of the database through their program, and don't understand why updates to one aren't reflected in the other. This is actually due to the AttachDbFileName setting, but in most examples I've seen, the two seem to go hand in hand.
Solution
SQL Server 2012 introduces a new feature, SQL Express LocalDB. The purpose of this new feature is to provide developers with a local environment that is much easier to install and manage. Instead of installing a service and configuring security, they just start up an instance of this new LocalDB runtime as needed. In addition, the download for the SQL Express LocalDB runtime is only about 33 MB (or 27 MB, if you are still on x86), compared to the 100s of MBs required to download a full edition of SQL Server Express.
Before you get started, you'll want to make sure that your operating system is patched to the latest service pack and current according to Windows Update. Supported operating systems are as follows:
- Windows 7
- Windows Server 2008 R2
- Windows Server 2008 Service Pack 2
- Windows Vista Service Pack 2
It will also work on Windows 8 if you're using any of the pre-release versions, but I have not tested this on any of the Server Core variants of Windows Server, so you're on your own there. For further information on system requirements, please see Hardware and Software Requirements for Installing SQL Server 2012.
You'll want to make sure that you've installed .NET Framework 4.0 and, equally as importantly, the .NET Framework 4.0.2 update (KB #2544514).
Once your system is up to date, you can download the SqlLocalDb installer from:
http://www.microsoft.com/en-us/download/details.aspx?id=29062
- If your system is 64-bit, you want x64/SqlLocalDB.MSI (33.0 MB)
- If your system is 32-bit, you want x86/SqlLocalDB.MSI (27.7 MB)
Setup is rather trivial:
Once installed, you can interact with SqlLocalDb using the command line. The following will tell you the version of SqlLocalDb:
Result:
If you want to create an instance:
Result:
To start the instance:
Result:
You can also create an instance and start it in one command using the -s argument:
To stop and delete an instance, you can issue two commands:
If you try to just delete the instance without first stopping it, you will get this error:
To check on the status and other details about an instance, you can run:
Result:
Now that an instance is created and started, you'll probably want to do other things like create databases and run queries. Unfortunately SqlLocalDb on its own only provides an interface to the engine; it does not provide a means to interact with databases. However there are several other ways to connect to and interact with SqlLocalDb instances.
sqlcmd
In order to use sqlcmd, you must install either SQL Server 2012 Management Studio Express or the client tools from a regular SQL Server 2012 edition (though I caution against using Evaluation Edition here, since the client tools will expire after 180 days). You can install just the SQL Server 2012 Command Line Utilities, but I will install Management Studio Express since it's free, supports all of the functionality you should need to manage LocalDB instances, and for most tasks is preferable to sqlcmd anyway. You can download Management Studio Express from this page:
http://www.microsoft.com/en-us/download/details.aspx?id=29062
As with SqlLocalDb, you'll want to pick the file that is appropriate for you, depending on whether you are running on x86 or x64. When the Installation Center opens, you'll want to pick the option "New SQL Server stand-alone installation or add features to an existing installation" - even though neither describes what you're really doing.
On the Product Updates screen, you can uncheck the box to prevent it from checking the web for updates (or to prevent it from timing out in the event you have slow or no connectivity). At the time of writing no updates were found anyway.
While it should be checked by default, make sure that on the Feature Selection screen, "Management Tools - Basic" is selected.
Once installed, you can connect to the local instance using sqlcmd. Be sure to use the most recent version of sqlcmd on your system; if you have multiple versions (e.g. from previous versions of SQL Server or Visual Studio), calling sqlcmd will pick up the first one in your PATH environment variable, which is almost certainly going to be the earlier version. You can create a shortcut to cmd setting the start location to:
So now that sqlcmd is installed, and assuming you started an instance called "MyInstance" per above, you can connect using sqlcmd this way:
Result:
So now you can create a database, create a table, etc.
Management Studio / Management Studio Express
We can connect to this instance using Management Studio Express in a similar way. You can launch ssms.exe from the cmd line, and when prompted for a connection, use the server name "(localdb)\MyInstance":
Here is the database / table we created through sqlcmd (we could of course continue adding/editing objects from Management Studio):
One thing you'll want to make note of is the location of databases in a SqlLocalDb instance. As described in this blog post, databases are by default created in your user profile directory. So if your profile is on C:\ and that drive is short on space, or if you want to create your databases on other drives for whatever reason, you'll need to use explicit locations in your CREATE DATABASE statement, e.g. to put the data file on D:\ you can say:
It would be nice if you could set the default data / file paths for the SqlLocalDb instance so that you didn't have to specify the locations every time, but this screen is greyed out:
SQL Server Data Tools
In addition to using sqlcmd and Management Studio Express, you can download SQL Server Data Tools (SSDT), which also installs the Visual Studio 2010 Shell if you don't already have some edition of Visual Studio 2010 installed. You can download SSDT from the following site:
http://msdn.microsoft.com/en-us/data/hh297027
Eventually this will launch the Web Platform Installer:
Once you've installed SSDT, you can launch the program, create a new SQL Server Database Project, and use the new (localdb) instance it creates:
But you can also use "Add Server..." to connect to and work with your existing instance:
Visual Studio
To use SqlLocalDb with Visual Studio proper, see the following blog post, where Roel van Lisdonk walks you through using both Visual Studio 2010 and the newer VS11 beta to connect to SqlLocalDb:
http://www.roelvanlisdonk.nl/?p=2607
PowerShell
It took me a little finagling to get PowerShell to connect to my LocalDB instance. Even with SQL Server 2012 client tools and the .NET 4.0.2 update installed, it seems that the version of SMO that PowerShell uses an older version of SMO that is not LocalDB-aware. Granted, this was Windows 7 without any explicit updates to PowerShell. While you should be able to connect using this syntax:
Various attempts at server names (such as (localdb)\.\MyInstance
and .\MyInstance
yielded generic "Failed to connect to server" errors. It turns out I had to connect using the named pipe syntax, which was necessary for other applications prior to the 4.0.2 update:
To determine that little hex code associated with the named pipe for the selected instance, you can use the SqlLocalDb info command. As you can see in the following screen shot, I just copied the output from the "Instance pipe name" line:
Note that this hex code will change every time you start the instance. Hopefully this issue will be fixed soon so that PowerShell can connect to SqlLocalDb instances just like the other applications can.
ADO.NET
In order to connect via ADO.NET, there is only a minor change in the way you specify your connection string. As per the above examples, you just use (localdb) in place of "." or "machine name":
This requires the .NET Framework 4.0.2 update, however, otherwise you'll have the same issues I disclosed about PowerShell, and you'll need to use the LOCALDB#HEX-PIPE
connection format.
Conclusion
Hopefully this gives you a jump start in using SQL Server 2012 Express LocalDB for local development. There are other aspects I haven't touched on, that I may treat in future tips - for example, sharing instances with other users, and the fact that LocalDB is named as such for a reason: it does not accept remote connections.
- SQL Server 2012 Express LocalDB 解决provider: SQL Network Interfaces error: 52
- provider: SQL Network Interfaces , error: 26 - Error Locating Server / Instance
- SQL Server 2012 Express LocalDB
- SQL Server 2012 Express LocalDB
- SQL Server 2012 Express LocalDB
- provider: SQL Network Interfaces, error: 26
- Sql Server服务远程过程调用失败 provider: SQL Network Interfaces, error: 26
- SQL 2008提示错误信息(provider: SQL Network Interfaces, error: 25
- SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
- SQL Server 2012 Express LocalDB 管理并与ArcGIS相连
- SQL Server 2014 Express LocalDB使用
- SQL Server 2014 Express LocalDB自动实例
- SQL Server Database在C#编程下遇到的问题:“provider:SQL Network Interfaces,error:26-定位指定的服务器/实例时出错”的解决办法
- provider: SQL Network Interfaces, error: 26 - 定位指定的服务器/实例时出错
- VS2012自带轻型的sql server express数据库---LocalDB
- VS2012自带sql server express数据库---LocalDB管理
- provider:SQL Network Interfaces,error:26 - 定位指定的服务器/实例时出错--错误原因及解决办法
- SQL Server 2012 LocalDB 管理之旅
- seo培训常识
- 映射集合属性之⑤:Map集合属性
- 确定网站盈利模式方向及可行性分析
- 如何优化MySQL insert性能
- SEO中的精髓
- SQL Server 2012 Express LocalDB 解决provider: SQL Network Interfaces error: 52
- 自己动手写类似酷狗播放器(4)_鼠标右键菜单的添加
- Linux /bin, /sbin, /usr/bin, /usr/sbin 区别
- RTSP传输协议之Methods总结
- 教你如何学习Linux驱动开发
- Linux驱动模型
- mainfest---android 不同包里面的activity在mainfest里的声明
- hdu 3374 String Problem
- 如何学习linux设备驱动