Outlook and SQL2000

I am having some issues with our security department in regards to having to have outlook on the SQL server. Basically their afraid the server can be comprimised through Outlook.
Does anyone have any thoughts on this? I tried another program STMPT but found it hard to setup and use. Basically it I can no longer use the alerts I created in SQL.
We use XP_SMTP_SendMail
http://www.aspfaq.com/2403
http://www.aspfaq.com/
(Reverse address to reply.)
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:8DBF7620-3465-430C-B0B2-A1E48F755C97@.microsoft.com...
> I am having some issues with our security department in regards to having
to have outlook on the SQL server. Basically their afraid the server can be
comprimised through Outlook.
> Does anyone have any thoughts on this? I tried another program STMPT but
found it hard to setup and use. Basically it I can no longer use the alerts
I created in SQL.
|||robert_at_cbb,
Well, outlook doesn't have to be running for you to be able to use
SQLMail. The server will be logged off. If you restrict who can log on
to the server console, and those people can be trusted not to abuse
Outlook, then I can't see a problem.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
robert_at_cbb wrote:
> I am having some issues with our security department in regards to having to have outlook on the SQL server. Basically their afraid the server can be comprimised through Outlook.
> Does anyone have any thoughts on this? I tried another program STMPT but found it hard to setup and use. Basically it I can no longer use the alerts I created in SQL.
|||Mark,
One of the issues I had on installing outlook on my servers is that some of them are local accounts only. HOw would I go about using outlook without creating a domain local admin from being logged in all the time?
"Mark Allison" wrote:

> robert_at_cbb,
> Well, outlook doesn't have to be running for you to be able to use
> SQLMail. The server will be logged off. If you restrict who can log on
> to the server console, and those people can be trusted not to abuse
> Outlook, then I can't see a problem.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> robert_at_cbb wrote:
>
|||robert_at_cbb ,
That's my point, you don't need to be logged on when using MAPI (the
Outlook client). See the articles below for information on how to set it up.
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
HOW TO: Troubleshoot SQL Mail with Microsoft Exchange Server
http://support.microsoft.com/?id=153159
You've got mail - or have you?
http://www.allisonmitchell.com/Articles/mail.htm
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
robert_at_cbb wrote:[vbcol=seagreen]
> Mark,
> One of the issues I had on installing outlook on my servers is that some of them are local accounts only. HOw would I go about using outlook without creating a domain local admin from being logged in all the time?
> "Mark Allison" wrote:
>
|||Thank you Mark. I read a few of the articles and found that I need to have a domain account if I use exchange which I do. Then the article went on to describe a way to create a mapi profile without installing outlook. Can I use this? I run sql2000 on a wi
n2000 server.
"Mark Allison" wrote:

> robert_at_cbb ,
> That's my point, you don't need to be logged on when using MAPI (the
> Outlook client). See the articles below for information on how to set it up.
> INF: How to Configure SQL Mail
> http://support.microsoft.com/?id=263556
> INF: Frequently Asked Questions - SQL Server - SQL Mail
> http://support.microsoft.com/?id=311231
> INF: Common SQL Mail Problems
> http://support.microsoft.com/?id=315886
> HOW TO: Troubleshoot SQL Mail with Microsoft Exchange Server
> http://support.microsoft.com/?id=153159
> You've got mail - or have you?
> http://www.allisonmitchell.com/Articles/mail.htm
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> robert_at_cbb wrote:
>
|||robert_at_cbb,
I've never tried it without Outlook. Some people have and it works for
them, but I think it tends to be more problematic than having Outlook
installed.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
robert_at_cbb wrote:
> Thank you Mark. I read a few of the articles and found that I need to have a domain account if I use exchange which I do. Then the article went on to describe a way to create a mapi profile without installing outlook. Can I use this? I run sql2000 on a
win2000 server.[vbcol=seagreen]
> "Mark Allison" wrote:
>

Outlook and SQL2000

I am having some issues with our security department in regards to having to
have outlook on the SQL server. Basically their afraid the server can be co
mprimised through Outlook.
Does anyone have any thoughts on this? I tried another program STMPT but fou
nd it hard to setup and use. Basically it I can no longer use the alerts I c
reated in SQL.We use XP_SMTP_SendMail
http://www.aspfaq.com/2403
http://www.aspfaq.com/
(Reverse address to reply.)
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:8DBF7620-3465-430C-B0B2-A1E48F755C97@.microsoft.com...
> I am having some issues with our security department in regards to having
to have outlook on the SQL server. Basically their afraid the server can be
comprimised through Outlook.
> Does anyone have any thoughts on this? I tried another program STMPT but
found it hard to setup and use. Basically it I can no longer use the alerts
I created in SQL.|||robert_at_cbb,
Well, outlook doesn't have to be running for you to be able to use
SQLMail. The server will be logged off. If you restrict who can log on
to the server console, and those people can be trusted not to abuse
Outlook, then I can't see a problem.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
robert_at_cbb wrote:
> I am having some issues with our security department in regards to having
to have outlook on the SQL server. Basically their afraid the server can be
comprimised through Outlook.
> Does anyone have any thoughts on this? I tried another program STMPT but found it
hard to setup and use. Basically it I can no longer use the alerts I created in SQL.
|||Mark,
One of the issues I had on installing outlook on my servers is that some of
them are local accounts only. HOw would I go about using outlook without cre
ating a domain local admin from being logged in all the time?
"Mark Allison" wrote:

> robert_at_cbb,
> Well, outlook doesn't have to be running for you to be able to use
> SQLMail. The server will be logged off. If you restrict who can log on
> to the server console, and those people can be trusted not to abuse
> Outlook, then I can't see a problem.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> robert_at_cbb wrote:
>|||robert_at_cbb ,
That's my point, you don't need to be logged on when using MAPI (the
Outlook client). See the articles below for information on how to set it up.
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
HOW TO: Troubleshoot SQL Mail with Microsoft Exchange Server
http://support.microsoft.com/?id=153159
You've got mail - or have you?
http://www.allisonmitchell.com/Articles/mail.htm
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
robert_at_cbb wrote:[vbcol=seagreen]
> Mark,
> One of the issues I had on installing outlook on my servers is that some o
f them are local accounts only. HOw would I go about using outlook without c
reating a domain local admin from being logged in all the time?
> "Mark Allison" wrote:
>|||Thank you Mark. I read a few of the articles and found that I need to have a
domain account if I use exchange which I do. Then the article went on to de
scribe a way to create a mapi profile without installing outlook. Can I use
this? I run sql2000 on a wi
n2000 server.
"Mark Allison" wrote:

> robert_at_cbb ,
> That's my point, you don't need to be logged on when using MAPI (the
> Outlook client). See the articles below for information on how to set it u
p.
> INF: How to Configure SQL Mail
> http://support.microsoft.com/?id=263556
> INF: Frequently Asked Questions - SQL Server - SQL Mail
> http://support.microsoft.com/?id=311231
> INF: Common SQL Mail Problems
> http://support.microsoft.com/?id=315886
> HOW TO: Troubleshoot SQL Mail with Microsoft Exchange Server
> http://support.microsoft.com/?id=153159
> You've got mail - or have you?
> http://www.allisonmitchell.com/Articles/mail.htm
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> robert_at_cbb wrote:
>|||robert_at_cbb,
I've never tried it without Outlook. Some people have and it works for
them, but I think it tends to be more problematic than having Outlook
installed.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
robert_at_cbb wrote:
> Thank you Mark. I read a few of the articles and found that I need to have a domai
n account if I use exchange which I do. Then the article went on to describe a way t
o create a mapi profile without installing outlook. Can I use this? I run sql2000 on
a
win2000 server.[vbcol=seagreen]
> "Mark Allison" wrote:
>

Outlook 2003 with SQL2000

Hi,
Is anyone using Outlook 2003 as the mail client with SQL2000 on either W2K
or W2K3? Please let me know if this combination is being used?
Thanks
Chris Wood
Alberta Department of Energy
CANADAHaving the same problem here.|||LW,
Are you having problems trying to use Outlook 2003 client for SQL Mail on
the SQL Server?
I just want to know if this is a valid combination.
Chris
"LW" <anonymous@.discussions.microsoft.com> wrote in message
news:7BD31EC5-16B0-47B5-8FBA-2B9E0C09C261@.microsoft.com...
> Having the same problem here.

Outlook 2003 with SQL2000

Hi,
Is anyone using Outlook 2003 as the mail client with SQL2000 on either W2K
or W2K3? Please let me know if this combination is being used?
Thanks
Chris Wood
Alberta Department of Energy
CANADAHaving the same problem here.|||LW,
Are you having problems trying to use Outlook 2003 client for SQL Mail on
the SQL Server?
I just want to know if this is a valid combination.
Chris
"LW" <anonymous@.discussions.microsoft.com> wrote in message
news:7BD31EC5-16B0-47B5-8FBA-2B9E0C09C261@.microsoft.com...
> Having the same problem here.

out of order identity field - sql2000

Hi All

I am finding unexpected results when inserted into a newly created
table that has a field of datatype int identity (1,1).

Basically the order I sort on when inserting into the table is not
reflected in the order of the values from the identity field.

Have I been wrong in assuming that it should reflect the order from the
sort?

The code is ...

create table tmp (A varchar(50), L float, C int identity(1,1))
insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll

and I don't understand why the values in tmp.C aren't in the order
suggested by the sort.

Any comments most appreciated
BevanTry ORDER BY C

<bevanward@.gmail.com> wrote in message
news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> Hi All
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
> Have I been wrong in assuming that it should reflect the order from the
> sort?
> The code is ...
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.
> Any comments most appreciated
> Bevan|||Hi Mike

Thanks for your comment - C is the field in the target table of the
insert that I was hoping would increment in the same sequence as the
sort of Aa, Ll

Cheers
Bevan

Mike C# wrote:
> Try ORDER BY C
>
> <bevanward@.gmail.com> wrote in message
> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> > Hi All
> > I am finding unexpected results when inserted into a newly created
> > table that has a field of datatype int identity (1,1).
> > Basically the order I sort on when inserting into the table is not
> > reflected in the order of the values from the identity field.
> > Have I been wrong in assuming that it should reflect the order from the
> > sort?
> > The code is ...
> > create table tmp (A varchar(50), L float, C int identity(1,1))
> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> > and I don't understand why the values in tmp.C aren't in the order
> > suggested by the sort.
> > Any comments most appreciated
> > Bevan|||You can't rely on an IDENTITY column to be assigned in a particular order or
to not have gaps in the sequence, btw. Try assigning a rank value manually
instead:

CREATE TABLE #tmp (A VARCHAR(50),
L FLOAT,
C INT NOT NULL PRIMARY KEY)

CREATE TABLE #tmp1 (Aa VARCHAR(50),
Ll FLOAT(50),
PRIMARY KEY (Aa, Ll))

INSERT INTO #tmp1 (Aa, Ll)
SELECT 'ABC', 123.45
UNION SELECT 'DEF', 456.12
UNION SELECT 'XYZ', 999.99
UNION SELECT 'RST', 023.43
UNION SELECT 'GHI', 146.56

INSERT INTO #tmp (A, L, C)
SELECT t1.Aa, t1.Ll, COUNT(*) Rank
FROM #tmp1 t1
INNER JOIN #tmp1 t2
ON t1.Aa >= t2.Aa
AND t2.Ll >= t2.Ll
GROUP BY t1.Aa, t1.Ll
ORDER BY t1.Aa, t1.Ll

SELECT C, A, L
FROM #tmp
ORDER BY C

DROP TABLE #tmp1
DROP TABLE #tmp

<bevanward@.gmail.com> wrote in message
news:1150341168.351876.35870@.i40g2000cwc.googlegro ups.com...
> Hi Mike
> Thanks for your comment - C is the field in the target table of the
> insert that I was hoping would increment in the same sequence as the
> sort of Aa, Ll
> Cheers
> Bevan
> Mike C# wrote:
>> Try ORDER BY C
>>
>>
>> <bevanward@.gmail.com> wrote in message
>> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
>> > Hi All
>>> > I am finding unexpected results when inserted into a newly created
>> > table that has a field of datatype int identity (1,1).
>>> > Basically the order I sort on when inserting into the table is not
>> > reflected in the order of the values from the identity field.
>>> > Have I been wrong in assuming that it should reflect the order from the
>> > sort?
>>> > The code is ...
>>> > create table tmp (A varchar(50), L float, C int identity(1,1))
>> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
>>> > and I don't understand why the values in tmp.C aren't in the order
>> > suggested by the sort.
>>> > Any comments most appreciated
>> > Bevan
>|||Hi Mike

Thanks for your comprehensive response. I had always assumed that this
insert was dependable (sequential and contiguous) ... I guess I need to
go back and re-write anywhere I have existing code that made this
assumption.

Thanks again, most appreciated.

Cheers
Bevan

Mike C# wrote:
> You can't rely on an IDENTITY column to be assigned in a particular order or
> to not have gaps in the sequence, btw. Try assigning a rank value manually
> instead:
> CREATE TABLE #tmp (A VARCHAR(50),
> L FLOAT,
> C INT NOT NULL PRIMARY KEY)
> CREATE TABLE #tmp1 (Aa VARCHAR(50),
> Ll FLOAT(50),
> PRIMARY KEY (Aa, Ll))
> INSERT INTO #tmp1 (Aa, Ll)
> SELECT 'ABC', 123.45
> UNION SELECT 'DEF', 456.12
> UNION SELECT 'XYZ', 999.99
> UNION SELECT 'RST', 023.43
> UNION SELECT 'GHI', 146.56
> INSERT INTO #tmp (A, L, C)
> SELECT t1.Aa, t1.Ll, COUNT(*) Rank
> FROM #tmp1 t1
> INNER JOIN #tmp1 t2
> ON t1.Aa >= t2.Aa
> AND t2.Ll >= t2.Ll
> GROUP BY t1.Aa, t1.Ll
> ORDER BY t1.Aa, t1.Ll
> SELECT C, A, L
> FROM #tmp
> ORDER BY C
> DROP TABLE #tmp1
> DROP TABLE #tmp
>
> <bevanward@.gmail.com> wrote in message
> news:1150341168.351876.35870@.i40g2000cwc.googlegro ups.com...
> > Hi Mike
> > Thanks for your comment - C is the field in the target table of the
> > insert that I was hoping would increment in the same sequence as the
> > sort of Aa, Ll
> > Cheers
> > Bevan
> > Mike C# wrote:
> >> Try ORDER BY C
> >>
> >>
> >> <bevanward@.gmail.com> wrote in message
> >> news:1150338610.291820.67350@.h76g2000cwa.googlegro ups.com...
> >> > Hi All
> >> >> > I am finding unexpected results when inserted into a newly created
> >> > table that has a field of datatype int identity (1,1).
> >> >> > Basically the order I sort on when inserting into the table is not
> >> > reflected in the order of the values from the identity field.
> >> >> > Have I been wrong in assuming that it should reflect the order from the
> >> > sort?
> >> >> > The code is ...
> >> >> > create table tmp (A varchar(50), L float, C int identity(1,1))
> >> > insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> >> >> > and I don't understand why the values in tmp.C aren't in the order
> >> > suggested by the sort.
> >> >> > Any comments most appreciated
> >> > Bevan
> >|||<bevanward@.gmail.com> wrote in message
news:1150342841.281994.283570@.u72g2000cwu.googlegr oups.com...
> Hi Mike
> Thanks for your comprehensive response. I had always assumed that this
> insert was dependable (sequential and contiguous) ... I guess I need to
> go back and re-write anywhere I have existing code that made this
> assumption.
> Thanks again, most appreciated.

No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
of the need for the self-join ranking method.|||Hi Mike

I have read fondly of row_number() in 2005 and can't wait. This has
existed in Oracle for years, from what I understand, and I'm not sure
how we have done without it for so long.

I have re-written the code for this and it doubles the execution time
unfortunately.

Thanks again for taking the time, most appreciated

Bevan

Mike C# wrote:
> <bevanward@.gmail.com> wrote in message
> news:1150342841.281994.283570@.u72g2000cwu.googlegr oups.com...
> > Hi Mike
> > Thanks for your comprehensive response. I had always assumed that this
> > insert was dependable (sequential and contiguous) ... I guess I need to
> > go back and re-write anywhere I have existing code that made this
> > assumption.
> > Thanks again, most appreciated.
> No problem. BTW, SQL 2005 has new functions like ROW_NUMBER() that gets rid
> of the need for the self-join ranking method.|||(bevanward@.gmail.com) writes:
> I am finding unexpected results when inserted into a newly created
> table that has a field of datatype int identity (1,1).
> Basically the order I sort on when inserting into the table is not
> reflected in the order of the values from the identity field.
> Have I been wrong in assuming that it should reflect the order from the
> sort?
> The code is ...
> create table tmp (A varchar(50), L float, C int identity(1,1))
> insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Ll
> and I don't understand why the values in tmp.C aren't in the order
> suggested by the sort.

Interesting. I get it to work most of the time, and I've even been told
that this is guarranteed to work as expected. Definitely in SQL 2005,
but the source said it was OK for SQL 2000 as well.

However, if you are running on a multi-processor machine (including a
hyper-threaded CPU), try adding OPTION (MAXDOP 1) at the end of the
query.

Note that is you use SELECT INTO instead, there is no guarantee that
the order is the desired.

By the way, what does SELECT @.@.version say?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E41419257FYazorman@.127.0.0.1...
> (bevanward@.gmail.com) writes:
> Interesting. I get it to work most of the time, and I've even been told
> that this is guarranteed to work as expected. Definitely in SQL 2005,
> but the source said it was OK for SQL 2000 as well.

I've found that it doesn't work all too often; particularly, as you pointed
out, if you are running hyperthreading, multiple processors, or have
multiple programs updating the table simultaneously. In that third
situation IDENTITY can leave extremely large gaps in a sequence. In my
experience, the only thing an IDENTITY column can guarantee is a different
number for each row.

To be honest, I don't think the INSERT statement guarantees the order in
which the rows will be inserted, which is a large part of the OP's problem
in this situation. Normally it doesn't matter what order rows get inserted
as long as they get in there. In this case the OP is dynamically assigning
numeric identifiers to each row as they're inserted which makes the order of
insertion important.

BTW - I didn't think about it last night, but with the SELECT INTO statement
(instead of INSERT) you might be able to use the IDENTITY() function to
assign values in the order you require. But SELECT INTO requires the target
table not exist before it's run. I haven't tried it, so can't guarantee it
would work, but hey...|||>> I am finding unexpected results when inserted into a newly created table that has a field [sic] of datatype int identity (1,1). <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to have a column
that defines that ordering. You must use an ORDER BY clause on a
cursor or in an OVER() clause.

Next, by definition -- repeat BY DEFINITION !!! -- IDENTITY is not a
key.

>> Have I been wrong in assuming that it should reflect the order from the sort? <<

Your assumptions are MUCH worse than that! You have missed ALL of the
foundations of RDBMS. As they say in Zen, you must empty your cup to
drink new tea. Please get a good book on RDBMS, take some time and get
it right before you kill someone.|||This might help from the SQL Engine team blog...

http://blogs.msdn.com/sqltips/archi.../20/441053.aspx

Its point 4, the identities are calculated in the right order just not
inserted but the insert order shouldn't matter if the identities are
calculated in the correct order.

1.. If you have an ORDER BY in the top-most SELECT block in a query, the
presentation order of the results honor that ORDER BY request
2.. If you have a TOP in the same SELECT block as an ORDER BY, any TOP
computation is performed with respect to that ORDER BY. For example, if
there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows
within a given sort. Note that this does not guarantee that subsequent
operations will somehow retain the sort order of a previous operation. The
query optimizer re-orders operations to find more efficient query plans
3.. Cursors over queries containing ORDER BY in the top-most scope will
navigate in that order
4.. INSERT queries that use SELECT with ORDER BY to populate rows
guarantees how identity values are computed but not the order in which the
rows are inserted
5.. SQL Server 2005 supports a number of new "sequence functions" like
RANK(), ROW_NUMBER() that can be performed in a given order using a OVER
clause with ORDER BY
6.. For backwards compatibility reasons, SQL Server provides support for
assignments of type SELECT @.p = @.p + 1 ... ORDER BY at the top-most scope.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"Mike C#" <xxx@.yyy.com> wrote in message
news:Gylkg.2679$%12.1269@.fe09.lga...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns97E41419257FYazorman@.127.0.0.1...
>> (bevanward@.gmail.com) writes:
>> Interesting. I get it to work most of the time, and I've even been told
>> that this is guarranteed to work as expected. Definitely in SQL 2005,
>> but the source said it was OK for SQL 2000 as well.
> I've found that it doesn't work all too often; particularly, as you
> pointed out, if you are running hyperthreading, multiple processors, or
> have multiple programs updating the table simultaneously. In that third
> situation IDENTITY can leave extremely large gaps in a sequence. In my
> experience, the only thing an IDENTITY column can guarantee is a different
> number for each row.
> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted, which is a large part of the OP's problem
> in this situation. Normally it doesn't matter what order rows get
> inserted as long as they get in there. In this case the OP is dynamically
> assigning numeric identifiers to each row as they're inserted which makes
> the order of insertion important.
> BTW - I didn't think about it last night, but with the SELECT INTO
> statement (instead of INSERT) you might be able to use the IDENTITY()
> function to assign values in the order you require. But SELECT INTO
> requires the target table not exist before it's run. I haven't tried it,
> so can't guarantee it would work, but hey...|||Mike C# (xxx@.yyy.com) writes:
> I've found that it doesn't work all too often; particularly, as you
> pointed out, if you are running hyperthreading, multiple processors, or
> have multiple programs updating the table simultaneously. In that third
> situation IDENTITY can leave extremely large gaps in a sequence. In my
> experience, the only thing an IDENTITY column can guarantee is a
> different number for each row.

Gaps due to simultaneous updates is another story. If you want contiguous
numbers, you should not use IDENTITY for your real tables. (You can
still generate ids with help of a temp table with an IDENTITY column.)

> To be honest, I don't think the INSERT statement guarantees the order in
> which the rows will be inserted,

Correct.

> which is a large part of the OP's problem in this situation.

I hope it isn't! What should matter is in which order the IDENTITY values
are generated. And that is what is guaranteed, at least in SQL 2005.

> BTW - I didn't think about it last night, but with the SELECT INTO
> statement (instead of INSERT) you might be able to use the IDENTITY()
> function to assign values in the order you require.

No! I pointed this out in my post, but I say it again: SELECT INTO
with the IDENTITY() function gives no guarantee about order, and is
overall more prone to botch the order.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:e6timt$hna$1$8300dec7@.news.demon.co.uk...
> This might help from the SQL Engine team blog...
> http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
> Its point 4, the identities are calculated in the right order just not
> inserted but the insert order shouldn't matter if the identities are
> calculated in the correct order.

I noticed the blogger states "*most* of the rules are valid for SQL 2000
too", though he doesn't specify which ones.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E4EF3786F5CYazorman@.127.0.0.1...
> Mike C# (xxx@.yyy.com) writes:
>> I've found that it doesn't work all too often; particularly, as you
>> pointed out, if you are running hyperthreading, multiple processors, or
>> have multiple programs updating the table simultaneously. In that third
>> situation IDENTITY can leave extremely large gaps in a sequence. In my
>> experience, the only thing an IDENTITY column can guarantee is a
>> different number for each row.
> Gaps due to simultaneous updates is another story. If you want contiguous
> numbers, you should not use IDENTITY for your real tables. (You can
> still generate ids with help of a temp table with an IDENTITY column.)

So we agree on gaps.

>> To be honest, I don't think the INSERT statement guarantees the order in
>> which the rows will be inserted,
> Correct.

And insert statement order guarantees.

>> which is a large part of the OP's problem in this situation.
> I hope it isn't! What should matter is in which order the IDENTITY values
> are generated. And that is what is guaranteed, at least in SQL 2005.

But this is a SQL 2000 problem. If this is supposed to be guaranteed in SQL
2000 as well, then there's apparently a hot fix needed for the OP's problem.

>> BTW - I didn't think about it last night, but with the SELECT INTO
>> statement (instead of INSERT) you might be able to use the IDENTITY()
>> function to assign values in the order you require.
> No! I pointed this out in my post, but I say it again: SELECT INTO
> with the IDENTITY() function gives no guarantee about order, and is
> overall more prone to botch the order.

Hence my use of the word "might", as in "I didn't try this, so I don't know
if it will produce desired results or not."

other programs won't start while SQL is running

First of all I'm not a sql guru. I'm just doing some troubleshooting on the server.
I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints from the DBA about sql performance.
The problem is while sql is running other programs won't start.
IIS
v2i protector
They time out. If I close sql and restart the other services they start fine but then sql won't start.
The event id's I get for all programs are
26-Application popup:'program.exe' application error: The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.
7009- Timeout (30000 milliseconds) waiting for the 'program' service to connect.
7000-The 'program' service failed to start due to the following error" the service did not respond to the start or control request in a timely fashion.
Has anyone seen a situation like this before?
ThanksHi,
Can you make the SQL Server Memory to Static and Restart the SQL Server and
other services. It seems
SQL Server meory set to dynamic and it utilize all the memory available in
the system. This might be a
problem.
Using task manager check the memory utilized by SQLSERVR.EXE.
--
Thanks
Hari
MCDBA
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>|||Rich,
Sounds like you may be lacking enough memory to operate properly or someone
has turned off the Dynamic Memory allocation in SQL Server by setting the
SET Working Set size to On. Make sure the Memory in SQL Server is still set
to Dynamic but lower the Max Memory setting enough to leave memory for the
other applications.
--
Andrew J. Kelly SQL MVP
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>|||"Andrew J. Kelly" wrote:
> Rich,
> Sounds like you may be lacking enough memory to operate properly or someone
> has turned off the Dynamic Memory allocation in SQL Server by setting the
> SET Working Set size to On. Make sure the Memory in SQL Server is still set
> to Dynamic but lower the Max Memory setting enough to leave memory for the
> other applications.
> --
> Andrew J. Kelly SQL MVP
>
> "Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
> news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> > First of all I'm not a sql guru. I'm just doing some troubleshooting on
> the server.
> >
> > I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
> from the DBA about sql performance.
> >
> > The problem is while sql is running other programs won't start.
> > IIS
> > v2i protector
> >
> > They time out. If I close sql and restart the other services they start
> fine but then sql won't start.
> >
> > The event id's I get for all programs are
> > 26-Application popup:'program.exe' application error: The application
> failed to initialize properly (0xc0000142). Click on OK to terminate the
> application.
> >
> > 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
> connect.
> >
> > 7000-The 'program' service failed to start due to the following error" the
> service did not respond to the start or control request in a timely fashion.
> >
> > Has anyone seen a situation like this before?
> >
> > Thanks
> >
> After you guys replied to my post I checked out the server after the boot. Between the MSMDSRV.EXE and the SQLSERVR.EXE they were using up 1.6 gig of memory!!! I'm still not quite sure of the fix yet but the I found a MS knowledgebase article concerning a memory leak in MSMDSRV.EXE. We're looking into it and are keeping your posts in mind. Thank you both very much.
>

other programs won't start while SQL is running

First of all I'm not a sql guru. I'm just doing some troubleshooting on the server.
I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints from the DBA about sql performance.
The problem is while sql is running other programs won't start.
IIS
v2i protector
They time out. If I close sql and restart the other services they start fine but then sql won't start.
The event id's I get for all programs are
26-Application popup:'program.exe' application error: The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.
7009- Timeout (30000 milliseconds) waiting for the 'program' service to connect.
7000-The 'program' service failed to start due to the following error" the service did not respond to the start or control request in a timely fashion.
Has anyone seen a situation like this before?
Thanks
Hi,
Can you make the SQL Server Memory to Static and Restart the SQL Server and
other services. It seems
SQL Server meory set to dynamic and it utilize all the memory available in
the system. This might be a
problem.
Using task manager check the memory utilized by SQLSERVR.EXE.
Thanks
Hari
MCDBA
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>
|||Rich,
Sounds like you may be lacking enough memory to operate properly or someone
has turned off the Dynamic Memory allocation in SQL Server by setting the
SET Working Set size to On. Make sure the Memory in SQL Server is still set
to Dynamic but lower the Max Memory setting enough to leave memory for the
other applications.
Andrew J. Kelly SQL MVP
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>
|||"Andrew J. Kelly" wrote:

> Rich,
> Sounds like you may be lacking enough memory to operate properly or someone
> has turned off the Dynamic Memory allocation in SQL Server by setting the
> SET Working Set size to On. Make sure the Memory in SQL Server is still set
> to Dynamic but lower the Max Memory setting enough to leave memory for the
> other applications.
> --
> Andrew J. Kelly SQL MVP
>
> "Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
> news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> the server.
> from the DBA about sql performance.
> fine but then sql won't start.
> failed to initialize properly (0xc0000142). Click on OK to terminate the
> application.
> connect.
> service did not respond to the start or control request in a timely fashion.
> After you guys replied to my post I checked out the server after the boot. Between the MSMDSRV.EXE and the SQLSERVR.EXE they were using up 1.6 gig of memory!!! I'm still not quite sure of the fix yet but the I found a MS knowledgebase article concerni
ng a memory leak in MSMDSRV.EXE. We're looking into it and are keeping your posts in mind. Thank you both very much.
>

other programs won't start while SQL is running

First of all I'm not a sql guru. I'm just doing some troubleshooting on the
server.
I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints fr
om the DBA about sql performance.
The problem is while sql is running other programs won't start.
IIS
v2i protector
They time out. If I close sql and restart the other services they start fin
e but then sql won't start.
The event id's I get for all programs are
26-Application popup:'program.exe' application error: The application failed
to initialize properly (0xc0000142). Click on OK to terminate the applicati
on.
7009- Timeout (30000 milliseconds) waiting for the 'program' service to conn
ect.
7000-The 'program' service failed to start due to the following error" the s
ervice did not respond to the start or control request in a timely fashion.
Has anyone seen a situation like this before?
ThanksHi,
Can you make the SQL Server Memory to Static and Restart the SQL Server and
other services. It seems
SQL Server meory set to dynamic and it utilize all the memory available in
the system. This might be a
problem.
Using task manager check the memory utilized by SQLSERVR.EXE.
Thanks
Hari
MCDBA
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>|||Rich,
Sounds like you may be lacking enough memory to operate properly or someone
has turned off the Dynamic Memory allocation in SQL Server by setting the
SET Working Set size to On. Make sure the Memory in SQL Server is still set
to Dynamic but lower the Max Memory setting enough to leave memory for the
other applications.
Andrew J. Kelly SQL MVP
"Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> First of all I'm not a sql guru. I'm just doing some troubleshooting on
the server.
> I've got a Win2000 adv server sp4 w/ sql2000 sp3. I've had no complaints
from the DBA about sql performance.
> The problem is while sql is running other programs won't start.
> IIS
> v2i protector
> They time out. If I close sql and restart the other services they start
fine but then sql won't start.
> The event id's I get for all programs are
> 26-Application popup:'program.exe' application error: The application
failed to initialize properly (0xc0000142). Click on OK to terminate the
application.
> 7009- Timeout (30000 milliseconds) waiting for the 'program' service to
connect.
> 7000-The 'program' service failed to start due to the following error" the
service did not respond to the start or control request in a timely fashion.
> Has anyone seen a situation like this before?
> Thanks
>|||"Andrew J. Kelly" wrote:

> Rich,
> Sounds like you may be lacking enough memory to operate properly or someon
e
> has turned off the Dynamic Memory allocation in SQL Server by setting the
> SET Working Set size to On. Make sure the Memory in SQL Server is still s
et
> to Dynamic but lower the Max Memory setting enough to leave memory for the
> other applications.
> --
> Andrew J. Kelly SQL MVP
>
> "Rich Lehr" <Rich Lehr@.discussions.microsoft.com> wrote in message
> news:2062A7B0-6AC9-4507-86ED-C7206DA1574F@.microsoft.com...
> the server.
> from the DBA about sql performance.
> fine but then sql won't start.
> failed to initialize properly (0xc0000142). Click on OK to terminate the
> application.
> connect.
> service did not respond to the start or control request in a timely fashio
n.
> After you guys replied to my post I checked out the server after the boot. Betwee
n the MSMDSRV.EXE and the SQLSERVR.EXE they were using up 1.6 gig of memory!!! I'm
still not quite sure of the fix yet but the I found a MS knowledgebase article conce
rni
ng a memory leak in MSMDSRV.EXE. We're looking into it and are keeping your posts in mind.
Thank you both very much.
>

OS for SQL2000

i run SQL2K w/sp4 on Windows2000 server .
is it ok to run SQL2K at Windows 2003 server?
i wish to make use some OS features of Windows 2003 server.
Thanks a lot.
tonyYes. Just make sure you have at least sp3 for your SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"tony wong" <x34@.netvigator.com> wrote in message news:eNayi9RnHHA.4896@.TK2MSFTNGP02.phx.gbl
..
>i run SQL2K w/sp4 on Windows2000 server .
> is it ok to run SQL2K at Windows 2003 server?
> i wish to make use some OS features of Windows 2003 server.
> Thanks a lot.
> tony
>|||Hi Tony
"tony wong" wrote:

> i run SQL2K w/sp4 on Windows2000 server .
> is it ok to run SQL2K at Windows 2003 server?
> i wish to make use some OS features of Windows 2003 server.
> Thanks a lot.
> tony
>
Windows 2003 had some changes to the I/O subsystem that can actually help
improve the performance of SQL Server.
John|||Thanks a lot.
"John Bell" <jbellnewsposts@.hotmail.com> glsD:3388B4D6-5097-4B87-A992-0DCCB56F1B2D
@.microsoft.com...
> Hi Tony
> "tony wong" wrote:
>
> Windows 2003 had some changes to the I/O subsystem that can actually help
> improve the performance of SQL Server.
> John

OS for SQL2000

i run SQL2K w/sp4 on Windows2000 server .
is it ok to run SQL2K at Windows 2003 server?
i wish to make use some OS features of Windows 2003 server.
Thanks a lot.
tony
Yes. Just make sure you have at least sp3 for your SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"tony wong" <x34@.netvigator.com> wrote in message news:eNayi9RnHHA.4896@.TK2MSFTNGP02.phx.gbl...
>i run SQL2K w/sp4 on Windows2000 server .
> is it ok to run SQL2K at Windows 2003 server?
> i wish to make use some OS features of Windows 2003 server.
> Thanks a lot.
> tony
>
|||Hi Tony
"tony wong" wrote:

> i run SQL2K w/sp4 on Windows2000 server .
> is it ok to run SQL2K at Windows 2003 server?
> i wish to make use some OS features of Windows 2003 server.
> Thanks a lot.
> tony
>
Windows 2003 had some changes to the I/O subsystem that can actually help
improve the performance of SQL Server.
John
|||Thanks a lot.
"John Bell" <jbellnewsposts@.hotmail.com> glsD:3388B4D6-5097-4B87-A992-0DCCB56F1B2D@.microsoft.com...
> Hi Tony
> "tony wong" wrote:
> Windows 2003 had some changes to the I/O subsystem that can actually help
> improve the performance of SQL Server.
> John

OS for SQL2000

i run SQL2K w/sp4 on Windows2000 server .
is it ok to run SQL2K at Windows 2003 server?
i wish to make use some OS features of Windows 2003 server.
Thanks a lot.
tonyYes. Just make sure you have at least sp3 for your SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"tony wong" <x34@.netvigator.com> wrote in message news:eNayi9RnHHA.4896@.TK2MSFTNGP02.phx.gbl...
>i run SQL2K w/sp4 on Windows2000 server .
> is it ok to run SQL2K at Windows 2003 server?
> i wish to make use some OS features of Windows 2003 server.
> Thanks a lot.
> tony
>|||Hi Tony
"tony wong" wrote:
> i run SQL2K w/sp4 on Windows2000 server .
> is it ok to run SQL2K at Windows 2003 server?
> i wish to make use some OS features of Windows 2003 server.
> Thanks a lot.
> tony
>
Windows 2003 had some changes to the I/O subsystem that can actually help
improve the performance of SQL Server.
John|||Thanks a lot.
"John Bell" <jbellnewsposts@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:3388B4D6-5097-4B87-A992-0DCCB56F1B2D@.microsoft.com...
> Hi Tony
> "tony wong" wrote:
>> i run SQL2K w/sp4 on Windows2000 server .
>> is it ok to run SQL2K at Windows 2003 server?
>> i wish to make use some OS features of Windows 2003 server.
>> Thanks a lot.
>> tony
> Windows 2003 had some changes to the I/O subsystem that can actually help
> improve the performance of SQL Server.
> John

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanks
Read about sp_change_users_login in Books Online. Also Google for sp_help_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.gbl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanksRead about sp_change_users_login in Books Online. Also Google for sp_help_revlogin.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.gbl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>

orphans user

I restored db from sql2000 to sql2005, i did fix the all
orphans users except SYS and DBO. How can i drop them from my db?
thanksRead about sp_change_users_login in Books Online. Also Google for sp_help_re
vlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viktor" <serguienkov@.hotmail.com> wrote in message news:OmbaV67yHHA.1188@.TK2MSFTNGP04.phx.g
bl...
>I restored db from sql2000 to sql2005, i did fix the all
> orphans users except SYS and DBO. How can i drop them from my db?
> thanks
>