Export data to Excel from SQL Server using OPENROWSET issue -

-

i using below script , got following error:

cannot create instance of ole db provider "microsoft.ace.oledb.12.0" linked server "(null)".

exec sp_configure 'show advanced options', 1;  go  reconfigure;  exec sp_configure 'ad hoc distributed queries', 1;  go  reconfigure;   insert openrowset ('microsoft.ace.oledb.12.0',  'excel 12.0;database=c:\csv\testing.xlsx;',  'select * [sheet1$]')  select top 10 * emp2

how can fix this? need export data excel file in unc path. please guide me how achieve it.

again try below script time getting different error:

cannot create instance of ole db provider "microsoft.jet.oledb.4.0" linked server "(null)".

exec sp_configure 'show advanced options', 1 reconfigure go exec sp_configure 'ad hoc distributed queries', 1 reconfigure go  insert openrowset     ('microsoft.jet.oledb.4.0',     'excel 8.0;database=c:\csv\testing.xlsx;','select id,name [sheet1$]')    select top 10 * emp2

please guide me how export data excel file generated in unc path.

here couple things try.

export data existing excel file sql server table

insert openrowset('microsoft.jet.oledb.4.0',      'excel 8.0;database=d:\testing.xls;',      'select * [sheetname$]') select * sqlservertable

if don't want create excel file in advance , want export data it, use

exec sp_makewebtask      @outputfile = 'd:\testing.xls',      @query = 'select * database_name..sqlservertable',      @colheaders =1,      @fixedfont=0,@lastupdated=0,@resultstitle='testing details'

to export data new excel file heading(column names), create following procedure

create procedure proc_generate_excel_with_columns (     @db_name    varchar(100),     @table_name varchar(100),        @file_name  varchar(100) )  --generate column names recordset declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100) select      @columns=coalesce(@columns+',','')+column_name+' '+column_name       information_schema.columns      table_name=@table_name select @columns=''''''+replace(replace(@columns,' ',''''' '),',',',''''')  --create dummy file have actual data select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'  --generate column names in passed excel file set @sql='exec master..xp_cmdshell ''bcp " select * (select '+@columns+') t" queryout "'+@file_name+'" -c''' exec(@sql)  --generate data in dummy file set @sql='exec master..xp_cmdshell ''bcp "select * '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c''' exec(@sql)  --copy dummy file passed excel file set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"''' exec(@sql)  --delete dummy file  set @sql= 'exec master..xp_cmdshell ''del '+@data_file+'''' exec(@sql)   after creating procedure, execute supplying database name, table name , file path  exec proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

all details here...

http://www.sqlteam.com/forums/topic.asp?topic_id=49926


Comments

Post a Comment

Popular posts from this blog

php - How to display all orders for a single product showing the most recent first? Woocommerce -

-
i have password protected page on frontend. on page, want display full list of purchases associated single woocommerce product id. option 1: i've looked through woocommerce docs , , found: wc_cli_order , list_( $args, $assoc_args ) function lists orders. presumably, orders can filtered product id [--=] filter orders based on order property. line item fields (numeric array, started index zero): line_items.0.product_id option 2: i found article , based on customer id. i've modified code based on guesses meta_key , meta_value. $customer_orders = get_posts( array( 'numberposts' => -1, 'meta_key' => '_product', 'meta_value' => get_product_id(), 'post_type' => wc_get_order_types(), 'post_status' => array_keys( wc_get_order_statuses() ), ) ); how display orders single product showing recent order first? here solution put based on these articles: http://www.w...
Read more

asp.net - How to correctly use QUERY_STRING in ISAPI rewrite? -

-
i'm having difficult time , hope guys can help. using helicon isapi rewrite version 3.1.0.104 on our iis server. edit http.conf file , have tried , still fail. this trying do: redirect url: https://www.domain.com/switch-by-version?version=2.8.5.2594 to: https://test.domain.com/load/load.aspx?tver=2.8.5.2594 the version number @ end source url change, , need target url have same version number @ end of url example above shows. i tried following not work: rewritecond %{query_string} ^version=(\d\d?)\.(\d\d?)\.(\d\d?)\.(.*)$ [nc] rewriterule ^/switch-by-version(.*)$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l] any appreciated! thanks. if it's running in equivalent context of .htaccess , rule shouldn't start / . here's simpler version: rewritecond %{query_string} ^version=(\d+\.\d+\.\d+(?:\..+)?)$ [nc] rewriterule ^switch-by-version/?$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l]
Read more

angularjs - How restrict admin panel using in backend laravel and admin panel on angular? -

-
https://scotch.io/tutorials/token-based-authentication-for-angularjs-and-laravel-apps following tutorials, try write authentication restrict admin panel, didn't understood how can that. this admin panel, admin panel don't want show , request database unauthenticated users. my laravel-routes route::get('/admin',function(){ return view('index'); }); route::get('/api/v1/employees/{id?}', 'employees@index'); route::get('/api/v1/users/{id?}', 'employees@users'); route::post('/api/v1/employees', 'employees@store'); route::post('/api/v1/employees/{id}', 'employees@update'); route::delete('/api/v1/employees/{id}', 'employees@destroy'); route::group(['prefix' => 'api'], function() { route::resource('authenticate', 'authenticatecontroller', ['only' => ['index']]); route::post('authenticate', 'authentic...
Read more