msmq to sql?

that's what I thought. I guess I'm going to recommend what you guys
suggested: Straight ADO db connection with SQL Srever mirroring. But what to
hec to do with that connections string... is there any way to secure it
enough whitin the application on user machine to convince them? Or maybe
retrieve connection string with web service?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OifLlSQHIHA.4112@.TK2MSFTNGP05.phx.gbl...
> Well that depends on what you are doing. Asynchronous access to a database
> is not very useful for most user applications. If you are only talking
> about SQL Server as a back end to an app that just needs to load data such
> as a logging process this may be fine. But if it is a user interface app
> and the user expects to see data back these techniques don't work well.
> Don't get me wrong things like Service Broker have a definite place and
> work great for what they were designed to do. But you can't front end a
> database server with these tools and expect them to work like a database
> server. I just got off a large project where we had to completely rewrite
> an app that made extensive use of web services because it couldn't scale
> as written and simply was the wrong tool for the job in this case.
> Management needs to define what their goals are first and then decide what
> the best tools for the job are, not the other way around.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Andy" <kc2ine@.yahoo.com> wrote in message
> news:%23y6juKOHIHA.4880@.TK2MSFTNGP03.phx.gbl...
>
Now we didn't really recommend anything since we don't know your actual
requirements. Why not use Windows authentication and you don't have to worry
about showing any sensitive data.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Andy" <kc2ine@.yahoo.com> wrote in message
news:uLB18nQHIHA.4196@.TK2MSFTNGP04.phx.gbl...
> that's what I thought. I guess I'm going to recommend what you guys
> suggested: Straight ADO db connection with SQL Srever mirroring. But what
> to hec to do with that connections string... is there any way to secure it
> enough whitin the application on user machine to convince them? Or maybe
> retrieve connection string with web service?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OifLlSQHIHA.4112@.TK2MSFTNGP05.phx.gbl...
>
|||Andy,
I suggest taking the secure road here. Yes, the connection string is a
key to SQL Server content--it can unlock the door to the entire server and
all of its data or to a single closet that contains just the data your
application needs. I suggest the latter as a security strategy. The
ConnectionString is not going to be visible to the end user unless you give
them the source code. Yes, there are programs to decompile the program, but
those are easily defeated with a couple of techniques. There are also ways
to encrypt the ConnectionString supported in VS 2005. However, if the
ConnectionString credentials only grant access to the few stored procedures
or views that are used by the application, the exposure is minimal. Of
course, this assumes that the application is written using this approach.
I would be available to come talk to your management team about this and
other related issues. We could even do this over a conference call.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Andy" <kc2ine@.yahoo.com> wrote in message
news:uLB18nQHIHA.4196@.TK2MSFTNGP04.phx.gbl...
> that's what I thought. I guess I'm going to recommend what you guys
> suggested: Straight ADO db connection with SQL Srever mirroring. But what
> to hec to do with that connections string... is there any way to secure it
> enough whitin the application on user machine to convince them? Or maybe
> retrieve connection string with web service?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OifLlSQHIHA.4112@.TK2MSFTNGP05.phx.gbl...
>

MSMQ to SQL Service Broker possible?

Hopefully a simple question..
Is it possible to send messages from MSMQ (Microsoft Message Queue) to
SQL Service Broker in SQL Server 2005?
Also would like to return a result code from SQL Service Broker back
to MS MQ.
Not directly. You either have to write your own wrappers/translators or hunt for some gateway
product (which might or might not exist). I found some info at below, but you might want to Google
further.
http://www.codeguru.com/cpp/data/mfc_database/sqlserver/article.php/c12569/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JustWannaBeAbleToJamAndNotEmbarassMyself" <wisertime2006@.gmail.com> wrote in message
news:94ec6823-15d8-475c-9a90-4fac96cccfa6@.k37g2000hsf.googlegroups.com...
> Hopefully a simple question..
> Is it possible to send messages from MSMQ (Microsoft Message Queue) to
> SQL Service Broker in SQL Server 2005?
> Also would like to return a result code from SQL Service Broker back
> to MS MQ.
>
>
sql

MSMQ to SQL Service Broker possible?

Hopefully a simple question..
Is it possible to send messages from MSMQ (Microsoft Message Queue) to
SQL Service Broker in SQL Server 2005'
Also would like to return a result code from SQL Service Broker back
to MS MQ.Not directly. You either have to write your own wrappers/translators or hunt for some gateway
product (which might or might not exist). I found some info at below, but you might want to Google
further.
http://www.codeguru.com/cpp/data/mfc_database/sqlserver/article.php/c12569/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JustWannaBeAbleToJamAndNotEmbarassMyself" <wisertime2006@.gmail.com> wrote in message
news:94ec6823-15d8-475c-9a90-4fac96cccfa6@.k37g2000hsf.googlegroups.com...
> Hopefully a simple question..
> Is it possible to send messages from MSMQ (Microsoft Message Queue) to
> SQL Service Broker in SQL Server 2005'
> Also would like to return a result code from SQL Service Broker back
> to MS MQ.
>
>

msmq missing messages after applying SP3a to SQL server

Used to work fine:
(windows 200 server, sql server 2000, JDBC driver SP2.)
Two java processes, one sending messages (using jdbc and sql as temp storage), second is receiving and placing on msmq.

After installing SP3a for SQL server, under high load (200 a sec) there are missing messages in msmq.
Installed everything from scratch on separate machine, works fine until SP3a is applied.
Any help is highly appreciatedCheck for error messages on the box with SP3. M$ closed the hole for buffer overflow. JDBC by default is declaring @.Px as NVARCHAR(4000). If you are not handling TYPEing correctly, post-SP3 machine will post something like "Invalid TDS packet sent by client" error to the log.|||That would be where I look first. With sp3/3a microsoft modified quite a bit on the security side which has caused problems in other areas as well. Check your event logs and sql server logs. If nothing there, use sql profiler on the test machine and trap on "errors and warnings".|||actually, i belive it was a good thing they addressed this, but a bad thing the way they did it. now the bewildered and confused jdbc coders are looking out the window and wondering if the world's really ending or it's just their code...

MSMQ did not make MQIS database in SQL 2000

I believe this is why I am having a error at boot from the Service =
Control Manager:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7022
Date: 12/14/2004
Time: 1:54:04 AM
User: N/A
Computer: HESTER
Description:
The Microsoft Search service hung on starting.=20
It hangs but has started when I get to the desktop. This started =
immediately after I installed the Windows 2000
Component MSMQ. I did not do it with Routing and I lessened =
permissions. This on the domain controller.=20
According to this article:
http://support.microsoft.com/default...b;en-us;193510
I should have an ODBC System DSN called MQIS pointing to a database in =
SQL that was made by the
installation of MSMQ called MQIS. Well I have the DSN but I don't have =
the database. It was not made. SQL 2000 SP3. How do I fix this? Is =
there some kind of sp which I can run in Query Manager that will in fact =
make this database the MSMQ is expecting?
Thanks.
--=20
George Hester
_________________________________
Well that's the end of the Op sys. It's been hosed. Thanks MSMQ.
--=20
George Hester
_________________________________
"George Hester" <hesterloli@.hotmail.com> wrote in message =
news:u#DZi2a4EHA.2452@.TK2MSFTNGP14.phx.gbl...
I believe this is why I am having a error at boot from the Service =
Control Manager:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7022
Date: 12/14/2004
Time: 1:54:04 AM
User: N/A
Computer: HESTER
Description:
The Microsoft Search service hung on starting.=20
It hangs but has started when I get to the desktop. This started =
immediately after I installed the Windows 2000
Component MSMQ. I did not do it with Routing and I lessened =
permissions. This on the domain controller.=20
According to this article:
http://support.microsoft.com/default...b;en-us;193510
I should have an ODBC System DSN called MQIS pointing to a database in =
SQL that was made by the
installation of MSMQ called MQIS. Well I have the DSN but I don't have =
the database. It was not made. SQL 2000 SP3. How do I fix this? Is =
there some kind of sp which I can run in Query Manager that will in fact =
make this database the MSMQ is expecting?
Thanks.
--=20
George Hester
_________________________________

MSMQ did not make MQIS database in SQL 2000

I believe this is why I am having a error at boot from the Service = Control Manager:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7022
Date: 12/14/2004
Time: 1:54:04 AM
User: N/A
Computer: HESTER
Description:
The Microsoft Search service hung on starting.
It hangs but has started when I get to the desktop. This started = immediately after I installed the Windows 2000
Component MSMQ. I did not do it with Routing and I lessened = permissions. This on the domain controller. According to this article:
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;193510
I should have an ODBC System DSN called MQIS pointing to a database in = SQL that was made by the
installation of MSMQ called MQIS. Well I have the DSN but I don't have = the database. It was not made. SQL 2000 SP3. How do I fix this? Is = there some kind of sp which I can run in Query Manager that will in fact = make this database the MSMQ is expecting?
Thanks.
-- George Hester
_________________________________Well that's the end of the Op sys. It's been hosed. Thanks MSMQ.
-- George Hester
_________________________________
"George Hester" <hesterloli@.hotmail.com> wrote in message =news:u#DZi2a4EHA.2452@.TK2MSFTNGP14.phx.gbl...
I believe this is why I am having a error at boot from the Service =Control Manager:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7022
Date: 12/14/2004
Time: 1:54:04 AM
User: N/A
Computer: HESTER
Description:
The Microsoft Search service hung on starting.
It hangs but has started when I get to the desktop. This started =immediately after I installed the Windows 2000
Component MSMQ. I did not do it with Routing and I lessened =permissions. This on the domain controller. According to this article:
http://support.microsoft.com/default.aspx?scid=3Dkb;en-us;193510
I should have an ODBC System DSN called MQIS pointing to a database in =SQL that was made by the
installation of MSMQ called MQIS. Well I have the DSN but I don't have =the database. It was not made. SQL 2000 SP3. How do I fix this? Is =there some kind of sp which I can run in Query Manager that will in fact =make this database the MSMQ is expecting?
Thanks.
-- George Hester
_________________________________sql

MSMQ Connection Manager and input into a data flow

Firstly the disclaimer: im a total SSIS bunny.
Ok, with that out of the way...
What i want to do is have data read from an MSMQ message (which will be a simple XML message) and have it transformed and inserted into a simple table. Sound simple, but i cant work out how to do it.

I started a new Integration Services Project and dropped a Data Flow task on the Control flow tab, then double-click it to go to the data flow tab. I have added a MSMQ connection manager which points at my private queue and i can hit the test button and it sees it tests ok. How do i wire that into my data flow?

On my dataflow tab i have a SQL Server Destination (set to the local SQl server instance) and ive been trying a DataReader source and try setting the Advanced Editor "Connection Manager" column on IDBConnection row to point to my MSMQ connection manager. I get some error "cannot aquire a managed connection from the run-time connection manager". How do i get a Data Flow source which reads the XML message off my MSMQ connection manager?

On the Control Flow tab you can add a Message Queue Task and i can bind it to my MSMQ connection manager and set it up as a recieve task etc, but how do i use that?

Help!

Well, like yourself I am a complete MSMQ bunny but nevertheless....

You are right that the MSMQ Task will be of help here. I don't know much about it but I am guessing that it allows you to save the message to a Variable. Am I correct? If so you can do that and then manipulate the data that is in the variable.

That is as much as I know. Like I say I don't have any experience of using MSMQ with SSIS.

-Jamie

|||Hi, yer, i think i can save the message data to a variable, but im a bit lost on which component ill need to transform that message - the XML one would seem the logical choice but i cant seem to bind it to that variable.
|||

Hi

im new in msmQ TASK

I already configured the msmque task for sending. - > connected to dataflowtask -> msmque task for recieving is this the proper way to implement?

MSMQ Connection Manager and input into a data flow

Firstly the disclaimer: im a total SSIS bunny.
Ok, with that out of the way...
What i want to do is have data read from an MSMQ message (which will be a simple XML message) and have it transformed and inserted into a simple table. Sound simple, but i cant work out how to do it.

I started a new Integration Services Project and dropped a Data Flow task on the Control flow tab, then double-click it to go to the data flow tab. I have added a MSMQ connection manager which points at my private queue and i can hit the test button and it sees it tests ok. How do i wire that into my data flow?

On my dataflow tab i have a SQL Server Destination (set to the local SQl server instance) and ive been trying a DataReader source and try setting the Advanced Editor "Connection Manager" column on IDBConnection row to point to my MSMQ connection manager. I get some error "cannot aquire a managed connection from the run-time connection manager". How do i get a Data Flow source which reads the XML message off my MSMQ connection manager?

On the Control Flow tab you can add a Message Queue Task and i can bind it to my MSMQ connection manager and set it up as a recieve task etc, but how do i use that?

Help!

Well, like yourself I am a complete MSMQ bunny but nevertheless....

You are right that the MSMQ Task will be of help here. I don't know much about it but I am guessing that it allows you to save the message to a Variable. Am I correct? If so you can do that and then manipulate the data that is in the variable.

That is as much as I know. Like I say I don't have any experience of using MSMQ with SSIS.

-Jamie

|||Hi, yer, i think i can save the message data to a variable, but im a bit lost on which component ill need to transform that message - the XML one would seem the logical choice but i cant seem to bind it to that variable.
|||

Hi

im new in msmQ TASK

I already configured the msmque task for sending. - > connected to dataflowtask -> msmque task for recieving is this the proper way to implement?

MSMQ and SQL

Hi,
It is possible to poll MSMQ form SQL Server?
Thanks
DTS has a MSMQ task for this. See "DTS Designer, Message Queue Task
Properties" for more details in BooksOnLine.
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:3CF305EA-613B-4B3D-9AC6-BF2690A68B26@.microsoft.com...
> Hi,
> It is possible to poll MSMQ form SQL Server?
> Thanks

MSMQ and SQL

Hi,
It is possible to poll MSMQ form SQL Server?
ThanksDTS has a MSMQ task for this. See "DTS Designer, Message Queue Task
Properties" for more details in BooksOnLine.
--
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:3CF305EA-613B-4B3D-9AC6-BF2690A68B26@.microsoft.com...
> Hi,
> It is possible to poll MSMQ form SQL Server?
> Thanks

MSMQ and SQL

Hi,
It is possible to poll MSMQ form SQL Server?
ThanksDTS has a MSMQ task for this. See "DTS Designer, Message Queue Task
Properties" for more details in BooksOnLine.
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:3CF305EA-613B-4B3D-9AC6-BF2690A68B26@.microsoft.com...
> Hi,
> It is possible to poll MSMQ form SQL Server?
> Thankssql

MSMQ & Integration Services

I have a SSIS package in SQL Server 2005 that attempts to publish some data into a MSMQ Private queue located on another machine.

While I was creating the package the connection string was pointing to the local server something like: localhost\private$\csin

Now I'm trying to get to the remote machine but I'm not sure what type of connection string I need to enter:

I tried: mw59\private$\csin where mw59 is the name of the remote machine I need to publish to but that result into a "Invalid Queue Path Name."

I tried: formatname:DIRECT=OS:mw59\private$\csin but that results in "The specified format name does not support the requested operation...."

Any idea?

Thanks,

Thierry

I haven't tried MSMQ in SSIS yet but I know that in SQL 2000 DTS the standard MSMQ task could not access remote private queues. I got around this with a couple of fairly simple/generic VBS tasks.

One task reads a text file into the message body and sends the message to a remote private queue. The second taks reads messages from the queue and merges them into a single text file. The next step in this tasks uses the text file source connection to read the data from the text file into a SQL table.

Dick Campbell

|||

The next Web update of Books Online, scheduled to coincide with Service Pack 1, contains an already-written new topic titled "Sending to a Remote Private Message Queue with the Script Task." Sadly, as we all know, it is not a pretty sight when one attempts to copy and paste the HTML of a BOL topic. Here is my best effort after converting it to plain text:

Sending to a Remote Private Message Queue with the Script Task

Microsoft Windows Message Queuing makes it easy for developers to communicate with application programs quickly and reliably by sending and receiving messages. A message queue may be located on the local computer or a remote computer, and may be public or private. The Integration Services MSMQ Connection Manager and Message Queue Task do not support sending to a private queue on a remote computer. However, by using the Script task, it is easy to send a message to a remote private queue.

Description

The following example uses an existing MSMQ connection manager, together with objects and methods from the System.Messaging namespace, to send the text contained in a package variable to a remote private message queue. The call to the AcquireConnection method of the MSMQ connection manager returns a MessageQueueTask object whose Send method accomplishes this task.

To configure this Script Task example

Create an MSMQ Connection Manager with the default name. Set its path to a valid remote private queue, in the following format:

FORMATNAME:DIRECT=OS:<computername>\private$\<queuename>

Create an Integration Services variable named MessageText of type String to pass the message text into the script. Enter a default message as the value of the variable.

Add a Script Task to the design surface and edit it. On the Script tab of the Script Task Editor, add the MessageText variable to the ReadOnlyVariables property to make the variable available inside the script.

Click Design Script to open the Visual Studio for Applications script editor.

Add a reference in the script project to the System.Messaging namespace.

Replace the contents of the script window with the code in the following section.

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Messaging

Public Class ScriptMain

Public Sub Main()

Dim remotePrivateQueue As MessageQueue
Dim messageText As String

remotePrivateQueue = _
DirectCast(Dts.Connections("Message Queue Connection Manager").AcquireConnection(Dts.Transaction), _
MessageQueue)
messageText = DirectCast(Dts.Variables("MessageText").Value, String)
remotePrivateQueue.Send(messageText)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

-Doug

MSMQ

Does anyone know or have any idea what's the use of msmq or how can this apply in ssis.

Thanks

Find out what MQ is-

Message Queuing (MSMQ)
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/msmq/html/ff917e87-05d5-478f-9430-0f560675ece1.asp)

Find out about the MQ task you have in SSIS-

Message Queue Task
(http://msdn2.microsoft.com/en-us/library/ae1d8fad-6649-4e93-b589-14a32d07da33.aspx)

|||

Do you have a requirement to use MSMQ, or are you just curious about it? If you're just curious about it then google both terms and do some research.

If you have specific questions about either MSMQ or the MSMQ task in SSIS ask them here, and I will try my best to answer them for you.

|||

can you help me how am i going to use this the "MSMQ task in SSIS"? thanks

Sean Schade wrote:

Do you have a requirement to use MSMQ, or are you just curious about it? If you're just curious about it then google both terms and do some research.

If you have specific questions about either MSMQ or the MSMQ task in SSIS ask them here, and I will try my best to answer them for you.

|||

Can you help us and explain what you are trying to achieve? The task is there, documented, and does what it says on the tin. Do you want to send a message? Do you want to recieve a message?

Have you read any of the references or do you have any specific questions?

Do you know how to create a SSIS package?

Can you add a task to a package? (Hint - Drag and drop from the toolbox)

Now open the the task, and fill in the details required. What could be simpler?

How you use it in the context of a wider application or process is an impossible question on its own as we have no idea what you need to do!

|||

Hi All,

I am going to build an application that is transaction sensitive with asp.net, C# and SQL Server. There will be a folder which will have the text files and I am writing a windows service that picks up these text files and inserts them into SQL Server database. In case, if the SQL Server is down I should not lose any transactions and based on what I read I am thinking that MSMQ is the option wherein I can queue the transactions and insert them into the database once the SQL server is up and running.

Please suggest if this is correct approach or if there is any other alternatvie.

Thanks,

Thanvi

MSMQ

Can SQL server from a trigger or Stored procedure, post a
message to MSMQ?
Or can SQL server from a trigger or stored procedure, run
or fire a VBscript to do the same?Yes, using the sp_oamethod, haven't done it but there are a few sites out
there that walk through doing it, here's one.
http://www.tagconsulting.com/Show.asp?Id=1000
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Peter Upfold" <peter@.statmon.com> wrote in message
news:054001c36767$98c11930$a401280a@.phx.gbl...
> Can SQL server from a trigger or Stored procedure, post a
> message to MSMQ?
> Or can SQL server from a trigger or stored procedure, run
> or fire a VBscript to do the same?
>