sql server - Importing Large Excel Sheet into SQL -


monthly i'm given spreadsheets need import sql. use sql server import , export wizard , import temp table call stored procedures complete import process. working fine on production server , working fine on development machine until last week when did clean install of windows 10. now, i'm still able import smaller spreadsheets few thousand rows, however, if attempt import data spreadsheets have 500,000+ rows of data takes couple minutes on both "setting source connection" , "setting destination connection" seems hang on "copying". i've checked logs , run sql profiler , see nothing regarding failures. i've let process run upward of hour , killed out, viewed temp table , shows no records imported. data format i'm able import on production server without issue.

now here's kicker... i'm able import these large sheets on development server if have spreadsheet open in excel @ same time, (note smaller spreadsheets don't need have spreadsheet open import). spreadsheet open, import process works slow , not effective way of doing things.

any thoughts on why import works on small spreadsheets unless spreadsheet opened in excel?

i'm using sql server 2008 r2 on windows 10 pro , office 2016. have accessdatabaseengine - 2007 installed.

edit: created ssis package , ran suggested , worked expected. make things more strange, went through import/export wizard , chose run , create ssis , again worked expected. went through wizard again chose run (did not create ssis) , hangs copying still. attempted run 4 more times , each time works if choosing "run immediately" , "create ssis" each time fails when choosing "run immediately" (without creating ssis). i'm confused how can happen , open more suggestions.

finally found solution problem!

since able import sheets when open, made me think issues due slow connection between sql , excel, started looking in direction. found few posts on other sites talking slow odbc connections windows 8 & 10. 1 site mentioned switching driver "sql native client". choosing destination "sql server native client 10.0", switched "sql native client" suggested , working expected.

i see these 2 drivers listed as:

driver                          version ------------------------------------------------ sql native client               2005.90.3042.00 sql server native client 10.0   2009.100.6000.34

i'm guessing older (2005) sql native client works because import/export utilizes accessdatabaseengine - 2007.

note: when using windows 7 or 2008 server, had no issues importing large spreadsheets (2009) sql server native client 10.0. seems issue later versions of windows. see several other sites mentioned there performance issues using tcp odbc connections on windows 10 , saw noticeable improvement using named pipes instead. production server uses tcp, didn't make change named pipes on development machine can't confirm that, wanted mention in event comes across post having issue.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more

ruby on rails - Seeing duplicate requests handled with Unicorn -

recently upgraded our ec2 instances c3.large in order boost more unicorn workers handle increased traffic our site. (6xworkers per machine - 2 ec2 instances). when did this, started seeing duplicate records being created! looks somehow, maybe 2 unicorn workers attempting process same request? in nginx logs see 1 [post] request particular rails controller - yet 2 records being created in database. i'm hitting kind of race condition issue - have no idea how debug this. doesn't happen time, when it's frustrating well. noticed 2 database records within 5 seconds of each other. (and yes disable button when user clicks submit button). unicorn 4.8.2 ruby 1.9.3 rails 3.2.14 any great! thanks! although may not fix problem, upgrade ruby 2.0.0. there blog mentioning idea upgrade ruby 2.0.0 if using unicorn. for example: https://www.digitalocean.com/community/articles/how-to-optimize-unicorn-workers-in-a-ruby-on-rails-app specifically: if using ruby 1.9, ...
Read more