Call sqlcmd commands in stored procedure code

Question

Because of Linked Server is not easy to send openrowset query I connect to remote Sql Server directly via SQLCMD mode. But this is Ok using Query Analyzer. When I put code to stored procedure I cannot even save it:

:Connect x.x.x.x -U sa -P password
select *
from  openrowset('VFPOLEDB','\\share\db\s_object.dbf';'';
    '','SELECT *  FROM s_object where i_class=21 order by 1')

Incorrect syntax near ':'.

SQL Server Management Studio has a sqlcmd compatible mode. The ancient Query Analyzer has a similar mode, but you have to talk to old folk to see how to enable it... — Remus Rusanu
– Remus Rusanu, Commented May 27, 2013 at 19:49

Remus Rusanu · Accepted Answer · 2013-05-27 19:59:37Z

2

sqlcmd mode :connect syntax is valid in SSMS and QA when sqlcmd mode is enabled, but it will always be a client side command, executed by SSMS or QA itself and never sent to the engine. As such by no way it can be embedded on a server side module, like a stored procedure. If you really mean stored procedure then no wonder the syntax error...

answered May 27, 2013 at 19:59

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

revoua Over a year ago

I am sorry for very long delay after your detailed response - I was looking for any other solution to implement openrowset/openquery over Linked Server. Actually you answered my question and I need to open another tread to find solution for that.

Collectives™ on Stack Overflow

Call sqlcmd commands in stored procedure code

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related