developer.* Blogs - Microsoft SQL Server - Comments

auto statistics = ON

damdave1 — Thu, 15 Feb 2007 07:20:07 -0800

Auto update/create statistics where ON and this fix worked for me. Thanks for posting it.

Also check out a related

Guest — Wed, 24 Jan 2007 23:09:10 -0800

Also check out a related post, "Hard Coded Limits Considered Harmful," on my blog.

Because there is an alternative to the hard coded values present in the functions? I don't see one...

Source of row loss

aroggeband — Tue, 23 Jan 2007 12:23:48 -0800

If you open an ADO recordset on a view or select that includes the table, any deletion can result in a cascaded delete on joined tables, even if the view is "not updateable". Try it in Enterprise Manager. MS access also does this with linked tables.

thanks

Guest — Sat, 30 Sep 2006 08:26:21 -0700

just exactly what i'm looking for. very, very useful info. not really easy to find it in the db docs

SQL Jobs

Steve — Tue, 29 Aug 2006 09:12:58 -0700

Have any of you guys tried sqlSentry? www.sqlsentry.net It sounds exactly like what you are talking about. It gives a color coded caldendar view of all the jobs and DTS packages on the server for successes and failures. It even sends emails on more conditions than I could even think up. We tried it at my job and management even loves the look of it. I hope they make the purchase. This thing will save hours of troubleshooting.

Hard Coded Limits

Edward G Nilges — Wed, 16 Aug 2006 21:13:19 -0700

To clarify Dan's comment, SQL Server ints can be up to 10 characters.

Also check out a related post, "Hard Coded Limits Considered Harmful," on my blog.

Edward

Cool!

Guest — Wed, 16 Aug 2006 06:34:56 -0700

Nice function. Thanks.

Length not issue for us

Mike — Fri, 04 Aug 2006 13:46:45 -0700

Our member numbers are all 6 characters, so I don't need to worry about anything larger. Infact our lowest numbers are still 3 digits so I could simply append 3 zeros to the leading edge.

I never considered creating a function, this is just a quick fix from our new database system to feed the web SQL box until we get the new system completely up and running.

Looks Like a Viable Solution

Daniel Read — Fri, 04 Aug 2006 13:43:11 -0700

I can't say whether or not this is particularly inefficient, but it looks like it should work fine as long as your company ID stays bellow 1,000,000. I believe a SQL Server int (> 0) can be up to 9 characters (?). Beyond that, though, I can't see anything wrong with it on the surface, especially if it were generalized into it's own function that took an int argument and returned a string. If you use this technique inline, it's less communicative than a function call would be.

Assuming you removed the limit on the 6 characters and generalized it into a function, it would be interesting to compare the two solutions for performance. Thanks for contributing this alternate solution.

Dan

Anything wrong with my method

Mike — Fri, 04 Aug 2006 13:05:36 -0700

I needed an int converted into a varchar of 6 characters with leading zeros. Add 6 zeros to the left of the converted int, then just take the 6 characters on the right.

RIGHT('000000' + CAST(dbo.Company.ID AS VARCHAR), 6) AS ID

Is this terribly ineffecient?

LPAD and RPAD

Guest — Mon, 03 Jul 2006 11:58:22 -0700

the RPAD and LPAD functions in SQL Server are very clever and appear to work perfectly. This is for non-commercial work, but I have given you credit in the code. Thanks!

scott
Prairie Heart Institute

Yet another feature?

Mario Van Damme — Tue, 20 Jun 2006 04:31:15 -0700

The 'Auto create statistics' and 'Auto update statistics' look very harmless seen from a distance. Naturally, why would you want to assist the RDBMS in doing its work? One important reason though is performance : If your database server is not or less used after business hours, it is better to schedule the creation and update of the statistics manually.
This way, performance is not hurt when these statistics are being created.

For the record : I don't think you have to turn off the auto creation and update of the statistics, but internally the auto-creation feature will run against the same problem. This implies ofcourse that the optimizer will no longer create statistics for new columns for which no statistical data is available.

thanks

Patrick — Fri, 16 Jun 2006 10:58:17 -0700

Excellent function. Worked like a charm.

Distribution List

Daniel Read — Wed, 07 Jun 2006 10:15:07 -0700

Hi. Hopefully I can help: what I meant was that we created a distribution list on the Exchange server itself. This is simply an email account that you create on your Exchange server, like for example sql_errors@yourcompany.com. Then configure this address in Exchange to route any messages to however many email addresses you choose.

Managing this centrally in Exchange is preferrable to trying to keep up with all the different jobs and configuration points that have these kinds of notification email addresses. Even if you only have one person who is going to receive the notifications, I would still suggest creating an "alias" email address so that when employees come and go all you have to do is update the Exchange settings.

Hope that helps,
Dan

SQL Server Job Success, Failure, and Everything In-Between

Guest — Wed, 07 Jun 2006 09:05:53 -0700

Do you mind a basic question from a DBA new to Sql Server? I tried to get a job schedule's notifications set to an Outlook distribution list but wasn't able to get it to work. I noticed you said "I try to increase redundancy by using distribution lists for the emails rather than a single email address". Can you tell me what the secret is for creating a distribution list that the job scheduler will recognize as an operator?