Contact Me – Archive 7

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

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

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

Copyright violation and Reproduction of blog:

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

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.

Community Rules

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

pinal “at” SQLAuthority.com
pinaldave “at” yahoo.com

About these ads

606 thoughts on “Contact Me – Archive 7

  1. Hi pinal
    i have gone through all articles which u hav pblished and am very much impresse with ironical work n here i hav a problem,can u help out

    i have to extract data from AR invoice and then i had to populate the data into two targeted tables, i have some validations to be done to the receipts before inserting into tables.. so please can you provide me a package that can do the above work

    Thanks & Regards
    P Manish Rao

    • Hi Pinal

      We have a scheduled process each night which drops and recreates index’s on one of our main Databases. This in turn causes the transaction logs to flux. As the database gets bigger then this causes the transaction log max filesize to get bigger etc.

      I was in the middle of doing some study for exam 70-433 for sql 2008 and noticed an Entry advising that its commen practise to change the recovery model of a database to Bulk logged while certain operations are done which are not intended to be logged.

      Just thought to ask your thoughts on this as I suppose it would be something like

      a) Doing a full backup prior to Index recreation
      b) flick the recovery model to Bulk Logged
      c) Drop and recreate the Indexes

      Any general thoughts/advise would be apreciated.

      Kind Regards,

      Matt

  2. I am trying to SQL 2005 database “backup permissions and indexes” as told to me by our application software company. We are planning to upgrade our database in May to a new version of the application software, and I was told by the software company experts to not only do a full backup of our SQL 2005 database, but to “backup permissions and indexes. Could you tell me how to do that? They suggested using the “ALL TASKS–>GENERATE SCRIPTS” option but the guy wasn’t for sure how to do it because he does not work that much with SQL. Could you help me please? We are going to do the conversion on May 3rd and I need a good backup of our SQL 2005 database including the permissions and indexes, etc.

    Thank you very much.

  3. hi Pinal,

    I have installed SQL server 2008. Can you pls guide me with the steps to configure Reporting Server 2008.

    thanks
    -jaya

  4. Hi Pinal,

    first of all saying that your blog is really excellent and you’re doing a great job. But I would to expose you a
    scenario and how you would avoid the use of nested views (i read out there they are evil but I think the are god). I tell you:
    Imagine you have the following tables: BasicSubOperations, Operations, WorkUnitsLevel1, WorkUnitsLevel2, WorkUnitsLevel3. Imagine you have a view for each of the previous tables. A WorkUnitLevel3 can contain several WorkUnitLevel2 and each WorkUnitatLevel2 can contain several WorkUnitsLevel1. Each WorkUnitLevel1 performs an operation and each operation can be compound of serveral BasicOperations. BasicOperations can have a lot of information but image they have a bit field called ProcessInmediatelly. This field has to be propagated from BasicOperations to WorkUnitsLeve3 in the manner that if one BasicOperation has this field to 1 then this field is 1 otherwise 0. And now the question, are the nestedviews justifieds in this scenario for propagating this field ? if no, which are the alternatives (I no triggers but i thing this had to be the last option,no?)

    Thanks in advance.

  5. Dear pinaldev,
    Here we are using sql 2008 in that ..we are having 2 databases,
    and test1 and test2 ,
    1. in this my database size is 4 gb and log file size is growing up to 12 gb …but so if it comes near 10 gb my database is going to recovery mode…
    2.getting share memmory error and sql disconnecting while working..if restart the service its working for and hour or 2 …if users use more it comes every 30 min or 40 min..
    kindly advice me what the reason..

    Thanks
    Prabhu con

  6. hi Pinalkumar Dave,

    Here we r using sql server 2000, i need to lock the my database, can u pls guide me regarding lockin specific database. iam waiting for u r valuable suggestion

    Regards,
    T.Rajesh Kumar

  7. I have a table where the information is
    empId, FName, SName, TName, LName
    1,ABC, DEF, GHI, JKL
    2,MNO,,,PQR
    ……..
    Another table has the user updated information of the same
    empId, FName, SName, TName, LName
    1,ABC1, DEF, GHI, JKL4
    2,MNO1,,,PQR4

    The final output that I require is (when filtered for empno =1)

    NewName, Old Data, New Data, updated — (4 new Col Names)
    FName, ABC, ABC1,yes
    SName, DEF, DEF,no
    TName, GHI, GHI, no
    LName, JKL, JKL1, yes

    all suggestions are welcome.
    my plan of action now is

    1, create a new table with the fields
    in this case a table with 4 fields.

    2, fill the table with the information
    that will require a field to be retrived at a time, that will be time consuming as the actual requirement has over 50 fields to be displayed.

    thanks in advance
    Shibu P

    • Actually it is pivot col to row (problem).

      I have a table where the information is
      empId, FName, SName, TName, LName
      1,ABC, DEF, GHI, JKL
      2,MNO,,,PQR
      ……..

      Another table has the user updated information of the same
      empId, FName, SName, TName, LName
      1,ABC1, DEF, GHI, JKL4
      2,MNO1,,,PQR4
      ……..

      The final output that I require is (when filtered for empno =1)
      NewName, Old Data, New Data, updated — (4 new Col Names)
      FName, ABC, ABC1,yes
      SName, DEF, DEF,no
      TName, GHI, GHI, no
      LName, JKL, JKL1, yes

      all suggestions are welcome. I am looking for a better solution than the one I worked out.

      my plan of action now is

      select ‘FName’ as newName, a.fname as oldData, b.fname as newData
      from table1 as a left join table2 as b
      on a.empid = b.empid
      UNION ALL
      select ‘SName’ as newName, a.sname as oldData, b.sname as newData
      from table1 as a left join table2 as b
      on a.empid = b.empid
      ….

      thanks in advance.
      best regards
      Shibu P

  8. Hello Mr. Pinal Dave

    I’m in Vietnam, I’m really like this blog! And I have a quession want to ask you.

    I have Parent table with a primary key is PK,

    Now, I want delete a record in Parent table, but I want check, if PK is Exists in some Children table then can’t delete that record.
    I really set relationship cascade for those talbes.

    Have any query to check that issue?

    • This can be done with normal FOREIGN KEY construct. Child table references Parent table and this prevents any row in Parent table, that has references in Child table, to be deleted. For example:

      CREATE TABLE Parent (ID INT PRIMARY KEY)
      GO
      CREATE TABLE Child(ID INT PRIMARY KEY, ParentID INT REFERENCES Parent(ID))
      GO

      INSERT INTO Parent SELECT 1
      GO
      INSERT INTO Child SELECT 1, 1
      GO

      –This throws an error
      DELETE FROM PARENT WHERE ID = 1
      GO

  9. Hi Marko Parkkola!
    Thanks for reply my question.

    But my problem is:
    I was set cascate for my tables. So , if I delete from Parent key, then All records in Children (not child) tables will be delete. I want to do this:

    IF (!Condition)
    /*Delete a record from Parent Table and All record from Children table*/
    Else
    /*Don’t Delete From Parent TAble, But delete recoreds From Some Children tables in All Children tables of Parent*/

    In that: Condition is a expression to check : Whether PK Exists in Children Table

    • Ah, Okay. Now I got it. There is (at least) two ways to do this.

      1) Create “instead of delete trigger” to Parent table. There’s a restriction though that you can’t define instead of delete trigger to table that has FK defined with DELETE action. In the trigger you check for condition and delete rows if needed. Here’s the trigger:

      CREATE TRIGGER TRG_Parent_Cascade ON Parent
      INSTEAD OF DELETE
      AS
      BEGIN
      DECLARE d CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT ID FROM deleted
      OPEN d

      DECLARE @id INT
      FETCH NEXT FROM d INTO @id

      WHILE @@FETCH_STATUS = 0
      BEGIN
      IF (!Condition)
      BEGIN
      – Delete all children
      DELETE FROM Child WHERE ParentID = @id
      – Delete parent
      DELETE FROM Parent WHERE ID = @id
      END
      FETCH NEXT FROM d INTO @id
      END
      END

      2) Create procedure to do all this.

      CREATE PROCEDURE CascadeDelete
      @ParentID INT
      AS
      BEGIN
      IF (!Condition)
      BEGIN
      – Delete child
      DELETE FROM Child WHERE ParentID = @ParentID
      – Delete parent
      DELETE FROM Parent WHERE ID = @ParentID
      END
      END

    • 1)FOR 32BIT SYSTEM
      50 instances on a stand-alone server for all SQL Server editions.

      SQL Server supports 25 instances on a failover cluster.

      2)FOR 64BIT SYSTEM
      50 instances on a stand-alone server.

      25 instances on a failover cluster.

  10. I am using Access pass through queries to run stored procedures and return tables to Access. The queries use system DSNs, SQL Server authentication, the ‘sa’ user ID and associated password.

    After fifty or so runs, the DSNs appear to not connect any longer and the Access pass through queries (set not to timeout) just hang indefinitely.

    The SQL database tables involved are relatively small, and work fine for limited periods of time.

    If I use manually reset the ‘ODBC Connect Str’ in each of the pass through query properties and restart my computer, the queries seem to work again for – again – perhaps fifty or so runs.

    Are you aware of any similar situations or do you have any guesses as to what the issue might be?

    I’ve read the post below and had hoped that it was the issue, but it doesn’t appear to have been the issue.
    http://blog.sqlauthority.com/2009/04/23/sql-server-fix-error-18486-login-failed-for-user-sa-because-the-account-is-currently-locked-out-the-system-administrator-can-unlock-it-unlock-sa-login/

  11. Just wanted to throw a post up here and ask for anyones experience with Idera’s SQL Safe. We are in the process of possibly purchasing a SQL compression and encryption software and its between Idera and Red Gate. Any information or insite would be appreciated

    Thanks
    Steve

  12. Following is my stored proc :-

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER PROCEDURE[dbo].[mob_cfas_final]
    as

    BEGIN
    DECLARE @mobility CHAR
    DECLARE @cfas CHAR
    DECLARE @cursor_company_site CURSOR

    Print ‘tblsite conversion begins’

    SET @cursor_company_site = CURSOR FOR
    SELECT companycode FROM CinguLINC..tblSite
    OPEN @cursor_company_site
    FETCH NEXT
    FROM @cursor_company_site INTO @mobility
    WHILE @@FETCH_STATUS = 0
    Begin

    SET @cfas = (SELECT cfas_companycode FROM tblMobCfasCompanyCodeMapping WHERE mob_companycode = @mobility )
    select @cfas
    if (@mobility is not null and @cfas is not null )
    update CinguLINC..tblSite set companycode = @cfas where companycode = @mobility
    FETCH NEXT
    FROM @cursor_company_site INTO @mobility
    END
    CLOSE @cursor_company_site
    DEALLOCATE @cursor_company_site

    Print ‘tblsite conversion ends’

    DECLARE @cursor_pymnttypelkup CURSOR

    DECLARE @mobility1 Float
    DECLARE @mobility2 CHAR
    DECLARE @cfas1 CHAR
    DECLARE @cfas2 CHAR

    declare @a char
    declare @b char

    Print ‘tblpaymenttypelkup conversion begins’

    SET @cursor_pymnttypelkup = CURSOR FOR
    SELECT account,costcenter FROM tblPaymenttypeLkUp
    OPEN @cursor_pymnttypelkup
    FETCH NEXT
    FROM @cursor_pymnttypelkup INTO @mobility1, @mobility2
    WHILE @@FETCH_STATUS = 0
    Begin

    set @cfas1= (SELECT cfas_account FROM tblMobCfasAccountMapping WHERE mob_account = @mobility1 )
    select @cfas1
    SET @cfas2 = (SELECT cfas_costcenter FROM tblMobCfasCostcenterMapping WHERE mob_costcenter = @mobility2 )
    select @cfas2
    if (@mobility1 is not null and @cfas1 is not null )
    update tblPaymenttypeLkUp set account = @cfas1 where account = @mobility1
    if (@mobility2 is not null and @cfas2 is not null )
    update tblPaymenttypeLkUp set costcenter = @cfas2 where costcenter= @mobility2
    FETCH NEXT
    FROM @cursor_pymnttypelkup INTO @mobility1, @mobility2
    END
    CLOSE @cursor_pymnttypelkup
    DEALLOCATE @cursor_pymnttypelkup

    Print ‘tblpaymenttypelkup conversion ends’

    DECLARE @cursor_vouchertemplate CURSOR

    DECLARE @mobility3 CHAR
    DECLARE @cfas3 CHAR

    Print ‘tblvouchertemplate conversion begins’

    SET @cursor_vouchertemplate = CURSOR FOR
    SELECT account,company FROM tblVoucherTemplate
    OPEN @cursor_vouchertemplate
    FETCH NEXT
    FROM @cursor_vouchertemplate INTO @mobility1, @mobility2
    WHILE @@FETCH_STATUS = 0
    Begin

    SET @cfas1 = (SELECT cfas_account FROM tblMobCfasAccountMapping WHERE mob_account = @mobility1 )
    select @cfas1
    SET @cfas2 = (SELECT cfas_companycode FROM tblMobCfasCompanyCodeMapping WHERE mob_companycode = @mobility2 )
    select @cfas2
    if (@mobility1 is not null and @cfas1 is not null )
    update tblVoucherTemplate set account = @cfas1 where account = @mobility1
    if (@mobility2 is not null and @cfas2 is not null )
    update tblVoucherTemplate set company = @cfas2 where company = @mobility2

    FETCH NEXT
    FROM @cursor_vouchertemplate INTO @mobility1, @mobility2
    END
    CLOSE @cursor_vouchertemplate
    DEALLOCATE @cursor_vouchertemplate

    Print ‘tblvouchertemplate conversion ends’

    DECLARE @cursor_summaryvouchertemplate CURSOR

    Print ‘tblsummaryvouchertemplate conversion begins’

    SET @cursor_summaryvouchertemplate = CURSOR FOR
    SELECT account,company,costcenter FROM tblSummaryVoucherTemplate
    OPEN @cursor_summaryvouchertemplate
    FETCH NEXT
    FROM @cursor_summaryvouchertemplate INTO @mobility1, @mobility2, @mobility3
    WHILE @@FETCH_STATUS = 0
    Begin

    SET @cfas1 = (SELECT cfas_account FROM tblMobCfasAccountMapping WHERE mob_account = @mobility1 )
    select @cfas1
    SET @cfas2 = (SELECT cfas_companycode FROM tblMobCfasCompanyCodeMapping WHERE mob_companycode = @mobility2 )
    select @cfas2
    SET @cfas3 =(SELECT cfas_costcenter FROM tblMobCfasCostcenterMapping WHERE mob_costcenter = @mobility3 )
    select @cfas3
    if (@mobility1 is not null and @cfas1 is not null )
    update tblSummaryVoucherTemplate set account = @cfas1 where account = @mobility1
    if (@mobility2 is not null and @cfas2 is not null )
    update tblSummaryVoucherTemplate set company = @cfas2 where company = @mobility2
    if (@mobility3 is not null and @cfas3 is not null )
    update tblSummaryVoucherTemplate set costcenter = @cfas3 where costcenter= @mobility3
    FETCH NEXT
    FROM @cursor_summaryvouchertemplate INTO @mobility1, @mobility2, @mobility3
    END
    CLOSE @cursor_summaryvouchertemplate
    DEALLOCATE @cursor_summaryvouchertemplate

    Print ‘tblsummaryvouchertemplate conversion ends’

    END

    It gives me an error that -

    Msg 512, Level 16, State 1, Procedure mob_cfas_final, Line 88
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.

    Please let me know what is syntax for cursor returning multiple vlaues

  13. Hi,
    Mr. Pinal Dave

    I am searching DB Email and sp_send_dbmail and i got ur block. It’s really a nice blog..

    I faced a problem when i m solving one of my issue.

    Scenario: Everyday i need to update Trail User password and send it to admin. So I create a SP (Update password & send email using sp_send_dbmail) and a Job which run the job.

    But the problem is, if the DBemail is down or problem with Internet, the password is updated but no email send.

    So, Is there any way to know the status of email and restart the job if the email is not sent.

    Regards
    Animesh Chandra Dey

  14. Hi,
    I have tables which says names employees and department.
    Employees have (empid, ename, deptid, mgrid)
    eg: filling table like this
    Row –>(12, Paul, 01, 34)
    Row –>(34, Mike, 02, 56)
    Row –>(56, Pinal, 01, 78)
    Department have (deptid, deptname)
    eg:
    Row–>(01, Development)
    Row –>(02, Testing)

    Now please help me to retrieve information of the particular employee say empid 12 in a manner like this

    [12(empid), PAUL(ename), Development(deptname), mike(ename)(is direct mgr to paul), mgr's deptname, mgr's mgrname, mgr's mgr deptname..... ]

    Can it be possible to get it in one query.
    I tried using self Joins and joins but i could not able to retrieve mgr’s mgr information and mgr’s mgr’s mgr information. Please do favour to me.
    Thank you in adv.
    Yours truly,
    Confused student.

    • @Paul

      You cannot return an undetermined amount of COLUMNs. Even PIVOT() requires a known list. Though, if was really needed, you could determine how many COLUMNs are required and build the statement dynamically. Or you could have dummy placeholders for possible levels of management. Neither of which is usually considered good practice.

      Ideally, each would be returned on it’s own row. Is there a reason you need them all in the same record?

  15. Why Ole Automation Procedures are in disable mode Defaultly.

    Does it causes any issues if i enable them on Production environment.

  16. Hi Brain Tkatch,

    I appreciate your answer dear. There is nothing reason. I just wanted to confirm about it. Cause I was asked (sorry forced) to do it in my application at office, i said it would be difficult but still they asked so i was confused. Thank you.

  17. Hello sir,

    How can i ensure sql server agent service can access the entire log server .

    from which of these can i do it

    remote Service account.
    remote System account.
    Local Service account.
    Domain account.

    pls explain for the specific answer as m new to SQL Server

    thnx

  18. Hello,

    This is Niikunj Panchal. I am installing SQL Server 2008 on Windows 7 64 bit home premium. but it gives me the error: ”

    Microsoft .NET Framework 3.5 installtion has failed. Sql server 2008 requires .NET framework 3.5 to be installed.”

    When I tried to install Microsoft .Net Framework, it gives setup error. i found some information on net this shows that Microsoft .NET framework is already installed with Windows & 64 bit home premium.

    Can you please give me the exact idea, it’s very important for me to install SQL server 2007 on Windows 7.

    I am waiting for your reply.

    Thank you.

    Nikunj Panchal.

  19. when I take backup of any database on sql serve 2008, i got error msg like,
    “Property BackupDirectory is not available for Settings ‘Microsoft.SqlServer.Management.Smo.Settings’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)”.

  20. Hi Pinal,

    Actually i want to generate a excel file with all tables from database my sql server 2005 with table headers but not the table data.
    i.e only table description of each and every table.

    if you could help me in this it would be greatful.

    thanks
    rita

  21. I hate to bother you with what should be something simple for me to do. I decided today that I would like to download the AdventureWorksDB database and install it on my matchine. I’m running a SQL Server 2005 Express Edition on my Windows Vista 64-Bit Edition.

    For one reason or the other I am not able to attach the database due to an (Access Denied) Error. This seems very odd to me since I am an ADMIN user on the machine and the folder where the database is located is SHARED as well.

    The main thing I notice is that my Data folder is located in a Program Files (x86) folder instead of the normal Program Files folder. I can’t image that would cause a problem, but I’m not able to get past it. I’ve never had a problem such as this, so that’s probably why I’m not getting anywhere right now.

    I get the following error after attempting to Attach the files.

    Msg 5120, Level 16, State 101, Line 1
    Unable to open the physical file “C:\DB\Server\Samples\AdventureWorks_Data.mdf”. Operating system error 5: “5(Access is denied.)”.

    My T-SQL Script is as follows:

    exec sp_attach_db @dbname = N’AdventureWorks’,
    @filename1 = N’C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf’,
    @filename2 = N’C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf’

    Would you mind pointing me in the right direction so I can resolve this issue?

    By the way, I like the information you provide on your web-site. Your information and the site is well done. Good job.

    Thanks, Greg

  22. SQL Server 2008 server named
    ITEXAMWORLD-DB1. There are also SQL Server Agent jobs on the server. Furthermore the
    database is backed up at a daily
    basis. One morning, the master database on ITEXAMWORLD-DB2 went down. You then restore and
    rebuild the database. The Itexamworld.com managers want ITEXAMWORLD-DB1 to work as it was
    in
    the past.
    What should you do?
    A. You should consider rebuilding the entire SQL Server Web Service for the database.
    B. You should consider reserving the model and master databases for the database.
    C. You should consider reserving the Log Transaction for the database.
    D. You should consider rebuilding the entire table schema for the database.

    pls let me know the answer as m new to SQL SERVER

    Can anybody link me to free MCTS 70-432 Study material too

  23. Hi,

    One interviewer asked me if any new requirement forces us to make a child table with primary and Foreign key relation, we can create one child table. But if we keep on going for creating new child tables like that whenever new requirement comes that is after 20 years or something,
    What is the best way to create or design a database for considering future requirements also.

    How can we make database design useful for future requirements also.How we can make database flexible. He also said there is a straightforward answer for this question. If i answer this question my job is going to confirm.

    Please let me know as early as possible the answer for this questions.

    Sincerely
    Murali Krishna.

  24. Hi Pinal,

    your articles about SQL are very interesting.

    Congratulations for your knowledge.

    Bye
    Marcus Linares – Brazil

  25. Hi Pinal,

    I installed AdventureWorks 2008, but I could not use it becuase I could not find the backup file. Also, I tried to attach it, but I failed .

    Ali
    Best Wishes

  26. Triggers fire once per statement execution, not once per row.

    i have wrote a trigger that should fire on each row insert.
    but it’s fire only once when i import data from a csv file or bluk insert statement.
    how can i fire trigger on each row insert.

    thanks
    shahid

  27. I wnt the window group user can connect to the sql server instance at specific time only ie from 10 to 11 pm and not at other time

    let me know hw can i do it ,with reason .

    AS m new to sql server

  28. Dave,
    I just wanted to take the time to thank you for all the help I have received from you via your website.

    I am fairly new to the database environment. I switched jobs from a Network Administrator to a DBA. Our shop is Oracle, but I was given the job of bringing Sql Server into our environment. Your knowledge and suggestions have helped me imensely.

    Thanks SO much for all your help !!!
    Leroy Larson
    DBA
    State of Minnesota, Dept of Transportation

  29. Dear Pinal,

    I need you expert help for my problem in SLQ Server.Below is the text for the problem

    hey i have my data in the following format in .txt file. There are more than 1000 line, but here i have pasted just 3 for demonstration of porblem.

    Line 1:
    000000004998152225|6783|1000|E|1110006042|000010|2006|03| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060217

    Line 2:
    F01U002255 |6783|1000|E|1110003940|000010|2006|03| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060217

    Line 3
    000000004998151567|6784|1000|E|1110006225|000090|2006|02| 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 |20060216

    I wrote following script to copy this data from .txt file to table in the server

    [db_scpm-sql].[testing].[mska3] in “Z:\40_SCPM\80_SQL-server\80_Implementation\90_testdata\MSKA.txt” -c -t”|” -T -S FE0DBP97

    but here it give me the following error. I tried with the format file option also, but it gives the same error.

    Starting copy…
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca
    st specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca
    st specification
    SQLState = 22005, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Invalid character value for ca
    st specification

    It would be so kind of you, if you would spend your some valuable time and help me.

    Any kind of help will be highly appreciated.

    Thanks a lot.

    Regards,
    Vishal Patel

  30. Hi,

    Can anyone please let me know if there are any discounts for Microsoft certifications. I am planning to take MS 70-432. Also, if you can let me know the materials I should refer to study for this exam.

    Thanks
    Hets

  31. Hi Pinal..

    I have serious problem while installation of Sqlserver 2008 in windows 7 (x64) home Premium..

    First I have installed VS2008(Ent-US) and updated VS2008 SP1..
    Then i started installing Sqlserve 2008 (Ent edition).. it is asking “Sqlserver 2008 required .Net Framework 3.5 SP1 required” I have download from microsoft site..
    I started intalling the “dotnetfx35setup.exe”. the exe is extracting and after there is updates…

    Second problem..
    I tried with sqlserver 2005 developer edition..installation is going fine.. even i enabled the IIS (windows 7).. due to this reporting services are not enabled while installation…

    thanks for your help in advance….

    Thanks
    umamahesh

  32. Hi Dave,

    Good morning. I read many of your articles. It is very much useful. I have a doubt. How can I implement set identity insert on option in all tables in a database using “sp_msforechtable” sp.

    The reason:

    I need to refresh table from SQL 2000 to SQL 2008. For that I tried this option before doing DTS Import from SQL 2000 to SQL 2008. But the option is not working. When I check in the DTS Wizard, the option is not selected. I manually select the option in all tables.

    Thanks & Regards,
    Balavenkatesh

  33. Hi Shahid,

    Trigger fired per statement. You don’t need to fire trigger after each row insert/update.

    You just need to use inserted table, which you can access in Trigger.

    This table gives you each row that you want to process.

    Tejas
    SQLYoga.com

  34. Hi Pinal ,

    I read your articles regularly, these are really informative, keep it up.

    I never read about Load Testing of SQL SERVER Database

    How can we Load Test a SQL SERVER Database ?

    Regards,
    M.Zafar Iqbal

  35. Hi

    I have two databases DB1 (used by my application) and DB2 (third party) under same SQL Server instance

    I get the following error

    SqlException ERROR: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

    connecting to my database (DB1)

    To fix this i used below

    To assign a TCP/IP port number to the SQL Server Database Engine
    In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for , and then double-click TCP/IP.

    In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you want to configure.

    If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.”

    Once I deleted those zeros and put in 1433 for TCP ports on each IP address DB1 works fine, but cannot access DB2 (third party)

    So basically what is the default settings 0 or 1433??

    how do i get my DB work with default TCP/IP settings

    Thanks
    Gauls

  36. Hi Pinal,

    I need your kind help.

    I have two big tables in MS SQL database, i need to change column from int to bigint. Both tables have PK and it is not allowing me to alter column directly.

    Is there any method i can use to change cloumn type? please help. Thanks in advance!

    Regards,
    Ratna Kumar.

    • Hi Ratna,

      If table is too large it creates a problem. You need find alternate. You cam do this by:

      1. Add new Column Called: Temp
      2. Update new column “Temp” with your column
      3. Rename yourcolumn (e.g. “TempDelete”)
      4. Rename “Temp” column to your actual columnname.

      This way it will allow you to do so.

      Tejas,

      SQLYoga.com

  37. Hi Pinal

    1.Sql server upgradation before or after how to find out Repication data .
    2.In my log shiping stand by server Log file Corrupted
    to resolve the problem

    please tell …sir

    Thanks&Regrads
    harishkumar.M

  38. dear,
    i am trying to install SQL server but it is giving me the following error…

    the sql server system configuration checker cannot be executed due to WMI configuration on the machine …. Error:2147749907 (0*80041013)

  39. Pinal,

    Is there any way to put multiple Else block in Case statement.

    CASE expression
    WHEN expression1 THEN do something
    WHEN expression2 THEN do something
    ELSE
    some code
    ELSE
    some code

    END

  40. Hi Pinal

    1.Sql server upgradation before or after how to find out Repication data .

    2.In my log shiping (Secondary )stand by server Log file Corrupted,how to to resolve the problem

    3.in logshipping secondary server transcation log file Corruted and how to Restore the secondary server.

    please tell …sir

    Thanks&Regrads
    harishkumar.M

  41. I have been regular visitor to your blog and it has helped me lot in difficult situation.I keep my sql server knowledge updated by viewing your blog.

    I took a question about clustered Index which had choice between two given answers
    A>Rows in a table are PHYSICALLY stored in the clustered index order
    B>Rows in a table are LOGICALLY stored in the clustered index order.
    I checked the first answer i.e A but the correct answer is B.Can I know how answer B is true?Since we all know that the data in a clustered index is stored in physical order.

  42. hii pinal
    i need your help…
    this is my table named tbl_subcode

    subject subjectcode
    ————————————————– ———–
    english 1
    maths 3
    science 4

    i am having another table named tbl_markdetails

    addno subjectcode marks
    ——— —————– ———
    12340 1 52
    12340 3 100
    12341 1 90
    12341 3 99

    i need the result as

    addno english maths
    ————- ———- ———–
    12340 52 100
    12341 90 99

    is it possible to get the result like this?
    if yes answer as soon as possible…
    thanks in advance….

  43. Hello Sir

    I have aws amazon server. When i want to take backup of my database on (d,e,f) drives then it shows me following error.

    Backup failed for server ‘****’. (Microsoft.SqlServer.SmoExtended)

    System.Data.Sql.Client.SqlError: Cannot opem backup device ‘E:\foldername\filename.bak’ Operating system error 5(Access is denied.). (Microsoft.SqlServer.Smo)

    But on c drive it successfully worked.

  44. hi pinal..

    i want to send data from one table to another in a asp.net project what will be the query.?

    kindly reply

  45. hello Admin

    I have littel problem in creating trigger
    i create table Employees which include four filed

    EmpName Sex Age Remark
    Amylee Female 45 Over Age Emp
    Jack Male 19 Young Age Emp

    i create trigger which automaticlly give Remark

    create trigger emp on employees
    for insert
    as
    if (select age from inserted)>20
    begin
    update employees
    set status=Over Age Emp’
    where age >=50
    end
    Else
    if (select age from inserted)<20
    begin
    update employees
    set status='Young Age Emp'
    where age <=50
    end
    This trigger not Work it update all data filed it give Remark
    "Over age Emp" for all emp which have under age 20 and over 20 it give same remark

    • You dont need a trigger
      You can use it in a select statement

      select columns,case when age>50 then ‘Over Age Emp’ else
      ‘Young Age emp’ end as status from your_table

  46. For my ASP.net Application on a hosted MSSQL server, i have had to re-ceate using the scripts generated from my local machine using sql tools with SSMSE. This worked beautifully and all appears to work. My ASP.net application users can create their accounts and login normally. Unfortunately, my create user wizard does not automatically generate roles for the clients. So, i am using a routine that includes the following

    Protected Sub CreateWizard1_OnUserCrated(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser

    Dim userInfo As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)

    userInfo.IsApproved = False

    Membership.UpdateUser(userInfo)

    Roles.AddUserToRole(CreateUserWizard1.UserName, “Friends”)

    End Sub

    which does not work because it is looking for a AddUserToRole stored procedure. This is confusing because it this SP IS on my local version and works fine. So i attemtped to create a new SP called AddUserToRole on the server but get an error message

    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 49
    Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation. (actually it occurs 5 times in the routine).

    Thanking u in anticipation for reply

  47. Hi in my case the exact error is

    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 45
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 52
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 76
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 83
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.
    Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 90
    Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

  48. I have littel problem in creating trigger
    i create table Employees which include four filed

    EmpName Sex Age Remark
    Amylee Female 45 Over Age Emp
    Jack Male 19 Young Age Emp

    i create trigger which automaticlly give Remark

    create trigger emp on employees
    for insert
    as
    if (select age from inserted)>20
    begin
    update employees
    set status=Over Age Emp’
    where age >=20
    end
    Else
    if (select age from inserted)<20
    begin
    update employees
    set status='Young Age Emp'
    where age <=20
    end
    This trigger not Work it update all data filed it give Remark
    "Over age Emp" for all emp which have under age 20 and over 20 it give same remark

  49. hi i want to convert table data in to XML format after that i want to insert that xml format data into specific table column in sql server 2000
    so it is possible i want to make it for dynamic query ——————sharad

  50. hi pinal i want to write a script to import tables in one database into another database, can you please me in this task…………….

      • hi thanks fo your help but iam unable to see the import/export wizard in my sql server 2005 can u suggest me in this n i need to write a script for:
        iam having a db ERPDB datbase, now i need to write a script for the tables in the databse. so that if i run that script in another databse i should get all the tables that are in ERPDB databse.

  51. Hi Pinal,

    I am extracting data from DB2 into SQL Server 2005 using IBM OLEDB provider for DB2. Source table have only 2 columns both are decimal in type. It has only one row for testing. While extracting data using SSIS I am getting following message

    SSIS package “Package1.dtsx” starting.
    Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
    Information: 0×40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
    Information: 0×40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
    SSIS package “Package1.dtsx” finished: Canceled.
    The program ‘[3796] Package1.dtsx: DTS’ has exited with code 0 (0×0).

  52. hi

    I have this concern. The mdf of my database grows fast. From 500 mb to 18 gb in short period of time. Hope you can remedy my concern.

    Thank you

  53. Hi Pinal,

    Few days back i was doing some crazy stuff in my SQL server Management studio, I wanted to create a SP which will delete itselft.. I did something like,

    CREATE Procedure usp_self_delete
    AS
    BEGIN
    DELETE usp_self_delete
    END

    I thought SQL server will throw error upon executing the above created SP. But to my surprise.. it didnt.. It actually deleted itself. So i was wondering how it can happen?

    Can you please give some light on it.

    Regards
    Taher

    • hi taher i have gone through the code u have written, i have executed it but it havnt deleted.

      regards,
      sridevi

      • I have successfully executed the code… in SQL server 2008.. and it is actually deleting itselft.. I am able to run the SP only for the first time, next time when i run it.. it shows object does not exits error.

  54. Hi Pinal ,

    I have to call a UDF from serv1 (SQL Server) to Serv2 (SQL Server). I set up a linked server on Serv1 and trying the following command on serv2. The link server is properly tested and the user is sysadmin, so no permission issues.

    UPDATE TABLE1
    SET COL1 = EXEC SERV1.DBNAME.DBO.UDF(@TEST)

    Please advise. thanks in advance.

  55. Hi Pinal,,

    I have the following Question:

    If you were integrating a feed of end of day stock price information (open, high, low, and closing price) for 5,000 companies, how would you do it? You are responsible for the development, rollout and ongoing monitoring and maintenance of the feed. Describe the different methods you considered and why you would recommend your approach. The feed would be delivered once per trading day in a comma-separated format via an FTP site. The feed will be used by 1000 daily users in a web application.

  56. Hi Pinal:

    Need small help of yours

    I need to duplicate a row having [name] column. Duplicate row should have like [existingname]Copy1

    I need to increment variable value in procedure, i know that can be done by using SET @i = @i+1

    But i need to keep checking continously if this name already exists in that table. Below example wil make things bit clear

    declare @i int
    set @i=1
    if EXISTS(select [name]+’Copy’+cast(@i AS varchar(50) from tablename where userid=@UserID)
    begin
    set @i=@i+1
    end

    this will yield in ‘nameCopy2′ as result but how i can continously check in loop if next incremented value already exist in table.

    any help will be greatly appreciated

    Please advice, Thanks

    • You dont need to use a loop

      Try this code

      select name, 'copy'+cast(sno as varchar(10)) from
      (
      select row_number() over (order by name) as sno,name from table_name
      where userid=@userid
      ) as t

      • Thanks Madhivanan,

        Sorry i saw your post little later as got my solution earlier.

        But It is good way of catching the row_number, i’ll keep note of this as well..

        Thanks

  57. Dear sir,

    I am using Sql Server 2005.

    I have 2 databases

    one is “olbDatabase” which contains live data and this database has not any relation ship.

    The second database is “newDatabase” which has relation ship

    I want to import data from oldDatabase to newDatabase without dropping constraints but, I get readonly column error.

    I can not import live data in to newDatabase…

    Please help me its urgent!!!

    Thanks…
    Nitesh Parmar from Gandhinagar (Gujarat)

  58. Hi

    sir is there any way to get specific row from a result set.My result set have 20 row then can i get 2 nd row only as result from that query

    thanks in advance
    Anoop

    • There is no concept of first row, second row, etc in relational database. You need to order it by a column and get it

      select * from
      (
      select row_number() over (order by col ) as sno, * from table
      ) as t
      where sno=2

  59. Hi pinal,

    I have gone through some of your articles which u hav published and am very much impresse with ironical work n here i hav a problem,can u help out

    How to run an SCRIPT IN SQL SERVER 2005? Please can you explain step wise. After executing the script, in next step what i should do. This script we are going to execute in prod environment.
    please help me out ASAP.

  60. Hello mr. Pinal Dave
    I have this Script:
    CREATE TABLE tblTempl
    (
    name CHAR(12)
    )
    GO
    INSERT tblTempl VALUES
    (‘abc’),
    (‘asc’),
    (‘dhs’),
    (‘wegweg’)
    GO
    //First Query
    SELECT *
    FROM tblTempl
    WHERE (name like ‘%’ + ” + ‘%’) (1)
    GO
    //Second Query
    DECLARE @e CHAR(12) = NULL;
    SELECT *
    FROM tblTempl
    WHERE (name like ‘%’ + ISNULL(@e,”) + ‘%’) (2)
    GO
    DROP TABLE tblTempl

    I don’t understand why (1) and (2) not match!, Please help me.

  61. Dear Pinal

    Amazed at your work.

    Niraj Bhatt was my trainer at Ardent collaborations in Bangalore. I am a kenyan and I am sure he remembers me.

    I do some projects in. NET Framework 2.0. However there are areas I would like to make my skillset very strong such as
    XML, Java scripting and AJAX. Programming platform is VB.NET and NOT C#. Coming from VB programming.

    A trip to your organization is what I am looking forward to..

    Kindly advice.

    Regards

  62. which is the best option while bulk inserting large amount of data(more than 10 million records)? Disable and enable the Indexes (or) Drop and recreate the Indexes

    • Srivivel,

      The best option would be to Drop and recreate the indexes. I think you cannot enable the cluster index after disabling it, infact you will have to recreate a cluster index.

      Thanks
      Vishal

  63. Hello mr. Pinal Dave,

    I’m migrating an Access database to SQL Server.
    I need to migrate some columns (but not all off them) from the Access file. I connected the Access file as a linked server. When I start querying it gives me the result that I expect. No problems so far but …Why is following query not working:

    SELECT * — need some columns here only
    — so I have to replace the *
    INTO #MyTempTable
    FROM EXEC(sp_columns_ex,
    @table_server = N’ACCESS_MIGRATION’,
    @table_name = N’ElsAangifte’)

    I used the stored procedure “sp_columns_ex” because this one gives me the results I need.

    Thanks for your reply in advance and I hope you give a solution.

    Kind regards

    Edward

  64. I made a Stored Procedure as Process with Table index when i execute it on my local PC it takes 50 seconds for 27000 records but while execute on Database Server it takes 18 minute . Why ?

    Note: same Structure is working fine another PC except Database Server.

    Can anyone help me what shoud i do ?

    Thanks

  65. Hi Pinal Dave,

    Firstly thank you very much for sharing your knowledge on SQL on your site/blog

    My question is: i have to compare a datetime and a date field, for this i have 2 options, i would like to know which one is lest expensive. the table has millions of rows and unfortunately the datetime field is not indexed

    for example:

    declare @date date
    set @date = getdate()

    – Option 1 (using cast or convert)
    select * from sysobjects where cast(crdate as date) = @date

    – Option 2 (using datediff)
    select * from sysobjects where datediff(dy,crdate, @date) = 0

    i would assume datediff will use more resources, please advise (PS: i’ts a sybase db)

    • This may be more effecient
      Compare the performance with

      select * from sysobjects
      where
      crdate>=dateadd(day,datediff(day,0,@date),0) and
      crdate<dateadd(day,datediff(day,0,@date)+1,0)

      • is’nt running functions for every tuple, more expensive?
        if i evaluate the cost, it goes as follows:

        crdate>=dateadd(day,datediff(day,0,@date),0) and
        crdate<dateadd(day,datediff(day,0,@date)+1,0)
        – 4 function executions & 3 comparisions per row

        datediff(dy,crdate, @date) = 0
        – 1 function execution & 1 comparision per row

        cast(crdate as date) = @date
        – 1 typecasting & 1 comparision per row

        i have millions of rows to query on, and currently do not have any performance meassurement tools, so i would like to make some logical decisions

        • You wont see performance difference if there is no index on crdate

          If there is index on crdate, your methods use function over it thus causing index scan than seek

          My method applies function over the date variable thus result to index seek

          • unfortunately the field is not indexed so it will have to scan,
            but i will keep in mind if i have to query an indexed field in the future

            wanted to know if any optimization could be done, as its a very heavy overnight batch

            thanks Madhivanan

  66. Hi Pinal,

    I am planning to create a view and retrieve data from the view instead of a table. My main goal is to eliminate locking onthe table. It is a simple SELECT 4 columns with (NOLOCK) query that issues Sch-S (schema stability) lock on the table. My goal is to eliminate Sch-S lock on the table. If I create a view (select 4 columns)on the table and retrieve data from the view, will it eliminate the lock from the table. I read that a view only adds performance hit since every time I run a query that references a view, db engine will have to cosntruct a virtual table (view) from the base table, meaning query the base table anyway.
    Can you please confirm if this is true?

    Thank you very much in advance,
    NS

  67. Hi Pinal

    I got and query. I believe you got the solution :)

    Here is the problem
    ————————–

    I have a database in sql 2005 with 10 Lac tuples

    My front-end application is designed in .net c# (aspx)

    when no application is running the sqlservr.exe show 20MB of memory usage in task manager.. but eventually when we start using application the memory usage of sqlservr.exe goes upto 1.8 GBBB .. what’s the issue here.
    after this Con.open() command fails to connect the application to sqlserver 2005

    Pls send me a solution.

    Regards
    Gurpreet Singh

  68. Hi Pinal,

    First of all Congratulations on your excellent knowledge in SQL Server.

    I have a question after reading some of the posts on indexes (Covering Indexes). All the posts say that if a covering index does not exist on the Select Columns a look up is done. So now my questions is if I do a select * then will a bookmark look up definitely appear in the query execution plan?

    Please comment

    Thanks,
    Vishal

  69. Hello Sir

    I am a student of MCA, read notes from your website. thank you for the notes these are usefull to me. But i have a confution i. e. I tried to execute the query “Select rows from sysindexes where id=OBJECT_ID(table1) and indid<2" I use table name "emp" at the place of "table1" but when I am execution this query its showing error "invalide column name emp" so can you tell me where is the problem and how can i use this query? Please guid me??

    And can you tell me how can i get Rowid from a table as we use Mysql and Oracle.

    Thank you Sir

  70. Hi pinal

    Please help me with the following issue I am getting:

    The server principal “content_ingestion” is not able to access the database “CMSUSERLOGS” under the current security context.

    waiting for your reply urgent
    rajeev

  71. Dear Pinal,
    my name is Ramakrishna and I am new to MS SQL technology. my aim is to do certifications for MS SQL technology(ADMIN, development after that BI). I am looking for some resources to to learn and I found CBT Nuggests videos. they are quite good as a stating point but I am also looking for some resource for practicing the exams questions before I take final exam.

    can you please provide me some resources, preferebly opensource resources that willl help me to do MS SQL 2008 certification exams. I would be very happy if you can suggest me that.

    with regards,
    Ramakrishna Paruchuri
    From Germany

  72. hi pinal sir,

    i am working as a sql dba. so i need ur guidence which book i should prefer and how i can increase my knowlede.
    beacause i never get seniors to learn from them.

    thanks & regards

  73. Hi Pinal,How tempdb gets its intial size .I am sure that it wont get it from model database.But where this intial size will be stored when server restrats.
    How can we find intial size using the T-SQL.

    Thanks,
    DK

  74. Hi Pinal
    I need to send an email from a stored procedure in sql server 2008 . I came across the option of database mail in one of your articles(http://www.codeproject.com/KB/database/SQLServer2008DatabaseMail.aspx)

    Are there any disadvantages for this method?.From what I have read and undestood its better compared to sqlmail.Kindly suggest whether database mail is a good option.

    One of my friends suggested to call an SSIS package from the stored procedure. This SSIS package will send the mail…But this idea seems a bit lengthy..i would like to know ur views as to which option is good…and in particular whether database mail has any disadvantages?

  75. how to add values from textbox in database table in sql server 2005 at run time
    i have written foll code for the same bt its not working

    if (con.State == ConnectionState.Closed)
    con.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = “insert into table1(personnel_no,password) values(@1,@2)”;
    cmd.Parameters.AddWithValue(“@1″, int.Parse(TextBox1.Text));
    cmd.Parameters.AddWithValue(“@2″, TextBox2.Text);
    cmd.ExecuteNonQuery();
    bindgrid();
    con.Close();

  76. how to add values from textbox in database table in sql server 2005 at run time
    i have written foll code for the same bt its not working

    if (con.State == ConnectionState.Closed)
    con.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = “insert into table1(personnel_no,password) values(@1,@2)”;
    cmd.Parameters.AddWithValue(“@1″, int.Parse(TextBox1.Text));
    cmd.Parameters.AddWithValue(“@2″, TextBox2.Text);
    cmd.ExecuteNonQuery();
    bindgrid();
    con.Close();

  77. Hello,

    adding a target server into a master I do the following steps:
    1) On Target server:
    Right click on agent -> Multi Server Administration -> Make this a Target…
    2) Next
    3) Pick Server
    4) Type in the master server and put in [B]SQL Server Authentication[/B] (sa, which even has same password on both servers)
    5) Next
    6) And here I get the error:
    [B][I]Login failed for user “DOMAIN\MyDomainAccount”[/I]![/B]

    The login from the remote (Target) server to the master server and with sa works through sqlcmd or even on Mgmnt Studio by e.g. registering the master server.
    And vis-versa it works to login with “sa” and SQL Server Authentication through the master into the target for enlisting the target then.

    The error seems to lye really in the Studio Management application and definitely this wizard steps.

    Can you try it out and give feedback please if same error or just working.

    Thx!

  78. Dear Sir:

    I would really appreciate for you help by provide me any script that generate an automate report via store procedure in SQL 2005 and email an attachment of the report to many users via email address listed in data table for specific of time.

    Thank you very much for your help.

    Best regards,

  79. hello

    i got an error while i as trying to make snapshot, dnt know why?

    my query was this

    CREATE DATABASE SnapshotDB ON
    (Name =’RegularDB’,
    FileName=’C:\SSDB.ssl’)
    AS SNAPSHOT OF RegularDB;

    i got error like below

    Msg 1823, Level 16, State 2, Line 1
    A database snapshot cannot be created because it failed to start.
    Msg 5123, Level 16, State 1, Line 1
    CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\SSDB.ssl’.

    thanks in advance

  80. Hi Pinal,
    I have a serious problem here.In my project we are developingInterface between our dataware house and Duck Creek(Third party tool which provides services to insurance domain clients),we are decided to use ssis for this.i would appriciate suggetions.

    Thanks
    Suni

  81. Hey,
    Can you please share the information about the tool which is used to Covert the Query from T-SQL(SqlServer) to Pl/Sql(Oracle) and Vise versa.
    Thanks

  82. Dear Mr. Pinalkumar Dave

    how to store the arbaic character in sql server 2005 with using power builder 10 and reteriving the data also.

    Kindly mail to us

    From

    Mohamed Bijili

  83. Hello Pinal,

    I have been looking all around the web for an answer to this question:

    I have an application using SQL Server 2005. and i need to intrecept the queries before they are sent to SQL Server to do some changes.

    triggers and view are not applicable in this case. I was wondering if i can intercept the communication over port 1433 and monitor/change the information needed.

    i was looking for a proxy tool that sets between the application and the SQL Server. and i can write the required listener to intercept the queries and do the required modifications

    thanks alot,
    ramadan

  84. Hello Sir.

    I want to call a Web-Service function throught SQL Service Broker feature. Is that possible ?

  85. Hello,

    I have question regarding adding an instance of sql server.

    I have two machines on the same network.

    One of them has a SQL Server 2005 version running on it with a default instance and a named instance.

    The second machine has a SQL Server 2008 version running on it with a default instance only.

    All the SQL Server Instances are using local windows authentification for login credential.

    On machine one,, i added a new port 1434 for the named instance and i can used both database engines successfully.

    On the second machine, i just maintain one default instance of sql server 2008 database engine.

    I am trying to connect to the sql server 2008 default instance on machine 2 from the sql server 2005 default instance on machine 1.

    Actually, i am trying to be able to use the sql server 2008 default instance from both of my sql server 2005 instances and also be able to use both of sql server 2005 instance in the sql server 2008 default instance.

    I tried the add linked server technique and also adding port techniques but there is something for sure that i am missing here.

    I would really appreciate if someone could tip me.

    Thanks
    John

  86. Hi ,
    I am currently working on Ticket Management Application in Asp.net. I want use Email triggering From SQL server in that.
    Scenario is :
    To trigger an E-mail to Admin if the TAT[ turn around time] is exceeded for a ticket raised. The TAT is calculated in such a way that, only working hours are included and Holidays are omitted.
    Eg. Working Hr is 9Am to 6PM,
    Saturady & sunday Holiday.
    If TAT of a ticket is 5hrs and ticket is raised at 5PM on Friday.Then an email has to be triggered on Monday 1PM (i.e after 5 working hours: [Friday 5pm to 6 PM] 1Hr+[Monday 9AM to1 PM] 4Hr =5 Hr ) if status of that ticket is not closed. Please suggest a method to implement this. Thank You.

  87. Hi Penal,
    I am new in SSRS. i need to design a report with drill down for a column. in a matrix like view. could you please help me for the same. I would also know the different report patterns can be made by using SSRS. Need help. thanks in advance.

  88. Hello Sir

    Thank you for replying its worked.

    Sir i want to ask two more questions

    1. how to retrieve ROW Id in sql server of records?
    2. what is difference between insert and insert into

  89. Hello sir

    Myself Nilam Shinde. Sir one query runs very fine in database but while fetching data from query crystal report slows down.Would you like to give me solution?

      • Approximately 500 to 600 rows are transfered for the month APR-2009.The view runs fine in database and takes only 10 sec to run but while fetching records through crystal report 8.5 it takes 4 to 5 minutes. So Pls give solution.

  90. dear sir

    i have two table as shown below

    fieldmaster
    —————————
    fieldid—– field_name
    1———– Sector Number
    2———– Plot no

    datavalue
    —————————
    Id—-datavalue—— fieldid
    1—–28——————1
    2—–30p—————–2
    3—–28p—————–2
    4—–52——————-1
    5—–12p—————–2
    6—–11p—————–2
    7—–7p——————-2

    and i want the result as:

    Sector Number——-Plot no
    28———————-30p
    28———————-28p
    52———————-12p
    52———————-11p
    52———————-7p

    Plz help to resolve it
    and also check all special character and ‘NA’

    • As far I see there is no relation ship between sectornumber and plot.

      Is it always true that after every sector the next row data would be plot till it hit another sector?

      If the answer is yes then you have do using a while loop or a crusor based on your requirement.

  91. Hello Pinal.

    I’ve a doubt about size and datafiles…

    Yesterday we had an space issue problem for one of our servers. I release space from the current databases executing shrink commands over them.

    But, there are a database that it has 31 GB for allocated space, but only 19 GB are currently allocated with data, the rest are free space. My problem was (as far as I know), there is not posible to reduce the datafile due the Error 21335: [SQL-DMO]The new DBFile size must be large than the current size.

    Now, I don’t know what the vendor did, but he did it, he reduce the primary datafile to 19 GB.

    How can be done?

    Many thanks for your time.

    Regards

  92. Hi,
    Sir

    i am not graduate but i have experiance in hardaware wng. 6yrs and i have certificate DHE & DCA Private Firm.
    and i want Teach Sql Server
    so pls Best Line Give me
    DBA Or MCDBA, MCITP, MCSE,

  93. I’ve come across a weird problem in Sharepint 2007 Database. I’m also checking with support at MSFT but it is new to them.

    I’ve applied the WSS 3.0 SP2 on Sharepoint and after rebooting , the sharepoint site was inaccessible to all the users. After doing deep analysis we have noticesd that in the dbo.UserInfo there are three entries for the same user and that is the user who had setup the sharepoint which is not anymore. His AD account was deelte almost a year back.

    And one more interesting thing is that in the dbo.UserInfo under Index there are only two entries while when we setup a new database there is seven entries. so no idea what went wrong.

    we have tried settingup the proper permission to Admistrator account, reset and re-applied the permission but it is same.

    any clue?

  94. 1. There is table name “MUSTER” with 2 columns ename and attendence

    ename – attendence
    a ———– y
    a ———– y
    a ———– n
    a ———– n

    update the table such that all y becomes n and n becomes y
    but condition is that u have to write single update statement only
    ????????????????????????

    reference
    ——————————————————————————-
    create table newtest
    (ename varchar(500),
    attendence varchar(500))

    insert into newtest values (‘a’,’y’)
    insert into newtest values (‘a’,’y’)
    insert into newtest values (‘a’,’n’)
    insert into newtest values (‘a’,’n’)

    select * from newtest

    ***********************************************
    2.create table candidate
    (cid int identity(1,1) primary key,
    cname varchar(50) ,
    )

    insert into candidate values(‘hasmukh’)
    insert into candidate values(‘potu’)
    insert into candidate values(‘nizam’)
    insert into candidate values(‘sandeep’)

    create table skill
    (sid int identity(1,1) primary key,
    skillname varchar(50) ,
    )

    insert into skill values(‘testing’)
    insert into skill values(‘asp.net’)
    insert into skill values(‘c#’)
    insert into skill values(‘c++’)
    insert into skill values(‘vb’)

    create table cand_skill
    (id int identity(1,1) primary key,
    cid int foreign key references candidate(cid),
    sid int foreign key references skill(sid)
    )

    insert into cand_skill values(4,5)
    insert into cand_skill values(4,3)
    insert into cand_skill values(1,2)
    insert into cand_skill values(1,1)
    insert into cand_skill values(1,3)
    insert into cand_skill values(3,2)
    insert into cand_skill values(2,5)

    select * from skill
    select * from candidate
    select * from cand_skill

    –QUESTION 1: RETREIVE ALL THE EMPLOYEES WITH THERE SKILLNAME
    –Note: candidate can have multiple skills

    Sir please can u please answer me this question..
    Please help me to solve this questions..

  95. I have a variable this varible i want insert in database with
    same cloumn
    string =””62308718,07678892,53963600,82535854″”

  96. i have one doubt

    what is the use ‘national’ keyword in sqlserver

    pls explain with example

    thank u

  97. In need a good and fast way to copy all records from sql table to access table via vb6 classic.
    Note:
    sql table have approx 4.500.000 records and on this sql table have only a reading permission.
    Tks to the all.

  98. hi,

    i need insert more than 1000 records from one table to another ,

    if only 500 records are inserted then it sould get rollback

    anyone can help

    Thanks

  99. Hello Pinal,

    Please let me know how to solve this issue.

    After running Micrsoft Upgrade Advisor, I have got this issue

    When to Fix : Anytime

    Description

    Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default

    ERRORS

    SQL Server provides a way to allow instance-level registration of new word breakers and filters.

    Affected Objects

    Component: .asx Component: .css Component: .hta Component: .htt Component: .idq Component: .pps Component: .xps Component: .xsl

    Kinldy let me know how to fix this and is there any impact of this on production if not fixed.

    Thanks a lot.
    Naina

  100. Hello Pinal,

    Im getting this issue, while i run the upgrade advisor

    When to Fix : before Issue : full text search has changed in sql server 2008

    please let me knw how to resolve this.

    Thanks
    Naina

  101. Hi!

    I have read your posts about enabling/disabling indexes and dropping primary keys but I have a situation where I want to disable the clustered index or primary key ready for a bulk data import.

    I am happy with disabling/rebuilding the non primary key indexes but was wondering how you would get around this issue?

    Thanks

    Jon

  102. How to Generate Recurring Events in the Database.

    I would like to create a .net application that allows us to add events to a overall application, anyone then can go search that event by a specific date and if that event falls under that recurrence day you will see it. This would be similar to Outlook where you can say it recurs every Monday, Tuesday, Wednesday,… or every third Monday, …Specifc day of the month, etc.

    Can you help me.

  103. I have a situation where an application running on sql2000 doesn’t release any space in transaction log, with auto-grow on it just grows and grows. with auto-growth turned off we get errors after a week or two because it runs out of space. it can be using only 100kb, yet grow to 60gb. Is this a sql2000 problem or badly written code?

    The log file for database ‘hsmprod’ is full. Back up the transaction log for the database to free up some log space

    Thank you. Your articles are always very helpful

  104. Hello Pinal,

    I am Madhab, a reader of your Blog. I have an issue with MS SQL Server, hope you could solve that.

    Description:

    Let consider I have a Table Student with 4 columns. Those are: Roll Primary_key, s_name Not null, Phone, city.
    I would like to write an insert statement that will insert values to the table, but specific columns. As Roll, s_name are notnull, I need to enter them mandatorily.
    But I want the the other two field should enter on users choice.

    EX: Insert into mydb.dbo.Student (Roll, s-name,,) values (100, ‘Madhab’)

    or something like that…

    Can you please help me on that…

    Thank you

    Maddy

  105. Hi

    I am facing problem in SQL server 2005 taking long time to insertion, while same application is running on another server work just perfect;

    Server A:
    Edition Enterprise Edition (64-bit):
    Product Level SP2
    Version 9.00.3042.00
    @@Version Microsoft SQL Server 2005 – 9.00.3042.00
    (X64) Feb 10 2007 00:59:02 Copyright
    (c) 1988-2005 Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT
    6.0 (Build 6001: Service Pack 1)

    Server B:
    Edition Enterprise Edition (64-bit)
    Product Level SP3
    Version 9.00.4035.00
    @@Version Microsoft SQL Server 2005 – 9.00.4035.00
    (X64) Nov 24 2008 16:17:31 Copyright
    (c) 1988-2005 Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT
    5.2 (Build 3790: Service Pack 2)

    Server A is working fine but Server B is in trouble,

    Your advice to fix the problem will be highly appreciated.

    Thanks and Regards

    sani

  106. Hi

    I am not able to understand flow of your website, like for new post, did not find and link, where I can go for new post with new subject; which is not already posted

    Thanks and Regards

    sani

  107. Hi all,

    How to get start date and end date from the following duration as ‘Jan 10 – Jun 10′?

    can you suggestion me as soon as,

    Thanks

    • Hi all,

      i have find this way to get start and end date….

      Declare @m INT, @y INT, @dt SMALLDATETIME,
      @Stdate DATETIME, @EndDate DATETIME,

      SELECT @m = 01, @y = 10, @dt = RTRIM(@y) + ’0101′;
      SET @Stdate=DATEADD(DAY, 0, DATEADD(MONTH, @m-1, @dt))
      SET @Enddate=DATEADD(DAY, -1, DATEADD(MONTH, @m+0, @dt))
      SELECT @stdate AS StartDate, @EndDate AS EndDate

      If any way to get start and end date the following period as ‘jan 10 – jun 10′, tell ur suggestions as soon as….

      Thanks to all

      • Refer this code

        declare @from varchar(10), @to varchar(10)

        select @from=’jan 10′, @to=’Jun 10′

        select cast(’01 ‘+@from as datetime), dateadd(month,1,cast(’01 ‘+@to as datetime))

        select columns from table
        where
        date_col>=cast(’01 ‘+@from as datetime) and
        date_col<dateadd(month,1,cast('01 '+@to as datetime))

  108. Sir,i had a problem when i m inserting data into my database named AddressBook from another database PAFDataAtoL,when i run the below query
    USE AddressBook
    INSERT INTO Addr_BK_Address(PZC, Town_ID,ThoroughfareKey,ThoroughfareDescriptorKey,DependentThoroughfareKey,DependentThoroughfareDescriptorKey,BuildingNumber,BuildingNameKey,SubBuildingNameKey,CMP_ID)
    SELECT Postcode,LocalityKey,ThoroughfareKey,ThoroughfareDescriptorKey,DependentThoroughfareKey,DependentThoroughfareDescriptorKey,BuildingNumber,BuildingNameKey,SubBuildingNameKey,OrganisationKey
    FROM PAFDataMtoZ.dbo.Address

    i get the following error
    Msg 9002, Level 17, State 4, Line 1
    The transaction log for database ‘AddressBook’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

    Plz sir what can i do now?

    Lailla Khan

  109. Hello,

    If you are interested in SQL Architect/Developer positions in Monrovia or Marina del Rey, please let me know. Thank you for your time!

  110. Hi,

    I have a problem, that cannot resolve.
    How can create one table of two tables that contains different registers.

    for example:
    The first table contain the fields next
    DATE_END
    PERCENT

    the second table contain
    ID_T
    DATE
    PERCENT

    So the rows first table are less that second table, I want one table that contain the rows the second table always that this rows are lower

    for example first table contain
    FFIN DATE
    ————- —————————————
    2010-06-07 25.00
    2010-06-15 50.00
    2010-06-19 60.00
    2010-06-22 70.00
    2010-06-26 85.00
    2010-06-27 100.00

    Second Table
    ID_T DATE PERCENT
    ————— ———————- ————–
    1 2010-06-01 5.00
    2 2010-06-02 11.60
    3 2010-06-03 25.00
    4 2010-06-04 32.90

    The result that I need is
    NEWDATE PERCENT
    ———————- ————–
    2010-06-01 5.00
    2010-06-02 11.60
    2010-06-03 25.00
    2010-06-04 32.90

    2010-06-07 25.00
    2010-06-15 50.00
    2010-06-19 60.00
    2010-06-22 70.00
    2010-06-26 85.00
    2010-06-27 100.00

    As I can obtain this result?

    thanks for your help.

    • @Adolfo

      I am sorry, I am having trouble understanding your question.

      I am not sure if I understood your question correctly. You want to display your data in Ascending order of date.

      Select * from (
      Select datecolumn, date percent
      From Table1
      UNION ALL
      Select datecolumn, date percent
      From Table2) A
      Order by datecolumn ASC

      Note: Use UNION, to exclude duplicates. When using UNION ALL or UNION, make sure the data types for columns in table1 and table2 are same, otherwise you cannot use UNION or UNION ALL.

      If you always want to display rows of first table below rows of second table. Then add an alias column in select statement and order by that column.

      Select * from (
      Select datecolumn, date percent, 2 AS Orderby_ID
      From Table1
      UNION ALL
      Select datecolumn, date percent, 1 AS Orderby_ID
      From Table2) A
      Order by Orderby_ID ASC

      Does that helps ?

      ~ IM.

  111. Hi Madhivanan,

    Table A:(GroupID, GroupName)
    1 Family
    2 Friends
    3 Business
    4 Clients
    5 Others

    Table B: (ConID,ConName,City,GroupID)
    1 Ramesh NULL 3
    2 Jegan NULL 1
    3 Arun NULL 2
    4 Mathan NULL 1

    I want result as

    GroupName NoofContacts
    Family 2
    Friends 1
    Business 1
    Clients 0
    Others 0

    can you suggestion me about this as soon as

    Thanks

    • Hi

      i have use this query,,

      SELECT A.GroupName,Count(B.ContactID)AS NoofContacts
      FROM CON_TblGroup AS A
      LEFT JOIN CON_TblContactMaster AS B
      ON A.GroupID = B.GroupID
      GROUP BY GroupName
      ORDER BY GroupName

      but suggestion me any other way to get result..

      Thanks

  112. Hi.
    I have a query result
    which i will have to put it in a excel sheet which is in a predefined format..
    The first 10 or 12 rows contains headings with some other information…
    Now i have a query which i can run using Oledb source and the result of this query must sit from 13th cell onwards..
    I tried mentioning Sheet$1A15:D250 this in openrowset option..but it is not working..
    i can easily fetch the range i want and put it a destination table
    but not viceversa..
    Can anybody please help me out on this problem..
    That would be of a great help..
    Thanks in advance…
    Bruno..

    • @Bruno,

      I am not sure if I understood your question correctly, You have an excel sheet in which top 15 rows you want to reserve for header information, you want to export data out of table to this excel and you wantSQL Server to write from 15th row and not from the first row.

      I had the similar requirement, this is how I did it.

      Method 1: Either You prepopulate those 14 rows in your excel template, so that when you start writing to excel, SQL Server will start writing from 15th row. You dont have to mention row numbers in openquery statement, it will start from 15th Row.

      Method2: If you are not aware of what those 14 rows will be in your header, you can do like this. Go to your excel, in top 14 rows type something in every cell and press delete button, do this one cell at a time. By doing this you are reserving space for that cell, When SQL Server will try to write to this excel, SQL Server will see that there is some data already in this cell even though the cell is empty it has already been used, So SQL Server will start from 15th Row. When I say delete, dont delete by doing Right Click, Delete entire row. I meant, type something in the cell and then press delete button from your key board so that the value you typed in the cell disappears.

      This is the way I have done at my work and it works with no issues.

      ~ IM.

  113. sir,
    i am already sql 2005 familiar

    i want to doing sql dba course so that i get degination SQl dba/ developer .

    pl give me right direction

    rg
    amit

  114. hi there,
    how can i insert values from my vb6 textboxes(6 of them,like text1,text2….text6) on a form into sql7 which is my back end
    thanks

  115. Hi,

    I now get a blank warning modal message box, warning icon, with no text whatsoever and an “OK” button every time I start SQL Server Management Studio.

    can you tell the suggestion as soon as, whats that problem, and how to fix that problem….

    Thanks to all

  116. Hi,

    I now get a blank warning modal message box, warning icon, with no text whatsoever and an “OK” button every time I start SQL Server Management Studio.

    can you tell ur suggestion about that issue, and how to fix that issue….

    Thanks,

    • Hi

      I have fix that issues in the following way…

      1. In Control Panel, open Add or Remove Programs.
      2. Click Microsoft .NET Framework 2.0.
      3. Click Change/Remove.
      4. Click Repair, and then click Next.
      5. When the repair is completed, restart the computer

      Thanks

  117. Sql server 2008
    We have option to edit top 200 rows
    but if i want to edit rows from 300 to 500 how i can do that

    • You need a pagination. Refer this post to know how to do it using row_number() function. Refer point 4http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

  118. Hi Pinal ,Imran and All,

    I need a help from you regarding Replication. I have two Sql Server 2008 A And B on separate Machine.

    “A” is a Live Server on which i created Trans Log Publisher and Subscriber is “B” Server.
    I have created setup successfully .But It is not updating data on B server.When i checked in Replication Monitor i got the error “The process could not execute ‘sp_replcmds’ on ‘ARUN\SQL2008′ ” The log reader agent is failed.

    Can u pls help me what could be the issue.I tried hard on google and other forum sites but unable to get the correct reason.

    Thnx,
    Arun

  119. Dear Pinal,
    I have one doubt regarding with one query.
    I have two table and need to write select query by putting table value as column name {value(table_name.coloumn) as coloumn name}

    employee schema table with data as below

    Pid Coloumn Display Name
    1 Name
    2 Age
    3 Sex

    Item Table data as below

    Pid Schema table Xid Item
    1 1 Pinal
    2 2 27
    3 3 Male
    4 1 Abdul Manzoor
    5 2 27
    6 3 Male

    Result should be like this
    Name Age Sex
    Pinal 27 Male
    Abdul manzoor 27 Male

    Is it possible…..

    Thanks
    Abdul

  120. Hi Pinal,

    I need a help from you regarding :

    How to create a database in Sql server authentication mode so that i can connect it with my web application?

    Thanks
    Apurva

  121. URGENT PLZZZZ

    I have a big code (inside after insert trigger) which causes problem (timeout) on the next insert from another user, i think thats because of locking the table.

    Is there a way to commit the insert then continue running the sql code after that (so the table will be unlocked). If not what do you recommend me to play around this issue !!

    Thank you,

  122. I have followed your suggestion to identify the heaviest-used stored procedure in my sql server 2005 database and collected these stats over time. I restarted sql server each night to get a fresh set. Can you tell me why the physical reads would vary so widely with the same approximate number of calls to the procedure?
    StoredProcedure Date execution_count total_IO total_physical_reads
    USP_GET_AUTHORIZATION 4/26/2010 172,221 29,357,867,028 7,409,485
    USP_GET_AUTHORIZATION 4/28/2010 209,830 3,654,080,547 1,846
    USP_GET_AUTHORIZATION 4/30/2010 59,614 14,468,926,585 3,597,000
    USP_GET_AUTHORIZATION 5/4/2010 227,852 3,999,076,244 951
    USP_GET_AUTHORIZATION 5/5/2010 191,958 42,126,062,731 10,496,595
    USP_GET_AUTHORIZATION 5/6/2010 217,545 51,097,826,346 12,630,635
    USP_GET_AUTHORIZATION 5/7/2010 119,129 28,107,707,537 6,982,525
    USP_GET_AUTHORIZATION 5/10/2010 227,118 4,016,253,953 1,275
    USP_GET_AUTHORIZATION 5/11/2010 206,457 44,431,030,980 11,128,297
    USP_GET_AUTHORIZATION 5/12/2010 221,866 48,635,832,683 12,146,244
    USP_GET_AUTHORIZATION 5/13/2010 186,085 3,298,051,128 2,137
    USP_GET_AUTHORIZATION 5/14/2010 110,064 23,564,004,802 5,876,825

  123. USE [CompuSwiftData]
    GO
    /****** Object: StoredProcedure [dbo].[selectOrderDetailsMulit] Script Date: 06/08/2010 23:42:38 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    – =============================================
    – Author:
    – Create date:
    – Description:
    – =============================================
    ALTER PROCEDURE [dbo].[selectOrderDetailsMulit]
    – Add the parameters for the stored procedure here
    @txtOption1 varchar(25),
    @txtOption2 varchar(25),
    @txtQuantity1 varchar(25),
    @txtQuantity2 varchar(25)

    AS
    BEGIN
    – SET NOCOUNT ON added to prevent extra result sets from
    – interfering with SELECT statements.
    SET NOCOUNT ON;

    — Insert statements for procedure here

    SELECT [newQuantity] = @txtQuantity1,
    [newQuantity] = @txtQuantity2,
    [03_CS_Number],
    [08_Description],
    [10_Period_Text],
    [17_Rand_Currency_Indicator],
    CONVERT(DECIMAL(16,2), SUM([25_Total_Cost]* @txtQuantity1)) As Cost

    FROM Product_List_easyCGI

    WHERE ([03_CS_Number] = @txtOption1 OR [03_CS_Number] = @txtOption2)

    GROUP BY [03_CS_Number],
    [08_Description],
    [10_Period_Text],
    [17_Rand_Currency_Indicator],
    [24_Sub_Total_LB]

    END

    The New quantity column is not in the table, but the query works however it does not return to me the result I want

    newQuantity newQuantity [03_CS_Number] [08_Description] [10_Period_Text] [17_Rand_Currency_Indicator] Cost
    5 10 Item1 Desc1 Bi-Annually R 100
    5 10 Item2 Desc2 Bi-Annually R 250

    when I want this

    newQuantity [03_CS_Number] [08_Description] [10_Period_Text] [17_Rand_Currency_Indicator] Cost
    5 Item1 Desc1 Bi-Annually R 100
    10 Item2 Desc2 Bi-Annually R 250

    Also The website page that calls this SP passes through the variables listed at the top. These however could be “null” depending on the clients selection on the page. the results then get sent back to the page and displayed in a gridview.

  124. Hi Mr Pinal,
    Recently we used Bcp to export our tables to txt fille
    I have written a procedure to execute it but unfortunately am getting error

    Error = [Microsoft][SQL Native Client]Unable to resolve column level collations.

    Here is what the SP i have used to execute

    Alter procedure sp_AOStextgeneration
    as
    begin

    set nocount on
    declare @sql as varchar(8000)

    declare @docid as varchar(max)
    select @docid=newid()

    declare @count as int
    declare @lastupdate datetime

    select @lastupdate=isnull(max(Dateofdelivery),CONVERT(datetime,’1900-01-01′)) from ProductCart.dbo.tbl_sapsettings where active=1

    set @count= (select COUNT(*) from ProductCart.dbo.[v_AOS-SAP] where datediff(mi,ProductCart.dbo.[v_AOS-SAP].InvoiceDate,@lastupdate)0
    begin
    select @sql = ‘bcp “exec ProductCart.dbo.X_sp_generateAOStoSAP_h_data ”’ + @docid + ”’” queryout E:\Interfaces\AOS-SAP\’ + @docid + ‘_d.txt -c -t, -T -S ‘+ @@servername

    exec master..xp_cmdshell @sql
    –print @sql

    –select @sql = ‘bcp “exec pc_bak.dbo.X_sp_generateAOStoSAP_h_data ”’ + @docid + ”’” queryout E:\Interfaces\AOS-SAP\’ + @docid + ‘_d.txt -c -t, -T -S ‘ + @@servername
    –exec pc_bak..xp_cmdshell @sql
    —-print @sql

    if @@ERROR =0
    begin
    select @sql=’Insert into ProductCart.dbo.tbl_sapsettings(Dateofdelivery, FileName,Active) values (getdate(),”’ + @docid + ‘.dat”,1)’
    exec(@sql)
    end
    end
    else
    begin
    print ‘Zero Rows Copied’
    end

    end

    Can you pls tell where exactly whats the cause behind this issue.
    Thanks
    Regards
    N.Balaji

  125. Hi,

    As per my knoweledge DDL never gets rollback then
    how truncate get rollback. Even i have tested that create table also get rollback.

    Please Can you tell me how this is possible …?

  126. Hi Pinal,

    Can you help me in providing the basic fundamentals of the Graphical Execution plan of a Query, Stored Procedure, UDFs.

    I want to know how to improve the performance of a Query/Stroed Performance/UDFs and what are the rules we need to obey while solving a Graphical Execution plan.

    Thanks in Advance,
    Sumit

  127. Hello friend

    I have request in SQL Server
    I have tables including data (like countries) and I want to create script take all data from this table and make script including this data and can take this script and install it on another server without any error
    So what I need way to get table structure and data and put it in script, so when I need it in the future, just click on this script and it will create this table with data

    I hope get the answer

    • 1 Generate script of a table
      2 Export data to text file using a bcp

      Whenever you want to copy the table, you can run the script and import data from the text file

  128. Hi pinal,
    I am having trouble when replicating 2 databases on 2 sql 2005 servers, restored from backups of sql 2000.
    the following message i get:
    Message: Data conversion failed
    Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause)
    at Microsoft.SqlServer.Replication.Snapshot.SqlServer.BcpOutThreadProvider.DoWork(WorkItem workItem)
    at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
    at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc()
    at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
    Get help: http://help/0

    I looked around the google and could not find the answer, so if you could help me i will realy preciate this

  129. hi,

    how many connection active from Server SQL Machine? and we are using dotnet for data transfer from sql server and my programe, in this case if execute some query, still waiting sql server? if waiting, how many min. wait?

  130. Hi Pinal,

    I am using sql server 2000.
    I saw some interesting stuff in sql server 2005 is row_number() function.
    Using this function we can retrieve the records between the row number.
    Is there any function quivalent to this in sql server 2000.
    waiting for reply.

    Thank you.

    Best Regards,
    Praveen

  131. Hi Pinal
    I wanted to help me.
    I have Desktop Application that I use SQL Server Express 2008.
    and I use Full Text Search for my search
    and it work fine !
    But I had a questions

    1- I know that i can Encrypt One or More Columns of my table in Sql Express 2008 Whether this is true????

    2- I know too that when I search and use Full text Search Sql Server search in Separate File (Index), so When I search over one Column (that Encrypt) , my search speed not reduce and not have any Disadvantage Whether this is true????

  132. Hi,
    I want to improve my sql skills.That includes primarily analytical skills.Please recommend me how to do it.
    thanks.

  133. Hi,

    I want to take the record in between two date values ,
    but that date values are come NULL or EMPTY , that time how do use ISNULL and Is Empty functions in the date field.

    Thanks

  134. Hi Pinal,

    I have a question,

    In oracle, I can define variable’s datatype using another table’s column datatype in procedure or function.. (see the example) can you help me find it out similar things in sql server 2005? Thanks in an advance.

    Example:

    eno in emp.empno%type,
    name out emp.ename%type,
    job out emp.job%type,
    salary out emp.sal%type,
    location out dept.loc%type

    create or replace procedure display (
    eno in emp.empno%type,
    name out emp.ename%type,
    job out emp.job%type,
    salary out emp.sal%type,
    location out dept.loc%type
    )
    is
    begin
    select ename,job,sal,loc into name,job,salary,location from emp e,dept d
    where e.deptno=d.deptno AND empno=eno;
    end;

  135. hellow mr pinal. i have three table
    1)cust_child
    colum list ->cust_ID,Product_ID,version etc…

    2)cust_master
    colum list ->cust_ID,Cust_name,etc…

    3)product_master.
    colume list->product_id,Name,etc….

    i wan list customers name with there product ..
    can you reply me fast ..

    • select m.cust_name,p.product_name from cust_master as m
      left join cust_child as c on m.cust_id=c.cust_id
      left join product_master as p on c.product_id=p.product_id

  136. hi
    i have four table and I also has output can you help me how to get it that.
    create table person
    (PID int,
    Name Varchar(20))
    insert into person values (1,’x’)
    select * from person
    create table maritalStatus
    (PID int,
    Status varchar(20),
    Date Date)
    insert into maritalStatus values(1,’s’,’06/01/2010′)
    insert into maritalStatus values(1,’m’,’06/20/2010′)

    create table JobStatus
    (PID int,
    Status varchar(20),
    Date Date)
    insert into JobStatus values(1,’U’,’06/01/2010′),(2,’E’,’06/10/2010′),(3,’U’,’07/01/2010′)

    create table Fstatus
    (PID int,
    Status varchar(20),
    Date Date)
    insert into Fstatus values (1,’F1′,’06/01/2010′),(1,’F2′,’06/10/2010′),(1,’F3′,’06/22/2010′)

    output is

    PID Date MaterialStatus Jobstatus Fstatus
    1 06/01/2010 S U F1
    1 06/10/2010 S E F2
    1 06/20/2010 M E F2
    1 06/22/2010 M E F3
    1 07/01/2010 M U F3

  137. Hi Pinal,

    i need to know,

    how to restore database in sql server 2008 from a network drive?

    i have tried it by giving the network path “\\MyHomeServer\share\test.bak”, but this gives me an error, that the login account does have the access to the drive, or there no shuch path.

    (Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device ‘\\MyHomeServer\share\test.bak’. Operating system error 5(Access is denied.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.)

    Please help me short it out.

  138. Hi, I want to know details about how we can connect two remote sql servers????

    Will u please give me some exaple of how i can use sp_addlinkedserver

    I have used it but got error

    login failed for user ‘sa’

  139. Friends,
    I have this issue, where I am able to export the data to a text file in the server, but my requirement is to put the export file in the local machine, the data should be available to the user calling for it.

    The command I am using is
    EXEC xp_cmdshell ‘bcp “select chr_TextLines from mydatabase.dbo.myTable” queryout “C:\bcptest2.txt” -Uxyz -Pabcd -Smyserver -c -t,’, no_output;

    Please advice.

    This is the first time I am using the BCP utility to write back data to text file, I have only used BCP to take in values to tables.
    Thanks in advance

  140. Friends,
    I have this issue, When i retrieve the text Data of more then 65000 characters from a table its taking around 2 min time causes time out exception in our application. same whn i retrieve around 25000 characters, it retrievies within a sec.

    Could you please advice

  141. Hi Pinal,
    Please help me in following queries;
    1)How to overcome SQL Server 2005 maximumm row limit i.e. 8060 bytes.
    2)How to run SQL Server 2005 and Sql Server 2008 Reporting Services simultaneously on single machine.If possible then is any changes required apart from instance name.
    3)My server configuration is SQL Server 2005 64 bit edition with SP2 running on windows 2003 server and it consumes 7.2 GB of RAM out of 8 GB.I run SQL Profiler check Perfmon and run dbcc memorystatus.Results are given below.Please check and suggest me some tips to reduce memory usage.Even transactions log for 1 hour is not exceeded 500 KB.

    Buffer Cache Hit Ratio 99.98
    Page Life Expentancy:24120
    Stolen Pages:188776
    Target Pages:736588
    Total Pages:671077
    Lock Blocks:6
    Lock Memory :9720
    Sql Cache Memory:20056
    Target Server Memory :5893520
    Total Server Memory: 5369080
    AWE Memory Allocated:5471072

    VM Reserved 8497400
    VM Committed 87820
    AWE Allocated 5471512
    Reserved Memory 1024
    Reserved Memory In Use 0

  142. Could you please tell me how can i create a database, which is a copy of an existing database by using T-SQL? please dont tell backup and restore method, since i want to create database in third party server and they will not provide lack of permissions.

    • Which version of SQL Server are you using?
      If you use versions from 2008 onwards, you can generate the script along with data and run that script on the target server

  143. Is there a keyboard shortcut to set a field to the current datetime from the grid view in the same way you can use ctrl+0 to set a field = null?

  144. hi pinal

    select pwdencrypt(‘AAAA’) can be used to decrypt the password
    but how can we decprypt to retrive the original password

    Thanks
    Prajin

  145. Hi

    I had an issue with our SQl Job Agent yesterday.

    Jobs were showing as executing, but had not started their first step???

    They also didn’t show as failed, they just skipped a day forward, making it hard to identify the ones effected.

    Below are the logs, some hint at memory resource but checking system logs they don’t indicate that, could it be a network dns/permissions glitch?:

    [382] Logon to server ‘SBE01\SBE01′ failed (ConnUpdateStartExecutionDate)

    [165] ODBC Error: 0, Memory allocation failure [SQLSTATE HY001]

    [165] ODBC Error: 0, Driver’s SQLAllocHandle on SQL_HANDLE_DBC failed [SQLSTATE IM005]

    [165] ODBC Error: 0, Memory allocation failure [SQLSTATE HY001]

    I’d appreciate any hints or help with this, google is coming up with nothing and its nice to understand why these things happen.

    P.S Great SQL site, i always recommend it…keep up the good work.

  146. How to do indexing or web crawling
    for fast searching in database in SQL server

    I m not getting right info in right format as u explain on web

  147. hi ,
    I have partitioned a table which is having a Non clustered index . After Partitioning,the query optimizer not choosing the non clustered index key for scan. It does only table scan. I followed the following sequence to partition existing table.
    1. Remove all non clustered indexes
    2. Add identity column
    3. Created Non clustered index
    4. Created index on clustered index on Identity column
    5. Create partition function and partition scheme
    6. Drop the clustered index with move option and applied Partition scheme

    after applying partition the optimizer is not using the Non clustered index key to fetch rows…
    It does only table Scan. anything wrong in that sequence?

  148. hi ,
    I have partitioned a table which is having a Non clustered index . After Partitioning,the query optimizer not choosing the non clustered index key for scan. It does only table scan. I followed the following sequence to partition existing table.
    1. Remove all non clustered indexes
    2. Add identity column
    3. Created Non clustered index
    4. Created index on clustered index on Identity column
    5. Create partition function and partition scheme
    6. Drop the clustered index with move option and applied Partition scheme

    after applying partition the optimizer is not using the Non clustered index key to fetch rows… It does only table Scan. anything wrong in that sequence?

  149. Hi,,

    I have one doubt, how do work oracle database in sql server 2005?

    If possible, please suggest me as soon as…

    Thanks

  150. Hi,

    I have one doubt in sql server 2005,

    How to access / use oracle database from sql server 2005?

    If possible, please suggest me….

    Thanks to all.

  151. Hi Pinal,

    Your website has bailed me out on many an occasion, but this particular problem I’m having really has me stumped. I’ve set up Database Mail in SQL server 2008 R2 to connect to a remote smtp server and have set it up using basic authentication. I have also opened up Port 1025 (which is what the remote SMTP server uses) on my computer’s Windows Firewall. I have also stopped/started SQL Server Agent.
    However, the remote SMTP server rejects the logon saying “Authentication required’. What could the issue be? I am able to use the same settings on Microsoft Outlook and successfully send to the same SMTP server.

    Thanks,
    - Radhika

  152. HI Pinal,
    Thank you very much for your help. I have an issue with linked server.

    I have two SQLServer2000 instances connected with linked server.

    I have one table(ReaderInfo) in remote instance with a datetime data type column (generated_time).

    I got an error “error converting dbtype_dbtimestamp to datetime” when I executed a query like below.

    select * from [linkedserver].[readersdb].[readersuser].ReaderInfo
    where generated_time > getdate().

    Finally, I found 150 rows with invalid date(the dates were before 1753) in ReaderInfo at generated_time column.

    I updated those 150 rows with current date and executed the above query again. But still I am getting this error.

    There are no other invalid dates. Finally I removed the primarykey constraint in ReaderInfo table and recreated again. Surprisingly it was working.

    What is the reason? is there any problem with indexes which were exist in buffer so the query’s ‘where’ still referring?.

    is recreating a primary key is correct solution?

    Please suggest me and help me

    Thanks
    Krishna

    • I am surprised to read this
      The column generated_time is of datetime datatype
      How is it possible to store dates prior to 1753?
      I think it should have been a varchar datatype

  153. Dear Pinal,

    According to your experience what we should set the size of following fields while creating a new database.

    MDF Initial Size = ?
    LDF Initial Size = ?

    MDF
    —-
    Enable Autogrowth = ?
    File Growth = ?
    Maximum File Size = ?

    LDF
    —-
    Enable Autogrowth = ?
    File Growth = ?
    Maximum File Size = ?

    Thanks,
    Adnan

  154. Database Portal_WSSContent [SQLSTATE 01000]
    Msg 7729, Sev 16, State 1, Line 1 : Cannot specify partition number in the alter index statement as the index ‘Roles_PK’ is not partitioned. [SQLSTATE 42000]

    The above is the error getting on production server,can you please help me step by step

  155. Hi Pinal,

    I am jr DBA ,

    i got this error , when i take a backup from one system and Restore it to another system ,Plz help me why this error is coming

    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device ‘c:arcdb\school\schoollatest.bak’. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

      • Thanks ,

        I tried what u have said , again same error I took a backup from Server and restore it to another computer
        this problem is coming , is there any permissions do i need to change ,

        This problem is occured to one Database only

        The error while restoring from SSMS is
        Read on “:\arcdb\school\schoollatest.bak” failed (Reached the end of the file (Microsoft Sql Error ,3203)

        Please suggest me

  156. Hi Pinal ,

    I have doubt that which operating system support sql server 2008- windows 7 home basic or windows 7 home premium or windows 7 professional for sql server 2008 standard edition(for complete ssis,ssas,ssrs).
    Plz give the solution.

    Regards,
    Ashok

  157. Hi Pinal,

    I am getting the following error on any event in my sql server management studio:

    System.NullReferenceException: Object reference not set to an instance of an object.
    at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.CreateCWWindow()
    at Microsoft.SqlServer.Management.UI.VSIntegration.ShellCodeWindowControl.OnHandleCreated(EventArgs a)
    at System.Windows.Forms.Control.WmCreate(Message& m)
    at System.Windows.Forms.Control.WndProc(Message& m)
    at Microsoft.SqlServer.Management.UI.VSIntegration.ShellTextEditorControl.WndProc(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
    at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
    at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

    I am connected with SQL Server through LAN. Other users are not getting this error. So, only thing I can understand that the problem is something in the settings of my machine.

    Please help. It’s urgent.

    Thanks,
    Vinod

  158. Dear Dave,

    Currently my web site is working in sql 2000. I am planning to move sql 2008.

    Will it work without changes of query?

    Thanks
    G.premkumar.

  159. Hello,

    Kindly help me

    while am creating a duplicate table without records v r giving query as

    …..select into from where 1 1 ….

    i want to know the meaning of y we r giving 11 near where condition..

    help me..thanks

    • It should be

      select into new_table from old_table where 1=0

      It means create new_table based on the old_table
      But the condition 1=0 falis so new_table will be created without any data

  160. Hi below is one of my stored procedure, The purpose is to fill a table with all the sql statments that will be executed inside the stored procedure based on input parameter value.

    ————————————————————————-
    ALTER PROCEDURE [dbo].[up_hd_utility_add_note]

    @relatedId uniqueidentifier,
    @note ntext,
    @noteType nvarchar(50),
    @userName nvarchar(50)

    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @noteTypeId uniqueidentifier,
    @query nvarchar(4000)

    SELECT @noteTypeId = (SELECT note_type_id FROM dbo.hd_note_types WHERE item_alias = @noteType)

    INSERT
    hd_notes
    (
    related_id,
    note,
    note_type_id,
    created_by,
    created_date
    )
    VALUES
    (
    @relatedId,
    @note,
    @noteTypeId,
    @userName,
    getdate()
    )

    /* This is what i am using to insert the query into different table */

    SET @query=’INSERT
    hd_notes
    (
    related_id,note,
    note_type_id,
    created_by,
    created_date)
    VALUES
    (‘+convert(varchar(38),@relatedId)+
    ‘,’+convert(varchar(4000),@note)+’,’
    +convert(varchar(38),@noteTypeId)+’,’
    +@userName+’,’
    +convert(varchar(50),getdate())+’)’

    INSERT zzz_ae_transations (sql_statment) VALUES (@query)

    END

    ———————————————————————-

    But there are around 250 stored procedures and if i follow this path it will be very hard maintaining them later on.

    IS there any table which stores the last executed statement with the values of variables substituted in it..
    ( i tried

    SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC

    DBCC INPUTBUFFER ()

    )

    but they all give the query with variable name but not the actual values passed into the procedure.

    Any pointers will be greatly appreciated.

    Thanks.

  161. hi

    i have sql script contains table creation, view creation, function creation, insert query and procedures

    it is like

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[TBLNARRATIONMST_01]‘) AND type in (N’U’))
    BEGIN
    CREATE TABLE [dbo].[TBLNARRATIONMST_01](
    [ID] [int] NOT NULL,
    [DESCRIPTION] [varchar](800) NOT NULL,
    [MODIFIEDBY] [varchar](50) NOT NULL,
    [MODIFIEDON] [datetime] NOT NULL,
    CONSTRAINT [NARRATION_PK_01] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
    CONSTRAINT [NARRATIONDESC_UK_01] UNIQUE NONCLUSTERED
    (
    [DESCRIPTION] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    END………..
    …………….
    ……….
    ans so on

    i fetch this script in one procedure as nvarchar(max) variable.

    how can i execute that script in procedure.

    I divide my script in substring with less than 4000 characters.
    but
    ————–
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ————-
    gives error
    why does GO give error execute in procedure?

    script executes successfully in query window, but i want to execute in procedure.
    my script is stored in one table and i fetch that in procedure.

    please give me suggestion…..

    thanks

  162. Hi Pinal,

    I am working on a project that uses SQL Server. I am having a problem to figure out how to split string from a certain field of datarows and then return the array of the string. I gonna use this for tag cloud.

    Let say I have a table named Content_tbl, this table sa 3 fields (Title, Body, Tags). When storing data to this table I would like the Tags field to hold a comma separated words.

    If Content_tbl has 2 records and on the first record my Tags field conatains “Tag A, Tag B” and on the second record my Tags field contains “Tag B, Tag C”. If I select all records I want the SQL to get the content of “Tags” field and return as a list like as follows:

    Tag A
    Tag B
    Tag B
    Tag C

    and if possible to make it this way also:

    Tag A 1
    Tag B 2
    Tag C 1

    I hope you can help me with this problem. I’ll appreciate it very much.

    Thank you,

    Gary

  163. Hi Pinal,
    In my machine i have sql server 2008. when i was trying to send mail from sql server 2008,i was not able to that.Bcoz in my machine sql server log tab there is no Database Mail tab.what i want do now,pls help me.Am very eager to send mail from sql server 2008.I have seen ur article in codeproject,same method am following.or anyother way is there…?

  164. HI,

    i want to know how can i get the list of users or no of users connected to my database remotely through any site.

    thanx.

  165. Hi Pinal,

    I am a junior DBA. I was doing a Transactional log shipping of a database into a secondary/standby server and I got the error as :

    Cannot open backup device
    ‘\\DS2\Demo_Database\Demo_Database_Mirroring.bak’. Operating system error 2(The system cannot find the file specified.).
    RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)

    Please suggest me how to proceed further and fix this error and do the Transactional Log Shipping successfully.

    Thanks in Advance

      • Yes, the Full Control permission is given to the Backup folders and Destination Folders./ Problem is that for the First time the transaction log shipping was done. But when I deleted the transaction log and again set it. It throws me this error.
        Please suggest.

        Thanks,
        Sumit

  166. Hi Pinal,

    I had SQL Server 2005 intalled on my pc and also installed 2008. I got the issue to start SQL Server Agent. I can’t see the instance of SQL server 2008 into Management studio connect to server screen in server name.

    I need help. kindly reply its big pain for me. I have search for blogs and binging also. but I did not find solution.

    Thanking you.


    Regard,
    Haresh

  167. I think I found the way through the e-mails. I have deleted the subscription. So the last comment is not valid.
    Thank you

  168. Hello,

    On my servers I always face a problem of SQL taking very large memory. My services are continuosly ineracting with MS SQL server, after some time task manager shows large memory usage by SQL. If I restart my services it starts working fine. But, again after sometime server gets slow because memory usage of SQL. Can you please guide how I can control this.

  169. hi,
    Problem wiht BCP.

    1. declare @fileName varchar(200)
    2. declare @bcpCommand varchar(300)
    3. SET @fileName = ‘C:\Test\testbcp.txt’
    4. SET @bcpCommand = ‘bcp “select chr_TextLines from pay_TextFile” queryout “‘
    5.SET @bcpCommand = @bcpCommand + @fileName + ‘” -c -t, -Uxx -Pxx -S’ + @@servername
    6. –print @bcpCommand
    7. EXEC xp_cmdshell @bcpCommand

    Please help

    The Error is
    1. SQLState = 42S02, NativeError = 208
    2. Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name ‘pay_TextFile’.
    3. SQLState = 42000, NativeError = 8180
    4. Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
    5. NULL

    • Hi,

      4. SET @bcpCommand = ‘bcp “select chr_TextLines from mydb..pay_TextFile” queryout

      The problem I guess was that I did not use the 3 part naming convention, and sql was looking for pay_TextFile (my table) in the master database.

      Thanks,

  170. Hello Sir

    I am using MS SQL Server 2005, all services are working well but when i tried to use sql server reporting services then it shows following error

    Cannot connect to Amit-PC

    Additional Information
    The Reporting Services Instance could not be found(Microsoft.SqlServer.Managment.UI.RSClient)

    Please tell me how can i fix this problem.. n how to configure reporting services on my pc

  171. hello dear,

    i am a sytem administrator of an organization. the organization wonts me to connect sql server 2008 witch are in differt domain and forestes. i had hints on connecting sql server in the same forest but no hint at all in different forest. so sir could you pleas help me what to do(what steps to follow to do this )

    Thanks in advance

    note
    i can ping to the commputers of different domains and i can access users of active directory in the other server witch is in different forest.

  172. We have following scenario of maintenance schedule of database backup
    Daily full Backup 12:00am
    Deferential Backup Every 5 hours
    Transaction log Backup Every 20 minute
    1) Suppose our database crash at 23:55, so what will be database restoring scenario.

    If full backup maintenance plan is already started at 12:00am, it will take around 2 hours to complete, deferential backup maintenance will run at 1:00am. So will scenario:
    i) Deferential backup also start at 1:00am schedule time or wait for full backup to complete.

    If both backup will start at their schedule time,
    What will restore scenario, as on following situation?
    ii) If both backup running , database crash at 01:05am
    iii) If Full backup is completed and differential backup running and database crash 01:15am
    iv) If deferential backup is competed and full backup is running.

  173. Hi Pinal,

    I’m impressed with your knowledge sharing here. My task includes writing a lot of scripts to upload raw data into MSSQL DB. Hence, I use many various ways, depending on the source file. Here is my question:

    Assuming I have this two options to choose only, which method is faster to do bulk insert ?

    (i) I notice from your blog, you use this method.
    INSERT INTO JoinedTable (ID2,Col2)
    SELECT 1,’First’
    UNION ALL
    SELECT 2,’Second’
    UNION ALL
    SELECT 3,’Third’
    UNION ALL
    SELECT 4,’Fourth’

    (ii) I’m currently using this method, and I know is not very efficient.
    INSERT INTO JoinedTable (ID2,Col2) VALUES 1,’First’
    INSERT INTO JoinedTable (ID2,Col2) VALUES 2,’Second’
    INSERT INTO JoinedTable (ID2,Col2) VALUES 3,’Third’
    INSERT INTO JoinedTable (ID2,Col2) VALUES 4,’Fourth’

    There can be up to >100k records for each upload. I’m using method (ii) by using vi text editor to format the raw data, as the data can come in various format, not conforming to bcp or MSSQL or delimited, etc.

    Note: I welcome replies on other faster methods, but my primary question here is which one is THE faster method to insert ? (i) or (ii)

    Thanks

      • Pinal, Madhivanan,

        I did read from Madhivanan blog earlier and may try out this method to load into staging-temp-tables first:
        “BULK INSERT to table with specific columns”

        Then proceed to massage the data into actual tables.

        It will help, as bcp takes <1 minute but the manual insert may take 3-5 minutes for 100k. Imagine if I need to do it for some initial full upload of 10 million records.

        More feedback are welcomed. Thanks

  174. Hi Pinal

    We have following scenario of maintenance schedule of database backup
    Daily full Backup 12:00am
    Deferential Backup Every 5 hours
    Transaction log Backup Every 20 minute
    1) Suppose our database crash at 23:55, so what will be database restoring scenario.

    If full backup maintenance plan is already started at 12:00am, it will take around 2 hours to complete, deferential backup maintenance will run at 1:00am. So will scenario:
    i) Deferential backup also start at 1:00am schedule time or wait for full backup to complete.

    If both backup will start at their schedule time,
    What will restore scenario, as on following situation?
    ii) If both backup running , database crash at 01:05am
    iii) If Full backup is completed and differential backup running and database crash 01:15am
    iv) If deferential backup is competed and full backup is running.

  175. Hi,

    I have learn the queries in oracle, and doubts the following conditions…

    product table with data as below

    Pro_Id Pro_Name Order_Date
    P1 Prod A 28-JUN-10
    P2 Prod B 27-JUN-10
    P3 Prod C 28-JUN-10

    I want query in oracle to get the following conditions result…

    1) Get 28-JUN-10 ordered prod_name
    2) get prod_name in between ’28-JUN-10′ to ’28-JUN-10′

    Thanks

      • I have use the following query, but it not retrieve the ’28-JUN-10′ result.

        select Pro_name from product where order_date>=’28-JUN-10′ and Order_Date<='28-JUN-10'

        bez i want to given the same dates in from and to date criteria…

  176. Hi Dave,i am venkat presently working as sqlservr data base and BI devloper , i am intrested to certification in the same,can u pls give me some guide lines to do certification and also suggest me preparing material

  177. Hi Dave,

    I am kinda new abut here is what I am trying to do. I have SQL Table that is laid out as follows:

    i.e data is arranged as binary tree fashion Each parent node can atmost have 2 childs (left node L & Right Node R)

    Aim: my obejective is to retrive sub tree for any node

    ID Name Supervisor NodeType
    1 Mike 0 0
    2 Dave 1 L
    3 Gary 1 R
    4 Sally 2 L
    5 Mary 2 R
    6 Susan 3 L

  178. Hi Pinal,

    I have created a package to import excel path. But I am facing one problem, there is a column in the excel which has values like “$0.031380″ , “$0.035020″. It seems that the column has been formatted, and the ‘$” sign has been added. When I import the file with the SSIS package developed, the values gets rounded off. e.g. “$0.031380″ is imported as 0.0314. How can I overcome this? Do I need to write any script? Can you please help me.

    Thanks in advance,
    Rupesh

  179. Hi Madhivanan,

    I have tried that also, but it did not work. However I’ll give it another try.
    I want to know, is it possible to write some script and remove the formatting of the columns in the excel (this script I want to use before reading the excel)?

    Thanks ,
    Rupesh

    • Rupesh,

      We use NUMERIC(28,8), although the users used up to 6 decimal points only.

      Also, to remove the $, we will normally load into temp table. Then use the REPLACE command before upload into actual table.

      The other method is to write VB/C#.net or Excel macros to remove the $ from the column, then only use MSSQL to load in.

      • “we will normally load into temp table”
        - to clarify, the temp table is varchar(50) column, in order to accept it as string value with $ or other currency.
        - then REPLACE
        - then upload into actual table with numeric(28,8)

  180. Hi Pinal.

    Can you help me whit this …?

    I have a Transactional Replication, this week the replication is marked inactive and need to be reinitialized.

    I manually copy de info that i need on the suscriber and all the data is the same on both sides.

    What a i have to do to mark de replication active again without do re Snapshot in the reinitialization process ???

    Thanks in advance …

    Saludos desde Mexico !!

  181. Hi i have a qucik and imp question,

    in our DB we have 6 users and Every one logs in with the same login, some one has dropped a really Imp table so we are trying to figure out who it is and at what time the table is dropped,
    can you please give me a query or info to check this

    really appreciate your Help

    Thanks in Advance

    • Which version of SQL Server do you have?
      If the version is later than 2000, you could have written a DDL trigger for this. Otherwise you should have run the profiler

  182. Hi Pinal,

    Can you please write somethig about the techiques of doing a Query optimization (by Graphical Execution Plan). All the tricks that should be measured for optimizing a query or stored procedure.

    As I am a new member of this blog, I am unaware of if this had already been posted before. I have a keen interest of knowing the tricks/techiniques of Optimizing an inline query/stored procedure.

    As I am a Jr. DBA, sometimes I ofetn faces situation of poor performing queries and I don’t have any idea how to optimize them.

    I would be very thankful if you consider my request. I guess the other members will also gain some ideas from it.

    I will be waiting for your reply and the techniques for learning the query optimization.

    With Best Regards,
    Sumit

  183. I have two tables which contain the same records but those records are entered by different users and i want to calculate similairty between same records(by every comapring every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C).

    For Example I have two tables Table_A and Table_B as below:

    Table_A
    ———————-
    RecordID StudentID Dept BookID
    1 123 CS 456
    2 123 CS 345
    3 223 TE 190

    Table_B
    ———————-
    RecordID StudentID Dept BookID
    1 123 CS 456
    2 223 TE 345
    3 223 TE 190

    and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows:

    Table_C
    ———————-
    Sim_RecordID Sim_StudentID Sim_Dept SimBookID
    1 1 1 1
    1 0 0 1
    1 1 1 1

    Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field. I hope i have clearly defined my probelm. any help would be appreciated.

      • @ samy / @kamaran,

        you can use the below query to get the Result as shown in table_c

        Select
        CASE WHEN Table_A.RecordID=Table_B.RecordID THEN 1 ELSE 0 END,
        CASE WHEN Table_A.StudentID=Table_B.StudentID THEN 1 ELSE 0 END,
        CASE WHEN Table_A.Dept=Table_B.Dept THEN 1 ELSE 0 END,
        CASE WHEN Table_A.BookID=Table_B.BookID THEN 1 ELSE 0 END
        From
        Table_A
        INNER JOIN Table_B
        ON(Table_A.RecordID=Table_B.RecordID AND Table_A.BookID = Table_B.BookID)

  184. I m going to start my new company for software development in .net with db as Sql 2008.So in simple lines can u tell me that which edition of .net and sql 2008 shoul I purchase.Please

  185. Hello Pinal,

    I am just learning SQL2005, and was wondering if there was a way to automate my current task. I have over 450 database, wherein each database contains the same table structure. Instead of fishing though 450 database looking at 1 specific table for a value greater then 2 (which I create a query). I was wondering if there is a way to search across all databases that contain this table name, where said table has a colume with a value that is greater then 2

    Thank you for your time in addressing my question.

  186. Hi Pinal,

    If a table have 1 lac record, by procedure dump to other table for reporting purpose. Next day that table have 3 lac record then will we again port all the record? is there any process to identify incremental record.& if some previous records are updated then how to identify the update previous record? & these changes how to reflect in dump table?

    Regards,
    Ashok

  187. Can anyone help me in explaining the script given below:

    I want to know how CASE Statement is working in the script. Its Urgent

    DECLARE @Text NVARCHAR(2000)
    DECLARE @StringDelimiter CHAR(1)
    SELECT @Text = ‘This T-sql will split senteneces into rows.’+ ‘How many rows will be returned?.’+ ‘M.a.y.b.e..n.n.o.e.?’, @StringDelimiter = ‘.’;
    With Tally (Number)
    AS
    ( SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number UNION ALL SELECT Number + 1 AS Number FROM Tally WHERE Number 0
    THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) – 1
    ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = ”
    AND CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0
    THEN LEFT(@Text,Number -1) ELSE RIGHT(LEFT(@Text,Number – 1),

    CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0 THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1
    ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END)
    END AS SPLIT FROM TALLY WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter OR Number – 1 = LEN(@Text))

    Can somebody explain me How this script actually is working ?
    especially Case Statement

  188. Can somebody explain me How the script given below is working ?
    especially Case Statement

    DECLARE @Text NVARCHAR(2000)
    DECLARE @StringDelimiter CHAR(1)
    SELECT @Text = ‘This T-sql will split senteneces into rows.’+
    ‘How many rows will be returned?.’+
    ‘M.a.y.b.e..n.n.o.e.?’,
    @StringDelimiter = ‘.’;
    With Tally (Number)
    AS
    (
    SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number
    UNION ALL
    SELECT Number + 1 AS Number
    FROM Tally WHERE Number 0
    THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) – 1
    ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = ”
    AND
    CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0
    THEN LEFT(@Text,Number -1)
    ELSE RIGHT(LEFT(@Text,Number – 1),

    CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0
    THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1
    ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END)
    END AS SPLIT FROM TALLY

    WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter
    OR Number – 1 = LEN(@Text))

    OPTION (MAXRECURSION 32767)

  189. This is the right script ……………..
    DECLARE @Text NVARCHAR(2000)
    DECLARE @StringDelimiter CHAR(1)
    SELECT @Text = ‘This T-sql will split senteneces into rows.’+
    ‘How many rows will be returned?.’+
    ‘M.a.y.b.e..n.n.o.e.?’,
    @StringDelimiter = ‘.’;
    With Tally (Number)
    AS
    (
    SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL) )AS Number
    UNION ALL
    SELECT Number + 1 AS Number
    FROM Tally WHERE
    Number 0THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) – 1 ELSE CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) END) = ”
    AND CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1))) = 0
    THEN LEFT(@Text,Number -1)
    ELSE RIGHT(LEFT(@Text,Number – 1),
    CASE WHEN CHARINDEX(@StringDelimiter,Reverse(LEFT(@Text,Number -1)),0) >0
    THEN CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) -1
    ELSE CHARINDEX(@StringDelimiter,REVERSE(LEFT(@Text,Number -1)),0) END)
    END AS SPLIT FROM TALLY

    WHERE(NCHAR(UNICODE(SUBSTRING(@Text,Number,1))) = @StringDelimiter
    OR Number – 1 = LEN(@Text))

    OPTION (MAXRECURSION 32767)

  190. Hello PinalKumar

    I teach SQL at the University here in the USA. Every year you become a celebrity among my students without me even telling them about you. It has been a fun experience each year to see how soon until everyone is using your site for their research. It usually happens when we get to advanced queries that have Ranking functions or CTEs. Your examples are vivid, clear, and explain it in a way where the student feels confident about what they have learned. You deserve great success for what you are doing.

    I am also the Author of the Joes 2 Pros SQL book series and would like to pay tribute to you in some upcoming books and training DVDs. Perhaps pointing people to your web site and maybe giving them a snipped from an article for you to choose. All of them are good. Would love to let all my readers know about you. Contact me directly when you want more materials.

    -Rick A. Morelan (Joes2Pros)

  191. hi
    I want to create a view which will give the output

    …..
    …..
    Value from the table
    …..
    …..
    –This is the problem area, I want to display “Others” as the last item. I dont mind using any symbol in front of “others” to make it the last item. In fact I tried “{” and “|” as they come after “z” in the ASCII table.

    I can not use the ID to sort as I want my Discipline to be sorted.
    ————
    SELECT DISCIPLIN AS Discipline, DISCIP_ID
    FROM dbo.mDISCIPLIN
    UNION
    SELECT ” AS Expr1, 0 AS Expr2
    UNION
    SELECT ‘|OTHERS|’ AS Expr1, 999 AS Expr2
    ORDER BY DISCIPLINE
    ———–
    Please help.

  192. hi Pinal,

    I am not able to copy my production diff backup using the cmdshell command .i want to tell you i am not a domain user on the server becouse server start under lacal service.
    so any other way so i can copy using that command

    exec xp_cmdshell ‘copy \\165.145.34.289\G$\Database_backup\Diff_backup_smswwil\latest_backup..bak \\165.145.34.289g$\diff_backup\latest_backup.bak’

    Regards,
    Pawan Singh

  193. Dear sir,
    Can u give me a documentation about the new features in sql server 2008…Actually i have joined a new office and i have been assigned a task to give a presentation on New Features in Sql Server 2008 to almost 50 people. I have a good knowledge of Asp.net and sql server 2005

    Thanks & Regards
    Sumit Thapar

  194. Dear Friends,
    Would anybody please answer this Question..

    Q) CPUs can dynamically be added to a running system, by using ____________ feature
    1)Cold Add CPU
    2)Hot Add CPU
    3)Dynamic add CPU
    4)Passive add CPU

    Thanks and Regards,
    Riyaz

  195. Dear dave,
    me working in server 2008, i got i month gap in that time i forgot my sa passwd but in that i have a lot of dbs more sps and almost my all my work through sa only they work.
    now can i change password. if i changes any my processes effect is there any way to get all should be work properly.

    waiting
    regards
    prasad

  196. Hello sir

    I am facing one problem in sql 2008.when I give a filter of date in view it becomes slow otherwise it runs fast.I can not understand why this happen and what is the solution.Please reply.

  197. Hello sir

    I am facing date filter problem in sql 2008 since sql 2008 is installed.When i give date filter in query it becomes slow and without date filter it runs fine.Can you give me solution for this?

  198. Why is that after I installed SQL Server 2008 and selected Default Instance, the Server name in the Login screen says (local) but not MSSQLSERVER? This is the Full version of the SQL Server and not Express.

    • We have migrated from sql 2000 to 2008.But after installing sql 2008 i face the problem in selecting dates.If i select 1year period in query or view it runs fine but if i select period of less than 1 year it takes time to run.For ex. If i select period from 1-apr-2009 to 31-mar-2010 then query or view runs very fine and takes 1 min to run but if i select period from 1-jul-2010 to 11-jul-2010 then it takes 5 min to run.Please give the solution.

  199. Sir,

    Good Morning.

    I have doubt regarding SQL Database. I am using SQL Server 2008 for 2005 database.

    I want to know the login name , ip address, query or procedure executed at the back end i.e. in database.

    Because I want to catch the person who is executing the scripts at the back end.

    Thank You.

    With Regards,
    Karthik

  200. Hi Pinal

    I’m documenting a database, all it’s tables,views, procedures etc and their dependencies. Which is fine for all objects in the database, but if I have a view or procedure that depends on a Linked Server e,g

    SELECT DRKY AS [Asset Status Code], DRDL01 AS [Asset Status Code Description]
    FROM MIF.JDE_PRODUCTION.PRODCTL.F0005 AS F0005

    None of the standard ways in SQL Server can return the table F0005. Do you have suggestions

    Regards
    Robert

  201. Hi pinal

    I am daily reading your blog and i get lots of knowledge about sql server from you so thanks for that.

    I found one script about to know more about how to work indentity column. but i have confusion about that.

    below are that script.

    *** 2010-07-07
    [18:52:32] *** rahul is Online
    [18:52:28] CREATE TABLE #Temp1
    (
    Temp1ID int NOT NULL IDENTITY (-2147483648,1),
    Temp1Value char(1) NOT NULL
    ) ;
    CREATE TABLE #Temp2
    (
    Temp2ID int NOT NULL IDENTITY (-1,-1),
    Temp2Value char(1) NOT NULL
    ) ;
    INSERT INTO #Temp1 (Temp1Value) VALUES (’1′) ;
    INSERT INTO #Temp1 (Temp1Value) VALUES (’2′) ;
    SET IDENTITY_INSERT #Temp1 ON ;
    INSERT INTO #Temp1 (Temp1ID,Temp1Value) VALUES (100,’3′) ;
    SET IDENTITY_INSERT #Temp1 OFF ;
    INSERT INTO #Temp1 (Temp1Value) VALUES (’4′) ;

    INSERT INTO #Temp2 (Temp2Value) VALUES (’1′) ;
    INSERT INTO #Temp2 (Temp2Value) VALUES (’2′) ;
    SET IDENTITY_INSERT #Temp2 ON ;
    INSERT INTO #Temp2 (Temp2ID,Temp2Value) VALUES (100,’3′) ;
    SET IDENTITY_INSERT #Temp2 OFF ;
    INSERT INTO #Temp2 (Temp2Value) VALUES (’4′) ;

    select * from #Temp1
    select * from #Temp2

    DROP TABLE #Temp1
    DROP TABLE #Temp2

    Thanks
    Mori Ajay

  202. Hi,
    Please Redirect this issue to concern person if i m hitting in between the wrong thread…

    Q: I want to create some hypothetical indexes in order to fire some issues..i googled a lot but couldn’t figure out how to create and retain a hypothetical index..while we run index tuning wizard it creates the hypothetical indexes but as soon as it finishes it deletes all the hypothetical indexes automatically.
    Please let me know the solution to the above query.

    Thanks & Regards
    Vijay Gupta

  203. Hi Pinal,

    i am regullar reader of your blog, your postings are very much helpful for me. i am working as a Microsoft Report / ETL developer. give me some examples of Gauge in SSRS 2008.

    Thanks in Advance

    -Raj

  204. Hi Pinal,

    Can you please tell me how to change of location for Reporting server http logs?

    I tried to change with jbelow instructions but it’s not working at all and error message come out when i edited the web config under reporting server folder.

    · c:\Program Files\Microsoft SQL Server\Reporting Services\ReportManager\web.config

    · c:\Program Files\Microsoft SQL Server\Reporting Services\ReportServer\web.config

    · c:\Program Files\Microsoft SQL Server\Reporting Services\ReportServer\Bin\ReportingServicesService.exe.config

    Add the following tag under RSTrace in all the above files:

    Be sure that you apply the same permissions to the new path folder that is on the LogFiles folder.

    Restart the ReportServer Windows Service

    your help will be appreciate.

    Thanks .
    Naing

  205. Hi,

    We installed some applicaions and SQL server express 2008 and after that we renamed the user account Administrator .Once that was done ,all admintrator dependable services LOGON AS was changed to the new user’s name and password.But i can see one of my JOB failing in the SQL.When we run that JOB it fails

    07/08/2010 13:27:00, SyncUserPrivilegesTables,Error,0,WINDOWS-B7L8D0H, SyncUserPrivilegesTables,(Job outcome),,The job failed. Unable to determine if the owner (WINDOWS-B7L8D0H\Administrator) of job SyncUserPrivilegesTables has server access (reason: Could not obtain information about Windows NT group/user ‘WINDOWS-B7L8D0H\Administrator’ error code 0×534. [SQLSTATE 42000] (Error 15404)).,00:00:00,0,0,,,,0

  206. Hi Pinal,

    We have scheduled a job to pull data from our client’s database (sql server 2000) setup as a linked server into our SQL server 2005 DB. The job selects records and updates a flag in the source (client) DB to indicate that the record has been pulled.

    The select statement executes within a second, however the update statement is taking on average 12 mins to complete; and at times gives the error ‘Cannot get the data of the row from the OLE DB provider “SQLNCLI” for linked server’. in case where the error is received, on checking the flag status, we have found that the flag has been updated.

    The update statement only has the primary key in the ‘where’ clause.

    The client has confirmed that there are no triggers on the table in question and there’s no activity on the database when the job is scheduled to run.

    There is another table in the client’s DB which we tested for update statements and the update completes within no time.

    Any ideas on what else we might be missing here that’s causing the delays and needs to be looked into??

    Thanks.

  207. SELECT TOP 1 ApplicationNumber
    FROM (SELECT TOP 5 ApplicationNumber FROM Outbound_Data od )a ORDER BY a.ApplicationNumber DESC

    SELECT TOP 1 ApplicationNumber
    FROM (SELECT TOP 5 ApplicationNumber FROM Outbound_Data od ORDER BY 1)a ORDER BY a.ApplicationNumber desc

    what is diff between this 2 Query? why its give diff Result?

  208. Hi Pinal ,
    this is Shital kasliwal.
    I have a one prob.

    CREATE TABLE temp
    (
    ID numeric PRIMARY KEY IDENTITY(1,1),
    name varchar(25)
    )

    insert into temp
    select ‘RAM’
    union all
    select ‘Sham’
    union all
    select ‘vijay’
    union all
    select ‘RAM’
    union all
    select ‘Sham’
    union all
    select ‘vijay’
    union all
    select ‘RAM’
    union all
    select ‘Sham’
    union all
    select ‘vijay’
    union all
    select ‘RAM’
    union all
    select ‘Sham’
    union all
    select ‘vijay’
    union all
    select ‘RAM’
    union all
    select ‘Sham’
    union all
    select ‘vijay’
    union all
    select ‘RAM’
    union all
    select ‘Sham’
    union all
    select ‘vijay’

    SELECT TOP 1 id
    FROM (SELECT TOP 5 id FROM temp ) a ORDER BY a.id desc

    SELECT TOP 1 id
    FROM (SELECT TOP 5 id FROM temp od ORDER BY 1)a ORDER BY a.id DESC

    both Query Given Diff result Why?
    How is it possible?

    • Hi Shital,

      In the first query since there is no order by clause , the top 5 results can be retrived from #temp can be in any order

      In the second query , the top 5 will be (1,2,3,4,5) since u have given the “order by 1″ it selects only the top 5 order by ID ASC

  209. Hi Pinal,
    I want to get SQL Server’s cpu usage and memory usage (which gives like 250 mb or %x of cpu – %y of ram)
    And i want to get current session on sql server, how many session is on the sql server, it should be live, can you help me please ?

  210. Hi Pinal Dave,

    I need query for this tables.

    Category
    ————————-

    ID
    CategoryName

    SubCategory
    —————————
    SID
    C_ID
    Value

    Query:
    ————————
    ID, Category Name1, Category Name2….etc

    1 Values of SID Values of SID

    2 Values of SID Values of SID

    Please let me know how to write a query for this solution

    • Hi,

      I have try to use the following query, but its appear error message …., please give me the correct query or tell that error message reasons…

      EXEC dynamic_pivot
      ‘Select C.ID,C.CategoryName,S.CateValue From TblCategory AS C INNER JOIN TblSubCategory AS S
      ON C.ID = S.C_ID’,
      ‘CategoryName’,
      ‘CateValue’

      (3 row(s) affected)
      Msg 156, Level 15, State 1, Line 4
      Incorrect syntax near the keyword ‘for’.

      Thanks to all

  211. Hi Pinal Dave,

    You Can make a Link between MSSQL And MSAccess by using
    linked Server
    using this Commands

    EXEC sp_addlinkedserver
    @server = N”,
    @provider = N’Microsoft.Jet.OLEDB.4.0′,
    @srvproduct = N’OLE DB Provider for Jet’,
    @datasrc = N”
    GO

    – Set up login mapping using current user’s security context
    EXEC sp_addlinkedsrvlogin
    @rmtsrvname = N”,
    @useself = N’TRUE’,
    @locallogin = NULL,
    @rmtuser = N”,
    @rmtpassword =
    GO

    But in the msaccess has a password no one can make this link

    can you help me in this ?????

  212. hello sir,

    i have a query regarding switching the data from one table to another.
    When i try with following query then all the records copied into another table but if i want to copy only few columns then its not working.

    insert into singhtable2 select * from singhtable1;

    Sachin Singh

    • @Sachin.

      You need to specify column names if you want to restrict columns in insert statement.

      Sample Script:

      Insert into Destination_Table ( Cola, Colb, Colc)
      Select Cola, Colb, Colc
      From Source_Table

      Explanation: What we are doing in above statement is, we are getting data for three columns from source table and inserting into destination table in the three columns, and the three columns are given in brackets.

      Check out books online, or google on topic, Insert statement in SQL Server, to find more examples.

      Let us know if you need help with this topic.

      ~Peace.

      • Dear Samy,

        Make your query something like this

        Insert Into Persons_Backup(Column,AnotherColumn)
        SELECT LastName,FirstName From Persons

        Hope it will help

        regards,
        Adnan

          • Hi,

            Can tell the difference between these queries.

            Option (1)

            SELECT LastName,FirstName
            INTO Persons_Backup
            FROM Persons

            Option (2)

            Insert Into Persons_Backup(LastName,FirstName)
            SELECT LastName,FirstName FROM Persons

            Thanks

  213. Hi Pinal,

    I have been a follower of your blog and I have a couple of questions for you. Do you have any tutorials or any link for tutorials on SSIS and SSRS other than the one available from MSDN?

    Thanks for your help in advance.

  214. Dear Pinal sir,
    Can u plz guide me how to loop through rows of table and fetch values one by one without using cursors..

    Thanks & Regards
    Sumit Thapar

  215. Hi Pinal

    I havent check your all articles, but gone through some of them. It’s really excellent. Recentally i have started working as a SQL Server DBA(2008), i would like to learn basic DBA stuffs, if its there any link or blog please let me know. Thanks a lot

  216. Pinal,

    i have a question regarding Insert Statement.

    i have a scenario to insert into a table.

    for example:

    INSERT into table2 values
    ([lsp_id]
    ,[prev_lsp_id]
    )
    select
    ISNULL([lsp_id],”),
    ISNULL([lsp_id],”)
    ) from table1

    i need use calculated value ISNULL([lsp_id],”) in both the
    fields instead of writing two times because i do have very big transformation if i write 2 times it will consume more time.

    for now i am inserting null value into second one and updating it using bulk update.

    how can i bulk insert calculated value more than once in a insert statement ? Please help me on this one.

  217. Hello Dave Pinal,
    Greetings, I’m a big fan, any time I need to research something, your site is at the top of my list.
    I have a problem running a store procedure and sending the results to a table:
    Here is my script (written on MSSQL2000):

    CREATE TABLE #tUsers
    (
    UserName sysname NULL,
    GroupName sysname NULL,
    LoginName sysname NULL,
    DefDBName sysname NULL,
    UserID smallint NULL,
    SID smallint NULL
    )

    INSERT #tUsers
    EXEC SP_HELPUSER

    However, this script fails when the database is using ALIASES, as the EXEC SP_HELP user part returns TWO results sets instead of just one.
    Any idea on how to workaround this dilema?

    Any help would be greatly appreciated.

    Thank you

    Miguel

  218. Hi Pinal,

    I am regular follower of your site. I have problem while deleting the database user. I have 3 databases in my server and have users and able to delete a user in one database with the help of below query.

    select dp2.name as role, dp1.name as owner
    from sys.database_principals as dp1 inner join sys.database_principals as dp2
    on dp1.principal_id = dp2.owning_principal_id
    where dp1.name = ”

    But when i try to delete the other database user, it is not working. Can you please help me in this regard.

    M. Mallikharjuna Rao

    • @Malikharjuna

      Can you please share with us what script you are using to delete a user from specific database

      Also, please paste your error message that you got while deleting user from database.

      I believe you are using SQL Server 2005 or above version.

      Above details are required in order to answer your question.

      ~ Peace.

      • I am using SQL Server 2005 SP3. Please find below the script which i am executing for deleting a user under database.

        select dp2.name as role, dp1.name as owner
        from sys.database_principals as dp1 inner join sys.database_principals as dp2
        on dp1.principal_id = dp2.owning_principal_id
        where dp1.name = ‘username’

        The above script i got from some other community.
        Assume that i have three database like DB1, DB2 and DB3 and have users DB1user, DB2user and DB3user. I have successfully delete DB1user from DB1 database. But when i execute the above script for removing DB2user, it is executing but not showing roles for changing the owner.

        Regards
        M. MallikharjunaRao

        • @Mallikharjuna

          How can you delete a user executing a select statement. You CANNOT delete a user by executing a select statement.

          Please provide more information.

          ~ Peace.

  219. Gud Evening Sir,
    Sir I am working as a system administrator at patiala (Punjab). I have connecte my client PC to server name sscspta. i copied data from server to my client machine by loging using \\sscspta command in run. But from some days it gives the error \\sscspta is not accessible. you might not have the permissions……………. contact your system administrator……..
    Please help me.
    Thank’s in advance.

    • @Dalbir

      It is very abivious from the error message, that you lost access to shared folder on your remote server.

      Assign proper permissions on folder, going to folder properties and then go to security tab, and assign proper permissions to Windows ID with which you are copying data from server to Client.

      ~Peace.

  220. Dear Sir,

    My name is Mahender Singh, presently i am working in Getit Infoservices Pvt. Ltd as a Juniour Database Administrator due to some personal reason i couldn’t complete my dba training, i am working here for last two years. When i face some crucial problems doing database job your article always help me & i heartly thanks to you. i would like to take sql database training for you, but i can’t left my job because my family depends on me.
    can u help me?

    Regards
    Mahender Singh

  221. Hai,

    In one table I have values as

    Account Ledger
    Account Debit
    Cost of Balance

    now i am writing query as:

    SELECT list FROM tablename

    I want to add ‘ALL’ which is used in another application.

    So I am write as:

    SELECT list FROM tablename
    UNION
    SELECT ‘ALL’ AS list

    I get output as

    Account Ledger
    Account Debit
    ALL
    Cost of Balance

    But the required output for me must be as:

    ALL
    Account Ledger
    Account Debit
    Cost of Balance

    That is I want to display ALL at the top.

    Thank You.

    • Hi Karthik

      you can use UNION ALL as shown below

      SELECT ‘ALL’ as LIST
      UNION ALL
      SELECT LIST FROM
      (
      Select ‘Account Ledger’ AS LIST
      UNION
      SELECT ‘Account Debit’
      UNION
      SELECT ‘Cost of Balance’
      ) tablename

  222. Hi ,
    I’m trying to implement search for my website, i’m storing content in my db ,and i manage to find exactly the page and article but i don’t know
    how to return a search snippet from my content to display in search results (the snippet doesn’t have to be a constant length) and i just wondering how exactly i can manage to do it?
    any suggestions?
    Thanks.

  223. Sir i am working on a real estate website project.. I have created a database and tables but not ubderstanding how to create relationship b/w them. Plzz help. the tables are as below,
    USE [RealEstate]
    GO
    /****** Object: Table [dbo].[Buyers] Script Date: 07/18/2010 19:58:11 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Buyers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PropertyType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Location] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Area] [float] NULL,
    [Budget] [money] NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    ==========================================================
    USE [RealEstate]
    GO
    /****** Object: Table [dbo].[Registeration] Script Date: 07/18/2010 19:58:48 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Registeration](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [UserName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EmailID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PassQ] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PassAns] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [PK_Registeration] PRIMARY KEY CLUSTERED
    (
    [UserName] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[Registeration] WITH CHECK ADD CONSTRAINT [FK_Registeration_Registeration] FOREIGN

    KEY([UserName])
    REFERENCES [dbo].[Registeration] ([UserName])
    GO
    ALTER TABLE [dbo].[Registeration] CHECK CONSTRAINT [FK_Registeration_Registeration]
    ==========================================================================================
    USE [RealEstate]
    GO
    /****** Object: Table [dbo].[Sellers] Script Date: 07/18/2010 19:59:04 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Sellers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ListingDays] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [State] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Location] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Area] [float] NULL,
    [TotalPrice] [money] NULL,
    [Negotation] [varbinary](50) NULL,
    [Description] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PropertType] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Photo] [image] NULL,
    [ListedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [PK_Sellers] PRIMARY KEY CLUSTERED
    (
    [PropertType] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

  224. Hello sir,
    we are developing a software using vs2005 + sql2005.
    And we are facing a problem.
    We want to develop this software in gujarati language.
    So we stored the data in gujarati but when we fire a query from front end in gujarati to sql the database doesnot produce any output .
    If we use access the same thing works fine but with sql2005 it does not so please sir help us out.
    Thank you for your support and help.

    Regards – Sunil kapadia

  225. I want to write a trigger that will create a unique document ID upon initial “SAVE” of new record, but am drawing a blank as to how to do it. . .

    I want the ID to be in the format of YYYY-###

    YYYY = ‘current year’
    ‘-’
    ### = (this should be a 3 digit sequential # that starts at 001 on Jan 1st of each year — so that the first new record saved on Jan 1, 2011 = 2011-001

    but the subsequent documents would be numbered in order saved (i.e. 2011-002, 2011-003, 2011-004 . . . 2011-100 etc)

    And same for Jan 1, 2012 – it’s unique ID would be 2012-001 and so on.

    Can you give me some direction?

      • As I understand it, this would give me the format I want but
        . . . wouldn’t it give me the same # for all records saved on the same day each year?

        I need a trigger that will “look-up” the # of the last record saved for the current year and increment by one (1) for the new record; which in combination with the ‘YYYY-’ will become the unique reference ID. This will allow me to prioritize and keep a track of the number of records submitted by year.

        • Have an identity column and use computed column

          declare @t table(id int identity(1,1), document_id as cast(year(getdate()) as char(4))+right(’00000′+cast(id as varchar(10)),5),othercols….)

  226. HI ..
    TOPIC:SQLAgent job behaviour

    I deleted sql agent jobs couple of days back and i am still getting the failure alerts for those jobs stating not able to retive the step for the job.
    I checked all the system tables related to the jobs and found nothing related to those jobs.
    What could be tha cause of this .Does it have anythin to take care about the temporary files.If yes which temp files do i need to delete.

    Thanks you .

  227. I’m trying to write a query that allows me to calculated the number of days elapsed between admissions.

    Table Example:

    PatientAcct|AdmissionDate|DischargeDate|Facility
    1234567|1/1/2010|1/5/2010|WMC
    1234567|1/6/2010|1/10/2010|WMC
    2345678|2/1/2010|2/2/2010|WMC
    3456789|2/3/2010|2/5/2010|WMC
    3456789|2/7/2010|2/10/2010|WMC
    3456789|2/11/2010|3/1/2010|WMC

    Current Query: (it calcuates the difference between the dates without checking if the disharge date occurred before the admission date)~(not what I want)
    SELECT PatientAcct, AdmissionDate, DischargeDate, Facility,
    DATEDIFF(day, DischargeDate,
    (SELECT MAX(AdmissionDate) AS AdmissionDate
    FROM dbo.Test_Readmission AS B
    WHERE (A.PatientAcct = PatientAcct) AND
    (A.AdmissionDate > DischargeDate))) AS ElapsedDay
    FROM dbo.Test_Readmission AS A

    (What I would really want is a query which matches the dates then ranks the discharge dates such that when the discharge date occurs before the admission date it does a datedifference to calculate the interval between the dates)

    Anyone know what the code is to get it to calculate the date from last
    discharge date to current admission date? I’m creating a SQL view in SQL
    2005. Thanks for help!

  228. Dear sir,
    i am a regular user of your blogs…..i am facing a big problem……i am working on SQl reporting services…..I have a table Contact and another one Payment…in contact table i have all details about a user and in Payment i have details about the payment user has given….htere is a column named dated in Payment table which states the date on which the payment has been given……

    Now the situation is my client requires a report which shows details of all those users who have made a single payment for each year for last five years…..means only those users need to be shown on the report who have made only 1 payment for each year starting from July 2005 till July 2010…..The year start from 1 July till 30 June next year……What i am doing is am using five queries for five separate years and then making an intersection for all queries…..so the result is all those users who have given only one payment for each year for five years……But the issue am facing is the query is taking 1 minute 38 seconds to execute…Is there some better way to do it

    Thanks & regards
    Sumit Thapar

    • @Sumit.

      Would it be possible to provide a sample data with your script, how you are doing it right now. It would be helpful to debug.

      The sooner you provide, sooner you will get solution.

      How many no. of records are we talking about here. If not too many, then I am sure, there will definitely be a way to improve performance of your query.

      ~ Peace.

      • hi imran,
        Thanks for the reply…i ll give u a scenario…..suppose i have entries for each user date wise…..like user 1 made a payment on 27 december 2005… now i take the year from 1 july 2005 to 30 june 2006…now user 1 has one entry for this year…there might be many users like this one who have just contributed only one payment for an year…. i have to fetch the records for all these users…..what am currently doing is something like following:

        SELECT * FROM (
        SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
        cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
        cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
        cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
        FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
        WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2009-07-01′,101)
        and CONVERT(DATETIME,’2010-06-30′,101)
        and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
        GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
        cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
        WHERE tbl.cnt=1

        intersect

        SELECT * FROM (
        SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
        cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
        cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
        cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
        FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
        WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2008-07-01′,101)
        and CONVERT(DATETIME,’2009-06-30′,101)
        and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
        GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
        cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
        WHERE tbl.cnt=1

        intersect

        SELECT * FROM (
        SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
        cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
        cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
        cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
        FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
        WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2007-07-01′,101)
        and CONVERT(DATETIME,’2008-06-30′,101)
        and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
        GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
        cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
        WHERE tbl.cnt=1

        intersect

        SELECT * FROM (
        SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
        cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
        cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
        cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
        FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
        WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2006-07-01′,101)
        and CONVERT(DATETIME,’2007-06-30′,101)
        and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
        GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
        cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
        WHERE tbl.cnt=1

        intersect

        SELECT * FROM (
        SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
        cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
        cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
        cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
        FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
        WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2005-07-01′,101)
        and CONVERT(DATETIME,’2006-06-30′,101)
        and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
        GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
        cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
        WHERE tbl.cnt=1

        as you can see each query gives me the no of users who gave only one payment for that year…after that i make an intersect to find all the common records for these 5 years to get all the records…..Please check and give me some better solution..

        thanks & regards
        Sumit Thapar

  229. Hi Pinal,

    I want to upload a file from a PC to FTP using SQL Server 2008. I used the below link’s guidance

    [link removed.]

    and I was able to copy the source file to the ftp site. But, the copied or destination file is empty. Can you please let me know where I am going wrong?

    Thanks a lot in advance.(I am not well versed in SQL but my team SQL DBA has met with an accident that I am responsible to complete this task in a day so please help me).

  230. Hi,
    Is there’s any SQL statements that will help us return a NULL, if a match is not found and if a match is found I want that value to be displayed.

    ex
    CREATE TABLE table01 (field01 varchar(15), field02 int)
    INSERT INTO table01(field01, field02) VALUES (‘One’,1)

    –This is a satisfying selection
    select field01 from table01 where field02 = 1
    –so the output should be “One”

    –This is a non satisfying selection
    select field01 from table01 where field02 = 2
    –so the output should be “NULL”

    Thanks in advance

  231. sir

    i wanna know about the performance of

    select *

    and

    select col1,col2,….

    which is better and why

    what the step perform by them ?

  232. hi imran,
    Thanks for the reply…i ll give u a scenario…..suppose i have entries for each user date wise…..like user 1 made a payment on 27 december 2005… now i take the year from 1 july 2005 to 30 june 2006…now user 1 has one entry for this year…there might be many users like this one who have just contributed only one payment for an year…. i have to fetch the records for all these users…..what am currently doing is something like following:

    SELECT * FROM (
    SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
    cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
    cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
    cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
    FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
    WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2009-07-01′,101)
    and CONVERT(DATETIME,’2010-06-30′,101)
    and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
    GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
    cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
    WHERE tbl.cnt=1

    intersect

    SELECT * FROM (
    SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
    cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
    cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
    cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
    FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
    WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2008-07-01′,101)
    and CONVERT(DATETIME,’2009-06-30′,101)
    and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
    GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
    cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
    WHERE tbl.cnt=1

    intersect

    SELECT * FROM (
    SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
    cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
    cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
    cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
    FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
    WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2007-07-01′,101)
    and CONVERT(DATETIME,’2008-06-30′,101)
    and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
    GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
    cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
    WHERE tbl.cnt=1

    intersect

    SELECT * FROM (
    SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
    cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
    cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
    cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
    FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
    WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2006-07-01′,101)
    and CONVERT(DATETIME,’2007-06-30′,101)
    and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
    GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
    cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
    WHERE tbl.cnt=1

    intersect

    SELECT * FROM (
    SELECT cb.ContactId,COUNT(*) AS cnt,cb.Va_Referencenumber ‘Reference Number’,cb.Va_Title ‘Title’,
    cb.FirstName ‘First Name’,cb.LastName ‘LastName’ ,cb.Address1_Line1 ‘Street 1′,
    cb.Address1_Line2 ‘Street 2′,cb.Address1_City ‘Suburb’,
    cb.Va_State ‘State’,cb.Address1_PostalCode ‘Postal Code’
    FROM Contact cb inner join Va_payment p ON cb.ContactId=p.va_contactid
    WHERE p.Va_Payment_Date between CONVERT(DATETIME,’2005-07-01′,101)
    and CONVERT(DATETIME,’2006-06-30′,101)
    and cb.Va_Deceased=0 and cb.Va_Mail_Status=1
    GROUP BY cb.ContactId,cb.Va_Referencenumber,cb.Va_Title,cb.FirstName,cb.LastName,cb.Address1_Line1,
    cb.Address1_Line2,cb.Address1_City,cb.Va_State,cb.Address1_PostalCode) tbl
    WHERE tbl.cnt=1

    as you can see each query gives me the no of users who gave only one payment for that year…after that i make an intersect to find all the common records for these 5 years to get all the records…..Please check and give me some better solution..

    thanks & regards
    Sumit Thapar

  233. Hi,

    I want to delete the Project Name from the Explorer. if i built the any report buy the visual studio then the same reflect on explorer if i want to delete the some reports from the explorer than how can i do the same.

    Please suggest me the same ASAP. i am still waiting for your reply..

    Warm Regards,

    Krishna Chaudhary

  234. Hi,

    I want to delete the Project Name from the Explorer. if i built the any report by the SSRS visual studio then the same reflect on explorer if i want to delete the some reports from the explorer than how can i do the same.

    Please suggest me the same ASAP. i am still waiting for your reply..

    Warm Regards,

    Krishna Chaudhary

    • hi Krishna,
      Intersect gives you the common records from both tables on left and right…..

      for eg..

      select ContactId from tblContacts
      Intersect
      select ContactId from tblPayments

      will give you ContactId ‘s which are both in tblContacts and tblPayments

      there are two conditions using Intersect:

      1)The number and the order of the columns must be the same in all queries
      2)The data types must be compatible

      Code Well….

      Thanks
      Sumit Thapar

  235. Hi,

    I want to use the pivot function in sql M Pasting raw data and
    report which i need from the raw data

    raw Data

    AGENT_ID DISPO DISPO_COUNT
    5464 – Sukhpal, Sukhpal CBL 20
    5464- Sukhpal, Sukhpal CBL1 4
    654158 – kiran, kiran CBL 24
    654158 – kiran, kiran CBL1 4
    54464 – singh, Ovend CBL 9
    54464 – singh, Ovend CBL1 3
    89554 – Chander, Naresh CBL 4

    Need below Report From Above Raw Data

    AGENT_ID CBL CBL1 Grand_Total
    5464- Sukhpal, Sukhpal 20 4 24
    654158 – kiran, kiran 24 4 28
    54464 – singh, Ovend 9 3 12
    89554 – Chander, Naresh 4 4

    Please write the query use with pivot for the same

    it’s Very urgent..

    Warm Regards,

    Krishna Chaudhary

  236. SELECT Screenname

    FROM inthemo_fTS.dbo.table
    inner join
    FREETEXTTABLE(INTHEMO_FTS.dbo.Table,Screenname),’bars’) as ftt on ftt.[Key]=Table.rowno

    For this query not getting results for Bar only getting to bars .

    I have two database in one data base its working fine i am getting for both bar and bars treating as same

    But in another database its looking like separate.

    Can u give us quick reply pls i was stuck in production system …….

  237. Hi ,

    How can i sum the column’s value if some columns are null in Sql 2008

    as

    columnA columnB columnC columnD
    5 null 6 null
    null 7 null 8
    8 null 5 7

    how can i sum all columns if i have 50 lak records and any column can be null

    I need

    Select columnA+ColumnB+ColumnC+ColumnD from test_table

    • if i am writting

      select Emp_name, (ifnull(columnA,0)+Ifnull(columnB,0)+ifnull(columnC,0)+ifnull(columnD,0)) as SumOfRecords from Test_table

      the same query not executing.

      Please write the right query for the same requiremant fro sql 2008.

  238. I require a function such that I will be passing 2 dates i.e. StartDateTime and EndDateTime.

    Office working hours are from 9:30 AM to 1:30 PM and 2:30 PM to 6:30PM
    1:30PM to 2:30PM is lunch time.

    So when I pass the StartDate and EndDate the function should return the time taken in hrs
    excluding the lunch time i.e. 1:30 to 2:30, excluding holidays and excluding sundays.

    For Example: if I give,
    1) StartDateTime = 2010-07-22 13:30:00
    EndDateTime = 2010-07-22 14:30:00

    Then function should return 0 (since it is lunch time).

    2) StartDateTime = 2010-07-24 18:25:00
    EndDateTime = 2010-07-26 09:35:00

    Then the output should be 10 minutes since 25 is holiday and after 6:30 it is not counted.

  239. Hi dear hope you ill be fine please help me to solve my problem.

    Dear i want to create a distributed database in SQL server 2005 is such a way that i have five departments and each department has its own type of Data have same fields. I want to add .ndf files to my database for each department and thus each department can directly insert there data to their particular file have each file on different computers over then network.

    is there any possibility to do so .

    Please help and send me some step by step configuration or settings

    Regards,

    Naveed Naeem

  240. hi Pinal,

    I’m using STUFF() function combined with FOR XML PATH(), to obtain a comma-separated list of value. Here’s an example:

    SELECT STUFF(
    (SELECT ‘,’ + MYTABLE.field
    FROM MYTABLE
    ORDER BY MYTABLE.field
    FOR XML PATH(”)),1,1,”) AS LIST

    that return value1,value2,value3……

    OK? well, now I want to create a generic function in which i can specify a table and a field to apply that function against, but to do so I guess I need to build a sql-string, put it in a varchar variable, and execute it somehow.

    Which is the best way?

    thanks, Balanza

    • Try this code

      declare @table varchar(100), @column varchar(100), @sql varchar(max)
      select @table=’table name’, @column=’column name’

      set @sql=’SELECT STUFF(
      (SELECT ‘,” + @column+’
      FROM ‘+@table+’
      ORDER BY ‘+@column+’
      FOR XML PATH(””)),1,1,””) AS LIST’
      exec(@sql)

    • Hi Pinal,

      I am very new to SQL SERVER. I faced 1 question in interview, ie suppose in server has 10 databases and sysadmin wants to give permission to sql login for 1 specific database, but remaining databases should not appear in that server when that particular sql login connect to the same instance..

      Please help me how to give permission.

      Thanks
      Sri Chitti Durga Rao Kona

    • 1. Take the log back with truncate_only
      2. Shrink the tempdb
      3. better to maintain tempdb on different drive

  241. Hi Pinal,

    i have one issue regarding create a dynamic database using stored procedure.

    i paste my stored procedure and problem below:

    i had created one script file “Xyz.sql’ and it’s path is “c:\scripts\xyz.sql”. this file have a create tables and create stored procedures implementation

    i had created one stored procedure “ConfigureDatabase”

    ////////////////////////////////////////////////

    CREATE PROCEDURE [dbo].[ConfigureDatabase](
    @DatabaseName VARCHAR(100)
    )
    AS
    BEGIN
    DECLARE @SqlCreate VARCHAR(MAX)

    IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = @DatabaseName)
    BEGIN
    SET @SqlCreate = ‘CREATE DATABASE ‘+ @DatabaseName +’ ON PRIMARY
    ( NAME = N”’+ @DatabaseName +”’, FILENAME = N”E:\TEST DATA\’+ @DatabaseName +’.mdf” , SIZE = 9675456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
    LOG ON
    ( NAME = N”’+ @DatabaseName +’_log”, FILENAME = N”E:\TEST DATA\’+ @DatabaseName +’_log.ldf” , SIZE = 52416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)’

    EXEC(@SqlCreate)
    END

    EXEC (‘USE ‘ + @DatabaseName)

    EXEC master.dbo.xp_cmdshell ‘osql -E -S PCT26 -i “c:\scripts\xyz.sql”‘

    END
    ///////////////////////////////////////////////////////////////

    when i execute [ConfigureDatabase] stored procedure
    like EXEC [ConfigureDatabase] ‘test’
    at that time new ‘test’ database is create and i try to set current database using Inline USE syntax, becuase ‘Use’ keyword doesn’t support in stored procedure.
    but all tables and stored procedure define in xyz.sql file are created in ‘master’ database instead of ‘test’ database

    i want to create all stored procedure and table in ‘test’ database

    Please help me.

    Thanks,
    Sujal Ramani

    • @Sujal.

      OSQL accepts database as parameter, asking in which database you want to run the query.

      Take off, Use Database Dynamic SQL in your script and use below script.

      Declare @Sqlcmd varchar(1000)

      Set @Sqlcmd = ”’osql -E -d ‘+@DatabaseName+’ -S PCT26 -i “c:\scripts\xyz.sql””’
      print @Sqlcmd

      EXEC (‘master.dbo.xp_cmdshell’+@Sqlcmd)

      In above script we are passing database name as parameter, so this script is executed in that specific database.

      Let us know if this does not solves your problem.

      ~ Peace.

  242. Hi everyone,
    I am working on Sql Server Reporting Services 2008…i have a report that shows records more than 3 lakh…..now when i try to export the report , it throws an error as a worksheet in excel can only take not more than 65k records…i ve heard that if i use page breaks after specific rows a new worksheet will be created after that specified number of rows….Please anybody help me how to add page breaks after specific number of rows……am desperately waiting…

    Thanks
    Sumit Thapar

  243. I would like to ask one questing about SQL index. Is it any side effect on creating a lot of index on one table and how to improve the sever performance to handle over 10 millions of records?

    Thanks so much :)

  244. Hi Pinal,

    I’m working on a database that uses Table and Index Partition. I have made a fresh new copy of the original database schema in a development server: this time the new database will use partitions. My goal now is to restore (copy the contents of) the original database to this new database. The issue is that the original database did not use different partitions only PRIMARY partition. When I ran the restore utility in SQL Server Management Studio I get the error “The backup set holds a backup of a database other than the existing ABCD”. However, the two databases are identical. I am trying to use the BK file that I made of the original database to copy the contents of my original database to the new one database because I want to run queries and determine any performance improvement between using partitions and not using partitions. What’s the best way to get the data from the old database to the new one? This is a large database of 1 TB… I’m relatively new to DBA (actually not really a DBA, but doing some DBA work for sure these days)… Any suggestions?

  245. I do not know how to connect a user to a database to manually enter data. I have configured the server for remote connections. Is there a string to enter into the browser to connect the user to the database to allow manual data entry?

  246. Hi dear hope you ill be fine please help me to solve my problem.

    Dear i want to create a distributed database in SQL server 2005 is such a way that i have five departments and each department has its own type of Data have same fields. I want to add .ndf files to my database for each department and thus each department can directly insert there data to their particular file have each file on different computers over then network.

    is there any possibility to do so .

    Please help and send me some step by step configuration or settings

    Regards,

    Naveed Naeem

  247. Hi Pinal sir,
    i have a confusion regarding a query for nth highest salary which you wrote in your following blog:

    http://blog.sqlauthority.com/2007/04/27/sql-server-query-to-retrieve-the-nth-maximum-value/

    the query is

    SELECT *
    FROM Employee E1
    WHERE (N-1) = (
    SELECT COUNT(DISTINCT(E2.Salary))
    FROM Employee E2
    WHERE E2.Salary > E1.Salary)

    my confusion is how does the query match the (N-1) value

    if i try “SELECT * FROM TBLSALARY WHERE (2)=(2)” , it shows me all the records of the table

    Thanks
    Sumit Thapar

  248. Hi,

    I want to delete the Project Name from the Explorer. if i built the any report by the SSRS in visual studio then the same reflect on explorer if i want to delete the some reports from the explorer than how can i do the same.

    Please suggest me the same ASAP. i am still waiting for your reply..

    Warm Regards,

    Krishna Chaudhary

  249. Hi,

    I am using SQL server 2008 for my java application, and port is 1433, static port. when ever it goes for database connection, i think it is generating a session Id (49244,49266… like this) but my antivirus is TrendMicro and it is blocking my application not to connect with the server machine. So is there any possibility to fix the sessionId, so i can give that particular port or within a range in TrendMicro. Please give a solution for my problem.

    with regards,
    bhanu

  250. Pinal

    I have a job that sends email alerts, up to recently the text email has been sufficient. However the client now wants rich html with an image. I have tried a few methods but so far have drawn a blank. I’m using sql2005.

    DECLARE @HTML VARCHAR(MAX);

    SET @HTML = N” +
    N’ ‘ +
    N”;

    EXEC msdb.dbo.sp_send_dbmail @profile_name = N’TestProfile’,
    @recipients = N’martin.mclarnon@firemelon.com’,
    @subject = N’Subject’,
    @body = @HTML,
    @body_format = N’HTML’,
    @file_attachments = UNC Path to image on SQL Server;

    This method added the image as an attachment but does not embed it. Any help would be appreciated.

    Regards
    Martin McLarnon

  251. Hi,

    I want to separate by space a firstname middlename lastname from a single column to separate columns in sql 2008

    as

    Question —-

    CUstName
    krishna Kumar Chaudhary

    Need result ——–

    Firstname middlename lastname
    krishna kumar chaudhary

    its very urgent…..

    Warm Regards,

    Krishna Chaudhary

    • If your name always has three parts

      select parsename(name,3),parsename(name,2),parsename(name,1) from
      (
      select replace(name,’ ‘,’.’) as name from table
      ) as t

      • Thanks Mr. Madhivanan

        Please tell me one think more

        if i don’t know how many space in a name
        as

        name

        krishna kumar chaudhary aaaa bbbb cccc
        vineet kumar chaudhary aaaa bbbb cccc ddddd eeee
        vikas kumar chaudhary aaaa bbbb ccc dddd eeee pp
        sohan kaur

        should be create column as per maximum space count

  252. One question related to your post …I have question table and answer table with quesid as foriegn key in answer table. there is another similar table structure and I want to insert from one table to another table i am using following query for it…

    –DECLARE @QuestionID AS BIGINT
    –INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionTextActive)
    –SELECT AreaID,UserID,QuestionType,QuestionText,Active
    –FROM ContributedQuestions
    –WHERE CQuestionID in (18,19,20)
    –SET @QuestionID = SCOPE_IDENTITY()
    –INSERT INTO QBAnswers ( Answer,QuestionID,IsCorrect)
    –SELECT Answer,@QuestionID,IsCorrect FROM ContributedAnswers
    –WHERE CQuestionID in (18,19,20)

    Now to insert mulitple row at one time I used query like this
    DECLARE @lclMySQL as varchar(MAX)
    SET @lclMySQL = ‘INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionText,Active)’
    SET @lclMySQL = @lclMySQL +’SELECT AreaID,UserID,QuestionType,QuestionText,Active FROM ContributedQuestions’
    SET @lclMySQL = @lclMySQL + ‘SET IsActive =0 WHERE CQuestionID IN (‘+@QuestionID+’)

    ‘ where I am using @question id as string of quesiton id but in this case how can I insert question id as foriegn key to Answer table???

    Is there some way in which i can use IN keyword in where condition to insert multiple rows and also insert forign key value in to answer table?????

  253. Hi Pinal

    I do have one question regarding to Index.
    Can you please let me know how do i know the creation and update date of perticular index?

    I have tried lots of blog, but didn’t get the accurate answer.

    I hope to hear soon from you.

    Thanks a lot in advance

  254. Hi Pinalkumar,

    Currently I got an issue to return results of stored procedure cross databases:

    Database A – stored procedure B, account ”UserA” has db_datareader and db_datawriter permisions.

    Database C – table D, role “execRole” has “grant “ and “with Grant” permissions. (I think account“UserA’ belongs to role “execRole”, otherwise I cannot login and create proceudre, etc)

    Because store procedure B returns lot of records, we plan to save them into table D of database C. Under db_owner’s account, the stored procedure can be executed successfully.
    But under an account “UserA”, the stored procedure got an error:

    Msg 1088, Level 16, State 7, Procedure procGetWorkHours, Line 38
    Cannot find the object “B” because it does not exist or you do not have permissions.

    Thanks in advance!

    Helen

    • Hi Pinal,

      Forgot to mention that in the stored procedure, there is logic like this:

      – @LockName = ‘DefaultLock’

      SET NOCOUNT ON;
      BEGIN TRAN @LockName

      TRUNCATE TABLE C.dbo.D
      INSERT C.dbo.D
      select …….
      from …..

      COMMIT TRAN @LockName;

      Regards,

      Helen

  255. if your issue yet not resolved then :-
    as per the error
    Cannot find the object “B” because it does not exist or you do not have permissions.

    if you can provide the code, it can be digged further

  256. Sir
    i take database backup in sql server 2005 in windows 7..
    i need to restore the backup file into sql server 2005 in windows xp sp2.

    plz replay

  257. Hi friends
    Today I was working on a query, and I got stuck at a place.

    We have this payroll system, where every month we get the “straight time” for the entire month, but the “over time” is paid a part in this month and the other part in the next month.

    I will take the month of Jul as an example.
    My Jul salary will have the “straight time” for Jul 01 to Jul 31. But my overtime is from Jun 21 to Jul 18th.

    Now, I need a report where I need to tell the weekly pay that I will get. The calculation of the month for “Straight time is direct, but for Week ending 25th Jul, I do not get an overtime till next month, and The next month for the same period i get only an overtime for 25th jul.

    The starting and ending week is saved in a table pay_cutoff
    (payMonth datetime, startweek datetime, endweek datetime)

    2010-06-01, 2010-05-23, 2010-06-20
    2010-07-01, 2010-06-27, 2010-07-18

    The weekly transactions are stored pay_weeklytran
    (emp_id int, week datetime, ot_hours, ot_rate)

    now my requirement is that I assign the correct “paymonth” for each record in “pay_weeklytran”

    the query I used finally was,

    select a.emp_id, (select b.paymonth from pay_cutoff as b where a.week between b.startweek and b.endweek) as paymonth, a.ot_hours * a.ot_rate as ot_earned
    from pay_weeklytran

    Please tell me if there is a better way to do it, I feel that this query is a bit messy.

    thanks in advance

  258. restoration not depends on OS. if you able to install the sql server on OS then you will be able to store it.

    why do you want PK on views…… views are just virtual table so refer the columns from table which have PK and use them in your where condition to fetch data fast in view

  259. Dear Sir,

    I have one sql 2000 DATABASE by name ‘ACCOUNTS’ which is working smoothly. But at the time of restoring the backup the the database on sql 2005 it shows error saying the “possible schema corruption run ‘DBCC CHECKCATALOG” for specific erros. During restoration process the it shows restored upto 100%, but at the end the end of the procell the above error is getting displayed.

    Secondly, i ran DBCC CHECKCATALOG it gives out the following results which i am not able sort out. Please help……

    Server: Msg 2513, Level 16, State 5, Line 1
    Table error: Object ID 1233190868 (object ‘DF__D0520081__Recove__4980FFD4′) does not match between ‘SYSOBJECTS’ and ‘SYSCOMMENTS’.
    Server: Msg 2513, Level 16, State 1, Line 1
    Table error: Object ID 1235286885 (object ’1235286885′) does not match between ‘SYSCOMMENTS’ and ‘SYSOBJECTS’.
    DBCC results for ‘current database’.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Regards,
    Akash R. Kambli,
    System Administrator,
    Panaji Head Post Offce,
    Goa 403001

  260. Here is my scenario.I have 10 different tables and I want to create a Search for all of them.Like In drop down list the column name would be displayed and if I will give a value of that particular column then It will search a particular row for that table.How can i Create storedprocedure for that?
    Thanks.

  261. Hi,

    I have pass last weekend 70-432 (sql 2008 ) exam. Prometric gave me paper with total score and it says if this is your first microsoft exam you will recd. email from microsoft regarding your MCP id and information how to recd. welcome package. Since this is my first exam with microsoft what do i need to do next?

    can some one please suggest.

    Thank you
    Tejal

  262. Hi,

    I have a question for you. Do you know if there is a way to retrieve a single file or a filegroup from a database backup without attaching it to database. I would like to obtain those files without creating a new database or restore them to the original database.

    Thanks.

  263. I have two different database server and one one databases on each server.How do I write trigger that fired from one database server and update to antoher database server.Thanks.

  264. sir , i want to know about the following query please explain me .
    ALTER TABLE dbo.CompCol ADD
    FullName AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)
    this query is used by you in the computed and persisted column blog
    thankyou

  265. sir , i want to know about the following query please explain me .
    ALTER TABLE dbo.CompCol ADD
    FullName AS POWER(LEN(LEFT((FirstName+CAST(ID AS VARCHAR(100))),3)), 12)
    this query is used by you in the computed and persisted column blog
    thankyou

  266. Hello,

    I had modified an SP on my database and then rolled back it after my testing. Now, I do not have any copy of the changes I made. Is it possible to retrive those changes from the database \ logs etc, and get the changed SP?

    Thanks
    Gopal

  267. Hello sir,
    I m first time reading ur sites its g8 but i m new in sql so pls describe backup and restore process in 2005….

  268. Hi,
    i have mstsc.exe version 6.1, installed in my system. till few days back copy/paste function was working perfectly. Now, even though all the settings in “Local resources” are activated, but still i’m unable to copy to/from remote desktop to my local. I also tried killing the process rdpclip.exe and again starting the same, but that also failed. Please suggest me some other options.

  269. The database cannot be opened because it is version 655. This server supports version 611 and earlier. A downgrade path is not supported.
    Could not open new database. CREATE DATABASE is aborted.
    An attempt to attach an auto-named database for file failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

  270. how to maintain transaction in two database in sqlserver?

    Ok here is the scenerio:-
    you have to database in sqlserver named DataA and DataB

    now if you have inserted a record in DataA (Accounts table) it should also insert a record in DataB (Payments table).

    But if anything goes wrong in one of the Database then you should rollback both.

    How can you achieve that in sqlserver.

    You have to maintain transaction across both the tables….

    -Anil

  271. is the rdp you doing on server exist in same environment from where you copying.
    Sometime this problem comes even though the environment is same. Try cleaning the temp file in serer by doing start-run-%temp% and delete all temp files there.

    it should resolve the issue. if not then try rebooting if possible and left no alternate

  272. Hi there,

    I just wanted to thank you for your time and effort on this site. I often get directed to your blog by google when I’m searching for stuff, and your information and advice is ALWAYS spot on.

    Thanks SOOO much for all your hard work – you make life easier for the rest of us.

    Cheers,

    Scott.

  273. Hi Guys,

    I am new to SQL and my requirement is simple:

    “If X-Date + 90 days < Current Date", then do something.

    Now I am getting the 'X-Date' value from a xml blob using a X-query. Its in the format – MM/DD/YYYY

    How do I write the code for this

  274. how to maintain transaction in two database in sqlserver?

    Ok here is the scenerio:-
    you have two database in sqlserver named DataA and DataB

    now if you have inserted a record in DataA (Accounts table) it should also insert a record in DataB (Payments table).

    But if anything goes wrong in one of the Database then you should rollback both.

    How can you achieve that in sqlserver.

    You have to maintain transaction across both the tables….

    -Anil

  275. Hi Pinal,

    Can U help me out know about magic tables. and how they work? I mean how they execute. there Architecture .. and can we manipulate them by our code?

    Thanks
    Sarika

  276. Hi Pinal,

    In one of my project, there needs to run a cleanup job which will deletes the records in a set of selected tables according to a retention period defined in a configuration table. During the deletion, the log file is getting loaded while we execute the job as the delete command is adding the delete log into the .ldf file. Please advice an alternative to remove the logging of the delete command in the ldf file.

    Thanks in advance.

    Pradeep Kumar K R

  277. Dear Pinal,

    Would you please recommend/suggest any Link, PDF or Book for a Database Architect related? From Basic to Advanced any level of book is also ok.

    Shaiju CK

  278. Hi,

    I am a commerce graduate but i have a gr8 interest to do the course of DBA or SQL so can you plz give me the advice which course is better for me for my future.

    Thanks,
    Gagan

  279. Dear Pinal,

    I am Developer working in small compnay.I have one Query i can i display Month name and Number of days in a month .i need like this

    Jan-31
    Feb-28…..

    ….
    …..
    so on

    Can you help me

    thanks
    Bharath

  280. Hi,

    I have an query in sql update:

    I am having a table which will maintain last one year data ( monthwise)in a single row. The table has 13 columns
    1. Key then 2 -13 monthly names ( Jan…Dec)

    How do I update particular month data using single query ( in Jan, we need to update Jan data, in Feb, we need to update feb data only …..) dynamically?

    Regards
    Ak.

  281. Can anyone suggest the best way to achieve this…

    I have a string, I need to extract words, its line positions and its word positions. Also I need to eliminate ignore words from it. See example below…

    String: “SQLAuthority.com is trademark of Pinal Dave”

    I need output like
    Word————————WordPos————–LinePos
    SQLAuthority.com 0 0
    trademark 1 21
    Pinal 2 34
    Dave 3 40

    When calculation word position we should no consider ignore words (is, of….)…

    When calculating Line position we should consider everything calculate position of the first letter of the word

  282. Hi,
    I am a Hugh fan!

    Is it possible to copy a directory from one directory to the other using xp_cmdshell?

    I know copying files works great but how do copy entire directories?

    I thank you in advance!

  283. Hi Pinal

    I have written a dll for executing SQL queries which come from my business logic class. I have put unique key constrains on my table.
    I want know how to capture the error id of the Unique Key error.

  284. Hello pinal,

    don’t know but think, I know you from previous work.

    Have you worked / studied in nirma university / L&T ?

    kaumil

  285. Hello Pinal,

    I am very impressed with your site and wonderful helpfulness.

    I wonder if you can help me?

    I am getting 2 errors from my SQL server:

    SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed.

    The source is MSSQLSERVER and category as Logon with event id 17806

    AND….

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

    These errors are stopping access to the application using the SQL database held on this SQL server.

    I have searched everywhere and cannot find a solution.

    Please, please can you help?

  286. Halo Pinal,

    I always refer your blog for notes and doubts. Very informative and helpful. I am facing an issue with SSIS Package Configuration. I am confident that you would be definitely able to help me out.

    I have a Development Server with a Development Database. I have a Source_Table in which I have three columns (Server_Name, Database_Name and Table_Name). In future, we may move few tables to a new server due to space issues. The Table_Name Values will remain the same, but when moved to a new server and database, this table will be updated. So, The Server_Name and Database_Name values could probably be changed when few tables are moved.

    I have SSIS packages configured on this server. I am trying to configure these packages to make them dynamic to pick the values of Server_Name and Database_Name from this Source_Table.

    I can configure two variables, for Server and Database, and store the package configuration in XML file and manually change the values, whenever there is a change in the Server or Database values in the Source_Table. But, my team says, they don’t want to touch any of the packages or config files, they want these to be pulled from a table. So the package configuration through XML configuration is rules out in my case.

    I am using the package configuration through SQL Server. But, when I change values in the SSIS Configurations table, the package doesn’t pick values from table; instead, it pulls value from the variable declared in the package. The variable declared has the value of Server and Database stored in it.

    How can I configure my package in such a way that the package picks the Server_Name and Database_Name for a particular Table_Name values from the Source_Table?

  287. Hello pinal

    i got problem which i dont understand
    i alreaedy have sql express 2008. and my application work ok. after i upgrade to sql express r2 thru update then my application not work. the error is connection error and i check previous sql use .\sqlexpress to connect to database
    how can i fix this
    hope can help me
    thank you

  288. Hello Pinal,

    i have 1 table & field like(questionsno,A,B,C,D) . From this table i want to get questionsno,[which field have values- field name)
    ex: 1- A,B(1,1,0,0)

    Is it possible to get result in above?

  289. Hello Pinal,
    I am working on Sql server 2008 almost from year and i used to read Your blogs. Basicaly My whole work depend on sql when ever i found difficulties in generating queries or in procedure i search in your site and most of the time i found correct answer .
    Pinal i want ask u about certification in Sql server 2008 .
    How can i get certified in sql please tell me.

  290. Hi Pinal,

    I’m a regular reader of your blogs and learned alot from this site. Please accept my sincere thanks for your wonderful posts all the time.

    I’ve a question, How do we create any user who will have SQL Error Logs view permission in SSMS.

    I tried using granting execute permission to xp_readerrorlog but didnt it work. Could you please help to understand if I’m missing anythning.

    Thanks again.

    Regards,
    Kanchan

  291. i have my database with the columns as follows:

    keyword, part1_d1, part1_d2 …….. part1_d25, part2_d26, ……part2_d34

    FYI: d1 through d34 are documents..

    how can I give a query to obtain columns with column_name like ‘%part1%’; as below

    keyword, part1_d1, part1_d2, …….. part1_d25

    I tried the query:

    select (Select COLUMN_NAME From INFORMATION_SCHEMA.COLUMNS where COLumn_NAME like ‘%part1%’) , keyword from sample

    But it dint work…

    Please let me know what to do?

  292. Hi Pinal,
    Is it possible to get the day like “Sunday”, “Monday”,”Tuesday”,”Wednesday”,”Thursday”,Friday” and “Saturday” by using Datetime column in the table ?

    My requirement is to get the count per day wise ,In the table i have count and datetime field. need to convert datetime field to “day” (Sunday etc…)

    can you send me is it possible using sql server 2000

    Regards
    velu

  293. Hi i have a search procedure which will search from a table of patients containing around 10 laks of records and and around 20 fields. i need to query the table with around 10 fields which may or may not be included in the search criterion based on the user selection. i have two options in front of us. writing a dynamic sql query is the first or the second one is which i have given below. Can you guide me in finalizing a best solution.

    DECLARE @Name as Varchar(100)

    DECLARE @Title as int

    DECLARE @dob as date

    DECLARE @PageIndex as int

    DECLARE @PageSize as int

    DECLARE @MAXRECORDTOSELECT AS INT

    set @Name = null

    set @Title = null

    set @PageIndex = 0

    set @PageSize = 20

    SET @MAXRECORDTOSELECT = @PageIndex * @PageSize + @PageSize

    IF ( @MAXRECORDTOSELECT = 0 )

    BEGIN

    SELECT @MAXRECORDTOSELECT = COUNT(*) FROM PAT_PATIENTMASTER_SVIEW

    END

    ;WITH CTE_PATIENTMASTER AS

    (

    SELECT TOP(@MAXRECORDTOSELECT)

    PM_ID_PK, PM_ExternalID, PM_MRN, FullName, PM_AKA, PM_DOB, PM_Sex_FK, PM_SSN, PM_Title_FK,

    PM_PreferredContactMethod, PM_Confidentiality_FK, PM_ExemptFromReport,

    PM_Active, PM_PatientType, PM_BloodGroup, PM_PreferredDisplyName, PMEx_Note, Age,

    ROW_NUMBER( ) OVER ( ORDER BY FullName DESC)

    AS ROWNUMBER FROM PAT_PATIENTMASTER_SVIEW

    WHERE PM_LastName Like case when @Name is null then PM_LastName else @Name end

    AND PM_Title_FK is null OR PM_Title_FK = CASE WHEN @Title IS NOT NULL then @Title else PM_Title_FK end

    )

    SELECT PM_ID_PK, PM_ExternalID, PM_MRN, FullName, PM_AKA, PM_DOB, PM_SSN, PM_Title_FK,

    PM_PreferredContactMethod, PM_Confidentiality_FK, PM_ExemptFromReport,

    PM_Active, PM_PatientType, PM_BloodGroup, PM_PreferredDisplyName, PMEx_Note, Age, SL_SexCode AS ____

    FROM CTE_PATIENTMASTER

    LEFT OUTER JOIN GEN_Sex_Lookup ON PM_Sex_FK = SL_ID_PK

    WHERE ROWNUMBER > (@PageIndex * @PageSize) AND ROWNUMBER <= ((@PageIndex + 1) * @PageSize) ORDER BY ROWNUMBER DESC

  294. I want to move records from online database to local server. Please suggest me how to do it.

    I’m taking backup but it remains in that server means unable to move from bak file to local server

    • You can try copying data using import / export wizard.

      Before you do import, disable all FK constraints on local DB so that you won’t get any constraint violation errors.

      Take care about IDENTITY columns….

  295. Hi Pinal,

    I am new at learning SQL server 2008.And i wish to master the DBA concepts completely

    I request you to please suggest some Good Books to learn the basic concepts speically administration stufff
    Thanks a lot in advance

    Hitesh

  296. Dear Pinal

    I have installed MS SQL Server 2008 R2 Express with Advanced Services in my machine. During installation i used default settings. The report sever database credentials are as thus;
    SQL Server Name: TLOX-PC
    Database Name: ReportServer Report Server Mode: Native Credential: Service Account Login: NT Authority\NetworkService Password: ********* When i try to run the report manager URL over the browser, i receive the ‘authentication required’ window where im prompted to enter Username and password. Since im using the service account and the authentication mode is set to windows, im assuming that i should straight away see the report manager on the browser. I did not set the above password and i dont know what these stars(“*”) represent. Is there a way i could change this password or at least see it? Thank you.

  297. Dear Pinal,

    This is Sajid Kamal Sr. Software engineer, we are working in Healthcare ERP.

    I regularly follow your blogs.

    This time we are facing very serious problem in SQL Server 2005.

    When user(s) saved a bill and get printed, some times data lost from transaction tables.

    All the saving is done through procedure under single transaction.

    Is it possible that after committing the data, data get removed from the table?

    I need your help.

    Thanks & Regards
    Sajid Kamal

    Sr. Software Engineer
    Akhil Systems Pvt. Ltd
    New Delhi

  298. Dear Pinal,

    I need ur help. my question is that how do make database for sent emails for every user using SMTP server, if we are using outlook exp?

  299. Hellp,

    I find this site extremely helpful, but I can’t seem to find anything on how to make a sqlagent job fail from a stored proc.

    Briefly, a sql agent job calls a stored proc. If a particular set of circumstances occurs the stored proc. emails me and I would like it to cause the job to fail.

    Any ideas would be helpful.

    Thanks.
    Susan

  300. Hi pinal,
    I badly need your help, I’ve a weird situation.
    I’ve a stuck query, normally it runs with in seconds, but on a bad day it stuck forever, everytime it’s stuck, it’s stuck for hours and hours, it’ll never completes.
    Activity monitor showing it as green, runnable, there’s no locking or blockings from other users either. Index usage is close to threshold.
    From server side, no disk activity, no I/O, which means nothing is happening. That’s the problem being said, here’s is the solution we are doing as of now..
    Kill that query, update the stats for all the tables involved in that query.. that’s it, when you run the same query, it’ll run in seconds. So, “update stats” is the key to unlock this stuck query situation, we are doing it everytime. It’s still a puzzle, we’ve no idea how this “update stats” is solving this. We do update stats everyday morning 5 am, as a scheduled job with default sample rate. Still, during the middle of the day, we’ve this stuck query.

  301. Hi Pinal,

    Here is my problem.
    I have a string for e.g: “12/09/2010 XYZxyz”.

    I want to check from a program if the string has alphabets from caps A to Z or small a to z. If yes I need to run something.

    How do I write a query for this

    Thanks,
    Bunty

    • Hi You can try PATINDEX..

      If PATINDEX(‘%[A-Za-z]%’,’12/09/2010 XYZxyz’) > 0
      print(‘found’)
      else
      print(‘not found’)

  302. Hi Pinal

    i need find certain transactions in the table two that have been writted more that 2 times.
    See my code below.

    DECLARE @my_GID UNIQUEIDENTIFIER

    DECLARE my_cursor CURSOR FOR
    SELECT GID FROM tbt_tableOne WHERE GID IN (SELECT tbl1GID FROM tbt_tableTwo);

    OPEN my_cursor;

    FETCH NEXT FROM my_cursor
    INTO @my_GID;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SELECT [ID],tbl1GID FROM tbt_tableTwo where tbl1GID = @my_GID
    –i basically want to identify all entries in table2 that have more than 2 of the same GID from table1
    –should i make use a nested cursor?

    FETCH NEXT FROM my_cursor
    INTO @my_GID;
    END
    CLOSE my_cursor;
    DEALLOCATE my_cursor;

  303. I want to start a blog but do not know how to start it. Can you please let me know the process and steps to do it. Your help will be much appreciated. Also I enjoy you blog and have used on several occasions to find solutions.

    Thank you

    • Hi
      I have to apologise in advance for my lack of knowledge about this subject.
      My problem is I have been running smse 2005 for about 3 years with no problems until last Sunday when the error message below appeared. I have read through all the previous blogs and tried all the suggestions with no luck.
      I am running the system on a local machine to run queries on a backup of the database. using the following
      Server Type Database Engine
      Server name computername/sqlexpress
      Authentication Windows Authentication
      user name Computer name/my user name

      SQL Server Browser is running
      Built in account is Local System
      Host is computername
      Shared memory is enabled

      The only difference I can see from previous quesions id that I have Microsoft sql server , error:2 at the end

      A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared memory Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, error:2)

  304. when i am try to install msde 2005 express on windows xp sp2 there is following error showing that

    SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
    Refer to server error logs and setup logs for more information. For details on how to view setup logs, see “How to View Setup Log Files” in SQL Server Books Online.

    can u help me please

    • Hi Naresh,

      You can find unmatched records by Full Outer Join by checking NULL conditions as follows:

      DECLARE @TableA TABLE(ID INT, Value VARCHAR(1))
      DECLARE @TableB TABLE(ID INT, Value VARCHAR(1))

      INSERT INTO @TableA VALUES(1, ‘A’)
      INSERT INTO @TableA VALUES(2, ‘B’)
      INSERT INTO @TableA VALUES(4, ‘D’)
      INSERT INTO @TableA VALUES(5, ‘E’)
      INSERT INTO @TableA VALUES(6, ‘F’)

      INSERT INTO @TableB VALUES(1, ‘A’)
      INSERT INTO @TableB VALUES(2, ‘B’)
      INSERT INTO @TableB VALUES(3, ‘C’)
      INSERT INTO @TableB VALUES(4, ‘D’)

      SELECT a.ID,
      a.Value,
      b.ID,
      b.Value
      FROM @TableA A
      FULL OUTER JOIN @TableB B ON a.ID = b.ID
      WHERE (a.ID IS NULL OR b.ID IS NULL)

      Thanks,
      Tejas
      SQLYoga.com

  305. Hi Pinal,
    When I am trying to access Report Manager (http://localhost/Reports), I am getting Access Denied error as follows:

    —————————————————————————
    Server Error in ‘/Reports’ Application.

    Access is denied.

    Description: An error occurred while accessing the resources required to serve this request. You might not have permission to view the requested resources.

    Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control Lists). Ask the Web server’s administrator to give you access to ‘C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\home.aspx’.

    Version Information: Microsoft .NET Framework Version:; ASP.NET Version:2.0.50727.3614
    —————————————————————————

    The Report Server Service Account is set to “Use built-in account: Network Service” but it still asking for the user credentials. I’ve searched in lot of websites but couldn’t find an answer to fix the issue. Your help would be much appreciated.

    Regards,
    Ranj

    • Hi Ranj,

      Which Operating system you are using?
      If you are using Vista, then run “IExplore” with Administrator rights.

      Right click in “Iexplore” and select “Run as Admisnistrator” and try that.

      Let me know if it helps you.

      Thanks,
      Tejas
      SQLYoga.com

  306. Hi!

    I need your regarding SQL Job.

    I have created one Console application with .NET to create one .dat file, then I need to create this file daily. so for that I have created one Job for this But it can not run successfully and give me error : the System can not find the path specified.

    Even if i execute .exe manually it works fine.

    Can u help me out for this?

    Thanks a lot in advance!

  307. Hi..

    I am facing a strange situation. when i executing the following query it is returning the error

    SELECT s.row_id as shipment,
    SUM(CAST(la5.attr_value AS INT)) AS Cases
    FROM
    shipment s WITH(NOLOCK)
    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id
    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id
    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND
    s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local
    INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = ‘CaseQty’ AND attr_grp = 3)
    WHERE s.spare2 = ‘RELEASED’
    AND shift.shift_desc = ‘c’
    AND DAY(ss.production_date) = DAY(’2010-08-09 00:00:00.000′)
    AND MONTH(ss.production_date) = MONTH(’2010-08-09 00:00:00.000′)
    AND YEAR(ss.production_date) = YEAR(’2010-08-09 00:00:00.000′)
    GROUP BY s.row_id

    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the nvarchar value ‘N’ to data type int.

    The column type of attr_value of lot_attr table is a USER DEFINED DATATYPE (Based on nvarchar datatype). containing both numeric type and non numeric type of data.

    But when i executing the following query

    SELECT s.row_id as shipment,
    –SUM(CAST(la5.attr_value AS INT)) AS Cases
    la5.attr_value AS Cases
    FROM
    shipment s WITH(NOLOCK)
    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id
    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id
    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND
    s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local
    INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = ‘CaseQty’ AND attr_grp = 3)
    WHERE s.spare2 = ‘RELEASED’
    AND shift.shift_desc = ‘c’
    AND DAY(ss.production_date) = DAY(’2010-08-09 00:00:00.000′)
    AND MONTH(ss.production_date) = MONTH(’2010-08-09 00:00:00.000′)
    AND YEAR(ss.production_date) = YEAR(’2010-08-09 00:00:00.000′)
    –and isnumeric(la5.attr_value)=1
    –GROUP BY s.row_id

    It is returning data like ..

    Shipment Cases

    12436 40
    12436 40
    12436 27
    12437 27
    12437 24
    12437 24
    12437 40

    We can see there is no non numeric type data fetched..

    if we executing the following query we are geting result what is expected .. just I have added isnumeric(la5.attr_value)=1 in the WHERE condition

    SELECT s.row_id as shipment,
    SUM(CAST(la5.attr_value AS INT)) AS Cases
    FROM
    shipment s WITH(NOLOCK)
    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id
    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id
    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND
    s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local
    INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = ‘CaseQty’ AND attr_grp = 3)
    WHERE s.spare2 = ‘RELEASED’
    AND shift.shift_desc = ‘c’
    AND DAY(ss.production_date) = DAY(’2010-08-09 00:00:00.000′)
    AND MONTH(ss.production_date) = MONTH(’2010-08-09 00:00:00.000′)
    AND YEAR(ss.production_date) = YEAR(’2010-08-09 00:00:00.000′)
    and isnumeric(la5.attr_value)=1
    GROUP BY s.row_id

    Shipment Cases

    12436 107

    12437 115

    My question is if there is no Non Numeric data selecting in that particular query the why I am geting error in the first query. In the Second query you can see.. there i no non numeric data found.. and in the Thrid query you can see if we add ISNUMERIC() FUNCTION then it is executing without returning any error..

  308. hi,

    I have a three tables where the information is
    countrycode,countryname
    1,India

    Another table information is
    StateCode,StateName,CountryCode
    1,Tamilnadu,1
    2,Delhi,1

    then another table information is
    CityCode,CityName,StateCode,CountryCode
    1,Chennai,1,1
    2,Madurai,1,1
    3,New Delhi,2,1

    I want the query result as
    CountryName,no of states,no of cities
    India,2,3

    Please let me know as early as possible the tell your suggestion or query for this.

    Thanks
    Samy

    • Select c.countryname,count(s.statecode) as states,count(ci.citycode) as cities from country as c inner join states as s on c.countrycode=s.countrycode inner join city as ci on s.statecode=ci.statecode

  309. Hello Pinal Dave,

    I have a situation where I need to insert records from a temp table into a table that has a large number of records only when they don’t already exist in the large table.

    In reality I only need to consider records from the large table that were inserted within the past few hours.

    I am using a left join construct to do this as below:

    INSERT myTable (TicketType,TicketKey,Provider,ExpirationDate,ErrorCode)
    SELECT
    vr.TicketType,
    vr.TicketKey,
    vr.Provider,
    vr.ExpirationDate,
    vr.ErrorCode
    FROM
    #MyTemp vr LEFT JOIN myTable vrt
    ON
    vr.TicketKey = vrt.TicketKey
    AND
    vr.Provider = vrt.Provider
    WHERE
    vrt.TicketKey IS NULL;

    My questions are:

    1. Is there a better way to do this?
    2. Would it be better to add a where clause to limit the records I left join to in the large table?
    3. Would it be better to create a view with a where clause to limit the records being compared?

  310. I have downloaded the SQL Server 2008 questions and answers pdf but can’t print it and I need to. Can I purchase the rights to print it from you?

  311. I am working on sql2pdf conversion.
    From google i got the sp for convert using (see this link )

    http://hosteddocs.ittoolbox.com/GB2.010807.pdf

    The pdf file is not generated in the C:\.

    When i appended the following code:

    EXEC @hr = sp_OADestroy @object
    IF @hr 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object
    END

    It displays the error as :
    Error: 0×80042730
    Source: ODSOLE Extended Procedure
    Description: The passed object is invalid.
    HelpFile: NULL
    HelpID: 0

    Can you please solve the problem.

    Regards,
    Dilip

  312. Hi
    I am trying to create some stored procedures in SQL 2005 after using 2000 for a number of years. I am encountering two problems that never occurred with the earlier version and cannot resolve the problems after numerous attempts. I will list the 2 problems in separate requests for help –
    1) I have an ID integer column that I want to compare to a string of passed parameters. I know that implicit conversion is no longer done but all attempts to convert or cast the integer field to varchar still result in the error message:
    Conversion failed when converting the varchar value ’1,2′ to data type int.
    I am building a querystring based on incoming parameters which is executed at the end. There are two ways I had tried to convert (using convert and cast) –
    example 1 – cast as varchar during select:
    Set @qry= ‘SELECT Person.PersonID,Job.JobID,Cast(JobOrg.OrgTypeID as varchar(5)) OrgTypeVarChar, ‘

    then compare to incoming parameter:
    Set @qry = @qry + ‘ AND OrgTypeVarChar IN(”’ + @OrgTypeList + ”’) ‘

    I have also tried to not convert the data type in the select but convert in the comparison:
    Set @qry = @qry + ‘ AND Cast(JobOrg.OrgTypeID AS varchar(5)) IN(”’ + @OrgTypeList + ”’) ‘

    Both result in the same error. Any advice would be greatly appreciated.

  313. Hi,

    I am working on the Full Text Search in Sql Server 2005. I have created a catalog and then an index on a particular table.

    I am searching for data using FREETEXTTABLE(tablename,*,@SearchKeyword). So I am searching in all columns in the index since any of the columns can contain the keywords.

    Is there is a way for me to know which columns in the index contained my search keywords?(without having to go and look at each column in the resultset)?

    I would appreciate any help in this regard. Thanks.

    Kalyan.

  314. Hi Pinal

    I need your help / ideas to perform advanced search on a string… I cannot use FTS because of many other constraints…..

    For ex: I have a string like below.
    “SQL Server 2005 Express Edition is the next version of MSDE and is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005.”

    User’s search would be: Express near4(“next”)

    Sentence should have “Express” word and “next” word should be there in either left or right side 4 words of “Express”.

    In the above sentence “next” is the 4th word (right side) from “Express” word so this should come as result…

    If user enters Express near3(“next”) — above sentence should not come because “next” word is not there 3 words….either side

  315. Hi Penal,
    I am using Access as a frontend application. My access forms include subforms. These subforms have a rowsource to temporary files. (On opening a form, I use code to generate a random number and use sql to create the table in my SQL database.
    I have decided to migrate to developing code using SQL stored procedures, however I still wish to keep access as my frontend application.
    Is there a way to use #temp files in SQL and send Access the name of the file which will inturn be used as the rowsource of the access subform?

  316. hi Dave,

    will you help me to get query to calculate difference between times ,data type is declared as varchar

    thanks in advance

    with regards
    S.Vanitha

  317. I want to write storeprocedure for retriev some data
    Table1:Group
    GroupId GroupName
    1 ABC
    2 XYZ
    3 PQR
    4 BNC
    Table2:- Contact
    ContactId Name GroupId
    1 Jone 3,2,4,1,
    2 Michle 2,5,
    3 Tiger 1,4,

    In parameter i pass the GroupId Like 1 ,2 ,3
    Suppose I am pass 2
    I want result like this
    1 Jone XYZ
    2 Michle XYZ

    Please help me for How to write a store procedure for that

    Thanks .
    Nishar

  318. Hi Dave,

    could you help me, our sql server is operated in US, so the data in the datetime field is storing in US Timezone Format.

    At the time of retreiving the Data , i need to convert into UK Timezone.

    How can acheive this UK timezone ?

    Could you send me some sample query

    Thanks & Regards
    Velu

  319. Hi Pinal,

    I have scheduled hrs for each employee based on this i need to calculate percentage of employee who scheduled less than 24 hrs ,percentage of employess who scheduled between 24 and 48 hrs and more than 48 hrs.

    SCheduled hrs will be weekly wise.

    Ouput should be like this

    Scheduled hrs 8/1/2010 8/8/2010 8/15/2010 8/22/20

    Less than 24hrs 45% 64% 76% 48%

    B/W 24 and 48 45% 35% 12% 61%
    more than 48 10% 1% 12%

  320. Hi.
    I have 20 tables in my database and almost more than 100 columns.My scenario is i have one stored procedure which connects 9 table and 51 columns.From that I need a search functionality.Like I can type any thing in one box and it will give me all the search result in rows with these columns and tables.I don’t need to define any table or column in the query because table may be more and column also may be more..How is it possible?
    I appreciate if any one can help me.

    Thanks.

  321. Hi Pinal,

    I know the internal execution of SQL Query.
    but , I want to know the internal execution of WHERE Clause.

    If there is ‘ABC’ table with 100 rows & 2 column(ID , NAME) & i am using WHERE Clause.

    SELECT * FROM ABC WHERE ID > 10

    suppose it gives me 60 rows. for that 60 rows they execute whole table using 100 rows.. right !!!!!!!!!!!!!!!

    but if query is like that,

    SELECT * FROM ABC WHERE ID > 10 AND NAME = ‘PRAKASH’

    suppose this time it gives me 40 rows.

    so, for getting this result SQL executes 100 rows 2 times????

    1st time for ‘ ID > 10 ‘ == 100 rows
    2nd time for ‘ NAME = ‘PRAKASH ‘ == 100 rows

    OR

    its use the following things.

    1st time for ‘ ID > 10 ‘ == 100 rows
    2nd time for ‘ NAME = ‘PRAKASH ‘ == 50 rows

  322. HI Dave,

    I knew about you and this site before a couple of months and am satisfied so much, now I have a question to ask you which your answer can help me in a great way.

    i have a select query as follows.

    SELECT column_name , data_type
    FROM information_schema.columns
    WHERE table_name = ‘transaction_queue’

    SELECT ‘ColumnName’,’DataType’

    which returns:

    transaction_queue_id int
    policy_id int
    transaction_queue_status_type_id int

    ‘ColumnName’,’DataType’

    My request is:

    How can i get the SECOND RESULT SET as header (first row in the result) as below

    column_name data_type

    transaction_queue_id int
    policy_id int
    transaction_queue_status_type_id int

    since I am beginner to SQL, I was little bit struggled and unable to answer my senior’s question, kindly help me to find a best and short-cut solutions for this.

    • SELECT ‘ColumnName’ as column_name,’DataType’ as DataType
      union all
      SELECT column_name , data_type
      FROM information_schema.columns
      WHERE table_name = ‘transaction_queue’

  323. Hi Pinal,

    my Application is scheduling agents(employees) for the compaign(callcenter) like dell,microsft,ibm.compaign will be under region.Should i need to maintain compaign and region columns in master table(EmpId,EmpName,Compaign,Region)

  324. Hi Pinal

    I am trying to know now is, how the sql engine is parsing this query?…

    When you use TOP in the inner query then tsql uses the inner query as virtual table.

    When I dont use TOP it errors out my.Why is the inner query not used as inline view or virtual table

    Use script below…

    create table record_dt (reldate varchar(10));
    insert into record_dt values (’2010 09 7 ‘);
    insert into record_dt values (’2010 0830 ‘);
    insert into record_dt values (’1999 01 26′);
    insert into record_dt values (’1998 12 01′);
    insert into record_dt values (’1999 03 09′);
    insert into record_dt values (’2009 02 10′);
    insert into record_dt values (’2008 10 28′);

    Query below does not DOES NOT WORK

    select * from (
    select
    convert(datetime,replace(replace(left(RelDate, 10), ‘ ‘, ‘-’), ‘zz’, ’01′)) REL_DATE
    from record_dt
    where len(reldate) > 4
    and (isdate(replace(replace(left(RelDate, 10), ‘ ‘, ‘-’), ‘zz’, ’01′)) = 1)
    ) as inn
    where inn.rel_date 4
    and (isdate(replace(replace(left(RelDate, 10), ‘ ‘, ‘-’), ‘zz’, ’01′)) = 1)
    ) as inn
    where inn.rel_date 4
    and (isdate(replace(replace(left(RelDate, 10), ‘ ‘, ‘-’), ‘zz’, ’01′)) = 1)
    ) as inn
    where inn.rel_date<=getdate()

    Thanks
    Sam

  325. hi,
    your articles are more helpful for me thanx ,
    i have a dought i.e,
    when we use “object” datatype in sql server.
    i faced this Question in an interview

    Thanks in Advance ,
    Rama krishna

  326. Hafiz Sajid Kamal

    Dear Pinal,

    This is Sajid Kamal Sr. Software engineer, we are working in Healthcare ERP.

    I regularly follow your blogs.

    This time we are facing very serious problem in SQL Server 2005.

    When user(s) saved a bill and get printed, some times data lost from transaction tables.

    All the saving is done through procedure under single transaction.

    Is it possible that after committing the data, data get removed from the table?

    I need your help.

    Thanks & Regards
    Sajid Kamal

    Sr. Software Engineer
    Akhil Systems Pvt. Ltd
    New Delhi

  327. hi
    i want to get data from CSv file into sql database.suppose the CSV file changes that is updations will be done on CSV file for every 8 hrs or 4 hrs like that.Then i have to get the updations reflected in my sql datbase also. Then how it is possible ????

  328. Hello Pinal ,

    i want to import .csv file database to sq lserver 2005, plz can you guide how to do this. File Format : csv or text no column and separated by tild (~) and i have near about 100 files how to do this multiple file import in sql server 2005 and separated by tild (~)

  329. Thank you, I have just been looking for info approximately this topic for ages and yours
    is the greatest I have came upon till now. However, what about the bottom line?
    Are you sure concerning the supply?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s