I'm trying to access a large Oracle database through SQL Server using OPENROWSET in client-side Javascript, and not having much luck. Here are the particulars: A SQL Server view that accesses the Oracle database using OPENROWSET works perfectly, so I know I have valid connection string parameters. However, the new requirement is for ...
hi all, I'm trying to create a new role on my analysis DB. i have tested multiple combination : when running a simple MDX query with Openrowset => it works. when running the it works. when trying to create anew SSAS role from SQl relational DB using openrowset=> ERROR. here is the code I'm trying SELECT * FROM OpenRowset('MSOLAP', 'D...
This question is long winded because I have been updating the question over a very long time trying to get SSIS to properly export Excel data. I managed to solve this issue, although not correctly. Aside from someone providing a correct answer, the solution listed in this question is not terrible. The only answer I found was to create...
I'm trying to write a stored procedure that will read an Excel file into a temp table, then massage some of the data in that table, then insert selected rows from that table into a permanent table. So, it starts like this: SET @SQL = "select * into #mytemptable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+@file+";HD...
All, I am successfully exporting to excel with the following statement: insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\template.xls;', 'SELECT * FROM [SheetName$]') select * from myTable Is there any standard way to use this template specifying a new name for the excel sheet so that the template never get...
My users upload excel files to a folder on the web server. I execute a sproc on the sql server that uses openrowset to open the excel file and import the data into a db table. All of this works great when the sql server and the web server are on the same machine (dev environment) but not when they are separate. I found a post that des...
Been investigating for a while now and keep hitting a brick wall. I am importing from xls files into temp tables via the OpenRowset command. Now I have a problem where I’m trying to import a certain column has a range values but the most common are the following. Columns structured as long numbers i.e. 15598 and the some columns as stri...
Having an interesting issue. I'm reading from an excel file on a server via an OpenRowset in Sql2005. I've run the query a number of times without any problems. I've just gone out for a quick meeting and suddenly I get the error "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"" I've made ...
I'm using OPENROWSET(BULK ...) to insert the contents of a file into my table. The problem is that I also need to specify the value of another column in the same INSERT statement. I have something like this: INSERT INTO MyTable SELECT * FROM OPENROWSET(BULK 'c:\foo.bin', SINGLE_BLOB) I'm sure there's a way to also specify the value ...
I have a stored procedure that imports differently formatted workbooks into a database table. does work on them then drops the table. Here is the populating query. SELECT IDENTITY(INT,1,1) AS ID INTO #test101 FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=WorkBook.xls',[WorkSheet$]) Some spreadsheets seem to crea...
I'd like to extract data from a .csv file. I cannot use OpenRowSet on a 64-bit machine because it says: Msg 7403, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" has not been registered. and there is no MSDASQL entry in the Linked Servers -> Providers section of the SQL Server 2005 SSMS the same way 32-bit ones have. Can som...
Hi, I have a Helper DB which is on 32bit sql server and i have added a linked server of 64bit, to perform Excel Import operation as Jet.oledb driver is not supported on 64-bit sql server machine. Everything is working fine, but i have to maintain transactions for insert ,update,delete that happens on linked server database, I have co...
The error: OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. And the answers I'm seeing is a conflict between 64 bit Sql Server and 32 bit Office. Is there a way to run an openrowset on Excel into Sql Server? insert into dbo.Fi...
Hi, I am using this: insert into bla select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=c:\bla.xls', 'select * from [Sheet1$]'); but for some reason some values contain null although the original data definately contains values (e.g. 'abc'). What could be the reason for this strange behaviour. Th...
Say that the raw text of CSV exports and an associated timestamps are stored in a database, where one record is equivalent to one export. Does anyone have a way to execute a query on the CSV file stored in that field without creating a second connection to the database or exporting the data to a file and then reopening it using the csv ...
Hi all, I have a table (AmenityData) of data and a column of this table contains postalsectors e.g. E14 7 I also have an Excel spreadsheet with a list of postal districts e.g. E14 I need to get all the data out of the AmenityData table where the postal districts are LIKE the postal sectors e.g. WHERE [PostalDistricts] + '%' LIKE [Post...
Here is some sample code that worked fine in a 32-bit machine (SQL server 2005) and needs to be updated for a 64-bit... I know this is a common problem but have not been able to find how to fix it! DECLARE @sourceFile varchar(255), @testRows int SELECT @sourceFile = @xmlInfo.value('(/SelectFile/DataSource)[1]', 'VARCHAR(100)') ...
Am trying to import Excel 2003 data into SQL table for SQL Server 2008. Tried to add a linked server but have met with little success. Now am trying to check if there's a way to use the BCP utility to do a BULK insert or BULK operation with OPENROWSET, using a format file to get the Excel mapping. First of all, how can I create a form...
Hi all, I'm trying to create a linked server in Sql server 2008 R2, just tried downloading the '64-bit version of the Office 2010 Access AD Engine' exe from Microsoft. Unfortunately, I had tried adding a linked server, BEFORE making this installation, and it failed: EXEC sp_addlinkedserver 'LinkedServer1', 'Excel', 'Microsoft.Jet.OLE...
This can be a debatable answer, but I'm looking for the case where a local Excel file needs to be exported to a local SQL Server 2008' table. Has anyone ever had the chance to check execution time to compare OpenRowSet/OpenQuery/OpenDataSource for a very large file import in SQL Server 2008? I'm able to use any of the 3 options, and th...