Update with Inner Join SQL Server

Problem

 

I have two tables (table1 and table2). I want to set a flag in table1 for
each common row with table2. I

 

 

Solution:

 

Update

iwf_requestdetails

set

LastUpdatedStatus=f.Status_ID

from

iwf_requestdetails i innerjoin [FinalWorkflowDetails1] f

on

i.[FormNo] = f.[FORM_NO]

Sqlserver : Looping through temporary table

CREATE

TABLE #ActiveCustomer

(

 

RowID

intIDENTITY(1, 1),

formno

varchar(50),

adddel

varchar(50),

optionid

varchar(50)

)

DECLARE

@NumberRecords int, @RowCount

int

DECLARE

 

 

@form_no varchar(50),@previousform_no varchar(50),@add_del varchar(50), @option_id varchar(50

)

 

 

 

INSERT

INTO #ActiveCustomer(formno,adddel,optionid

)

 

select form_no, add_del, option_id from dbo.iws_kapiti_optionsorderby form_no

SET

@NumberRecords =

@@ROWCOUNT

 

SET

@RowCount =1

 

 

WHILE

@RowCount <= @NumberRecords

BEGIN

SELECT

@form_no = formno,@add_del = adddel,@option_id = optionid

FROM

#ActiveCustomer

WHERE

RowID = @RowCount

if

@add_del =

‘A’

 

begin

end

if

@add_del =

‘D’

 

begin

end

 

SET @RowCount = @RowCount + 1

END

 

DROP

TABLE#ActiveCustomer

 

 

sqlserver cursor

ALTER

procedure [bouser].[InsertTransfer_delete]

as

declare @form_no asvarchar(50)declare @from_br asvarchar(50)declare @to_br asvarchar(1000)declare @reccount asinteger

 

set @reccount = 0declare iws_transfr_detail_cursor cursorforselect form_no, from_br, to_br from dbo.iws_transfr_detail

 

open iws_transfr_detail_cursorfetchnextfrom iws_transfr_detail_cursor into @form_no, @from_br, @to_brwhile@@FETCH_STATUS= 0Begin

 

UPDATE IWF_RequestDetails SET NewBranchCode = @from_br , OldBranchCode = @to_brwhere FormNo = @form_noset @reccount = @reccount + 1fetchnextfrom iws_transfr_detail_cursor into @form_no, @from_br, @to_br

 

print @reccountEndclose iws_transfr_detail_cursordeallocate iws_transfr_detail_cursor

SqlServer Lessons

INSERT INTO VALUES

The syntax for inserting data into a table one row at a time is as follows:

INSERT INTO “table_name” (“column1″, “column2″, …)
VALUES (“value1″, “value2″, …)

Assuming that we have a table that has the following structure,

Table Store_Information
Column Name Data Type
store_name char(50)
Sales float
Date datetime
and now we wish to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999. On that day, this store had $900 in sales. We will hence use the following SQL script:

INSERT INTO Store_Information (store_name, Sales, Date)
VALUES (‘Los Angeles’, 900, ‘Jan-10-1999′)
INSERT INTO SELECT

The second type of INSERT INTO allows us to insert multiple rows into a table. Unlike the previous example, where we insert a single row by specifying its values for all columns, we now use a SELECT statement to specify the data that we want to insert into the table. If you are thinking whether this means that you are using information from another table, you are correct. The syntax is as follows:

INSERT INTO “table1″ (“column1″, “column2″, …)
SELECT “column3″, “column4″, …
FROM “table2″

Note that this is the simplest form. The entire statement can easily contain WHERE, GROUP BY, and HAVING clauses, as well as table joins and aliases.

So for example, if we wish to have a table, Store_Information, that collects the sales information for year 1998, and you already know that the source data resides in the Sales_Information table, we’ll type in:

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

Here I have used the SQL Server syntax to extract the year information out of a date. Other relational databases will have different syntax. For example, in Oracle, you will use to_char(date,’yyyy’)=1998.

 ——————

SQL Server: INSERT INTO with SubQuery
October 11, 2008 by decoding

Introduction

This is a short tutorial on how to insert data with a subquery on SQL Server databases.
The reason I am writing about it is that the required syntax is not that obvious!

Sample tables and data

Let’s create two simple tables for this tutorial with the names ‘Customers’ and ‘Orders’.
view sourceprint?
1 CREATE TABLE [dbo].[Customers]( 

2     [id] [int] IDENTITY(1,1) NOT NULL, 

3     [name] [varchar](100) NOT NULL, 

4     [username] [varchar](20) NOT NULL

5 )
view sourceprint?
1 CREATE TABLE [dbo].[Orders]( 

2     [order_id] [int] IDENTITY(1,1) NOT NULL, 

3     [customer_id] [int] NOT NULL, 

4     [order_date] [datetime] NOT NULL

5 )

We are also going to insert a new row on table ‘Customers’:
view sourceprint?
1 INSERT INTO Customers 

2 VALUES (‘John Smith’,'john.smith’)

Inserting data with a subquery

The obvious syntax to insert data into the orders table with a subquery would be:
view sourceprint?
1 INSERT INTO Orders 

2 VALUES ((SELECT id FROM Customers WHERE username = ‘john.smith’), GETDATE())

However if you try the above query, you would end up with the following message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

The correct syntax to accomplish our task is:
view sourceprint?
1 INSERT INTO Orders 

2 SELECT id, GETDATE() FROM Customers 

3 WHERE username = ‘john.smith’

or if you would like to specify the columns:
view sourceprint?1 INSERT INTO Orders (customer_id, order_date) 

2 SELECT id, GETDATE() FROM Customers 

3 WHERE username = ‘john.smith’

————————————

Following three questions are many time asked on this blog.

How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?

There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
—-Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
—-INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
—-Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

———————————

sqlserver replace character in position :::::

Using REPLACE in an UPDATE statement

By Bill Graziano on 31 March 2010 | 12 Comments | Tags: UPDATE, Functions

---

This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment.

Replace searches for certain characters in a string and replaces them with other characters. So this statement:

 

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'Rolls')

will return

SQLTeam.com Rolls!

REPLACE searches the the first string for any occurance of the the second string and replaces it with the third string. You can also do replacements of different sizes. For example,

 

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'is cool')

gives us

SQLTeam.com is cool!

I replaced a five character string with a seven character string with no problem. If the string isn’t found, no changes will be made.

 

SELECT Replace('SQLTeam.com Rocks!', 'Yak', 'Tibetan bison')

returns exactly what we started with which is

SQLTeam.com Rocks!

If it doesn’t find anything to change it just returns the string unchanged. You can use REPLACE in an UPDATE statement. Using the pubs database we could write:

 

Update dbo.authors
Set    city = replace(city, 'Salt', 'Olympic');

There were two authors that had “Salt Lake City” in the CITY field. Now that field holds “Olympic Lake City” for those two authors. The CITY field is unchanged for all the other authors.

A more common approach is to use this in conjuntion with a WHERE clause like this:

 

UPDATE dbo.authors
SET    city = replace(city, 'Salt', 'Olympic')
WHERE  city LIKE 'Salt%';

This only affects the rows that start with ‘Salt’

—————————

1. CREATE FUNCTION fnReplaceCharAtPos(@Str varchar(8000),@Pos int, @Chr char(1))
2. RETURNS varchar(8000) AS  
3. BEGIN 
4. declare @Res varchar(8000)
5. set @Res=left(@Str,@Pos-1) + @Chr  + right(@Str,len(@Str)-@Pos)
6. return @Res
7. END
8.  

and call it in a query with

Expand|Select|Wrap|Line Numbers

1. select dbo.fnReplaceCharAtPos(str,8,’Z')
2. from YourTable

—————————-

SQL Functions – STUFF() and REPLACE()

by Vishal

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.

– © 2011 – Vishal (http://SqlAndMe.com)

DECLARE @string1 VARCHAR(20) = ‘Microsoft Server’

DECLARE @string2 VARCHAR(20) = ‘SQL Server 2005′

SELECT      @string1 + ‘ -> ‘ + STUFF(@string1, 11, 0, ‘SQL ‘)

            AS ‘String 1′,

            @string2 + ‘ -> ‘ + STUFF(@string2, 15, 1, ’8 R2′)

            AS ‘String 2′

Result Set:

String 1                                 String 2

—————————————- ————————————-

Microsoft Server -> Microsoft SQL Server SQL Server 2005 -> SQL Server 2008 R2

(1 row(s) affected)

In the first string it inserts ‘SQL ‘ at specified position – 11, the third argument 0 indicated the number of characters to be deleted before inserting the new string.

For second string, we have deleted one (1) character starting from position 15, which deletes ’5′, and then it inserts the new string at position 15 – ’8 R2′.

REPLACE():

REPLACE() replaces all the specified characters with new characters.

DECLARE @string3 VARCHAR(35) = ‘sql 2005, sql 2008, sql 2008 r2′

SELECT @string3, REPLACE(@string3,’sql’,'SQL’)

Result Set:

———————————–      ———————————–

sql 2005, sql 2008, sql 2008 r2   SQL 2005, SQL 2008, SQL 2008 r2

(1 row(s) affected)

However, it is not limited to same number of characters:

DECLARE @string3 VARCHAR(35) = ’2008 R2′

SELECT @string3, REPLACE(@string3,’20′,’SQL Server 2′)

Result Set:

————–       ————————

2008 R2              SQL Server 208 R2

(1 row(s) affected)

Hope This Helps! Cheers!

Reference : Vishal (http://SqlAndMe.com)

———————-

 

Create User defined function and how to call UDF
CREATE FUNCTION fx_SumTwoValues
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
  RETURN (@Val1+@Val2)
ENDThe structure of the CREATE FUNCTION statement is fairly straightforward. You provide an object name (fx_SumTwoValues), input parameters (@Val1 and @Val2), the type of data the function will return () and the statement(s) the function executes are located between the BEGIN…END block. The following SELECT statement calls the function. Note that the two-part name (owner.object_name) is required when calling this function.
SELECT dbo.fx_SumTwoValues(1,2) AS SumOfTwoValues

SumOfTwoValues
————–
3

 

SQL SERVER – Retrieve Last Inserted Identity of Record

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Sqlserver Self Join

How To Use Self Join In Sql Server 2000 2005

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.

For self join in sql you can try the following example:

Create table employees:

emp_id	emp_name	emp_manager_id
1	John	Null
2	Tom	1
3	Smith	1
4	Albert	2
5	David	2
6	Murphy	5
7	Petra	5

 

Now to get the names of managers from the above single table you can use sub queries or simply the self join.

Self Join SQL Query to get the names of manager and employees:

select e1.emp_name ‘manager’,e2.emp_name ‘employee’
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Result:

manager	employee
John	Tom
John	Smith
Tom	Albert
Tom	David
David	Murphy
David	Petra

 

Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.

from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

Introducing Dynamic Management Objects (DMO) in Sqlserver

Dynamic Management Objects (DMO)

Dynamic Management Views(DMV) ,  Dynamic Management Functions(DMF)

Execution :  Starts with sys.dm_exec

1 . sys.dm_exec_connections ( Get a count of SQL connections by IP address)

2. Session Ownership (sys.dm_exec_sessions)

3. Current expensive, or blocked, requests (sys.dm_exec_requests ,sys.dm_exec_sql_text)

4 .Query Stats – Find the “top X” most expensive cached queries  (sys.dm_exec_query_stats)

5 How many single-use ad-hoc Plans? (sys.dm_exec_cached_plans)

6 DMV#6: Ad-hoc queries and the plan cache (sys.dm_exec_cached_plans and sys.dm_exec_sql_text)

7 Investigate expensive cached stored procedures ( sys.dm_exec_procedure_stats )

8  Find Queries that are waiting, or have waited, for a Memory Grant (sys.dm_exec_query_memory_grants)

Transactions : begin with “sys.dm_tran_”.

1. Monitor long-running transactions (    sys.dm_tran_database_transactions ,  sys.dm_tran_session_transactions)

2. Identify locking and blocking issues (    sys.dm_tran_locks )

Database and Indexes:  Starts with sys.dm_db_

1. sys.dm_db_missing_index_groups
2. sys.dm_db_missing_index_group_stats
3. sys.dm_db_missing_index_details
4. Interrogate Index usage            sys.dm_db_index_usage_stats,
5. Table Storage Stats (Pages and Row Counts)        sys.dm_db_partition_stats
6. Monitor TempDB         sys.dm_db_file_space_usage

Disk I/O

1.  sys.dm_exec_query_stats – IO that a given query has cost over the times it had been executed

2.  sys.dm_exec_connections – IO that has taken place on that connection

3.  sys.dm_exec_sessions – IO that has taken place during that session

4.  sys.dm_os_workers – IO that is pending for a given worker thread

5.  Investigate Disk Bottlenecks via I/O Stalls ( sys.dm_io_virtual_file_stats)

6.  Investigate Disk Bottlenecks via Pending I/O  (sys.dm_io_pending_io_requests)

Operating System :  “sys.dm_os_”,

1. sys.dm_os_wait_stats(Returns information about all the waits encountered by threads that executed)
2. sys.dm_os_performance_counters(exposes the PerfMon counters)
3. sys.dm_os_sys_info  (Basic CPU Configuration)
4. sys.dm_os_ring_buffers(CPU Utilization History)
5. sys.dm_os_schedulers (Monitor Schedule activity)
6. sys.dm_os_sys_memory  (System-wide Memory Usage)
7. sys.dm_os_sys_memory  (System-wide Memory Usage)
8. sys.dm_os_memory_cache_counters (Investigate Memory Usage Across all Caches)
9. sys.dm_os_memory_cache_counters (Investigate Memory Usage Across all Caches)

Miscellaneous

1. sys.dm_clr_tasks Rooting out Unruly CLR Tasks
2. sys.dm_fts_active_catalogs Full Text Search
3. dm_db_mirroring_auto_page_repair Page Repair attempts in Database Mirroring

 

Download the pdf : http://ahref=

Download the word document : Important Notes

http://ahref=

General Notes

Convert.ToDateTime(date1).ToString(“dd-MMM-yyyy”);

—–

declare @myxml xml;

select @myxml = ‘<ParameterValues>

<ParameterValue>
<Name>TO</Name>
<Value>myemail@mail.com</Value>
</ParameterValue>

<ParameterValue>
<Name>BCC</Name>
<Value>myemail@mail.com</Value>
</ParameterValue>

<ParameterValue>
<Name>IncludeReport</Name>
<Value>True</Value>
</ParameterValue>

<ParameterValue>
<Name>Subject</Name>
<Value>Customers in Switaly</Value>
</ParameterValue>

</ParameterValues>’

–select @myxml

–select @myxml.query(‘/ParameterValues/ParameterValue/Value[../Name = ''Subject'']‘)
select @myxml.query(‘/ParameterValues/ParameterValue/Value12′)

=======================

select * from wsiagingreport
select [XmlQuerySql].value(‘(/ROOT/Slot1/SlotPeriodValue)[1]‘, ‘varchar(200)’) as Slot1PeriodValue,
[XmlQuerySql].value(‘(/ROOT/Slot1/SlotPeriodType)[1]‘, ‘varchar(200)’) as SlotPeriodType,
[XmlQuerySql].value(‘(/ROOT/Slot1/ThresholdType)[1]‘, ‘varchar(200)’) as ThresholdType,
[XmlQuerySql].value(‘(/ROOT/Slot1/ThresholdValue)[1]‘, ‘varchar(200)’) as ThresholdValue
from wsiagingreport

====================================

Except in sqlserver is equivalent to Minus in ORACLE

===

usage of case when then else end
………………………….
select @Slot1_Count = count(*) from wsiincident
where @Slot1_PeriodValue >=
case when @Slot1_PeriodType =’Hour’
     then datediff(hour, sourcetoolcreateddate,getdate())  
     when @Slot1_PeriodType =’Day’
     then datediff(Day, sourcetoolcreateddate,getdate())
     when @Slot1_PeriodType =’Week’
     then datediff(Week, sourcetoolcreateddate,getdate())
     when @Slot1_PeriodType =’Fortnight’
     then datediff(Day, sourcetoolcreateddate,getdate())/14
     when @Slot1_PeriodType =’Month’
     then datediff(Month, sourcetoolcreateddate,getdate())
     when @Slot1_PeriodType =’Quarter’
     then datediff(Quarter, sourcetoolcreateddate,getdate())
     when  @Slot1_PeriodType =’Year’
     then datediff(Year, sourcetoolcreateddate,getdate())
     else 0 end

===================================

select datediff(Year,’2003-12-12′,getdate())
declare @ss nvarchar(100)
set @ss = ‘h’
select case when @ss =’h’ then datediff(hour,’2003-12-12′,getdate())
   when @ss =’m’ then datediff(minute,’2003-12-12′,getdate())
   when @ss =’s’ then datediff(second,’2003-12-12′,getdate())
   else 0 end

============================================
suppose in a sp u need like this

create proc p_c
as
begin
select * from table
where names in (‘s,g,d’)
end  .. this will work

but
create proc p_c
@strings nvarchar(1000)
as
begin
select * from table
where names in (@strings )
end  .. this wont work as strings is a parameter.

To solve this problem
create a splitstring function and split the string and insert into a temporary table

create proc p_c
@strings nvarchar(1000)
as
begin
select * from table
where names in (select names from db0.splitstrings(@strings))
end  .. this will work
splitstring function is:
ALTER FUNCTION [dbo].[SplitStrings](@String varchar(8000), @Delimiter char(1))       

      returns @temptable TABLE (items varchar(8000))       

as       

begin       

    declare @idx int       

    declare @slice varchar(8000)       

 

    select @idx = 1       

        if len(@String)<1 or @String is null  return       

 

    while @idx!= 0       

    begin       

        set @idx = charindex(@Delimiter,@String)       

        if @idx!=0       

            set @slice = left(@String,@idx – 1)       

        else       

            set @slice = @String       

 

        if(len(@slice)>0)  

            insert into @temptable(Items) values(@slice)       

 

        set @String = right(@String,len(@String) – @idx)       

        if len(@String) = 0 break       

    end   

return       

end 

 

 

 

usage : where id in (select id from [SplitStrings](‘a,b’,',’))
===================================

Notes July 1st to July 25th 2009

1.  http://realfantasy.wordpress.com/2009/01/05/sql-server-user-defined-function-udf-vs-stored-procedure-sp/

UDFs vs. Stored Procedures
UDFs and stored procedures are both SQL Server objects that store one or more T-SQL statements in a single named, executable routine. Although you can often implement the same or similar functionality using either a UDF or a stored procedure, the code will look significantly different depending on which technique you choose. Here are the main differences between UDFs and stored procedures:

A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn’t have to.
You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can’t use a stored procedure in a SELECT statement.
A UDF can’t use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.
A UDF can’t change server environment variables; a stored procedure can.
A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you’ve used proper error handling code.

========

asp.net pages are kept in pageviewer webpart
———–
uploading documents to sp doclibrary :

SPList
Spquery
splistitem
spattachmentcollection

spfoldercolleciton
spfolder
spfilecollection
Hashtable
spfile
http://kartooz.wordpress.com/2009/05/27/sharepoint-document-library-and-list-file-upload/
———————–
http://blogs.msdn.com/cjohnson/archive/2006/09/05/application-development-on-moss-2007-amp-wss-v3.aspx
Option 4: ASPX pages added to SharePoint Site — ADDED 15-March-2007 UPDATED 16-November 2007

(Thanks to Michal Gwozdek for emailing me an updated set of steps that work for him)

This option actually was suggested in the comments by a reader.  I thought it was so good i tried it out … and it works great!  So here it is.

This option allows you to add your ASP.Net application pages into your SharePoint Site.  It also provides for compiling all using the code behind your pages into a DLL.

In a nutshell this option allows you to build your ASP.Net application outside of SharePoint, build it, test it & then add it to SharePoint.  Its great!

Here is how to do it:

1. Install the Visual Studio 2005 Web Application Projects extension.  This gives you the ‘old style’ web projects in Visual Studio … so you can compile down to a single DLL etc…

2. START – File – New Project – ASP.NET Web Application – Name it “ItDoesWork”

3. Add reference to Microsoft.Sharepoint

Leave only Microsoft.SharePoint, System, and System.Web

4. In the Solution Explorer create folder “~masterurl” and add masterpage “default.master” inside

5. Replace code behind for the masterpage with:

using System;
using Microsoft.SharePoint;

namespace ItDoesWork._masterurl
{

public partial class _default : System.Web.UI.MasterPage
{

protected void Page_Load(object sender, EventArgs e)
{
}

}

}

6. In the designer, rename ContentPlaceHolder’s ID to “PlaceHolderMain”

7. Delete Default.aspx, and add new page – SamplePage.aspx

8. Replace source content with the following:

<%@ Page Language=”C#” MasterPageFile=”~masterurl/default.master” CodeBehind=”SamplePage.aspx.cs” Inherits=”ItDoesWork.SamplePage” Title=”Untitled Page” meta:webpartpageexpansion=”full” meta:progid=”SharePoint.WebPartPage.Document” %>

<asp:Content ID=”Content5″ ContentPlaceHolderID=”PlaceHolderMain” runat=”server”>

Testing Page…

<asp:Label ID=”Label1″ runat=”server” Text=”Label”></asp:Label>

</asp:Content>

9. Replace code behind for the page with:

using System;
using Microsoft.SharePoint;
namespace ItDoesWork
{

public partial class SamplePage : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{

Label1.Text = SPContext.Current.Site.Url;

}

}

}

10. Project properties – Build – Output path:

Point it to \BIN folder of our SharePoint Web application. E.g.

C:\Inetpub\wwwroot\wss\VirtualDirectories\moss.litwareinc.com80\bin

You can also manually copy your projects DLL into the \BIN folder each time.

11. Compile your project.

12. Open the web.config file for the SharePoint Web Applicaiton E.g.

C:\Inetpub\wwwroot\wss\VirtualDirectories\moss.litwareinc.com80\web.config

13. Add the following line to the SafeControls section (change to suit your assembly and namespace etc…)

<SafeControl Assembly=”ItDoesWork” Namespace=”ItDoesWork” TypeName=”*” />

14. Change the <trust level=”WSS_Minimal” originUrl=”" /> line to <trust level=”WSS_Medium” originUrl=”" />

15. Open your site in SharePoint Designer and drag and drop your SamplePage.aspx page into a folder in your site.

16. Browse to your page E.g.

http://moss.litwareinc.com/TestApp/TestPages.aspx

17. Jackpot! (Hopefully) You should now have your aspx page running in SharePoint.

————————–
How do I use a variable in an ORDER BY clause?

http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html

CREATE TABLE blat
(
    blatID INT,
    hits INT,
    firstname VARCHAR(3),
    email VARCHAR(9)
)
GO
 
SET NOCOUNT ON
INSERT blat VALUES(1, 12, ‘bob’, ‘bob@x.com’)
INSERT blat VALUES(2, 8,  ‘sue’, ‘sue@x.com’)
INSERT blat VALUES(3, 17, ‘pat’, ‘pat@x.com’)
INSERT blat VALUES(4, 4,  ‘pam’, ‘pam@x.com’)
INSERT blat VALUES(5, 1,  ‘jen’, ‘jen@x.com’)
INSERT blat VALUES(6, 3,  ‘rod’, ‘rod@x.com’)
INSERT blat VALUES(7, 5,  ‘nat’, ‘nat@x.com’)
INSERT blat VALUES(8, 19, ‘rob’, ‘rob@x.com’)
INSERT blat VALUES(9, 24, ‘jan’, ‘jan@x.com’)
INSERT blat VALUES(10, 0, ‘meg’, ‘meg@x.com’)
GO
 
The first thing people tend to try in T-SQL is the following:
 
DECLARE @col VARCHAR(9)
SET @col = ‘firstname’
SELECT * FROM blat ORDER BY @col
GO
 
However, this returns:
 
Server: Msg 1008, Level 15, State 1, Line 4
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
 
Clearly, SQL Server doesn’t like variables in the ORDER BY clause. Well, we can just use dynamic SQL, right?
 
DECLARE @col VARCHAR(9)
SET @col = ‘firstname’
EXEC(‘SELECT * FROM blat ORDER BY ‘+@col)
GO
====
How to avoid cursors
http://www.thoughts.com/srinivasanr/blog/how-to-avoid-cursors-in-sql-server-103740/

How to avoid cursors in SQL Server
This item has been flagged as inapproriate content. Are you sure you want to view it?
Yes, I want to view it!
An example of a SQL Cursor that we want to avoid:

DECLARE @CustomerID int
DECLARE @FirstName varchar(30), @LastName varchar(30)
– declare cursor called ActiveCustomers
DECLARE ActiveCustomers Cursor FOR
SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE Active = 1
– Open the cursor
OPEN ActiveCustomers
– Fetch the first row of the cursor and assign its values into variables
FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName
– perform action whilst a row was found
WHILE @@FETCH_STATUS = 0
BEGIN
Exec MyStoredProc @CustomerID, @Forename, @Surname
– get next row of cursor
FETCH NEXT FROM ActiveCustomers INTO @CustomerID, @FirstName, @LastName
END
– Close the cursor to release locks
CLOSE ActiveCustomers
– Free memory used by cursor
DEALLOCATE ActiveCustomers

Cursor alternative 1: Using the SQL WHILE loop:

SQL provides us with the WHILE looping structure. This can be utilised with a temporary table that enables us to avoid using a cursor:

– Create a temporary table, note the IDENTITY
– column that will be used to loop through
– the rows of this table
CREATE TABLE #ActiveCustomer (
RowID int IDENTITY(1, 1),
CustomerID int,
FirstName varchar(30),
LastName varchar(30)
)
DECLARE @NumberRecords int, @RowCount int
DECLARE @CustomerID int, @FirstName varchar(30), @LastName varchar(30)

– Insert the resultset we want to loop through
– into the temporary table
INSERT INTO #ActiveCustomer (CustomerID, FirstName, LastName)
SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE Active = 1

– Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

– loop through all records in the temporary table
– using the WHILE loop construct
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT @CustomerID = CustomerID, @FirstName = FirstName, @LastName = LastName
FROM #ActiveCustomer
WHERE RowID = @RowCount

EXEC MyStoredProc @CustomerID, @FirstName, @LastName

SET @RowCount = @RowCount + 1
END

– drop the temporary table
DROP TABLE #ActiveCustomer

We can see the above code gives the same functionality as the first code example but without using a cursor. This gives us the benefits that the Customer table is not locked as we are looping through our resultset so other queries on the Customer table that are submitted by other users will execute much faster. We will also have a faster operating SQL script by avoiding cursors which are slow in themselves.

Cursor Alternative 2: Using User Defined Functions:

Cursors are sometimes used to perform a calculation on values that come from each row in its rowset. This scenario can also be achieved by replacing a Cursor with a User Defined Function. An example of a User Defined Function performing a calculation is given below:

– return a discount %age that the customer
– can recieve based on their no. and value
– of purchases
CREATE FUNCTION dbo.GetDiscountLevel(
@CustomerID int
)
RETURNS int
AS
BEGIN
DECLARE @DiscountPercent int
DECLARE @NumberOrders int, @SalesTotal float

SELECT @NumberOrders = COUNT(OrderID),
@SalesTotal = SUM(TotalCost)
FROM Sales
WHERE CustomerID = @CustomerID

IF @SalesTotal > 5000.00 AND @NumberOrders > 5
SET @DiscountPercent = 5
ELSE
BEGIN
IF @SalesTotal > 3000.00 AND @NumberOrders > 3
SET @DiscountPercent = 3
ELSE
SET @DiscountPercent = 0
END

Return @DiscountPercent
END
An example of this function being used to replace a cursor might look something like:

SELECT FirstName, LastName, dbo.GetDiscountLevel(CustomerID) As DiscountPercent
FROM Customer

Conclusion:

In this article we have seen how SQL Cursors can cause performance problems and affect other queries by locking tables. We have demonstrated two popular ways of avoiding the use of cursors. The WHILE loop does avoid the use of a Cursor but it still uses an iterative loop. The User Defined Function option keeps our SELECT query tidy and enables us to perform calculations using column values from our SELECT statement.

When attempting to redesign your code to avoid cursors you should always check the execution time of your scripts. Very occassionally you may find a cursor gives better performance than an alternative method of performing the same task. When doing this remember that the script execution time is not the only thing to check, impact on queries being run by other users at the same time is also a key factor.
 
 

——-
Visual Studi0

Through code how to put debugger

 System.Diagnostics.Debugger.Break();

=======================

 

XML : USage of CDATA

From a UI screen we have different controls to enter data. On click of submit button we are preparing a xml string .
Characters like < , & are treated as special characters by XML parser.
use validaterequest= false in page directive for ignoring “<” and use CDATA in front of the controls to make the parser ignore special characters like &

<?xml version=’1.0′ encoding=’ISO-8859-1′ ?>

<Incident>

      <WorkType>Incident</WorkType>

      <IncidentID>incident123</IncidentID>

      <TicketShortDescription><![CDATA[Ticket Short Description 1]]></TicketShortDescription>

      <TicketDescription><![CDATA[Ticket Description]]></TicketDescription>

      <SourceToolCreatedDate>2/3/2009 10:30 AM</SourceToolCreatedDate>

      <CustToolCreatedDate>2/3/2009 10:30 AM</CustToolCreatedDate>     

      <CustomerCode>Customer Code 1</CustomerCode>

      <ProjectCode>Project1</ProjectCode>

      <Priority>P2</Priority>

      <WorkGroup>FICO</WorkGroup>

      <ResponseSLAEndDateTime></ResponseSLAEndDateTime>

      <ResolutionSLAEndDateTime></ResolutionSLAEndDateTime>

      <ResponseSLABTG></ResponseSLABTG>

      <ResolutionSLABTG>ResolutionSLABTG1</ResolutionSLABTG>

      <SourceToolStatus>Open</SourceToolStatus>

      <CustToolStatus>CustToolStatus1</CustToolStatus>

      <SourceTool>HPSM</SourceTool>

</Incident>
and passing this xml string to a webservice and reading the nodes and inserting the node values into db.But using cdata we can validate the input control values.
—-
C#   string.IsNullOrEmpty
     string.Format

=========
Sqlserver

Build the query and assign it to a string and use sp_executesql and N to execute the query

USE AdventureWorks;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N’SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID’;
SET @ParmDefinition = N’@CustomerID int,
    @SalesOrderOUT nvarchar(25) OUTPUT’;
SET @IntVariable = 22276;
EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@CustomerID = @IntVariable
    ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
– This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
– This SELECT statement uses the value of the OUTPUT parameter in
– the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;