Cannot find template file for new query (C:\Program Files\Microsoft SQL Server\90\Tools\ Binn\VSShell\Common7\ IDE\sqlworkbenchprojectitems\Sql\ SQLFile.sql)

Just a day ago while playing with SQL Server I suddenly faced new kind of error, which I have never seen before. This error happens when clicked on New Query on SQL Server Management Studio.

“Cannot find template file for new query (C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql\SQLFile.sql) “

Fix/Workaround/Solution:
It was pretty new to me and I could not find any solution for this online. I had SQL Server installed on another machine as well. I copied SQLFile.sql from location mentioned above from another computer to my computer where error was happening. After this when I clicked on New Query it worked just like before.

You can download SQLFile.sql (Right Click and Download)from here and put it in the folder where SQL Server is installed. If you have installed SQL Server on default location you should put SQLFile.sql in the folder C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql.

SQL SERVER – Fix : Error: 15372 Failed to generate a ser instance od SQL Server due to a failure in starting the process for the user instance. The connection will be closed

Solution for this error is fairly simple.

Fix/Workaround/Solution:

For XP:
Delete the directory “c:\Documents and Settings\username\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS “

For Vista:
Delete the directory “c:\Users\\AppData\Local\Microsoft\Microsoft Sql Server Data\SQLEXPRESS”

Reboot the system.

SQL SERVER – FIX : ERROR : (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: )

1) SQL Server should be up and running.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Services, and check if SQL Server service status is “Running”.

In addition, ensure that your remote server is in the same network. Run “sqlcmd -L” in your command prompt to ascertain if your server is included in your network list. You can even find tutorial for the same here SQL SERVER – Find All Servers From Local Network – Using sqlcmd – Detect Installed SQL Server on Network.

I have confronted numerous situations when these nerve-wracking errors crop up, and most of the time when I try to troubleshoot I notice that SQL Server services are neither running nor installed. If SQL Server is not installed as default instance SQL Server Browser should be running together with it; we will explore this further in Topic 5.

2) Enable TCP/IP in SQL Server Configuration

When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP

Right Click on TCP/IP >> Click on Enable

You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

3) Open Port in Windows Firewall

Windows Firewall is very efficacious in protecting the Operating system from all sorts of malicious attacks. By default, many of the ports and services are refrained from running byfirewall. Time and again, SQL Server ports are not open in firewall as well. All the ports on which SQL Server is running should be added to exception and firewall should filter all the traffic from those ports. As described, by default SQL Server runs on port 1433, but ifdefault port is changed then the new port should be added to exception. If SQL Server has named instance (another instance besides default instance) is installed, SQL Server browsershould also be added to the exception, as described in Step 7.

Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions  >> Add Port

Make the following entries in popup “Add a Port” and click OK.

Name : SQL
Port Number: 1433
Protocol: Select TCP

4) Enable Remote Connection

Enabling remote connection is another important, yet oft-neglected step that is frequently missed by database administrators while setting up SQL Server. If this feature is turned off SQL Server will function smoothly on local machine, but it will let another server connect to it remotely. By default this feature is ON in SQL Server 2008.

Right click on the server node and select Properties.

Go to Left Tab of Connections and check “Allow remote connections to this server”

5) Enable SQL Server Browser Service

If SQL Server is not installed as default instance but instead installed as named instance and also if there is no specific TCP/IP port configured, it will give rise to the error that is being discussed in this article. If SQL Server Browser service is enabled, it will allow the server to be connected through dynamic TCP/IP port. Enabling this service is a one-time process, as on enabling it once it will apply to all the instances installed on the same server.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> SQL Server Browser

Right Click on SQL Server Browser >> Click on Enable

6) Create exception of sqlbrowser.exe in Firewall

As elucidated in Step 6, sqlbrowser service needs to be enabled for named instance. Windows Firewall may prevent sqlbrowser.exe to execute. So, it is imperative to addexception for the same in windows firewall.

Search for sqlbrowser.exe on your local drive where SQL Server is installed. Copy the path of the sqlbrowser.exe like C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exeand create the exception of the file in Firewall, as delineated in Step 3.

7) Recreate Alias

It is getting quite common to create alias of SQL Server and use it in application. This will ensure that in future if any physical SQL Server has to be moved, it will not be required to change any code or connection string. You can simply create alias with the same name pointing to different SQL Server and it will start working instantaneously. I have observed that a couple of times due to internal error while recreating alias this error was fixed.

Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Native Client 10.0 Configuration >> Aliases

Delete the alias that is giving problem and recreate it with identical parameters.

I have tried my best to include all the methods of fixing this error and if I have missed any, please leave a comment and I will be very glad to include them here. I have put in my effort to encompass this issue in one article that needs to be refereed when any connection error comes up.

How to backup and restore database on Microsoft SQL Server 2005

Introduction

This post will give you a brief introduction of SQL Server backup. But in the example below, I will show a step-by-step database backup from SQL Server 2005 and restore the database to another SQL Server 2005 Express Edition using Microsoft SQL Server Management Studio.

Step-by-step

Backup a database.

Now I will backup AdventureWorks database on BKKSQL2005 which runs Microsoft SQL Server 2005 to a file.

1. Connect to source server. Open Microsoft SQL Server Management Studio and connect to BKKSQL2005.
2. Right-click on the AdventureWorks database. Select Tasks -> Backup…
   
3. On Back Up Database window, you can configure about backup information. If you’re not familiar these configurations, you can leave default values. Here are some short descriptions.
   1. Database – a database that you want to backup.
   2. Backup type – you can select 2 options: Full and Differential. If this is the first time you backup the database, you must select Full.
   3. Name – Name of this backup, you can name anything as you want.
   4. Destination – the file that will be backup to. You can leave as default. Default will backup to “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
   5. Click OK to proceed backup.
   
4. Wait for a while and you’ll see a pop-up message when backup is finished.
   
5. Browse to the destination, you’ll see a backup file (.bak format) which you can copy to other server for restore in the next step. Default backup directory is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup”.
   

Restore the database.

Next, I will restore the AdventureWorks database from a file that I’ve created above to BK01BIZ001 which runs Microsoft SQL Server Express Edition.

1. Copy the backup file from source server to destination server. I’ve copied into the same directory as source server.
   
2. Connect to destination server. Open Microsoft SQL Server Management Studio Express and connect to BK01BIZ001.
3. Right-click on Databases. Select Restore Database…
   
4. Restore Database window appears. On Source for restore, select From device and click [...] buttton to browse file.
5. On Specify Backup, ensure that Backup media is “File” and click Add.
   
6. On Locate Backup File, select the backup file. This is the backup file that was created in Backup a database section and was copied to this server. Click OK. OK.
   
7. Back to Restore Database window.
   1. On Destination for restore, select “AdventureWorks”.
      Note: If you haven’t added the backup file on Source before (step 4-6), you won’t see the database name on Destination.
   2. On Source for restore, check the box in front of the backup name (in Restore column).
   3. Click OK.
   
8. Wait until restore finish and there’ll be a pop-up message notify.
   
9. Now you’ll see the restored database on the destination SQL Server.
   

REMOVE DUPLICATE ROW FROM SQL 2005

WITH CTE (FIELDNAME, DuplicateCount)
AS
(
SELECT  FIELDNAME ,
ROW_NUMBER() OVER(PARTITION BY  FIELDNAME  ORDER BY  FIELDNAME ) AS DuplicateCount
FROM TABLENAME
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

REMOVE NULL FROM A SPECIFIC FIELD OF SQL 2005

Update TABLENAME set FIELDNAME=' ' where FIELDNAME is null

Unattended SQL Express 2005 Installtion via batch file

Open text file and type the following script and save as SQL2005Express.bat

SQLEXPR-2005.EXE /qb addlocal=all InstanceName=alfs\SQLEXPRESS DisableNetworkProtocols=2 SECURITYMODE=SQL SAPWD=abc123
@cls
EXIT


I tested it and found very use full to me

here alfa is my server name and sapwd is my password