OpenRowSet And OpenDataSource Fail On Production To Open Excel Data

Jun 26, 2006

The following statement fails when using SQL Analyzer under sa but works on all of our development and staging server. All are SQL Server 2005 SP1. We upgraded production over the weekend from SQL Server 2000, creating a new instance machinenameSQL2005.

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=d:data est.xls',

'SELECT * FROM [Sheet1$]')

The error we are getting only in prodcution is:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Any help seriously appreciated,



Tronn

View 44 Replies


ADVERTISEMENT

Any Idea Why OpenRowSet To Open Excel File Doesn't Work Well In SQL 2005?

Mar 3, 2007

Maybe it worked once, but in most time it doesn't work, query like below

select top 10 *
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=\ws8webablefilessitefiles4000010
eibcactive.xls',
'select * from [crap2$]')


I got error

OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "microsoft.jet.oledb.4.0" for linked server "(null)".


but the same query can run without any problem on a SQL 2000 server run on a server in the same network.

Any idea?

View 12 Replies View Related

Openrowset Opendatasource

Feb 8, 2008



I need to read a tables in an acess database. The access database has no user/password. All the examples of openrowset show a userid and password. How do I read access table without them?

Should I use opendatasource?


thanks

View 1 Replies View Related

OPENDATASOURCE / OPENROWSET Via NT Authentication

May 10, 2002

Does anyone know if OPENDATASOURCE or OPENROWSET can be called via NT authentication, rather than with a SQL Login? If so, what would be the correct syntax to use?

View 4 Replies View Related

OPENROWSET And OPENDATASOURCE Truncation To 255 Characters

Dec 21, 2004

I have the following T-SQL commands which run without errors:

select Description_Text
from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:Import;','select * from company_finance.csv')

select Description_Text
from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:Import;Extended Properties="Text;HDR=Yes;FMT=Delimited"')...company_finance#csv

When I run either command on our TEST server, all the text in the fields retrieved is returned, but when I run on our PRODUCTION server, all fields are truncated to 255 characters. The source file company_finance.csv is identical on both servers. Both servers have the same O/S (Win 2K Server), same version of SQL Server 2000 and MDAC (2.71). I cannot figure out if the problem is on the SQL Server side, or has something to do with some registry settings or something else, but both the Text Driver and the Jet drivers produce the similar results on each servers. Are there some config settings in SQL Server or in the registry I can look at to determine why the truncation is occurring? Help!

Thanks for your time.

View 2 Replies View Related

Strange Problem With Using OpenDataSource() To Get Data From Excel.

Apr 7, 2005

:(
Hey guys. I got some problem here.

I have an Excel spreadsheet. the data is something like :

COL1 COL2 COL3
DATA1 1 DETAIL1
DATA2 2 DETAIL2
DATA3 3 DETAIL3
DATA4 4 DETAIL4
DATA5 5 DETAIL5
DATA6 6 DETAIL6
DATA7 7 DETAIL7
etc

there are 1877 * 3 cells in total in the spreadsheet.
but when I execute a query like:

select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=Test.xls;User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]

It returns 2132 * 5 units of data!!! And the "F4" and "F5" column just have NULL. so do row 1878 to 2132. I'm confused with that.

I need your help~ 3X in advance. :)

View 4 Replies View Related

Transact SQL :: Insert Data Into Excel Using OPENDATASOURCE

May 14, 2015

While trying to insert data into existing XLS file, using below command, i am getting following error.

Insert into OPENDATASOURCE( 'Microsoft. ACE.OLEDB.12.0','Data Source=e:ediuploadhello1.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
Select top 50 product_no From product_mst
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" could not INSERT INTO table "[Microsoft.ACE.OLEDB.12.0]". Unknown provider error.

View 2 Replies View Related

Delete Data In Excel Using Openrowset?

Feb 23, 2008

Hello,
I would like to delete all data from an excel sheet except the headers row.
Is there any way to do this using Openrowset with JET or something like that?
Thanks
Philippe

View 26 Replies View Related

SQL 2012 :: OPENROWSET To Read In Data From Excel 2010 File (XLSX)

Jun 12, 2015

I have a test server (TEST1) running SQL 2012 and Windows 2012R2. One of the developers wants to use OPENROWSET to read in data from an Excel 2010 file (an xlsx file).

I have loaded the Microsoft Drivers in "AccessDatabaseEngine_64.exe" and enabled the Ad Hoc Distributed Queries option in SQL.

This is the sample code we are working with:

SELECT
X.MEMBID
FROM OPENROWSET(
'MSDASQL',
'Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=etworkserverFolder1Folder2MEMBIDs.xlsx',
'SELECT * FROM [Sheet1$]'
) AS X

I can run the sample query from my laptop with SSMS (I have admin rights) and I can also run it as SA from my laptop. So all is good, right?

But if I RDC into TEST1, I cannot run the query. I get this error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver] Your network access was interrupted. To continue, close the database, and then open it again.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Excel Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x574 Thread 0xb74 DBC 0x1d07f08 Excel'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".But wait! It gets better.

I can run the query as SA from TEST1.

And of course, the developer can't run it either.

And it works fine in the production server.

I'm thinking the basics are there but something isn't right in some permission somewhere.

View 9 Replies View Related

Open A Second Connection When The Fist Fail

Mar 28, 2008

Hi, I have a web apllication using asp.net, I have two connectionstrings (cadenaCon and cadenaCon2) in web.config.
<connectionStrings>  <add connectionString="Server=SUNBOGW-MROMEROSQLEXPRESS;Database=ModulosWebkkk;UID=sa;PWD=martha;"  name="cadenaCon" providerName="System.Data.SqlClient" />  <add connectionString="Server=SUNBOGW-MROMEROSQLEXPRESS;Database=ModulosWeb;UID=sa;PWD=martha;" name="cadenaCon_bak" providerName="System.Data.SqlClient" /></connectionStrings>In the class file I have this sub and it works well
Public Sub New()         conexion = New SqlConnection(ConfigurationManager.ConnectionStrings("cadenaCon").ConnectionString.ToString)End Sub
My problem is I like to try to connect with cadenaCon and if this connection fails use the other. I used a try but it don´t work,
Public Sub New()   Try      conexion = New SqlConnection(ConfigurationManager.ConnectionStrings("cadenaCon").ConnectionString.ToString)   Catch ex As Exception       Throw New Exception(ex.Message)        conexion = New SqlConnection(ConfigurationManager.ConnectionStrings("cadenaCon_bak").ConnectionString.ToString)   Finally   End TryEnd Sub
Please I aprecitate a sugestion. Thanks 
Martha

View 1 Replies View Related

Cannot Open Report On Production Server

Oct 16, 2007

Hello there,

I have created a report with MySQL using ODBC connection.It works on my localhost server.then when i deployed it on the production server i get error saying.i checked my data source connection to my localhost and both have the same setting.what could be wrong.please help i am very desperate to make this work.thanks.
An error has occurred during report processing.
Cannot create a connection to data source 'Payment'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

View 2 Replies View Related

Cannot Open Report On Production Server

Oct 16, 2007

Hello there,

I have created a report with MySQL using ODBC connection.It works on my localhost server.then when i deployed it on the production server i get error saying.i checked my data source connection to my localhost and both have the same setting.what could be wrong.please help i am very desperate to make this work.thanks.
An error has occurred during report processing.
Cannot create a connection to data source 'Payment'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

cheers
zolf

View 4 Replies View Related

Integration Services :: Can't Preview Data In Excel Source Editor Unless Sheet Is Open?

Nov 10, 2010

I have a package which has an Excel source with the 'Data access mode' set to SQL command and then a sql select statement.  When I try and hit the 'Preview...' button below the 'SQL command text' window I get the following error:

 "Error at Standard Data Flow Tasks [source tasks name]: No column information was returned by the SQL command"
 
Ordinarily this would be down to the fact that my SQL is shocking, I hit the 'Preview...' button whilst the workbook the source is pointing at was open and it works fine??
 
I can't figure this out, but needless to say the package errors with a NEEDSNEWMETADATA when I try and run it.

View 17 Replies View Related

OPENROWSET And EXCEL

Nov 7, 2006

I having an excel file called TEST.XLS (in c: drive) which has 3 columns (c0,c1,c2) & 8 rows
c0 c1 c2
1 a A
2 b B
3 c C
4 d D
5 e E
6 f F
7 g G
8 h H

In sql server i have one table called TEST which has three columns
c0 char(10)
c1 char(10)
c2 char(10)


when i runnig the following query

select *
into TEST from openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:TEST.xls;HDR=YES',
'select * from [Sheet1$]')



i am getting error like this

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

Version : Sql server 2000 & Excel 2002

Anybody having any idea why this error occuring?
thanks in advance...
Philkos

View 2 Replies View Related

OPENROWSET To Excel

Jan 14, 2007

Hi Anyone I am new member and happy to be here.

I am having trouble exporting the correct data from sql server to an excel spreadsheet using 'OPENROWSET'

The problem is that although the data from my sql table is say :
'a',1,2,3 , the excel spreadsheet sees the data as :
'a','1,'2,'3 ..1,2,3 are of course numbers NOT text, its just that the driver has put a single appostraphy before the number !

I know there is a bug with the ISAM driver but has anyone managed to solve this or has anyone have any alternatives ?

best regards

Reza

R Khan

View 3 Replies View Related

Openrowset And Excel

Apr 17, 2007

I'm doing an openrowset query on an excel sheet. (Using SQL Server 2005) Everything works great, except that I have one column that has both numeric and text data in the spreadsheet. The query returns that column as datatype varchar but puts nulls in the rows that have numeric data in the spreadsheet.

Any suggestions?

I run:

select *
from openrowset('microsoft.jet.oledb.4.0',
'Excel 8.0;database=[filepathandname],
'select * from [Sheet1$A4:G5000]')

on spreadsheet data that looks like:

Model_id Model_name
. . .
123 t4556
124 x225
125 455
. . .

and get something like . . . .

Model_id Model_name
. . .
123 t4556
124 x225
125 NULL
. . .

View 1 Replies View Related

OpenRowSet With Excel DENIED

Oct 7, 2005

Same with OpenDataSource. SQL 2000, logged in as non-SA.Getting message:Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has beendenied. You must access this provider through a linked server.Can run with SQLOLEDB with connection string to same server(obviously), but when trying to access an Excel file, get the messageabove.It appears that the file name given in the connection string to Excelis with respect to the SQL server, so I have used UNC names to thefile. Nothing.Want to avoid DTS when it's a table that I need and can't use a linkedserver because it's always a one time thing.Any tips appreciated!

View 2 Replies View Related

Openrowset Excel 2007

Jul 31, 2007

Haven't been able to find much information on using openrowset with excel 2007 xlsx files. I've tried the following with no success so far.

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=\serverfolderfile.xlsx;HDR=No;IMEX=1',
'SELECT f1 FROM [Raw Keywords$] WHERE f1 IS NOT NULL')

If anyone knows the correct way to do this please tell me.

View 3 Replies View Related

How To Query An Excel File By Using Openrowset

Mar 1, 2007

Hi everyone,

I'm trying to query an excel file and I get a mistake. The query is as follows:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:ExcelFile.xls', 'select * from Sheet1')

and I get the following error message:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'Book1'. Make sure the object exists and that you spell its name and the path name correctly.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

I'm thanking any help that you can give.

Thanks,

Oscar.

View 7 Replies View Related

T-SQL (SS2K8) :: Access EXCEL Using OPENROWSET

May 22, 2014

I'm trying to access Excel file from SQL Server management studio using OPENROWSET using the below query but getting the error listed below.

Query:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=E:TestFilesExcelFile.xlsx',
'SELECT * FROM [Sheet1$]'
)

Error:
Msg 7415, Level 16, State 1, Line 2
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.

View 9 Replies View Related

Can Not Access Excel File Using OpenRowset

Aug 8, 2007



I am using SQLServer 2005 SP2. I enabled the Ad Hoc Distributed Queries and DisallowAdhocAccess registry option is explicitly set to 0. Query is working fine when I remote desk to the server and execute when I run same query from my workstation I am getting following error


Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


Any help is appreciated.
Thanks
--
Farhan

View 8 Replies View Related

OPENROWSET ( CAN WE CREATE FILE WITHOUT EXCEL TEMPLATE??)

Mar 20, 2008

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D: esting.xls;', 'SELECT * FROM [SheetName$]') select * from pubs.dbo.authors
 
I am using similiar query to above to create a excel file, however for this to work, I need to create a template file which has the same columns as the authors table. Is there a way to NOT to define template columns , as I some times will not know which columns will be available... as teh query is dynamic....

View 4 Replies View Related

T-SQL (SS2K8) :: OPENROWSET When Excel Worksheet Name Is Unknown

Sep 7, 2011

I've created a stored procedure that accepts two arguments: the name of an Excel workbook (@workbookBillRun), and the name of the first worksheet found in that workbook (@worksheetBillRun).

Through dynamic SQL, I'm able to construct a statement that pulls out data.

SELECT @sqlBillRun = '
SELECT [ACCTNUM], [INVNUM], [RECURRING], [NON RECURRING], [USAGE],[DISCOUNT],[TAXES]
FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;;Database=' +@workbookBillRun+ ';HDR=YES'',
''SELECT * from [' +@worksheetBillRun+ '$]'')'

The workbook name will always be known: when the user uploads the file, I change the name to match a certain pattern. Example: 2011-08 AUG.xlsx

However, I won't always know the worksheet name. The workbook *should* contain just one worksheet; and the worksheet *should* be named identically to the workbook (2011-08 AUG), but it may not be named as such. And if I pass an invalid worksheet name, the code above will fail.

I've created error handling to alert the user of an incorrectly named sheet. However, I was curious whether I could extract the name of the first worksheet in the workbook from within T-SQL.

View 6 Replies View Related

Export In Excel With Using OPENROWSET From Stored Procedure

Nov 22, 2007



Hi
I am Utarsh Gajjar.
I am working on SQL Server 2005.

I have following Stored Procedure.

----------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ExportInExcel]
@QueryString VarChar(8000) =''
AS
BEGIN TRY

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Customers.xls;IMEX=1','SELECT * FROM [Sheet1$]')
EXEC (@QueryString )


END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(8000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH

-------------------------------------------------------------------------------

Error is

-------------------------------------------------------------------------------
(0 row(s) affected)

Msg 50000, Level 16, State 2, Procedure ExportInExcel, Line 31
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not support the required transaction interface.

(1 row(s) affected)



how can i solve this problem?


Thanks in advance.

View 16 Replies View Related

Import Excel-File (OPENROWSET) On X64 SQL Server

Jan 7, 2008

Hi, recently I encountered the following problem:
I tried to execute a stored procedure on the newly installed SQL 2005 Server (now on x64 Win Server 2003) which imports an Excel-File into a DB table.
We use OPENROWSET to access the Excel data. But I recognized this is dependent on Jet OLE DB which seems is not available for x64 windows.

Is there another way to import excel data using a stored procedure.

thank you in advance, rene

View 12 Replies View Related

Passing A Variable To A Linked Query (OPENROWSET For Excel Syntax)

May 11, 2007

Hello,

I responded to a very old discussion thread & afraid I buried it too deep.

I have studied the article: How to Pass a Variable to a Linked Query (http://support.microsoft.com/default.aspx?scid=kb;en-us;q314520)



but I have not gotten all the ''''' + @variable syntax right.



Here is my raw openrowset with what I am aiming at.






Code Snippet

-- I want to use some kind of variable, like this to use in the file:

DECLARE @FIL VARCHAR(65)

SET @FIL = 'C:company foldersDocumentationINVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:company foldersDocumentationINVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT



Anyone game? Many thank-yous, in advance.



Kind Regards,

Claudia.

View 1 Replies View Related

Reporting Services :: Open Report In Excel But Need Raw Data Not Duplicate Of Report Output

Nov 23, 2015

We are about to implement SSRS reports (SQL Server 2012), and have found one issue we don't like. The report looks great when we open it in pdf format. However, when we open it in Excel format, we want it to be the raw data, not look like the pdf report. I tried the simpl headers, and that removed the page header/footer. However, the report format was still in play.In other words, we would like the report when opened in Excel, to look just like it does when we open it as a CSV.I have tried to modify the config file a couple of times. Here is my latest attempt:

Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
     <Name Language="en-US">CSV (comma delimited)</Name>
    </OverrideNames>
   </Extension>
   <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <OverrideNames>
     <Name Language="en-US">Excel (csv)</Name>
    </OverrideNames>
   </Extension>

Essentially I have set Visible = "false" for the Excel type, and tried to duplicate the CSV, changing the name to make it look to the user like it is "Excel". This fails. I checked the event viewer, and I see two entries.. The RSReportServer.config file has been modified. (Information)The value for UrlRoot in RSReportServer.config is not valid. The default value will be used instead.I literally took the file, opened it in Notepad, pasted in my changes, from another notepad window, and tried to run the report. I did not see EITHER of the two names I expected, using the OverrideNames tag. As I said earlier, I was able to affect the outcome, by setting SimpleHeaders to true (or however it is identified). That did work in that the page header and footer was removed.

View 6 Replies View Related

Excel Job &#043; Column Import Fail

May 23, 2008

Hi all, need help before i break this pc! trying to get an import job to read from an excel file. Normally this works fine, no issues but have a certain excel file that is just not importing correctly. one row is importing nulls for some values but without any visible reason. I have a file with over 15000 rows. 9 columns. last column stores a year in the format yyyy. this is the problem column. in the import job it shows up as a float. have checked the format of the cell and it says General in the excel file. when i execute the job over 5000 row come throught with null Years. cant see a reason for this. anyone able to shed some light please..

N.

View 3 Replies View Related

Recurring Issue - OPENROWSET For Excel Query Stops Working , Cured By Restart Of SQL Server

May 25, 2007

Hi,

I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.

I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.

Thanks,
Darrell Young

View 3 Replies View Related

Import Excel Into SQL 2005 Working In Development But Not In Production

Jul 26, 2007

I have a simple code that uses a the file upload control to read an excel sheet and upload the data into a SQL 2005db.
I'm using Visual Web Developer and Sql's express edition to test it.  It works fine when I test.  However, when I push it up to the production server and try it via the any other pc it does not.  The page loads fine.  However, when it starts to upload it errors out.
Any reason why?  I've never seen this happen.
Here's the code.  Thanks in advance. Protected Sub BtnUpload2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnUpload2.Click
UploadTextDocument()
End Sub

Private Sub UploadTextDocument()
Dim location As String = FileUpload1.PostedFile.FileName.ToString
' Connection String to Excel Workbook
Try
Dim excelConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location)
' Create Connection to Excel Workbook
Using connection As Data.OleDb.OleDbConnection = New Data.OleDb.OleDbConnection(excelConnectionString)
Dim command As Data.OleDb.OleDbCommand = New Data.OleDb.OleDbCommand("Select BuilderID,SeriesID,OptionLevel,CommunityID,PhaseID,PlanID,ElevationID,OptionID,CurrentSalesPrice,LocalComments,Active,DateAdded,DateAvailable,DateInactive,SalesPriceEffective,SalesPriceExpires,PreviousSalesPrice,CutOffNotBefore,CutOffNotAfter FROM [Data$]", connection)
connection.Open()
' Create DbDataReader to Data Worksheet
Using dr As Data.Common.DbDataReader = command.ExecuteReader()
' SQL Server Connection String
Dim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString
' Bulk Copy to SQL Server
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = "ExcelData"
bulkCopy.WriteToServer(dr)
End Using
End Using
connection.Close()
End Using
LBError.Text = "The spreadsheet was successfully uploaded."
Catch
LBError.Text = "There was an error. Check the spreadsheet for correct format."
End Try
End Sub 
 

View 14 Replies View Related

Open Excel File

May 12, 2006

hey
I am just trying a simple query of an Excel file.

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:MyFile.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')


and i am getting this error

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].


I know for a fact that the path is correct and that it is the path of the server. So what is the problem with this??

tibor

View 3 Replies View Related

Open Excel File From SP

Aug 4, 2014

How can I open an excel file from a stored procedure? what I like to do to run my SP and at the end add a command to open an excel sheet I have?

View 3 Replies View Related

Openrowset To Obtain CSV Data In SQL Server

Aug 2, 2007

I am trying to obtain data from CSV file (in Excel) to SQL Server table using Openrowset command. There is one issue I am facing – The columns which have blank values in the first 20 records and then only has a proper value in the 21st record are not getting imported correctly. For such columns, I get a NULL for the proper value as well. I need to proper value to come. Any suggestion?

Thanks in advance,

sajmera

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved