Do I need cursors?

I am creating a stored procedure to send emails (with xp_sendmail, I think)
based on certain conditions. This is my logic:
I need to loop through all of the Table1 records.
if Table2 exists for Table1 and Table2.column='T'
send variation 1 of email
else if table2 exists for table 1 and Table2.column='F'
send variation 2 of email
else if table3 exists for table 1
send variation 3 of email
Should I use a cursor to loop through the Table1 records? Or should I join
Table1 and Table2/Table3 and not have a top level query? I'm thinking I
should not use a cursor.
My second problem is this. Depending on the email variation I need to loop
through some records and concatenate their values. Do I need a cursor for
that? Or do I have other options?
Thanks for any help, I really appreciate it.Nick
DECLARE @.EmailName VARCHAR(100),@.userid VARCHAR(20)
IF EXISTS (SELECT * FROM Table2 JOIN Table1 ON Table2.pk=Table1.pk AND
Table2.column='T')

> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
SET @.userid='john,arie,alex'
SELECT @.EmailName=@.EmailName+COALESCE(Emailadd,
'') +',' FROM users where
CHARINDEX(',' + userid + ',',','+ @.userid +',')>0 and EmailName IS NOT
NULL
SET @.EmailName=LEFT(@.EmailName,LEN(@.EmailNam
e)-1)
--Send emails
I'm currently unable to test it but I'm sure it gives you an idea.
"Nick" <nickfinity@.nospam.nospam> wrote in message
news:48C35529-4878-455F-9DCC-F97057A4A5B8@.microsoft.com...
>I am creating a stored procedure to send emails (with xp_sendmail, I think)
> based on certain conditions. This is my logic:
> I need to loop through all of the Table1 records.
> if Table2 exists for Table1 and Table2.column='T'
> send variation 1 of email
> else if table2 exists for table 1 and Table2.column='F'
> send variation 2 of email
> else if table3 exists for table 1
> send variation 3 of email
> Should I use a cursor to loop through the Table1 records? Or should I join
> Table1 and Table2/Table3 and not have a top level query? I'm thinking I
> should not use a cursor.
> My second problem is this. Depending on the email variation I need to loop
> through some records and concatenate their values. Do I need a cursor for
> that? Or do I have other options?
> Thanks for any help, I really appreciate it.|||>From the limited information you've posted, it sounds like a CASE
statement is what you're looking for. In general, the only time I ever
find that I must use a cursor is when I have to call a stored procedure
on each value in a resultset. They seem to be much more useful to me in
ad hoc situations than in deployed solutions.
CASE Example in an UPDATE (sorry for the poor formatting):
UPDATE <tablealias1>
SET EmailAddress = CASE WHEN <condition1> THEN <expression>
WHEN <condition2> THEN <expression> ELSE <expression> END
FROM Table1 <tablealias1> INNER JOIN Table2 ON <...>
WHERE <condition>

Distributed transaction error

We have a server, for various reasons has link to a loop back linked server.
We have dozens of stored procedures that refer to the linked server in their
code. They have worked fine for several years until a developer added an on
update/insert trigger to a table. Now, when these stored procedures execute
,
there is a distritbuted transaction error. Distributed Transaction
Coordinator is verified as being on.
Does anyone know of a way to fix this or what is going on?
Thank you.For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
option XACT_ABORT should also be on to support modifications in a
distributed transaction.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Distributed transactions are not supported on loopback linked servers! If
itrigger is requred on a table and references another database on the same
server, hardcode the full 3 part name, ommiting server name.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
> We have a server, for various reasons has link to a loop back linked
> server.
> We have dozens of stored procedures that refer to the linked server in
> their
> code. They have worked fine for several years until a developer added an
> on
> update/insert trigger to a table. Now, when these stored procedures
> execute,
> there is a distritbuted transaction error. Distributed Transaction
> Coordinator is verified as being on.
> Does anyone know of a way to fix this or what is going on?
> Thank you.|||Thanks for the advice, however, we have tested it with XACT_ABORT set to ON.
One of our database guru's mentioned:
--start quote--
“SQL Server still sees the all the involved statements as a single
transaction and because it have a call to the linked server, it tries to
initiates it as distributed transaction.”
I read in the sql server docs that whenever two databases are involved, sql
server treats the transaction as a distributed transaction even if the
databases are on the same box
--end quote--
Example: our stored procedure has a query similar to the following:
sql1 is the loop back linked server, the database resides ON the same box,
however, for various reasons we have left the sp to reference it as a linked
server.
update mytable
set mytable.column1 = 1
where NOT EXISTS
(select id from sql1.dbo.mytable2 p where p.column = mytable.column)
The following error message is generated whenever the trigger on
update/insert is active on the table mytable. We remove the trigger and the
SP works as always:
error msg: The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
Thanks for any help you can provide.
"Itzik Ben-Gan" wrote:

> For SQL Server's OLEDB provider (SQLOLEDB), I believe that the session
> option XACT_ABORT should also be on to support modifications in a
> distributed transaction.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||I appreciate your response Farmer,
however, the trigger itself has no reference at all to the loopback linked
server. It is trying to update a simple field in a table on the same server
as the trigger. The error happens in the SP, which has worked for several
years. If you remove the trigger, the SP works again.
"Farmer" wrote:

> Distributed transactions are not supported on loopback linked servers! If
> itrigger is requred on a table and references another database on the same
> server, hardcode the full 3 part name, ommiting server name.
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:EF38347D-6147-407B-A86F-AC0C326AD168@.microsoft.com...
>
>|||Any data modification statements BEGIN an implisit transactions, that is why
selects worked and update fails now. Because it is involving linked server,
it is considered distributed.
BOL
Starting Transactions
You can start transactions in Microsoft SQL ServerT as explicit,
autocommit, or implicit transactions.
Explicit transactions
Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.
Autocommit transactions
This is the default mode for SQL Server. Each individual Transact-SQL
statement is committed when it completes. You do not have to specify any
statements to control transactions.
Implicit transactions
Set implicit transaction mode on through either an API function or the
Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement
automatically starts a new transaction. When that transaction is completed,
the next Transact-SQL statement starts a new transaction.
Connection modes are managed at the connection level. If one connection
changes from one transaction mode to another it has no effect on the
transaction modes of any other connection.
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:A8695506-E70A-4412-8417-97E91699C2A0@.microsoft.com...
> Thanks for the advice, however, we have tested it with XACT_ABORT set to
> ON.
> One of our database guru's mentioned:
> --start quote--
> "SQL Server still sees the all the involved statements as a single
> transaction and because it have a call to the linked server, it tries to
> initiates it as distributed transaction."
> I read in the sql server docs that whenever two databases are involved,
> sql
> server treats the transaction as a distributed transaction even if the
> databases are on the same box
> --end quote--
> Example: our stored procedure has a query similar to the following:
> sql1 is the loop back linked server, the database resides ON the same box,
> however, for various reasons we have left the sp to reference it as a
> linked
> server.
> update mytable
> set mytable.column1 = 1
> where NOT EXISTS
> (select id from sql1.dbo.mytable2 p where p.column = mytable.column)
> The following error message is generated whenever the trigger on
> update/insert is active on the table mytable. We remove the trigger and
> the
> SP works as always:
> error msg: The operation could not be performed because the OLE DB
> provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
>
> Thanks for any help you can provide.
>
> "Itzik Ben-Gan" wrote:
>