none
OpenRowSet Issue

    Question

  • Hi,

    I am trying to access remote db server's stored procedure and am using openrowset for that. i am aware of that i can use linked server. but due to some reason my customer doesn't want to use that. Here is my sample code

    server1

    db: Test

    declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
      EXEC('SELECT *
                FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
                ''
                SET NOCOUNT ON;
                set fmtonly off exec Employee.[dbo].TestInsert ' + @IdEmployee + ',' + @Name+ '
                ''
                ) AS a;')
    
    server2 (170.30.149.34)

    db: Employee

    Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))
    
    as 
    
    BEGIN
    
     Insert into Emp(IdEmployee,Name) values(@IdEmployee,@Name)
    
    END

    When i execute the query it throws an exception

    The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object
    any suggestion or correction please


    loving dotnet

    Wednesday, July 09, 2014 12:32 AM

Answers

All replies

  • Check if user Demo has write access on the remote server. 

    Also code required below tweak for passing varchar parameter

    declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
      EXEC('SELECT *
                FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
                ''
                SET NOCOUNT ON;
                set fmtonly off exec Employee.[dbo].TestInsert ' + @IdEmployee + ',''' + @Name+ '''
                ''
                ) AS a;')


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, July 09, 2014 1:38 AM
  • Hi Visak,

    Thanks for your reply and when i execute am getting syntax error:


    Msg 102, Level 15, State 1, Line 5
    Incorrect syntax near 'Test'.

    i squeezed my mind and am unable to find. any suggestion please


    loving dotnet

    Wednesday, July 09, 2014 2:05 PM
  • declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
      EXEC('SELECT *
                FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
                ''
                SET NOCOUNT ON;
                set fmtonly off exec Employee.[dbo].TestInsert ' + convert(varchar,@IdEmployee) + ',''' + @Name+ '''
                ''
                ) AS a;')
    Damn those non-implicit int-to-string conversions.
    Wednesday, July 09, 2014 2:37 PM
  • Hi patrik,

    When i execute the above query here is the error am getting

    [quote]Msg 7357, Level 16, State 2, Line 1
    Cannot process the object "
                SET NOCOUNT ON;
                set fmtonly off exec Employee.[dbo].TestInsert 11,'Peter'
                ". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
    [/quote]

    but insert is working fine. why it throws this error. how to avoid this error . 

    any sugestion please

    loving dotnet


    Wednesday, July 09, 2014 6:45 PM
  • Why are you using fmtonly?

    perhaps try adding a new line, and a semi-colon?

    declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
      EXEC('SELECT *
                FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
                ''
                SET NOCOUNT ON;
                set fmtonly off;
     exec Employee.[dbo].TestInsert ' + convert(varchar,@IdEmployee) + ',''' + @Name+ '''
                ''
                ) AS a;')

    Wednesday, July 09, 2014 7:02 PM
  • If i don't add  set fmtonly off then insert is not working. I tried  new line, and a semi-colon and still it throws an error.

    The stored procedure which am calling is not returning any rows as it has only insert statement. also it is on another server. thats why am using this mechanism to do. any suggestion please

    [quote]

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object "                       
                set fmtonly off;
                 exec Employee.[dbo].TestInsert 11,'Peter'
                ". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    [\quote]


    loving dotnet


    Wednesday, July 09, 2014 7:18 PM
  • Can you not have the proc return something?

    Perhaps:

    select * from employeeTable
    where employeeID = @IdEmployee

    Wednesday, July 09, 2014 7:27 PM
  • Hi can you try below

    the issue is highlighted below bold, i tested in my machine it worked

    exec Employee.[dbo].TestInsert 11,'Peter'

    should be

    exec Employee.[dbo].TestInsert 11,''Peter''

    declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
      EXEC('SELECT *
                FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
                ''
                SET NOCOUNT ON;
                set fmtonly off;
     exec MStest1.[dbo].TestInsert ' + convert(varchar,@IdEmployee) + ',''''' + @Name+ '''''
                ''
                ) AS a;')


    if it is not working, what is your SQL server version of 170.30.149.34 is, if it is SQL 2012

    try SQLNCLI11 instead of SQLNCLI


    • Edited by SaravanaC Wednesday, July 09, 2014 7:45 PM
    Wednesday, July 09, 2014 7:38 PM
  • Hi Saravana,

    Thanks for your reply and if i execute am getting error as follows,

    Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'convert'.

    i am using Sqlserver2008 R2

    any suggestion please


    loving dotnet

    Wednesday, July 09, 2014 9:13 PM
  • Hi Saravana,

    Thanks for your reply and if i execute am getting error as follows,

    Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'convert'.

    i am using Sqlserver2008 R2

    any suggestion please


    loving dotnet

    for checking you can use print instead for EXEC like below

    declare @IdEmployee int = 11,@Name varchar(20) = 'Peter';
      PRINT('SELECT *
                FROM OPENROWSET(''SQLNCLI'', ''Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee'',
                ''
                SET NOCOUNT ON;
                set fmtonly off;
     exec MStest1.[dbo].TestInsert ' + convert(varchar,@IdEmployee) + ',''''' + @Name+ '''''
                ''
                ) AS a;')

    the output will be as

    SELECT *
                FROM OPENROWSET('SQLNCLI', 'Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee',
                '
                SET NOCOUNT ON;
                set fmtonly off;
     exec Employee.[dbo].TestInsert 11,''Peter''
                '
                ) AS a;
    

    check by running directly the above and see, if you are getting error. and also check executing the SP "TestInsert" in 170.30.149.34 with same parameter and see it is running without any error, there could be changes in SP to produce Incorrect syntax near the keyword 'convert'.

    Thanks

    Saravana Kumar C




    Thursday, July 10, 2014 3:01 AM
  • If i don't add  set fmtonly off then insert is not working. I tried  new line, and a semi-colon and still it throws an error.

    The stored procedure which am calling is not returning any rows as it has only insert statement. also it is on another server. thats why am using this mechanism to do. any suggestion please

    [quote]

    Msg 7357, Level 16, State 2, Line 1
    Cannot process the object "                       
                set fmtonly off;
                 exec Employee.[dbo].TestInsert 11,'Peter'
                ". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

    [\quote]


    loving dotnet


    Then whats the point in doing a select from it if doesnt have a resultset?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, July 10, 2014 4:35 AM
  • Hey guys,

    The operation what am performing is i need to do some operations on  remote servers(db)  from my production db. The operation will have select, insert,update and delete.

    for example,i will be writing a stored procedures in my production db to call the stored procedure in the remote database. my remote database procedures will have select,insert,update, delete statements.

    To select the data i am already done. but calling the procedure which has insert statement fails as i posted in my previous posts. i should not be using linked server for this as my client requirement.

    so i chosen openrowset concept. The reason i have select statement is i need to pass the no of rows affected to the calling procedure because based on that i will be showing success/failure message on the front end(c# .net).

    please help me now.


    loving dotnet

    Thursday, July 10, 2014 10:18 AM
  • Follow your inserts with:

    SELECT ROWCOUNT_BIG ()

    I think the problem you were having is a result of nothing being returned to the client.

    This will cause the number of rows effected by the previous statement to be returned.

    Thursday, July 10, 2014 2:09 PM
  • Hi patrik,

    Still having the same issue and i have tried saravana's logic once again,

    SELECT *
                FROM OPENROWSET('SQLNCLI', 'Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee',
                '
                SET NOCOUNT ON;
                set fmtonly off;
     exec Employee.[dbo].TestInsert 11,''Peter''
                '
                ) AS a;

    it inserts the data twice, not sure why? any suggestion please


    loving dotnet

    Thursday, July 10, 2014 3:13 PM
  • Hi patrik,

    Still having the same issue and i have tried saravana's logic once again,

    SELECT *
                FROM OPENROWSET('SQLNCLI', 'Server=170.30.149.34;Uid=Demo;Pwd=Welcome;Database=Employee',
                '
                SET NOCOUNT ON;
                set fmtonly off;
     exec Employee.[dbo].TestInsert 11,''Peter''
                '
                ) AS a;

    it inserts the data twice, not sure why? any suggestion please


    loving dotnet

    post code of
    TestInsert

    Thursday, July 10, 2014 3:47 PM
  • Here is the code of TestInsert
    Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))
    
    as 
    
    BEGIN
    
     Insert into Emp(IdEmployee,Name) values(@IdEmployee,@Name)
    
    END


    loving dotnet

    Thursday, July 10, 2014 4:09 PM
  • Here is the code of TestInsert
    Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))
    
    as 
    
    BEGIN
    
     Insert into Emp(IdEmployee,Name) values(@IdEmployee,@Name)
    
    END


    loving dotnet

    can you try this instead of above code

    Create Procedure TestInsert(@IdEmployee int, @Name varchar(20))
    as
    BEGIN
    Declare @test1 table(IdEmployeeint,name varchar(20))
                
                Insert into Emp(IdEmployee,Name)
                output INSERTED.IdEmployee, INSERTED.name into @test1
                Select @IdEmployee ,@Name
                
                select COUNT(IdEmployee) as rowsaffected from @test1
     
    
    END

    Thursday, July 10, 2014 4:15 PM
  • Hi Saravana,

    i did try what you have proposed and it returns rowaffected as 1 but the data not inserted on the table


    loving dotnet

    Thursday, July 10, 2014 7:50 PM
  • Hi Saravana,

    Appreciate your time on this

    but by seeing these many constraints, i will propose my client to change this methodology to lined server. is it possible to block other developers to use this linked server? because not of the developers should have execute access to the linked server. is it possbile to do? any samples please how to achieve this impersonation.


    loving dotnet

    Thursday, July 10, 2014 11:04 PM
  • Sure, just created the linked server to use the current users credentials, and grant access to your user on the remote server.
    Friday, July 11, 2014 2:00 PM