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_addlinkedser ver
@SERVER = 'Freddie',
@PROVIDER = 'Microsoft.Jet. OLEDB.4.0',
@SRVPRODUCT = 'OLE DB Provider for Jet',
@DATASRC = 'C:\temp\HMIS_R ecipe.mdb'
-- I am not sure if this is required
EXEC sp_addlinkedsrv login '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...FRED6 5
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...
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_addlinkedser ver
@SERVER = 'Freddie',
@PROVIDER = 'Microsoft.Jet. OLEDB.4.0',
@SRVPRODUCT = 'OLE DB Provider for Jet',
@DATASRC = 'C:\temp\HMIS_R ecipe.mdb'
-- I am not sure if this is required
EXEC sp_addlinkedsrv login '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...FRED6 5
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...
Comment