Saving Changes Is Not Permitted On SQL Server 2008 Management Studio. How To Solve?

Saving changes is not permitted occurs when doing alter table (table structure is changed):

1. Change data type on existing columns
2. Or change allow nulls on existing columns

 
To allow you to save changes after you alter table, do disable prevent changes:
 

1. Open Microsoft SQL Server Management Studio 2008
2. Click Tools menu options, then click Options 
3. Select Designers 
4. Uncheck "prevent saving changes that require table re-creation" option
5. Click OK
6. Try to alter your table
7. Your changes will performed as desired  

Error while installing SQL Server 2005 – Native Client cannot be found

Recently while installing SQL Server 2005 Standard Edition on a “fresh” server I found that crazy thing just wouldn’t install!

I was getting the following error:

“An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package ‘sqlncli.msi'”

Most cryptic… Considering my installation package is the disc given to me by Microsoft and I’m positive that the sqlncli.msi file is right there.

However, the actual issue appears to be as simple as an inability to upgrade an old Native Client edition.

To resolve, use the Add / Remove Programs panel to uninstall an existing SQL Server Native Client installation and you should be good to roll.


Note that this appears to be common for other editions of SQL Server 2005 too.

SQL SERVER – Enable Login – Disable Login using ALTER LOGIC – Change name of the ‘SA’

In SQL SERVER 2005, all the login (including ‘sa’ ) can be enabled and disabled using ALTER LOGIN command.

To disable ‘sa’ login:
ALTER LOGIN sa DISABLE
GO
To enable ‘sa’ login:
ALTER LOGIN sa ENABLE
GO
Also for additional security (prevent educated guess from hackers) the name of the ‘sa’ account can be changed.
ALTER LOGIN [sa] WITH NAME = [AdminUser]
GO

SQL SERVER – 2005 Query Analyzer – Microsoft SQL SERVER Management Studio

Following may be very simple to some and helpful to other type of question. I have seen this in my server log as well as this has been always first question in my Developer Team.

Where is SQL SERVER 2005 Query Analyzer?
SQL SERVER 2005 has combined Query Analyzer and Enterprise Manager into one Microsoft SQL SERVER Management Studio (MSSMS). To see the familiour Query Analyzer Window follow the image below.

SQL SERVER – Fix : Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection

Some errors never got old. I have seen many new DBA or Developers struggling with this errors.

Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.

Fix/Solution/Workaround:
Change the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)”
to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.

Run following script in SQL Analyzer to change the authentication

LOGIN sa ENABLE
GOALTER LOGIN sa WITH PASSWORD = ''GO

OR

In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties. On the General page, you may have to create and confirm a password for the sa login. On the Status page, in the Login section, click Enabled, and then click OK.

Create exceptions in Windows Firewall

These steps apply to the version of Windows Firewall that is included in Windows XP Service Pack 2 (SP2) and in Windows Server 2003. If you are using a different firewall, see your firewall documentation for more information. 

If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 are blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you have to identify the correct instance ID. To obtain an instance ID, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe, click Open, and then click OK. 
   
   Note The path might be different, depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

Create an exception for the SQL Server Browser service in Windows Firewall

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK. 
   
   Note The path might be different, depending on where SQL Server 2005 is installed.

Enable the SQL Server Browser service

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you have to enable the SQL Server Browser service to allow for remote connections. For example, SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS. You only have to enable the SQL Server Browser service one time, regardless of how many instances of SQL Server 2005 you are running. To enable the SQL Server Browser service, follow these steps.

Important These steps may increase your security risk. These steps may also make your computer or your network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to, or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you choose to implement this process, take any appropriate additional steps to help protect your system. We recommend that you use this process only if you really require this process.

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic forStartup type, and then click Apply. 
   
   Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.

Note When you run the SQL Server Browser service on a computer, the computer displays the instance names and the connection information for each instance of SQL Server that is running on the computer. This risk can be reduced by not enabling the SQL Server Browser service and by connecting to the instance of SQL Server directly through an assigned TCP port. Connecting directly to an instance of SQL Server through a TCP port is beyond the scope of this article. For more information about the SQL Server Browser server and connecting to an instance of SQL Server, see the following topics in SQL Server Books Online:

• SQL Server Browser Service
• Connecting to the SQL Server Database Engine
• Client Network Configuration

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

You have to enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply. 
   
   Note Click OK when you receive the following message:
   Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, clickStop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

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

Unattended SQL Management Studio Express 2005 Installtion via batch file

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

SQLServer2005_SSMSEE.msi /qb addlocal=all InstanceName=alfa\SQLEXPRESS DisableNetworkProtocols=2 SECURITYMODE=SQL SAPWD=abc123
@cls
EXIT

i tried it and found very use full to me

here alfa is my server name and sapwd is my passoword

SQL 2005 Set disable failed for server protocol tcp

Open SQL Configuration manager
Under SQL native Claint Configuration
Enable TCP,Named Pipes, VIA


Then open

1. Go to server machine and log in to Sql Server Management Studio.
2. Right click on server -> select Properties

3. Click on connections -> Check whether 'Allow remote connections to this server'.

4. If this option is not checked then select the option and try to connect from other system.

5. Also check the Security tab -> Server authentication -> should be 'SQL Server and Windows Authentication mode'

If you are using Windows 7 then

When you open the SQL Server Configuration, right click and select RUN AS ADMINISTRATOR if you are on Windows 7 before making the above mentioned changes.

Error: 5123 CREATE FILE encountered operating system error 5A(Access denied.)

set the Operating system permission on that .mdf file to full permission to 'Everyone'  in new server.

SQL Server Setup failed to obtain system account information for the ASPNET

Trying to setup MSSQL 2005 Developer version in my new machine OS Windows XP Profesional SP3, when come to installation point, error message below appear:
nder Add/Remove Programs, i have Framework 1.1, 2.0 and 3.0 installed.

What next? After surf around, here is the solution:

Steps:

1. Click on Start -> Run

2. Type in cmd and click OK

3. Command prompt screen will appear. go to"C:\Windows\Microsoft.NET\Framework\v1.1.4322" folder by using command cd C:\Windows\Microsoft.NET\Framework\v1.1.4322 and hit enter. Please refer screen below:

4. Type in aspnet_regiis -i and hit enter.

5. Screen below will be shown and dotnet user installed.

Try to install MSSQL 2005 again. It's work. Problem solved.

This command was only recognized in .NET framework versions 2 and lower. I ran it for the 3.0 and 3.5 version folders and nothing happened. Well, actually I got an error message saying that aspnet_regiis -i was not a valid/recognized command. However, after having run this command in the Version 2 & lower folders, my SQL Install proceeded without further error.