Attach database problem

Hi,

I have got database files from sql server 2000 which i'm trying to attach to sql server 2005 database. During the attach process, I'm getting the following erros:

EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';

Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Dev'. CREATE DATABASE is aborted.

Any idea what this refers to?

Regards,
D2

This usually happens with DML operations. I'm guessing you retried the operation a few times and they all failed with the same error. Can you attach this database back to SQL Server 2000?

Another thing you can try is to run SQL Server in console mode with traceflag 3630:

Start->Run->cmd->Enter

cd "%ProgramFiles%\Microsoft SQL Server\<instance_name>\mssql\binn"

sqlservr.exe -c -T3630

Then try to attach the database again and watch for this message on the console:

spid <id> ex_raise <nn>:<nn> (sev=<nn>,state=<nn>), called from 0x<nnnnnnnn>

This is the error original error which caused 601. Cut&Paste that line here and that will give us more information. <nn>:<nn> will be the error number, e.g. 6:01 -> error 601

Thanks,

Fabricio.

attach database issue

Hi,
I have got database files from sql server 2000 which i'm trying to
attach to sql server 2005 database. During the attach process, I'm
getting the following erros:
EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Dev'. CREATE DATABASE is aborted.
Any idea what this refers to?
Regards,
D2
D2,
How about trying to do same thing in SSMS attach menu?
and I found similar SQL in BOL :
CREATE DATABASE pubs ON PRIMARY
(FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf')
LOG ON (FILENAME =
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
FOR ATTACH;
GO
try it
+--
Kenial.GhostOnNetwork.
D2 wrote:
> Hi,
> I have got database files from sql server 2000 which i'm trying to
> attach to sql server 2005 database. During the attach process, I'm
> getting the following erros:
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>
> Msg 601, Level 12, State 3, Line 1
> Could not continue scan with NOLOCK due to data movement.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Dev'. CREATE DATABASE is aborted.
> Any idea what this refers to?
> Regards,
> D2
>
|||D2
This example I took from the BOL. Try it .
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
FOR ATTACH');
GO
"D2" <dhapola@.yahoo.com> wrote in message
news:1182324219.833398.13530@.q19g2000prn.googlegro ups.com...
> Hi,
> I have got database files from sql server 2000 which i'm trying to
> attach to sql server 2005 database. During the attach process, I'm
> getting the following erros:
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>
> Msg 601, Level 12, State 3, Line 1
> Could not continue scan with NOLOCK due to data movement.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Dev'. CREATE DATABASE is aborted.
> Any idea what this refers to?
> Regards,
> D2
>
|||Tried the attach from menu before writing queries :-)
thanks for your time.
On Jun 20, 2:04 pm, Kenial <ken...@.shinbiro.com.korea> wrote:[vbcol=seagreen]
> D2,
> How about trying to do same thing in SSMS attach menu?
> and I found similar SQL in BOL :
> CREATE DATABASE pubs ON PRIMARY
> (FILENAME =
> 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf')
> LOG ON (FILENAME =
> 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
> FOR ATTACH;
> GO
> try it
> +--
> Kenial.GhostOnNetwork.
> D2 wrote:
>
>
|||Already tried that; BOL query is only trying to dynamically attach the
folder path.
sp_attach_db sp is internally executing the same query.
thanks for your time.
On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> This example I took from the BOL. Try it .
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> FOR ATTACH');
> GO"D2" <dhap...@.yahoo.com> wrote in message
> news:1182324219.833398.13530@.q19g2000prn.googlegro ups.com...
>
>
>
|||D2
Do you have any open transactions? Any other actvities while the attach
process is running?
"D2" <dhapola@.yahoo.com> wrote in message
news:1182332774.416879.280860@.j4g2000prf.googlegro ups.com...
> Already tried that; BOL query is only trying to dynamically attach the
> folder path.
> sp_attach_db sp is internally executing the same query.
> thanks for your time.
> On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
|||On this particular database: no. i'm trying to attach these files to a
new database.
However the same sql server instance is hosting many other databases,
in any of those there may be open transaction.
On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> Do you have any open transactions? Any other actvities while the attach
> process is running?
> "D2" <dhap...@.yahoo.com> wrote in message
> news:1182332774.416879.280860@.j4g2000prf.googlegro ups.com...
>
>
>
>
>
|||D2
Are you saying that you created a new database called 'Dev' and then run the
following script?
EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
sp_attach_db is depricated by MS and will be removed in the future.
I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
Try moving those files to another location and try execute.
"D2" <dhapola@.yahoo.com> wrote in message
news:1182336562.364728.71990@.i13g2000prf.googlegro ups.com...
> On this particular database: no. i'm trying to attach these files to a
> new database.
> However the same sql server instance is hosting many other databases,
> in any of those there may be open transaction.
> On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
|||No. What I mean is.. while executing these sql statements, a new
database called Dev is being created having filegroups set to the mdf
and ldf files ginve in the filename parameters.
I have done attach/detach many times.. both by writng query and using
ssdm menus. Only this time I'm getting some problem.
On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> Are you saying that you created a new database called 'Dev' and then run the
> following script?
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
> sp_attach_db is depricated by MS and will be removed in the future.
> I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
> Try moving those files to another location and try execute.
> "D2" <dhap...@.yahoo.com> wrote in message
> news:1182336562.364728.71990@.i13g2000prf.googlegro ups.com...
>
>
>
>
>
>
>
>
|||D2
Okay, do you have a backup of the database. Try using RESTORE coomand
instead of attaching the files.
"D2" <dhapola@.yahoo.com> wrote in message
news:1182342259.453149.68560@.i38g2000prf.googlegro ups.com...
> No. What I mean is.. while executing these sql statements, a new
> database called Dev is being created having filegroups set to the mdf
> and ldf files ginve in the filename parameters.
> I have done attach/detach many times.. both by writng query and using
> ssdm menus. Only this time I'm getting some problem.
> On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>

attach database issue

Hi,
I have got database files from sql server 2000 which i'm trying to
attach to sql server 2005 database. During the attach process, I'm
getting the following erros:
EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Dev'. CREATE DATABASE is aborted.
Any idea what this refers to?
Regards,
D2D2,
How about trying to do same thing in SSMS attach menu?
and I found similar SQL in BOL :
CREATE DATABASE pubs ON PRIMARY
(FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf')
LOG ON (FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
FOR ATTACH;
GO
try it :)
+--
Kenial.GhostOnNetwork.
D2 wrote:
> Hi,
> I have got database files from sql server 2000 which i'm trying to
> attach to sql server 2005 database. During the attach process, I'm
> getting the following erros:
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>
> Msg 601, Level 12, State 3, Line 1
> Could not continue scan with NOLOCK due to data movement.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Dev'. CREATE DATABASE is aborted.
> Any idea what this refers to?
> Regards,
> D2
>|||D2
This example I took from the BOL. Try it .
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
FOR ATTACH');
GO
"D2" <dhapola@.yahoo.com> wrote in message
news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
> Hi,
> I have got database files from sql server 2000 which i'm trying to
> attach to sql server 2005 database. During the attach process, I'm
> getting the following erros:
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>
> Msg 601, Level 12, State 3, Line 1
> Could not continue scan with NOLOCK due to data movement.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Dev'. CREATE DATABASE is aborted.
> Any idea what this refers to?
> Regards,
> D2
>|||Tried the attach from menu before writing queries :-)
thanks for your time.
On Jun 20, 2:04 pm, Kenial <ken...@.shinbiro.com.korea> wrote:
> D2,
> How about trying to do same thing in SSMS attach menu?
> and I found similar SQL in BOL :
> CREATE DATABASE pubs ON PRIMARY
> (FILENAME => 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf')
> LOG ON (FILENAME => 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
> FOR ATTACH;
> GO
> try it :)
> +--
> Kenial.GhostOnNetwork.
> D2 wrote:
> > Hi,
> > I have got database files from sql server 2000 which i'm trying to
> > attach to sql server 2005 database. During the attach process, I'm
> > getting the following erros:
> > EXEC sp_attach_db @.dbname = N'Dev',
> > @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> > @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
> > Msg 601, Level 12, State 3, Line 1
> > Could not continue scan with NOLOCK due to data movement.
> > Msg 1813, Level 16, State 2, Line 1
> > Could not open new database 'Dev'. CREATE DATABASE is aborted.
> > Any idea what this refers to?
> > Regards,
> > D2|||Already tried that; BOL query is only trying to dynamically attach the
folder path.
sp_attach_db sp is internally executing the same query.
thanks for your time.
On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> D2
> This example I took from the BOL. Try it .
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> FOR ATTACH');
> GO"D2" <dhap...@.yahoo.com> wrote in message
> news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
> > Hi,
> > I have got database files from sql server 2000 which i'm trying to
> > attach to sql server 2005 database. During the attach process, I'm
> > getting the following erros:
> > EXEC sp_attach_db @.dbname = N'Dev',
> > @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> > @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
> > Msg 601, Level 12, State 3, Line 1
> > Could not continue scan with NOLOCK due to data movement.
> > Msg 1813, Level 16, State 2, Line 1
> > Could not open new database 'Dev'. CREATE DATABASE is aborted.
> > Any idea what this refers to?
> > Regards,
> > D2|||D2
Do you have any open transactions? Any other actvities while the attach
process is running?
"D2" <dhapola@.yahoo.com> wrote in message
news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
> Already tried that; BOL query is only trying to dynamically attach the
> folder path.
> sp_attach_db sp is internally executing the same query.
> thanks for your time.
> On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> D2
>> This example I took from the BOL. Try it .
>> USE master;
>> GO
>> sp_detach_db Archive;
>> GO
>> -- Get the SQL Server data path
>> DECLARE @.data_path nvarchar(256);
>> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> FROM master.sys.master_files
>> WHERE database_id = 1 AND file_id = 1);
>> -- Execute CREATE DATABASE FOR ATTACH statement
>> EXEC ('CREATE DATABASE Archive
>> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> FOR ATTACH');
>> GO"D2" <dhap...@.yahoo.com> wrote in message
>> news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
>> > Hi,
>> > I have got database files from sql server 2000 which i'm trying to
>> > attach to sql server 2005 database. During the attach process, I'm
>> > getting the following erros:
>> > EXEC sp_attach_db @.dbname = N'Dev',
>> > @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
>> > @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>> > Msg 601, Level 12, State 3, Line 1
>> > Could not continue scan with NOLOCK due to data movement.
>> > Msg 1813, Level 16, State 2, Line 1
>> > Could not open new database 'Dev'. CREATE DATABASE is aborted.
>> > Any idea what this refers to?
>> > Regards,
>> > D2
>|||On this particular database: no. i'm trying to attach these files to a
new database.
However the same sql server instance is hosting many other databases,
in any of those there may be open transaction.
On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> D2
> Do you have any open transactions? Any other actvities while the attach
> process is running?
> "D2" <dhap...@.yahoo.com> wrote in message
> news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
> > Already tried that; BOL query is only trying to dynamically attach the
> > folder path.
> > sp_attach_db sp is internally executing the same query.
> > thanks for your time.
> > On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> D2
> >> This example I took from the BOL. Try it .
> >> USE master;
> >> GO
> >> sp_detach_db Archive;
> >> GO
> >> -- Get the SQL Server data path
> >> DECLARE @.data_path nvarchar(256);
> >> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> >> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> >> FROM master.sys.master_files
> >> WHERE database_id = 1 AND file_id = 1);
> >> -- Execute CREATE DATABASE FOR ATTACH statement
> >> EXEC ('CREATE DATABASE Archive
> >> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> >> FOR ATTACH');
> >> GO"D2" <dhap...@.yahoo.com> wrote in message
> >>news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
> >> > Hi,
> >> > I have got database files from sql server 2000 which i'm trying to
> >> > attach to sql server 2005 database. During the attach process, I'm
> >> > getting the following erros:
> >> > EXEC sp_attach_db @.dbname = N'Dev',
> >> > @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> >> > @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
> >> > Msg 601, Level 12, State 3, Line 1
> >> > Could not continue scan with NOLOCK due to data movement.
> >> > Msg 1813, Level 16, State 2, Line 1
> >> > Could not open new database 'Dev'. CREATE DATABASE is aborted.
> >> > Any idea what this refers to?
> >> > Regards,
> >> > D2|||D2
Are you saying that you created a new database called 'Dev' and then run the
following script?
EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
sp_attach_db is depricated by MS and will be removed in the future.
I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
Try moving those files to another location and try execute.
"D2" <dhapola@.yahoo.com> wrote in message
news:1182336562.364728.71990@.i13g2000prf.googlegroups.com...
> On this particular database: no. i'm trying to attach these files to a
> new database.
> However the same sql server instance is hosting many other databases,
> in any of those there may be open transaction.
> On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> D2
>> Do you have any open transactions? Any other actvities while the attach
>> process is running?
>> "D2" <dhap...@.yahoo.com> wrote in message
>> news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
>> > Already tried that; BOL query is only trying to dynamically attach the
>> > folder path.
>> > sp_attach_db sp is internally executing the same query.
>> > thanks for your time.
>> > On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> >> D2
>> >> This example I took from the BOL. Try it .
>> >> USE master;
>> >> GO
>> >> sp_detach_db Archive;
>> >> GO
>> >> -- Get the SQL Server data path
>> >> DECLARE @.data_path nvarchar(256);
>> >> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> >> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> >> FROM master.sys.master_files
>> >> WHERE database_id = 1 AND file_id = 1);
>> >> -- Execute CREATE DATABASE FOR ATTACH statement
>> >> EXEC ('CREATE DATABASE Archive
>> >> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> >> FOR ATTACH');
>> >> GO"D2" <dhap...@.yahoo.com> wrote in message
>> >>news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
>> >> > Hi,
>> >> > I have got database files from sql server 2000 which i'm trying to
>> >> > attach to sql server 2005 database. During the attach process, I'm
>> >> > getting the following erros:
>> >> > EXEC sp_attach_db @.dbname = N'Dev',
>> >> > @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
>> >> > @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>> >> > Msg 601, Level 12, State 3, Line 1
>> >> > Could not continue scan with NOLOCK due to data movement.
>> >> > Msg 1813, Level 16, State 2, Line 1
>> >> > Could not open new database 'Dev'. CREATE DATABASE is aborted.
>> >> > Any idea what this refers to?
>> >> > Regards,
>> >> > D2
>|||No. What I mean is.. while executing these sql statements, a new
database called Dev is being created having filegroups set to the mdf
and ldf files ginve in the filename parameters.
I have done attach/detach many times.. both by writng query and using
ssdm menus. Only this time I'm getting some problem.
On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> D2
> Are you saying that you created a new database called 'Dev' and then run the
> following script?
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
> sp_attach_db is depricated by MS and will be removed in the future.
> I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
> Try moving those files to another location and try execute.
> "D2" <dhap...@.yahoo.com> wrote in message
> news:1182336562.364728.71990@.i13g2000prf.googlegroups.com...
> > On this particular database: no. i'm trying to attach these files to a
> > new database.
> > However the same sql server instance is hosting many other databases,
> > in any of those there may be open transaction.
> > On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> D2
> >> Do you have any open transactions? Any other actvities while the attach
> >> process is running?
> >> "D2" <dhap...@.yahoo.com> wrote in message
> >>news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
> >> > Already tried that; BOL query is only trying to dynamically attach the
> >> > folder path.
> >> > sp_attach_db sp is internally executing the same query.
> >> > thanks for your time.
> >> > On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> >> D2
> >> >> This example I took from the BOL. Try it .
> >> >> USE master;
> >> >> GO
> >> >> sp_detach_db Archive;
> >> >> GO
> >> >> -- Get the SQL Server data path
> >> >> DECLARE @.data_path nvarchar(256);
> >> >> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> >> >> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> >> >> FROM master.sys.master_files
> >> >> WHERE database_id = 1 AND file_id = 1);
> >> >> -- Execute CREATE DATABASE FOR ATTACH statement
> >> >> EXEC ('CREATE DATABASE Archive
> >> >> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> >> >> FOR ATTACH');
> >> >> GO"D2" <dhap...@.yahoo.com> wrote in message
> >> >>news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
> >> >> > Hi,
> >> >> > I have got database files from sql server 2000 which i'm trying to
> >> >> > attach to sql server 2005 database. During the attach process, I'm
> >> >> > getting the following erros:
> >> >> > EXEC sp_attach_db @.dbname = N'Dev',
> >> >> > @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> >> >> > @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
> >> >> > Msg 601, Level 12, State 3, Line 1
> >> >> > Could not continue scan with NOLOCK due to data movement.
> >> >> > Msg 1813, Level 16, State 2, Line 1
> >> >> > Could not open new database 'Dev'. CREATE DATABASE is aborted.
> >> >> > Any idea what this refers to?
> >> >> > Regards,
> >> >> > D2|||D2
Okay, do you have a backup of the database. Try using RESTORE coomand
instead of attaching the files.
"D2" <dhapola@.yahoo.com> wrote in message
news:1182342259.453149.68560@.i38g2000prf.googlegroups.com...
> No. What I mean is.. while executing these sql statements, a new
> database called Dev is being created having filegroups set to the mdf
> and ldf files ginve in the filename parameters.
> I have done attach/detach many times.. both by writng query and using
> ssdm menus. Only this time I'm getting some problem.
> On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> D2
>> Are you saying that you created a new database called 'Dev' and then run
>> the
>> following script?
>> EXEC sp_attach_db @.dbname = N'Dev',
>> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
>> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>> sp_attach_db is depricated by MS and will be removed in the future.
>> I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
>> Try moving those files to another location and try execute.
>> "D2" <dhap...@.yahoo.com> wrote in message
>> news:1182336562.364728.71990@.i13g2000prf.googlegroups.com...
>> > On this particular database: no. i'm trying to attach these files to a
>> > new database.
>> > However the same sql server instance is hosting many other databases,
>> > in any of those there may be open transaction.
>> > On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> >> D2
>> >> Do you have any open transactions? Any other actvities while the
>> >> attach
>> >> process is running?
>> >> "D2" <dhap...@.yahoo.com> wrote in message
>> >>news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
>> >> > Already tried that; BOL query is only trying to dynamically attach
>> >> > the
>> >> > folder path.
>> >> > sp_attach_db sp is internally executing the same query.
>> >> > thanks for your time.
>> >> > On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> >> >> D2
>> >> >> This example I took from the BOL. Try it .
>> >> >> USE master;
>> >> >> GO
>> >> >> sp_detach_db Archive;
>> >> >> GO
>> >> >> -- Get the SQL Server data path
>> >> >> DECLARE @.data_path nvarchar(256);
>> >> >> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> >> >> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> >> >> FROM master.sys.master_files
>> >> >> WHERE database_id = 1 AND file_id = 1);
>> >> >> -- Execute CREATE DATABASE FOR ATTACH statement
>> >> >> EXEC ('CREATE DATABASE Archive
>> >> >> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> >> >> FOR ATTACH');
>> >> >> GO"D2" <dhap...@.yahoo.com> wrote in message
>> >> >>news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
>> >> >> > Hi,
>> >> >> > I have got database files from sql server 2000 which i'm trying
>> >> >> > to
>> >> >> > attach to sql server 2005 database. During the attach process,
>> >> >> > I'm
>> >> >> > getting the following erros:
>> >> >> > EXEC sp_attach_db @.dbname = N'Dev',
>> >> >> > @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
>> >> >> > @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>> >> >> > Msg 601, Level 12, State 3, Line 1
>> >> >> > Could not continue scan with NOLOCK due to data movement.
>> >> >> > Msg 1813, Level 16, State 2, Line 1
>> >> >> > Could not open new database 'Dev'. CREATE DATABASE is aborted.
>> >> >> > Any idea what this refers to?
>> >> >> > Regards,
>> >> >> > D2
>|||Was the source database cleanly detached using sp_detach_db?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"D2" <dhapola@.yahoo.com> wrote in message
news:1182342259.453149.68560@.i38g2000prf.googlegroups.com...
> No. What I mean is.. while executing these sql statements, a new
> database called Dev is being created having filegroups set to the mdf
> and ldf files ginve in the filename parameters.
> I have done attach/detach many times.. both by writng query and using
> ssdm menus. Only this time I'm getting some problem.
> On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> D2
>> Are you saying that you created a new database called 'Dev' and then run
>> the
>> following script?
>> EXEC sp_attach_db @.dbname = N'Dev',
>> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
>> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>> sp_attach_db is depricated by MS and will be removed in the future.
>> I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
>> Try moving those files to another location and try execute.
>> "D2" <dhap...@.yahoo.com> wrote in message
>> news:1182336562.364728.71990@.i13g2000prf.googlegroups.com...
>> > On this particular database: no. i'm trying to attach these files to a
>> > new database.
>> > However the same sql server instance is hosting many other databases,
>> > in any of those there may be open transaction.
>> > On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> >> D2
>> >> Do you have any open transactions? Any other actvities while the
>> >> attach
>> >> process is running?
>> >> "D2" <dhap...@.yahoo.com> wrote in message
>> >>news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
>> >> > Already tried that; BOL query is only trying to dynamically attach
>> >> > the
>> >> > folder path.
>> >> > sp_attach_db sp is internally executing the same query.
>> >> > thanks for your time.
>> >> > On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> >> >> D2
>> >> >> This example I took from the BOL. Try it .
>> >> >> USE master;
>> >> >> GO
>> >> >> sp_detach_db Archive;
>> >> >> GO
>> >> >> -- Get the SQL Server data path
>> >> >> DECLARE @.data_path nvarchar(256);
>> >> >> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
>> >> >> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
>> >> >> FROM master.sys.master_files
>> >> >> WHERE database_id = 1 AND file_id = 1);
>> >> >> -- Execute CREATE DATABASE FOR ATTACH statement
>> >> >> EXEC ('CREATE DATABASE Archive
>> >> >> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
>> >> >> FOR ATTACH');
>> >> >> GO"D2" <dhap...@.yahoo.com> wrote in message
>> >> >>news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
>> >> >> > Hi,
>> >> >> > I have got database files from sql server 2000 which i'm trying
>> >> >> > to
>> >> >> > attach to sql server 2005 database. During the attach process,
>> >> >> > I'm
>> >> >> > getting the following erros:
>> >> >> > EXEC sp_attach_db @.dbname = N'Dev',
>> >> >> > @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
>> >> >> > @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log.LDF';
>> >> >> > Msg 601, Level 12, State 3, Line 1
>> >> >> > Could not continue scan with NOLOCK due to data movement.
>> >> >> > Msg 1813, Level 16, State 2, Line 1
>> >> >> > Could not open new database 'Dev'. CREATE DATABASE is aborted.
>> >> >> > Any idea what this refers to?
>> >> >> > Regards,
>> >> >> > D2
>

attach database issue

Hi,
I have got database files from sql server 2000 which i'm trying to
attach to sql server 2005 database. During the attach process, I'm
getting the following erros:
EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Dev'. CREATE DATABASE is aborted.
Any idea what this refers to?
Regards,
D2D2,
How about trying to do same thing in SSMS attach menu?
and I found similar SQL in BOL :
CREATE DATABASE pubs ON PRIMARY
(FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf')
LOG ON (FILENAME =
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
FOR ATTACH;
GO
try it
+--
Kenial.GhostOnNetwork.
D2 wrote:
> Hi,
> I have got database files from sql server 2000 which i'm trying to
> attach to sql server 2005 database. During the attach process, I'm
> getting the following erros:
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
>
> Msg 601, Level 12, State 3, Line 1
> Could not continue scan with NOLOCK due to data movement.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Dev'. CREATE DATABASE is aborted.
> Any idea what this refers to?
> Regards,
> D2
>|||D2
This example I took from the BOL. Try it .
USE master;
GO
sp_detach_db Archive;
GO
-- Get the SQL Server data path
DECLARE @.data_path nvarchar(256);
SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files
WHERE database_id = 1 AND file_id = 1);
-- Execute CREATE DATABASE FOR ATTACH statement
EXEC ('CREATE DATABASE Archive
ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
FOR ATTACH');
GO
"D2" <dhapola@.yahoo.com> wrote in message
news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
> Hi,
> I have got database files from sql server 2000 which i'm trying to
> attach to sql server 2005 database. During the attach process, I'm
> getting the following erros:
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
>
> Msg 601, Level 12, State 3, Line 1
> Could not continue scan with NOLOCK due to data movement.
> Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'Dev'. CREATE DATABASE is aborted.
> Any idea what this refers to?
> Regards,
> D2
>|||Tried the attach from menu before writing queries :-)
thanks for your time.
On Jun 20, 2:04 pm, Kenial <ken...@.shinbiro.com.korea> wrote:[vbcol=seagreen]
> D2,
> How about trying to do same thing in SSMS attach menu?
> and I found similar SQL in BOL :
> CREATE DATABASE pubs ON PRIMARY
> (FILENAME =
> 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf'
)
> LOG ON (FILENAME =
> 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
> FOR ATTACH;
> GO
> try it
> +--
> Kenial.GhostOnNetwork.
> D2 wrote:
>
>
>
>
>|||Already tried that; BOL query is only trying to dynamically attach the
folder path.
sp_attach_db sp is internally executing the same query.
thanks for your time.
On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> This example I took from the BOL. Try it .
> USE master;
> GO
> sp_detach_db Archive;
> GO
> -- Get the SQL Server data path
> DECLARE @.data_path nvarchar(256);
> SET @.data_path = (SELECT SUBSTRING(physical_name, 1,
> CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
> FROM master.sys.master_files
> WHERE database_id = 1 AND file_id = 1);
> -- Execute CREATE DATABASE FOR ATTACH statement
> EXEC ('CREATE DATABASE Archive
> ON (FILENAME = '''+ @.data_path + 'archdat1.mdf'')
> FOR ATTACH');
> GO"D2" <dhap...@.yahoo.com> wrote in message
> news:1182324219.833398.13530@.q19g2000prn.googlegroups.com...
>
>
>
>
>
>|||D2
Do you have any open transactions? Any other actvities while the attach
process is running?
"D2" <dhapola@.yahoo.com> wrote in message
news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
> Already tried that; BOL query is only trying to dynamically attach the
> folder path.
> sp_attach_db sp is internally executing the same query.
> thanks for your time.
> On Jun 20, 2:03 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>|||On this particular database: no. i'm trying to attach these files to a
new database.
However the same sql server instance is hosting many other databases,
in any of those there may be open transaction.
On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> Do you have any open transactions? Any other actvities while the attach
> process is running?
> "D2" <dhap...@.yahoo.com> wrote in message
> news:1182332774.416879.280860@.j4g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>|||D2
Are you saying that you created a new database called 'Dev' and then run the
following script?
EXEC sp_attach_db @.dbname = N'Dev',
@.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
@.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
sp_attach_db is depricated by MS and will be removed in the future.
I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
Try moving those files to another location and try execute.
"D2" <dhapola@.yahoo.com> wrote in message
news:1182336562.364728.71990@.i13g2000prf.googlegroups.com...
> On this particular database: no. i'm trying to attach these files to a
> new database.
> However the same sql server instance is hosting many other databases,
> in any of those there may be open transaction.
> On Jun 20, 3:29 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>|||No. What I mean is.. while executing these sql statements, a new
database called Dev is being created having filegroups set to the mdf
and ldf files ginve in the filename parameters.
I have done attach/detach many times.. both by writng query and using
ssdm menus. Only this time I'm getting some problem.
On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:[vbcol=seagreen]
> D2
> Are you saying that you created a new database called 'Dev' and then run t
he
> following script?
> EXEC sp_attach_db @.dbname = N'Dev',
> @.filename1 = N'F:\SQL2005\Data\LAWSONDatabase\DEV.MDF',
> @.filename2 = N'F:\SQL2005\Data\LAWSONDatabase\Dev_log
.LDF';
> sp_attach_db is depricated by MS and will be removed in the future.
> I used CREATE DATABASE ...FOR ATTCH option and it worked just fine.
> Try moving those files to another location and try execute.
> "D2" <dhap...@.yahoo.com> wrote in message
> news:1182336562.364728.71990@.i13g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>|||D2
Okay, do you have a backup of the database. Try using RESTORE coomand
instead of attaching the files.
"D2" <dhapola@.yahoo.com> wrote in message
news:1182342259.453149.68560@.i38g2000prf.googlegroups.com...
> No. What I mean is.. while executing these sql statements, a new
> database called Dev is being created having filegroups set to the mdf
> and ldf files ginve in the filename parameters.
> I have done attach/detach many times.. both by writng query and using
> ssdm menus. Only this time I'm getting some problem.
> On Jun 20, 4:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>sql

Asynchronous Batch Processing

Currently we have a process that does synchronous batch processing that can
take a long time and the user cannot do anything while this is happening. W
e
want to encapsulate this process into an asynchronous operation. I imagine
that the processing itself will now reside in a DTS package (SSIS package
actually, since we will be using SQL Server 2005).
Scheduling a package to run asynchronously is no problem, but it would be
nice if it notified the calling app (which is UNIFACE btw - I don't know a
lot about it, so don't ask me) that the batch process is complete. We could
have the UNIFACE app poll to check the status, but I was wondering if Servic
e
Broker could help in this capacity or would that be overkill?
Ultimately, we will be replacing the UNIFACE app with our own suite, and
want the process to be as modular as possible, to facilitate a painless
conversion.
Thanks,
BrandonBrandon,
There are a number of reasons which I believe make Service Broker specially
qualified for this kind of jobs (i.e. asynchronous execution). Being
entirely contained in the database and is running inside the SQL Server
process allows Service Broker based apps to benefit from backup/restore (the
state of your jobs is backed up as part of the database), from
failover/clustering and from database mirroring (the job schedule just fails
over along with the database). Service Broker also gives you a mean to
communicate back from this jobs to the calling app (dialogs are always
bidirectional, the job can reply back on the same dialog that started the
job). Also you'll benefit from the poll free model of the Service Broker:
WAITFOR (RECEIVE ...) does not poll, it blocks until a message becomes
available.
Another nice feature of Service Broker is that it can give you persisted
timers (BEGIN CONVERSATION TIMER ...), stored in the database (again,
benefiting from all the backup/restore and availability benefits of
databases)
What are you afraid of when you say that Service Broker would be overkill?
HTH,
~ Remus
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:0F6D5B45-7A24-4539-BE76-E5B8675A3E83@.microsoft.com...
> Currently we have a process that does synchronous batch processing that
> can
> take a long time and the user cannot do anything while this is happening.
> We
> want to encapsulate this process into an asynchronous operation. I
> imagine
> that the processing itself will now reside in a DTS package (SSIS package
> actually, since we will be using SQL Server 2005).
> Scheduling a package to run asynchronously is no problem, but it would be
> nice if it notified the calling app (which is UNIFACE btw - I don't know a
> lot about it, so don't ask me) that the batch process is complete. We
> could
> have the UNIFACE app poll to check the status, but I was wondering if
> Service
> Broker could help in this capacity or would that be overkill?
> Ultimately, we will be replacing the UNIFACE app with our own suite, and
> want the process to be as modular as possible, to facilitate a painless
> conversion.
> Thanks,
> Brandon|||Mainly I am hesitant for two reasons... I am not that familiar with the
capabilities of UNIFACE (from what I understand it would have to poll instea
d
of using a blocking call like WAITFOR to determine whether job
completed/status of job) and also that I have only had minimal experience
with Service Broker (in the form of the several very simply demos out there)
.
Since the UNIFACE interface will eventually be replaced by a Delphi.NET app,
I can more easily see how that would work better in the long term.
Have you seen any Service Broker examples that communicate with a SSIS
package?
Thanks,
Brandon|||I'm gonna have to do some research about SSIS to see how it integrates with
SSB
What kind of asynchronous batch is gonna be processed? Are talking about
launching an external process, calling a stored proc, running a t-sql batch,
calling an CLR stored procedure?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon|||Two Connect has a sample of Service Broker custom tasks for SSIS:
[url]http://www.twoconnect.com/pages/product_solutions/sqlserver.enhancements.ASPX[/url
]
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Brandon Lilly" <avarice@.nospam_swbell.net> wrote in message
news:ACEE6EC9-ADE7-4FEE-B6C0-EDBFE6DDAFF6@.microsoft.com...
> Mainly I am hesitant for two reasons... I am not that familiar with the
> capabilities of UNIFACE (from what I understand it would have to poll
> instead
> of using a blocking call like WAITFOR to determine whether job
> completed/status of job) and also that I have only had minimal experience
> with Service Broker (in the form of the several very simply demos out
> there).
> Since the UNIFACE interface will eventually be replaced by a Delphi.NET
> app,
> I can more easily see how that would work better in the long term.
> Have you seen any Service Broker examples that communicate with a SSIS
> package?
> Thanks,
> Brandon

async_network_io wait during replication

I have re-initialize some subscription. After generated snapshot successfully, the synchronization view showing 'The process is running and is waiting for a response from the server.' and I found the replication process is under 'async_network_io wait'. The job has run for more than 8 hrs. Any idea to improve it? Thanks in advaise.

Are you running the sync agent (like merge agent/distribution agent) in continuous mode? If yes, it will show running forever, although the submitted transactions/commands have been replicated, agent job is just keeping waiting for next actions.

Thanks

-Yunjing

|||Yes, in continuous mode. But the replication hadn't completed in 8 hrs. Any idea? Thanks|||

If an continuous sync complete for a session, its current history log usually shows something like "No replicated transactions are available" and sync job is still showing as running.

As for your case, could you open sqlmonitor to check the current status for the job, is it still running or fail already? If it's running, what's the current history shown? Does the history get updated every a few minutes?

-Yunjing

ASYNC_NETWORK_IO

Hello, When I execute a SQL SERVER 2005 stored procedure from Visual Basic 6 Application, I get my process locked with ASYNC_NETWORK_IO wait type.

When I Execute the same procedure from sql server console, It doesn't occur...

This is the visual basic code:

With objcommand

.ActiveConnection = CnnConexionPpalDb
.CommandTimeout = 0
.CommandText = "EJECUTIN"
.CommandType = adCmdStoredProc

'- Parametros del Command

'.Parameters("@.StrOrdenEstablecido").Value = StrOrdenEstablecido

End With
Set gTbl.VisOrdenamientoDatosFinalCiudades = objcommand.Execute

With gTbl.VisOrdenamientoDatosFinalCiudades

This is SQL SERVER CODE:

ALTER PROCEDURE [dbo].PCN_CONSULTAR_VISORDENAMIENTODATOSFINALCIUDADES_ORDENADO] @.StrOrdenEstablecido varchar(200)
as
BEGIN TRAN
DECLARE @.lvaSentencia NVARCHAR(4000)

SELECT dbo.Tbl_DatosSubidos.Direccion, dbo.Tbl_DatosSubidos.Ciudad, dbo.Tbl_DatosSubidos.Departamento,
dbo.Tbl_DatosSubidos.Inserto, dbo.Tbl_DatosSubidos.IndiceOLlaveUnica, dbo.Tbl_DatosSubidos.Identificacion,
dbo.Tbl_DetalleCiudadesBuenas.IdArchivo, dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes,
dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytesFinalDato, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Dep_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dep_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.DatoIngresado, dbo.Tbl_DetalleCiudadesBuenas.Dis_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dis_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.Doc_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Doc_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Cli_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Cli_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.DCB_Novedad,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Georreferenciada, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Prioridad,
dbo.Tbl_DetalleCiudadesBuenas.Geo_CodigoZona, dbo.Tbl_DetalleCiudadesBuenas.DatoCiudadCompleta,
dbo.Tbl_DetalleCiudadesBuenas.Tipo_Distribucion, dbo.Tbl_DatosSubidos.Factura, dbo.Tbl_DatosSubidos.Nombre,
dbo.Tbl_ArchivosSubidos.Ars_NombreArchivoSubido AS NombreArchivo
FROM dbo.Tbl_DatosSubidos WITH (NOLOCK) INNER JOIN
dbo.Tbl_ArchivosSubidos WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_ArchivosSubidos.Ars_Codigo INNER JOIN
dbo.Tbl_DetalleCiudadesBuenas WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_DetalleCiudadesBuenas.IdArchivo AND
dbo.Tbl_DatosSubidos.PosicionEnBytes = dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes

ORDER BY 1
COMMIT

This wait type means the server is busy sending rows to the client fo processing. The best way to fix it is to reduce the number of rows returned. Alternatively, client-side processing should be accelerated. Or both.

-Ryan / Kardax

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_IO
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a process
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use “Localhost” rather than the DNS name
(thinking that it might have to ask the DNS server what it’s name is) but the
process will still get the same error.
I’m confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-Eric
The fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among all
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, you
are probably okay.
Linchi
"ep" wrote:

> I have a process that runs that keeps getting a wait type of async_network_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes from
> one table and inserts/updates another. This is the one that starts a process
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use “Localhost” rather than the DNS name
> (thinking that it might have to ask the DNS server what it’s name is) but the
> process will still get the same error.
> I’m confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_IO
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a process
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use â'Localhostâ' rather than the DNS name
(thinking that it might have to ask the DNS server what itâ's name is) but the
process will still get the same error.
Iâ'm confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-EricThe fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among all
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, you
are probably okay.
Linchi
"ep" wrote:
> I have a process that runs that keeps getting a wait type of async_network_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes from
> one table and inserts/updates another. This is the one that starts a process
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use â'Localhostâ' rather than the DNS name
> (thinking that it might have to ask the DNS server what itâ's name is) but the
> process will still get the same error.
> Iâ'm confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>sql

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_I
O
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a proces
s
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use “Localhost” rather than the DNS name
(thinking that it might have to ask the DNS server what it’s name is) but
the
process will still get the same error.
I’m confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-EricThe fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among al
l
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, yo
u
are probably okay.
Linchi
"ep" wrote:

> I have a process that runs that keeps getting a wait type of async_network
_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID
5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes fr
om
> one table and inserts/updates another. This is the one that starts a proc
ess
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use “Localhost” rather than the DNS name
> (thinking that it might have to ask the DNS server what it’s name is) bu
t the
> process will still get the same error.
> I’m confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>

assigning process priority to transactions?

I am using ADO 2.8 and SQL server 2000.
Is there any way to assign process priorities to multiple DB connections?
For example, consider that there is a long-time taking transaction and many
short-time transactions running on a server machine. I want to assign low
priority to the long-time transaction to prevent from starvation of
short-time transactions. Of couse, let's assume that there is no locking
influence between them.
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeHi
I am affraid you cannot do that. Perhaps you want to look at SET
LOCK_TIMEOUT command.
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
news:upjA1nJbFHA.228@.TK2MSFTNGP12.phx.gbl...
> I am using ADO 2.8 and SQL server 2000.
> Is there any way to assign process priorities to multiple DB connections?
> For example, consider that there is a long-time taking transaction and
many
> short-time transactions running on a server machine. I want to assign low
> priority to the long-time transaction to prevent from starvation of
> short-time transactions. Of couse, let's assume that there is no locking
> influence between them.
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>

Assertion Errors:

Hi All,

I have started getting Assertion Errors in SQL.
It appears when I process a cube (Most of the time)
Other SQL statements, usually with a join or 6 do the same thing.

Whaving a scratch around google, I noticed the most people who get
these errors are using SATA drives. Either RAID or not.
Surprise, I am using SATA in RAID 1.

Is this a common thing with SATA? I can't go to the pwers that be and
say I need a couple large SCSI drives because I _think_ it's the
SATA's.

Another very odd thing that happened thismorning was I copied the mdf
and ldf files off my machine (About 70GB) and onto the server. attached
them and SQL was happy.
Select Count(*) from aview gave me the count I was expecting.
Select * From aview returned no rows. most of the time.

I thought I was going mad. F5 works, then it doesn't then it does then,
you get the point.
Backup and restore seemed better until the errors below started...

HELP!!!!

Thanks
Cheers,
Crispin

17066 :
SQL Server Assertion: File:
<q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.<crispin.proctor@.gmail.com> wrote in message
news:1105982769.678231.261080@.f14g2000cwb.googlegr oups.com...
> Hi All,
> I have started getting Assertion Errors in SQL.
> It appears when I process a cube (Most of the time)
> Other SQL statements, usually with a join or 6 do the same thing.
> Whaving a scratch around google, I noticed the most people who get
> these errors are using SATA drives. Either RAID or not.
> Surprise, I am using SATA in RAID 1.
> Is this a common thing with SATA? I can't go to the pwers that be and
> say I need a couple large SCSI drives because I _think_ it's the
> SATA's.
> Another very odd thing that happened thismorning was I copied the mdf
> and ldf files off my machine (About 70GB) and onto the server. attached
> them and SQL was happy.
> Select Count(*) from aview gave me the count I was expecting.
> Select * From aview returned no rows. most of the time.
> I thought I was going mad. F5 works, then it doesn't then it does then,
> you get the point.
> Backup and restore seemed better until the errors below started...
>
> HELP!!!!
> Thanks
> Cheers,
> Crispin
> 17066 :
> SQL Server Assertion: File:
> <q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.

This KB article seems to describe what you're seeing:

http://support.microsoft.com/kb/828337

Simon|||Thanks Simon,

Problem is the checkDB does the same thing.
We have replaced the drives, drive controller etc.
Even replaced the mother board.
Only thing I have not replaced on the machine is the mouse :)

My concern is we have shipped some servers to client with a simlar
config. (SATA drives running RAID 1)
Now if this is a limitation of SATA I am in a world of sh1t.
We use SATA drives as a cheaper alternative to SCSI for some clients.
I reasoned they would be faster than IDE and if Dell, HP etc have SANs
running on SATA drives, they can't be all that bad.

arrrrggggggg. This is killing me. It's actually gettiing worse. Run the
diagnostics on the drives and no error's reported.

Cheers,
Crispin

Simon Hayes wrote:
> <crispin.proctor@.gmail.com> wrote in message
> news:1105982769.678231.261080@.f14g2000cwb.googlegr oups.com...
> > Hi All,
> > I have started getting Assertion Errors in SQL.
> > It appears when I process a cube (Most of the time)
> > Other SQL statements, usually with a join or 6 do the same thing.
> > Whaving a scratch around google, I noticed the most people who get
> > these errors are using SATA drives. Either RAID or not.
> > Surprise, I am using SATA in RAID 1.
> > Is this a common thing with SATA? I can't go to the pwers that be
and
> > say I need a couple large SCSI drives because I _think_ it's the
> > SATA's.
> > Another very odd thing that happened thismorning was I copied the
mdf
> > and ldf files off my machine (About 70GB) and onto the server.
attached
> > them and SQL was happy.
> > Select Count(*) from aview gave me the count I was expecting.
> > Select * From aview returned no rows. most of the time.
> > I thought I was going mad. F5 works, then it doesn't then it does
then,
> > you get the point.
> > Backup and restore seemed better until the errors below started...
> > HELP!!!!
> > Thanks
> > Cheers,
> > Crispin
> > 17066 :
> > SQL Server Assertion: File:
> > <q:\SPHINX\NTDBMS\storeng\drs\include\record.inl>, line=1447
> > Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
> This KB article seems to describe what you're seeing:
> http://support.microsoft.com/kb/828337
> Simon|||(crispin.proctor@.gmail.com) writes:
> Problem is the checkDB does the same thing.
> We have replaced the drives, drive controller etc.
> Even replaced the mother board.
> Only thing I have not replaced on the machine is the mouse :)
> My concern is we have shipped some servers to client with a simlar
> config. (SATA drives running RAID 1)
> Now if this is a limitation of SATA I am in a world of sh1t.
> We use SATA drives as a cheaper alternative to SCSI for some clients.
> I reasoned they would be faster than IDE and if Dell, HP etc have SANs
> running on SATA drives, they can't be all that bad.

The KB article that Simon referred you to, suggested that you should
open a case with Microsoft, and I would encourage you do that.

Assertion errors are always bugs in SQL Server in the sense that they
should not occur. But it could be that instead you should have gotten
a better error message. In your case, it appears that your database is
corrupt. (Which could be due to a previous hardware error.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ASR backup of SQL server 2000

Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordon
please disregard this post.
"GoHwang" wrote:

> Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASR backup of SQL server 2000

Is there a process or procedure to use ASR as part of backup and recovery of
a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordonplease disregard this post.
"GoHwang" wrote:

> Is there a process or procedure to use ASR as part of backup and recovery
of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASR backup of SQL 2000 on win2k3

Is there a process or procedure to use ASR as part of backup and recovery of
a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordonplease disregard this post
"GoHwang" wrote:

> Is there a process or procedure to use ASR as part of backup and recovery
of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASR backup of SQL 2000 on win2k3

Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordonplease disregard this post
"GoHwang" wrote:
> Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASR backup of SQL 2000 on win2k3

Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
Thanks,
Gordon
please disregard this post
"GoHwang" wrote:

> Is there a process or procedure to use ASR as part of backup and recovery of a SQL server 2000 box? We are using SQL 2000 on win2k3 EE.
> Thanks,
> Gordon

ASPNETDB.MDF: The process cannot access the file because it is being used by another proce

Hi,

I'm trying to upload the ASPNETDB.MDF file to a hosting server via FTP, and everytime when it was uploaded half way(40% or 50%)

I would get an error message saying:

"550 ASPNETDB.MDF: The process cannot access the file because it is being used by another process"

and then the upload failed.

I'm using SQL Express. Does anybody know what's the cause?

Thanks a lot

It is because there are some users who are accessing the database through your web application. When the database is in process you cannot replace it. If you want to upload you need to make sure that no one is accessing your application. ASP.NET provides an easy way to disconnect all the clients to your web application with app_offline.htm file

Just create a simple html file with name app_offline. First upload this file to the web root of your application. Once this file was placed into the root directory, all clients will be disconnected and you can make some quick changes like replacing files etc, and after you finish replacing the files, delete the app_offline.htm file from the root.

You can also display a friend message in the app_offline like "Site under maintenance"

app_offline.htm file:

<html>
<body>
Site under maintenance!
</body>
</html>

Thanks

ASPNET worker process account can't login to Database

Hi,
I'm using .Net Remoting and IIS (5.1) to pull data from a (local for now)
SQL Server 2005 database. My application is failing at the point at which a
connection to the database is being opened.
Using cn as New SqlConnection(ConnectString)
cn.Open()
Causes an SQLException to be thrown. The message is...
Cannot open database "AppData" requested by the login. The login failed.
"Login failed for user '<MachineName>\ASPNET'
This is the ASP.Net worker process user. I have had some problems with this
User Id on this machine - it was originally absent from the machine. I got
it back by re-installing ASP.Net (ver 2.0). Now all my remoting stuff is
working, but I cannot get to the data.
So my question is, what configuration items / permissions in SQL Server
should I set up to give the ASPNet worker process access to my data?.
For now the application, SQL Server Instance, and IIS are all on the same
machine. Everything seems to work except for the login call to the Database
.
Thanks for your help.
BBMHello BBM,
You could add the <Machine Name>\ASPNET user in the SQL Login. After that,
you could grant the proper permission for the account to access the sql
database.
Here is an article you could refer:
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn2.microsoft.com/en-us/library/aa302387.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thanks for your help. I knew this had to be covered someplace.
BTW, Searching Help in SQL Server, or in Visual Studio on this topic, the
ASP "worker process" account, returns nothing remotely useful.
Thanks again.
BBM
"Wei Lu [MSFT]" wrote:

> Hello BBM,
> You could add the <Machine Name>\ASPNET user in the SQL Login. After that,
> you could grant the proper permission for the account to access the sql
> database.
> Here is an article you could refer:
> Building Secure ASP.NET Applications: Authentication, Authorization, and
> Secure Communication
> http://msdn2.microsoft.com/en-us/library/aa302387.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello BBM,
You could use the MSDN search in the web directly. And you could search for
ASPNET and SQL Server as the key words.
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

asp.net worker process runs out of memory when using a large dataset

Hi,

I'm running an application on a server which grabs data from a database table on another server using SqlConnection, SqlDataAdapter and DataSet.

The application then updates every row in that DataSet's DataTable and the updates are saved back using DataAdapter. The code is pretty much straightforward code that you would find on MSDN documentation for using DataSets. The table contains a little over a million rows.

When I run the application, I get an error saying the Server Application is not available. Upon looking into the application event log, I get this message.

aspnet_wp.exe was recycled because memory consumption exceeded the
306 MB (60 percent of available RAM)

How do I get round this? I thought DataSets were supposed to handle large datatables comfortably without having memory issues.

-Thanks

They can, just increase your recycle point. Although with a million rows, it's likely you grind that little box into the ground. Buy more RAM, or expect to wait forever for a response. Using a DataReader instead of a dataset is what will allow your little box to access large tables without consuming a ton of memory.