SQL SERVER : Rename all stored procedures in database

I was working on a project and got a requirement from client to rename all stored procedures with  some a specific work as prefix.

I referred some websites and finally found following command,

It renames all stored procedures as single execution.

ALTER DATABASE failed because a lock could not be placed on database 'linkoblast_DB'. Try again later. Msg 5069, Level 16, State 1, Line 4 ALTER DATABASE statement failed.

Sometimes when you try to take your database offline. As soon as you run the process, you may face issue with following error message:

ALTER DATABASE failed because a lock could not be placed on database 'linkoblast_DB'. Try again later.

Msg 5069, Level 16, State 1, Line 4 ALTER DATABASE statement failed.

Below is the simple solution to this error message:

SQL Server : Executing the query "CREATE TABLE [dbo].[###] (Column 0] v..." failed with the following error: "There is already an object named '###' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

When I was Importing data from a flat file, I met with the below error:

Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE [dbo].[###] (
[Column 0] v..." failed with the following error: "There is already an object named '###' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

At very first sight, I couldn't understand. But after some investigation found the resolution.

SQL SERVER - Login Failed for "NT-AUTHORITY\IUSER"

When your connection string has the words "Integrated Security=SSPI" and you try to access the database, you will get this error

Login Failed for "NT-AUTHORITY\IUSER".

Below are the steps to resolve the error:

• Open your Microsoft SQL Server Management Studio
• Connect to your SQL Server instance

How To : Drop all stored procedures from a database

Ever faced such a condition where you need to remove all stored procedures from your Database?
If you have to do the same, what will you do?  I am sure you will Google :)

I have got a link at MattBerther where you can find a script that helps you to remove all stored procedures from selected database.
Here is the copy of that script:

Enable Remote Connection on SQL Server 2008 Express

Update to my earlier post "A network-related or instance-specific error occurred while establishing a
connection to SQL Server. ....".

Now SQL Server 2008 Express doesn’t allow
remote connection on default installation as on SQL Server 2005
Express. So you have to enable it manually.

If
you’re trying to connect to SQL Server 2008 Express remotely without
enable remote connection first, you may see these error messages:

• “Cannot connect to SQL-Server-Instance-Name
  
  An error has occurred while establishing a connection to the server.
  When connecting to SQL Server 2005, this failure may be caused by the
  fact that under the default settings SQL Server does not allow remote
  connections. (provider: SQL Network Interfaces, error: 28 – Server
  doesn’t support requested protocol) (Microsoft SQL Server)”
  

Saving changes is not permitted The changes you have made require the following tables to be dropped and re-created. - SQL Server 2008

When you try to change something in SQL Server 2008 related to table, you may encounter a dialog saying the following message

Saving changes is not permitted The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Normally, whenever I faced this message earlier, I used to do the change using SQL command, but today I found something interesting.

Here are the abstracts from that:

New default in SQL Server's Management Tools: When you design a table in
a database and then try to make a change to a table structure that
requires the table to be recreated, the management tools will not allow
you to save the changes. Instead you'll be greeted by this friendly
dialog:

Notice that there's no option to save the changes - it's a hard rule
that is applied upon saving and you can get past this other than back
out of the dialog.

Rename a table in SQL

I ran to a problem today...I wanted to rename a table on the fly and use the new name in my queries.

I have found an useful command to rename a table in Microsoft SQL server.
The syntax and the command is as below:

exec sp_rename 'existing tablename' , 'new table name'
GO