Comment-Pour synchroniser les données entre Excel et SQL Server | serveur connecté


SQL Server 2008 offre de nombreuses technologies différentes pour l'intégration de systèmes. Les développeurs ont plusieurs options allant de la procédure stockée à des objets de serveur pour faire face à ne importe quelle conception de la situation. Utilisation de Microsoft Excel en tant que client, ce tutoriel va vous montrer comment utiliser un des objets de serveur lié Server pour intégrer SQL Server 2008 avec Microsoft Excel 2007 (2010) en utilisant la nouvelle OLE DB Jet 4.0.

Créer un serveur lié à Excel de SSMS (SQL Server Management Studio)





Création d'un serveur d'objets reliés par SSMS est très simple. L'assistant est bien expliqué.

Ouvrez SQL Server Management Studio, développez le Objets serveur Explorateur d'objets.

Suivant un clic droit sur Serveurs liés et sélectionnez Nouveau serveur lié.

Dans la boîte de dialogue Ouvrir, sélectionnez le Général, et entrez les informations suivantes pour définir le serveur de l'objet connecté .:

  1. Entrez un nom pour le serveur lié dans le serveur hors ligne.
  2. Pour le type de serveur pour Une autre source de données.
  3. Fournisseur, cliquez sur Microsoft Jet 4.0 OLE DB Provider si vous utilisez SQL Server 2005 ou SQL Server 2000. Sinon, choisissez Microsoft Office 12 Accès fournisseur OLE Database Engine DB pour SQL Server 2008
  4. Nom du produit, le type Excel pour le nom de la source de données OLE DB.
  5. Source de données, tapez le chemin complet et le nom du fichier Excel.
  6. Chaîne fournisseur, entrez Excel 8.0 pour Excel 2002, Excel 2000 ou Excel 97 classeur. Pour Excel 2007, Excel entrez 12,0
  7. Cliquez sur D'ACCORD pour créer le nouveau serveur lié

Lien Excel avec SQL Server avec sp_addlinkedserver

Sinon, vous pouvez utiliser la procédure de sp_addlinkedserver stockée pour créer le même objet serveur lié. Voici quelques exemples de code que vous pouvez utiliser:


DICHIARARErc int

DECLARE nvarcharservername (128)

DECLARE nvarcharserverproduct (128)

Nvarchar DICHIARAREserviceprovider (128)

DICHIARAREdatasource nvarchar (4000)

DECLARE nvarcharproviderstring (4000)



- Les valeurs des paramètres de SET

Setservername = 'xlsLinkedServer'

SETserverproduct = 'Excel'

= SETserviceprovider 'Microsoft.ACE.OLEDB.12.0'

SetDataSource = 'c: \ temp \ Loterie Numbers.xlsx'

'Excel 12,0' SET = providerstring


EXECRC = [maître]. [Dbo]. [Sp_addlinkedserver] serveur, srvproduct, fournisseur, DATASRC, chemin @, provstr, @ catalogue


Je utilise le "" fournisseur de services pour Excel 2007 si vous utilisez Excel 2003, 2002 ou 95, alors vous devriez le «Microsoft.Jet.OLEDB.4.0" fournisseur de services. Même si vous utilisez Excel 2007, vous devez le fournisseur de chaîne "Excel 12,0" toujours utiliser le "Excel 8.0" String pour les versions antérieures d'Excel.

Utilisation objet serveur lié à jour les données SQL Server en utilisant Excel

2007

Le premier exemple va utiliser le serveur lié objet "Excel_Lined" qui a été créé avec l'assistant SSMS. Pour que cet exemple fonctionne, nous aurons besoin de créer une table pour nos numéros de loterie. Ce est un simple colonnes: LotoNr, LotoDate et LotoName. Vous copiez et collez le script suivant dans une requête page SSMS et l'exécuter si vous voulez suivre et le code de test THW. Sinon, vous pouvez télécharger les scripts en utilisant l'URL inclus dans la section des ressources à la fin de ce livre.


Le premier exemple va utiliser le serveur lié objet "Excel_Lined" qui a été créé avec l'assistant SSMS. Pour que cet exemple fonctionne, nous aurons besoin de créer une table pour nos numéros de loterie. Ce est un simple colonnes: LotoNr, LotoDate et LotoName. Vous copiez et collez le script suivant dans une requête page SSMS et l'exécuter si vous voulez suivre et le code de test THW. Sinon, vous pouvez télécharger les scripts en utilisant l'URL inclus dans la section des ressources à la fin de ce livre.

Septembre ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO - ============================================= - Auteur: Kevin Languedoc - Date de Création: 09/24/2010 - Description: Charger des données à l'aide de serveur lié: Excel_Linked - ============================================= CREATE PROCEDURE dbo.LoadExcelData AS COMMENCER inclure dans dbo.Loto (LotoDate, LotoNr, LotoName) select convert (datetime, [date]), convertir (char (22), [numéros gagnants]), «6/49» par Excel_Linked ... [LotoNumbers $] FIN GO

Lorsque vous compilez et exécutez la procédure stockée, le contenu du fichier Excel seront chargés dans le tableau dbo.Loto. Voici une capture d'écran d'un échantillonnage des données

Le deuxième exemple qui montre comment il est facile de mettre à jour un enregistrement existant dans SQL Server en utilisant les données dans Excel. Pour cet exemple, je crée une nouvelle procédure stockée appelée "UpdateLotoUsingExcelData". Le code est indiqué ci-dessous et peut être copié et collé dans une requête de page dans SQL Server Management Studio, que tous les exemples.


Dans cet exemple, je ai créé une nouvelle feuille de calcul dans Excel appelé "UpdateLotoNumbers '' qui contient deux colonnes:" Date "et" NewLotoNumber ". Notez également que vous pouvez facilement faire référence à fichier Excel en utilisant le serveur d'objets connectés dans la clause FROM que toute autre base de données. La clause WHERE contient une référence à l'enregistrement de la table dbo.Loto sera mis à jour.

Septembre ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO - ============================================= - Auteur: Kevin Languedoc - Date de Création: 09/24/2010 - Description: Charger des données à l'aide de serveur lié: Excel_Linked - ============================================= CREATE PROCEDURE dbo.UpdateLotoUsingExcelData AS COMMENCER mise à jour dbo.Loto Septembre LotoNr = xls.NewLotoNumber par Excel_Linked ... [UpdateLotoNumbers $] xls où LotoDate = '18/09/2010' FIN GO

Une mise en garde importante à retenir est de toujours près Excel sinon vous obtiendrez des messages d'erreur cryptique dans la nature. L'utilisateur exécutant le service de base de données SQL dans Compter ou sur le serveur si vous ne utilisez pas SQL Express doit avoir lire et écrire des autorisations pour le dossier où vous trouverez l'Excel.

Excel est l'une des la plupart des applications client utilisées dans la finance et de business intelligence. Ce est également l'une des pires base du référentiel et ne devrait pas alors.

(0)
(0)

Commentaires - 0

Sans commentaires

Ajouter un commentaire

smile smile smile smile smile smile smile smile
smile smile smile smile smile smile smile smile
smile smile smile smile smile smile smile smile
smile smile smile smile
Caractères restants: 3000
captcha