Anuj's Share IT

Here I am not going to talk about fulltext search as generally in small applications which not that expensive servers we do not really go for full text indexes. Thus we are going to talk about wild card search. Now a days providing search facility to end user has become quite normal. Users generally will have 'n' fields to search. They can search by providing values for all fields or they may provide values for only one field. There are two solutions to this:- The one i like - I have not put any validation in place which we should as if there is no parameter specified the procedure will fail because of bad syntax as there will be a bad where clause. Thus check if no parameter is spec CREATE PROCEDURE SearchProcedureName         -- Add the parameters for the stored procedure here         @Name VARCHAR(100),         @Id VARCHAR(10),         @error int output AS BEGIN set @error =...

I have been playing with merge application of late, but for production changes and not on a sample project. Let me first share our environment with you. We have a central server and few number of clients. Server and client share same schema. We have used merge replication to replicate the changes. There were some load issues and thus few changes have to be done. I will list down the change, which I am going to talk about today. Partition a table vertically. Transfer data from the existing table into the new table. Make sure that the network usage (replication) will be as less as possible. This is a normal scenario for those who are working on distributed environment. I am sharing this post for all those who will work on such environments in future . Challenges For once it all seems so simple. We can run a data migration script which will transfer data from table A to table B on client and server and we are good. But, the problem is that then the merge replication will try...

Let us add the article using the GUI first. First of all create the table on publisher database. Now right click on the publisher and select properties from the menu. Click on articles. Uncheck the - "Show only checked articles in the list" checkbox, in order to see the newly added table. Select the table as shown in the figure below. Press ok The article has now been added to the publisher We now need to recreate the snapshot Right click the publication and select – “View snapshot agent status”. Click start to regenerate snapshot. Now right click on the subscription (I have both on same server you may have on different servers) and select “View synchronization status” Click on start on the agent. The schema changes will propagate to the client if you have "Replicate schema changes" property set to true in the publisher.

Yesterday I took an interview of an EXPERIENCED candidate. I was quite shocked to see the information he has on JOINs. Thus I thought about blogging about the basics of JOINs. The scenario I gave him was - CREATE TABLE #JOIN1 (     ID INT,     [TEXT] VARCHAR(100) ) CREATE TABLE #JOIN2 (     ID INT,     [TEXT] VARCHAR(100) ) INSERT INTO #JOIN1 VALUES(1,'TEXT JOIN 1 - 1') INSERT INTO #JOIN1 VALUES(2,'TEXT JOIN 1 - 2') INSERT INTO #JOIN1 VALUES(3,'TEXT JOIN 1 - 3') INSERT INTO #JOIN1 VALUES(1,'TEXT JOIN 1 - 4') INSERT INTO #JOIN1 VALUES(NULL,'TEXT JOIN 1 - 5') INSERT INTO #JOIN2 VALUES(1,'TEXT JOIN 2 - 1') INSERT INTO #JOIN2 VALUES(2,'TEXT JOIN 2 - 2') INSERT INTO #JOIN2 VALUES(4,'TEXT JOIN 2 - 3') INSERT INTO #JOIN2 VALUES(NULL,'TEXT JOIN 2 - 4') INSERT INTO #JOIN2 VALUES(5,'TEXT JOIN 2 - 5') Note: There is no primary key column. Left Outer Join MSDN defines a left outer join as - Th...

Introduction You can use encryption in SQL Server for connections, data, and stored procedures. This Article explains you how to Encrypt and Decrypt a text using T-SQL functions.you can encrypt a password and can store a password as VarBinary in a column by using EncryptByPassPhrase function . Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE function. Encryption Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted. Although encryption is a valuable tool to help ensure security, it should not be considered for all data or connections. When you are de...

As the error says the particular login is Disabled. Thus, we need to enable the user. Follow these steps to enable the user. Go to start -> programs. Open Sql server management studio Login to the SQL server (using windows authentication). Expand the security tab. Expand the Login folder. Select the user for which you got this error. In this case 'sa'. Right click and select properties. Enable the user. Save settings. Now it should work fine. Hope this will help.

I told you about the problem faced by my friend while trying to install DotNetNuke for Windows Web App Gallery. He got Login Failed For User 'sa' error. For resolution check here . After resolving the error he tried again to install DotNetNuke and this time he got the error -   A connection was  successfully established with the server, but then an error occurred  during the login process. (provider: Shared Memory Provider, error: 2 -  Connection was terminated) The reason for this was that he forgot to restart the SQL server service after he changed the configurations as explained in my last post.  I am posting it separately as I have seen many of us searching for this kind of error. Hope this will help.

One of my friend was trying to install DotNetNuke from Windows Web App Gallery. While installing he was prompted for SQL server administrator user ('sa') credentials. He was clueless about this as he never gave a password for it. He tried using his system password (he was working on his personal system) and got the famous  error - Login failed for user 'sa'. Reason: Actually while installing SQL server he had used windows authentication and now he was trying to use SQL Server authentication. To resolve this -  Go to Start-> Programs  and open SQL server management studio. Connect to the SQL Server you want to change user authentication for (using windows authentication). Right click on the SQL Server and select properties. Now select "Security"and select SQL Server authentication Save settings. Now go to Administrative tools and restart the SQL Server service to let the change take effect. Hope this will help yo...

When I need to change anything in my database or I need to analyze something in it I generally try to play safe and I hope we all try to do so :) !!! I mean, I research on the flow and dependencies and then will jump on any conclusion/solution. Here I just want to share few T-SQL queries which I use for this purpose. These queries also helps in regression. First Query to find presence of particular text in any SQL object. Text can be a table name (in order to find out where all this table is getting used), it can be a procedure or view's name. SQL objects can be procedures, views etc.. SELECT NAME FROM SYSOBJECTS WHERE ID IN ( SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE '%TEXT GOES HERE%' ) Second Query to find out the dependencies between SQL objects. e.g Table A is getting used in View B. Then the below mentioned query will give you Table A's name as output as View B has dependecy on Table A. Note : if View B is further used in View C and we use V...

This article is about checking the status of a SQL job. In our work place we have lot of SQL jobs. These jobs will run whole day and are business critical. They will load the data and generate extracts which will be used by business people. Thus, it becomes quite essential to support the system efficiently so that the job finishes in time and as desired. Also, while designing a new system sometimes we need to check the dependency of one job over another. In such scenario we need to check whether a particular job has finished or not. All this can be achieved in SQL Server by using the procedures:- sp_help_job xp_sqlagent_enum_jobs Note: xp_sqlagent_enum_jobs is an undocumented proc inside of sp_help_job and is used extensively to get SQL agent job information. sp_help_job: This procedure gives some insight into the status, and information, about a job. This stored procedure provides information such as last start time, job status etc. Syntax sp_help_job { [ @job_id= ] jo...