Contact Me – Archive 1

About Pinal Dave

Pinal Dave is a common man who enjoys listening music, reading books, travelling places, watching movies and writing blogs. Pinal has over 6 years industrial experience gained through his work within the IT industry and SQL Server Community.

He is a highly respected and leading figure in Indian IT field. He is recognized through his work as founder of the highly useful SQL Server Site Microsoft has presented him SQL – MVP award for his extraordinary contribution as a SQL Server Expert.

Contact Pinal Dave

Email is always welcome, whether you’re interested in talking about projects, SQL Server 2008 and SQL Server 2005 technologies, .NET(C#), ColdFusion, Web Architecture, have taken an online tutorial class at, or have an event where you believe Pinal Dave would be a great speaker or panelist.

If you want Pinal Dave to review your product, book, website or code, please do not hesitate to contact him. Or, feel free to say “hi” anytime. All comments are welcome.


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

Child Rights and You:

If this blog has been helpful to you and if you want to help me. Please stand up for the child rights. Donate money to Child Rights and You by visiting their site directly.

pinaldave “at”
pinal “at”

1,192 thoughts on “Contact Me – Archive 1

  1. Dear Pinal,
    I used ur corsor example and it worked great thanx for that…\
    I am facing a problem I have a coulmn address and it is having house no with cutyname,state and country all in address field and I want to separte all these to differnet column as addrees, city ,state and country……..

    how can i do that……
    I have used charindex also but it only fiends the first one
    plz help……..
    Its very urgent………..

    Thanx in advance…………….


    • Hi,
      I have a doubt it is,
      I have two databases say DA1 and DA2.
      Both. DA1 and DA2 have table1 which is a clone.
      If I alter table1 from DA1 then I wish to alter table1 FROM DA2. But before altering table1 of DA2 a notification specifying what are the changes in table structure must be given. Also data from the table1 must not be lost.
      It’s very urgent please reply.


    • hiii..
      i am using sql server 2008 with asp…..when i want to connect master database, then it works ok…..
      but when i use the same connection string to connect to mydatabase,it gives me an error……
      Microsoft OLE DB Provider for SQL Server error ‘80004005’

      Cannot open database “mydb” requested by the login. The login failed.

      the connecton string used is….
      sConn= “Provider=SQLOLEDB;Integrated Security=SSPI;Trusted_Connection=yes;Initial Catalog=mydb;Connect Timeout=0;Data Source=VIVEK-PC”….

      i am using windows authentication….


    • Hi Pinal,

      I am looking to migrate the SQL 2000 applications to SQL Server 2008 and the OS will be Windows 2003 Server.
      the server where we run a lot of DTS’s. I understand that SQL 2008 requires some add-ons to be downloaded and installed to make DTS’s work.

      Can you please let me know which type of add-ons I need to download it?

      Thanks in advance,


    • Hi Pinal,
      i have one question. we have little complex report in sql server, confused about the logic, can you please help me?
      the report is like,there are 3 parameters we need.

      Todays Ticket Bookings, Previously Booked Tickets For Current Date, Today Booked Tickets as An Advance for upcoming events.

      What can be the possible logic? and we need this report monthly basis.

      With regards


    • sir i got a prob…
      This exception occur..when i insert record in a table first 10 record are insert succesfully but after when i insert new record it wil gice me exception..
      Procedure or function x has too many arguments specified. in
      what i do?????
      my sp is..
      CREATE PROCEDURE [dbo].[Proc_Corporate_Info]

      @Corporate_Product varchar(50),
      @Corporate_Description varchar(2000),
      @Corporate_Address1 varchar(200),
      @Corporate_Address2 varchar(200),
      @Corporate_Country varchar(50),
      @Corporate_City varchar(50),
      @Corporate_State varchar(50),
      @Corporate_User varchar(50),
      @Corporate_Company_Name varchar(100),
      @Corporate_Company_Email varchar(50)
      declare @Corporate_Id varchar(50)
      select @Corporate_Id=count(Corporate_Id) from onlineshop123.Corporate_Info_Table
      if @Corporate_Id =0
      select @Corporate_Id=@Corporate_Id+1
      select @Corporate_Id=max(Corporate_Id) from onlineshop123.Corporate_Info_Table
      select @Corporate_Id=@Corporate_Id+1
      insert into onlineshop123.Corporate_Info_Table (Corporate_Id,

      please help me….


    • Hi pinaldave,

      this is Ramanjaneya Reddy,
      i have one query in ssis 2005, i.e in my c:\ folder i have 10 *.bak files all bak files have same table after restore database. so i need to insert all database tables records into one table.i traied using for each loop container but this files not mdb or txt or excel files these is .bak file how can i wil do… please give some example for this query in ssis using for each loop container.. please its a urjent

      example for for each loop container with *.bak extension in ssis 2005


    • Hi Pinal,

      I Have a problem with deadlock error across 4 servers! Could you please tell me the solution how can i overcome this issue.Error logging indicated an error even though the deadlocks were properly handled. Deadlock did not occur during QA, SIT.

      Thanks & Regards
      Sandeep Kumar


    • hello i created one user name admin by server authentication but today i update that particular user password and click ok but now that user not display and nothing work happen


  2. Hi Pinal,
    Do you have have the documentation for upgrading SQL Server 2000 to 2005. I am very new in SQL Server and i got a project to migrate SQL Server 2000 to 2005. Please help me with the detail process and procedures for the migration.

    I look forward to hearing from you soon.

    Thank you


  3. Abi,

    I recently upgraded 10 SQL Server from 2000 to 2005.
    First I unattached Databases.
    Second, I did is uninstall SQL SERVER 2000 installed SQL SERVER 2005. Installation of SQL SERVER 2005 is very simple. Once you install it just reattach the databases.

    I will create detailed explanation tutorial soon and post on web.

    Kind Regards,
    Pinal Dave


    • Hi Pinal dave I am using this blog first time to ask question.We are building a windows application where there is a grid view which show table name and its primary key column.They request to give query to find primary key column in a table I tested query in AdventureWorks2012 database .In this Database there is a table with the name ‘Person.BusinessEntityAddress’ I use following query on this table It is showing there are 3 primary key columns in this table but those are not primary key columns and also there is no primary key column in this table.Can you please share me query to find primary key column in a table.

      SELECT c.Name
      FROM [sys].[index_columns] ic
      INNER JOIN [sys].[columns] c
      ON ic.[object_id] = c.[object_id]
      AND ic.[column_id] = c.[column_id]
      INNER JOIN [sys].[indexes] i
      ON i.[object_id] = ic.[object_id]
      AND i.[index_id] = ic.[index_id]
      WHERE i.is_primary_key = 1
      AND ic.[object_id] = OBJECT_ID(‘Person.BusinessEntityAddress’);


  4. Hi dave.
    yr articles r just so nice, especially for students like me.

    Is there anyway i can subscribe to yr blog without using the feeds?


  5. Pinal,
    Thank you and i look forward to receiving the upgradation docs.
    I will have bunch of questions for you if i run into any issue.

    I like your online articles …it is really great!



  6. Mr. D Pinal,

    How can we arrange meeting with you? If we pay your expenses would you travel to visit our branch in Los Angeles in weekdays? If it works out we would like to have contract with you as external expert. You must provide suggestions in writing at the end of review.

    Hope to hear from you soon,

    Margie Smith


  7. hi Pinal Dave,

    you site is excellent, i wanted a small help.
    i have just one year exp in MSSQL and currently work as a Asst. DBA, i want increase my knowledge as a Production DBA can you please tell me the list of areas where i should focus.

    Thanx with Regards,


  8. Hi,

    I have one table which contains certain rows.
    I have to display records in to datagrid.

    Now, one user is getting 10 records on one page in datagrid,
    if other user accessing tht same page, he can show all the records except those 10 records.
    i.e he cann’t read those 10 records.

    Please, tell me how we can implement using Sql Server 2005.

    Is it possible using ‘Sql Server Isolation Level’ or
    by ‘Locking Hints’ on Database,table,row,page?

    please reply asap.


  9. I would like to effectively screen candidates for various DBA roles, ranging from junior to senior. Do you have the list of interview quesitons for SQL Server broken down into what one’s each level should be able to answer?


  10. Hi Pinal,

    I’ve come to bottleneck while trying Log Shipping in SQL 2005 between two data centers, one of which is the primary and the other a secondary fail over location. I wanted to create a shared folder at the primary location, from where the secondary server can access the log backups. Right now, I have a mapped drive on the secondary server to the primary server, which can be accessed providing network credentials. However, the copy job at the secondary fails citing access issues. I didn’t find a place in the Log Shipping configuration where you can set network credentials, so how can I provide that?



  11. Hi Pinal,

    I came to know about your sql server’s intellectual through my friend and able to find you in Google.
    I hope you will be helping on my sql server problem.

    I have transactional replication happening from one server to another and both are having sql server 2000 enterprise edition with sp4 installed on them.
    One of the table say A is getting replicated to another server which is having one column having sql_varient as a datatype.
    Now the problem is the values are getting displayed fine at publisher side but we are not able to see the values in that column in subscriber.

    We are just seeing the unprintable characters and junk values.

    Help me if you have time as my reports are getting affected due to this.

    Mail me back if you need more information,

    Your reply will be highly appreciated.


  12. Hi Pinal,
    Your articles have enriched my knowledge. Can you please
    suggest different ways of exporting data from excel to sql


  13. Hi Pinal,

    I have been a frequent visitor of your Site, and must say it is quite informative and helpful !! Hearty Thanks.

    I was wondering if you could also let us know something about the “Row Overflow Data” Feature in SQL 2005.

    This is related to the Max Row Size in SQL 2005.

    Deepan S.


  14. Hi Pinal,

    I have a problem with an Oracle database, may be you could help as well:
    The database is running on a database server, accessed by an application server. From time to time, we have long response times for a single select on a small table (10 records), which takes about up to 40 seconds. At the same time, a deadlock on other processes is detected by Oracle and resolved. The delayed select does not have any relation to the deadlock tables, but it comes back at the same timestamp, when the deadlock is solved.

    Any idea for a desperate guy?




  15. Hi Pinal,

    I’m really impressed by the knowledge sharing by you.

    Can you help me in dobut.

    I need to delete records from two tables, Table1 contains the primary key and Table2 contains PK and FK of table1.

    I want to delete a records from Table1 and 2 without affecting the constraint by using a single Delete Query.


    Nakkeeran Rengasamy


    • HI Pinal ,

      There was a problem in my sql server 2008 the sql server agent automatically restarted The memory consumption at that time was 94 %
      The error was : ex_handle_except encountered exception C0000005 – Server terminating
      and the
      * Short Stack Dump
      77E4BEF7 Module(kernel32+0000BEF7)
      026F8EB4 Module(sqlservr+016F8EB4)
      026FC9E9 Module(sqlservr+016FC9E9)
      026FC585 Module(sqlservr+016FC585)
      02A12121 Module(sqlservr+01A12121)
      77E761B7 Module(kernel32+000361B7)
      77BC3E74 Module(msvcrt+00023E74)
      77BCB547 Module(msvcrt+0002B547)
      77E6482F Module(kernel32+0002482F)

      Kindly help my email id is [email removed]


  16. Hi Pinal

    i have a varchar field in a table that is used to store a variety of values (including dates, text, nulls etc).

    I need to find all dates (these are currently stored in the format dd/mm/yyyy) and convert them to yyyy-mm-dd format. These values need to remain in this varchar field (rather than a datetime field).

    Any help would be greatly appreciated!



  17. hi pinal,

    i want to depict tempdb size every hour …

    which table can have actual size of tempdb size ….

    other than sp_helpdb,sp_spaceused,sysfiles….

    ur given column shud say actual occuppied size, freee space…


  18. Hi Pinal,

    I am in beginner stage with the concept of stored procedure.

    So I need the proper and simplest guidance to go through the matters of Stored Procedure.

    Plz send me the URL to get the proper guidance from you.. ( URL from your branch )

    I thank you in advance …


    Wilson Gunaniithi . J


  19. Hi Pin (pet name),

    I ve sent to you my doubts on stored procedure and some other things..

    But I didnt get any reply for them.

    Please consider my doubts and reply immediately..

    Regards ,

    Wilson Gunanithi . J


  20. Hello Wilson,

    I try my best to reply emails and comments. It may take upto 48 hours for me to answer questions, due to high volume of emails and comments I receive.

    Regarding basic tutorial about stored procedures, you should as that is the best tutorial ever for beginner. Once you have learned basic you can go to Search Authority and search for Stored Procedure to learn advance tricks and tips.

    Kind Regards,


    • Hello,

      I have a que regarding SSRS report with List control and footer if anybody can provide information how to solved it.

      I am having report with list control and inside list control i am having sub reports. i am trying to put footer for each based based on one value in list control. I have tried using report item text box but with that what is happening is only on first page of each list i am seeing footer and its not repeating if list has many page.

      Can anybody tell me how i can solve this?


  21. I want use more than 8000 characto string as input parameter in my stored procedure.It come frm the .cs file.
    and Ii want to manuplate that string.And i hv use text fro that as varchar wont supprot more that 8k of charctor.But problem is that u cant assign value to text feild acccept input parameter.Canu pls suggest wat shuold i do??/
    Is there any other son for that..
    Pls help Thank you.



  22. Hi Pinal,

    How to migrate the data from sql server 2000 to sql server 2005. Please send me the details about this.

    Thanks in advance.


    Prakash S


  23. GID GName Status
    1 Compliance Pending For Approval
    1 Compliance Rejected
    1 Compliance Approved
    2 Risk Management Pending For Approval
    3 Legal Pending For Approval

    I may not be able to explain the exact issue but hope you will understand the scenario.

    Due to execution of certain SQL statements I receive above table in a SP.

    Now the final result that I am looking for is
    GID GName Status
    1 Compliance Pending For Approval
    2 Risk Management Pending For Approval
    3 Legal Pending For Approval

    If there is any multiple record for any GID I want the least priority( Status) record

    Priority is
    0: Pending For Approval

    If there is any 0 Priority record then it should show ‘Pending for approval
    If there is no record with priority 0 then it should look for priority 1 if found ‘Rejected’ will be displayed else
    Approved (Priority 2)


    • You should have a status table with data defined
      Your original table should store statusid instead of name
      Then you can use

      select GID ,GName ,min(Status) as Staus from table
      group by GID , GName

      otherwise see how complicate it is

      declare @t table(GID int,GName varchar(100),Status varchar(100))
      insert into @t
      select 1, 'Compliance', 'Pending For Approval' union all
      select 1, 'Compliance', 'Rejected' union all
      select 1, 'Compliance', 'Approved' union all
      select 2, 'Risk Management', 'Pending For Approval' union all
      select 3, 'Legal','Pending For Approval'
      select t1.GID,t1.GName,t2.status from
      	select GID,GName,min(statusid) as statusid from
      	select GID ,GName ,case when Status='Pending For Approval' then 0 when status='Rejected' then 1 when status='Approved' then 2 end as statusid,status from @t
      	) as t1
      	group by GID,GName
      ) as t1
      inner join
      	select 2 as statusid,'Approved' as status
      	union all
      	select 1,'Rejected'
      	union all
      	select 0,'Pending For Approval'
      ) as t2
      on t1.statusid=t2.statusid
      order by GID


  24. Hi Pin ,

    How to find the number of columns in the table by query…

    And how to find the total number of user tables in the database by query…

    I worked on this situation in the Oracle Database.,but I have no idea about this in SQL server 2005..

    Reply me with the query.. Ok ..bye


    Wilson Gunanithi . J


    • number of columns in a table

      select count(name)
      from sys.columns
      where object_id = object_id(‘table_name’)


      select max_column_id_used from sys.tables
      where name = ‘table_name’

      number of tables
      select count(*) from sys.tables where type = ‘U’


  25. Hi Pin ,
    I m working in Software Company as DB Developer…
    By mistake I ve done a mistake in a real database.. So I ve to restore within two days …
    I have a backup..But I dont know how to restore…
    Send me the way to restore the backup by query or anything ..
    Kind regards,
    Wilson Gunanithi . J


  26. Hi Pin ,
    Thank you for your quick consideration about my doubt among your fasttrack life.
    I tried your restore script as you gave.
    But I received one error as follows:
    Server: Msg 3234, Level 16, State 2, Line 1
    Logical file ‘D:\Program Files\Microsoft SQL Server\MSSQL\data\wiltest.mdf’ is not part of database ‘wiltest’. Use RESTORE FILELISTONLY to list the logical file names.
    Server: Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    But I checked so many times the ‘mdf’ file name. Definitely it is correct.
    Then how to avoid the error to complete my job.
    Please refer this doubt quickly .. I am awaiting for your reply.
    Kind regards ,
    Wilson Gunanithi . J


  27. Hi Pin,

    One month ago, I was asked by the interviewer that ‘ What is the purpose of WHEN clause in SQL Server 2005 ‘?

    I have been used WHERE clause in my script so many times as it is a core clause of SQL, but I never heard about the WHEN clause..

    Is there WHEN clause in SQL Server ? If yes , reply me that what is the purpose of that clause ? Where we’ve to use? and also send me what are the clauses there in SQL server ?

    If no , Send me what are the clauses there in SQL server ? …

    I am awaiting for your wonderful reply..




  28. Hi Pin,

    I have been come again to ask you one doubt.

    Send me the script for migrating the database from lower version to higher version using SQL script.


    Wilson . J


  29. Wilson,
    You should search in my blog search first before your ask here as that is faster way to get your questions answered. I have answered the latest question in two parts already in my blog.
    I enjoy helping everybody though, you should do your part by searching in my blog before asking question – this will save your lots of time.
    Search for “Database Compatible” at Search SQLAuthority
    Kind Regards,


  30. I have a table that has an Identity field and primary key and a description field of what the record contains. I also have 6 other columns that have data that can be redundant.
    I want to group the data by the redundant data to get a count of the 6 fields that repeat. I can do this easily.
    However, I can’t seem to figure out how to also display the key and description field for the first record found in a group by query.


  31. Hi Pinal,

    I need to disable a trigger for a particular update statement in a stored procedure then i need to enable it…

    Is it possible in MS SQL ?
    If yes, HOw? I tried adding diable trigger before Update, but SQL shows error



  32. Hi,
    I went thor’ the URL.
    I need to do in the Stored procedure, before and after an Update… some thing like this…

    Create proc a


    Disable trigger trigger1 on tbl1
    update tbl1 set col1 = 3
    enable trigger trigger1 on tbl1

    is it possible…


  33. Hi Pinal,
    I just wanted to know what conflicts can happen during restoration if backup of SQL databases is happening by both veritas netback up and SQL command backup(Full backup ,diff backup and transactional log backup).
    how can I schedule them.
    Should I Continue with one..
    I m confused.
    please suggest


  34. Hi Vaibhav,

    I always use SQL Server backup methods. It is online operation and happens in background. I have been successfully able to restore that database always.

    I have not used Veritas backup. I trust SQL Server backups and their restoration.

    Pinal Dave (


  35. hi pinal,
    I have gone through your some articles their really very helpfull to me as I’m new to SQL, I’m using sql server 2000 & sql 2005 express in a project.In our application we providing backup & restore facility, for the same I used sql query to take a back it creates “.bak” file but when I’m tring to restore same bak file it show following error :

    my code is as follows

    Restore Database TestDB
    from Disk=’c:\pay.bak’
    with move ‘payroll’ to ‘d:\program files\microsoft sql server\mssql\data\TestDB.mdf’,
    move ‘payroll_log’ to ‘d:\program files\microsoft sql server\mssql\data\TestDB.ldf’

    logical file ‘payroll’ is not part of database ‘TestDB’, Use RESTORE FILELISTONLY to list the logical file names.


  36. Hi Pin ,

    I have received the information about the backup devices in SQL Server 2005 that there are three type of devices as :

    Disk / Tape / Pipe — in this, I have some idea about first two.But I dont have any idea about the ‘Pipe’. So reply for the same.

    Sorry for the frequent disturbance.

    I am awaiting for your reply.


    Wilson Gunanithi . J


  37. Hi pinal

    My bakup job failed with below error on sql 2005

    SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online. [SQLSTATE 42000] (Error 15281). The step failed.

    Pls give resolution



  38. Thanks a lot Pinal for your Quik response my issue was resoled , always ur hints vl helping me a lot . Once again Thank you very much .


  39. Great site!

    I have a user DB where the sysobjects and sysindexes tables are either no longer present or recognized. CHECKTABLE can find neither.

    Is there a way to have SQL Server (2000) rebuild these by inspecting the contents of the MDF?

    Thank you.


  40. Hi Pinal,

    i want to attach mssql 2000 mdf file to mssql 2005 and upgrade it , please tell me the way to do it.
    i have tried it but i am facing some issues.



  41. I have two tables say TableA and TableB. TableA has column ColumnA and TableB has column ColumnB. ColumnB is a unique column. ColumnA is receiving values from the user. I want to write a query which can update the values of ColumnB after reading values from ColumnA. Do I have to check each value in ColumnA to make sure that it does not exist in ColumnB and then issue Update Command else issue insert command. ColumnA can have duplicate values

    What will be the best option?
    Both tables can be accessed by multiple users. So I have to make sure that while selecting value from ColumnA by one user, the value in ColumnB is not being updated


  42. Hi Pinal,
    I am getting following two errors.

    1)Repository engine V3.0 or higher is required to run meta data browser.

    2)REPODBC.dll is missing

    Both of these errors are on different servers.
    Please suggest…


  43. Hi Pinal,
    I want to transfer tables from porodox to sqlserver , I have file with *.db format,but during Import wizard ,sql send me this error message

    Error Source:Microsoft JET Database Engine
    Error Description:External table is not in the expected format
    Contex:Error calling open rowset on the provider

    What should I do?
    Please suggest



  44. Hi Dave,

    How to change the row value into the column name..?


    Col1 Col2 Col3
    Img1 10 20
    Img2 20 40

    I need ,

    Col2 Col3 Img1 Img2
    10 20 xxx yyy
    20 40 yyy xxx

    Is this possible..? If yes, Please tell the way..

    I ve already searched in ” ”

    But , I didnt get anything based on this..

    So , help me .. this is the task from our company client..


  45. Hi Dave,

    Can we move the attribute values from another attribute?




    In which ‘always’ wilson won’t have mark for any exam..

    So I need to move the mark from “Siva” or “Pinal” to “Wilson”.

    Move means not like copy – paste.., its like cut – paste…

    Is this possible? If yes, Plz tell the way by words or query..

    This is also the request from our company client…

    So Plz respond me immediately.. Please….


  46. Hello Wilson,

    This is totally possible and very easy. If your client needs to advise, I can provide contact information of technical professionals, who should be able to help with this small matters timely.

    Kind Regards,
    Pinal Dave (


  47. Hi Pinal,

    I have recently joined your blog and have found some very intresting articles.

    Well i have a problem for which i think you are the right person to be contacted.

    The situation is that i have Meta DB architecture in which i have various content databases for each dealer(client) and a single MetaDB containg the meta info of all the content databases(like: Server Name, Server IP, Connection string etc.

    Now, the concern over here is of maintenance. Whenever i had to make a production release and definately the release would contain various db object changes to be released. To tell you that the pace with which the content db are created in my application is quiet high. Currently my application supports 71 content db’s. now here lies the problem of releasing the db changes on 71 individual databases.

    I have analysed some solutions like:

    1. Create a publisher subscriber policy.
    2. Created a small application or a script which will loop through the list of dealers from the Meta DB and execute my script files through “osql” command.

    The publisher subscriber is not completely feasible for me since i want few customizations like: i may release my scripts on few content db’s instead of all and like other customizations which are i think quiet difficult to configure in this case.

    So, currently i am using the second option.

    Please, guide in case you have some better solution.



  48. Hello Dave ,

    I received your wonderful reply.. You ve told that I ll give some contact info of professionals to my clients..

    But I need their contact …

    Plz send their contact info to me….



  49. Wilson,

    Please send me details of project as well budget and I will forward the email to my contacts. They will get in touch with you for further project discussion.

    Please make sure that you have properly defined your project proposal with need of developers.



  50. Hi Pinal,
    I want to transfer tables from porodox to sqlserver , I have file with *.db format,but during Import wizard ,sql send me this error message

    Error Source:Microsoft JET Database Engine
    Error Description:External table is not in the expected format
    Contex:Error calling open rowset on the provider

    Please suggest



  51. Hi Pinal,
    I got a new project in setting up Clustering: Active/ Active clustering in SQL server 2005.
    I read lot of stuff and i did get some knowledge in clustering. But i am new in this but i would like to take it as challange. So if you have time could you tell me how do i start from the scratch to set up clustering in SQL server 2005 assuming that Windows Clustering has already been set up.
    I know that there will be virtual server to which application will connect for accessing the database. I do have understanding about it but still if you could give me start up point. For example, when i go to the company first day how should i start from the scratch. Also let me know how to setup quorum disk.
    I would appreciate if any one could give me the startup idea from the scratch.
    Thank you in advance.


  52. Hi Pinale,

    I Need a SQL Query that can answer my question.

    Here is my Question. For eg- there is one table called PRODUCT. This is how the data is loaded.

    John Nuts
    John Bolts
    Peter Nuts
    Peter Bolts
    David Nuts
    David Bolts
    David Screws
    Mike Nuts
    Sue Bolts

    Now what i want is the query that will give me the names of people who sell whatever John sells, without passing goods criteria. Query should return Peter and David. But not Mike and Sue because they only sell Nuts and Bolts respectively. Furthermore, if John starts selling Screws in the future the query should fetch only David.


    • check this out :
      create table #temp (name varchar(100), prod varchar(100))
      insert into #temp
      select ‘john’, ‘bolts’ union all
      select ‘john’, ‘nuts’ union all
      select ‘David’, ‘nuts’ union all
      select ‘David’, ‘screws’ union all
      select ‘David’, ‘bolts’ union all
      select ‘Sue’, ‘bolts’ union all
      select ‘kate’, ‘screws’ union all
      select ‘Mike’, ‘bolts’ union all
      select ‘peter’,’nuts’ union all
      select ‘peter’, ‘bolts’

      select from #temp t1
      inner join #temp t2
      on =
      where ‘john’
      and = ‘john’
      group by
      having count(1) > = (select count(1) from #temp
      where name = ‘john’)


  53. Pinal,

    Is there any knowledge out there in the ether about SQL2005, Vista, Coldfusion and IIS

    I have an app I’m working on which I need to load on to a new laptop running Vista. But no ammount of fiddling with settings etc can make the three things work together.


  54. Mr. Dave –

    I was wondering if you could assist me in the answers to two questions:

    First, I work with the federal government with MS SQL 2000 and 2005 at the current time. We are very tight with our funding this year and as such are very limited in our spending for good DBA database monitoring tools. Do you happen to know of any OPEN source monitoring toosl (i.e. equivelent to SQL Sentry and the like) as well as performance tuning/monitoring tools out there for these two MSSQL engines?

    Second, do you have or could you point me in the direction of a Stored Procedure Organizational Chart seperating all the stored procedures contained in the Master DB grouped by some type of logical arrangement?

    Thanks again!


  55. Hi Pinal,
    I work for a company which is developing a web application in cold fusion. We are exploring the possibility of using SQL 2005 reporting solutions with Cold fusion. I know that Adobe has a reporting solution with cold fusion, but am not sure we want to use that. I also don’t like crystal reports because of the cap on their concurrent user access licenses of 5 per software license.

    Have you had any experience using SQL reporting solutions with cold fusion? Any major “Gotch –ya’s “ I need to think about when doing this?

    I’m also thinking to wait till SQL 2008 comes out, before I purchase SQL 2005, in anticipation that the price for 2005 will drop dramatically when 2008 comes out….

    Any thoughts on the benefits of other reporting solutions?



  56. Pinal,

    Thanks for your advise. Tom, Seth and Mya says hi as well. It was good to meet you in person and hopefully you will have time to help us in final phase of the design approval.

    Please give us call.



  57. Hello Pinal ,
    I m in desperate need of help.

    The Project is half way through and we r in a rollout stage.

    There is a major concern of Performance as the client has SQL Server 2005 standard edition.

    Please provide some help or some test that has to be perfomed on th databse to check whether the DB tuning is perfect..

    Urgent Help required.

    Thanks & Regards
    Pritam Shetty


  58. Hi Pinal,
    I need help to resolve follwoing problem:
    There are three SQL Server instances , Instance1, Instance2 and Instance3, in a single box(server). My moderator asked me to give access to only Instance1 to my co-worker.
    How can i give the access to only a single Instance ( Instance1 ) in a server. How do i do this..I really need help for this, i am not sure how to do it . I look forward to hearing from you soon. Also if you get time, i am hoping to get help in clustering as well.

    Thank you.


  59. Abi,

    Every SQL Server instance has its own permissions.
    If you go to your server instance >> Security >> Login >> here you can create your own instance.

    Kind Regards,
    Pinal Dave (


  60. Pinal,
    I guess my question is not clear or i have limitted understanding. I need to create logins for a single instance to access by a co-worker. How do i do this. I hope this helps .


  61. Hello Pinal ,
    I m in desperate need of help.

    The Project is half way through and we r in a rollout stage.

    There is a major concern of Performance as the client has SQL Server 2005 standard edition.

    Please provide some help or some test that has to be perfomed on th databse to check whether the DB tuning is perfect..

    Urgent Help required Please.

    Thanks & Regards
    Pritam Shetty


  62. Hi Pinal,
    Could you please help me with advice and recommendations on creating a large database? I have a table where there is stored data for the last several years (all in all, billions of records).
    Should I divide this table into several tables (for example, each year will have a corresponding table)? What type of the field is it more preferable to use as a Primary Key?
    Thank you.


  63. Hi Pinal,
    Size of my database file(.mdf) is 35 GB.
    I have an application for that database which has the facility to purge data of database before a specified date.
    When I run that,application monitor displays the size of database less than what it was before purging.
    But the size of my .mdf file remains same.
    even when i shrink the database, there is no change in the

    Please Suggest


  64. Hi Pinal.

    I started workign on SQL Server 2005, and I notice that lot of DML triggers are automatically created for tables. How can I disable these triggers on one go, or delete the triggers in one go.

    Please help


  65. Thank you again for your help. I am glad that you helped us out.
    Looking at your skills and in depth knowledge our manager has asked if you are interested in job, we want to offer you one.
    Please let me know. Also please put my name in reference, this will also good on my cv.


  66. Hello Dave,

    I have a problem with the DTS conversion (Excel to SQL)
    In Excel sheet I ve data on particular cell.
    But after the conversion of Excel into SQL by DTS, SQL contains NULL for that particular cell.
    In Excel ,——-Name———-Rank

    After the conversion, In SQL ,——-Name———-Rank

    Why this happens? How to avoid this.? I ve searched in… But I didnt get anything to solve my problem… So tell me the way…


  67. Hello Dave,


    I m not creating the table via SQL..

    In DTS, table will be created automatically..

    Then how can I set the column’s datatype?


  68. Hi guys, I have a problem with error: 40.

    I made a Web page, and it works perfect on my local machine, everything is OK, until I uloaded page to the server, it reports this error:


    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. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    I dont’t Know what to do. Please HELP!



  69. Hi,

    I want to know if its possible to copy a database in SQL Server 2005, without its data. (copy only the structure).If yes, how can we do it using the ‘copy’ wizard that SQL Server 2005 supports?

    Please give me some advise as i’m new to SQL server and need this information desparately.



  70. hi,

    i am astudent of the 5th dem MCA from ahmedabad,

    i have encountered the follewing problem in my project….
    i need Ur help…

    i want to insert the records in the SQLServer through the Visual Studio .Net 2005….
    Using the stored procedure…..

    the whole purpose is to tacle the concurrency issues.

    hope U will help……

    if U have the code for the same send it…..
    plaese its very much needed and urgent….



  71. Hi Pinal;
    Have you ever seen the physical files names as dbname_1.mdf., dbname2_mdf….and so on after restoring a large database via Enterprise manager in sql 2000? The properties states user defined file groups and logical file names as previous, but the physical file names are different.

    I have restored hundreds of small databases before, but never seen this. The only logic I could figure is that perhaps the sql engine will first leave the physical filename as 1,2…mdf, then rename them later. But, when the Enterprise manage states, the restore has completed in a messagebox, I would imagine, it has already renamed the files.

    Your explanation, help is greatly appreciated.




  72. Can you please tell me what I wrong with this code?

    declare @account char

    DECLARE Accounts_Cursor CURSOR FOR
    SELECT account_number FROM accounts
    OPEN accounts_Cursor
    FETCH NEXT FROM accounts_Cursor into @account
    update results
    set account_number = @account
    FETCH NEXT FROM accounts_Cursor into @account

    CLOSE accounts_Cursor
    DEALLOCATE accounts_Cursor

    I just need to get one field from the accounts table and put it in one field in the results table


  73. Hi Pinal,
    In post number 21 Nakkeeran has asked,
    “I need to delete records from two tables, Table1 contains the primary key and Table2 contains PK and FK of table1.

    I want to delete a records from Table1 and 2 without affecting the constraint by using a single Delete Query.”

    Please correct me if i am wrong.

    In the table properties if we select “Enforce relationship for Inserts and updates” and select “cascade Delect related records”

    And if we delete the records in one table the related records with the FK relationship in the second table gets deleted.



  74. Hi Dave,

    I have the data in .txt format. I need to bring them into SQL server.

    Can we import the data from notepad to sql server?

    If yes , tell the way.

    If no , tell alternative way.



  75. Hello Dave,

    Thank you for wonderful reply..

    I ‘ve tried via SSIS to import the data from .txt to SQL., but the truncation error occured..
    How can I solve this?

    Please send the way ….



  76. Hey Pinalkumar Dave,

    I am log shipping two databases. I have no problem with one, but the other is another story.

    The restore part of the process stops working after a while. I notice job messages that state that certain transactional files are too recent. It skips those files and the job fails.

    Any ideas?


  77. Hi Dave,

    I have the data in .txt format. I need to bring them into SQL server.

    In .txt has many no of columns.

    How many columns are accepted in Sql server?.

    Please answer my question…



  78. hi there,
    I hope you have done that before, it is very simple job i am trying to get done.
    I want to use dts package to convert table into text format and then want to sent to ftp site.

    Ftp component only give a facility to download but not upload. Do you have any script handy.



  79. Hello Dave,

    I ve visited so many times ur website.

    There is much difference between earlier stage and now…

    In earlier stage, you will answer properly for all user’s question…

    But,, now even you dont care about so many user’s questions…as user get increased…

    Why.? Plz reply especially for this question.. Dont ignore this..

    This is the ? on so many users heart…….



  80. Dear Varun,

    Thanks for your comment. Users have definitely increased in recent time. I have been receiving more than 200 valid comments in a day. I used to hold each comment and answer them when I have time.

    However, I realize that many questions are time sensitive and need answered quickly. Many smart readers visits my blog and read the questions and they help out my other reader’s questions. I am very pleased that while I answer a few questions, other smart readers and developers are helping my readers.

    I see many of the questions getting answers from comments from my readers. This nice little community is growing with the help of good readers and developers. If I see any question not getting answered, I will jump right on it and answer myself or contact my other reader who are enthusiastic to answer questions.

    I have created custom search for my readers so they can find answers faster for the question already answered. I direct users to search while I focus on new questions which needs attention. I take my readers very seriously and this blog is extremely important platform for SQL community.

    Pinal Dave (


  81. Pinal,

    Your interview question helped me to get job. Your site has helped me to get job immediately after my company closed one morning. I never felt so bad in my whole life when I lost job. Bangalore companies are no more safe.

    May God give you long life.


  82. Hi Pinal,

    The content on your site is very useful. Keep up the good work.

    I have a query regarding Triggers.

    Suppose my DML query updates 10 rows of a table, how many times trigger is fired?

    Trigger type can be either “after” or “instead of” in the above case.

    Kindly suggest answer for this.

    Thanks and regards,

    PRAShant Patil.


  83. HI Pinal

    I have gone thru the site, its very informative. I know you have successfully upgraded to 2005 without interupting any service.

    Here we recently bought new server for SQL 2005 having 4 cpu. We were using sql 2000 till now.

    What necessary steps we need to follow to put all databases in 2005?

    Also I have to setup a standby sever. can you tell how it can be done ?

    how to transfer data from standby server to the production server once it is online?

    Thanks &



  84. Hi Pinal,
    I have a FTP server in which files are loaded everyday, these files are then pulled down, unzip and through a SQL Script the data in these files is populated in SQL Server. Now, I want to automate this process and eliminate all the manual work…….
    the ftp path is as following:
    ftp://ftp.example…./documents/ This path contains 6 to 8 folders. each of which contain have around 30files or more and in the Modified column there is the date and time stamp.

    Now I need to write a script that will automatically connect to the ftp server above go to the document folder then go in each folder and compare the Modified date with today’s date from system. If the date mataches then go ahead and pull that file on the local machine may be in c:/data unzip it and then run the SQL SCRIPT to populate the data…….

    Any help in this is really appreciatd. This is really important and urgent to me so please if you have any way out email me soon.

    Thanks In Advance.


  85. Hi

    May be u can help me

    I have following errors every day

    High Stolen Page 28254 Threshold is 5
    High Page Reads Per Second 43.1910 Threshold is 30
    High Read Ahead Page Per Seond 669.0730 Threshold is 30

    Current environment is SQL Server 2005 Standard Edition with SP1 running on Windows 2003. 4 Processor with 4GB Memory – dedicated SQL Server

    Any help will be highly appreciated.

    Many Thanks


  86. HI Pinal,

    I have a querry , i need a querry or script which helps in inserting all the data from one table to another table
    and the both tables have same fields

    Eagerly awaiting your reply


  87. Hi Seema,

    Answer to your FTP question.
    Yes, we have been doing something similar for our import data process. We have ColdFusion script which logs in the FTP account, retrieves the data to correct place and uses BULK INSERT to insert the data in the SQL Server. We are planning to re-write that in .NET in near future.

    I am not sure if we can get time stamp from the modified column using SQL Script. I might have not understood your questions.

    Pinal Dave (


  88. HI Dave ,

    I have been going through youir site since last couple of days .Inface i m searching for some help regarding sqal server installation .

    I am very new to this database side and now my immediate job responsibility is to install sql server 2005 along with sql server 2000 in same windows server( i heard sql server 2005 is capable of coexisting with sql server 2000).can u suggest me how to do that

    Thanks in advance


  89. Hi Srikanth,

    you can absolutely have two SQL Server 2000 and SQL Server 2005 on one machine. However, you can not have both of them as same default instance, you will have to save one as default and another as named instance or both of them as named instances.

    Pinal Dave (


  90. Hi pinal,
    I’m trying to run .sql script for ftp nad everytime it gives me the same error…….
    ***Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
    SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online. ***

    I tried to change the settings in “SQL Server Surface Area Configuration” but every time I enter any other computer name which I know is on network. It gives another error:
    ***Computer does not exist on network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again. ***

    I don’t understand this error…
    Any suggestion or help…


  91. Hi Seema,

    It is difficult to provide suggestion from here in this case however, you can do following.

    You may want to go login as local administrator to the computer for which you want to enable local area surface and enable it. This should have no issues like you mentioned.

    Pinal Dave (


  92. Hi
    I have been tasked with a task of creating a timesheet That must automatically be diplay for every user per month, their monthly timesheet, setting the weekends to Off and the week days to the default hours of the individual and can also be edited.
    The thing is I do not know how I am going to have this done when the user do not have to insert his time at any time during the month. I have proposed to my supervisor that it would be easier for me to have the user insert his time at the end of the day but he does not. I have no idea how I am going to have this records displayed when I do not have them stored any where. Can you please help


  93. Hi Pinal,

    How to migrate the data from sql server 2000 to sql server 2005. Please send me the details about this.



  94. Hi Dave,

    I have the data in .txt format. I need to bring them into SQL server.

    In .txt has many no of columns.

    How many columns are accepted in Sql server?.

    Once agin send this question.



  95. Hello Dave,

    How r u?.

    what is the use of trrigers in sql.

    Please explain which situation u to use trrigers.

    Please explain with Example



  96. Hi Pinal,
    I need help for this immediately. This looks simple but i am not sure
    and not confident. Please help me. My scenario is:

    I have created a database called ASPStateDB to hold the seesion of
    objects in SQL server.
    Now i have to create a Local service account for this database.
    The local service account name will be ‘ASPUSER’.
    ASP.NET is installed in the machine and have to use ‘ASPUSER’ account
    to connect to the application from the database.

    So how can i create local service account with the above servic account name.
    account name.
    Please help me immediately, i have to set this up on this monday.

    Thank you


  97. HAI SIR,



  98. How to find out user activities in sql?

    for ex:

    to find which user delete the data in database.

    Please Ans me immediate very urgent



  99. Dear Sir,

    How to write a query to find count of tables, SPs, triggers, primary keys, constraints, views and all objects in a database?

    Output must be similar to…

    Object Name Count

    Procedures 27

    Triggers 20

    Primary Keys 10

    Please give explanation also…

    Thanks & Regards,


  100. I would like to make a comment about compatibility issues i.e 65 and 80.

    I been testing to all the store procs , jobs, dts which we want to move over to 80. From all the testing experince what i learned was that there isnt any differences if you copy all the settings of 65 to 80 and run the sql code in 80 compability. (No need to change the sql codes, just change setting of 80 as of 65)

    Do you see if I am wrong?


  101. Great paper Pinal. My name is Mike Biddle and I am the webmaster for the Detroit Area SQL Server User Group (DASSUG),

    I would like, with your permission, to post a link to a downloaded SQLInterview.pdf document on our website.


    Mike Biddle
    DASSUG Web Adminsitrator


  102. Hello Dave,

    I am using your answers very effectively.Thanks..

    Now I need to convert the “TIFF” images into “JPG” images using query.

    I have searched in your blog.But I did not get anything…

    So,Plz consider this issue and reply for the same..

    I ll be waiting for your reply..

    Wilson Gunanithi.J


  103. Hi Dave,

    Scripts are very useful …

    Can you provide a good example set for implementing partitioned tables concept in SQL 2005.

    Thank you,


  104. @Rajesh,

    If you are asking for any third party tools, I do not have much experience with it.

    I use SQL Server BULK INSERT or bcp for the same purpose. You can also filter your records/rows using query.


    I will sure work on partitioned tables and post my views on this platform.

    @Wilson Gunanithi.J

    I would not use SQL convert images, I will let application programming language (ASP.NET, PHP, COLDFUSION etc) to take care of that situation.

    Pinal Dave (


  105. Hello Dave,

    Thank you very much your wonderful reply of question no 145..
    (Plz, dont hide this mail from users’ view)

    Wilson Gunanithi.J


  106. Hi Pinal,

    I am a newbie to SQL 2005. I have a requirement with the client where in he wants me to optmize a stored procedure.I have executed the stored proc and am looking at the execution plan.Execution plan has clustered index seek and also index seek which are the most expensive i.e cost is ranging from 50%-100%. I have no idea how to go about this. I would appreciate if you could throw some insight into it.

    Regards and thanks,


  107. Sir We are upgrading our SQL server 2000 to 2005 but i am faceing some problems after restoring the database.

    In 2000 after restoring database from sysusers table after deleting use we can create againg that user in security but in 2005 i can’t find this table and can’t understand what is the solution your kind help will be highly appreciated.


  108. Dear Readers,

    I have been receiving constant request to answer few questions. I try my best to answer all the questions. If I do not know the answer, I request my other knowledgeable readers to answer question. Please be patient, every question has answer.



  109. Hi

    I am new to SQL serevr 2005 and we are usinf Express edition.

    I have few questions it would be nice if you could answer.

    1. I want to store total no. of rows of a table in a variable is it possible?

    I came to your blog site with a query that shows rows and cols and size of all tables in a database but i could nt figure out how to get no. of rows in a temporary table in a variable.

    2. I have a sql serevr 2000 database when i take backup of it and try to restore in on sql server 2005 express edition it says access denied. I could not figure out why?



  110. hi pinaldave i am new in sql-server 2005, before i worked oracle9i…. now i am shifted to sql-server2005.. so how is it… and also please send me MCDBA and sql-server 2005 study materials..

    Thanking you


  111. Hello Dave,

    Actually i’m facing a big difficulty.

    Can you tell me how can i query several SQL databases at the same time on the Same server. All the databases are similar in structure.

    i need to run it throught VB.



  112. Hello Dave,

    Thanks for your reply..

    You have told that ” I ll use ASP.NET,PHP,COLDFUSION ” on that situation ( “TIFF to JPEG” ).

    Ok.. please, can you send the coding in ASP.NET to this situation and the coding to convert the “COLOR Images to BLACK & WHITE Images” as your reply..

    I will be waiting for your reply…

    Thanks in advance..

    Wilson Gunanithi.J


  113. Hi,

    How can i find records having duplicate values in the same column of a table.

    I have a column called ‘desc’ in my table. I want to find out all the records where the word ‘profile’ is repeated more than once in the column.

    Appreciate any help.



    • select * from your_table
      where desc = ‘profile’ — would suffice
      I’m not sure when you say duplicate records and would like to find all records… sounds confusing


  114. Hi Pinal,
    I need to write a query which based on a column name for example this_Column will update data in the database. If there is no entry found with a particular field name append it to existing DB else if there exists an entry overwrite the whole record with the new one. i.e; update this_Column, make, model etc. Any help is appreciated.


    • I dont understand your question. 1st Query- Syntax error.. 2nd Query, as there is no condition updates for all records irrespective of what MonKey contains.


  115. Hi Pinal,
    what is Basic Multilingual Plane(range U+0000 to U+FFFF).
    Does SQL Server support this.
    If yes how to check it???

    Please suggest..



  116. Pinal,

    would be possible to let me know if having ‘Print’ statements in stored procedure for debugging purposes affects performance and therefore should be commented out? what’s your suggestion?



  117. Vijay,

    Yes, Print statement in SP does degrade the performance because SP has to return more data then usual. However, the performance degradation is usually very negligible so I see use it once in a while. If you are using extensively or SP is called many times, I would remove it.

    Pinal Dave (


  118. Hello Dave,

    I have the backup of DB1 database(d:\DB1_Bkp.bak).

    But, I need to restore this backup into DB2 database (…using query…)

    I have the query to restore the backup within a same database.

    But, I dont have the query to restore the backup in another database.

    Plz, reply for the same with the query.

    Plz, dont ignore this. very urgent.

    Thanks ,
    Wilson Gunanithi.J


  119. Wilson,

    You just have to replace the name of the database and it should work for another database.


    I have request if you please control the quality of the question asked.

    Thanks for your detailed reply about backup and restore in email. Your interview questions are good too.


    Kevin G.


  120. Kevin,

    No question is less important or more important. As per me, I enjoy reading all the questions and like to answer as much as I can.

    There are many great readers visiting this blog regularly. They have helped out many of my other readers who have questions.

    This blog is created as my personal book mark and reference base. I am glad many people are finding it useful and participating in my strong and cozy community.

    I have edited your comment and removed inappropriate remarks to other reader.

    Kind Regards,
    Pinal Dave ( )


  121. Hi Dave or Pinal,

    You are doing awesome job with this blog. Do not get distracted with comments. Your articles are your money.

    Nobody shares information like you do. You are gift to community.

    Dave Moore


  122. Hi Pinalkumar,

    Thank you again for your help with our backup system. It is running excellent. We are willing to share your scripts if you approve. We are sent you email regarding few other things.

    You should right about those script which you provided to us. They can be useful to everybody who have issues with backups.

    Sanjeev Nayak.
    Sr. Network Administrator


  123. Very good very very good.

    Is it possible to have you over to our corporation one day to discuss few things and go over our system? All paid trip to DC?

    send me email.


  124. Hi Pinal,
    I want to use log shipping for Disaster Recovery backup of my database. But question is only that, will it effect on my application performance ? If yes, how much.?

    Hemant Nehete


  125. Hello Pinal,

    Can you please explain for my doubts,

    We have one sql server,we are not configured sql backups with this server but some other backup tools taking all servers drives backups to different sever every 30 mnts,

    now my database with Full recovery model, and on 10.00 am my ldf and mdf files backup done(third party backup tools taking drives backups). now my database gone crash at 10.25 am so how can we recover point to point failure data. we are not taking any sql backup for these servers.

    Thanks and Regards
    Jayamohanan M.R.


  126. @ Hemant,

    It all depends. I use the same setup for some of my not so much used servers. There is little bit of performance degradation but I consider it as part of this set up and architecture. Do not worry, this is not the best set up but works and have been working for me on certain servers.

    Since we have moved our databases to SAN, space is no more an issue so it works great.


    If you are not taking backup on server and it has crashed. I am very sorry but you are out of luck. There are very few things can be done in that situation. You can visit here and see if something you can apply in your situation.

    Pinal Dave ( )


  127. Thank you Pinal for your detailed answer.
    All the time you have answered me. How come you help so much whereas other blog authors ignore users.



  128. Hi,
    I have a situation. I have to load the data from CSV file into a SQL server database table. In the CSV file there are 16 columns, and in table there are 24 columns. I do not know how to do this as there are differences in the columns. I tried using Data Import Wizard, i was not able to include nulls in the rest of the columns of the table so please help me how to resolve it.



  129. Hi pinal I an getting following error when I am restoring database. but the same backup is restored on other server.
    Source MSSQL
    Event ID 3266
    Type Error
    The backup data at the end of “D:\Websupport\db_ncdex_Old_0708231405.bak” is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.

    please reply. I am in serious trouble.



  130. @Rajesh,

    Thanks. Read my reply here :


    Please follow the suggestion in the error message. Use RESTORE HEADERONLY and then follow up it with RESTORE VERIFYONLY. Additional Information is here :


    Cursors are not good and alternative is mentioned here with examples.

    Pinal Dave ( )


  131. Hi,
    I am importing the CSV file data into SQL Server table using the Import Wizard Tool but i got this error. What does this mean and how can i be able to get rid of this. Please help me as soon as possible.

    Information 0x402090dc: Data Flow Task: The processing of file “C:\Test.csv” has started.
    (SQL Server Import and Export Wizard)


  132. everyday I learn from you.
    Good work. Please email me with your contact info. We learned from Manmeet that you have helped him to look over his TempDB and now it is optimized and no locks. We want that help.

    Soniya (ebrahims@tm…)


  133. Hi Pinal,

    My CEO wants your help. I send you four emails and you no reply. Today I am writing to forum. Can you visit our organization?? You will have to pay for your travel and accommodation. We will pay for food only. I have promised CEO you come because you friend.

    reply faster. Weekdays only. Do not ignore this message and publish it. We want you to come and fix everything. Weekdays only. Reply is must.

    good site. We like it.



  134. @Rajguru,

    I thank you for your offer to visit your firm/organization. I wanted to reply your email earlier. My schedule is little tight recently as I have been traveling in weekend. I had wonderful visit to biggest environmental firms recently. You can read about that here :

    I will check my schedule and will reply to you when I will be visiting your organization. I will have to take a day off from my regular workday to visit your place, so please be patience. I will also check my budget as I will have to fly to NY.

    I will sure get back to you soon. I hope this reply will satisfy your curiosity and doubts. No need to further communicate about this, I will get back to you very soon.

    Kindest Regards,
    Pinal Dave ( )

    PS: I have removed name of your website to protect your privacy.


  135. @Rajguru S,
    Good letter. I am surprised why Pinal did not reply to you earlier. I am glad now you have posted to this “forum” to invite Pinal to your “organization”. Just so you know Pinal loves Indian Food.


    Kevin G.

    PS. Do not edit this post.


  136. Pinal,

    Do you know how I can pass a hashed value into a SQL Server 2005 stored procedure as an input parameter?

    I am using a database symmetric key on my db to encrypt sensitive data like cc number, and ssn fields.

    I am storing these values as varbinary encrypted in the tables.

    I am passing a SecureString (encrypted) type from my C# application to my SS stored proc.

    Do you have any ideas how to do this?

    Fred Z. (Atlanta)


  137. Hello Dave,

    Is this possible to increase the size of the index key >900 bytes?

    If yes, reply how to do..

    If no, reply the reason..

    Please, reply for this question.

    Wilson Gunanithi.J


  138. hi Pinal,

    very goodevening first.. I need one help in one query which will give an addition of a decimal column of successive rows. E.g.
    Output should
    Pls respond fast as I need solution.


  139. Hi pinaldave,

    I want to extract all the statements of Procedure. Is it possible to extract or do we have this information in any system table.

    Please help me out if you have the information. Please send me a mail to my mail id…..

    Thanks a lot in advance.


  140. Hi pinaldave,
    I don’t want to display the text of the procedure. Yes. It can be displayed using sp_helptext or from syscoments. But want i want is, to extract the exact statements from the procedure as shown in the below example…

    For Example


    @Sample_Param INT
    IF @Sample_Param = 0



    IF @Sample_Param = 0

    Help would be very much appreciated…


  141. Hi Pinal,
    I need some advice from you. I am working on a project. Here is a small description. We have an Intra net application with a SQL Server 2000 back end. Now the new project will create a public web portal which allows users to enter data this data will be stored in a SQL Server 2005 created as part of the new project. An important requirement here is that whenever the data in the Sql Server 2005/SQL Server 2000 is changed it should update the SQL Server 2000/SQL Server 2005 database with these changes(there needs to be two way updating upon triggers on each of the databases. There will be corresponding identical tables in both these databases.

    So the databases in these two stand alone applications should communicate in a bi-directional way upon data changes.

    What is the best way to solve this problem?
    Thank You


  142. Hi Pinal,

    Can you suggest when to use views and when to use stored procedure. if we generally want to fetch the data from a table so which would be the better option of fetching the records,
    “Views” or “Stored Procedure”

    Which one is better in terms of performance.



    • Both objects are meant for different usage. Views meant to provide secured access to tables. We cant implement business logic, neither we can pass parameters not implement conditional statements.
      Meanwhile, both SP and View are pre-compiled. Both offers same performance as long as the nature of SQL statements are same.


  143. We are having problem with Microsoft Dynamic GP , when we create new function or module in it ,or even small customization we have to install this package in all users PC (50 pc) .
    This issue is very important to us as organization and its time consuming
    So there are no way to run patch or script file on the server that does all of that for us .
    Thank you


  144. Hi Pinal,

    I will first give a brief of the situation, My application allows customers to create service request from my company through a web portal. Now the customer fills up the request form and saves it . The data goes to the SQL Server 2005 DB . Their is a Service request ID column which is the identity column. I want the index to be generated in a particular format by the SQL Server 2005. The format should be SR(mm)(yy)xxxx. For eg: SR0907235 means this item is the 235 th request made in the month of August 2007. How can I achieve this. Please help me out.


  145. Hello Dave,

    What is sql server? why is it used? is it good to use?

    If yes, reply how and why…

    If no, reply the reason… why not…

    Please, reply for this question.

    Wilson Gunanithi.J


  146. Hi Pinal,

    Your blog is very attaractive and giving good knowledge of sql . I Have one Q i am using ‘case’ in select statement i wanna
    that if column contain NULL value i wanna show ‘Some Other Information’ but NULL is not refrenced please help
    like this
    case when ‘NULL’ then ‘NA’ else ‘AN’ end

    Thanks in Advance.



  147. Hi PinalDave,

    first of all, my praises to you for this very informative website!! so awesome…

    i have a small problem though on my ssis project, im sure this is just a walk in the park for you:

    we have 2 separate DAtabases and each DB has a common table , lets say
    Tracking Table. In DB1, Tracking has columns that has varchar datatypes, and
    at DB2 it has same columns but of INT datatype.

    Basically, each night , I want to extract only new records from Tracking
    table at DB1 to Tracking Tbl at DB2. Then convert the other columns from
    varchar to INT.

    Also there are some columns at DB1 that need to be looked up at some
    reference tables before it can be saved at the Tracking at DB2. But that can
    come in later, what i need now is just to convert those varchars into INTs
    on the DB2 server. Im talking about 10-15 millions of rows from DB1 per day.

    Any ideas which tool I should use in SSIS? any links I can read to help with
    my problem ? I know I should use 2 diffrent connection managers for DB1 and
    DB2 on the SSIS project, and i also wanna avoid creating temp tables at
    either Databases. Maybe just store the new records in the memory (after
    comparing the 2 tables from 2 databases ) and then iterate through that
    memory recordset then save new records to the DB2 tracking Table. And then I
    should also use the For loop component? Can u show me the flow of how
    my SSIS should be???

    thanks in adv and more power 2 u !!


  148. Pinaldave,

    i used ISNULL but this is not working in this case

    case when ISNULL( ,’NA’) then ‘NA’ else ‘AN’ end

    pls. check with example and forward me. waiting for reply.

    Pls. Help.




  149. Hi Pinal dave,

    situation :-
    i have a table having 2 field namely userid,menuid
    both field are foreign key
    now if new menu add in menu table
    i want that this menu aaded for all the user in this table
    i don’t want to manually insert it , just i need to insert new record for all the user in this table





  150. Dear Pinal,

    Fantastic website !

    Please can you answer a question for me ?

    I have a SQL server 2005 that does not need to be running 24/7

    Is it wrong to perform backups by shutting down SQL with TSQL SHUTDOWN and then simply backing up the entire hard disks (all the MDF and LDF data files) ?

    Microsoft books on line etc does not mention this method at all and I wondered if there is a problem with it or it is wrong in some way ?

    kind regards



  151. Hi Pinal Dave,
    Good Evening.

    The below article was present in our company’s community site. I want to share the same with you. The article is related to debugging of stored procedures. Especially look at the end of the article. It tells us how to enable Debug menu in case it is not enabled.
    Thinking whether a stored procedure can be debugged?

    Yes of course Y NOT. Here are the steps.

    • Open SQL query analyzer.
    • Open the object browser.
    • Expand the database in which the stored procedure you wish to debug is present.
    • Expand the stored procedures node.
    • Locate the stored procedure. Right click on the stored procedure. The last option, saying “Debug”, will allow you to debug the stored procedure.
    • Once you click debug, you will be prompted to enter all the parameters.
    • If you want to debug the stored procedure without being changing the values within the tables then select the option “Auto roll back” option.
    • Enter all the parameter values and click on “Execute”.
    • The debug point will be created on the first executable statement of the stored procedure.
    • In case of character values, that need to be passed to the stored procedure as parameters; do not include them in single quotes.
    • In case of datetime values, pass them as yyyy-mm-dd hh:mm:ss; do not include them in single quotes.

    If the debug option is disabled then, collapse the ‘master’ database node then extended stored procedure node, locate the extended stored procedure ‘sp_sdidebug’. Right click on this extended stored procedure; select ‘Properties’. Click ‘Permissions’. Grant the ‘Exec’ permission to the ‘Public’ role. Click ‘Apply’ and then click ‘Ok’.


  152. My coworker and I, both DBD’s have been trying to figure out a way to “load” a number of varchar row results into a single variable or field, without using a cursor.

    ID(int) FirstName(varchar(50))
    1 David
    2 Susan
    3 Tommy

    We want the variable contents to look like:


    We haven’t been able to find a way to do this in a single line of code, such as an Update (to a temp table for example) or a Set @TmpVar = blah

    Any suggestions?


    • check this out :
      create table #temp2 (
      ID int,
      FirstName varchar(50))
      insert into #temp2
      select 1, ‘David’
      union select 2, ‘Susan’
      union select 3, ‘Tommy’

      declare @sql varchar(1000)
      select @sql = coalesce(@sql + ‘, ‘, ”) + FirstName
      from #temp2
      select @sql

      Please note that this would give csv of all records. But if ID are duplicates and u want csv for specific IDs, then put condition in where clause


  153. You absolutely rock.
    Thanks our CTO wants to say thanks. He would like to request another phone conference call with you. Your help on the spot and saved day.


  154. Hi Pinal Dave,

    This is second request. Let me know when you can visit our company? We will pay for your travel, accommodation and food of your choice. (desi food?)

    Just let us know what it takes for us to have you over. A good direction in beginning is all we ask. I also asked Mr. Das to contact you with additional information.

    I will personally take you to visit all the good places in NYC.

    If you do not respond in three days we will have to seek another consultant. Our email address is {removed}.

    Sarjeet Singh


  155. I have bunch of dirty and duplicate datas across my table. I was asked to match and De-duplicate based on only 5 columns(FN,LN,Address,Zip,Buyer/Seller) out of 25 total column. Unfortunately, there is no Primary Key. I figured that Fuzzy Lookups, Groupings and Conditional Split would do the magic. But I am still struggling! How can I populate 25 column based on these 5 duplicates?


  156. Hi Dave,

    in my log getting below messege repetedly

    database starting ”

    unable to find answer in Search SQL .please help me on this application team reported tat performancce issue.waiting for ur reply.


  157. I hav a doubt abt using 2 cursors inside a recursive procedure..
    Inside the procedure i hav open cur1 done certain calculations.After closing cur1,i opened cur2 and inside while fetch i recursively called the same procedure…
    As a result,cur1 fetching is possible but occurs an error like this:
    Msg 16916, Level 16, State 1, Procedure debit_credit_calculations, Line 31
    A cursor with the name ‘cur2’ does not exist.

    My code is like this:

    use test1
    alter procedure debit_credit_calculations(@grpid int)
    declare @ah_id int,@ah_name varchar(30),@amt int,@gid int
    declare cur1 cursor for select tab1.accountheadid,tab1.accountheadname from accountheadmaster as tab1 where tab1.groupid=@grpid
    declare cur2 cursor for select GroupId from groupmaster where ParentGroupId=@grpid
    open cur1

    could you plz help me out,waitin for u’r rely…


  158. Hi Pinal,
    Please give us Some key points to be keep in mind when applying Log Shipping, to maintain application performance as well as good programming practise.

    Hemant Nehete


  159. Hi Pinal,
    Please see following query and tell us what’s the difference, with performance prospective.


    SELECT SMCOCD as CompanyCode, SMCUCD as CustomerCode, SMGCCD as GroupCompany, SMCUNA as CustomerName, SMCTST as State FROM CUSMST

    Hemant Nehete


  160. Hi Pinal sir,
    How ru??

    See i found the below article when i am searching for shortcut keys for comment in SQL2005 Management Studio.

    I want to know what is we call Ctrl+K keys actually. Why we need to pres these keys, Why not microsoft retains the previous keys (CTRL+SHIFT+C). Any reason to change this.

    When we press Ctrl+K in the Management Studio then in the status bar below it shoes
    “(Ctrl+K) was pressed. Waiting for second key of cord”
    What this cord means????
    I know that this is a very silly matter to discuss BUT if u can tell me then its great. Else no problems.
    And one thing that your blog is “Simply AWESOME”.
    Keep it up.
    Byee and Take care


  161. Hi Sir,
    Can u please explain me the CUBE and ROLLUP statements, WHAT these stament do, and why and when to use these.
    Please , eplain these and if u can give any example for these using Norhwind database, then its good. Thanks you !!!!!


  162. Hello Pinal,

    We are getting errors in our production database when we restarting the sql services

    Could not find database ID 2, name ‘tempdb’. The database may be offline. Wait a few minutes and try again.

    can you please tell me why this occuring



  163. heylo Pinal,
    can u just let me know how can v remove a primary key constraint from a table.

    eg) create table ABC(
    Emp_No int primary key,Emp_Name varchar(20))

    please let me know at the earliest…waiting for ur reply
    bye bye


  164. When I try to insert a record into a table with few columns one of the values that I provide has a length greater than the column length. We get a generic error message that the “String or Binary field would be truncated …..”. Is there any way to extract from SQL Server which column had issues than manually looking at every single column to figure out the culprit?

    Srinivasan Prasanna


  165. Hi Pinal,
    First of all thanks a lot for creating such a great & helpful blog, i highly appreciate this.
    Well, i am planning to make my career as SQLDBA, infact i have already started the studies also. Could you please guide me through the process / procedure which i need to follow to become a successful sqldba. I will highly appreciate if you could tell me how to plan the studies, i mean, since i am new to sqldba, what should i understand first, and then onwards…

    Any help in this regards is highly appreciable.
    Once again thanks a lot for creating such a wonderful blog.


  166. Hi Pinal,
    We are frequently facing this error while working on SQL.

    [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server ‘MELAWAI-EAS’ in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

    Hemant Nehete


  167. hi there,
    I have a issues updating a current proc. Currently proc is written to go back one yr from today. This needs to be changed to pull one year worth of data ending six months prior to last sales week.

    Please help me out.

    Thank you.


  168. Hi Pinal,
    I have bunch of records that are being pulled from DB2 databse using ETL and loaded into SQL Server Database tables. But these records have single quotes and i have to remove these quotes from the records in the SQL database tables. Thus i am looking for a script or a way to automate these quotes nightly basis because the data load from DB2 database to SQL is nightly process. So please let me know how do i be able to work around as this is a crtical issue for me.

    I look forward to receiving your help.

    Thank you


  169. This code is giving me Sunday as a starting day of the week

    ‘Week ‘ + cast(datediff(WEEK,(date – (Day(date)-1)),date)+1 as varchar) + ‘ ‘ + cast( DATEPART(YEAR, Date) as varchar ) AS WeekDescription

    could you please tell me that how to get the monday as starting day of the week


  170. Thanks for the great site. I had found a T-SQL script that I used in my 7.0 database. It allowed me to find a piece of text in any of tables (715+) in the database.
    I tried to create the stored proc in SQL 2000, to search a new 2000 DB for data. However, the proc would not load/run for some reason. I think there may be a compatibility issue.

    The DB is only going to exist for a short time, and I need to send to someone else. I did not want to add full text search if that could create an issue.

    Is there a way on 2000 or 2005 to find data within all tables in a database?
    I can send the Stored Proc, if you think that might be useful.


  171. Mr. Pinal – I do some creation of SQL tables and importing them from Access databases but have now been asked to put an Access database on SQL so that associates around the US can access it. Is this possible? Thanks in advance… have gotten alot out of your website and will continue to use it as a reference!



  172. Hi Pinal :) how are you today?
    i just stumbled upon your website and foud that your articles on MS SQL 2005 are great and understandable.

    I have a problem need to solve, and I hope you can help me with it.

    The problem.
    I have 2 databases namely: DB_Old and DB_New.

    In DB_Old, there is a table called tPhonebook_Old.
    While in DB_New, there is a table called tPhonebook_New.

    tPhonebool_Old contains 100,000 records while tPhonebook_New contains just a mere 1000 records.

    So now i want to append all the old records from the tPhonebook_Old into the new table, tPhonebook_New. How to do it?

    Here is my script which i tried to do but it doesn’t work, as it returns on “0 records affected”.

    USE DB_Old

    DECLARE @refno char(12)
    SET @refno = ‘SELECT NO_RUJUKAN from tPhonebook_Old’

    DECLARE @pinno char(16)
    SET @pinno = ‘SELECT PIN_No from tPhonebook_Old’


    USE DB_New

    DECLARE @refno char(12)
    DECLARE @pinno char(16)

    UPDATE tPhonebook_New
    SET PINNO = @pinno
    WHERE RefNo = @refno


    Do please take a look and tell me what are the problems with this script. Thank you.

    Daniel – 02.10.2007


  173. Hello

    I am planning to write in depth article about log shipping. Keep checking the blog.

    Yes, it is totally possible. However, please be careful as usually cursor is not required and same task can be achieved using T-SQL Script.

    @Prashant Pandey,
    I will try to answer your questions in either email or on blog.

    Restart the server and try it again, if this keeps on happening you want to check your tempdb logical file names.

    @vineet vijayvargiya,
    DROP CONSTRAINT constraintname ON tablename should do it.

    @Srinivasan Prasanna,
    You will have to manually test this.

    There is no shortcut to hard work and real word experience. Keep reading good SQL Sites and Blog.

    You need to add link server as it says in error. I will write a post about it soon.

    I need additional information to answer your questions.

    I am SQL Server Expert not DB2 so I am sorry I do not have answer for you.

    Please visit and search for datefirst and you will find the answer to your question.

    FullText Search is very powerful and I always have used it sucessfully. No issues.

    Create tables and use SSIS to insert database.

    GO statement is resetting your query.
    Visit :

    Pinal Dave ( )


  174. Unable to connect to server IP

    Server:Msg 17,Level 16, State 1
    [Microsoft] [ODBC SQL Server Driver] [DBNETLIB] SQL Server does not exist or access denied.

    Whenever to connect sql server from query analysier at present to display above error. Please give the solution.




    • Unable to connect to server \\SRVNAME:

      Server: Msg 17, Level 16, State 1
      [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

      i have this problem plase give me a solutin


  175. Hi pinaldave,

    I’m looking for a way to search for a certain value (number) in a databasefield (text). The problem is that the datafield contains html code that contains certain numbers. The goal is to return all rows that contain a value equal to or above a given parameter. Is this possible on a sql base?

    Thanks very much,


  176. hii
    i am having a small doubt.

    I need to copy the data from on etable to the other in tha sam edata base. the problem is in one table the Date of birth is declared as nvarchar(50) and in other table it is declared as smalldate. so i need to convert the date in order to copy the data.

    so can u help me out in the query that is required to copy the data successfully.



  177. Hi Dave

    frequently i am getting error

    WARNING: Failed to reserve contiguous memory of Size= 65536.

    And iam unable to backup my databases running on this server all backups jobs are failing

    can u please give me queck soluton to resolve the issue …

    version : Sql server 2000 sp4


  178. Hello Dave,

    I have the column for “Images”, in which I have the path of images.



    Now, I want to check the invalid images.

    To check whether the image is in the path, or not.

    i.e. I want to find the invalid images from database (which are not in the specified folder/path).

    Is this possible by query? If yes, please tell me..

    Wilson Gunanithi.J


  179. Hi Pinal,

    Can you tell me something about sleep_task waittypes in sql 2005, how we can avoid this waittype?.

    We are getting continues this waittype with store procedures.



  180. hi all,

    i want to write a generic Insert & update procedure.It should work with different tables having diffrerent datatypes.suppose let say table1 may have col1 int,col2 varchar… and let say table2 may have col1 varchar,col2 that..and so on..

    generally till now wat iam doing is that iam writting different stored procedure for different tables..

    Now i want to write One Insert & update procedure in my project…and for all the tables in the project i want to use only that procedure.

    Is it Possible? If yes kindly help me ..on resolving this issue..

    Thanks & regards


  181. Pinal,

    Can you tell me is there load sql available for sql server 2005. Some one asked me if I can load sql for sql server? I thaught it is only available for Oracle.

    Thank you for your input in advance.


  182. hello dave
    I wish to thank you for creating such a helpful blog .
    I am new to the field of SQLDBA, freshly graduated and without proper training I have been assigned the job of DBA . With lots of struggle I have managed to learn quiet a bit which earlier seemed quiet tough.
    Can you suggest me some books to refer?
    My first assignment was to find the number of records in entire database,after 5 hours of struggle I copied your code.(sorry for that).
    I’ll be looking forward for your guidance in future.
    Wont copy this time though……
    Thanks and regards.


  183. Dave,

    I have a Coldfusion Webpage that is timing out. The datasource is SQL Server 2000. I have modified the table that the sql uses for the page. Now there is only one criteria in the sql it is a range on a float(8) column

    Select …..
    from details
    where invoice between #firstinv# and #lastinv#

    The webpage pulls up without timing out, but it’s slow.
    This table is growing and I’m concerned it will start timing out again. I added an index to the one column in the where statement. I created a non-clustered index on invoice float(8)
    the web page timed out again. I deleted the index and the page pulls up slowly, but does not time out.

    How can I create an index with sql that will not timeout?



  184. Hi Pinal,

    I am Raaj from India and i am now working for a project which has been using Merge Replication for the Data Synchronization within its locations across the globe. Each Site / location updated the data to the central server. For example there are two locations “A” and “B” and all the three are in diff time zone. There is a POSSIBLE problem of overwriting of data and data corruption. If Location / site “A” has the correct data and updates the central server with the local time, due to connectivity is slow it takes a bit longer for it to update. It lags a time diff with the Location / site “B” which in turn updates the same data to the central server and the same data is pushed to the subscribers “A” and “B”. Here comes the problem. The data updated by “B” is not correct. I have planned to resolve this by having the GMT in place of the local time. I want your help how to implement GMT in the merge replication (publisher, Distributor and the Subscribers)?? I want when the data is pushed to the publisher it has go with the GMT and vice versa. Kindly help me to overcome this problem and fix for the same.

    Thanks and Regards
    Raaj KR


  185. Is there an advantage or disadvantage to using multiple fields for the clustered primary key, instead of just using one ID field? I am concerned with any performance issues this might cause……

    If you could give me the advantages of both types that would be very helpful as I could not find any good writings on this.



    • Primary Key created for multiple fields is called Composite PrimaryKey. So, Clustered Index is created for these columns. During insertion for the primary key fields, it looks if these fields values not exist are not null. Then inserts and reorders.. Hence, primary key on multiple fields is just costlier


  186. I haven’t find any good article regarding how Table Partitioning works in SQL 2005 and how to implement the horizontal partitioning on a 200GB transaction log table. Can you write an article about that and what will be your approach if you have to solve that problem?


  187. I’ve been trying to find your article on how to script a complete database (objects and data), but I can’t find it. I found the article on how to script the objects, but I’m needing to script the data also. Any chance you can reply with the direct link?

    Thanks for all of the great articles,


  188. I want to store more than 64000 characters in a single field in SQL 2005 DB. I read your article on the website, and tried out varchar(MAX) and text fields but all of them are truncating the data at 64000 characters. Is there any particular setting on SQL 2005 that I am missing out or is SQL not able to hold more that 64000 characters in a single field.



  189. hi Sir,
    have a varchar field in a table that is used to store a variety of values (including dates, text, nulls etc).

    I need to find all dates (these are currently stored in the format dd/mm/yyyy) and convert them to yyyy-mm-dd format. These values need to remain in this varchar field (rather than a datetime field).

    Any help would be greatly appreciated!

    sabeel khan


  190. Hi:

    I like to know how to use a store procedure to query sql 2005 all tables in a particular user database to show me the column property say all nulls is set to yes, or default value is zero for this column.

    But do not know how to do it –


  191. Hi Pinal,

    I would like to know about SQL Reporting Services 2005. Let say I would like to deliver those reports to cold fusion using Reporting’s web service, how’s the command needed to insert into cold fusion script ? Hope able to get some help from you here and thanx a lot.

    Best Regards,


  192. Hi Pinal,
    I haven’t found any best place to learn sql in such depth.just keep it up.I have a requirement which i feel necesarry while working with sql server.supoose i want all the sp which consist of particluar word i have a solution for this its
    @StringToSearch varchar(100)
    SET @StringToSearch = ‘%’ + @StringToSearch + ‘%’
    AND SO.NAME LIKE @StringToSearch
    ORDER BY SO.Name
    but what if i want to search all the tables names used in the result of above StringToSearch output sp list.please provide me a solution it will be a great help to all.plz mail me solution and do post in your blog


  193. Hi Pinal,
    Is it posiible to pass a table to a user defined function?
    I have

    Create function Split( @text varchar(1000), @delimiter varchar(20)= ‘ ‘ )

    When I call this function

    SELECT * FROM Split(‘foo, bar’, ‘,’)

    Instead of this I want to call a table. can I do something like this.

    Select * from Split(Select Terms From dbo.temp, ‘ ‘ )


  194. Hi,

    How can i know, when the stored procedures last run ?
    Is there any system table in 2000 or 2005 ?
    If you have any ideas or script then advise me.



  195. Hi Pinal,

    I wanted to ask a question about how I can use a T-SQL script to move the transaction logs for System databases to another drive and change the database and log settings to different drives letters for future database creations? Do you have a script that can achieve this?




  196. Hi Pinaldave,

    This site is awesome. I am a beginner and got stuck at some point.

    My query is i have 2 tables (tblbuilding & tbltype) with columns as building, buildingid, type, typeid.

    I have one main table tbltechen2 with columns group,seats,room,buildingid, typeid.

    My question is when i submit the survey page with building and type specified, i should get the buildingid and typeid to be entered into tbltechen2 getting the buildingid and typeid from tblbuilding and tbltype respectively..

    Can you tel me the query for inserting into tbltechen2.

    Thanks a tonnnnn,


  197. Hi Pinal Dave,

    Actually i wnted to save .log file every day to diffrent folder how do i go for this and once it is saved the file should get refreshed and new data should be there in the .log file can you please help me i did it copy file and then but next day it get replaced but the older information will still remains


  198. Hi Pinal,
    I am having a performance issue with a data flow task is ssis. I am pumping data from a view to a table.
    Could you please help with some suggestions

    Thanks in Advance,


  199. Hi Pinal,

    I’m a SQL DBA and working on downloading the files from FTP site to local machine using FTP task in SSIS package.I want to download the files depending on the corresponding modified date in the FTP site (if the modified date matches with the date in SSIS package,then the file should be downloaded).

    Could you please help me in this regard…
    Appreciate your help..



  200. Hi Pinal,

    When i developed my stored procedure and run it on my laptop, it took 30ms. But when in test it at my server, the query took 2.30min. My server specs is so much better than my laptop. I using MSSQL 2005 standard edition for my server.
    It came to me that the server is having problem. Correct me if i’m wrong. What are the test can i do to prove to my boss? Thks in advance.


  201. Hello and Good Afternoon Mr. Pinal.

    I’d like to make a question for you. I would appreciate a lot if you could help me with the next issue.

    I have two Servers on different buildings and both buildings need to have the data replicated on both servers with a delay of one or two hours ( better if I don’t have a delay ). Right now the two servers aren’t communicated but in short they will be.

    The have tables that are constantly changing in the order of millions. The principal problem we’re facing is the indexes on both servers cause of the independent growth.

    If you could point me at some direction I would appreciate a lot

    Thanks for reading this.

    Alberto Hernandez


  202. Hi Pinal

    I have problem ,I have deleted 10 rows from table by mistake,
    but now i have to recover these rows,I dont hav any backup &
    transaction backup.

    can u tell me any way to recover these rows.


    Ashutosh Gupta


  203. Hi Pinal Dave.

    I read your posts about collation problems. They were of tremendous help as we’ve been having trouble with collations.

    Thanks a lot for these posts.


  204. Hi Pinal Dave

    I am using SQL Server 2005.
    But i am not able to connect it with vb.Net 2005 using DSN.

    can you please tell me how to connect SQL Server 2005 with the use of DSN?

    Thanx .


  205. I am trying to take data from excel to sql server and i am getting the following error please help.



  206. Hi man

    I need help, I’ve been working on this for a weeks now. I have created a Job in SQL Server Agent. The job is is executing fine but the my SQL Statement in the Command block is not correct.

    I’m using SQL Server 2005, I need to import an Access Database to SQL server automatically every morning, and Delete the old data before Inserting new data.

    Can you please help me with the SQL statement to do this job.



  207. Hi Pinal Dave. I remember you from your great tools developed with ColdFusion. I’m a CFer with a little bottleneck in SQL Server and XML. Look this, please:
    – I have an XML file with deep structure(nodes). What’s the best approach to import this XML data into a SQL Server 2000 table? What do you suggest? I don’t have success using OPENXML with a path to that XML file. Cheers


  208. i have installed sqlserver 2005 from on my server

    and i have installed sql server 2005 client componets on client side.

    and i was unable to connect to the server from client side.

    i have run queries through command line (client side)from there i was able to connect to the server side system databases but i was unable to connect to the user databases

    1. i have enabled all the protocols except named pipes in sql server 2005 network configuration

    2.i have enabled all the client protocols except VIA in sqlnative client configuration and i have given default port 1037 in tcp\ip ( the similar settings i have made on sql server side.)

    3. i have enabled remote connections in surface area configuration and i have restarted all the services also

    and the similar error come repeats always:

    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. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server, Error: -1)


  209. Hi Pinal,

    Your website has worked wonders for me so thanks for that. My question for you is:

    Using cursors (or nested cursors), how can I display all the columns with a clustered index across all tables, all databases within one server?

    I have the following code that gives me all databases within a server:

    declare @DBname nvarchar(128)
    declare @testCursorForDB cursor

    set @testCursorForDB = cursor fast_forward
    select name from sys.databases with (nolock)
    where name not in (‘master’,’tempdb’,’model’,’msdb’)

    open @testCursorForDB
    fetch next from @testCursorForDB
    into @DBname

    while @@fetch_status = 0
    print @DBname
    fetch next from @testCursorForDB
    into @DBname

    close @testCursorForDB
    deallocate @testCursorForDB

    I have the following code that gives me the table name and column name where the cluster index exists:

    select object_name( as TableName, as IndexName
    from sys.sysindexes as i
    where i.indid = ‘1’

    However, the above code only works for one database.

    Would you happen to know how I can combine those 2 tasks into 1 using some kind of nested cursor? Or perhaps a better way of doing it?

    My end result would have the database name, the table name & the column with the clustered index for that table.

    Thanks so much…you rock :)


  210. hi pinal,

    i have gone trough it was so interesting

    and i am new to this site

    1. i want to create a index for a table. can u send me with example
    2. i want to create a custom control can i know the procedure how to register it


  211. Hi Pinal,
    I want to export a database from one server and import it to another. The destination server will not allow me to restore from a backup it did not create(, so i need to generate scripts. I read your blog on scripting the db, but how do i script out the data ?


  212. Hi there Pinal.

    I am creating a simple application to record employees information. In the database structure under the “Eployees” tbl I want to keep a unique alphanumeric string which will be self generated from the first letter of each new added employee LastName and a self increasing integer.

    For example:

    Smith—-> S001
    and when a new record is added it will take the below shown sequence
    Svenson —->S003 (it checks and finds the last record starting with S and adds +1 to generate the next integer that will follow the letter)

    If you have something to propose on that I would be great full.
    Best regards,


  213. @jahir,
    Your database server name must be incorrect or database server is not running. Check services.
    You can also search for solution.

    You can use CHARINDEX or LIKE function to search in text field.

    You should use CAST or CONVERT function.
    You can also search for solution.

    You need more more space on your harddrive or filegroup.

    @jayamohanan MR,
    There is no specific answer to your query. You can send me details of the SP and I can give it a look.

    Not a good idea.

    @Sonal Dave,
    I have not heard of it.

    I will send you email.

    @Martha McPherson,
    Index is good option.
    You can read my articles on Index Tuning

    It all depends on what you are queries are. If you queries have joins on composite key then it is advantages, vice versa for single column primary column.

    Read this blog regularly.

    Sure I will write on soon.

    I will research it and reply to you.

    Read this blog regularly.

    Use ISNULL.
    I will request some of my smart reader to answer this question.

    Sure I will write article about it.

    You can do that in SQL SERVER 2008 but not in any previous versions.

    @Amrish Patel,
    You can check in buffer. I will write article about this soon.

    Search for solution.

    @Ashwini Kumar,
    You should read articles here :

    @Bala, @Swathi,
    Need more information.

    Server must be busy when you ran your query.

    Replication or mirroring can fix your problem. I will soon write articles about replication.

    @Ashutosh Gupta,
    Sorry you are out of luck. You need back up for sure. Read articles here :


    Search for solution.

    You need to install jet drivers.

    need more information to answer your question.

    @Marco Antonio,
    I am using SQL Server 2005 as it has XML as native datatype.

    @arihant kothari,
    Search for solution.

    @Arthur Garcia,
    You need to link servers and use insert into select.

    @To All Readers,

    Thank you very much for kind suggestions and words. I will answer everybodies question as soon as possible.

    Pinal Dave ( )


  214. Dear Dave,
    my database has 7.5 GB of the log size. How can I reduce its size by achieving the maximum efficiency ?

    Prompt me ASAP.

    Thanks in Advance


  215. hi pinal.

    i have gone through ur blog.sqlauthority and i have done all the settings and even through

    1.i have allowed remote connections
    2.firewall settings are also done
    3.pipes tcp etc were enabled still i am unable to connect to remote server

    but still i am getting this error
    can u please help me out

    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. (provider: SQL Network Interfaces, error: 26 – Error Locating Server\Instance Specified) (Microsoft SQL Server)

    can u tell me these instances settings


  216. Great blog – thx.

    How can I call an sp from within another sp and get its result (it returns a single value).

    Eg (pseudo code)

    proc Wrapper
    select @innerResult = exec InnerProc
    select ‘hello world’, @innerResult

    proc InnerProc
    select 1

    BTW I am running on 2005 but would prefer a solution that works on both.


  217. Hi Pinal Dave,
    I am vamshi SAP Basis actually we have an issue regarding the SQL Server 2005,We want to change the transaction log file to a different disk location. Now it is on the same disk as of data files. Can We do this through Management Studio, If so Please help me regarding this issue.

    Thanks in Advace


  218. Hi,

    I am a SSIS Developer.I am using Execute SQL Task.
    In this, I need to write a simple query that can retrieve each row in a table using a parameter.Please help me
    Thank You


  219. dear penal
    i have recently find your blog on net , ia hve some problem i have two table in one table i have four field in which station_name , std_code,phoneno_to,phoneno_from and second table have phone_no,std_code,station_name
    i want to update second table for station_name in which query will pick station_name from first table by checking std_code and phone_no between phoneno_to and phoneno_from if this condition satisfy it will update second table for station name in each row one by one but i am getting the error “single-row subquery returns more than one row.”
    please solve my problem i need help urgently pls .


  220. Hi Pinal,


    Thanks for continuing this great work. It would be nice, if you post an article on Common Table Expression with recursion.

    Thanks & Best Regards
    Chella Ganesh


  221. Hi,

    Nice to see you here with lot of database expertness.

    I am facing one critical problem here, I hope you could suggest me. I am trying to optimize a very huge database transactions having some trillions of records in it. Actually, our database is clustered accross multiple physical hard drives(24 hdd) connected with optical network because of terra bytes of data in it. Here, particularly, I am trying to reduce record retrival access time and batching some DML and DDL statements together. I have used various methods of SQL, PL/SQL and Java Stored Procedure optimization techniques, but it has resulted to take more time than existing SQL approach.

    Here the question is, Does PL/SQL or Java Stored Procedures takes more time than SQL statements? Is it because of the data is being clustered physically accross multiple hard drives? Please let me know if have any idea on this kind of performance related issues over distrubuted/clustered databases.

    Veeresh D.


  222. Hi,

    I have a question regarding sql reporting services using sql 2005( VS2005).

    Start End 10/20/2007 12:05:57 PM 10/20/2007 12:08:06 PM
    Duration = End – start = 00:02:09.6570000

    I need to display this duration as
    00:02:09.65 ( i need to round of that milliseconds to 2 decimal places).

    Any idea?



  223. hi
    this is keyur
    i want to create .mdf .ldf file from any data base without data

    help me
    i sure i will get positive response
    i dont want to generate a script so,.



  224. Hi Pinal,

    I am in an unusual situation, I am working on SAP, and in 22 days I have a client interview on SQL Server2000.

    Can you help me as to how should I go about it and where I can find material to study SQL Server 2000, any online tutorials or something.

    The only knowledge I have of SQL is the part of queries and not much.

    So can I get some recommendations from you as to how go about…



  225. hi pinal,
    while connecting to sql server 2005 i am getting this error.

    A connection was successfully established with the server but then an error occured during the login process (Provider :Tcp provider,error:0 -An existing connection was forcibly closed by the remote host.)(Microsoft Sql Server,Error:10054)

    can u help me out of this situation


  226. hi there,
    This is question for anyone who know the answer.
    i have a string
    “102 101 320 102 101”
    I have to remove duplicate out of that string. Take 3 char match as crieteria.


  227. i created this inline function hope it will help others.

    CREATE FUNCTION dbo.Remove_duplicate_instr
    (@datalen_tocheck int,@string varchar(255))
    RETURNS varchar(255)

    declare @str varchar(255)
    declare @count int
    declare @start int
    declare @result varchar(255)
    declare @end int

    set @start=1
    –set @count=4
    set @end=@datalen_tocheck
    set @count=@datalen_tocheck

    set @str = @string –‘123456789123’
    –SELECT REPLACE(‘abcdefghicde’,’cde’,’xxx’)
    –SUBSTRING ( expression , start , length )

    while (@count <=255)
    –while (datalength(@str)!=0)

    set @result=@result+substring(@str,@start,@end)
    set @str=replace(@str,substring(@str,@start,@end),”)

    set @count=@count+@datalen_tocheck

    –select @mystr as mystr

    return @result

    — how to use it select dbo.Remove_duplicate_instr(3,’123456789123456456′)


  228. viresh,

    it depends what kind of query you are writing, whether these queries are optimised or not. you can use query execution plan and check any table scan in there.
    1. If query is doing a table scan then you need to create index on those column.
    2. if you are using odbc connection it has different algorithm to generate the result which is takes longer than normal .net client.
    3. check statistics in the table make sure then are updated.
    4. try to make sure there is no blocking going on.
    there are lot of other factors could be making it slower, i can’t till i see.

    Clustering of database just g ive you virtualization of sql server instance it has nothing to do with sql server performance.

    clustering of disk shouldn’t not be an issue check with your dba or system administrator what kind of raids has been used and what raid data has been stored.


  229. Does anyone know how make a backup of a serie of databases in Standby-ReadOnly state? The reason: I’m using Log Shipping Microsoft Solution for replicate info.

    A SnapShot isn’t a possible solution.


  230. Hello,

    I have my SQL 2005 database on a remote server. I do not have access to the actual database file itself. Is there something I can run from my local machine to create a copy of the database to my local machine, possibly automatically every day?

    If this is asking too much, I apologize for my ignorance, but any help would be most greatly appreciated.

    Kindest regards,
    Robert Tilley


  231. Hi Pinal,

    As you are a champ my question would sound little funny to you, but i have a task where in my database we have 140 datafiles in which 60 would be index data files
    so my requirement is they want me to delete or remove unsed datafiles from the database, and as am very new to sql i dont know how to do this and to be honest to use google in correct way is also a challenge and a talent

    And 2 question which bothers me a lot bcoz in every interview they asked me :

    1) you created a query or store proc which was running good and very next day it started taking 1 to 2 hrs to execute what could be the reason

    2)you are able to execute your store proc and get the result in 1 2 sec and the same store proc when executed by other user is taking 1 hr on same network why?

    I hope its not that hard for you to answer.
    And i apologize if am asking too much question



  232. Hello

    How to prevent records from deleting accidently when we pass a command like delete from ‘tablename’. I want to get a message confirming to proceed or not in this condition.



  233. Pinal,

    I am currently frustrated with SQL2005 and it’s datetime capabilities. I have a couple of questions. My problem is how would I be able to determine the last day of the current or next month ( some months have 30 days and sometimes a leap year happens )? Also a very similar problem if I have a date of January 31 and I were to add 1 month to that date ( giving me a date of February 31 , an invalid date which can be recognized by the ISDATE( ) function ) how can I determine or receive a valid date of February 28 or February 29 depending on if it is a leap year.

    Thanks for the help!


    • SP needs recompilation unless you have Dynamic SQL embed into it. Otherwise, it just compile once and run many times
      The problem what sameer is facing is slowness in execution. Need to check what SP is doing .


  234. Sorry to double post but I found out the answer to my previous post. This line should do the trick. Thanks for a great website.

    SET @time = DATEADD( mm, datediff( mm, 0, dateadd( mm, 1, @time ) ), -1 )



  235. Hi Pinal,

    Can you please help me to write a recursive query?

    I have got a table WORKORDER. wonum and parent are the two columns in the table. A wonum can have children.Those children can have children, so on. if i am giving a wonum,it should display all the children ,their children and so on.

    Sample data in the table is as follows

    wonum parent
    7792 NULL
    7793 7792
    165305 7793
    7794 7792
    7795 7792

    7792 is a workorder,which has got children and grand children

    7793 is a child of 7792
    165305 is a child of 7793
    7794 is a child of 7792 and
    7795 is a child of 7792

    When I give the 7792 in the query,it should fetch all the children and grand children,etc. ie 7793,165305,7794,7795

    How can we do that?


  236. hi dave,

    while writing a stored proc, i came across a scenario where ther were 2 insert statements. The first statement was a legal insert statement, whereas the second one was designed in such a way that it would throw an error as a result of which the first statement got committed and obviously the second didnt.

    what i need to know is that is there any means by which i can rollback the first insert statement?


  237. hi dave,

    can i get tsql script that generates restore commands for the latest backup into a notepad, …… that if i run those restore command after copying backup file to another server it should be able to restore.
    T sql script which automatically restore latest backup.


  238. Hi,

    I have a problem with scheduled backups. No log is maintained. Any backups that call xp_sqlmaint or rather anything that is been executed using xp_sqlmaint procedure hangs.

    Could someone please help?

    This is really crucial as backups are not happening.



  239. Hi Dave,

    Sir, I am working on large scale of database. When i transfer database from online to my local sql. There is lot of procedures , triggers and constraints. It is failed to transfer on my local server.

    To resolve this problem I do following steps

    1. I just create database.
    2. Alter table with no check in implement triggers, constraints like foreign key, and primary key

    then procedures

    Am i doing right thing or there is some other way. I’m also try to replicate but i want to confirm that is there any problem to replicate data


  240. Can I create a view which will refer some tables from local database and some tables located in different location
    (database server which is located in clients machine)


  241. Hi Pinal,

    My organization is currently migrating about 35 databases from SQL 2000 to SQL 2005. Most of the databases are small with the largest being about 6gb. These databases are not very transaction intensive. We are about half way through as we started the project a year ago. We purchased new hardware for this SQL 2005 so it is a side-by-side migration and it has been going well so far. I made one oversight which I think will come back to haunt us down the line. I installed Windows Server 2003 Standard R2 Edition and of course now I am considering putting more RAM in the server. We have 4 GB of RAM in the server and of course Standard Edition will not address more 4GB of physical ram. I have not seen any memory pressure yet but as databases are migrated over the amount of physical RAM drops. My buffer hit cahce ratio and page life expectancy performance counters are fine so far. The server is a brand new HP DL 380 G5 with with tons of disk and processing power, but only 4GB of RAM. I can’t migrate to another server with more ram obviously.

    1. What would you do in a situation like this?

    2. I could do an in-place upgrade to Windows Server 2003 Enterprise R2. Has anyone attempted an in-place upgrade? I have no applications
    on this server, just SQL 2005 Standard.

    3. Would the /3gb switch be an option? I have heard mixed reviews on this one.

    As far as database optimization, %75 of the databases are third party so we have little flexibility in tuning them or redesigning



  242. How to get WEEKS and months listed in a query by passing the from date and to date


    actually am having one problem regarding weeks and month
    what i want is .if suppose am passing from date and to date as a parameter.. then i want total weeks listed
    like 02-12-07 to 08-12-07 ,09-12-07 to 15 -12-07 in one column as a week same for months as well



  243. Hi Pinal,

    I’ve created a table with 2 columns (Myfilename varchar(255) and the myPicture Varbinary(max)).

    I was able to upload a file into the table using Openrowset(Bulk ‘E:\temp\myTest.jpg’,SINGLE_BLOB) AS [file]… worked like a charm.

    I now, need to retrieve the file from the table and put in a folder using TSQL. I’ve been searching everywhere without any success…

    Would you have any idea? It would be greatly appreciated…

    Thanking you in advance.
    John G.


  244. Hi Archana,

    You can get the number of days between two entered dates by using datediff function
    SELECT DATEDIFF(day, ‘20071201 23:59:59.997’, ‘20071231 00:00:00.000’)
    this function will give you number of days which you can divide by seven to get number of weeks.
    To get number of months you have to divide by 30 days.
    However for the month of february, you need to calculate the number of days and divide by 28.
    That will give you number of months.

    Hope that answers your question.



  245. Thank u shreyas n good morning to u

    .. whatever u told that will give me only no of weeks i.e . 4 or 5
    n i want those listed in list
    like 02-12-07 to 08-12-07 It means 1st week
    09-12-07 to 15-12-07



  246. Hi Archana,

    A very good morning to you.
    Actually your question wasn’t quite clear to me before and even now.

    Select datepart(ww,’2007/12/12′) –(returns 50)
    Select datepart(ww,’2007/1/12′) –(returns 2)

    The above function return you week number if a year.

    However, if you want to get week number in a month, you would probably require to write a function which returns week number of the month.

    Let me know if that satisfies your question.
    good luck.



  247. @Anil,

    Sure I will get in touch with you.


    You can truncate and shrink database as well backup on backup device.
    For more help :


    Try to access server using IP address.


    Sure, you can use OUTPUT parameter.


    Please search for solution here :


    I will sure write about SSIS soon.


    Glad you liked it.


    Your subquery is returning more than one records, please look into that.


    Read this blog regularly and read lots of SQL.


    I already have posted article about CTE on this blog.
    Please search for solution here :


    Sure, I will write about that to you.


    It can be rounded to only 3 miliseconds


    Create empty database.


    Need more information.


    I already have posted article about your issue on this blog and thank you for sending solution.
    Please search for solution here :


    Everyday more tahn 4 hours


    I will write about this very soon.


    Please search for solution here :


    Please search for solution here :


    I wrote about accidental deletes on this blog already.
    Please search for solution here :


    I read for coldfusion.


    SQL Server 2008 has new DATE datatype. Very helpful.


    You have to use error handeling in SQL Server.
    Please search for solution here :


    Sure you will have to use SQLCMD to create text file and use it.


    I will send you email soon about this.




    Please search for solution here :


    I do not think that is possible.


    Good explanation, I will send you email with detailed information.


    You can do it using sqlcmd.


    I answers questions as soon as I can. Thanks for your help and wonderful comments on this blog. Please participate more.

    Pinal Dave ( )


  248. Hi..

    I have also received the Error “Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”
    could you please tell me how to fix this error. I am using two diffeent VB Applications whis use same SQL Server 2000 database.
    I have found the one process which became a victim. Could you please let me know where to set up the DEADLOCK_PRIORITY as i am new in SQL server 2000. I really appreciate your help.



  249. Sir,

    i need your help,

    i had created a table student, studentid column with alpha numeric primary key with varchar datatype

    now my problem: i want to sort the student id accroding to studentid like


    but i’m getting the sorted result like this, how to overcome this problem, please guide me sir



  250. Sir,
    I am Vinoth just i need a help from u i am trying to create a database with username and password in SQl using query.

    please help me in this problem and i need query to create databse with username and password.



  251. Sir,
    I,need the Storedprocedure related answer.
    How to write a single storedprocedure for all controls in a form.

    Thanking You


  252. hello sir , i want to become an sql server DBA . please suggest some books or some institutes for guidance.i’m a beginner and new to sql server


  253. Hello sir,

    I am having some confusion, related to Select and update statement in term of performance. pls help me out in this topic. I am explaining one problem.

    I am using MySql 5.1. Now the problem is. related to update the record after inserting. what i have to do is inserting the record in database but one of the filed is using the Auto generated ID value. for that we have to insert the record first and then have to update that particular field.

    For this we think another way i.e. fetching the id max + 1 value from the table and used it for inserting.

    pls suggest us what is better and safe way in term of performance.

    Thanks and Regards,

    Pinkesh Maheshbhai Khatri


  254. Hello Sir,

    I am using Sqlserver 2000 and i faced a problem.

    I have no latest backup of my database.The Database
    is now on suspect mode and the logfile is corrupted.
    How can i recover my database.

    Pls tell me the procedure in detail.



  255. Hi Pinal! Recently i have know about SQL Server Management Objects. I want to create table using SMO in sql sever 2005 with primary key & foreign key. can you help me in this regards?


  256. Hi Senthil,

    I understand your problem. However, if would have been better to create 2 fields for using Student Id.

    Example one field as just numeric (auto increment) whereas other as alphanumeric.

    So Student table can be as ID (Character), ID1 (Number), StudID (Character) Name (Character), etc.
    ID can store “STU” whereas ID1 can be 1 and StudID (ID+ID1) = (STU1)

    In this case, you can either select StudID as primary key or ID1 as primary key. In fact you won’t even need to create StudID as for display you can always show it as ID+ID1 as explained above.

    Let me know if that suits.


    Let me know how that


  257. Hi Pinal,

    Impressed with your knowledge.

    Need help with this. I have connected to teradata as a linked server from SQL Server 2005.I have done this using OLE DB Provider . I can see the table and views and the test connection succeeds. But When I run any query it gives me. Cannot open schema rowset Msg 7311 error. Its a 32 bit windows server 2003 machine running 32 Bit Sql Server 2005 and the teradata linked server is also a 32 bit server.

    I haven’t found a solution anywhere. I will greatly appreciate any help.



  258. Hi Pinal and all users facing same problem,

    I was able to solve the problem for maintenance plan backups not happening.

    After lot of investigation, what was found that sqlmaint.rll was the main culprit. This file was found missing in a specific location where sql looks for.
    After copying this file from another location it was all Ok.

    Pinal: Thanks for your help anyways



  259. Pinal,
    I am trying to use a charindex to find either a space or a colon within a string. From there I am trying to get the string that is the shortest in length, but I keep getting an substring arguments error or null values for the colon based srting when I attempt to run my SQL. Do you have any suggestions?


    SELECT fullname, CHARINDEX(‘ ‘,fullname) CountToSpace, CHARINDEX(‘:’,fullname) CountToColon,
    (case when ((CHARINDEX(‘ ‘,fullname) = 0) and (CHARINDEX(‘:’,fullname) = 0)) then fullname
    when CHARINDEX(‘ ‘,fullname) > 0 and CHARINDEX(‘ ‘,fullname) 0 and CHARINDEX(‘:’,fullname) < CHARINDEX(‘ ‘,fullname) then SUBSTRING(fullname,1,CHARINDEX(‘:’,fullname)-1)end)


  260. Hello all

    I am having a problem on database.

    In details: I downloaded 1 database from online server and restored it on my machine . but all the tables and stored procedures created in that database is under 1 user
    like select * from abc.tbl_Login
    and i am logging to the sql server 2005 using usename=sa
    and password=abc123
    so while accessing tables or stored procedures i have to prefix it with username like abc.tbl_Login ….am not able to access it by only using select * from tbl_Login

    SO is anybody has any solution for this then plz forward me
    otherwise i have to change whole code by using like abc.tbl_Login



  261. hello sir,
    i want to use MAX function in insert query, how can i do that?

    insert into xyz(id,name) values(max(id),’nirav dave’)

    i want to use max(id) in insert query coz i don’t want to fire special query to find max number of id,

    plz. help me.


  262. Hi Pinal,

    I am working as a software developer on the .NET 2.0 platform and SQL Server .My front end skills are good but I want to enhance my database or back end skills.
    The reason is that I have done my BE in Chemical Engineering and then I joined an IT firm. I studied ASP.NET and C# of my own.
    The book I refer for these topics are C#: The Complete reference and ASP.NET unleashed.
    Can you suggest me some books which would help me in enhancing my T-SQL programming and SQL Sever 2005 programming and which cover the basics.

    Thanks and Regards,

    Imagination is more important than knowledge –>Albert Einstein


  263. hi pinal,

    i have installed sql server 2005 successfully . but when i was logging in

    i was getting the same error

    A connection was successfully established with the server but then an error occured during the login process (Provider :Tcp provider,error:0 -An existing connection was forcibly closed by the remote host.)(Microsoft Sql Server,Error:10054)

    next i have seen tht the services but the services has not been started.i.e

    ms sqlserver
    ms sql agent

    when i am going to start these services it is giving me this message

    “”some services stop automatically””.

    and the services r back to their normal position(i.e in stop position)

    so please pinal help me to solve this situation


  264. Dear Shri Pinal Dave,

    To introduce my self, I am Rajesh Thakkar, an ex bank officer and an ex-employee of Virmati Softwares a Banking Software development company, I worked with them as Domain Expert and Business Analyst.
    I belong to Ahmedabad and have migrated to Chicago(US) last year and am pursueing studies of SQL Server 2005 as per a recruiter’s advice.
    I came across your site and has found it extremely useful.
    I some times find some queries extremely difficult to formulate. Can you give me some web resources which I can refer to, to formulate queries or can you support me to formulate correct queries?

    Thanks in hopeful anticipation.



  265. Hi Pinal,
    Rupesh here I am working as a DBA.
    I am having one problem related to Performance…
    My database size is about 20GB Testing Database In sql server logs I m finding this Error:

    SQL Server has encountered 130 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\SQLDATABASE\SPSTAGE2006_Data.MDF] in database [SPSTAGE2006] (5). The OS file handle is 0x000007B8. The offset of the latest long I/O is: 0x000000ed2a8000

    This seems to be a hardware issue:

    and retrieving of simple select statement is also taking so much time sometimes…
    Please give me some solutions ..



  266. Hi,

    I would like to get a list of all queries related to a database using SQL server, instead of searching every file in that folder for a query.

    How do I do that? I am new to the project, so how do I search and list all the queries that have been created on it?

    Any help is appreciated.


  267. gud day sir, im a newbie in using MS Sql 2005, ive tried using the Rebuild All Indexes using the enterprise manager, by right clicking the Index folder then choosing the rebuild all option, but some tables with (non unique, unclustered index) fragmentation level does not drop to 0, what am i doing wrong? thanks.. can u email me?? thanx. God Bless U


  268. hi pinal, i have a stored procedure that have some input as well as some output parameters. now i want to list following information about a particular stored procedure.

    parameter_name, data_type, length, parameter_direction.

    i have tried sp_help system stored procedure. it gives all the information except ‘parameter direction’. can u help me in this regards?


  269. hi,
    i have one problem, i am using sql server 2000.

    my sql server database is on remote location. and i want to take backup of the database on my local machine by using enterprise manager, can it possible? if yes then please tell me how can i do this.

    with regards


  270. Hi Pinal,

    I am new to SSIS. I am attempting to read the system variable PackageName inside a Script Task. I get the following error message:
    “The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.”

    Can you please shed some light on what I am doing wrong?



  271. Hi Pinal / anyone who passed this stage,

    I want to migrate from sql server 2000 to 2005. as far as databases are concerned, i can, but how to migrate DTS packages?

    Can you guide me the best and easiest way to do it ? I know there is DTS wizard / transfer.




  272. Dear Pinal,

    I need your help on some sql time outs. I just joined this company and they are having several time outs on sql procs. I checked the procedure and did some minor updates such as set nocount on and off, etc.
    I checked the error log and the error message is;

    ‘Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.’

    Can you please post couple of option to solve this issue. Thanks


  273. Legacy style joins (*= or =*) with SQL 2005.

    I have inherited some stored procedures that were written a while back by some of our off shore resources and migrated to SQL 2005. Some of our customers, run into issues with the left/right outer join pages through our application and we found the issue to be using these joins (*= or =*). I want to know if it is true that SQL 2005 stopped supporting these legacy joins? I searched MSDN and google and couldn’t particulary find any article to reflect the same. I may have searched using wrong key words so if any one has information on this that will be highly beneficial.

    Thanks much


  274. Hi Dav!
    you said don’t store images in databas. The database will go slow performance.
    Please can you send me how can insert images(URL) in database and retreive into datagrid using



  275. HI, I am looking at the remote database administraton and waswandering is there is a good resource for the scripts that i need to run and report on to ensure the helath of the server or database.
    thanks in advance


  276. Hello pinal
    i m designing a database for matrimonial site in which main consideration is given to search criteria(more than 15 columns are used).so i want to know should i have to design a single table for the whole information.if i normalize that table then multiple tables are used for searching n more joins are i want solution according to performance.

    thanx in advance


  277. hi pinal,
    i want to change one column based on codition if the value of column is a then it shd get updated to b and if b then into a
    how shd i do it


  278. I would like to design one database (OLTP) for my company and database size is going to be 300 GB and it will grow up to 900 GB in another five years.

    I need your suggestion for creating log file.Generally,I used to create single log file.Another DBA in my company likes to create multiple log file for that database.What is your suggestion for single or Multipe log file?Please advice.


  279. Greetings Pinal, I enjoy reading your blogs. I have a newbie question that I can’t seem to get a good answer for.

    If you use a select distinct on a 10 column table with two columns used in distinct, but need the resultset to include an index so it can be used to drill down in an app, what’s the best way to accomplish that? I jave found a couple of convoluted ways to do that, but they invariably are very long and complicated and sometimes produce incorrect results. If I include the index, I get every record as expected but not wanted.

    You provide clear and detailed answers, thank you!


  280. I am looking create a DB build script for my SQl Server 2005 database, execution of this should create table, insert data, execute a set of SQL commands that fetch from cursor and insert into a table etc.
    I would like to place all my create table statements in 1 create.SQL file. I would like to place all my inserts realted to table1 in insert_table1.sql etc.
    In my DB Build Script, I would like to make the calls to create.SQL and execute. Similarly, I would like to make the calls to insert_table1.sql and execute it.
    How can I accomplish this? In Oracle, I would use @create.SQL and @insert_table1.sql. Any help is greatly appreciated.


  281. Hello Pinal,

    I have a great confusion regarding what to use “uniqueidentifier or identity ” as primary key.

    My Database contains apprx :70 tables.

    Should i use uniqueidentifier or identity as primary key across my whole database.


    Should i split i mean in some tables uniqueidentity as primary key and in some tables as identity.

    Kindly revert back on this to clear my confusion.



  282. Hi Pinal,

    It was wonderful to meet you in person yesterday. Hopefully, you will visit our company again and teach us some more.

    Milind Karthik


  283. I have package on sql server 2000, when ever i execute that package it throws an error saying “Login failed for user(null). Reason: Not associated with a trusted sql server connection” Can you guys please suggest me what should i do regarding that the package runs successfully. Please helpme.



  284. Hello Venkat,

    This could be problem with your ODBC connection.
    Make sure that user name and password is entered correctly.

    Let m know if that solves the problem.
    Also you could try running the package in query analyser step by step for testing purpose.



  285. @Devdatta,

    Read regulalary.

    @Nitesha Patel,


    Sure I will send you email describing your answer.


    I need little more information to answer your question.


    Search your question at


    Read regulalary.

    @Pinkesh Khatri,

    My experties lies in SQL Server.


    Sorry. There is no solution if you do not have complete backup.

    @Samir Nigam,


    This is interesting problem. Let us discuss through email, once we find solution we will post it here.


    I will follow up with you in email. NULL values are always interesting to play with.


    You are facing problem with Schema. You need to give permissions to users to access schema.


    You will have to use GROUP BY clause.

    @Prashant Sharma,

    Read regulalary. I post lots of good book review.

    @Arihant Kothari,

    What is the server you are using with.


    Not sure what is your question.


    Sure. I am always willing to help.


    It seems like error with operating system or Hard Drive might have corrupted.


    I have already written about this topic on my blog post.
    Search your question at .


    I will send you email.

    @angelo conti,

    You need to defragment your database.
    Search your question at .

    @Samir Nigam,

    I will try to write about that soon on this blog.


    You will have to connect to remote admin using enterprise manager.


    The best way to upgrade your packages is with the Package Upgrade Wizard.


    You need to provide more information why it got into suspect stage?

    @Fahima Shaffi,

    Need to know more detail about query.

    @Srinivasan Prasanna,

    Search your question at .


    I will send you email.


    Search your question at .


    Post your job request at


    Use CASE statement.

    @Boby Khan,

    Create log files on SAN.


    Sure, I will post about this on my blog very quickly.


    Sure, I will post about this on my blog very quickly.


    I will send you detailed answer in email.


    I have already written about this topic on my blog post.
    Search your question at .

    @Milind Karthik,

    It was my pleasure as well.

    @Mike Dymtri,



    We have moved to SQL Server 2005 so I have no way to reproduce your error.You might have entered incorrect username or password.


    Thanks for helping blog readers. Appriciate your help.

    Pinal Dave ( )


  286. hi,

    I have a database say last year database. Now
    I want to create a new database , new tables, new stored procedures and new udf.

    I created new database using SQLDMO.
    I created new tables in new database using code

    Now I want to create stored procedures and udf’s programitically using visual basic and ado code.

    For this I tried following options :
    1. I generated a SQL Script of my custom stored procedure
    in a text file. I tried to access the text file and using exec command in a new stored procedure I picked the text file data which will create my custom stored procedures.
    2. I tried to put all stored peocedures create method in
    one new stored procedure and call that ‘CREATE_SP’ stored procedure which will create my custom stored peocedures.

    But both the attempts were failed.

    Please guide me on this.



  287. Hi, Pinal !

    I am working on MS SQL 2005 and have a question on using TOP issue while creating views. A view (just about 100 records) is created with TOP (100) PERCENT and order by, say LName. The output did not look ordered. I changed to TOP(214147483647) and the output came out in ordered. But I came to know the system is getting slow. Please explain of how the TOP(214147483647 worked and is this created a cache that make the system slow or what? Do you know how do I ordered the view by LName?

    Your response is greatly appreciated.



  288. Hi Pinal,

    Im new at SQL and at my job I have been asked to create a SQL-query to create more than 250 databases of a single query. This databases are mostly for testing purposes.

    Thanks in advance,



  289. hello pinal
    i have done orcale 9i database administration and i m new to sql server database so i wana to know about the security through logins.means how to create then n assign different priviliges to them n wt role should be define to them for only selection of data.n how to define schema to them so that they create objects in their schema.i need it urgently.



  290. Hi Pinal,

    I have a question about getting the path/folder name of where my backup is going. For example, when I go into the maintenance screen, I can see that I am creating a sub-directory for each database. The next line is the folder. In my example it is ‘H:\MSSQL\Backup’. I am writing a script to monitor the size of my disk and the size of my backup to make sure that I have enough space on the disk to preform the backup.

    how in the world do I get the H:\MSSQL\Backup via SQL or a stored procedure.

    Thank you in advance.


  291. hi
    in my comp two applications are there one is desktop application and another is web based application.both database are in sql server 2005.
    i wana to know is it possible to make a common database for that so that they can communicate with each other.

    its very very urgent

    so please reply as soon as possibe



  292. Hi Pinal;

    I have quick question if you can reply me back soon as possible that will be great.

    What is database schema, and how do you define this schema?

    How do you merge to database in to one database? Do you have to define schema when you merge these to database in to one database?

    How do you merge two tables in to one table? Do you have define schema when you merge two table into one?

    Thank you in advance for you help.

    Rahul Patel


  293. Insert in Chunks..

    Can You please give me an example in sql 2005 on how to insert data in chinks to reduce locks as well as not compromise on performance..

    –insert in chunks

    insert tbTest(Id,Fname,Amt,……….)
    select x,…… from tbSource


  294. Dear Pinal

    I am a software engineer from chennai, India and have two years experience working in SQL 2000. My job nature is mainly handling the business loggic of our prjoect through stored procedures and triggers. Now i am planning of doing a certification course in SQL which would nehance me technically and give me better job perpectives. What certification do you suggest. Please help. Waiting for your response.




  295. Pinal,

    I have question

    We have interface which brings down few tables from web database to our local database, in this table some of the columns are bit columns, my question is if some values in this column are null then what it will bring down ‘True’ or ‘False’ or Null.



  296. Hi, Dave

    I want information about replications I have to network LAN-WAN-LAN one LAN end keep server to update data in data server and also same server have applicaton to service from the request. I just want make replication database server to keep other end of LAN and it is syncronized data automatic when transaction update . So how i can do.



  297. Pinal,

    I want to post a review of your site in our blog (

    I am really impressed with your site. I think it is better than the BOL in many aspects. Your site needs to be advertised in as many channels as possible. And I would love to do that. As you may already know, I have your site in my blogroll.

    Please email me the info and the highlights of your site ASAP.

    Marlon Ribunal, DBAdmin/Linked DBA Group Manager


  298. Hi Mark,

    Firstly, Go through the syntax for creating database….

    Now, what you could do is write a stored procedure wherein, you create database test(i)……..

    Declare i as variable and loop the create database test(i) till i reaches 250, in your case.

    Let me know if that works.



  299. Hi Sonal,

    If they are bit columns then they will not give true or false.

    Bit datatype always can be 1 or 0. So while creating table if you specify datatype as bit and accept null then if you query result as
    select * from table where column = ”
    then it will return you all ‘0’ values.



  300. Hi pinal,

    Now i am working in java with sql server.
    but i am very intresting to work in sql server.
    so i have plan to switchover from java to sql server.

    what i have to do now..
    suggest me.. please….



  301. i have table has 3.5 milion record and want to do some operation on it and i alwase have problem with time and speed
    wark with mssql 2005 and amake application with microsoft c# 2005
    pleas i want some information to increas performance of mssql 2005 more and more times
    thank you


  302. Pinal,

    Have you ever seen an error such as I see in my Appliation log on a Windows 2003 server?

    Sqllib error: OLEDB Error encountered calling IDBInitialize::Initialize. hr = 0x80040e4d. SQLSTATE: 42000, Native Error: 18456
    Error state: 1, Severity: 14
    Source: Microsoft OLE DB Provider for SQL Server
    Error message: Login failed for user ‘NT AUTHORITY\SYSTEM’.

    The client also has errors in their application of WindowsFormsParkingWindow – Memory could not be read.

    Any help would be appreciated.

    Thanks, Mark


  303. hi dave
    why its not possible to exist two identity columns that will be auto incremented in a single table in sql server 2005

    thanks in advance


  304. Hi Pinal,

    Your web provides a lot information about MS SQL and it is really helpful.

    Could I ask you a question about replicating a new stored procedure automatically? Is there any way we could setup to replicate the new stored procedure automatically without add it to a new articles manually?

    Thank you


  305. We are having some problems training our developers. Consequently we have tables dropped and stored procedures changed without the proper controls on the process.

    We are using the new DDL trigger to track changes, which helps. But now we would like to programmatically script out all databases on all servers so that we can easily recover from the unexpected dropped objects. We have 11 servers with about 400 databases.

    I appreciate any suggestions that you might have to develop a way to do this.


  306. Hello good noon to all,

    I have following query:

    I want to remove all the triggers and identity property of all tables from specified database in one go

    Ex. suppose my database ‘Test’ consist of 10 tables and each one is having identity property and triggers defined
    n i want in one go all triggers and identity property should

    Is it possible?? if yes then how

    Thanks in advance,


  307. — Get the script to drop all the tables


    — Execute the script

    When you drop the table , all the triggers and identity property drop with it.

    Satish Shrikhande


  308. Hi Pinal,

    We are using SQL Server 2000. Some one is deleting record from one important table.

    We wrote on e trigger for getting the deleted data.

    Can I able to know which machine ID the particular query is executed?

    Pls advice me to proceed further?



  309. Hi Pinal,

    I have a database on SQL Server 2000 and recently we moved it to SQL Server 2005.
    While running some queries we found that we are not getting the same results in SQL Server 2005 as in 2000 server.
    We ran the same query in both the servers but they returned different outputs.
    I am not sure why this is happening.
    Please advice on this


  310. Hi Pinal,

    I need to use split function in sql query same as .net split function.
    I am not finding any sql function for splitting particular column. So i decided to write use defined function for the split functionality. but I am not getting values in multiple columns after splitting the column


  311. @ Satish Shrikhande


    I want all tables plus data of those tables
    all i need just to remove identity property and triggers on those tables as i want to create copy of those tables with data into other databse depending on some condition

    Thanks for ur reply


  312. Hello Pinal,
    I am read u r articals regulraly
    and it will help me lot off
    thanx for that
    I wanna help for one sql query
    I want to select date of the current month’s
    Second and fourth Saturday
    Please help me
    I am waiting for u r reply

    plz help……..
    Its very urgent………..

    Thanx in advance…………….


  313. Hello Pinal,

    My database has a duplicate entries like 0123 and 123. I want to find such duplicates. How do I go about it? I tried for substring but that returns exact same entries not the once that I mentioned above.
    Can you help with this?

    Your articles are informative. Great effort!!

    Thank you.



  314. dear pinal,

    after a long time i have got this error on sql server 2005 . whenever i use to attach database or detach database always the error use to be

    “Could not load the DLL xpstar90.dll”

    what does it mean?

    and when i have seen the services of sql server 2005 sql server agent was stopped i was starting the services but it was showing me that some services stop automatically when there is no use .

    so please pinal can u help me out of this situation.


  315. Hi Pinal,

    I am read u r articals ,it will help me lot off……….

    thanx …..

    I wanna help for one topic.

    I want to run multiple “*.SQL ” files at a time (like Batch Process) , it’s Possible without using any third party tools ?

    Please help me

    I am waiting for u r reply

    Thanx in advance…………….


  316. Hi

    I have a database in which one of the table is having huge data.i want to create file group for this table based on date range like for jan 1 file,feb another.. like this.I am inserting data into this table using SSIS packages.So how do i handle this situation.. LIke from packages how can i check the constraint and create a file if required .Is it possible or not.Is there any work around if it is not possible from SSIS packages to create files.

    thanks in advance


  317. Hi

    A third party system we work with has twelve identical tables. Lets name them INFO1 to INFO12.

    I would like to write one (1) stored procedure which, with some kind of case statement, is able to join to either of the tables. Some kind of “JoinWithThisTable” variable is probably necessary.

    select, i.something
    from person p
    left join INFO1 i on =

    INFO1 could be INFO7, or INFO9, or..

    Is there a “native TransactSQL way” – today I build up the query in a varchar and end the store procedure with exec(@query).

    Thanks in advance


  318. Hi Pinal:

    I am currently using all my data in xml files to run a GUI developed using MFC in visual studio 2005. My GUI is a stand alone application.

    How much effort required to convert xml files to port to sql server 2005 and write stored procedures and make it work?

    Could I still keep my GUI as stand alone application or do I need to convert to client-server architecture?

    Thanks for your time.


  319. Hi

    I want to copy database from one sql server 2005 to another 2005 how can i do that …. i am not getting copy database in my management studio so i have generate scripts but it did not help me ..I have given different database name …is this problem ?
    What should i do ??? please help me …

    Thanks & Regards


  320. hi Dave

    Please help me installing

    Analysis services & MDAC in SQL Server 2005

    Please provide me any docs available

    thanks waiting for your reply


  321. Hi Dave,

    I want to understand the logic behind the working of the following command in retrieving the 3rd Highest Salaried Employee. I mean what happens just by using a number (2 in this case) to match with the result of a sub query.

    Select Emp_Name,Emp_Salary From Employee A
    Where 2 = (Select Count(*) From Employee B Where B.Emp_Salary > A.Emp_Salary)


    Rakesh Verma


  322. Hi Dave,

    I have a Database in SQL 2005. having 12 tables.
    and now my requirement is i wanted to keep the history of all the table modification.
    I want to trace what all changes were made in
    this database on a particular date an time.
    A kind of report that will highlight all changes made to the Database on that date.
    How do I acheive that ?

    Please advice



  323. Hi Pinal,

    I am trying to set up Database Mirroring in SQL Server 2005 for Disaster Recovery Solution.

    The principal server and the DR Server are geographically separate locations. So with Asynchronous Mirroring will we have performance problems on the Principal Server during periods of high writes to the Database?

    If Mirroning fails from where does it start mirrroring the next time it starts?

    Also is Log Shipping preferred? I have been thinking of setting up Mirroring and also manually backing up the Tran Log and physically copying it over to the DR server. Can setting up manual copying of Tran Log affect Mirroring?

    If I do the manual copying of Tran Log each hour, how can I restore using some automated script at the DR Site?

    Can log shipping and Mirroring be set up simultaneously?


  324. Hi Pinal

    how can i store the result of an update query
    into a variable
    for example after i execute an update query i see a message “6 rows affected