Showing posts with label company. Show all posts
Showing posts with label company. Show all posts

Tuesday, March 27, 2012

Database Sizing Question

My company is currently setting up an application for a client that requires a Database server on the backend. We have selected SQL Server 2000 Enterprise as our DB but we are not sure how to size the hardware of the server. I have searched around and it appears that DB sizing is more experience/trial & error based than formula-based. I hope someone here can provide me with some advice if I provide the performance requirements of the server.

5 GB of data currently (will double in size every year) Approx. 10 million records (will double in size as well) 85,000 Transactions / hour 200 Concurrent users Client requires no greater than 1/2 second response time.

Questions:

How many CPU's should be needed? Why? How much RAM should be needed? Why?
RAID 10 was the recommended fault tolerance. Agree? Disagree?

Thanks in advance

JBAs you guessed there is no magical answer but here are some things to think about.

The actual size of the db is not too critical as long as you plan for growth and take into account how the drive arrays should be set up to achieve good performance. 85K per hour is less than 30 a second and although I wouldn't try that on a single processor box it is not too bad. (I routinely do over 800 a second with 4 Processors).

But since you need to handle Peak amounts and want less than 1 second response time you should be particularly aware of minimums. Ram will depend on how much of the data you actually use on a regular basis. You want to aim for enough ram to have all the data that you access on a regular basis in cache at one time leaving room for the procedure cache etc. Just because you have 5GB of data doesn't mean you will need 5GB of ram. You can always add ram if needed later just make sure you plan for growth ahead of time and save some ram slots so you don't have to throw away ram later to add more. It's always better to have more ram than not enough (don't forget to leave some for the OS too).

As for the hardwareI would shoot for the following:
RAID 1 (or 10) for the Log file(s).
RAID 5 or 10 for the data files (smaller and more disks vs less larger ones).
Raid 1 for the OS and SQL System files.

Depending on how much you will use tempdb (sorting etc) you may or may not want a separate Raid for tempdb.

If you do disk backups you may want to think about another RAID or 5 for the backups. In all cases make sure the arrays are expandable for future growth.

As for processors you will probably want to start with a 4 or 8 processor box with less than all the procs to begin with. The number of procs will depend on so many things but if you don't have enough you will probably see less than 1/2 second response time in peak loads while the rest of the time it will be fine. Poor code or schema is usually the reasons for needing more processors.

Andrewsql

Monday, March 19, 2012

database schema for HelpDesk application

hello
i have to make helpdesk application for teh IT Department of my company,so if anyone can help by supporting me by database schema for HelpDesk application.
thank you for the help

You can use the Asp.net 2.0 built in profile as one of the tables for a small application and use Trigger with fake time stamp to write to the incident table. The time stamp you need should be fake because the current SQL Server time stamp is a derived data type used by SQL Server. Another option is to use the database, tables and constraints from the ISSUE Tracker starter kit. Try the links below for the fake time stamp trigger and download the Issue tracker starter kit. Hope this helps.


http://forums.asp.net/832746/showpost.aspx

http://www.aspfaq.com/show.asp?id=2448

Database Schema Designing

Case 1:
A company is involved into e-commerce..hosting multiple websites for different products.

CAse 2:
The above scenario could also be implemented with a single site having multiple products for sale.

For Case 2 one would go for a single database for all the products.
While for CAse 1 ,a separate Database is developed for each Site.

What I fill is CAse2 is a more appropriate choice even if we have multiple sites for different products.

This would help us in rapid development of any ecommerce site...
ANd better ERP management for the Company.

I would appreciate some expert guidelines for the above scenario

Thanx in Advance
Warm Regards
GirijaUnless there is something to do with IIS (or whatever web server you are using) that would benefit from different databases, I would have all the data in one database, and have more than web site access the database.

Having all of your data in one database would allow provide much better information (not just straight forward data entry, but the trends you can find through relations, etc.) and make it easier to manage...in my oppinion. However, make sure that if you use one database (and these are high volume web sites) that you are writing your code carefully to avoid unneccessary record locks.

Just my oppinion...

Sunday, March 11, 2012

database reusability with sql server

Hi,

The company I work for often develops databases for clients. Those
databases always need to do similar tasks. One company might need a
database to do task 1 and task 2, while another company might need it
for task 2 and 3. Instead of having to redevelop each database from
scratch, I'd like to be able to develop several "modules" (kind of
mini-databases) representing each of this task, and then chose the
modules which are relevant. Then all I would have to develop would be
the final layer putting together all the information gathered from
those modules. I'm sure this has been done heaps of time before but I
can't really find any information on it. Any of you have any pointers
or experience you would like to share. I'd like to have a good think
about how I'm going to go about it, and the limitations that might
arise before I get stuck into it.

Thanks for your help,

MaelleYou could do this in several ways, depending on your exact requirements and
the tools you have available. Are you going to put all your modules in every
client database, for example, and only 'activate' the ones that client
really needs? Or do you plan to set up a different code base for each
client, based on a different selection of modules every time? (The second
approach could become very complex to manage to support.) Will you have a
common module with global data and code to be used in all other modules?
Etc.

Either way, if you want to 'modularize' your code, it's really up to you to
track dependencies carefully, and ensure you always know exactly which
objects go into each module. Some tools can help with this, e.g. Erwin
allows you to split your data model into functional areas, and you can view
them separately or together. In your source control system, you would group
related tables/procs/functions etc. together.

A good naming convention can also help, perhaps by giving related objects a
prefix to indicate that they are related. You will have to look at the
resources you have available, and how you want to manage code once it's been
deployed - it's difficult to give a more specific answer, as you know your
own application best.

Simon

"Maellic" <maellic2002@.yahoo.fr> wrote in message
news:15aa032f.0306231501.5af14872@.posting.google.c om...
> Hi,
> The company I work for often develops databases for clients. Those
> databases always need to do similar tasks. One company might need a
> database to do task 1 and task 2, while another company might need it
> for task 2 and 3. Instead of having to redevelop each database from
> scratch, I'd like to be able to develop several "modules" (kind of
> mini-databases) representing each of this task, and then chose the
> modules which are relevant. Then all I would have to develop would be
> the final layer putting together all the information gathered from
> those modules. I'm sure this has been done heaps of time before but I
> can't really find any information on it. Any of you have any pointers
> or experience you would like to share. I'd like to have a good think
> about how I'm going to go about it, and the limitations that might
> arise before I get stuck into it.
> Thanks for your help,
> Maelle|||Maellic (maellic2002@.yahoo.fr) writes:
> The company I work for often develops databases for clients. Those
> databases always need to do similar tasks. One company might need a
> database to do task 1 and task 2, while another company might need it
> for task 2 and 3. Instead of having to redevelop each database from
> scratch, I'd like to be able to develop several "modules" (kind of
> mini-databases) representing each of this task, and then chose the
> modules which are relevant. Then all I would have to develop would be
> the final layer putting together all the information gathered from
> those modules. I'm sure this has been done heaps of time before but I
> can't really find any information on it. Any of you have any pointers
> or experience you would like to share. I'd like to have a good think
> about how I'm going to go about it, and the limitations that might
> arise before I get stuck into it.

We are building our product in this fashion. Admittedly the core is still
the largest building stone, but the idea is that a customer that does
not need the XYZ functionality does neither get the tables or the stored
procedures for it.

We've found that this is fairly easy to do with new development, but
as I mentioned the core is fairly large, and if we ever manage to
break it up remains to see. The difficult thing is to get the
dependencies right. If you have modules A and B, you can permit
references in one direction, but not in two.

What we have put quite some effort in, is to have a tool that supports
this concept for installation and updates. I don't know if this is an
issue for you, but we regularly send new versions of our product to
our customers.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Saturday, February 25, 2012

Database Redundancy

My company has valuable data being constantly logged to a database 24/7. Actually, the data logs to two separate database systems in case one goes down for whatever reason such as power failure, routine maintenance, glitch, whatever. These two systems are housed at different physical locations.

We are planning to set up a third database system which polls the other two every minute. It compiles and mantains a deduped, clean, and complete collection of data from the other two database systems. If one of the first two systems goes down or misses a row, the third will automatically get the data from the other system. If this deduped database system goes down, we can rebuild the data from the other two.

Does this seem like the right route to take or is there a simpler or safer route available?Talking about safer is to pay the bill (add $50 administrative fee + 15% of outstanding balance per day past due). It's also simpler to present your data the way it should be presented, - SPAM targets, many of which are email addresses of many participants of this forum that would appreciate this SPAM to stop. Anybody else sees any value in this kind of data?|||Huh? SPAM? Administrative fees? What?|||rdjabarov seems to go ballistic every time RogerWilco posts... I'd write that one off as a lover's quarrel myself.

-PatP|||Well, that's a relief. I thought it was just me...|||Hmmm, interesting observations ;)

I don't go ballistic, I just hate when people like Kurt display that level of immaturity by trying to avoid to pay for the services that were provided.

I went out of my way, thinking that if someone needs help I will go an extra mile just because I am that kind of person.

His case was not disasterous, he is a smart guy, a very good programmer, but definitely lacks maturity and ethics when it comes to business.

He continues showing up here and hopes for a free help while capitalizing on strategy that I presented to him, for which he should pay, right? Or am I the only one thinking that our services (if asked for) are not free? After all, that WAS the initial agreement...|||So you had a business agreement with him? What's the SPAM stuff all about?|||I don't go ballistic, I just hate when people like Kurt display that level of immaturity by trying to avoid to pay for the services that were provided.I'd say that was open to debate. ;)
He continues showing up here and hopes for a free help while capitalizing on strategy that I presented to him, for which he should pay, right? Or am I the only one thinking that our services (if asked for) are not free? After all, that WAS the initial agreement...That might well be another story. I don't have any clue about what private agreements might exist between you two, so I can't comment on them. All I know is that everytime RogerWilco posts, it appears to me that you promptly go off the deep end.

I simply don't respond to RogerWilco's posts anymore. I don't need the aggrivation.

-PatP|||So you had a business agreement with him? What's the SPAM stuff all about?That's what his "company" does, - SPAM. His "valueable" data is millions and millions of people's personal info, including emails and addresses, that they are getting from somewhere. As I said, he is a very good programmer, writes his own parsing and processing engines (C++), and manages to get by with more-or-less decent T-SQL stuff. When he asked for help I spent most of the weekend visiting his site and presenting to him some conceptual changes in his infrastructure that will facilitate more efficient processing of this huge bulk of data. Before and shortly after my visit I received about a dozen of emails from him discussing all that, and after the visit received a "thank you" note from him and his boss, saying that "the payment will be made promptly."

Well, my question to him is "WHERE THE HECK IS IT?"

And Pat, if you can't comment (as you wrote yourself), - then DON'T I definitely didn't ask for your opinion. :cool:|||And Pat, if you can't comment (as you wrote yourself), - then DON'T I definitely didn't ask for your opinion. :cool:I don't believe that I commented on your private arrangements, only on my perception of your public (on this forum) behavior.

-PatP|||I am afraid I didn't ask for your perception either. Making the first post for the guy's question and asking him to pay his bill is not indicative of "public behavior" you're incinuating.|||rdjabarov, i can understand how you feel, i've been stiffed before too

however, this site is not the place for you to raise the issue

anybody else besides me feel that it would be a good idea to purge the whole thread?

somebody, please, click on that little exclamation mark, and it's gone...|||OK, I am all for it, just don't be so eager to answer his questions. He can actually fake his way through whatever problems he has on his own ;)|||Ok, you can call him spammer scum, but DUDE! YOU HELPED HIM!

I can see why you want to get paid. Full-body disinfection can't be cheap. Then there is the truckload of margaritas required to get the bad taste out of your mouth, not to mention paying a monastery full of monks to pray your soul out of purgatory. Cost of business, sure, but it all has to add up...|||Robert!! You have my email and cell phone number and until now you never mentioned payment problems or any dissatisfaction in dealing with me. You really should have mentioned this to me in private rather than like this on a public forum.

I emailed our accountant to arrange payment for 8 hours worth of work (this was all several months ago); if that didn't happen I assure you it was a purely unintentional mistake and it will be corrected PROMPTLY!

I'm going to continue this discussion with you in private as it's really inappropriate here.

I'd like to stress that I really do appreciate the help that I get here. This forum is designed on a free basis but honestly much of the help that I get here is very valuable and would be worth paying for. If I owned the business I would give bounties on every useful answer I got but it's hard to sell those kinds of idead to my boss.

It's not that I'm not willing to pay for help; I work in a technical architect/team lead role. Getting management approval for funds for outside help is difficult even though I am a strong proponent of doing so.

Hmmm, interesting observations ;)

I don't go ballistic, I just hate when people like Kurt display that level of immaturity by trying to avoid to pay for the services that were provided.

I went out of my way, thinking that if someone needs help I will go an extra mile just because I am that kind of person.

His case was not disasterous, he is a smart guy, a very good programmer, but definitely lacks maturity and ethics when it comes to business.

He continues showing up here and hopes for a free help while capitalizing on strategy that I presented to him, for which he should pay, right? Or am I the only one thinking that our services (if asked for) are not free? After all, that WAS the initial agreement...|||I simply don't respond to RogerWilco's posts anymore. I don't need the aggrivation.

-PatP

May I ask why? You certainly don't owe me anything but I very much appreciate the valuable assistance of you, blindman, Robert, Brett, and several others and I hate to lose it.|||OK, all is well, all misunderstanding (and that's what it was) is gone, let's forget about harsh words and inappropriate remarks, and move on. I know how hard it is to do your job AND keep track of things that should have bee a direct responsibility of someone else. But it looks like it has been resolve, so let's...move on, as I said ;)

Going back to the original question, - I would recommend to set up a custom log shipping (fancy word for applying transaction log dumps on a scheduled basis while having a standard edition even if EE is already invested into). Implementing this guarantees simplicity of the architecture and timely consistency of the data.|||I forgive you too, spammer-scum.

Robert, you are hoot! I am literally Rolling On The Floor Laughing My Ass Off right now...|||and the winner is .... RdJabarov by a nose.

and the spammer? well y'all gotta tune in next week just to see what happens.|||I forgive you too, spammer-scum.

Robert, you are hoot! I am literally Rolling On The Floor Laughing My Ass Off right now...Hey, I do give a damn, but I also hope you enjoyed yourself (hopefully translated into one less offensive post from you)|||and he comes back out swinging!

Friday, February 24, 2012

Database record disappear

I has a strange question.
My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY? Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times.
If the transaction has NOT YET committed, but you are in code AFTER the
insert, you will get good results from @.@.error @.@.identity. But then later in
the same transaction, you are aborted the row will be removed...A deadlock
error would look like this, a batch abort would look like this also...
Use Profiler to see if the transaction is being aborted... Also look for
Erland Sommerskog on Google.. His web site has the best information availabe
about aborts...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"salamol" <salamol@.hotmail.com> wrote in message
news:chm1au$bh4@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY? Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.
>

Database record disappear

I has a strange question.
My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY? Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times.
please show us your exact stored procedure code.
thanks.
"salamol" <salamol@.hotmail.com> wrote in message
news:chm1an$qp31@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY? Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.
>

Database record disappear

I has a strange question.

My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY?? Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times."salamol" <salamol@.hotmail.com> wrote in message
news:chm1a4$bb2@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY?? Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.

My guess is something is causing the transaction to rollback. Best bet is
to post the code, DDL and repro script if at all possible.

Remember, @.error gets set after every statement, not just at the end.
|||On Wed, 8 Sep 2004 12:20:12 +0800, salamol wrote:

>I has a strange question.
>My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
>The system is busy and handle a lot of SELECTs and INSERTs all the time.
>Sometimes, some transactions are blocked by some other transactions.
>For those INSERT transactions, we usually call a stored procedure and pass
>parameters in to do the INSERT, at the end of the INSERT stored procedure,
>we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
>auto number primary key) to confirm the INSERT is success. But the strange
>things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
>@.@.IDENTITY but when I fetch the record by the primary key (the value of
>@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
>key is skipped! I found that this happens usually when the INSERT execute
>at the time when some other transactions are blocking. Anyone knows why the
>record is disappear while @.ERROR = 0 and the stored procedure can return
>value from @.@.IDENTITY?? Anyone has such case happen in their server as
>well? Please tell me some solutions on how to solve this, thank you x
>10000000 times.

Hi salamol,

If you were using SQL Server 2000, I'd advise you to use SCOPE_IDENTITY
instead. Unfortunately, @.@.IDENTITY gives you the last identity value used,
not limited to the current scope. I guess the scenario is something like
this:

* Connection 1 starts an insert.
* While connection 1 is processing, connection 2 initiates an insert as
well. This one is blocked.
* When connection 1's insert is finished, connection 2's insert starts.
* When processing of connection 1 reaches the statement where @.@.IDENTITY
is retrieved, the insertion of connection 2 is already finished and the
@.@.IDENTITY value returned to connection 1 will be for conenction 2's row.
* After that, connection 2 has to do a rollback. There now no longer is a
row with the @.@.IDENTITY value saved by conneciton 1 (that actually
belonged to connection 2).

Since you don't have SQL Server 2000, you can't use SCOPE_IDENTITY
instead. Another alternative is to read back the data. Your table should
have a column (or combination of columns) that form the natural key for
the data in the table. Use this in a WHERE clause to SELECT the identity
value assigned to the row just inserted.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> * Connection 1 starts an insert.
> * While connection 1 is processing, connection 2 initiates an insert as
> well. This one is blocked.
> * When connection 1's insert is finished, connection 2's insert starts.
> * When processing of connection 1 reaches the statement where @.@.IDENTITY
> is retrieved, the insertion of connection 2 is already finished and the
> @.@.IDENTITY value returned to connection 1 will be for conenction 2's row.

Sorry Hugo, but this is plain wrong. @.@.identity is global to the connection,
not global to the server. This cannot happen.

What can happen is that you insert a row into a table and that table has
a trigger which also inserts a row into a table with the IDENTITY property.
In this case @.@.identity will return the value of the row into the second
table, and you have no good way of getting the value for the first table.

In SQL7 RTM there was also a bug which, if memory serves, caused @.@.identity
to be NULL, if your trigger inserted into a non-identity table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||salamol (salamol@.hotmail.com) writes:
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions. For
> those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored
> procedure, we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it
> is usually the auto number primary key) to confirm the INSERT is
> success. But the strange things is, sometimes, @.@.ERROR is equals to 0
> and I can get a value from @.@.IDENTITY but when I fetch the record by the
> primary key (the value of @.@.IDENTITY) it returns nothing!! The record
> is disappear and the primary key is skipped! I found that this happens
> usually when the INSERT execute at the time when some other transactions
> are blocking. Anyone knows why the record is disappear while @.ERROR = 0
> and the stored procedure can return value from @.@.IDENTITY?? Anyone has
> such case happen in their server as well? Please tell me some solutions
> on how to solve this, thank you x 10000000 times.

There is just far too little information to say something really useful.

But just checking @.@.error and @.@.identity is not enough. If you have
transactions they can be rolled back, for instance because of deadlock.

Also, a nasty thing which may happen in a busy system which is not properly
implemented is that blocking leads to clients timing out. Now, assume that
a client calls a stored procedure, which starts a transaction, and after
30 seconds the default timeout of the client sets in, so the query is
cancelled. This does *not* rollback the transaction! This means that the
client may job along with the transaction and everything looks fine -
and then, there is a deadlock or some other error which cancels the batch,
and everything since 10 o'clock this morning is rolled back.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The problem I am facing is neither cannot get an identity (identity not
null) nor getting a wrong identity.
the server returns a correct identity value which should belongs to the
record that I just insert, but when I fetch the record by that identity
value, the record is missing
i.e. the identity for the last record is 100, and I do an insert, the system
return 101 as @.@.identity and 0 as @.@.Error, however if i select the record
from 95 - 105 it will returns records 95, 96, 97, 98, 99, 100, 102, 103,
104, 105 (i.e. 102-105 is other successfully insert after 101)

"Erland Sommarskog" <esquel@.sommarskog.se> ?
news:Xns955F4D9B18C7Yazorman@.127.0.0.1 ?...
> Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> > * Connection 1 starts an insert.
> > * While connection 1 is processing, connection 2 initiates an insert as
> > well. This one is blocked.
> > * When connection 1's insert is finished, connection 2's insert starts.
> > * When processing of connection 1 reaches the statement where @.@.IDENTITY
> > is retrieved, the insertion of connection 2 is already finished and the
> > @.@.IDENTITY value returned to connection 1 will be for conenction 2's
row.
> Sorry Hugo, but this is plain wrong. @.@.identity is global to the
connection,
> not global to the server. This cannot happen.
> What can happen is that you insert a row into a table and that table has
> a trigger which also inserts a row into a table with the IDENTITY
property.
> In this case @.@.identity will return the value of the row into the second
> table, and you have no good way of getting the value for the first table.
> In SQL7 RTM there was also a bug which, if memory serves, caused
@.@.identity
> to be NULL, if your trigger inserted into a non-identity table.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Wed, 8 Sep 2004 22:30:07 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> * Connection 1 starts an insert.
>> * While connection 1 is processing, connection 2 initiates an insert as
>> well. This one is blocked.
>> * When connection 1's insert is finished, connection 2's insert starts.
>> * When processing of connection 1 reaches the statement where @.@.IDENTITY
>> is retrieved, the insertion of connection 2 is already finished and the
>> @.@.IDENTITY value returned to connection 1 will be for conenction 2's row.
>Sorry Hugo, but this is plain wrong. @.@.identity is global to the connection,
>not global to the server. This cannot happen.
>What can happen is that you insert a row into a table and that table has
>a trigger which also inserts a row into a table with the IDENTITY property.
>In this case @.@.identity will return the value of the row into the second
>table, and you have no good way of getting the value for the first table.
>In SQL7 RTM there was also a bug which, if memory serves, caused @.@.identity
>to be NULL, if your trigger inserted into a non-identity table.

Hi Erland,

Thanks, Erland, for putting me straight. And apologies to the OP for the
confusion I may have caused. I guess it shows that I hardly use IDENTITY
in my own code...

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||salamol (salamol@.hotmail.com) writes:
> The problem I am facing is neither cannot get an identity (identity not
> null) nor getting a wrong identity. the server returns a correct
> identity value which should belongs to the record that I just insert,
> but when I fetch the record by that identity value, the record is
> missing i.e. the identity for the last record is 100, and I do an
> insert, the system return 101 as @.@.identity and 0 as @.@.Error, however if
> i select the record from 95 - 105 it will returns records 95, 96, 97,
> 98, 99, 100, 102, 103, 104, 105 (i.e. 102-105 is other successfully
> insert after 101)

If you have received 101 in @.@.identity for an inserted row, and the row
is later nowhere to be found, the insertion of the row has been rolled
back, as I discussed in my other post.

While you may already be aware of this, I like to point out that it lies
in the concept of the IDENTITY property that you never can trust the numbers
to be contiguous. If you attempt to insert a row into a table, and that
insertion fails for whatever reason, you have still consumed a number.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, so how can I check if the insertion is success or not?
should I fetch for that record again immediate after the insert?

"Erland Sommarskog" <esquel@.sommarskog.se> ?
news:Xns955F72B0A42D0Yazorman@.127.0.0.1 ?...
> salamol (salamol@.hotmail.com) writes:
> > The problem I am facing is neither cannot get an identity (identity not
> > null) nor getting a wrong identity. the server returns a correct
> > identity value which should belongs to the record that I just insert,
> > but when I fetch the record by that identity value, the record is
> > missing i.e. the identity for the last record is 100, and I do an
> > insert, the system return 101 as @.@.identity and 0 as @.@.Error, however if
> > i select the record from 95 - 105 it will returns records 95, 96, 97,
> > 98, 99, 100, 102, 103, 104, 105 (i.e. 102-105 is other successfully
> > insert after 101)
> If you have received 101 in @.@.identity for an inserted row, and the row
> is later nowhere to be found, the insertion of the row has been rolled
> back, as I discussed in my other post.
> While you may already be aware of this, I like to point out that it lies
> in the concept of the IDENTITY property that you never can trust the
numbers
> to be contiguous. If you attempt to insert a row into a table, and that
> insertion fails for whatever reason, you have still consumed a number.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||salamol (salamol@.hotmail.com) writes:
> OK, so how can I check if the insertion is success or not?
> should I fetch for that record again immediate after the insert?

I have to ask a question: have you read my discussion about transaction
being rolled back? You seem to be obsessed with the problem that the
insertions may fail. But if insertion is successful does not help, if
the transaction is rolled back later.

It is difficult to add more to the subject, unless you show us some of the
code you are having problems with.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,

I experienced data disappear. Four tables with a 1-n relationship are
heavily updated and are target of some big queries.

Cenario:
Table A with ~3.000 rows sometimes rollback updates
Table B with ~5.000 rows sometimes rollback updates and inserts
Table C with ~60.000 rows have several rollbacked inserts
Table D with ~2.500.000 rows is havely rollbacked

The workflow:
1) Users inserts a row at table A
2) Users inserts one or more rows at B
3) Users inserts lots of rows at C
4) Users inserts lots of rows at D
5) Users updates D
6) Users updates B and C
7) Users updates A and prints a report with all content from the four
tables concerning with the row from A
8) Minutes or hours later users checks for that report and updates A
9) Days later users print a big report from all month data

Problem:
At steps 8) and 9) users tell some rows from some tables are vanished!

Details:
All update, delete and insert are performed by Stored Procedures.
Some selects are performed by no indexed views.
Some selects use more than 12 joins!
Reports are printed before the data vanished.
Time to data disapear (at users discretion) varies from minutes to days.
Each insert, each update, each delete for EACH ROW, is a explicit
transaction.
Im using MSSQLServer 2000 last sp and Windows Server 2000.
The server and users lies at the same network, the users uses a client
application (not web).
The users app connects with BDE but i coded a newer version with ADO
but this not worked.
All worked fine for sometime but sundenly this problem appears.
Users tell the loss of data is harder now the loss is growing.
The LAN posses 2 hub is slowdown from 100MB to 10MB all network (the
number of users is constantly growing).
All server memory is used, some locks take too long some deadlocks
errors arise.
I put a trigger to make sure any insert/update at the problematic tables
inputs a row at a dummy table (since i have the report i can search for
the row) no missing row are found at the dummy table.
Some triggers make a complex validations at some inserts/tables and
roolbacks the transaction, but when tested these triggers works fine.
I run checkdb, rebuild indexes checked realtionships, stored procedures,
application, triggers, etc.

Conclusions:
We need a better server with a lot more memory and a rebuild at the
network. ;)
This problem can be from a deadlock for sources like
memory+network+diskIO and the SQLServer cannot dont handle it.
The locks for sources are handled by windows but maybe the OS failed to
comunicate deadlocks problems to SQLserver.

Workaround:
I activated de server log to write any deadlock, timeout is -1(infinite)
a new server (and switches) are arriving shortly.
I hope this solves the problem. Someone experienced data loss like it?

Jean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Database record disappear

I has a strange question.
My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
The system is busy and handle a lot of SELECTs and INSERTs all the time.
Sometimes, some transactions are blocked by some other transactions.
For those INSERT transactions, we usually call a stored procedure and pass
parameters in to do the INSERT, at the end of the INSERT stored procedure,
we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
auto number primary key) to confirm the INSERT is success. But the strange
things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
@.@.IDENTITY but when I fetch the record by the primary key (the value of
@.@.IDENTITY) it returns nothing!! The record is disappear and the primary
key is skipped! I found that this happens usually when the INSERT execute
at the time when some other transactions are blocking. Anyone knows why the
record is disappear while @.ERROR = 0 and the stored procedure can return
value from @.@.IDENTITY' Anyone has such case happen in their server as
well? Please tell me some solutions on how to solve this, thank you x
10000000 times.If the transaction has NOT YET committed, but you are in code AFTER the
insert, you will get good results from @.@.error @.@.identity. But then later in
the same transaction, you are aborted the row will be removed...A deadlock
error would look like this, a batch abort would look like this also...
Use Profiler to see if the transaction is being aborted... Also look for
Erland Sommerskog on Google.. His web site has the best information availabe
about aborts...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"salamol" <salamol@.hotmail.com> wrote in message
news:chm1au$bh4@.imsp212.netvigator.com...
> I has a strange question.
> My company is using a old system with Win NT 4.0 Server + MS SQL 7.0.
> The system is busy and handle a lot of SELECTs and INSERTs all the time.
> Sometimes, some transactions are blocked by some other transactions.
> For those INSERT transactions, we usually call a stored procedure and pass
> parameters in to do the INSERT, at the end of the INSERT stored procedure,
> we always check @.@.ERROR = 0 and retrieve the @.@.IDENTITY (it is usually the
> auto number primary key) to confirm the INSERT is success. But the
strange
> things is, sometimes, @.@.ERROR is equals to 0 and I can get a value from
> @.@.IDENTITY but when I fetch the record by the primary key (the value of
> @.@.IDENTITY) it returns nothing!! The record is disappear and the primary
> key is skipped! I found that this happens usually when the INSERT execute
> at the time when some other transactions are blocking. Anyone knows why
the
> record is disappear while @.ERROR = 0 and the stored procedure can return
> value from @.@.IDENTITY' Anyone has such case happen in their server as
> well? Please tell me some solutions on how to solve this, thank you x
> 10000000 times.
>

Sunday, February 19, 2012

Database query performance

I have a database table. The table has number of fields. Out of those fields one is Company and
another is DateTime. The table has thousands of records. I want to get the most recent
record for each company. In order to do that I am using the following query

SELECT * from CompanyDetail AS X
WHERE [DateTime]=(SELECT max([DateTime]) FROM CompanyDetail WHERE Company=X.Company)
ORDER BY Company


Note: There is only one record exists for the given company on a given date

The problem is that this query is very slow. Am I doing something wrong or there could be another
alternative way to improve it ?

Thanks in advance
KDV


Your best bet is to ask this question in the Transact-SQL forum:
http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=85

David Sceppa
Microsoft

Friday, February 17, 2012

Database patching.

Hi Everyone.
The company that I work for have a database, which needs patching every so
often from one version to the next. We've been trying different methods of
upgrading the database from one version to the next. But each idea that
we've tried has been difficult, clunky or just plain stressful. Anyway, in
order to ease the process of patching both Schema and data there must be a
way of doing this which is painless and easy to accomplish.
Does anyone have any suggestions that are worth checking out?
Regards
Colin Dawson
www.cjdawson.com"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:hzWLf.26203$wl.25912@.text.news.blueyonder.co.uk...

> Does anyone have any suggestions that are worth checking out?
Not too sure what your problem is... I do this sort of thing all the time
with nothing more than T-SQL. The only thing you need to be careful of is
making the schema and/or data modifications in the right order...|||Colin Dawson wrote:
> Hi Everyone.
> The company that I work for have a database, which needs patching every so
> often from one version to the next. We've been trying different methods
of
> upgrading the database from one version to the next. But each idea that
> we've tried has been difficult, clunky or just plain stressful. Anyway,
in
> order to ease the process of patching both Schema and data there must be a
> way of doing this which is painless and easy to accomplish.
> Does anyone have any suggestions that are worth checking out?
> Regards
> Colin Dawson
> www.cjdawson.com
What is your existing process and what exactly is the problem? For
incremental fixes one might expect:
- Unit-tested change scripts checked into source control
- Release script compiled from an amalgamation of the change scripts
- Release to a test environment for system and regression testing
- Release to a UAT environment (to include testing by production
support if appropriate)
- Release to production
If you need help compiling and validating the scripts then check out
some software like the RedGate compare tools: www.red-gate.com
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi all.
Just to elaborate a little, I'm an architech for a team of 20 developers.
They're always making stored procedure changes and schema changes, basically
the problem is that it's happening so fast that I'm not able to keep up.
What I want to be able to do is skip checking, diagnosing every single
little change, and pile them into one large change script which I can then
check quicker.
Ideally, I really suppose that I'm looking for some kind of Silver Bullet.
I know that there is rarely anything that will solve problems like this.
But there's always hope.
Regards
Colin Dawson
www.cjdawson.com

Tuesday, February 14, 2012

database ownership question

Our SQL Server DBA has left the company with many of the databases owned by his domain user. I understand that I could change database ownership using sp_changedbowner. Does anyone know what would be a better option, from security point of view?

1. Let databases owned by 'sa' like all system databases.
2. Let databases owner by the domain account that runs SQL Service.
3. Create a local/domain account which owns all databases on this SQL Server instance.
4. Let databases owned by Administrator account.

I am new to SQL Server. Help would be appreciated.Howdy

'sa' is the best way to go.

By all means, set up SQL & SQL Agent to run using a domain account unless it needs to be a highly secure box.

By the way, any account who is in the local Administrators group ( this is NT security I'm talking about ) on the box, is automatically sa ( potential LARGE security hole ).

Post back if any more questions. Happy to answer.

Cheers

SG|||Thanks for the info, will try that.