tag:blogger.com,1999:blog-92204309767448750952024-03-14T01:37:33.970-05:00Sagi Shkedy's Technical BlogShort technical articles and tips for .Net developers, SQL developers/DBAs, web developers and designersSagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.comBlogger20125tag:blogger.com,1999:blog-9220430976744875095.post-44113317059804899112012-12-18T10:59:00.002-05:002012-12-18T10:59:38.380-05:00Looking for columns in SQL databasesA really quick solution that I always have to look up.<br />
Every once in a while I need to find some table in a database, I don't know the name of it but I have an idea of how one of the columns is called.<br />
For example - if I have a ZenCart database and I want to know where tracking numbers are stored I can guess that the column name might contain the string track in it.<br />
So for MySQL I can run a simple query:<br />
<br />
SELECT <br />
TABLE_SCHEMA, <br />
TABLE_NAME, <br />
COLUMN_NAME <br />
FROM <br />
information_schema.columns <br />
WHERE information_schema.columns.COLUMN_NAME LIKE '%track%'<br />
<br />
It's that simple.<br />
<br />
What about SQL server:<br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">SELECT</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">cols</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">.</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">name</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">as</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">col_name</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">,</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">tbls</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">.</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">name</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">AS</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">tbl_name</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">FROM</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;">sys</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">.</span></span></span><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;">all_columns</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">cols</span></span></span><br />
<span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"></span></span><br />
<span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"></span><br /></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">INNER</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">JOIN</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;">sys</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">.</span></span></span><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;"><span style="color: green; font-family: Consolas; font-size: x-small;">all_objects</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">tbls</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">ON</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">cols</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">.</span></span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">object_id</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">=</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">tbls</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">.</span></span></span><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;"><span style="color: magenta; font-family: Consolas; font-size: x-small;">object_id</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">AND</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">tbls</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">.</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">type</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">=</span></span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'u'</span></span></span><br />
<span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">
</span></span></span><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"></span></span></span><br />
<span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;"><span style="color: blue; font-family: Consolas; font-size: x-small;">WHERE</span></span> </span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">cols</span></span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">.</span></span></span><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;"><span style="color: teal; font-family: Consolas; font-size: x-small;">name</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;"><span style="color: grey; font-family: Consolas; font-size: x-small;">LIKE</span></span></span><span style="font-family: Consolas; font-size: x-small;"><span style="font-family: Consolas; font-size: x-small;"> </span></span><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;"><span style="color: red; font-family: Consolas; font-size: x-small;">'%track%'</span></span></span><br />
<span style="color: red;"></span><br />
</span></span></span>
That's it!Anonymoushttp://www.blogger.com/profile/13566770653570908877noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-18762736556004881932010-08-30T11:01:00.012-05:002010-08-30T15:53:26.127-05:00Free Outlook/Windows Dialer For AstriskI have been using Asterisk as the PBX for my business and I am a big fan.<br />I am not a Guru by any means but I was able to get it to do what I need so far so I am pretty happy with the technology.<br /><br />I am very bad with reading and dialing numbers - not sure why that is but If I try to manualy dial a phone number, especially one I don't usualy dial I have a success rate of about 60%.<br />Since most of the phone numbers I use are in outlook or in Windows somewhere I thought it would be great if I can have a "Click To Dial" button on my computer or something similar to that feature. It took a while to research and through different forum posts I found out there is such a plug in that is free from a company called <a href="http://www.thirdlane.com/products/thirdlane-dialer">ThirdLane</a>. In order to obtain this dialer you need to register and it is available foor download.<br /><br /><strong><u>Note to readers - after my inital post I found a different product which I like better because it has more features under Asterisk. The product is called Outcall from Bicom systems. Free and Open Source available: </u><a href="http://www.bicomsystems.com/">http://www.bicomsystems.com/</a><br /><br /></strong>I left the inital instructions.<br /><br />The instructions I saw on the site were not very clear so I figured I would document the process a little better.<br /><br /><strong><u>Installing the dialer:<br /></u></strong>Go to <a href="http://www.thirdlane.com/">http://www.thirdlane.com/</a> select download, register if needed and download the dialer.<br />After you install the product you will have a small window on the right top corner of the desktop.<br />Because the dialer is not registered with Asterisk it will be red.<br /><br /><br /><br /><br /><p><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 320px; DISPLAY: block; HEIGHT: 136px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5511236603089664194" border="0" alt="" src="http://1.bp.blogspot.com/_97LcEz-utj0/THvYfaBKOMI/AAAAAAAAABo/KClHq3bKVzs/s320/NotConfigured.PNG" /></p><br /><br /><p><strong><u>Configuring Asterisk<br /></u></strong>In order to allow the dialer to speak to asterisk you will need to edit a single configuration file.<br />The file to be edited is in /etc/asterisk/manager_custom.conf. It might be somewhere else in your system.</p><br /><br /><p>If you are using Windows like I do - you can use <a href="http://winscp.net/eng/index.php">WinSCP</a> to connect and edit the file. </p><br /><br /><p>We will add a new section to the file, detailing a username, passord and permissions. These permissions will allow the dialer to log in to the Asterisk Manager Interface and originate the calls for you:<br /></p><br /><a href="http://2.bp.blogspot.com/_97LcEz-utj0/THvamLDIP0I/AAAAAAAAABw/G7bm_iJgfsk/s1600/ConfigFile.PNG"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 320px; FLOAT: left; HEIGHT: 126px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5511238918353731394" border="0" alt="" src="http://2.bp.blogspot.com/_97LcEz-utj0/THvamLDIP0I/AAAAAAAAABw/G7bm_iJgfsk/s320/ConfigFile.PNG" /></a><br /><br /><br /><br /><br />.<br /><br /><br /><br /><br /><br /><br /><p></p><br /><br /><p>The text will look something like:</p><pre>[outlook]<br /><br />secret = PASSWORD<br /><br />deny=0.0.0.0/0.0.0.0<br /><br />permit=192.168.XXX.XXX/255.255.255.XXX<br /><br />read = system,call,log,verbose,command,agent,user,config,command,dtmf,reporting,cdr,dialplan,originate<br /><br />write = system,call,log,verbose,command,agent,user,config,command,dtmf,reporting,cdr,dialplan,originate<br /><br /><br /><br /></pre><br /><br /><p>You will need to change the following:<br /><strong>Set username:<br /></strong>The part that says [outlook] specifies the username so you can make it anything you want. Don't use admin or any of the other ones specified in previous sections on the page<br /><strong>Set Password:<br /></strong>The word PASSWORD in this sample (the value for secret) should be replaced by a password that you assign and note for yourself.<br /><strong>Set IP ACL:<br /></strong>The permit keyword lists the IP address and netmask for the addresses that the username is allowed to connect. For example, if you use the adress range 192.168.0.1-192.168.0.254 in your network and want to allow access to any computer on that network you would specify:<br />192.168.0.1/255.255.255.0 if you only want ip address 192.168.5.9 to access you would specify:192.168.5.9/255.255.255.255.<br />If you want to specify multiple ranges, you can add additional lines.<br /><br /><strong><u>Apply Settings<br /></u></strong>The next step is to apply the settings - log on to an Asterisk Console.<br />(I do this using <a href="http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html">putty</a> to ssh my asterisk computer).<br />Once logged in type the following:<br /></p><pre><br />asterisk -r<br /><br /></pre><br />And hit enter<br />Once logged in<br /><br /><pre><br />reload<br /><br /></pre><br /><br /><br /><p><br />You can keep this window open to see how things progress.<br /></p><br /><br /><br /><p><br /><strong><u>Configuring the dialer<br /></u></strong>Next step is to configure the dialer. Right Click the dialer (anywhere in the red zone) in windows and select Options. Click on the Connection tab.<br />You will see the following window:<br /></p><br /><a href="http://1.bp.blogspot.com/_97LcEz-utj0/THvgrCIrUMI/AAAAAAAAAB4/TjIhgt_4R0Y/s1600/Configure.PNG"><img style="MARGIN: 0px 10px 10px 0px; WIDTH: 400px; FLOAT: left; HEIGHT: 362px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5511245598930194626" border="0" alt="" src="http://1.bp.blogspot.com/_97LcEz-utj0/THvgrCIrUMI/AAAAAAAAAB4/TjIhgt_4R0Y/s400/Configure.PNG" /></a><br /><br /><p><br /><br /></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p></p><p>The settings to edit: </p><p><strong>Connection Type</strong>: Asterisk Manager Interface<br /><strong>Host: </strong>the IP address of the Asterisk server<br /><strong>Port: </strong>Default is 5038<br /><strong>Manager User:</strong>This is the username from before<br /><strong>Manager Password: </strong>The password from before<br /><strong>My Phone (Channel):</strong>This is the channel that the calls will be transfered to - for example, if you have a SIP extension number 200 you would fill it with SIP/200. When you click the call button the call will be initiated and you phone will ring.<br /><strong>Context:</strong>The appropriate Asterisk Context, in Elastix and TrixBox this is "from-internal"</p><p>Click OK and wait a few seconds.<br />If everything was alright - the dialer box will turn green.<br /><br /><strong><u>Testing/troubleshooting:<br /></u></strong>In order to test this you can simply try to place a call. using the dialer. If you are running into problems do the following:<br />Go back to your console window and type:<br /><pre><br />core set verbose 999<br /></pre><br />and hit enter<br />And try to place a call.<br />You can look in the console window and see what is going on.<br />If you see something like:<br /><br /><pre><br /> == Connect attempt from '192.168.xxx.xxx' unable to authenticate<br /></pre><br /><br /><p><br />It means your username and password are worong.<br /></p><br /><br /><p>You can </p><p>This tuturial was done on Elastix 2.01, Outlook 2007 and Windows 7</p><br /><br /><p>Happy Dialing!</p>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-36469195751345148702010-05-05T13:47:00.003-05:002010-05-05T13:52:41.238-05:00Parsing Search Terms from URL in SQL ServerI often have the save the referrer URL for sites I work on to see where the visitors are coming from.<br />I needed a quick way to figure out what were people searching when they came from a search engine.<br />Most search engines will have the search term in the query string as q=search+term it could be in the middle, end or beginning of the query string.<br />I created a quick UDF to parse these and return the search term:<br /><br /><span style="color:#009900;">CREATE FUNCTION fnGetSearchTerms (@MyUrl nvarchar(1000))<br />RETURNS VARCHAR(500)<br />AS<br />BEGIN<br /><br />DECLARE @idx int --Index of the search term<br />DECLARE @iex int --Index of the seperator after the search term<br /><br />SET @idx =1<br /><br />--Return value<br />DECLARE @Ret varchar(500)<br /><br />WHILE ((SUBSTRING(@MyUrl,@idx,3)!='&q=') AND (SUBSTRING(@MyUrl,@idx,3)!='?q=') ) AND @idx < LEN(@MyUrl)<br />BEGIN<br />SET @idx = @idx+1<br />END<br /><br />IF ((SUBSTRING(@MyUrl,@idx,3)='&q=') OR (SUBSTRING(@MyUrl,@idx,3)='?q=') )<br />BEGIN<br /><br />SET @idx=@idx+3<br />SET @iex=Charindex('&',@MyUrl,@idx+3)<br />if @iex=0<br />BEGIN<br />SET @iex = LEN(@MyUrl)+1<br />END<br /><br />SET @Ret=SUBSTRING(@MyUrl, @idx, @iex-@idx)<br /><br />END<br />SET @Ret=REPLACE(@Ret,'+',' ')<br /><br />RETURN @Ret<br /><br />END<br />go </span><br /><p><span style="color:#009900;"></span> </p><p><span style="color:#009900;"><span style="color:#000000;">Quick and simple</span></p><br /><br /></span>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com1tag:blogger.com,1999:blog-9220430976744875095.post-87852166083749081122010-04-23T16:40:00.002-05:002010-04-23T16:57:33.097-05:00Free downloads every programmer needsSo everyone has a go to list of favorite programs/util that go on your machine as soon as it is setup - these are mine:<br /><a href="http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html">Putty</a> - your ssh telnet machine to log into your various unix/linux/asterisk boxes<br /><a href="http://winscp.net/eng/index.php">Winscp</a> - moving files in and out of your various unix/linux boxes and the occasional editing<br /><a href="http://www.debugmode.com/wink/">Wink</a> - create annotated screen captures and tuturials quickly<br /><a href="http://notepad-plus.sourceforge.net/uk/site.htm">Notepad++</a> - Quick and dirty text/code editor on steriods<br /><a href="http://www.irfanview.com/">IrfanView</a> - Resize and manipulate images, change format, compress etc.<br /><a href="http://winmerge.org/">WinMerge</a> - Find differences between files and directories and merge versions of the same file<br /><a href="http://windirstat.info/">WinDirStat</a> - graphic representation of hard drive usage<br /><a href="http://audacity.sourceforge.net/">Audacity</a>- A great sound editorSagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-59925655352437407502009-05-08T19:24:00.003-05:002009-05-08T19:52:52.420-05:00Sending Emails from .Net using Google's SMTP serversThe .Net framework has a few classes to handle mail.<br />The System.Net.Mail namespace will let you send an email message via the SMTP server configured in your web.config file or app.config file.<br />This works very well for many providers - but if you are using Gmail or google apps things get a little sticky since the only way to connect to the SMTP servers is using SSL which is not supported by system.net.mail.<br />I found a workaround this problem using an open source component called stunnel - stunnel allows you to create SSL tunnels to communicate with specific addresses and ports.<br />The way this would work is this:<br />1. we will tell our program to send email using SMTP to a non standard port (say 8025 instead of 25).<br />2. We will tell STunnel to listen on this port and forward all the traffic through an SSL tunnel to Google's SMTP server.<br /><br />So here are the step by step directions:<br />1. Download the Stunnel installer from Stunnel.Org - <a href="http://www.stunnel.org/">http://www.stunnel.org/</a> - install this on your server<br />2. go to the the install directory for stunnel and change the stunnel.conf file to look like:<br /><br />cert = stunnel.pem<br />socket = l:TCP_NODELAY=1<br />socket = r:TCP_NODELAY=1<br />;debug = 7<br /><br />output = stunnel.log<br />; Use it for client mode<br />client = yes<br />[GmailSMTP]<br />accept = XXX.XXX.XXX.XXX:8025<br />connect = smtp.gmail.com:465<br /><br />You will need to replace XXX.XXX.XXX.XXX with the IP address for the server you are running on - do not use localhost or 127.0.0.1 - they failed for me.<br />3. From the start menu do - stunnel service install and then stunnel servic start (you can change the start mode for the service to automatic also).<br />4. You can test your work - so far - open a command prompt window and type the following:<br />Telnet XXX.XXX.XXX.XXX:8025 (where XXX.XXX.XXX.XXX is the address above).<br />If everything works well you should see something like:<br />220 mx.google.com ESMTP q18sm6397127pog.5<br />Close the window.<br />5. In your application edit the System.Net configuration section of web.config or app.config as follows:<br /><system.net> <br /><mailsettings> <br /><smtp from="name@domain.com" > <br /><network host="XXX.XXX.XXX.XXX"<br />port="8025"<br />password="YOUR PASSWORD" userName="name@domain.com"> <br /></smtp> <br /></mailsettings> <br /></system.net> <br /><br />That should do it.<br /><br />The inspiration for this was an article I read here:<br /><a href="http://quack.me/2007/02/gmail_via_pop3_on_exchange.php">http://quack.me/2007/02/gmail_via_pop3_on_exchange.php</a>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com3tag:blogger.com,1999:blog-9220430976744875095.post-39901329868237157522009-02-13T10:57:00.007-05:002009-02-13T12:21:35.134-05:00Disable user interface during ajax postbacks on ASP.NetOne of the biggest issues I have with public facing websites I work on is double clicks on buttons or users interrupting the postback process. This happens a lot with the payment button of credit card transactions and other time consuming postbacks.<br />I have done some Javascript and CSS tweaks and tricks to handle specific controls and I was unable to get something I was truely happy with.<br />Until I met jQuery. jQuery is a javascript library that does a whole bunch of things, introduces shortend syntax and a pluggable API. It can be downloaded at <a href="http://jquery.com/">http://jquery.com/</a>.<br />Coupling JQuery with a plugin called BlockUI. Can be downloaded at <a href="http://malsup.com/jquery/block/">http://malsup.com/jquery/block/</a><br />What I came out with is a fairly generic solution for ASP.Net that handles all the ajax postbacks for a specific page or website by wiring the application_beginRequest and application_endRequest on the client side.<br />So here is the recipe:<br />Ingrediants -<br />1. ASP.Net 3.5 website<br />2. An aspx webpage<br />3. A script manager with a reference to:<br /><br /><br /><br /><ol><br /><br /><br /><li>The JQeury library</li><br /><br /><br /><li>Block UI plugin</li><br /><br /><br /><li>A very short Javascipt file (enclosed below)</li></ol><br /><br /><br /><p>4. An update panel</p><br /><br /><br /><p>Instructions:</p><br /><br /><br /><p>1. Create your website - I use Visual Studio 2008 </p><br /><br /><br /><p>2. Add your page or your master page </p><br /><br /><br /><p>3. Create a <em>js </em>directory and add the downloaded scripts from jQuery and JBlockUI</p><br /><br /><br /><p>4. Add a new file to the <em>js </em>directory called <em>wireRequestEvents.js</em></p><br /><br /><br /><p>with the following code:</p><br /><br /><br /><p><br /><em><span style="font-size:85%;color:#009900;">Sys.WebForms.PageRequestManager.getInstance().add_beginRequest(BeginRequestHandler);</span></em></p><span style="color:#009900;"><br /><br /><br /><p><br /><span style="font-size:85%;"><em>Sys.WebForms.PageRequestManager.getInstance().add_endRequest(EndRequestHandler);<br />function BeginRequestHandler(sender, args)<br />{<br />$.blockUI();<br />}<br />function EndRequestHandler(sender, args)<br />{<br />$.unblockUI();<br />}</em></span></p><br /><br /><br /><p><span style="font-size:85%;"><span style="font-size:100%;color:#000000;">5. Drop a script manager in your page with the following syntax:</span></span></p><br /><br /><br /><p><br /><?xml:namespace prefix = asp /><asp:scriptmanager id="theScriptManager" runat="server"><br /><scripts><br /><asp:scriptreference path="~/js/jquery-1.3.1.min.js"></asp:scriptreference><br /><asp:scriptreference path="~/js/jquery.blockUI.js"></asp:scriptreference><br /><asp:scriptreference path="~/js/wireRequestEvents.js"></asp:scriptreference><br /></scripts><br /></asp:scriptmanager></p><br /><br /><br /><p></span></p><span style="color:#000000;">6. Add your update panels with contect templates and you are good to go.</span><br /><br /><br /><p>To demonstrate the technique I created a simple page with a button and a label inside an update panel. Once clicked the button waits 4 seconds and then changes the label. This imulates a long transaction on the server.</p><br /><br /><br /><p>Here is what the users will see:<br /></p><br /><a href="http://2.bp.blogspot.com/_97LcEz-utj0/SZWmrS5xPlI/AAAAAAAAAA8/DXpaNaWN-1g/s1600-h/Capture1.jpg"><img id="BLOGGER_PHOTO_ID_5302327399037353554" style="WIDTH: 320px; CURSOR: hand; HEIGHT: 277px" alt="" src="http://2.bp.blogspot.com/_97LcEz-utj0/SZWmrS5xPlI/AAAAAAAAAA8/DXpaNaWN-1g/s320/Capture1.jpg" border="0" /></a><br /><br /><div><a href="http://2.bp.blogspot.com/_97LcEz-utj0/SZWmXg4wDVI/AAAAAAAAAA0/GavN_eM2DzA/s1600-h/Capture1.jpg"></a><br /><p>After they click the button the UI is blocked by a semi transperant div<br /></p><br /><a href="http://4.bp.blogspot.com/_97LcEz-utj0/SZWnGkJmGNI/AAAAAAAAABE/zrlsTxKRWZQ/s1600-h/Capture2.jpg"><img id="BLOGGER_PHOTO_ID_5302327867523602642" style="WIDTH: 320px; CURSOR: hand; HEIGHT: 277px" alt="" src="http://4.bp.blogspot.com/_97LcEz-utj0/SZWnGkJmGNI/AAAAAAAAABE/zrlsTxKRWZQ/s320/Capture2.jpg" border="0" /></a><br /><div><br /><p><br />When the request returns the users will see the updated screen<br /></p><br /><a href="http://2.bp.blogspot.com/_97LcEz-utj0/SZWnbqtI0wI/AAAAAAAAABM/WPeAPoBLSr8/s1600-h/Capture3.jpg"><img id="BLOGGER_PHOTO_ID_5302328230060544770" style="WIDTH: 320px; CURSOR: hand; HEIGHT: 277px" alt="" src="http://2.bp.blogspot.com/_97LcEz-utj0/SZWnbqtI0wI/AAAAAAAAABM/WPeAPoBLSr8/s320/Capture3.jpg" border="0" /></a><br /><p></p></div></div><br /><p>Pretty simple!</p><p>The code for this is available at <a href="http://www.simplicityc.com/files/JQuerySample.zip">http://www.simplicityc.com/files/JQuerySample.zip</a><br /></p>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com1tag:blogger.com,1999:blog-9220430976744875095.post-2304911050463972782008-01-14T14:44:00.000-05:002008-01-17T10:50:11.570-05:00Code Camp 2008.1 - t-Sql to CLR performance comparison testing and Cursor Avoidance TechniquesWe had a great session at Code Camp. Thanks to <span class="blsp-spelling-corrected" id="SPELLING_ERROR_0">Sebastian</span> <span class="blsp-spelling-error" id="SPELLING_ERROR_1">Meine</span> who did most of the work for the session. I contributed by brainstorming with <span class="blsp-spelling-corrected" id="SPELLING_ERROR_2">Sebastian, coding the CLR components of the project and walking through the code.</span><br />We concluded that there is no conclusion...<span class="blsp-spelling-error" id="SPELLING_ERROR_4">CLR</span> is faster for some things and T-<span class="blsp-spelling-error" id="SPELLING_ERROR_5">SQL</span> is faster for others. There are some very big limitations to the <span class="blsp-spelling-error" id="SPELLING_ERROR_6">CLR</span> integration in <span class="blsp-spelling-error" id="SPELLING_ERROR_7">SQL</span> server - the main one is the 8000 byte limit when serializing custom aggregates and the <span class="blsp-spelling-corrected" id="SPELLING_ERROR_8">indifference</span> to order in those functions.<br />We also played with some neat SQL-2005 enhancements like common table expressions and Cross apply which was new for some of the users.<br />I will try to get the code from Sebastian and post it here.Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-19436647237917531682008-01-10T15:35:00.000-05:002008-01-10T15:43:48.201-05:00Troubleshooting SQL server database mailI am putting database mail on SQL server 2005 into use more and more these days with my consutling projects and it is making things much easier on me.<br /><br />It is very easy to trigger emails based on transactions that happen on the database - like payments posted, account goes below a certain balance etc.<br /><br />It is also nice to use some of the included views to see what is the status of your email messages.<br /><br />If a message that I send did not reach the recipient I can check:<br /><span style="color:#3366ff;">SELECT * FROM msdb.dbo.sysmail_faileditems</span><br />to look for that message.<br /><br />Once I find it there I can can look at the <span style="color:#3366ff;">mailitem_id</span> in the <span style="color:#3366ff;">msdb.dbo.sysmail_event_log </span><span style="color:#000000;">view.</span><br /><br />Happy mailing!Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-51498023167965358902007-11-21T09:46:00.000-05:002007-11-21T10:11:41.464-05:00Where did my SQL Server Memory go?<a href="http://2.bp.blogspot.com/_97LcEz-utj0/R0RF3nA933I/AAAAAAAAAAg/jA_f69cDHao/s1600-h/TaskManagerMemory.gif"><img id="BLOGGER_PHOTO_ID_5135306296777367410" style="FLOAT: left; MARGIN: 0px 10px 10px 0px; CURSOR: hand" alt="" src="http://2.bp.blogspot.com/_97LcEz-utj0/R0RF3nA933I/AAAAAAAAAAg/jA_f69cDHao/s320/TaskManagerMemory.gif" border="0" /></a><br /><div>In managing one of my servers I had to make sure everything was running the way it should. I am working on fairly big databases (in the order of 10s of gigabytes) and I wanted to make sure <span class="blsp-spelling-error" id="SPELLING_ERROR_0">SQL</span> is using all the memory it has available.<br /><br />The server I am working on is an x64 windows 2003 enterprise running <span class="blsp-spelling-error" id="SPELLING_ERROR_1">SQL</span> 2005 enterprise 32 bit. The server is also equipped with 12GB of ram.</div><div> </div><div>Whenever I would look into the task manager or the performance monitor the only memory allocation I see for <span class="blsp-spelling-error" id="SPELLING_ERROR_2">SQL</span> server is around 202 MB which makes no sense.</div><div> </div><div>If you look at the screen capture enclosed you can see the memory allocation for <span class="blsp-spelling-error" id="SPELLING_ERROR_3">SQL</span> server.</div><div> </div><div>I did some research on the subject and found the answer. <span class="blsp-spelling-error" id="SPELLING_ERROR_4">SQL</span> has a dynamic management view called <span class="blsp-spelling-error" id="SPELLING_ERROR_5">sys</span>.<span class="blsp-spelling-error" id="SPELLING_ERROR_6">dm</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_7">os</span>_memory_clerks. This view <span class="blsp-spelling-corrected" id="SPELLING_ERROR_8">described</span> in <a href="http://technet.microsoft.com/en-us/library/ms175019.aspx">http://technet.microsoft.com/en-us/library/ms175019.aspx</a> has detailed memory allocation information for <span class="blsp-spelling-error" id="SPELLING_ERROR_9">SQL</span> server. When you examine this view you can see a column that details AWE memory allocations that are detailed in the awe_allocated_<span class="blsp-spelling-error" id="SPELLING_ERROR_10">kb</span> column, in my case this was summed to the 10GB I assigned to the server. There are other useful rows columns in the view and they are documented in the <span class="blsp-spelling-error" id="SPELLING_ERROR_11">technet</span> article mentioned here.</div><div> </div><div>One useful query i run constantly:</div><div><br />SELECT<br />SUM(awe_allocated_<span class="blsp-spelling-error" id="SPELLING_ERROR_12">kb</span>)/1024 AS [AWE_<span class="blsp-spelling-error" id="SPELLING_ERROR_13">MEM</span>_MB],<br />SUM(virtual_memory_reserved_<span class="blsp-spelling-error" id="SPELLING_ERROR_14">kb</span>)/1024 AS [<span class="blsp-spelling-error" id="SPELLING_ERROR_15">VM</span>_RES_MB],<br />SUM(virtual_memory_committed_<span class="blsp-spelling-error" id="SPELLING_ERROR_16">kb</span>)/1024 AS [<span class="blsp-spelling-error" id="SPELLING_ERROR_17">VM</span>_COM_MB],<br />SUM(shared_memory_reserved_<span class="blsp-spelling-error" id="SPELLING_ERROR_18">kb</span>)/1024 AS [SM_RES_MB],<br />SUM(single_pages_<span class="blsp-spelling-error" id="SPELLING_ERROR_19">kb</span> )/1024 [<span class="blsp-spelling-error" id="SPELLING_ERROR_20">SNG</span>_PAGE_MB],<br />SUM(multi_pages_<span class="blsp-spelling-error" id="SPELLING_ERROR_21">kb</span> )/1024 [<span class="blsp-spelling-error" id="SPELLING_ERROR_22">MLT</span>_PAGE_MB]<br />FROM <span class="blsp-spelling-error" id="SPELLING_ERROR_23">sys</span>.<span class="blsp-spelling-error" id="SPELLING_ERROR_24">dm</span>_<span class="blsp-spelling-error" id="SPELLING_ERROR_25">os</span>_memory_clerks</div><div> </div><div>This returns the memory allocation totals in megabytes.<br /></div><div> </div>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com2tag:blogger.com,1999:blog-9220430976744875095.post-47122294209341989412007-11-15T13:54:00.000-05:002007-11-15T14:02:33.142-05:00Running C# and VB.Net Code on Microsoft SQL Server - Slides code and databaseThanks for coming to the November PSSUG Meeting at Microsoft.<br />All and all it went pretty well with a couple of Visual Studio crashes, SQL server acting funky but that comes with the territory.<br />As promised the code is available by clicking below:<br /><a href="http://www.blogger.com/www.simplicityc.com/files/sql_clr.zip">Code, database and slides from Running C# and VB.Net Code on Microsoft SQL Server talk</a><br />I had to remove the ANTLR part of the demo since there are IP issues involved.<br />To get this running extract the zip file to drive c:\<br />it will create a folder c:\SQL_CLR<br />Attach the database in c:\SQL_CLR\db to sql server<br />You can look at a word document in c:\SQL_CLR\powerpoint\ for instructions to run the demo.<br />The slides are in c:\SQL_CLR\powerpoint\ also.<br />To run the solution open:<br />C:\SQL_CLR\SimpleFunctions\SimpleFunctions.sln<br />Good luck and let me know if you have any questions!<br />SagiSagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-49296423946134786232007-09-17T13:11:00.000-05:002007-09-17T13:16:30.743-05:00Updated Code and Slides for the SQL CLR Integration TalkI updated the code and the slide for the <span class="blsp-spelling-error" id="SPELLING_ERROR_0">SQL</span> Integration talk that I gave in Code Camp 2007.2 in Philadelphia.<br />The slides code and slides are <span class="blsp-spelling-corrected" id="SPELLING_ERROR_1">available</span> here:<br /><a href="http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip">http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip</a><br />I added a <span class="blsp-spelling-error" id="SPELLING_ERROR_2">readme</span> file that <span class="blsp-spelling-corrected" id="SPELLING_ERROR_3">explains</span> how to run these samples.<br />There are samples for:<br />-<span class="blsp-spelling-error" id="SPELLING_ERROR_4">SQL</span> Stored Procedure written in C#, it uses return parameters, the message <span class="blsp-spelling-corrected" id="SPELLING_ERROR_5">channel</span>, internal connections to the database.<br />-User defined Aggregate function in C#<br />-Table valued function in VB.Net<br />-Scalar Values functions in C# and VB.Net<br />And a couple moreSagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-81523495937748403662007-09-15T15:34:00.000-05:002007-09-15T15:36:46.353-05:00Thanks for coming to my talk in code camp!Thanks for coming to my talk in code camp.<br />I will post the revised code on the blog tomorrow - need to run to a wedding right now.<br />There were serveral examples I didn't have a chance to go through but they will be in my code and slided that will be posted - tomorrow.<br />Enjoy the rest of the weekend!Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-29512466312688937442007-09-14T03:11:00.000-05:002007-09-14T03:14:02.616-05:00Code for Philly Code Camp 2007.2The code for my session in Code Camp is available here:<br /><a href="http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip">http://www.simplicityc.com/CodeCamp2007_CLR_Integration.zip</a><br />If you want to run the samples you will need SQL server 2005 and Visual Studio.Net 2005.<br />The slides are not yet ready but they will be posted here before the talk.Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-69232716370324221352007-09-06T11:32:00.000-05:002007-09-06T11:39:49.475-05:00Code Camp 2007.2 Running C# and VB.Net Code on Microsoft SQL ServerI will be speaking at the Philly Code Camp 2007.2 on the subect of integrating C# and VB.Net Code on MS-SQL server 2005.<br />I will post my slides and code files here in a couple of days.<br />This is a link to the event on the PhillyDotNet website:<br /><a href="http://www.phillydotnet.org/Default.aspx?tabid=598">http://www.phillydotnet.org/Default.aspx?tabid=598</a><br /><strong>Abstract for the talk</strong><br />SQL server 2005 allows .NET code to be run as functions, stored procedures, aggregate functions and other objects. In this session we will explore the integration of the .Net CLR with SQL server. We will go over the implications of running managed and unmanaged .Net code within SQL server 2005. We will talk about some useful applications for this type of integration and then we will get our hands dirty. We will create several objects in Visual Studio and deploy them in SQL server. Time permitting we will either go into debugging or informal performance testing by comparing .Net functions to native T-SQL code. This talk is geared towards DBAs and programmers alike. The code and slides will be posted ahead of time at http://blog.shkedy.com (check the night before the talk). If you are interested in running the code you will need SQL server 2005 and Visual Studio 2005 although it is not required in order to benefit from the session.Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-30136376282158368392007-02-15T09:57:00.000-05:002007-02-15T10:38:10.976-05:00Configuring ASP.Net 1.1 on IIS 6.0 in x64 versions of windows Server<span style="font-weight: bold;">32 vs 64 bit versions of windows server</span><br />The 32Bit version of windows 2003 does not take full advantage of 64 bit hardware. We got a double Xeon machine with windows 2003 server R2 x64 which is the 64 bit version of the OS. The advantages of the OS is the increased memory allowance (32 bit only allows 4GB form which .Net can only use 2GB, with the 64bit you can go up to 32GB of memory which makes a lot of sense for a web server.<br /><span style="font-weight: bold;">The problem<br /></span>By default iis 6.0 is running as a 64 bit host which means it will not run 32 bit applications.<br />ASP.Net 1.1 was never ported as a 64 bit app (2.0 was).<br />There is a way to configure iis to run 32 apps like ASP.Net 1.1: <a href="http://support.microsoft.com/?id=894435" target="_new">http://support.microsoft.com:80/?id=894435</a><br />A couple of things to know - if you allow iis to run 32 bit processes this will affect all applications. In the ASP.Net tab in IIS MMC console will disappear - making it impossible to switch between versions 1.1 and 2.0 for a specific website - this can be done only from the command prompt.<br /><span style="font-weight: bold;">The solution</span><br />Since I have a busy web server to manage with many sites I automated the process by writing a batch file that takes 2 arguments; the .Net framework version and the target site id. The scripts Changes the registration for that site.<br />To make this work - open notepad and paste the following:<br /><br /><div style="font-style: italic; font-family: courier new; color: rgb(0, 102, 0); background-color:black;"> <br /><br /><br />@echo off<br /><br />IF(%1) == () GOTO :MissingArgs<br /><br />IF(%2) == () GOTO :MissingArgs<br /><br />IF (%1) == (1.1) GOTO :VER11<br /><br />IF (%1) == (2.0) GOTO :VER20<br /><br /><br /><br /><br /><br />GOTO :END<br /><br /><br /><br />:VER11<br /><br />ECHO 1.1<br /><br /><br /><br />@echo on<br /><br />c:\windows\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis -s /w3svc/%2/root<br /><br />@echo off<br /><br />GOTO :END<br /><br /><br /><br />:VER20<br /><br />ECHO 2.0<br /><br />@echo on<br /><br />c:\windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -s /w3svc/%2/root<br /><br />@echo off<br /><br />GOTO :END<br /><br /><br /><br /><br /><br />:MissingArgs<br /><br />ECHO Usage change_asp_ver version sitenumber<br /><br /><br /><br />:END<br /><br />ECHO QUITTING<br /><br />PAUSE<br /><br /></div><br /><br />Save the file as change_asp_ver.bat in a directory you can recall like c:\scripts\<br />To run the script you will need to find out the site id that you wish to change. Open the IIS MMC console click websites and you will see the site identifier on the right.<br /><br />If you want to change site id 1234 to version 1.1 and you saved the file in the scripts directory you will type:<br />Start->run->cmd (enter)<br />cd c:\scripts\ (enter)<br />change_asp_ver 1.1 1234 (enter)<br /><br />You might need to change some of the paths in your script if you used non standard directories to install the frameworks.<br /><br />This is based on a post response to microsoft.public.interserver.iisSagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-85813566408239030562007-01-25T17:25:00.000-05:002007-01-26T18:17:42.254-05:00ClickOnce Button for ASP.Net 2.0We have a couple of forms from which we get double submissions by people hitting the submit button again and again. We decided to go with a ClickOnce button that disables itself when the form submits. I saw a couple of solutions that implement it but nothing for ASP.Net 2.0.<br />I ended up implementing it in myself.<br /><br />About 5 lines of code placed in the Page_Load event handler:<br /><br /><br />// Define the name and type of the client script on the page.<br />String csname = "OnSubmitScript";<br />Type cstype = this.GetType();<br />// Get a ClientScriptManager reference from the Page class.<br />ClientScriptManager cs = Page.ClientScript;<br />// Check to see if the OnSubmit statement is already registered.<br />if (!cs.IsOnSubmitStatementRegistered(cstype, csname))<br />{<br />String cstext = "if (typeof(ValidatorOnSubmit) == 'function' && ValidatorOnSubmit() == false)return false; else { var myCtl = document.getElementById('" + this.SubmitButton.ClientID + "'); myCtl.value = 'Please wait...'; myCtl.disabled = true;}";<br />cs.RegisterOnSubmitStatement(cstype, csname, cstext);<br />}<br /><br />On the aspx page we have:<br /><br /><asp:button id="SubmitButton" onclick="SubmitButton_Click" usesubmitbehavior="False" runat="server"><br /><br /><br />There are 2 gotchas here:<br />1. Make sure validation takes place and the page validates prior to disabling the button. This is done with JavaScript<br />2. Make sure the button is not rendered as input type submit otherwise when it is disabled it will not trigger the correct event handler on postback - for that we use: UseSubmitBehavior="False".<br /></asp:button><?xml:namespace prefix = asp /><asp:button id="SubmitButton" onclick="SubmitButton_Click" usesubmitbehavior="False" runat="server"></asp:button>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com2tag:blogger.com,1999:blog-9220430976744875095.post-27246397177625614582007-01-19T12:38:00.000-05:002007-01-19T13:02:55.899-05:00Temporary Tables and Dynamic SQLWhen programming in SQL on occasion there is a need to generate temporaty tables inside a sproc without knowing the colums or type when the sproc is written. One such situation would be when you want to pivot a table. You can create a cursor over a column result set and create a new column for each value.<br /><br />When you try to create temporary tables with dynamic SQL you will run into a scoping problem.<br />for example:<br /><br /><em>DECLARE @SQL nvarchar(4000)<br />SELECT @SQL = 'CREATE TABLE #Temp (col1 int)'<br />EXEC (@SQL)<br />SELECT * FROM #Temp</em><br /><p>This will cause an error:</p><p><em>Msg 208, Level 16, State 0, Line 4<br />Invalid object name '#Temp'.</em></p><p>The problem here is the scope of the session. When we execute dynamic sql via EXEC or sp_executesql a new scope is created for a child session. Any objects created in that session are dropped as soon as the session is closed.</p><p>One solution I have found for this problem is creating the table in the "parent" scope and then just using dynamic sql to modify the table. For this to work a table is created with a minimum set of colums. And then we use the ALTER TABLE statement with dynamic SQL. The Child session has access to the objects created in the parent session so the table can be modified with dynamic sql:</p><p><em>DECLARE @SQL NVARCHAR(4000)<br />CREATE TABLE #Temp ( id int null)<br />SELECT @SQL = 'ALTER #Temp ADD Col1 int null'<br />EXEC (@SQL)<br />SELECT * FROM #Temp<br />DROP TABLE #Temp</em><em><em></p></em></em>This table is visible and both columns will show up.<br /><em><em></em></em>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com8tag:blogger.com,1999:blog-9220430976744875095.post-81604502279525643242007-01-18T11:54:00.000-05:002007-01-18T12:20:19.758-05:00Improving on the ASP.Net Regular expression validator for phone numbersWhen I am working on web development I often use the ASP.Net regular expression validator in order to validate phone numbers.<br />The validation expression for US phone numbers that is built in to Visual Studio expects phone numbers in xxx-xxx-xxxx format.<br />The problem with that is that you alwas have to preface the prompt for the phone number with a sentence explaining the format and even then people often mess it up and do not understand why their form does not submit.<br />A good solution for the problem is to wire the onkeyup event that will add the dashes in all the right places.<br />For example this is in ASP.Net 2.0:<br /><br /><asp:textbox id="homePhoneTextBox"<br /> runat="server" maxlength="12" onkeyup="phoneFormat(this, event)"></asp:textbox><br />In asp.net 1.1 you can do it on the code behind:<br /><br />in c# this whould be something like:<br /><br /><em>this.homePhoneTextBox.Attributes.Add("onkeyup", ="phoneFormat(this, event)");</em><br /><br />Now all you need to do is handle this event with a JavaScript function that will format the phone number for the user:<br /><br /><em>function phoneFormat(sender, ev)<br />{<br />var theNumber;<br />var i;<br />theNumber = '';<br />for(i = 0; i < thenumber =" theNumber" keycode ="="> 0)<br />{<br />theNumber = theNumber.substring(0, theNumber.length -1);<br />}<br />if (theNumber.length > 6)<br />{ sender.value = theNumber.substring(0,3) + '-' + theNumber.substring(3,6) + '-' + theNumber.slice(6);<br />return(true);<br />}<br />if (theNumber.length > 5)<br />{<br />sender.value = theNumber.substring(0,3) + '-' + theNumber.substring(3,6)+ '-';<br />return(true);<br />}<br />if(theNumber.length > 3)<br />{<br />sender.value = theNumber.substring(0,3) + '-' + theNumber.slice(3)<br />return(true);<br />}<br />if (theNumber.length > 2)<br />{<br />sender.value = theNumber.substring(0,3)+ '-';<br />return(true);<br />}<br />sender.value = theNumber;<br />return(true);<br />}<br /></em><br />Not the shortest function ever but it does the trick.<br />If you want to grab the file you can do it here:<br /><a href="http://www.shkedy.com/blogFiles/01182007/CustomValidation.js">http://www.shkedy.com/blogFiles/01182007/CustomValidation.js</a>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com0tag:blogger.com,1999:blog-9220430976744875095.post-81100299087056668972007-01-17T06:51:00.000-05:002007-01-17T07:31:10.229-05:00Alternating HTML table Row Styles with javascript and CSSIn a website that we built for a clients <a href="http://www.isoabroad.com/">http://www.isoabroad.com/</a> we had to use alternating row styles for the tables. When I generate tables dynamically using server side code this is pretty easy. I can just specify a different class for each <tr>. If the tables are static this becomes a mess. For <a href="http://www.isoabroad.com/">http://www.isoabroad.com/</a> one of our web designers had to hand code the classes for each of the rows and each time we had to add or remove a row he had to edit all consecuitive rows.<br />For another website we were working on I had used a different approach. Using JavaScript and CSS I set a class for the table (altRows). When the page load I look for all the table elements and traverse the rows, setting the different classes for alternating rows. If a row already has a class defined the script will not change it which makes it perfect for header rows etc. I tested the solution in IE, Firefox and Opera and it works :)<br />The code is listed below:<br /><br />function alternateRowStyles()<br />{<br />var i, j;<br />var table;<br />for( i =0; i < document.getElementsByTagName("table").length; i++) { if( document.getElementsByTagName("table")[i].className == "altRows") { table = document.getElementsByTagName("table")[i]; for ( j = 0; j < table.rows.length; j++) { if (table.rows[j].className == "") { if (j%2 == 0 ) { table.rows[j].className = "oddClass"; } else { table.rows[j].className ="evenClass"; } } } } } }<br />You can see it in action at <a href="http://www.usnetcare.com">http://www.usnetcare.com</a><br /><br />If you want to download the code including a function that will wire the function to the onLoad event of the page you can do so at:<br /><a href="http://www.shkedy.com/blogfiles/01172007/formatting.js">http://www.shkedy.com/blogfiles/01172007/formatting.js</a>Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com1tag:blogger.com,1999:blog-9220430976744875095.post-85519186995986280782007-01-16T15:34:00.000-05:002007-01-17T06:33:33.857-05:00Creating GUIDs with Client Side JavaScriptI needed to generate <span class="blsp-spelling-error" id="SPELLING_ERROR_0" onclick="BLOG_clickHandler(this)">GUIDs</span> (<span class="blsp-spelling-corrected" id="SPELLING_ERROR_1">globally</span> unique identifiers) for a web project I was working on.<br />The only solutions I could see for that task involved evoking an <span class="blsp-spelling-error" id="SPELLING_ERROR_2" onclick="BLOG_clickHandler(this)">ActiveX</span> control which makes it really useless for general <span class="blsp-spelling-corrected" id="SPELLING_ERROR_3">purpose</span>. I ended up writing a s<span class="blsp-spelling-corrected" id="SPELLING_ERROR_4">hort</span> javascript that will <span class="blsp-spelling-corrected" id="SPELLING_ERROR_5">achieve</span> this task:<br /><br />function generateGuid()<br />{<br />var result, i, j;<br />result = '';<br />for(j=0; j<32; j++)<br />{<br />if( j == 8 || j == 12|| j == 16|| j == 20)<br />result = result + '-';<br />i = Math.floor(Math.random()*16).toString(16).toUpperCase();<br />result = result + i;<br />}<br />return result<br />}Sagi E. Shkedyhttp://www.blogger.com/profile/14339923662454470396noreply@blogger.com15