(SQL Server 2000, SP3)
Hello all!
My company is in the process of providing some Access Management capabilities within our
SQL Server database, and as part of this work we will be defining an association between
users of our product and some hierarchical structure (like organization/department) for
those users.
I think the table to house the hierarchical structure is pretty well-understood; in that
there will be some identifier and the table will have a field that is a reference to
itself to represent its parent identifier.
As part of our definition for this hierarchical structure, all child nodes will "inherit"
the parent node access rights. I'm not sure how best to craft the SQL that will help to
quickly identify all the parent nodes given a particular node in the tree.
I'm wondering if there are some good/recommended resources on manipulating hierarchical
data that would help me in this endeavor? Perhaps some suggestions on how I should
structure the table itself, and attendant queries.
If you know of any such material, I'd be very much obliged! :-)
John PetersonYou should definitely consider the Nested Sets approach as your hierarchy
model.
http://www.intelligententerprise.com/001020/celko1_1.shtml
http://www.dbazine.com/tropashko4.html
--
David Portas
--
Please reply only to the newsgroup
--
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23xWg01PYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> My company is in the process of providing some Access Management
capabilities within our
> SQL Server database, and as part of this work we will be defining an
association between
> users of our product and some hierarchical structure (like
organization/department) for
> those users.
> I think the table to house the hierarchical structure is pretty
well-understood; in that
> there will be some identifier and the table will have a field that is a
reference to
> itself to represent its parent identifier.
> As part of our definition for this hierarchical structure, all child nodes
will "inherit"
> the parent node access rights. I'm not sure how best to craft the SQL
that will help to
> quickly identify all the parent nodes given a particular node in the tree.
> I'm wondering if there are some good/recommended resources on manipulating
hierarchical
> data that would help me in this endeavor? Perhaps some suggestions on how
I should
> structure the table itself, and attendant queries.
> If you know of any such material, I'd be very much obliged! :-)
> John Peterson
>|||Thanks, David!
Oddly, that second link seems to suggest that Nested Sets is kind of a poor performer for
typical types of queries. Have you had any experience with Nested Sets?
Thanks!
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eW2BX%23PYDHA.1580@.tk2msftngp13.phx.gbl...
> You should definitely consider the Nested Sets approach as your hierarchy
> model.
> http://www.intelligententerprise.com/001020/celko1_1.shtml
> http://www.dbazine.com/tropashko4.html
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23xWg01PYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > My company is in the process of providing some Access Management
> capabilities within our
> > SQL Server database, and as part of this work we will be defining an
> association between
> > users of our product and some hierarchical structure (like
> organization/department) for
> > those users.
> >
> > I think the table to house the hierarchical structure is pretty
> well-understood; in that
> > there will be some identifier and the table will have a field that is a
> reference to
> > itself to represent its parent identifier.
> >
> > As part of our definition for this hierarchical structure, all child nodes
> will "inherit"
> > the parent node access rights. I'm not sure how best to craft the SQL
> that will help to
> > quickly identify all the parent nodes given a particular node in the tree.
> >
> > I'm wondering if there are some good/recommended resources on manipulating
> hierarchical
> > data that would help me in this endeavor? Perhaps some suggestions on how
> I should
> > structure the table itself, and attendant queries.
> >
> > If you know of any such material, I'd be very much obliged! :-)
> >
> > John Peterson
> >
> >
>
Showing posts with label company. Show all posts
Showing posts with label company. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
Need some initial direction
Hi all. The company I work for is looking for a new SQL server. Where can I find information and or a tool for sizing information? By sizing information I mean how big a pile of hardware am I going to need to run MS SQL for x number of connected users with x size database, etc. I've been tooling around the internet and MS' site but can't find any info on this.
Can anyone shed some light for me?
To be honest it totally depends.
The DB's I manage on my SQL box have about 50 users, the data is <20gig and im running the Dell PowerEdge750 Server with a Pentium4 HT Processor and it really does fly. By saying fly I mean its really quick, I have never had a problem with it. I cant comment on larger production environments as I simply dont have the experience.
Edit: I should add I am using 2 gigabytes of RAM for this particular server.
hth
Barry Andrew
|||Following MSDN libraries might be a good start for you to get familiar with Microsoft SQL Server 2005.
http://msdn2.microsoft.com/en-us/library/ms166352.aspx
http://msdn2.microsoft.com/en-us/library/ms188607.aspx
And following articles might be a good start for data warehousing, Architecture and Design.
http://msdn2.microsoft.com/en-us/library/aa226328(SQL.70).aspx
http://msdn2.microsoft.com/en-us/sql/aa336281.aspx
hope this helps. :-)
Thanks
|||Excellant! Thank you. That should get me started.sqlNeed some guidance!
I have been trying to rescue my company's helpdesk database because the company moved premesis and we lost that particular server.
It was originally running SQL Server 6.5, and my line manager simply copied the database.DAT files straight out of the MSSQL\Data directory without running a backup through Enterprise Manager.
Does anyone know how i can rescue these files so that we can get our helpdesk up and running again?!
thanks in advance for any help you can provide.I've never done this...but did you try and put the files in the exact location as the old box?
Is the line manager your boss?
If not, smack him/her in the head...
If yes, say what a great job they did to back up the data...
IOs the old box still available?
And how big is the file?|||ok,
Yes i have tried putting them back into the MSSQL\Data directory of an SQL server 6.5 install on a different server, but enterprise manager doesn't recognise them. I've tried creating databases with the said files in question and over-writing them with the originals, but it just makes them suspect, and therefore unusable.
Yes the line manager is my boss, and unfortunately, there's not much i can really say about them, but the "great job" one has sprung to mind.
The files are 77Mb for the main database i want to restore, and the master database is 100Mb.|||As SA you can go into master and manually change the status of the database from Suspect to "AOK". This is not guaranteed to work, but I have done it succesfully on 6.5.
If you got nothing to lose...|||Ok, so he's your boss...buy him a drink...
So you don't have a contact to someone where the old box is?
I remember reading that you have to modify the system tables...
It's not the best solution...
I would try and find someone to help from the old box...is it decommisioned?
Also try doing a search over at sqlteam...I know I saw it over there...|||thanks for that. we don't really have anything to lose, but thankfully we have an old version of the database that works, but it'll put us back to march 2002, and my boss thinks that is unacceptable.
Unfortunately the old box has been formatted and is being used for something else.
have you got the url for sqlteam? it won't hurt to go and have a look, and we might just get a solution to this (we've been working on it for almost a week now).|||Seriously. Try what you did before where you got the suspect database warnings.
Then set the database to allow changes to the system tables, locate your database in the sysdatabases table, and change its status to the value for a database that is not suspect. What can you lose? Not all suspects are guilty, you know...|||http://www.sqlteam.com/default.asp
Good luck...
Formatted the box...sheesh...
Do they have a tape backup?
And if your boss thinks it's unacceptable...you might want to mention that taking backups with his method was un...well not the prefered method...
Where are you from...want me to pay a visit?
I don't have to be nice....
Or better yet...send the hit squad...
The blind dude and rdjabarov should put the fear of god in to him...|||Got the first part done, but as for this part:
Then set the database to allow changes to the system tables, locate your database in the sysdatabases table, and change its status to the value for a database that is not suspect. What can you lose? Not all suspects are guilty, you know..
This is going to sound more than a little stupid, but where do i go to access the above? (i've just been going along blind, because A, im the only one who is free to do it, and B because i don't have any training on server software at all. Not even our IT guy knows how to work with SQL server 6.5, but if you could provide me with a bit of an idiots guide to get to those areas then i would really appreciate it.
Brett, im from outside Aberdeen, but i dont think my boss would take too kindly to having to get someone in to sort this one out. Must be a manager thing...|||That doesn't sound stupid. This is advanced crap.
To set the allow updates option:
Expand a server group.
Right-click a server, and then click Properties.
Click the Server Settings tab.
Under Server behavior, select or clear the Allow modifications to be made directly to the system catalogs check box.
Now run:
select status, name from sysdatabases
to see that status of all your databases
Then run:
Update sysdatabases set status = [YourDesiredStatus] where name = [YourDatabaseName]
Then RESET THE ALLOW UPDATES OPTION!
Here are the bit values for the status column. Select the options you want, add up their values, and that is [YourDesiredStatus].
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
I'd run backups of any critical data on the server before doing this, just as a matter of principle.
You now know enough to be very dangerous.
Oh, and we DBAs prefer to call it "Books Online" rather than "Idiot's Guide".|||ok, cheers for that. I just had a thought though. Would the same be possible on a server 2000 install? i.e. re-naming the old .DAT files to the respective file formats and following the above (with the obvious changes due to the software).
If it does then i can put 2000 on the test machine to see if that works out. I seem to remember something about 6.5 databases being incompatible with 2000 for some reason, unless a direct upgrade is carried out...|||You don't know them to well...
I didn't say they'd come in and fix it...mostly berate the guy...
And it's 6.5...I didn't look to close...
I try to forget everything pre 7...
A search...this was a fun thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36375&whichpage=1&SearchTerms=restore%2Cdb%2Cwith%2Cdb%2Cfile
Need to do more digging...|||That doesn't sound stupid. This is advanced crap.
You now know enough to be very dangerous.
Oh, and we DBAs prefer to call it "Books Online" rather than "Idiot's Guide".
AMEN brother
Your a DBA?...go figure...
And it's BOL|||im nowhere near a DBA (just incase that was intended for me), i just seem to be the try and fix it guy.
Oh, here's another challenge for you. I've just discovered that i need to have it up to service pack 5a, looking through the microsoft site there's only post service pack 5a, not much use there. do you guys know where to find the relevant service packs 'cause im now stumped. (and it all started off with my boss saying "no you don't need the service packs. It should run fine enough without them.")|||and it all started off with my boss saying "no you don't need the service packs. It should run fine enough without them.")
I'm speechless...
What line of business are you in?
I hope it's one where you don't need to worry about data
http://www.microsoft.com/sql/downloads/2000/sp3.asp|||Im not speechless, im pretty fed up with it.
The company i work for is a supplier for power managment companies like the National Grid and a few other ones in other countries. The helpdesk that im trying to fix (which im employed to take the calls for, not fix it) deals with any control system problems that may exist, as well as keeps track of all the hardware that is used in the makeup of the control system (so you can see it's a fairly important thing to get sorted out asap, but it hasn't been the case.)
the problem had been sitting unresolved with a few half-hearted efforts to solve it over the past 8 months, which i was pretty much screaming to get it fixed the whole time. now i got fed up so i started looking into it, and that brings us to where we are today.
Anyway, im going to have to log off now and go home and finish off servicing 4 mountain bikes (finally, something relaxing!), which should take me until friday to do.
I'll post any progress made and/or any other queries up here tomorrow, because i'll probably get stuck real quick.|||You gotta be across the pond somewhere...Ireland perhaps?
And you need the service packs for bot the OS and SQL Server...
Ever hear of slammer?
And if it's out there 8 months now...how are they recording the data now?
Good luck...and have fun with the bikes...
Sounds like you know a good deal about them...
I'm looking to buy a couple...what would be a good one...not too high end, but not junk either...|||If you are still have problems, why don't you just place a call to your company's helpdesk? That person should know all about database servers and system tables and suspect files and ... Oh. Nevermind...
Do NOT try klugeing in a 6.5 database file in place of a 2000 database file and then hand-changing the status code. A general rule of ANY issue resolution situation is to fix ONE THING AT A TIME. Reinstall your 6.5 data base server, bet the old database working, and then upgrade to 2000.|||thanks for the input guys. Brett, i live by Aberdeen, Scotland. If you're looking for a couple of bikes, what kind of riding are you most likely to do?|||How big is aberdeen?
http://www.dbforums.com/showthread.php?p=3701716#post3701716|||Aberdeen has a population of about 265,000 (i think). it covers a fair old area at any rate.|||Did ya give a shout out to David?
And your day's close to being over...isn't it?|||yeah. You'll see my replies in the Yak Corral thread. I wish it was almost over. Its 14:25 here and i don't finish until 16:45. I went and asked a mate of mine if i could do a straight swap for his bike. won't find out until around 7ish, and i really hope he goes for it, even if he is losing out slightly.|||is there any way to use command prompt to create a backup of a database file from a specified location i.e. C:\backup\tempdb.DAT?|||Yes, through the OSQL utility included with SQL Server.|||thanks blindman. I was in the middle of finding a tutorial for using bcp, but i'll try this first to see if it works.|||actually, how do you use it? just had a quick look and it seems to be along the same lines as bcp (i.e. too complicated for me).
just going back to one of your past posts, the database server we are running for the helpdesk is a seperate entity from the company network, and as such doesn't come under normal company IT support. If we were going to raise a call with the IT department, they would be required to take full control of the server and that would mean that they could do as they see fit with it, which my boss says is unacceptable (as usual).
so basically im left with the job of trying to figure out a way to fix the problem (with no training in servers, sql or database management at all, but with this company im not suprised that they expect things to work with no training being given), which is why i ended up here. At least i'll be able to get advice on areas i won't have thought on, or can't because i don't really know what im doing.|||OSQL utility allows you to run SQL statements from the command prompt. You can either enter them line-by-line or call OSQL with a batch file of SQL commands. Your SQL batch file would contain the SQL commands for backing up your database.
BCP is only mildly complicated. It can be a bit quirky to get to work right the first time, but after that it is very fast, simple, and reliable.|||along the same lines as bcp (i.e. too complicated for me).
Huh?
Damn that hurt...
bcp is your freind...get to know her extremely well....
and I use osql very infrequently...you can do it all in sprocs...
I actually justy use osql so I can build a script that I give to my PROD DBA...mostly so they don't screw up a release...|||i guess it'll just take time to learn how to use it.
Got some other bad news today. I was trying to re-allocate resources on my website, and because of the package i have i can't re-allocate any resources at all (im running a php 6.5 website at the moment). So i only have three options: upgrade my package deal (cost somewhere in the region of $100US to do it though), build a brand new html website that won't have the same functionality as the one i have at the moment, or move servers to somewhere where i can re-allocate resources to my hearts content. Is that readysetconnect server any good? 1250Mb with 40Gb transfer is a heck of alot better than my current 100Mb space with 1Gb transfer.|||Here's a nice quick GUI tool for BCP.
http://weblogs.sqlteam.com/davidm/archive/2004/03/31/1151.aspx|||that's brilliant. thanks peter|||A GUI?
Don't use xp_cmdshell....|||Man, you guys are confusing him! OSQL did not exist in 6.5, so the only thing is ISQL there. BCP? He can't even get to his database, how in the world is he gonna use BCP? Monkeying with the status? Oh Codd...Check DISK REFIT/DISK REINIT...Man, I don't even have BOL for 6.5 any more...
Search for Sybase documentation, they had a lot in common, including the commands that I posted.|||You're right. OSQL did not exist. But ISQL did and he should have that. I never did figure out what the difference was...|||ISQL is using DB-Library to "talk" to the server, while OSQL is using ODBC.|||Well, I am wading into this thread a little late. Let me see if I have this all straight.
1) There once was a SQL 6.5 box with the database for the helpdesk application on it.
2) In a move, this server was lost.
3) The last known backup of the database is from March.
4) You have the .DAT files from the old SQL 6.5 box, and you boss thinks you can get them attached to the new 6.5 box?
Hmm. I think I would have to plead ignorance on this one, and have your boss show the "poor ignorant lowly wage drone" just how to do it. After he fails, you can have a private laugh in the pub ;-).
There might be a way of doing it, but I can not think of what it may be. I have not touoched SQL 6.5 in ages (3 years), now. If I remember right, there was a column in the master database that was related to the last transaction ID in each database. If that is right (and that is a big "if"), I think reattaching this database could be impossible. As Blindman pointed out, it is certainly darned difficult.
Without the database being attached to a live SQL Server, You will not be able to run anything against it (isql and bcp included). All I can think of is what you have already tried (create new database with the same name, shut down, switch files, pray).
As I remember detaching and reattaching databases was a big deal when SQL 7.0 came out. I did not see the big deal at the time, and thought it was just another Microsoft marketing blitz fixating on minor details that have almost no application. Wait... I still think that. Shows you how often I use detaching databases, eh? The point is, it is not supported in SQL 6.5
If you do go humbly before your boss to have him show you how it is done, be sure to let us know what he says when he confronsts the inevitable. I think we can all guess what Microsoft Support's answer to all of this will be.|||...Again, DISK REFIT/DISK REINIT should do the drick ONLY if you have all .DAT files. I hope you're not missing log devices or have an incomplete set of .DAT files...|||rdabarov probably has it. I do not remember DISK REFIT, but DISK REINIT rings a bell. Be sure you have a safe copy of the .dat files, though. These commands may make a few changes to the files.|||ISQL is using DB-Library to "talk" to the server, while OSQL is using ODBC.
Yeah, but I don't remember any functional difference. Was there?|||Beyond OSQL having a few extra/changed switches, there was not a whole lot of difference that I could see. I could have been Microsoft trying to phase out DB Library. Why they went to ODBC, I am not entirely sure. Looks like OSQL could be used with an ODBC DSN, so technically, you could try to query Oracle with it. Not sure why you would want to, though.
Judging by the SQL 2005 download, it looks like ISQL is dead, OSQL is deprecated, and SQLCMD is the new tool for scripting. Again, just a few changes in the switches from OSQL. Probably a lot under the covers, but I don't see it, yet.
EDIT: Oh, and BCP is still there. How much of Brett's stuff would break, if they changed BCP to SQLCOPY? ;-)|||Bite your tonque...the horror, the sheer horror....|||We need a topic change. Or move it to Brett's place (everybody should know by now where it is ;))|||Does Brett's place serve Margaritas? I've been working with Oracle all week, so I could use a pitcher myself.|||I have all the .DAT files, but they are not backup files of the databases. My boss has only just realised that he should have made a backup of everything through the enterprise manager, but instead just copied them directly from the MSSQL\Data directory.
But here's the latest on what's been going on:
We have set up the box we were using to run server 2000 (windows and sql) with the march 2002 backup we know works. sounds straight forward enough.
My boss has put another box together and has set it up the same as we had it before (windows server 2000 and sql 6.5), but he wants to try and see if he can get it working.
Im overjoyed that he finally took it in hand and decided to try this for himself because he knows alot more about sql server and all the rest too. I keep getting asked about what i did and how i did it, but i'll mention this and see if he knows about it. (rdjabarov, if you could give me a procedure on how to do the DISK REFIT/DISK REINIT then that would be great)
but utill then im stuck trying to get the rest of the software working, which due to fact it is installed on different machines, will have to be re-license and god only knows how much that'll cost. Once that's done, i'll have to try and bring the information up to date manually, or at least until we rescue those .DAT files.|||ok, here's the latest update:
I got the AHDTEST database that i had been trying to rescue working to a degree in 6.5, but i got 81 pages (A4 pages) of errors because the master database has not been restored. I have a few queries about this because the .DAT files were made on a server called BK_MAN5_INV, and the name of this server is XW4000, so there might be a problem with the DISK REFIT/DISK REINIT anyway wouldn't there? My train of thought leads me down to the fact that the servers have different names and different configurations, thus unless the REFIT/INIT commands altered these settings to be the same as the target machine (in this case XW4000) then that would be fine, but im not so sure about that.
Does anyone know if it actually does this?|||So what database have you restored? I didn't mention it (got busy here for a change ;)) but when installing a fresh 6.5 instance you needed to make sure that code page, language, and sort order are the same as the original. Is this the case?|||no one here knows what the code page, language and sort order settings were due to this being an inheritence case. all we could do is assume they used system defaults. Database wise, i got the AHDTEST50 database in without it going suspect, but got a shed load of errors due to the fact that the master database, and thus all the other databases too, haven't been put in. So as a result im looking at restoring the system databases as well as the user databases. I just wish there was a way to put them all in at the same time (through MS Query or something like it).sql
It was originally running SQL Server 6.5, and my line manager simply copied the database.DAT files straight out of the MSSQL\Data directory without running a backup through Enterprise Manager.
Does anyone know how i can rescue these files so that we can get our helpdesk up and running again?!
thanks in advance for any help you can provide.I've never done this...but did you try and put the files in the exact location as the old box?
Is the line manager your boss?
If not, smack him/her in the head...
If yes, say what a great job they did to back up the data...
IOs the old box still available?
And how big is the file?|||ok,
Yes i have tried putting them back into the MSSQL\Data directory of an SQL server 6.5 install on a different server, but enterprise manager doesn't recognise them. I've tried creating databases with the said files in question and over-writing them with the originals, but it just makes them suspect, and therefore unusable.
Yes the line manager is my boss, and unfortunately, there's not much i can really say about them, but the "great job" one has sprung to mind.
The files are 77Mb for the main database i want to restore, and the master database is 100Mb.|||As SA you can go into master and manually change the status of the database from Suspect to "AOK". This is not guaranteed to work, but I have done it succesfully on 6.5.
If you got nothing to lose...|||Ok, so he's your boss...buy him a drink...
So you don't have a contact to someone where the old box is?
I remember reading that you have to modify the system tables...
It's not the best solution...
I would try and find someone to help from the old box...is it decommisioned?
Also try doing a search over at sqlteam...I know I saw it over there...|||thanks for that. we don't really have anything to lose, but thankfully we have an old version of the database that works, but it'll put us back to march 2002, and my boss thinks that is unacceptable.
Unfortunately the old box has been formatted and is being used for something else.
have you got the url for sqlteam? it won't hurt to go and have a look, and we might just get a solution to this (we've been working on it for almost a week now).|||Seriously. Try what you did before where you got the suspect database warnings.
Then set the database to allow changes to the system tables, locate your database in the sysdatabases table, and change its status to the value for a database that is not suspect. What can you lose? Not all suspects are guilty, you know...|||http://www.sqlteam.com/default.asp
Good luck...
Formatted the box...sheesh...
Do they have a tape backup?
And if your boss thinks it's unacceptable...you might want to mention that taking backups with his method was un...well not the prefered method...
Where are you from...want me to pay a visit?
I don't have to be nice....
Or better yet...send the hit squad...
The blind dude and rdjabarov should put the fear of god in to him...|||Got the first part done, but as for this part:
Then set the database to allow changes to the system tables, locate your database in the sysdatabases table, and change its status to the value for a database that is not suspect. What can you lose? Not all suspects are guilty, you know..
This is going to sound more than a little stupid, but where do i go to access the above? (i've just been going along blind, because A, im the only one who is free to do it, and B because i don't have any training on server software at all. Not even our IT guy knows how to work with SQL server 6.5, but if you could provide me with a bit of an idiots guide to get to those areas then i would really appreciate it.
Brett, im from outside Aberdeen, but i dont think my boss would take too kindly to having to get someone in to sort this one out. Must be a manager thing...|||That doesn't sound stupid. This is advanced crap.
To set the allow updates option:
Expand a server group.
Right-click a server, and then click Properties.
Click the Server Settings tab.
Under Server behavior, select or clear the Allow modifications to be made directly to the system catalogs check box.
Now run:
select status, name from sysdatabases
to see that status of all your databases
Then run:
Update sysdatabases set status = [YourDesiredStatus] where name = [YourDatabaseName]
Then RESET THE ALLOW UPDATES OPTION!
Here are the bit values for the status column. Select the options you want, add up their values, and that is [YourDesiredStatus].
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
I'd run backups of any critical data on the server before doing this, just as a matter of principle.
You now know enough to be very dangerous.
Oh, and we DBAs prefer to call it "Books Online" rather than "Idiot's Guide".|||ok, cheers for that. I just had a thought though. Would the same be possible on a server 2000 install? i.e. re-naming the old .DAT files to the respective file formats and following the above (with the obvious changes due to the software).
If it does then i can put 2000 on the test machine to see if that works out. I seem to remember something about 6.5 databases being incompatible with 2000 for some reason, unless a direct upgrade is carried out...|||You don't know them to well...
I didn't say they'd come in and fix it...mostly berate the guy...
And it's 6.5...I didn't look to close...
I try to forget everything pre 7...
A search...this was a fun thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36375&whichpage=1&SearchTerms=restore%2Cdb%2Cwith%2Cdb%2Cfile
Need to do more digging...|||That doesn't sound stupid. This is advanced crap.
You now know enough to be very dangerous.
Oh, and we DBAs prefer to call it "Books Online" rather than "Idiot's Guide".
AMEN brother
Your a DBA?...go figure...
And it's BOL|||im nowhere near a DBA (just incase that was intended for me), i just seem to be the try and fix it guy.
Oh, here's another challenge for you. I've just discovered that i need to have it up to service pack 5a, looking through the microsoft site there's only post service pack 5a, not much use there. do you guys know where to find the relevant service packs 'cause im now stumped. (and it all started off with my boss saying "no you don't need the service packs. It should run fine enough without them.")|||and it all started off with my boss saying "no you don't need the service packs. It should run fine enough without them.")
I'm speechless...
What line of business are you in?
I hope it's one where you don't need to worry about data
http://www.microsoft.com/sql/downloads/2000/sp3.asp|||Im not speechless, im pretty fed up with it.
The company i work for is a supplier for power managment companies like the National Grid and a few other ones in other countries. The helpdesk that im trying to fix (which im employed to take the calls for, not fix it) deals with any control system problems that may exist, as well as keeps track of all the hardware that is used in the makeup of the control system (so you can see it's a fairly important thing to get sorted out asap, but it hasn't been the case.)
the problem had been sitting unresolved with a few half-hearted efforts to solve it over the past 8 months, which i was pretty much screaming to get it fixed the whole time. now i got fed up so i started looking into it, and that brings us to where we are today.
Anyway, im going to have to log off now and go home and finish off servicing 4 mountain bikes (finally, something relaxing!), which should take me until friday to do.
I'll post any progress made and/or any other queries up here tomorrow, because i'll probably get stuck real quick.|||You gotta be across the pond somewhere...Ireland perhaps?
And you need the service packs for bot the OS and SQL Server...
Ever hear of slammer?
And if it's out there 8 months now...how are they recording the data now?
Good luck...and have fun with the bikes...
Sounds like you know a good deal about them...
I'm looking to buy a couple...what would be a good one...not too high end, but not junk either...|||If you are still have problems, why don't you just place a call to your company's helpdesk? That person should know all about database servers and system tables and suspect files and ... Oh. Nevermind...
Do NOT try klugeing in a 6.5 database file in place of a 2000 database file and then hand-changing the status code. A general rule of ANY issue resolution situation is to fix ONE THING AT A TIME. Reinstall your 6.5 data base server, bet the old database working, and then upgrade to 2000.|||thanks for the input guys. Brett, i live by Aberdeen, Scotland. If you're looking for a couple of bikes, what kind of riding are you most likely to do?|||How big is aberdeen?
http://www.dbforums.com/showthread.php?p=3701716#post3701716|||Aberdeen has a population of about 265,000 (i think). it covers a fair old area at any rate.|||Did ya give a shout out to David?
And your day's close to being over...isn't it?|||yeah. You'll see my replies in the Yak Corral thread. I wish it was almost over. Its 14:25 here and i don't finish until 16:45. I went and asked a mate of mine if i could do a straight swap for his bike. won't find out until around 7ish, and i really hope he goes for it, even if he is losing out slightly.|||is there any way to use command prompt to create a backup of a database file from a specified location i.e. C:\backup\tempdb.DAT?|||Yes, through the OSQL utility included with SQL Server.|||thanks blindman. I was in the middle of finding a tutorial for using bcp, but i'll try this first to see if it works.|||actually, how do you use it? just had a quick look and it seems to be along the same lines as bcp (i.e. too complicated for me).
just going back to one of your past posts, the database server we are running for the helpdesk is a seperate entity from the company network, and as such doesn't come under normal company IT support. If we were going to raise a call with the IT department, they would be required to take full control of the server and that would mean that they could do as they see fit with it, which my boss says is unacceptable (as usual).
so basically im left with the job of trying to figure out a way to fix the problem (with no training in servers, sql or database management at all, but with this company im not suprised that they expect things to work with no training being given), which is why i ended up here. At least i'll be able to get advice on areas i won't have thought on, or can't because i don't really know what im doing.|||OSQL utility allows you to run SQL statements from the command prompt. You can either enter them line-by-line or call OSQL with a batch file of SQL commands. Your SQL batch file would contain the SQL commands for backing up your database.
BCP is only mildly complicated. It can be a bit quirky to get to work right the first time, but after that it is very fast, simple, and reliable.|||along the same lines as bcp (i.e. too complicated for me).
Huh?
Damn that hurt...
bcp is your freind...get to know her extremely well....
and I use osql very infrequently...you can do it all in sprocs...
I actually justy use osql so I can build a script that I give to my PROD DBA...mostly so they don't screw up a release...|||i guess it'll just take time to learn how to use it.
Got some other bad news today. I was trying to re-allocate resources on my website, and because of the package i have i can't re-allocate any resources at all (im running a php 6.5 website at the moment). So i only have three options: upgrade my package deal (cost somewhere in the region of $100US to do it though), build a brand new html website that won't have the same functionality as the one i have at the moment, or move servers to somewhere where i can re-allocate resources to my hearts content. Is that readysetconnect server any good? 1250Mb with 40Gb transfer is a heck of alot better than my current 100Mb space with 1Gb transfer.|||Here's a nice quick GUI tool for BCP.
http://weblogs.sqlteam.com/davidm/archive/2004/03/31/1151.aspx|||that's brilliant. thanks peter|||A GUI?
Don't use xp_cmdshell....|||Man, you guys are confusing him! OSQL did not exist in 6.5, so the only thing is ISQL there. BCP? He can't even get to his database, how in the world is he gonna use BCP? Monkeying with the status? Oh Codd...Check DISK REFIT/DISK REINIT...Man, I don't even have BOL for 6.5 any more...
Search for Sybase documentation, they had a lot in common, including the commands that I posted.|||You're right. OSQL did not exist. But ISQL did and he should have that. I never did figure out what the difference was...|||ISQL is using DB-Library to "talk" to the server, while OSQL is using ODBC.|||Well, I am wading into this thread a little late. Let me see if I have this all straight.
1) There once was a SQL 6.5 box with the database for the helpdesk application on it.
2) In a move, this server was lost.
3) The last known backup of the database is from March.
4) You have the .DAT files from the old SQL 6.5 box, and you boss thinks you can get them attached to the new 6.5 box?
Hmm. I think I would have to plead ignorance on this one, and have your boss show the "poor ignorant lowly wage drone" just how to do it. After he fails, you can have a private laugh in the pub ;-).
There might be a way of doing it, but I can not think of what it may be. I have not touoched SQL 6.5 in ages (3 years), now. If I remember right, there was a column in the master database that was related to the last transaction ID in each database. If that is right (and that is a big "if"), I think reattaching this database could be impossible. As Blindman pointed out, it is certainly darned difficult.
Without the database being attached to a live SQL Server, You will not be able to run anything against it (isql and bcp included). All I can think of is what you have already tried (create new database with the same name, shut down, switch files, pray).
As I remember detaching and reattaching databases was a big deal when SQL 7.0 came out. I did not see the big deal at the time, and thought it was just another Microsoft marketing blitz fixating on minor details that have almost no application. Wait... I still think that. Shows you how often I use detaching databases, eh? The point is, it is not supported in SQL 6.5
If you do go humbly before your boss to have him show you how it is done, be sure to let us know what he says when he confronsts the inevitable. I think we can all guess what Microsoft Support's answer to all of this will be.|||...Again, DISK REFIT/DISK REINIT should do the drick ONLY if you have all .DAT files. I hope you're not missing log devices or have an incomplete set of .DAT files...|||rdabarov probably has it. I do not remember DISK REFIT, but DISK REINIT rings a bell. Be sure you have a safe copy of the .dat files, though. These commands may make a few changes to the files.|||ISQL is using DB-Library to "talk" to the server, while OSQL is using ODBC.
Yeah, but I don't remember any functional difference. Was there?|||Beyond OSQL having a few extra/changed switches, there was not a whole lot of difference that I could see. I could have been Microsoft trying to phase out DB Library. Why they went to ODBC, I am not entirely sure. Looks like OSQL could be used with an ODBC DSN, so technically, you could try to query Oracle with it. Not sure why you would want to, though.
Judging by the SQL 2005 download, it looks like ISQL is dead, OSQL is deprecated, and SQLCMD is the new tool for scripting. Again, just a few changes in the switches from OSQL. Probably a lot under the covers, but I don't see it, yet.
EDIT: Oh, and BCP is still there. How much of Brett's stuff would break, if they changed BCP to SQLCOPY? ;-)|||Bite your tonque...the horror, the sheer horror....|||We need a topic change. Or move it to Brett's place (everybody should know by now where it is ;))|||Does Brett's place serve Margaritas? I've been working with Oracle all week, so I could use a pitcher myself.|||I have all the .DAT files, but they are not backup files of the databases. My boss has only just realised that he should have made a backup of everything through the enterprise manager, but instead just copied them directly from the MSSQL\Data directory.
But here's the latest on what's been going on:
We have set up the box we were using to run server 2000 (windows and sql) with the march 2002 backup we know works. sounds straight forward enough.
My boss has put another box together and has set it up the same as we had it before (windows server 2000 and sql 6.5), but he wants to try and see if he can get it working.
Im overjoyed that he finally took it in hand and decided to try this for himself because he knows alot more about sql server and all the rest too. I keep getting asked about what i did and how i did it, but i'll mention this and see if he knows about it. (rdjabarov, if you could give me a procedure on how to do the DISK REFIT/DISK REINIT then that would be great)
but utill then im stuck trying to get the rest of the software working, which due to fact it is installed on different machines, will have to be re-license and god only knows how much that'll cost. Once that's done, i'll have to try and bring the information up to date manually, or at least until we rescue those .DAT files.|||ok, here's the latest update:
I got the AHDTEST database that i had been trying to rescue working to a degree in 6.5, but i got 81 pages (A4 pages) of errors because the master database has not been restored. I have a few queries about this because the .DAT files were made on a server called BK_MAN5_INV, and the name of this server is XW4000, so there might be a problem with the DISK REFIT/DISK REINIT anyway wouldn't there? My train of thought leads me down to the fact that the servers have different names and different configurations, thus unless the REFIT/INIT commands altered these settings to be the same as the target machine (in this case XW4000) then that would be fine, but im not so sure about that.
Does anyone know if it actually does this?|||So what database have you restored? I didn't mention it (got busy here for a change ;)) but when installing a fresh 6.5 instance you needed to make sure that code page, language, and sort order are the same as the original. Is this the case?|||no one here knows what the code page, language and sort order settings were due to this being an inheritence case. all we could do is assume they used system defaults. Database wise, i got the AHDTEST50 database in without it going suspect, but got a shed load of errors due to the fact that the master database, and thus all the other databases too, haven't been put in. So as a result im looking at restoring the system databases as well as the user databases. I just wish there was a way to put them all in at the same time (through MS Query or something like it).sql
Friday, March 9, 2012
need opinions
I am developing a web application that will host many companies' data in one
database. And there will be the possibility that the company may decide to
take their data at some point and host it themselves.
So my question relates to the table structure and relationships. If a
company decides to take their data, I want it to be as easy as pulling all
data from all tables where Company_id = x. That said, let's just assume I'm
going to have the Company_Id in all tables or most. Do I create a separate
primary key field that's unique across all companies or unique within the
company_id ? I like the idea of all companies starting at 1 for their
primary key IDs, but this also brings more complexity and inefficiencies to
querying the database. Plus it means defining a two field unique index per
table and having to base all relationships between tables on two fields.
The joins are more complicated.
I'm leaning toward using one primary key field across all companies to link
tables, but still having the Company_id in all tables just to be able to
extract the data easily if I ever need to. The Primary key field would not
be an Auto ID, as this would pose problems when migrating existing data to a
new database.
Or do I just use a different database per customer. I expect having 1000 or
more companies, so I don't think separate databases will fly.
Looking for your opinion and getting ideas about how things are being done
elsewhere.
thanks,
BillBill
CREATE TABLE Companies
(
company_id INT NOT NULL PRIMARY KEY,
company_description VARCHAR(50) NOT NULL
.....
.....
)
CREATE TABLE Companies_Data
(
row_id INT NOT NULL PRIMARY KEY,
company_id INT NOT NULL REFERENCES Companies (company_id),
comp_activities VARCHAR(...) or INTEGER ( Depends on your business
requierements)
customer_id.....
.....
)
CREATE TABLE Customers
(
custoemid INT NOT NULL PRIMARY KEY,
custname VRACHAR(50) NOT NULL,
....
)
Look , the company may have more than one actvities , right? Consider
crerating an UNIQUE CONSTRAINT on these fields
> Or do I just use a different database per customer. I expect having 1000
> or more companies, so I don't think separate databases will fly.
Well, I have seen some systems that customers hosted their databases (with
costomer info) and just connected to the main server to make operations as
well as the main server has all info about customers. It is really depends
on your business requirements.
"Bill H" <bill@.somewhere.com> wrote in message
news:OwWFpNXTGHA.5900@.tk2msftngp13.phx.gbl...
>I am developing a web application that will host many companies' data in
>one database. And there will be the possibility that the company may
>decide to take their data at some point and host it themselves.
> So my question relates to the table structure and relationships. If a
> company decides to take their data, I want it to be as easy as pulling all
> data from all tables where Company_id = x. That said, let's just assume
> I'm going to have the Company_Id in all tables or most. Do I create a
> separate primary key field that's unique across all companies or unique
> within the company_id ? I like the idea of all companies starting at 1
> for their primary key IDs, but this also brings more complexity and
> inefficiencies to querying the database. Plus it means defining a two
> field unique index per table and having to base all relationships between
> tables on two fields. The joins are more complicated.
> I'm leaning toward using one primary key field across all companies to
> link tables, but still having the Company_id in all tables just to be able
> to extract the data easily if I ever need to. The Primary key field would
> not be an Auto ID, as this would pose problems when migrating existing
> data to a new database.
> Or do I just use a different database per customer. I expect having 1000
> or more companies, so I don't think separate databases will fly.
>
> Looking for your opinion and getting ideas about how things are being done
> elsewhere.
> thanks,
> Bill
>
database. And there will be the possibility that the company may decide to
take their data at some point and host it themselves.
So my question relates to the table structure and relationships. If a
company decides to take their data, I want it to be as easy as pulling all
data from all tables where Company_id = x. That said, let's just assume I'm
going to have the Company_Id in all tables or most. Do I create a separate
primary key field that's unique across all companies or unique within the
company_id ? I like the idea of all companies starting at 1 for their
primary key IDs, but this also brings more complexity and inefficiencies to
querying the database. Plus it means defining a two field unique index per
table and having to base all relationships between tables on two fields.
The joins are more complicated.
I'm leaning toward using one primary key field across all companies to link
tables, but still having the Company_id in all tables just to be able to
extract the data easily if I ever need to. The Primary key field would not
be an Auto ID, as this would pose problems when migrating existing data to a
new database.
Or do I just use a different database per customer. I expect having 1000 or
more companies, so I don't think separate databases will fly.
Looking for your opinion and getting ideas about how things are being done
elsewhere.
thanks,
BillBill
CREATE TABLE Companies
(
company_id INT NOT NULL PRIMARY KEY,
company_description VARCHAR(50) NOT NULL
.....
.....
)
CREATE TABLE Companies_Data
(
row_id INT NOT NULL PRIMARY KEY,
company_id INT NOT NULL REFERENCES Companies (company_id),
comp_activities VARCHAR(...) or INTEGER ( Depends on your business
requierements)
customer_id.....
.....
)
CREATE TABLE Customers
(
custoemid INT NOT NULL PRIMARY KEY,
custname VRACHAR(50) NOT NULL,
....
)
Look , the company may have more than one actvities , right? Consider
crerating an UNIQUE CONSTRAINT on these fields
> Or do I just use a different database per customer. I expect having 1000
> or more companies, so I don't think separate databases will fly.
Well, I have seen some systems that customers hosted their databases (with
costomer info) and just connected to the main server to make operations as
well as the main server has all info about customers. It is really depends
on your business requirements.
"Bill H" <bill@.somewhere.com> wrote in message
news:OwWFpNXTGHA.5900@.tk2msftngp13.phx.gbl...
>I am developing a web application that will host many companies' data in
>one database. And there will be the possibility that the company may
>decide to take their data at some point and host it themselves.
> So my question relates to the table structure and relationships. If a
> company decides to take their data, I want it to be as easy as pulling all
> data from all tables where Company_id = x. That said, let's just assume
> I'm going to have the Company_Id in all tables or most. Do I create a
> separate primary key field that's unique across all companies or unique
> within the company_id ? I like the idea of all companies starting at 1
> for their primary key IDs, but this also brings more complexity and
> inefficiencies to querying the database. Plus it means defining a two
> field unique index per table and having to base all relationships between
> tables on two fields. The joins are more complicated.
> I'm leaning toward using one primary key field across all companies to
> link tables, but still having the Company_id in all tables just to be able
> to extract the data easily if I ever need to. The Primary key field would
> not be an Auto ID, as this would pose problems when migrating existing
> data to a new database.
> Or do I just use a different database per customer. I expect having 1000
> or more companies, so I don't think separate databases will fly.
>
> Looking for your opinion and getting ideas about how things are being done
> elsewhere.
> thanks,
> Bill
>
Wednesday, March 7, 2012
Need info about odbc and sql through access2k
I recently started with a company that uses access for a front end and sql2000 for the back. They have set up an ODBC connection though access2k to reach sql2k. This works fine but fouls up in regards to the record locking. You can set record locking in access but it has no effect. In the office help it says 'data in a form, report, or query from an Open Database Connectivity (ODBC) database is treated as if the No Locks setting were chosen, regardless of the RecordLocks property setting.'
Also, the access lock option info says it 'could' lock the few records around the record you wish to lock.
So my question is this: How can i (without destroying this ODBC link from access2k to sql2k) get locks for individual records to work successfully?
any info would be great, thankshttp://advisor.com/doc/13043 for information about record locking in Access.
For SQL server http://www.sql-server-performance.com/reducing_locks.asp and
http://www.databasejournal.com/features/mssql/article.php/3289661
HTH
Also, the access lock option info says it 'could' lock the few records around the record you wish to lock.
So my question is this: How can i (without destroying this ODBC link from access2k to sql2k) get locks for individual records to work successfully?
any info would be great, thankshttp://advisor.com/doc/13043 for information about record locking in Access.
For SQL server http://www.sql-server-performance.com/reducing_locks.asp and
http://www.databasejournal.com/features/mssql/article.php/3289661
HTH
Subscribe to:
Posts (Atom)