Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exec on Linked Server Expand / Collapse
Author
Message
Posted Wednesday, January 13, 2010 11:23 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 1:53 AM
Points: 1,569, Visits: 57
Comments posted to this topic are about the item Exec on Linked Server
Post #847350
Posted Thursday, January 14, 2010 12:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:30 AM
Points: 17,946, Visits: 15,934
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
Post #847374
Posted Thursday, January 14, 2010 4:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 30, 2011 3:44 AM
Points: 971, 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
Post #847442
Posted Thursday, January 14, 2010 6:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:24 PM
Points: 3,384, Visits: 2,017
Doh! I read the question too fast and answered incorrectly. Good QOTD.
Post #847497
Posted Thursday, January 14, 2010 7:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 2,409, Visits: 2,818
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


-------------------
"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
Post #847538
Posted Thursday, January 14, 2010 7:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 1,928, Visits: 1,013
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?
Post #847551
Posted Monday, January 18, 2010 4:41 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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?
Post #849080
Posted Monday, January 18, 2010 8:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:42 AM
Points: 1,928, Visits: 1,013
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
Post #849186
Posted Monday, January 18, 2010 9:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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.
Post #849250
Posted Thursday, February 11, 2010 2:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 7,847, Visits: 9,596
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
Post #864316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse