Cannot read Excel file from SQL Server with multiple versions of SQL Server -

- 

exec sp_configure 'show advanced options', 1 reconfigure go  exec sp_configure 'ad hoc distributed queries', 1 reconfigure go  use [master]  go   exec master.dbo.sp_msset_oledb_prop n'microsoft.ace.oledb.12.0', n'allowinprocess', 1  go   exec master.dbo.sp_msset_oledb_prop n'microsoft.ace.oledb.12.0', n'dynamicparameters', 1  go

reading excel

declare @sqlconnect varchar(8000)  set @sqlconnect =  'select * openrowset(''microsoft.ace.oledb.12.0'',                        ''excel 8.0;database=d:\wages.xlsx;'',                         ''select * [sheet2$]'')'                        exec (@sqlconnect)

exception

msg 7399, level 16, state 1, line 1
ole db provider "microsoft.ace.oledb.12.0" linked server "(null)" reported error. provider reported unexpected catastrophic failure.

msg 7303, level 16, state 1, line 1
cannot initialize data source object of ole db provider "microsoft.ace.oledb.12.0" linked server "(null)".

sometimes gives error goes in infinite loop of execution.

note: getting error after installing sql server 2008 r2 on sql server 2012.

  • os: windows 7 32-bit
  • sql server 2008 r2 , sql server 2012 installed

the sql server error message if user have no rights sql server temp directory:

ole db provider "microsoft.jet.oledb.4.0" linked server "(null)" returned message "unspecified error".
msg 7303, level 16, state 1, line 1
cannot initialize data source object of ole db provider "microsoft.jet.oledb.4.0" linked server "(null)".

grant rigths temp directory
(i.) step required 32-bit sql server ole db provider

the main problem ole db provider creates temporary file during query in sql server temp directory using credentials of user run query.

the default directory sql server default directory sql server service account.

if sql server run under network service account temp directory like:

c:\windows\serviceprofiles\networkservice\appdata\local\temp

if sql server run under local service account temp directory like:

c:\windows\serviceprofiles\localservice\appdata\local\temp

microsoft recommends 2 ways solution:

a change of sql server temp directory , grant of full rights users directory. grant of read/write rights current sql server temp directory.

see details: prb: "unspecified error" error 7399 using openrowset against jet database

usually few accounts used import operations. can add rights these accounts.

for example, icacls utility can used rights setup:

icacls c:\windows\serviceprofiles\networkservice\appdata\local\temp /grant vs:(r,w)

for more information follow link


Comments

Post a Comment

Popular posts from this blog

google chrome - Developer tools - How to inspect the elements which are added momentarily (by JQuery)? -

-
i looking @ how website designed using inspector in chrome. can use firefox developer tools or firefox web developer add-on or chrome web developer extension. so website has slide-show , using jquery it. looking @ markup , css in inspector, i see 2 things constantly changing slides change: one opacity css property understandable, i'd see changing values, changes fast see read anything. second- a div added in div slides change momentarily , moment when appears short notice except div . so question is there way stop slideshow can temporarily rid of blinking sort of effect (by speedy adding , removal , changing of elements , values)? where can inspect div element gets added such short moment , , gets removed? , can see changes in value of opacity takes place? is there way stop slideshow can temporarily rid of blinking sort of effect (by speedy adding , removal , changing of elements , values)? if opacity animated css transition or animation,
Read more

angularjs - Showing an empty as first option in select tag -

-
my app showing empty select field first entry. have read other solution given , tried still not working. my controller code is: .controller('usercreatecontroller', function(user,profile,auth) { alert("hello"); var vm = this; vm.type = 'create'; profile.all() //this service fetch profiles shown in select field .success(function(data){ vm.profile = data; }); vm.userdata.profile = vm.profile[0]; //here have set default value select field and html page is: <label class="col-sm-2 control-label">profile</label> <div class="col-sm-8"> <select ng-model="user.userdata.profile" ng-options="person._id person.profilename person in user.profile"> </select> </div> hierarchy of vm.profile { "_id": 46, "profile": objectid("5516498e95b84548156db754"), "isactive": true, "password": "$2
Read more

php - Cloud9 cloud IDE and CakePHP -

-
i attempting cakephp running on cloud9, cloud based ide. unaware, cloud collaboration ide intermixes services such bitbucket. site gives sudo rights, , installed cakephp using these instructions: https://www.digitalocean.com/community/tutorials/how-to-install-cakephp-on-an-ubuntu-12-04-vps when attempt run database exported machine cakephp installed, error message "url rewriting not configured on server." attempted solve issue using official guide cakephp: http://book.cakephp.org/2.0/en/installation/url-rewriting.html but no cigar, same error message. has gotten cakephp working, or aware of cloud ide free/low cost cloud9 support cakephp? here's how got working now: sudo apt-get update sudo apt-get install php5-intl curl -ss https://getcomposer.org/installer | php -- --filename=composer ./composer create-project --prefer-dist cakephp/app this creates project in /app default. move workspace docroot: mv app/* ./ mv app/.* ./ rm -rf app then de
Read more