Jeff, I would not suggest it.

I did it once and I end up in deadlock situation.

Command shell waits for transaction to be finished to start execute not rollable back command.

And...

Which problem?

There is no any problem except the one you created.

But you don't process data in SQL Server, as I can see.

You store it as you get it in free text. And when you need to get something from it...

I'm not attacking your solution.

I just don't see any point of purchasing SQL Server licenses if you are using this kind of solutuions.

What's gonna change if we make little change...

They are not opposite.

They are about different things.

For the approach we can see in Sergei's post there is no need in SQL Server. I don't see any of RDMS...

Which search do you mean?

Like Google?

There is no way. It must be proper database solution. 95% done on "storing strings" part, and 5% on "searching and retrieving" part.

Like 10000...

Good enough, you say?

Go to Google and start search by some long string.

Number of searched pages is definetely more than 10000, and result will be returned in less than a...

For this you need to use linked server.

Something like this:

sp_addlinkedserver [REMOTE_SERVER_NAME]

SELECT ReturnValue FROM OPENQUERY ( [REMOTE_SERVER_NAME] , 'Select [REMOTE_SERVER_NAME].Database.dbo.userdefinedfunction(@Param1) as ReturnValue' )

But actually calling UDF on remote server is not...

You know, Lee, I don't believe it could take 30 seconds to just pull some records and copy it to another table, even if that table is another database.

Unless these...

Actually this query is quite OK.

But you say you want an UPDATE and performing INSERT.

And your INSERT potentially creates duplications. It's not good.

It must be something like that:

UPDATE L

SET JobCostCenter...

Do you know anybody who is NOT in this situation?

I know 2 guys who definetely know the best practices on speeding up a text field search.

They used their knowledge to create Google.

May be ask them?

Trigger does not have parameters.

The only parameters for triggers are tables INSERTED and DELETED.

Only things trigger may reference from outside world are other tables in database.

So, there is no...

Is it scalar or table function?

Why not just try?