Exec on Linked Server

Question

Exec on Linked Server

Sudhirc

SSCommitted

Points: 1921
More actions
January 13, 2010 at 11:23 pm

#214509

Comments posted to this topic are about the item Exec on Linked Server

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for the QOD. This was one where I was sure I knew the answer, but still needed to verify it online.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Richard M Karpel Ten Centuries Points: 1221 More actions · Answer 2

I was sure I knew the answer to this, but I do believe I am too influenced by Developing T-SQL and PL/SQL at the same time. This information is also available at

http://msdn.microsoft.com/en-us/library/ms188332.aspx.

Execute a pass-through command against a linked server

{ EXEC | EXECUTE }

( { @string_variable | [ N ] 'command_string [ ? ] ' } [ + ...n ]

[ { , { value | @variable [ OUTPUT ] } } [ ...n ] ]

)

[ AS { LOGIN | USER } = ' name ' ]

[ AT linked_server_name ]

I should have gone to the books online and verified my answer. This was a great question.

Thanks,

RickMan 😀

Rick Karpel

Ken SSCoach Points: 17191 More actions · Answer 3

Doh! I read the question too fast and answered incorrectly. Good QOTD.

webrunner SSC-Dedicated Points: 31715 More actions · Answer 4

Kenneth Wymore (1/14/2010)
Doh! I read the question too fast and answered incorrectly. Good QOTD.

Double Doh! I also read too quickly and didn't register that it was asking about the parameter marker.

Good question.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Jedak SSCertifiable Points: 5521 More actions · Answer 5

Interesting question. However, I'm wondering, wouldn't this be a function of the provider chosen when setting up the linked server rather than EXEC() itself?

For example if you used the .NET SQLClient as the driver the parameters would probably need to use the @ sign as that is how that provider works?

vk-kirov SSCertifiable Points: 7686 More actions · Answer 6

Jedak (1/14/2010)
if you used the .NET SQLClient as the driver the parameters would probably need to use the @ sign as that is how that provider works?

MSDN says that question marks should be used to process parameters in OLE DB (http://msdn.microsoft.com/en-us/library/ms722661(VS.85).aspx):

Using Parameters (OLE DB)
SQL providers represent parameter markers within a command as question marks (?).

Can you please provide an example of using the @ sign in .NET SQLClient?

Jedak SSCertifiable Points: 5521 More actions · Answer 7

vk-kirov (1/18/2010)
Jedak (1/14/2010)
if you used the .NET SQLClient as the driver the parameters would probably need to use the @ sign as that is how that provider works?
MSDN says that question marks should be used to process parameters in OLE DB (http://msdn.microsoft.com/en-us/library/ms722661(VS.85).aspx):

I know. I've written a ton of DTS packages and you use ? with the parameters on OLE DB.

vk-kirov (1/18/2010)
Jedak (1/14/2010)
Using Parameters (OLE DB) [/b]
SQL providers represent parameter markers within a command as question marks (?).
Can you please provide an example of using the @ sign in .NET SQLClient?

In SSIS if you use the ADO.NET provider or in straight ADO.NET in C#/VB.Net using the SQLCommand object you need to use @ and variable name not ?. Which is because of the difference in the provider used. So, I was asking a general question if the ? was more a function of the provider used then the use of EXEC. I'm not criticizing the question, just wondering out loud. 🙂

If you're looking for a code example, it will be a while as I'll have to find some time. I probably should not even be writing this post. 🙂

However, here is a link to some provider parameter information for .NET. Now whether this would translate to needing to use @Param in EXEC I don't know.

http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

vk-kirov SSCertifiable Points: 7686 More actions · Answer 8

Jedak (1/18/2010)
I've written a ton of DTS packages

I wrote only two or three when I was preparing to MCP exams, so I have a very brief experience in this area 🙂

I forgot to point out another link (http://msdn.microsoft.com/en-us/library/ms188279.aspx):

Linking Servers
A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers.
A linked server definition specifies the following objects:
- An OLE DB provider
- An OLE DB data source

So you can only use OLEDB in linked servers. This means that only the quotation mark (?) can be used as the parameter marker in 'EXECUTE AT' statements.

Now I understand that .NET SQLClient and OLEDB are different things, and I understand your point of view 🙂 Thanks for the MSDN link, it's very useful.

TomThomson SSC Guru Points: 104773 More actions · Answer 9

A good QOTD -one of the better ones.

It's quite amusing how MS muddles its terminology in this area, though

If you read http://msdn.microsoft.com/en-us/library/ms188332.aspx you will find the following text:-

L. Using a parameter with EXECUTE and AT linked_server_name
The following example passes a command string to a remote server by using a question mark (?) placeholder for a parameter. The example creates a linked server SeattleSales that points to another instance of SQL Server and executes a SELECT statement against that linked server. The SELECT statement uses the question mark as a place holder for the ProductID parameter (952), which is provided after the statement.

which uses the term "placeholder" twice for the "?" symbol in the context of exec at a linked server.

The Visual DB Tools documentation has many statements like

To mark a name as a parameter and not as a string of literal characters, you place a prefix in front of the parameter name (and, as an option, a suffix after it). For example, parameter marker characters might be "@", ":", or "%".

(this quote was from http://msdn.microsoft.com/en-us/library/aa276127(SQL.80).aspx but you can find much the same thing at http://msdn.microsoft.com/en-us/library/fb35s21e(VS.71).aspx and at http://msdn.microsoft.com/en-us/library/fb35s21e.aspx and so on). This makes it pretty clear that in the Visual DB Tools world "parameter marker" means something completely different from "parameter placeholder".

However other documentation often says "A parameter marker is a question mark (?) placed in the location of an input or output expression in a Transact-SQL statement." (eg http://msdn.microsoft.com/en-us/library/aa212698(SQL.80).aspx and http://msdn.microsoft.com/en-us/library/ms191162.aspx). Which directly contradicts the Visual DB Tools documentation and uses different terminolgy from that in the EXECUTE (Transact-SQL) entry in SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference.

MS must be a weird outfit to get BOL for SQLS 2008 contradicting itself about what those "?" things are called!

Tom