1

Topic: Remote access sql server 2008

Hello...
There are 2 servers Superac and Webac
Superac - windows server 2008, sql server 2008
Webac - windows server 2003, sql server 2005
On Webac all adjustments normally work... Remote access is included, the coordinator etc.
And here with 2008 I can not understand... Prompt :
I fulfill request on Webac to Superac

 select * from OPENDATASOURCE (' SQLOLEDB ', ' Data Source=172.18.2.212; User ID=opt; Password=123456 ').ac.dbo. LP
insert into OPENDATASOURCE (' SQLOLEDB ', ' Data Source=172.18.2.212; User ID=opt; Password=123456 ').ac.dbo. LP (login, pas)
select ' qwq ', 12 

The first select works, and insert is not present... The pancake was worn out... Why so ??
Error

 Msg 7343, Level 16, State 2, Line 11
The supplier of an OLE DB "SQLNCLI" for a linked server "(null)" cannot INSERT INTO the table "[SQLNCLI]". 

2

Re: Remote access sql server 2008

Silence...

3

Re: Remote access sql server 2008

Sandist;
And if server name  in a line of connection?

4

Re: Remote access sql server 2008

1. Try to use Native Client 10.0 (instead of 9.0 as in your case).
2. Try to use Linked Server instead of OPENDATASOURCE.

5

Re: Remote access sql server 2008

Bigheadman wrote:

1. Try to use Native Client 10.0 (instead of 9.0 as in your case).
2. Try to use Linked Server instead of OPENDATASOURCE.

And how to use Native Client 10.0?

6

Re: Remote access sql server 2008

Interesting that in the opposite direction the request transits... c Superac on Webac transits and it is successful enough

7

Re: Remote access sql server 2008

Try to envelop insert in

8

Re: Remote access sql server 2008

pacha wrote:

Try to envelop insert in

 exec (' insert into OPENDATASOURCE ("SQLOLEDB", "Data Source=172.18.2.212; User ID=opt; Password=123456"). ac.dbo. LP (login, pas) select ' ' qwq "12 ') 

In this sense?

9

Re: Remote access sql server 2008

Bigheadman wrote:

1. Try to use Native Client 10.0 (instead of 9.0 as in your case).
2. Try to use Linked Server instead of OPENDATASOURCE.

Linked too it is impossible...

 Msg 7343, Level 16, State 2, Line 2
The supplier of an OLE DB "SQLNCLI" for a linked server "SUPERAC" cannot INSERT INTO the table "[SUPERAC]. [ac]. [dbo]. [LP]". 

10

Re: Remote access sql server 2008

Sandist;
It is not assured but NativeClient 10 probably it - SQLNCLI10 of a line of connection

11

Re: Remote access sql server 2008

i2akai1 wrote:

Sandist;
It is not assured but NativeClient 10 probably it - SQLNCLI10 of a line of connection

Interesting!!! :-)

 Msg 7403, Level 16, State 1, Line 1
The supplier of an OLE DB "SQLNCLI10" is not registered.

it produced... You know as ?

12

Re: Remote access sql server 2008

It is not assured that something acceptable quits.
Try something of type such:

 exec OPENDATASOURCE (' SQLOLEDB ', ' Data Source=172.18.2.212; User ID=opt; Password=123456 ').ac.dbo.spInsertProcedure @login=123, @pas=123

Procedure, of course, beforehand should be created on the target server

13

Re: Remote access sql server 2008

Sandist wrote:

it is passed...
Interesting!!! :-)

 Msg 7403, Level 16, State 1, Line 1
The supplier of an OLE DB "SQLNCLI10" is not registered.

it produced... You know as ?

See also here .
Concerning registration - so SQL Native Client 10.0 it is necessary to install.
x86
x64

14

Re: Remote access sql server 2008

To Sozheleniju approaches nothing... The same error... I already rebuilt all... Translated both servers on windows server 2008 and sql server 2008... The same error and not what changes...

15

Re: Remote access sql server 2008

 AN OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Cursor operation conflict".
Msg 7343, Level 16, State 2, Line 1
The AN OLE DB provider "SQLNCLI" for linked server "(null)" could not INSERT INTO table "[SQLNCLI]". 

an error if completely...

16

Re: Remote access sql server 2008

People!!! Can eat still what assumptions? I was worn out... select, update, delete works... And insert the pancake is not present!!!

17

Re: Remote access sql server 2008

 exec (' insert into OPENDATASOURCE ("SQLOLEDB", "Data Source=172.18.2.212; User ID=opt; Password=123456"). ac.dbo. LP (login, pas) values ("qwq" 12 ')) 

transits as a variant?
-------------------------------------
Jedem Das Seine

18

Re: Remote access sql server 2008

So too does not quit... I checked up

19

Re: Remote access sql server 2008

And if simply to create  the server?
-------------------------------------
Jedem Das Seine

20

Re: Remote access sql server 2008

The trick that this request worked... Then unexpectedly ceased to work! I thought can adjustments any from a server flied... Or that! Most likely a problem in server adjustments!

21

Re: Remote access sql server 2008

Well so on the server that 172.18.2.212 request comes or not?

22

Re: Remote access sql server 2008

Glory wrote:

Well so on the server that 172.18.2.212 request comes or not?

Yes transits... Looked through Profiler there a heap of messages... Here the last...

 exec sp_cursor 180150009,4,0, N ' [ac]. [dbo]. [LP] ', @login ='qwq ', @pas = ' 12 ' 
 exec sp_cursorclose 180150009 
 exec sp_reset_connection 

the empty... And the end