SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reading Ad Hoc Text Files with OpenDataSource


Reading Ad Hoc Text Files with OpenDataSource

Author
Message
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2406 Visits: 920
Comments posted to this topic are about the item Reading Ad Hoc Text Files with OpenDataSource

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218402 Visits: 41996
Good, short, sweet coverage of OpenDataSource and some of the key advantages to using it and when to use it... Nicely done!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GG-173138
GG-173138
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 99
Hi,

Have you found a way to do this in SQL 2005 64bit?

Apparently there is a MSDASQL provider being released in Longhorn for 64bit , but I haven't been able to find any other information.

Your help will be much appreciated!

Regards,
GG
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2406 Visits: 920
GG (12/13/2007)
Hi,

Have you found a way to do this in SQL 2005 64bit?

Apparently there is a MSDASQL provider being released in Longhorn for 64bit , but I haven't been able to find any other information.

Your help will be much appreciated!

Regards,
GG


I have been searching for that ever since my company got a 64bit server, last week. My current ugly work around is to use linked servers to execute the procedures on a 32 bit server and then bring the results across to the 64 bit server.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Philippe Cand
Philippe Cand
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 294

My current ugly work around is to use linked servers to execute the procedures on a 32 bit server and then bring the results across to the 64 bit server.

Same for me, No JET support in 64 bits.
About SQL2008? is it going to work in either 32 or 64 bits?

BI Guy
pcwc66
pcwc66
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 174
It definitely helps me to understand about OpenDataSource. I'm working on a project to see how to create sample SQL databases (32bit/64bit SQL Server 2005/2008 including Express Edition) for a .NET application. I'm trying to figure out the following items:

1. How to create the databases?
e.g.
- using sql scripts
- backup/restore
- detach/attach
2. Where to store the sample data?
e.g.
- xml
- mdb
- text files
- csv
- backup
- detached files
3. How to import the data?
e.g.
BCP
BULK INSERT
Import/Export Wizard
SSIS
OPENROWSET
OPENDATASOURCE
OPENQUERY
SqlBulkCopy
Backup/Restore
Detach/Attach

What are the pros and cons (especially what datatype cannot be imported) in each option? What data source should I create (e.g. text file, XML, Access database(MDB), Excel file (CVS)) if portability is a must? Ideally, the data importing can be initialized from a .NET 3.0/3.5 application instead of thru SSMS or command prompt.



timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2406 Visits: 920
pcwc66 (12/13/2007)
It definitely helps me to understand about OpenDataSource. I'm working on a project to see how to create sample SQL databases (32bit/64bit SQL Server 2005/2008 including Express Edition) for a .NET application. I'm trying to figure out the following items:

1. How to create the databases?

2. Where to store the sample data?

3. How to import the data?

What are the pros and cons (especially what datatype cannot be imported) in each option? What data source should I create (e.g. text file, XML, Access database(MDB), Excel file (CVS)) if portability is a must? Ideally, the data importing can be initialized from a .NET 3.0/3.5 application instead of thru SSMS or command prompt.


Most of those questions revolve around exactly what you are trying to do, but for a few rules of thumb:

1.
If you are moving a preexisting database to a test environment I would use backup and restore. It tends to be one of the easier ways and more complete ways. If you are creating something new or highly modified, I would use t-sql scripts as they will give you the most direct control.

2. How you export the data depends on who you are sending it to and what they can read. As a general rule of thumb, csv files are simultaneously the smallest way to store the data and one of the most widely accepted since they can be easily read by a variety of programs including straight text editors and they can be imported into almost anything. The downside of course is that they do not include within them information like data type or relations between groupings of files that can be more thoroughly expressed in a richer format. For a richer format, you may wish to use access files or create a sql backup and send the entire backup file, depending again on your exact intent.

3. When it works, bulk insert is almost always one of the fastest and most efficient ways of importing, but it only works on certain file types formatted in certain ways and it has very little fault tolerance. Also for rarely repeated imports, there is nothing wrong with just using the SSIS or DTS wizards. I tend to use opendatasource for a lot of the automated imports in procedures because it provides more flexibility and more fault tolerance than bulk insert while remaining pure T-SQL and not requiring outside programs.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
pcwc66
pcwc66
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 174
Tim,

Thanks for prompt reply on my questions. Besides reading your article, I have been reading other articles regarding importing data to SQL Server. This article about SqlBulkCopy http://www.developer.com/net/asp/article.php/10917_3702826_1 is another good information and may applicable to my case since I'm dealing with .net application.



timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2406 Visits: 920
PCWC66, thanks for pointing out that article. It is one of the best best primers on SqlBulkCopy that I have seen. It is definitely a great technique for the right circumstances, but there are times when it is desirable to keep things T-SQL pure or nearly so.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
MudLuck
MudLuck
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 519
I'm in SQL 2008 64bit clustered this is my unhappiness

Error/Information message I receive

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Query I'm running

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\SqlServerInstances.xls;Extended Properties=Excel 8.0')...[Customers$]


Am I missing something? Have they renamed the provider to something else, or simply removed it .

End of day so tired come back with a fresh brain tomorrow in the mean time if you got something please share!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search