Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2003
    Posts
    24

    Unanswered: SQL 2005 snapshot problem

    Best all,

    I have an urgent problem.

    I have SQL Server 2005 installed in Windows 2000.
    In SQL Server Management Studio's SQL Editor, when I run following:

    USE bugdb2
    SET ALLOW_SNAPSHOT_ISOLATION ON;
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT

    I get following errors:

    Msg 195, Level 15, State 5, Line 6
    'ALLOW_SNAPSHOT_ISOLATION' is not a recognized option.
    Msg 102, Level 15, State 1, Line 8
    Incorrect syntax near 'SNAPSHOT'.

    Please help to solve this problem..

    Thank you very much

    Mel

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    This is the correct syntax.

    Code:
    ALTER DATABASE bugdb2
    SET ALLOW_SNAPSHOT_ISOLATION ON;
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT



    Last edited by mallier; 01-30-06 at 06:19.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Sep 2003
    Posts
    24
    Thanks mallier,

    But after running this:

    ALTER DATABASE bugdb2
    SET ALLOW_SNAPSHOT_ISOLATION ON;
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT


    I still get error:

    Msg 102, Level 15, State 6, Line 2
    Incorrect syntax near 'ALLOW_SNAPSHOT_ISOLATION'.
    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near 'SNAPSHOT'.

    Can you run that in your SQL 2005 to see if it works by you?

    Million Thanks,

    Mel

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    I have tried that code in SQL 2005,Its working fine here.
    Check ur db's isolation level below code.
    Code:
    SELECT name, snapshot_isolation_state,snapshot_isolation_state_desc AS description,state_desc,
    FROM sys.databases
    WHERE name= N'bugdb2';
    GO
    
    Last edited by mallier; 01-30-06 at 07:08.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Sep 2003
    Posts
    24
    Now I think it's actually not a problem with "SNAPSHOT" but some completely different problem, because I can NOT even run this:

    ALTER DATABASE bugdb2
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    Error:
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'TRANSACTION'.


    In fact I can not run this:

    ALTER DATABASE pubs

    Error:
    Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near 'pubs'.

    I login as Windows user.

    Don't know why ..

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Talking

    Quote Originally Posted by Melvine
    Now I think it's actually not a problem with "SNAPSHOT" but some completely different problem, because I can NOT even run this:

    ALTER DATABASE bugdb2
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    Error:
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'TRANSACTION'.


    In fact I can not run this:

    ALTER DATABASE pubs

    Error:
    Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near 'pubs'.

    I login as Windows user.

    Don't know why ..
    I hope u have connected SQL 2005 not SQL 2000 from SQL server Managment Studio!
    Last edited by mallier; 01-30-06 at 07:24.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  7. #7
    Join Date
    Sep 2003
    Posts
    24

    Angry

    Indeed I have SQL 2000 on the same machine too.

    But I "believe" I am well working inside SQL 2005 Studio.

    Here is the screenshot of my working stuido:

    http://img70.imageshack.us/img70/9192/a15ll.jpg

    my headache ...

  8. #8
    Join Date
    Jun 2003
    Posts
    269

    Question

    yes ur using sql server managment studio.but ur connected to sql 2000.U can see the built version (sql server 8.0.194).
    SQl server 2005 built version is sql server 9.0...

    I dont know how u got pubs db in sql server 2005 with out installing.

    try this code,

    SELECT @@VERSION

    show me the result
    Last edited by mallier; 01-30-06 at 07:43.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  9. #9
    Join Date
    Sep 2003
    Posts
    24
    Mallier you are so kind really thanks..

    SELECT @@VERSION

    Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    So it is indeed SQL 2000.

    I am still thinking about this ..

  10. #10
    Join Date
    Sep 2003
    Posts
    24
    Now I entered SQL Server 2000's Enterprise Manager, and in SQL Query Tool, run this:

    ALTER DATABASE bugdb2
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    It also gives error:
    Incorrect syntax near the keyword 'TRANSACTION'.

    But run this then well OK:
    USE bugdb2
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    Also:
    if I don't use "ALTER DATABASE bugdb2", but only use:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    Then it works.

    The command(s) completed successfully.

    I think the problem might be about the "permission" of the user..

    Still busy figuring it out ..


    Btw:
    I dont know how u got pubs db in sql server 2005 with out installing.
    --
    I think after installing SQL 2005, those existing databases of the old SQL 2000 are automatically "imported" to SQL 2005..
    Last edited by Melvine; 01-30-06 at 08:41.

  11. #11
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Quote Originally Posted by Melvine
    Now I entered SQL Server 2000's Enterprise Manager, and in SQL Query Tool, run this:

    ALTER DATABASE bugdb2
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    It also gives error:
    Incorrect syntax near the keyword 'TRANSACTION'.

    But run this then well OK:
    USE bugdb2
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    Also:
    if I don't use "ALTER DATABASE bugdb2", but only use:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    Then it works.

    The command(s) completed successfully.

    I think the problem might be about the "permission" of the user..

    Still busy figuring it out ..
    u mentioned sql 2005 in first post.There is a difference in syntax between sql2000 and sql2005.
    Ur second syntax is correct.first syntax only work with sql 2005.I dont think its a permission problem here.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  12. #12
    Join Date
    Sep 2003
    Posts
    24
    OK, I think I should first delete SQL 2000, then install 2005.
    Let me try this first, and I will let you know if the problem will be solved.

    Thanks a lot Mallier, I really appreciate your help.

    God bless you and have a great day
    Last edited by Melvine; 01-30-06 at 11:28.

  13. #13
    Join Date
    Sep 2003
    Posts
    24

    Thumbs up

    Hello Mallier,

    It works now.
    I first removed both SQL2000 and SQL2005, then re-installed SQL2005, then it works.
    http://img213.imageshack.us/img213/2046/a17kl.jpg

    Thank you so much for your help, if it was not because of your idea, I would have never thought about that SQL2000 might have caused trouble.

    Thank you again, and with best wishes ..

    Mel

  14. #14
    Join Date
    Jun 2003
    Posts
    269
    U know sql2000 and sql 2005 and co-exist in a same machine.U should give separte name when u install sql2005.Though Im not recommending this option(both SQl2000 and SQl2005 in same machine) in production server.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  15. #15
    Join Date
    Sep 2003
    Posts
    24
    Thanks Mallier,

    I might try that some time later.

    Great thanks,

    Cheers ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •