Tanveer Online

Configure Xdebug with phpDesigner

2012-08-27T10:16:00.000-07:00

1. Create a file with the name info.php in htdocs folder

<?php

phpinfo();

2. Check the version of PHP that you had installed / using .

http://localhost:8090/info.php

3. Go to http://xdebug.org/download.php

4. Download the correct version of the xDebug extension dll

In this case, I had downloaded php_xdebug-2.2.1-5.4-vc9.dll

5. Copy the downloaded file and paste it in the following folder

D:\PHP\XAMPP\php\ext

6. Ope the file D:\PHP\XAMPP\php\php.ini

7. Find the word / text "[xDebug]"

8. Paste the following lines of code just below the above searched tag

zend_extension="./ext/php_xdebug-2.2.1-5.4-vc9.dll"
xdebug.remote_enable=true
xdebug.remote_host=127.0.0.1
xdebug.remote_port=9000
xdebug.remote_handler=dbgp
xdebug.profiler_enable=1
xdebug.dump.*
xdebug.profiler_output_dir=D:\PHP\Program Files\XDebugCache

9. Restart the apache server

10. Start debugging in phpDesigner

Happy debugging !

CodeIgniter Tutorial and Sample Application Links

2012-08-24T04:56:00.002-07:00

Here are the few links i found on the web for CodeIgniter tutorial/sample application:

http://video.derekallard.com/
http://godbit.com/article/introduction-to-code-igniter
http://www.mrforbes.com/wordpress/2007/05/13/a-quick-code-igniter-and-query-ajax-tutorial/
http://www.maestric.com/wiki/doku.php?id=php:codeigniter_template
http://68kb.com/2007/10/28/templating-with-codeigniter/
http://swik.net/codeigniter+Tutorial
http://www.michaelwales.com/2007/09/live-tutorial-codeigniter-blog/
http://mini-app.peccavi.com/page/docs
http://www.jimohalloran.com/2007/09/23/building-a-complete-codeigniterapplication-part-2/
http://pr0digy.com/category/mootools/
http://pr0digy.com/archives/
http://www.alexajax.com/
http://bleakview.orgfree.com/obsession/
http://www.4webby.com/freakauth/tutorials/using-zend-framework-omponents-in-code-igniter
http://www.4webby.com/freakauth/tutorials/ci-swift-mailer
http://www.ngcoders.com/php/pquery-for-code-igniter-v-15x/
http://www.glossopteris.com/journal/post/table-relationships-in-ci
http://simplepie.org/
http://www.phpinsider.com/php/code/ContentFeeder/
http://www.ciforge.com/CodeIgniter_ApiDocs_1.4.1/

You can find more links here:
http://www.blinklist.com/tag/codeigniter/
http://swik.net/codeigniter

Fore more result google with the exact keyword below
site: code igniter tutorial
site: code igniter ajax
site: code igniter jquery
site: code igniter xajax
site: code igniter mootool

Note: this content is taken from the website

http://rakibulislam.wordpress.com/2007/11/07/codeigniter-tutorailsample-application-links/

Thanks to Rakibulislam for providing such wonderful list.

How to handel postback event in php

2012-08-22T00:22:00.002-07:00

Method 1.

if (!isset($_POST)) {
    //code here}

Method2.

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

  // handle POST here
}

Joining String

2012-06-25T02:24:00.003-07:00

DECLARE @categories varchar(1000)
SET @categories = NULL
SELECT @categories = COALESCE(@categories + ',','') + CONVERT(VARCHAR(20),WIndApplicationID) FROM dbo.WIndApplications
WHERE WIndFormTypeID = 4
PRINT @categories

SELECT ',' + CONVERT(VARCHAR(20),WIndApplicationID)
FROM dbo.WIndApplications
WHERE WIndFormTypeID = 4
FOR XML PATH('')

Component with CLSID XXX failed due to the following error: 80070005 Access is denied

2012-05-15T00:26:00.002-07:00

Issue:

Component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

[5/11/2012 2:02:45 PM] Error: Excel Extraction Failed :- Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

Solution:

1. In DCOMCNFG, right click on the My Computer and select properties.
2. Choose the COM Securities tab
3. In Access Permissions, click "Edit Defaults" and add Network Service to it and give it "Allow local access" permission. Do the same for <Machine_name>\Users.
4. In launch and Activation Permissions, click "Edit Defaults" and add Network Service to it and give it "Local launch" and "Local Activation" permission. Do the same for <Machine_name>\Users

Export To PDF

2012-05-09T00:48:00.000-07:00

private void ExportToPDF(DataTable pdtblData)
{
byte[] bytFileData = null;
string strfname = Services.IDGenerator.GetUniqueKeyWithPrefix(_strPerfix, 10) + ".pdf";
bytFileData = GeneratePDF(pdtblData);
if(bytFileData != null)
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + strfname);
Response.ContentType = "application/octet-stream";
Response.BinaryWrite(bytFileData);
Response.End();
}
else
{
ScriptManager.RegisterStartupScript(UpdatePanel3, UpdatePanel3.GetType(), "scriptEF", "alert('Error Occured')", true);
}
}

Export To Excel

2012-05-09T00:47:00.001-07:00

private void ExportToExcel(DataTable pdtblData)
{
string strFileData = string.Empty;
byte[] bytFileData = null;
string strfname = string.Empty;
strFileData = GetHTMLData(pdtblData);
strfname = Services.IDGenerator.GetUniqueKeyWithPrefix(_strPerfix, 10) + ".xls";
bytFileData = Encoding.ASCII.GetBytes(strFileData.ToString());
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + strfname);
Response.ContentType = "application/octet-stream";
Response.BinaryWrite(bytFileData);
Response.End();
}

Export To CSV

2012-05-09T00:46:00.003-07:00

private void ExportToCSV(DataTable pdtblData)
{
string strFileData = string.Empty;
byte[] bytFileData = null;
string strfname = string.Empty;
strFileData = GetCSVData(pdtblData);
bytFileData = Encoding.ASCII.GetBytes(strFileData.ToString());
strfname = Services.IDGenerator.GetUniqueKeyWithPrefix(_strPerfix, 10) + ".csv";
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + strfname);
Response.ContentType = "application/octet-stream";
Response.BinaryWrite(bytFileData);
Response.End();
}

Export To HTML

2012-05-09T00:46:00.001-07:00

public void ExportToHTML(DataTable pdtblData)
{
string strFileData = string.Empty;
byte[] bytFileData = null;
string strfname = string.Empty;
strFileData = GetHTMLData( pdtblData);
bytFileData = Encoding.ASCII.GetBytes(strFileData.ToString());
strfname = Services.IDGenerator.GetUniqueKeyWithPrefix(_strPerfix, 10) + ".html";
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + strfname);
System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
System.Web.HttpContext.Current.Response.BinaryWrite(bytFileData);
System.Web.HttpContext.Current.Response.End();

}

There was no endpoint at XXX that could accept the message

2012-05-09T00:43:00.000-07:00

Adding the belwo tag solved my problem of uploading the file more than 4 MB of size.

< system.web>
<httpRuntime maxRequestLength="65536" />
< /system.web>

However just for testing purpose I tried uploading file more than 20 MB of size.
The test failed :( and the new exception I got was

"There was no endpoint at XXX that could accept the message. This is often caused by an incorrect address or SOAP action"

My Service bindings was as below

<binding name="My_Binding" closeTimeout="00:10:00" openTimeout="00:10:00"
    receiveTimeout="00:10:00" sendTimeout="00:10:00" bypassProxyOnLocal="false"
    transactionFlow="false" hostNameComparisonMode="StrongWildcard"
    maxBufferPoolSize="2147483647" maxReceivedMessageSize="2147483647" messageEncoding="Text"
    textEncoding="utf-8" useDefaultWebProxy="true" allowCookies="false">
     <readerQuotas maxDepth="32" maxStringContentLength="2147483647" maxArrayLength="2147483647" maxBytesPerRead="2147483647" maxNameTableCharCount="2147483647" />
     <reliableSession ordered="true" inactivityTimeout="00:10:00" enabled="false" />
     <security mode="Message">
      <transport clientCredentialType="Windows" proxyCredentialType="None" realm="" />
      <message clientCredentialType="Windows" negotiateServiceCredential="true" algorithmSuite="Default" />
     </security>
    </binding>

Also I had <httpRuntime maxRequestLength="65536" /> in web.config

Googling through several article, finally I found a solution. Adding the below tag in my WCF Service help me to upload document more than 20 MB of size

<system.webServer>
     <security>
           <requestFiltering>
                    <requestLimits maxAllowedContentLength="204800000" />
          </requestFiltering>
   </security>
</system.webServer>

An error (The request was aborted: The request was canceled.) occurred while transmitting data over the HTTP channel.

2012-05-09T00:04:00.003-07:00

I was getting an error "An error (The request was aborted: The request was canceled.) occurred while transmitting data over the HTTP channel." when I was trying to upload / stream large file through WCF Service, the file I was using is of more than 4 MB.

When we sent small files through the service, all worked fine. However, larger files (about 4MB and higher) generated the very unhelpful http exception: (400) Bad Request. This exception occurs because the entire message is not processed.

We looked at the typical culprits of this problem like <binding> settings such as maxReceivedMessageSize and <readerQuota> values to no avail.

It turned out the real problem was not in WCF, but in IIS. HTTP communication has a size limit that you have to override if you want to send large messages to your WCF service hosted in IIS. In the config file (web.config), adjust the <httpRuntime>‘s maxRequestLength attribute to a large enough size to support your messages. The value will be an integer representing the size in Kilobytes and has about a 2GB limit. The setting looks something like this (I set mine to about 64MB):


< system.web>
          <httpRuntime maxRequestLength="65536" />
< /system.web>

If IIS is the problem, you should now be able to process messages up to the size indicated in the maxRequestLength attribute.

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

2012-04-20T01:30:00.002-07:00

To solve this error,
Add the following line of code in web.config

<system.webServer>
<validation validateIntegratedModeConfiguration="false" />
< /system.webServer>

Joins in SQL Server

2012-03-20T23:18:00.000-07:00

Lets get started - For this FAQ I'll be using an example of a fictious health care system. We have 2 simple tables for the initial discussion.

Doctors Patients
ID FullName MedCntrID ID FullName DocID
1 Joe Manners 1 1 Jim Thick 4
2 Sue Tongs 1 2 Tom Small 2
3 Jeff Spine 1 3 Al Downs 4
4 Mary Rasch 2 4 Ann Hills 1
5 Tom Thumb 2 5 Tim Burrow 3
6 Norm Lobe 3 6 Jane Fern 5
7 Sam Broom 2
8 Gary Far 1
9 Bill Out 5
10 Dave Bell 4
11 Fred Overs 5
12 Greg Double 1
13 Bob Marks 9

Now that we have our 2 tables lets see how we can join them

JOINS take on the format of

FROM Left_Table
JOIN_TYPE Right_Table
ON Join_Condition

INNER JOIN - This JOIN takes every record in the Left_Table and looks for 1 or more matches in the Right_Table based on the Join_Condition. If 1 is found the record is added to the result set. If more then one matching record is found in the Right_Table then there are multiple records added to the result set.

Lets look at this in action

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
FROM Doctors D
INNER JOIN Patients P
ON D.ID = P.DocID
The query exectutes and grabs the first record in the Left_Table Doctors D

1 Joe Manners 1

It then matches all records in the Right_Table Patients P based on the Join_Condition D.ID = P.DocID

4 Ann Hills 1
8 Gary Far 1
12 Greg Double 1

And produces this in the result set

DoctorName PatientName
Joe Manners Ann Hills
Joe Manners Gary Far
Joe Manners Greg Double

Then the next record in the Left_Table Doctors D is grabbed

2 Sue Tongs 1

Then all matching records in the the Right_Table Patients P for this record based on the Join_Condition D.ID = P.DocID are found and the records are added to the result set. The final result set for this query would look like this

DoctorName PatientName
Joe Manners Ann Hills
Joe Manners Gary Far
Joe Manners Greg Double
Sue Tongs Tom Small
Sue Tongs Sam Broom
Jeff Spine Tim Burrow
Mary Rasch Jim Thick
Mary Rasch Al Downs
Mary Rasch Dave Bell
Tom Thumb Jane Fern
Tom Thumb Bill Out
Tom Thumb Fred Overs

Note that Norm Lobe does not appear in the result set because no Patients have him listed as a doctor. Also Bob Marks does not appear in the result set because his doctor is not in the Doctors table

OUTER JOIN - This JOIN takes on 3 variations. All 3 have a similar function. These JOINs are designed to bring 2 tables together but include data even if there the Join_Condition is does not find a matching record(s). What it does is fill in the tables columns with NULLs. Lets take the the different types and talk about them.

LEFT OUTER JOIN - This JOIN, is a bit like the INNER JOIN. It takes the Left_Table and tries to match records based on the Join_Condition in the Right_Table. If record(s) are found in the Right_Table then they are match just as they would be in the INNER JOIN. If no match is found in the Right_Tablethen only 1 row is added to the result set for the record in the Left_Table and the columns that come from the Right_Table have the value of NULL

So a query of

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
FROM Doctors D
LEFT OUTER JOIN Patients P
ON D.ID = P.DocID

has a result set of

DoctorName PatientName
Joe Manners Ann Hills
Joe Manners Gary Far
Joe Manners Greg Double
Sue Tongs Tom Small
Sue Tongs Sam Broom
Jeff Spine Tim Burrow
Mary Rasch Jim Thick
Mary Rasch Al Downs
Mary Rasch Dave Bell
Tom Thumb Jane Fern
Tom Thumb Bill Out
Tom Thumb Fred Overs
Norm Lobe NULL

Note this is the same result set as the INNER JOIN but includes a the bolded record because the rule is that ever record in the Left_Table will end up in the result set.

RIGHT OUTER JOIN - This JOIN is just like the LEFT OUTER JOIN except the Right_Table is the table that will have every one of its records in the result set and if no record is found in the
Left_Table then its columns in the result set will be NULL

So a query of

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
FROM Doctors D
RIGHT OUTER JOIN Patients P
ON D.ID = P.DocID

has a result set of
DoctorName PatientName
Joe Manners Ann Hills
Joe Manners Gary Far
Joe Manners Greg Double
Sue Tongs Tom Small
Sue Tongs Sam Broom
Jeff Spine Tim Burrow
Mary Rasch Jim Thick
Mary Rasch Al Downs
Mary Rasch Dave Bell
Tom Thumb Jane Fern
Tom Thumb Bill Out
Tom Thumb Fred Overs
NULL Bob Marks

FULL OUTER JOIN - This JOIN is a combination of both. All records from both Left_Table and Right_Table are in the result set and matched when they can be on the Join_Condition when no record is found in the opposit table NULL values are used for the columns.
So a query of

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
FROM Doctors D
FULL OUTER JOIN Patients P
ON D.ID = P.DocID

has a result set of
Norm Lobe NULL
DoctorName PatientName
Joe Manners Ann Hills
Joe Manners Gary Far
Joe Manners Greg Double
Sue Tongs Tom Small
Sue Tongs Sam Broom
Jeff Spine Tim Burrow
Mary Rasch Jim Thick
Mary Rasch Al Downs
Mary Rasch Dave Bell
Tom Thumb Jane Fern
Tom Thumb Bill Out
Tom Thumb Fred Overs
NULL Bob Marks

Now there is one more Join type in T-SQL. This is not frequently used and I've personally come up with 1 use for it and that is filling up a table with dummy data to get a rough idea of performance. r937 has shown me a few other times you can use these and links to those Threads are at the bottom of this section.

CROSS JOIN This JOIN has a slightly different format in that it does not have a ON clause with a Join_Condition. This is because of the nature of the CROSS JOIN it doesn't need a join condition. What it does is perform a cartesian product of the tables involved in the join. This mean every row in the Left_Table is joined to every row in the Right_Table. For our tables with 6 doctors and 13 patients we would get a result set of 6x13 or 78 records. The query would look like

SELECT D.FullName AS DoctorName, P.FullName AS PatientName
FROM Doctors D
CROSS JOIN Patients P

The result set would look roughly like this

Joe Manners Jim Thick
Joe Manners Tom Small
Joe Manners Al Downs
Joe Manners Ann Hills
Joe Manners Tim Burrow
.
.
.
Norm Lobe Fred Overs
Norm Lobe Greg Double
Norm Lobe Bob Marks

following threads are some great examples of using cross joins by r937, thanks for enlightening me.

Thread183-755853 -- triple cross-join of the integers 0 through 9 to create the numbers which are used to generate a range of dates

Thread436-755873 -- cross join a single row of one table to unrelated rows of another

Thread701-728879 -- cross join two tables to get all possible combinations, then outer join to find the ones that are missing

Now there are the JOINs and how they work. What about when you want to join more then 2 tables?
Really you can only join 2 tables together. You just can have multiple sets of 2 tables and 1 single table may be joined to more then 1 other table, heck you can even join a table back to itself.

Some rules to follow to make things easier.
1) Use a LEFT OUTER JOIN over a RIGHT OUTER JOIN when ever possible. It will make things easier as you can always think of the Left_Table as the base table.

2) Start from you base table and work outwards towards the auxillary tables. Say you wanted to get a list of Patients with that have doctors on your register and pull the information about the Medical Center the doctor works at if it is avialable then you would do it like this

SELECT ....
FROM Patients P
INNER JOIN Doctors D *NOTE 1
ON P.DocID = D.ID
LEFT OUTER JOIN MedicalCenters MC *NOTE 2
ON D.MedCntrID = MC.ID

*NOTE 1 INNER JOIN because you only want patients that have a doctor that is on your register
*NOTE 1 Left OUTER JOIN because you want the information about the medical center if you have it but if you don't you still want the record to be included. If you made this a INNER JOIN then if a Doctor's medical center was not on record then the doctor would not be included in the result set meaing that any of their patients would not be on the result set since Patients and Doctors is done with an INNER JOIN too.

3) You can have multiple conditions for the ON clause just like you can in a WHERE clause. Make sure you know the difference between a JOIN condition and a WHERE clause. Though they look similiar in task they can execute slightly different giving unexpected results if you are not careful.

4) Use Alias in tables names they can make reading your queries much easiers

5) Format your queries for easy reading. A query doesn't run any faster if its all on 1 line or spread over 40 lines. As you can see from my formating style it is very easy to see what joins to what and the conditions they use which means less of a chance you made a error and will get unexpected results.

6) JOIN conditions are prime canidates for indexs. On small tables it doesn't matter but large tables will show great performance benifits from haveing the columns indexed.

I hope this little FAQ helps you understand JOINs better.

The content of this article is takne from http://www.tek-tips.com/faqs.cfm?fid=4785

Paging in SQL Server

2012-02-28T00:26:00.002-08:00

CREATE proc P_GET_PAGE

(

@p_page int,

@p_page_size int

)

as

begin

set nocount on

declare @total_rows int

declare @first_row int

declare @first_row_id int

select @total_rows = count(Num) from Users

select @first_row = (@p_page - 1) * @p_page_size + 1

if (@first_row <= @total_rows)

begin

set rowcount @first_row

select @first_row_id = Num

from Users

order by 1

set rowcount @p_page_size

select * from Users

where Num >= @first_row_id

order by 1

end

set nocount off

end

exec P_GET_PAGE 10, 10

DECLARE @PageNum AS INT;

DECLARE @PageSize AS INT;

SET @PageNum = 1;

SET @PageSize = 10;

WITH OrdersRN AS

(

SELECT ROW_NUMBER() OVER(ORDER BY Num) AS RowNum,

UserID,

UserName

FROM Users

)

SELECT UserID, UserName

FROM OrdersRN

WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1

AND @PageNum * @PageSize

ORDER BY Num

Getting Public Key Token of Assembly Within Visual Studio

2012-02-28T00:17:00.001-08:00

In Visual Studio, go to the Tools menu and choose External Tools.

That brings up a new dialog window. Click the Add button and add the following:

Title: Get &PublicKeyToken

Command: C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\sn.exe

Arguments: -Tp $(TargetPath)

Check the Use Output Window checkbox. The final looks like this:

Create a project and sign it using a strong name key. Then choose Tools / Get PublicKeyToken, and in the output window you will see something like the following:

Microsoft (R) .NET Framework Strong Name Utility  Version 3.5.21022.8
Copyright (c) Microsoft Corporation.  All rights reserved.

Public key is
0024000004800000940000000602000000240000525341310004000001000100d59451ff2ed310
447372c4d689f24dcece5aaaef6dddaffc3e43c36a9235586b33ec9e3121ad844ee521bd76fbc0
9a9a357bfeec32d87d8cd1278cd7697667263724e6ff1712e5ee3054542cfbb11b9241da118fbd
c1df7439ba13db77b63f8bf557c7f081946c02e32884c82806e0e95667e879d15b9a2912012398
76e0efa7

Public key token is 9589fa1be527eb6c

9589fa1be527eb6c is the Public Key Token

Referencing assemblies from folder other than /bin

2012-02-28T00:11:00.002-08:00

Add the below tag in web.config file of your web application

<runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="CoreFeature"
                          publicKeyToken="5e3a2279d3663dea"
                          culture="neutral" />
        <codeBase version="1.0.0.0" href="file:///F:/Test/References/CoreFeature.dll"/>
            </dependentAssembly>
    </assemblyBinding>
</runtime>

While Statement in T-SQL

2012-02-22T22:35:00.001-08:00

DECLARE @intFlag INT

DECLARE @endFlag INT

SET @intFlag = 1

SET @endFlag = 10

WHILE (@intFlag < = @endFlag)

BEGIN

PRINT @intFlag

SET @intFlag = @intFlag + 1

END

GO

Restoring Database

2012-02-22T22:31:00.000-08:00

USE [master]

ALTER DATABASE [Employee]

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [Employee] FROM

DISK = N'F:\Employee_Aug_08_2011.bak'

WITH FILE = 1, MOVE N 'Employee_Prod'

TO N'F:\Microsoft SQL Server Database Files\MSSQL10_50.TSTIM\MSSQL\DATA\Employee.mdf',

MOVE N'eSubmission_Prod_log' TO N'F:\Microsoft SQL Server Database Files\MSSQL10_50.TSTIM\MSSQL\DATA\Employee.ldf',

NOUNLOAD, REPLACE, STATS = 10

GO

USE [Employee]

ALTER DATABASE [Employee]

SET MULTI_USER;

Creating C# Properties with SQL Server Table Schema

2012-02-22T22:28:00.001-08:00

declare @TableName sysname = 'WAccessRequests'

declare @result varchar(max) = ''

select @result = @result + '

public ' + ColumnType + ' ' + ColumnName + ' { get; set; }

'

from

(

select replace(col.name, ' ', '_') ColumnName,

case typ.name

when 'bigint' then 'long'

when 'binary' then 'byte[]'

when 'bit' then 'bool'

when 'char' then 'char'

when 'date' then 'DateTime'

when 'datetime' then 'DateTime'

when 'datetime2' then 'DateTime'

when 'datetimeoffset' then 'DateTimeOffset'

when 'decimal' then 'decimal'

when 'float' then 'float'

when 'image' then 'byte[]'

when 'int' then 'int'

when 'money' then 'decimal'

when 'nchar' then 'char'

when 'ntext' then 'string'

when 'numeric' then 'decimal'

when 'nvarchar' then 'string'

when 'real' then 'double'

when 'smalldatetime' then 'DateTime'

when 'smallint' then 'short'

when 'smallmoney' then 'decimal'

when 'text' then 'string'

when 'time' then 'TimeSpan'

when 'timestamp' then 'DateTime'

when 'tinyint' then 'byte'

when 'uniqueidentifier' then 'Guid'

when 'varbinary' then 'byte[]'

when 'varchar' then 'string'

end ColumnType

from sys.columns col

join sys.types typ on

col.system_type_id = typ.system_type_id

where object_id = object_id(@TableName)

)
t

print @result

Output

(17 row(s) affected)

public short AccessRequestID { get; set; }

public short UserCreated { get; set; }

public short UserModified { get; set; }

public int SourceContactID { get; set; }

public DateTime IndividualDateOfBirth { get; set; }

public DateTime RegistrationDate { get; set; }

public DateTime DateCreated { get; set; }

public DateTime DateModified { get; set; }

public bool isRegistered { get; set; }

public bool ValidRequest { get; set; }

public string Num { get; set; }

public string IndividualName { get; set; }

public string ValidNumber { get; set; }

public string IndividualJobTitle { get; set; }

public string IndividualNationality { get; set; }

public string IndividualEmailAddress { get; set; }

public string RegistrationPassword { get; set; }

Using COALESCE to Build Comma-Delimited String

2012-02-22T04:53:00.001-08:00

declare @CategoryList varchar(1000)
select @CategoryList = coalesce(@CategoryList + ', ', '') + IndividualEmailAddress from dbo.WAccessRequests
select Results = + @CategoryList

Paging in SQL Server

2012-02-18T23:28:00.003-08:00

CREATE proc P_GET_PAGE

(

@p_page int,

@p_page_size int

)

begin

set nocount on

declare @total_rows int

declare @first_row int

declare @first_row_id int

select @total_rows = count(Num) from WFirms

select @first_row = (@p_page - 1) * @p_page_size + 1

if (@first_row <= @total_rows)

begin

set rowcount @first_row

select @first_row_id = Num

from WFirms

order by 1

set rowcount @p_page_size

select * from WFirms

where Num >= @first_row_id

order by 1

end

set nocount off

end

exec P_GET_PAGE 10, 10

DECLARE @PageNum AS INT;

DECLARE @PageSize AS INT;

SET @PageNum = 1;

SET @PageSize = 10;

WITH OrdersRN AS

(

SELECT ROW_NUMBER() OVER(ORDER BY Num) AS RowNum,

Firmname,

Num

FROM Wfirms

)

SELECT Num, FirmName

FROM OrdersRN

WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1

AND @PageNum * @PageSize

ORDER BY Num

Invalid object name = 'tempdb.dbo.ASPStateTempApplications'.

2012-02-16T06:11:00.000-08:00

Execute the below script to solve the error : Invalid object name = 'tempdb.dbo.ASPStateTempApplications'.

USE [ASPState]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[CreateTempTables]

SELECT 'Return Value' = @return_value

GO

Search Through Stored Procedure

2011-10-19T05:47:00.000-07:00

How to find if particular table is being used in the stored procedure?
How to search in stored procedures?

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Docs%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%FileLoc%'
go

Using sp_send_dbmail from a SQL-Agent job gives error EXECUTE permission denied.

2011-10-10T01:13:00.000-07:00

Executed as user: domain\user1. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.

To solve this issue, execute the below line
ALTER DATABASE [SampleDB] SET TRUSTWORTHY ON

Ref: http://technet.microsoft.com/en-us/library/ms187861.aspx
http://blog.arjanfraaij.com/2011/06/using-spsenddbmail-from-sql-agent-job.html

Active Directory With C#

2011-08-17T03:07:00.001-07:00

very good link to AD programming using C#

http://www.ianatkinson.net/computing/adcsharp.htm