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

    Like

    • 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

      Like

  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.

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

    • 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

      Like

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

    Like

    • 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

      Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • @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?

      Like

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

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

    Like

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

    Like

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

    Like

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

    Like

  18. 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)”.

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  23. Hi Pinal,

    your articles about SQL are very interesting.

    Congratulations for your knowledge.

    Bye
    Marcus Linares – Brazil

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  49. 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: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
    Information: 0x40043007 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 (0x0).

    Like

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

    Like

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

    Like

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

        Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

      • 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

        Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

      Like

      • 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

        Like

        • 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

          Like

          • 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

            Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  73. 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();

    Like

  74. 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();

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

        Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

    • 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

      Like

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

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  138. hi pinal

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

    Thanks
    Prajin

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

      • 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

        Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

      • 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

        Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

      • 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

        Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

      • “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)

        Like

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

    Like

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

    Like

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

    Like

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

    Like

      • @ 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)

        Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  196. 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 0x534. [SQLSTATE 42000] (Error 15404)).,00:00:00,0,0,,,,0

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

      Like

      • 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

        Like

          • 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

            Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

      • 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

        Like

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

          Like

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

    Like

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

      Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

        Like

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

          Like

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

    Like

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

    Like

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

    Like

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

      Like

      • 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

        Like

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

    Like

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

    Like

  222. sir

    i wanna know about the performance of

    select *

    and

    select col1,col2,….

    which is better and why

    what the step perform by them ?

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

    • 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

      Like

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

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

      • 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

        Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like