openrowset - 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

General SQL Server Forums

New to SQL Server Programming

openrowset

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-06-22 : 07:07:34

when i try to execute the below query ,following error occurs.can anybody correct this problem..

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@a".


declare @a nvarchar(max)
set @a= 'fin_wh.dbo.Mis_allocation_main_sp ' +'''All'','+'''All'','+'''01 apr 2009'','
+'''30 jun 2009'','++'''USD'','+'''All'','+'''All'''

SELECT  * 
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec @a')
AS tbl

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-22 : 07:49:44

@a doesn't exist in the scope created by OPENROWSET.

try


declare @a nvarchar(max)
set @a= 'fin_wh.dbo.Mis_allocation_main_sp ' +'''All'','+'''All'','+'''01 apr 2009'','
+'''30 jun 2009'','++'''USD'','+'''All'','+'''All'''

PRINT @a

SELECT  * 
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec (' + @a + ')')
AS tbl

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-06-23 : 01:13:37

when i try to execute the below query,i get the following error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '+'.

quote:
Originally posted by Transact Charlie

@a doesn't exist in the scope created by OPENROWSET.

try

declare @a nvarchar(max)
set @a= 'fin_wh.dbo.Mis_allocation_main_sp ' +'''All'','+'''All'','+'''01 apr 2009'','
+'''30 jun 2009'','++'''USD'','+'''All'','+'''All'''

PRINT @a

SELECT *
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec (' + @a + ')')
AS tbl

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 03:30:11

Try this

declare @a nvarchar(max)
set @a= 'fin_wh.dbo.Mis_allocation_main_sp ' +'''''All'''','+'''''All'''','''+'''01 apr 2009'''','''
+'''30 jun 2009'''','''+'''USD'''','''+'''All'''','''+'''All'''' '

PRINT @a

set @a = 'exec ' + @a

set @a = 'SELECT * FROM OPENROWSET(''SQLOLEDB'', ''Server=(local);TRUSTED_CONNECTION=YES;'', ''' +  @a + ''') AS tbl'

print @a
exec (@a)

E 12°55'05.63"
N 56°04'39.26"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-06-23 : 03:44:57

while iam try to execute the below error occurred,
exec fin_wh.dbo.Mis_allocation_main_sp ''All'',''All'',''01 apr 2009'',''30 jun 2009'',''USD'',''All'',''All''
SELECT * FROM OPENROWSET('SQLOLEDB', 'Server=ramcovm392;TRUSTED_CONNECTION=YES;', 'exec fin_wh.dbo.Mis_allocation_main_sp ''All'',''All'',''01 apr 2009'',''30 jun 2009'',''USD'',''All'',''All'' ') AS tbl
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec fin_wh.dbo.Mis_allocation_main_sp 'All','All','01 apr 2009','30 jun 2009','USD','All','All' ". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

quote:

Originally posted by Peso

Try this

declare @a nvarchar(max)
set @a= 'fin_wh.dbo.Mis_allocation_main_sp ' +'''''All'''','+'''''All'''','''+'''01 apr 2009'''','''
+'''30 jun 2009'''','''+'''USD'''','''+'''All'''','''+'''All'''' '

PRINT @a

set @a = 'exec ' + @a

set @a = 'SELECT * FROM OPENROWSET(''SQLOLEDB'', ''Server=(local);TRUSTED_CONNECTION=YES;'', ''' +  @a + ''') AS tbl'

print @a
exec (@a)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 03:54:50

Do you have permission on the object?
The account used for the linked server, that is.

E 12°55'05.63"
N 56°04'39.26"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-06-23 : 04:05:01

iam into sa[administrator].if i try to execute


exec Mis_allocation_main_sp 'All','All','01 apr 2009',30 jun 2009','USD','All','ALL'

its getting the resultset..

quote:
Originally posted by Peso

Do you have permission on the object?
The account used for the linked server, that is.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources