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”
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]
Continue reading “SQL Server: Cannot Connect to WMI Provider, No Permission”
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.
Here we have a way:
Use the following script:
SELECT ‘DROP TABLE “‘ + TABLE_NAME + ‘”‘
WHERE TABLE_NAME LIKE ‘RC11%’
Continue reading “Delete multiple tables in SQL Server by table names”
There are 2 ways to check SQL Server database size:
1: Use script:
Get information about the total size of the database
Get information about the size of specific table
Continue reading “Check SQL Server Database Size”
Simply writing stored procedures isn’t enough to adequately secure your application. You should also consider the following potential security holes.
* Grant EXECUTE permissions on the stored procedures for database roles you want to be able to access the data.
* Revoke or deny all permissions to the underlying tables for all roles and users in the database, including the public role. All users inherit permissions from public. Therefore denying permissions to public means that only owners and sysadmin members have access; all other users will be unable to inherit permissions from membership in other roles.
* Do not add users or roles to the sysadmin or db_owner roles. System administrators and database owners can access all database objects.
* Disable the guest account. This will prevent anonymous users from connecting to the database. The guest account is disabled by default in new databases.
* Implement error handling and log errors.
* Create parameterized stored procedures that validate all user input. Treat all user input as untrusted.
* Avoid dynamic SQL unless absolutely necessary. Use the Transact-SQL QUOTENAME() function to delimit a string value and escape any occurrence of the delimiter in the input string.
above content from MSDN
Maybe you got error when you try to update your database SQL Server such as add a Non-Nullable field.
One possible reason is because the data table has already been inserted data. and you are trying to add a not nullable column to this table. so this is why you got the error.
Want to build a DB Access layer for multiple database types ?
Found 2 articles that about how to Implementing a Generic Data Access Layer in ADO.NET
1: Implementing a Generic Data Access Layer in ADO.NET Part 1, 2, 3
2: Implementing a Data Access Layer in C#