OPENQUERY-1

Question

OPENQUERY-1

Viewing 15 posts - 16 through 30 (of 31 total)

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

Carlo Romagnano SSC-Insane Points: 22667 More actions · Answer 1

timwell (3/17/2014)
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?

yes.

exec sp_serveroption @server = MyServer,@optname = 'DATA ACCESS',@optvalue = 'TRUE'

Carlo Romagnano SSC-Insane Points: 22667 More actions · Answer 2

These queries demonstrate the two different connection:

select @@SPID

SELECT * FROM OPENQUERY(MyServer,'select @@SPID')

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 3

PChiragS

SSCarpal Tunnel

Points: 4965

March 17, 2014 at 2:47 pm

#1697781

nice question..

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

Steven.Howes (3/17/2014)
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.

+1

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

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!

-1 😀

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 6

[Incorrect answers: 80%], hmmm interesting.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Carlo Romagnano SSC-Insane Points: 22667 More actions · Answer 7

Hany Helmy (3/18/2014)
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!
-1 😀

Nooooooooooooo! 😀

Always the minus Number One!

Carlo Romagnano SSC-Insane Points: 22667 More actions · Answer 8

crussell-931424 (3/18/2014)
It's still a little confusing.

About?

Marcia J SSCertifiable Points: 5649 More actions · Answer 9

I'm not sure I completely understand this.

Is this what's going on?

-The query initially adds a single row with a value of 1.

- The open query returns the deleted set which is a single row with a value of 1; however, the delete is rolled back so the initial row stays there.

- The 'deleted' set is then inserted into a, so it ends up with 2 rows with a value of 1.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 10

Interesting Q, thanks

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Carlo Romagnano SSC-Insane Points: 22667 More actions · Answer 11

marcia.j.wilson (3/18/2014)
I'm not sure I completely understand this.
Is this what's going on?
-The query initially adds a single row with a value of 1.
- The open query returns the deleted set which is a single row with a value of 1; however, the delete is rolled back so the initial row stays there.
- The 'deleted' set is then inserted into a, so it ends up with 2 rows with a value of 1.

Exactly!

Marcia J SSCertifiable Points: 5649 More actions · Answer 12

Carlo Romagnano (3/19/2014)
marcia.j.wilson (3/18/2014)
I'm not sure I completely understand this.
Is this what's going on?
-The query initially adds a single row with a value of 1.
- The open query returns the deleted set which is a single row with a value of 1; however, the delete is rolled back so the initial row stays there.
- The 'deleted' set is then inserted into a, so it ends up with 2 rows with a value of 1.
Exactly!

Thanks for the confirmation. It took me a while to get my brain wrapped around this and even then I wasn't sure I was right.

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Hany Helmy (3/18/2014)
[Incorrect answers: 80%], hmmm interesting.

About 36 hours later and the error rate is down to 70%. But still 3% selecting the answer "-1". Definitely interesting.

Tom

Marcia J SSCertifiable Points: 5649 More actions · Answer 14

TomThomson (3/19/2014)
Hany Helmy (3/18/2014)
[Incorrect answers: 80%], hmmm interesting.
About 36 hours later and the error rate is down to 70%. But still 3% selecting the answer "-1". Definitely interesting.

I got the wrong answer, but at least -1 was never a consideration for me.

TomThomson SSC Guru Points: 104773 More actions · Answer 15

marcia.j.wilson (3/19/2014)
TomThomson (3/19/2014)
Hany Helmy (3/18/2014)
[Incorrect answers: 80%], hmmm interesting.
About 36 hours later and the error rate is down to 70%. But still 3% selecting the answer "-1". Definitely interesting.
I got the wrong answer, but at least -1 was never a consideration for me.

Nor for me. I can't imagine how 24 people came to pick that answer.

Tom