Welcome to Sign in | Help
in Search

Cum selectez datele din tabela TSQL in MDB?

Last post 09-06-2007, 10:17 AM by maneaflorin@hotmail.com. 2 replies.
Sort Posts: Previous Next
  •  09-05-2007, 5:11 PM 2608

    Cum selectez datele din tabela TSQL in MDB?

    Salut,

    Am o procedura stocata pe serverul dedicat SQL 2005 care, in final, trebuie sa exporte datele intr-o baza access situata pe o statie de lucru.

    Am creat baza cu Acces 2000, am pus fisierul si pe server si pe statie in radacina, si totusi nu merge.

     

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

     SELECT * INTO [C:\db1.mdb].test
    FROM DBO.TMP_PMS0802PN

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

    Eroare: 

    The specified schema name "C:\db1.mdb" either does not exist or you do not have permission to use it. 

     

    Gresesc pe undeva?

    Florin 

  •  09-05-2007, 5:41 PM 2609 in reply to 2608

    Re: Cum selectez datele din tabela TSQL in MDB?

    Poti crea un "linked server" catre BD Access si apoi comanda INSERT tabela_access SELECT ... FROM tabela_sql_server:

     

    EXEC sp_addlinkedserver @server = 'LINK_ACCESS_DB',
        @srvproduct = 'commandos1',
        @provider = 'Microsoft.Jet.OLEDB.4.0',
        @datasrc = 'C:\BazaDeDate.mdb' --bd Access

    INSERT INTO LINK_ACCESS_DB...Produs (Cod,Denumire)
    SELECT IDArticol, Denumire
    FROM Articol --tabela din bd SQL Server
     

    Doar pt. selectia datelor din BD Access poti utiliza fraza SQL:

    SELECT *

    FROM LINK_ACCESS_DB...Produs
     

    Sau poti folosi OPENQUERY / OPENDATASOURCE :

    EXEC sp_configure 'Ad Hoc Distributed Queries' , 1  --atentie la setarea 'Ad Hoc Distributed Queries'  care trebuie sa fie activa !
    RECONFIGURE

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
          'C:\BazaDeDate.mdb';'admin';'',Produs)
    SELECT IdArticol , Denumire
    FROM Articol

    EXEC sp_configure 'Ad Hoc Distributed Queries' , 0  --daca este cazul dezactivezi optiunea 'Ad Hoc Distributed Queries'
    RECONFIGURE

     

  •  09-06-2007, 10:17 AM 2612 in reply to 2609

    Re: Cum selectez datele din tabela TSQL in MDB?

    Am incercat prima versiune si merge acum!

    Iti multumesc tare mult si sa ai o zi tare buna!
     

    Florin 

View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems