Posts

Showing posts from 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

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

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.

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. CREATETABLE #Attributes ( id INT, name VARCHAR(50), valueVARCHAR(50), dt DATETIME); INSERTINTO #Attributes VALUES (1,'Field1', 'A', '20080701'); INSERTINTO #Attributes VALUES (2,'Field1', 'B', '20080701'); INSERTINTO #Attributes VALUES (3,'Field1', 'C', '20080701'); INSERTINTO #Attributes VALUES (3,'Field2', 'X', '20080701'); INSERTINTO #Attributes VALUES (4,'Field1', 'D', '20080702'); INSERTINTO #Attributes VALUES (4,'Field2', 'X', '20080702'); INSERTINTO #Attributes VALUES (5,'F…

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), valueVARCHAR(50), dt DATETIME); DECLARE @AttributesPivoted TABLE ( id INT, dt DATETIME, Field1 VARCHAR(50), Field2 VARCHAR(50) ); INSERTINTO @Attributes VALUES (1,'Field1', 'A', '20080701'); INSERTINTO @Attributes VALUES (2,'Field1', 'B', '20080701'); INSERTINTO @Attributes VALUES (3,'Field1', 'C', '20080701'); INSERTINTO @Attributes VALUES (3,'Field2', 'X', '20080701'); INSERTINTO @Attributes VALUES (4,'Field1', 'D', '20080702'); INSERT

C# Application in the System Tray

SQL Like statement with variable

DECLARE @myVariable aschar(2) SELECT @myVariable=column1 FROMTable2 WHERE id = 1 SELECT * FROMTable1 WHEREcolumn1 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. privatebool ReadyForReading(FileInfo file) { try { using(new FileStream(file.FullName,FileMode.Open,FileAccess.Read,FileShare.None)){} returntrue; }catch(Exception ex) { log.Warn(string.Format("Could not read file '{0}', probably writing in progress", file.Name),ex); returnfalse; } }

Refresh view after you change underlying table in SQL Server

EXECsp_refreshview 'view_name'

Set foreign key data based on data in another table

Update Table3
Setref = (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
UNIONSELECT 2, NULL
UNIONSELECT 3, NULL

INSERT INTO @Load_T1
SELECT 1,'Test1'
UNIONSELECT 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
Setref = (Select a.id from Table1 a, Table2 b
Where a.name = b.name and b.id = Table3.ref)

Disable identity insert in SQL Server

SETIDENTITY_INSERT dbo.table_name OFFINSERTINTO table_name( [Id] , [Name]) SELECT 1 , 'Name1' SETIDENTITY_INSERT dbo.table_name ONGO

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.logBCM 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

INSERTINTO table_name SELECT 'value1' UNIONSELECT 'value2' UNIONSELECT 'value3'

Case sensitive query in SQL Server

selectdistinct column_name collate latin1_general_cs_as from table_name

Update column from NOT NULL to NULL in SQL Server

ALTERTABLE table_name ALTERCOLUMN column_name VARCHAR(255) NULL

Add/Drop column in SQL Server

Add new column to existing table IFNOTEXISTS( SELECT 1 FROM information_schema.columns WHERE table_name = 'table_name' AND column_name = 'column_name' ) BEGINALTERTABLE table_name ADD column_name VARCHAR(255) NULLENDGORemove column from the table IFEXISTS( SELECT 1 FROM information_schema.columns WHERE table_name = 'table_name' AND column_name = 'column_name' ) BEGINALTERTABLE table_name DROPCOLUMN column_name ENDGO

Rename column in SQL Server

EXECsp_rename 'table_name.[column_name]', 'new_column_name', 'COLUMN'

Rename table in SQL Server

EXECsp_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

IFNOTEXISTS( SELECT 1 FROM information_schema.table_constraints WHERE table_name = 'table_name' AND constraint_name = 'contraint_name' ) ALTERTABLE [dbo].[table_name] ADDCONSTRAINT contraint_name FOREIGNKEY ( [Id] ) REFERENCES [table2_name] ( [Id] ) GOTo allow cascade delete update the last line ) REFERENCES [table2_name] ( [Id] ) ONDELETECASCADEGOTo remove constraintALTERTABLE table_name DROPCONSTRAINT [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