Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628

    Unanswered: Problem with MS Query

    I am trying to make changes in the master database through MS Query, but whenever i try to make a change i get an error message stating that i have to reconfigure the database to allow ms query to make changes, even though im logged in through the sa account.

    does anyone have any ideas on how to do this???

  2. #2
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    have you modified the server settings to allow updtes to the master catalog? If not, in Entmgr select server properties,. On the "Server Setting" tab look at 'server behavior'...

  3. #3
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    This is SO dangerous it gives me shivers!!!!!!!!

  4. #4
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    Quote Originally Posted by grahamt
    This is SO dangerous it gives me shivers!!!!!!!!

    ...I have to agree with you on that one, but the cutomers gets whats they wants!!!

  5. #5
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Next question will be "How do I restore my database?"

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Or, do you have a database that can give me directions to the unemployment office.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    thanks for that. I found the setting that needs to be changed and it worked.

    And for once, my next question isn't "how do i restore my database?" because it's been asked often enough here to search on, but my next question is along similar lines.

    The "this is so dangerous it gives me shivers" comment is true, but when you don't really have anything to lose by trying, then it isn't so bad. Let me explain...

    There was a box set up about two years before i started that had NT server and SQL server 6.5 installed that was running fine. Then in December last year, my company moved into a new complex, and some bright spark never made any backups of the data before they powered down the server (not my responsibility as im just the user). Now, before the server was powered down, my line manager (and boss) took direct copies of the .dat files of all the databases (AHD, AHDLOG, AHDTEST50, AHDTEST50LOG, MASTER, MODEL, MSDB, TEMPDB and PUBs) and put them on a CD. When they tried to power u the server at the new premesis, they found that no-one knew the NT login password (the IT guy left about a year before we moved, and it just happens to be the only server that he wasn't told the passwords for, and it also just happened to be the only server where no backups had been made whatsoever since it was set up back in 1999/2000 time). Their solution? format the server and use it for something else.

    Now, here's the best bit...

    The server was left for 8-9 months (i had to use the old paper system in the meantime), and i was asking time and again untill i was pretty much blue in the face to get the server sorted, but it took me asking for a computer to try and see if i could get it to work myself. (sound familliar yet?)

    I found an old backup of the database that functioned, but it was made back in March 2002 by our head office when they were playing around with the whole system. Anyway, after telling my boss this he said "no, that's unacceptable. We have to have the latest information or there's no point in having the database at all." I just said that i'd try and see how it went, but if it can't be rescued then he's just going to have to suffer the old backup or kill off the helpdesk database full stop.

    so that pretty much brings me to my next question. Does the server name play an important role in a restore attempt of a .dat file? (the original server was called BK_MAN5_INV, and the test server im working on is called XW4000. I'd rename the test server to BK_MAN5_INV, but it has been setup for some other use.) If it doesn't matter about the server name then that's fine, and i'll just have to look at how i can restore every database without corrupting the files, and then if it works finding out how to get our helpdesk software to interact with the server (as the company we paid to set up the helpdesk has gon out of business now, and the people who set it up can't remember what settings/passwords they used).

    So as you can see im not exactly jumping for joy on having to do something i have no idea how to do, and the fact that i have absolutely no training or getting paid to do this, but need to do it to keep my job going (catch 22 or what?).

  8. #8
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Server name doesn't seem to matter. I have done backups on one server and then a restore on a different one (both SQL 2000 though) and everything seemed OK with the exception of a few minor things (one was a User Login issue I think.)

    I sure don't envy you your job. What a nightmare! I now see why you want to play with the master DB. With nothing to lose, go for it. Can't hurt what doesn't work anyway.

  9. #9
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135
    ...OUCH!...I know the inheritance thing, I inherited 25 instances some of which hadn't been successfully backed up in almost a year...

    ...no risk, no reward!

  10. #10
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Anybody know what would happed if he restored the 2002 copy of the DB, shut down SQL, replaced the .dat file with the newer one, and then started SQL back up?

  11. #11
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    well, if anyone is around Kintore, then you're more than welcome to come and lend a hand!

  12. #12
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Ontario or Scotland?
    Everybody has their own way of approaching a problem but with your problem here's where I'd start.

    1. Restore the old 2002 copy of the HelpDesk DB
    2. Export the master and msdb to Access
    3. DROP the HelpDesk DB
    4. Export master and msdb to different Access DBs than before
    5. Write program to compare the before and after DROP Access DBs
    6. Note which records are different/missing in the After version

    Now I would at least know which records/fields apply to the HelpDesk DB.

    Might work...

  13. #13
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Scotland im afraid.

    That sounds like a pretty good idea grahamt, i think i'll give it a shot today, but there is only one problem with it... The 2002 backup only works in SQL server 2000, and the original ones are 6.5 databases (and i need to rescue them before i can move up to SQL 2000).

    what the hey, i'll try and set up the 2002 backup in 6.5 (not like i have anything to lose.)

Posting Permissions

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