Database I/O error

I am using SQL 2000 Dev edition. I create a DB on box
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.
Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>
sql

Database I/O error

I am using SQL 2000 Dev edition. I create a DB on box
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>

Database I/O error

I am using SQL 2000 Dev edition. I create a DB on box
1. I run a simple program that loops through all records
in a table. It works fine. (VBA Code in MS Access
accessing the SQL table via ADO if it matters.) I then
detach the database and copy the file, plus log file, to
Box 2.
I attach the database on Box 2 and run the same code to
loop through all records in the table. It fails with an
I/O error 998 (Invalid access to memory location)
detected during read at offset 0x00000b8f1e0000 in
file 'My DB File'
It does not always stop at the same point. When I check,
it shows EOF to be true - even though a counter shows I
am less than 30% of the way through the file.
Is there some form of utility to check the validity of
the DB? I ran a ShrinkDatabase on box 1. Then copied
the file to box 2 again. Same results.
Known differences:
- Hardware.
- OS. (Can that really matter?)
- Box1 = Win2003 Server.
- Box2 = Win XP Pro SP1
Both boxes are SQL 2000 Developers edition SP3.
I'm pulling my hair out on this. Any help is greatly
appreciated.
Thank you.Sounds like a hardware issue, I have seen similar problems just prior to a
hard disk crapping out. Have you run any disk checking utilities against
the problem machine? Checked the event viewer for operating system and/or
Disk errors/warnings?
http://www.aspfaq.com/
(Reverse address to reply.)
"DB" <anonymous@.discussions.microsoft.com> wrote in message
news:180801c4d6fc$9cb189c0$a501280a@.phx.gbl...
> I am using SQL 2000 Dev edition. I create a DB on box
> 1. I run a simple program that loops through all records
> in a table. It works fine. (VBA Code in MS Access
> accessing the SQL table via ADO if it matters.) I then
> detach the database and copy the file, plus log file, to
> Box 2.
> I attach the database on Box 2 and run the same code to
> loop through all records in the table. It fails with an
> I/O error 998 (Invalid access to memory location)
> detected during read at offset 0x00000b8f1e0000 in
> file 'My DB File'
> It does not always stop at the same point. When I check,
> it shows EOF to be true - even though a counter shows I
> am less than 30% of the way through the file.
> Is there some form of utility to check the validity of
> the DB? I ran a ShrinkDatabase on box 1. Then copied
> the file to box 2 again. Same results.
> Known differences:
> - Hardware.
> - OS. (Can that really matter?)
> - Box1 = Win2003 Server.
> - Box2 = Win XP Pro SP1
> Both boxes are SQL 2000 Developers edition SP3.
> I'm pulling my hair out on this. Any help is greatly
> appreciated.
> Thank you.
>

Database error code: 515

We have MS SQL 2000 server. Recently, on our database application we gate
"Databse error code: 515" It says: Cannot insert the value NULL into cilumn
"contact_id", table name, column doesn't allow nulls. INSERT fails. No
changes made to databse. ...
A recent action on this table was, we exported the contact table to Excel,
updated, and imported from Excel.
Would you please advise us what the problem could be and how we can address
this.
Thanks,
Yigedeb.
CBI Inc
Hello,
Just see if you insert NULL into Contact_ID field. Please take a look into
table schema using SP_HELP <TableName> and verify the data you
are trying to insert..
Thanks
Hari
"Yigedeb" <Yigedeb@.discussions.microsoft.com> wrote in message
news:2FBF9D1C-F344-48AA-8172-0FF3E00A00BA@.microsoft.com...
> We have MS SQL 2000 server. Recently, on our database application we gate
> "Databse error code: 515" It says: Cannot insert the value NULL into
> cilumn
> "contact_id", table name, column doesn't allow nulls. INSERT fails. No
> changes made to databse. ...
> A recent action on this table was, we exported the contact table to Excel,
> updated, and imported from Excel.
> Would you please advise us what the problem could be and how we can
> address
> this.
> Thanks,
> Yigedeb.
> CBI

Database Engine Tuning Wizard: "Failed to initialize MSDB database for tuning (exit code:

Error trying to tune a database:
Database Engine Tuning Wizard: "Failed to initialize MSDB database for
tuning (exit code: -1). (DTAClient)
Steps to Reproduce the Problem
1. Launch Database Engine Tuning Advisor
2. Enter connection info
Server Name: <The Server dns name>
Authentication: SQL Server Authentication
Login: sa
Password: <the sa password>
3. Click "Connect"
Notes:
a. The target server has an MSDB database
b. The server is Windows 2000
c. The server is SQL Server 2000
d. The sa login can connect and see the msdb database
e. My desktop machine has no application compatibility flags set for
explorer.exe
Full error log:
===================================
Failed to open a new connection.
===================================
Failed to initialize MSDB database for tuning (exit code: -1). (DTAClient)
--
Program Location:
at
Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeTuningParametersDatabase()
at
Microsoft.SqlServer.Management.DTA.Client.TuningServer.InitializeConnection()
at Microsoft.SqlServer.Management.DTA.Client.TuningServer.Connect()
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServerInternal(SqlConnectionInfo
connectionInfo, SqlConnection connection)
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.AddServer(SqlConnectionInfo
connectionInfo, SqlConnection connection)
at
Microsoft.SqlServer.Management.DTA.Shell.SessionMonitor.OnConnect(Object
sender, EventArgs e)
===================================
Failed to establish a connection to the server.With regard to the "Database Engine Tuning Wizard: "Failed to initializ
MSDB database for tuning (exit code: -1). (DTAClient)" error, I just ra
into this myself with a fresh install of MS SQL Server 2005 + SP1.
I ran SQL Profiler on another server, that was working, to see what wa
happening when firing up the Database Tuning Advisor, and found that i
was looking for the following in the MSDB database:
Tables:
'DTA_input',
'DTA_progress',
'DTA_output',
'DTA_tuninglog',
'DTA_reports_database',
'DTA_reports_partitionfunction',
'DTA_reports_partitionscheme',
'DTA_reports_table',
'DTA_reports_tableview',
'DTA_reports_query',
'DTA_reports_querytable',
'DTA_reports_querydatabase',
'DTA_reports_index',
'DTA_reports_queryindex',
'DTA_reports_column',
'DTA_reports_indexcolumn',
'DTA_reports_querycolumn'
Stored Procedures:
'sp_DTA_add_session',
'sp_DTA_delete_session',
'sp_DTA_help_session',
'sp_DTA_update_session',
'sp_DTA_get_tuninglog',
'sp_DTA_update_session',
'sp_DTA_get_session_report',
'sp_DTA_get_tuninglog',
'sp_DTA_get_session_tuning_results',
'sp_DTA_set_interactivestatus'
These objects were missing from the server that was failing, so
scripted them from the server that was working. Once I created th
objects in the MSDB database, Database Tuning Advisor now works.
I've attached a zip file with the SQL scripts
+---
|Filename: DTA_Objects.zip
|Download: http://unixadmintalk.com/attachment.php?attachmentid=2
+---
--
bkp_4
----
bkp_42's Profile: http://unixadmintalk.com/77
View this thread: http://unixadmintalk.com/showthread.php?t=18525sql

Database creation using VB.NET Code

Hello.

I think its silly question but i expect some solution .

Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.

Please, if somebody have some solution then guide me in detail.

Thanks in adv.If the database you need to create is a copy of some fixed database, you can create SSIS package, and then run it from VB.NET code.
If you just need to create a database from scratch, use SMO. See Books Online, and if you need help consult SMO/DMO forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1
|||

Hi,

I am trying to create a copy of some fixed database. I am wondering how creating a SSIS package will help.Is there any code for making this database replication?

Thank You!

|||

Partially it depends on exactly what your problem is. If you just want to take a database from one server and move it to another, why not issue a backup, copy, and restore using 3 SSIS tasks? You'll need to fix up any SQL Server users, but that could be scripted as well as adding any missing logins.

Another alternative if you can take the database offline, is to detach the database, copy the database files directly, and then reattach on both servers. Might be a bit faster but requires some down-time.

There's also a Transfer SQL Server Objects task if more approrpriate to your needs which might wrap most of the stuff up into one task.

If you just want to sync one database to another while they are both online, then hopefully your database is structured to do that or you'll have to truncate or delete the records from the tables in the destination and then create data flow tasks for each level of referential integrity to move the data.

You could also look at SQL Server replication or SQL Server Mirroring.

|||

Basically, what I am trying to do is within the same server, i want to create a copy of an existing database. This copy will be used for a different purpose.

|||

Backup and restore to a new name WITH MOVE sounds like the easiest. On a single server you could do it all in one SQL Task or break it into two, you won't have to fix users or add logins either.|||Is there any sample code where I can look at written in vb.net?

|||

Just standard .Net code. You'll likely have better luck over in the VB or C# forums for help with the code and in the Transact SQL forums for assistance with the SQL. Below is a fictionalized example. You might add a DROP DATABASE, you may want to kill off connections, etc. It would just be additional SQL in the code below. Change the CommandTimeout based on how long you expect it to restore (in seconds) and then maybe double it. Below is assuming it takes 30ish minutes to backup and restore.

Imports System.Text

Module Module1

Sub Main()

Using cn As New SqlClient.SqlConnection(My.Settings.Connection)

cn.Open()

Dim sql As New StringBuilder

sql.AppendLine("BACKUP DATABASE [myDatabase] TO DISK='C:\mypath\myfile.BAK';")

sql.AppendLine("RESTORE DATABASE [myDatabaseNEW] FROM DISK='C:\mypath\myfile.BAK WITH MOVE myDatabase_Data TO 'C:\mydatapath\myDatabaseNEW.MDF', MOVE myDatabase_Log TO 'C:\mydatapath\myDatabaseNEW.ldf'")

Using cmd As New SqlClient.SqlCommand(sql.ToString, cn)

cmd.CommandTimeout = 1800

cmd.ExecuteNonQuery()

End Using

End Using

End Sub

End Module

|||

Hi,

I get an error saying that 'myDatabase.mdf' cannot be overwritten because it us being used by database 'myDatabase'

|||

Your WITH MOVE isn't working, make sure the files listed are different than the files of the original database.

Such as myDatabaseNew.mdf not myDatabase.mdf.

|||

I found where the mistake was. Instead of using WITH MOVE 'myDatabase' I wrote it as WITH MOVE 'myDatabaseNew'. This was the reason for the error.

Its working well now.

Thanks !

|||Just an alternate suggestion - there is a Transfer Database task in SSIS that automates much of this.

Database creation using VB.NET Code

Hello.

I think its silly question but i expect some solution .

Is there any way to create sql sever database by using VB.NET code ? I have to create database,tables,sps and also transfer the data from one database to newly created database.

Please, if somebody have some solution then guide me in detail.

Thanks in adv.
If the database you need to create is a copy of some fixed database, you can create SSIS package, and then run it from VB.NET code.
If you just need to create a database from scratch, use SMO. See Books Online, and if you need help consult SMO/DMO forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=88&SiteID=1
|||

Hi,

I am trying to create a copy of some fixed database. I am wondering how creating a SSIS package will help.Is there any code for making this database replication?

Thank You!

|||

Partially it depends on exactly what your problem is. If you just want to take a database from one server and move it to another, why not issue a backup, copy, and restore using 3 SSIS tasks? You'll need to fix up any SQL Server users, but that could be scripted as well as adding any missing logins.

Another alternative if you can take the database offline, is to detach the database, copy the database files directly, and then reattach on both servers. Might be a bit faster but requires some down-time.

There's also a Transfer SQL Server Objects task if more approrpriate to your needs which might wrap most of the stuff up into one task.

If you just want to sync one database to another while they are both online, then hopefully your database is structured to do that or you'll have to truncate or delete the records from the tables in the destination and then create data flow tasks for each level of referential integrity to move the data.

You could also look at SQL Server replication or SQL Server Mirroring.

|||

Basically, what I am trying to do is within the same server, i want to create a copy of an existing database. This copy will be used for a different purpose.

|||

Backup and restore to a new name WITH MOVE sounds like the easiest. On a single server you could do it all in one SQL Task or break it into two, you won't have to fix users or add logins either.|||Is there any sample code where I can look at written in vb.net?

|||

Just standard .Net code. You'll likely have better luck over in the VB or C# forums for help with the code and in the Transact SQL forums for assistance with the SQL. Below is a fictionalized example. You might add a DROP DATABASE, you may want to kill off connections, etc. It would just be additional SQL in the code below. Change the CommandTimeout based on how long you expect it to restore (in seconds) and then maybe double it. Below is assuming it takes 30ish minutes to backup and restore.

Imports System.Text

Module Module1

Sub Main()

Using cn As New SqlClient.SqlConnection(My.Settings.Connection)

cn.Open()

Dim sql As New StringBuilder

sql.AppendLine("BACKUP DATABASE [myDatabase] TO DISK='C:\mypath\myfile.BAK';")

sql.AppendLine("RESTORE DATABASE [myDatabaseNEW] FROM DISK='C:\mypath\myfile.BAK WITH MOVE myDatabase_Data TO 'C:\mydatapath\myDatabaseNEW.MDF', MOVE myDatabase_Log TO 'C:\mydatapath\myDatabaseNEW.ldf'")

Using cmd As New SqlClient.SqlCommand(sql.ToString, cn)

cmd.CommandTimeout = 1800

cmd.ExecuteNonQuery()

End Using

End Using

End Sub

End Module

|||

Hi,

I get an error saying that 'myDatabase.mdf' cannot be overwritten because it us being used by database 'myDatabase'

|||

Your WITH MOVE isn't working, make sure the files listed are different than the files of the original database.

Such as myDatabaseNew.mdf not myDatabase.mdf.

|||

I found where the mistake was. Instead of using WITH MOVE 'myDatabase' I wrote it as WITH MOVE 'myDatabaseNew'. This was the reason for the error.

Its working well now.

Thanks !

|||Just an alternate suggestion - there is a Transfer Database task in SSIS that automates much of this.