Dynamic query and Incorrect syntax near 'GO' error.

This is in codeproject... :)

Fun! hah?

Do you know which windows port is used by Microsoft SQL Server?

The default TCP port of Microsoft SQL Server default instance is 1433. But if the named instance is different than default instance then SQL server could use different port. Here I found a useful script to find out which TCP port is currently used by SQL server.


DECLARE @InstName VARCHAR(16) 
DECLARE @RegLoc VARCHAR(100) 

SELECT @InstName = @@SERVICENAME 

IF @InstName = 'MSSQLSERVER' 
BEGIN 
    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\' 
END 
ELSE 
BEGIN 
    SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\' 
END 

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'


Now, why it is important to know this TCP port number?
If you are planning to access database from remote computer and/or
if the firewall of your computer is opened then you have to allow that TCP port number as exception.

Thats it.

Happy Coding.

SQL Execution Error: Cannot insert explicit value for identity column in table.....

Sometimes we get a very interesting error when we try to insert a new row in a table. The error is like following:

Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF

Then we developers starts googling and find almost same solution. 

SET IDENTITY_INSERT TableName  On
insert into TableName  Values(1,1,1)
SET IDENTITY_INSERT TableName  OFF

This solution is ok. But before going to that solution we need to know in which context we have to set identity_insert on. 

The context is if we want to insert data to an Identity Specified column. (Generally we specify the primary key column identical.)

So, do we want to insert the data to a Identity column? If answer is “No” then check your sql query. I have seen many beginners do this wrong in their sql query. 

So, Happy Coding.

About a MS SQL Server Table Schema Change Warning - Saving changes is not permitted......

After changing a table schema sometimes we get a MS SQL Server warning saying that - Saving Changes is not permitted. The changes you have made require the following tables to be dropped and re-created......

To avoide this warning do the following:-

1. From Tool menu select Options.

2. Expand "Designers" node. Then uncheck "Prevent saving changes that require table re-creation". Then click OK.

For Visual Studio select Tools --> Options then under "Database Tools" under "Table and Database Designers" select "Prevent saving changes that require table re-creation"

That's all. Happy coding :)

What is the query processing order at SQL Server?

Does SQL Server execute the query from top to bottom, starting with the SELECT clause and working its way down? You might think that, but that is not how a query is processed in SQL Server at all. SQL Server logically processes a query in the following order:

(8) SELECT
(9) TOP
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) WITH
(7) HAVING
(10) ORDER BY

:-)




I collect this information from book Wrox Professional LINQ