title: T-SQL, OPENROWSET copying tables between databases on the same server
body: <p>I am developping some code that transfers data between SQL servers. In this phase, all my work is on the same server (local, where I am the owner), but I am already trying to implement the OPENROWSET functionnalities that will be used at production time (where  data will be on different servers and where I will have to build queries for the transfers). The following 3 codes are supposed to do the very same thing, but the one making use of OPENROWSET is giving me an error ... Bref, I am stuck! if anyone could help...</p>
  
  <p>3 parts naming: works</p>
  
  <pre><code>USE db1
  SELECT * INTO dbo.myTable FROM db2.dbo.myTable
  </code></pre>
  
  <p>OPENDATASOURCE: works</p>
  
  <pre><code>USE db1
  SELECT * INTO dbo.myTable FROM OPENDATASOURCE
      ('SQLOLEDB', 
      'Data Source=127.0.0.1\SQLEXPRESS;Integrated Security=SSPI'
      ).db2.dbo.myTable
  </code></pre>
  
  <p>OPENROWSET: does not work</p>
  
  <pre><code>USE db1
  SELECT * INTO dbo.myTable FROM OPENROWSET
      ('SQLOLEDB', 
       'Trusted_Connection=yes;Server=(Local)', 
       'db2.dbo.myTable')
  </code></pre>
  
  <p>Where I am getting the following message:</p>
  
  <pre><code>OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
  OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
  </code></pre>
  
  <p>Of course I have tried to use the standard properties of my connection string (as with OPENDATASOURCE) but I was also getting the following error:</p>
  
  <pre><code>OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification".
  OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".
  </code></pre>