execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.com
Hi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>
|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor
|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com
|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>

execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
--
Message posted via http://www.sqlmonster.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrés Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
--
Message posted via http://www.sqlmonster.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example below.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via SQLMonster.com" wrote:
> Hi,
> I am trying to execute a store procedure from the query analyzer, but I get
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.sqlmonster.com
>sql

execure store procedure from Query Analizer

Hi,
I am trying to execute a store procedure from the query analyzer, but I get
following error "Could not find stored procedure 'GetSubscriptions'", my
store procedure has 3 parameters.
I wrote the following statements to the Query Analizer:
Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec GetSubscriptions; 3
What can I do?
Tnx in advance
Isambella
Message posted via http://www.droptable.comHi Isambella
You try this way
set @.Par1 = 1
set @.Par2 = 'some value'
set @.Par2 = 'some value'
exec <database_name>..GetSubscriptions @.par1, @.par2, @.par3
Probably you are trying to execute the SP in a different database
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
---
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>|||On Thu, 12 May 2005 23:02:02 -0700, Chandra wrote:
> Probably you are trying to execute the SP in a different database
And if that doesn't help, check who the owner of the procedure is. You
might have to run it using something like: "DATABASE.USER.GetSubscriptions".
HTH,
Andrs Taylor|||I did the following:
exec GetSubscriptions @.Par1 = 1, @.Par2 = 'some value', @.Par3 = 'some value'
Its ok now
Thx
Isambella
Message posted via http://www.droptable.com|||I ran your code and recieved the same error. You need to check to see if
stored procedure exist and if so drop it and then write create procedure
process before you can execute the paramaters. Please refer to example belo
w.
-- creating the store procedure
IF EXISTS (SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [ID] = OBJECT_ID(N'Create_NEWSP' )
AND OBJECTPROPERTY =[ID],N'IsProcedure') =1)
BEGIN-- Drop Stored Procedure
DROP PROCEDURE Create_NEWSP
END-- Drop Stored Procedure
GO
CREATE PROCEDURE Create_NEWSP
@.p1 INT = 0,
@.p2 INT = 0
AS
SELECT @.p1, @.p2
GO
-- execute the store procedure
EXECUTE Create_NEWSP 1, 2
GO
"Isambella via droptable.com" wrote:

> Hi,
> I am trying to execute a store procedure from the query analyzer, but I ge
t
> following error "Could not find stored procedure 'GetSubscriptions'", my
> store procedure has 3 parameters.
> I wrote the following statements to the Query Analizer:
> Declare @.Par1 as int, @.Par2 as varchar , @.Par3 as varchar
> set @.Par1 = 1
> set @.Par2 = 'some value'
> set @.Par2 = 'some value'
> exec GetSubscriptions; 3
> What can I do?
> Tnx in advance
> Isambella
> --
> Message posted via http://www.droptable.com
>

exec xp_sendmail error on SQL Server 2000

I have try to send a mail via xp_sendmail in Query Analyzer and it succeeded.
So I try to have it executed in a trigger but it failed.

Here is the trigger creation script and error message

use mlcb
go
if exists (select name
from sysobjects
where name = 'test' and
type = 'TR')
DROP TRIGGER TEST
GO

CREATE TRIGGER test on mlcb.dbo.trans_errlog
for insert
as
declare @.email_subject varchar(100),
@.email_content varchar(4000),
@.email_recipients varchar(50)

set @.email_subject='SQL Mail test mail'
set @.email_recipients='some@.world.com.tw'
set @.email_content='this is a test mail, don't reply this mail'

exec master.dbo.xp_sendmail @.recipients=@.email_recipients,@.subject=@.email_subj ect,@.message=@.email_content
GO

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Appreciate any prompt reply.

JDbefore we get to your problem, let's talk about this for a second.

is your logic valid if more than one record is inserted at a time?

have you thought about the associated overhead for each transaction here?

have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

I am nearly certain your problem is permissions related. which is another can of worms.|||before we get to your problem, let's talk about this for a second.

is your logic valid if more than one record is inserted at a time?

have you thought about the associated overhead for each transaction here?

have you thought about your sql server getting hung up try to connect to exchange if the mail server is suddenly unavailable?

I am nearly certain your problem is permissions related. which is another can of worms.

Thanks very much for your reminders
I really didn't think about these.

Only one record is inserted at a time.
There is no much transaction, just something like error notification.

mmmmm, I didn't know unavailable exchange server will cause such issue.
So how can I avoid it?

How to fix the permission issue?

Thanks for your help!!

JD|||Let me echo the warning that was already posted. When you use SQL Mail in SQL 2000, you are opening a potential can of worms. Outlook is a single threaded application. If it hangs for any reason (say the Exchange server takes a vacation), you can end up with a heap of trouble. I tried running a subscription based service off of SQL 7.0/2000 back in '00/'01. We had to abandon that effort because SQL kept hanging whenever the mail server went off line (or network connectivity prevented a connection).

Go with something that is lightweight (ie, SMTP). Consider some other method for sending notifications; insert a record into a table, create an external app that runs on a schedule to watch that table, etc. Anything but this.

That said,

Error Message:
Server: Msg 2812, Level 16, State 62, Line 6
Could not find stored procedure 'master.xp_startmail'.
The statement has been terminated.

Have you checked for the existence of this sp in master? the name looks wrong to me. It should by rights be 'master.dbo.xp_startmail'.

Have you configured a SQL Mail profile? Is the profile correct? Can you send mail outside of the trigger?

Regards,

hmscott|||hi hmscott,

Thanks for helping me away such dangerous condition.

It is really bad hear that. I thought I am almost there.
I will take your advice not using SQL Mail. There is no such warning heard before when searching around the web. Now I have to go from the beginning.

Can you provide any reference for SMTP usage?

But I am still want to know how to solve the issue I have right now.
I can run the script out of trigger.

I did exec master.dbo.xp_startmail.

master.xp_startmail was in the error message when firing the trigger.

Best Regards,
JD|||My first suggestion for looking into SMTP would be to investigate SQL 2005. SQL 2005 introduces Database Mail which is an SMTP based solution and works very well (you can even define multiple SMTP servers in case the primary is out to lunch somewhere). Besides, anything new you are designing now should be done in SQL 2005 since mainstream support for SQL 2000 won't be around too much longer...

As far as your error message...are you certain that you have configured SQL Mail correctly? Be sure you differentiate between SQL Mail and SQL Agent Mail. They work the same way (using Outlook and an Outlook Profile), but they must be configured separately.

Also, did you check for the existence of the master.dbo.sp_startmail proc?

Regards,

hmscott

EXEC SQL CONNECT TO

We tried to use the embedded “EXEC SQL CONNECT TO servername.dbname USER u
name.password” but we kept getting the error message: “Server: Msg 156,
Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.”
We tried the upper / lower case of the “connect to”, with / without the
" around the server name, database name, user name, but none of them work. W
hat did we do wrong?
Thanks in advance for your help.This does not in any way seem to be valid SQL Server SQL language construct.
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.pas
sword" but we
kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'TO'."
> We tried the upper / lower case of the "connect to", with / without the " around t
he server name,
database name, user name, but none of them work. What did we do wrong?
> Thanks in advance for your help.
>|||This is how it documented in the BOL or the URL below:
connect to:
_01_1g1b.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_01_1g1b.asp
set connection:
_01_5pda.asp" target="_blank">http://msdn.microsoft.com/library/d...>
_01_5pda.asp
"Tibor Karaszi" wrote:

> This does not in any way seem to be valid SQL Server SQL language construc
t...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
> news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> kept getting the error message: "Server: Msg 156, Level 15, State 1, Line
1
> database name, user name, but none of them work. What did we do wrong?
>
>|||I see, this is embedded SQL, supposed to be run through a precompiler. I sup
pose that you have below
inside some C-program and you get the error message when you run the c-progr
am through the
precompiler? Is that the case?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:AB147E8A-3A19-4BDD-9B6C-D47F02C1C020@.microsoft.com...[vbcol=seagreen]
> This is how it documented in the BOL or the URL below:
> connect to:
> rf_01_1g1b.asp" target="_blank">http://msdn.microsoft.com/library/d...
rf_01_1g1b.asp
> set connection:
> rf_01_5pda.asp" target="_blank">http://msdn.microsoft.com/library/d...
rf_01_5pda.asp
>
>
> "Tibor Karaszi" wrote:
>
we[vbcol=seagreen]
name,[vbcol=seagreen]sql

EXEC SQL CONNECT TO

We tried to use the embedded â'EXEC SQL CONNECT TO servername.dbname USER uname.passwordâ' but we kept getting the error message: â'Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.â'
We tried the upper / lower case of the â'connect toâ', with / without the " around the server name, database name, user name, but none of them work. What did we do wrong?
Thanks in advance for your help.This does not in any way seem to be valid SQL Server SQL language construct...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.password" but we
kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'TO'."
> We tried the upper / lower case of the "connect to", with / without the " around the server name,
database name, user name, but none of them work. What did we do wrong?
> Thanks in advance for your help.
>|||I see, this is embedded SQL, supposed to be run through a precompiler. I suppose that you have below
inside some C-program and you get the error message when you run the c-program through the
precompiler? Is that the case?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:AB147E8A-3A19-4BDD-9B6C-D47F02C1C020@.microsoft.com...
> This is how it documented in the BOL or the URL below:
> connect to:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/esqlforc/ec_6_erf_01_1g1b.asp
> set connection:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/esqlforc/ec_6_erf_01_5pda.asp
>
>
> "Tibor Karaszi" wrote:
> > This does not in any way seem to be valid SQL Server SQL language construct...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
> > news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> > > We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.password" but
we
> > kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> > > Incorrect syntax near the keyword 'TO'."
> > >
> > > We tried the upper / lower case of the "connect to", with / without the " around the server
name,
> > database name, user name, but none of them work. What did we do wrong?
> > >
> > > Thanks in advance for your help.
> > >
> >
> >
> >

EXEC SQL CONNECT TO

We tried to use the embedded “EXEC SQL CONNECT TO servername.dbname USER uname.password” but we kept getting the error message: “Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.”
We tried the upper / lower case of the “connect to”, with / without the " around the server name, database name, user name, but none of them work. What did we do wrong?
Thanks in advance for your help.
This does not in any way seem to be valid SQL Server SQL language construct...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> We tried to use the embedded "EXEC SQL CONNECT TO servername.dbname USER uname.password" but we
kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'TO'."
> We tried the upper / lower case of the "connect to", with / without the " around the server name,
database name, user name, but none of them work. What did we do wrong?
> Thanks in advance for your help.
>
|||This is how it documented in the BOL or the URL below:
connect to:
http://msdn.microsoft.com/library/de...rf_01_1g1b.asp
set connection:
http://msdn.microsoft.com/library/de...rf_01_5pda.asp
"Tibor Karaszi" wrote:

> This does not in any way seem to be valid SQL Server SQL language construct...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
> news:532A0CB3-51DC-46FE-AD74-E3FA921B3471@.microsoft.com...
> kept getting the error message: "Server: Msg 156, Level 15, State 1, Line 1
> database name, user name, but none of them work. What did we do wrong?
>
>
|||I see, this is embedded SQL, supposed to be run through a precompiler. I suppose that you have below
inside some C-program and you get the error message when you run the c-program through the
precompiler? Is that the case?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RC5640808" <RC5640808@.discussions.microsoft.com> wrote in message
news:AB147E8A-3A19-4BDD-9B6C-D47F02C1C020@.microsoft.com...[vbcol=seagreen]
> This is how it documented in the BOL or the URL below:
> connect to:
> http://msdn.microsoft.com/library/de...rf_01_1g1b.asp
> set connection:
> http://msdn.microsoft.com/library/de...rf_01_5pda.asp
>
>
> "Tibor Karaszi" wrote:
we[vbcol=seagreen]
name,[vbcol=seagreen]

EXEC sp_readerrorlog 1;

Hi,

EXEC sp_readerrorlog 1; usually gives me errors from days ago without the errors from today. When I look at the error logs in the Enterprise Manager, the first errors I see are indeed the ones appearing in my list which I retrieved from the sp_readerrorlog 1; but when I scroll down to the bottom, I can see the logs from today.

So, why is it that the logs from today do not appear in my list pulled from sp_readerrorlog 1; ?

I have this problem on multiple SQL Servers 2000 Enterprise Edition

I hope I'm not the only one with this problem.

Many thanks!

Regards,

Worf

First, be aware that sp_readerrorlog resides in the 'undocumented realm', so it's on use at your own risk policy.

sp_readerrorlog 1 says to read logfile #1, which is the file previous to the currently logfile.
(the default file# is 0)

File 1 may contain items from today (if recently cycled), yesterday or weeks or months ago, depending on how long the current log (file 0) has been active and not cycled.

Are you sure that you're really asking for what you think you're asking for, since you say you want 'the newest archived log'? I'd guess that it's most likely that sp_readerrorlog 1 won't show any logs from the current day in most cases.

/Kenneth

sql

exec sp_dropmergearticle @publication = N'RWBreathe_Publication', @article = N'Call'

hi :
i got error when i run above command
Cannot drop article 'Call' from publication 'RWBreathe_Publication'
because its snapshot has been run and this publication could have active
subscriptions.
i used merge replication , and PDA as subscriber.
Cheers
Nick
Nick,
this is one difference to transactional replication. In merge you can't drop
subscriptions then drop an article. In fact, as soon as the snapshot agent
has run, your options are limited. If you script it out then recreate it
without running the snapshot agent, you'll ba able to drop the article from
Enterprise Manager. Alternatively of course you could remove references to
the article in the script before running it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

EXEC sp_attach_db have a error

I entered the following in the Query Analyzer and got the result shown:

EXEC sp_attach_db @.dbname = N'DS2004',
@.filename1 = N'd:\ds2004_data.mdf',
@.filename2 = N'd:\ds2004_log.Ldf'

Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'd:\ds2004_data.MDF' is not a valid database file header. The PageAudit property is incorrect

I can find nothing about PageAudit property.

What is it and how do I need to modify the command?

Many thanksI believe the message is trying to tell you that the PageAudit property of the database file header property in ds2004_data.mdf is bad. I think your command is okay, but you have a corrupted file.|||Maybe you're trying to detach a SQL 2k db and restore to SQL 7.0? It doesn't work. Attach or restore a sql 2000 database to version 7.0 is not supported.

EXEC Permission

Hi,
I got this 'EXECUTE permission denied on object <mySproc>' error message
everytime I try executing my SQL server Sproc.
What's this and how to fix this err?
many thnaks in advance,
mikeMike
You are not probably an owner of the stored procedure.
So, DBA should grant you 'EXECUTE' permissions on the object
Also ,If you use dynamic sql within the stored procedure it will be forced
you to grant permissions on underlying tables
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ONJfv8pBFHA.1004@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I got this 'EXECUTE permission denied on object <mySproc>' error message
> everytime I try executing my SQL server Sproc.
> What's this and how to fix this err?
> many thnaks in advance,
> mike
>
>|||Thanks Uri,
Actually, I got this prob after I moved my ASP.NET web appl to a new
hosting.
And I never such prob with my old hosting.
How can I grant any permissions in such hosted environment?
rgds,
mike
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O8wyEHrBFHA.4004@.tk2msftngp13.phx.gbl...
> Mike
> You are not probably an owner of the stored procedure.
> So, DBA should grant you 'EXECUTE' permissions on the object
> Also ,If you use dynamic sql within the stored procedure it will be forced
> you to grant permissions on underlying tables
>
> "Mike L." <globalwebservices2005@.yahoo.com> wrote in message
> news:ONJfv8pBFHA.1004@.TK2MSFTNGP15.phx.gbl...
>|||One more thing,
Everytime I log in to my SQL server using my login name/pwd, i've never get
my default DB as the first db in QA db list. It's always 'master' db.
I found out also, that my default db for my login name is not assigend to my
default db, it's to 'master' db instead.
Could this be a 'sign' that I really don't have enough permissions on my own
db?
rgds,
mike|||Hello Mike L.,
It's starting to sound like you should contact your hosting provider about
the way that your database is set up. From the sound of it it looks like
your login doesn't have permission to your database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've
> never get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend
> to my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on
> my own
> db?
> rgds,
> mike|||You can change your default database using sp_defaultdb:
EXEC sp_defaultdb 'MyLogin', 'MyDatabase'

> Could this be a 'sign' that I really don't have enough permissions on my
> own
> db?
The default database is used when you haven't specified an initial database
context in your connection string. Since you got far enough to get a
permission error, this indicates that your login has access to your database
but insufficient permissions to execute the proc.
Your hosting provider needs to either:
1) Provide a separate administrative login that you can use to manage your
database and permissions
2) Add your login to a privileged role like db_owner
3) Perform database administration on your behalf. Was this how the
database was created?
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ORub5jsBFHA.1396@.tk2msftngp13.phx.gbl...
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've never
> get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend to
> my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on my
> own
> db?
> rgds,
> mike
>|||Thanks Aaron, Dan,
I'll try to contact my hosting provider to fix this up.
rgds,
mike
"Mike L." <globalwebservices2005@.yahoo.com> wrote in message
news:ORub5jsBFHA.1396@.tk2msftngp13.phx.gbl...
> One more thing,
> Everytime I log in to my SQL server using my login name/pwd, i've never
get
> my default DB as the first db in QA db list. It's always 'master' db.
> I found out also, that my default db for my login name is not assigend to
my
> default db, it's to 'master' db instead.
> Could this be a 'sign' that I really don't have enough permissions on my
own
> db?
> rgds,
> mike
>sql

EXEC and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts
If @.@.ERROR > 0
 begin
 RAISERROR('Error in sp',16,1)
 ROLLBACK TRANSACTION exec1
 RETURN 99
END
 COMMIT TRANSACTION exec1

sql

Exclusive accessn cannot be obtained while restoring DB

Hi,
I keep getting this error while trying to restore the
database:
"Exclusive Access could not be obtained because the
database is in use. RESTORE database is terminating
abnormally."
I am sure though that noone has logged onto the system
while I doing the restore but I keep getting this error.
Any clue?
Thanks.use sp_who to see if there is any connection made to the database. Also you
can trying running command through query analyzer.
alter database <db_name> set single_user with rollback immediate.
above command will kill all the session with rolling back on going
transactions for the particular database. then run the restore command with
the same session (since your database is in single user mode).
-Vishal
"John" <kumarj@.logica.com> wrote in message
news:08ed01c33f0c$4c0c7c70$a301280a@.phx.gbl...
> Hi,
> I keep getting this error while trying to restore the
> database:
> "Exclusive Access could not be obtained because the
> database is in use. RESTORE database is terminating
> abnormally."
> I am sure though that noone has logged onto the system
> while I doing the restore but I keep getting this error.
> Any clue?
> Thanks.
>|||John,
Are you trying to restore via enterprise manager?Execute sp_who in Query
analyzer and make sure the concerned databasename is not mentioned in the
output.
If you're running SQL Server 2000, you can issue this command to remove all
connections to the database :
ALTER DATABASE <databasename> SET single_user WITH ROLLBACK IMMEDIATE
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John" <kumarj@.logica.com> wrote in message
news:08ed01c33f0c$4c0c7c70$a301280a@.phx.gbl...
> Hi,
> I keep getting this error while trying to restore the
> database:
> "Exclusive Access could not be obtained because the
> database is in use. RESTORE database is terminating
> abnormally."
> I am sure though that noone has logged onto the system
> while I doing the restore but I keep getting this error.
> Any clue?
> Thanks.
>

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

sql

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exchange 2003 and "xp_sendmail: failed with mail error 0x80070005" error

Hello,
We just migrated a mailbox that sql server agent was using as a MAPI
profile to Exchange 2003 from Exchnage 2000.
I can successfully start sql agent with this profile. SQLmail is configured
successfully with this profile.
But I am having the above error when I try to use xp_sendmail from query
analyzer. Any ideas?
Thanks,
Biva
I had a similar problem a couple of weeks ago and did a lot of research
with no results. This was causing issues with us for at least a week.
For the heck of it I went into Query analyzer and ran xp_stopmail, then
xp_startmail. Don't know why, but this did it for us. We have been using
xp_Sendmail now for over a week without any errors.
Jim
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40fc1289$0$978$39cecf19@.news.twtelecom.net...
> Hello,
> We just migrated a mailbox that sql server agent was using as a MAPI
> profile to Exchange 2003 from Exchnage 2000.
> I can successfully start sql agent with this profile. SQLmail is
configured
> successfully with this profile.
> But I am having the above error when I try to use xp_sendmail from query
> analyzer. Any ideas?
> Thanks,
> Biva
>
|||Hi Jim,
You are awesome. That was it. I did xp_stopmail and xp_startmail and
now its working fine. Thanks a bunch for your help. Saved me a lot of
time.
biva
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Exchange 2003 and "xp_sendmail: failed with mail error 0x80070005" error

Hello,
We just migrated a mailbox that sql server agent was using as a MAPI
profile to Exchange 2003 from Exchnage 2000.
I can successfully start sql agent with this profile. SQLmail is configured
successfully with this profile.
But I am having the above error when I try to use xp_sendmail from query
analyzer. Any ideas?
Thanks,
BivaI had a similar problem a couple of weeks ago and did a lot of research
with no results. This was causing issues with us for at least a week.
For the heck of it I went into Query analyzer and ran xp_stopmail, then
xp_startmail. Don't know why, but this did it for us. We have been using
xp_Sendmail now for over a week without any errors.
Jim
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40fc1289$0$978$39cecf19@.news.twtelecom.net...
> Hello,
> We just migrated a mailbox that sql server agent was using as a MAPI
> profile to Exchange 2003 from Exchnage 2000.
> I can successfully start sql agent with this profile. SQLmail is
configured
> successfully with this profile.
> But I am having the above error when I try to use xp_sendmail from query
> analyzer. Any ideas?
> Thanks,
> Biva
>