Microsoft Announced the Plan of SQL Server on Linux
Microsoft SQL Server is a popular database system. It is also the one of top 3 database systems in the world (The other 2 are Oracle and MySQL). It is also the biggest database systems on Windows platform.
How to Stop a SQL Server Instance
In some cases you might have to stop a SQL Server instance. For example: you want to install SQL Server 2012, but you have a previous version like SQL Server 2008 installed. and, both of two versions are set the default TCP/IP port number, which is 1433 for SQL Server. so in this case you will have to stop the old SQL Server and install new one.
SQL Server Cannot Connect to WMI Provider, No Permission
We have SQL Server Management Studio 2012 Express works well, but when we tried to launch SQL Server Configuration Manager, we got the following error message:
How to Change Multiple Data Table Names in SQL Server
In our one case we have multiple data tables with names includes spaces, we need to cut off all spaces.
Copy Data Tables From Another Database Using SQL Server Management Studio
Sometimes we need to copy data tables from another database in SQL Server. There are multiple ways to do it. Here we show the steps using SQL Server Management Studio.
When SQL Server Express is Oversized
We record for our internal issue about the Free SQL Server oversized, to backup our operations for checking in the future.
Check SQL Server DB Size
Sometimes we have to check SQL Server database size, especially for SQL Server Express edition since it is free with database file size limitation.
Limitations of SQL Server 2012 Express
Some software providers want to use free SQL Server Express as one part of their software product and then deploy to their customers. Personally I don’t think it is a good idea, but actually they can do like that. But we must acknowledge some limitations when using SQL Server Express as product.
Change Sql Server Database Name with Related Filenames Changing
Sometimes we need to change a database name, for example, we have a database, but later we have newer version database based on this database, we want to keep both of versions on same SQL Server machine, and also we want to use keep the database name on newer version. What the one way which we can do is rename the old one.
SqlCmd vs oSql vs iSql
SqlCmd, oSql and iSql actually looked like the same product.
Update Local SQL Server ‘ServerName’ after Changed Computer Name
If you changed your computer name with a SQL Server installed (my version is SQL Server 2012 Express), you will find your installed SQL Server did not change its Servername to new host computer name, which will causes some issues if you have stuff using Windows Authentication access.
Delete multiple tables in SQL Server by table names
If data tables have similar table names, such as data log tables named by dates, you might want to delete multiple tables, for example, you wanted to delete all tables in a month, or in a year.
Check SQL Server Database Size
There are 2 ways to check SQL Server database size:
A System Editor module in a Scada System using WPF part 2
This is the part 2 of the WPF applicatoin, the Part 1 is here:
Microsoft’s SQL Server Connection Strings samples
Here is Microsoft’s SQL Server Connection Strings samples.
A System Editor module in a Scada System using WPF Part1
Let's start to introduce the new System Editors module in a Coal Minding Scada system.
Conspec Windows Primary System S950 Part 1
Windows Primary System is a Coal Mine gas detection device control system which provided Conspec Controls.
Install SQL Server Management Studio Express 2012
After we have installed LocalDB or SQL Server Express, we still feel we need SQL Server Management Studio Express to manage the local database system, it is more convenient than using a command line.
Install SQL Server 2012 Express LocalDB ?
First let us know: If you have installed Visual Studio 2012 or 2013, normally you do NOT have to install SQL Server Express LocalDB separately because “In Visual Studio 2013 (and in 2012), LocalDB is installed by default with Visual Studio.” (from here)
SQL Server 2008 R2 Remote Connection
We had a post about Remote Connection for SQL Server 2008 R2 Express, but this time we work on a SQL Server 2008 R2 version, which is not an free express version.
SQL Server Install Locations
When we install SQL Server database such as SQL Server 2008 R2, maybe we want the data to be stored on another driver other than C: driver;
Add a User in SQL Server 2008 R2
Here we are going to create users in SQL Server 2008 R2 using SQL Server Management Studio, and, this user will be used in our application which is based on current SQL Server database. (this new user will be created according to our application case, for your case, there might be slight difference)
SQL Server Compact Supported Collations
Sometimes we have to search what Collation is supported by SQL Server, for example, if we know our computer system is English Windows edition , and English Visual Studio, so what the collation exactly is supported by default when you directly add a SQL Server file such as a SQL Server Compact 4.0 file ?
Import SQL Server 2008 data table structure into Microsoft Visio 2010
Seems there is few help information on internet that how to import an existing SQL Server data table structure into Microsoft Visio 2010, or there are some information, but when you read them, you are always confused because most of them missed some important steps.
SQL Server Compact 4.0 for Visual Studio 2010
The last time we posted "Install Microsoft SQL Server Compact 4.0", Some friends feed backed they could not see anything about SQL Server Compact 4.0 in their Visual Studio 2010.
Select Last N Rows In A SQL Server Data Table
How to select last 5 or 10 or … rows in a SQL Server data table ?
Update a Table using the Data from Another Table
There are lots of ways to update a table data, here we have one example as the following:
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand
There was a coder wrote the following code about a SQL Server saving operation:
Unable to find the requested .Net Framework Data Provider. It may not be installed.
We have a small MVC project, once a time we got the following error message in a popup window:
Install Microsoft SQL Server Compact 4.0
Microsoft SQL Server Compact 4.0 is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. SQL Server Compact 4.0 has a small footprint and supports private deployment of its binaries within the application folder, easy application development in Visual Studio and WebMatrix, and seamless migration of schema and data to SQL Server. – From Microsoft
DataSet vs DataReader
DataSet is an in-memory representation of a database, providing a consistent relational programming model no matter what format of data source. It contains multiple data tables and their relationship and constraints information.
Update data with If exists in SQL Server Stored Procedure
There is a table structure here:
Reset Identity in SQL Server
We have already had a post about “Clear ID counter after delete data table” which is just for reset identity column in SQL Server using Truncate command.
Grant Permissions on Stored Procedure
After you finished your Stored Procedures, you have to know you should grant permissions to database users, otherwise, when you deploy your application, you might find your stored procedures can not work due to permissions issues.
Select Data By Page in SQL Server 2008 Side
When we have huge data and we need to display data in client side page by page, the best way might be paging data in SQL Server side, otherwise the huge data retrieving will be ‘dead’ in your client side.
Install SQL Server 2008 Management Studio Express
For Microsoft languages developer, SQL Server 2005 / 2008 Management Studio Express is a required database management tool.
Hate installations? Use Lightweight IIS and SQL Server
When you build an ASP.NET application, you might fee some boring works which you have to install a SQL Server database , no matter you can install SQL Server Express version, but you still have to install it. and also, if you want to try your application without using Visual Studio, you have to install IIS or set the existing IIS in your Windows system.
SQL Server DateTime to String
We have already posted SQL DateTime Operations. Here we need another sample which is about how we should compare a SQL Server DateTime and a C# DateTime String:
A SQL sample for pager
The best way for pager of searching result is use pager in Database side, use stored procedure:
CREATE VIEW must be the only statement in a batch?
In SQL Server, when you try to input a “Create View” sentence in a Stored Procedure. you might get the following error:
Insert Unicode Characters Without Changing Collation
Normally, in western country, when you create a SQL Server database, your database collation is default by ‘SQL_Latin1_General_CP1_CI_AS’, If you want to use Unicode for data table column, you use nvarchar or nchar, Do not use varchar / char because they are only for Non-Unicode characters. This is the 1st condition we should have for Unicode.
Show enum descript in sql SELECT sentense
We have a sql select as the following:
Can not add a Non-Nullable field in your data table?
Maybe you got error when you try to update your database SQL Server such as add a Non-Nullable field.
SQL Server Restricted User ?
Sometimes you might find your SQL Server database is in “Restricted User” mode: like following:
How to import SQL Server 2005 Tables into Microsoft Visio 2007 (2) ?
We have already discussed "How to import SQL Server 2005 Tables into Microsoft Visio 2007 (1) ?", Now we continue the part 2:
How to import SQL Server 2005 Tables into Microsoft Visio 2007 (1) ?
In some cases we need to import SQL Server tables to Microsoft Visio. Current the popular Visio version should be Visio 2007, Here we will use Visio 2007 as our sample version.
Check table exists in SQL Server
Here we collect 3 methods:
ConfigurationManager can not be found in .NET 2.0 ?
When we added something such as database Connection String in App.config file in .Net 2.0, we need to use the following 2 ways to get Connection String:
Again Remote connect to SQL Server 2008 Express
We installed SQL Server 2008 Express on a new Windows 7 Professional PC, and met some new stuff when we installed.
Keeping Identity Values When Importing Data in SQL Server 2005
If you have data tables which are set identity columns, you have to carefully when you migrate your database, or import / outport data. The point is: How to Keeping Identity Values When Importing Data in SQL Server 2005 ?
Primary Key vs Uniqueindentifier vs Unique Key in SQL Server
- Primary key – Only one Identity column – Can't be null
Mapping Your Data with Bing Maps and SQL Server 2008
Found an article about “Mapping Your Data with Bing Maps and SQL Server 2008”
Invalid operator for data type. Operator equals add, type equals bit
When I wrote a sql server stored procedure, got a error message about bit data type when I tried to run this script:
nchar vs nvarchar (Transact SQL)
Both of nchar and nvarchar are character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
Set Port and Firewall to enable remote connect to SQL Server 2005 or 2008 Express Database
After 2 articles Remote connect to SQL Server 2005 Express or SQL Server 2005 and Change SQL Server or SQL Server Express Authentication Mode, the SQL Server remote connection still have more stuff to talk about.
Change SQL Server or SQL Server Express Authentication Mode
When I installed SQL Server Express on my local PC, I used to select Windows Authentication mode only, but later I might have to change the Authentication mode for some reasons such as connect this SQL Server database from another computer and I don't want to set a new windows user account for another computer to access current computer, So I have to select SQL Server Authentication mode then.
Remote connect to SQL Server 2005 Express or SQL Server 2005
Microsoft SQL Server 2005 or Microsoft SQL Server 2005 Express do not allow remote access by default. If you want to remote connect or use another computer in your LAN, you have to configure on your SQL Server 2005 or SQL Server 2005 Express.