I have noticed that If we are using a transaction within any Stored Procedure like


CREATE PROCEDURE ....
AS
BEGIN
BEGIN TRANSACTION
...
IF @@Error <> 0 GOTO Rollback_Transaction
...
IF @@Error <> 0 GOTO Rollback_Transaction
...
IF @@Error <> 0 GOTO Rollback_Transaction
COMMIT TRANSACTION
PRINT 'Success'
Rollback_Transaction:
ROLLBACK TRANSACTION
PRINT 'Fail'
END

Error encountered when I execute this code block. This is because while executing the code flow when “COMMIT TRANSACTION” is reached, execution goes past “Rollback_Transaction:” label and execute the “ROLLBACK TRANSACTION” and as “COMMIT TRANSACTION” is executed there is no OPEN transaction to rollback.

Instead you can use like

COMMIT TRANSACTION
PRINT ‘Success’
GOTO After_Success:
Rollback_Transaction:
ROLLBACK TRANSACTION
After_Success:
PRINT ‘Fail’

Here you can add one more label like “After_Success” that will execute the flow “After_Success” label when Commit Transaction is reached.

Today after having so many days effort, I finally able to find out how we can manage multiple stoplists for a database?

“Why MS-SQL Server 2008 is providing facility to create multiple custom stoplists?”, I was thinking over this and wondering how we can manage any specific stoplist out of multiple stoplist created? I have tried to find out this over internet but finally found in MSDN only. May be because I was searching for my content with inappropreate keywords. What so ever, Finally I found what I wanted.

I wanted to create multiple stop-lists and then wanted to use each one of them in my different requirements for the same database.

For example, There is a Job portal where applicants are searching for their appropreate Jobs, whereas Employers/Companies are searching for the right applicants with some keywords. Now consider that there are two different stop-lists that I would like to manage, one Stop-List will be specific to Job Search only and will be used by applicants for searching appropreate jobs and another stop-list will be specific to applicant’s CV/Resume Search and will be used by Companies to find out right candidate.

How can I manage this?

Well its really simple. You need to follow below steps and some scripts.

1. Create Fulltext Index for a database table that you wanted to include in a search query (e.g. job details or CV details tables)
How to?: http://msdn.microsoft.com/en-us/library/ms187317.aspx

2. Create StopLists of your choice (Custom Stoplist)
How to?: http://msdn.microsoft.com/en-us/library/cc280405.aspx

3. Associate any Stoplist to any fulltext indexed table of your choice
How to?: http://msdn.microsoft.com/en-us/library/ms188359.aspx

(E.g. for 3rd Step you can use a simple SQL script as below one to associate any stoplist to any fulltext indexed table:
ALTER FULLTEXT INDEX ON table_name
SET STOPLIST stoplist_name WITH NO POPULATION ;)

Now if you apply any fulltext search on the tables say for example “Job Details” then fulltext search will ignore the words you have defined into associated stoplist that you have created.

To find out the association between the stoplist and associated fulltext catalog you can execute following SQL query:
select fulltext_catalog_id,stoplist_id, * from sys.fulltext_indexes;

To find out catalog details, you can execute following SQL query:
select * from sys.fulltext_catalogs

Happy Programming.

This content is password protected. To view it please enter your password below:

DECLARE @SQLQuery VARCHAR(4000)
SET @SQLQuery = 'SELECT @Query_Result_Count = Count(Column_1) FROM table_A'
DECLARE @Outer_Variable AS INT
EXEC Sp_executesql
@query = @SQLQuery,
@params = N'@Query_Result_Count INT OUTPUT',
@Query_Result_Count = @Outer_Variable OUTPUT

If you can see, we have used “@Query_Result_Count” variable into @SQLQuery which is not declared anywhere before into the declared variable script. This is because we are passing this variable as a parameter into SP_ExecuteSQL statement the same way we are executing the stored procedure. You can check this with “@params = N’@Query_Result_Count INT OUTPUT’,” line of statement.

You can get the output of any dynamic SQL into your local SQL variable by declaring that variable first like we did “DECLARE @Outer_Variable AS INT” and then get this variable filled with the values return by executing the SP_ExecuteSQL statement this way “@Query_Result_Count = @Outer_Variable OUTPUT”. Here “@Query_Result_Count” is the variable that we are passing and using into dynamic SQL.

You can also pass multiple parameters to Sp_ExecuteSQL this way,
@params = N'@Para1 INT, @Para2 Varchar(200), @Query_Result_Count INT OUTPUT '

Also you Need to provide values for input parameters like @Para1, and @Para2 by declaring an outer variable or any static values this way:


DECLARE @Outer_Para1 INT
SET @Outer_Para1 = 10
DECLARE @Outer_Para2 VARCHAR(200)
SET @Outer_Para2 = 'Test'
EXEC Sp_executesql
@query = 'SELECT @Query_Result_Count = Count(Column_1) FROM table_A WHERE Column_2 = @Para1 AND Column_3 = @Para2 ',
@params = N'@Para1 INT, @Para2 Varchar(200), @Query_Result_Count INT OUTPUT',
@Para1 = @Outer_Para1 , @Para2 = @Outer_Para2, @Query_Result_Count = @Outer_Variable OUTPUT

Here I am providing the code snippet that will allow us to delete the file once the file is downloaded on the client’s machine. Below are the code snippets

C#

private void DownloadFile()
{
Response.ContentType = ContentType;
Response.AppendHeader("Content-Disposition","attachment; filename=myFile.txt");
Response.WriteFile(Server.MapPath("~/uploads/myFile.txt"));
Response.Flush();
System.IO.File.Delete(Server.MapPath("~/uploads/myFile.txt"));
Response.End();
}

VB.Net


Private Sub DownloadFile()
Response.ContentType = ContentType
Response.AppendHeader("Content-Disposition", "attachment; filename=myFile.txt")
Response.WriteFile(Server.MapPath("~/uploads/myFile.txt"))
Response.Flush()
System.IO.File.Delete(Server.MapPath("~/uploads/myFile.txt"))
Response.End()
End Sub

I found some problem using Union and Group By clause together, and I later realized that my technique of using these words together is not proper.

Most of the users are making the same mistakes when they deal with Union and Group By clause.

You can use like below:

SELECT x FROM (
SELECT x FROM table_A
UNION
SELECT x FROM table_B
)
GROUP BY x;

I have searched many sites for to disable anchor and I know that IE supports ‘disable’ property for anchor tag, however Firefox does not support this. So whenever you try with ‘disable’ property of an anchor tag “<a></a>” you will get proper output in IE browser but not in Firefox browsers.

I have found a very good article which disables the anchor tag using few tricks because without tricks it is not possible to mange disable anchor in Firefox. You can find this article here. However, you can copy the same javascript script of above article as below.
function disableAnchor(obj, disable){
if(disable){
var href = obj.getAttribute("href");
if(href && href != "" && href != null){
obj.setAttribute('href_bak', href);
}
obj.removeAttribute('href');
obj.style.color="gray";
}
else{
obj.setAttribute('href', obj.attributes['href_bak'].nodeValue);
obj.style.color="blue";
}
}

Also note that you can perform the same thing on the code behind (for asp.net) if you are using a server control for anchor tag and you want to disable the anchor, you just need to remove the ‘href’ property using  MyAnchor.Attributes.Remove("href") and then just apply gray color to anchor and its done. It will work in both IE and Firefox (javascript and server code both).

Happy Programming!!