April 22, 2008 at 4:46 am
Hi All..
I have created form in my application for exporting data into excel file from Sql Server 2005.
For that i am using the following Stored Procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =========================================================================================================
-- Author: Aes Tech Team
-- Create date: April 22, 2008
-- Description: This it used to export the tables into Excel File. This is done using Linked Server concept.
-- =========================================================================================================
ALTER PROCEDURE [dbo].[prExportToExcel]
@FileName Varchar(300) ,
@Table varchar(40),
@DbName varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
---- Drop Linked Server..
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
AND srv.name = 'ExportData')
Begin
EXEC master.dbo.sp_dropserver @server=N'ExportData', @droplogins='droplogins'
-- print 'Dropped..'
End
--Creating the Linked Server with the table
EXEC sp_addlinkedserver 'ExportData','Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', @FileName ,NULL,'Excel 8.0'
--Exec('EXEC sp_addlinkedserver ''ExportData'',''Jet 4.0'', ''Microsoft.Jet.OLEDB.4.0'',''' + @FileName + ''',NULL,''Excel 8.0''')
-- Print 'Created'
EXEC sp_addlinkedsrvlogin 'ExportData','TRUE',NULL,'sa','almighty'
-- Print 'Login Provided'
--Inserting the data into Excel WorkSheets
Exec ('INSERT INTO ExportData...[' + @Table + '$]
select * from ' + @DbName + '..' + @Table )
-- SELECT @FileName, @Table
END
--Exec [prExportToExcel] 'E:\Temp Documnets\Export Excel Worksheet.xls','tbl_dept','test'
This procedure is working fine.. when executing from the SQL Server 2005.
While I am trying to execute this procedure from front end (Vb.net 2005),
I am getting the follwing error..
Error:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExportData" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExportData".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ExportData" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Can any one help me in solving this issue????
Thanks in Advance.....
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
April 24, 2008 at 11:52 am
April 24, 2008 at 3:29 pm
I had a similar headache with this error message a few days ago with linked servers a few days ago when I was trying to read a .CSV file that was locked using a DSN.
I ended up using a C# program to copy the file so that the copy wasn't locked before reading the copy.
However, if you insist on using SQL, try reading this link to look at the nolock and set transaction isolation level statements
http://qa.sqlservercentral.com/Forums/Topic344649-149-1.aspx
April 24, 2008 at 10:57 pm
Hi bledu..
Here i suppose to export lacks of data.. Each table contains more than 80,000 records..
While i query and export the data into Excel it consumes more than 5 min for exporting the data..
For reducing the time consumption, i have tried this method of approach to solve it like exporting the data from the SQL Server 2005.
If you find a better way for exporting into Excel after querying data in VB.Net with consideration of less execution time consumption.. Just convey me...
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply