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
Showing posts with label install. Show all posts
Showing posts with label install. Show all posts
Friday, March 23, 2012
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
>
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
>
Wednesday, March 21, 2012
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
>
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
>
Monday, March 12, 2012
osql vs. QA and extended characters
Hi, I have some sql commands I incorporate into an install and I've been
using scripts and osql.exe to perform these with generally good results.
A recent addition to these installs is a script that populates a table
of usernames and encryped passwords, and this is where I'm having
trouble. If I run a script in Query Analyzer that contains this line:
insert into operator (op_code, password, first_name, last_name,
security, operator, salespoint) values ('ADMIN', '??', 'ADMIN',
'ADMIN', 9, 'SYSTEM', 'SYSTEM')
I get predictable results and the encrypted password gets correctly
decrypted in our application.
If I run the very same script using osql like this:
osql.exe -E -i MyScript.sql -o "Output.txt"
The password doesn't end up in the database as '??', it seems to get
changed to '?+-' which obviously wouldn't decrypt correctly in the
application.
So this makes me think there's some kind of translation going on with
these extended characters when running the script via osql. Anyone know
how I can achieve the same results with osql as QA? I'm hoping there's a
switch or something that will do the trick. Thanks!
Matt
MattB wrote:
> Hi, I have some sql commands I incorporate into an install and I've
> been using scripts and osql.exe to perform these with generally good
> results.
> A recent addition to these installs is a script that populates a table
> of usernames and encryped passwords, and this is where I'm having
> trouble. If I run a script in Query Analyzer that contains this line:
> insert into operator (op_code, password, first_name, last_name,
> security, operator, salespoint) values ('ADMIN', '??', 'ADMIN',
> 'ADMIN', 9, 'SYSTEM', 'SYSTEM')
> I get predictable results and the encrypted password gets correctly
> decrypted in our application.
> If I run the very same script using osql like this:
> osql.exe -E -i MyScript.sql -o "Output.txt"
> The password doesn't end up in the database as '??', it seems to
> get changed to '?+-' which obviously wouldn't decrypt correctly in
> the application.
> So this makes me think there's some kind of translation going on with
> these extended characters when running the script via osql. Anyone
> know how I can achieve the same results with osql as QA? I'm hoping
> there's a switch or something that will do the trick. Thanks!
> Matt
See this page:
http://www.windowsitpro.com/SQLServe...ver_37470.html
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> MattB wrote:
>
> See this page:
> http://www.windowsitpro.com/SQLServe...ver_37470.html
>
Great. Thanks!
Matt
using scripts and osql.exe to perform these with generally good results.
A recent addition to these installs is a script that populates a table
of usernames and encryped passwords, and this is where I'm having
trouble. If I run a script in Query Analyzer that contains this line:
insert into operator (op_code, password, first_name, last_name,
security, operator, salespoint) values ('ADMIN', '??', 'ADMIN',
'ADMIN', 9, 'SYSTEM', 'SYSTEM')
I get predictable results and the encrypted password gets correctly
decrypted in our application.
If I run the very same script using osql like this:
osql.exe -E -i MyScript.sql -o "Output.txt"
The password doesn't end up in the database as '??', it seems to get
changed to '?+-' which obviously wouldn't decrypt correctly in the
application.
So this makes me think there's some kind of translation going on with
these extended characters when running the script via osql. Anyone know
how I can achieve the same results with osql as QA? I'm hoping there's a
switch or something that will do the trick. Thanks!
Matt
MattB wrote:
> Hi, I have some sql commands I incorporate into an install and I've
> been using scripts and osql.exe to perform these with generally good
> results.
> A recent addition to these installs is a script that populates a table
> of usernames and encryped passwords, and this is where I'm having
> trouble. If I run a script in Query Analyzer that contains this line:
> insert into operator (op_code, password, first_name, last_name,
> security, operator, salespoint) values ('ADMIN', '??', 'ADMIN',
> 'ADMIN', 9, 'SYSTEM', 'SYSTEM')
> I get predictable results and the encrypted password gets correctly
> decrypted in our application.
> If I run the very same script using osql like this:
> osql.exe -E -i MyScript.sql -o "Output.txt"
> The password doesn't end up in the database as '??', it seems to
> get changed to '?+-' which obviously wouldn't decrypt correctly in
> the application.
> So this makes me think there's some kind of translation going on with
> these extended characters when running the script via osql. Anyone
> know how I can achieve the same results with osql as QA? I'm hoping
> there's a switch or something that will do the trick. Thanks!
> Matt
See this page:
http://www.windowsitpro.com/SQLServe...ver_37470.html
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> MattB wrote:
>
> See this page:
> http://www.windowsitpro.com/SQLServe...ver_37470.html
>
Great. Thanks!
Matt
Wednesday, March 7, 2012
OSQL command for checking to see if a DB exists
What is the OSQL command for checking to see if a database exists (for MSDE
2000)
I am getting an error after my install that says database does not exist or
access denied. Not sure about the best way to troubleshoot this.
Thanks.
STom
Select DB_ID('Northwind')
6
(1 row(s) affected)
Select DB_ID('SomeDBThatDoesntexists')
NULL
(1 row(s) affected)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"STom" <stombiztalker@.hotmail.com> schrieb im Newsbeitrag
news:eYMp39CbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> What is the OSQL command for checking to see if a database exists (for
> MSDE 2000)
> I am getting an error after my install that says database does not exist
> or access denied. Not sure about the best way to troubleshoot this.
> Thanks.
> STom
>
|||Do you mean from the command line:
osql Select DB_ID('Northwind') ...I'm pretty sure you meant to preface it
with more commands.
The first thing I would like to do is make sure that the password and
username that I think is supposed to be used is being used. If this is
correct, then I would like to check to see if the database exists.
What is the command line for this?
Thanks!
STom
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uv91zDDbFHA.348@.TK2MSFTNGP14.phx.gbl...
> Select DB_ID('Northwind')
> --
> 6
> (1 row(s) affected)
>
> Select DB_ID('SomeDBThatDoesntexists')
> --
> NULL
> (1 row(s) affected)
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "STom" <stombiztalker@.hotmail.com> schrieb im Newsbeitrag
> news:eYMp39CbFHA.2968@.TK2MSFTNGP10.phx.gbl...
>
|||hi STomn
STom wrote:
> I am getting an error after my install that says database does not
> exist or access denied. Not sure about the best way to troubleshoot
> this.
the error you probably get is "SQL Server does not exists or access denied",
which is a generic MDAC error..
please have a look at
http://support.microsoft.com/default...06&Product=sql
if helps..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
STom wrote:
> Do you mean from the command line:
> osql Select DB_ID('Northwind') ...I'm pretty sure you meant to
> preface it with more commands.
actually Jens intended for sure you were already logged in with oSql.exe..
> The first thing I would like to do is make sure that the password and
> username that I think is supposed to be used is being used. If this is
> correct, then I would like to check to see if the database exists.
> What is the command line for this?
there's non command line for that...
you can execute a call to oSql.exe providing a -Q"SELECT ..." and inspect
results, like
c:\..\>osql -Usa -Ppwd -Q"SELECT DB_ID('Northwind')" -oC:\err.txt
wich will output, in the C:\err.txt file, something like
--<--
6
(1 row affected)
if the correct userid and pwd are provided, else something like
--<--
Login failed for user 'sa'.
will be reported if a wrong pwd is provided...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
2000)
I am getting an error after my install that says database does not exist or
access denied. Not sure about the best way to troubleshoot this.
Thanks.
STom
Select DB_ID('Northwind')
6
(1 row(s) affected)
Select DB_ID('SomeDBThatDoesntexists')
NULL
(1 row(s) affected)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"STom" <stombiztalker@.hotmail.com> schrieb im Newsbeitrag
news:eYMp39CbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> What is the OSQL command for checking to see if a database exists (for
> MSDE 2000)
> I am getting an error after my install that says database does not exist
> or access denied. Not sure about the best way to troubleshoot this.
> Thanks.
> STom
>
|||Do you mean from the command line:
osql Select DB_ID('Northwind') ...I'm pretty sure you meant to preface it
with more commands.
The first thing I would like to do is make sure that the password and
username that I think is supposed to be used is being used. If this is
correct, then I would like to check to see if the database exists.
What is the command line for this?
Thanks!
STom
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uv91zDDbFHA.348@.TK2MSFTNGP14.phx.gbl...
> Select DB_ID('Northwind')
> --
> 6
> (1 row(s) affected)
>
> Select DB_ID('SomeDBThatDoesntexists')
> --
> NULL
> (1 row(s) affected)
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "STom" <stombiztalker@.hotmail.com> schrieb im Newsbeitrag
> news:eYMp39CbFHA.2968@.TK2MSFTNGP10.phx.gbl...
>
|||hi STomn
STom wrote:
> I am getting an error after my install that says database does not
> exist or access denied. Not sure about the best way to troubleshoot
> this.
the error you probably get is "SQL Server does not exists or access denied",
which is a generic MDAC error..
please have a look at
http://support.microsoft.com/default...06&Product=sql
if helps..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
STom wrote:
> Do you mean from the command line:
> osql Select DB_ID('Northwind') ...I'm pretty sure you meant to
> preface it with more commands.
actually Jens intended for sure you were already logged in with oSql.exe..
> The first thing I would like to do is make sure that the password and
> username that I think is supposed to be used is being used. If this is
> correct, then I would like to check to see if the database exists.
> What is the command line for this?
there's non command line for that...
you can execute a call to oSql.exe providing a -Q"SELECT ..." and inspect
results, like
c:\..\>osql -Usa -Ppwd -Q"SELECT DB_ID('Northwind')" -oC:\err.txt
wich will output, in the C:\err.txt file, something like
--<--
6
(1 row affected)
if the correct userid and pwd are provided, else something like
--<--
Login failed for user 'sa'.
will be reported if a wrong pwd is provided...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Monday, February 20, 2012
OS requirements for 2005.
Howdy all. Im considering buying a new home PC, and would like to install
the developer edition of SQL 2005 once I have it. Most new PC's that Im
looking at have either WIN XP Home or Media Center. I see the requirements
for SQL 2005 here:
http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
but it doesnt say specifically if these versions of the OS will work or not.
I know it will install fine on XP Pro, but Id like to avoid the purchase
upgrade if possible. Can someone please assist?
TIA, CFRHome and Media Centre does not have an IIS aboard. So all functionality
(like reporting services, http endpoinds, etc) that need IIS won't work
with these versions.
To use all functions in SQL, you need a pro or a server.
ChrisR wrote:
> Howdy all. Im considering buying a new home PC, and would like to install
> the developer edition of SQL 2005 once I have it. Most new PC's that Im
> looking at have either WIN XP Home or Media Center. I see the requirements
> for SQL 2005 here:
> http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
> but it doesnt say specifically if these versions of the OS will work or not.
> I know it will install fine on XP Pro, but Id like to avoid the purchase
> upgrade if possible. Can someone please assist?
> TIA, CFR
>|||Thanks for the speedy response!
"Theo Verweij" <tverweij@.xs4all.nl> wrote in message
news:eAdEHPCAHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Home and Media Centre does not have an IIS aboard. So all functionality
> (like reporting services, http endpoinds, etc) that need IIS won't work
> with these versions.
> To use all functions in SQL, you need a pro or a server.
> ChrisR wrote:
> > Howdy all. Im considering buying a new home PC, and would like to
install
> > the developer edition of SQL 2005 once I have it. Most new PC's that Im
> > looking at have either WIN XP Home or Media Center. I see the
requirements
> > for SQL 2005 here:
> >
> > http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
> >
> > but it doesnt say specifically if these versions of the OS will work or
not.
> > I know it will install fine on XP Pro, but Id like to avoid the purchase
> > upgrade if possible. Can someone please assist?
> >
> > TIA, CFR
> >
> >|||You will need SP2 for Home Edition.
And if you want to do development that requires IIS, this article may help.
Installing IIS on Windows XP Home Edition
http://www.webthang.co.uk/Tuts/tuts_server/iis_xph/pippo_xp.asp
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23c%23WZMCAHHA.4428@.TK2MSFTNGP04.phx.gbl...
> Howdy all. Im considering buying a new home PC, and would like to install
> the developer edition of SQL 2005 once I have it. Most new PC's that Im
> looking at have either WIN XP Home or Media Center. I see the requirements
> for SQL 2005 here:
> http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
> but it doesnt say specifically if these versions of the OS will work or
> not.
> I know it will install fine on XP Pro, but Id like to avoid the purchase
> upgrade if possible. Can someone please assist?
> TIA, CFR
>
the developer edition of SQL 2005 once I have it. Most new PC's that Im
looking at have either WIN XP Home or Media Center. I see the requirements
for SQL 2005 here:
http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
but it doesnt say specifically if these versions of the OS will work or not.
I know it will install fine on XP Pro, but Id like to avoid the purchase
upgrade if possible. Can someone please assist?
TIA, CFRHome and Media Centre does not have an IIS aboard. So all functionality
(like reporting services, http endpoinds, etc) that need IIS won't work
with these versions.
To use all functions in SQL, you need a pro or a server.
ChrisR wrote:
> Howdy all. Im considering buying a new home PC, and would like to install
> the developer edition of SQL 2005 once I have it. Most new PC's that Im
> looking at have either WIN XP Home or Media Center. I see the requirements
> for SQL 2005 here:
> http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
> but it doesnt say specifically if these versions of the OS will work or not.
> I know it will install fine on XP Pro, but Id like to avoid the purchase
> upgrade if possible. Can someone please assist?
> TIA, CFR
>|||Thanks for the speedy response!
"Theo Verweij" <tverweij@.xs4all.nl> wrote in message
news:eAdEHPCAHHA.4348@.TK2MSFTNGP04.phx.gbl...
> Home and Media Centre does not have an IIS aboard. So all functionality
> (like reporting services, http endpoinds, etc) that need IIS won't work
> with these versions.
> To use all functions in SQL, you need a pro or a server.
> ChrisR wrote:
> > Howdy all. Im considering buying a new home PC, and would like to
install
> > the developer edition of SQL 2005 once I have it. Most new PC's that Im
> > looking at have either WIN XP Home or Media Center. I see the
requirements
> > for SQL 2005 here:
> >
> > http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
> >
> > but it doesnt say specifically if these versions of the OS will work or
not.
> > I know it will install fine on XP Pro, but Id like to avoid the purchase
> > upgrade if possible. Can someone please assist?
> >
> > TIA, CFR
> >
> >|||You will need SP2 for Home Edition.
And if you want to do development that requires IIS, this article may help.
Installing IIS on Windows XP Home Edition
http://www.webthang.co.uk/Tuts/tuts_server/iis_xph/pippo_xp.asp
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:%23c%23WZMCAHHA.4428@.TK2MSFTNGP04.phx.gbl...
> Howdy all. Im considering buying a new home PC, and would like to install
> the developer edition of SQL 2005 once I have it. Most new PC's that Im
> looking at have either WIN XP Home or Media Center. I see the requirements
> for SQL 2005 here:
> http://www.microsoft.com/sql/editions/developer/sysreqs.mspx
> but it doesnt say specifically if these versions of the OS will work or
> not.
> I know it will install fine on XP Pro, but Id like to avoid the purchase
> upgrade if possible. Can someone please assist?
> TIA, CFR
>
Subscribe to:
Posts (Atom)