"Sophie Guo [MSFT]" <v-sguo@online.microsoft.com> wrote in message
news:n2c8bveKFHA.2876@TK2MSFTNGXA02.phx.gbl...
> Hello Eric,
>
> I have reproduced the issue on my side. Based on my research, the
> following
> command works well:
>
>
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'sa';'password',
> 'SELECT * FROM test.dbo.tableB ') AS a
> GO
>
> or
>
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb;UID=sa;PWD=password;Database=test;S
> erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
>
>
>
> Therefore, I recommend you perform the following commands:
>
> 1. Make sure the Authentication Mode of MSDE is mixed mode.
>
> The following article is for your reference:
>
> INFO: MSDE Security and Authentication
> http://support.microsoft.com/default.aspx?scid=kb;en-us;325022#3
>
>
> 2. Run the following command to test:
>
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
> 'SELECT * FROM databaseA.dbo.tableB ') AS a
> GO
>
>
> Or
>
>
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb;UID=sa;PWD=password;Database=databa
> seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as
> a
>
>
> Note:
>
> 1. You need to replace the <your MSDE instance name> with your MSDE
> instance name.
>
>
> For more detailed information about OPENROWSET, please refer to the
> OPENROWSET topic in SQL Books Online(BOL).
>
>
> I hope the information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
>
>