Out of Order Object ID's

While trying to run an application install that needs to write to
master, it fails b/ c it cant create a new object id. So I followed the
instructions in article 827448 for this error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'TableName' in the database.
No matter how many times I run the recommended script and increment "i",
I cant seem to create a new object. My object id's are very sporadic.
Only the first 98 objects are in order. The next number jumps to
1,000,000 and the last object (object#1012) ends in 2,145,442,717.
Questions:
1. What can I do to create a new object?
2. What causes the object id's to be out of order like this? I don't
recall any huge deletes of objects. Certainly not 1,000,000 of them,
unless temp tables are given object ids?
3. Having object ids out of wack like this... does it cause any kind of
performance hit?
Thanks much!
Chris
> While trying to run an application install that needs to write to master,
> it fails b/ c it cant create a new object id. So I followed the
> instructions in article 827448 for this error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'TableName' in the database.
> No matter how many times I run the recommended script and increment "i", I
> cant seem to create a new object.
The error seems to be that the table already exists. Why are you
incrementing "i"?

> 2. What causes the object id's to be out of order like this? I don't
> recall any huge deletes of objects.
An object_id is assigned arbitrarily by SQL Server. They are not
incremented linearly.
USE TempDB
GO
CREATE TABLE dbo.foo(id INT);
CREATE TABLE dbo.bar(id INT);
SELECT OBJECT_ID('dbo.bar'),OBJECT_ID('dbo.foo');
GO
DROP TABLE dbo.foo,dbo.bar;
GO
On my system, I get:
1870720859, 1854720802
When I run it again, I get:
59238408, 43238351

> Certainly not 1,000,000 of them, unless temp tables are given object ids?
I thought you were "writing to master"? Are you creating permanent tables
in master, or temp tables, or something else?
And yes, temp tables get Object_ids. Run this multiple times:
USE TempDB;
GO
CREATE TABLE #foo(id INT);
SELECT OBJECT_ID('#foo');
DROP TABLE #foo;

> 3. Having object ids out of wack like this... does it cause any kind of
> performance hit?
No, why would it? It's just an arbitrary lookup number. This is like
saying there would be a change in performance if your first name was Wanda
instead of Chris.
Maybe you could explain exactly what you are doing, why you are using a
counter like i and incrementing it, and what the actual failure is.
Aaron
|||Hi Aaron,
I was following the instructions from this article:
http://support.microsoft.com/kb/827448/en-us
I thought object id's were assigned arbitrar numbers. I should have done
a simple test to confirm it (duh) ... but I was taking the word of a
developer. So I second guessed myself.
Aaron Bertrand [SQL Server MVP] wrote:
>
> The error seems to be that the table already exists. Why are you
> incrementing "i"?
>
>
> An object_id is assigned arbitrarily by SQL Server. They are not
> incremented linearly.
> USE TempDB
> GO
> CREATE TABLE dbo.foo(id INT);
> CREATE TABLE dbo.bar(id INT);
> SELECT OBJECT_ID('dbo.bar'),OBJECT_ID('dbo.foo');
> GO
> DROP TABLE dbo.foo,dbo.bar;
> GO
> On my system, I get:
> 1870720859, 1854720802
> When I run it again, I get:
> 59238408, 43238351
>
>
> I thought you were "writing to master"? Are you creating permanent tables
> in master, or temp tables, or something else?
> And yes, temp tables get Object_ids. Run this multiple times:
> USE TempDB;
> GO
> CREATE TABLE #foo(id INT);
> SELECT OBJECT_ID('#foo');
> DROP TABLE #foo;
>
>
> No, why would it? It's just an arbitrary lookup number. This is like
> saying there would be a change in performance if your first name was Wanda
> instead of Chris.
> Maybe you could explain exactly what you are doing, why you are using a
> counter like i and incrementing it, and what the actual failure is.
> Aaron
>
sql

Out of Order Object ID's

While trying to run an application install that needs to write to
master, it fails b/ c it cant create a new object id. So I followed the
instructions in article 827448 for this error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'TableName' in the database.
No matter how many times I run the recommended script and increment "i",
I cant seem to create a new object. My object id's are very sporadic.
Only the first 98 objects are in order. The next number jumps to
1,000,000 and the last object (object#1012) ends in 2,145,442,717.
Questions:
1. What can I do to create a new object?
2. What causes the object id's to be out of order like this? I don't
recall any huge deletes of objects. Certainly not 1,000,000 of them,
unless temp tables are given object ids?
3. Having object ids out of wack like this... does it cause any kind of
performance hit?
Thanks much!
Chris> While trying to run an application install that needs to write to master,
> it fails b/ c it cant create a new object id. So I followed the
> instructions in article 827448 for this error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'TableName' in the database.
> No matter how many times I run the recommended script and increment "i", I
> cant seem to create a new object.
The error seems to be that the table already exists. Why are you
incrementing "i"?

> 2. What causes the object id's to be out of order like this? I don't
> recall any huge deletes of objects.
An object_id is assigned arbitrarily by SQL Server. They are not
incremented linearly.
USE TempDB
GO
CREATE TABLE dbo.foo(id INT);
CREATE TABLE dbo.bar(id INT);
SELECT OBJECT_ID('dbo.bar'),OBJECT_ID('dbo.foo');
GO
DROP TABLE dbo.foo,dbo.bar;
GO
On my system, I get:
1870720859, 1854720802
When I run it again, I get:
59238408, 43238351

> Certainly not 1,000,000 of them, unless temp tables are given object ids?
I thought you were "writing to master"? Are you creating permanent tables
in master, or temp tables, or something else?
And yes, temp tables get Object_ids. Run this multiple times:
USE TempDB;
GO
CREATE TABLE #foo(id INT);
SELECT OBJECT_ID('#foo');
DROP TABLE #foo;

> 3. Having object ids out of wack like this... does it cause any kind of
> performance hit?
No, why would it? It's just an arbitrary lookup number. This is like
saying there would be a change in performance if your first name was Wanda
instead of Chris.
Maybe you could explain exactly what you are doing, why you are using a
counter like i and incrementing it, and what the actual failure is.
Aaron|||Hi Aaron,
I was following the instructions from this article:
http://support.microsoft.com/kb/827448/en-us
I thought object id's were assigned arbitrar numbers. I should have done
a simple test to confirm it (duh) ... but I was taking the word of a
developer. So I second guessed myself.
Aaron Bertrand [SQL Server MVP] wrote:
>
> The error seems to be that the table already exists. Why are you
> incrementing "i"?
>
>
> An object_id is assigned arbitrarily by SQL Server. They are not
> incremented linearly.
> USE TempDB
> GO
> CREATE TABLE dbo.foo(id INT);
> CREATE TABLE dbo.bar(id INT);
> SELECT OBJECT_ID('dbo.bar'),OBJECT_ID('dbo.foo');
> GO
> DROP TABLE dbo.foo,dbo.bar;
> GO
> On my system, I get:
> 1870720859, 1854720802
> When I run it again, I get:
> 59238408, 43238351
>
>
> I thought you were "writing to master"? Are you creating permanent tables
> in master, or temp tables, or something else?
> And yes, temp tables get Object_ids. Run this multiple times:
> USE TempDB;
> GO
> CREATE TABLE #foo(id INT);
> SELECT OBJECT_ID('#foo');
> DROP TABLE #foo;
>
>
> No, why would it? It's just an arbitrary lookup number. This is like
> saying there would be a change in performance if your first name was Wanda
> instead of Chris.
> Maybe you could explain exactly what you are doing, why you are using a
> counter like i and incrementing it, and what the actual failure is.
> Aaron
>

Out of Order Object ID's

While trying to run an application install that needs to write to
master, it fails b/ c it cant create a new object id. So I followed the
instructions in article 827448 for this error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'TableName' in the database.
No matter how many times I run the recommended script and increment "i",
I cant seem to create a new object. My object id's are very sporadic.
Only the first 98 objects are in order. The next number jumps to
1,000,000 and the last object (object#1012) ends in 2,145,442,717.
Questions:
1. What can I do to create a new object?
2. What causes the object id's to be out of order like this? I don't
recall any huge deletes of objects. Certainly not 1,000,000 of them,
unless temp tables are given object ids?
3. Having object ids out of wack like this... does it cause any kind of
performance hit?
Thanks much!
Chris> While trying to run an application install that needs to write to master,
> it fails b/ c it cant create a new object id. So I followed the
> instructions in article 827448 for this error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'TableName' in the database.
> No matter how many times I run the recommended script and increment "i", I
> cant seem to create a new object.
The error seems to be that the table already exists. Why are you
incrementing "i"?
> 2. What causes the object id's to be out of order like this? I don't
> recall any huge deletes of objects.
An object_id is assigned arbitrarily by SQL Server. They are not
incremented linearly.
USE TempDB
GO
CREATE TABLE dbo.foo(id INT);
CREATE TABLE dbo.bar(id INT);
SELECT OBJECT_ID('dbo.bar'),OBJECT_ID('dbo.foo');
GO
DROP TABLE dbo.foo,dbo.bar;
GO
On my system, I get:
1870720859, 1854720802
When I run it again, I get:
59238408, 43238351
> Certainly not 1,000,000 of them, unless temp tables are given object ids?
I thought you were "writing to master"? Are you creating permanent tables
in master, or temp tables, or something else?
And yes, temp tables get Object_ids. Run this multiple times:
USE TempDB;
GO
CREATE TABLE #foo(id INT);
SELECT OBJECT_ID('#foo');
DROP TABLE #foo;
> 3. Having object ids out of wack like this... does it cause any kind of
> performance hit?
No, why would it? It's just an arbitrary lookup number. This is like
saying there would be a change in performance if your first name was Wanda
instead of Chris.
Maybe you could explain exactly what you are doing, why you are using a
counter like i and incrementing it, and what the actual failure is.
Aaron|||Hi Aaron,
I was following the instructions from this article:
http://support.microsoft.com/kb/827448/en-us
I thought object id's were assigned arbitrar numbers. I should have done
a simple test to confirm it (duh) ... but I was taking the word of a
developer. So I second guessed myself.
Aaron Bertrand [SQL Server MVP] wrote:
>>While trying to run an application install that needs to write to master,
>>it fails b/ c it cant create a new object id. So I followed the
>>instructions in article 827448 for this error:
>>Server: Msg 2714, Level 16, State 6, Line 1
>>There is already an object named 'TableName' in the database.
>>No matter how many times I run the recommended script and increment "i", I
>>cant seem to create a new object.
>
> The error seems to be that the table already exists. Why are you
> incrementing "i"?
>
>>2. What causes the object id's to be out of order like this? I don't
>>recall any huge deletes of objects.
>
> An object_id is assigned arbitrarily by SQL Server. They are not
> incremented linearly.
> USE TempDB
> GO
> CREATE TABLE dbo.foo(id INT);
> CREATE TABLE dbo.bar(id INT);
> SELECT OBJECT_ID('dbo.bar'),OBJECT_ID('dbo.foo');
> GO
> DROP TABLE dbo.foo,dbo.bar;
> GO
> On my system, I get:
> 1870720859, 1854720802
> When I run it again, I get:
> 59238408, 43238351
>
>>Certainly not 1,000,000 of them, unless temp tables are given object ids?
>
> I thought you were "writing to master"? Are you creating permanent tables
> in master, or temp tables, or something else?
> And yes, temp tables get Object_ids. Run this multiple times:
> USE TempDB;
> GO
> CREATE TABLE #foo(id INT);
> SELECT OBJECT_ID('#foo');
> DROP TABLE #foo;
>
>>3. Having object ids out of wack like this... does it cause any kind of
>>performance hit?
>
> No, why would it? It's just an arbitrary lookup number. This is like
> saying there would be a change in performance if your first name was Wanda
> instead of Chris.
> Maybe you could explain exactly what you are doing, why you are using a
> counter like i and incrementing it, and what the actual failure is.
> Aaron
>

OSQL fails silently

Hello,
I have a InstallShield program that installs MSDE and then creates a new
database in it. To set up the new database on MSDE I run OSQL. Most of the
time, OSQL works OK. But, sometimes it fails and doesn't print out any
error messages (in the output file).
Has anyone seen this intermittant silent failure behavior before?
The OSQL command I use is:
OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
Regards,
RobertHi Robert
You don't say if you check ERRORLEVEL after the call?
How large the input file?
Have you tried the -e parameter to see the commands?
John
"Robert Wheadon" wrote:

> Hello,
> I have a InstallShield program that installs MSDE and then creates a new
> database in it. To set up the new database on MSDE I run OSQL. Most of t
he
> time, OSQL works OK. But, sometimes it fails and doesn't print out any
> error messages (in the output file).
> Has anyone seen this intermittant silent failure behavior before?
> The OSQL command I use is:
> OSQL.EXE -E -b -i SetupRTEDatabase.sql -o SetupRTEDatabase.log
> Regards,
> Robert
>
>|||Hello John,

> You don't say if you check ERRORLEVEL after the call?
>
Yes, I do. When OSQL decides to fail, I get an ERRORLEVEL of 1.

> How large the input file?
>
10 KB

> Have you tried the -e parameter to see the commands?
>
No. I hadn't thought of that.|||Hi
Non zero values of ERRORLEVEL indicate and error. Have you checked the
output for Msg?
OSQL can have issues with larger files, you may want to split your script
into multiple files and run them separately.
You may also want to specify -n to get cleaner output.
John
"Robert Wheadon" wrote:

> Hello John,
>
> Yes, I do. When OSQL decides to fail, I get an ERRORLEVEL of 1.
>
> 10 KB
>
> No. I hadn't thought of that.
>
>|||I've sorted this one out. See my post "OSQL returns error code of 1 -
intermittently.
SysInternals' FileMonitor and ProcessMonitor tools were helpful in finding
the problem.
"Robert Wheadon" <robert.wheadon@.monitorbm.co.nz> wrote in message
news:eslJ5B3UGHA.4772@.TK2MSFTNGP14.phx.gbl...
> Hello John,
>
> Yes, I do. When OSQL decides to fail, I get an ERRORLEVEL of 1.
>
> 10 KB
>
> No. I hadn't thought of that.
>

Orphaned Sessions

Hi,
I have a SQL Agent task that fails, however the session carries on
regradless and compeltes the task successfully. The task is a call to a
stored procedure, and the Agent just gives an error of unknown token.
Running SQL 2000 sp3a on Windows nt4 server.
Can anyone shed any light on this?Hi
Have you checked the log files for the agent and SQL Server?
Does your stored procedure have error checking?
John
"Julian" <Julian@.discussions.microsoft.com> wrote in message
news:90531DDC-DAF8-4A74-9BEB-3FF5B01F7081@.microsoft.com...
> Hi,
> I have a SQL Agent task that fails, however the session carries on
> regradless and compeltes the task successfully. The task is a call to a
> stored procedure, and the Agent just gives an error of unknown token.
> Running SQL 2000 sp3a on Windows nt4 server.
> Can anyone shed any light on this?|||Hi,
Yes checked both log files and nothing.
Yes the SP does have error handling.
Julian
"John Bell" wrote:

> Hi
> Have you checked the log files for the agent and SQL Server?
> Does your stored procedure have error checking?
> John
> "Julian" <Julian@.discussions.microsoft.com> wrote in message
> news:90531DDC-DAF8-4A74-9BEB-3FF5B01F7081@.microsoft.com...
>
>|||Hi
Can you post the code and which statement it is failing on?
John
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Orphaned Sessions

Hi,
I have a SQL Agent task that fails, however the session carries on
regradless and compeltes the task successfully. The task is a call to a
stored procedure, and the Agent just gives an error of unknown token.
Running SQL 2000 sp3a on Windows nt4 server.
Can anyone shed any light on this?
Hi
Have you checked the log files for the agent and SQL Server?
Does your stored procedure have error checking?
John
"Julian" <Julian@.discussions.microsoft.com> wrote in message
news:90531DDC-DAF8-4A74-9BEB-3FF5B01F7081@.microsoft.com...
> Hi,
> I have a SQL Agent task that fails, however the session carries on
> regradless and compeltes the task successfully. The task is a call to a
> stored procedure, and the Agent just gives an error of unknown token.
> Running SQL 2000 sp3a on Windows nt4 server.
> Can anyone shed any light on this?
|||Hi,
Yes checked both log files and nothing.
Yes the SP does have error handling.
Julian
"John Bell" wrote:

> Hi
> Have you checked the log files for the agent and SQL Server?
> Does your stored procedure have error checking?
> John
> "Julian" <Julian@.discussions.microsoft.com> wrote in message
> news:90531DDC-DAF8-4A74-9BEB-3FF5B01F7081@.microsoft.com...
>
>
|||Hi
Can you post the code and which statement it is failing on?
John
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Orphaned Sessions

Hi,
I have a SQL Agent task that fails, however the session carries on
regradless and compeltes the task successfully. The task is a call to a
stored procedure, and the Agent just gives an error of unknown token.
Running SQL 2000 sp3a on Windows nt4 server.
Can anyone shed any light on this?Hi
Have you checked the log files for the agent and SQL Server?
Does your stored procedure have error checking?
John
"Julian" <Julian@.discussions.microsoft.com> wrote in message
news:90531DDC-DAF8-4A74-9BEB-3FF5B01F7081@.microsoft.com...
> Hi,
> I have a SQL Agent task that fails, however the session carries on
> regradless and compeltes the task successfully. The task is a call to a
> stored procedure, and the Agent just gives an error of unknown token.
> Running SQL 2000 sp3a on Windows nt4 server.
> Can anyone shed any light on this?