read/write text files using sql server? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

read/write text files using sql server?

Author

Topic

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-27 : 11:36:04

Oracle has a text_io package, by which you can read and write text files. What about SQL Server guys?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-05-27 : 12:46:21

No one is responding because all of us are busy exporting our data to text files and installing Oracle so we can read the files ....

(Use the language of your choice to write an extendend stored procedure, or better yet, store and access your data in the database management system, not the file system.)

Jay White
{0}

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-28 : 03:35:10

My language is Visual Basic 6, but how can I write an 'extendend stored procedure' in Visual Basic and use it with SQL Server?

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-28 : 04:09:56

You are right though! Oracle is the best! No comparison at all!

srinivasanr
Starting Member

15 Posts

Posted - 2004-05-28 : 05:03:50

Pat,
why do you manipulate text files from SQL Server...
I would recomment any programming language..
SQL is great..

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-28 : 05:15:22

I do use a programming language of course to do it, but I was wondering if it is possible to be done by SQL Server...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-05-28 : 07:45:43

quote:
Originally posted by PaTRiCKDRD

My language is Visual Basic 6, but how can I write an 'extendend stored procedure' in Visual Basic and use it with SQL Server?

sp_addextendedproc

Jay White
{0}

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-28 : 08:45:52

You are right though! Oracle is the best! No comparison at all!

??? Oracle is better in some instances possibly. SQL Server is a better and much more cost effective option for many companies though. With the introduction of SQL Server 2000 and the upcoming Yukon, you will continue to see MS steal a greater share of the market.

Oracle has good scalability. It's a nightmare to manage in a 24/7 environment though and any Oracle DBA will verify that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2004-05-28 : 09:46:01

Page47 could you give me a simple example with sp_addextendedproc?

and

derrickleggett what do you mean by 24/7 environment?
24 hours per day and 7 days a week? (I guess)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-28 : 10:45:10

No. 24 clusters and 7 node groups.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-05-28 : 12:04:49

I'm not going to re-type what is available already in Books Online ...

Jay White
{0}

gaskaj
Starting Member

1 Post

Posted - 2004-06-04 : 17:03:51

Forget the extended stored procedure, use a COM object from within SQL. This stored proc works, enjoy. Email me if you have any questions.

-Joe

CREATE PROCEDURE dbo.uspWriteToFile
@FilePath as VARCHAR(255),
@DataToWrite as TEXT
-- @DataToWrite as VARCHAR(8000)
AS
SET NOCOUNT ON
DECLARE @RetCode int , @FileSystem int , @FileHandle int

EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject' , @FileSystem OUTPUT
IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
RAISERROR ('could not create FileSystemObject',16,1)

EXECUTE @RetCode = sp_OAMethod @FileSystem , 'OpenTextFile' , @FileHandle OUTPUT , @FilePath, 2, 1
IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
RAISERROR ('Could not open File.',16,1)

EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Write' , NULL , @DataToWrite
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Could not write to file ',16,1)

EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Close' , NULL
IF (@@ERROR|@RetCode > 0)
RAISERROR ('Could not close file',16,1)

EXEC sp_OADestroy @FileSystem
RETURN( @FileHandle )
ErrorHandler:
EXEC sp_OADestroy @FileSystem
RAISERROR ('could not create FileSystemObject',16,1)
RETURN(-1)
GO

Magelis
Starting Member

1 Post

Posted - 2005-04-21 : 03:04:22

this is very COOL gaskaj. Since this can be done using extended stored procedures via Visual C++ or delphi, i haven't thought to use COM objects as you have meant. Can you send me any links that i can learn more on this topic?

Thanks in advice...

Btw; oracle, sybase, db2 or MS SQL....etc. All of these RDBMSes are greater than each other depending on the environment they are used. Admin question: Why should companies use oracle or sybase, if their daily transaction number is not more than 50000?
Answer: To give more pain to DBA and to love loosin $...

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2005-04-21 : 03:17:18

What about reading a file?

like this?
EXECUTE @RetCode = sp_OAMethod @FileHandle , 'Read' , NULL , @DataToRead

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-21 : 03:43:08

quote:
Originally posted by derrickleggett

No. 24 clusters and 7 node groups.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ROTFLOL

this is indeed a nightmare to manage (i'd like to know how'd they set it up)

--------------------
keeping it simple...

Beachsandintoes
Starting Member

15 Posts

Posted - 2005-05-09 : 16:43:47

quote:
Originally posted by derrickleggett

You are right though! Oracle is the best! No comparison at all!

??? Oracle is better in some instances possibly. SQL Server is a better and much more cost effective option for many companies though. With the introduction of SQL Server 2000 and the upcoming Yukon, you will continue to see MS steal a greater share of the market.

Oracle has good scalability. It's a nightmare to manage in a 24/7 environment though and any Oracle DBA will verify that.

When life gives you a lemon, fire the DBA.

Tell me, is ignorance as blissful as I hear? I'm asking you because you clearly do not know what you are talking about. Since I'm a member of the 'any Oracle DBA' that you cite, I am obligated to help educate you in something called reality.
In the company that I work for, I am a part of a small DBA team that manages over 1,000 Oracle instances and over 100 SQL Server installations in an 24/7 environment. The company enjoys very high uptime with it's Oracle Instances. If there is a problem with accessing Oracle then the issue is generally a storage or network failure. Even then, the passive node in the cluster takes over and the potentially impacted databases continue to run. As a matter of fact, the Oracle databases have been up since last year's scheduled generator and UPS upgrade.
The SQL Server databases, however, have been a series of costly disappointments. The company expected to 'save money' on SQL Server (after all, SQL Server is cheaper, right?) but, after all of the bills were paid, SQL Server cost the company more because SQL Server features pale in comparison to Oracle and DB2. (As testimony for SQL Server's ineptness, I am writing this because the maximum length of a varchar in SQL Server is 8000?)

Fortunately, Oracle has long since released a licensing model comparable to SQL Server and the company that I work for has already started saving money by converting the surviving SQL Server installations back to Oracle.

Unfortunately for other companies that will be upgrading SQL Server to Yukon, SQL Server costs are about to go up with Microsoft's new SQL Server licensing model.

By the way, I've been to a few Yukon presentations and I've shaken hands with Yukon developers in Redmond, CA.. They are all very nice people and I've always enjoyed speaking to them. However, my typical reaction to Yukon's new features has typically been 'Yeah, but Oracle and/or DB2 has been doing that for years.'

Since you believe that Yukon will gobble up market share, you should know that the real 'threat' to market share is not MS SQL Server. The real threat to market share is/ (will be) MySQL.

When life gives you a lemon, pray you have a DBA that knows what their doing.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-09 : 17:17:41

How did this year old topic get revived?

Maybe we need a separate forum for Oracle vs. SQL Server vs. MySQL flame wars.

quote:
Originally posted by Beachsandintoes
...Rant deleted...

CODO ERGO SUM

Beachsandintoes
Starting Member

15 Posts

Posted - 2005-05-09 : 19:06:47

Hi,

I apologize for the flame. Normally, I have a live and let live attitude. I know, I know - SQL Server DBAs should be allowed to breathe the same air as Oracle DBAs - even when SQL Server DBAs are filled with nothing but hot air. j/k! Really… I'm just kidding...

The frustration that I vented earlier is from watching an important project tank because of limitations in SQL Server that we do not have in DB2 and Oracle.

Currently, we are converting adhoc SQL into stored procedures in order to improve Procedure Cache usage and eliminate SQL injection threats. We can do this because there are about 150,000 DML statements that can be converted into 75 stored procedures. (FYI: we didn't originally write the code, we are simply fixing it) Our plan is to write stored procedures that will write other stored procedures. We already have stored procedures that automatically generate new stored procedures in Oracle and DB2. Unfortunately, SQL Server is the proving ground for this project because the impact of adhoc SQL is far worse in SQL Server than any of the other database platforms and we keep running into issues with SQL Server. The latest issue is that NVARCHAR only supports up to a maximum of 4000. Arg! Some of our generated stored procedures can go well beyond 4000 or even 8000 characters. (FYI: varchar2 in Oracle supports over 32,000 characters)

Ok, so our next attempt will be writing our generated stored procedures to a file on the database server. Heh heh, if we were doing this in Oracle we would do as the original poster noted, we would have used text_io and we would be home with our families.

I hope all is well. Take care and again I apologize for the flame.

- Beach

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-09 : 19:08:55

So why not use text or ntext data types in SQL Server to avoid the 4000/8000 nvarchar/varchar limitation?

Tara

Beachsandintoes
Starting Member

15 Posts

Posted - 2005-05-09 : 19:21:22

Thanks Tara,

We tried but:

1) Working with TEXT is not the same as working with nvarchar or varchar. For example, you can't simply do: set @strSQL = @strSQL + @strNewLine

2) We experimented with 'EXEC @txtSQL' and 'EXEC sp_execute @txtSQL'. Neither command was able to execute a simple as 'CREATE PROCEDURE XXXX AS ...'.

Tara since you are the almighty SQL Goddess, do you know the goddess Caffiena or Stressa? LOL - I seem to be paying homage to them every day.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-09 : 19:21:49

Instead of sending out flames, it would be a lot more productive and positive to state what you are trying to do, and ask for some help with how to do it.

You seem to have made the assumption that since you could not figure out how to do it, there is no way to get it done in SQL Server.

For item 1, use WRITETEXT to append data to the NTEXT column in a table.

For item 2, you can execute the data in the NTEXT column from a table like this:

select
	@txt_1	= substring(ntext_data,    1, 4000),
	@txt_2	= substring(ntext_data, 4001, 4000),
	@txt_3	= substring(ntext_data, 8001, 4000),
	.. and so on...
from 
	#temp_data


exec (@txt_1 + @txt_2 + @txt_3 + ...and so on...)

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources