OPENROWSET error - 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 error

Author

Topic

verybrightstar
Starting Member

16 Posts

Posted - 2006-05-02 : 06:28:29

hi all,

i tried to execute this statement in the query analyzer but failed saying that

Server: Msg 7357, Level 16, State 1, Line 3
Could not process object 'exec dbo.stp_GetStudentProfile '+@StudId+''. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.

----------------------query analyzer-----------------------------
declare @StudId as varchar(30)
set @StudId = '123456'

SELECT *
FROM OPENROWSET('SQLOLEDB','MYSERVER';'sa';'1234','exec dbo.stp_GetStudentProfile '''+@StudId+''')

---------------------------------------------------

This is my stored procedures for the stp_GetStudentProfile
CREATE PROCEDURE [dbo].[stp_GetStudentProfile]

@StudId as varchar(30)

As

select * from StudentProfile where StudId = @StudId

GO

kt

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 06:40:37

AFAIK you can't have a string expression, or any variables in your OPENROWSET call

So you would have to do:


declare @StudId as varchar(30)
        @strSQL nvarchar(4000)
set @StudId = '123456'

SELECT @strSQL ='
SELECT *
FROM OPENROWSET(''SQLOLEDB'',''MYSERVER'';''sa'';''1234'',''exec dbo.stp_GetStudentProfile ''''' + @StudId + ''''')'
PRINT @strSQL -- Check it looks right!!
EXEC (@strSQL)

Kristen

verybrightstar
Starting Member

16 Posts

Posted - 2006-05-02 : 06:54:55

hi it works , however need to add extra quote behind the @StudId

declare @StudId as varchar(30)
declare @strSQL nvarchar(4000)
set @StudId = '123456'

SELECT @strSQL ='
SELECT *
FROM OPENROWSET(''SQLOLEDB'',''MYSERVER'';''sa'';''1234'',''exec dbo.stp_GetStudentProfile ''''' + @StudId + ''''''')'
PRINT @strSQL -- Check it looks right!!
EXEC (@strSQL)

quote:
Originally posted by Kristen

AFAIK you can't have a string expression, or any variables in your OPENROWSET call

So you would have to do:

declare @StudId as varchar(30)
@strSQL nvarchar(4000)
set @StudId = '123456'

SELECT @strSQL ='
SELECT *
FROM OPENROWSET(''SQLOLEDB'',''MYSERVER'';''sa'';''1234'',''exec dbo.stp_GetStudentProfile ''''' + @StudId + ''''')'
PRINT @strSQL -- Check it looks right!!
EXEC (@strSQL)

Kristen

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 07:02:34

"need to add extra quote"

They are a nightmare to sort out in this sort of code - best to use a pepper-pot full of them!

Glad you got it sorted,

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources