QUOTENAME Problem

I have following four cases

Code Snippet

1) select QUOTENAME (QUOTENAME ('ABCD', ''''),'''' )
Output: '''ABCD'''

Code Snippet

2) select QUOTENAME (QUOTENAME ('ABCD', ''''),']' )
Output: ['ABCD']

Code Snippet

3) select QUOTENAME (QUOTENAME ('ABCD', ''''),'"' )
Output: "'ABCD'"

Code Snippet

4) select QUOTENAME (QUOTENAME ('ABCD', '"'),'"' )
Output: """ABCD"""

Now my questions is second & three outputs are fine, but what happen to the first & forth one. I want single quote twice around string(in first case) and double quote twice around string(in forth case) but it gives me three single/double quotes, WHY ?

Gurpreet S. Gill

There was a recent post here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1537605&SiteID=1

In which I noted this behavior in Louis Davidson's example. This behavior also appears in books online so it is intended and you will need to account for it in your coding.

|||

Sorry, but I can not see what your concern is. Let me try to explain 1 and 4.

1 - select QUOTENAME (QUOTENAME ('ABCD', ''''),'''' )

The first call to the function will produce 'ABCD', correct?. Ther second call has to wrap the string 'ABCD' between aposthopes (including the existing aposthropes), so in order to wrap an aposthrope between apostropes you have to double the inner apostrophe. The result of the second call will be '''ABCD''', where the inner apostrophes were double. Let us put some spaces to differentiate them.

' ''ABCD'' '

2 - select QUOTENAME (QUOTENAME ('ABCD', '"'),'"' )

The same as in number 1, but this time both wrap will be between the double qoute ("). The inner double quote should be double and wrap them between double quote.

" ""ABCD"" "

We need to scape the character inside the string and we do it doubling the character being used, in those cases apostrophe and double quote. Let us see what happend if we decide to do the same with ']'.

select quotename(quotename('ABCD', ']'), ']')

Result:

[[ABCD]]]

The behavior with ']' is different compare with apostrophe or double quote, just the most right is doubled.

Hope my english does not get you dizzy.

AMB

queueing xml output to msmq

does anyone have an idea how can i queue xml output to msmq from the sql
query itselfSee KB article
http://support.microsoft.com/defaul...kb;en-us;555070
Sending a message to MSMQ from SQL requires writing a COM object and then
calling that COM object from a T-SQL procedure.
Mike
"kamal" <kamal@.discussions.microsoft.com> wrote in message
news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
> does anyone have an idea how can i queue xml output to msmq from the sql
> query itself
>|||I think in most cases you will find using an external application to
retrieve the XML and then send the MSMQ message will be more efficient
because the stored procedure will have to open the connection to MSMQ every
time it runs.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
> See KB article
> http://support.microsoft.com/defaul...kb;en-us;555070
> Sending a message to MSMQ from SQL requires writing a COM object and then
> calling that COM object from a T-SQL procedure.
> Mike
> "kamal" <kamal@.discussions.microsoft.com> wrote in message
> news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
>|||I've got a synchronization application (toned-down version of replication
with a few special requirements in it) that does that, but eliminates
polling by the application. I wrote an extended stored proc that sets a
named Windows event whenever another process needs to act. Code in a trigger
(in my case) calls the extended proc to set the event. The other process
waits on the named Windows event; when the event is fired, it queries the
database and does what it needs to do, which in this case actually happens
to be sending messages via MSMQ. You can then do all the performance
optimizations in the app, like Roger was saying, with the added benefit of
not having to poll. Of course, the drawback is that you are using a custom
extended procedure.
Mike
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:eWex$gRVFHA.928@.TK2MSFTNGP15.phx.gbl...
> I think in most cases you will find using an external application to
> retrieve the XML and then send the MSMQ message will be more efficient
> because the stored procedure will have to open the connection to MSMQ
every
> time it runs.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
then
sql
>

queueing xml output to msmq

does anyone have an idea how can i queue xml output to msmq from the sql
query itself
See KB article
http://support.microsoft.com/default...b;en-us;555070
Sending a message to MSMQ from SQL requires writing a COM object and then
calling that COM object from a T-SQL procedure.
Mike
"kamal" <kamal@.discussions.microsoft.com> wrote in message
news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
> does anyone have an idea how can i queue xml output to msmq from the sql
> query itself
>
|||I think in most cases you will find using an external application to
retrieve the XML and then send the MSMQ message will be more efficient
because the stored procedure will have to open the connection to MSMQ every
time it runs.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
> See KB article
> http://support.microsoft.com/default...b;en-us;555070
> Sending a message to MSMQ from SQL requires writing a COM object and then
> calling that COM object from a T-SQL procedure.
> Mike
> "kamal" <kamal@.discussions.microsoft.com> wrote in message
> news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
>
|||I've got a synchronization application (toned-down version of replication
with a few special requirements in it) that does that, but eliminates
polling by the application. I wrote an extended stored proc that sets a
named Windows event whenever another process needs to act. Code in a trigger
(in my case) calls the extended proc to set the event. The other process
waits on the named Windows event; when the event is fired, it queries the
database and does what it needs to do, which in this case actually happens
to be sending messages via MSMQ. You can then do all the performance
optimizations in the app, like Roger was saying, with the added benefit of
not having to poll. Of course, the drawback is that you are using a custom
extended procedure.
Mike
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:eWex$gRVFHA.928@.TK2MSFTNGP15.phx.gbl...
> I think in most cases you will find using an external application to
> retrieve the XML and then send the MSMQ message will be more efficient
> because the stored procedure will have to open the connection to MSMQ
every
> time it runs.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
then[vbcol=seagreen]
sql
>

Queue Reader failed for Transactional Repl

Hello,
I have set up queued updating Transactional Repl. The Queue Reader keeps
failing and cannot start up any more. The output shows the following error
"cannot have more than one instance of queue reader agent for the
distribution database". What does this error message mean? Please help.
Thanks in advances
Please can you post up the complete error message, including the error
number?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for your reply. Finally I solved by shutting down the queue reader
exe on the task manager. I thought the exe would not run if the Queue Reader
fails. But it is cluster environment and it might be a bug somewhere with the
Queue Reader exe. After I shut down the process, restarting Queue Reader
agent worked fine.
have a good one,
FJY
"Paul Ibison" wrote:

> Please can you post up the complete error message, including the error
> number?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>