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:
>

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."

OSQL output file contains extra blank lines

I have a process where I am trying to run "sp_helptext" in OSQL and piping
the output to a file so that I could basically generate the "Create
Procedure" statement for a particular Stored Proc. When I run this - I am
getting extra blank lines in the output file.
For example, I run the following OSQL command to generate the pubs.ByRoyalty
stored procedure:
osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
byroyalty" >ByRoyalty.sql
The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
Is there any osql switch that could eliminate these blank lines? I've tried
adjusting the -w switch and it has no affect.
Thanks!
CREATE PROCEDURE byroyalty @.percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @.percentage
If you can work with other tools like the query analyzer instead you can get
the desired output using the following script:
USE pubs
CREATE TABLE #ByRoyalty (output varchar(8000))
INSERT #ByRoyalty
EXEC sp_helptext byroyalty
SELECT * FROM #ByRoyalty
The trick now is to find a way to send the output to a file
Hope it helps
"TJTODD" wrote:

> I have a process where I am trying to run "sp_helptext" in OSQL and piping
> the output to a file so that I could basically generate the "Create
> Procedure" statement for a particular Stored Proc. When I run this - I am
> getting extra blank lines in the output file.
> For example, I run the following OSQL command to generate the pubs.ByRoyalty
> stored procedure:
> osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
> byroyalty" >ByRoyalty.sql
> The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
> Is there any osql switch that could eliminate these blank lines? I've tried
> adjusting the -w switch and it has no affect.
> Thanks!
>
> CREATE PROCEDURE byroyalty @.percentage int
>
> AS
>
> select au_id from titleauthor
>
> where titleauthor.royaltyper = @.percentage
>
>

OSQL output file contains extra blank lines

I have a process where I am trying to run "sp_helptext" in OSQL and piping
the output to a file so that I could basically generate the "Create
Procedure" statement for a particular Stored Proc. When I run this - I am
getting extra blank lines in the output file.
For example, I run the following OSQL command to generate the pubs.ByRoyalty
stored procedure:
osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
byroyalty" >ByRoyalty.sql
The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
Is there any osql switch that could eliminate these blank lines? I've tried
adjusting the -w switch and it has no affect.
Thanks!
CREATE PROCEDURE byroyalty @.percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @.percentageIf you can work with other tools like the query analyzer instead you can get
the desired output using the following script:
USE pubs
CREATE TABLE #ByRoyalty (output varchar(8000))
INSERT #ByRoyalty
EXEC sp_helptext byroyalty
SELECT * FROM #ByRoyalty
The trick now is to find a way to send the output to a file
Hope it helps
"TJTODD" wrote:

> I have a process where I am trying to run "sp_helptext" in OSQL and piping
> the output to a file so that I could basically generate the "Create
> Procedure" statement for a particular Stored Proc. When I run this - I am
> getting extra blank lines in the output file.
> For example, I run the following OSQL command to generate the pubs.ByRoyal
ty
> stored procedure:
> osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
> byroyalty" >ByRoyalty.sql
> The contents of ByRoyaly.sql is below...(Note all the annoying blank lines
).
> Is there any osql switch that could eliminate these blank lines? I've tri
ed
> adjusting the -w switch and it has no affect.
> Thanks!
>
> CREATE PROCEDURE byroyalty @.percentage int
>
> AS
>
> select au_id from titleauthor
>
> where titleauthor.royaltyper = @.percentage
>
>

OSQL output file contains extra blank lines

I have a process where I am trying to run "sp_helptext" in OSQL and piping
the output to a file so that I could basically generate the "Create
Procedure" statement for a particular Stored Proc. When I run this - I am
getting extra blank lines in the output file.
For example, I run the following OSQL command to generate the pubs.ByRoyalty
stored procedure:
osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
byroyalty" >ByRoyalty.sql
The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
Is there any osql switch that could eliminate these blank lines? I've tried
adjusting the -w switch and it has no affect.
Thanks!
CREATE PROCEDURE byroyalty @.percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @.percentageIf you can work with other tools like the query analyzer instead you can get
the desired output using the following script:
USE pubs
CREATE TABLE #ByRoyalty (output varchar(8000))
INSERT #ByRoyalty
EXEC sp_helptext byroyalty
SELECT * FROM #ByRoyalty
The trick now is to find a way to send the output to a file
Hope it helps
"TJTODD" wrote:
> I have a process where I am trying to run "sp_helptext" in OSQL and piping
> the output to a file so that I could basically generate the "Create
> Procedure" statement for a particular Stored Proc. When I run this - I am
> getting extra blank lines in the output file.
> For example, I run the following OSQL command to generate the pubs.ByRoyalty
> stored procedure:
> osql -n -h-1 -w180 -SServerName-dpubs -Usa -Psapass -Q"exec sp_Helptext
> byroyalty" >ByRoyalty.sql
> The contents of ByRoyaly.sql is below...(Note all the annoying blank lines).
> Is there any osql switch that could eliminate these blank lines? I've tried
> adjusting the -w switch and it has no affect.
> Thanks!
>
> CREATE PROCEDURE byroyalty @.percentage int
>
> AS
>
> select au_id from titleauthor
>
> where titleauthor.royaltyper = @.percentage
>
>