How to get list of all stored procedures from SQL database

If you want to get list of all stored procedures from the database you are working on, just fire this query in one of the query window and all procedure names will be returned.

SELECT DISTINCT object_name(id) from sys.syscomments

You can further customize the query and filter stored procedures based on its name like the following query will return storec procedures that has 'add' in its name:

SELECT DISTINCT object_name(id) from sys.syscomments WHERE TEXT like '%add%'

Cannot insert explicit value for identity column in table

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'table1' when IDENTITY_INSERT is set to OFF.

Resolution:
run this query before inserts and then run it with mode set to 'OFF' once insert is done

SET IDENTITY_INSERT table1 ON

How to truncate log file in SQL Server

I was once dealing with a simple sql server project which had a database of around 25 MB but its log file was 100+ MB and I had to download it from the production server to development machine for testing.

This is one of the situations when we should truncate the log file. Here's a code snippet which I have used with SQL Server 2005 and which works.

Say you have a database called ABCD and its files are ABCD.mdf and ABCD_log.ldf

Now log into sql server 2005, open a new query window in ABCD database, paste and run following commands

DBCC SHRINKFILE('ABCD_log', 1)
BACKUP LOG ABCD WITH TRUNCATE_ONLY
DBCC SHRINKFILE('ABCD_log', 1)
GO

Make sure to put in the name according to your database.
Also note the number '1' means log will be truncated to 1 MB.

A network-related or instance-specific error occurred while establishing a connection to SQL Server

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

 If you get this error, firstly check that if your database is on a separate machine, you are able to ping the machine. Especially make sure that if you are using ip address in the connectionstring for server, ping it ,you should get reply.

Also ensure your firewall is not blocking the connection

In anycase, try changing connection string (if you have options) and see if the error disappears!

A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - An unexpected network error occurred.)

You may encounter this error while working with MS Sql Server Management Studio or using MS Sql in the code as data layer.
There are number of reasons because of which you might get this error, I'm listing few of them here, check if it applies to you.
* Ensure that you are connecting to sql using proper method (names pipes / shared memory )
* Check if you are able to ping the machine where sql server is installed from your development machine (where this error is coming), chances are that due to network problems (including loose cable connections) you might not be able to connect to the database!

A comment would be helpful incase this post solved your problem :)