Searching word in Stored Procedure

Sometime it's very useful if we can search word in Stored Procedure for debugging purposes or etc. Use below script to search with stored procedure using table called "tblLookup"

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tblLookup%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME ASC

64-bit or 32-bit / x64 or x84

It's always come to the point that we need to know our system is 64-bit or 32-bit / x64 or x84 when we download some installer or choose installation option.

Below is how we determine 64-bit or 32-bit / x64 or x84 for different windows OS.

Windows Vista

Method 1
1.Click Start, type system in the Start Search box, and then click system in the Programs list.
2.The operating system appears as follows:
• For a 64-bit version operating system: 64-bit Operating System appears for the System type under System.
• For a 32-bit version operating system: 32-bit Operating System appears for the System type under System.

Method 2
1.Click Start, type system in the Start Search box, and then click System Information in the Programs list.
2.The operating system appears as follows:
• For a 64-bit version operating system: x64-based PC appears for the System type under Item.
• For a 32-bit version operating system: x86-based PC appears for the System type under Item.

Microsoft Windows XP Professional

Method 1
1.Click Start, click Run, type sysdm.cpl, and then click OK.
2.Click the General tab. The operating system appears as follows:
• For a 64-bit version operating system: Microsoft Windows XP Professional x64 Edition Version <year> appears under System.
• For a 32-bit version operating system: Microsoft Windows XP Professional Version <year> appears under System. Note is a placeholder for a year.

Method 2
1.Click Start, click Run, type winmsd.exe, and then click OK.
2.In the details pane, locate Processor under Item. Note the value.
• If the value that corresponds to Processor starts with x86, the computer is running a 32-bit version of the Windows operating system.
• If the value that corresponds to Processor starts with ia64 or AMD64, the computer is running a 64-bit version of the Windows operating system.

Microsoft Windows Server 2003

Method 1
Click Start, click Run, type sysdm.cpl, and then click OK.
Click the General tab. The operating system appears as follows:
• For a 64-bit version operating system: Microsoft Windows Server 2003 Enterprise x64 Edition appears under System.
• For a 32-bit version operating system: Microsoft Windows Server 2003 Enterprise Edition appears under System.

Method 2
1.Click Start, click Run, type winmsd.exe, and then click OK.
2.In the details pane, locate Processor under Item. Note the value.
• If the value that corresponds to Processor starts with x86, the computer is running a 32-bit version of the Windows operating system.
• If the value that corresponds to Processor starts with EM64T or ia64, the computer is running a 64-bit version of the Windows operating system.

reference: http://support.microsoft.com/kb/827218

Invalid use of 'EXECUTE' within a function

I've tried to create a MSSQL "User Defined Functions" with execution of dynamic query (return a final value) and hit error message: "Invalid use of 'EXECUTE' within a function"

The reason is MSSQL not allow execution of dynamic query in "User Defined Functions". Because of this, i need to change from user "User Defined Functions" to "stored procedure" SP by using sp_executesql.

Below show example the Stored Procedure that i use to overcome my problem.

I need my SP to return a calculated value.

CREATE PROCEDURE dbo.psp_GetOCPrice
(
@strSKUNo VARCHAR(50),
@BOMT NUMERIC(9,2),
@OtherCharges NUMERIC(9,2) OUTPUT
)
AS
BEGIN

DECLARE @Count INT,
@Max INT,
@Cost NUMERIC(9,2),
@SQL NVARCHAR(4000),
@Formula NVARCHAR(1000),
@othMainIdn INT,
@othIdn INT


DECLARE @tblOthCharges TABLE
(
ID INT IDENTITY(1,1),
SKUNo VARCHAR(50),
othIdn INT,
othMainIdn INT,
Formula VARCHAR(1000),
Cost NUMERIC(9,2)
)

INSERT INTO @tblOthCharges(SKUNo, othIdn, othMainIdn, Formula)
SELECT sm.SKUNo, sm.pid, sm.ItemGroup,ISNULL(sm.Formula,0) Formula
FROM tblSKUMat sm
WHERE SKUNo = @strSKUNo
AND sm.othCharFlag = 1 ORDER BY sm.seqID ASC

SET @BOMT = ISNULL(@BOMT,0)

SET @Max = (SELECT COUNT(ID) FROM @tblOthCharges)

SET @Count = 1
SET @Cost = 0

SET @SQL = ''

SET @SQL = @SQL + ' DECLARE @BOM NUMERIC(9,2), ' + char(13)
SET @SQL = @SQL + ' @Cost NUMERIC(9,2) ' + char(13)
SET @SQL = @SQL + ' SET @BOM = ' + CONVERT(VARCHAR,@BOMT) + ' ' + char(13)
SET @SQL = @SQL + ' SET @rOtherCharges = 0 ' + char(13)

WHILE @Count <= @Max BEGIN SELECT @Formula = Formula, @othMainIdn = othMainIdn, @othIdn = othIdn FROM @tblOthCharges WHERE ID = @Count

SET @SQL = @SQL + ' SELECT @Cost = ' + CONVERT(VARCHAR,@Formula) + char(13)

SET @SQL = @SQL + ' DECLARE @v' + CONVERT(VARCHAR,@othMainIdn) + '_value NUMERIC(9,2) '

SET @SQL = @SQL + char(13)

SET @SQL = @SQL + ' SET @v' + CONVERT(VARCHAR,@othMainIdn) + '_value = ISNULL(@Cost,0) '

SET @SQL = @SQL + char(13)

SET @SQL = @SQL + ' SET @rOtherCharges = @rOtherCharges + ISNULL(@Cost,0) ' + char(13)

SET @Count = @Count + 1
END

DECLARE @ParmDefinition NVARCHAR(500);

SET @ParmDefinition = N'@rOtherCharges NUMERIC(9,2) OUTPUT';

EXECUTE sp_executesql
@SQL,
@ParmDefinition,
@rOtherCharges = @OtherCharges OUTPUT;

END

GO

i need to use dynamic query because the field "Formula" is mathematics expression where involve calculation using value from parameter.
By using sp_executesql, the value for @rOtherCharges will be assigned to @OtherCharges OUTPUT.

To call this SP, simply call execution

Declare @OtherCharges
Execute dbo.psp_GetOCPrice 'XIN200805010001', 2000.20, @OtherCharges NUMERIC(9,2)

Done!

Create View using Stored Procedure dynamically

We can create view using stored procedure dynamically.

Please find my sample below:
I want to create a view name v_rpt + @Type and @Type as parameter. If the view already exists, it will be drop and recreate a new one.

We will need to execute @SQL twice

• execution 1: check whether the view exists, drop it if exists
• execution 2: create a new view

if we put both in the same query, it will hit error "'CREATE VIEW' must be the first statement in a query batch."


CREATE PROCEDURE [dbo].psp_Generate_view
(
@Type VARCHAR(255)
)
AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)

SET @SQL = ''
SET @SQL = @SQL + 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N''[dbo].[v_rpt' + CONVERT( VARCHAR, @Type) + ']'') AND OBJECTPROPERTY(id, N''IsView'') = 1) ' + char(13)
SET @SQL = @SQL + 'DROP VIEW [dbo].[v_rpt' + CONVERT( VARCHAR, @Type) + '] ' + char(13)

EXECUTE (@SQL)

SET @SQL = ''
SET @SQL = @SQL + 'CREATE VIEW dbo.v_rpt' + CONVERT( VARCHAR, @Type) + ' AS ' + char(13)
SET @SQL = @SQL + 'SELECT DISTINCT '
SET @SQL = @SQL + 's.SKUNo, '
SET @SQL = @SQL + 's.Model, '
SET @SQL = @SQL + 'lk1.LookupDesc [Region], '
SET @SQL = @SQL + 'lk2.LookupDesc [Country], '
SET @SQL = @SQL + 'CASE s.Status '
SET @SQL = @SQL + ' WHEN 1 THEN ''Saved'' '
SET @SQL = @SQL + ' WHEN 2 THEN ''Rejected'''
SET @SQL = @SQL + ' WHEN 3 THEN ''Approved'''
SET @SQL = @SQL + 'END AS Status, '
SET @SQL = @SQL + 's.Remark, '
SET @SQL = @SQL + 'u1.FullName [Creator], '
SET @SQL = @SQL + 's.CreDte [Date of Creation], '
SET @SQL = @SQL + 'u2.FullName [UpdatedBy], '
SET @SQL = @SQL + 's.LastUpdDte [Updated On], '
SET @SQL = @SQL + 'FROM tblSKU s '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblUser u1 ON s.OwnerID = u1.UserID '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblUser u2 ON s.LastUserID = u2.UserID '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblLookup lk1 ON s.Region = lk1.RefIdn AND lk1.GroupIdn = 1 '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblLookup lk2 ON s.CTY = lk2.RefIdn AND lk2.GroupIdn = 2 '
SET @SQL = @SQL + 'WHERE '
SET @SQL = @SQL + 's.Type= ' + CONVERT( VARCHAR, @Type) + ' AND '

EXECUTE (@SQL)


SET NOCOUNT OFF
END

Please let me know if you have any comment.

The worst wireless modem router that have ever used

Trust me, don't go to get Aztech modem. You will regrets if you have one.

Aztech DSL600EW ADSL2/2+ Ready Wireless LAN 802.11g is the worst modem that i ever had. The wireless line simply disconnected and need to restart the router, the ADSL frequently disconnected with the service provider, the stupid un-userfriendly interface (even a simple port forwarding takes me hour to configure and yet cannot be done). I bet you will definately throw it out from your house or office.

VBScript: Open new windows (IE)

To open new window using client side vbscript,
use open "url", "target", "options"

example:

Sub fnOpenUrl(url)

open url ,"newWindow", "toolbar=no, menubar=no, status=no, width=830, height=660 titlebar=no, scrollbars=yes, resizable=yes, top=5, left=5 "

End Sub

to call the function

fnOpenUrl("http://www.xincrm.com")

Done.

DefaultButton for ImageButton not working in firefox

Well, read this article first before finding other solution http://support.microsoft.com/kb/921277

I've been trying to look around and work on the code but it's not working. Don't waste your time for simple problem unless you have plenty of them.

The easiest way is change the ImageButton control to Button control as stated in the article. Put in some css will give the same effect/result in term of display like in ImageButton.

Below is the css that i use for this purposes.

.buttoncss
{
width:98px;
height:43px;
background-color:#FFFFFF;
background-image:url(http://xincrm.com/img/Button.gif);
border-style:none;
cursor:pointer;
}

Try this http://www.pagetutor.com/button_designer/index.html if you want to customize your button css. Very useful for me.

Remember set UseSubmitBehavior for Button control to False in Properties window.

UseSubmitBehavior="False"


it's work for me. My problem solved.

System.Runtime.InteropServices.ExternalException: A generic error occurred in GDI+.

Try to upload picture and hit below error.

A generic error occurred in GDI+.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.ExternalException: A generic error occurred in GDI+.

Source Error:

Line 469: MemoryStream mem = new MemoryStream();
Line 470: resizedImage.Save(mem, jpegICI, encoderParams);
Line 471: resizedImage.Save(filePath + "\\" + fileName, ImageFormat.Jpeg);
Line 472:

Source File: c:\Inetpub\wwwroot\BlogA\Account.aspx.cs Line: 471

Stack Trace:

[ExternalException (0x80004005): A generic error occurred in GDI+.]
System.Drawing.Image.Save(String filename, ImageCodecInfo encoder, EncoderParameters encoderParams) +397778
System.Drawing.Image.Save(String filename, ImageFormat format) +69
Account.WarpImageDimensions(String filePath, String fileName, Bitmap imageToSave) in c:\Inetpub\wwwroot\BlogarateNewLayout\Account.aspx.cs:471
Account.ImageButton1_Click(Object sender, ImageClickEventArgs e) in c:\Inetpub\wwwroot\BlogA\Account.aspx.cs:392
System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +86
System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +115
System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

in my case:

It's caused by 2 thing:
1. Upload path - i put the wrong path
2. Folder permission - set the correct permission. In my case, i allow write permission for internet user (IUSR_xxxxx).

Problem solved.

Manage/Delete IIS log files using scheduler

Microsoft have provide a very good resource kits. Forfiles is one the them that we can use to manage our log files especially IIS log. My purpose for this article is to show how i use forfiles to help me to manage/delete my IIS log file more than 14 days (you can delete any time frame you want).

Steps:
1. download and install Windows Server 2003 Resource Kit Tools
2. write a bat script
3. create a scheduler


Let look at the details

1. download and install Windows Server 2003 Resource Kit Tools

In order to us it, you need to install it in your server first. Go to microsoft website "Windows Server 2003 Resource Kit Tools" and download it. After finish downloading, you can start to install it.
After finish installation, you can find Forfiles in C:\WINDOWS\system32\forfiles.exe


2. write a bat script

Syntax for forfiles

forfiles [/p Path] [/m SearchMask] [/s] [/c Command] [/d[{+ -}] [{MM/DD/YYYY DD}]]

the details syntax you can get it from technet.

For me, i want to delete the log files date 14 days before. Below are some of the details

1. log file location = C:\WINDOWS\system32\LogFiles\W3SVC1
2. file type = *.log
3. Date to delete = -d -14 (14 days before)

Below is the command that i'm able to come out and save it as .bat format. Say, "managelog.bat"

Forfiles -p C:\WINDOWS\system32\LogFiles\W3SVC1 -s -m *.log -d -14 -c "Cmd /C DEL @File"

copy forfiles.exe from C:\WINDOWS\system32\forfiles.exe and managelog.bat to the same location which you prefer.

3. create a scheduler

You need to create a scheduler to run the job automatically. For me, i run my job weekly at mid night 12:30AM.

To know how to create a window scheduler job, please find reference here.

Please test it before make it run automatically. I suggest that you use below command line to test. I change the DEL to ECHO so that i work safely.

Forfiles -p C:\WINDOWS\system32\LogFiles\W3SVC1 -s -m *.log -d -14 -c "Cmd /C ECHO @File"

Done. Good luck.

Shrink Database MSSQL

Log file grow until very huge file? Check this step to shrink sql ldf file http://dotnetfish.blogspot.com/2007/12/shrinking-transaction-log.html