An internal error occurred. [ ID = 3639 ] on delete query

Hi,

When I am trying to execute a delete query i'm getting the following error An internal error occurred. [ ID = 3639 ]. This is happening for some specific rows only. Other rows are getting deleted without any issues. Some 7 rows are there which i'm not able to delete. I am able to run update query on that rows. but not delete query

Pls help

Thanks

Nebu

This error does not look like a SQL CE error. Are you using SQL CE or SQL Server ?

Also, could you show us the statement and describe your environment, then others may be able to help you.

|||I'm using SQL CE.

delete from tabl1 where ID=1

We are using SLQ ce as a databse for a windows application. We use C# . Some times there will be insert and delete statements of more than 10000. I executed this statement for more than 10000 records, in that apart from 7 records every thing else got deleted. I'm not able to delete it even from SQL server management studio expresss.

tahnks

Nebu

|||Sounds like your sdf file has become corrupted. Try to run a Compact/Repair on the db. You can also try to send the sdf file to me, and I will have a look at it with SSMS.

|||

Hi Erik,

Thanks for the reply. I will not able to send you the sdf bcos of confidentiality. Can you please tell me how to run a reapir?

Thanks

Nebu

|||

Repair can be done from SSMS(E), right click the database, select Properties and the Shrink/Repair tab.

Also, the SqlCeEngine object has a Repair method.

|||Thanks Eirk.. It worked.. Do you have any idea why did it happen? Is it

because of the no of transactions?

Thanks

Nebu

An INSERT EXEC statement cannot be nested.

I try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;

declare @.dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @.dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'

Select doccode, docdate, debtoraccount
From @.dtReturnData

Inside the GetPickList It will do like this, but most of the code I not included;

ALTER PROCEDURE GETPICKINGLIST
@.doctype nvarchar(2),
@.datefrom datetime,
@.dateto datetime,
@.includegrn char(1),
@.includesa char(1),
@.includedata nvarchar(5)
AS
BEGIN
declare @.dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))

IF (@.DOCTYPE = 'SI')
BEGIN
Insert Into @.dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
ELSE
BEGIN
Insert Into @.dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @.DateFrom, @.DateTo, @.IncludeGRN, @.IncludeSA, @.IncludeData
END
Select doccode,docdate,debtoraccount From @.dtReturnData

END

So how can I select a nested store procedure? can someone help me

Jeremy,

This is a problem that comes up from time to time in SQL Server. My first suggestion when this comes up is to look at both points in which you are using the INSERT ... EXEC syntax. See if it is possible to convert at least one of the procedures into a user defined function. There is a "Plan C" for this but it is not nearly as clean as the option of converting to a function (if possible).

Also, for future keep in mind that it is a good idea to consider using functions -- especially inline functions -- instead of stored procedure when it is the intent to load the output from a procedure into a table.

See if SalesInvoiceRegister and DeliveryOrderRegister can be converted to functions.

Kent

AmoAdventureWorks sample problems.

Can execute the above sample (on XP Pro SP2) successfully once. Subsequent attempts result in error below(after grabbing all system resources for a few minutes). Tried deleting the database and then stop starting the server before retrying - to no avail.

Kind of sapping my confidence in AMO objects.

Any ideas?

Thanks in advance

Zub

--

Unhandled Exception: System.Data.OleDb.OleDbException: Deferred prepare could no

t be completed.

Query timeout expired

at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)

at System.Data.OleDb.OleDbDataReader.BuildSchemaTableRowset(Object handle)

at System.Data.OleDb.OleDbDataReader.GenerateSchemaTable(OleDbDataReader data

Reader, Object handle, CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behav

ior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(Comman

dBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataT

able datatable, SchemaType schemaType, IDbCommand command, String srcTable, Comm

andBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType sc

hemaType, IDbCommand command, String srcTable, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType sc

hemaType, String srcTable)

at Microsoft.Samples.SqlServer.Program.AddTable(DataSourceView dsv, OleDbConn

ection connection, String tableName) in AmoAdventure

Works\Program.cs:line 239

at Microsoft.Samples.SqlServer.Program.CreateDataSourceView(Database db) in AmoAdventureWorks\Program.cs:line 211

at Microsoft.Samples.SqlServer.Program.CreateAndProcessDatabase(Server svr) in AmoAdventureWorks\Program.cs:line 72

at Microsoft.Samples.SqlServer.Program.Main() in

AmoAdventureWorks\Program.cs:line 48

The sample application assumes you have installed SQL Server on the same machine you have your Analysis Server.

And you have attached sample AdventureWorksDW SQL database. Search for "connectionString" in your project and you be able to point it to any SQL Server you have AdventureWorksDW database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward I'm not sure what you are suggesting.

I have SQL Server Anaylsis Server and AMOAdventureWorks.exe all running on the same machine.

The point is that it works once. The AS database is created, cubes defined and populated. The problem is that subsequent exceutions fail.

Thanks

Zub

|||

The error message suggests problems with connecting to relational database.

AMOAdventureWorks creates a AS database and populates it with data from SQL server you installed locally. Is it possible that SQL server was unavalible?

I've tried to run AMOAdventureWorks several times. And it worked without a problem.

Try running it in the debugger and see exactly where it fails.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward

Thanks for the response.

OK I'm debugging.

Rebooted machine. Ran the program once - it succeeded.

Second time I get a IndexOutofRangeException on the line

DataTable dataTable = dataTables[0];

in

static void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName)

{

OleDbDataAdapter adapter = new OleDbDataAdapter(

"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",

connection);

DataTable[] dataTables = adapter.FillSchema(dsv.Schema,

SchemaType.Mapped, tableName);

DataTable dataTable = dataTables[0];

......

when called from

AddTable(dsv, connection, "DimCustomer");

rgds

Zub

|||

Couple things I notice in the code.

1) It works the first time but not the second time. Does your table name exist in DB when you pass in second time?

2) If you pass same table name on second time, it does not make sense because DimCustomer is already in dsv.Schema after you call it first time. This is redundent.

Try replacing with following lines and see if you can get the table everytime you call.

DataSet dataSet = new DataSet();

DataTable[] dataTables = adapter.FillSchema(dataSet,

SchemaType.Mapped, tableName);

3) Anyway, you don't use any AMO object except in the line of doing FillSchema. If you still have problem as my suggestion in Step 2, then it must be the connection problem. Check if the table exists in the DB and valid permission to access that table as well.

|||

Sorry I just notice one more thing.

You got Query time out error on second time. Is your table a big table or a complicated view when you call second time? It seems that it took a lot of time to execute the statement. You may want to increase the connection time out in your connection or set it to unlimited.

Also, you can try the same SQL Statement "Select * from [dbo].[DimCustomer] where 1=0" as New query in SQL Management studio. See how long it takes to get the result.

am I reading this right?

When I see this in my Maintenance Plan job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
C8270137-357F-4781-BFB8-CF86BE6A80B5 -WriteHistory -RebldIdx 10 '
I think that means a Fill Factor of 90%, leaving 10% free. Is that correct?
It could mean a FF of 10, but thats not the way Im reading it.
--
TIA,
ChrisRYes That is a fillfactor of 90%
http://sqlservercode.blogspot.com/
"ChrisR" wrote:
> When I see this in my Maintenance Plan job:
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> C8270137-357F-4781-BFB8-CF86BE6A80B5 -WriteHistory -RebldIdx 10 '
> I think that means a Fill Factor of 90%, leaving 10% free. Is that correct?
> It could mean a FF of 10, but thats not the way Im reading it.
> --
> TIA,
> ChrisR