Hi,
try to use xp_sendmail. but you don't need the database in
single user to run dbcc checkdb, you only need single user
mode if you use the repair option.
CMLC
>--Original Message--
>Folks,
>Can you point me to where I can look for more info to
complete this task.
>There is a proprietary app running with SQL 2000 as
backend. Everynight the
>app put DB in single user mode and runs DBCC itself to
check data. That
>runs well except few times it failed to get exclusive
login in order to
>clear single user mode. It causes the app fails to
open. I think I may
>want to run a script at my computer querying the DB and
notify someone by
>using Outlook on my computer. Is there any info that can
tell me how to
>open Outlook from in inside sql script?
>Thanks,
>
>.
>
hi,
to use xp_sendmail we must have SQL mail configured on the SQL box. This is
not my case because I can't touch the box. What I wanted is to use Outlook
on my pc to send email out. Is it possible?
> you don't need the database in single user to run dbcc checkdb, you only
> need single user mode if you use the >repair option
the app is proprietary so ... no touching. It seems that it would repair
data if possible.
"CMLC" <anonymous@.discussions.microsoft.com> wrote in message
news:196d01c4a188$bc87d820$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> try to use xp_sendmail. but you don't need the database in
> single user to run dbcc checkdb, you only need single user
> mode if you use the repair option.
> CMLC
>
> complete this task.
> backend. Everynight the
> check data. That
> login in order to
> open. I think I may
> notify someone by
> tell me how to
Showing posts with label run. Show all posts
Showing posts with label run. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Outer Join Syntax Problems (Multiple Tables)
Hello all--
I'm trying to run a SELECT on 3 tables:Class,Enrolled,Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I knowto be incorrect. Furthermore, it appears that the counts are beingmultiplied together (in one instance, enrolled should be 14, waitingshould be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts areaccurate. The problem only occurs when I try to pull counts from boththe tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
Run this query and you'll see what it's doing:
SELECT Class.Name, Enrolled.StudentID AS EnrolledCount, Waiting.StudentID AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
Something like this will work:
SELECT c.Name, e.EnrolledCount, w.WaitingCount
FROM Class c
LEFT OUTER JOIN
(select classid, COUNT(*) AS EnrolledCount
from Enrolled
group by classid) e
on c.classid = e.classid
LEFT OUTER JOIN
(select classid, COUNT(*) AS WaitingCount
from Waiting
group by classid) w
on c.classid = w.classid
There's other ways to do it with subqueries. Something like this would also work
select c.classname, (select count(*) from enrolled where classid = c.classid) as Enrolled, (select count(*) from waiting where classid = c.classid) as Waiting
from Class c|||Thanks, PDraigh. I used your subquery example and it worked great.
Thanks!
--Jeremy
I'm trying to run a SELECT on 3 tables:Class,Enrolled,Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I knowto be incorrect. Furthermore, it appears that the counts are beingmultiplied together (in one instance, enrolled should be 14, waitingshould be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts areaccurate. The problem only occurs when I try to pull counts from boththe tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
Run this query and you'll see what it's doing:
SELECT Class.Name, Enrolled.StudentID AS EnrolledCount, Waiting.StudentID AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
Something like this will work:
SELECT c.Name, e.EnrolledCount, w.WaitingCount
FROM Class c
LEFT OUTER JOIN
(select classid, COUNT(*) AS EnrolledCount
from Enrolled
group by classid) e
on c.classid = e.classid
LEFT OUTER JOIN
(select classid, COUNT(*) AS WaitingCount
from Waiting
group by classid) w
on c.classid = w.classid
There's other ways to do it with subqueries. Something like this would also work
select c.classname, (select count(*) from enrolled where classid = c.classid) as Enrolled, (select count(*) from waiting where classid = c.classid) as Waiting
from Class c|||Thanks, PDraigh. I used your subquery example and it worked great.
Thanks!
--Jeremy
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
>
sql
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
>
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
>
Out Of Memory
Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.
Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause
Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO
Content of the .asp file is <%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>
Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.
What should i do to solve this problem
Plz Help me out in this
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.
In Detail.
This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.
Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause
Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO
DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)
SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error
IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO
Content of the .asp file is <%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>
Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.
What should i do to solve this problem
Plz Help me out in this
Thanks in advance
Dinesh
I can't tell exactly what is going on here, but it seems to be limited to the web access method, since it works correctly from the batch file. So I would limit my search to that area - you might want to try turning on garbage collection:
http://support.microsoft.com/kb/911716
Buck Woody
Out of Memory
Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.
Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause
Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO
Content of the .asp file is
<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>
Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.
What should i do to solve this problem
Plz Help me out in this
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.
In Detail.
This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.
Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause
Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO
DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)
SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error
IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO
Content of the .asp file is
<%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>
Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.
What should i do to solve this problem
Plz Help me out in this
Thanks in advance
Dinesh
You need to troubleshoot from ASP side. It is unlikely this has anything to do with SQL or the batch file itself.
Out Of Memory
Hi Guys,
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.
Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause
Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO
Content of the .asp file is <%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>
Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.
What should i do to solve this problem
Plz Help me out in this
What i'm trying here is to run a .bat file containing Osql Command's on the server using asp. Which is Generating Out Of Memory Error.
In Detail.
This .bat file is having a Osql command to connect to a database with a parameter to run a .sql file and output parameter to give to output in .txt file. This is working fine if I simply run the .bat file on the server. its working fine no probes, but when I try the same from a .asp file running this .asp file from the same machine its giving me the Out of Memory Error.
Content of the .bat file is
@.cd
@.osql -U user -P Password -i c:\BatchUpdateTest\test.sql -o c:\BatchUpdateTest\test.txt
@.pause
Content of the .sql file is
SELECT * INTO ARC_TABLENAME TABLENAME
GO
DECLARE @.del_error int
DECLARE @.description VARCHAR(1000)
SELECT @.del_error= @.@.ERROR
SELECT @.description= description FROM MASTER..SYSMESSAGES WHERE error = @.del_error
IF @.del_error = 0
SELECT * FROM ARC_TABLENAME
ELSE
PRINT @.description+' ARC_TABLENAME'
GO
Content of the .asp file is <%
var server_shell = Server.CreateObject("wscript.shell")
try{
server_shell.Run("c:\BatchUpdateTest\test.bat",1)
}catch(e){
Response.Write(e.description)
}
%>
Actualy when i run this code in the .bat file from .asp it did not give any error. So what i did is put a try catch block to trap the error which gives me this error Out of memory.
What should i do to solve this problem
Plz Help me out in this
Thanks in advance
Dinesh
I can't tell exactly what is going on here, but it seems to be limited to the web access method, since it works correctly from the batch file. So I would limit my search to that area - you might want to try turning on garbage collection:
http://support.microsoft.com/kb/911716
Buck Woody
Out of Memery Error while reading records
Hi,
I'm using CR9 with PostgreSQL.
While trying to run a report for a particular date, it returns
"Failed to open rowset"
"Out of memory while trying to read tuples"
But while running the same thing as a sql query from Postgres by limiting the records count it's working fine. So I think the problem is with the crystal reports.
Is there any way to limit the no of records? Also I tried to limit the records in CR RAS. It's not working.
Any idea, pls. help me
ThanksWhat's the maximum size of the table/ Max no of records Crystal Reports Support?
OR
Is it depends on the physical size of the machine.
I caught by the error "Out of Memory while reading tuples". The same table is working while filtering the records by another field which returns smaller no of records.
Have any idea, pls share with me.
Thankssql
I'm using CR9 with PostgreSQL.
While trying to run a report for a particular date, it returns
"Failed to open rowset"
"Out of memory while trying to read tuples"
But while running the same thing as a sql query from Postgres by limiting the records count it's working fine. So I think the problem is with the crystal reports.
Is there any way to limit the no of records? Also I tried to limit the records in CR RAS. It's not working.
Any idea, pls. help me
ThanksWhat's the maximum size of the table/ Max no of records Crystal Reports Support?
OR
Is it depends on the physical size of the machine.
I caught by the error "Out of Memory while reading tuples". The same table is working while filtering the records by another field which returns smaller no of records.
Have any idea, pls share with me.
Thankssql
Out of disk space
What is the impact to SQL Server 2000 (sp1) databases should the disk
run out of space?It depends on whether there is free space in the database files or not. If the database files are
full and cannot autogrow, you get below errors when more space is needed to accommodate your
modifications:
1105 for data files
9002 for log files
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1163694924.709204.294940@.e3g2000cwe.googlegroups.com...
> What is the impact to SQL Server 2000 (sp1) databases should the disk
> run out of space?
>
run out of space?It depends on whether there is free space in the database files or not. If the database files are
full and cannot autogrow, you get below errors when more space is needed to accommodate your
modifications:
1105 for data files
9002 for log files
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1163694924.709204.294940@.e3g2000cwe.googlegroups.com...
> What is the impact to SQL Server 2000 (sp1) databases should the disk
> run out of space?
>
Monday, March 12, 2012
osql will not come up MSDE (sp3) or earlier
When I try to run osql, I get the following error:
c:\program files\Microsoft SQL Server\80\Tools\Binn\
> osql -E -d CCOP or osql -U sa -P AStrongPassword -d CCOP
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
I can start the instance "CCOP" from sqlmangr without any
issue, but when I try to run osql against the database, I
can never get past the startup
-d is the switch for the database name, not the instance name. Use the -S
switch to specify the instance name.
If you are not sure of the instance name, issue a osql -L for a list of SQL
Server instances.
See
http://msdn.microsoft.com/library/de...ta_01_2r1e.asp
for more information on running the osql utility.
Terri Morton
MVP - ASP/ASP.NET
"Ed H" <anonymous@.discussions.microsoft.com> wrote in message
news:0f4301c4ff5e$4c209930$a401280a@.phx.gbl...
> When I try to run osql, I get the following error:
> c:\program files\Microsoft SQL Server\80\Tools\Binn\
>
> [Shared Memory]SQL Server does not exist or access denied.
> [Shared Memory]ConnectionOpen (Connect()).
> I can start the instance "CCOP" from sqlmangr without any
> issue, but when I try to run osql against the database, I
> can never get past the startup
|||
>--Original Message--
>-d is the switch for the database name, not the instance
name. Use the -S
>switch to specify the instance name.
>If you are not sure of the instance name, issue a osql -
L for a list of SQL
>Server instances.
>See
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/acdata/ac_8_mta_01_2r1e.asp
>for more information on running the osql utility.
>Terri Morton
>MVP - ASP/ASP.NET
>"Ed H" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f4301c4ff5e$4c209930$a401280a@.phx.gbl...
CCOP[vbcol=seagreen]
denied.[vbcol=seagreen]
any[vbcol=seagreen]
database, I
>
>.
> Thx Terri. I had to re-install amoung other things.
The MSDN Help was a very valuable.
c:\program files\Microsoft SQL Server\80\Tools\Binn\
> osql -E -d CCOP or osql -U sa -P AStrongPassword -d CCOP
[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).
I can start the instance "CCOP" from sqlmangr without any
issue, but when I try to run osql against the database, I
can never get past the startup
-d is the switch for the database name, not the instance name. Use the -S
switch to specify the instance name.
If you are not sure of the instance name, issue a osql -L for a list of SQL
Server instances.
See
http://msdn.microsoft.com/library/de...ta_01_2r1e.asp
for more information on running the osql utility.
Terri Morton
MVP - ASP/ASP.NET
"Ed H" <anonymous@.discussions.microsoft.com> wrote in message
news:0f4301c4ff5e$4c209930$a401280a@.phx.gbl...
> When I try to run osql, I get the following error:
> c:\program files\Microsoft SQL Server\80\Tools\Binn\
>
> [Shared Memory]SQL Server does not exist or access denied.
> [Shared Memory]ConnectionOpen (Connect()).
> I can start the instance "CCOP" from sqlmangr without any
> issue, but when I try to run osql against the database, I
> can never get past the startup
|||
>--Original Message--
>-d is the switch for the database name, not the instance
name. Use the -S
>switch to specify the instance name.
>If you are not sure of the instance name, issue a osql -
L for a list of SQL
>Server instances.
>See
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/acdata/ac_8_mta_01_2r1e.asp
>for more information on running the osql utility.
>Terri Morton
>MVP - ASP/ASP.NET
>"Ed H" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0f4301c4ff5e$4c209930$a401280a@.phx.gbl...
CCOP[vbcol=seagreen]
denied.[vbcol=seagreen]
any[vbcol=seagreen]
database, I
>
>.
> Thx Terri. I had to re-install amoung other things.
The MSDN Help was a very valuable.
Friday, March 9, 2012
osql using :r and GO
I have a script that calls other scripts using :r. I am using osql to run th
e
script. I am getting an error that seems to indicate GO is not allowed in a
:r script, yet it is required to create a table and trigger in the same
script. How do I do this?
I have greatly simplified the scripts, and :r files must be used. Here are
the details:
File releaseA.sql:
:r code/releaseA1.SQL
File releaseA1.SQL
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
GO
CREATE TRIGGER A1_RBUI
ON A1 FOR INSERT, UPDATE
AS
BEGIN
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
GO
COMMAND:
osql -i releaseA.sql
OUTPUT:
Incorrect syntax near the keyword 'CREATE'.
Msg 111, Level 15, State 1, Server MIRAGE, Line 35
'CREATE TRIGGER' must be the first statement in a query batch
Msg 170, Level 15, State 1, Server MIRAGE, Line 49
Line 49: Incorrect syntax near 'GO'.Hi
The following will work!
File f.sql
:r F1.sql
GO
:r F2.sql
File F1.sql
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
File F2.sql
CREATE TRIGGER A1_RBUI ON A1 FOR INSERT, UPDATE
AS
BEGIN
IF @.@.ROWCOUNT = 0 RETURN
SET NOCOUNT ON
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
John
"Bevo" wrote:
> I have a script that calls other scripts using :r. I am using osql to run
the
> script. I am getting an error that seems to indicate GO is not allowed in
a
> :r script, yet it is required to create a table and trigger in the same
> script. How do I do this?
> I have greatly simplified the scripts, and :r files must be used. Here are
> the details:
> File releaseA.sql:
> :r code/releaseA1.SQL
>
> File releaseA1.SQL
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
> GO
> CREATE TRIGGER A1_RBUI
> ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> GO
> COMMAND:
> osql -i releaseA.sql
> OUTPUT:
> Incorrect syntax near the keyword 'CREATE'.
> Msg 111, Level 15, State 1, Server MIRAGE, Line 35
> 'CREATE TRIGGER' must be the first statement in a query batch
> Msg 170, Level 15, State 1, Server MIRAGE, Line 49
> Line 49: Incorrect syntax near 'GO'.|||Thank you for the solution John, yet I was hoping there would be another way
.
This approach would require a seperate file for each trigger. Considering ou
r
database may have at least one trigger on each of our 100+ tables, this
results in alot of trigger files...
"John Bell" wrote:
> Hi
> The following will work!
> File f.sql
> :r F1.sql
> GO
> :r F2.sql
>
> File F1.sql
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
>
> File F2.sql
> CREATE TRIGGER A1_RBUI ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> IF @.@.ROWCOUNT = 0 RETURN
> SET NOCOUNT ON
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> John
>
> "Bevo" wrote:
>|||Hi
In general I would consider a trigger as a separate object to the table
anyhow, therefore they will be kept separately in version control. With
a decent file naming convention it is not difficult to know which
tables have triggers (something that you can not currently tell from
your files!), and the fact that are in different files make no
difference once you have a scripted build process.
John|||The file names provided were for demostrative purposes only.
Another limitation I have found is that osql will not allow nested :r files.
This is not allowed:
F1.sql
:r F2.sql
F2.sql
:r F2_1.sql
This limits the source directory structure to just one tier, so one cannot
group functionality into tiered directories.
"John Bell" wrote:
> Hi
> In general I would consider a trigger as a separate object to the table
> anyhow, therefore they will be kept separately in version control. With
> a decent file naming convention it is not difficult to know which
> tables have triggers (something that you can not currently tell from
> your files!), and the fact that are in different files make no
> difference once you have a scripted build process.
> John
>|||Hi
I think that you may be trying to make this overly complicated.
http://tinyurl.com/5299q
John
e
script. I am getting an error that seems to indicate GO is not allowed in a
:r script, yet it is required to create a table and trigger in the same
script. How do I do this?
I have greatly simplified the scripts, and :r files must be used. Here are
the details:
File releaseA.sql:
:r code/releaseA1.SQL
File releaseA1.SQL
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
GO
CREATE TRIGGER A1_RBUI
ON A1 FOR INSERT, UPDATE
AS
BEGIN
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
GO
COMMAND:
osql -i releaseA.sql
OUTPUT:
Incorrect syntax near the keyword 'CREATE'.
Msg 111, Level 15, State 1, Server MIRAGE, Line 35
'CREATE TRIGGER' must be the first statement in a query batch
Msg 170, Level 15, State 1, Server MIRAGE, Line 49
Line 49: Incorrect syntax near 'GO'.Hi
The following will work!
File f.sql
:r F1.sql
GO
:r F2.sql
File F1.sql
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
File F2.sql
CREATE TRIGGER A1_RBUI ON A1 FOR INSERT, UPDATE
AS
BEGIN
IF @.@.ROWCOUNT = 0 RETURN
SET NOCOUNT ON
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
John
"Bevo" wrote:
> I have a script that calls other scripts using :r. I am using osql to run
the
> script. I am getting an error that seems to indicate GO is not allowed in
a
> :r script, yet it is required to create a table and trigger in the same
> script. How do I do this?
> I have greatly simplified the scripts, and :r files must be used. Here are
> the details:
> File releaseA.sql:
> :r code/releaseA1.SQL
>
> File releaseA1.SQL
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
> GO
> CREATE TRIGGER A1_RBUI
> ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> GO
> COMMAND:
> osql -i releaseA.sql
> OUTPUT:
> Incorrect syntax near the keyword 'CREATE'.
> Msg 111, Level 15, State 1, Server MIRAGE, Line 35
> 'CREATE TRIGGER' must be the first statement in a query batch
> Msg 170, Level 15, State 1, Server MIRAGE, Line 49
> Line 49: Incorrect syntax near 'GO'.|||Thank you for the solution John, yet I was hoping there would be another way
.
This approach would require a seperate file for each trigger. Considering ou
r
database may have at least one trigger on each of our 100+ tables, this
results in alot of trigger files...
"John Bell" wrote:
> Hi
> The following will work!
> File f.sql
> :r F1.sql
> GO
> :r F2.sql
>
> File F1.sql
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
>
> File F2.sql
> CREATE TRIGGER A1_RBUI ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> IF @.@.ROWCOUNT = 0 RETURN
> SET NOCOUNT ON
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> John
>
> "Bevo" wrote:
>|||Hi
In general I would consider a trigger as a separate object to the table
anyhow, therefore they will be kept separately in version control. With
a decent file naming convention it is not difficult to know which
tables have triggers (something that you can not currently tell from
your files!), and the fact that are in different files make no
difference once you have a scripted build process.
John|||The file names provided were for demostrative purposes only.
Another limitation I have found is that osql will not allow nested :r files.
This is not allowed:
F1.sql
:r F2.sql
F2.sql
:r F2_1.sql
This limits the source directory structure to just one tier, so one cannot
group functionality into tiered directories.
"John Bell" wrote:
> Hi
> In general I would consider a trigger as a separate object to the table
> anyhow, therefore they will be kept separately in version control. With
> a decent file naming convention it is not difficult to know which
> tables have triggers (something that you can not currently tell from
> your files!), and the fact that are in different files make no
> difference once you have a scripted build process.
> John
>|||Hi
I think that you may be trying to make this overly complicated.
http://tinyurl.com/5299q
John
osql using :r and GO
I have a script that calls other scripts using :r. I am using osql to run th
e
script. I am getting an error that seems to indicate GO is not allowed in a
:r script, yet it is required to create a table and trigger in the same
script. How do I do this?
I have greatly simplified the scripts, and :r files must be used. Here are
the details:
File releaseA.sql:
:r code/releaseA1.SQL
File releaseA1.SQL
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
GO
CREATE TRIGGER A1_RBUI
ON A1 FOR INSERT, UPDATE
AS
BEGIN
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
GO
COMMAND:
osql -i releaseA.sql
OUTPUT:
Incorrect syntax near the keyword 'CREATE'.
Msg 111, Level 15, State 1, Server MIRAGE, Line 35
'CREATE TRIGGER' must be the first statement in a query batch
Msg 170, Level 15, State 1, Server MIRAGE, Line 49
Line 49: Incorrect syntax near 'GO'.As you have noticed, the GO cannot be included in the :r script, because the
osql tool does not try to examine or parse the contents of the file, but
sends it directly to SQL Server for parsing, and the SQL Server doesn't like
GO.
Normally, GO is caught by osql itself, and never makes it to the back end.
Do you have to do this from within an osql session? A file with embedded GO
can be specified as a command line parameter to osql.
If the scripts have to call other scripts, can those other scripts be
editted at all? Instead of having a script with :r, maybe the script could
call xp_cmdshell which would call osql with the embedded script as a
parameter.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:B2348B52-4A37-4330-88E4-2837200E3036@.microsoft.com...
>I have a script that calls other scripts using :r. I am using osql to run
>the
> script. I am getting an error that seems to indicate GO is not allowed in
> a
> :r script, yet it is required to create a table and trigger in the same
> script. How do I do this?
> I have greatly simplified the scripts, and :r files must be used. Here are
> the details:
> File releaseA.sql:
> :r code/releaseA1.SQL
>
> File releaseA1.SQL
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
> GO
> CREATE TRIGGER A1_RBUI
> ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> GO
> COMMAND:
> osql -i releaseA.sql
> OUTPUT:
> Incorrect syntax near the keyword 'CREATE'.
> Msg 111, Level 15, State 1, Server MIRAGE, Line 35
> 'CREATE TRIGGER' must be the first statement in a query batch
> Msg 170, Level 15, State 1, Server MIRAGE, Line 49
> Line 49: Incorrect syntax near 'GO'.|||It seems :r files cannot be tiered, is this not allowed:
F1.sql
:r F2.sql
F2.sql
:r F2_1.sql
"Kalen Delaney" wrote:
> As you have noticed, the GO cannot be included in the :r script, because t
he
> osql tool does not try to examine or parse the contents of the file, but
> sends it directly to SQL Server for parsing, and the SQL Server doesn't li
ke
> GO.
> Normally, GO is caught by osql itself, and never makes it to the back end.
> Do you have to do this from within an osql session? A file with embedded G
O
> can be specified as a command line parameter to osql.
> If the scripts have to call other scripts, can those other scripts be
> editted at all? Instead of having a script with :r, maybe the script could
> call xp_cmdshell which would call osql with the embedded script as a
> parameter.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:B2348B52-4A37-4330-88E4-2837200E3036@.microsoft.com...
>
>|||So use xp_cmdshell and don't use :r at all.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:170F0DC1-B0B1-4183-8540-34509092778D@.microsoft.com...
> It seems :r files cannot be tiered, is this not allowed:
> F1.sql
> :r F2.sql
> F2.sql
> :r F2_1.sql
>
> "Kalen Delaney" wrote:
>
e
script. I am getting an error that seems to indicate GO is not allowed in a
:r script, yet it is required to create a table and trigger in the same
script. How do I do this?
I have greatly simplified the scripts, and :r files must be used. Here are
the details:
File releaseA.sql:
:r code/releaseA1.SQL
File releaseA1.SQL
CREATE TABLE A1 (
LOG_ID BIGINT IDENTITY NOT NULL,
CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
)
GO
CREATE TRIGGER A1_RBUI
ON A1 FOR INSERT, UPDATE
AS
BEGIN
UPDATE A1
SET CTRL_UPDATE_DT = GETDATE(),
CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
FROM INSERTED I
INNER JOIN A1
ON I.LOG_ID = A1.LOG_ID
END
GO
COMMAND:
osql -i releaseA.sql
OUTPUT:
Incorrect syntax near the keyword 'CREATE'.
Msg 111, Level 15, State 1, Server MIRAGE, Line 35
'CREATE TRIGGER' must be the first statement in a query batch
Msg 170, Level 15, State 1, Server MIRAGE, Line 49
Line 49: Incorrect syntax near 'GO'.As you have noticed, the GO cannot be included in the :r script, because the
osql tool does not try to examine or parse the contents of the file, but
sends it directly to SQL Server for parsing, and the SQL Server doesn't like
GO.
Normally, GO is caught by osql itself, and never makes it to the back end.
Do you have to do this from within an osql session? A file with embedded GO
can be specified as a command line parameter to osql.
If the scripts have to call other scripts, can those other scripts be
editted at all? Instead of having a script with :r, maybe the script could
call xp_cmdshell which would call osql with the embedded script as a
parameter.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:B2348B52-4A37-4330-88E4-2837200E3036@.microsoft.com...
>I have a script that calls other scripts using :r. I am using osql to run
>the
> script. I am getting an error that seems to indicate GO is not allowed in
> a
> :r script, yet it is required to create a table and trigger in the same
> script. How do I do this?
> I have greatly simplified the scripts, and :r files must be used. Here are
> the details:
> File releaseA.sql:
> :r code/releaseA1.SQL
>
> File releaseA1.SQL
> CREATE TABLE A1 (
> LOG_ID BIGINT IDENTITY NOT NULL,
> CTRL_INSERT_DT DATETIME DEFAULT GETDATE() NOT NULL,
> CTRL_UPDATE_DT DATETIME DEFAULT GETDATE() NOT NULL
> )
> GO
> CREATE TRIGGER A1_RBUI
> ON A1 FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE A1
> SET CTRL_UPDATE_DT = GETDATE(),
> CTRL_INSERT_DT = ISNULL(I.CTRL_INSERT_DT, GETDATE())
> FROM INSERTED I
> INNER JOIN A1
> ON I.LOG_ID = A1.LOG_ID
> END
> GO
> COMMAND:
> osql -i releaseA.sql
> OUTPUT:
> Incorrect syntax near the keyword 'CREATE'.
> Msg 111, Level 15, State 1, Server MIRAGE, Line 35
> 'CREATE TRIGGER' must be the first statement in a query batch
> Msg 170, Level 15, State 1, Server MIRAGE, Line 49
> Line 49: Incorrect syntax near 'GO'.|||It seems :r files cannot be tiered, is this not allowed:
F1.sql
:r F2.sql
F2.sql
:r F2_1.sql
"Kalen Delaney" wrote:
> As you have noticed, the GO cannot be included in the :r script, because t
he
> osql tool does not try to examine or parse the contents of the file, but
> sends it directly to SQL Server for parsing, and the SQL Server doesn't li
ke
> GO.
> Normally, GO is caught by osql itself, and never makes it to the back end.
> Do you have to do this from within an osql session? A file with embedded G
O
> can be specified as a command line parameter to osql.
> If the scripts have to call other scripts, can those other scripts be
> editted at all? Instead of having a script with :r, maybe the script could
> call xp_cmdshell which would call osql with the embedded script as a
> parameter.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:B2348B52-4A37-4330-88E4-2837200E3036@.microsoft.com...
>
>|||So use xp_cmdshell and don't use :r at all.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:170F0DC1-B0B1-4183-8540-34509092778D@.microsoft.com...
> It seems :r files cannot be tiered, is this not allowed:
> F1.sql
> :r F2.sql
> F2.sql
> :r F2_1.sql
>
> "Kalen Delaney" wrote:
>
Osql Syntax Help
Dear friends,
I was trying to run this script from a dos batch file under win xp but it's not working. Please help with the syntax.
================================================== =======
OSQL -U sa -P samsde -S firebird /Q "RESTORE DATABASE NAVIMEX FROM DISK = 'C:\30704' WITH
REPLACE, MOVE'NAVIMEX_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\NAVIMEX_Data.MDF',MOVE 'NAVIMEX_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\NAVIMEX_LOG.LDF'" QUIT
================================================== ========
Please provide me with the correct syntax to be put in a 'restore.bat' file :rolleyes:
Thanks in advance.
HotBirdWhat errors are you getting? You can add "/o<filename>" or use command line redirection to capture all errors. And don't use QUIT unless your RESTORE was unsuccessful.|||make sure that you dont hit enter after "microsoft sql" in the path
try a shorter path
also i believe that when you issue a restore cmd from disk, you have to specify the file name
ex
from disk = 'c:\data\northwind.mdf'
to back up robert, what errors do you get?|||Dear Friends,
Thank you for your replies,
The script works but I'm facing a problem while trying to restore from a network drive or a remote path (\\xxx.xxx.xxx.xxx\mypath\filename)
can you help please?
Regards,
Samer Chaer :rolleyes:|||It's usually due to a lack of necessary permissions on behalf of the security context under which the process is invoked.
I was trying to run this script from a dos batch file under win xp but it's not working. Please help with the syntax.
================================================== =======
OSQL -U sa -P samsde -S firebird /Q "RESTORE DATABASE NAVIMEX FROM DISK = 'C:\30704' WITH
REPLACE, MOVE'NAVIMEX_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\NAVIMEX_Data.MDF',MOVE 'NAVIMEX_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\NAVIMEX_LOG.LDF'" QUIT
================================================== ========
Please provide me with the correct syntax to be put in a 'restore.bat' file :rolleyes:
Thanks in advance.
HotBirdWhat errors are you getting? You can add "/o<filename>" or use command line redirection to capture all errors. And don't use QUIT unless your RESTORE was unsuccessful.|||make sure that you dont hit enter after "microsoft sql" in the path
try a shorter path
also i believe that when you issue a restore cmd from disk, you have to specify the file name
ex
from disk = 'c:\data\northwind.mdf'
to back up robert, what errors do you get?|||Dear Friends,
Thank you for your replies,
The script works but I'm facing a problem while trying to restore from a network drive or a remote path (\\xxx.xxx.xxx.xxx\mypath\filename)
can you help please?
Regards,
Samer Chaer :rolleyes:|||It's usually due to a lack of necessary permissions on behalf of the security context under which the process is invoked.
OSQL SA Password Restrictions involving # & ^ symbols
Is anyone aware of such a restriction? All my SQL Jobs etc run fine with my
SA account & password that includes both a # and a ^, but when I try to run
jobs from OSQL the job fails with a bad password error. Naturally, I have
double checked the password entry. I have also tried running a simple oqsl
job under an account that uses just plain aplhanumeric characters in the
password & that runs fine.
Thanks,
Nick.
use double quotation marks (")... osql -S<server> -U<user> -P <"password">
"Nick" wrote:
> Is anyone aware of such a restriction? All my SQL Jobs etc run fine with my
> SA account & password that includes both a # and a ^, but when I try to run
> jobs from OSQL the job fails with a bad password error. Naturally, I have
> double checked the password entry. I have also tried running a simple oqsl
> job under an account that uses just plain aplhanumeric characters in the
> password & that runs fine.
> Thanks,
> Nick.
SA account & password that includes both a # and a ^, but when I try to run
jobs from OSQL the job fails with a bad password error. Naturally, I have
double checked the password entry. I have also tried running a simple oqsl
job under an account that uses just plain aplhanumeric characters in the
password & that runs fine.
Thanks,
Nick.
use double quotation marks (")... osql -S<server> -U<user> -P <"password">
"Nick" wrote:
> Is anyone aware of such a restriction? All my SQL Jobs etc run fine with my
> SA account & password that includes both a # and a ^, but when I try to run
> jobs from OSQL the job fails with a bad password error. Naturally, I have
> double checked the password entry. I have also tried running a simple oqsl
> job under an account that uses just plain aplhanumeric characters in the
> password & that runs fine.
> Thanks,
> Nick.
OSQL SA Password Restrictions involving # & ^ symbols
Is anyone aware of such a restriction? All my SQL Jobs etc run fine with my
SA account & password that includes both a # and a ^, but when I try to run
jobs from OSQL the job fails with a bad password error. Naturally, I have
double checked the password entry. I have also tried running a simple oqsl
job under an account that uses just plain aplhanumeric characters in the
password & that runs fine.
Thanks,
Nick.use double quotation marks (")... osql -S<server> -U<user> -P <"password">
"Nick" wrote:
> Is anyone aware of such a restriction? All my SQL Jobs etc run fine with m
y
> SA account & password that includes both a # and a ^, but when I try to r
un
> jobs from OSQL the job fails with a bad password error. Naturally, I have
> double checked the password entry. I have also tried running a simple oqsl
> job under an account that uses just plain aplhanumeric characters in the
> password & that runs fine.
> Thanks,
> Nick.
SA account & password that includes both a # and a ^, but when I try to run
jobs from OSQL the job fails with a bad password error. Naturally, I have
double checked the password entry. I have also tried running a simple oqsl
job under an account that uses just plain aplhanumeric characters in the
password & that runs fine.
Thanks,
Nick.use double quotation marks (")... osql -S<server> -U<user> -P <"password">
"Nick" wrote:
> Is anyone aware of such a restriction? All my SQL Jobs etc run fine with m
y
> SA account & password that includes both a # and a ^, but when I try to r
un
> jobs from OSQL the job fails with a bad password error. Naturally, I have
> double checked the password entry. I have also tried running a simple oqsl
> job under an account that uses just plain aplhanumeric characters in the
> password & that runs fine.
> Thanks,
> Nick.
OSQL SA Password Restrictions involving # & ^ symbols
Is anyone aware of such a restriction? All my SQL Jobs etc run fine with my
SA account & password that includes both a # and a ^, but when I try to run
jobs from OSQL the job fails with a bad password error. Naturally, I have
double checked the password entry. I have also tried running a simple oqsl
job under an account that uses just plain aplhanumeric characters in the
password & that runs fine.
Thanks,
Nick.use double quotation marks (")... osql -S<server> -U<user> -P <"password">
"Nick" wrote:
> Is anyone aware of such a restriction? All my SQL Jobs etc run fine with my
> SA account & password that includes both a # and a ^, but when I try to run
> jobs from OSQL the job fails with a bad password error. Naturally, I have
> double checked the password entry. I have also tried running a simple oqsl
> job under an account that uses just plain aplhanumeric characters in the
> password & that runs fine.
> Thanks,
> Nick.
SA account & password that includes both a # and a ^, but when I try to run
jobs from OSQL the job fails with a bad password error. Naturally, I have
double checked the password entry. I have also tried running a simple oqsl
job under an account that uses just plain aplhanumeric characters in the
password & that runs fine.
Thanks,
Nick.use double quotation marks (")... osql -S<server> -U<user> -P <"password">
"Nick" wrote:
> Is anyone aware of such a restriction? All my SQL Jobs etc run fine with my
> SA account & password that includes both a # and a ^, but when I try to run
> jobs from OSQL the job fails with a bad password error. Naturally, I have
> double checked the password entry. I have also tried running a simple oqsl
> job under an account that uses just plain aplhanumeric characters in the
> password & that runs fine.
> Thanks,
> Nick.
OSQL restore Database
Hi
I need to do a restore similar to the Restore Database in SQL Enterprise Manager using OSQL. I created an app to do the OSQL Run script command and it works fine. Is this the best way to create a setup to restore the database? Any ideas please!!
Best Regards
PhilipI'd use a VBS script. Easier then a full application...|||Thanks
OSQL prompts for password
Hello,
I have a batch file setup for backing up an msde 2000 database but the only
way it will run is by inserting -Usa -Ppassword. I don't want to expose the
sa password and I would like to use windows authentication so I've included
the -E switch. However, it prompts for a password and no matter what I put
in it fails with invalid password and my user login name. The password that
I enter is correct and the server is setup with Windows Authentication. The
batch file is called from an application and I don't want the user to to
have to enter a password, I just want the process to run when started.
Here's How I'm calling the sql script.
OSQL -E -iTestBackup.sql
TIA
Vic
Problem Solved! The switch should be uppercase E and I had lower case which
means echo.
I have a batch file setup for backing up an msde 2000 database but the only
way it will run is by inserting -Usa -Ppassword. I don't want to expose the
sa password and I would like to use windows authentication so I've included
the -E switch. However, it prompts for a password and no matter what I put
in it fails with invalid password and my user login name. The password that
I enter is correct and the server is setup with Windows Authentication. The
batch file is called from an application and I don't want the user to to
have to enter a password, I just want the process to run when started.
Here's How I'm calling the sql script.
OSQL -E -iTestBackup.sql
TIA
Vic
Problem Solved! The switch should be uppercase E and I had lower case which
means echo.
OSQL prompts for password
Hello,
I have a batch file setup for backing up an msde 2000 database but the only
way it will run is by inserting -Usa -Ppassword. I don't want to expose the
sa password and I would like to use windows authentication so I've included
the -E switch. However, it prompts for a password and no matter what I put
in it fails with invalid password and my user login name. The password that
I enter is correct and the server is setup with Windows Authentication. The
batch file is called from an application and I don't want the user to to
have to enter a password, I just want the process to run when started.
Here's How I'm calling the sql script.
OSQL -E -iTestBackup.sql
TIA
VicProblem Solved! The switch should be uppercase E and I had lower case which
means echo.
I have a batch file setup for backing up an msde 2000 database but the only
way it will run is by inserting -Usa -Ppassword. I don't want to expose the
sa password and I would like to use windows authentication so I've included
the -E switch. However, it prompts for a password and no matter what I put
in it fails with invalid password and my user login name. The password that
I enter is correct and the server is setup with Windows Authentication. The
batch file is called from an application and I don't want the user to to
have to enter a password, I just want the process to run when started.
Here's How I'm calling the sql script.
OSQL -E -iTestBackup.sql
TIA
VicProblem Solved! The switch should be uppercase E and I had lower case which
means echo.
Subscribe to:
Posts (Atom)