Display SQL result horizontally (comma separated) in MSSQL 2005

Here is a sql statement to display a column result horizontally. e.g. record1, record2, record3, record4, ... recordx
DECLARE @id_list VARCHAR(MAX) --varchar max works for SQL 2005 or above
 
SELECT @id_list = CASE WHEN @id_list IS NULL THEN CONVERT(VARCHAR,column1) 
		ELSE @id_list + ',' + CONVERT(VARCHAR,column1) END
		FROM table1		
SELECT @id_list
FacebookTwitterGoogle+Share

Visual Source Safe 2005 issue – opening VS solution redirects to My Computer

I have been using Visual Source Safe 2005 for quite sometime. I came to an unusual problem. I tried opening a project solution created under Visual Studio 2010. When I double clicked the solution, it redirected me to "My Computer" list which contained Physical and Network directories. After quite a bit of searching for answer, I found a solution to this issue. There is a Microsoft patch (KB943847) for VSS 2005 that needs to be applied to the VSS 2005. This solves the issue of redirecting to My computer when trying to open solution on VSS 2005. http://www.microsoft.com/downloads/details.aspx?FamilyID=8a1a68d8-db11-417c-91ad-02aab484776b&displaylang=en

Auto create gallery for JonDesign’s Smooth Gallery

This is something I wrote to make my life easier adding images on the JDGallery. It automatically scans the directory and creates the div section (where gallery and images are defined). Simply replace the following section.
<div class="content">
<div id="myGallerySet">
.....
</div>
</div>
with the output of the code below
<?php
$path = "gallery"; //path to scan for folders/images
 
//couple of things
//thumbnails must be named {filename}_thumb.{extension}
//extension of file is limited to 3 character like jpg, gif, not jpeg, etc.
//sorry, got no time to enhance it, so you can copy and modify as needed, it's free.
 
//using the opendir function
$dir_handle = @opendir($path) or die("Unable to open $path");
 
echo "Copy Paste the code below.<br><br>";
echo "<tt>&lt;div class="content"&gt;<br>&lt;div id="myGallerySet"&gt;<br><br>";
 
list_dir($dir_handle,$path);
 
echo "&lt;/div&gt;<br>&lt;/div&gt;</tt>";
 
function list_dir($dir_handle,$path)
{
	$countgallery = 1;
	$countimages = 1;
 
	//running the while loop
    while (false !== ($file = readdir($dir_handle))) {
        $dir =$path.'/'.$file;
		if(is_dir($dir) && $file != '.' && $file !='..' )		
        {
			$handle = @opendir($dir) or die("unable to open file $file");
			echo "&lt;div id="gallery$countgallery" class="galleryElement"&gt;<br>&lt;h2&gt;$file&lt;/h2&gt;<br><br>";
            list_dir($handle, $dir);
			echo "&lt;/div&gt;<br><br>";
			$countgallery++;
			$countimages = 1;
        }
 
		elseif($file != '.' && $file != '..' && substr($dir, -9, 5) != 'thumb')
        {
			$mainfilename = $dir;
			$thumbfilename = substr($dir, 0, -4) . "_thumb.jpg";
			echo "&lt;div class="imageElement"&gt;<br>&lt;h3&gt;Item $countimages Title&lt;/h3&gt;<br>&lt;p&gt;Item $countimages Description&lt;/p&gt;<br>&lt;a href="#" title="open image" class="open"&gt;&lt;/a&gt;<br>&lt;img src="$mainfilename" class="full" /&gt;<br>&lt;img src="$thumbfilename" class="thumbnail" /&gt;<br>&lt;/div&gt;<br>";
			$countimages++;
        }
    }   
 
    //closing the directory
    closedir($dir_handle);
}
?>

Max pool .NET issue on ADO.NET and a quick solution

This is the error message you will get when pool is maxed out. Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Please try closing reader, command, connection by using close() and dispose() before using the method below. If the pooling is enabled in the application, sometime closing the connection by using Dispose() or Close() is just going to put the connection on the sleep mode (awaiting command). Even though there is connection closing statements on the application, on the database side, it was putting those connections on the sleep mode. As it built up, there were no more pool left, therefore it timed out. Solution is to add Pooling = False on the connection string of the application. This closes the connection as soon as dispose() is introduced. Hence no timeouts waiting for open pool.
Database=testdb; Server=testserver; uid=test; PWD=test; Pooling=False; Connect Timeout=30
I am not responsible if you use my solution and mess up something. Do at your own risk. Works for me here. Checking number of connections on database to see if max pool has reached …
--Total number of connections
SELECT DB_NAME(dbid) AS 'Database Name',
COUNT(dbid) AS 'Total Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid;
 
--Connections
SELECT hostname, program_name, STATUS, cmd FROM sys.sysprocesses
WHERE DB_NAME(dbid) = 'Database Name goes here';

Opening new window from button/link button on ASP.net 2.0/3.5

I Googled for the solution and never found one that is simple, short and to the point. Here is the fastest solution to opening a link in a new page if you are using a button or a link button on ASP.NET 2.0/3.5. Add this attribute to your button. This goes on your page load section of the code.
button1.Attributes.Add("onclick", "window.open('somepage.aspx');")
Unfortunately I could not find a solution using response.redirect

Removes the ^M character in Unix/Linux

This script removes the ^M character from the file. Mainly for those who use windows editor to edit Unix/Linux text files. I would definitely recommend VI or emacs if you are messing with Unix/Linux text files. Create a shell script file and copy paste the code below.
#!/bin/ksh
#Script to remove the ugly DOS ^M characters
 
for readfiles in $@
do
tr -d 'r' < $readfiles > $sometempfile.tmp
mv $sometempfile.tmp $readfiles
done

Fast tutorial for explain plan on Oracle

user1@testserver : /home/firestorm => sqlplus
 
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jul 2 15:27:33 2008
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Enter user-name: user1
Enter password: **********
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
 
dbserver:SQL> @?/rdbms/admin/utlxplan
 
dbserver:SQL> truncate table plan_table;
 
Table truncated.
 
dbserver:SQL> commit;
 
Commit complete.
 
dbserver:SQL> explain plan for
2  select count(*) from schemaname.tablename;
 
Explained.
 
dbserver:SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation              |  Name            | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|                  |     1 |       | 26979 |        |      |            |
|   1 |  SORT AGGREGATE        |                  |     1 |       |       |        |      |            |
|   2 |   SORT AGGREGATE       |                  |     1 |       |       | 20,00  | P->S | QC (RAND)  |
|   3 |    TABLE ACCESS FULL   | TABLENAME        |   309M|       | 26979 | 20,00  | PCWP |            |
--------------------------------------------------------------------------------------------------------
 
Note: fully remote operation, cpu costing is off
 
11 rows selected.
 
dbserver:SQL> exit;

Temp space check in oracle

Ever got this issue in oracle ... ORA-01652: unable to extend temp segment by x in tablespace TEMP_TS Here is a way to find out if you are really out of temp space
--Temp space total size, free space and used space
SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024/1024 AS Total_GB,
used_blocks*16/1024/1024 AS Used_GB,
free_blocks*16/1024/1024 AS Free_GB
FROM   v$sort_segment;
 
--Temp space utilization by user
 
SELECT
b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
b.username,
b.blocks*16/1024/1024 AS GB,
a.SID,
a.serial#,
a.status
FROM v$session a, v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.username, b.tablespace, b.blocks;

Unix script to find the total size of files

Create a shell script file and copy paste the code. Please let me know if you find any bugs
awk '
# filesum: list files and total size in bytes
# input: long listing produced by "ls -l"
BEGIN {
printf("%15s %s\n", "BYTES", "FILE");
}
# test for 9 fields, files begin with "-"
NF == 9 &amp;&amp; /^-/ {
++filenum;            # count files
sum += $5;            # accumulate sizes
num = sprintf("%.2f", $5);
while (num ~ /[0-9][0-9][0-9][0-9]/) {
sub(/[0-9][0-9][0-9][,.]/, ",&amp;", num);
}
sub(/\..*/, "", num);
printf("%15s %s\n", num, $9);    # print size and name
}
END {
num = sprintf("%.2f", sum);
while (num ~ /[0-9][0-9][0-9][0-9]/) {
sub(/[0-9][0-9][0-9][,.]/, ",&amp;", num);
}
sub(/\..*/, "", num);
printf("\n%15s total bytes in %d files\n\n", num, filenum);
}'

Converting a grid to excel document in vb.net

A little script to convert a gridview to an excel document. This always came handy when someone needed to create a report right out of gridview ... You can call the function like this ...
ExportGridToExcel(grd, "report.xls")
 
---Copy the code below and paste it in your script
 
Public Sub ExportGridToExcel(ByRef grdGridView As GridView, ByVal fileName As String)
 
Response.Clear()
 
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}.xls", fileName))
 
Response.Charset = ""
 
Response.ContentType = "application/vnd.xls"
 
Dim strWriter As New StringWriter()
 
Dim HtmlWriter As New HtmlTextWriter(strWriter)
 
grdGridView.RenderControl(HtmlWriter)
 
Response.Write(strWriter.ToString)
 
Response.End()
 
End Sub
 
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
 
'You will need this part else you will get error like "runat=server needs to be before ....."
 
End Sub