Posted by jc on 08/07/06 08:22

Hello. I want to ask about the possibility of copying both a table
structure and it's contents from a
SQL server table to a table within MS access. The problem cannot be
solve with a permanent table structure at the target location.
The names of the columns are essentially data with the application and
so are subject to change. I am targeting a solution using SQL Query
Manager.

The approach I have tried (with failure) is
SELECT *
INTO <linkedserver table>
FROM <local table>

This should create and copy. However, I am not sure if this is
achievable with this approach.

Refer to the dialogue;
-------------------------------------------------------
USE MASTER
GO
EXEC sp_addlinkedserver
@SERVER = 'Freddie',
@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',
@SRVPRODUCT = 'OLE DB Provider for Jet',
@DATASRC = 'C:\temp\HMIS_Recipe.mdb'

-- I am not sure if this is required
EXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULL

SELECT * FROM Freddie...FRED -- This is OK

SELECT * INTO #Temp FROM Freddie...FRED -- This is OK

-- This fails - Refer error
SELECT * INTO Freddie.FRED65
from #temp

Server: Msg 2760, Level 16, State 1, Line 1
Specified owner name 'Freddie' either does not exist or you do not have
permission to use it.

-- This also fails and I thought reflected the above select with naming
- Refer error
SELECT * INTO Freddie...FRED65
from #temp

Server: Msg 117, Level 15, State 1, Line 2
The object name 'Freddie...' contains more than the maximum number of
prefixes. The maximum is 2.

EXEC sp_dropserver 'Freddie',
@droplogins = 'droplogins'

------------------------------------------------------------

Thank you.

Regards JC...

[Back to original message]