Tips And Tricks For SQL Server / Affiliates / google ads / C#: 2013

Sunday, 15 September 2013

Add full-text feature in SQL Server and Setup it for any column

If your like queries are taking so much time to execute you can apply full-text search in your queries :-

Firstly How To Add This Feature In Sql Server?

You can add full text to an existing instance by changing the SQL Server program in Programs and Features:

Open the Programs and Features control panel.
Select Microsoft SQL Server 2012 and click Change.
When prompted to Add/Repair/Remove, select Add.
Advance through the wizard until the Feature Selection screen. Then select Full-Text Search. enter image description here
On the Installation Type screen, select the appropriate SQL Server instance.
Advance through the rest of the wizard.

Then How To Setup It For Any Column Of Table In Sql Server?

To implement SQL Full Text Search programming, we have five step processes as below:
a. Data Setup
Let us take a classic example of Employee table for our illustration. As the first step, Employee table is created with the name 'EmpName' of below schema:

Our objective is to search the employee records with the free flow search of any field i.e. the user can extract the record which matches any column in the database table. As an example, the user can enter a name ('Raj') to search from EmpName table. The result may be from FirstName, LastName, ManagerFirstName, ManagerLastName. If the user enters 'Paul Raj', the full text search fetches either Paul or Raj from any of the name columns in EmpName table. Let us load the sample data of 5 below records into EmpName table as:

b. Full Text Column Setup
To initiate the full text search process, let us identify full text column, which is the consolidated data of each textcolumns that you want to include in the full-text. In our example, let us create a new column FullTextValue in EmpName table as below:

To fill the data in newly added FullTextValue column, let us execute the below query
Collapse | Copy Code

UPDATE EmpTable
SET FullTextValue = [EmpID] +
+ ' ' + [FirstName]
+ ' ' + [LastName]
+ ' ' + [ManagerFirstName]
+ ' ' + [ManagerLastName]
+ ' ' + [DivisionName]
+ ' ' + [Location]
+ ' ' + [Country]

Now, the data elements of EmpName table looks like:

c. Full Text Catalog Setup
A full-text catalog provides a mechanism for organizing full-text indexes. Each catalog can contain zero or more indexes, but each index can be associated with only one catalog A full-text catalog is a logical concept that refers to a group of full-text indexes. The catalog is not associated with a file group.
As the thumb rule, if a given column contains documents stored as binary data, we must specify a table column that identifies the type of each document in the column being indexed.
New full text catalog is created in SQL Server by expanding 'Storage' section of the selected DB instance, right click menu of 'Full Text Catalog' option as below:

The catalog system uses SQL Server full-text catalogs to store and search catalog content. In our example, a new fulltext catalog namely 'FT_Employee' has been created in the full text catalog wizard as below:

Our new catalog is created successfully by running the above wizard. We can proceed with full text index definition.

d. Full Text Index Setup
To match the newly created catalog with the proper DB table, let us run the process of defining the full text index on the table. In our example, let us go EmpTable where we want to achieve the full text search algorithm. On right click of the table in Database explorer, we get 'Full-Text index' menu, sub menu with 'Define Full-Text index' as below:

On clicking Define Full-Text index option, it launches full text indexing wizard as:

As the first step of the process, the unique index of the given table 'EmpTable' is selected from the populated drop down box. In our example, it is EmpID table with the primary key definition of PK_EmpTable. So, unique index of the table is selected as:

Next, we need to select the table columns in terms of full text queries. Developer can select multiple table columns and so the user interface is with check boxes. In our example, we have the consolidated column 'FullTextValue' as the eligible table columns for full text query and so the column is selected as:

Another important step is to select an existing full text catalog. If not exists, SQL provides the facility to create a new catalog as marked in a check box. In our example, we created our own full text catalog namely 'FT_Employee' in the previous section. So, let us select the created full text catalog FT_Employee as:

A stopword can be a word with meaning in a specific language, or it can be a token that does not have linguistic meaning. For example, in the English language, words such as "a," "and," "is," and "the" are left out of the full-textindex since they are known to be useless to a search. In our example, we disable the full text stop list by selecting off option from the above highlighted drop down box.

Now, we successfully created full text index on EmpTable with 0 error and 0 warning. If we encountered with any issue, it might be reported in the Report expand button with the details.

e. Full Text Query
It is all set now. As the last step, we can fetch the result of the full text query as:
Collapse | Copy Code

SELECT tbl.*
FROM EmpTable tbl
JOIN containstable
(EmpTable, FullTextValue, '"3"') as key_tbl
ON tbl.EmpID = key_tbl.[Key]
ORDER BY key_tbl.[Rank] asc

In this example, we are trying to fetch the records with the contained data '3' in any of the column of EmpTable. It returns the third row of EmpTable because it contains 3 in all the columns EmpID, FirstName, LastName, ManagerFirstName, ManagerLastName, DivisionName, Location and Country. SQL returns this record even if any one of the above column contains 3.
Thus, we can achieve the full text search mechanism in SQL Server.

Evaluation period has expired... Error Will Require Uninstalling SQL Server 2008 R2 Due To Evaluation Edition

"Evaluation period has expired..." Error Will Require Uninstalling SQL Server 2008 R2 Due To Evaluation Edition

My SQL server was working fine since yesterday and suddenly i got this error , which is "Evaluation period has expired...".I just tried to find some solutions online and i found some blogs on that but i keep on getting different errors while following those steps,Some of this steps that i have written are those that i have followed from someones blog,so i have taken it as it is from that,but whatever situation i have faced that i have added to them :-

Today I spun up a rather aging VM so that I could take some screen shots from the 2008 R2 version of Management Studio.
When I clicked on my shortcut, I received this error:

For searchability, here is the text (with an actual clickable link, imagine that):

---------------------------
Microsoft SQL Server Management Studio
---------------------------
Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy
---------------------------
OK
---------------------------

Of course what this meant was that, for this particular VM, I had installed an Enterprise Evaluation instance before the RTM bits for Developer Edition were available. And clearly this happened more than 180 days ago, and I forgot about it.
If you go the link (which ends up at http://www.microsoft.com/sqlserver/en/us/default.aspx), there isn't really any information about converting evaluation software to licensed software (even if I already have licensed bits in hand). In fact there is not really any information about Evaluation Edition at all, except for an offer to install a second trial instance on my machine. I'm trying to remove one, but thanks anyway.
So the first thing I do is to install a real, licensed version of SQL Server 2008 R2 Developer Edition. I figure, if anything, it will at least give me a functional Management Studio again, even if I have an outdated Evaluation Edition engine lying around. Nope, I run through install, and when I try to launch Management Studio, my evaluation period has still expired. I plead to #sqlhelp on twitter, and I'm told I need to remove all of the 2008 R2 bits. This was the first time I'd had any reason to uninstall 2008 R2; at my previous job, we had only just migrated to 2008 in production late last year. I guess I should test cleanup more often.
Now, removing SQL Server 2008 R2 turns out to be a much bigger challenge than you might expect. Sure, you can go through the Control Panel and uninstall all of the components individually, but this is a hassle, and it still might not work very smoothly. To clear a path, I've been uninstalling applications that might be related (e.g. Visual Studio), and even applications with no hope in hell of being related: Office 2010, Flash, Windows Live "Essentials"... I even uninstalled an instance of SQL Server 2008 (not an eval), and when I tried to remove the setup support files, I received this warning:

Why on earth are the R2 Common Files dependent on SQL Server 2008 Setup Support Files? And further to that, I'd like to know why, when I clicked No on this dialog, it seemed to remove the SQL Server 2008 Setup Support Files anyway. It turns out there is some flag that removes the entry from the Programs and Features list, even if you don't go through with the uninstall. All was returned to normal when I closed the window and opened it again. Anyway, let's see what happens when I try to remove R2 via the Control Panel. I right-click the item in the list (and as you can see, "the list" is pretty short - and never mind the blurred out entries, I'll talk more about those soon, and they aren't related to R2):

When I choose Uninstall/Change, I get this box, and I click Remove of course:

When I click Remove, I get the Setup Support Rules dialog (just like I do for a new installation), and then when I click OK, I get this lovely dialog (I trimmed it so I wouldn't have to shrink it, but you get the gist):

Why does this not seem like it will end well?
Because according to the dialog, nothing is going to be removed (usually you see actual features with checkboxes). I should not even be able to see a Next > button. But for fun, I'll click it anyway, since my only other option at this point is to Cancel. I click Next > a couple of times, then a big shiny Remove button, and then after several seconds I get another even more lovely dialog, indicating that the uninstall has crashed:

And again for searchability (typo and all):
TITLE: SQL Server Setup failure.

------------------------------

SQL Server Setup has encountered the following error:

No feature were uninstalled during the setup execution. The requested features may not be installed. Please review the summary.txt logs for further details.

Error code 0x84B30002.

------------------------------
OK
------------------------------

At this point I've spent more time on this than I would have if I had just built a brand new VM and. But I was determined to see it through. So I finally gave in and decided to go the msiexec.exe /x route. I located all of the R2-related GUIDs in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\ Here is one example:

So I went through the entire list in uninstall folder, and saved the following commands to a batch file: (make one file with .bat extension that we can run by double click on it and we can add content in that by right click on it and edit)

rem DisplayName
msiexec /x "{guid}"

The above formula means that , i have go through all folders under uninstall folder and take DisplayName and UninstallString from folders whose UninstallString start from “MsiExec.exe /I”. And I also added one that wasn't in the GUID list; there was a key under Uninstall called "Microsoft SQL Server 2008 R2" with the following properties:

Here is what my final .bat file looked like. You'll note that I put the Setup Support Files last. Also please note that your set of GUIDs may be different than mine... I'm just listing what I did as an example: rem SQL Server 2008 R2 Common Files

msiexec /x "{234F6B0D-10AE-4BB7-B2F3-E48D4861952D}"

rem SQL Server 2008 R2 Common Files
msiexec /x "{36F70DEE-1EBF-4707-AFA2-E035EEAEBAA1}"

rem SQL Server 2008 R2 Database Engine Shared
msiexec /x "{A2122A9C-A699-4365-ADF8-68FEAC125D61}"

rem Microsoft SQL Server 2008 Setup Support Files
msiexec /x "{C0C690C8-F335-4BA4-A2AD-675EAD1DFA90}"

rem SQL Server 2008 R2 Database Engine Shared
msiexec /x "{C942A025-A840-4BF2-8987-849C0DD44574}"

rem Microsoft SQL Server 2008 R2 (64-bit)
msiexec /x "{20E42995-BBE9-4697-8394-FCDC4338706B}"

rem Microsoft SQL Server 2008 R2 Setup (English)
msiexec /x "{C3E48238-5FA0-4C82-9509-36D47E371A29}"

In above list they have listed “SQL Server 2008 R2 Common Files” with msiexec /x but i find all with msiexec /I so i have removed them but in this command i have written msiexec /x and “{guide from msiexec /I}”

Running through these, I had several that returned a dialog about an unexpected error, but I ignored those and plowed through. When I was finished, I ran the batch file again, and every line item raised this dialog:

This is a good thing; it smells like I'm almost home. So - finally - I try to run SQL Server 2008 R2 setup again. To avoid some wasted time, I install only the Management Tools on my first run. Though, if I'm being honest, even installing just the client tools takes several minutes. Precious, Monday Night Football minutes.

After doing all above things I was able to uninstall sql server but when i rerun the setup again to install sql server i got an error once again : -

---------------------------
Rule Check Result
---------------------------
Rule "Performance counter registry hive consistency" failed.

The performance counter registry hive is corrupted. To continue, you must repair the performance counter registry hive. For more information, see http://support.microsoft.com/kb/300956.
---------------------------
OK

For that follow the steps given below :-

This may mean that the value in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib under Last Counter and the last number value in Counter under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009 are not the same. The same also for Last Help and Help.

In this example, 4720 is the number in both Last Counter and the last counter value. However, in my case my HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009 looks like this:

How Do You Solve this Problem?

This fix only applies if HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009 has no values and you can not add any.

009 is the English Locale ID. You may have this problem with another locale where you have no values and cannot add any either. The same fix will apply. I am trying to install on a virtual differencing disk that I have run NewSID against.
I found myself in the Microsoft Forums (see reference #5), but no one had figured out how to fix my particular problem. One person came close but wasn't quite there. So I started searching for how to fix my performance counter problem. Isn't it awesome that things that you don't really care about sometimes become issues with things you do?! I found the solution and I wanted to share. I found that I had disabled performance counters and to have the Counter and Help registry values, I only needed to fix that and reload all of my counters.

Steps to Resolve Performance Counter Issues
1. Back up your registry.
2. Enable Performance Counters: Check the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib to see if you have the key Disable Performance Counters and it is set to 1. If so set it to 0. This should now allow you to build your Counter and Help registry items following the rest of the instructions. (NOTE: You will not find this in any link I have referenced. This is the piece every set of instructions was missing.)

3. Then you may or may not need to rebuild your performance counters (see reference #1). Try to see your performance counters (step #4 below). If you can't see any or you just see numbers, you will need to rebuild your performance counters. If you have Windows 2003, you can run the command line and put in these two commands:

cd %systemroot%\system32
lodctr /R

4. You know this has worked when you open your performance counters (Control Panel -> Administrative Tools -> Performance) and try to add and actually see items for your computer:

5. Try your install again.
6. If that doesn't work, you need to perform an in-place upgrade of Windows (see reference #4). Then follow the instructions above to rebuild the performance counters. You will also need to re-install all service packs for Windows after you do this. The good news is that you don't lose your applications at all.
7. Once you have completed this, try to install the application you were originally trying to install. In my case this is the monolithic SQL Server 2008.

But here i face another problem that when i click on 009 folder i got one another error

[Window Title]
Error Opening Key

[Content]
009 cannot be opened.
An error is preventing this key from being opened.
Details: Access is denied.

[OK]

You have to run RegEdit as Administrator
Right Click on the inner most folder for that you want to open
select permissions from the context menu
In the permissions dialog, click the advanced button.
In the advanced security settings dialog switch to the Owner tab
I assume your usercode is a member of the local Administrators group
Click the Replace owner on subcontainers and objects box.
If Administrators is NOT the owner, select it in the change owner to box...press Apply...OK.
You are now back at the Permissions dialog box.
Select Administrators and give them Full control by selecting the Full Control checkbox in the Allow column...press Apply ... Ok

And Here I got one more error list :-

First one is

And still if m trying to change permission i just keep on getting above error on every step and finally when i m trying to change owner by clicking on apply, i get error given below :-

And to resolve this follow steps given below :-

to be able taking the ownership, you should logged in with administrator privileges. the easiest way is to enable the built-in and hidden super administrator account.
goto start menu, find the icon to open cmd -don't open/run it-, right click on it->run as administrator.
then on the cmd console, type : net user administrator /active:y
press enter. if everything ok, there should be prompted: ... successfully.
logoff windows. you should see the Administrator account. it usually has no password.
login using administrator, then you can take the ownership on most files including the registry key

Tuesday, 10 September 2013

Multiple ways to give row number in sql based on different criteria

In sql you can provide row number based on different perspective to get different results.For that different syntax and different results are given below :-

1) Using Dense_rank with order by

DENSE_RANK() over (Order by P2.Store_Id)

Store_ID	Ro_Num
404	1
506	2
546	3
506	2
650	4
404	1
506	2
650	4

2) Using Rank with order by

RANK() over (Order by P2.Store_Id)

Store_ID	Ro_Num
404	1
506	1
546	1
506	2
650	1
404	2
506	3
650	2

3) Using Rank with partition by and order by

RANK() over (partition by SubCategoryID Order by ItemCode)

SubCategoryID	ItemCOde	Row_Num
1	A	1
1	C	3
1	B	2
1	D	4
2	F	2
2	K	3
2	C	1
3	J	2
3	L	3
3	E	1

3) Using Row_Number with order by

Row_Number() over (Order by P2.Store_Id)

Store_ID	Row_Num
404	1
506	3
546	6
506	4
650	7
404	2
506	5
650	8

Monday, 9 September 2013

Steps to setup an account of merchant for tracking in postaffiliate

If you want to generate a tracking code for any merchant in post affiliate then firstly you have to make its account and then you can generate script for tracking.So steps to make account are as below :-

Create Account
First we have to create an account start->Account->Account Manager
On that page Add Account --> Add all the accountinfo from Merchant Details and in Agreement part write description of % or INR that they have decided to give you on product sale,and create account
After creating account -> Edit it from grid and in that in merchant panel edit grid again and in that change the password by password that you want to give your merchant to login to check his/her click/sale
Create Campaign
create campaign for the number of products on which they have given different % and If it is same for all products then create one campaign for all products
Campaign name -> storename for which product
Description -> flat % on that product
Account -> for which account we have created the campaign
Limit cookie lifetime to -> 31 (this means that if anyone visit that product from our website and not purchase it then if one purchase it in 31 days then also we will get the money)
Edit that campaign -> go to commissions tab and disable 'per click' and 'CPM'
Edit per sale -> check 'Save transaction with zero commission'
Select RS. or % as per given and its value and save it
In grid make your campaign the default one
Then delete the default campaign for that account that will be there in list of campaign
Create Banner
In banner manager -> create banner -> Text link
Name - provider on product
Select campaign
Destination URL -> URL OF your site where you are promoting this merchant
Select Wrapper For Banner -> plain
Title and text -> provider on product tracking
To generate script for sales track and click track,Go to Tools -> integration and there you can get both click and Sales track
Here you can get script based on the platform in which your merchant's website has been built
Generate Tracking URL to add in your urls of that merchant
Login in to post Affiliate on /affiliates/panel.php page
In left menu promotion - > Banners & Links
Select our campaign
Select Banner type --> text link
Click On Apply
In dynamic link --> paste one url of one product and get code and get tracking

Steps to setup Google DFP (DoubleClick For Publishers) for your account

If you want to show google ads on your site or want to showcase banners for any publishers on your site , then you can setup DFP (DoubleClick For Publishers) account and steps are as given Below :-

URL For this is https://www.google.com/dfp/signin‎
There are two main things in DFP first is “Ad units” and second one is “Placements”
And then first step is to add new ad unit
When you click on it you will get form to fill
You can give appropriate name to ad unit and description to describe it and according to which size of banner or google ad is required, you can select it from list or you can enter your own required size
Then you can select target window if you want to open it in itself or want to open new window
And then if you have already made placement then you can include it from this placements window and if you haven’t made it yet , you can add it later or even you can add adunit in placement in its entry form
There is one more thing “Adsense inventory settings” and you can override it by clicking on override
And By changing this setting you can change look and design of google ad.and then click on save to complete process,and by doing this you can see the added ad unit in list of ad units as shown in first snap shot
As You will click on placements link you can see the list of placements same as ad units with (+ New Placement) button on the top
As you click on it , you will get entry form for it as below
Where you can give name to placement as per the placement of ad or connected ad unit and in description you can describe its use
From Assigned Inventory list you can include ad units to placement
Now the most important step is here,there is one checkbox of google adwords,if you check it the block of script that you generate will show google ad and if you uncheck it,you can use that placement for banner.and then enter other information accordingly
Now to get script to place on site,click on generate tags link
There you will get list of adunits.from this include all ad units for which you want to generate script.If you select multiple , you will get combine script.So if you want individual script , then generate tags for individual ad unit.include it and click on generate tags
So you will get this script to place on site.Place the script on head part and body part in the place where you want to display ad or banner
Now , This will work when you want google ad script , but In the case when you want to display banner in ad units, then you have to follow some extra steps
For that you have to create Order,so click on orders tab
Here you have click on (+ New Order)
You can enter any name for your campaign of order ,and then in advertiser textbox you will get (add a new company in dropdown),select it to add advertiser name
Then select type of advertiser and enter its name and add it , so you will get it in list of advertisers and then select it
Here you can enter name of line item,which is related to your banner,then select you inventory size according to your adunit size,that you will use for your banner.Then give start time and end time of your banner.then your goal of quantity as impressions or click and enter rate of inr per click or impression and then discount in that rate
Select As fast as possible in Deliver impressions
When you click on any line item you will get list of creatives , if you have made any,if it is blank you can add it by clicking on (+ add creative)
When you will click on add new creative,you will get window like
Now click on “select files from your computer”,so it will open dialogbox,then select image from your computer drive , so it will display form as below
Enter Name for creative and enter Click-through URL ,which is a link that you want to open on click of this banner.and then click on save at end