SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exec on Linked Server


Exec on Linked Server

Author
Message
Sudhirc
Sudhirc
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1603 Visits: 59
Comments posted to this topic are about the item Exec on Linked Server
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65205 Visits: 18570
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

Richard M Karpel
Richard M Karpel
SSC Eights!
SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)SSC Eights! (991 reputation)

Group: General Forum Members
Points: 991 Visits: 62
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 :-D
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6545 Visits: 2396
Doh! I read the question too fast and answered incorrectly. Good QOTD.
webrunner
webrunner
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7734 Visits: 4000
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Jedak
Jedak
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2977 Visits: 1130
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
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4324 Visits: 4408
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
Jedak
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2977 Visits: 1130
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
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4324 Visits: 4408
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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25599 Visits: 12494
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search