June 15, 2008 at 11:54 pm
I'm Using Asp.net with C# with Sql Server 2005..
I'm working with OpenQuery()..
It is Working fine for Me with this Stored Procedure..
select * from OpenQuery([Server2],'Emp_Details.dbo.Employees')
It has NO INPUT PARAMETERS..
But If there is Input Parameters it is Not Woking..
select * from OpenQuery([Server2],'Emp_Details.dbo.Emp_Data Sri 1')
[Here Sri=EmpName & 1=EmpNo --- Input Parameters]
I'm Getting Error like
//-------------------------------
Cannot process the object "'Emp_Details.dbo.Emp_Data".
The OLE DB provider "SQLNCLI" for linked server "Server2" indicates that
either the object has no columns or the current user does not have permissions on that object.
//----------------------------------
How to resolve this Problem...
Thank You
June 17, 2008 at 7:03 am
Have you created the linked server between the two servers? Otherwise OPENQUERY is not going to work.
June 17, 2008 at 7:10 am
Here is an example of using input parameter in OPENQUERY.
declare @input VARCHAR(10)
declare @sql nvarchar (4000)
SET @input = 'abc'
SET @sql = 'SELECT col1, col2 FROM server1.dbo.table1 WHERE col3 = ''' + @wcID + ''''
SET @sql = N'select * from OPENQUERY(server1, ''' + REPLACE(@sql, '''', '''''') + ''')'
PRINT @sql
EXEC (@sql)
June 17, 2008 at 7:11 am
sorry, it should be
Here is an example of using input parameter in OPENQUERY.
declare @input VARCHAR(10)
declare @sql nvarchar (4000)
SET @input = 'abc'
SET @sql = 'SELECT col1, col2 FROM server1.dbo.table1 WHERE col3 = ''' + @input + ''''
SET @sql = N'select * from OPENQUERY(server1, ''' + REPLACE(@sql, '''', '''''') + ''')'
PRINT @sql
EXEC (@sql)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply