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
>

Out Join Syntax

Hello,

I need to write and Ansi Outer Join that has 2 columns from the same table.

I keep getting

Server: Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dim_person.person_key" could not be bound.

here is what the code looks like. any help is appreciated.

select ...

from dim_day_of_year
, dim_company
, dim_currency
, dim_account
, dim_person
, ods_conference
left outer join fact_usage_alloc fua1 on ods_conference.conf_key=fua1.conf_key
left outer join fact_usage_alloc fua2
on dim_person.person_key=fua2.requestor_person_key

where...from ...
, ods_conference
left outer
join fact_usage_alloc fua
on fua.conf_key = ods_conference.conf_key
and fua.requestor_person_key = dim_person.person_key|||That certainly makes sense to me but produces a syntax error :mad:

left outer join fact_usage_alloc fua
on fua.conf_key=ods_conference.conf_key
and on fua.requestor_person_key=dim_person.person_key
where fua.company_key = dim_company.company_key...........

Thanks for the reply|||you're welcome

you can't say "and on"|||hmmm. Still getting the bind error. I even cut and pasted your example in and I get the same thing. I didn't realize mssql was so particular :S|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)|||Mixing non ansi and ansi forms is just a bad idea

Plus, unless I missing it, your outter join tables don't seem to be joing to anything

Plus, you really should get into good formatting, it will make your like so much easier

SELECT ColumnList
FROM dim_day_of_year a
INNER JOIN dim_company b
ON b.col = ?.col
INNER JOIN dim_currency c
ON c.col = ?.col
INNER JOIN dim_account d
ON d.col =?.col
INNER JOIN dim_person e
ON e.col = ?.col
INNER JOIN ods_conference f
ON f.col = ?.col
LEFT JOIN fact_usage_alloc g
ON e.conf_key = f.conf_key -- This one makes no sense
LEFT JOIN fact_usage_alloc h
ON d.person_ke = g.requestor_person_key -- Nor does this one|||okay, here's the deal: re-write your other comma-delimited table joins as INNER JOINs

i bet the error goes away ;)

You funny

The message you have enetered is too short|||You funnyno, i was serious -- i bet the error goes away because of how the FROM clause is parsed

the explicit JOINS are probably being interpreted first, before being combined with the comma-delimited tables

sort of like * and / are done before + and -

thus, in this local context:
ods_conference
left outer
join fact_usage_alloc fua
on fua.conf_key = ods_conference.conf_key
and fua.requestor_person_key = dim_person.person_keythis explains the error message -- "dim_person.person_key" could not be bound

dim_person hasn't been parsed as a table yet|||Thanks for the effort guys. This was a quick test to see how easy it would be to convert a legacy system to MSSQL. I think we got a portion of the answer.

Thanks Again.sql

Out in email.

I am using extended stored procedure xp_sendmail to send the email now my
question is, is there any way in can write the out of following query into
one file and send it to through email? or output directly goes to email..
restore FILELISTONLY from disk = 'c:\fullpath.bak'
Thanks
Probably a few ways but a couple off the top of my head -
Execute the command using osql and specify an output file in
the osql command.
Execute the command within a job - for the job step doing
the restore filelistonly, go to the advanced tab and specify
an output file.
You can then send the output file as an attachment using
xp_sendmail.
-Sue
On Mon, 30 Oct 2006 08:20:28 -0500, "Rogers"
<naissani@.hotmail.com> wrote:

>I am using extended stored procedure xp_sendmail to send the email now my
>question is, is there any way in can write the out of following query into
>one file and send it to through email? or output directly goes to email..
>restore FILELISTONLY from disk = 'c:\fullpath.bak'
>Thanks
>
|||Thanks Sue...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0ladk2d0g0n0gm69qv3ot2tgq5fagd7vkr@.4ax.com...
> Probably a few ways but a couple off the top of my head -
> Execute the command using osql and specify an output file in
> the osql command.
> Execute the command within a job - for the job step doing
> the restore filelistonly, go to the advanced tab and specify
> an output file.
> You can then send the output file as an attachment using
> xp_sendmail.
> -Sue
> On Mon, 30 Oct 2006 08:20:28 -0500, "Rogers"
> <naissani@.hotmail.com> wrote:
>

OSQL question

Hi I am trying to write a batch file that will give me all records relating to the query.

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%1054193%'"

The query is truncating the 1054193 to 054193....

Is there a problem with using LIKE and the % wildcard in an OSQL statement?

Thanks,
Darrin Carterhow do you know it's truncating? did you see it in profiler? i just ran it here (slightly modified) and it runs correctly, profiler shows the exact string.|||Originally posted by ms_sql_dba
how do you know it's truncating? did you see it in profiler? i just ran it here (slightly modified) and it runs correctly, profiler shows the exact string.

I run it from the batch file that I created. In the DOS window it is missing the 1 at the beginning as well as the % signs

This is the actual statement:

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%1054193%'"

This is how it shows up in the DOS window.

I:\>osql -S sqlserver -d database -U sa -P "" -I -Q "select docloc from mhgroup.docmaster where docloc like 054193"|||ok, now I get it. %1 is a replacement for the first argument following the batch file name, which is the reason why %1 disappears. I am not sure about the other % sign.|||you probably will have to use DOS escape codes to make %' visible to OSQL.|||Just use two percent signs (eg : %%) to make one visible to DOS

eg your actual statement should be :

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%%1054193%%'"|||Originally posted by Enigma
Just use two percent signs (eg : %%) to make one visible to DOS

eg your actual statement should be :

osql -S sqlserver -d database -U sa -P "" -Q "select docloc from mhgroup.docmaster where docloc like '%%1054193%%'"

That did it. Thanks!