We often need to have a password validator where the user needs to be intimated about the strength of the password which they have entered so that the user can be aware about their password.
(select top 1 name from fruits order by id desc) as terminator
from
list
union all
select
head,
(select value from list where num = prev.num - 1) + ', ' + tail as tail,
num - 1,
terminator
from
concatenations as prev
where
num > 0
and head = terminator
)
,
concatenated as (
select
max(tail + head) as items
from
concatenations
where
num = 1
)
select * from concatenated
SQL - SERVER - 2008
@ValidExtension = COALESCE((SELECT TOP 1
(
SELECT DT.Extension + ', ' as [text()]
FROM
dbo.ProjectDocTypes PDT
INNER JOIN
dbo.DocumentTypes DT ON PDT.[DocTypeId] = DT.[Id]
WHERE ClientId = U.ClientID AND ProjectId = U.DefaultProjectId
ORDER BY ClientId,ProjectId ASC FOR XML PATH('')
) AS ValidExtension),'')
create table marks(
StudentID int not null
,StudentName varchar(10) not null
,Subject varchar(10) not null
,Score int not null)
insert into marks values(1,'sanjeev','Math',87)
insert into marks values(1,'sanjeev','Geography',76)
insert into marks values(1,'sanjeev','History',98)
insert into marks values(1,'sanjeev','Science',85)
insert into marks values(2,'gourav','Crafts',89)
insert into marks values(2,'gourav','Science',88)
insert into marks values(2,'gourav','History',76)
insert into marks values(3,'Munish','English',87)
insert into marks values(3,'Munish','Science',76)
insert into marks values(3,'Munish','Geography',83)
In 2000
--------
select studentname, subject, score
from marks o
where score in (select top 2 score from marks i where i.studentname = o.studentname order by score desc)
order by studentname,score desc
In 2005
--------
select studentname, subject, score
from
(
select studentname, subject, score,
row_number() over (partition by studentid order by score desc) as rownum
from marks
) dt
where rownum<=2
SET @Sql = N'UPDATE WFTransaction SET UserId= @UsrId,
endDate=getdate(),
LMBy=@LMBy,
LMDt=getdate()
FROM WFTransaction AS wft
INNER JOIN
TTask AS tt
ON
wft.task_id =tt.tsk_Id
WHERE
wft.SId=@SId AND
wft.Doc_Id=@DocId AND
wft.Process_Id=@ProcessId AND
UPPER(tt.TSKName) IN('+@TaskName+')'
IF(UPPER(@RCode) <> 'PU')
SET @Sql = @Sql + ' AND wft.[Role]=@RoleId'
SELECT * FROM
(SELECT
ROW_NUMBER() OVER (ORDER BY ALY_ID ASC) AS ROWID,
ISNULL(FCO_ID,0) AS FCO_ID,
ISNULL(FCOCode,'') AS FCOCode,
ISNULL(FCODescription,'') AS FCODescription,
ISNULL(AnalysisCOde,'') AS AnalysisCode,
ISNULL(ALYDesc,'') AS ALYDesc,
ISNULL(ALPType,'') AS ALPType,
ISNULL(ALPMinType,'') AS ALPMinType,
ISNULL(CAST(ALPMinVal AS VARCHAR(5)),'') AS ALPMinVal,
ISNULL(CAST(ALPMaxVal AS VARCHAR(5)),'') AS ALMaxVal,
ISNULL(ALPConform,'') AS ALPConform,
ISNULL(ALPDetect,'') AS ALPDetect,
ISNULL(CAST(RStatus AS VARCHAR(5)),'') AS RStatus,
ISNULL(LMBy,'') AS LMBy
FROM
TempFull) AS T
WHERE
ROWID>=720001
Creating a table and moving from one table to another table
------------------------------------------------------------
SELECT
ALY_ID,
FCO_ID,
AnalysisCode,
ALPType,
ALPMinType,
ALPMinVal,
ALPMaxType,
ALPMaxval,
ALPConform,
ALPDetect,
ALPPermitted,
RStatus
INTO TempFull_Bkp
FROM TempFull
Moving from one table to another when table is created
------------------------------------------------------------
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
Moving from one table to another when table is not created
-----------------------------------------------------------
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
Typeforwarding is a CLR feature that allows us to move a type from its original assembly to another assembly in such a way that there is no need to recompile the applications referring to the original assembly.
The Requirement
Suppose you have created an assembly named MyLibrary which contains a class named MyClass. Let's say, some of your applications refer to the MyLibrary whih contains MyClass. In a later phase, you decide to move MyClass from MyLibrary to a newly created assembly called MyAdvancedLibrary. If you ship a new version of MyLibrary (which now doesn't have MyClass), along with MyAdvancedLibrary, then your existing applications looking for MyClass in MyLibrary and end up with errors.
The Solution
Now, in order to run your applications without recompiling them, you can use the TypeForwarding feature of the Common Language Runtime.
In the above scenario, you need to apply TypeForwardedToAttribute to the new version of your MyLibrary, so that requests for MyClass are now forwarded to the newly created library MyAdvancedLibrary that now contains MyClass.
Steps
Move the MyClass code from MyLibrary to MyAdvancedLibrary.
Put TypeForwardedToAtrribute in MyLibrary for the MyClasstype.
// C# Example
[assembly:TypeForwardedToAttribute(typeof(MyClass))]
Compile the newly created MyAdvancedLibrary.
Add a reference of MyAdvancedLibrary into MyLibrary.
Recompile MyLibrary (because MyClass used to be located in that).
Now, you can ship the new version of MyLibrary along with MyAdvancedLibrary and run your applications without a recompile!
The Limitation
The .NET Framework version 2.0 does not allow typeforwarding from assemblies written in Visual Basic. However, a Visual Basic application can consume forwarded types if it uses the assemblies coded in C# or C++.
Usually we know that the dbml acts as an interface between the database and user interface for getting connected with the database.
We create the dbml file in our development system and itz quite obvious that the dbml file will directly point to our development database.
What if we move our application into to the production server there the database server will be pointing to the different location. So we need to manually write a code to point to the production database.
Here is the steps which you need to follow.
1. Open the LINQ project.
2. Open the Properties in that go to the file Settings.settings -> Settings.designer.cs file.
public string FreeFlockServiceConnectionString {
get {
//this is the place which concerns us, this is value which is auto generated
//this is auto-generated by the dbml file
// return ((string)(this["FreeFlockServiceConnectionString"]));
//create a code which point to the database from the web.config
return ((string)(System.Configuration.ConfigurationManager.ConnectionStrings["FreeFlockServiceConnectionString"].ConnectionString));
}
}
3. Create a connection string tag in the web.config which is pointing to the production server database.
Siva Shunmugam is a software Professional works for Effindi Technologies. He loves technology because it makes the life easy and likes socializing. By beeing a professional he comes to know about how the technology is beeing handled in the real time world. Among the many technological programes he uses and embraces SQL-SERVER, ASP.NET, JQUERY simply because he uses it in his profession.
DISCLAIMER
This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet/ BOL. My employer do not endorse any tools, applications, books, or concepts mentioned on the blog. I have documented my personal experience on this blog.
Posts
