OPENQUERY-1

Question

OPENQUERY-1

Carlo Romagnano

SSC-Insane

Points: 22597
More actions
March 15, 2014 at 4:55 pm

#284597

Comments posted to this topic are about the item OPENQUERY-1
March 17, 2014 at 2:14 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Bit tricky, like the questions. Thanks, Carlo.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Thats a very nice question to kick start your monday 🙂

no lazy starters

Mikael Eriksson SE SSCommitted Points: 1706 More actions · Answer 3

I guess the reason for the rollback is that the output clause is not allowed when you do remote queries.

Using a query like this will show the the generated error message.

delete from openquery(MyServer, 'select * from tempdb.dbo.a') output deleted.*;

Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 4

Mikael Eriksson SE (3/17/2014)
I guess the reason for the rollback is that the output clause is not allowed when you do remote queries.
Using a query like this will show the the generated error message.
delete from openquery(MyServer, 'select * from tempdb.dbo.a') output deleted.*;
Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

The OPENQUERY start a NEW connection and the first implicit statement is BEGIN TRAN. On disconnect a ROLLBACK is done. There is not error. Your statement is different: A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 5

Stewart "Arturius" Campbell (3/17/2014)
Interesting question, thanks
wouldn't the correct way to handle deletions from a remote server be
DELETE OPENQUERY(MyServer,'SELECT * FROM tempdb.dbo.a')

In this case, you haven't the DELETED.* resultset.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

Interesting one, thank you for the post, Carlo. 🙂

(till now... great discussion on the matter)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

A very interesting question. Good way to start the week. Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Thomas Abraham SSChampion Points: 10761 More actions · Answer 8

Thanks for the question, Carlo.

Don't know which is more interesting - the option for -1 rows, or the fact that 3% selected it as their answer!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 9

Thomas Abraham (3/17/2014)
Thanks for the question, Carlo.
Don't know which is more interesting - the option for -1 rows, or the fact that 3% selected it as their answer!

:-D:-D:-D:-D

Unbelievable, but true!

Mikael Eriksson SE SSCommitted Points: 1706 More actions · Answer 10

Carlo Romagnano (3/17/2014)
Mikael Eriksson SE (3/17/2014)
I guess the reason for the rollback is that the output clause is not allowed when you do remote queries.
Using a query like this will show the the generated error message.
delete from openquery(MyServer, 'select * from tempdb.dbo.a') output deleted.*;
Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.
The OPENQUERY start a NEW connection and the first implicit statement is BEGIN TRAN. On disconnect a ROLLBACK is done. There is not error. Your statement is different: A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

So if the rollback is not caused by an error then why is there a rollback?

Steven.Howes SSCertifiable Points: 6230 More actions · Answer 11

I really have to try answering these later in the day(when I'm more awake). I choose '1'. I didn't realize that I was suppose to choose the number of rows. Being honest, I still don't know if I would have guessed '2'

Great question.

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 12

Mikael Eriksson SE (3/17/2014)
Carlo Romagnano (3/17/2014)
Mikael Eriksson SE (3/17/2014)
I guess the reason for the rollback is that the output clause is not allowed when you do remote queries.
Using a query like this will show the the generated error message.
delete from openquery(MyServer, 'select * from tempdb.dbo.a') output deleted.*;
Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.
The OPENQUERY start a NEW connection and the first implicit statement is BEGIN TRAN. On disconnect a ROLLBACK is done. There is not error. Your statement is different: A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.
So if the rollback is not caused by an error then why is there a rollback?

When you DISCONNECT a session from SQLSERVER and one transaction is active an implicit ROLLBACK is done. OPENQUERY creates a NEW connection to the server and then disconnects before returning the resultset.

timwell SSCertifiable Points: 5315 More actions · Answer 13

I have a question.

I got crossed up because the documentation says that sp_serveroption and openquery are for connecting to remote or linked servers. I was thinking the delete was happening on some theoretical remote server that may or may not have a tempdb.dbo.a table.

So in the example, is it connecting back to the server you are running the query from as if it is a "remote" server?