Reading a text file from a stored procedure.

A rookie question - all I want to do is open a text file x.txt and read each line - no bcp or bulk insert required.
Is there a simple way to do this ?
Thanks in advance to all who reply !Not with ANSI-92 syntax. You'd have to use specific DBMS extensions for it. Pick the engine (Oracle, SQL Server, UDB, etc.) and post accordingly.|||I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply !|||I use sp_OAxxx with FileSystemObject.|||Originally posted by BrutusBuckeye
I am using SQL Server 7.0.

I know how to do this in Oracle using the DBMS functions. Are there similar functions in MSSQL ?

Thanks for your reply ! In my opinion, this is one of the fundamental design flaws in Oracle. They are attempting to make PL/SQL a programming language instead of a data[base] manipulation language.

If you stop and think about it, reading text the way that you want to do it is a client side activity. Using BCP or BULK INSERT are server side activities. There is a fundamental difference between them (which machine the code actually runs on)!

Any solution you find for MS-SQL will involve server side activity. Sybase (now Microsoft) never intended for Transact-SQL scripts to run on the client, they always assumed that those Transact-SQL scripts would run on the server. That is exactly why user interface code, file access, etc are absent from Transact-SQL... The absence is by design.

Using Microsoft Transact-SQL, you'll need to either adopt a server centric point of view, or write your client side code using the client language. There is a clear distinction between the client and server in Transact-SQL.

-PatP|||BrutusBuckeye, Pat has a very strong opinion about all this ;)

I'd still use sp_OAxxx if you insist on reading a text file one line at a time, but why bother? Use BULK INSERT and then deal with it in a recordset-based fashion!|||UNCLE !!!! :)

Thanks for all your help. I am indeed going the Bulk Insert route.

Thanks again from the school of hard knocks.. :)|||Originally posted by rdjabarov
BrutusBuckeye, Pat has a very strong opinion about all this ;) Dang! Did I let that secret out again ?!?!

-PatP

reading a text file as it is written

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@.spamcop.net. Thanks.
Greg Stigers, MCSA
remember to vote for the answers you like
NT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/de...classtopic.asp)
However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Greg Stigers, MCSA" <gregstigers+wmsn@.spamcop.net> wrote in message
news:Ot6Ibr0$EHA.608@.TK2MSFTNGP15.phx.gbl...
>A remote office developed a PC-based app that writes its data to a text
>file. We want to maintain this data at the main office, and in SQL Server,
>due to the significance of the data. A typical day's file may have a couple
>hundred records, written over an eight hour shift. Rather than changing the
>application to connect to our SQL Server, and worrying about network
>connectivity and impact on productivity, is there a reasonable means to
>have SQL Server detect when this text file is updated, and add the most
>current record(s) to our SQL Server table? Please also reply to my email
>address, gregstigers@.spamcop.net. Thanks.
> --
> Greg Stigers, MCSA
> remember to vote for the answers you like
>

reading a text file as it is written

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@.spamcop.net. Thanks.
--
Greg Stigers, MCSA
remember to vote for the answers you likeNT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/d...rclasstopic.asp)
However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Greg Stigers, MCSA" <gregstigers+wmsn@.spamcop.net> wrote in message
news:Ot6Ibr0$EHA.608@.TK2MSFTNGP15.phx.gbl...
>A remote office developed a PC-based app that writes its data to a text
>file. We want to maintain this data at the main office, and in SQL Server,
>due to the significance of the data. A typical day's file may have a couple
>hundred records, written over an eight hour shift. Rather than changing the
>application to connect to our SQL Server, and worrying about network
>connectivity and impact on productivity, is there a reasonable means to
>have SQL Server detect when this text file is updated, and add the most
>current record(s) to our SQL Server table? Please also reply to my email
>address, gregstigers@.spamcop.net. Thanks.
> --
> Greg Stigers, MCSA
> remember to vote for the answers you like
>sql

Reading .Txt file informtion by SSIS

Hi,

I want to read and get information of Database name, userid, password from a text file by Integration services 2005.

Please let me know..

Thanks

Hi Anurag,

For that you need to use Package configuration. Add the desired configuration managers (eg. an ole db connection manager). Then from the menu "SSIS" choose "Package Configrations..." and add a new XML configuration file. Choose which proporties you want to be able to configure and click finish. Now you have a XML file that you easily can change the settings from.

Regards
Simon
sql

Read\Write Files in SQL 2000

Hi, I was just wondering if it was possible/ the best way to read and write text files for example in SQL 2000?

Is it only possible to do this using extended stored procedures or do SQL commands exist to perform file IO?

Thanks very much,

Ewen

What is your purpose for this ? Do you intend to write query results to text file ? You can use BCP to do that. Or for importing a text file into a table ? use BULK INSERT

Read XML > 8k in sql TEXT column?

Hi,
I have a SQL2k database that holds XML in a TEXT column because it is
greater than 8k, I need to extract the value from a couple of fields in the
XML. How can I do this? Examples would be great; I'm new to world of XML.
BTW I'm don't have control of the database design so I can't change the
structure of the db to hold the data in a more senisble way.
Thanks,
Paul.Hello Paul,
Thank you for posting.
Regarding on the read XML data from multiple columns in SQLServer database,
are you using ADO.NET components to access the database table? Based on my
understanding, if we can make sure the content order of those columns in
the database table, we can just use ADO.NET datareader or dataadapter to
query the records out, and then combine the text in those columns together
to construct a complete text stream.
Please let me know if you have any detailed question or concerns here.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Steven,
Thanks for the reply, I'm not using ADO.NET.
All the data is in a single SQL2k Text column but it is greater than 8k in
size. What I'm looking to do get data out of a couple of elements and write
them back to two sql columns using a stored procedure.
The basic issue is because I can't delclare a variable of type text in the
procedure. How can I use the sp_xml_preparedocument and OPENXML commands in
a
procedure with a large text column with more than 8k of XML.
Thanks,
Paul.
"Steven Cheng[MSFT]" wrote:

> Hello Paul,
> Thank you for posting.
> Regarding on the read XML data from multiple columns in SQLServer database
,
> are you using ADO.NET components to access the database table? Based on my
> understanding, if we can make sure the content order of those columns in
> the database table, we can just use ADO.NET datareader or dataadapter to
> query the records out, and then combine the text in those columns together
> to construct a complete text stream.
> Please let me know if you have any detailed question or concerns here.
> Regards,
>
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Thanks for your response Paul,
So you're going to done the multiple text column string concatenate at
server-side through T-SQL. Based on my research, I'm afraid this is not
supported in SQL 2000 since the datatype are limited to varchar or ntext
which has 8000 limitation. And we can not have local variable that have
larger value return from concatenate of such columns. So we may consider
done it at upstream level(in data access component, ADO or ADO.NET).
BTW, if it is possible to upgrate to SQL 2005, there has built-in sql xml
type and CLR code supported which may help resolve such issue.
Sorry for the inconvenience this brings you.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Read Timeout

Hi Friends,

I have the text file in my server which is 21 MB size.

i want to insert that text file into temporary table and then based on the conditions will insert the original table.
But before inserting all the records in temporary table, i got the below error message but the backend process still running(inserting into temp table)

The following error was encountered:Read Timeout

The system returned:

 [No Error]

A Timeout occurred while waiting to read data from the network. The network or server may be down or congested. Please retry your request.

I am using the following code configuration section

<

sessionStatetimeout="600" />

<

httpRuntime

executionTimeout="110"

maxRequestLength="21504"

requestLengthDiskThreshold="80"

useFullyQualifiedRedirectUrl="false"

minFreeThreads="8"

minLocalRequestFreeThreads="4"

appRequestQueueLimit="5000"

enableKernelOutputCache="true"

enableVersionHeader="true"

requireRootedSaveAsPath="true"

enable="true"

shutdownTimeout="90"

delayNotificationTimeout="5"

waitChangeNotification="0"

maxWaitChangeNotification="0"

enableHeaderChecking="true"

sendCacheControlHeader="true"

apartmentThreading="false" />

How to implement the page keep active while all the records are inserted into DB?

If you are using AJAX, you could add an AJAX Timer to show the progress

Read Text file from SqlServer

Hi Frinds,

I have the text file in my server.

Is it possible to read the text file and insert into table via sql server stored procedure.

yes. You can use BCP or BULK INSERT to do the job. Refer to Books Online for more details. http://search.live.com/macros/sql_server_user_education/booksonline

Read Text File from SQL Server, Read its content, and load it in RichTextBox (Related comp

OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBox

THINGS I'VE DONE AND HAVE WORKING:
1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image)

2) I've also able to load the file using a Handler as below:

using System;
using System.Web;
using System.Data.SqlClient;

public class HandlerImage : IHttpHandler {

string connectionString;
public void ProcessRequest (HttpContext context)
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings
["NWS_ScheduleSQL2000"].ConnectionString;
int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);
SqlConnection myConnection = new SqlConnection(connectionString);

string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@.Image_Id";
SqlCommand cmd = new SqlCommand(Command, myConnection);
cmd.Parameters.Add("@.Image_Id", System.Data.SqlDbType.Int).Value = ImageID;

SqlDataReader dr;

myConnection.Open();
cmd.Prepare();
dr = cmd.ExecuteReader();
if (dr.Read())
{ //WRITE IMAGE TO THE BROWSER
context.Response.ContentType = dr["Image_Type"].ToString();
context.Response.BinaryWrite((byte[])dr["Image"]);
}
myConnection.Close();
}

public bool IsReusable {
get {
return false;
}
}

}'>'>

<ahref='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>

File

</a>
- Click on this link, I'll be able to download or view the file

WHAT I WANT TO DO, BUT HAVE PROBLEM:
- I would like to be able to read CONTENT of this file and load it in a string as below

StreamReader SR = new StreamReader()
SR = File.Open("File.txt");
String contentText = SR.Readline();
txtBox.text = contentText;

BUT THIS ONLY WORK FOR files in the server.
I would like to be able to read FILE CONTENTS from SQL Server.

PLEASE HELP. I really appreciate it.

I've figured it out, but it takes too long for my browser to load, and end up with string that include many squares like "Submitted 22155 1966 Servers".

Below is my code that goes with what is above.

Stream theFile =newMemoryStream((byte[])dr["Doc"]);

StreamReader SR =newStreamReader(theFile);

String contentText = SR.ReadToEnd();

If anybody can tell me why it's so slower to load the content of the text file on browser compare to uploading it to the database, and a way to speed it up

I'll really appreciate it.

Thanks,

read text file

hi
i need to know how can i read a text file and then insert it into the table. i dont need all the information in the text file and i just need to take some information

thanksCheck my respond here:

http://www.msdner.com/forum/thread715092.html|||hi
thanks for your response
but what i need is that i have atext file that so any rows and columns are in it but i just need some rows and columns not all of them.

thanks|||Insert text into a #temporary table and then do your query on what you need and insert it into a production table the rest will go away when procedure will end.

Good Luck.

Read Text file

Hi,

I have the text file in my server.

Is it possible to read that text file and insert into the table.

pls advice

Regards

Antony

SELECT PVListFile.F1 AS mytxt INTO MyTable FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\";User
ID="Admin";Password=;Extended Properties="text;HDR=No"')...filename#txt as PVListFile
|||

Refer to Books Online about using 'BULKINSERT', 'bcp', and/or 'OPENDATASOURCE'.

See this recent thread about using bcp.

sql

read file and exec contents

I have a text file that contains "create index" statements - thanks to those
of you here who helped me get past my hurdles with this step earlier in the
w. :)
Now I need to read the file and execute it - basically execute the contents
of the file. I'm looking for suggestions on how to do it. I thought about
using the code below to read the contents into a global var but it seems as
if it only reads so many chars into a global var. My thought was to read it
into a global var then use an execute sql task to execute the global var.
Here is what I have so far. Any suggestion on the best way to apply the
create index statements in the text file?
Thanks, Andre
Function Main()
Set fso = CreateObject("Scripting.FileSystemObject")
Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
' msgbox textStreamObject.ReadAll
DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
Set textStreamObject = Nothing
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
Sample of my TableIndexes.txt file
ALTER TABLE [MYTABLE] ADD
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
(
[RowID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GOWhy don't you just use OSQL or SQLCMD to do this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <no@.spam.com> wrote in message news:eufKUxwTGHA.1728@.TK2MSFTNGP11.phx.gbl...darkred">
>I have a text file that contains "create index" statements - thanks to thos
e of you here who helped
>me get past my hurdles with this step earlier in the
> w. :)
> Now I need to read the file and execute it - basically execute the content
s of the file. I'm
> looking for suggestions on how to do it. I thought about
> using the code below to read the contents into a global var but it seems a
s if it only reads so
> many chars into a global var. My thought was to read it
> into a global var then use an execute sql task to execute the global var.
> Here is what I have so far. Any suggestion on the best way to apply the c
reate index statements
> in the text file?
> Thanks, Andre
> Function Main()
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
> ' msgbox textStreamObject.ReadAll
> DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
> Set textStreamObject = Nothing
> Set fso = Nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> Sample of my TableIndexes.txt file
> ALTER TABLE [MYTABLE] ADD
> CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>|||Since you are reading the file from VBScript, you could simply store the
string in a local variable and execute as a statement using
ADODB.Command.Execute method call from the same script:
http://msdn.microsoft.com/library/d.../>
xecutex.asp
It seems there is no need to store the string in a DTS variable, just a
local variable. For example:
Dim strIndexes
If this task does not need to be schedules, then perhaps instead of DTS, use
Windows Scripting Host or OSQL from the command line.
"Andre" <no@.spam.com> wrote in message
news:eufKUxwTGHA.1728@.TK2MSFTNGP11.phx.gbl...
>I have a text file that contains "create index" statements - thanks to
>those of you here who helped me get past my hurdles with this step earlier
>in the
> w. :)
> Now I need to read the file and execute it - basically execute the
> contents of the file. I'm looking for suggestions on how to do it. I
> thought about
> using the code below to read the contents into a global var but it seems
> as if it only reads so many chars into a global var. My thought was to
> read it
> into a global var then use an execute sql task to execute the global var.
> Here is what I have so far. Any suggestion on the best way to apply the
> create index statements in the text file?
> Thanks, Andre
> Function Main()
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
> ' msgbox textStreamObject.ReadAll
> DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
> Set textStreamObject = Nothing
> Set fso = Nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> Sample of my TableIndexes.txt file
> ALTER TABLE [MYTABLE] ADD
> CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>|||It does need to be scheduled, and is actually part of a large DTS package.
I did change it to be in a local var rather than a global var - thanks for
that tip. It's working great now, thanks for your help.
Andre

Read a text file in Crystal Reports

I wonder if anyone could help me.

I have a text file and want to read this file using Crystal Report 10. This text file is like a database.

The question is... Can I make a connection to this file and if so how is it done. I want to be able to put certain fields from the text file on the report etc.

If this cannot be done like above, Has anyone got any ideas how I could acheive this.

Thanks in advanceA suggestion might be to set up a quick database in access and import all the data from your text file and then make a conection to it.

As far as I am aware connection to a text file as it were a database is not possible as it has no tables or fileds

Read / Write Into .doc From SQL Stored Procedure

I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?

Thank you.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

Re: How to encrypt a column in SQL Server

Hi everyone,

How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.

Thanks in advance.To make the control show asterisks, set its password attribute.

To encrypt the data, pick an encryption algorithm and implement that algorithm in your Access MDB or data project.

-PatP|||we did this one for her already

http://www.dbforums.com/t1095162.html|||Yeah, I know we've done it before... I think more than once actually.

I just figure that if we consistantly give the same answers, eventually it will help solve the problem. Maybe I should have just posted a link to one of the previous answers, but I was feeling too lazy to go find it just then. I've been kind of busy lately, and that often leads me to take the easy answer instead of doing it up properly, especially if the easy and proper ways both generate the same results!

-PatP|||but it is kind of a waste of my employers hard paid salary to have me redudently goofing off.|||I suppose that's true, but they are rarely discriminating about goofing off. I've never heard of a case where redundant goofing off was treated differently than applied goofing off was.

-PatP|||Owhay ancay ooyay encryptay anay olumnkay innay SQLAY Erversay? Unway ethodmay issay ootay oosyay uhthay amousfay "Igpay Atinlay" gorithmlay. Rytay ittay eforebay ooyay ecideday ootay ostpay uthay amesay amnday estionquay againay.

Okay?

Re: How can you encrypt a column in SQL Server?

Hi everyone,

How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.

Thanks in advance.Pick an encryption method, and handle it on the client. ROT13 is fine for most casual stuff, BlowFish is good for the really hairy stuff.

-PatP|||In access, right click on your textbox and go to properties. on the data tab, put Password in the input mask field.

this how ever is not encryption. There are third party dll available that use the RSA cypher and other methods to encrpyt data with public key encryption and you can write the encrypted data to SQL server but you can not really encrypt a column per se.|||You may want to look into SQLShield, but as it was noted before, - the encryption should be done in the front-end code, so that clear-text passwords are not travelling the network.|||sql server 2k gives you the ability to set permissions on a single column in a table if you wish.
however, you could give users access to the table through a view and not include the pwd column in the view definition.

just an alternative.