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.

Actually we want to stop entire old SQL Server system for the new installation, but there is no such a way to stop entire SQL Server system but you will have to uninstall the old one, so you can just stop a SQL Server instance which you have created in the previous SQL Server edition.

Continue reading “How to Stop a SQL Server Instance”

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:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manager SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]

image

Continue reading “SQL Server: Cannot Connect to WMI Provider, No Permission”

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.

We know we can use LTRIM and RTRIM in Transact-SQL, Let us try to use in SQL Server.

Firstly, We need to generate sql scripts to change sql data table names, in SQL Server, we have to use the procedure sp_rename to change a table name.

The following is for our case: Generate scripts for trim all table names with ‘RC’ prefix (SysDB is our database name):

SELECT ‘EXEC sp_rename ‘
+ ”” + QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) + ”’, ‘
+ ”” + LTRIM(RTRIM(TABLE_NAME)) + ””
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’ AND TABLE_CATALOG=’SysDB’
and TABLE_NAME like ‘RC%’

Continue reading “How to Change Multiple Data Table Names in SQL Server”

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.

SQL Server Management Studio provides us a function named Import Data…, we can find this options by right click a database.

1: Right click the target database which you want to add data tables, select Tasks, then Import Data…

image

Continue reading “Copy Data Tables From Another Database 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.

We know SQL Server Express edition has limited features. What we can do when we meet the file size issue are the following steps:

1: Delete some of data tables, please check here which we tried and confirm working well for SQL Server 2008R2;

Actually in this step we can select all generated result items, and they copy to working area to execute.

2: Restart related software in your system which connected the database (this step is optional, depends on how your software designing and implemented, but for our case, for our old software, we should restart one program which connected to database.)

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.

We collect part of limitations of SQL Server Express 2012 as below:

Continue reading “Limitations of SQL Server 2012 Express”

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.

Continue reading “Change Sql Server Database Name with Related Filenames Changing”

SqlCmd vs oSql vs iSql

SqlCmd, oSql and iSql actually looked like the same product.

iSql was the 1st, which used for more older SQL Server before SQL Server 2000 version, oSQL seems most of time used for SQL Server 2000, later , from SQL Server 2005, we found MS gave a newer command: SqlCmd.

That’s it.

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.

You can use the following script to check your current server name:

select @@SERVERNAME

Continue reading “Update Local SQL Server ‘ServerName’ after Changed Computer Name”