Contact Me – Archive 6

Pinalkumar Dave is a Microsoft SQL Server MVP and a Mentor for Solid Quality India. He has written over 1200 articles on the subject on his blog at He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager, who specializes in SQL Server Programming and has 7 years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCDBA and MCAD (.NET). He was awarded Regional Mentor for PASS Asia.

I am proficient in Corporate Training. I have designed and implemented complex database architecture, and have also implemented strategies for database high availability and scalability. Furthermore, my core expertise lies in query tuning and performance optimization.

If you want to seek my expertise then drop me a line and tell me about your requirements by using the form below or send me email pinal “at” I value development community and will be happy to help you at any stage of project development, from design to deployment.

Copyright violation and Reproduction of blog: is trademark of Pinal Dave. Exact work “SQLAuthority”  or “SQL Authority” in any form or medium without explicit written permission of Pinal Dave. If any article published on this blog violates copyright please contact me, I will remove it right away. Linking to this blog is allowed. It is allowed to reproduce maximum of 160 words or 8 lines, which ever is maximum in event of linking to blog (Link is must).


If you have any questions for faster response, Search It is possible that your question is already answered in one of the hundreds articles.

Community Rules

  • Do not post obscene, indecent, hateful, offensive, defamatory, abusive, harassing or profane material. They will removed.
  • Do not post junk mail, pyramid schemes, chain letters or advertisements.
  • Do not engage in personal attacks. We have zero tolerance for such incidents.
  • Vulgar and insulting nicknames will be removed. Nicknames attempting to impersonate other users will be removed.
  • Do not post anyone’s telephone number, street address, email address. or any other personal information.
  • Do not post copyrighted material.
  • Lively debate and opposing opinions are welcome, but please behave courteously.

pinal “at”
pinaldave “at”

About these ads

532 thoughts on “Contact Me – Archive 6

  1. Hi there,

    I am a fresher and looking for a job.

    I want to ask that i am trying to store a database on a local drive but unable to do it.

    Could u please tell me how to store a database on to a local drive(can we do it through export if yes then how?) and retrieve (may be it’s import option) it back from a local drive.

    Plz help……….. can we get the assistance within 24 hrs. if stuck somewhere

    shalini Gupta
    [phone and email address removed]

    • While creating a database,it uses default location of datafiles and log files.You can change them then and there.If you want to avoid it,in the server settings put the path where you want to keep your database files.From then onwards,new database will take this path automatically.

      I could not understand the later half of your question.You can keep a backup of your database on your local disk and you can restore it whenever you need.

      Please explain your question clearly if I missed out something.


    • Copy the mdf file from instance folder and take where ever u go .. use Sql Server to attach the file to new database .it will take care every thing ……..

      • We are running Microsoft Dynamics4.0 and I perform daily normal backup through sql server management studio the database name is AXDynamics.
        Now I want to restore it on another Virtual machine.

        Please help me to solve this issue.

    • dear ghada
      you can give your data type for barcode as string type

      because the barcode is having alphanumeric data
      you can have to check a barcode from any product

  2. I am working as SQL DBA in NOC based compnay, i am facing some regular problem of CPU and memory high utilization, normally we suggest for the services to be restart but after restart services we found it increase utilization after some days or we suggest for configure max memory limit for the particular instance, i know this is not proper way, please suggest us to better option with step so that we can follow to troubleshoot issue. this is very important to me solve this issue with proper manner.
    Waiting for your precious reply.
    Thanks you.

  3. Hello Gulzar,

    Even CPU and IO utilization increases with workload on an OLTP database but you can improve the utilization indirectly by query optimization. Using profiler or SSMS summary reports get some queries that are mostly using the CPU, memory or IO. Optimizing these queries could help in reducing the high utilization.

    Pinal Dave

    • Hi pinal,

      Thanks for ur response.

      i jus wanna knw how to get the usernames who have the rights to access the particular db. is there any way?….

    • Hi

      This is Subhash,
      ;With CTE as ( Query..) when ‘with clause’ run on SQL server2005 then ‘)’ error arise. its not being support or ..
      Tell me sol?

    • Hi Pinal Dave,

      i am working as a progrmmer ,i got scenior, in my database column i am having incident description field , i want to get value strating with number and ending with alpha.i have search in goolge ,but i could not find any example.

      input in database column :
      column name is:
      Incident description
      SYD – SYD 28E Handset socket broken.

      my out should be

      outPut : 28E

      could you please help me

  4. Hi Pinal Dave thanks for saving me lot of times in job with ur work …

    if u dont mind can u me solution for 1 thing which i have prob

    like for a string

    ex this is a nice blog site for sql

    i want like @temp tables with 1 column

    with rows like

    this is
    is a
    a nice
    nice blog
    blog site
    site for
    for sql

    this is for 2 to words …… it possible

    • Why do you want to do this?

      Try this code too

      Declare @s Varchar(100),@delimiter varchar(5)
      Set @s = 'this is a nice blog site for sql'
      set @delimiter = ' '
      Declare @Xml Xml
      Select @Xml = Cast('' + Replace(@s, @Delimiter,'') + '' As Xml );

      declare @t table (sno int identity(1,1),data varchar(100))
      insert into @t(data)
      SELECT T.split.value('.', 'nvarchar(max)') AS data
      FROM @XML.nodes('/d') T (split)

      select' ' as data from @t as t1
      inner join @t as t2
      on t1.sno+1=t2.sno

  5. @Kumar,

    Check if this helps…

    declare @Sqlcmd1 varchar(100)
    set @Sqlcmd1 = LTRIM(RTRIM(‘this is a nice blog site for sql ‘))
    declare @Temp_Table table (Column1 varchar(100))

    While len(@Sqlcmd1)>0
    if ( SELECT CHARINDEX(‘ ‘, @Sqlcmd1, CHARINDEX(‘ ‘, @Sqlcmd1)+1) ) > 0 — checking for second space.
    insert into @Temp_Table (Column1) values (substring (@Sqlcmd1,1 , CHARINDEX(‘ ‘, @Sqlcmd1, CHARINDEX(‘ ‘, @Sqlcmd1)+1)))

    select @Sqlcmd1 = (substring ( @Sqlcmd1
    , (CHARINDEX(‘ ‘, @Sqlcmd1)+1)
    , (len(@Sqlcmd1)- CHARINDEX(‘ ‘, @Sqlcmd1)+1)

    insert into @Temp_Table (Column1) values (@Sqlcmd1)
    Set @Sqlcmd1 =”

    select * from @Temp_Table

    ~ IM.

  6. Hello Pinal,

    I am a regular follower of ‘SQLAuthority’

    Recently I had to face a problem related to maintaining ‘Transactions’ while doing sql manipulation.


    There are two different tables (say db1..table1 & db2..table2) in two different databases (db1 & db2) of same server (say server1).

    I need to insert records in these two tables in a single transaction.

    There are around 40+ input parameters,hence I decided to opt for dynamic sql (sending comma separated param as string). I did it in following way,

    sample :

    CREATE PROCEDURE [dbo].[StaffMasterInsertForApprovers]

    @Table1Param AS VARCHAR(1000),
    @Table2Param AS VARCHAR(1000)



    — Turn off count return.


    SET @Table1SQL = ‘
    INSERT INTO db1..Table1

    SET @Table2SQL = ‘
    INSERT INTO db2..Table2

    –INSERT IN db1..Table1
    IF @Table1Param ”
    SET @Table1Insert = @Table1SQL + @Table1Param + ‘)’
    EXECUTE sp_executesql @Table1Insert

    –INSERT IN db2..Table2
    IF @Table2Param ”
    SET @Table2Insert = @Table2SQL + @Table2Param + ‘)’
    EXECUTE sp_executesql @Table2Insert






    But above transaction didnt rolled back when I tried to raise error at some level in above sp.

    Please help…

    – > Kshitij

  7. Hi Dave,

    Just curious if there is a possible way to trace if a column result of an sql DML statement is taken from an ‘Included column’ of an index or if it is taken from the clustered index ? I have quite a complicated sql statement and I’m wondering if the optimizer is actually using the included column of my indexes.

    Thanks for this website, its been helping me a lot !


  8. Hello Merellano,

    Using execution plan we can check which index is used and if your result having data of included column is returned from an index (that have included column) then its apparent that included column is used.

    Pinal Dave

    • Hi Pinal Dave,
      i have one doubt,
      i have database which is stored in different locations.if i delete one file in one location what is the database status? how to change the databse status?


      • Hello Nagaraju,

        A database file can not be dropped unless this is offline. So to drop a file first alter the database to offline that file. The database will be available with data of all other online files.

        Pinal Dave

  9. Respected sir,
    I personally attended the community day event today at Ahmedabad and i am very much pleased with it.and really admire your work and sincere efforts..

  10. Greetings Dave,

    I have a question regarding encrypted data. I have a base database that contains a table with encrypted data in it. I use this database to create databases in our other environments. That table of encrypted data needs to be carried over to the new databases. I am using SMO in C# to copy the databases, create the symmetric key, and execute the Transfer. This appears to work, the database is there, the key is there with the same Password as the source database, but the encrypted data is null. Is there a way to move the encrypted data over using C# and SMO? Or do I need to read the data from the source, decrypt it, and then insert it into the new database encrypted with the key on the new database? It would be nice if I could make an exact copy the database including keys and data. Do you have a suggestion on this?


  11. Hi,

    We are planning to convert Informix Database to SQL Server 2005.

    What are the steps to follow to convert from Informix to SQL server 2005?

  12. Hi Sir,

    I have one problem, actually our software running in Clients (more than 10) side. when I changed Database change how to update Table & others changes (Each client different Database version like 1 Client update January 1, 2nd Client January 10) now I DB version February 1st. so how I know DB Changes need to update?


  13. Hello,

    I try to use teh SQLBAP and I don’t see my sql server instance, the message is unable to connect.
    Please help me for this.

  14. Hello Amit,

    what issue you are facing while calculating large recordset? In SQL server there is no constraint of row number in any clause or functionality.

    Pinal Dave

  15. Hi Folks,

    I have seen this code in an SQL Server 2008 tutorial:

    DECLARE @StrSalary Varchar(10),
    @StrHours Varchar(6),
    @WeeklySalary Decimal(6,2)
    SET @StrSalary = N’22.18′;
    SET @StrHours = N’38.50′;

    SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) *
    CAST(@StrHours As Decimal(6,2));
    SELECT @WeeklySalary;

    In code lines 4 and 5 above, is the capital letter ‘N.’

    What does the N mean or signify here?

    Thanks in advance for your help.

    -Tom Jarosinski-

    • This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT.

      Unicode is designed so that extended character sets can still “fit” into database columns. What this means is that Unicode character data types are limited to half the space, because each byte actually takes two bytes (Unicode is sometimes referred to as “double-wide”).

      While using Unicode is a design choice you can make in building your own applications, some facilities in SQL server require it. One example is sp_executeSQL. If you try the following:

      EXEC sp_ExecuteSQL ‘SELECT 1′

      You will get this error:

      Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
      Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.

      You can get around this in two ways:

      — (a) using the N prefix

      EXEC sp_ExecuteSQL N’SELECT 1′

      — (b) using a variable

      DECLARE @sql NVARCHAR(100)
      SET @sql = N’SELECT 1′
      EXEC sp_ExecuteSQL @sql

      It may be helpful…..

  16. Thank u for your reply,Actually i have a method to manipulate various datas of binary trees but if my datas crosses 1 lakh then it takes lots of time,can say 6-7 hours,and its very difficult to manipulate my tree.
    Can you suggest some ideas to reduce my calculating time.

    Thank you

  17. Pinal Sir,
    I visit this site very often. Thank you for this very great effort. I have 2 suggestions.
    1, Can you please include a question answer section. As you can see, many questions are spread over a lot of the articles, if we have a common page to post our question for a topic that is not currently being discussed. ex. In an article about generating scripts of stored procedures, a question about which field to use for storing a bar code information, I feel is out of place. When I have a question outside that of the topic discussed, i use the contact me to raise my question. I admit, members of this blog have always been kind to help me solve my issues effectively, a big thank you to all for that. I am only suggesting a common area to post a sql related question.
    2, My second suggestion is that this is a blog used by many sql professionals, we should have a job posting board, where a member who knows that there is a sql related job in his or her company can post that vacancy, and a member who is looking out for a job change can pick on that lead.

    • Hello Chethan,

      The complied plan of both function and stored procedure are stored in memory. so theoratically there should be no major performance difference.

      Pinal Dave

  18. Hi everybody,
    I have excel 2003 files with some visual basic code on XP SP3. The files retrieve information from SQl 2005. SQL2005 is installed on win2003 server. The connection is done with TCP/IP. The files run fine. But last month I got a new dell server with win2008. I installed sql2005 on the new server and works fine. I have some web applications running and connecting to sql 2005. Everything is OK. But the excel files with the visual basic code can’t connect to the win2008 server. the connect fails. I checked the IP, IP on the new server is enable.
    Any suggestion.

  19. hello sir,
    i want to insert “amit-005″ in table student only in single query,But “amit” should store into Name field and “005” should store in RollNo field . How i can do this ??? Hurry !!!reply

    • Hi Amit,

      You can use CHARINDEX and SUBSTRING to identify name and rollno.

      You can do it as:

      DECLARE @x VARCHAR(50)
      SELECT @x=’amit-005′

      SELECT SUBSTRING(@x,0,CHARINDEX(‘-‘,@x)) AS Name,
      SUBSTRING(@x,CHARINDEX(‘-‘,@x)+1,LEN(@x)) AS RollNo



  20. Hi ,

    I am using SQL Server 2005.
    Can any one explain “sp_dropwebtask” ?

    I used this system defined procedure but it is not working .

    exec sp_dropwebtask @procname=N’test_tmp’, @outputfile=N’\\devdb\RAMCOERPDOCUMENTS\chd1.xls’

    showing error message :-
    Msg 16802, Level 11, State 1, Procedure sp_dropwebtask, Line 25
    sp_dropwebtask cannot find the specified task.

  21. hi pinal

    i have one problem ,please solve it.

    problem statement :

    i have one text file that i want to import it into sql sever 2005.
    in text file data is like this

    [00:00:00 16/11/2011] wf t0=43483 tf=147677 id=a40215
    [00:00:00 16/11/2011] nu t0=42659 tf=147599 id=a40215n
    [00:00:00 16/11/2011] wf t0=54788 tf=111787 id=a41059
    [00:00:00 16/11/2011] nu t0=52919 tf=111719 id=a41059n
    [00:00:00 16/11/2011] ic dd=1730 ds=1 ld=MALIG NEO SKIN LIP
    [00:00:00 20/09/2012] ic dd=2720 ds=4 ld=PURE HYPERCHOLESTEROLEM
    [00:00:00 20/09/2012] ic dd=2859 ds=7 ld=ANEMIA NOS
    [00:00:00 20/09/2012] ic dd=4019 ds=3 ld=HYPERTENSION NOS
    [00:00:00 20/09/2012] ic dd=4111 ds=2 ld=INTERMED CORONARY SYND
    [00:00:00 20/09/2012] ic dd=41401 ds=1 ld=CORON ATHEROSCLER NATIVE
    [00:00:00 20/09/2012] ic dd=49390 ds=5 ld=ASTHMA, UNSPECIFIED
    [00:00:00 20/09/2012] ic dd=53081 ds=6 ld=ESOPHAGEAL REFLUX
    [00:00:00 20/09/2012] ic dd=V707 ds=8 ld=EXAM PARTIC CLINICAL TRI
    [10:00:00 20/09/2012] po tf=[12:00:00 21/09/2012] pt=Unit Dose rt=PO fr=QD fs=10 dn=1 m1=Aspirin (Buffered) v1=325 u1=mg
    [10:00:00 20/09/2012] po tf=[12:00:00 21/09/2012] pt=Unit Dose rt=PO fr=QD fs=10 dn=1 m1=Atorvastatin v1=10 u1=mg

    means in a colom many datatype value come together e.g datetime,varchar,int .
    how we seperate them in different colom according to their datatype.
    please help me it’s requried for my project.

  22. Hi Pinal,
    We are using SQL Server 2005. We have scheduled Push-Replication between Server-1 (Publisher & Distributor) and Server-2(Subscriber). We are currently designing on Disaster recovery plan.

    Consider the scenario that Server-1 goes unavailable.

    What are the steps to restore the Server-1 with publishing jobs and User Databases and re establish the replication with Server-2?
    What are the back-ups of databases we need to take (like master, msdb of publisher etch..) for re-establishing the replication?
    During the downtime of Server-1, there might be some data additions to the user database in Server-2. How to retain them while re-storing the Replication?


  23. Hi Pinal,

    How do i migrate DTS packages from sql server 6.5 to sql server 2005 .

    Thanks you i need your help please .

    kind regards

  24. Hi Pinal,

    Request you please look into the below.

    I am working on the logic for the following scenario.

    Table : t1 with cols (EmpId int , PresentDate DateTime , Shift Varchar(20))
    No of rows are variable in the above table.Eg of data is

    EmpId Shift id Attendance date
    21213 1 12/02/09
    21225 2 12/02/09
    21213 2 13/02/2009
    21225 1 13/02/2009

    What i am trying to achieve is

    EmpID 12/02/09 13/02/2009
    21213 1 2
    21225 2 1

    Appreciate your help.


    • Hi Pawan,

      You can use Cursor/While loop for it :-

      we have a table which contains like

      Invoice No Item Item Desc Item club Location
      7/08-09 SER Service Charges for Repair CHD
      8/08-09 SER1 Service Charges for Repair CHD
      8/09-10 SRC Service Charges TMT CHD
      9/08-09 SERVCH Service Charges for Repair CHD
      10/09-10 SERV1 Service Charges for Repair CHD
      11/08-09 SER2 Service Charges for Repair CHD
      11/09-10 SER3 Service Charges for Repair CHD
      12/08-09 SER4 Service Charges for Repair CHD

      Now I used Cursor for clubbing of item description for each invoice number i.e.

      declare @locn_no varchar(20)
      declare @locn_no_tmp varchar(20)
      declare @invoice_no as varchar(20)
      declare @invoice_no_tmp as varchar(20)
      declare @item_no as varchar(20)
      declare @description as varchar(250)
      declare @description_tmp as varchar(2000)

      select @description_tmp=”

      declare getrun cursor for

      select location,invoice_no, item_no, description
      from invoice_item_tbl
      order by location,invoice_no, item_no

      open getrun
      fetch next from getrun into @locn_no,@invoice_no,@item_no,@description

      while @@fetch_status=0

      select @invoice_no_tmp=@invoice_no
      select @locn_no_tmp=@locn_no

      update invoice_item_tbl
      set item_desc=@description_tmp+@description+’ , ‘
      from invoice_item_tbl
      where invoice_no=@invoice_no
      and location = @locn_no

      select @description_tmp=item_desc
      from invoice_item_tbl
      where invoice_no=@invoice_no
      and location= @locn_no

      fetch next from getrun into @locn_no, @invoice_no,@item_no,@description

      if @locn_no_tmp = @locn_no
      if @invoice_no @invoice_no_tmp
      set @description_tmp=’ ‘

      if @locn_no_tmp @locn_no
      set @description_tmp=’ ‘

      close getrun
      deallocate getrun

      It may be done thorugh While loop

  25. @Pawan.

    Try using PIVOT OPeration.

    Try below script, may be this could give you a start…

    Create table t1 (EmpId int , PresentDate DateTime , Shift Varchar(20))

    insert into t1 (EmpId, Shift, PresentDate) values ( 21213, 1, ’02/12/09′)
    insert into t1 (EmpId, Shift, PresentDate) values ( 21225, 2, ’02/12/09′)
    insert into t1 (EmpId, Shift, PresentDate) values ( 21213, 2, ’02/13/2009′)
    insert into t1 (EmpId, Shift, PresentDate) values ( 21225, 1, ’02/13/2009′)


    select * from t1
    SELECT EmpID, [2009-02-12], [2009-02-13]
    FROM (
    SELECT EmpID, PresentDate, Shift
    FROM t1) up
    PIVOT ( max(shift) FOR PresentDate in ([2009-02-12], [2009-02-13]) )AS pvt

    If your concern is rows are variables, meaning there could be multiple dates and if there are multiple dates then in your final output you will have more columns, then I would suggest you to use dynamic SQL.

    Let us know if you need help with this.

    ~ IM.

  26. Hi Pinal,

    Everybody getting answers and suggestions from you and your team for their queries.But i haven’t received any thing.

    Just My question is:

    In our organization, they are planning to migrate from Informix Database to SQL Server 2005 database.

    I need to prepare documentation.I am new to prepare documentation.Could you help me where and how do i start?

  27. Hi,
    this is Lokesh, I have joined Sql server DBA Course, i am about to complete the course in a week..

    So, I want to know is there openings for Freshers? Please let me know, if any openings is there..
    Because I am not having experience.. I am planning to do Certification in DBA, I hope it will be an Extra Advantage.

    Please guide me…>


  28. Hi,

    I created linked server connection to Access 2003 (.MDB) file from SQL server 2005 server.

    Can I create new columns in access database dynamically through this linked server connection and dump data?

    I tried to execute alter table command using OPENQUERY, did not owrk….

    Please help me in solving this issue…

    Thanks for your help….

    Surya Prasad.

  29. dear sir,

    i have reading all your solution that help me a lot.

    but here i can’t find that how to merge same database with same table and attributes that exist on different system. i want to merge one system database in other

    how can i achieve it.

    if you have some spare time then kindly please make a query for this above mentioned problem

    i hope this is helpful to others.

    thanks with respect

    • dear sir,

      Please, received my thank first that you gave me your valuable time and suggestion.

      what i do when using mysql.

      actually i have to work with SqlSever and MySql both.

      my offline database is distributed on 3 different sites and here the problem i have facing to merge all sites data in to single one database.

      i have searched more but would not get any fruitful result
      for this i have created a new database and restore all 3 sites database but the newer database data is over write the old one.

      kindly, Please tell me any procedure regarding mysql

  30. Hello Sir,

    I am working on SQL Server 2000 and Development of DTS Packages. Now i want to learn sql 2005 and Datawarehousing. Could you please suggest me some books (pdf format) so that i can learn myself.


  31. pinal,
    in CSV File i have 3 columns and in Destination Table i have 4 Columns and the Extra Column is not Null(Should have some value inserted)

    this 4th column value is populated from the other Column of the Table.

    destination columns=csv file + other column from other table.

    i’m need to use Bulk Insert or any other option ,in this situation how can i proceed.
    Your Help is Appreciated


  32. @Sudarshan
    Perform bulk copy in some other table , or may be temporary table and then insert into your destination table.

    ~ IM.

    • Hi Imran Mohammed ,

      I have one dought in sqlserver2005 Copy_only option is there for without Break LSN Sequence number ,

      In sqlserver 2000 how to do Environmental Refresh Ex: IAM using Logshipping (or)Replication

      please tell me

      Any Script please Provide me


  33. Hi,
    Im always checks ur site for my sql querys.

    I want to implement keyword search in sqlserver2005.

    after googling i found FULL-Text search with indexing.
    I have a problem o how can identify which colum i want to search depends on keyword.

    i want to get accurate result depends on key word.

    my table fields are

    [CollegeName] (varchar)
    [GroupName] (varchar)
    [CollegeAdd1] (varchar)
    [CourseDetails] (xml)

    maximum details are stored in xml field.
    xml is like this




    C.S.E, Computers



    Govt Employee
    Software Engineer
    Hardware Engineer
    Aronatical Engineer


  34. Greetings Pinal Dave,

    Our one of the project using Hyperion Essbase 6.5.3 as backend, now we are planned to migrate into SQL Server 2005. Essbase is Multidimensional Database, in our application we can able do the following: Insert, Update, Delete and Analytical Reports, Ad-hoc Report. How can we do the same in Sql Server 2005?. Please advice me and provide sample code/link.. Hope you reply as earlier…

  35. Hi Pinal or anyone

    In our SQL Server 2005 (on Windows 2003 64bit SP 2 with 10 GB RAM), we are getting these error messages very frequently:

    A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 18832, committed (KB): 5390488, memory utilization: 0%.

    I searched on Google, found some articles even from Microsoft website, but couldn’t solved our problem. Actually some of our clients have complained about very slow response of the server, even when I tried to login to the server (both thru Management Studio on my machine and Thru Remote Login direct to the server), I couldn’t login. However, after a short while I was able to login. I couldn’t find any unusual activity or error message in any log except the above error messages.

    I had my Wintel guy applied a solution from this article:

    But I am still getting the error messages, even more frequently, (it used to be 20 to 30 minutes now after every 5-10 minutes) even though nobody is complaining, but still it is not good sign – I think.

    May be useful for the investigation, in our server the Virtual memory is:

    Total Paging File size for all Drives:

    Minimum Allowed: 16 MB
    Recommended: 15262 MB
    Currently allocated: 4092 MB

    Please let me your observation and suggestions – what could be the reason and solution. Thanks.

    From Canada

  36. I have a question related to defragmenting indexes. I can identify the indexes I want to defragment and rebuild them one by one through a temp table and a WHILE statement. I have a SQL Server Agent job run the process weekly. So here’s the question. Why did 3 of the 119 indexes degragmented actually have a higher defrag percent after the process than before? Most percentages looked a lot better after the process, some were marginal, but higher? I don’t understand that and hope you can shed some light on the subject.

  37. Hello,

    I need a help. As iam not from software background. but i need to execute a query in sql server management studio express periodically.

    SELECT Table1. * INTO Table2
    FROM Table1
    WHERE (“Date_time” BETWEEN ‘1/11/2010 11:52:18 AM’ and ‘1/11/2010 12:26:35 PM’)

    the above query should be executed automatically everyday morning 9.00 am. Is this possible in this tool. ????

    i have tried to do this with internet assistance.

    the following steps were what i have tried:

    1. i have created a batch file with the following script

    @echo off
    osql -U sa -S .\gft -P Fiduciary -i Script.txt -o output.txt

    here, the script.txt file contains the query which creates table 2 from table 1, and i have created an output .txt file also.

    2.if i double click on to the batch file it was not executing anything. command prompt was just opened with a blank screen and it is not closing down.

    3. after i opened the command prompt and typed the below script there.

    @echo off
    osql -U sa -S .\gft -P Fiduciary -i Script.txt -o output.txt

    and i just gave enter. after processing sometime it is giving me the below error.

    [SQL Native Client]SQL Network Interfaces: Error Locating
    Server/Instance Specified [xFFFFFFFF].
    [SQL Native Client]Login timeout expired
    [SQL Native Client]An error has occurred while establishing a
    connection to the server. When connecting to SQL Server 2005, this
    failure may be caused by the fact that under the default settings SQL
    Server does not allow remote connections.

    4. so to change the default settings and to allow remote connection i have done the settings with the following site link assistance :

    After that also i have been getting the same error.

    5. So now i have opened the command prompt and just typed the blow script

    osql -U sa -S

    and it was asking for password and i just press enter, after processing sometime it is giving me the below error:

    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.

    C:\Documents and Settings\Administrator>cd c:\

    C:\>osql -U sa
    [SQL Native Client]Named Pipes Provider: Could not open a connection to SQL
    Server [2].
    [SQL Native Client]Login timeout expired
    [SQL Native Client]An error has occurred while establishing a connection to
    the server. When connecting to SQL Server 2005, this failure may be caused by
    the fact that under the default settings SQL Server does not allow remote

    6. so to sort out the SQL Server [2] error i again use the below site link assistance:

    in that they mentioned about SQLEXPRESS which suppose to be in SQL Server Configuration Manager (Local) → SQL Server 2005 Network Configuration. but in my system i could not found that.

    After all above i came here. i need it severly.please anybody explain me in detail, ASAP.

    Thanks in advance.


    • Hello Ramesh,

      In the osql command provide the password of “sa” login using -P switch.
      If working on SQL Server 2005 or later version then use the sqlcmd utility.

      Pinal Dave

  38. Hi Pinal ,

    I have one dought in sqlserver2005 Copy_only option is there for without Break LSN Sequence number ,

    In sqlserver 2000 how to do Environmental Refresh Ex: IAM using Logshipping (or)Replication

    please tell me

    Any Script please Provide me


  39. Hi Friends,

    Note: Let me know if this is the right place to post this question.

    I have an issue with SQL Server Reporting Service 2005 explained below.

    I have three Servers

    ServerA :- SQL Server 2005, 32 bit machine, Reporting Service installed.

    ServerB :- SQL Server 2005, 32 bit machine , Reporting Service installed.

    ServerC :- SQL Server 2008, 64 bit machine, Reporting Service NOT INSTALLED.

    Please see below and let me know your suggestions as I am getting different errors in different scenario.

    From event logs and error logs I found below errors.

    1. Accessed the web site on ServerA (staging server) and used connection string to connect to ServerC. ServerA has reporting service and accessing data from ServerC (SQL Server 2008 on 64 bit machine,Windows 2003).

    In this case below are the errors I got for reporting charts in eventlog and reporting service log file.

    –Login failed for user ‘ServerC\xyz’. Reason: Token-based server access validation failed with an infrastructure error.
    Check for previous errors. (CLIENT:

    Here the irony is that I was able to login as “ServerC\xyz” from my local machine and could access C drive of the ServerC.

    Here, first I am not able to understand how it picked up above user. As I was running site from my local system, I did not specify above user anywhere and thought that the site is running under “Network Service” user for domain access by default and that user will be used to login to SQL Server 2008 machine, but I was wrong. We have ‘xyz’ user on all the servers (staging, production, not on client where I am accessing site) with same password.

    Also it says failing to connect with above user, then with which user it is accessing data, as I am able to see data in data grid from the ServerC database but only charts are failing?

    And the strange thing is, on ServerC, I see Kerberos or negotiate as the authentication package in Security event log for all the connection with other machines but for the connection from ServerA, I see “NTLM”. Please also let me know if you can, how to verify with which user connection are made from reporting service or w3wp process to SQL Server 2008.

    2. I accessed the site on ServerB (its production server and this also has reporting service, 32 bit machines) and gave connection string of ServerC, I got below errors from event log and reporting service logs.

    –Failure Audit:- Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT:]

    –Error:- SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT:]

    What do you suggest for these?

    Appreciate your time and help.


  40. Hi,

    What is most suitable data type in sql server 2005 to store time duration? As there is no such data type “time”, so most people use datetime; other ways can be to store in float/decimal types like 1.5 can be considered as 1hr & 30mins, depending on your interpretation.

    What is most recommended?


    • FLOAT is the worst kind of way to store durations or almost anything (well, maybe REAL would be worse). You end up with weird rounding errors eventually. If you need to use decimals, use fixed point types like NUMERIC.

      Personally I would use INT or BIGINT to store the durations in minutes, seconds, milliseconds – depending on the accuracy required. It’s easy to convert integer value to for example .NET’s TimeSpan type and display it to user by calling ToString method.

  41. @moin
    For SQL Server 2005 you have to use datetime which is the best datatype for storing datetime values, sql server 2008 has data type TIME, if you want to store only time and this is only on your interpretation then you can modify the value of your date time and store it in some varchar column.
    What kind of time you exactly want in your column?

  42. Hi Pinal,

    How can you set difference total record count of the table?
    I have 3 tables Table A, Table B, Table C,
    that gets wipe out every friday at 9:00 am and new records insert by schaduled job in each table.

    If I want check difference between total record count of new and old records in all 3 tables, How can you suggest me to do that?

  43. Hi,

    I’m getting error msg in SQL management studio when creating sql login. Here is the error msg could not continue with NOLOCk due to data movement. (microsoft sql server, error:601) can someone please advise on the error that keep on prompt. Thanks

  44. Hi Pinal

    I was reading your “Important Guidelines for SQL Server”. I am posting this question at a cost of appearing daft.

    You mention that table names should end with “s”. Isn’t that against the entity modelling 101 course which I took a while ago?

    We were told that entity names, (from which we derive our table names), should never be pluralized, because we are always referring to a single instance of that particualr entity at any given time. Which in turn makes it easier for us to determine whether relationships are many-to-one or otherwise. Or is it just a case of semantics, did I miss the train completely?

    Thanks for the wonderful work. Your blog is an inspiration.


    • I wouldn’t pluralize table names either. I use ORMs and especially Entity Framework heavily. If I got, let’s say, table named Cars that converts to class named Cars in the code it would look really silly to say:

      Cars myCar = new Cars();

      This instantiates a single Cars-object into memory but the type says Cars which means many objects. This could easily confuse coders who are not so proficient with the C# code. Much more clear would be:

      Car myCar = new Car();

      Of course I could change the name of the class to Car and ORM mapping takes care that I’m dealing with table Cars but that feels silly also. Now I would have to remember that class Car is written to table Cars.

  45. I have googled extensively on this issue and cannot find much information related to this problem.

    I am using sql server database encryption with a symmetric key protected by password.

    in my c# code, when i want to read some data, i
    1. submit an “open symmetric key MyKey decryption by password = ‘mypassword'” SqlCommand to open the symmetric key.
    2. call my stored proc using a new SqlCommand to return me back encrypted data.
    3. submit another new SqlCommand “close symmetric key MyKey” to close the symmetric key.

    if i issue the 3 commands in that order, the data is returned but *not* decrypted.

    when i wrap all 3 commands in a *transaction*, the data is decrypted and returned.

    since some stored procs are taking a long time to return it is causing deadlocks. anybody who’s anybody knows that you dont read data within a transaction but it seems unless i open the key, read my data and close the key in a transaction the key does not remain open.

    has anyone else seen this behaviour?

    pinal – can you shed any light on this?

    many thanks in advance for anyone’s help

  46. Hi Pinal,

    Excellent post, I configured my Database Mail using your blog, but how do I ask the stored procedure sp_send_db_mail to send all the file names in the back job when the backup is successful
    or fails. I have created a job and sends test mail until the following: but when I add @file_attachements = ‘C:\Program Files\Microsoft SQL Server\….., it states access
    Use msdb
    EXEC sp_send_dbmail @profile_name =’DB Mail Profile’,
    @recipients =’’,
    @copy_recipients =’’,
    @body =’This is a test message’,
    @subject=’Database Backup’,



  47. SELECT
    @SourceNote = SUM(TotalPatientCount) / DATEDIFF(MONTH, @MinDate, @MaxDate)
    Summary.Remits_DrugView S

    JOIN @Top5 TP ON TP.Name = S.PayerName

    JOIN @Drug D ON D.DrugCsv = s.DrugMstName
    OR ( ISNULL(@DrugCsv, ”) = Replace(S.DrugMstName, S.DrugMstName, ”) )
    JOIN @Age A ON A.AgeCsv = s.Age
    OR ( ISNULL(@AgeCsv, ”) = Replace(S.Age, S.Age, ”) )
    JOIN t_ReportRegions_D R ON R.zipcode = S.ZipPlus3
    AND R.RegionGroup = @RegionGroup
    JOIN @state ST ON ST.StateCsv = R.State
    OR ( ISNULL(@StateCsv, ”) = Replace(R.State, R.State, ”) )
    JOIN @Region RC ON RC.RegionCsv = R.RegionName
    OR ( ISNULL(@RegionCsv, ”) = Replace(R.RegionName, R.RegionName, ”) )

    i need to join with that @Top5 for one report and dont need to join for another.
    Is there any condition i can use .so that i can get two reports from only one report.
    plz have a look when u have time
    Thank You



  48. I have a filestream on a SQL 2008 dbase and the volume is becoming full about 1.5TB. What is the best method create a second filestream will all new data be written to filestream. If load balancing is used will it write only to the volume that has enough storage? Thanks in advance

  49. Hi Pinal,
    I think I need your help in sorting out an issue I have at the min.

    The existing code is in PHP using RESTful web services with CURL extension.

    The Apache web server requests hsbc webserver and retrieves the response as XML data.

    We are migrate to that code into sql server.

    I’ve implemented SOAP web services using web services tasks in some projects. But I can’t do that here. As there are no methods exposed to us or no WSDL exist in REST.

    Can you shed some light into this and help me how I ho about it.

    I am currently analysing the existing PHP code and come to a stage I have interpreated correctly.

    And looking forward to see your reply

    Thanks in advance

  50. Hi,

    I liked your articles. A small suggestion for you to the list of articles (only to this page).

    1) Add a column to group by category – SQL Server, SQL Authority (as we know that you work most of the time two categories at top level). You may have multiple sub-categories

    2) Add table filter using JQuery

    3) Add table sorter using Jquery

    Hope you consider it.

    This is only useful for guys coming back repeatedly.




  51. Hi,

    I am Raju working as a Web Developer.

    I am into a problem, please let me out.

    I have a table containing colums (TopicId, SubTopicId, QuestionNo, DifficultyLevel, RevisionLevel)

    Revision Level column is for maintaining different versions of a question.
    Difficulty Level will tell the toughness of the question.

    I have a query which will get me all the questions with latest Revision Level

    (SELECT MAX(revisionlevel) as revisionlevel, QuestionNo, topicid, subtopicid FROM table1 where subtopicid = 12 GROUP BY QuestionNo,topicid,subtopicid order by QuestionNo )

    No I want the questions to be fetched based on Difficulty Level also.(1 to 4 As Easy, 5-7 As Medium and 8-10 as Difficult)

    For this am using the below query.

    (SELECT MAX(revisionlevel) as revisionlevel, QuestionNo, topicid, subtopicid FROM table1 where subtopicid = 12 and difficultylevel between 1 and 4 GROUP BY QuestionNo,topicid,subtopicid order by QuestionNo )

    This query is executing but the result is not that I need.

    Here the condition “difficultylevel between 1 and 4 ” will consider all the questions titled with difficulty level 1 to 4 irrespective of latest revision level.

    Please help me in writing this query which should consider latest revision level ( latest version of question) which has 1 to 4 difficulty level.

    A question will have

    TopicId SubtopId QiestionNo DiffLevel RevisionLevel

    1 1 1 2 1
    1 1 1 4 2
    1 1 1 6 3
    1 1 1 8 4

    IN the above table I should not get first 3 revisionlevels even the condition is “difficultyLevel between 1 and 4″

    This is any how am retrieving with condition “difficulty level between 8 and 10″ but not for “difficulty level between 1 and 4.

    In the above table

    TopicId SubtopId QiestionNo DiffLevel RevisionLevel

    1 1 1 7 5

    Then question 1 should be retrieved only for condition “difficulty level between 5 and 7″ and not for any other condition.

    Please help me in resolving this.

    Thanks in Advance.

    With regards.

  52. why the following error comes

    “Microsoft.ReportingServices.ReportProcessing.Unhandled ReportRenderingException
    in SSRS 2005?

    We tried all possible ways, the report has image data in bulk,IS it because of that?

    Please give the solution if found?


  53. Hi,

    I have Question Sql data Encryption? I want to encrypt the data in Sql 2005 & 2008 .
    What are the data type can we use ?
    If it is Only Varbinary data type .. Please let me know the reason and what is the length of varbinary should we use.

  54. Hi Pinal ,

    I have one dought in sqlserver2005 Copy_only option is there for without Break LSN Sequence number ,

    In sqlserver 2000 how to do Environmental Refresh Ex: IAM using Logshipping (or)Replication

    please tell me

    Any Script please Provide me


    • Hello Harish,

      Yes, COPY_ONLY option is to take FULL or TRANSACTION LOG backup without affecting the LSN sequence.
      Let us know what is your doubt.
      In SQL Server 2000 there was no way to implement the functionality of COPY_ONLY.

      Pinal Dave

      • HI pinal ,

        Thank you For giving reply.

        No.of interviewers Asking How to Copy_only in Sqlserver2005,

        In Sqlserver 2000 Production server that using in log shipping or mirroring ?.

        With out LSN BreakSqlServer2000 Production to My test Enviroment how to take any backup(Environmental refresh) Any other .net(C#) or Any oTher third party Tools is there please tell me

        Please Don’t Angry…. pinal, how much busy this current possition you are giving Replay. thank you

        Thank you once Again Giving Replay.


  55. Hi Imran,

    I have one dought in sqlserver2005 Copy_only option is there for without Break LSN Sequence number ,

    In sqlserver 2000 how to do Environmental Refresh Ex: IAM using Logshipping (or)Replication

    please tell me

    Any Script please Provide me


  56. we have couple couple of user they are client, they have SQL 2005,
    when they try to create view from management studio, they get message “invalid default schema” but when they write query it works fine
    what could be the issue

  57. Hello Venkat,

    String values can be concatenated using the + operator. For example:

    DECLARE @SqlString nvarchar(10)
    DECLARE @SqlString1 nvarchar(10)
    SET @SqlString = ‘ABC’
    SET @SqlString1 = ‘xyz’
    select @SqlString + @SqlString1

    It returns “ABCxyz” as output.

    Pinal Dave

  58. Hi Pinal Dave,

    can you let me know the answer for the below solution please..

    I have Question Sql data Encryption? I want to encrypt the data in Sql 2005 & 2008 .
    What are the data type can we use ?
    If it is Only Varbinary data type .. Please let me know the reason and what is the length of varbinary should we use.

  59. Hello Arun,

    In SQL Server 2008 there is two type of encryption TDE and cell-level. At first you have to decide which one is fulfilling your requirements.
    For TDE no change is required in database design, schema and application. But its just encrypt data while writing on disk and decrypt data while reading.
    In cell-level encryption you can encrypt data to save from unauthorized access. But this needs change in database schema, and code to read and write in tables. The encypted data can be stored in varbinary data type column and the maximum length of encrypted value could be 8000 bytes.

    Pinal Dave

  60. Hello Pinal and friends.

    In the Q and A of the interview section, can you please include
    1, Compare the advantages and disadvantages of a, Log Shipping; b, Data Mirroring; c, Clustering
    2, Compare the advantages and disadvantages of a table variable and the temporary table.
    3, Partition Views and Partition Tables.

    Can you please explain each of these topics separately. (I have read your informative articles on partition tables)

    I suggest that you split (tag) the interview questions as general, developer, production and BI.


    • Chetan,

      “SET” is set based operation.
      “SELECT” is a SQL clause which uses to return either one or more than one attribute from the entity.

      For e.g.
      1. DECLARE @Val INT
      SET @Val = 1

      2. SELECT TOP 1 name FROM sys.objects

      In above example, SQL Server generates the plan only for second query.

  61. Hi Pinal

    I am trying to insert from one database table to another database table.When i a m creating procedure it shows one error.

    SET IDENTITY_INSERT ‘sysname.dbname.owner.tablename’ ON

    The object name ‘sysname.dbname.owner.tablename’ contains more than the maximum number of prefixes. The maximum is 2.

    Please help Me

  62. Good Day Pinalkumar Dave,

    I was wondering if you could speak too or refer me too any articles that might shed light on the above subject:
    Using Top N with a Where Clause SQLServer 2008

    I have found that if I run a query like

    SELECT TOP 15000 field1
    FROM tbl1
    Inner join tbl2 on =
    WHERE field2 = x and field3 = y and field4 = z

    Let’s say I get 14500 records, yet when I run the same query but get rid of the TOP 15000 I get 200000.

    Why wouldn’t I get 15000 records?

    Any ideas?

    Thank you

    Software Architect

    • Sir,
      If you are asking does the number fluctuate, I don’t believe so. I would need to confirm but I believe that it does not. I am thinking that it is getting top 15000 records from the join first and then looking at the where? I don’t know…I’m stumped.
      In the end it doesn’t return what I am looking for.

      Any information you can provide would be greatly appreciated.

      Thank you

      Software Architect

      • If the total number of rows returned without top is less than 15000, you get that much number of rows you use top 15000
        But it would never return more than the number of rows specified at the TOP clause

        Also top without Order by clause doesn’t make any sense

  63. Hi Pinal,

    I want to be a SQL professional.
    Can you please help me learning sql server and what are the process to learn sql server and ho di acheave it.

    I am redy to work hard but I wan the guaidence.

    Please help.


  64. Hi Pinal,

    I am big fan of you and I do regularly visit your site for many nice tips.

    I am having problem with one of table in our database which have 200milion plus records and schema look as below.

    CREATE TABLE Prices](
    AssetId [int] NOT NULL,
    Date [smalldatetime] NOT NULL,
    Supplied [smalldatetime] NOT NULL,
    Price [float] NOT NULL,
    Currency [int] NULL,
    LastChangeDate [smalldatetime] NULL,
    [AssetId] ASC,
    [Date] ASC
    ) ON [PRIMARY]

    I need to change Date column type from smalldatetime to DateTime.

    I wrote script to drop primary constraint then change column data type then put back primary key constraint then finally rebuild the index.

    My script only able drop script then it fails to do the rest of the tasks.

    I need your help.


        IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID(‘tempdb..#tmpErrors’)) DROP TABLE #tmpErrors
        CREATE TABLE #tmpErrors (Error int)
        PRINT N’Dropping constraints from [dbo].[Prices]’
        ALTER TABLE [dbo].[DS_T02_Prices] DROP CONSTRAINT [PK_Prices]
        PRINT N’Altering [dbo].[Prices]’
        ALTER TABLE [dbo].[Prices] ALTER COLUMN [Date] [datetime] NOT NULL
        ALTER TABLE [dbo].[Prices] ALTER COLUMN [LastChangeDate] [datetime] NULL
        PRINT N’Creating primary key [PK_Prices] on [dbo].[PK_Prices]’
        ALTER TABLE [dbo].[Prices] ADD CONSTRAINT [PK_Prices] PRIMARY KEY CLUSTERED ([AssetId], [Date])
        PRINT ‘The database update succeeded’
        ELSE PRINT ‘The database update failed’
        DROP TABLE #tmpErrors

  65. Hi Pinal,
    Thanks a lot for your forum and articles. You know Whenevr i face any problem related to sql Server .I always gets the Exact solution on your site only.

    Thanks a lot.
    Keep it up.

    • we can’t create clustered index on non primary key just
      because of the simple reason that in clstered
      index,physical sorting is done while in non primary key
      there might be hundreds of duplicate entries. so, if we
      create clusterd index on non primary key it will get
      confused and then error.

  66. Hello Pinal,

    I need your help. This is the best place where i can come when i am stuck up :). Here is my problem.

    I have table in which i have added a new column in the schema.

    Now there is an Bulk insert statement which inserts into this table from another table. The bulk insert statement looks like this

    INSERT into destinationTable
    SELECT *,getdate() as created_ts FROM sourceTable

    Now i have added a column in the destination table, the same column is also added in the sourceTable.

    But when the select is done from the sourceTable the last column which comes is a date (from getdate()) but in my destination table the last column is my newly created column (which char(1)).

    Due to some reasons it is not possible for me to change the insert statement (to remove the *).

    So i am thinking is there some mechanism to give the column order explicitly in the ALTER statement (while adding the column).

    Awaiting for your reply (much needed !)


  67. Hi Pinal,

    Very useful blog. keep it up. I have a question. I have a scheduled job to delete .bak files older than 3 days but the files are not deleted. I manually have to go and delete these files. I am using 2005 sql server and the Recovery model is simple. Any help is appericiated.


    Abdul M.

  68. Hi to All,

    How can drop all user defined stored procedures and functions from database in single query

    Can u tell me that sql query.

    – Samy

  69. Hello Pinal,

    I am inserting lots of rows (near 12 crore) in database using BULKINSERT syntax.
    But my problem is some rows are different format like…

    I have three columns in table: Firstname, LastName and Phone.
    My data file contains two types of row in files.
    1) sumit,sanghani,”123456789,123456789”
    2) sumit, sanghani,123456789

    When I insert rows using “,” separated value first row return 4 column and second row return 3 columns.

    Please reply asap.

    Sumit sanghani

  70. Sir,

    I’m wondering if there is a way to use Visual Studio 2008 and VB.NET to build a front end while using a simple Access 2007 database on a common server for the back end. Can ADO.NET pass queries to the Access database which in turn uses it’s own copy of the Jet Engine to do the work? I know this sounds nutty but I have all the desktop resources I need but I’m having a hard time acquiring SQL Server resources at my company.


    William Rich

  71. I would like to be able to pass the name of the database (through a function, for example). I want to be able to do something like

    declare @run nvarchar(25) = ‘[Feb10]’
    select * from @run.dbo.Companies

    This doesn’t work. I’ve been getting around this by doing:

    declare @sql1 nvarchar(50)
    declare @run nvarchar(25) = ‘[Feb10]’
    set @sql1 = ‘select * from ‘+@run+’.dbo.Companies’

    This works for now, but it seems like there has to be a more efficient way. As the queries I’m using are becoming increasingly complex, I worry that this method is making them go too slowly. Is there a better way to do this?

  72. Remember when concatenating SQL statements like that you risk to open up your database to SQL injection attacks. If you need to build up dynamic queries use predefined views and sp_executesql procedure with which you can parametrize your queries.

    Also make sure that if client needs only select data from the database give the client read rights only and only to the procedure that’s building up the dynamic SQL. Revoke any other (unnecessary) rights.

    And what comes to performance, I’ve read that the optimizer can build up execution plan when sp_executesql is used so it can use that on subsequent calls.

  73. I have a setup a test server using Production server.

    I have over 100 jobs and SSIS packages in Production server.
    I also want logins and all security into test server.

    I have get to everything into test server without shutting down production server. Like I want to setup test server with 1 day old data from prod server.

    Can someone help me with this and give detailed steps to do.

    Thanks in Advance.

  74. Hi Pinal,

    I am a fresh graduate and I am new to SQL Server administrating stuffs. I want to know how to show users of a particular roll using a stored procedure..

    Thanks in advance….

    • Hi Piumi,

      Add a where clause to filter for a Role in following query:

      WHEN (r.principal_id IS NULL) THEN ‘public’
      END GroupName, LoginName,
      FROM sys.database_principals u
      LEFT JOIN (sys.database_role_members m
      JOIN sys.database_principals r
      ON m.role_principal_id = r.principal_id)
      ON m.member_principal_id = u.principal_id
      LEFT JOIN sys.server_principals l
      ON u.sid = l.sid
      WHERE u.TYPE ‘R’

      Pinal Dave

  75. Hi pinal,

    I have two Questions

    On what scenario we create clustered index and non clustered index on a table? Can you explain with example?

    What is extended Stored Procedures?

    Thanks in advance


    • @Chethan.K.V

      1. If you do not know the answer, use SQL Server’s defaults. It will make the PK CLUSTERED (if it is there before any other CLUSTERED INDEX), and all other INDEXes will be non-CLUSTERED.


      Extended Stored Procedures
      This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CLR Integration instead.

      Extended stored procedures let you create your own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.
      CLR Integration provides a more robust and secure alternative to writing extended stored procedures.

  76. Greetings!

    In spite of any and all negative arguments about a vertically aligned database, my customer has mandated its use. We’ve strongly discourged its use with no success in our attempt to dissuade them.

    With that I have a question with regard to returning horizontal rows for a collection of records per “System.” A system – for our purposes – would represent one horizontal record with all its attributes stored vertically (a collection of individual records which make up the one System’s record).

    What I need to do is create a view that returns (in horizontal form) all the System records as a table from which I can perform a Select statement against it.

    A view doesn’t support the SQL Cursor function, a SQL View doesn’t permit the use of stored procedures as its data source.

    SoI’ve been reading about Table-Valued functions. The return statement in a TVF typically has the fields hard-coded and I’ve gotten that to work in my Vertical database model. However, I need to have the TVF dynamically build the fields in the table it returns. This is required so that when a field is added or deleted from the Systems schema design, the target field will either be included (if added) or will not cause the function to error (if deleted).

    Any assistance in getting this to work would be extremely helpful.


  77. Hello Dave,

    I am new to sql and I am trying to figure out the easiest and shortest way to pull out the highest sale price from the sale table as well as few other information from product and customer table. I came up with two ways, please let me know which way is better. If you have a different suggestion, I would really appreciate it.

    Here is my code. Both statements return the same result:

    select top 1 c.Customerid,c.firstname,c.lastname,p.productname,p.category,s.saleprice
    from customers c,products p,sales s,
    (select top 1 max(saleprice) as saleprice,customerid from sales
    group by customerid order by saleprice desc) x
    where c.customerid=s.customerid
    and s.productid=p.productid
    and s.customerid=x.customerid
    order by s.saleprice desc


    select * into ##temp from sales
    where saleprice in (select max(saleprice) as b from sales)

    select c.Customerid,c.firstname,c.lastname,p.productname,p.category,(select saleprice from ##temp) as price
    from customers c,products p
    where customerid in (select customerid from ##temp)
    and p.productid in (select productid from ##temp)

    Warm regards,

    • Here is another way:

      select top 1 c.Customerid,c.firstname,c.lastname,p.productname,p.category,s.saleprice
      from customers c,products p,sales s
      where exists (select top 1 max(saleprice) as saleprice,customerid from sales
      group by customerid order by saleprice desc)
      and c.customerid=s.customerid
      and s.productid=p.productid
      order by s.saleprice desc

  78. Pinal Dave:

    Thanks for rescuing me from the ever annoying appearance of “sqldumper library failed initialization” error message. The site had no clue how to fix it. You’re on top of your game.Bill Gates should send you a commission.



  79. hey Dev,

    i have a Table1 ,Col1 has a Values ‘A”B’,’C’,’D’ etc
    and other Table2 has a columns ‘A”B’,’C’,’D’ etc(from Table1)

    i need to get the data of the Columns of ‘A”B’,’C’,’D’ etc of Table 2.

    Table1 has

    A| B| C| D (Four Columns)
    x| Y|Z|V|

    i need to get the data in col A(x),Col B(y),Col C(z),Col D(v)………

    Please guide me,
    Thanks in Advance…….

  80. Hi Pinal,

    Myself Anup and I am a regular reader of your awesome BLOG.

    I have a situation and I would request your advice in this regard.

    Suppose I have a mirroring session which is working on High Availability mode and Data Transfer between Principal and Mirrored server is Synchronous.

    I understand that data will flow via Principal and will be committed first in Mirrored server and then it will be committed in Principal and acknowledgement will be sent to the application.

    What if: The mirrored server is down? Because there is a commit dependency on the mirrored server.

    Incase the environment is a high transactional system, then log file will be full because commit dependency is there.

    Please advise how the system will work for this situation.

    Thanks in advance.

    Thank You,

  81. Hi Pinal,

    How are you?

    if Database and Shareopoint server in Same box, i am able to see Data in BI webparts,But if data Base is diffrent server/Different machine

    1. If Sharepoint URL Opened in Different Machine,then Data is not displaying BI webParts.
    Showing below error

    This error BI web parts

    Can not create instancee

    This below error from Reportserver

    [An error has occurred during report processing.
    Cannot create a connection to data source ‘PBXTracking’.
    For more information about this error navigate to the report server on the local server machine, or enable remote errors ]

    Appshark pvt ltd,Hyd,India

  82. Hello Pinal,

    Few days back I attended a interview. In that i was asked a question, as below

    There is a table in which there is no Primay key constraint and the records in the table are duplicated (as shown below)

    ID Fname Lname
    —– ———— —————-
    1 XYZ PQR
    1 XYZ PQR
    1 XYZ PQR
    2 ABC DEF
    2 ABC DEF
    2 ABC DEF

    I need to write a single delete query which will delete all the duplicate records and keep only a unique copy of each record. I said that it can be done by doing a bulk insert into a temporary table, by selecting from source table doing a group by on all columns and then truncating the original table and then again doing a bulk insert from temp table.

    But the interviewer said that it can be done by a single delete statement. I tried to search on the internet but i am not able to find any answer. Can you please suggest….

    PS : I cleared the interview :) :)

  83. Hello Pinal,

    Got the answer to my previous question (on this blog only).. Thanks a lot. This blog rocks !!!! :)


  84. When i run this query, its appear the following error message, can u tell me any person, why appears this error message?

    Create Table TestCompression (Col1 INT, Col2 CHAR(50))
    Insert Into TestCompression Values (10, ‘Compression testing’)
    GO 5000
    — Original
    EXEC sp_spaceused TestCompression
    ALTER TABLE TestCompression
    EXEC sp_spaceused TestCompression
    ALTER TABLE TestCompression
    EXEC sp_spaceused TestCompression
    ALTER TABLE TestCompression
    EXEC sp_spaceused TestCompression

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword ‘WITH’.

    Msg 319, Level 15, State 1, Line 3
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword ‘WITH’.

    Msg 319, Level 15, State 1, Line 3
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword ‘WITH’.

    Msg 319, Level 15, State 1, Line 3
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    – Samy

  85. Hello Pinal,

    I have been reading your articles for sometime now but I have not come across an article that will solve a pending issue.

    I need a way (using SQL) to compare an inventory request for say a quantity of 2 to the current balance of quantity 5 and then update (temp table) to set balance equal to 3 before I compare the next quantity request of 1.

    CREATE TABLE invTest( reqID int, prodID varchar(20), reqQty int, invQty int)

    INSERT invTest(1234,’015551212′,2,5)
    INSERT invTest(1235,’015551212′,2,5)
    INSERT invTest(1236,’015551212′,1,5)
    INSERT invTest(1237,’015551212′,4,5)

    The goal would be to use LIFO (last in first out), so the inventory would be exhausted after filling reqID(1237,1236)

    I hope this makes sense, but I cannot seem to find a resolution.

    Anything that you can do would be greatly appreciated.

    Thank you,
    Randy Sigmond

  86. just want to say that you are great. every thing you understood us by photo. which is very helpful for us. Thanks.

  87. emp_no emp_name supervisor_no
    1 vinay 3
    2 kamesh 1
    3 muhu NULL

    This is my table datas.
    I want the result to be in this format;

    for vinay the suprevisor is muhu which is from the same table…

    emp_name supervisor_name
    vinay muhu
    kamesh vinay
    muhu NULL

    • Select a.EMP_Name,b.Supervisor_Name From
      (Select Emp_no as SUP_No,Emp_Name as Supervisor_Name From EmpTable Where Emp_No in (Select Distinct SUP_NO From EmpTable)) as b,
      EmpTable as a
      where a.SUP_NO=b.SUP_NO and a.Emp_Name not in (‘null’)

      EMP_Name Supervisor_Name
      ————————- ————————-
      Vinay Muthu
      Kamesh Vinay
      Muthu NULL

      (3 row(s) affected)

  88. Dear Pinal,
    Problem1) Sql Server 2005 64 bit enterprise edition is consuming 7 Gb memory out of 8 GB.Database size is also 1GB and transaction occurs in kbs.I saw performance monitoras well as hit ratio is greater than 98 %.What would be the problem.Cpu utilization is 1%.
    Problem2)I scheduled transaction backup every half hour.After executing transaction log backup i execute DBCC SQLPERF(Logspace).But it shows log percenatge always same as before backup.What would be the cause.
    Problem 3) After transaction log backup we see log percentage growth to zero but when we check log size then it is not changed.So i want to know actually what transaction log backup works.
    Problem 4)How to trace Database size weekly
    Problem 5)I am unable to create jobs in SQl Server using maintenance jobs.

  89. hi sir regarding the bulk copy from csv to sql…is there any way which can validate if there’s an existing data in sql from csv???need you help..thanks

  90. Dear Pinal,

    What is the best way of deigning Audit trails in DB? Further to add it, how can we optimize the queries on top of this, to get the reports, query on single column for updates, etc.


  91. Hi Pinal,

    How Can I add a non DBO user to read sysobjects in Sql Server . Is this possible or does the user have to be DBO to access sysobjects table. Appreciate your help on this .

    • Hello Nima,

      Check if sys.all_objects is accessible. The permission is not required for sysobjects catalog view, but on objects that can be viewed in its result. The user must have some permission on object to get viewed in sysobjects output.

      Pinal Dave

  92. Hi Pinal,

    Is there any alternate for wildcard character in full text search.. my script is taking more time to use this full text search..

    Thanks in advance !

    • In Full text search with CONTAINS or CONTAINSTABLE fucntions there is no need of wildcard (*) until unless you are not looking for specified prefix of suffix.
      Please let us know for what type of search, what is your query.

      Pinal Dave

  93. new to this so bear with me. how can i verify that the data in my csv file (created from an excelspreadsheet) is clean and contains no corrupt data.

  94. Hi Pinal
    I am a silent follower of your website. I really like your post and they are much helpfull too. I work as a BI Consultant in US. Here I have a Task on which I need your help. Its really very Urgent!!

    I am supposed to retrieve files from a SFTP Server (*.dat files) to our local server using SSIS 2005 package. When I tried googling regarding this, many of them suggested using couple of Third party tools, as I cannot perform this Task using FTP. I am not allowed to use those 3rd Party Tools here.

    Can you please help me with this. The best possible script for my package to recieve files from a sftp server to my local server with out using Third party tools.

  95. Any one know what is the max number of columns allowed in SQL Server 2005 replication. I have two tables of more then 350 columns.

    You reply is much appreciated in advance.

    Kamlesh C. Patel

  96. Hi Goud,

    SSIS provides in-memory intermediate storage, better error handling with error-row redirection and event procedures, comprehansive logging, dynamic deployment with configuration files and easy user interface.
    But if your data migration is a simple select-insert then use t-sql queries.

    Pinal Dave

  97. Hello Dave sir,
    i have installed visual studio 2008 alog with SQL that was already in studio 2008. but when i tryed to create DataBase it showing me following error…

    An error occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact under the default setting SQL Server does not allow remote connection.(Provider: Named pipes Provider, error:40-Could not open a connection to SQL Server )

    and also tell me how to find server name….is it “SQLEXPRESS” or else….while creating DataBase he is asking me for server name, so what should i write over there?????…pls tell me it’s veerrrrryyyyyy urgent?
    thank uuuu….
    byeee tc…

  98. Hello Amit,

    studio 2008 could be : SSMS, BIDS, or visual studio. The service that you are using should be running. Even you can work in disconnected mode in these studio.

    Pinal Dave

  99. Amit kalke

    Hello Dave sir,
    i have installed visual studio 2008 along with SQL that was already in studio 2008. but when i tryed to create DataBase it showing me following error…

    An error occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact under the default setting SQL Server does not allow remote connection.(Provider: Named pipes Provider, error:40-Could not open a connection to SQL Server )

    and also tell me how to find server name….is it “SQLEXPRESS” or else….while creating DataBase he is asking me for server name, so what should i write over there?????…pls tell me ?

    byeee tc…

    • @Amit

      When you install SQL Server 2005 on a machine you usually have two options.
      1. Install SQL Server with Default Instance Name ( Default instance name is nothing but name of the computer).
      2. Install SQL Server with Named Instance (You need to provide name to instance and complete SQL Server instance name will be Computer Name\Instance Name)

      When Installing Any Edition of SQL Server 2005 (Like Enterprise, Developer, Standard, Workgroup but not Express) with Default Instance Name, you will see that SQL Server Service name will be MSSQLSERVER. When you want to connect to this SQL Server Instance, you need to type in name of the Server(Windows Machine).

      When Installing Express Edition of SQL Server 2005, SQL Server Service name will be shown as SQLEXPRESS (like in your case), if you want to connect to this SQL Server, you need to type in the name of the computer as Server Name.

      If you are trying to connect to this server from other machine and you get this error: SQL Server does not allow remote connection, then follow this link,

      Let us know if you this do not solves your issue.
      ~ IM.

  100. dear sir

    i have company table and create a stored procedure for it

    if new company inserted the procedure out a parameter ‘Company saved’ and if comany exists it out a param ‘Comapny already exists’ but the problem is that when we run this it can’nt out this parameter

    the SP is here:

    ALTER PROCEDURE [dbo].[InsCompanyMaster]
    @CompanyName varchar(500),
    @result varchar(50) output
    if not exists(select * from CompanyMaster where CompanyName=@CompanyName)
    Insert CompanyMaster (CompanyName) values (@CompanyName)

    select @result=’Error in query’
    select @result=’Company saved’
    select @result=’Company already exists’

    • Can you use this procedure….

      CREATE Procedure dbo.InsCompanyMaster
      @CompanyName varchar(50)
      Declare @Result varchar(50)
      IF NOT EXISTS (Select * From CompanyMaster Where CompanyName=@CompanyName)
      Insert CompanyMaster Values (@CompanyName)
      SET @Result=’Company Saved’
      SELECT @Result Output
      SET @Result=’Company Exists’
      SELECT @Result Output

  101. Hi,

    I need a sql query to get the following

    Findout that are all the Stored procedures called against a database, by whom. When an application call of to execute the SQL action requested.

    Procudure Name
    Machine name (Executed from)
    login name
    Executed time


  102. Hello Pinal,

    I am facing a problem regarding Multilanguage in SQL Server 2008. When i store a multilanguage string in database using the nvarchar datatype, its stored as “아라파트”. When i try to search this particular column value from my application (using nvarchar N’searchval’), its not searching. I want to know why in database its stored like that way. Is it a functionality of SQL Server 2008. As far as i know in 2005 it was storing multilanguage in some other format, some symbols kind of thing.


  103. Hi
    Data Synchronization In SQL Server database table using sql script or TSQL script or code.
    I want to Synchronize my In SQL Server database table at server end using client database table where data are entered, both database have same table name.
    if this can be accomplish by using TSQL ,Stored Procedure then it is good otherwise the script in ASP.NET(C#.NET) is better.

    • You can make use of replication
      or add another server as linked server and do query

      insert into linked_server,db.owner.table(columns)
      select columns from table as t
      where not exists
      (select * from linked_server,db.owner.table where keycol=t.keycol)

  104. Hi Pinal and Everyone

    Above is the URL what I use to send SMS by providing the actual mobile number and message. Till now I am using the HTTP URL PUSH method from a JSP file which does not open the browser, just process the above URL on the server and the mobile number mentioned receives the SMS.

    Now I want to remove JSPs from the picture and do the same task whenever a new record is inserted into a table. Suppose the record has the mobile number and the message to be sent, how can I generate the above http request in the trigger on insert on that table?

    Any help will be appreciated, I am struck into it for the last 3 days and tired of googling.



    • In the AFTER INSERT trigger,write

      insert into target_table(columns)
      select ‘’+cast(mobileno as varchar(20))+’&message=’+message from inserted

  105. Hi,

    I want to get the first and last row from .txt file using SSIS wizard.

    Could you please help me out on this?

  106. Pinal Dave,

    You seem to have an extremely strong far reaching knowledge of sql server. I was wondering if you have ever run into the the issue I am trying to solve.

    I have a page that dynamically builds forms input fields from a table allowing users to enter input into the form. Each input is a field in a _info table.

    I would like report showing all of the entries of a user in one line. Each field entry is a row. So I need to take the vertical data and make it horizonal. There are many different forms with different configurations so must be dynamic. I’ve come across a lot of articles for cross-tab pivots but they only work for aggragates/sums.

    Below is an example table layout:

    500 “Custom Form 1″

    1 500 “Field 1″
    2 500 “Field 2″

    23 500 45
    24 500 98

    23 1 “My Name”
    24 2 “My City”

    Required Report Output:
    REG_ID |Field 1 |Field 2 |FORM_ID |USER_ID
    23 |My Name |My City |500 |45
    24 |Another Name |Another City |500 |98

    • Hi Troy,

      You can do it by creating two derived tables for each Fordm_field as below:


      This is not a tested query so you need to edit and verify it it as per your requirement.

      Pinal Dave

  107. Hi Pinal,

    I am using SQLCMD with -itest.sql and -otest.log and noticed that log file is not showing information accurately.

    Example, in test.sql I have

    select ‘Update customer_name at: ‘, getdate()

    update 1

    update 2

    update 3

    test.log will have information in just two of them. It just seems that it skips writing to log file ramdomly.

    Can you please advice what should I do to fix this?

    Best regards,

  108. hi,
    i wanted to know the difference between view and cursor ? I am reading a large amount of data and was wondering if we can subset what would be advantageous. A cursor with selected rows or a view on that table.

    • Hi Pal,

      View is a Virtual table and Cusror works as a datareader. Anyhow there is no similarity in View and Cursor.

      But in respect to get large amount of data in subset View is a better approach.

      because in cursor we have to again read the whole subset data and it’s a time consuming process.

  109. Get last Child record from root parent Category id

    i have table “Category”….the fields of table are as per Below

    1) CategoryId
    2) CategoryName
    3) ParentId…(its foreign key to the CategoryId of this table)
    4) CategoryLevel

    now i want to get last child category id from the root category id….

    Records are as per below….

    CategorId CategoryName ParentId CategoryLevel
    1 Root Cat 1 0 1
    2 Root Cat 2 0 1
    3 Child Cat 1 1 2
    4 Child Cat 2 1 2
    5 Child Cat 3 2 2
    6 Child Cat 4 2 2
    7 Child Cat 5 3 3
    8 Child Cat 6 3 3
    9 Child Cat 7 3 3
    10 Child Cat 8 4 3
    11 Child Cat 9 5 3

    now if i pass CategoryId=’1′ then i expect results as per below

    CategorId CategoryName ParentId CategoryLevel
    3 Child Cat 1 1 2
    4 Child Cat 2 1 2
    7 Child Cat 5 3 3
    8 Child Cat 6 3 3
    9 Child Cat 7 3 3
    10 Child Cat 8 4 3

  110. Hi pinal, i would like to findout tht is it possible for me to perfom a full text indexed search on an sql server running CTP version. i have configured everything ok but when i run the sql statement no rows are returned. can you please hel me on this.

  111. SQL Management studio ( SSMS.exe ) is throwing memory exceptions while selecting from a record size of 2 Crores. i have noticed that the process running in taskmanager is SSMS.exe*32 and its close to 1.5 GB Memory. The server has a 64 bit processor as well as SQL 2008 64 bit with 8 GB RAM. I have set 4 GB max for SQL server process. Is the issue due to 32 bit studio that is used for this 64 bit database. I tried installing SQL Mngmt studio 2008 Express 64 bit but i am unable to select features.

  112. Hi Pinal,
    First of all I wan tto thanks you for all of your blogs, helped me a lot to learn and grow.
    then I am facing a problem..
    I am connected to a DB exists on a SQL SERVER 2005
    I am deleting data from a table which consists approx.. 68 lac records. I need to empty it and Insert data again it this table.
    But when I execute “Delete from ” Or “Truncate Table ” It takes s long time to delete the data, today when I run the delete command it took 4 Hrs to delete the data. There is no index on table, please help me and suggest what may be the cause of it, and how I can resolve it.
    I am really very frustrated with this situation. please Help me..

    Thanks in advance..


    • Hello Gyan,

      If there is no foreign key constraint on this table than use truncate instead of delete to delete all records.
      Verify if there is any trigger for delete statement.
      Is this replicated or published by any way?

      Pinal Dave

  113. Hi,
    I need your kind assist , my scenario is I’ve 5 branches including one main branch all running sql server 2003 and on daily basis pull the data from other server to the main head office . What is the best and secure way of pulling data from there currently there is no vpn is setup yet . Hope to get some idea from you or any other member .

    Tehseen Sagar

    • Well, you do need data encryption between your offices anyway so I wouldn’t do anything until I have VPN in place.

      At my workplace we have exactly the same situation. Five factories which all have their own Sql Servers (lot’s of them) and head office which pulls everything from factory servers to our big servers. I’m not expert on this area but I’ve understood we use DTS to the actual transfer, timed jobs to execute the transfer once a night and VPN to keep the data safe during the transfer. We also have some heavy network infrastructure (2×1 gb fiber in every location IIRC) to make sure that everything goes fast.

  114. In SQL Server Mode one can access the database by supplying valid login name and password. We can restrict the database visibility using SQL Server Authentication. Im doing this in customer place at the time of installation in SYSTEM-A [Computer Name], so that he can not see the database tables or stored procedure scripts.

    Now the scenario is like this:
    Assume a techie at customer place copied database files from SYSTEM-A, and try to restore the database in SYSTEM-B using Windows authentication mode. There he can see the table structures and sp scripts. How to avoid this, is the question.
    OR. How to lock database tables and sp’s in this scenario?

    • Hello Lokesh,

      SQL server 2008 introduced new feature called Transparent Data Encryption (TDE) to encrypt data in database files and backups.
      In previous versions you will have to keep the backup files secure enough to come in suspecious hands.

      Pinal Dave

  115. Hello Dave,

    Is this good practice to use NOLOCK with in the BEGIN TRANSACTION AND COMMIT TRANSACTION in a stored procedure..


    –Some operatrions on tables…


    Thanks in Advance,

    • Hello Shalem,

      The functionality of NOLOCK does not affected by BEGIN or COMMIT TRAN statemets. So if you want to reduce locking and blocking at the cost of dirty read then use NOLOCK with each SELECT statement.

      Pinal Dave

  116. Hi Pinal

    Do you provide professional Service.. We need to expert opinion on How we can increase our database efficiency .. If yes please let me know.. I will be providing further details.

    Suraj jain

  117. Hello Dev sir

    I have two table with there fields are below . .



    ” Department ”


    My Query is ===>>>

    ” Find out the employee name and department name of employee in each department “

  118. Hello all,
    help please
    I recently enabled my production server for mirroring, which works fine but my transaction log just keeps growing and is up to 98G and my datbase is only 3G..i find when i remove mirroring i can shrink the log file…is there any way i can shrink my log file without removing mirroring.

  119. please help

    I came across a peculiar problem with two tables. Can you
    please let me know the solution. Please fnd below the scenario
    A productcentre can have any type of applications

    a ProductCentre table
    prdcntrId (primary key), prdcntrname

    a ApplicationType table
    apptypeid (primary key)
    prdcntreid(foreign key to ProductCentre )

    ProductCentre table
    prdcntrId prdcntrname
    001 Delhi
    002 Mumbai
    003 Hyd

    ApplicationType table
    apptypeid prdcntreid apptypname
    11 001 Busines
    12 003 Engg
    13 001 Soft
    14 002 Science

    The end result should be like this
    prdcntrname Busines Engg Soft Science
    Delhi 1 0 1 0
    Mumbai 0 1 0 1
    Hyd 0 1 0 0

    Result table shows delhi has 1 busines, 1 softwre application
    mumbai has 1 engg aplication etc.
    pleae let me knw if we can achevie this result

  120. Dear all
    i am fresher i stuck in following problem pls help me…

    i have “Employee” table with fields.


    the datatype of HireDate is date time ….my query is that i want to display all the employee whose joining is in 1999 …so how i can do this ????

    • Hi Amit,

      You can simple write a query with where clause.

      You can identify year as:

      SELECT YEAR(HireDate)
      FROM Employee

      To find employee hired in 1999, you can write it as:

      SELECT *
      FROM Employee
      WHERE YEAR(HireDate) = 1999



  121. HI
    We are working with our client to migrate their Enterprise data from SQL 2005 32 bit to SQL 2008 64 bit. Keeping this in mind, we wanted to talk to people who have done SQL Server migrations so that we can make sure that we cover all the different aspects related to the migration. It could be related to different architectural approaches to ensure high availability OR issues that we might encounter during migration etc..
    can you share some docs if you have (steps to follow)


  122. Hello sir..

    please go through link..

    i have question.

    please give me some hint.. by Sql Server..
    as of now
    i have solved it by creating temporary table in SP. and have played with query to generate such output…

    but i m not satisfied with this solution..
    please give me some hint… or proper solution..


  123. Dear Pinal,

    I have a simple query.

    I have different tables with Auto incremented INT as primary key.
    PK auto incremented INTs are FKs in some other tables.

    I use joins in sql statements to retrieve rows from multiple tables.

    All I want to know is that
    – If I use SQL server and define a MAPPING (at database level using the diagram tool), does the query execute faster?

    Does a linking tables affect the speed at which joins are calculated when a query is executed?
    I get the same result, with or without the tables being linked at database level. Just wondering about efficiency.

  124. Dear dave,
    I m working as sql programmer at Lucidinfotech Bhubaneswar, India. I want to prepare for SQL DBA. Will u please help me knowing the process of Microsoft Certification. And also which book I shall go through for the same.

    Thanks & regards
    Sirajur Rahmaan

    • Dear Pinal,

      I have a Situation that Create a interface to Upload table from excel to SQL Database.

      For that my approach is like create a table, copy the data from excel to new table Using bulk copy. Process the table and Update the Processed data to Required table. After updating delete the created table.

      This works fine if the one user is Uploading. Wat if multiple user try to upload. Whether SQL will Automatically manage the table Creation and update process ?


  125. Dear Pinal,

    I have a problem on connecting to the SQL server 2005 via the port 1433. Firewall are turned off and the server is running on Windows 2003 environment. Every time I am trying to connect it says cannot connect to the SQL Server.
    What are the possible reasons for that???

    Thanks & regards


  126. I am not sure, whether I can ask it here or not,

    I am newbie, I would like to select the list of users in the table, DrillTypes, and count. I would like these count fields as dynamic columns. ex

    Name applDrillCnt BreathDrilCnt KnotsDrlcnt SmalGeaCnt
    1. Giri 12 15 5 20

    how can i do that with best performance

  127. Hi Thanks for previous one, I have one more query.
    how can i display dynamic columns, in my customer table, i have different products, and the purchased dates. i want to display the customername, productname, count(product), 1stPurchase, 2ndPurchase, 3rdpurchase, ….so on
    groupby the purchasedate but dont want to display the dates, instead i want like 1st, 2nd, 3rd,… these should be dynamic columns.

    pls help me how can i do that

    • Hello Gerry,

      You can specify the column name as in following example:

      USE AdventureWorks;
      SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
      (SELECT PurchaseOrderID, EmployeeID, VendorID
      FROM Purchasing.PurchaseOrderHeader) p
      COUNT (PurchaseOrderID)
      FOR EmployeeID IN
      ( [164], [198], [223], [231], [233] )
      ) AS pvt
      ORDER BY pvt.VendorID;

      Pinal Dave

  128. Hello sir,

    My question is my two application is running one is Window Application and second one is Web Application.
    In both Application Some Functionality is same for example i am add customer detail in window application also same same detail in Web application.i want whatever data i can insert or update its effect on both database or During some period i can run Query on sql server 2005 then its data transfer to Web Application Data.

    Is it Possible ?Because Lots of problem is Created in Data when i m Copy Data to Window Application And Paste In Web Application Data. or Enter in Both Database.

    Please Give Me Solution ,

  129. Hello Pinal,

    Do you conduct any training on SQL Server in Ahmedabad. I am looking for a crash course in SQL Server 2008 for 1-2 weeks.

    Thanks & Regards


  130. Hi Pinal,

    I found below indexes defined one db table.

    CK_GroupByColumns nonclustered located on PRIMARY ItemID, UnitOfIssue, RegHospDivID, CabinetID

    PK_TransactionDetail clustered, unique, primary key located on PRIMARY TransactionDetailID

    YMC_Ex nonclustered located on PRIMARY YearMonthCode, ExclusionType

    YMC_TD nonclustered located on PRIMARY YearMonthCode, RegHospDivID, CabinetID, UnitOfIssue

    YMC_UIUS nonclustered located on PRIMARY YearMonthCode, ExclusionType, ItemID

    YMN_EXT_Item nonclustered located on PRIMARY YearMonthCode, ExclusionType, ItemID

    I am querying the table in one query with RegHospDivId, YearMonthCode in where condition and in other query with YearMonthCode, RegHospDivID in where condition and join on CabinetID.

    1. I am not sure, how will above repeat of columns in multiple non-clustered index help?

    2. Which non-clustered index will be used in which case then?

    Appreciate your time.


  131. please help me out i am using this query, but the result set 0 on every count. when i execute the subquery i can get the results. is there any other way to do this?

    DECLARE @RecruitId Int
    SELECT Distinct(RecruitId), (
    SELECT COUNT(DrillType)
    FROM dbo.Drill
    WHERE DrillType LIKE ‘Appliance%’
    AND RecruitId = @RecruitId)AS Appliance, (
    SELECT COUNT(DrillType)
    FROM dbo.Drill
    WHERE DrillType LIKE ‘Breathing%’
    AND RecruitId = @RecruitId)AS Breathing, (
    SELECT COUNT(DrillType)
    FROM dbo.Drill
    WHERE DrillType LIKE ‘Driving%’
    AND RecruitId = @RecruitId)AS Driving, (
    SELECT COUNT(DrillType)
    FROM dbo.Drill
    WHERE DrillType LIKE ‘RoadCrash%’
    AND RecruitId = @RecruitId)AS RoadCrash, (
    SELECT COUNT(DrillType)
    FROM dbo.Drill
    WHERE DrillType LIKE ‘Knots%’
    AND RecruitId = @RecruitId)AS Knots
    From dbo.Drill
    Group by RecruitId

  132. Hi,
    according one of your blogs, I restored a database through queries but after execution, it says successfully completed which the restoring a database in the process when I physically view in the object explorer of the Microsoft SQL server 2008. Neither I cant open that database. Please suggest me this issue.

  133. Hi Sir,
    While executing a stored procedure ,It has a statement like



    I Get error while executing the stored procedure .Error like

    User ‘guest’ does not have permission to run DBCC shrinkdatabase for database ‘tempdb’.

    Could you please suggest me the possible reason for this error and what could be the possible solution.


    • Hello Sradhanjali,

      This is permission denied issue. You can execute this stored procedure by the login that is either db_owner of database or a member of sysadmin server role.

      Pinal Dave

  134. Hii
    plzzz i need ur help
    i have problem with instal MS SQL 2005 on windows 7,
    when i try to login give me error 18456
    login by sa is dosnt work

    plz help

    thanks for ur time

  135. please help me with solution

    Please fnd below the scenario

    a ProductCentre table
    prdcntrId (primary key), prdcntrname

    a ApplicationType table
    apptypeid (primary key)
    prdcntreid(foreign key to ProductCentre )

    ProductCentre table
    prdcntrId prdcntrname
    001 Delhi
    002 Mumbai
    003 Hyd

    ApplicationType table
    apptypeid prdcntreid apptypname
    11 001 Busines
    12 003 Engg
    13 001 Soft
    14 002 Science

    The end result should be like this
    prdcntrname Busines Engg Soft Science
    Delhi 1 0 1 0
    Mumbai 0 1 0 1
    Hyd 0 1 0 0
    A productcentre can have any type of applications
    Application types can be business, enrng, science.
    productcentres are delhi, mumbai,hyderabad.

    Result table shows delhi has 1 busines, 1 softwre application. mumbai has 1 engg aplication etc.

    pleae let me knw if we can achevie this result
    by using queries.


    • @Karthik

      Something like this?

      ProductCentre(prdcntrId, prdcntrname)
      SELECT 1, ‘Delhi’ UNION ALL
      SELECT 2, ‘Mumbai’ UNION ALL
      SELECT 3, ‘Hyd’
      ApplicationType(apptypeid, prdcntreid, apptypname)
      SELECT 11, 1, ‘Busines’ UNION ALL
      SELECT 12, 3, ‘Engg’ UNION ALL
      SELECT 13, 1, ‘Soft’ UNION ALL
      SELECT 14, 2, ‘Science’
      MAX(CASE ApplicationType.apptypname WHEN ‘Busines’ THEN 1 ELSE 0 END) Busines,
      MAX(CASE ApplicationType.apptypname WHEN ‘Engg’ THEN 1 ELSE 0 END) Engg,
      MAX(CASE ApplicationType.apptypname WHEN ‘Soft’ THEN 1 ELSE 0 END) Soft,
      MAX(CASE ApplicationType.apptypname WHEN ‘Science’ THEN 1 ELSE 0 END) Science
      ApplicationType.prdcntreid = ProductCentre.prdcntrId
      GROUP BY

      • hi Brian
        Thanks for the reply. But the given reply didnt turn up the correct result. My Application can be in any product centre. To be more precise, a productcentre can have business/engrng/science applications.
        i have two parent tables productcentre , application types. a child table which has foreign keys of these two parent tables. This child table has records.

        prdctrid(pk) prdcntrename
        001 delhi
        002 hyd
        003 del

        AppId(pk) prdctrid(fk) AppName
        1 001 busi
        2 001 eng
        3 002 soft

        Mainid prdctrid(fk) AppId(fk) etc ….columns
        This maintable has records in this pattern
        Mainid prdctrid(fk) AppId(fk)
        0001 001 1
        0002 002 2
        0003 001 1
        0004 001 2
        0005 002 1

        So we have
        in 001 productcentre 3 applications( 2 bus 1 eng)
        in 002 prdtcentre 2 applications ( 1 eng 1 bus)

        so i want a query which shows gives result like the one in below
        prdtcntrename bus eng sci
        del 2 1 0
        hyd 1 1 0
        this is the desired result.

        please help me

        • @karthik
          Hope this will help you this is same query as Brian provide you with same concept just a little midification.

          Select b.prdcntrname,t.Busines,t.Engg,t.Science from
          select prdctrid,SUM(CASE WHEN APPID=1 then 1 else 0 end) as Busines,SUM(CASE WHEN APPID=2 then 1 else 0 end) as Engg
          ,SUM(CASE WHEN APPID=3 then 1 else 0 end) as Science from maintable group by prdctrid
          ) T join ProductCentre B on T.prdctrid=b.prdcntrId

          • @Ashish

            Thanks for reply.
            The query provided by you doesnot work. Business/eng/science/soft is not a column in applicationtype table.

            can anyone please provide the solution for my query

  136. Hi Dave,

    I want to know while working on websites, which is the best way of handling locking in sql for dml statements when we have multiple users working on the tables.


    • Hello Adarsh,

      For DML statements, locking can not be avoided. But faster and smaller transactions can help in blocking other transactions.
      Also ROWLOCK lock hint is recommonded on tables in DML statements to reduce blocking.

      Pinal Dave

  137. Hello all,
    I have one query i.e. I want to find 2nd Highest Salary person with all his details. I have answer but it gives salary only

    My answer is:

    SELECT MIN(Sal) AS Highest_Sal
    FROM (SELECT TOP (2) Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No
    FROM Employee
    ORDER BY Sal DESC) AS derivedtbl_1

    pls help me to solve the query

    • Hello Amit,

      There are many ways to get such result. Among those one is by using ROW_NUMBER() function as below:

      WITH CTE AS (SELECT TOP 2 Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No, ROW_NUMBER() OVER (ORDER BY Sal) AS RecordPos
      FROM Employee)
      SELECT Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No FROM CTE WHERE RecordPos = 2

      Pinal Dave

    • @Amit Kumar

      WITH CTE AS (SELECT ROW_NUMBER() OVER(ORDER BY ORDER BY Sal DESC) RN, Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No FROM Employee)
      SELECT Emp_No, Emp_Name, Job, Mgr, Hire_Date, Sal, Comm, Dept_No FROM CTE WHERE RN = 2;

  138. Hello Pinal,

    I have a query on CTE. I want to use conditional statement in CTE like if statement & depending upon rowcount i need to insert a row with some data init & i need to use recursion for it so using CTE. Can I use if statement in CTE if yes can u give me small example of it? Thanks.

    • Hello Krishnapratap,

      Are you facing any issue in using CASE statement in CTE query?
      I think CASE can be used in CTE same as in other t-sql queries.

      Pinal Dave

      • Hello Pinal,

        I will paste my CTE to you with result set & tell you what i want:

        Here is my CTE:

        WITH sh (Id, Name, LR, ParentId, Level)
        SELECT Id, Name, [LeftRight], [Parent Id], 0 AS Level
        FROM SelfHelpMummyCodes09thFeb2010
        WHERE Id = ‘SH2313802873′

        UNION ALL

        SELECT s.Id, s.Name, s.LeftRight, s.[Parent Id], Level + 1
        FROM SelfHelpMummyCodes09thFeb2010 s
        INNER JOIN sh
        ON sh.Id = s.[Parent Id]

        SELECT * FROM sh ORDER BY Level

        Result Set:

        SH2313802873 Sarika Amol Apte Left SH7734102870 0
        SH8599602874 Sarika Amol Apte Left SH2313802873 1
        SH3172902875 Sarika Amol Apte Right SH2313802873 1
        SH5310803026 Jayesh Suresh Vaidya Left SH3172902875 2
        SH3633903027 Sarika Amol Apte Right SH3172902875 2
        SH4547303526 Sarika Amol Apte Left SH8599602874 2
        SH8343003055 Priya Kunal Ghosh Right SH8599602874 2
        SH9978903056 Shilpa Abhay Vaidya Left SH8343003055 3
        SH9846903672 Anuya Ramesh More Left SH4547303526 3
        SH9478703528 Poonam Shashikant Date Right SH4547303526 3

        I want to find ParentId Count lesser than 2 & if it so then i want to insert or show my own row with specfic data. I am using CTE because it will save my time & do recursion for me for all child records. Thanks.

  139. sir i have a database table like given below

    here BatchID(Primary, AutoIncrement) bind in document index table which have DocumentIndexID(Primary, AutoIncrement) which again bind in AssignedDescriptionID(Primary, AutoIncrement)

    if i merge it then it prevent
    but the task is it. how can i do it.

    Batch Table

    BatchID BatchName
    ———- ————–
    116 300-B-HQ-2008
    117 200-B-HQ-2009

    DocumentIndex Table

    DocumentIndexID BatchID DestinationImageName
    ———————– ———- ——————————
    17 116 vijay kumar.tif
    18 116 sunil kumar.tif

    AssignedDescription Table

    AssignedDescriptionID DocumentIndexID DescriptionValue
    —————————- ———————– ———————
    113 17 VIJAY KUMAR
    114 17 Sunil kumar

      • actually i want to merge table so that the primary key and autoincrement done there job as usuasl

        and data show like

        Batch Table

        BatchID BatchName

        116 300-B-HQ-2008
        117 200-B-HQ-2009

        from source table that have batchid 1
        insert like below

        118 300-b-hq-2009

        DocumentIndex Table

        DocumentIndexID BatchID DestinationImageName

        17 116 vijay kumar.tif
        18 116 sunil kumar.tif

        from source table that have documentindexid 1
        insert like below

        19 118 shanker.tif

        and so on………for next table

  140. Hi,

    Is there a simple way to create a UDF that works out the number of woking days between two dates, exclusign weekends and public holidays?

    The idea being that if in a table you hve two date fields, use a UDF to create a computed field to return the number of woking days between the two.


  141. Hello Pinal,

    Hope all is well with you. I am one of your millions of followers and would like to ask your most help on how can I make my SQL 2005 DB Prod replication work successfully with my SQL 2005 Backup Server? I am also Planning to Have our BI implemented in our Backup server (separate DB).

    Thank you so much in Advance,


  142. Hi,

    I need to backup MSSQL database to txt files, and restore the database from those files – using C#.

    Please let me know if you have any idea.


  143. Hi Dave,

    I do have a question regarding defragmentation of sql-server-databasese.

    Already having a defragmented database, is it a good idea to:

    1. backup the defragmented db on another drive
    2. delete the backuped db on the fragmented drive and free up as much as additional space as possible
    3. defragment the drive with an appropriate windows defrag-tool
    4. restore the database on the defragmented drive from the backup with an initial size big enough to avoid further growing

    Especially I want to know if the restore of the database also re-creates the internal defragmentation as it was originally in the backuped database?

    If yes, there seems to be no gain.

    Any help highly appreciated.



  144. Can we install sql server 2005 in centos (linux). If not what is proper ways of using sql 2005 as we are planning to shift our entire OS to linux.

  145. hi Pinal,

    I want delete the admission records from database, but these students register numbers are in excel sheet in external path,
    so If possible to use from external excel file in WHERE Clause in SQL server. please suggest me…



  146. Hi,,

    i have query

    select * from emp where empid in (3,1,5,2,11)

    i want result should be in this order 3,1,5,2,11.

    is there any way to display in sql reports 2000.

    • Quick’n’dirty solution:

      select * from emp where empid = 3
      union all
      select * from emp where empid = 1
      union all
      select * from emp where empid = 5
      union all
      select * from emp where empid = 2
      union all
      select * from emp where empid = 11

      • or have seperate table with specific orders and join it

        select emp.* from emp
        inner join
        select 3 as empid, 1 as sorder union all
        select 1 as empid, 2 as sorder union all
        select 5 as empid, 3 as sorder union all
        select 2 as empid, 4 as sorder union all
        select 11 as empid, 5 as sorder
        ) as orders
        on emp.empid=orders.empid
        where emp.empid in (3,1,5,2,11)
        ORDER BY orders.sorder

  147. HI i didn’t found any other ugly way to achieve this.. so this will work.

    select *,
    case when empid=3 then a
    when empid=1 then b
    when empid=5 then c
    when empid=2 then d
    when empid=11 then e
    END as sortorder
    from emp where empid in (3,1,5,2,11)
    order by sortorder

  148. How to take SQL Server database back without having the permissions?
    i have the right of read,write,delete.
    but i am trying to take the back it is showing the path of the server drive.

    i have hared of various tools, but never used can u guide me through.

  149. Pinal:
    I want to prevent a row from being updated or deleted after a value has been entered into a specific column.

    IF NOT [Frozen] IS NULL
    — Don’t update or delete

    Will you please point me to a web site that will help me freeze a row based on a column value?

    thank you!

  150. hi,
    i need to write a query for selecting the record in a specific order,
    The order is to select top first from each category and then top second from each category and so on…
    i include a sample data to understand my need easier.

    The actual data is
    nId GroupId

    1 1
    2 1
    3 2
    4 2
    5 1
    6 5
    7 3
    8 4
    9 7
    10 3
    11 2
    12 5
    13 3
    14 1
    15 8
    16 1
    17 4
    18 2
    19 7

    i need the output in the following order
    nId GroupId
    1 1
    3 2
    7 3
    8 4
    6 5
    9 7
    15 8
    2 1
    4 2
    10 3
    17 4
    12 5
    19 7
    5 1
    11 2
    13 3
    14 1
    18 2
    16 1

    Suggest me some ideas to bring the desired output…
    an english statement to explain my requirement is also welcome to improve my english skill.


  151. Hi All,

    I hv got error while taking backup of Databe. Its an TFS Server Database,

    Please help me.


    TITLE: Microsoft SQL Server Management Studio

    Backup failed for Server ‘TFS’. (Microsoft.SqlServer.Smo)

    For help, click:


    System.Data.SqlClient.SqlError: The backup of full-text catalog ‘TeamFoundationServer10FullTextCatalog’ is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)

    For help, click:



    • Hello,

      The error stats that there is a full-text catalog in offline status. So first drop or convert the full text catalog to online and then take the backup.

      Pinal Dave

  152. Sir,

    I have an Stored Proc which returns 4 record sets. This SP is getting called from lot of web pages. In a new webpage, i need a modified 3rd recordset returned by the above SP do some more filtering. Can I do the following:

    Call the 1st SP in another SP and do some more joins on the 3rd recordset and return to the new webpage.

    I cannot modify this logic in 1st SP, as it includes signature changes, which will affect lot of page in my application.

    At the same time i don’t want to copy the code from 1st SP to 2nd SP, which would result in maintenance issues in future.

    Can you please suggest me a solution for this.


    • Here’s quick example how you can do this. You need to use table variable or temp table (I would suggest the former) to get results from SP1.

      create procedure SP1 as
      select 1, ‘foo’
      select 2, ‘bar’

      create procedure SP2 as
      declare @t table(id int, sometext varchar(max))
      insert into @t
      exec SP1
      select id, sometext, len(sometext) sometext_len from @t

      exec sp2

  153. Hello Sir,

    I have to write a stored Procedure, I have table in which I have a column Amount for the user. So I want that I transfer the amount from one account to another account. so that increase in other account and decerase in other account in single table.

    Plz give solution.

    Thank you

    with Regards

    Vikas Semra

  154. Below is my question.

    Lets say, I have a list of id’s ‘1,2,3,4,5,6’ . There is a table which has these id’s and there is a related names.

    So Table ‘CityName’ is like this

    int varchar
    1 Mumbai
    2 Delhi
    3 New York
    4 Paris
    5 London
    6 Tokyo

    I would want a function which will return me names of what ever list of ID’s I pass?

    Somehow I do know SQl well, but seems like I am having a problem doing this.

    so lets call the function listOfCities(listofIDs) returns list of cityNames


    • @Tanvit

      Are you sure you want a FUNCTION for this? It is very easy to query, and can be added to an existing query with a simple join.

      Please give an example of how you want to use it.

    • Where do you want to show data?
      If you use front end application, return names and do concatenation there


      declare @list varchar(100), @names varchar(1000)
      set @list='1,2,3,4,5,6'

      select @names=coalesce(@names+',','')+ names from table_name
      where ','+@list+',' like '%,'+cast(id as varchar(10))+',%'

      select @names

  155. Sir,

    I am completed my engineering and currently doing SQL server2008 BI. Now i need to know the major difference between Sql2000, SQL2005 and SQL2008. Could send me the developer details and all the developing queries and its syntax. It would help me develope my knowledge and skill on an interview point of view and other places. So please give a reply on to my request.

    Thank you

    [phone number removed]

    • @Nagarajan
      I don’t think so that anybody there will provide you all developer details or developing details, as their is so much changed from SQL Server 200 to know SQL Server 2008 R2.
      So my suggestion to you is go to books online and look their what is changed. Read this blog frequently as you will find many new things here so keep track of this blog as well as read books online and jump to what new section.

  156. Hello Sir,

    I have a user Table which contains the 129462 records,
    Fields are (UserID,UserPhone,UserCountrycode,UserCountry)

    In This Table Which users not Selected the Country then I stored the -1.both for UserCountrycode and UserCountry (217 records)

    Now I wants to update the records which contains the -1,
    I have a field UserPhone where from i will get first three characters for country code and then select the country from my this table which have the other users of this country (First 3 Charaacters are same) and then update it as desired.

    Please guide me,


    Amir Abdullah

  157. hey pinal…i would like to register for your course “query optimization and performance tuning”…can you pls tell me how do i can register it…

  158. Hello

    I want to create some kind of Job that helps me take care of capacity planning so I use following command to get all the information I need but I am not able to insert output in table, can you please help me.
    insert into DatabaseInformation
    EXEC @command1=”use [?] exec sp_spaceused”

    when I try to use above query I get following error message
    Msg 137, Level 15, State 2, Line 2
    Must declare the scalar variable “@command1″.

    please help me

  159. I want a query or function to get the hours of the day.


    1:00:00 AM

    2:00:00 AM


    12:00:00 AM

    Also is it possible that if the system datetime is set to 24 hr format then i get time values as


    24:00:00 hr

    Please advise.


  160. Hi Xaria,

    To generate a list of hours, you can write this query with CTE as:

    ;WITH cte AS(
    SELECT CAST(‘1900-01-01 01:00′ AS DATETIME) AS H
    SELECT DATEADD(hh,1, c.h) AS H
    FROM cte c
    WHERE DATEADD(hh,1, c.h) <= '1900-01-01 23:59:59.997'
    FROM cte

    Let me know if you have any question.



  161. Hi Pinal,
    I am new to Sql Server 2008, I am taking the training in sql Server 2008. Would you please suggest for certification which books for test preparation would be helpful. Which practice test(which author) will be helpful

    Any feedback would be highly appreciated.

    I am looking forward to hearing from you as per your earlier convenience.

    Narendra Garg

  162. Hi,

    Is there any system table from where we can can time taken for restoration of databases?

    From msdb..restorehistory we can get only Start time but I am interested in End time also so that It can help me in analyzing total time taken for restorations.


  163. Hi,

    Is there any system table from where we can get time taken for restoration of databases?

    From msdb..restorehistory we can get only Start time but I am interested in End time also so that It can help me in analyzing total time taken for restorations.


    • I dont think you can find it in a system tables
      One option is to look up the Error Log files
      The backup informations will be there where you can find start and ending dates with time

  164. Hi Pinal,

    We have database which is in NON -Unicode. we want to convert it into Non-Unicode so that we can support our application for multiple languages.

    What should be the best approch to convert it into Unicode.

    Is there any tool which can convert tables schema (Varchar to NVarchar)and stored procs into UNICODE.


  165. Hi sir,

    I read in an article and in that i got an info like it is be possible to drop the parent table even when a child table exists using CASCADE CONSTRAINTS…

    is it possible to do so.. if possible pls reply me with an example..

    This is the info that i read from that article..

    What is the Use of CASCADE CONSTRAINTS…?

    When this clause is used with the DROP command, a parent table can be dropped even when a child table exists..

    I am reluctant with this info..pls reply me with the same..

  166. Hello all,
    i have query i.e. i have 100 records in table i want to display records from 5 to 15 then how i can do this..???

    • @ankur do you have any criteria or basis on which you can identify records no. In between you can try like this

      select * from (
      select *,ROW_NUMBER() OVER(order by (select 1)) as RN from UrTable
      where RN>=5 and RN<=15

      if you have any criteria then just put it in place of select 1.

      Hope this will help.

  167. Hi,
    I am creating a report from a single table which have columns DealerName, CarModels, PurchaseDate
    i want create a report which should group both dealername and Models and date should be dynamic columns to display as Purchase 1, purchase 2, Purchase,,,, like least date as purchase 1, to highest date (don’t want to display the date)

    Dealer Model Purchase 1 Purchase 2 purchase 3
    Kerry Honda 5 2 10
    oldmac mazda 5 3 6

    can you please help me, or guide me how can i do this

  168. Hi,

    I am creating a state report that needs to be in a specific layout. but I am having problems in the query. BTW I am using Mssql 2005

    this is my source table:
    studentid, school, cycle, InstrSetting, ExcessHrs.

    A Student may contain different InstrSettings for a cycle. Saying that, the result I need is as follows.

    studentid, school, cycle, InstrSetting, ExcessHrs, Instrsetting2, ExcessHrs2, Instrsetting3, ExcessHrs3

    I tried to use Pivot but it won’t work (at least that is my conclusion).

    I have some data to support what I am trying to explain.

    stid schid cycle instrset exchrs
    1234 122 01 00 0.000
    1234 122 02 00 0.000
    1234 122 03 00 9.000
    1234 122 04 00 0.500
    1234 122 04 41 1.000
    1234 122 05 41 0.000

    Desired Format
    stid schid cycle instrset exchrs instrset2 exchrs2
    1234 122 01 00 0.000
    1234 122 02 00 0.000
    1234 122 03 00 9.000
    1234 122 04 00 0.500 41 1.000
    1234 122 05 41 0.000



  169. Hello Sir,
    I am Prakash Neupane from Nepal and I am studing BSc Computer Science & IT. I attained you whole presentation in Techmela
    Sir, Can I get all material that you show us in presentation so that it will be easy for me to further more understand and practice

  170. Hi Pinal,
    i dont know where did it goes wrong, here is my query, when i excute this it gave me the error

    USE [NTFRS_SkillsConsolidation_DEVT]
    SELECT RecruitId,
    [Appliance] AS ApplianceDrills,
    [BreathingApparatus] AS BreathingDrills,
    [DrivingAndOperation] AS DrivingDrills,
    [Knots] AS KnotsDrills,
    [SmallGear] AS SmallGearDrills,
    [HazardousMaterials] AS HazmatDrills,
    [CallsAttended] AS CallsAttended,
    [RoadCrashRescue] AS RoadCrashRescueDrills
    FROM (SELECT RecruitId, DrillType FROM dbo.vwDrillsSubmitted)T
    PIVOT(COUNT(DrillType) FOR DrillType IN([Appliance],[BreathingApparatus],[DrivingAndOperation],


    the error message is:
    Msg 170, Level 15, State 1, Line 11
    Line 11: Incorrect syntax near ‘PIVOT’.

    can you please advise what should i do to fix it.

  171. Hi,

    I have taken the Scheduled Backup through Jobs.
    My Question is Where the respective backups are usually

    I mean the path where backups are store..

    I have given the path while doing scheduling….

    But the Backups were not stored…. i could not found even
    single file…..

    But It shows the Job executed Suceesfully……..

  172. Hello Sir,
    I have small isssue about Installing SQL SERVER 2008.
    I Followed your step how to installed SQL SERVER 2008. Its really good it works.
    But my problem is that while installing second time after first one remove from system as well as registry its give me INSTANCE Problem i.e. it displays INSTANCE NAME “MSSQLSERVER” already used. Since Installation failed.
    I need your guidance to how solve this problem

    Thank You

    • Hi,

      Have u Deleted “Microsoft SQL Server” folder from Program Files, if not please follow the following steps,

      1) Open command Prompt
      2) Give SQL Server installation path on Command Prompt as per follows,
      C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

      4) After that run following command,
      C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe /action=uninstall


  173. Hi,

    I have a database backup of 500GB which contains 250 days of backup sets. It does not contains expiration date. I want to remove backup sets older than 180 days. How can i do it?

    • @chinthaka
      Hope this will help you

      Declare @expiration_log datetime
      SET @expiration_log = DATEADD(DD,-180,getdate())
      EXECUTE master.dbo.xp_delete_file 0,N’URPATH’,N’bak’,@expiration_log

      You can test in carefully before implement as it will delete files from your folder so test it carefully before implement.

  174. If using Maintanance plan then create a new backup everytime and delete the older backups on the basis of creation date & time or create a script to delete old backups.

    Pinal Dave

  175. Hi Pinal Dave,

    I am now facing a problem that is I am using Windows XP as a server to connect Windows Server 2003 as a database by using ODBC. But it pops me error:

    Connection failed:
    SQLState: ‘01000’
    SQL Server Error: 10060
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect()).


    Connection failed:
    SQLState: ‘08001’
    SQL Server Error: 17
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] SQL Server does not exist or access denied.

    I have had follow your comment by viewing your blog but it still cannot solve my problem. Can you please guide me?

    Thanks in advance!

  176. Hi Everybody,

    This is Rama Sai. I am joing as a fresher in a small
    They are giving a chance to prepare my self
    on Sql server 2005.
    so please give any suggestions how
    can i start sql server 2005 and if it is possiable send any
    links related to sql server 2005.

  177. Hi Pinal,

    Recently i have occurred one problem with sql query:

    e.g : suppose i have created one product.aspx page and i will get product id in string.

    e.g : for (i=0; i<proId; i++)
    string proId += i + ",";

    now i have one string like this : "1,2,3,4,6" ….

    i want to pass this parameters in sql stored procedure and use in key word of T-Sql

    e.g select * from product where pro_id in (@proIdList)
    It is possible ??

    Thanks in Advance.

    • Either use a split function and do join

      select t1.* from product as t1 inner join split_values(@ProidList) as t2 on t1. po_id =t2.po_id


      select * from product
      where ','+@ProidList+',' like '%,'+cast(po_id as varchar(10))+',%'

  178. I’m a big fan of yours and have referenced your site for 2 years. You have many great solutions for novice, intermediate and even advanced peers. Thank you!!

    If I want to add Replication Services to an already running MSSQL 10 (2008) enterprise installation, do I have to stop all services to get replication installed or can I just add the service through “add remove programs”?

    • Solved my own problem by testing the effect. Apparently the different services are actually services and may be added or removed from the server without affecting the status of the core DBMS services.

      Thanks for the opportunity to share.

  179. Hi Pinal,

    Can we identify the index is working properly which we are creating on table.
    If Yes then how to know my index is used to search the data?

    As per my knowledge it is the internal process to maintain the index by sql server. am i write?


  180. Dear All,
    I have Emp Table with following fields

    Display Manager No and salary of the lowest payed employee for that manager.Exclude any group where minimum salary is 6000 or less. sort o/p in descending order????

  181. My database size is 3.5GB. When I try to take transaction log backup, it takes too much time. Why is that?

    *Full Backup & Differential Backup does not take much time.


  182. Dear all,

    i have the two table like


    Batchid batchname
    ——— ————–
    1 b1
    2 b2
    3 b3


    indexid Batchid document
    ——— ——— ————-
    1 null b1
    2 null b1
    3 null b2
    4 null b3
    5 null b3
    6 null b2

    Result i want:—-

    indexid Batchid document
    ——— ——— ————-
    1 1 b1
    2 1 b1
    3 2 b2
    4 3 b3
    5 3 b3
    6 2 b2

    here document get the value from batchname(table1)
    so it have one or more batchname as under document(table2)

    i want to update batchid according to first table.
    how can i do this?

      • if the table2 have the value like ‘doc/b1′

        batch table


        documentIndex table

        how can i update documentIndex table for getting the result like below:

        1———– 1———–b1
        2———– 1———–b1
        3———– 2———–b2
        4———– 3———–b3
        5———– 3———–b3
        6———– 2———–b2

    • Hello Shiv,

      The desired output can be generated by following simple inner join:

      SELECT indexid, Batchid, document
      FROM Table1 INNER JOIN table2
      ON table1.batchname = table2.document

      Pinal Dave

  183. I have a failry large table which contains one image data type column. How can I find out if this table needs to have its indexes rebuilt or re orgarnized.
    Can I perform the reindexing or reorganizing any time of the day ie., the database is a live one and there are lot of DMLs that get executed on this table constantly.


  184. hi sir.. im a regular reader of your blog,,
    i have a small doubt regarding DBCC commands,,

    plz do reply

    Thank you sir.,,

  185. Hello Sir,

    I am a regular reader of your Blog. Its a really very nice place for developers. Its very very helpful for me.

    I want to execute a insert query automatic whenever i on my computer. I have a User table. and i want to insert user name and system time when the user start the computer.

    Plz reply,

    Thanks Sir

  186. Hi
    I have all my packages stored on desktop folder ,now i need to move all of them to sql server instance….


    In config file i have all my packages stored

  187. Hi friends,
    I have one more question…I need to migrate my tables ,stored procedures..from sql 2005 to sql 2008…

    Any immediate response is truly appreciated

  188. Dear Pinal Sir,

    Kindly explain difference between various versions of SQL SERVER ie Enterprise editions, developer edition etc…

  189. Hello Pinal ,

    I wanted to know is there any way to find out last modified date for a table in SQL Server 2000.


    • Hello Pinal,

      When i checked few of ur comments …found that few techniques are avilable for higher version of Sql Server 2005 or 2008 ( sys.tables ) to check last modified date for a table.

      Please let me know is ther any way we can check last modified date for a table in SQL Server 2000.


      • Hello Naina,

        There is no in built feature provided by SQL Server to know the last modified data-time. You would have to design a method as per your requirement.

        Pinal Dave

  190. hello sir am new to this blog,i have read some of your explanation for the queries impressed with your explanation.

    am facing a problem in executing following procedure
    it runs successfully “Command(s) completed successfully.”

    but when i pass values and execute then the problem raises

    ALTER PROCEDURE [dbo].[proc_name](@From INT,@To INT)
    DROP TABLE ##newTbl
    DROP TABLE ##tbl
    EXEC(‘SELECT * INTO ##newTbl FROM (SELECT * from table_name WITH (NOLOCK) where ID=15) temp’)
    SELECT * INTO ##tbl FROM ##newTbl
    ALTER TABLE ##tbl ADD sno INT Identity(1,1)
    –select * from #tbl2

    when i execute this procedure is giving return value as null and query completed with errors

  191. sir,

    this is the first time i had a visit for your will be very helpful for students.

    sir i have a u have provided server 2008 interview qs and answers

    sir can you please post server 2005 introductio,questions and answers in your blog

    i am waiting to see about server 2005 in your blog

    thank you sir

  192. Hi Dear,

    I am Najam Khan and working as a software Engg., I faced problem in PIVOT.I would like to generate Column at run when query is execute So kindly help me on this issue.

    Declare @Issuer int
    Declare @From_Dt datetime
    Declare @To_Dt datetime
    set @Issuer=141
    set @From_Dt=’2009-01-01′
    set @To_Dt=’2009-12-31′

    (SELECT lrc.Policy_No,(select comp_name from company_master where comp_id =(lrc.issuer_id))AS Comp,
    (select Data from master_data where master_id=20 and data_value =(ar.prem_Freq)) As Prem_freq, DATENAME(month, lrc.renewal_dt) AS Renewal, lrc.prem_comm_rcvd

    FROM life_renewal_comm lrc Inner Join Ar_head ar
    ON Ar.policy_no=lrc.policy_no

    WHERE lrc.renewal_dt between @From_Dt and @To_Dt
    and ar.issuer_id=@Issuer
    and lrc.Policy_No IN (select distinct(Policy_No) from life_renewal_comm )) src

    PIVOT (SUM(prem_comm_rcvd) FOR Renewal

    IN (select +'[‘+DATENAME(month, @From_Dt)+’]’+’,’+'[‘+ DATENAME(month, dateadd(m,1,@From_Dt))+’]’
    +’,’+'[‘+ DATENAME(month, dateadd(m,2,@From_Dt))+’]’+’,’+'[‘+ DATENAME(month, dateadd(m,3,@From_Dt))+’]’
    +’,’+'[‘+ DATENAME(month, dateadd(m,4,@From_Dt))+’]’+’,’+'[‘+ DATENAME(month, dateadd(m,5,@From_Dt))+’]’
    +’,’+'[‘+ DATENAME(month, dateadd(m,6,@From_Dt))+’]’+’,’+'[‘+ DATENAME(month, dateadd(m,7,@From_Dt))+’]’
    +’,’+'[‘+ DATENAME(month, dateadd(m,8,@From_Dt))+’]’+’,’+'[‘+ DATENAME(month, dateadd(m,9,@From_Dt))+’]’
    +’,’+'[‘+ DATENAME(month, dateadd(m,10,@From_Dt))+’]’+’,’+'[‘+ DATENAME(month, dateadd(m,11,@From_Dt))+’]’)) AS pvt order by Policy_No

  193. SSRS Question:
    I’ve a table in my CRM database of Image URLs. The images are stored in Sharepoint Document Library.

    I’ve a report which will load the dataset with all image URL’s and display the Images in Report. But the images are not getting displayed in SSRS reprot. If I add a image url from any of the website those images are perfectly getting displayed. But not the Images from Sharepoint Server.

    Both CRM / Database / Reprot Server / Sharepoint Server are in the same domain.

    My first thought is as Sahrepoint works on AD Users, can i do something so that the images will be displayed in REport.

    Awaiting your feedback ASAP. Thanks, Raj

  194. Hi Pinal,

    I wanted to know the sequencal steps for creating a new db user, with my own schema and grant specific rights to different users to this user.

    Thanks and regards,

  195. HI Pinal,

    Please let me know is there any tool avilable to check the SQL Server 2000 to Sql Server 2008 Migration, so that we can anlyze the procedure or any discrepancies with the Database we are trying to migrate and list all potential issues.


  196. Hi Pinal sir,
    I have a question…I need to migrate my tables ,stored procedures..from sql 2005 to sql 2008…
    If possible could provide us some doc so that it will be useful…
    Any immediate response is truly appreciated

  197. Hi

    I am using MS SQL Server 2008 on MS Windows Server 2003.

    While attempting to mirror two sql servers without a witness server, the following error kept coming through right at the end:

    The server network address “TCP://″ can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error:1418)

    I checked that both endpoints were created and on/started. I can ping both servers from the other server. I can telnet both servers on 1433 and 5022 both sides work.

    I am following these steps …..

    1. Created a new database named it as testmirror.
    3. Right clicked on the database Tasks-backup-BackupType–Full–Ok created testmirror.bak
    4. Right clicked on the database Tasks-backup-BackupType–Transactional Log–Ok created testmirror-Transaction Log backup (testmirror.trn)

    5. Copied this testmirror.bak from my system to the Mirror Server.

    6. Right click on Mirror Server Database–Restore Database –Manually wrote down the ToDatabase
    7. Selected from device and selected the added the copied backup file
    8. Performed a restore (with no recovery) — selected the relevant backup–checked the checkbox on restore

    (Did it individually for backup and transactional log)

    9. After going through security wizard When I start mirroring on principal server for testmirror DB. I am getting this error

    The server network address “TCP://″ can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error:1418)

    I have tested the network connectivity with ping and telnet. I was able to telnet on both ports 1433 and 5022 on both machines. I double checked the connectivity with netstat on other server.

    I don’t know what I am doing wrong?

    I tried following things

    1. I used local system Administrator accounts when i was connecting to the mirror server in security wizard. but no joy….

    2. I created mirroring login on both SQL Servers and gave sysadmin role to “mirroring” login …. bot no joy

    As mentioned here

    I followed

    Step 1) system Firewall should not block SQL Server port.

    Step 2) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Network Configuration
    Enable TCP/IP protocol. Make sure that SQL SERVER port is by Default 1433.

    Just to make sure follow one more step which may or may not be necessary.

    Step 3) Go to Computer Management >> Service and Application >> SQL Server 2005 Configuration >> Client Configuration
    Enable TCP/IP protocol.

    but again no joy …. still the same error – 1418

    Any help will really be appreciated ….

    Many thanks…

  198. we are coding getdate() functions for all the date columns in our application. presently our servers are in Los Angeles (US)

    we will go worldwide release . i have got some doubt .IF SOME one FROM different country post comment ON their profiles. we will order BY GETDATE coloumn .IF i am IN different timezones
    how i have TO TAKE care this one .. Actually i dont know how it will display ..IF posible can u suggest what i need TO do FOR this

  199. Hi,

    I have add Constraints (Check Constraints) to a table.
    There is no Try… Catch block or RAISEERROR message.
    If user enter invalid data, the row of data would not be inserted into the database and EXECUTENONQUERY returns -1.
    How can I have exception or error raised on this instead of returning -1 to me?

    Thank you & Good day.

  200. Hi Pinal
    I have a question ..
    Why We can not create or alter procedure,trigger inside
    if block …
    IF (1=1)
    PRINT ‘Hiii’
    Print ‘Procedure Created.’
    PRINT ‘Byyy’

    Rahul Bhargava

    • It is becuase CREATE/ALTER procedure should be the first statement in the block

      You can rewrite it as

      IF (11)
      PRINT ‘Byyy’


      Print ‘Procedure Created.’


      PRINT ‘Hiii’

  201. Hello ,
    As you described to copy the whole database and its objects using management studio [Create Script to Copy Database ], can it possible to create a query file using a store procedure ?
    as i am using one application and if i fire that store procedure with a file name and it will create a sql file at that path.

    • Yes Madhivanan i had seen your reply
      but what will happen when i need code like..
      If any procedure already in my DB then alter it otherwise create it…

  202. Hi Dave,

    I am Wilson Gunanithi. Few years back, I used to ask many questions to you and got the answers also.

    But, somebody used my name for the irritating questions. So, please ignore if the questions are irrelevant or very silly.

    You are doing very very great job. :)

    Kind Regards,
    Wilson Gunanithi.J

    • Hi Wilson,

      Thanks for coming back, do not worry at all. If you spot someone using your name, do let me know and I will take necessary actions.

      Kind Regards,

  203. Hi,

    DECLARE @var2 INT = 0
    declare @var3 varchar(100)

    set @var3=’table_’ + @var2

    create table @var3
    id int

    i want table name should be ‘table_’+ @var2.

    out put like this table_0,table_1.

  204. Hi
    We have SQL 2005 Free Addition and wish to archive excess data, are we able to do this or is there a program that we could use for this purpose?

  205. Hi Pinal,

    Can u explain in detail how we can troubleshoot issue of CPU and Memory high utilization in step by step manner with the help of Perfomance, Query and profiler. and how we can improve the utilization indirectly by query optimization as said earlier post,
    Please explain step which we have to follow troubleshoot issue.

    Thanks in advaced.

  206. Hi sir,
    Could you please give me some idea ..on how to generate script for all jobs under sql server agent,,

    Previoulsy i posted some question ,but unfortunatley i didn’t receive any reponse for them..

  207. Hello Pinal,

    I was tring to use Microft Upgrade Advisor with SQl SERVER 2008

    clicked on :-

    Launch Upgrade Advisor Analysis Wizard

    after giving Instance name and sa Authentication its not going ahead.

    Im getting below Error :-

    SQL Server version: 10.00.1600 is not supported by this release of Upgrade Advisor, only SQL2000 or SQL2005 is supported.

    Please let me knw how to resolve this


  208. Hi Dave

    I modified about a hundred stored procedure and created about a hundred .sql files on my computer and I want to update (alter the existing one) them like a bunch or through a batch file to run directly from my computer to a different server database.
    I can open the files in SQL Server Management studio and “Execute” them but I’m looking for something neater 
    Any suggestions please
    Best Regards

  209. Hi Dave,

    I need some help regarding the locking mechanism of SQL server. There’re so many chapters written already on this… but I’m not getting what I need exactly. The problem is generating of a serial number automatically. I cannot use the auto increment field option of SQL server, as in my case the serial number field depends on other parameters also.

    I used to store the last serial number in a particular table that holds other parameters too. And when in need I used to fetch the last serial number and incremented it. However, this procedure is not optimum in a network based system when more than one machine using the same remote database, as more than one user may hit the database with same query fetching the last serial number at the same time. Please note that the serial number, along with other parameters, creates a unique key for another table. So… when two or more users are reading the same serial number at a time, same unique key is generated for different records and I need to control this.

    Can you please guide me how I can lock the code from other users when one user is reading and then incrementing and updating it. Also I need to know, how to release the lock when the current user is done with the process so that others can access it. It would also be very helpful if you give me some information on how SQL server prioritizes which user from the queue should get the next response. Used platform and code bases are – .NET, VB/C#. SQL Server 2000 preferable.


    • I don’t see why you couldn’t use your approach of separate table or I missed something here. There is no way multiple queries could fetch the same serial number if you are doing the fetching and inserting inside a transaction. This is really easy with .NET/C#.

      using (SqlConnection conn = ..)
      var tran = conn.BeginTransaction();

      var fetchCmd = new SqlCommand(“SELECT Serial FROM SerialTable”, conn, tran);
      var serial = fetchCmd.ExecuteScalar();

      var updateCmd = new SqlCommand(“UPDATE SerialTable SET Serial = Serial + 1″, conn, tran);

      var insertCmd = new SqlCommand(“INSERT INTO MyTable(Serial) VALUES(@serial)”, conn, tran);
      insertCmd.Parameters.AddWithValue(“serial”, serial);



  210. Hello Dave,

    Is possible to create an Access database using SQL server 2005? I’ve been Googling a lot lately for an answer but I couldn’t find any.
    Please help me out.

    Thanks a lot in advance.


  211. Violation of PRIMARY KEY constraint ‘PK_users’. Cannot insert duplicate key in object ‘dbo.users’

    /* Make sure all Jitter JC users are up to date */
    DELETE FROM users
    where WWID in (select WWID from juice.dbo.emp where isactive = 0)

    INSERT INTO users (Username, NameGiven, NameFamily, Email, WWID, CompanyID, Mobile, IsVerified, VerifyText)
    select UPPER(RTRIM(SUBSTRING(WindowsUserName,4,100))), NameGiven, NameFamily, Email, WWID, ‘002010’, ’61’ + RTRIM(SUBSTRING(MobileNumber,2,100)), CASE WHEN MobileNumber IS NULL THEN 0 ELSE 1 END, NULL
    from juice.dbo.emp e
    where e.WWID not in (SELECT WWID FROM users) and e.Email not in (SELECT Email FROM users WHERE Email is not NULL) and e.WindowsUserName not in (SELECT Username FROM users) and e.IsActive = 1 and LEN(e.WindowsUserName) > 3 and RTRIM(SUBSTRING(e.WindowsUserName,4,100)) not in (select username from users where username is not NULL)

    UPDATE users
    SET NameGiven = e.NameGiven,
    NameFamily = e.NameFamily,
    Username = UPPER(RTRIM(SUBSTRING(e.WindowsUserName,4,100))),
    Email = e.Email,
    CompanyID = ‘002010’
    FROM juice.dbo.emp e

  212. Hi Pinal,

    I really appreciate your contribution.. guys like learn a lot from your blog. i have a situation to get report in sql 2000. this is my idea of doing? is there any other best way you can suggest? or else just help me in completing this query. here is my query

    Create table ##table1 (orderid,product,col1,col2, col3)

    select orderid, case toyid
    SUM(CASE toyid WHEN 1 THEN toyCount ELSE 0 END) AS [col1]
    SUM(CASE toyid WHEN 2 THEN toyCount ELSE 0 END) AS [col2]
    SUM(CASE toyid WHEN 3 THEN toyCount ELSE 0 END) AS [col3]
    end toyid
    from vwtoys
    –result gives 1 row insert as 1st row in ##table1

    select orderid, case groceryid
    SUM(CASE groceryid WHEN 1 THEN groceryCount ELSE 0 END) AS [col1]
    SUM(CASE groceryid WHEN 2 THEN groceryCount ELSE 0 END) AS [col2]
    SUM(CASE groceryid WHEN 3 THEN groceryCount ELSE 0 END) AS [col3]
    from vwgrocery
    –result gives 1 row insert as second row in ##table1

    select orderid, case sportitemid
    SUM(CASE sportitemid WHEN 1 THEN sportitemCount ELSE 0 END) AS [col1]
    SUM(CASE sportitemid WHEN 2 THEN sportitemCount ELSE 0 END) AS [col2]
    SUM(CASE sportitemid WHEN 3 THEN sportitemCount ELSE 0 END) AS [col3]
    from vwsportitem
    —-result gives 1 row insert as third row in ##table1

    I have 10 table’s like this….want to insert 10rows into ##table1

    insert into(orderid, ‘toys’, col1, col2, col3) —when it is first row(vwtoys)
    insert into(orderid, ‘groceries’, col1, col2, col3) —when it is second row(vwgrocery)
    insert into(orderid, ‘sport’, col1, col2, col3) —when it is third row(vwsport)

    10 rows

    how can i do this?

  213. Hi Pinal…
    My self Narsing working on Sql server 2005 and in mean while i want to attend for interviews in big companies,can u pls give me the Interview questions so that it is useful to me to get good jop.

    Thanks & Regards

  214. Hi Pinal,

    i need some help for the following query

    i have two columns and the datatype are in varchar and the values stored in the field are 67.00-per HOUR-USD,97.00-per HOUR-USD like that.

    now i have to display three coloumns in report
    1st columns= 67.00-per HOUR
    2ndcolumn= 97.00-per HOUR
    3rd column=1stcol-2ndcol=30-per Hour

    please solve this query

    • @Venkat

      ‘67.00-per HOUR’ Col1,
      ‘97.00-per HOUR’ Col2
      AS VARCHAR(5)
      ) + ‘-per HOUR’ Col3

      It is usually considered good deisgn to remove the text from the COLUMN, and making it SMALLMONEY instead.

      • You may need to use generalised approach

        '67.00-per HOUR' Col1,
        '974556.00-per HOUR' Col2

        – CAST(LEFT(Col1, 5) AS SMALLMONEY)
        AS VARCHAR(15)
        ) + '-per HOUR' as brian,

        cast(substring(col2,1,patindex('%[^0-9.]%',col2)-1) as money)-
        cast(substring(col1,1,patindex('%[^0-9.]%',col1)-1) as money)
        as varchar(10))+ '-per HOUR' as madhivanan

  215. Hi pinal,
    In my project we have sql server 2000 db and we want to do data conversion to another prebuilt application which is in sql server 2005 and we want to get rid of the sql server 2000 after the conversion.we are planning for one time
    loading from 2000 to 2005.
    Now my question is do we need to migrate 2000 db to 2005 for doing data conversion and placing data in 2005 db?

    By using ssis 2005 can we connect to 2000 database and do conversions, loading into 2005 db?

    please let me know your suggestions it will be very helpful to me.can you give me any strategy doc for this kind of projects.Thank you

    • One option is to take a backup from version 2000 and restore it version 2005. Also make use of Upgrade advisor. Note that there can be behaviral changes between the versions. Read about them too

  216. Hi Pinal,

    Let me start with saying your site is excellent source of SQL Server stuff.

    I am looking for a SQL Server Health Check software/scripts. Can you suggest any?

    Thank you

  217. i want to Query 2nd Tuesday of Current Date
    How Can i query ? Plz Help Me out ,Nex_Day Function is not support with sql management

  218. I have query like below

    declare @str_CustomerID int
    Insert into IMDECONP38.[Customer].dbo.CustomerMaster
    ( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
    values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121′

    select @str_CustomerID= scope_identity()
    after execution it returns null in my parameter

    i want to get value of identity how can i do that

    the main issue over here is “IMDECONP38″- server name that i used if i remove this i can able to get the value of identity in my parameter

  219. Hi pinal

    SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot

    in this if u dont mind can u suggest how to use this snapshot for other severs . is it posible to restore in linked server .I f posible how?

  220. Hi,

    I have been a fan of your blog ever since I started my carrer 4 years back. Is there a place where i can practice complex joins and writting dynamic queries. what i mean is so area where i can have a sample database which has certain predefined tasks where i can practice sample examples which help me write complex joins and dynamic queries

  221. I am interested in the writing queries and acheiving results. Is it possible for you to provide me tasks which I can help resolve. I have a unsual liking for writing queries.

  222. There is a need to copy table data from db1 that resides on server1 to db2 that resides on server2.

    We are using sql server 2005. Wondering what are my options. We attempted to use a job and write t-sql on server2, but unable to ‘see’ server1

    • @Tom Edwards

      Have you tried using SSIS. It has tasks to copy data.

      Ultimately, you can generate SQL scripts via the UI to copy and paste with a connection to the other server.

  223. Hi Dave,

    I am intermediate level user of SQL Server however I like to know about technology deeply.

    My query is ” What changes are required to run the SQL Server if the host name changed after SQL Server installation?”


  224. Hi Pinal

    I have SQL 2005 with a db named test. DB is in Full Recovery Mode. There are two file-groups one is Details & another is Security there are two tables respectively. One of table gets deleted wrongly. I have full backup of whole db, file-group backup in which that deleted table was exist. Now I have to recover that deleted table. Every-time when I try to restore the db with file-group backup the db goes in “RECOVERING” mode. This has happened many times with me. Can you suggest me what to do ?

  225. Hello Pinal,

    Please let me know if we found some issues with SQL SERVER 2000 while running the Upgrade Advisor, and we fix that…and Upgrade it to SQL SERVER 2008…….
    IS IT backward comaptible ???

    If we found sme issue and cant goahead ….is it possible to go back to SQL SERVER 2000

    Kindly let me know about this.


  226. Hi Pinal,
    I’m trying to setup p2p replication on sql server 2008 but in the internal network team. The two nodes have two network cards each of them so i want replication to run in a private network. Any ideas please?
    Thanks in advance.

  227. Hello Dave, I am sort of a beginner with sql and in the work that I do I only have the option of creating multiple queries in one window to populate my reporting. I was wondering instead of highlighting sections of the query at a time, is it possible to call what lines you want the query to execute next?

    Thanks Jayme

  228. Hi ,

    I develop this query to find string from database and chage it. Like in this case i have data where there is ‘NULL’ string and I want to convert it to NULL value.
    So i write down this query to automatic fetching columname and tablename from system database and perform string operation.

    query works it execute but it wont update any field.

    Any help will be really appriciated

    use project
    Declare @tablename varchar(200)
    Declare @columname varchar(200)
    Declare @find varchar(20);
    Declare @replace varchar(20);

    SET @find = ‘NULL';
    SET @replace = Null;
    Declare db2 cursor for
    SELECT name AS TableName FROM sys.tables
    open db2
    fetch next from db2 into @tablename
    —- Cursor Db1 fetching columname from system table using Tablename—–
    Declare db1 cursor for
    SELECT column_name FROM information_schema.columns
    WHERE table_name = @tablename
    open db1
    fetch next from db1 into @columname
    set @varsql = ‘update’ + @tablename +’
    where’+ @columname +’like ‘+ @find

    exec (@varsql)
    FETCH NEXT FROM db1 INTO @columname;
    CLOSE db1
    ——cursor Db1———————————-
    FETCH NEXT FROM db2 INTO @tablename ;
    CLOSE db2
    —–Cursor Db2————————–

    • @Tapan


      will add a NULL value to a string, which turns the entire string into a NULL. Instead or
      SET @find = ‘NULL';
      SET @replace = Null;


      SET @find = ”’NULL””;
      SET @replace = ‘Null';

      • hi Brian,

        I want to convert entire string in the Null value.

        For exa. if I don’t define 2nd cursor then query will give me all columname and tablename.

        use project
        Declare @tablename varchar(200)
        Declare @columname varchar(200)
        Declare @find varchar(20);
        Declare @replace varchar(20);
        DECLARE @varSQL VARCHAR(512);

        SET @find = ‘NULL';
        SET @replace = Null;
        Declare db2 cursor for
        SELECT name AS TableName FROM sys.tables
        open db2
        fetch next from db2 into @tablename
        WHILE @@FETCH_STATUS = 0
        —- Cursor Db1 fetching columname from system table using Tablename—–
        –Declare db1 cursor for
        SELECT column_name FROM information_schema.columns
        WHERE table_name = @tablename
        –open db1
        –fetch next from db1 into @columname
        —WHILE @@FETCH_STATUS = 0
        —set @varsql = ‘update’ + @tablename +’
        —where’+ @columname +’like ‘+ @find

        —exec (@varsql)
        –FETCH NEXT FROM db1 INTO @columname;
        –CLOSE db1
        —DEALLOCATE db1
        ——cursor Db1———————————-
        FETCH NEXT FROM db2 INTO @tablename ;
        CLOSE db2
        DEALLOCATE db2
        —–Cursor Db2————————–

        But when I define 2nd cursor for converting ‘NULL’ string in to a NULL value then it wont works.

        Query is executing successful but won’t perform any action on database. so without error message I cant figure it out solution.

        I tried with your suggestion but It wont work.

        • @Tapan

          The main point is the variables.

          If you were to UPDATE the value ‘NULL’ to a NULL, the statement would be:

          UPDATE table SET column = NULL WHERE column = ‘NULL';

          If this is done with variables:

          DECLARE @from_value VARCHAR(6);
          DECLARE @to_value VARCHAR(6);

          SET @from_value = ‘NULL';
          SET @to_value = NULL;

          UPDATE table SET column = @to_value WHERE column = @from_value;

          If the SQL statement itself is also a string, it needs more quotes:

          DECLARE @from_value VARCHAR(6);
          DECLARE @to_value VARCHAR(6);
          DECLARE @sql VARCHAR(75);

          SET @from_value = ”’NULL”';
          SET @to_value = ‘NULL';

          SET @sql = ‘UPDATE table SET column = ‘ + @to_value + ‘ WHERE column = ‘ + @from_value;

          select @sql;

          Or, the quotes could be in the statement:

          DECLARE @from_value VARCHAR(6);
          DECLARE @to_value VARCHAR(6);
          DECLARE @sql VARCHAR(75);

          SET @from_value = ‘NULL';
          SET @to_value = ‘NULL';

          SET @sql = ‘UPDATE table SET column = ‘ + @to_value + ‘ WHERE column = ”’ + @from_value + ””;

          select @sql;

          But in both cases @to_value must be in quotes. If it isn’t, watch what happens:

          DECLARE @from_value VARCHAR(6);
          DECLARE @to_value VARCHAR(6);
          DECLARE @sql VARCHAR(75);

          SET @from_value = ‘NULL';
          SET @to_value = NULL;

          SET @sql = ‘UPDATE table SET column = ‘ + @to_value + ‘ WHERE column = ”’ + @from_value + ””;

          select @sql;

          The whole string becomes NULL.

          Please look at this until you understand it. As NULL is treated differently than other values. Note, all the quotes i used are regular single-quotes. They may need to be edited to single-quotes if copied from here, as wordpress changes them.

  229. Please have a look on SP,

    CREATE PROCEDURE spGetDynamicResultSet

    SET @SQL=”
    DECLARE C1 CURSOR FOR SELECT DISTINCT table2.pgid,table2.FQ FROM table2 INNER JOIN table1 ON table2.pgid = table1.pgid where required = 1
    OPEN C1
    SET @SQL=’SELECT pgid,a1,a2,a3,’+@FEATURE+’ FROM table1 WHERE pgid=’+@ID
    CLOSE C1

    a1 a2 a3 f1 f2 f3 pgid
    dfs fdf df x y z 1
    fgdfg dfgfdg fdgfdg x1 y1 z1 1

    TABLE 2 is
    pgid FQ required
    1 f1 1
    1 f2 1
    1 f3 0


    pgid a1 a2 a3 f1 f2
    1 dfs fdf df x y
    1 fgdfg dfgfdg fdgfdg x1 y1

    PLease send me email back or let me know how can i check answer

    • @MUhammad Iqbal

      Here’s a quick solution:

      TABLE2.required = 1
      — Join TABLE1 for information.
      AND TABLE1.pgid = TABLE2.pgid;

  230. Sir,
    I am using it as a back end for ASP.NET.. I always get a login error which says the user is not associated with a trusted a trusted SQL SERVER connection.. How to fix this prob?.. plzz help me as early as possible as my work is getting affected.. Waiting for ur reply..
    Thanx in advance

  231. Hey Dev,

    i have a Table

    age Name Loc
    10 XXX AP
    10 YYY MP
    20 AAA UP
    20 BBB HP
    30 CCC GP
    40 DDD TY


    10 XXX AP
    10 YYY MP
    20 AAA UP
    20 BBB HP


    • @Sudarshan

      Data(age, Name, Loc)
      SELECT 10, ‘XXX’, ‘AP’ UNION ALL
      SELECT 10, ‘YYY’, ‘MP’ UNION ALL
      SELECT 20, ‘AAA’, ‘UP’ UNION ALL
      SELECT 20, ‘BBB’, ‘HP’ UNION ALL
      SELECT 30, ‘CCC’, ‘GP’ UNION ALL
      SELECT 40, ‘DDD’, ‘TY’
      Data Data_1
      Data Data_2
      Data_2.age = Data_1.age
      GROUP BY
      COUNT(*) > 1

      • or

        Data(age, Name, Loc)
        SELECT 10, 'XXX', 'AP' UNION ALL
        SELECT 10, 'YYY', 'MP' UNION ALL
        SELECT 20, 'AAA', 'UP' UNION ALL
        SELECT 20, 'BBB', 'HP' UNION ALL
        SELECT 30, 'CCC', 'GP' UNION ALL
        SELECT 40, 'DDD', 'TY'

        select t1.* from data as t1 inner join
        select age from data group by age having count(*)>1
        ) as t2 on t1.age=t2.age

  232. Hi Pinal,

    I am mahesh i am fresher learning sql,

    I have no clarity what happens in memory during creation of clustered index, whether data in the table wil be rearranged or a seperate table wil be created for the index page in order specified in the index defnition.

  233. Hi Mr. Dave,

    I want to ask about master database recovery, i knew that backup master database is very important. All i know is if you want to restore & recover master and the SQL service is stopped, then :
    1. Backup master database
    2. Open command-line and goto “c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn” and type sqlservr.exe -m
    3. open sqlcmd through command-line and then RESTORE DATABASE master FROM….

    But what if the physical file of master is corrupted or broken, how i can even restore master. Well i did litle experiment, i change file name of physical file so sql instance can’t started. And i try to do step 2, running sqlservr.exe but it failed. And it was error too when i run sqlcmd to RESTORE database.

    Please explain it, how we can recover & restore master if the file is broken?

  234. Hi All,
    Is it possible if I want to display cursor result set in each different columns instead rows for example,


    Fetch into @id,@feature
    NOw when i open this cursor its default behaviour will be

    FIRST TIME result

    Second Time

    and so on BUT I want to display above information as below

    @feature @feature
    x y
    x1 y1
    x2 y2

    Please advice if it is possible

  235. Hi Pinal,

    My requirement is for removing records that are almost duplicates based on the date field. For instance:

    Record 1:

    C1   C2     C3  C4              C5
    Str   Str    Str  Date           Str
    ABB  AMD 001 6/30/2009  'remark'

    Record 2:

    C1   C2     C3  C4              C5
    Str   Str    Str  Date           Str
    ABB  AMD 001 7/30/2009  'comment'

    (difference in date and text)

    Record 2 could also be:
    Record 2:

    C1   C2     C3  C4              C5
    Str   Str    Str  Date           Str
    ABB  AMD 001 7/30/2009  'remark'

    (difference in date only)

    I need the latest date for C1 + C2 + C3 regardless of C5, so that I get the comment dated 7/30/2009 every time. I’ve got a procedure that uses a self-join and a temp table, and most times it’s okay, but if the record with the right date is not the record with the max(ID), then it’s wrong.

              SELECT * 
              WHERE O.XTYPE IN ('U') 
                   AND O.ID = OBJECT_ID(N'TEMPDB..#TEMP2')
       INTO #TEMP2
                        FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_TYPE='BASE TABLE' 
                          AND TABLE_NAME='CASE_MILESTONE_FOR_REPORTS3') 
       FROM #TEMP2 T
                         ,MAX(ID) AS ID
               FROM #TEMP2
    		    ,MILESTONE) TMP
         AND TMP.ID = T.ID

    ( I hope that formatted properly)

    I’ve tried the max(DATE), but the fact that the entire record is not exactly the same is what throws this off. Any help whatsoever will be MUCH appreciated.

    • @Teresa

      Use ROW_NUMBER() in a CTE to number the records. For example:

      Data(C1, C2, C3, C4, C5)
      SELECT ‘ABB’, ‘AMD’, ‘001’, ‘6/30/2009′, ‘remark’ UNION ALL
      SELECT ‘ABB’, ‘AMD’, ‘001’, ‘7/30/2009′, ‘comment’ UNION ALL
      SELECT ‘ABB’, ‘AMD’, ‘001’, ‘7/30/2009′, ‘remark’
      RN = 1;

      • Thank you, Brian, I will give this a try and let you know. I had to run my procedure 3 times this morning to get the correct record where date and remark were different. *sigh*

      • Hi Brian,

        I neglected to specify that I’m using this on a SQL 2000 database. We’re upgrading to 2005 this year, but the government isn’t as good at keeping up to date as the rest of the world. :\

        • Declare @Table1 table ( C1 varchar(10), C2 varchar(10), C3 varchar(10), ProcessDate datetime, Remark varchar(10))
          insert into @Table1
          SELECT ‘AB1′, ‘AM1′, ‘001’, ‘6/30/2009′, ‘Remark’ UNION ALL
          SELECT ‘AB1′, ‘AM1′, ‘001’, ‘7/30/2009′, ‘Comment’ UNION ALL
          SELECT ‘AB2′, ‘AM2′, ‘002’, ‘7/30/2009′, ‘Remark’ UNION ALL
          SELECT ‘AB2′, ‘AM2′, ‘002’, ‘5/30/2009′, ‘Comment’

          select * from @Table1

          Select a.*
          from @Table1 A
          JOIN ( Select C1
          , C2
          , C3
          , MAX(ProcessDate) Max_Process_Date
          From @Table1
          group by C1
          , C2
          , C3
          ) B ON A.C1 = B.C1
          and A.C2 = B.C2
          and A.C3 = B.C3
          and A.processdate =B.Max_Process_Date
          order by A.C1, A.C2 , A.C3, A.ProcessDate

          Since you have mentioned that depending on C1, C2, C3 you wanted the latest date, do an inner query that has your most uniue columns, if you think, you could have duplicates dates for the same combination of C1, C2, C3 then you need to do a group by on inner query otherwise, it should be fairly simple. I am still not sure if I understand your question correctly ?

          Please let us know if this helps.

          ~ IM.

          • Thank you, Imran, this may be what I need. I did a test run against my table and tweaked it a little, and it looks good so far. I’ll know for sure when I run the report later this morning.

            Thank you so much!

  236. Hi,

    We have a script that scripts out all agent jobs from one instance. The script is DMO/VB Script. I need to load these sql files into another sql server instance excluding some jobs, and disable all these after loading. When an Operation DBA runs the command prompt/Windows script it must:
    1) Script out all agent jobs in primary instance
    2)Load these jobs, excluding few, in secondary instance
    3) Disable all the created jobs in the secondary instance

    The primary instance and secondary instances are passed by the operation DBA. Can you please help me out with this Windows script.


  237. Please advice when I call following SP through MS Access its give me error some thing like FILE IS ALREADY OPEN EXC OR YOU DONT HAVE PERMISSION TO VIEW DATA ANY IDEA WHY
    Thanks in advance.

    ALTER PROCEDURE spGetDynamicResultSet
    @TABLE_NAME varchar(100) = NULL

    SELECT @GET_TABLE = @table_name

    IF EXISTS(SELECT * FROM master..sysservers
    WHERE srvname = ‘NMDAccess’)
    EXEC sp_dropserver ‘NMDAccess’, ‘droplogins’

    EXEC sp_addlinkedserver
    ‘OLE DB Provider for Jet’,
    EXEC sp_addlinkedsrvlogin ‘NMDAccess’, ‘false’

    IF EXISTS(SELECT name FROM sysObjects WHERE name = ‘product_file’)
    DROP TABLE product_file

    SET @SQL_GET_TABLE = ‘SELECT * INTO product_file FROM NMDAccess…’+@GET_TABLE

    IF EXISTS(SELECT name FROM sysObjects WHERE name = ‘outputfile’)
    DROP TABLE outputfile

    SET @SQL=”
    –SET @SQL1=”

    FROM NMDAccess…Feature_Question AS F INNER JOIN product_file ON CAST(F.pgname AS NVARCHAR(255)) = CAST(product_file.pgname AS NVARCHAR(255)) where F.[use] = 1
    OPEN C1


    CLOSE C1
    –SELECT @sql

    IF EXISTS(SELECT * FROM master..sysservers
    WHERE srvname = ‘NMDAccess’)
    EXEC sp_dropserver ‘NMDAccess’, ‘droplogins’

  238. Hai Pinal ,

    Hope Fine . i would like to know about how to store image manually in sql server 2008 . There is a way we can store
    through Application ,can we directly store like values there a way or we can’t store in sql server ? Anyway i’ll be waiting for your answer . Plz try to post it on the blog . Thanking you very much .

    Regards ,
    Shaik .

  239. I need to delete ophan records in the sys.objects system table of Sql Server 2008.

    I have tried DAC but still error pops up “Ad hoc updates to
    system catalogs are not allowed.”

    I tried googling but cant find any appropriate answer specific for Sql Serer 2008.

    Please help.

    Thanking YOU IN ADVANCE.

  240. Hi,

    I have 2 RAID 1 groups disks (C and E). ( I have only 4 disks in my windows 2008 svr)
    Can I place my sql log files along with windows OS and SQL Server binaries in disk C and place the user sql data files in E ?
    or should I place both my sql log files and user sql data files in E and leave windows OS and SQL Server binaries in C.

    Which option will give me good performance?

    Thank you in advance,

  241. Hi Pinal,
    My sql server 2005 keeps producing sqldumpxxxx.txt log files and it is mentioned that user initiated this log. the rest of the message is some HEX codes. I couldn’t find the reason and I do not want to restart sql service which solved the same issue temporarily.
    My google searches seems not working the rasn for this issue. approx. in 6 hours my free space is going to end up due to this log file grow and I neet to watch it non stop.
    do you have any idea?

  242. Hi Pinal
    We are developing a web application. I want to have softe delete in my project. I dont want to physical delete any record from the database, and use it as an recovery tool for detching data.
    Is it fesiable to maintain a flag Isdeleted with delete timestamp in the same Table.
    Is there any other method to have such methodlogy to maintain such records without any physical deletion.

  243. hi,

    i need to write a query given input is database name,

    i need to retrieve all tables last 10% of rows from the given database .

    and also in need output in the format of

    insert into tablename(column names’) values(value);

      • hi,

        Purpose is i have a database of 80Gb now i want to create a tempdatabase with all tables and stored procedures and all entities,

        But i don’t want to copy all records from database , i want only 10 0r 5 % of the data and move the data to new temp db i don’t have any relationships between tables also.

        so no issue of relationship….also..

        • @Nagarjuna.

          Two Questions:

          1. So you need script to insert data from one database to another database for all tables. Does this means, that you managed to get a blank database with empty tables and stored procedures OR you want us to write script to create database and then create tables and stored procedure.

          2. You want latest 10% records in each table or just 10% record no matter if they are latest or old. If you want latest 10%, then is there any datetime column in every table by which I can identify where the record was inserted.

          If you just want to generate script to insert any 10% of data in any format you want, its simple. Please answer above questions, should not be hard to write SQL, Please also mention which version of SQL are you using, 2000/2005/2008.


  244. Hi,

    i can create bank database with all tables .. no issues.. but i want script to insert last 100 records or (suppose if i give 200 i should be able to retrieve last 200 records and so on )

    i don’t have date time columns in all tables and also i don’t have id as primary key in all tables (i.e in some tables name is primary key)

    sql server version 2005 & 2008


  245. hello all,
    i have ms visual studio 2008 and sql 2005 but database connectivity is working properly in windows form with ( and c#) but not with what to do….

  246. Hi Pinal,

    Recently moved to a new position where we currently have 250 SQL Servers. To register these servers in management Studio manually would be very time consuming.

    Can i register all my servers in management studio automatically through Powershell script or any other mean??

    I can get the list of SQL Server instances into a table or text file.

    Does anyone have a script that can do this?
    I Would be very grateful.

    versions are SQL Servers are 2000, 2005 and few of thems are on 2008.

    Thanks in Advance,

    A. Singh

  247. I am using execute process task inside the for each loop.
    I have passing variables values into execute process task.
    My question is that

    when execute process task does not find file at given location (actualy i have deleted file from location which execute process task looking) it’s gives sucessvalues “1” with error.

    I don’t want to see this error, so someone help me to get out from execute process task and go for next value at foreach loop.

    Actualy if you help me out to store unfound file entry into dirent location or table then that make me more happy


    • Hi Raju,

      You can use “Script Task” to check file is exists or not. If file is not exists, you can insert record in database from “Script task” too.

      If file is not exists, then you can set SSIS variable and make Conditional flow to how to proceed.


  248. Dear Pinal Dave

    I am going to work on WEB application which will use stored procedures for running any type of transactions (Simple to complex). I read the below mentioned artilce:

    And found it will be useful to me as it handles Deadlocks,Exceptions. THrough out my little career i found your blog really helpful. Can You tell me :

    1_ A better way to use stored procedures for complex transactions or do you recommend me the format used in above link.

    2_ Do I need to handle Isolation levels in transactions in sp at db level as we do in C# code if yes How and if No Why?

    Waiting for your reply .

  249. I got a SSIS package which loads the loadfiles placed in filesystem c:\Client Data\LoadFiles\ to SQL Server table TransactionData. I would like to automate the package such that as soon as client places loadfile in said folder the very next second package should be triggered for execution. I dont need anyone to explicitly execute package as file is available.
    Any approach would be appreciated

      • Madhi,
        I’m just looking for answers from Windows event front. How to catch event Windows generate as soon as file gets created / made available in file system.
        Well, for job scheduling, I believe this is a costlier in terms of maintenance overhead.
        So looking for some easy methods say FileWatcher etc.

  250. Whats wrong with ISNUMERIC() ? It still returns 1 for non-numeric characters when I execute the below code snippet :
    declare @t table ( id int, charac varchar(1), is_numeric bit)
    declare @count int, @char varchar(100)
    select @count = 1
    select @char = char(@count)
    insert into @t (id, charac,is_numeric)
    select @count, char(@count), isnumeric(char(@count))
    set @count = @count + 1
    select * from @t
    where is_numeric = 1

  251. hi,
    i need to do performace tuning but hear profiler is not allowed to use, so how to check performance in sql server.
    i new to this…
    any exports …

    advance thanks…

  252. i need to check performance tunning , but hear profiler is not allowed, what are the ways i can check ..
    help me out.
    any exports answer me plz

  253. I wanted to implement data encryption/
    decryption in sql server 2005 using Asymmetric keys. Can anyone please help to provide the steps. Column contains XML data.


    Ashish Sinha

  254. Hi Pinal Dave

    I have one table with a ID and one another table with foriegn key ID

    1 100 3 1 1 test
    1 200 3 2 1 test1
    2 210 4 3 2 testing1
    2 220 4 4 2 testing2
    3 240 5

    i want output

    1 100 test
    2 200 test1
    3 210 testing1
    4 220 testing2
    5 240 NULL


  255. Hi Pinal Dave

    I have one table with a ID and one another table with foriegn key ID

    Ist Table
    ID KHNo,KhataNo
    1 100 3
    1 200 3
    2 210 4
    2 220 4
    3 240 5

    2nd table

    1 1 test
    2 1 test1
    3 2 testing1
    4 2 testing2

    i want output

    1 100 test
    2 200 test1
    3 210 testing1
    4 220 testing2
    5 240 NULL

  256. Hi people,

    My issue is more of a SQL issue than ASP.NET.
    I am trying to store a 3digit integer value with a leading Zeros in front such as 001

    i have a company table , which auto increments the id as 001, 002, 003…and so on…
    In the company table i also have a Empoyee ..
    Which has five employee names,
    eg: aa, bb..

    So when creating the company..
    The user types in the data and select a country eg: aa,
    The Id should be I001,
    The next record if he chooses bb,,, then it will be
    A002 … A003, I004..and so on…till A999(as the last record).

    Can anyone help me how to implement this?..

    Ur help is much appreciated…
    Thanks in advance..

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s