OpenRowset On A DBase IV File

May 26, 2000

Has anyone had any experience using the openrowset function to access a dbase IV file? I can access the file using ADO from within VB but not getting anywhere using openrowset. If anyone knows the syntax or has an example it would be greatly appreciated.

Thanks in advance...

Jim

View 4 Replies


ADVERTISEMENT

How Do I Export A Table To Dbf (dbase) File ?

Dec 29, 2006

besides using the costly EMS Data export application?

I'm using the SQL 2005 Express version......I heard the enterprise manager can do the job but I can't afford a standard edition yet....

View 11 Replies View Related

Trying To Access A DBase III File With A SSIS Package

May 4, 2006

I am trying to get a handle on the new SQL Server Integration Services in SQL Server 2005. There is a legacy DTS package that I need to get working on our new server (using SQL Server 2005) but I can't seem to get it to work.First, I migrated it and attempted to run the package. It gave me many errors. So, I thought I would just rebuild it in integrated services. Everything was going smooth until I got to the real data transformation part.I added a data flow task and it took me to the Data Flow tab. Then I added a DataReader Source. I am trying to read from a dBase III file and I assumed I would be using an ODBC driver for that. Well... it has been hell trying to get it to see the dBase III file and access it.I have tried two solutions from the internet.This one and this one (by Wenyang) with no positive results.No matter what I do I still get an error in the bottom of the Advanced Editor for DataReader Source that reads: "Error at Sales Transformation [DataReader Source [81]]: Cannot acquire a managed connection from the run-time connection manager."Has anyone attempted anything like this before? Are there any SSIS experts around here that can drop me a few pearls of wisdom?Thanks in advance... I'm dying here.

View 3 Replies View Related

Openrowset To Text File

Mar 29, 2002

Can I use OPENROWSET in Query Analyzer to connect to a text file in a SELECT statement and/or an INSERT statement? I need to move data from a text file to a SQL Server db. But, I can't figure out the right parameters for the OPENROWSET method. Thanks for any help.

Gerald

View 1 Replies View Related

OPENROWSET On XML File Through HTTP

May 7, 2008

Hi everyone,


I am currently using the OPENROWSET command to read a XML file.


-- Use OPENROWSET to read an XML file from the file system

SELECT @xml = BulkColumn

FROM OPENROWSET(BULK 'c:file.xml', SINGLE_BLOB) TempXML


However, the file I wish to process is being generated externally and published through a web server. The file is constantly being updated, and I need to have the latest data frequently. Is it possible to do something along the lines of:


-- Use OPENROWSET to read an XML file from the file system
SELECT @xml = BulkColumn

FROM OPENROWSET(BULK 'http://webserver/file.xml', SINGLE_BLOB) TempXML


If it is not possible, can someone recommend the best method to copy/download the file? I have considered standalone Windows Services and utilizing SQL Server. Are there significant pros or cons to either approach? Are there other methods I am not awere of? Any feedback on this would be greatly appreciated.


Thanks,


Matt K.

View 3 Replies View Related

From Sql Column To File, OPENROWSET?

Aug 28, 2006

Hi There

I am using OPENROWSET to import a file from disk to a varbinary(max) column in sql server.

However as far as i can see OPENROWSET is only to import into Sql Server. However i need to reverse this as well, by that i mean export a varbinary(max) column data to a file on disk. How can i do this ? (The file is a compress text file).

How does this work for file types, i mean you can import any file as binary but when you export it back to disk will the file typr still work, will a .xls or a.exe imprted and exported like this still function 100%.

Thanx?

View 9 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

OPENROWSET Format File Dynamic

May 22, 2008

Hi,

Can someone please point me as to where I can find info for the following. I have seen this somewhere but I am unable to find it.

I am trying to do
INSERT INTO Table
SELECT ...
FROM OPENROWSET(BULK '\sharedDriveDataFile.txt',
FORMATFILE = '\sharedDriveFormatFile.txt',
FIRSTROW = 2)
AS Q

How can I make that shared drive dynamic? as such..hoping not to use dynaic SQL
INSERT INTO Table
SELECT ...
FROM OPENROWSET(BULK @SharedDrive DataFile.txt',
FORMATFILE = @SharedDrive FormatFile.txt',
FIRSTROW = 2)
AS Q

View 1 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

Usinf OPENROWSET To Read From A .ttx File

Mar 5, 2008


I have a .ttx file similar to this:

"xxxxxxxxx" "dc8" "184:30" "168:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" 0 0 0 1

I want to read from this file from T-SQL, I saw in a previous post that its possible to do it using OPENROWSET (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)

I don€™t have a clue what parameters i must use on the OPENROWSET T-sql command (provider name to ttx ?)

I would appreciate if anyone could give an example of how to do this

Thanks !

View 7 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

Openrowset(bulk '\server1c$file.txt') Fails!

Aug 3, 2007

select * from openrowset(bulk '\server1c$file.txt', SINGLE_BLOB) as t
works from sql server itself, but doesn't work from any other machine. I got
"Operating system error code 5(Access is denied.)."
I am running as the domain admin, the file.txt has full control for everyone. In server1 even log, I see Anonymous Logon.


Please help!

Thanks,

Bo

View 3 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

OPENROWSET, Querying External Text File

May 8, 2006

Hi,

I have to write a query that queries a database table and a text file wich is delimited using fixed width values. I have seen some examples using access or another databse but not much about a text file. Is this the best way? Idon't want to set up a linked server. I want everything to be within the query.

My understanding is that I should be able to do it since a text file can be a valed OLE DB datasource, but I am having trouble finding example syntax. Can all my connection info be passed with the OPENROWSET function? Can I do it by just writing a query? Or do I have to set up and configure other things?

Any guidence, tips, advice, examples, or links of syntax appreciated.

Mike

View 1 Replies View Related

How To Read CSV File In SQL Server 2005 Using OpenRowSet Function

May 4, 2007

Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.



Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

View 8 Replies View Related

SQL Server 2012 :: Loading Image From File System Into Query Using Openrowset

May 14, 2014

I have a directory with images and a table in my DB with the path of each file. The main application allow me to create reports where I can display an image, so I was thinking to use a query like:

SELECT [ID]
,[PT_CODE]
,[FILE_PATH],
CASE WHEN [FILE_PATH] IS NOT NULL THEN
(SELECT * FROM OPENROWSET(BULK [FILE_PATH], SINGLE_BLOB) TT)
END
AS IMAGE_LOADED
FROM [DB].[dbo].[TABLE_MR_FILES]But I keep getting the error:

Incorrect syntax near 'FILE_PATH'.I have try multiple combinations without luck to make the OPENROWSET read the path stored in the column [FILE_PATH]. What am I missing?

Note: I am using MSSQL 2012. I don't want to import the images into the DB just load them in the fly as needed by the report runned from the application. I have full access to the DB so if a store procedure is the solution I can go with it.

View 4 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

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

Transfer From DBase To MS SQL

Jun 12, 2004

In my project, I need to transfer the database from dBase to MS SQL 2000. I only have the .dbf files for the dBase database. Can someone give me a help? Thanks in advance.

View 1 Replies View Related

How Can I Access Dbase?

Aug 30, 2006

Hi,

I want to access dbase database, can anyone help?

I try :

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:Account;Extended Properties=DBASE III;',
TRANS.DBF)

and I got the following message :

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
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)".

Thanks and I really appreciate any help
joe

View 3 Replies View Related

SQl Dbase Query From Different Folders

Feb 4, 2008

HI, I have a Dbase Database stored in several subfolders. I allready made my connection with a sqldatasource and a gridview, and all works fine when i do a query with the tables in to root folder. The problem is when I try to include tables from the subfolders, the compiler send me a syntax error in the FROM Clausule
I Allready try this.
 SELECT products.id, products.descri, condition.discount  from products, clientscondition where products.line=condition.line
 SELECT producst.id, products.descri, clientscondition.discount from products, clientscondition where products.line=condition.line
 SELECT products.id, products.descri, condition.discount from products, condition in 'clients' where products.line=condition.line
 SELECT producst.id, products.descri, clientscondition.discount from products, 'clients'.condition where products.line=condition.line
And many other different combinations. I would like to be helped if you know the correct syntax or a link where i can consult the syntax for VB.net and dbase with several folders
Another option a tried is having a query from 2 different datasources, but I don´t know how to make a reference to the 2 sqldatassources in a new one to be linked to the gridview

View 7 Replies View Related

Copy Dbase To SQL Performance

Mar 6, 2002

I am searching for a better way to move data from DBase3 files into SQL Server 2k. The Dbase file has approximately 2 million records and after applying a SELECT statement to retrieve the data I need to move, there are still over 600,000 records. To move this data I have used DTS (data pump) to transform the data- THIS IS UNBEARABLY SLOW. Next, I created an exe to transform the data to a flat file and then used BULK INSERT to copy the data. The exe is slow, but the BULK INSERT is fast. I have moved the source and destination to the local Server to increase performance, but with little imporvement.
My question:
Is there a better way to move large amounts of data from DBase3 files to SQL Server without waiting 38 hours to move 600,000 rows? Thanks for your help.

View 1 Replies View Related

MS Access DBase Question

Feb 7, 2006

Hello a,,

I have a MS SQL database that I use MS ACESS to add data to and also to query.

My question is how can I setup the MS Access database so that it will not allow duplicate entries to be added to the MS SQL database? I know How I could do it using php via a webpage but I need to be able to do this using the MS Access program directly since thats what we use to add and search the dbase with.

thanks

View 5 Replies View Related

How To Transfer Data From DBase To SQL?

Sep 13, 2004

I have some database files with .dbf from dBase and I want to transfer data from the .dbf files to Microsoft SQL database (2000). Can someone help me with that? Thanks.

View 4 Replies View Related

Linked Server SQL To DBASE(DB4)

Mar 23, 2006

Please anybody help me.
Can't get oledb for odbc work.Need an explanation what to put in each field of linked server properties General tab to get connected. Have ODBC server DSN working fine with Crystal reports.
Did this:
exec sp_addlinkedserver @server = 'ODBC_TEST' ,
@srvproduct = 'dBase',
@provider = 'MSDASQL',
@datasrc = 'DB4_GBS'
GO
-- Adding linked server login:

EXEC sp_addlinkedsrvlogin 'ODBC_TEST', 'false', 'sqlLogin', 'sqlpassword', NULL
Getting error : datasource name not found and no default driver specified.

I also attempt to create OLEDB ODBCless link and can't do it because after that I have to change path in TEMP and TMP variables and restart db, but I cann ot restart the server.
:eek:

View 10 Replies View Related

Conversion Of DBase II To SQL Server

Aug 28, 2007

I have been asked to look into converting a DBase II database on an AS400 to MS SQL Server 2000 or newer running on Windows 2003 or newer.

I know that it can be done but how hard will it be and how long could it take?

Are there any conversion packages out there that will do the majority of the leg work for us?

We also need to convert all of the screens written in RPG to a web based application. We want to get rid of the AS400.

Thanks
enak

View 5 Replies View Related

SQL Server And Clipper/dBase

Jul 20, 2005

I would like to see a Clipper/dbase DBF file as a table in SQL Server7.0. How can I SELECT rows from DBF file? Should I use OLE DB Provideror ODBC, and how?

View 10 Replies View Related

How Can I Get Sql Server Dbase Software?

Nov 7, 2007

Dear All,

Can some one help me to get or how to get an SQL SERVER dbase software cos have been trying to download bt i dnt get it right.

Urgent!!!!!!!!!!


OBALALU, Babatunde S.

View 5 Replies View Related

Detach Dbase On SAN With SQL2005

Apr 16, 2007

I'm not sure if this is the right place for this but I have an issue.



I have a SQL2005 server with SAN drives for the MDF/LDF files. I have updated recently to SP2.



When I detach a Dbase using the appropriate command it strips the Security on the file level (which SP2 is supposed to fix) and I am unable to attach the database.



I am using a domain account to do this with from the query analyser window.



Anyone have any thoughts on this?

View 6 Replies View Related

Connect Dbase II In Ssis

Aug 22, 2006

I am having trouble connecting dbf of version dbase II. I can connect dbase III and dbase IV in ssis package,but can't connect dbase II.hope your help!

View 3 Replies View Related

Dbase Access On A 64-bit System

Nov 21, 2006

Hello all...

I created several SSIS packages on a 32-bit system, using odbc to to access the dbase source file. Finally, after setting up a proxy, etc., we got the package to run and import data. We then needed to move the packages to a 64-bit system. So, we built our manifests, and installed all of our packages. We are using several dtsConfig files for our packages, and in there we specify the DSN that we will be accessing. I understand that MS does not support odbc drivers in a 64-bit world (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=85703&SiteID=1). Has anyone been faced with this issue? How did you get past it? I accessed the 32-bit odbc drivers on the 64-bit system, and i setup a dsn that points to the dbase file, but when i run the job, the log file has an error that the dsn was not found. However, when I run the package itself, it runs as expected...

Please help!!  :-)

Craig

View 1 Replies View Related

How To Generaate A Report With ASP.Net From A MS_SQL DBase

Mar 4, 2004

You guys are probably going to tell me to go to school (training, and I'd like to)
My question is, I have a stored procedure in our MS_SQL 7 dbase that pukes out the info in a table with MSAccess. What I would like to do is, have a nice customized report be generated via http link. I designed and made such a report that queries the SQL sp_ with Crystal Report 8 I installed the browser plug in on a test workstation launched the report (via web) and I get prompted for access credentials (username & Password). Nothing works except the sa account in on the SQL server (and I am a sysadmin on that server),. I am giving that up in hopes to achieve this goal with a ASP page, but I really do not even know where to start. remember I have a storeed procedure that does the querying for me, so if there is a way to make "form that queries the sp_ maybe? ...I don't know really new at this.

View 1 Replies View Related

Update Query Based On Another Dbase

Jun 5, 2000

Can i update a field in a table in 1 dbase based upon data in another table in another dbase.
could somebody let me know if this is possible.
Thanks

View 1 Replies View Related







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