Archive

Archive for the ‘Sql Server’ Category

Comma separated values in mysql


MY SQL:

GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.

Syntax GROUP_CONCAT([DISTINCT] exp [ORDER BY sorting] [SEPARATOR ‘sep’])
Quick Example SELECT GROUP_CONCAT(city) FROM cities GROUP BY state;
Separator Comma (,) by default, ” eliminates separator
NULL Values Skipped
Max Length 1024 by default, specified by group_concat_max_len system variable

Sample:

CREATE TABLE cities
   (
     city VARCHAR(70),
     state CHAR(2)
   );
 
   INSERT INTO cities VALUES ('San Francisco', 'CA');
   INSERT INTO cities VALUES ('San Diego', 'CA');
   INSERT INTO cities VALUES ('Los Angeles', 'CA');
 
   INSERT INTO cities VALUES ('Austin', 'TX');
   INSERT INTO cities VALUES ('Houston', 'TX');

   SELECT state, GROUP_CONCAT(city)
   FROM cities
   GROUP BY state;
The result:
CA San Francisco,San Diego,Los Angeles
TX Austin,Houston
SQL Server:
SELECT state,
STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'')
FROM cities c
GROUP BY state

Hope this help !
Rahul Bhatia

SQL: Alter script to change the Primarykey Column datatype

February 23, 2015 Leave a comment

–This will drop the primary key constraint
ALTER TABLE [pTARGET_TYPE] DROP CONSTRAINT PK_TARGET_TYPE

–Alter datatype
ALTER TABLE [TARGET_TYPE] ALTER COLUMN [TARGET_TYPE_ID] INT NOT NULL

–Add Primary key constraint
ALTER TABLE [TARGET_TYPE] ADD CONSTRAINT PK_TARGET_TYPE PRIMARY KEY ([TARGET_TYPE_ID])

SQL Server Setup Issue : How to resolve Reporting Services Catalog Database File Existence Failed or Temporary Database File Existence failed

January 28, 2015 Leave a comment

When you Uninstall SQl server some of the traces will be left behind one of them are Reporting serivces catalogs in order to fix this issue follow the screen shots below

You can delete those file from following location

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

  • ReportServer.mdf
  • ReportServer_log.LDF
  • ReportServerTempDB.mdf
  • ReportServerTempDB_log.LDF.

Hope this help !

 

Categories: Sql Server

SQL Error : Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

November 26, 2014 1 comment

Error :

Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Following setting required for the above problem

[1] ALTER DATABASE [$(DatabaseName)] SET ALLOW_SNAPSHOT_ISOLATION ON;

[2]  ALTER DATABASE [$(DatabaseName)]SET READ_COMMITTED_SNAPSHOT ON;

 

Hope this help !

MySQL Error: The user specified as a definer does not exist

October 17, 2014 Leave a comment

Error :

the user specified as a definer (‘root’@’%’) does not exist

 

Solution :

grant all on *.* to ‘root’@‘%’ identified by ‘password’ with grant

 option;
Categories: Sql Server Tags: , , ,

The file is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. Restore database is terminating abnormally.

February 18, 2014 2 comments

Problem : The file is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. Restore database is terminating abnormally.

The file “<file.mdf>” is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. Restore database is terminating abnormally.

Cause:

This is because, a read/write database [.mdf and .ldf] files cannot be created on a compressed drive/directory. [Compressed directory/drive is in blue color.]

Solution:

-De-compress the drive/data directory by Right click on “DATA” directory
-Click Advanced
-Uncheck “Compress contents to save disk space”

Hope it helps!

 

Enable Intelligence in SQL Server 2005, 2008

June 13, 2013 2 comments

As we know SQL Server 2000, 2005, 2008 and 2008R2 doesn’t provide intellisense like Visual Studio by default. While writing SQL statements/queries, we need to recall the database table structure (including columns, their data types, size) and other database objects structure like stored procedure, view and functions again and again. Sometimes it becomes tedious task.

Also there is a common and still active issue that “The IntelliSense feature in SSMS 2008 R2 may stop working after you install Visual Studio 2010 SP1”

Until a good solution found for above problem we can use following external tools for Enabling Intelligence in SQL Server

[1] SQL Complete : http://www.devart.com/dbforge/sql/sqlcomplete/

 

[2] SQL Developer Bundle : http://www.red-gate.com/products/sql-development/sql-developer-bundle/

Hope it helps!

Categories: Sql Server

Visual Studio – Database Project Not Loading


Have you ever created a Database project and after some time you are not able to load it in the Visual Studio? Probably you might be getting following error:

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: 26 – Error Locating Server/Instance Specified)

The database project requires an active database connection to load at design time. Probably it uses that to compile/verify scripts. The problem here is Visual Studio is not able to connect to any such instance. Following are few solutions (these worked for me)

  1. Make sure the instance to which you want to connect is running and allows remote connections.
  2. After performing step 1, if it is still not loading then you may need to start the SQL Browser Service. Make sure it’s running.
  3. If the problem is still there, it has to be fixed from Visual Studio itself. Actually database project do not store to which instance it can connect. It’s a Visual Studio setting.In VS2008 go to Tool > Options > Database Tools > Design-time Validation Database > Connection Options. Specify your instance name here like “\SQLEXPRESS”

Also If you upgraded your database project from 2005 to 2008 and if  you try to open the 2008 database project solution file then you may get the following error:

Coluld not load file or assembly ‘System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken = 89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

To resolve this you need to install SQL Server CE 3.5 SP1 from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9&displaylang=en

Hope it helps !

Cannot drop database because it is currently in use.


This is a very common error when DROP Database is command is executed and the database is not dropped.

The following commands will raise above error:
USE AdventureWorks;
GO
DROP DATABASE AdventureWorks;
GO

Solution:
The following commands will not raise an error and successfully drop the database:

USE Master;
GO
DROP DATABASE AdventureWorks;
GO

If you are still getting the error after you try using
use master
go
drop database (databaseName)
go

Close SQL Server Management Studio completely. Open it again and connect as normal. Now you will be able to drop the database with
use master
go
drop database (databaseName)
go

Hope it helps!