ITDevSpace - Development Tips & Tricks

DECLARE @Table1 TABLE( Column1 INT IDENTITY(1,1) PRIMARY KEY ) INSERT INTO @Table1 DEFAULT VALUES SELECT * FROM @Table1

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

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 &

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 ', '

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

DECLARE @myVariable as char (2) SELECT @myVariable= column 1 FROM Table 2 WHERE id = 1 SELECT * FROM Table 1 WHERE column 1 like ' % '+@myVariable+' % '

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 ; } }

EXEC sp_refreshview ' view_name '

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 )

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

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

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

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

select distinct column_name collate latin1_general_cs_as from table_name

ALTER TABLE table_name ALTER COLUMN column_name VARCHAR (255) NULL

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

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

EXEC sp_rename ' old_tablename ', ' new_tablename '

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

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 [ table 2_name] ( [Id] ) GO To allow cascade delete update the last line ) REFERENCES [ table 2_name] ( [Id] ) ON DELETE CASCADE GO To remove constraint ALTER TABLE table_name DROP CONSTRAINT [constraint_name]

bunzip2 <filename.bz>

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

ps -auxww

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

cat | mail

cvs rtag -F mytag myfile.txt