Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2014
    Posts
    4

    Unanswered: Data Import To SQL Server 2008 R2

    Good Morning,

    I had a import method working for around 2 years. I would get an extract in Excel 2007 format and would use MS Access TransferSpreadsheet method to import it onto a linked table(Resides on MS SQL Server). This way a user could do it and not rely on me doing it having Management studio. Then my employer decided to change my data source and structure. So I rebuilt the system data structure to accommodate the new data. Since I have had nothing but problems with the end user import. It works fine with me doing an import directly with the server but not through the TransferSpreadsheet method. When running that command it gives me a generic you cannot record your changes because a value you entered violates the settings defined for this table. So I changed the extract format to a csv and then to a tab delimitated to no avail same result. (Just thought it might have been a funky excel data change somewhere). Like I said it can still be imported directly from the server but that is not the solution I like.

    Using Access as my import method or the import wizard in SQL server is the only way I have imported data into SQL Server. I was wondering if any of you could steer me in the correct direction on either why all of a sudden why my transferspreadsheet stopped or another method to import aprox 100,000 rows of data to the server instead of inserting row by row.... BTW I cannot do a Bulk insert command either(DBA restricted that. Working on getting that changed as well.)

    Any help would be great!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have you checked to see if the destination table has constraints on it? Primary key, check constraints, Unique constraints, foreign keys, that sort of thing?

  3. #3
    Join Date
    Oct 2014
    Posts
    4
    Good thought but yes... The odd part is that I can do the import directly using management studio. I am only unable to import via the transferspreadsheet method. I might be wrong but if I had constraint issues it would crop up during the import in management studio.

  4. #4
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    After you rebuilt the system structure ... Did you RELINK the Access table that points to your SS data ?

    Wayne

  5. #5
    Join Date
    Oct 2014
    Posts
    4
    Yes I have relinked the table. Actually I had created a new table and brought it in from base. I just do not know if access would be putting a constraint on artificially. Does anyone know a way to import using a vbscript with no bulk insert. I am negotiating now to see if we can get that enabled by chance.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have to admit, I don't know much about the transfer spreadsheet method, but are you able to populate an Access table with the data using this method? (in short take SQL Server out of the picture).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •