How to change the content?

Hi there !

I'm quite desperate as I can't find a quick method to partially change the content of a field.

I explain:
In a field named 'notes', PART of the content is:
<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>
This content is wrong.

It must be:
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>

How can I make the change with a SQL Query instead of manually make the change row by row (approximately 1500 rows contain the error!!!) considering the fact that abc@.domain.com is different in each of the rows?

Many thanks in advance for any help!

Best regards,

Gerardupdate daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'|||update daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'
Hi !

Thank you for replying. I appreciate.
But, as I'm quite old and learning, I want to really understand the syntax I'll write.

Some questions arise:
1 - Why have we "replace" repeated twice?
2 - The single and double quotes you wrote in your example doesn't correspond to those I put in my example.
3 - What will happen to the email addresses which are differents?

Be sure, professor, I certainly do not want to bother you! :-)

Best regards,

Gerard|||did you try it? what results did it give?|||Yes, I tried... on a copy of the table!
After two attempts, this worked fine:
UPDATE `table_name` SET column_name = REPLACE(column_name, '"', '') WHERE column_name LIKE '%href=\'mailto:"%';

I thank you for your valuable help.

Best.|||all you have done is remove the double quotes

in other words, you've changed this --<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>to this --<a href='mailto:abc@.domain.com'>abc@.domain.com</a>which is not what you asked

you asked for this --<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>|||YES you are right about the kind of quotes but the principle remained the same: remove the extra quotes just before and after the address, no matter if the quotes are single or double. I opted for the single quotes which are more "orthodox"! :-)|||either single or double can be used

see http://www.w3.org/TR/html4/intro/sgmltut.html#attributes

if you look at all the web sites out there (okay, just look at some, you don't really have to look at all of them) you will find that most of them use double quotes

also, the incidence of a single quote required inside a double-quoted string is more frequent than a double quote inside a single-quoted string

:)|||You are right.
I have read about attributes in the page you mentioned.
And I tried to follow your advice and to replace the single quotes with double quotes.

For example, I tried to replace where it is necessary
<a href='mailto:abc@.domain.com'>
with
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">
(Of course, this text isn't the only text contained in the field...)

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '', "") WHERE column_name LIKE '%mailto:\'%';
This query has no effect!

What's wrong?|||REPLACE(column_name, '''', '"')|||Still no effect !
And I perfectly know that more than a thousand rows must be affected...

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:\'%';|||you keep messing up in different places
WHERE column_name LIKE '%mailto:''%'|||Maybe it's because I'm French but I don't understand what you mean: "you keep messing up in different places".

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:''%';
...and nothing happens, right or wrong!|||what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:''%'and what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:"%'this should tell you why

:)|||Both queries return zero !!!!!

Which isn't true !
Here is the copy of the REAL full content of the field in one the rows:
Autres adresses de courriel : <a href='mailto:jr.reverte@.laposte.fr'><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href='mailto:entsoa23@.fr.st'><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>

and I want to change it to:
Autres adresses de courriel : <a href="http://links.10026.com/?link=mailto:jr.reverte@.laposte.fr"><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href="http://links.10026.com/?link=mailto:entsoa23@.fr.st"><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>

This is driving me quite mad... :-) GRRRR !!!|||i believe you are not copying my exact code, but rather typing it in yourself, and making errors while typing

you must make very sure that you understand the difference between two single quotes in a row:''and a doublequote:"|||Obviously, yes. I know the difference.
And I copied the exact code you posted.

But I found one error.
It isn't
'%mailto:\'%'
but
'%\'mailto:%'
we are looking for.

The position of the quote was wrong.
And this change makes the query returning a number!

Using this change, I unsuccessfully (no row affected) tried to apply the modifying query this way (real names of table and column):
UPDATE `_annuairecopy` SET notes = REPLACE ( notes, '', "") WHERE notes LIKE 'href=%\'mailto:%\'%';
(please report to the REAL content of field I previously posted)|||please stop using those horrible backslashes!!!
UPDATE `_annuairecopy`
SET notes = REPLACE ( notes, '''', '"')
WHERE notes LIKE '%href=''mailto:%'|||Yes indeed, they are horrible.
But I was so tired to get error messages each time I used an even number of single quotes without a backslash!

The query has worked fine !!!
Could you imagine how happy I am?

I thank you for your patience and all the time you spent helping me.

Best from Gerard.

how to change "Default language for user" for Instance name? (with regedit or other method

Hi all,
how to change "Default language for user" for Instance name? (with regedit
or other method?)
I want to change other language for my default language for user option
programaticly (in setup program)
ThanxAt the instance level you use sp_configure 'default language' specifying the
langid you want (this value can be obtained from the syslanguages table in
master)
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Asking" <asking@.ispro.net.tr> wrote in message
news:OybHe5moGHA.3808@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> how to change "Default language for user" for Instance name? (with regedit
> or other method?)
> I want to change other language for my default language for user option
> programaticly (in setup program)
>
> Thanx
>|||Thank you very much
"Asking" <asking@.ispro.net.tr> wrote in message
news:OybHe5moGHA.3808@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> how to change "Default language for user" for Instance name? (with regedit
> or other method?)
> I want to change other language for my default language for user option
> programaticly (in setup program)
>
> Thanx
>

How to call ReportViewer Refresh method?

I have a web form with a textbox that contains a parameter for a report defined in the ReportViewer control. I want to refresh the report when the user clicks on my refresh button. I can't find the Refresh method. It works if I click on the reviewer's refresh button, but my users need to see that big button that says Refresh. Any ideas of how to call the refresh method? ThanksFound it.

Me.ReportViewer1.LocalReport.Refresh()

How to call .NET components in SQL Server

Hi,
Can any guide me, how to register .NET component in SQL Server 2000 and call
the method.
Thanks & Regards
V.Edayachandran
You cannot call a .NET component from SQL Server 2000 directly. Either you
need to use an extended stored procedure wrapper to call it or try to create
a CCW for the component and then invoke the same using sp_OA* procedures.
Remember these are NOT supported by MS though they work ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"EDA" <EDA@.discussions.microsoft.com> wrote in message
news:DE695846-A575-4B0F-B4C9-E4FCBD5F559B@.microsoft.com...
> Hi,
> Can any guide me, how to register .NET component in SQL Server 2000 and
call
> the method.
> Thanks & Regards
> V.Edayachandran
|||To add to Vinod's response, here's a link to an MSKB article on the subject:
http://support.microsoft.com/default...;en-us;322884.
You'll have a lot more options in SQL 2005, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"EDA" <EDA@.discussions.microsoft.com> wrote in message
news:DE695846-A575-4B0F-B4C9-E4FCBD5F559B@.microsoft.com...
> Hi,
> Can any guide me, how to register .NET component in SQL Server 2000 and
> call
> the method.
> Thanks & Regards
> V.Edayachandran
|||"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cuadre$1f6$1@.news01.intel.com...
> You cannot call a .NET component from SQL Server 2000 directly. Either you
> need to use an extended stored procedure wrapper to call it or try to
> create
> a CCW for the component and then invoke the same using sp_OA* procedures.
While it's not supported to call .NET components directly from SQL Server,
indirect methods which don't load the CLR into the SQL Server process are
OK. So you can use the MSXML2.ServerXMLHTTP COM library to invoke a .NET
web service, or you can install your COM-Callable .NET component in COM+ as
a COM+ server application. In the first case you are loading an unmanaged
COM library in the SQL Server process, and in the second, COM is supplying
an unmanaged proxy for the SQL Server process and remoting calls over to a
COM+ process where the CLR and and your component are loaded.
David

How to call .NET components in SQL Server

Hi,
Can any guide me, how to register .NET component in SQL Server 2000 and call
the method.
Thanks & Regards
V.EdayachandranYou cannot call a .NET component from SQL Server 2000 directly. Either you
need to use an extended stored procedure wrapper to call it or try to create
a CCW for the component and then invoke the same using sp_OA* procedures.
Remember these are NOT supported by MS though they work ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"EDA" <EDA@.discussions.microsoft.com> wrote in message
news:DE695846-A575-4B0F-B4C9-E4FCBD5F559B@.microsoft.com...
> Hi,
> Can any guide me, how to register .NET component in SQL Server 2000 and
call
> the method.
> Thanks & Regards
> V.Edayachandran|||To add to Vinod's response, here's a link to an MSKB article on the subject:
http://support.microsoft.com/default.aspx?scid=kb;en-us;322884.
You'll have a lot more options in SQL 2005, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"EDA" <EDA@.discussions.microsoft.com> wrote in message
news:DE695846-A575-4B0F-B4C9-E4FCBD5F559B@.microsoft.com...
> Hi,
> Can any guide me, how to register .NET component in SQL Server 2000 and
> call
> the method.
> Thanks & Regards
> V.Edayachandran|||"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cuadre$1f6$1@.news01.intel.com...
> You cannot call a .NET component from SQL Server 2000 directly. Either you
> need to use an extended stored procedure wrapper to call it or try to
> create
> a CCW for the component and then invoke the same using sp_OA* procedures.
While it's not supported to call .NET components directly from SQL Server,
indirect methods which don't load the CLR into the SQL Server process are
OK. So you can use the MSXML2.ServerXMLHTTP COM library to invoke a .NET
web service, or you can install your COM-Callable .NET component in COM+ as
a COM+ server application. In the first case you are loading an unmanaged
COM library in the SQL Server process, and in the second, COM is supplying
an unmanaged proxy for the SQL Server process and remoting calls over to a
COM+ process where the CLR and and your component are loaded.
David

How to call .NET components in SQL Server

Hi,
Can any guide me, how to register .NET component in SQL Server 2000 and call
the method.
Thanks & Regards
V.EdayachandranYou cannot call a .NET component from SQL Server 2000 directly. Either you
need to use an extended stored procedure wrapper to call it or try to create
a CCW for the component and then invoke the same using sp_OA* procedures.
Remember these are NOT supported by MS though they work ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"EDA" <EDA@.discussions.microsoft.com> wrote in message
news:DE695846-A575-4B0F-B4C9-E4FCBD5F559B@.microsoft.com...
> Hi,
> Can any guide me, how to register .NET component in SQL Server 2000 and
call
> the method.
> Thanks & Regards
> V.Edayachandran|||To add to Vinod's response, here's a link to an MSKB article on the subject:
http://support.microsoft.com/defaul...b;en-us;322884.
You'll have a lot more options in SQL 2005, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"EDA" <EDA@.discussions.microsoft.com> wrote in message
news:DE695846-A575-4B0F-B4C9-E4FCBD5F559B@.microsoft.com...
> Hi,
> Can any guide me, how to register .NET component in SQL Server 2000 and
> call
> the method.
> Thanks & Regards
> V.Edayachandran|||"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cuadre$1f6$1@.news01.intel.com...
> You cannot call a .NET component from SQL Server 2000 directly. Either you
> need to use an extended stored procedure wrapper to call it or try to
> create
> a CCW for the component and then invoke the same using sp_OA* procedures.
While it's not supported to call .NET components directly from SQL Server,
indirect methods which don't load the CLR into the SQL Server process are
OK. So you can use the MSXML2.ServerXMLHTTP COM library to invoke a .NET
web service, or you can install your COM-Callable .NET component in COM+ as
a COM+ server application. In the first case you are loading an unmanaged
COM library in the SQL Server process, and in the second, COM is supplying
an unmanaged proxy for the SQL Server process and remoting calls over to a
COM+ process where the CLR and and your component are loaded.
David

How to build dynamic Xquery

Hello

I am trying to use the xml.query() method to output xml. Is there any way of storing the xqueries themselves in the database?

This works:

SELECT Col.query('
<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>
')
FROM xmltest where id = 1

but this doesn't:

declare @.xquery nvarchar(max)
set @.xquery = '<Root>
<Header>
{
for $e in Report/PolicyBatchRef
return $e/PolicyBatchRef
}
</Header>
<NewElement>
{
for $e in Report/PolicyBatchRef/Locations/Location
return $e
}
</NewElement>
</Root>'
SELECT col.query(@.xquery) from xmltest where id = 1

I get the error

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the xml data type method "query" must be a string literal.

Same thing happens when I store the xquery in the DB.

Any ideas?

Thanks very much

The string argument to the query function must be a string literal, so you cannot pass it in as a parameter. You have a few options:

1. Create UDF's that encapsulate the SELECT and the xquery, and invoke these.

2. Store strings that represent the SELECT and the xquery and invoke them at runtime using sp_executesql

3. store the strings that represent the xquery and combine them with the strings for the SELECT statement, and execute with sp_executesql. This has the most potential for SQL injection since you are constructing SQL dynamically with string concatenation. This method should be used only if the other two ways cannot be used.

|||

You can dynamically create XPath queries using sql variables.

I have successfully used something like

declare @.Date varchar(10)

set @.Date = replace(convert(varchar(10), getdate(), 121), '-', '')

WITH XMLNAMESPACES( 'https://www,somewhere.com/Bureau' AS "Bureau")

SELECT

AggDefaultAmount = convert(varchar(50), ResponseXML.query('sum(/BureauResponse/Bureau:ND07/Bureau:ND07/Bureau:Amount[../Bureau:InformationDate<sql:variable("@.Date")])'))

FROM

DB..testxml WITH (NOLOCK)

Adapt adopt and improve.

|||

Hi,

I need to a have a Store procedure that takes xml as input and it stores in my database tables. Using the nodes() method and value() method, I am able to solve this but only issue I have is these methods take arguments only as string literals. So, I have to hard code the xquery in the SP. I expect to read the xquery from a table and fetch it in a variable within the SP and pass it as the parameter to the Value() and nodes() method.

Please advice.

Sample code I have as below

declare @.xmldoc xml

SET @.xmldoc = '<customer><name>John</name><city>New York</city></customer>'

--This select works

SELECT

T.C.value('name[1]',varchar(50))

T.C.value('city[1]',varchar(50))

FROM @.xmldoc.nodes('/customer') AS T(C)

--But this does not work when I try to specify xquery using a variable as below

declare @.xquery_name varchar(100),@.xquery_city varchar(100), @.xquery_cust varchar(100)

SELECT @.xquery_name = 'name[1]', @.xquery_city = 'city[1]', @.xquery_cust = '/customer'

SELECT

T.C.value(@.xquery_name ,varchar(50))

T.C.value('@.xquery_city',varchar(50))

FROM @.xmldoc.nodes(@.xquery_cust) AS T(C)

Please help me out.