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 SQLAuthority.com. 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 SQLAuthority.com, 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.

Search SQLAuthority.com

If you have any questions for faster response, Search SQLAuthority.com. 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” yahoo.com
pinal “at” SQLAuthority.com

About these ads

1,187 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,
      Vamsi.

    • 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 asp.net
      ..
      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)
      AS
      declare @Corporate_Id varchar(50)
      select @Corporate_Id=count(Corporate_Id) from onlineshop123.Corporate_Info_Table
      if @Corporate_Id =0
      begin
      select @Corporate_Id=@Corporate_Id+1
      end
      else
      begin
      select @Corporate_Id=max(Corporate_Id) from onlineshop123.Corporate_Info_Table
      select @Corporate_Id=@Corporate_Id+1
      end
      insert into onlineshop123.Corporate_Info_Table (Corporate_Id,
      Corporate_Product,
      Corporate_Description,
      Corporate_Address1,
      Corporate_Address2,
      Corporate_Country,
      Corporate_City,
      Corporate_State,
      Corporate_User,
      Corporate_Company_Name,
      Corporate_Company_Email)
      values(@Corporate_Id,
      @Corporate_Product,
      @Corporate_Description,
      @Corporate_Address1,
      @Corporate_Address2,
      @Corporate_Country,
      @Corporate_City,
      @Corporate_State,
      @Corporate_User,
      @Corporate_Company_Name,
      @Corporate_Company_Email)

      RETURN
      ————————–
      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
    Abi

  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
    (http://www.SQLAuthority.com)

    • 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!

    Regards
    Abi

  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,
    Kishan

  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?

    Thank
    Jaes

  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.

    Cheers,
    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?

    TIA

    Jochen

  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.

    Thanks,

    Regards,
    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!

    Aaran

  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 …

    Regards..

    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 http://msdn2.microsoft.com/en-us/library/ms187926(SQL.90).aspx 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,
    Pinal

  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.

    Regards,
    Harshal

  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.

    Regards

    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
    2:Approved
    1:Rejected
    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

    Regards.,

    Wilson Gunanithi . J

    • number of columns in a table

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

      or

      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..

    Regards,

    Wilson

  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.

    Regards,

    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,
    Pinal

  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

    Thanks

  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

    as

    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.

    Regards,
    Pinal Dave (SQLAuthority.com)

  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.

    Regards,

    Wilson Gunanithi . J

  37. Hi,

    Really you site is very nice. we guys are always make use of this … thanks for your service all the best….

    Mani…

  38. 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

    Thanks

  39. Hi Pinal

    Issue was resolved

    by using Surface Area Configuration tool enabled the xp_cmdshell

    My job went success

    Thanks

  40. 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 .

  41. 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.

  42. 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.

    Krsh

  43. 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

  44. 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…

  45. 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

    Thanks
    Vaibhav

  46. Hi Dave,

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

    Eg..

    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 ” search.pinaldave.com ”

    But , I didnt get anything based on this..

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

  47. Hi Dave,

    Can we move the attribute values from another attribute?

    Eg..

    Names—–Exam——–Mark
    ———————————–
    Wilson—–Maths——-null
    Siva——–Maths——-90
    Pinal——-Maths——-null

    Wilson—-Science——null
    Siva——-Science——null
    Pinal——Science——95

    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….

    • update t1
      set mark=t2.mark
      from table as t1 inner join
      (
      select Exam,max(mark) as mark from table
      group by Exam
      ) as t2
      on t1.Exam=t2.Exam
      where Name=’Wilson’

  48. 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 (SQLAuthority.com)

  49. 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.

    thanks,
    Umesh

  50. 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….

    Thanks…

  51. 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.

    Regards,
    Pinal

  52. 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

    Thanks
    Vaibhav

  53. 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.
    Abi

  54. 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.

    NAME GOODS
    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))
      go
      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’
      go

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

  55. 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.

  56. 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!
    Kevin

  57. 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?

    -Philip

  58. 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.

    Margie

  59. 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

  60. 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.
    Abi

  61. 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 (SQLAuthority.com)

  62. 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 .
    Thanks

  63. 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

  64. 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.

  65. Hi Pinal,

    I need to setup clustering between to sql server, could you please provide me information for that?

    Thanks,
    Rupal

  66. 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
    size.

    Please Suggest
    Thanks,
    Vaibhav

  67. 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

  68. 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.
    Raj

  69. 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.
    Eg..
    In Excel ,

    Reg.no——-Name———-Rank
    —————————————-
    10————Wilson———1
    20————Saran———-20
    30————Giri————–6
    40————Ram————-8

    After the conversion, In SQL ,

    Reg.no——-Name———-Rank
    —————————————-
    10————Wilson———1
    20————Saran———-NULL
    30————Giri————–NULL
    40————NULL————NULL

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

  70. Hello Dave,

    sorry…

    I m not creating the table via SQL..

    In DTS, table will be created automatically..

    Then how can I set the column’s datatype?

  71. I have to download a pgp encrypted file, file name will be like
    20070701.txt.pgp

    how can i decrypt the file in T-sql or or batch + t-SQl in sql 2005

  72. 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:

    ON MY LOCAL MACHINE EVERYTHING WORKS AS IT SHOLULD, BUT ONLINE WONT, PLEASE HELP

    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!

    Zdena

  73. 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.

    Thanks,
    Nivetha

  74. 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….

    thanks…

  75. 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.

    Sincerely,

    srijan.

  76. 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
    WHILE @@FETCH_STATUS = 0
    BEGIN
    update results
    set account_number = @account
    FETCH NEXT FROM accounts_Cursor into @account
    END

    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

  77. 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.

    Thanks,
    Sam

  78. 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.

    Regards,
    Wilson

  79. 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 ….

    Regards,
    Wilson

  80. 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?

  81. 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…

    Regards,
    S.saravanan.

  82. 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.

    thanks

  83. 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…….

    Regards,
    Varun

  84. 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 http://search.SQLAuthority.com 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.

    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  85. 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.
    Sarad.

  86. 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.

  87. 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 &
    Regards

    Mathew

  88. 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.

  89. 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
    DK

  90. 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

  91. 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.

    Regards,
    Pinal Dave (http://www.sqlauthority.com)

  92. 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

  93. 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.

    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  94. 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…

  95. 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.

    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  96. 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

  97. Hi Pinal,

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

    Regards
    S.Saravanan.

  98. 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.

    Regards,
    S.saravanan.

  99. 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

    Regards,
    S.Saravanan.

  100. 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
    Abi

  101. HAI SIR,
    HOW DO FIND LAST ROW IN ANY ONE TABLE WITHOUT KNOWN ANY COLUMNS IN THE SQL
    PLEASE GIVE SOLUTION FOR ME THIS QUESTION

    BY
    T.SURESH

  102. 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

    Thanks,
    S.Saravanakumar.

  103. 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,
    Suresh

  104. 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?

  105. Great paper Pinal. My name is Mike Biddle and I am the webmaster for the Detroit Area SQL Server User Group (DASSUG), http://www.sqlsig.org.

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

    Thanks.

    Mike Biddle
    DASSUG Web Adminsitrator

  106. 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..

    Regards,
    Wilson Gunanithi.J

  107. Hi Dave,

    Scripts are very useful …

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

    Thank you,
    arjun

  108. @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.

    @Arjun,

    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.

    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  109. Hello Dave,

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

    Regards,
    Wilson Gunanithi.J

  110. 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,
    Kiran

  111. 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.

  112. 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.

    Regards,
    Pinal

  113. 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?

    Regards,
    A

  114. 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
    ashok

  115. 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.

    Thanks
    Mooneer

  116. 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..

    Regards,
    Wilson Gunanithi.J

  117. 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.

    Sanjay

    • 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

  118. 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.
    Thanks.

  119. My question is simple, which one is faster:

    UPDATE Money_Master
    SET MonKey = 6
    WHERE MonKey 6

    OR

    UPDATE Money_Master
    SET MonKey = 6

    • 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.

  120. 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..

    Thanks,
    Vaibhav

  121. 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?

    Thanks
    Vijay

  122. 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.

    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  123. 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

  124. Wilson,

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

    Pinal,

    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.

    YOU ROCK SQLAUTHORITY!!!

    Kevin G.

  125. 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 ( http://www.SQLAuthority.com )

  126. 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

  127. 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

  128. 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.

  129. 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.?

    Regards,
    Hemant Nehete

  130. 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.

  131. @ 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.

    @Jay,

    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.

    http://blog.sqlauthority.com/tag/sql-backup-and-restore/

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  132. 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.

    Raj

  133. 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.

    Thanks
    Abi

  134. 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.

    Thanks,
    Vaibhav

  135. @Rajesh,

    Thanks. Read my reply here : http://blog.sqlauthority.com/contact-me-contact-pinaldave/#comment-9344

    @Vaibhav,

    Please follow the suggestion in the error message. Use RESTORE HEADERONLY and then follow up it with RESTORE VERIFYONLY. Additional Information is here : http://blog.sqlauthority.com/tag/sql-backup-and-restore/

    @girish,

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

    http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  136. 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)

  137. 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…)

  138. 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.

    rajguru

  139. @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 :

    http://blog.sqlauthority.com/2007/08/13/sqlauthority-news-author-visit-database-architecture-and-implementation-discussion-new-york-new-jersey-details/

    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 ( http://www.SQLAuthority.com )

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

  140. @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.

    @Pinal,
    LOL!

    Kevin G.

    PS. Do not edit this post.

  141. 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)

  142. 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.

    Regards,
    Wilson Gunanithi.J

  143. 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.
    1
    4
    8
    10
    15
    Output should
    1
    5
    12
    18
    25
    Pls respond fast as I need solution.

  144. 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.

  145. 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

    Procedure

    CREATE PROC Sample_SP
    @Sample_Param INT
    AS
    BEGIN
    IF @Sample_Param = 0
    BEGIN
    SELECT ‘ZERO’
    END
    ELSE
    BEGIN
    SELECT ‘NON ZERO’
    END

    END

    Statements

    IF @Sample_Param = 0
    SELECT ‘ZERO’
    SELECT ‘NON ZERO’

    Help would be very much appreciated…

  146. 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

  147. 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.

    Thanks

    • 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.

  148. 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

  149. 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.

  150. 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.

    Regards,
    Wilson Gunanithi.J

  151. 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.

    Regards
    Praveen

  152. 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 !!

  153. 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.

    Thanks

    Regards
    Praveen

  154. 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

    thanks

    Regards

    Praveen

  155. 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

    Mike

  156. 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’.

  157. 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.

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

    We want the variable contents to look like:

    David,Susan,Tommy

    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))
      go
      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

  158. 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.

  159. 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

  160. 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?

  161. Hi Dave,

    in my log getting below messege repetedly

    database starting ”

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

  162. 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)
    as
    begin
    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…

  163. 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.

    Regards,
    Hemant Nehete

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

    1]
    SELECT SMCOCD, SMCUCD, SMGCCD, SMCUNA, SMCTST FROM CUSMST

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

    Regards,
    Hemant Nehete

    • Both are same per performance wise. The Second Query looks more readable and is as per Best Practices. Always good to use second type

  165. Hi Pinal sir,
    How ru??

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

    http://nickbarclay.blogspot.com/2005/10/ssms-comment-keyboard-shortcut.html

    BUT
    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

  166. 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 !!!!!

  167. 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

    Regards
    Jay

  168. 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

  169. 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?

    Thanks
    Srinivasan Prasanna

  170. 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.

  171. 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.

    Regards,
    Hemant Nehete

  172. 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.

  173. 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

  174. 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

  175. 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.

  176. 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!

    Jani

  177. 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’

    GO

    USE DB_New

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

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

    GO
    ================================================

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

    Daniel – 02.10.2007

  178. Hello

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

    @Nyvi,
    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.

    @Jay,
    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.

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

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

    @Priya,
    I need additional information to answer your questions.

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

    @Maple,
    Please visit http://search.sqlauthority.com and search for datefirst and you will find the answer to your question.

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

    @Jani,
    Create tables and use SSIS to insert database.

    @Daniel,
    GO statement is resetting your query.
    Visit : http://blog.sqlauthority.com/2007/05/11/sql-server-explanation-sql-command-go/

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  179. 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.

    By

    Jahir

    • 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

  180. 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,
    Al

  181. 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.

    regards
    sarath

  182. 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

  183. Hello Dave,

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

    Eg.,

    c:\ppl\images\image01.jpg

    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..

    Regards,
    Wilson Gunanithi.J

  184. 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.

    Regards
    jay

  185. 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 char..like 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
    suman

  186. 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.

  187. 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.
    chandrakant

  188. 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?

    Thanks,
    Martha

  189. 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

  190. 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.

    Thanks!

    • 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

  191. 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?

  192. 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,
    Jim

  193. 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.

    Thanks
    Eric

  194. 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!

    Thanks
    ————
    sabeel khan

  195. 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 -

  196. 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,
    Hans

  197. 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
    CREATE PROCEDURE Find
    @StringToSearch varchar(100)
    AS
    SET @StringToSearch = ‘%’ + @StringToSearch + ‘%’
    SELECT DISTINCT SO.NAME
    FROM SYSOBJECTS SO (NOLOCK)
    WHERE SO.TYPE = ‘P’
    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

  198. 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, ‘ ‘ )

  199. 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.

    Thanks
    amarish

  200. 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?

    Regards

    Rakesh

  201. 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,
    karthik

  202. 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

  203. 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,
    Bala

  204. 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..

    Thanks,
    Swathi.

  205. 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.

  206. 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

  207. 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.

    Thanx

    Ashutosh Gupta

  208. 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.

  209. 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 .

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

    SELECT * INTO Pips FROM OPENDATASOURCE(‘Microsoft.Jet.OLEDB.4.0′,

  211. 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.

    Ndindi

  212. 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

  213. i have installed sqlserver 2005 from microsoft.com 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)

  214. 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
    for
    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
    begin
    print @DBname
    fetch next from @testCursorForDB
    into @DBname
    end

    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(i.id) as TableName,
    i.name 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 :)

  215. hi pinal,

    i have gone trough sqlauthority.com 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

  216. 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(godaddy.com), so i need to generate scripts. I read your blog on scripting the db, but how do i script out the data ?
    thanks,
    Art

  217. 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
    Saron—->S002
    Egor—->E001
    Allen—->A001
    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,
    Stratos

  218. @jahir,
    Your database server name must be incorrect or database server is not running. Check services.
    You can also search http://search.sqlauthority.com for solution.

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

    @sarath,
    You should use CAST or CONVERT function.
    You can also search http://search.sqlauthority.com for solution.

    @sampath,
    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.

    @k.n.m.suman,
    Not a good idea.

    @Sonal Dave,
    I have not heard of it.

    @CHANDRAKANT GAURAV,
    I will send you email.

    @Martha McPherson,
    Index is good option.
    You can read my articles on Index Tuning http://blog.sqlauthority.com/category/sql-index/

    @Jay,
    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.

    @Maple,
    Read this blog regularly.

    @Roy,
    Sure I will write on soon.

    @Eric,
    I will research it and reply to you.

    @ajmal,
    Read this blog regularly.

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

    @Hans,
    Sure I will write article about it.

    @Khushi,
    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.

    @Rakesh,
    Search http://search.sqlauthority.com for solution.

    @Ashwini Kumar,
    You should read articles here : http://wordpress.com/tag/sql-backup-and-restore/

    @Bala, @Swathi,
    Need more information.

    @Samuel,
    Server must be busy when you ran your query.

    @Alberto,
    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 : http://wordpress.com/tag/sql-backup-and-restore/

    @Mayoz,
    Thanks.

    @Utkarsh,
    Search http://search.sqlauthority.com for solution.

    @Herman,
    You need to install jet drivers.

    @ndindi,
    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 http://search.sqlauthority.com 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.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  219. 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
    Sabah

  220. hi pinal.

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

    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

  221. 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
    begin
    select @innerResult = exec InnerProc
    select ‘hello world’, @innerResult
    end

    proc InnerProc
    begin
    select 1
    end

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

  222. 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
    vamshi

  223. 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

  224. 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 .

  225. Hi Pinal,

    Greetings,

    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

  226. 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.

    http://drveresh.googlepages.com

  227. 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?

    Thanks,
    Pavan

  228. 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,.

    thanks

  229. 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…

    Archayan

  230. 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

  231. 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.

  232. 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)
    AS

    begin
    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)
    begin

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

    set @count=@count+@datalen_tocheck

    –select @mystr as mystr
    end

    return @result
    end

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

  233. 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.

  234. 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.

  235. 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

  236. 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

    Thanks
    sameer

  237. 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.

    Regards
    Vikas

  238. 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!
    Robert

    • 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 .

  239. 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 )

    Robert

  240. 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

    eg:
    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?

  241. 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?

  242. hi dave,

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

  243. 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.

    thanks
    Shreyas

  244. 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

  245. Hello sir, I hope you be fine…
    I have a problem. I want generate object script via stored procedure. Please guide me to find and use it.

  246. 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)

  247. 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
    tables.

    Thanks,
    Alex

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

    Hi

    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

    Thanks

  249. 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.

  250. Hi Archana,

    You can get the number of days between two entered dates by using datediff function
    example:
    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.

    Shreyas

  251. 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
    likewise

    thanks
    archana

  252. 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.

    Shreyas

  253. @Anil,

    Sure I will get in touch with you.

    @Sabah,

    You can truncate and shrink database as well backup on backup device.
    For more help : http://search.pinaldave.com

    @arihant,

    Try to access server using IP address.

    @Nilesh,

    Sure, you can use OUTPUT parameter.

    @Vamshi

    Please search for solution here : http://search.pinaldave.com

    @Varsha,

    I will sure write about SSIS soon.

    @George,

    Glad you liked it.

    @manish,

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

    @Abhay,

    Read this blog regularly and read lots of SQL.

    @Chella,

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

    @Veeresh,

    Sure, I will write about that to you.

    @pavan,

    It can be rounded to only 3 miliseconds

    @Keyur,

    Create empty database.

    @Archayan,

    Need more information.

    @viren,

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

    @Tina,

    Everyday more tahn 4 hours

    @Edgar,

    I will write about this very soon.

    @Robert,

    Please search for solution here : http://search.pinaldave.com

    @sameer,

    Please search for solution here : http://search.pinaldave.com

    @vikas,

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

    @Carmen,

    I read http://www.bennadel.com/ for coldfusion.

    @Robert,

    SQL Server 2008 has new DATE datatype. Very helpful.

    @Rajesh,

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

    @varun,

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

    @Shreyas,

    I will send you email soon about this.

    @Amit,

    http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

    @mahdi,

    http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

    @Senthilkumar,

    Please search for solution here : http://search.pinaldave.com

    @prasad,

    I do not think that is possible.

    @Alex,

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

    @John,

    You can do it using sqlcmd.

    @Shreyas,

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

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  254. 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.

    Regards
    Nitesha

  255. 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

    STU1
    STU2
    STU3
    STU4
    STU5

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

    STU1
    STU10
    STU100
    STU1000
    STU10000
    STU2
    STU20
    STU200
    STU2000
    STU20000
    STU20001

  256. 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.

    Regards
    Vinoth

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

    Thanking You
    G.Thangapragassam

  258. 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

  259. 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

  260. 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.

    Thanks.

  261. 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?

  262. 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.

    Shreyas

    Let me know how that

  263. 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.

    Thanks,
    Aarthi

  264. 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

    Regards
    shreyas

  265. 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?

    Thanks,
    David

    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)

  266. 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

    Thanks

  267. 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.

  268. 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,
    Prashant

    Imagination is more important than knowledge –>Albert Einstein

  269. 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

  270. 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 sqlauthority.com 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.

    Rajesh

  271. 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 ..

    Regards,
    Rupesh

  272. 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.
    Thanks.
    Suchitra.

  273. Hello sir,

    As am arief from chennai. i have a doubt.

    i want assign multiple table to single data set

    i hope you will responce for my doubt

    thanx

  274. 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

  275. 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?

  276. 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.

    thanx
    with regards
    maneesh

  277. 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?

    Regards
    Suresh

  278. 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.

    Regards

    Shreyas

  279. 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

  280. 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

  281. 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 asp.net.

    Thanks….

  282. 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

  283. 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 performed.so i want solution according to performance.

    thanx in advance

  284. 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

  285. 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.

  286. 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!
    Charles

  287. 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.

  288. 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.

    OR

    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.

    Regards,
    Siya

  289. 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

  290. 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.

    Venkat

  291. 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.

    Shreyas

  292. @Devdatta,

    Read http://blog.sqlauthority.com regulalary.

    @Nitesha Patel,

    @Senthil,

    Sure I will send you email describing your answer.

    @Vinoth,

    I need little more information to answer your question.

    @G.Thangapragassam,

    Search your question at http://search.sqlauthority.com

    @raghuvaran,

    Read http://blog.sqlauthority.com regulalary.

    @Pinkesh Khatri,

    My experties lies in SQL Server.

    @Bhabani,

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

    @Samir Nigam,

    http://blog.sqlauthority.com/category/sql-constraint-and-keys/

    @Aarthi,

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

    @David,

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

    @Archana,

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

    @Nirav,

    You will have to use GROUP BY clause.

    @Prashant Sharma,

    Read http://blog.sqlauthority.com regulalary. I post lots of good book review.

    @Arihant Kothari,

    What is the server you are using with.

    @ilan,

    Not sure what is your question.

    @Rajesh,

    Sure. I am always willing to help.

    @Rupesh,

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

    @Suchitra,

    I have already written about this topic on my blog post.
    Search your question at http://search.sqlauthority.com .

    @Arief,

    I will send you email.

    @angelo conti,

    You need to defragment your database.
    Search your question at http://search.sqlauthority.com .

    @Samir Nigam,

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

    @Maneesh,

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

    @Shreyas,

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

    http://www.microsoft.com/downloads/details.aspx?familyid=1470E86B-7E05-4322-A677-95AB44F12D75&displaylang=en

    @Suresh,

    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 http://search.sqlauthority.com .

    @muhabas,

    I will send you email.

    @cube,

    Search your question at http://search.sqlauthority.com .

    @Rakesh,

    Post your job request at http://jobs.sqlauthority.com

    @Manisha,

    Use CASE statement.

    @Boby Khan,

    Create log files on SAN.

    @Aditi,

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

    @Charles,

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

    @Vashudha,

    I will send you detailed answer in email.

    @Siya,

    I have already written about this topic on my blog post.
    Search your question at http://search.sqlauthority.com .

    @Milind Karthik,

    It was my pleasure as well.

    @Mike Dymtri,

    Thanks.

    @venkat,

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

    @Shreyas,

    Thanks for helping blog readers. Appriciate your help.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  293. 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.

    Sankalp

  294. 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.

    Titus

  295. 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,

    Mark.

  296. 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.

    thanx

  297. 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.

  298. 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

    thanx

  299. 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

  300. 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

  301. 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.

    Regards

    Balaji

  302. 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.

    Sonal

  303. 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.

    regards,
    sandy

  304. Pinal,

    I want to post a review of your site in our blog (http://dbalink.wordpress.com).

    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.

    Regards,
    Marlon Ribunal, DBAdmin/Linked DBA Group Manager

  305. 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.

    Shreyas

  306. 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.

    Shreyas

  307. 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….

    thanx
    dheena

  308. 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

  309. 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

  310. 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

  311. 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
    Ting

  312. 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.

  313. 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
    removed

    Is it possible?? if yes then how

    Thanks in advance,
    Archana

  314. – Get the script to drop all the tables

    SELECT ‘DROP TABLE’ + table_name FROM INFORMATION_SCHEMA.TABLES

    — Execute the script

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

    Satish Shrikhande

  315. 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?

    Regards,
    Suresh

  316. 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

  317. 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

  318. @ Satish Shrikhande

    Hi

    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

  319. 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…………….

  320. 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.

    Regards,
    Rashmi

  321. 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.

  322. 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…………….

  323. 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
    moupiya

  324. 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 p.name, i.something
    from person p
    left join INFO1 i on i.id = p.id

    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
    JJ

  325. 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.
    kasi

  326. 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
    dev

  327. hi Dave

    Please help me installing

    Analysis services & MDAC in SQL Server 2005

    Please provide me any docs available

    thanks waiting for your reply

  328. 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)

    Thanks,

    Rakesh Verma

  329. 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

    Rajan

  330. 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?

  331. 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”
    is there anyway that i can store this message into a variable

    Thanx in advance
    MAKAROV

  332. Hi Pinal,

    I was wondering if it is possible to use the SQL Where clause and have 2 different files in it. For example:
    Where file1.Status ‘V’ AND file2.Status ‘V’. I’m not sure if the where clause is limited to 1 file or not and cant seem to locate any examples of multiple files in a where clause. If it is possible, do you have an example? I greatly appreciate your time and response, thank you!

    Dan

  333. hi! pinal .
    I am new learner of sql server. can u plz tell me how it is useful for mechanical and civil engineering stream. Can u plz give one application of each stream (mech/civil) where it is used.
    thanku bye.

  334. Hi Pinal

    I have a question, I am working on Microsoft SQL Server database, and only 3 of the stored procedures have a padlock against them, and the modify option is disabled, how do I enable that?

    Thank You
    Harp

  335. Hi Pinal,
    I m very much impressed with your blog. It is everytime very useful and knowledgeable.
    i have one problem here hope you can help me.

    Table1 (this is my present table)
    ———————————————-
    col1 col2
    —– —–
    1 abc
    2 xyz
    ———————————————-

    Table1 (I added Col3 and want to put same data as in col2)
    ———————————————-
    col1 col2 col3
    —– —– —–
    1 abc abc
    2 xyz xyz
    ———————————————-

    how is it possible to put col2 data into col3 for all records?

    Many Thanks,
    saroj

  336. Hi, I made some publications from diff servers every time.But When i go to my replication monitor,it shows all other Publications made previously and which r now not in use.Can u pls tell me how can i delete all those publications.

    amit

  337. Hi Pinal,

    It is regarding Best Practice Analyzer. I went through the sample.

    http://blog.sqlauthority.com/2007/07/05/sql-server-2005-best-practices-analyzer-tutorial-sample-example/.

    Can we give the server name in computer name field?
    When we access the server we need to give the user credentials.

    I give my credentials when I login to my workstation, but I use my colleauge’s credential to login to the sever(remote login). I have sysadmin role for database.

    When I give the server name, I get error “An error occured while enumerating services…..”. Please help me how can I proceed with the analyzer tool. Thanks.

  338. I am having an issue with I am thinking SQL 2005 and a third party product.
    I went in and changed the sa password to a simple password before starting the install of Tririga on SQL 2005 on a Windows 2003 SP2 server. Nothing else is running on this box.
    But the install fails each time. the install tries to create a user “tridata” and a database tridata and the password is again tridata which I have tried to change to complex but it still fails.
    The eveent viewer shows as error 18456 Sev 8 or Sev 5 SQL server log shows login failed for user “tridata” [client:127.0.0.1] I have tried to install locally and remotely no luck either way and suggestions ( I also created the user inadavance and still the same failure)

  339. Hi Pinal,

    Is it possible to restore Sql Server 2000 Backup file in Sql Server 2005, I have a SQL 2000 backup file need to restore in SQL 2005.

    thanks In advance

  340. Sir,

    I thank you to you for your help and guidance. I will never forget the advise you gave me. I wish all your reader can atleast have phone call with you.

    Thank you again,

    Megha

  341. Hello pinal, i visit your blog periodically. It is highly informative. i recently a multi media content website http://www.dotnetvideos.net.i have over 100+ videos on the site right now with an additional 200+ videos in the pipeline ready to be added. Many more videos are in the pipeline. I created this website as a community give back.Every registered user of my website will receive a FREE 6-month subscritpion to asp.net PRO magazine.i would appreciate it, if you can announce the launch to the members of your blog readers or a .net user group you are associated with or any other .net developers.You may be instrumental in making your blog readers or visitors to receive a premier asp.netPRO magazine.

    Please look at Scott Mitchell’s (www.4guysfromrolla.com creator) comments on his website about http://www.dotnetvideos.net at http://scottonwriting.net/sowblog/posts/13074.aspx

    **I do like lot the content on your blog, If would you like to contribute any content to my website please contact me.

    i would also invite you to visit my website. thanks for your time and consideration.

    –ravi nangunoori
    http://www.dotnetvideos.net

  342. Hi Pinal,
    I have question about sql server collations. How can I tell whether collation supports
    storage of particular language. Is there a mapping table?
    For Instance, I need to make sure existing columns collation will support
    French, English , German and Spanish. I know that the best option is probably to use
    Column types that support unicode, but is it really necessary in this case? All languages
    required are use single byte character sets, also unicode will use more space, another problem is that
    I would have to change existing db columns (by design), which requires significant regression testing,
    Which I don’t really have a time for.
    Let me know your thoughts.

    Regards Igor.

  343. Hi Pinal,

    I am from Australia. I visit your website and found lot of useful articles, I was wandering if I could ask you a question, Currently I am working as a 1st and 2nd Level Helpdesk Support position and wanna move to Database side, could you please guide me to correct direction, as I was planning to do Microsoft SQL Server 2005 and apply for Database admin or Database developer job.

  344. Hi Pinal,

    I need create a database user in SQL server 2005 with speicific permissions like execute, create new tables, views, storedprocedures, add new datatypes…., but I should not use ‘dbowner’ role.

    Could you please help in this..thank in advance.

    Cheers,
    Pruthvi

  345. Hi Pinal,

    I tried searching some article on Data modeling but I could not find one ( Correct me if I missed ). Wondering if you can share your views on data modeling on your blog. Keep up the good work.

    Thanks,

  346. Hello, I m Trying to remove some snapshot Publications for the databases that right now does not exists.some snapshot publications r removed but some r not. Is it related to some distributor problem.

  347. Hi Pinal,

    I want to upgrade from sql 2000 to sql 2005 ,and have databases with data. Do i need to bring down sql server 2000 before installing 2005 ,also will I be able to access the databases of SQL 2000 ?Please advise what steps I need to upgrade to 2005.

  348. Pinal,

    Concerning Error 18452 and the info you provided. I was had the issue arrise and followed almost all the blog and support site configs to resolve the issue. But none of them worked. What I found to be the issue was DNS and the naming context. My application servers were set to use one set of DNS Names and the SQL Server was set to use another set of names for example:

    Apllication Server 1 – DNS Suffix order was – DNS1.com, DNS2.com, DNS3.com and the domain that the server belonged to was DNSDev1.com.

    SQL Server1 – DNS Suffix order was – DNSDev1.com, DNS1.com and the domain that the server belonged to was DNSDev1.com.

    As soon as I added the DNSDev1.com to the search order and placed it at the top of the list TCP/IP on the Application Server. Both TCP/IP and Shared Memory connections started working.

    I thought you might be able to pass this along.

  349. Hi Pinal,

    I read your articles, those are very useful.

    I want to write sql script to migrate data from sql server 2000 database to server 2005 database using sql script.

    Please guide for same…

    Thanks a lot….
    Chandrashekhar Mahale

  350. Hi Pinal,

    Really a great work.

    My questions are all about my career. I have not so much experience in SQL server DBA. But in general companies are going to give their databases to a less experienced person. They need minimum 2 years of exp. (General senario of India).

    Currently I am working on DWH, specially BO products.

    How to proceed?

    Thanks in advance.

    Regards,

    Arup

  351. Hello Readers,

    This community is doing well and I am glad many readers are participating to answers questions of other readers.

    @Sankalp Mehta,
    I will write a blog article over it.

    @Titus Jacob,
    Create indexed view.

    @Mark,
    You can subscribed to My Feed.

    @Rakesh,
    You should search my blog http://search.sqlauthority.com

    @Paul,

    http://blog.sqlauthority.com/category/sql-backup-and-restore/

    @Rahul Patel,
    I have already answered your questions on my previous articles on my blog. Search http://search.sqlauthority.com

    @Ranga,

    Good suggestion.

    @Balaji,
    You should focus on learning new things. Certifications are just motivations.

    @Sonal Dave,
    I am not sure it all depends on many settings of your database.

    @sandy,
    Search http://search.sqlauthority.com for solution.

    @Marlon Ribunal,
    Appriciate new blog to help community.

    @Siva,
    I will send you email about all your questions.
    I have already answered your questions on my previous articles on my blog. Search http://search.sqlauthority.com

    @Vladimir.
    I do not understand your problem well, please explain properly.

    @dhenna,
    Read http://blog.sqlauthority.com regulalary. I tried to update it frequently.

    @Mark Karcher,
    I have not faced that error before I will research that and will let you know.

    @thamilasarasan,
    There are tools available to do this task. I will research best in market and will write about it.

    @Ting,
    Thanks, yes there is a way and I will write about it.

    @Rakesh Nirmal,
    Please explain your problem in detail.

    @Richard Mason,
    DDL Trigers are great way to avoid problems like you are facing. I have previously written about it.
    Search http://search.sqlauthority.com for solution.

    @Archana,
    Yes, it is possible. I will try to write custom solution for you.

    @Satish,
    Good script.
    Other readers use it carefully, it will drop all your columns.

    @Suresh,
    If someone is modifing data DML triggers will help you.
    If someone is modifing schema DDL triggers will help you.

    @Vipul Agarawal,
    Some syntax may act different. What is exact SQL for your problems. Send me email.

    @Chetan Sharda,
    Post your function to me and I will look into it.

    @Amol,
    Search http://search.sqlauthority.com

    @Rashmi,
    I will write UDF for you.

    @Raman,
    Congratulations.

    http://jobs.sqlauthority.com

    @arihant jothari,
    Send me mroe detail.

    @Binoj Daniel,
    I am aware of same feature in SQL Server 2005.

    @Koushik Chakraborty,
    sqlcmd can be used in your case.

    @Moupiya,
    I will soon write article about your suggestion.

    @Jens Johanneson,
    I am not sure what is your questions.

    @kasi,
    Client-Server Architecture is good model for your case.

    @Dev,
    I have previously written about it.
    Search http://search.sqlauthority.com

    @sampath,
    I will send you docs requested.

    @Smita,
    I have previously written about it.
    Search http://search.sqlauthority.com

    @Rajan,
    Use DML triggers.

    @Kal,
    Yes you can do both together. However set up is very complex. I will write down action plan for you.

    @MAKAROV,
    I will write article about it.

    @Dan,
    Sure, I will write about it soon.

    @shaila,
    SQL is userful to everyone.

    @Bhupali,
    You can define 1 clustered and 249 non clustered index
    on a particular table.

    @Dhruv,
    Thanks for your answer.

    @Harpal,
    I will soon write about it.

    @amit saxena,
    Replication problems are always interesting to solve. Provide more details to me.

    @Rakesh, @saloj,
    Good to see you got your answer.

    @Prasant,
    Good question, I will send you email with answer.

    @Max,
    Yes totally possible. You use restore feature of SQL Server 2005.

    @Megha Raychand,
    It is always pleaasure to help good people.

    @ravi,
    Congratulations for wonderful site.

    @igor,
    I have previously written about it.
    Search http://search.sqlauthority.com

    @vim,
    Please continue reading this blog.

    @Pruthvi,
    I am not sure what you are asking.

    @sunitha,
    I have previously written about it.
    Search http://search.sqlauthority.com I think it is db_name() function.

    @Navdeep,
    Sure I will write about it to you.

    @amit,
    Please provide more details and I will answer you.

    @Ray,
    Great Stuff.

    @chandrashekhar,
    I have previously written about it.
    Search http://search.sqlauthority.com

    @Arup,
    Read http://blog.sqlauthority.com regulalary.

    Regards,
    Pinal Dave ( http://blog.sqlauthority.com )

  352. Hi Pinal,
    Need help for creating a SP which will retrun me the first business working day ( USA) of the Month in a year for all the months..

    Thanks in advance
    Dhananjay

  353. Hi,

    i need to combine the data of 4 different tables & place it into the 3rd table in sql2000.
    how do i go for that??
    thnx a lot…
    Amit

  354. Hi Dave,

    I have 2 servers, main & backup,

    I want to use an “Insert into table5 (Select * From tableA ) where table 5 is on the backup server and tableA is on the main server,

    The connect from the main server is direct thru internal IP of 192.168.1.202 with a username and password to connect.

    Can you help please ?

    Richard

  355. I recently experienced 2 users (1 mssql2005 and 1 2000) who had issues querying to the database after reattaching and or restoring an image of the db. It appears that the objects were given the users schema rather than their original dbo schema (user.table rather than just (table). The users had to run some scripts to reassign the ownership/schema. Why did this happen?

    Thanks

  356. Hi,

    1.Generally what is the retention period in transactional replication .

    2. what are the factors we have to consider while creating a login.
    3.what is index management.

    these are the questions i have faced in a interview.
    could u please answer these questions

    thanks,
    sripathi.

  357. Hi,

    1. iam using transactional replication. i published one article but it is not replicating at the subscribers .what i have to do.

    2.If we set transaction replication what are the coumns are added

    thanks,
    sripathi

  358. Hi Pinal,
    I am trying for foloowing problem and unable to get proper solution.
    My actual task is to provide to Import/Export facility to user in application. For that I was using SqlServer 2005 Bakcup and Restore scripts, I noticed that only work if restore machine has same envoirnment as backup machine (I am not sure if this is the reason), I want to move “Complete” DB. Then I found that I should user Attach/Detach script.
    The steps I am doing for Detach are as follows:
    1- ALTER DATABASE MYDB SET OFFLINE
    2- EXEC sp_detach_db @dbname = N’MYDB’, @keepfulltextindexfile=N’true’
    3- Then programmatically I allow to copy/move application user to copy files MYDB.mdf and MYDB_LOG.ldf

    This works fine, and I am happy with it. :)

    Problem is DETACH, when I try to Detach it says “Cannot open database MYDB requested by the login. The login failed. Login failed for user mymachinename/mywindowsusername”

    I am using windows authentication.

    In my application user should be allowed to copy DB files (a way to provide back up) and should be able to install application anywhere and place the files back so he will have all the schema and data back in that machine. I think the approach I am trying is correct, if i can getrid of the above mentioned error.

    Please guide me if approach is correct, and help me to resolve aboe mentioned error, or if there is better way to achieve the task i am trying to do.

    Best Regards
    –Junaid

    P.S. I am not sure when you post a reply I will get email that reply is there and I should visit your web page to read it.??

  359. Hi Pinal,

    I have installed SQL Server with windows Authentication mode. Later I planned it to change as SQL Server Authentication Mode. I opened server properties – security – changed the mode. When I try to login in it is asking for password. I tried with blank password and my previous password, Though it is not working, pls help me……………

  360. My previous post probably not explaining the problem exactly, here I try again.
    I am trying to Detach and Attach DB, I read your one previous blog about that and I am following the same process.
    In Sql Management studio it works fine and no problems at all. If I try to run the mentioned scripts in C# then DETACH works fine, copying files done properly, but ATTACH never works. The error I can see in exception message is:
    Cannot open database MYDB with this login. Login Failed.

    I need help to resolve this issue.

    Regards
    –Junaid

  361. Hi Pinal,

    I need to insert all data which is updated in a table, for this i have created a update trigger on table.

    Let Suppose there is two table T1 and T2 now i have updated 10 rows by a command i want to insert all 10 rows in T2 table how can i do it?

    Is update trigger is good approach?

    Thanks in Advance

  362. re: http://blog.sqlauthority.com/2007/05/15/sql-server-dbcc-commands-list-documented-and-undocumented/

    Pinal —
    I don’t know how to directly approach my documentation issue–perhaps you can help. Please.
    I have been invited to make a presentation at UCLA in just over three weeks about DBCCs–both documented and undocumented. I have a list so far of over a 130 from the beginning of time. I’d like to suggest to the students/professionals in attendance when to use which resource. For example, DBCC ShrinkDB (to my knowledge) should be replaced by DBCC ShrinkDataBase in all jobs/scripts.
    Can you point me to a resource (whether inside or external to Microsoft) that discusses ALL DBCCs? I’m looking for more than syntax to include efficacy. I have, for another example, a reference that suggests DBCC ShrinkDataBase is a “hit-or-miss affair”. Proving or debunking this kind of statement is what I’d like to do.
    I’m willing to share back the results for broader dissemination.
    — Dave Owen, MCT, (805) 658-6775

  363. Hi,
    I am stuck where I have requirement I have to optimize a small query ..

    I have a table with 40Million rows
    And that query do’s a self join on that table and do some MATHS like

    Table 1 has 5 Coloumns

    and my query is

    Insert into Table2
    select A.Column1, B.Column1, count(A.Column1) as Frequency ,sum(B.Column2 – A.Column2) as Difference
    From Table1 A with (readuncommitted)
    inner join Table1 with (readuncommitted)
    on A.Column2 = B.column2

    Am doing Minus(-) on Column2 so its like 1 row is going and looking 20Million Rows and more to get the best REsult
    I tried Partioning but it didnt worked out

    Any Suggestion would be help

    Thanks
    Sameer

    Pinal I hope these should be a piece of Cake for you :))

  364. Hi Dave,
    I am graduating soon and want to apply for a
    SQL server DBA as an Entry Level. I have some experience on SQL server, but not much. I posted my resume, and a few compnies had called me already, but I afraid I am not experienced enough to take this Entry level job.

    Can you please tell me specifically what Entry Levels DBA do and what their duties are? What should I focus on to prepared for this job? What topics of SQL server do I need to practice to successfully be able to handle an entry level DBA job. I am pretty sure you have managed some people, so if a company hire me as an entry level DBA, will the training be enough for duties of an entry level DBA?
    Thanks Dave!

    John

  365. Hi,

    I have table with date,productname and quantity columns.

    I want to result in tabular format.

    Like,

    Date Product1 Product2 product3 ….
    01/01/2008 5 7 8
    02/01/2008 15 9 8
    03/01/2008 25 17 18

    So,please help me to write query for this type situation.

    Send me solution by email.

    Thanks in advance.It’s very urgent.

  366. Good Day Pinal,

    I come to you since ive been doing google all day and i just cant get to work one thing:

    I have 2 servers, in both servers there is SQL Server 2000 and from server 1 i did a vinculation of servers with a particular Database, it works fine i even had some issues with collationg but nothing that could not be solved, but i made this StoreProcedure and i wanted to add it in a task job, but keeps on failing with Error 7399 failing authentication, i did the log on thing of giving access to c:\Temp and all stuff but keeps on failing…

    The store Procedure has queries that uses tables from server1.database and server2.database, so i had to do when creating the Stored Procedure the
    SET ANSI_NULLS ON
    GO
    SET ANSI_WARNINGS ON
    GO

    The SP works in the QA fine, but on the task it keeps failing, could you give me a hand?

    Thanks

  367. Sir,

    I have one query for Scripting the tables.what is the use of this scripted table.I want to know the answer for

    ” From one server,how i can do DML commands in another server’s tables, and also copying of my tables in to that server. “

  368. Hello Pinal,
    I have updated a column in a table . is there any process by which we can get that previous column data.In simple terms, is there any undo(ctrl+z) type of command in sql server.

  369. Hello Dave,
    Having a problem deleting about 4 million records by date out of a SQL DB. My CF script keeps timing out even though I’ve added “RequestTimeout=10000000″ to my url.

    delete from tablename where sdate < ’10/1/2007′;

    Any suggestions would be greatly appreciated.

  370. i want to install SQL server and when i start instalation i need to set sa password and i dont know where a can set it

    can you help me

  371. Hello Good noon,

    I facing problem with performance in sql server ,
    My application totally depends on database ,what am doing in that is selecting records from one(Previous) datbase
    and inserting those records into newly creted database depending upon date selection by user
    and after that am removing those selected records from previous tables.
    All is working fine bt the problem is that its taking too much time
    Ex. its taking more than 3 hrs for 1.58 Gb of database
    so Is There any Way to minimise the timing for better performance
    Plz tell me

    Thanks,
    Archana

  372. hi pinal dave,

    i have got this error
    Could not load the DLL xpstar90.dll”

    i have resolved the error .the dll was missing

    instead of taking backup in the same server i want to take backup on other remote server . can u tell me how we can do it?

  373. Hi pinal,

    How to fix this issue in sql sever
    “Transaction (Process ID 81) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    Item cannot be found in the collection corresponding to the requested name”

    How to resolve this issue as i am facing lot of problem because to this deadlock.Please help me to fix this issue.

    Thanks,
    AnjanA

  374. Hi pinale,
    I have a doubt, have seen your post for get first and last records.

    SELECT *
    FROM Sales.SalesOrderDetail
    WHERE SalesOrderDetailID IN (
    SELECT TOP 1 SalesOrderDetailID
    FROM Sales.SalesOrderDetail
    ORDER BY SalesOrderDetailID)
    OR
    SalesOrderDetailID IN (
    SELECT TOP 1 SalesOrderDetailID
    FROM Sales.SalesOrderDetail
    ORDER BY SalesOrderDetailID DESC)

    my simplified query is on below. please suggest me my below qry is correct or not.

    select * from tbl1
    where col1 in (select max(col1) from tbl1)
    or col1 in (select min(col1) from tbl1)

    note: col1 is PRIMARY KEY

  375. Hi Pinal,
    I have one stored procedure and its taking 2 mins to execute. My stored procedure has 7 input parameters ( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 2 seconds. I am really puzzled with this.

    What could be the reason why the SP is taking more than query,

    Regards,
    Hemant Nehete

  376. Hello pinal,
    this is my second post to you.
    In sql server 2005 express when maximum concurrent connections is reached it throws a sql exception. But this exception is general
    how can i know that the exception is due to the maximym concurrent connections, so that i can display appropriate message in my c# 2003 code.

    thanks.

    samir mackwan
    software developer

  377. Hi Pinal,

    I’d like to know how can a stored procedure be executed as a different user than the current one.I tried the following command:

    exec ‘name_of_stored_procedure’ as user=’impersonated_user’

    I’m using MS SQL Server 2000.The error I’m getting is:

    Incorrect syntax near the keyword ‘as’.

    Am I doing something wrong?

    Thanks!

  378. Dave, I’m new a SQL sever 2005. I have a stored procedure with a insert statement. The table has 56 fields. Is there a way to select all fields within table without having to type each one of them.

    Thanks

  379. Hi Pinal Dave

    Ur site is very useful for me … i m new to sql server

    recently i have faced 1 interview ,in that they asked some questions which i dont knw

    please provide me the answer for the following

    1 “If one table is locked in front-end , how can we remove the lock from back-end”?

    2 max how many @@ error and raise error we can use?
    3 max how many .ndf files we can create at the time of creation of a database?
    4 how do we know an index is properly used or not?
    5 max how many .ndf,.ldf,.mdf files we can create?

  380. Hi,

    I ‘ve got a problem with using “apply” in views.

    Indeed, I want to create a view by right-clicking “Views” then “New View” and Paste a request including “outer apply” statement (due to the use of function).

    I always get this error message : “the outer apply sql construct or statement is not supported.”

    Is it possible to use “apply” with a view ?

  381. i have two tables T1 and T2. Both has the same schema. I am inserting a few records manually in T1 which has columns empname and empid.I need to create a stored procedure which should delete a particular record in T1 based on the parameter value in T1. Before the record is deleted, it should get copied into table T2 which has the same schema.

    How to achieve this? Pls give me the full stored procedure coding.

    It is quite urgent.

    Thanx in advance

    Regards

  382. Hi,
    I just read your blog on not posting images in a sqlserver 2005 database. This makes sense to me. However since I’m painfully new to sql and asp.net 2.0 I have no clue as to what the syntax would be to save the actual url of the photo to the database. The url is:
    ImageUrl=”~/App_Data/Photos/WeddingPhotos/1.jpg”
    When I put this in it shows in the database field, however it is simply text. The photo doesn’t load (in gridview) which would be my goal. I’ve spent hours/days searching for a tutorial on how to do this with a url vs a image file but have found nothing that really meets my needs or my limited knowledge.

    Am I wrong in my assumption that storing the url to the photo will result in the photo showing when the gridview opens? If I am wrong could you point me in the right direction. If I am not wrong, then I need to now the syntax for inserting the url in the database so the image populates.

    Thanks,
    Leesha

  383. Hi Pinal,

    I’ve hard time finding reliable information about number of the bytes each data types uses in SQL server 2005.
    I am trying to create a design document which shows amount of space require for each table and collectively for the whole database.

    appreciate your help

    Kia

  384. Hi Pinal,

    I need your help regarding transaction log file. I am totally confused about this. I need exact information i.e what is the main purpose of transaction log file.

    Plz help me out.
    Thanks
    Madhu

  385. Hi Pinal

    I am having difficulty in something very simple. I am quite new to SQL.

    I have table A, where records are inserted with duplication.

    I need to create a trigger FOR INSERT on TABLE A. Copy the new records to Table B. First check if the Name which is Primary key exists in Table B, if exists update the row with the new data.

    If it does not exist create a new row in Table B

    Plz Help
    thanks
    Zinadeen

  386. how to run a command on command prompt that will sure me all the computer that runs sql server over a certain domain.

    Thanks.

  387. hi
    i am working on sqlserver 2000.
    right now i have problem that
    i have to retrive the column data into row
    if u have any solution please reply to me
    waiting for response shortly

    thank you

  388. Hi Dave.

    Thanks for helping Database community, great work.

    I have one question for you.

    I have more than 1 non-cluster index in a table, how can i enforce to use particular index in my query?

    Thanks
    Jaya

  389. Hi Dave,

    I want to congratulate you on your 500th post. Your blog has a wealth of knowledge for a novice as myself. Thank you, for your time and effort.

    Regards,

  390. Hi Pinal,

    In sql Server 2005 when I leave query window ideal for some times it is disconnected autometically showing in its header as not connected .so where is the connection properties to change its status. I searched tru google and in sql server also I couldnt find any solutions. plz help.

  391. I have some serious issues with paging thru large result sets.

    Coding for Paging, Sorting, Filtering in a signle page is very difficult in SQL Server 2000 days. I think this same with SQL
    SErver 2005 also (even with ROW_NUMBER()).

    Why Microsoft is failed to comeup with a neat solution like LIMIT clause in MySql. Its so much easy to use, even in commandText.

    And, I wouldlike to know comparision between ROW_NUMBER() and LIMIT clause in mysql. Which is the best interms of performance?.

    Looking forward for good reply…

    Regards,

    Pradeep

  392. Hi

    This is UmaMahesh. iam .NET Developer. i need ur help in SQL server.

    i have one table say table name is Table1.
    which contains 3 columns called ‘year1′, ‘year2′, ‘year3′ and
    also alwasy the table contains only one row. The values will be like this

    Year1 Year2 Year3
    1 0 0

    i need the column value, whichever column contains the value ‘1’

    means in the above scenario i need year1 value only

    plz help me
    it is urgent for me

    Regards
    UmaMahesh.Ch

  393. Hi sir,
    I wnat to become a DBA.How can i become a DBA?Tell me the best institute in india providing courses in the field of database.Reply me soon.Waiting for ur reply.

    Thanks & Regards
    Vinay Pandey

  394. Hi,
    I have used START WITH …. CONNECT BY in my sql to get the output in a hierarchical structure(tree structure).

    Since sql server does not have the connect by hierarchical option, could you please let me know if there is any similar option for SQL Server and DB2 databases too?

    Also, could you please tell me the need of using WITH
    ex:
    WITH n(empid, name) AS
    (SELECT empid, name
    FROM emp
    WHERE name = ‘Goyal’
    UNION ALL
    SELECT nplus1.empid, nplus1.name
    FROM emp as nplus1, emp AS n
    WHERE n.empid = nplus1.mgrid)
    SELECT name FROM n;

    Thanks in Advance

  395. Hi Ankur,

    Ankur will you please tell me that how can we insert a value of one database table to another database table through query?

    Regards

    Jani vishal

  396. hai dave ,

    i want to dump an excel file with large amount of data to a table .
    what are the different methods ,please tell me?
    send me reply as early as possible. i am great full to u if u suggest in this regard.

  397. Hi Pinal,

    I have one master table , in a table having password field as varchar(50). Now i want to convert to varbinary using sql script.

    Please give me a solutions.

  398. Hi Pinal
    I am using SQL Server 2005 and would like to get all the rows that exist in
    (Table1 – Table2) without the answer being distinct, what do i do?

    Example:

    Table1:
    Products: A, B, C, D, D, D, E

    Table2:
    Products: A, C

    select * from Table1
    except
    select * from table2

    will give me: B, D, E

    But i want it to give me: B, D, D, D, E

    Any way that would work will be helpful. Thanks in advance.

  399. Hi Pinal,

    I’ve a subquery which returns more than one row (user_group), and i need to convert those rows into a comma delimited list. i tried with various possibilities but unable to find the solution. can you pls.help.

    here is my query below.

    SELECT
    user_ID,
    user_Firstname,
    user_Lastname,
    user_Email,
    Username = (Case When user_Username = ‘ ‘ or user_username is Null then ‘None’ else User_Username end),
    user_creationdate,
    Active = (Case When user_Active = 1 then ‘Yes’ else ‘No’ end),
    User_Group = (Select group_name from usergroup a, usergroup_combo b where a.group_id = b.ug_groupID and b.ug_userid = user_id),
    TheatreCode = (Case When User_TheaterCode = ‘ ‘ or User_TheaterCode is Null then ‘N/A’ else User_TheaterCode end),
    JobTitle = (Case When user_JobTitle = ‘ ‘ or User_Jobtitle is Null then ‘N/A’ else User_Jobtitle end)
    FROM [user]

    thanks
    Sreedhar

  400. One more suggestion,

    why don’t you make a Forum of SQL GROUP Pinal..that way it’ll be easier for everybody to post and easier to read

    my 2 cents.. :)

    thanks
    Sreedhar

  401. Hi Sreedhar,

    Create a User Defined function which will take the UserID as IN parameter and return you the groups in comma delimited.
    Try out, if not let me know. I don’t have SQL, but can give you psuedocode for you.

    Regards
    Saroop

  402. hi Pinal,

    im developing an accounting software db design.. But im not familier wit accounting operations, itz some awat complex for me… if u ve any idea or any reference plz tell me… if u know any website to learn system based accounting operations plz tell me….

    thank u,
    wit regards
    vasan

  403. How can you copy a database and rename it so it can be used within the same instance. E.G. copy sales DB to sales_2007 Tead End DB

    Regards

    Jon

  404. Hi Pinal,

    Could you please give me a link or document where I can get step by step upgradation/migration process from SQL 2000 standard to SQL 2005 standard…

    Regards,
    Nihar

  405. can u help me in this?
    =================
    Suppose if i want insert data which has same name but has Unique ID by using range number.
    _______________________________________
    example if i want insert ‘ItemName’ SIX times |
    ______________________________________ |
    code here is set Auto.
    ———————-

    Insert into Items values
    (
    ‘ItemName’,null,null,null,null
    )

    thanks and best regards
    NOT just SIMPLY i ASK you, but with appreciation of ur kindness.

  406. Hi Pinal,

    I would like to know whether there is a way to know
    the exact timestamp ( without using the timestamp field)
    of a row updation or insertion in a MSSQL table?

    Is there any undocumented feature for this?

    Thanks
    Dileep.

  407. Dearest Pinal,

    I am trying to update a field in table A by linking table A and B.

    When the date from Table A, falls in between the two date fields on tableB, I want to populate a field in tableA with a value from a field in tableB.

    For example the date 9/30/01 exists on tableA. TableB has a date range of 7/30/01 to 11/30/01 and a value of XYZ corresponding to that date range. I want to populate tableA with value XYZ since 9/30/01 from tableA is between those dates on tableB.

    Thank You,

    Sunita

  408. Hi

    I have one Flat File. It contains around 300 rows.

    Issue is,

    The records are getting merged with the same line.Like i have column in excel sheets look

    a b c d
    — — — —
    1 aa bb cc
    2 ww cc yy
    3 oo mm ii

    Am getting the results of ,

    a b c d
    — — — —
    1 aa bb cc 2
    3 oo mm ii 5
    6 ff jj ee

    Please give me the solution for the above. why the flat file is not terminated with the single record.

    Thanks in advance

    while inserting the records from flat file to database thru ssis package.

  409. Hi Pinal,

    My question is related to a database. I work as a BA and working on a project related to Oracle(9i) database. Let me give me some brief description about the project.

    There is a requiremet to create a new oracle database on production enviorment for XYZ departement of the company. The end user wants database backup on daily basis. Also, there is a requirement that database should have the ability to be restored to a prior version.

    First of all, What is difference between database backup and database version ( what do they mean by prior version? oracle version or what?). If they are different, what are the recommended steps/considerations to get both backup and version?

    Please help me to find this out.

    Regards.

  410. Hi Pinal,
    Can I in some way find out a column or a value is hard coded in the sp’s or views.

    That is for suppose on in a table there ar3e two columns

    x and Y

    where x = id (its a primary key)

    y = name

    now I want to find stored procedure or view where the following thigs are hard coded:

    1. Column names (x, Y)
    2 .id is hard coded
    3. Name is hard coded
    Can you pls help me out in this

    thanks in advance
    praveena

  411. Hi Pinal

    It is really nice to read all your articles,from past 8-9 months i am a regular reader of this blog thanks to providing such useful stuff to us.

    i have a query to you
    i) is there any method to take backup of a perticular table from a database.

    Thanks in advance

    Jayasimha

  412. @Dhananjay,

    Sure I will get that for you.

    @Richard
    Thank you.

    @T.Amit,
    You should write SELECT statements using Joins after that insert using SELECT insert.

    @Richard Guyon,
    Please provide more information.

    @Ken,

    http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

    @sripathi,
    search your answers here : http://search.pinaldave.com

    @Junaid,
    I send email most of the time. Many times if question is quick I post it here.

    @Venkat,
    I will send you email with solution.

    @Amit kumar jain,
    search your answers here : http://search.pinaldave.com

    @Dave Owen,
    Good Note.

    @sAMEER,
    search your answers here : http://search.pinaldave.com

    @john Tran,
    I will write blog post about this.

    @Kamal,

    http://blog.sqlauthority.com/2007/05/06/sql-server-udf-function-to-convert-list-to-table/

    @johand,
    Sure send me email with little more details.

    @Thomas,
    search your answers here : http://search.pinaldave.com

    @amit,
    No. you will have to retrieve from logs.

    @Travis,
    You need to increase the time out value.

    @shaik,
    You need to add order by column and insert value in order you want it ordered.

    @marfi,
    When you are installing it will ask you in separate propt.

    http://blog.sqlauthority.com/2007/12/29/sql-server-change-password-of-sa-login-using-management-studio/

    @Archana,
    Implement Index optimization. You can search here : http://search.pinaldave.com

    @arihant kothari,
    I am not sure if I understood your problem.

    @Anjan,
    You need to find out which processes are long running and optimize it.

    @sabarish,
    Your query is correct if your query is primary key.

    @hemant,
    Network traffic can be issue.

    @samir,
    I need more information to answer your question.

    @kash,
    I have previously written about this : You can search here : http://search.pinaldave.com

    @Paul,
    I support SQL Server 2005 and higher versions.

    @Ron,
    SELECT * FROM TABLENAME

    @zakeer,
    I have answered all this answers in this blog. You can search here : http://search.pinaldave.com

    @sedor,
    View does not support outer apply.

    @CRPRAJAN,
    You should use trigger.

    @Leesha,
    I suggest that we should use filesystem to store the images and keep the path to files in database.

    @Kia,
    Thank you for your words.

    @madhu,
    Transaction log files stores the information of all the transaction.

    @Zinadeen,
    You need to create for insert trigger as you said.

    @sai krishna,
    select statement and self join can not be compared. They are apples and oranges.

    @chuene,
    You should write shell script for it.

    @ajay,
    Use Pivot table.

    @jaya,
    SELECT * FROM TableName T (INDEX = idx1) WHERE Field1 = ‘A’

    @saravanan.p,
    You can not hide database in SQL Server.

    @Oscar,
    Thank you very very much.

    @ravi,
    Search for replication at http://search.pinaldave.com

    @Rupesh,
    Right click on database and see property.

    @pradeep,
    You can use TOP(number) for the same purpose.

    @Manish,

    http://blog.sqlauthority.com/2008/03/05/sql-server-simple-example-of-cursor-sample-cursor-part-2/

    Use IDENTITY

    @UmaMahesh,
    I do not understand your quesiton.

    @vinay pandey,
    Read this blog regulalary.

    @Raja,
    SQL Server 2008 supports hierachical options.

    @Vego,

    http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

    @Mohamed Hanif,
    Use keyword CONVERT.

    @Elad,
    Use UNION

    @sreedhar,
    I will look into the forum option.

    @Saroop Chand,
    thanks for helping readers.

    @vasan,
    I have writen about accounting issues previously http://search.pinaldave.com

    @jon,
    You will have to restore the database.

    @Nihar,
    This blog has talked about this many times.

    @Zafar,
    Please explain you question little more.

    @Dileep,
    I do not think it is possible.

    @imran,
    http://search.pinaldave.com search your questions.
    http://job.sqlauthority.com find your job.
    http://blog.sqlauthority.com read it regulalary.

    @Sunita and @Ashok and @Praveena,
    I will send you email with answer to your query.

    @Himdeo,
    Backup is copy of your data.
    Version keeps track of your changes.

    @Jayasimha,
    Thank you for your words. You can not take single table backup.

    Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

  413. Hi pinal dave,
    I have gone thru ur articles and its intersting. i was searching for “how to give parameter for DTS”. i didnt find it in ur posting. If u hve any idea can u please post.
    Thanks in advance..
    ashim

  414. Hello Pinal Dave,

    I am new with mysql 5.0.16 database.
    I have to create the backup of database. But in dump process it give me the error of trigger. So, i want to delete all trigger.
    I don’t know any trigger name. So, please guide me.
    I want list of all triggers in database. So, which command is used to get list of all triggers.
    I am working with CentOS(Linux).

  415. Hi,

    I came across your blog today and found it very interesting.. Recently I have been hired as a DBA and report writer to work with SQL 2005 and Crystal Reports XI. I also have a data warehousing project coming up.. Could you please suggest some good books that’ll help me learn data warehousing concepts or point to it in your website.. if you have some topics that covers reporting as well, that would help me too..

    Thanks,
    Lakshmi

  416. Hi,
    Could you please help me out to built this query.
    I am having a table which has the following column:
    1. EmpId
    2. EmpName
    3. Salary
    4. DeptName

    Q: I want to list all the employee name , deptname & salary and at the end of each dept i want to display the sum of salary of all the employees according to the dept.

    Q: I want to display the highest salary of each dept & the empname who is having the highest salary in respective dept.

    Thanks

  417. what is the maximum numbers of foreign keys on a table?

    what is the maximum numbers of columns can have foreign keys in a table ?

    what is the difference between sysusers and syslogins ?

    how i can catch the return value from a procedure?

    how we can explicitly call triggers?

    how do i know from query which sql server service pack is installed on the machine ?

    what is the maximum number of joins in a query?

    what is the maximum number of inner queries in a query?

  418. I’m look for a script that will find a specific column heading from all tables in my sql 2005 db. I will need to find the ‘acctper’ and ‘acctyr’ This would be helpful in knowing how many tables and columns that I will need to update with the current year and accounting period.

  419. Hello pinal
    I have requirement in which i database backup has to take place automatically to a backup server. My backup server is located in a different place and my main server is in some other place. I am aware of backing up data in the same machine by doing creating a maintainance plan for the backup purpose. But how i can do this when my backup server is in a remote place.
    Please help me.
    Thanks and Regards,
    Anand

  420. Dear Pinal,

    I need the sql programming using cursor for the following issue.

    I have to insert the values of 4 fields.
    Sno(Primary key), StudentName, College, SortId.

    If i enter the collegename(VKK College) the sortId should be sorted and displayed as 1.
    If i enter the same collegname(VKK College) the sortId should be displyed as 2.

    Pls send I need this using cursor.

    Thanks
    vikkas.

  421. Hi Pinal,

    This is Thakarshi if you remember me then( We worked 2gether on autobiz)..Now I am in london.

    I need your help..
    I want to generate unique random card nubers at time 50,000 and store in table. So when i need to issue card then i will use this unique numbers. n remove row from this table,

    So pls help to how to generate unique random numbers which may between 6 to 20 digits.

    Cheers,
    Thakarshi

  422. Hi Dave

    I recently discovered your site and find it to be very resourceful for someone like me who is new to SQL server. Thanks for the good work.

    I would like to use an ENDPOINT as an interface between SQL server database and a client web page. I would also like the web client to access a Procedure in SQL server through the endpoint and pull data that is displayed on the client web page. I would like to do this without having to code objects in programming language like C# or visual basic ….etc. I have created the endpoint and a procedure for this purpose.

    Sincerely,

    Biggy

    CREATE ENDPOINT sql_getEmployee
    STATE = STARTED
    AS HTTP(
    PATH = ‘/sql/AdventureWorks’, AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR) )
    FOR SOAP (
    WEBMETHOD ‘pr_getEmployee’
    (name=’AdventureWorks.dbo.pr_getEmployee’,
    schema=STANDARD),
    WEBMETHOD ‘GetProductsNoSchema’
    (name= ‘AdventureWorks.dbo.pr_getEmployee’,
    schema=NONE),
    WSDL = DEFAULT,
    BATCHES = ENABLED,
    DATABASE = ‘AdventureWorks’,
    NAMESPACE = ‘http://www.www.mywebsitename/’)

    Create Proc pr_getEmployee
    As
    Select H.EmployeeID, H.Title, YEAR(H.HireDate) as HireYear, H.Gender
    From HumanResources.Employee H
    Where H.EmployeeID <5
    Return
    Go

  423. hai dave

    how can i over come database suspect

    i found one database instance is showing suspect
    when i can not access it.
    is there any chance to restore it .

    can u please give solution please

    thank u
    vego reddy

  424. Hello, I am facing this issue:

    I have 2 severs one of them is 32 bits with sqlserver 2005 the other one is 64 bits with sqlserver 2005

    I made a backup in the fisrt one server (32 bits). I restored the backup in the second one server (64 bits).

    The nonprintables characters look diferents.

    So, when I run a report made with reporting services and that report query the datas in 64 bits server and the report has parameters which query datas with nonprintables characters the following message is showed.

    There is an error in XML document (1, 17960).
    hexadecimal value 0x1A, is an invalid character. Line 1, position 17960.

    thanks a lot,

  425. Hello Pinal Dave,
    Thank you for your web SQLAuthority.com and postings. That’s helped me in great deal…

    after following your instruciton of “SQLSer 2005 – Create Script to Copy Database Schema and All The Objects – Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects”, the SP, table names, Functions, Triggers are created, but i still have a trouble to load all rows into the tables. I tried the SSIS, import/export, copy database that none of are working. Can you make a suggestion for me to get this work done?

    Thank You!
    Denise

  426. Hi,
    What would correct an error like this!
    Thanks

    This was a from saved backup that I tried to restore back into my database. They are also all program generated, I did no coding.

    MySQL said:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘-3.29.2008_15-31-03_thetalke/’ at line 1
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,

  427. Hi Pinal,
    I have a question plz reply, In sql server how could we get the name of columns which are produced by a select statement,Actually i wanna make a utility that generates code for producing temporary table by supplying sp name only. I have seen that sql server query execution plan has property outputlist but how to use that through query.

  428. Can you tell me how to use the INSERT INTO IN clause to transfer data from a table in MS SQLServer database into a table in a Oracle Database?

  429. Hello Sir,

    Lets say i have 2 tables or 10 tables.
    In all these tables , there is one column same; say orderNo.
    Now if the value of the column is changed in any of the tables , how can we populate the changes of that column in all tables?

    Regards

  430. Hi Pinal / SSIS users,

    In SQL Server 2005, in SSIS, how do I get datestamp on file name while creating/copying/moving file.

    Example: I’m able to generate file name but I want to generate it with date timestamp on it.

    Thank you in advance.

    Shreyas

  431. Dear Pinal Dave,

    i have one query,

    how to display the table content in column,

    For Example,

    Emp Table having 3 rows and 2 Column,

    select * from Emp
    _________________
    | Emp-id | Emp-Name |
    —————————-
    | 1 | John |
    —————————-
    | 2 | Dave |
    —————————-
    | 3 | Max |
    —————————-

    the query result will be in normal, but i want the result to in Column views..

    _________________________________________
    |Emp-id | 1 | 2 | 3 |
    ——————————————————————
    |Emp-Nme | John | Dave | Max |
    ——————————————————————

    how can we acheive this type result using Sql Query.

    Thanks in Advance.
    windows_mss

  432. Dear Pinal,

    I receive below error when firing any DML operation on a specific database.

    ——————————————————————————–
    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
    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.
    ——————————————————————————-

    When I enable it, Update runs but I am getting NULL as a ouput

    output
    —————————————————————————————————————————————————————————————————————————————————————
    NULL

    (1 row(s) affected)

    This causing application to get extra row??

    Please suggest me what to do….

    Thnx in advance
    Rakesh

  433. Hi Pinal Dave,

    i’m developing a project and i use SQL server to store my data.
    i made a table which contain at least 70,000 record and this
    number may be increased to 200,000 or even half million.

    if i want to search for specific record, can i use the primary key of the table to search for this record, you know that
    the primary key has the indexer cabability which leads to faster search .?

    or i should partition this table first using horizontal or vertical partition ? or what ???

  434. hi pinal i am reading a csv file and i want to bulk insert the data to sql server 2005 table i tried your bulk insert code but it is giving me error of syntax can you please help me..

  435. Hai Pinal Dave,

    This is srinivas to day i saw ur web site it was very nice and i saw the answers given by you.I had no of queries in sql server can u please guide me for better results.

    Actually i am a .Net developer (fresher) write now i am working in web applications.If possible can u give me ur mailid to me

    Thank you,
    Srini

  436. Greetings Pinal

    Wow what a rich source of information you have! I would like to ask the following question

    Which one is best practice, more efficient and speedy?

    SELECT BinLoc,
    InventoryTag
    FROM MyTable AS Mt
    WHERE (LTRIM(RTRIM(Mt.BINLOC)), 2) NOT IN(‘MC’)

    -or-

    SELECT BinLoc,
    InventoryTag, (LTRIM(RTRIM(Mt.BINLOC)), 2) NOT IN(‘MC’) As FormattedField,
    FROM MyTable AS Mt
    WHERE FormattedField NOT IN (‘MC’)

    Is formatting best done in WHERE clause, SELECT clause or none of the above. What do you suggest.

    Thank you!!

    • Use second method. It should actually be

      SELECT BinLoc,
      InventoryTag, (LTRIM(RTRIM(Mt.BINLOC)), 2) As FormattedField,
      FROM MyTable AS Mt
      WHERE FormattedField NOT IN (‘MC’)

      so that the index will be used (if available on that column)

  437. Hi Pinal,

    I have inserted around 3 lakhs of records in a table in SQL server2005 from a dat file.
    I have to update and insert that table with data from new dat file with increased number of records records, in very less time.
    Which is the best method.

    Thanks,
    Vijay.

  438. Hi Pinal, I am having trouble & thought that you might be the perfect person to ask for input.

    I am creating a dynamic new members page & would like to display them acconding to the ‘datecreated’ field in table A.
    I have created a view that pulls the info I need from the 3 different tables. I have been able to display them all here http://www.tennisindustry.org/Membership/NewMembers.cfm.
    My issue is that I would like to be able to display them with a Month heading for each of the 12 months and cfif out the months that aren’t here yet or that had no members join in that month. I have a static page here, http://www.tennisindustry.org/Membership/NewMembersStaticWeb.cfm as an example of what I want to do which can be found at the bottom of our home page http://www.TennisIndustry.org.

    Any thought or direction that you might offer would be greatly appreciated.

    Thank you very much…

  439. Yosias,

    good question; but logically thinking formatting in where clause will be faster, if that makes sense reason being for where clause it will compare it just one time but in select clause it has to format it (n-row) times.

    However, Pinal can confirm or correct me if I’m wrong.

    Shreyas

  440. Develop,

    I dont know if there is one statement which will force update on all tables. But I guess updating all tables will have the desired output.

    Shreyas

  441. Talker,

    I believe that you haven’t coded for restore and assume that you are using sql server manual restore utility for restoration.
    Having said that, have you tried restoring using query analyser and restore command? Do you still get same errors?

    Shreyas

  442. Hello Pinal,
    I have been working with PL/SQL development for last 4 years. I need a small advice. My client is having production SQL 2005 db’s of approximately 2GB’s (back up file size) in 9 sites / 4 different contries. We need to do a data warehousing in the head office for consilidated reports for the Group.
    The possibilities for me are
    1. Shipping of back up files every day from each sites to head office using an ftp transfer or so. – This is heavy load and will take too long time
    2. Shipping of incrimental back up from each sites to head office every day – An Incremental back up will take the logs from last full back up. So in sites there is the possibility of taking the back up of the db any time for certian purpose, so the integrity of teh incremental back up is not reliable.
    3. Shipping of refined data (a seperate db contains refined data) from sites every day to head office after ETL process locally – This is what I planned to do but size is getting big each day.

    I just want to know, is there any method of tranfering the logs every day automatically through internet to a partcular mailid or ftp address and we can update that in the head office?

    Thanks and regards
    BineeshThomas

  443. Hi Pinal Dave,

    i’m developing a project and i use SQL server to store my data.
    i made a table which contain at least 70,000 record and this
    number may be increased to 200,000 or even half million.

    if i want to search for specific record, can i use the primary key of the table to search for this record, you know that
    the primary key has the indexer cabability which leads to faster search .?

    or i should partition this table first using horizontal or vertical partition ? or what ???

  444. Hi Develop,

    lets say you have 4 tables: a, b, c and d
    All have got orderno. table a is master table whereas b,c and d are detail tables having foreign key to table a(orderno). In this case you update all the detail tables viz. b, c an d with new order no and then update table a with order no

    hope that makes sense

    Shreyas

  445. New Question.
    I’m looking through some code at my new job and I ran across this:

    *The code is functional, so just look at the general design

    INSERT rdi_Class
    SELECT DISTINCT
    @Var_A, Var_B, Var_B, NULL, Var_C, NULL, Var_D, NULL,
    Var_E, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0, 1, NULL
    FROM Table_B
    WHERE LTRIM(RTRIM(Var_C))+LTRIM(RTRIM(Var_E))+LTRIM(RTRIM(Var_B))+LTRIM(RTRIM(Var_D)) IN
    (SELECT DISTINCT LTRIM(RTRIM(Var_C))+LTRIM(RTRIM(Var_E))+LTRIM(RTRIM(Var_B))+LTRIM(RTRIM(Var_D))
    FROM Table_C)

    I re-wrote it as:

    INSERT Table_A (Var_A, Var_B, Var_B, var_C, Var_D, Var_E, another_var, another_var, another_var, another_var)
    SELECT DISTINCT @Var_A, c.Var_B, c.Var_B, c.Var_C, c.Var_D, c.Var_E, 0, 0, 1, NULL
    FROM Table_B c
    INNER JOIN Table_C cr ON c.Var_C = cr.Var_C
    AND c.Var_E = cr.Var_E
    AND c.Var_D = cr.Var_D
    AND c.Var_B = cr.Var_B

    The previous code runs in under 1 second, my new code takes ~1:57… minutes. I put indexes on all of the coloumns I need to make a comparison and it stil ltakes ~50 seconds. All columns are varchars because we’re importing ‘untrusted’ raw data. The tables are ‘generic’ tables created while making a DTS project and each column is varchar(255).

    it seems to me that ltrim/rtrim and concatinate your variables runs faster than doing inner joins in this case.

    Table_B is about 6k rows and Table_C is about 50k

    Query Estimation for the first example spends 17% on hash inner join

    My code does 40% on inner join

    P.S. being that these tables are truncated and repopulated during our nightly import, we have no indexes. Having a 2min run time for a nightly process is no issue, but if the previous query runs as fast as I can hit F5, then there’s no reason to change it. Even if it doesn’t look ‘propper’

    Thanks