OpenRowset to exec sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

OpenRowset to exec sp

Author

Topic

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2007-05-09 : 16:30:29

How can I exec insert stored-procedure by using openrowset?
I had no problem when exec select sp.
I got error for exec insert sp:
My script:
EXEC('SELECT a.* FROM OPENROWSET(''SQLOLEDB'',''Server=MyServerName;uid=somep;pwd=anything;database=mydb'', ''exec mydb.dbo.mysp @p1 = ''''16050'''',
@p2 = ''''TESTING'''',
@p3 = ''''TEST'''''') AS a')
Error:
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB'..
And I don't want to add linked server.
Help please!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-09 : 20:43:35

You can try something along this line.


select
	a.*
from
	openrowset('SQLOLEDB','SERVER=(local);Trusted_Connection=yes;',
'
SET FMTONLY OFF;
EXEC SP_HELPDB
') a

CODO ERGO SUM

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2007-05-10 : 09:21:07

I tried and got the same error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-10 : 09:33:28

Make sure procedure resturns data with proper column names

Madhivanan

Failing to plan is Planning to fail

Dance Doll
Yak Posting Veteran

54 Posts

Posted - 2007-05-10 : 11:11:03

I run the sp in remote server successfully.
So, the columns are right.
Something wrong with OpenRowSet. Maybe it can't doing Update, Delete or Insert remotely...
:(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources