Monday, 23 February 2009

How to Insert data to a table with only Identity Column

DECLARE @Table1 TABLE(
  Column1 INT IDENTITY(1,1) PRIMARY KEY
)

INSERT INTO @Table1 DEFAULT VALUES

SELECT * FROM @Table1

Thursday, 19 February 2009

Windows Remote Desktop on multiple screens

To get the multiple screen support on Windows XP download the latest (6.0 or above) 'Remote Desktop Connection' software from Microsoft website. And run the following command mstsc /span /v:"pc-name" Here you can find more info about mstsc command

Tuesday, 17 February 2009

Could not find file 'Microsoft.Windows.CommonLanguageRuntime, Version=2.0.50727.0'

Could not find file 'Microsoft.Windows.CommonLanguageRuntime, Version=2.0.50727.0' If you get this error go through your projects in the solution and uncheck 'Enable ClickOnce Security Settings' on Properties/Security. This is sometimes happening when you by mistake click Publish on the project.

Friday, 13 February 2009

SQL Server pivoting data with dynamic sql

Example of how to dynamically generate sql statement to PIVOT data in SQL Server 2005 and above. You can put this code into stored procedure and use it instead of a view. I'm not aware of any alternative to that approach as you can't call stored procedure from the view and you can't dynamically define return table columns for table-valued functions.
CREATE TABLE #Attributes (
id INT,
name VARCHAR(50),
value VARCHAR(50),
dt DATETIME);
INSERT INTO #Attributes VALUES (1,'Field1', 'A', '20080701');
INSERT INTO #Attributes VALUES (2,'Field1', 'B', '20080701');
INSERT INTO #Attributes VALUES (3,'Field1', 'C', '20080701');
INSERT INTO #Attributes VALUES (3,'Field2', 'X', '20080701');
INSERT INTO #Attributes VALUES (4,'Field1', 'D', '20080702');
INSERT INTO #Attributes VALUES (4,'Field2', 'X', '20080702');
INSERT INTO #Attributes VALUES (5,'Field1', 'E', '20080702');
INSERT INTO #Attributes VALUES (5,'Field2', 'X', '20080702');
SELECT * FROM #Attributes
DECLARE @cols NVARCHAR(2000)
DECLARE @colsmax NVARCHAR(2000)
SELECT 
  @cols = COALESCE(@cols + ',[' + Name + ']','[' + Name + ']'),
  @colsmax = COALESCE(@colsmax + ',MAX([' + Name + ']) ['+ Name +']','MAX([' + Name + ']) ['+ Name +']')
FROM    #Attributes
GROUP BY Name
ORDER BY Name
SELECT @cols
SELECT @colsmax
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT b.*
FROM
(SELECT id, dt, '+
@colsmax +'
   FROM (
  SELECT id, dt, name, value
  FROM #Attributes 
   ) AS a
   PIVOT ( 
   max(Value) FOR Name IN ( '+ @cols +' )
) AS PivotTable
   GROUP BY id, dt) b'
SELECT @query
EXECUTE(@query)
DROP TABLE #Attributes

SQL Server pivoting data

Example of how to use PIVOT/UNPIVOT keyword in SQL Server 2005 and above.
DECLARE @Attributes TABLE (
id INT,
name VARCHAR(50),
value VARCHAR(50),
dt DATETIME);
DECLARE @AttributesPivoted TABLE (
id INT,
dt DATETIME,
Field1 VARCHAR(50),
Field2 VARCHAR(50)
);
INSERT INTO @Attributes VALUES (1,'Field1', 'A', '20080701');
INSERT INTO @Attributes VALUES (2,'Field1', 'B', '20080701');
INSERT INTO @Attributes VALUES (3,'Field1', 'C', '20080701');
INSERT INTO @Attributes VALUES (3,'Field2', 'X', '20080701');
INSERT INTO @Attributes VALUES (4,'Field1', 'D', '20080702');
INSERT INTO @Attributes VALUES (4,'Field2', 'X', '20080702');
INSERT INTO @Attributes VALUES (5,'Field1', 'E', '20080702');
INSERT INTO @Attributes VALUES (5,'Field2', 'X', '20080702');
SELECT * FROM @Attributes
INSERT INTO @AttributesPivoted
-- Pivot
SELECT id,
dt,
MAX(Field1) AS Field1,
MAX(Field2) AS Field2
FROM @Attributes
PIVOT
(MAX(value) FOR name IN
([Field1], [Field2])) AS P
GROUP BY id, dt;
SELECT * FROM @AttributesPivoted
-- Unpivot
SELECT id, name,value,dt
FROM
(SELECT id, dt, Field1, Field2
 FROM @AttributesPivoted) p
 UNPIVOT
 (value FOR name IN 
	([Field1],[Field2])
) AS unpvt

Wednesday, 11 February 2009

C# Application in the System Tray

C# Tip: Placing Your C# Application in the System Tray

Tuesday, 3 February 2009

SQL Like statement with variable

DECLARE @myVariable as char(2)
SELECT @myVariable=column1
FROM Table2 
WHERE id = 1
SELECT * FROM Table1
WHERE column1 like '%'+@myVariable+'%'

C# Check if the file is ready for reading

This code checks if the file is ready for reading and that writing process is not having a lock on the file anymore.
private bool ReadyForReading(FileInfo file)
{
  try
  {
    using(new FileStream(file.FullName,FileMode.Open,FileAccess.Read,FileShare.None)){}                
    return true;
  }catch(Exception ex)
  {
    log.Warn(string.Format("Could not read file '{0}', probably writing in progress", file.Name),ex);
  return false;
  }
}

Monday, 2 February 2009

Refresh view after you change underlying table in SQL Server

EXEC sp_refreshview 'view_name'

Set foreign key data based on data in another table

Update Table3
Set ref = (Select a.id from Table1 a, Table2 b
Where a.name = b.name and b.id = Table3.ref)
DECLARE @T1 TABLE(
id INT,
v VARCHAR(50)
)

DECLARE @Load_T1 TABLE(
id INT,
v VARCHAR(50)
)

INSERT INTO @T1
SELECT 1,NULL
UNION SELECT 2, NULL
UNION SELECT 3, NULL

INSERT INTO @Load_T1
SELECT 1,'Test1'
UNION SELECT 3, 'Test3'

SELECT * FROM @T1
SELECT * FROM @Load_T1

UPDATE @T1
SET t.v = l.v
FROM @T1 t JOIN @Load_T1 l ON t.id = l.id

SELECT * FROM @T1


UPDATE @T1 t
SET t.v = (SELECT l.v
FROM @Load_T1 l JOIN @T1 t ON l.id = t.id
AND t.id = @T1.id)

SELECT * FROM @T1


Update Table3
Set ref = (Select a.id from Table1 a, Table2 b
Where a.name = b.name and b.id = Table3.ref)

Disable identity insert in SQL Server

SET IDENTITY_INSERT dbo.table_name OFF
INSERT INTO table_name( [Id] , [Name])
SELECT 1 , 'Name1'
SET IDENTITY_INSERT dbo.table_name ON
GO

Get table details from SQL Server information schema

select * from information_schema.columns WHERE table_name = 'table_name'

BCP data to/from SQL Server

BCP data from database to file

"c:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" database_name..table_name out c:\data_file.csv -c -q -b 1024 -S server_name -U user_name -P password -e c:\\log_file.log

BCM data from file to database

"c:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" database_name..table_name in c:\data_file.csv -c -q -b 1024 -S server_name -U user_name -P password -e c:\\log_file.log

Multiple insert statement in SQL

INSERT INTO table_name
      SELECT 'value1'
UNION SELECT 'value2'
UNION SELECT 'value3'

Case sensitive query in SQL Server

select distinct column_name collate latin1_general_cs_as from table_name

Update column from NOT NULL to NULL in SQL Server

ALTER TABLE table_name ALTER COLUMN column_name VARCHAR(255) NULL

Add/Drop column in SQL Server

Add new column to existing table

IF NOT EXISTS( SELECT 1 FROM information_schema.columns WHERE table_name = 'table_name' AND column_name = 'column_name' )
BEGIN
  ALTER TABLE table_name ADD column_name VARCHAR(255) NULL
END
GO

Remove column from the table

IF EXISTS( SELECT 1 FROM information_schema.columns WHERE table_name = 'table_name' AND column_name = 'column_name' )
BEGIN
  ALTER TABLE table_name DROP COLUMN column_name
END
GO

Rename column in SQL Server

EXEC sp_rename 'table_name.[column_name]', 'new_column_name', 'COLUMN'

Rename table in SQL Server

EXEC sp_rename 'old_tablename', 'new_tablename'

Execute SQL Server query from command line

sqlcmd -S server_name -i test.sql -o test.sql.out

SQL Add/Remove constraints, foreign key

IF NOT EXISTS( SELECT 1 FROM information_schema.table_constraints WHERE table_name = 'table_name' AND constraint_name = 'contraint_name' )
ALTER TABLE [dbo].[table_name] 
ADD CONSTRAINT contraint_name FOREIGN KEY 
 (
  [Id]
 ) REFERENCES [table2_name] (
  [Id]
 ) 
GO

To allow cascade delete update the last line

 ) REFERENCES [table2_name] (
  [Id]
 ) ON DELETE CASCADE 
GO

To remove constraint

ALTER TABLE table_name DROP CONSTRAINT [constraint_name]

Unzip bz files

bunzip2 <filename.bz>

linux split line by specific character

Splits the line in the file by comma and shows columns 3 to 10 grep file.txt | cut -d ',' -f 3-10

linux ps command with full path

ps -auxww

unix/linux tar/untar quick guide

UNIX TAR/UNTAR Creating archive: tar -cf archive.tar myDirectories/ Listing the contents of an archive: tar -tf archive.tar Extracting all files from an archive: tar -xf archive.tar

Send email from unix/linux

cat | mail

CSV tag files

cvs rtag -F mytag myfile.txt