Merging two dbs: February 2012

Saturday, February 25, 2012

Messed up SQL 2000 - Error Code 193

Somehow my SQL Server 2000 installation got messed up and refuses to run. I
narrowed it down, I believe, to a problem of the DLL drivers for SQL Server
being out of sync with the location the Registry had for them.
When I now attempt to create a SQL Server ODBC connection, I get:
"The setup routines for the ODBC Driver could not be loaded due to System
Error Code 193." I then get a message about the translator files failing.
I tried installing the latest MDAC, but it says my operating system already
has MDAC and won't install them.
Anyone ever see this...and have any ideas on fixing this and getting my SQL
Server running again?
Much thanks,
B. Rosman
Chicago, ILRosman Computing said (on or about) 12/10/2006 03:28:
> Somehow my SQL Server 2000 installation got messed up and refuses to run.
I
> narrowed it down, I believe, to a problem of the DLL drivers for SQL Serve
r
> being out of sync with the location the Registry had for them.
> When I now attempt to create a SQL Server ODBC connection, I get:
> "The setup routines for the ODBC Driver could not be loaded due to System
> Error Code 193." I then get a message about the translator files failing.
> I tried installing the latest MDAC, but it says my operating system alread
y
> has MDAC and won't install them.
> Anyone ever see this...and have any ideas on fixing this and getting my SQ
L
> Server running again?
> Much thanks,
> B. Rosman
> Chicago, IL
I ran into a similar problem with the ODBC Setup. In my case the SQL
Server was not affected because it was running on a server and the
ODBC Setup problem occurred on the client.
As I recall, the situation came about because the client PC had a
newer version of MDAC on it than the one on the SQL Server
installation disc. When I used the SQL Server installation disc to
install the SQL Server tools on the client, the ODBC Setup programs
went out of sync and when I tried to either create a new ODBC
connection (or modify an existing one) I got an error about not being
able to load the setup routines.
I was never able to resolve the problem. Since I only had one
application that needed to access SQL Server and it already had a
valid connection, it wasn't a big problem. When I needed to make a
second connection to a different db (test instance), I just looked at
the various registry settings for the existing one and duplicated them
for the 2nd. I no longer have the problem because that client PC was
recycled and I made sure that the SQL Server tools were installed on
the new one before I did anything like upgrading MDAC.|||I was able to fix it anyway. I installed the SQL Server 2000 Server Pack, an
d
that automatically installed the SQLSRV32.DLL into the system directory. It
worked after doing that.
Thanks,
Bill r.
"DGA" wrote:

> Rosman Computing said (on or about) 12/10/2006 03:28:
> I ran into a similar problem with the ODBC Setup. In my case the SQL
> Server was not affected because it was running on a server and the
> ODBC Setup problem occurred on the client.
> As I recall, the situation came about because the client PC had a
> newer version of MDAC on it than the one on the SQL Server
> installation disc. When I used the SQL Server installation disc to
> install the SQL Server tools on the client, the ODBC Setup programs
> went out of sync and when I tried to either create a new ODBC
> connection (or modify an existing one) I got an error about not being
> able to load the setup routines.
> I was never able to resolve the problem. Since I only had one
> application that needed to access SQL Server and it already had a
> valid connection, it wasn't a big problem. When I needed to make a
> second connection to a different db (test instance), I just looked at
> the various registry settings for the existing one and duplicated them
> for the 2nd. I no longer have the problem because that client PC was
> recycled and I made sure that the SQL Server tools were installed on
> the new one before I did anything like upgrading MDAC.
>

messed up password

I changed the password for the reporting service account on the AD server,
now I can't access my reports. I keep getting logon failed, and every 5
seconds I get logon failures on teh sql server. If I stop the service on
the sql server, the errors go away. I changed the password for the service,
which did not help. Where can I fix this?
Thanks,
Robert ElderThis is a multi-part message in MIME format.
--=_NextPart_000_00DC_01C50F79.58A0A670
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Have you tried this...?
To change Reporting Services access to DB after password changed for =Windows Authentication:
At a Command Prompt, run:
rsconfig -c -u <<The RS UserID>> -p <<The RS User's Password>> -s =<<The RS Server Name>> - d ReportServer -a windows
Should get:
The command completed successfully
BTW: "-d ReportServer" is the name of the database in SQL Server. "-a =windows" says "Windows Authentication"
If you need help on rsconfig, type "rsconfig -h" at the command =prompt.
"Robert Elder" <bobe@.realtechllc.com> wrote in message =news:Oe$c5twDFHA.392@.TK2MSFTNGP14.phx.gbl...
> I changed the password for the reporting service account on the AD =server, > now I can't access my reports. I keep getting logon failed, and every =5 > seconds I get logon failures on teh sql server. If I stop the service =on > the sql server, the errors go away. I changed the password for the =service, > which did not help. Where can I fix this?
> > Thanks,
> Robert Elder > >
--=_NextPart_000_00DC_01C50F79.58A0A670
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Have you tried this...?
To change Reporting Services access to DB after =password changed for Windows Authentication:
At a Command Prompt, run:
rsconfig -c -u <> -p <> -s <> - d ReportServer -a windows
Should get:
The command completed successfully
BTW: "-d ReportServer" is the name of =the database in SQL Server. "-a windows" says "Windows =Authentication"
If you need help on rsconfig, type ="rsconfig -h" at the command prompt.
"Robert Elder" =wrote in message news:Oe$c5twDFHA.392@.TK2MSFTNGP14.phx.gbl...> I =changed the password for the reporting service account on the AD server, > =now I can't access my reports. I keep getting logon failed, and every 5 => seconds I get logon failures on teh sql server. If I stop the =service on > the sql server, the errors go away. I changed the =password for the service, > which did not help. Where can I fix =this?> > Thanks,> Robert Elder > > =

--=_NextPart_000_00DC_01C50F79.58A0A670--

messages.sql error during upgrade

I found the solution to this on Microsoft's site.
http://support.microsoft.com/?scid=kb;en-us;Q300676
But I can't download the file s80305i.exe , can anyone send me this file
please ?
Thank you,
FulyaHi Funda,
I tried to send it to your e-mail account with removing nospam stuff from yo
ur e-mail address.
Hope you'll get it..
Umut Nazl?ca, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)|||Thank you so much but it didn't pass our spam filter. If I give you an ftp ,
can you please put it there'
Fulya
"Umut Nazlica" <anonymous@.discussions.microsoft.com> wrote in message
news:4A4F6755-768F-4079-ADAE-E8DF09449BB5@.microsoft.com...
> Hi Funda,
> I tried to send it to your e-mail account with removing nospam stuff from
your e-mail address.
> Hope you'll get it..
> Umut Nazlica, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)
>|||It didn't pass our spam filter.You can send it also to the following
address;
fulyaerol888@.hotmail.com
Thank you.
Fulya
"Umut Nazlica" <anonymous@.discussions.microsoft.com> wrote in message
news:4A4F6755-768F-4079-ADAE-E8DF09449BB5@.microsoft.com...
> Hi Funda,
> I tried to send it to your e-mail account with removing nospam stuff from
your e-mail address.
> Hope you'll get it..
> Umut Nazlica, (MCSE 2000/NT; MCDBA; MCSA; MCP+I)
>|||Hi,
i send it to your hotmail account..|||Hi Umut,
I couldn't receive it also at my hotmail account. I need that file badly.
can you please send it to my ftp account? I would appreciate it so much.Can
you please send me your email address so that I can send you the ftp info?
Thank you.
Fulya
"Umut Nazlica" <anonymous@.discussions.microsoft.com> wrote in message
news:38C89CCE-2937-4B28-BCE2-E8A0CE413867@.microsoft.com...
> Hi,
> i send it to your hotmail account..
>|||Hi,
I wasn't at my desk for a while.
You can send your ftp info to umutn@.hotmail.com (if you're still looking for
the file.)

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: SQL Server does not exist or access denied.

Receiving the following error:
Message: SQL Server does not exist or access denied.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.ConnectionPool.GetConnection
(Boolean& isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledCon
nection(SqlConnectionString options, Boolean&
isInTransaction)
any clues please?
Check the Datasource property of the connection string to make sure the
server name /instance name is correct - you can check by creating a new text
document, renaming it to test.udl, opeing and setting the connection
properties, then closing it and open in notepad.
Make sure you can ping the server.
Make sure the DBA's or network dudes haven't closed port 1433
"Belinda" <anonymous@.discussions.microsoft.com> wrote in message
news:37a001c4a06d$7b5405c0$a501280a@.phx.gbl...
> Receiving the following error:
> Message: SQL Server does not exist or access denied.
> Source: .Net SqlClient Data Provider
> at System.Data.SqlClient.ConnectionPool.GetConnection
> (Boolean& isInTransaction)
> at
> System.Data.SqlClient.SqlConnectionPoolManager.Get PooledCon
> nection(SqlConnectionString options, Boolean&
> isInTransaction)
> any clues please?

Message: Missing end comment mark '*/'.

Hi,

I created a web syncronization merge replication ,both servers and clients are sql server 2005 and everything is fine with the settings , but while synchronization and after i received some sch files i got the following message at the subscribers on the initial snapshot operation:

2006-06-27 07:50:14.471 The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'myfile_311.sch' could not be propagated to the subscriber.
2006-06-27 07:50:14.471 Category:NULL
Source: Microsoft SQL Native Client
Number: 113
Message: Missing end comment mark '*/'.

Any idea ?

Tarek Ghazali

SQL Server MVP

My guess is that myfile_311.sch contains a stored procedure|view|function with something like

...

There is a longstanding limitation in our batch parsing logic that will erroroneously recognize 'go's embedded in comments to be batch delimiters. The only workaround for this is to edit the script generated by the snapshot agent to remove the offending comment. We really hope that we can address this issue in the near future.

-Raymond

message: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. (severity 14)

Hi Im getting the following error when trying to connect to a php page...any ideas

message: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. (severity 14)

Hi,

seems that you tried to impersonate the WebUser at the SQL Server database. The question would be what you want to use, wheter Windows Authentication or SQL Authentication ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Message: Cannot use Large Page Extensions

Hello!
I have noticed following messaeg in SQL Server 2005 error log:'Cannot
use Large Page Extensions: lock memory privilege was not granted.' Server
is Active/Passive 64-bit SQL Server cluster with 28GB or RAM.
Can anyone explaing what this messaeg means?
Thanks,
Igor
Did you give the service account for the SQL Server service "Lock Pages in memory" user rights.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:umi2vLIRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have noticed following messaeg in SQL Server 2005 error log:'Cannot use Large Page
> Extensions: lock memory privilege was not granted.' Server is Active/Passive 64-bit SQL Server
> cluster with 28GB or RAM.
> Can anyone explaing what this messaeg means?
> Thanks,
> Igor
>
|||Tibor,
Is this a good idea to do this on 64-bit vesion of SQL Server 2005? BOL
says: 'Though not required, Microsoft recommends locking pages in memory
when using 64-bit operating systems'. Will my SQL Server benefit from having
this option configured?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eBUVHQIRGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Did you give the service account for the SQL Server service "Lock Pages in
> memory" user rights.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:umi2vLIRGHA.4952@.TK2MSFTNGP09.phx.gbl...
>
|||Slava Oks has several articles on memory management on his blog. As I recall, he recommends locking
pages on 64 bit as well. Check it out at http://blogs.msdn.com/slavao/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:O0LuocIRGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Tibor,
> Is this a good idea to do this on 64-bit vesion of SQL Server 2005? BOL says: 'Though not
> required, Microsoft recommends locking pages in memory when using 64-bit operating systems'. Will
> my SQL Server benefit from having this option configured?
>
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eBUVHQIRGHA.4300@.TK2MSFTNGP14.phx.gbl...
>

Message: Cannot use Large Page Extensions

Hello!
I have noticed following messaeg in SQL Server 2005 error log:'Cannot
use Large Page Extensions: lock memory privilege was not granted.' Server
is Active/Passive 64-bit SQL Server cluster with 28GB or RAM.
Can anyone explaing what this messaeg means?
Thanks,
IgorDid you give the service account for the SQL Server service "Lock Pages in m
emory" user rights.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:umi2vLIRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have noticed following messaeg in SQL Server 2005 error log:'Cannot u
se Large Page
> Extensions: lock memory privilege was not granted.' Server is Active/Pass
ive 64-bit SQL Server
> cluster with 28GB or RAM.
> Can anyone explaing what this messaeg means?
> Thanks,
> Igor
>|||Tibor,
Is this a good idea to do this on 64-bit vesion of SQL Server 2005? BOL
says: 'Though not required, Microsoft recommends locking pages in memory
when using 64-bit operating systems'. Will my SQL Server benefit from having
this option configured?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eBUVHQIRGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Did you give the service account for the SQL Server service "Lock Pages in
> memory" user rights.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:umi2vLIRGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||Slava Oks has several articles on memory management on his blog. As I recall
, he recommends locking
pages on 64 bit as well. Check it out at http://blogs.msdn.com/slavao/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:O0LuocIRGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Tibor,
> Is this a good idea to do this on 64-bit vesion of SQL Server 2005? BOL
says: 'Though not
> required, Microsoft recommends locking pages in memory when using 64-bit o
perating systems'. Will
> my SQL Server benefit from having this option configured?
>
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eBUVHQIRGHA.4300@.TK2MSFTNGP14.phx.gbl...
>

Message: Cannot use Large Page Extensions

Hello!
I have noticed following messaeg in SQL Server 2005 error log:'Cannot
use Large Page Extensions: lock memory privilege was not granted.' Server
is Active/Passive 64-bit SQL Server cluster with 28GB or RAM.
Can anyone explaing what this messaeg means?
Thanks,
IgorDid you give the service account for the SQL Server service "Lock Pages in memory" user rights.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:umi2vLIRGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have noticed following messaeg in SQL Server 2005 error log:'Cannot use Large Page
> Extensions: lock memory privilege was not granted.' Server is Active/Passive 64-bit SQL Server
> cluster with 28GB or RAM.
> Can anyone explaing what this messaeg means?
> Thanks,
> Igor
>|||Tibor,
Is this a good idea to do this on 64-bit vesion of SQL Server 2005? BOL
says: 'Though not required, Microsoft recommends locking pages in memory
when using 64-bit operating systems'. Will my SQL Server benefit from having
this option configured?
Thanks,
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eBUVHQIRGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Did you give the service account for the SQL Server service "Lock Pages in
> memory" user rights.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:umi2vLIRGHA.4952@.TK2MSFTNGP09.phx.gbl...
>> Hello!
>> I have noticed following messaeg in SQL Server 2005 error log:'Cannot
>> use Large Page Extensions: lock memory privilege was not granted.'
>> Server is Active/Passive 64-bit SQL Server cluster with 28GB or RAM.
>> Can anyone explaing what this messaeg means?
>> Thanks,
>> Igor
>|||Slava Oks has several articles on memory management on his blog. As I recall, he recommends locking
pages on 64 bit as well. Check it out at http://blogs.msdn.com/slavao/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:O0LuocIRGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Tibor,
> Is this a good idea to do this on 64-bit vesion of SQL Server 2005? BOL says: 'Though not
> required, Microsoft recommends locking pages in memory when using 64-bit operating systems'. Will
> my SQL Server benefit from having this option configured?
>
> Thanks,
> Igor
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eBUVHQIRGHA.4300@.TK2MSFTNGP14.phx.gbl...
>> Did you give the service account for the SQL Server service "Lock Pages in memory" user rights.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:umi2vLIRGHA.4952@.TK2MSFTNGP09.phx.gbl...
>> Hello!
>> I have noticed following messaeg in SQL Server 2005 error log:'Cannot use Large Page
>> Extensions: lock memory privilege was not granted.' Server is Active/Passive 64-bit SQL Server
>> cluster with 28GB or RAM.
>> Can anyone explaing what this messaeg means?
>> Thanks,
>> Igor
>>
>

message with xml validation

I created a XML SCHEMA and MESSAGE TYPE with VALIDATION = VALID_XML WITH SCHEMA COLLECTION.

I thought this would validate the message send with the schema. but when I try to send the message with a different xml, the message is send. there were no errors. Is there something I did not set?

thanks

Does the target database (if different from the initiator database) define the message type with the same schema?

|||its the same, but it should not been able to send if the xml is not according to the valid schema?|||Can you post your CREATE MESSAGE TYPE, CREATE CONTRACT, CREATE (target) SERVICE, BEGIN DIALOG and SEND statements?

Message when performing end user sort

Hi,

I have added interactive sort to report. When i perform the sort in the preview of the report, i see a message 'Report is being generated'. But when i do the sorting from Report viewer control, the screen goes blank before the sorted data comes up.

Is there any way I can display a message telling sorting is in progress?

Any help in this regard is appreciated.

Thanks.

Possibly you could do this by handling the Sort event.

Maybe put a Label above the report viewer and make invisible on report viewer sort? I haven't tried it, but it's an idea.

Code Snippet

Private Sub ReportViewer1_Sort(ByVal sender As Object, ByVal e As Microsoft.Reporting.WebForms.SortEventArgs) Handles ReportViewer1.Sort

End Sub

|||

Thanks for your reply. But I am not able to find a sort event for the report. The only events I get are the ReportSnapshotUpdated and ReportHistorySnaphot.

Also from my application, I am just calling the Report server URL for the report viewer.

Can you please tell me how I can proceed in thi scnario.

|||This event can only be handled on the report viewer in Visual Studio 2005.

Message when performing end user sort

Hi,

Is there any way I can display a message telling sorting is in progress?

Any help in this regard is appreciated.

Thanks.

Possibly you could do this by handling the Sort event.

Maybe put a Label above the report viewer and make invisible on report viewer sort? I haven't tried it, but it's an idea.

Code Snippet

Private Sub ReportViewer1_Sort(ByVal sender As Object, ByVal e As Microsoft.Reporting.WebForms.SortEventArgs) Handles ReportViewer1.Sort

End Sub

|||

Thanks for your reply. But I am not able to find a sort event for the report. The only events I get are the ReportSnapshotUpdated and ReportHistorySnaphot.

Also from my application, I am just calling the Report server URL for the report viewer.

Can you please tell me how I can proceed in thi scnario.

|||This event can only be handled on the report viewer in Visual Studio 2005.

Message when click on login user

Hi all,
I restored 4 databases onto another SQL server and added
sql logins. When I click on a login under
Security I get this message?
"One or more databases are inaccessible and will not be displayed in the
database access tab"
I don't understand this message and couldn't find anything on it.
thanks
gvA database is off line, in standby, suspect, recovering,
etc. Check the statuses of your databases. Did you fully
restore all of them or is one of the restored databases
still recovering?
-Sue
On Thu, 12 Oct 2006 12:25:41 -0400, "gv"
<viator.gerry@.gmail.com> wrote:

>Hi all,
>I restored 4 databases onto another SQL server and added
>sql logins. When I click on a login under
>Security I get this message?
>"One or more databases are inaccessible and will not be displayed in the
>database access tab"
>I don't understand this message and couldn't find anything on it.
>thanks
>gv
>|||Still looking into this
thanks for your help
gv
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:c7ati2d5j4evg2inafemok4d44d2jckim6@.
4ax.com...
>A database is off line, in standby, suspect, recovering,
> etc. Check the statuses of your databases. Did you fully
> restore all of them or is one of the restored databases
> still recovering?
> -Sue
> On Thu, 12 Oct 2006 12:25:41 -0400, "gv"
> <viator.gerry@.gmail.com> wrote:
>
>

Message Type and Message Body..............

Hello,

I am having trouble specifying a message body that is valid. I mean for the client to send. If I leave it as null then everything is ok but if I create a memorystream and add a line of text it reports back it did not pass validation. I do not understand this and am not sure what to do. I need to send a message based on a code and text but do not know the format of the body that is allowed. The code I am refering to comes out of HelloWord_CLR because that is what I am formating my sample after. I call it the same way it calls the return message done in ServiceProc. I need to know the message format including body since this does not seem to work. A sample of the call is bellow.

// Create an empty request message

string Msg = "Hello";

MemoryStream body = new MemoryStream(Encoding.ASCII.GetBytes(Msg));

Message request = new Message("Request", body);

Thanks,

Scott Allison...

You set the validation of your Request message to be 'Empty'. Hence Service Broker is making sure that you do not attempt to send a message with a non-empty body. If you want to sent arbitrary bytes, set validation to 'None'. If you want to send XML and have it validated set it to 'Well_Formed_XML' or 'XML with schema validation'.

Message Time to Live

Hi There

I have issues around forwarding message being dropped because they exceed the forwarding message time to live.

I cannot find anything in BOL or the net regarding changing the message time to live value to be higher, does anyone know where i change this setting ?

Thanx

The message time to live is 30 seconds and cannot be changed. However the tolerated time drift between servers is 30 minutes, so effectively a message can stay in traffic 30 minutes and 30 seconds. With such a long lifetime, I find it difficult to believe message are really dropped because of lifetime expiration. First of all, can you verify that there is no time drift between the servers involved?

|||

Hi Remus

I just posted the complete scenario under the thread "Messages cannot be dispatched", i will mark this quesiton as resolved, we can continue on that thread, i will check server times now.

Thanx

|||

Hi Remus

ANother point is that we are expecting to send very large messages over very slow netowkrs that will take hours to send, are you saying that is not possible with SB ?

Thanx

|||

During transport a 'message' is an individual fragment, 90kb in size. The 90Kb have to reach the final destination within 30 minutes. Are you saying your network is slower than 60 bps?

|||

Hi Remus

The server times are the same.

The network is +- 1KB per second, i doubt the 30 minutes is the issue , but i am definately expering the problem, but like i said my real concern is actually a related issue under the thread "Large Messages cannot be dispatched" lets continue there, Thank You

message that the tables are read-only

I have an Access 2000 ADP application and MS SQL 7 and when the user opens it on a second computer (and a seperate application) they get the message that the tables are read-only.

When I try it in my office on 2 computers I don't get it.

What should I be looking for?

Michaelthat essentially says that you open the file in exclusive mode. If you have this setting (its the default I believe) then if you can't get it in exclusive mode, it becomes read only.

I might have this wrong in that if a machine grabs the file in exclusive mode it might be locking out the others.

Check the settings on your machine(s), I believe its under Tools-Options-Advanced.

HTH|||I did not think you could open the tables in an MS SQL 7 database exclusively. I know you can when using an .MDB back end.

Are you sure this is the case? I will look at it on my server side.

Message size limitations on Service Broker?

Hi Everyone
Does anyone know if there is/isn't a size limitation on the Service
Broker. I have a remote machine running with express 2005 and I would
like to send message to/from the machine in Head Office running SQL
Server Enteprise 2005.
Thanks in advance
EricThe max message size of the service broker is 2 gigabytes (one billion
Unicode characters) if I am not mistaken.
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
<ricolee99@.gmail.com> wrote in message
news:1158184604.504436.77770@.m73g2000cwd.googlegroups.com...
> Hi Everyone
> Does anyone know if there is/isn't a size limitation on the Service
> Broker. I have a remote machine running with express 2005 and I would
> like to send message to/from the machine in Head Office running SQL
> Server Enteprise 2005.
> Thanks in advance
> Eric
>

Message size limitations on Service Broker?

Hi Everyone
Does anyone know if there is/isn't a size limitation on the Service
Broker. I have a remote machine running with express 2005 and I would
like to send message to/from the machine in Head Office running SQL
Server Enteprise 2005.
Thanks in advance
Eric
The max message size of the service broker is 2 gigabytes (one billion
Unicode characters) if I am not mistaken.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
<ricolee99@.gmail.com> wrote in message
news:1158184604.504436.77770@.m73g2000cwd.googlegro ups.com...
> Hi Everyone
> Does anyone know if there is/isn't a size limitation on the Service
> Broker. I have a remote machine running with express 2005 and I would
> like to send message to/from the machine in Head Office running SQL
> Server Enteprise 2005.
> Thanks in advance
> Eric
>

Message size limitations on Service Broker?

Hi Everyone
Does anyone know if there is/isn't a size limitation on the Service
Broker. I have a remote machine running with express 2005 and I would
like to send message to/from the machine in Head Office running SQL
Server Enteprise 2005.
Thanks in advance
EricThe max message size of the service broker is 2 gigabytes (one billion
Unicode characters) if I am not mistaken.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
<ricolee99@.gmail.com> wrote in message
news:1158184604.504436.77770@.m73g2000cwd.googlegroups.com...
> Hi Everyone
> Does anyone know if there is/isn't a size limitation on the Service
> Broker. I have a remote machine running with express 2005 and I would
> like to send message to/from the machine in Head Office running SQL
> Server Enteprise 2005.
> Thanks in advance
> Eric
>

Message sent retry timeout

here is from BOL

>When a conversation is marked delayed, Service Broker performs the matching process again after a timeout period. Notice that failure to find a matching route is not considered an error.

for example it could happen when there is no route for a service.
the question is - what is this timeout. Is it configurable? on a system wide
basis ?

It will be great if this process is described in more details.

Best regards,

Leonid.The timeout period is not configurable. If you do want to force reclassification, however, you could trigger that by running ALTER ROUTE on some pre-existing route that basically does not change any attributes. The ALTER ROUTE would then have no effect, but it would trigger metadata change which causes Service Broker to retry classifying delayed conversations.|||

But what is the timeout is ? What is the overhead when SB trying to connect to a sevice with no route. As I understood SB will try to send a message every minute. Could you please clarify.

Leonid.

|||4 seconds that exponentially backs out to about a minute. But don't quote me on this.

Message says another user accessing data

My Access database as link SQL Server tables. I am trying to remove records
from a subtable and getting this message:
The Microsoft Jet database engine stopped the process becuase you or another
user are attempting to change the same data at the same time.
This isn't the case, however. No one else is on the Database ( I have a
tool which displays the users) and if they were, I am working with test
records which noone would have an interest in viewing.
This happened on a table before, but cleared up when I shut down the
database and opened it again. Now I can't delete the records.
Any help is appreciated.
God Bless,
Mark A. Sam
Mark A. Sam wrote:
> My Access database as link SQL Server tables. I am trying to remove
> records from a subtable and getting this message:
> The Microsoft Jet database engine stopped the process becuase you or
> another user are attempting to change the same data at the same time.
> This isn't the case, however. No one else is on the Database ( I
> have a tool which displays the users) and if they were, I am working
> with test records which noone would have an interest in viewing.
> This happened on a table before, but cleared up when I shut down the
> database and opened it again. Now I can't delete the records.
> Any help is appreciated.
Add a Timestamp column to the table on the server and make sure any bit fields
do not allow Nulls. Links below explain reasons for this to occur.
http://support.microsoft.com/default...kb;en-us;96897
http://support.microsoft.com/default...b;en-us;280730
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
|||Rick,
The table had a time stamp, but a bit filed I just added was allowing nulls.
When I unchecked and tried to save I got this error:
'Customer ST Products Tasks' table
- Unable to modify table.
ADO error: Cannot insert the value NULL into column 'TestRecord', table
'STSIData.dbo.Tmp_Customer ST Products Tasks'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
I don't know what this means since it was checked to allow nulls.
I am working remotely through terminal services and don't have the use of
Project Manager, so I set up a project in Access 2000 to modify the tables.
That could be the problem, I don't know. I tried removing the field and
reentering it, unchecking the 'AllowNulls' checkbox with the same problem.
God Bless,
Mark
"Rick Brandt" <rickbrandt2@.hotmail.com> wrote in message
news:IdJIe.6467$6D5.722@.newssvr29.news.prodigy.net ...
> Mark A. Sam wrote:
> Add a Timestamp column to the table on the server and make sure any bit
fields
> do not allow Nulls. Links below explain reasons for this to occur.
> http://support.microsoft.com/default...kb;en-us;96897
> http://support.microsoft.com/default...b;en-us;280730
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
|||You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
Value and NO NULL value.
The reason is, several of the records in your table have null values... when
you try to modify the design so that it does not allow nulls, it cant modify
it because there already are nulls in the table in some records.
Try running an update query on that table that converts all NULLS on the
field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
then try to modify your table Again
"Mark A. Sam" wrote:

> Rick,
> The table had a time stamp, but a bit filed I just added was allowing nulls.
> When I unchecked and tried to save I got this error:
> 'Customer ST Products Tasks' table
> - Unable to modify table.
> ADO error: Cannot insert the value NULL into column 'TestRecord', table
> 'STSIData.dbo.Tmp_Customer ST Products Tasks'; column does not allow nulls.
> INSERT fails.
> The statement has been terminated.
>
> I don't know what this means since it was checked to allow nulls.
> I am working remotely through terminal services and don't have the use of
> Project Manager, so I set up a project in Access 2000 to modify the tables.
> That could be the problem, I don't know. I tried removing the field and
> reentering it, unchecking the 'AllowNulls' checkbox with the same problem.
> God Bless,
> Mark
>
> "Rick Brandt" <rickbrandt2@.hotmail.com> wrote in message
> news:IdJIe.6467$6D5.722@.newssvr29.news.prodigy.net ...
> fields
>
>
|||There aren't any Null values. In fact I am able to delete new records, but
there is a batch that I am unable to remove. When I added the Field, the
system entered 0 to the existing records.
"TNB" <TNB@.discussions.microsoft.com> wrote in message
news:509E4A52-DE3B-4B03-9996-2DA7698B7DCB@.microsoft.com...
> You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
> Value and NO NULL value.
> The reason is, several of the records in your table have null values...
when
> you try to modify the design so that it does not allow nulls, it cant
modify[vbcol=seagreen]
> it because there already are nulls in the table in some records.
> Try running an update query on that table that converts all NULLS on the
> field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
> then try to modify your table Again
> "Mark A. Sam" wrote:
nulls.[vbcol=seagreen]
nulls.[vbcol=seagreen]
of[vbcol=seagreen]
tables.[vbcol=seagreen]
problem.[vbcol=seagreen]
time.[vbcol=seagreen]
bit[vbcol=seagreen]
|||Well this clear up for no apparent reason other than prayer. ;)
"TNB" <TNB@.discussions.microsoft.com> wrote in message
news:509E4A52-DE3B-4B03-9996-2DA7698B7DCB@.microsoft.com...
> You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
> Value and NO NULL value.
> The reason is, several of the records in your table have null values...
when
> you try to modify the design so that it does not allow nulls, it cant
modify[vbcol=seagreen]
> it because there already are nulls in the table in some records.
> Try running an update query on that table that converts all NULLS on the
> field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
> then try to modify your table Again
> "Mark A. Sam" wrote:
nulls.[vbcol=seagreen]
nulls.[vbcol=seagreen]
of[vbcol=seagreen]
tables.[vbcol=seagreen]
problem.[vbcol=seagreen]
time.[vbcol=seagreen]
bit[vbcol=seagreen]

Message says another user accessing data

My Access database as link SQL Server tables. I am trying to remove records
from a subtable and getting this message:
The Microsoft Jet database engine stopped the process becuase you or another
user are attempting to change the same data at the same time.
This isn't the case, however. No one else is on the Database ( I have a
tool which displays the users) and if they were, I am working with test
records which noone would have an interest in viewing.
This happened on a table before, but cleared up when I shut down the
database and opened it again. Now I can't delete the records.
Any help is appreciated.
God Bless,
Mark A. SamMark A. Sam wrote:
> My Access database as link SQL Server tables. I am trying to remove
> records from a subtable and getting this message:
> The Microsoft Jet database engine stopped the process becuase you or
> another user are attempting to change the same data at the same time.
> This isn't the case, however. No one else is on the Database ( I
> have a tool which displays the users) and if they were, I am working
> with test records which noone would have an interest in viewing.
> This happened on a table before, but cleared up when I shut down the
> database and opened it again. Now I can't delete the records.
> Any help is appreciated.
Add a Timestamp column to the table on the server and make sure any bit fiel
ds
do not allow Nulls. Links below explain reasons for this to occur.
http://support.microsoft.com/defaul...=kb;en-us;96897
http://support.microsoft.com/defaul...kb;en-us;280730
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com|||Rick,
The table had a time stamp, but a bit filed I just added was allowing nulls.
When I unchecked and tried to save I got this error:
'Customer ST Products Tasks' table
- Unable to modify table.
ADO error: Cannot insert the value NULL into column 'TestRecord', table
'STSIData.dbo.Tmp_Customer ST Products Tasks'; column does not allow nulls.
INSERT fails.
The statement has been terminated.
I don't know what this means since it was checked to allow nulls.
I am working remotely through terminal services and don't have the use of
Project Manager, so I set up a project in Access 2000 to modify the tables.
That could be the problem, I don't know. I tried removing the field and
reentering it, unchecking the 'AllowNulls' checkbox with the same problem.
God Bless,
Mark
"Rick Brandt" <rickbrandt2@.hotmail.com> wrote in message
news:IdJIe.6467$6D5.722@.newssvr29.news.prodigy.net...
> Mark A. Sam wrote:
> Add a Timestamp column to the table on the server and make sure any bit
fields
> do not allow Nulls. Links below explain reasons for this to occur.
> http://support.microsoft.com/defaul...=kb;en-us;96897
> http://support.microsoft.com/defaul...kb;en-us;280730
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>|||You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
Value and NO NULL value.
The reason is, several of the records in your table have null values... when
you try to modify the design so that it does not allow nulls, it cant modify
it because there already are nulls in the table in some records.
Try running an update query on that table that converts all NULLS on the
field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
then try to modify your table Again
"Mark A. Sam" wrote:

> Rick,
> The table had a time stamp, but a bit filed I just added was allowing null
s.
> When I unchecked and tried to save I got this error:
> 'Customer ST Products Tasks' table
> - Unable to modify table.
> ADO error: Cannot insert the value NULL into column 'TestRecord', table
> 'STSIData.dbo.Tmp_Customer ST Products Tasks'; column does not allow nulls
.
> INSERT fails.
> The statement has been terminated.
>
> I don't know what this means since it was checked to allow nulls.
> I am working remotely through terminal services and don't have the use of
> Project Manager, so I set up a project in Access 2000 to modify the tables
.
> That could be the problem, I don't know. I tried removing the field and
> reentering it, unchecking the 'AllowNulls' checkbox with the same problem.
> God Bless,
> Mark
>
> "Rick Brandt" <rickbrandt2@.hotmail.com> wrote in message
> news:IdJIe.6467$6D5.722@.newssvr29.news.prodigy.net...
> fields
>
>|||There aren't any Null values. In fact I am able to delete new records, but
there is a batch that I am unable to remove. When I added the Field, the
system entered 0 to the existing records.
"TNB" <TNB@.discussions.microsoft.com> wrote in message
news:509E4A52-DE3B-4B03-9996-2DA7698B7DCB@.microsoft.com...
> You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
> Value and NO NULL value.
> The reason is, several of the records in your table have null values...
when
> you try to modify the design so that it does not allow nulls, it cant
modify[vbcol=seagreen]
> it because there already are nulls in the table in some records.
> Try running an update query on that table that converts all NULLS on the
> field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
> then try to modify your table Again
> "Mark A. Sam" wrote:
>
nulls.[vbcol=seagreen]
nulls.[vbcol=seagreen]
of[vbcol=seagreen]
tables.[vbcol=seagreen]
problem.[vbcol=seagreen]
time.[vbcol=seagreen]
bit[vbcol=seagreen]|||Well this clear up for no apparent reason other than prayer. ;)
"TNB" <TNB@.discussions.microsoft.com> wrote in message
news:509E4A52-DE3B-4B03-9996-2DA7698B7DCB@.microsoft.com...
> You have to ensure all your bit fields have either a TRUE (1) or FALSE (0)
> Value and NO NULL value.
> The reason is, several of the records in your table have null values...
when
> you try to modify the design so that it does not allow nulls, it cant
modify[vbcol=seagreen]
> it because there already are nulls in the table in some records.
> Try running an update query on that table that converts all NULLS on the
> field into 0 "UPDATE tablename set fieldname = 0 where fieldname is null"
> then try to modify your table Again
> "Mark A. Sam" wrote:
>
nulls.[vbcol=seagreen]
nulls.[vbcol=seagreen]
of[vbcol=seagreen]
tables.[vbcol=seagreen]
problem.[vbcol=seagreen]
time.[vbcol=seagreen]
bit[vbcol=seagreen]

Message Retieval

Hello again,

A.
This time I would like to get your input on two ways of getting a message off a queue. Both are samples, but I would like to know more about the implications.
This first example (BOL) goes directly to the queue:

WAITFOR (

RECEIVE TOP(1)

@.messageTypeName = message_type_name,

@.messageBody = message_body,

@.conversationHandle = conversation_handle

FROM ExpenseQueue

), TIMEOUT 500 ;
The second one (SQL Server samples) obtains a conversation hanle, then gets a message:
WAITFOR(
GET CONVERSATION GROUP @.conversation_group_id FROM [dbo].[ProcessBCHQueue]),
TIMEOUT 500 ;

RECEIVE
TOP(1)
@.conversation_handle = conversation_handle,
@.message_type_name = message_type_name,
@.message_body =
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'<none/>' AS XML)
END
FROM [dbo].[ProcessBCHQueue]
WHERE conversation_group_id = @.conversation_group_id ;

what are the implications between both calls?
I would think that:
1. In any case messages are initially send via a dialogue, so I am not sure why the first example eliminates the conversation handle;
2. Does the second example allow for more readers getting messages on different conversations, since unique conversation handles are explictly obtained?
3. Since the first method was in the Handling Poison Messages of BOL, is that a more straight forward approach that allows for easier handling of error messages?
4. Anything else that might be interesting to note? Please share.

Thanks again,

Lubomir

The second example shows how to process messages when you need to retrieve a state from your database associated with the incomming messages. The recomended way is to store this state in a table and use the conversation_group as the table key. The second example allows you to first lock an available conversation group (one that has messages to be received), then look up the state associated with this particular group, then receive the messages of this group.

The different approach is only a performance optimization. The first example would require you to look up this state for each message received. The second example allows you to look up the state once, then process all messages for that particular conversation group in a loop, w/o looking up the state again.

The typical example of processing in the second case is:

BEGIN TRANSACTION
WAITFOR (GET CONVERSATION GROUP...), TIMEOUT ...
WHILE (@.conversation_group IS NOT NULL)
BEGIN
-- lookup state in the state table here, using the @.conversation_group key
--
RECEIVE TOP(1) ... WHERE conversation_group = @.conversation_group
WHILE (@.conversation_handle IS NOT NULL)
BEGIN
-- proces message here
--
RECEIVE TOP(1) ... WHERE conversation_group = @.conversation_group
END
COMMIT;
BEGIN TRANSACTION;
WAITFOR (GET CONVERSATION GROUP...), TIMEOUT ...
END
COMMIT

The transaction boundaries are important in this example. One shouldn't commit each individual message as it processes the inner loop, as a commit will actually release the lock on the conversation_group and another reader might alter the looked up state.

There is no difference between the two approaches on how many readers can progress in paralel. Both approaches will lock the conversations in the same way, the difference is only in how the procedure would handle an application specific state lookup.

HTH,
~ Remus

|||Thank you very much Remus,

Lubomir

message queues

I having a fight with what I think is the message queue system when
running stored procedures. If I have this as a sp
print 'Handling First File'
(do some processing on the first file)
print 'Handling Second File'
(do some processing on the second file)
print 'Handling Third File'
(do some processing on the third file)
print 'Done'
I want to use the SQLMDO 'ExecuteWithResultsAndMessages' and
'ServerMessage' functions to use print commands as part of my front end
but what happens is that while all the processing is done my dialog
filled by 'ServerMessage.Message' is blank and then all my print
commands come together - London bus style. (Query Analyser does the
same).
This presumably is down to Message Queuing ? One help file I saw says
something like "when you create a Stored Procedure (with queuing
enabled)...." suggesting that you can create a sp with queing
disabled? but I cant find anything in the syntax to stipulate that.
Is there a way round this so that messages are forwarded by the agent
as they are reached in the script and not queued.?
Thanks
GlennIt really has nothing to do with queuing per say it is that the packet does
not get sent to the client until the batch is done or the buffer is full.
In a nutshell since the packets are around 4K in size it doesn't waste many
round trips each time a little bit of info is placed in the buffer. It
waits until it is full until it sends it or when the batch is done. Here is
an example to show this. Comment out the replicate statement and you will
see it doesn't print until the batch is done.
DECLARE @.X INT
SET @.X = 1
WHILE @.X < 100
BEGIN
PRINT CAST(@.X AS VARCHAR(20))
PRINT REPLICATE(' ',8000)
SET @.X = @.X + 1
waitfor delay '00:00:01'
END
Andrew J. Kelly SQL MVP
<glenn.hughes@.luk.net> wrote in message
news:1140543654.666118.191320@.g44g2000cwa.googlegroups.com...
>I having a fight with what I think is the message queue system when
> running stored procedures. If I have this as a sp
> print 'Handling First File'
> (do some processing on the first file)
> print 'Handling Second File'
> (do some processing on the second file)
> print 'Handling Third File'
> (do some processing on the third file)
> print 'Done'
> I want to use the SQLMDO 'ExecuteWithResultsAndMessages' and
> 'ServerMessage' functions to use print commands as part of my front end
> but what happens is that while all the processing is done my dialog
> filled by 'ServerMessage.Message' is blank and then all my print
> commands come together - London bus style. (Query Analyser does the
> same).
> This presumably is down to Message Queuing ? One help file I saw says
> something like "when you create a Stored Procedure (with queuing
> enabled)...." suggesting that you can create a sp with queing
> disabled? but I cant find anything in the syntax to stipulate that.
> Is there a way round this so that messages are forwarded by the agent
> as they are reached in the script and not queued.?
> Thanks
> Glenn
>|||You can 'cheat' the messages out faster by raising low-level errors...
RAISERROR('Hack!!!',0,1) WITH NOWAIT
HTH,
Ben
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SqYsHxNGHA.1676@.TK2MSFTNGP09.phx.gbl...
> It really has nothing to do with queuing per say it is that the packet
> does not get sent to the client until the batch is done or the buffer is
> full. In a nutshell since the packets are around 4K in size it doesn't
> waste many round trips each time a little bit of info is placed in the
> buffer. It waits until it is full until it sends it or when the batch is
> done. Here is an example to show this. Comment out the replicate
> statement and you will see it doesn't print until the batch is done.
> DECLARE @.X INT
> SET @.X = 1
> WHILE @.X < 100
> BEGIN
> PRINT CAST(@.X AS VARCHAR(20))
> PRINT REPLICATE(' ',8000)
> SET @.X = @.X + 1
> waitfor delay '00:00:01'
> END
>
> --
> Andrew J. Kelly SQL MVP
>
> <glenn.hughes@.luk.net> wrote in message
> news:1140543654.666118.191320@.g44g2000cwa.googlegroups.com...
>|||Thanks to both of you. Ben, I just love that word "Cheat" its what
programming is all about. The error level idea sounds good. I'll try
that
Cheers
Glenn

Message Queue Task 64 bit Cluster issue

Hello,

I'm using the Message Queuing task to create a local private queue message. Everything works great on a 32 bit machine. When I try this on a 64 bit Itanium Cluster I keep getting the message "Message queue service is not available" in my SSIS log. I've using this string as my path "ClusterName\private$\QueueName". Does anyone know of any issues with the Message Queue task on 64 bit or a cluster? The Message Queue service is up and running, it doesn't make sense.

Thanks,

Andy

I found out that you must install MSMS on each node of the cluster separately. Then set up your Message Queue using the Cluster Administrator. See this document for help.

http://download.microsoft.com/download/4/f/5/4f518f76-c1ce-431b-b79f-71caf9e27578/MSMQ3incluster.doc

Also, it's easier if you use the utility mmcv.exe to set up the cluster:

http://support.microsoft.com/?kbid=898701&SD=tech

Andy

Message Queue Task

Ok, im making some progress. So what i have is a Message Queue Task which is bound to a message queue connection manager (which 'tests' ok). The Message Queue Task is set to recieve, variable from string message (declared a variable of type string) and to remove the message from the queue. The output of that task is piped into the data flow task.
The data flow task expands into a XML Source which is configured to get its input from the string i declared in the Message Queue Task and i point the schemas path to an appropriate schema. I then pipe the output of that into a SQL server destination which ive mapped all the columns from the XML message to a table (which the SQL server destination created for me).

It all looks good on paper, and builds properly with no errors etc. There is already a message in the appropriate private queue. When i go to debug it, it just sits on the Message Queue Task node (its yellow) and goes no further. No data is put into the DB. I have put a watcher on the link between the XML Source and the SQL server destination, and can see no data being piped through.
Even if i send another message, the execution of my package doesnt step passed the Message Queue Task. Its just sitting there waiting for something? what? I thought it would block until there was a message on that queue, and then process it if and when it arrives. But it doesnt seem to do that.

Any ideas?

I read on MSDN that you need integration services installed. I have checked and i do, and its running. Is theres something else i need to configure?

Help!

This works for me on either private or public Message Queue. How did you send the message to the private message queue?

This is what I'm doing

Create a MSMQ task (MSMQ Connection to machinename\private$\juantest1). Create a string variable in the package: sendVariable (value: ValueSend). In The MSMQ Task use the above connection, select send variable and use the create variable. Execute the package

Create another package MSMQReceive (same connection manager). Create a string variable recVariable (value: Default value). In the MSMQ Task select the correct MSMQ connection and for message select Receive Message, message type= variable message; variable = user::recVariable. Put a break point after execution. Execute the package. The value of variable recVariable = ValueSend

Juan Acosta (Microsoft SSIS Team)

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

|||Hi Juan, thanks for the reply. Its interesting you ask 'how am i sending it?' I wouldnt have thought it would matter. An MSMQ message should be independant of how it was sent, shouldnt it? Im actaully sending it from a small console .NET 1.1 C# app using the standard .NET MQ library (system.messaging?) Its just a simple XML message. I can post the console app code if you think that would help?
I could also build the sender app in SSIS and see if that works for me. If it does we know its something with the way im sending the message - but that seems a bit counter-intuitve to me.

Thanks again, will let you know how i get on.|||Hi Juan. I have just made another SSIS package which is a simple MSMQ task which is set to send to the private queue i have set up withe the Message Queue Connection Manager (which tested ok). That package runs and says it completed fine, and it posts a message to the private queue (.\private$\testq). I can see the message from the computer managment applet etc.

I run the reciever app and it just sits there doing nothing. Waiting on the MSMQ task.
The way i have it set up is on controlflow tab i have an MSMQ task which pipes into a Dataflow task. i have a break point set on the dataflow task node on the controlflow tab. It never hits that breakpoint, no matter how i sent the message (from my console app or from another SSIS package), whether theres a message there or not when i start the reciever app.

Another thing which MAY be of interest is that im running all this on a virtual PC (WMWare 5.0) running W2K SP4. I have not applied Yukon SP1 yet either.

Still nothing seems to work|||have applied SQLSvr2005 SP 1 now, and it has made no difference :(|||We dont need to install MSMQ triggers do we?|||

Taurineman,

I was just having the same problem assisting a Microsoft Partner. But I found the solution. The message label is what the Message Queue task looks at for the type of message.
For a String Message set the label to "String Message" for a data file set the message label to "Data File Message"

This isn't very straight forward. The way I stumbled onto this is by setting up two Message Queue tasks in my SSIS package. The first one sends the message and the second one recieves the message. This runs just fine. I then I took a look at the messages to determine what I was missing from the messages I was sending from a C# project. It turns out the Label string needs to be exact.

Code To Send Message Correctly with C# project for Message Queue Task to process:
System.Messaging.Message recoverableMessage = new System.Messaging.Message()
recoverableMessage.Body = "Test";
recoverableMessage.Label = "String Message";
recoverableMessage.Recoverable = true;
MessageQueue msgQ = new MessageQueue(@."machinename\private$\810");
msgQ.Send(recoverableMessage);

|||

Unfortunately this forum does not have much discussion on using Message Queue Task.

I tried the above post by Oliver and it worked fine. I have a complex problem in using MSMQ task.

i have a sender package that has a MSMQ task and SQL Exec task. The SQL Exec task get data from SQL 2005 (like "select name from person"). And I used a variable (of type Object) "srcVariable" to store the full result set. I then use MSMQ to send this object to a local private queue. It worked fine. Note that since i used ADO.NET provider, so the actual data type for this object is "System.Data.DataSet". OLEDB provider does NOT work for MSMQ because the object is of type __ComObject that can NOT be serializable.

Next I have another receiver package that has a MSMQ task. The MSMQ task was able to read object from the queue. I use a package-level variable to store it. However, the returning type for this object is "String" but the value is "System.Data.DataSet".

Can any expert please suggest how to convert this object from String type to DataSet type so that it can be used downstream?

|||

So if I understand the objective, it is to store a dataset in MSMQ, and then to retrieve the same dataset in a separate IS package for downstream use.

SSIS will not serialize the the Object ("srcVariable") which contains the DataSet for you. Because of that, when your receiver task picks up the message, all you have is what amounts to the ToString() call on a DataSet, which returns "System.Data.DataSet".

For example, it would be the same in powershell to do the following:

(new-object System.Data.DataSet).ToString()
"System.Data.DataSet"

Instead, you can serialize the DataSet object instance to an SSIS String variable in the sender, and then reconstitute it back from a string to a DataSet in the receiver. For example, if you have in sending package two SSIS variables (an Object named "DataSet" which contains a DataSet and a String named "SerializedDataSet"), and in the receiving package the same two variables, you could use the following script tasks to handle the serialization/de-serialization of the dataset. The DataSet could then be used downstream, perhaps in foreach loop container or a dataflow task.

The first task is used in the sender to serialize the dataset to a string prior to that string's transmission via a MSMQ task. The second is for use in the receiver package after the MSMQ message receipt to de-serialize the stringified dataset for downstream use.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text

Public Class ScriptMain

Private shouldTerminate As Boolean = False

Private sourceObject As Object

Private sourceDataSet As DataSet

Private stringifiedDataSet As StringBuilder = New StringBuilder()

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

sourceObject = Dts.Variables("DataSet").Value

sourceDataSet = DirectCast(sourceObject, DataSet)

Using sw As StringWriter = New StringWriter(stringifiedDataSet)

sourceDataSet.WriteXml(sw, XmlWriteMode.WriteSchema)

' stringify DataSet for transmission

End Using

Dts.Variables("SerializedDataSet").Value = _

stringifiedDataSet.ToString()

Catch ex As Exception

shouldTerminate = Dts.Events.FireError(1, _

ex.TargetSite.ToString(), ex.Message, String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Imports System.Text

Public Class ScriptMain

Private shouldTerminate As Boolean = False

Private sourceObject As Object

Private stringifiedDataSet As String

Private targetDataSet As DataSet = New DataSet()

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

sourceObject = Dts.Variables("SerializedDataSet").Value

stringifiedDataSet = DirectCast(sourceObject, String)

Using sr As StringReader = New StringReader(stringifiedDataSet)

targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)

' reconstitute dataset

End Using

Dts.Variables("DataSet").Value = targetDataSet

Catch ex As Exception

shouldTerminate = Dts.Events.FireError(1, _

ex.TargetSite.ToString(), ex.Message, String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

|||

This sounds a very plausible solution. I will try it out and post my finding back.

Just out of my curiosity, how do you know that SSIS does not serialize DataSet object while it is sent to Q ? Any documentation info ?

In the MSMQ receiving task, there is a type of "String To Variable"... then what does this really mean ? Does this reconstruct object from a serialized XML string?

|||SSIS does serialize the DataSet stored in the IS Object variable, along with other IS variable types (using a SOAP formatter as you noted in another thread), when using the message type of "Variable Message". The MSMQ task in receive mode just doesn't understand its own sent messages, excepting strings. That is, the MSMQ task is incompatible with itself, when used to send and then receive a non-string variable message type.

Hence the need, at least right now, to use IS String variables (or IS Object variables which can wrap strings), when using the MSMQ task to receive a variable message.

To my knowledge, the "inability to deserialize" non-strings is not documented, as the receive side of the MSMQ task doesn't limit you to variables of type String or Object, when in receive variables mode.|||

This is really good stuff...

Unfortunately i cannot make it work by using the script above.. Did I miss anything ? Here is what I did.

1. Inside sender package, I used your sending script above, and was able to send to Q using "String message" as Message Type. Inside StringMessage column, I keyed in "User::SerializedDataSet". This works fine and the message got sent into the Q.

2. Inside receiver package, I have a receiving MSMQ task which is followed by a script task (that is copied and pasted your above receiving script). For MSMQ task, in the "Receive" property page, i configured MessageType to be "String message to variable" and variable to be "User::SerializedDataSet". And this MSMQ task works fine. BUT, the script task blows up at:

targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)

Anything is wrong here? Please help.

BTW - I also tried String Message (as opposed to "String message to variable") for MessageType inside receiver package and did not get it work either.

|||

Steve Wang 2006 wrote:
This is really good stuff...
Unfortunately i cannot make it work by using the script above.. Did I miss anything ? Here is what I did.
1. Inside sender package, I used your sending script above, and was able to send to Q using "String message" as Message Type. Inside StringMessage column, I keyed in "User::SerializedDataSet". This works fine and the message got sent into the Q.
Set the expression on the Message String property of the sending MSMQ task to the IS string variable User::SerializedDataSet. That is, the expressions node for the task should look like:
MessageString => @.[User::SerializedDataSet]
This as opposed to the literal value of "User::SerializedDataSet" in the StringMessage property.
2. Inside receiver package, I have a receiving MSMQ task which is followed by a script task (that is copied and pasted your above receiving script). For MSMQ task, in the "Receive" property page, i configured MessageType to be "String message to variable" and variable to be "User::SerializedDataSet". And this MSMQ task works fine. BUT, the script task blows up at:
targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)
Anything is wrong here? Please help.
The "blow up" on the .ReadXml line is a consequence of the message not containly xml (yet). As long as the message does not contain xml, this DataSet.ReadXml() method will throw an exception. So fixing the sending package to use an expression containing xml for the task's StringMessage property will "fix" the receiver task too.
BTW - I also tried String Message (as opposed to "String message to variable") for MessageType inside receiver package and did not get it work either.
The MessageType property inside the receive task should be set to "String Message to Variable", loading the message contents into the receiver package's IS string variable User::SerializedDataSet.

|||

Thank you much... Both Sender package and receiver packages worked great.

I also tried to send a DataSet object from C# code to the same Q, it worked as well. Here is what I did...for reference purpose.

Inside C# code, get data from database and create a DataSet object. Then use the serilization above to create an XML string which is sent to the Q next. Some tricky stuff here is: Be sure to use the identical lable as noted above AND approrpiate formatter which is ActiveXMessageFormatter for the message. I spent quite a bit time to figure this out. I am assuming that SSIS uses this formatter but I did not find any documentation on this. Otherwise SSIS's MSMQ Receiving task won't work.

Of course once SSIS MSMQ Receiving task reads the string, it needs to convert to DataSet as jaegd pointed out above.

|||

As an extra mile to go....

i tried inside C# code to receive the message from Q that is sent from SSIS MSMQ sender package... and I was not able to get the message by using "ActiveXMessageFormatter" which was applied to the Q instance. It blows up at Receive() method call by throwing the following error:

"cannot deserialize the message passed as an argument. Cannot recognize the serialization format"

Any suggenstion ?

Message Queue Task

Any ideas?

I read on MSDN that you need integration services installed. I have checked and i do, and its running. Is theres something else i need to configure?

Help!

This works for me on either private or public Message Queue. How did you send the message to the private message queue?

This is what I'm doing

Juan Acosta (Microsoft SSIS Team)

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

Thanks again, will let you know how i get on.

|||Hi Juan. I have just made another SSIS package which is a simple MSMQ task which is set to send to the private queue i have set up withe the Message Queue Connection Manager (which tested ok). That package runs and says it completed fine, and it posts a message to the private queue (.\private$\testq). I can see the message from the computer managment applet etc.

Another thing which MAY be of interest is that im running all this on a virtual PC (WMWare 5.0) running W2K SP4. I have not applied Yukon SP1 yet either.

Still nothing seems to work

|||have applied SQLSvr2005 SP 1 now, and it has made no difference :(
|||We dont need to install MSMQ triggers do we?
|||

Taurineman,

|||

Unfortunately this forum does not have much discussion on using Message Queue Task.

I tried the above post by Oliver and it worked fine. I have a complex problem in using MSMQ task.

Can any expert please suggest how to convert this object from String type to DataSet type so that it can be used downstream?

|||

So if I understand the objective, it is to store a dataset in MSMQ, and then to retrieve the same dataset in a separate IS package for downstream use.

For example, it would be the same in powershell to do the following:

(new-object System.Data.DataSet).ToString()
"System.Data.DataSet"

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text

Public Class ScriptMain

Private shouldTerminate As Boolean = False

Private sourceObject As Object

Private sourceDataSet As DataSet

Private stringifiedDataSet As StringBuilder = New StringBuilder()

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

sourceObject = Dts.Variables("DataSet").Value

sourceDataSet = DirectCast(sourceObject, DataSet)

Using sw As StringWriter = New StringWriter(stringifiedDataSet)

sourceDataSet.WriteXml(sw, XmlWriteMode.WriteSchema)

' stringify DataSet for transmission

End Using

Dts.Variables("SerializedDataSet").Value = _

stringifiedDataSet.ToString()

Catch ex As Exception

shouldTerminate = Dts.Events.FireError(1, _

ex.TargetSite.ToString(), ex.Message, String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.IO

Imports System.Text

Public Class ScriptMain

Private shouldTerminate As Boolean = False

Private sourceObject As Object

Private stringifiedDataSet As String

Private targetDataSet As DataSet = New DataSet()

Public Sub Main()

Dts.TaskResult = Dts.Results.Success

Try

sourceObject = Dts.Variables("SerializedDataSet").Value

stringifiedDataSet = DirectCast(sourceObject, String)

Using sr As StringReader = New StringReader(stringifiedDataSet)

targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)

' reconstitute dataset

End Using

Dts.Variables("DataSet").Value = targetDataSet

Catch ex As Exception

shouldTerminate = Dts.Events.FireError(1, _

ex.TargetSite.ToString(), ex.Message, String.Empty, 0)

Dts.TaskResult = Dts.Results.Failure

End Try

End Sub

End Class

|||

This sounds a very plausible solution. I will try it out and post my finding back.

Just out of my curiosity, how do you know that SSIS does not serialize DataSet object while it is sent to Q ? Any documentation info ?

In the MSMQ receiving task, there is a type of "String To Variable"... then what does this really mean ? Does this reconstruct object from a serialized XML string?

Hence the need, at least right now, to use IS String variables (or IS Object variables which can wrap strings), when using the MSMQ task to receive a variable message.

This is really good stuff...

Unfortunately i cannot make it work by using the script above.. Did I miss anything ? Here is what I did.

targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)

Anything is wrong here? Please help.

BTW - I also tried String Message (as opposed to "String message to variable") for MessageType inside receiver package and did not get it work either.

|||

Steve Wang 2006 wrote:
This is really good stuff...

Unfortunately i cannot make it work by using the script above.. Did I miss anything ? Here is what I did.

1. Inside sender package, I used your sending script above, and was able to send to Q using "String message" as Message Type. Inside StringMessage column, I keyed in "User::SerializedDataSet". This works fine and the message got sent into the Q.
Set the expression on the Message String property of the sending MSMQ task to the IS string variable User::SerializedDataSet. That is, the expressions node for the task should look like:
MessageString => @.[User::SerializedDataSet]
This as opposed to the literal value of "User::SerializedDataSet" in the StringMessage property.

2. Inside receiver package, I have a receiving MSMQ task which is followed by a script task (that is copied and pasted your above receiving script). For MSMQ task, in the "Receive" property page, i configured MessageType to be "String message to variable" and variable to be "User::SerializedDataSet". And this MSMQ task works fine. BUT, the script task blows up at:

targetDataSet.ReadXml(sr, XmlReadMode.ReadSchema)

Anything is wrong here? Please help.
The "blow up" on the .ReadXml line is a consequence of the message not containly xml (yet). As long as the message does not contain xml, this DataSet.ReadXml() method will throw an exception. So fixing the sending package to use an expression containing xml for the task's StringMessage property will "fix" the receiver task too.

BTW - I also tried String Message (as opposed to "String message to variable") for MessageType inside receiver package and did not get it work either.

The MessageType property inside the receive task should be set to "String Message to Variable", loading the message contents into the receiver package's IS string variable User::SerializedDataSet.

|||

Thank you much... Both Sender package and receiver packages worked great.

I also tried to send a DataSet object from C# code to the same Q, it worked as well. Here is what I did...for reference purpose.

Of course once SSIS MSMQ Receiving task reads the string, it needs to convert to DataSet as jaegd pointed out above.

|||

As an extra mile to go....

"cannot deserialize the message passed as an argument. Cannot recognize the serialization format"

Any suggenstion ?

Saturday, February 25, 2012

Messed up SQL 2000 - Error Code 193

messed up password

messages.sql error during upgrade

messages stuck in sys.transmission_queue

Messages stuck in sys.transmission_queue

Messages stopped appearing in Queue!

Messages stopped appearing in Queue!

Messages in the sys.transmission_queue for a disable queue

messages in sqlsgent.out (sql server 2000)

Messages back from SqlServer while a Backup is being completed?

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

Message: SQL Server does not exist or access denied.

Message: Missing end comment mark '*/'.

message: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. (severity 14)

Message: Cannot use Large Page Extensions

Message: Cannot use Large Page Extensions

Message: Cannot use Large Page Extensions

message with xml validation

Message when performing end user sort

Message when performing end user sort

Message when click on login user

Message Type and Message Body..............

Message Time to Live

message that the tables are read-only

Message size limitations on Service Broker?

Message size limitations on Service Broker?

Message size limitations on Service Broker?

Message sent retry timeout

Message says another user accessing data

Message says another user accessing data

Message Retieval

message queues

Message Queue Task 64 bit Cluster issue

Message Queue Task

Message Queue Task

Merging two dbs

Blog Archive

About Me