Microsoft JET Databse Engine Error Message

Hi,
When I try to import DBF files into the SQL Server,
I got the following error messages for some files.

Error Source: Microsoft JET Databse Engine
Error Destination: External table is not in the expected
format
Context: Error calling OpenRowset on the provider.

Does anyone know about this error?

I found that only files that have *.FPT (memo) files didn't work
and other files were imported successfully/

I installed sql server service pack and I checked that
the machine has the latest JET Database engine (4.0).

I'm using Microsoft SQL Server 2000 Standard Edition
w/ Service Pack 2 & Microsoft FoxPro 2.6.

Thank you in advance.

- Hyung -Did anyone ever answer this?sql

messages stuck in sys.transmission_queue

Sorry for the stupid question, but I can't seem to figure it out...

There are 119 messages that are stuck in the transmission queue, all for the same queue. When I check the status of the queue (via sys.service_queues), is_receive_enabled = 1, is_activation_enabled = 1, and max_readers = 3. When I check to see if there is an active queue monitor (via sys.dm_broker_queue_monitors) there is nobody watching this queue. What would cause this queue to be active, enabled but have nobody montioring it? Is there something internal that went wrong that made these (outbound) messages get stuck in the transmission queue, and is not showing up in the views? How can I get these messages "un-stuck" and flow through the system?

A problem I am seeing is the return message to this queue (to signify the target has consumed the message, and to end the conversation) are not being consumed, thus getting stuck in the "DI" state.

Any suggestions would be greatly appreciated.

Thanks in advance,

John Hennesey

p.s. The transmission status is blank for all 119 rows.|||

Are all messages in transmission queue on the same dialog or on different dialogs?
Are the messages remote or local (if local, is it same instance or same db) ?
Attaching the profiler and monitoring for events in the Broker category shows any activity?
Is there any route for the messages destination service in the database?
Do new messages sent end up in the same situation or they get trough fine?

To trigger an internal 'recycle' of the whole broker 'machinery' you can disable and then enable back the broker (ALTER DATABASE ... SET DISABLE_BROKER / ENABLE_BROKER), but I'd like you to try the profiler first to confirm if there realy isn't any broker activity for those messages.

HTH,
~ Remus

|||

Remus - thank you so much for your quick response. I was hoping you would see this... :) To answer your questions:

Are all messages in transmission queue on the same dialog or on different dialogs?

When you say dialog, do you mean the same conversation? The conversation handles are indeed different.|||

I am puzzled myself. My recommendation would be to focus on one individual conversation that exposes the problem. Lookup the conversation_handle in sys.transmission_queue for any of the messages stuck (pick one). Starthing from this, investigate as follows:
- find the corresponding conversation (that owns the message) in sys.conversation_handles
- using the conversation_id, find the peer conversation handle (initiator and target both share the same conversation_id value)
- what states are the two conversation endpoints found in the previous step? For messages to travel, both should be in CONVERSING state.
- check if there are discrepancies between the send_sequence and receive_sequence between the two conversation endpoints. In each direction (initiator to target and target to initiator) there should be a contigous sequence of message numbers: i.e. if initiator's send_sequence is 10 and target's receive_sequence is 5, the messages numbered 5,6..10 should all be in the transmission_queue. See if you can spot any gap (e.g. send_sequence 10, peer's receive_sequence is 9, but message 10 is missing from transmission_queue) or overlap (e.g. send_sequence 10, receive_sequence also 10 but message 10 was not yet deleted from transmission_queue)
- monitor again the Profiler broker events, but filter the events only for the conversation you're focusing on. The meaning of each column displayed in the Profiler for broker events is documented here: http://msdn2.microsoft.com/en-us/library/ms186347.aspx and you can filter based on a given conversation_id (the one you're focusing on).

HTH,
~ Remus

|||

Remus - thank you very much for the response. Today we had to deactivate our queues (for maintenance purposes), and I jumped at the chance to also deactivate the queue in question. Turns out we have a script that deactivates the queues every morning, kicks off a cube processing event (so it will be built on static data), then reactivates the queues. I didn't know it, but this queue is not part of that script. I think this script was the culprit - somehow when things were processing, it severed something, somewhere.

It made it look like the queue was enabled and active, all signs pointed to everything alive and well, but nothing was monitoring the queue (via sys.dm_broker_queue_monitors). We deactivated the queue, disabled the queue, enabled the queue and reactivated the queue and everything started flowing smoothly. Queue counts are going down, our conversations are closing properly and the sys.conversation_endpoints view is showing CD conversations being purged after the 30 minute period. Awesome!

I still don't know exactly what caused it, nor did I get the chance to really dive into your info from the last post, but some of the thing you mentioned got me thinking down this path. I really appreciate your help!

If there are any questions you have from me, please do not hesitate to ask - i.e. if the product group is interested in some of the processes we do that may have caused the queue to be in this state. Otherwise, I will mark this as answer.

Thanks again - have a great weekend.

John

|||

My recommendation would be to describe this problem at http://connect.microsoft.com/SQLServer/Feedback

Thanks,
~ Remus

Messages stuck in sys.transmission_queue

Hi,

At my company, we're trying to use service broker to create a client-server system where there is a head office machine and multiple outlets registered with that head office. My problem is that sometimes when a branch sends a message to the head office, it just seems to sit in the transmission queue and never gets sent. If I run a script that forcibly ends the conversations on the client machine (with cleanup), storing the message bodies and then resend them, they seem to get through fine.

The way that we send messages is by calling a t-sql stored procedure from a c# application using SqlCommand (don't know if this should make any difference).

If I monitor the Head Office machine and one of the Outlets while this is happening, on the HO I get three events in a row:

Broker: Message Classify (1 - Local) Audit Broker Conversation (2 - No Certificate) Broker: Message Undeliverable (1 - Sequenced Message)
The TextData contained in the third event is: This message could not be delivered because the security context could not be retrieved.

The RoleName of the server is Initiator, and the TargetUserName is the name of the service on the Outlet.

On the Outlet I get the following event repeatedly (presumably as it continues to try sending the message) - Broker: Remote Message Acknowledgement (1 - Message With Acknowledgement Sent).

On the client the RoleName also appears to be Initiator, and the TargetUserName is blank.

This would make me suspect that certificates were missing or something, except that if I remove messages from the queue and resend them they seem to get through, and also I've checked both databases and they have the correct certificates.

Any ideas?

Thanks in advance,

AdamThanks for the detailed info, it is always helpfull to have the full details when trying to diagnose an issue.
The most likely culprit in such cases is an account that has more certificates to be picked from when encrypting/signing a message. Whenever SSB has more than one certificates 'valid' to be used for a particular database principal, it will pick the one with the latest expiration date. So sometimes even though the configuration 'seems' correct, SSB will pick the 'wrong' certificate simply because it has multiple choices. The typical account prone to this problems is 'dbo', as certs are being created for dbo for various reasons and by default they are all valid for SSB to pick.
To confirm this problem, the 2nd event in your post (the Audit Broker Conversation one) actually tells which certificate was not found: it has the issuer name and serial number of the cert 'not found' on two of the columns (I can't remember now which ones and the BOL 'ommits' this detail). You can then look into the message sender's database to see why that particular cert was picked.
Certs can actualy be controled whether they are available for SSB to 'pick' by turning off the ACTIVE_FOR_BEGIN_DIALOG option on them (the option refers to certs used for the reverse path from target to initiator as well, despite the name 'begin_dalog'...)|||Thanks for the reply. What you're saying makes sense, and seems possible to me (we re-registered one of the outlets recently which could have caused this). The only problem is that the Audit Broker Conversation event doesn't appear to have the issuer or serial number in any of the columns. Maybe I'm using profiler wrong or something, but I would have thought that just selecting the event would have been enough. Any idea what I'm doing wrong?

Adam

|||

IssuerName is DBUserName and SerialNumber is in TargetLoginName.

Make sure the columns above are selected when defining the trace.

|||I had worked out that the IssuerName was DBUserName, but hadn't noticed the serial number. Thanks for explaining that. The thing is that the IssuerName isn't much use to me because the way I generated the certificates, they all have the same name .

But anyhow, I found a solution to the problem. Basically we had an old database on the same server that had broker enabled as well, and I'm guessing that since they both have the same service names in them, that was causing the problems. I disabled service broker on the old db and everything seems to be working again. The only thing I'm worried about now is how the broker became enabled on the database - but that's not really a service broker issue.

Thanks for the help.

Adam
|||

Whenever SSB finds multiple instances of the same service it assumes a load balancing scenario and will hash the incomming conversations to the available service instances. Specifying a broker_instance in the begin dialog from the initiator would make the application stick to a specific service (database), even if more instances of the service show up on the target.

Messages stopped appearing in Queue!

I recently restored my database from a backup and it seems that something in my service broker setup has stopped working, no messages now appear.

I checked that the broker was enabled (it wasnt and i had to use NEW_BROKER because of the 'same id' message)

The database is still set to trustyworthy.

There are also no messages in sys.transmission_queue

I used profiler with all the broker events enabled and the result was as follows:

Broker:Conversation Group
Broker:Conversation STARTED_OUTBOUND

Broker:Conversation CONVERSING

Broker:Message Classify
Broker:Conversation CLOSED
Broker:Conversation Group
Broker:Message Classify

But i'm not sure whether this highlights a problem or not? Any help gratefully appreciated!!

Thanks,

Andy

Hi,

I had the complete conversation - including the end of the conversation - in a stored procedure.

This was the reason i wasn't getting anything in sys.transmission_queue - i didnt end the conversation and the error appeared.

It turned out to be a permissions thing as when i'd restored the database it had set the owner to [SA]

Sorry for wasting your time if you'd bothered to read my problem!

Cheers,

Andy

Messages stopped appearing in Queue!

I recently restored my database from a backup and it seems that something in my service broker setup has stopped working, no messages now appear.

I checked that the broker was enabled (it wasnt and i had to use NEW_BROKER because of the 'same id' message)

The database is still set to trustyworthy.

There are also no messages in sys.transmission_queue

I used profiler with all the broker events enabled and the result was as follows:

Broker:Conversation Group
Broker:Conversation STARTED_OUTBOUND

Broker:Conversation CONVERSING

Broker:Message Classify
Broker:Conversation CLOSED
Broker:Conversation Group
Broker:Message Classify

But i'm not sure whether this highlights a problem or not? Any help gratefully appreciated!!

Thanks,

Andy

Hi,

I had the complete conversation - including the end of the conversation - in a stored procedure.

This was the reason i wasn't getting anything in sys.transmission_queue - i didnt end the conversation and the error appeared.

It turned out to be a permissions thing as when i'd restored the database it had set the owner to [SA]

Sorry for wasting your time if you'd bothered to read my problem!

Cheers,

Andy

Messages in the sys.transmission_queue for a disable queue

I may have a misunderstanding of how SB works, but this seems like a problem.

If a queue is disable (i.e. status = off) and a message is sent to the queue the message is placed on the sys.transmission_queue. Once the queue is enabled I thought the messages were sent to the queue in the order they were placed on the sys.tranmission_queue? I have been troubleshooting a problem and this is not the case. Do I have a misunderstanding of how the sys.transmission_queue works?

The queue has retention turned off.

Messages are delivered in order within each conversation (RECEIVE cannot return msg 2 before msg 1 for any conversation). But the order of messages in the xmit queue does not determine in any way the order of which messages are actually being delivered.

HTH,
~ Remus

|||

If you have a scenario where you want to send a message to a service broker queue that updates a row in a table and then later you would like to send another message to do another update to the row in the same table and the queue is disabled before the first send there is no way to guarantee these messages will be processed in order they were sent? It sounds like unless you actually end up on the queue itself there is no gurantee in what order the xmit queue will deliver them to the queue. Am I correct?

Thanks for the help

|||

If the order matters, you must deliver them using the same conversation. Otherwise you are never quaranteed anything about the order and you can hit the xmit queue for other reasons that target queue disabled (low memory, a lock bounce etc)

messages in sqlsgent.out (sql server 2000)

I am getting following messages in sqlagent.out. Can anyone help me understand what does these mean.. and what can i do for them?

2006-11-17 15:45:00 - + [235] Job CR QUEUE ON LINE - [HU][10720]20061117 15:44:51 is being deleted (job has delete level 1)
2006-11-20 09:33:20 - + [235] Job CR QUEUE ON LINE - [WM][3293]20061120 09:33:09 is being deleted (job has delete level 1)
2006-11-20 11:24:37 - + [235] Job CR QUEUE ON LINE - [LA][541]20061120 11:24:26 is being deleted (job has delete level 1)
2006-11-20 11:25:24 - + [235] Job CR QUEUE ON LINE - [LA][542]20061120 11:25:16 is being deleted (job has delete level 1)
2006-11-20 11:35:37 - + [235] Job CR QUEUE ON LINE - [HU][10721]20061120 11:35:27 is being deleted (job has delete level 1)
2006-11-20 12:04:22 - + [235] Job CR QUEUE ON LINE - [LB][3920]20061120 12:04:12 is being deleted (job has delete level 1)
2006-11-20 13:30:46 - + [235] Job CR QUEUE ON LINE - [PD][2594]20061120 13:30:35 is being deleted (job has delete level 1)
2006-11-20 15:44:43 - + [235] Job CR QUEUE ON LINE - [LB][3921]20061120 15:44:32 is being deleted (job has delete level 1)
2006-11-21 08:57:46 - + [235] Job CR QUEUE ON LINE - [HU][10722]20061121 08:57:29 is being deleted (job has delete level 1)
2006-11-21 09:00:14 - + [235] Job CR QUEUE ON LINE - [HU][10724]20061121 09:00:05 is being deleted (job has delete level 1)
2006-11-21 09:04:43 - + [235] Job CR QUEUE ON LINE - [HU][10725]20061121 09:04:34 is being deleted (job has delete level 1)What does the CR QUEUE ON LINE job do?

Messages back from SqlServer while a Backup is being completed?

I have a WinForms application in which I am wrapping the backing up of a sql
database. In order to back up the database I am using the BACKUP DATABASE
t-sql and the STATS=5 in order to get the progress back from sql server.
Here is the exact script:
BACKUP DATABASE test_dvpt
TO test_Backup
WITH INIT,
STATS=5
Does anyone know how I can get the messages like "5 percent backed up" ...
"10 percent backed up" to my win forms application in order to update a
progress bar?You can handle the connection object InfoMessage event to get these progress
messages.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jason Haley" <JasonHaley@.discussions.microsoft.com> wrote in message
news:3D7A28DB-F43E-4A3E-8909-14363404A177@.microsoft.com...
>I have a WinForms application in which I am wrapping the backing up of a
>sql
> database. In order to back up the database I am using the BACKUP DATABASE
> t-sql and the STATS=5 in order to get the progress back from sql server.
> Here is the exact script:
> BACKUP DATABASE test_dvpt
> TO test_Backup
> WITH INIT,
> STATS=5
> Does anyone know how I can get the messages like "5 percent backed up" ...
> "10 percent backed up" to my win forms application in order to update a
> progress bar?

messages aren't getting through after backup / restore on a different server...

after restoring a database and regenerating keys, I'm trying to queue messages, and they're all hanging out in sys.transmission_queue with the following message:
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I can't find any documentation or blog info on this error... Help!

thanks!Have you set your database to trustworty?

ALTER DATABASE db_name
SET TRUSTWORTHY ON

Also, maje sure you have a master key in both databases.

CREATE DATABASE MASTER KEY
ENCRYPTION BY PASSWORD = 'somePassW0rd1'

Please let us know if this helps

Niels|||hi Niels.

Nope, the SET TRUSTWORTHY ON on the database did not work.

It would probably be helpful if I mentioned that the backup was taken on the April CTP server, and the restore on the September version...

also - both the initiator and target queues live in the same database...

|||

Which server principal (i.e. login) owns the restored database? Has anything changed with the SQL instance or Windows users since backup was taken? For example you are trying to restore on a machine not connected to a domain and the server principal owning the database was a domain user?

Later,
Rushi

|||Rushi -- a domain administrator owns both databases.
Both servers are connected to the domain, and all services are also run by the domain administrator.|||Probably the dbo of the database is the original login that created the database, on the original server, and that login cannot be impersonated on the new server (e.g. the login is the Windows login corresponding to the original server Administrator account).
Use ALTER AUTHORIZATION ON DATABASE <dbname> TO <loginname> to change the owner of the database to a new login (e.g. [sa]), it will change the dbo to map to a login than is OK to be impersonated.

HTH,
~ Remus|||thanks Remus -- that solved the problem.

Message Queue Task

Simple Question.

I have a requirement to read XML Messages from a Remote private MSMQ.

These messages are essentially Database records that will need cleaning up and insterting into a Local Database table.

Is this possible with SSIS?

Would Biztalk be a more suitable tool for this type of process?

If its possible, are there any resources taht can point me in the right direction?

Thanks for your help!

J.

That is what the Message Queue task is for, so it seems possible, though I don't have personal experience with using it to read a Remote private queue.

message could not be delivered errors in service broker

Hi,

I am using service broker in between two database servers. following is the way i am sending and receiving messages

Send

BEGIN TRAN
BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
WITH LIFETIME = @.lifetime;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);
COMMIT

Receive

BEGIN TRAN;
RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;

-
-- Check to make sure a message was returned to process. In theory this should never happen.
-
IF @.@.rowcount = 1
BEGIN

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
COMMIT
RETURN 0
END

IF @.message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR(N'Received error %s from service [Target]', 10, 1)
END CONVERSATION @.handle;
COMMIT
RETURN 0
END

SET @.sql = 'EXEC '+@.message_processor_name+' @.xml'

BEGIN TRAN
EXEC sp_executesql @.sql, N'@.xml XML', @.xml=@.xmlMessage
COMMIT TRAN
END CONVERSATION @.handle;
END
COMMIT

I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.

1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.

2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.

Let me know if you need any other information

---

Please let me know if I have missed anything in above post|||

Just wondering if you resolved this issue as I am having the same issues with very similar code.

If you have resolved this, could you share what you did please so that I can (hopefully) resolve my problems too?

Thanks in advance.

message could not be delivered errors in service broker

Hi,

I am using service broker in between two database servers. following is the way i am sending and receiving messages

Send

BEGIN TRAN
BEGIN DIALOG CONVERSATION @.handle
FROM SERVICE @.SendService
TO SERVICE @.ReceiveService
ON CONTRACT @.Contract
WITH LIFETIME = @.lifetime;

SEND ON CONVERSATION @.handle
MESSAGE TYPE @.xmlMessageType(@.xmlMessage);
COMMIT

Receive

BEGIN TRAN;
RECEIVE TOP(1) @.xmlMessage = message_body,
@.handle = conversation_handle,
@.message_type = message_type_name
FROM TransactionQueue;

-
-- Check to make sure a message was returned to process. In theory this should never happen.
-
IF @.@.rowcount = 1
BEGIN

IF @.message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @.handle;
COMMIT
RETURN 0
END

IF @.message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
BEGIN
RAISERROR(N'Received error %s from service [Target]', 10, 1)
END CONVERSATION @.handle;
COMMIT
RETURN 0
END

SET @.sql = 'EXEC '+@.message_processor_name+' @.xml'

BEGIN TRAN
EXEC sp_executesql @.sql, N'@.xml XML', @.xml=@.xmlMessage
COMMIT TRAN
END CONVERSATION @.handle;
END
COMMIT

I see Messages are delivered to the target every thing working fine other than following errors which i am seeing in profiler.

1) "This message could not be delivered because the conversation endpoint has already been closed." I see this error on initiator end. Is it like ending conversation on initiator end when i get "EndDialog" send an acknowledgement, which cannot be recieved by target as it has already ended conversation.

2) "An error occurred while receiving data: '64(The specified network name is no longer available.)'." I don't have much idea about the reason for this error. But in profiler i see value for GUID is different for this error and the real message.

Let me know if you need any other information

---

Please let me know if I have missed anything in above post|||

Just wondering if you resolved this issue as I am having the same issues with very similar code.

If you have resolved this, could you share what you did please so that I can (hopefully) resolve my problems too?

Thanks in advance.

Message compression

We have run some tests on our application. Average message is about 2.5 MB. Messages are send once every 30 minutes. This is 3.5 Gb per month for one site. Now we already have three sites that will be sending this messages. This will be VERY high load on the WAN channel, and will cost us a LOT of money .
Isn't it possible to add some compression? XML should compress very well, this feature will make brokers more useful.Service Broker messages are not required to be XML. They can simply be blobs. So you could perform the compression and decompression fairly easily in your app and send compressed messages through the Broker. That way you control the compression algorithms and not the system.|||

Some tips for reducing the traffic when using Service Broker:
- use binary messages instead of XML
- compress the message payload in the application, before calling SEND
- use one long lived dialog to send all messages, don't start a new dialog for every message
- few large messages is better than many small messages
- use short names for services, contracts, message types

I'd add that Service Broker is quite unchaty. For instance, there is no handshake for dialogs: first message SEND on the dialog is the first message sent on the wire.

HTH,
~ Remus