Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K

Hello,

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).

Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Thanks!
RickOn 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

I'm trying to create a simple back up in the SQL Maintenance Plan that will
make a single back up copy of all database every night at 10 pm. I'd like
the previous nights file to be overwritten, so there will be only a single
back up file for each database (tape back up runs every night, so each days
back up will be saved on tape).
>
Every night the maintenance plan makes a back up of all the databases to a
new file with a datetime stamp, meaning the previous nights file still
exists. Even when I check "Remove files older than 22 hours" the previous
nights file still exists. Is there any way to create a back up file without
the date time stamp so it overwrites the previous nights file?

Yes, I believe option is "WITH INIT". Please see BOL for details.

Regards

robert|||Hi Robert,

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the Enterprise
Manager.

Rick

"Robert Klemme" <shortcutter@.googlemail.comwrote in message
news:596ghdF2jtij6U1@.mid.individual.net...

Quote:

Originally Posted by

On 24.04.2007 15:29, Rico wrote:

Quote:

Originally Posted by

>I'm trying to create a simple back up in the SQL Maintenance Plan that
>will
>make a single back up copy of all database every night at 10 pm. I'd
>like
>the previous nights file to be overwritten, so there will be only a
>single
>back up file for each database (tape back up runs every night, so each
>days
>back up will be saved on tape).
>>
>Every night the maintenance plan makes a back up of all the databases to
>a
>new file with a datetime stamp, meaning the previous nights file still
>exists. Even when I check "Remove files older than 22 hours" the
>previous
>nights file still exists. Is there any way to create a back up file
>without
>the date time stamp so it overwrites the previous nights file?

>
Yes, I believe option is "WITH INIT". Please see BOL for details.
>
Regards
>
robert

|||Rico (me@.you.com) writes:

Quote:

Originally Posted by

Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
using T-SQL, just trying to create the Maintenance plan from the
Enterprise Manager.

From what you described, you should not use a maintenance plan. Just set up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.

Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,

I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

I will give that a try (creating a back up job and scheduling)

THanks!
Rick

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

Rico (me@.you.com) writes:

Quote:

Originally Posted by

>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>using T-SQL, just trying to create the Maintenance plan from the
>Enterprise Manager.

>
From what you described, you should not use a maintenance plan. Just set
up
a scheduled job to run the BACKUP statement. You can do this from the
GUI where you backup databases, and select Schedule somewhere on a button.
In the end you get a one-step job that has a BACKUP job.
>
Then again, if you have any interest in acquiring basic DBA skills, you
should certainly learn to write basic BACKUP commands in T-SQL.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||
"Rico" <me@.you.comwrote in message news:8LvXh.9$_G.8@.edtnps89...

Quote:

Originally Posted by

Thanks Erland,
>
I don't have a problem creating the T-SQL commands, it's using the
Enterprise Manager to do more than create dbs is where I get lost.

That's the problem with using EM.

BTW... the reason you're seeing the behavior you're seeing is that the
maintenance job takes the conservative approach and assumes that the most
recent backup HAS to succeed before it'll delete the older one.

So setting it to 22 hours or anything won't force it to delete the older
file until the new one is successfully created.

Quote:

Originally Posted by

>
I will give that a try (creating a back up job and scheduling)
>
THanks!
Rick
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns991D5A7E496DYazorman@.127.0.0.1...

Quote:

Originally Posted by

>Rico (me@.you.com) writes:

Quote:

Originally Posted by

>>Thanks, but I should have mentioned that I'm using SQL 2000 and I'm not
>>using T-SQL, just trying to create the Maintenance plan from the
>>Enterprise Manager.

>>
>From what you described, you should not use a maintenance plan. Just set
>up
>a scheduled job to run the BACKUP statement. You can do this from the
>GUI where you backup databases, and select Schedule somewhere on a
>button.
>In the end you get a one-step job that has a BACKUP job.
>>
>Then again, if you have any interest in acquiring basic DBA skills, you
>should certainly learn to write basic BACKUP commands in T-SQL.
>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Having a problem attaching a database

My SQL Server crashed back in August. I was able to recover some of the data
but it was not worth using. I was better off just starting over from
scratch. However, I did save the recovered information in case I needed to
pull any of it for reference. Well, that is what I need to do and I am
having trouble attaching the database to pull the required information. I am
very green when it comes to this stuff. I am running SQL Server 2000 v5, SP4
on Windows 2000. Below are the steps that I have taken to try to retrieve
the information:
1. Copy the recovered database to a different drive and rename it to
eliminate any possibility of over-writing my current database information
2. Open SQL Server Enterprise Manager
3. Attach Database
4. Select the database that I want to attach (renamed file in new location)
5. I have to rename the "Current file(s) location" to the correct file name
for the mdf and ldf files. The ldf file is still not recognized because the
file does not exist and will be created when attached.
6. I change the "Attach as:" to match the file name and assign the
appropriate owner.
7. Click OK and a message appears asking if I want to create an ldf file.
8. I click Yes to have the file created and an error message pops up:
Error 5105: Device activation error. The physical file
'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect.
Plant1_Shreve is on drive E. The F drive is the location of the current
database files.
9. I click OK and another window pops up stating "Attach database has
failed". I click OK to this and I am back where I started
Please try sp_attach_db in "Query Analyzer" and see what happens.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"M Figy" <M Figy@.discussions.microsoft.com> wrote in message
news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
> My SQL Server crashed back in August. I was able to recover some of the
> data
> but it was not worth using. I was better off just starting over from
> scratch. However, I did save the recovered information in case I needed
> to
> pull any of it for reference. Well, that is what I need to do and I am
> having trouble attaching the database to pull the required information. I
> am
> very green when it comes to this stuff. I am running SQL Server 2000 v5,
> SP4
> on Windows 2000. Below are the steps that I have taken to try to retrieve
> the information:
> 1. Copy the recovered database to a different drive and rename it to
> eliminate any possibility of over-writing my current database information
> 2. Open SQL Server Enterprise Manager
> 3. Attach Database
> 4. Select the database that I want to attach (renamed file in new
> location)
> 5. I have to rename the "Current file(s) location" to the correct file
> name
> for the mdf and ldf files. The ldf file is still not recognized because
> the
> file does not exist and will be created when attached.
> 6. I change the "Attach as:" to match the file name and assign the
> appropriate owner.
> 7. Click OK and a message appears asking if I want to create an ldf file.
> 8. I click Yes to have the file created and an error message pops up:
> Error 5105: Device activation error. The physical file
> 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
> database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
> error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be
> incorrect.
> Plant1_Shreve is on drive E. The F drive is the location of the current
> database files.
> 9. I click OK and another window pops up stating "Attach database has
> failed". I click OK to this and I am back where I started
|||Once I figured out how to use the sp_attach_db in Query Analyzer, I got the
same results as before. The syntax that I used follows:
EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
@.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
Did I do this right?
"wei xiao [MSFT]" wrote:

> Please try sp_attach_db in "Query Analyzer" and see what happens.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "M Figy" <M Figy@.discussions.microsoft.com> wrote in message
> news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
>
>
|||Hi There,
You need to try using sp_attach_single_file_db (see BOL)
This allows the attaching of just the MDF file and creates a new Log file
for your DB.
HTH,
Cheers,
Des
On 24/11/2004 14:05, in article
D64E767D-2DDB-416B-8C77-9895131C7DC6@.microsoft.com, "M Figy" <M
Figy@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> Once I figured out how to use the sp_attach_db in Query Analyzer, I got the
> same results as before. The syntax that I used follows:
> EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
> @.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
> Did I do this right?
> "wei xiao [MSFT]" wrote:
|||I really appreciate everyone's help but I am still getting the same error
message that refers back to the file PRODUCTION_Log.LDF. Let me try to make
my request as simple as possible. I apologize for my ignorance. I am try to
recover data from a database that was corrupted due to the server crashing.
Some of the corrupt data was recovered and the database files were stored in
another location. I want to pull this recovered data into a database with a
different name so that I do not risk overwriting any current information. I
need some detailed instruction on how to do this, if it is possible. Please
do not hesitate to ask me any questions to gather as much info that you may
need. If it may be easier, we can communicate via e-mail:
mfigy@.wauseonmachine.com
Thank you very much for your anticipated help. Mike
"Des FitzGerald" wrote:

> Hi There,
> You need to try using sp_attach_single_file_db (see BOL)
> This allows the attaching of just the MDF file and creates a new Log file
> for your DB.
> HTH,
> Cheers,
> Des
>
> On 24/11/2004 14:05, in article
> D64E767D-2DDB-416B-8C77-9895131C7DC6@.microsoft.com, "M Figy" <M
> Figy@.discussions.microsoft.com> wrote:
>
>
|||"M Figy" <MFigy@.discussions.microsoft.com> wrote in message
news:DBBF9AEA-54C0-4BB4-8FDE-1DAE25623175@.microsoft.com...
> I really appreciate everyone's help but I am still getting the same error
> message that refers back to the file PRODUCTION_Log.LDF. Let me try to
make
> my request as simple as possible. I apologize for my ignorance. I am try
to
> recover data from a database that was corrupted due to the server
crashing.
> Some of the corrupt data was recovered and the database files were stored
in
> another location. I want to pull this recovered data into a database with
a
> different name so that I do not risk overwriting any current information.
I
> need some detailed instruction on how to do this, if it is possible.
Please
> do not hesitate to ask me any questions to gather as much info that you
may
> need. If it may be easier, we can communicate via e-mail:
> mfigy@.wauseonmachine.com
The reality is.. you may not be able to attach the corrupted databases.
Your best bet is to call Microsoft Server Support with Credit Card in hand.
They have a few tricks that MAY get your DB back.

Having a problem attaching a database

My SQL Server crashed back in August. I was able to recover some of the data
but it was not worth using. I was better off just starting over from
scratch. However, I did save the recovered information in case I needed to
pull any of it for reference. Well, that is what I need to do and I am
having trouble attaching the database to pull the required information. I am
very green when it comes to this stuff. I am running SQL Server 2000 v5, SP4
on Windows 2000. Below are the steps that I have taken to try to retrieve
the information:
1. Copy the recovered database to a different drive and rename it to
eliminate any possibility of over-writing my current database information
2. Open SQL Server Enterprise Manager
3. Attach Database
4. Select the database that I want to attach (renamed file in new location)
5. I have to rename the "Current file(s) location" to the correct file name
for the mdf and ldf files. The ldf file is still not recognized because the
file does not exist and will be created when attached.
6. I change the "Attach as:" to match the file name and assign the
appropriate owner.
7. Click OK and a message appears asking if I want to create an ldf file.
8. I click Yes to have the file created and an error message pops up:
Error 5105: Device activation error. The physical file
'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect.
Plant1_Shreve is on drive E. The F drive is the location of the current
database files.
9. I click OK and another window pops up stating "Attach database has
failed". I click OK to this and I am back where I startedPlease try sp_attach_db in "Query Analyzer" and see what happens.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"M Figy" <M Figy@.discussions.microsoft.com> wrote in message
news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
> My SQL Server crashed back in August. I was able to recover some of the
> data
> but it was not worth using. I was better off just starting over from
> scratch. However, I did save the recovered information in case I needed
> to
> pull any of it for reference. Well, that is what I need to do and I am
> having trouble attaching the database to pull the required information. I
> am
> very green when it comes to this stuff. I am running SQL Server 2000 v5,
> SP4
> on Windows 2000. Below are the steps that I have taken to try to retrieve
> the information:
> 1. Copy the recovered database to a different drive and rename it to
> eliminate any possibility of over-writing my current database information
> 2. Open SQL Server Enterprise Manager
> 3. Attach Database
> 4. Select the database that I want to attach (renamed file in new
> location)
> 5. I have to rename the "Current file(s) location" to the correct file
> name
> for the mdf and ldf files. The ldf file is still not recognized because
> the
> file does not exist and will be created when attached.
> 6. I change the "Attach as:" to match the file name and assign the
> appropriate owner.
> 7. Click OK and a message appears asking if I want to create an ldf file.
> 8. I click Yes to have the file created and an error message pops up:
> Error 5105: Device activation error. The physical file
> 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
> database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
> error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be
> incorrect.
> Plant1_Shreve is on drive E. The F drive is the location of the current
> database files.
> 9. I click OK and another window pops up stating "Attach database has
> failed". I click OK to this and I am back where I started|||Once I figured out how to use the sp_attach_db in Query Analyzer, I got the
same results as before. The syntax that I used follows:
EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
@.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
Did I do this right?
"wei xiao [MSFT]" wrote:
> Please try sp_attach_db in "Query Analyzer" and see what happens.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "M Figy" <M Figy@.discussions.microsoft.com> wrote in message
> news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
> > My SQL Server crashed back in August. I was able to recover some of the
> > data
> > but it was not worth using. I was better off just starting over from
> > scratch. However, I did save the recovered information in case I needed
> > to
> > pull any of it for reference. Well, that is what I need to do and I am
> > having trouble attaching the database to pull the required information. I
> > am
> > very green when it comes to this stuff. I am running SQL Server 2000 v5,
> > SP4
> > on Windows 2000. Below are the steps that I have taken to try to retrieve
> > the information:
> >
> > 1. Copy the recovered database to a different drive and rename it to
> > eliminate any possibility of over-writing my current database information
> > 2. Open SQL Server Enterprise Manager
> > 3. Attach Database
> > 4. Select the database that I want to attach (renamed file in new
> > location)
> > 5. I have to rename the "Current file(s) location" to the correct file
> > name
> > for the mdf and ldf files. The ldf file is still not recognized because
> > the
> > file does not exist and will be created when attached.
> > 6. I change the "Attach as:" to match the file name and assign the
> > appropriate owner.
> > 7. Click OK and a message appears asking if I want to create an ldf file.
> > 8. I click Yes to have the file created and an error message pops up:
> >
> > Error 5105: Device activation error. The physical file
> > 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
> > database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
> > error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be
> > incorrect.
> >
> > Plant1_Shreve is on drive E. The F drive is the location of the current
> > database files.
> >
> > 9. I click OK and another window pops up stating "Attach database has
> > failed". I click OK to this and I am back where I started
>
>|||> This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--B_3184150360_1248646
Content-type: text/plain;
charset="US-ASCII"
Content-transfer-encoding: 7bit
Hi There,
You need to try using sp_attach_single_file_db (see BOL)
This allows the attaching of just the MDF file and creates a new Log file
for your DB.
HTH,
Cheers,
Des
On 24/11/2004 14:05, in article
D64E767D-2DDB-416B-8C77-9895131C7DC6@.microsoft.com, "M Figy" <M
Figy@.discussions.microsoft.com> wrote:
> Once I figured out how to use the sp_attach_db in Query Analyzer, I got the
> same results as before. The syntax that I used follows:
> EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
> @.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
> Did I do this right?
> "wei xiao [MSFT]" wrote:
>> Please try sp_attach_db in "Query Analyzer" and see what happens.
>> --
>> Wei Xiao [MSFT]
>> SQL Server Storage Engine Development
>> http://weblogs.asp.net/weix
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "M Figy" <M Figy@.discussions.microsoft.com> wrote in message
>> news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
>> My SQL Server crashed back in August. I was able to recover some of the
>> data
>> but it was not worth using. I was better off just starting over from
>> scratch. However, I did save the recovered information in case I needed
>> to
>> pull any of it for reference. Well, that is what I need to do and I am
>> having trouble attaching the database to pull the required information. I
>> am
>> very green when it comes to this stuff. I am running SQL Server 2000 v5,
>> SP4
>> on Windows 2000. Below are the steps that I have taken to try to retrieve
>> the information:
>> 1. Copy the recovered database to a different drive and rename it to
>> eliminate any possibility of over-writing my current database information
>> 2. Open SQL Server Enterprise Manager
>> 3. Attach Database
>> 4. Select the database that I want to attach (renamed file in new
>> location)
>> 5. I have to rename the "Current file(s) location" to the correct file
>> name
>> for the mdf and ldf files. The ldf file is still not recognized because
>> the
>> file does not exist and will be created when attached.
>> 6. I change the "Attach as:" to match the file name and assign the
>> appropriate owner.
>> 7. Click OK and a message appears asking if I want to create an ldf file.
>> 8. I click Yes to have the file created and an error message pops up:
>> Error 5105: Device activation error. The physical file
>> 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
>> database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
>> error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be
>> incorrect.
>> Plant1_Shreve is on drive E. The F drive is the location of the current
>> database files.
>> 9. I click OK and another window pops up stating "Attach database has
>> failed". I click OK to this and I am back where I started
>>
--B_3184150360_1248646
Content-type: text/html;
charset="US-ASCII"
Content-transfer-encoding: quoted-printable
&

---

Re: Having a problem attaching a database
Hi Th=ere,
You need to try using sp_attach_single_file_db (see BOL)
This allows the attaching of just the MDF file and creates a new Log file f=or your DB.
HTH,
Cheers,
Des
On 24/11/2004 14:05, in article D64E767D-2DDB-416B-8C77-9895131C7DC6@.micros=oft.com, "M Figy" wrote:
> Once I figured out how to use the sp_attach_db i=n Query Analyzer, I got the
> same results as before. The syntax that I used follows:
>
> EXEC sp_attach_db @.dbname =3D N'--Plant1_Shreve',
> @.filename1 =3D N'E:\Program Files\jobboss\--Plant1_Sh=reve.mdf'
>
> Did I do this right?
>
> "wei xiao [MSFT]" wrote:
>
> Please try sp_attach_db in "Quer=y Analyzer" and see what happens.
>>
>> --
>> Wei Xiao [MSFT]
>> SQL Server Storage Engine Development
>>http://weblogs.asp.net/weix<=/a>
>>
>> This posting is provided "AS IS" with no warranties, and= confers no rights.
>>
>> "M Figy" wrote =in message
>> news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
> My SQL Server crashed back in Aug=ust. I was able to recover some of the
>> data
>> but it was not worth using. I was better off just starti=ng over from
>> scratch. However, I did save the recovered information i=n case I needed
>> to
>> pull any of it for reference. Well, that is what I need =to do and I am
>> having trouble attaching the database to pull the required inf=ormation. I
>> am
>> very green when it comes to this stuff. I am running SQL= Server 2000 v5,
>> SP4
>> on Windows 2000. Below are the steps that I have taken t=o try to retrieve
>> the information:
>>
>> 1. Copy the recovered database to a different drive and rename= it to
>> eliminate any possibility of over-writing my current database =information
>> 2. Open SQL Server Enterprise Manager
>> 3. Attach Database
>> 4. Select the database that I want to attach (renamed file in =new
>> location)
>> 5. I have to rename the "Current file(s) location" t=o the correct file
>> name
>> for the mdf and ldf files. The ldf file is still not rec=ognized because
>> the
>> file does not exist and will be created when attached.
>> 6. I change the "Attach as:" to match the file name =and assign the
>> appropriate owner.
>> 7. Click OK and a message appears asking if I want to create a=n ldf file.
>> 8. I click Yes to have the file created and an error message p=ops up:
>>
>> Error 5105: Device activation error. The physical file
>> 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could =not open new
>> database 'Plant1_Shreve'. CREATE DATABASE is aborted. &n=bsp;Device activation
>> error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF'= may be
>> incorrect.
>>
>> Plant1_Shreve is on drive E. The F drive is the location= of the current
>> database files.
>>
>> 9. I click OK and another window pops up stating "Attach =database has
>> failed". I click OK to this and I am back where I s=tarted
>
>>
>>
--B_3184150360_1248646--|||I really appreciate everyone's help but I am still getting the same error
message that refers back to the file PRODUCTION_Log.LDF. Let me try to make
my request as simple as possible. I apologize for my ignorance. I am try to
recover data from a database that was corrupted due to the server crashing.
Some of the corrupt data was recovered and the database files were stored in
another location. I want to pull this recovered data into a database with a
different name so that I do not risk overwriting any current information. I
need some detailed instruction on how to do this, if it is possible. Please
do not hesitate to ask me any questions to gather as much info that you may
need. If it may be easier, we can communicate via e-mail:
mfigy@.wauseonmachine.com
Thank you very much for your anticipated help. Mike
"Des FitzGerald" wrote:
> Hi There,
> You need to try using sp_attach_single_file_db (see BOL)
> This allows the attaching of just the MDF file and creates a new Log file
> for your DB.
> HTH,
> Cheers,
> Des
>
> On 24/11/2004 14:05, in article
> D64E767D-2DDB-416B-8C77-9895131C7DC6@.microsoft.com, "M Figy" <M
> Figy@.discussions.microsoft.com> wrote:
> > Once I figured out how to use the sp_attach_db in Query Analyzer, I got the
> > same results as before. The syntax that I used follows:
> >
> > EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
> > @.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
> >
> > Did I do this right?
> >
> > "wei xiao [MSFT]" wrote:
> >
> >> Please try sp_attach_db in "Query Analyzer" and see what happens.
> >>
> >> --
> >> Wei Xiao [MSFT]
> >> SQL Server Storage Engine Development
> >> http://weblogs.asp.net/weix
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no rights.
> >>
> >> "M Figy" <M Figy@.discussions.microsoft.com> wrote in message
> >> news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
> >> My SQL Server crashed back in August. I was able to recover some of the
> >> data
> >> but it was not worth using. I was better off just starting over from
> >> scratch. However, I did save the recovered information in case I needed
> >> to
> >> pull any of it for reference. Well, that is what I need to do and I am
> >> having trouble attaching the database to pull the required information. I
> >> am
> >> very green when it comes to this stuff. I am running SQL Server 2000 v5,
> >> SP4
> >> on Windows 2000. Below are the steps that I have taken to try to retrieve
> >> the information:
> >>
> >> 1. Copy the recovered database to a different drive and rename it to
> >> eliminate any possibility of over-writing my current database information
> >> 2. Open SQL Server Enterprise Manager
> >> 3. Attach Database
> >> 4. Select the database that I want to attach (renamed file in new
> >> location)
> >> 5. I have to rename the "Current file(s) location" to the correct file
> >> name
> >> for the mdf and ldf files. The ldf file is still not recognized because
> >> the
> >> file does not exist and will be created when attached.
> >> 6. I change the "Attach as:" to match the file name and assign the
> >> appropriate owner.
> >> 7. Click OK and a message appears asking if I want to create an ldf file.
> >> 8. I click Yes to have the file created and an error message pops up:
> >>
> >> Error 5105: Device activation error. The physical file
> >> 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
> >> database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
> >> error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be
> >> incorrect.
> >>
> >> Plant1_Shreve is on drive E. The F drive is the location of the current
> >> database files.
> >>
> >> 9. I click OK and another window pops up stating "Attach database has
> >> failed". I click OK to this and I am back where I started
> >>
> >>
> >>
>|||"M Figy" <MFigy@.discussions.microsoft.com> wrote in message
news:DBBF9AEA-54C0-4BB4-8FDE-1DAE25623175@.microsoft.com...
> I really appreciate everyone's help but I am still getting the same error
> message that refers back to the file PRODUCTION_Log.LDF. Let me try to
make
> my request as simple as possible. I apologize for my ignorance. I am try
to
> recover data from a database that was corrupted due to the server
crashing.
> Some of the corrupt data was recovered and the database files were stored
in
> another location. I want to pull this recovered data into a database with
a
> different name so that I do not risk overwriting any current information.
I
> need some detailed instruction on how to do this, if it is possible.
Please
> do not hesitate to ask me any questions to gather as much info that you
may
> need. If it may be easier, we can communicate via e-mail:
> mfigy@.wauseonmachine.com
The reality is.. you may not be able to attach the corrupted databases.
Your best bet is to call Microsoft Server Support with Credit Card in hand.
They have a few tricks that MAY get your DB back.

Having a problem attaching a database

My SQL Server crashed back in August. I was able to recover some of the dat
a
but it was not worth using. I was better off just starting over from
scratch. However, I did save the recovered information in case I needed to
pull any of it for reference. Well, that is what I need to do and I am
having trouble attaching the database to pull the required information. I a
m
very green when it comes to this stuff. I am running SQL Server 2000 v5, SP
4
on Windows 2000. Below are the steps that I have taken to try to retrieve
the information:
1. Copy the recovered database to a different drive and rename it to
eliminate any possibility of over-writing my current database information
2. Open SQL Server Enterprise Manager
3. Attach Database
4. Select the database that I want to attach (renamed file in new location)
5. I have to rename the "Current file(s) location" to the correct file name
for the mdf and ldf files. The ldf file is still not recognized because the
file does not exist and will be created when attached.
6. I change the "Attach as:" to match the file name and assign the
appropriate owner.
7. Click OK and a message appears asking if I want to create an ldf file.
8. I click Yes to have the file created and an error message pops up:
Error 5105: Device activation error. The physical file
'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect.
Plant1_Shreve is on drive E. The F drive is the location of the current
database files.
9. I click OK and another window pops up stating "Attach database has
failed". I click OK to this and I am back where I startedPlease try sp_attach_db in "Query Analyzer" and see what happens.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"M Figy" <M Figy@.discussions.microsoft.com> wrote in message
news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
> My SQL Server crashed back in August. I was able to recover some of the
> data
> but it was not worth using. I was better off just starting over from
> scratch. However, I did save the recovered information in case I needed
> to
> pull any of it for reference. Well, that is what I need to do and I am
> having trouble attaching the database to pull the required information. I
> am
> very green when it comes to this stuff. I am running SQL Server 2000 v5,
> SP4
> on Windows 2000. Below are the steps that I have taken to try to retrieve
> the information:
> 1. Copy the recovered database to a different drive and rename it to
> eliminate any possibility of over-writing my current database information
> 2. Open SQL Server Enterprise Manager
> 3. Attach Database
> 4. Select the database that I want to attach (renamed file in new
> location)
> 5. I have to rename the "Current file(s) location" to the correct file
> name
> for the mdf and ldf files. The ldf file is still not recognized because
> the
> file does not exist and will be created when attached.
> 6. I change the "Attach as:" to match the file name and assign the
> appropriate owner.
> 7. Click OK and a message appears asking if I want to create an ldf file.
> 8. I click Yes to have the file created and an error message pops up:
> Error 5105: Device activation error. The physical file
> 'F:\Jobboss\PRODUCTION_log.LDF' may be incorrect. Could not open new
> database 'Plant1_Shreve'. CREATE DATABASE is aborted. Device activation
> error. The physical file 'F:\Jobboss\PRODUCTION_log.LDF' may be
> incorrect.
> Plant1_Shreve is on drive E. The F drive is the location of the current
> database files.
> 9. I click OK and another window pops up stating "Attach database has
> failed". I click OK to this and I am back where I started|||Once I figured out how to use the sp_attach_db in Query Analyzer, I got the
same results as before. The syntax that I used follows:
EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
@.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
Did I do this right?
"wei xiao [MSFT]" wrote:

> Please try sp_attach_db in "Query Analyzer" and see what happens.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "M Figy" <M Figy@.discussions.microsoft.com> wrote in message
> news:85957AE4-6F99-4CA7-ADBE-E8FB534DD35D@.microsoft.com...
>
>|||Hi There,
You need to try using sp_attach_single_file_db (see BOL)
This allows the attaching of just the MDF file and creates a new Log file
for your DB.
HTH,
Cheers,
Des
On 24/11/2004 14:05, in article
D64E767D-2DDB-416B-8C77-9895131C7DC6@.microsoft.com, "M Figy" <M
Figy@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
> Once I figured out how to use the sp_attach_db in Query Analyzer, I got th
e
> same results as before. The syntax that I used follows:
> EXEC sp_attach_db @.dbname = N'--Plant1_Shreve',
> @.filename1 = N'E:\Program Files\jobboss\--Plant1_Shreve.mdf'
> Did I do this right?
> "wei xiao [MSFT]" wrote:
>|||I really appreciate everyone's help but I am still getting the same error
message that refers back to the file PRODUCTION_Log.LDF. Let me try to make
my request as simple as possible. I apologize for my ignorance. I am try t
o
recover data from a database that was corrupted due to the server crashing.
Some of the corrupt data was recovered and the database files were stored in
another location. I want to pull this recovered data into a database with a
different name so that I do not risk overwriting any current information. I
need some detailed instruction on how to do this, if it is possible. Please
do not hesitate to ask me any questions to gather as much info that you may
need. If it may be easier, we can communicate via e-mail:
mfigy@.wauseonmachine.com
Thank you very much for your anticipated help. Mike
"Des FitzGerald" wrote:

> Hi There,
> You need to try using sp_attach_single_file_db (see BOL)
> This allows the attaching of just the MDF file and creates a new Log file
> for your DB.
> HTH,
> Cheers,
> Des
>
> On 24/11/2004 14:05, in article
> D64E767D-2DDB-416B-8C77-9895131C7DC6@.microsoft.com, "M Figy" <M
> Figy@.discussions.microsoft.com> wrote:
>
>|||"M Figy" <MFigy@.discussions.microsoft.com> wrote in message
news:DBBF9AEA-54C0-4BB4-8FDE-1DAE25623175@.microsoft.com...
> I really appreciate everyone's help but I am still getting the same error
> message that refers back to the file PRODUCTION_Log.LDF. Let me try to
make
> my request as simple as possible. I apologize for my ignorance. I am try
to
> recover data from a database that was corrupted due to the server
crashing.
> Some of the corrupt data was recovered and the database files were stored
in
> another location. I want to pull this recovered data into a database with
a
> different name so that I do not risk overwriting any current information.
I
> need some detailed instruction on how to do this, if it is possible.
Please
> do not hesitate to ask me any questions to gather as much info that you
may
> need. If it may be easier, we can communicate via e-mail:
> mfigy@.wauseonmachine.com
The reality is.. you may not be able to attach the corrupted databases.
Your best bet is to call Microsoft Server Support with Credit Card in hand.
They have a few tricks that MAY get your DB back.sql

hash warning, hash recursion errors

sql2000 sp3.
strange problem here. i've got an extremely complex query written by a
developer. the query works and it comes back in a reasonable amount of
time. however, the query generates a "hash warning/hash recursion"
error when it runs. after reading bol, these errors don't really seem
to be very bad. i decided to investigate it further by restoring the
production db to a test server and try it there. guess what. no hash
warning errors on the test server.
prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
allocated for sql server).
test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
allocated for sql server).
the query runs slower on the test server (as expected) even with little
to no traffic and no hash warnings.
my next guess was that on the prod server, all of sql server's ram was
being used by other objects. i did a dropcleanbuffers and a
freeproccache. query still generates hash warnings.
any ideas as to what would cause the hash warnings on prod server but
not test server?
i can't reboot the prod server and i can't stop sqlserver on prod
server. are there any things i can try in sqlserver to free up
resources other than dropcleanbuffers and freeproccache?Does SQL choose the same execution plan on the production and test server?
It's possible that the plans aren't the same...
also... hashing algorithtms take amount of memory into consideration. So
it's entirely possible that the prod server is making a mistake when it
guesses how much memory will ultimately be available for it.
You might also want to make sure statistics are up to date on prod. Out of
date stats might make the optimizer come up with bad hashing (and other)
decisions...
--
Brian Moran
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FBB6FB8.C5FF3195@.dontemailme.com...
> sql2000 sp3.
> strange problem here. i've got an extremely complex query written by a
> developer. the query works and it comes back in a reasonable amount of
> time. however, the query generates a "hash warning/hash recursion"
> error when it runs. after reading bol, these errors don't really seem
> to be very bad. i decided to investigate it further by restoring the
> production db to a test server and try it there. guess what. no hash
> warning errors on the test server.
> prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> allocated for sql server).
> test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> allocated for sql server).
> the query runs slower on the test server (as expected) even with little
> to no traffic and no hash warnings.
> my next guess was that on the prod server, all of sql server's ram was
> being used by other objects. i did a dropcleanbuffers and a
> freeproccache. query still generates hash warnings.
> any ideas as to what would cause the hash warnings on prod server but
> not test server?
> i can't reboot the prod server and i can't stop sqlserver on prod
> server. are there any things i can try in sqlserver to free up
> resources other than dropcleanbuffers and freeproccache?
>
>|||I have a bizarre "Hash Warning" performance issue I am trying to
resolve - any help, explanations or thoughts appreciated:
Configuration:
sql2000 sp3 & as2000 sp3
1Gb RAM, Single 1GHz CPU
Note: All queries/testing described below is performed on the same
database on the same server. Statistics are up to date for this
database.
When drilling through on a cube, Analysis Server generates a T-SQL
SELECT and executes it via a call to sp_prepexec.
For a specific drill-through I am testing (see below for actual
T-SQL), this code takes between 28 and 40 seconds to execute
(regardless of caching).
If I copy the exact query (captured via SQL Profiler) into Query
Analyzer, the same query executes in less than 5 seconds - sub-second
on subsequent executions (i.e. with cached data).
The result is the same slow execution using either Analysis Server's
cube browser or a web-based OLAP client application.
I ran SQL Profiler to capture the execution of this code from both
Analysis Server on Drill-Through and Query Analyzer to determine any
differences, and the only differences I can find are:
1) Query Analyzer event is captured as "SQL:BatchCompleted", whereas
the Analysis Server event is captured as "RPC:Completed"
2) No Warning or Error events are generated by Query Analyzer
executing the code, however the Analysis Server-based execution
generated 3 "HASH WARNING" events. The ObjectIDs captured by SQL
Profiler for these 3 Hash Warnings are 1, 12 and 12 again for the
third event, which map to the "sysobjects" and "sysdepends" tables!!
The specific code being executed for this test case is as follows:
================================================================declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 tinyint,@.P2 char(3),@.P3 char(3),@.P4
char(3),@.P5 char(3),@.P6 char(3),@.P7 char(3),@.P8 char(3),@.P9
char(3),@.P10 char(3),@.P11 char(3),@.P12 char(3),@.P13 char(3),@.P14
char(3),@.P15 char(3),@.P16 char(3),@.P17 char(3),@.P18 char(3),@.P19
char(3),@.P20 char(3),@.P21 char(3),@.P22 char(3),@.P23 char(3),@.P24
char(3),@.P25 char(3),@.P26 char(3),@.P27 char(3),@.P28 char(3),@.P29
char(3),@.P30 char(3),@.P31 char(3),@.P32 char(3),@.P33 char(3),@.P34
char(3),@.P35 char(3),@.P36 char(3),@.P37 char(3),@.P38 char(3),@.P39
char(3),@.P40 char(3),@.P41 char(3),@.P42 char(3),@.P43 char(3),@.P44
char(3),@.P45 char(3),@.P46 char(3),@.P47 char(3),@.P48 char(3),@.P49
char(3),@.P50 char(3),@.P51 char(3),@.P52 char(3),@.P53 char(3),@.P54
char(3),@.P55 char(3),@.P56 char(3),@.P57 char(3),@.P58 char(3),@.P59
char(3),@.P60 varchar(13),@.P61 int,@.P62 int,@.P63 varchar(7)', N'SELECT
"dbo"."mr_Activity"."DetectionDate",
"dbo"."mr_Activity"."ReversalFlag", "dbo"."mr_Activity"."VenueID",
"dbo"."mr_Activity"."ProductCode",
"dbo"."mr_Activity"."ActivityAmount", "dbo"."mr_Venue"."VenueName",
"dbo"."mr_ProductStructure"."ProductHierarchyName",
"dbo"."mr_AccountRef"."XrefNumber", "dbo"."mr_CaseRef"."WIN" FROM
"dbo"."mr_Activity", "dbo"."mr_ActivityReason", "dbo"."mr_Calendar",
"dbo"."mr_ProductStructure", "dbo"."mr_AccountRef",
"dbo"."mr_CaseRef", "dbo"."mr_Venue" WHERE
(mr_ProductStructure.ProductHierarchyID=mr_Activity.ProductCode AND
mr_AccountRef.DataWarehouseAccountID=mr_Activity.DataWarehouseAccountID
AND mr_CaseRef.DataWarehouseCaseID=mr_Activity.DataWarehouseCaseID)
AND (("dbo"."mr_Venue"."VenueID"="dbo"."mr_Activity"."VenueID")) AND
("dbo"."mr_Activity"."ActivityTypeID"=@.P1) AND
("dbo"."mr_Activity"."ProductCode" IN
(@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P12,@.P13,@.P14,@.P15,@.P16,@.P17,@.P18,@.P19,@.P20,@.P21,@.P22,@.P23,@.P24,@.P25,@.P26,@.P27,@.P28,@.P29,@.P30,@.P31,@.P32,@.P33,@.P34,@.P35,@.P36,@.P37,@.P38,@.P39,@.P40,@.P41,@.P42,@.P43,@.P44,@.P45,@.P46,@.P47,@.P48,@.P49,@.P50,@.P51,@.P52,@.P53,@.P54,@.P55,@.P56,@.P57,@.P58,@.P59))
AND ("dbo"."mr_ActivityReason"."BusinessType"=@.P60) AND
("dbo"."mr_Activity"."ReasonID"="dbo"."mr_ActivityReason"."ReasonID")
AND ("dbo"."mr_Activity"."ActivityTypeID"="dbo"."mr_ActivityReason"."ActivityTypeID")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 100) +
DatePart(month,"dbo"."mr_Calendar"."CalendarDate")=@.P61) AND
("dbo"."mr_Calendar"."CalendarDate"="dbo"."mr_Activity"."DetectionDate")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 10) +
"dbo"."mr_Calendar"."FiscalQtr"=@.P62) AND (''Measure''=@.P63)', 1,
'-36', '-39', '261', '222', '-40', '042', '-42', '259', '221', '-43',
'141', '-45', '030', '025', '026', '027', '028', '029', '-46', '035',
'032', '024', '-47', '282', '283', '284', '285', '286', '287', '-11',
'288', '289', '290', '291', '292', '293', '-11', '294', '295', '296',
'297', '298', '299', '-11', '-11', '-48', '-51', '220', '215', '216',
'217', '218', '219', '-53', '257', '214', '139', '-54', 'New
Business', 200307, 20031, 'Measure'
select @.P1
================================================================
The fact that Hash Warnings are appearing against sysobjects and
sysdepends in the database I find quite bizarre. I suspect these hash
warnings are related to the performance discrepancy betweeen the two
"modes" of execution (1 second vs. 30 seconds).
Any help on resolving (or even explaining) this performance
discrepancy issue is greatly appreciated.
Piquet.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message news:<epmCOPqrDHA.536@.tk2msftngp13.phx.gbl>...
> Does SQL choose the same execution plan on the production and test server?
> It's possible that the plans aren't the same...
> also... hashing algorithtms take amount of memory into consideration. So
> it's entirely possible that the prod server is making a mistake when it
> guesses how much memory will ultimately be available for it.
> You might also want to make sure statistics are up to date on prod. Out of
> date stats might make the optimizer come up with bad hashing (and other)
> decisions...
> --
> Brian Moran
>
> "chxxx" <chxxx@.dontemailme.com> wrote in message
> news:3FBB6FB8.C5FF3195@.dontemailme.com...
> > sql2000 sp3.
> >
> > strange problem here. i've got an extremely complex query written by a
> > developer. the query works and it comes back in a reasonable amount of
> > time. however, the query generates a "hash warning/hash recursion"
> > error when it runs. after reading bol, these errors don't really seem
> > to be very bad. i decided to investigate it further by restoring the
> > production db to a test server and try it there. guess what. no hash
> > warning errors on the test server.
> >
> > prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> > allocated for sql server).
> > test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> > allocated for sql server).
> > the query runs slower on the test server (as expected) even with little
> > to no traffic and no hash warnings.
> >
> > my next guess was that on the prod server, all of sql server's ram was
> > being used by other objects. i did a dropcleanbuffers and a
> > freeproccache. query still generates hash warnings.
> >
> > any ideas as to what would cause the hash warnings on prod server but
> > not test server?
> >
> > i can't reboot the prod server and i can't stop sqlserver on prod
> > server. are there any things i can try in sqlserver to free up
> > resources other than dropcleanbuffers and freeproccache?
> >
> >
> >sql

Has anyone tried "Using SQL Stored procedure in MS ACCESS"?

I am developing a program using ms access with an SQL 2000 back end. I am using an odbc connection to access my data. Using this connection, i can only access the tables and views of my database. I am planning to create "Stored procedure" in SQL and connect it to my MS Access program but have no idea how to do it.

Please help! Thanks in Advance!Might I suggest an Access newsgroup as its an Access specific problem? SQL Server SPs should just be called like a SQL statement is executed, ie "exec usp_foo" instead of "select * from foo".|||if you want to view stored procedure from Access I suggest you to not use a Access Project (file .adp) instead of the classic Access database (.mdb);
in this way in the Query tab of you Access Project you will see views and stored procedure of the Sql server and then you can manage these directly by Access.
bye

Has anyone tried "Using SQL Stored procedure in MS ACCESS"?

I am developing a program using ms access with an SQL 2000 back end. I am using an odbc connection to access my data. Using this connection, i can only access the tables and views of my database. I am planning to create "Stored procedure" in SQL and connect it to my MS Access program but have no idea how to do it.

Please help! Thanks in Advance!Might I suggest an Access newsgroup as its an Access specific problem? SQL Server SPs should just be called like a SQL statement is executed, ie "exec usp_foo" instead of "select * from foo".|||if you want to view stored procedure from Access I suggest you to not use a Access Project (file .adp) instead of the classic Access database (.mdb);
in this way in the Query tab of you Access Project you will see views and stored procedure of the Sql server and then you can manage these directly by Access.
bye

Handling Transaction

hi friends,

I like to put a set of sql statements under a transaction and wish the sql server to take care of commit / roll back the entire set depending upon the success/failure of the statements in the set. I want the whole set is either to success or to failure.

When I go through the docs, i find that SQL Server 2000 operates three transaction modes:
Autocommit transactions : Each individual statement is a transaction.

Explicit transactions : Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions: A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

As one can see, it seems that it is not possible to define "atomic compound sql statements". Even if i used explicit transaction, it is not possible to achieve this, since i couldn't find a mechanism to handle errors for a group of statements.

I wonder how to write atomic compound sql statement in the sql server.
can anybody please help me on this...

JakeThe only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.|||hi Kaiowas,

thanks for the suggestion. instead of checking for success & failure for each line, i found another way of doing this.
If we put the set of statements in a BEGIN TRANSACTION ... COMMIT TRANSACTION block, the commit transaction will get execute iff all the statements succeed. Otherwise all the statements get rolled back. This solves my problem.

But still there is no way to handle exception for a block of statements.
Does Yukon release have support for this? any idea?

Jake

Originally posted by Kaiowas
The only solution that I am aware of is enclosing the inserts/updates into one transaction and check each for success. If one fails; rollback, if all succeed; commit.