How To Read A File In A Directory Using T-Sql

Mar 3, 2008

Hi,
I need to read a XML file inside my procedure and put its contents in a variable.
How do I do it?
Is there a dbo procedure to do it?

Cheers,

View 2 Replies


ADVERTISEMENT

Read Text File From SQL Server, Read Its Content, And Load It In RichTextBox (Related Component: Context.Response.BinaryWrite(), And StreamReader)

Nov 26, 2007

OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS 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 BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "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 belowStreamReader 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.

View 1 Replies View Related

Integration Services :: File System Task - Set Source Variable And Pickup BAK File In Directory To Delete

Nov 9, 2015

I have created a File System task which is contained in a Foreach Loop Container. I have .bak files that are populating a directory from a maintenance backup plan.

There is a point where I need to delete the .bak file's after I've zipped them all up.

How do I set the SourceVariable to read through the directory and pick up just the .bak file's in the directory to delete.

View 3 Replies View Related

Read Text File From Flat File Connection Manager SSIS

May 13, 2008

Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.

Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{

//Some code to read file and write it into new file

return DTSExecResult.Success;

}


public const string Property_Task = "CustomErrorControl";

public const string Property_SourceConnection = "SourceConnection";



public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)

{

if (node.Name != Property_Task)

{

throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));

}

else

{

try

{



_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;



}

catch (Exception ex)

{

infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);

}

}

}

public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)

{

try

{

// // Create Task Element

XmlElement taskElement = doc.CreateElement("", Property_Task, "");

doc.AppendChild(taskElement);

// // Save source FileConnection

XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);

sourcefileAttribute.Value = _sourceConnectionId;

taskElement.Attributes.Append(sourcefileAttribute);

}

catch (Exception ex)

{

infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);

}

}

In UI Class there is OK Click event.

private void btnOK_Click(object sender, EventArgs e)

{

try

{



_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);

btnOK.DialogResult = DialogResult.OK;

}

catch (Exception ex)

{

Console.WriteLine(ex);

}

#endregion

}

View 10 Replies View Related

SQL 2012 :: How To Do Selective Read Of File Stored In File Table

Jul 2, 2015

I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.

Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.

I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.

View 3 Replies View Related

Splitting Directory And File Name

Feb 19, 2006

First of all apologies for posting this again but I posted it in thewrong place the first time.Can anybody help me with the post below?http://groups.google.com/group/SQL-...5ec9f04d978d0deThanks,Paul

View 1 Replies View Related

What Directory Would Be Best To Store The .SDF File In?

Jan 13, 2006

I am creating some applications for Windows Mobile 5.0 devices to be used internally at our company. Each device will run several home grown applications that will all share the same common SQL Mobile database.

What I am wondering is whether or not there is a standard path where such a database is typically stored in Windows Mobile 5.0. I see a folder called Application Data. Is that what it is for, or is that one for something else? I'm not real familiar with the layout of the Pocket PC in general. Could someone tell me where vendors normally put their database file(s)?

Thanks,

Brad

View 1 Replies View Related

Where Do I 'Set Directory' For File DSN On Vista?

Jul 19, 2007

I did something stupid. I was messing around with a file DSN and deleted it. I can't seem to recreate it either. I "set the directory" in the ODBC dialogue to a location I liked better and moved the file DSN there. It didn't work when I tried to use the applications on my system later on (huh, shooting self in foot...). (using vista ultimate 64-bit). One of the databases is SQL 2005 Express, and the other is an Access database.

Maybe I lost all my ODBC connections? Neither databases work now... When I am in the File DSN tab and click "Add" I have only two drivers showing up (SQL Native Client and SQL Server) in the Create New Data Source window. Do I need to add an ODBC driver? Any better documentation on File DSNs? (or using the ODBC Administrator)... the help documentation is REALLY lacking!!!

View 1 Replies View Related

Getting Directory Name And File Name From Path

Dec 12, 2007

I have a sql compact table that stores a series of directory paths like so:
C:F1BackupadministratorCDatakit.zip__--1-07272007124351.F1O
C:F1BackupadministratorCDataDeploySMD_FSWsetup.exe__--1-11292007122443.F1O
C:F1BackupadministratorCDataDeploySMD_FSWSMD_FSW.application__--1-11292007122443.F1O

Is there a way in sql that I can get just the directory name so the results would look like this:
C:F1BackupadministratorCData
C:F1BackupadministratorCDataDeploy
C:F1BackupadministratorCDataDeploy


Also, is there a way to just get the file names so the results would look like this:
kit.zip__--1-07272007124351.F1O
setup.exe__--1-11292007122443.F1O
SMD_FSW.application__--1-11292007122443.F1O

I've seen some examples with Sql server but they use the reverse function which is not available in sql compact.

Any ideas would be greatly appreciated.

View 1 Replies View Related

How Read File CSV File In Remote Server Using Bulk

Mar 24, 2008

Hi All,

I need to read a csv file, which is in remote server using SQl Bulk Insert Command.

Can I read a file Which is in remote server using BULK INSERT.

Thank you.......

View 1 Replies View Related

Importing All The Text File In A Directory Using DTS

Sep 28, 2005

Hi everyone

Thanks for reading and helping me out of this problem.
I have a directory where I have text file with date as a file name, thus all the files have different file names.

But I want to do is import all the text files into sql server 2000 ,right now i have a DTS package which i have up upload all the text files manually , I do not know how to loap it so that it DTS funcation check all the files in a directory and import it into database.

I will appreciate if anyone can help me out of this .

View 1 Replies View Related

Sql2005 Export File To Directory

Jul 23, 2007

Hi all,
I have scheduled a package to export data from the table to the execl file in the local directory, it work fine. However, when the next time you execute the package it give error on 'file already exists'. Prephap this is due to previously already created a file in the directory.

I try to use drop option in the ssis during creating the package for export, but the result still the same. I do not know the drop option is working, can someone tell me is that working fine? if not what is the solution or possible cause.

Thanks for your help.:)

View 1 Replies View Related

SQL 2012 :: How To Get Name And Timestamp Of File From A Directory

Mar 18, 2015

I want to write a stored procedure or a simple T-SQL snippet that takes a parameter "Directory Path" and gives me the name and datetimestamp associated with each of the files within that directory. I can not use xp_cmdshell since it is not allowed. CLR integration is not enabled on the server either.

Is there any other way at all to achieve this directly from SQL Server using T-SQL?

View 3 Replies View Related

Check Directory To Make Jpg File Exist

Mar 6, 2012

We have a database called Itemphotos. In this table is a field called 'PID' picture ID. We have a photos directory with files like 2181.jpg, 2182.jpg, 2184.jpg. The number is the ID number for the picture of the part. If a record exist in the database based on the PID "Primary Key" , check the directory to make sure the jpg file exist. Here is the code I have so far.

use [ItemPhotos]
select pid from dbo.itemdata
declare @file_path nvarchar(500)
declare @file_exists
int set @file_path = 'av-sql2c$inetpubphotosmacolaphotos' + [itemdata].[pid]

[code]....

View 7 Replies View Related

Created BAT File Just To Make A Directory Not Getting Recognized

Aug 13, 2015

i am trying to run an exe using t-sql command and even through job, but it is giving me issues. But if i double click on the exe it runs fine.

For example i created a bat file just to make a directory - mkdir d:example
double clicking it, a folder is created BUt through
EXEC xp_cmdshell "D:ew.bat"
it gives me error -
'D:ew.bat' is not recognized as an internal or external command,

and when i do with the job with command - @command=N'idhimahajanD$ew.bat',
it gives an error on execution - Executed as user: NT ServiceSQLAgent$PAXAS. The process could not be created for step 1 of job 0x2FCDDE36F160CD4688D2ACE1D748A99E (reason: Access is denied). The step failed.

View 3 Replies View Related

How To Retrieve Most Recent File From Local Directory?

May 30, 2007

Hi,



Here's my situation. Every day I will be downloading extracts to a folder. The extracts are named:



20070529.Extract1.csv

20070528.Extract1.csv

20070527.Extract1.csv

20070529.Extract2.csv

20070528.Extract2.csv

20070527.Extract2.csv



So, on any given day, I will want to find the most recent versions of Extract1 and Extract2, for example:

20070529.Extract1.csv & 20070529.Extract2.csv



How would I go about doing this?



Thanks much

View 12 Replies View Related

Create Directory With A File System Task

Sep 13, 2006

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:myprog20060913 and move my processed file there.

Can anyone help me? Please.

View 7 Replies View Related

HOW TO FIND BACKUP FILE IN DEFAULT DIRECTORY

Dec 17, 2007



I am getting a backup file in default directory by using this script







Code Block
BACKUP DATABASE CPHONE
TO DISK = 'CPHONE.Bak' -- send to default pathaway
WITH FORMAT,
NAME = 'Full Backup of CPHONE'






Is there any way to find where is the default path of the SQL server, in order to show to the user from a VB program in which pathway the database back up is stored




View 4 Replies View Related

How Do I List Contents Of File Directory In The SQL Query Analyzer

Apr 13, 2006

how do I list contents of file directory in the SQL Query Analyzer

View 1 Replies View Related

Stored Procedure - Create Trigger File In Particular Directory

Oct 26, 2012

I created stored procedure to create trigger file in a particular directory using xp_cmdshell.

I am calling the procedure from windows batch script as follows

@@set osq200=osql /a 4096 /b /E /e /d %dbn% /m-1 /r 0 /S %dbs% /Q "exec SP_Create_TriggerFile %2,%1 "
@%osq200% >>%3rec.txt 2>%3rec_err.txt
@set dberr=%errorlevel%
@if %dberr% GTR 0 goto createDATriggerFileErr >>%3rec.txt

If the directory doesn't exist, its throwing error "The system cannot find the path specified" , but the %errorlevel% still showing as 0..

View 1 Replies View Related

SQL 2012 :: Converting Directory File Path Into HierarchyID

Jul 22, 2014

I am trying to create hierarchyID for a directory file path.

View 1 Replies View Related

SQL 2012 :: Query To Get File Name And Date Modified From A Directory?

Sep 3, 2014

basically what we need is a query that will allow me to provide a directory and a variable for number of days for instance 1 day old and I want to be able to able to delete all files older than that date and of course I want to be able exclude files of a particular type where I would give it a wild card statement for example say i wanted to save all csv files i would have the wild card say <> '%.csv'

View 3 Replies View Related

File System Task - How To Move Directory Contents

Jul 13, 2006



Hi,

I want to move all files of one directory/folder into another usng SSIS, like in DOS Prompt we use the command,

Move d:ftpSource*.* d:ETLSource

I have tried by creating a file system task, but there is no option for Move Directory Contents. Move File is not accepting wild cards.

Regards,

Imran.





View 3 Replies View Related

File System Task -Delete Directory Content

Jul 25, 2007

This delete's the directory content as well as sub directory content! Is their a way i can just set it to delete the files in the said directory and not sub directories?

View 11 Replies View Related

Directory Structure Not Known Then How To Provide File Path For A FILENAME Param

Feb 19, 2007

Hi,

I am adding a file to a filegroup on a remote system, and i don't know the direcotry structure of that machine, how can i provide the path for FILENAME param. :eek:

ALTER DATABASE TESTDB ADD FILE (NAME = N'TESTFILE', FILENAME = N'physicalfilepath.ndf') TO FILEGROUP TESTFILEGROUP

Warm Regards,
Mushq

View 1 Replies View Related

Integration Services :: Error / Directory Is Not Specified In File Connection Manager

Jul 18, 2012

I have one SSIS package that has FTP task which takes files from FTP and save on my local drive but i'm getting above error.

[FTP Task] Error: Directory is not specified in the file connection manager "User::ProcessingFileLocation".

In FTP Editor setting :

User::ProcessingFileLocation used SSIS variable and values sets to this is MYSERVERRaw Data

why getting this error?

when i mention direct path it is working fine BUT why not for variable.I'm using same path for other packages too they all are working fine.

View 11 Replies View Related

Using Provider For Microsoft Directory Services For Querying Active Directory

Apr 6, 2007

Has anyone used this successfully from an OLEDB source component, or even from the Execute SQL Task? I've seen some examples of using a script component, but nothing that uses it through a connection manager.

View 6 Replies View Related

SQL Server 2012 :: Bulk Insert Using UNC Path Of File Table Directory

Jul 22, 2013

Overall goal: Write a Bulk Insert statement using the UNC path of a filetable directory.

Issue: When using the UNC path of the filetable directory in a Bulk Insert Statement, receiving "Operating system error code 50(The request is not supported.)" Looking for confirmation as to whether this is truly not supported.

Environment: SQL Server 2012 Standard. Windows Server 2008 R2 Standard

View 9 Replies View Related

SQL 2012 :: Delete Particular Trace File And At Same Time Keeping Directory Dynamic

Mar 13, 2014

I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.

I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".

del $(ESCAPE_SQUOTE(SQLDIR)) + "LogTestTrace.trc"

View 4 Replies View Related

Receive Files From Remote Site Only If File Does Not Exist In Local Directory

Jul 13, 2007

I need to only receive files via ftp when the file does not exist on my local machine. FIles are being added to the remote location on a weekly basis and they are being downloaded locally. I do not want to download all the files each time. Instead, just download what was not already downloaded. Is there a way to do this? I want to do this using SSIS / ftp task.



Thanks in advance.

View 4 Replies View Related

Read XML File From File System?

Jan 8, 2002

Anyone reading XML disk-files into SQL Server?

I have a process that I may want to do this with.

It would be a stored procedure that would read the XML attributes into 2 tables, the number of attributes could be 1-N, so I thought XML would be a good choice. Also, one of the attributes could be up to 4000 characters. I think this may limit our options, can 100-150 4000 character strings be passed in a standard call to a query/proc in SQL?

Currently the client application makes round-trip network calls to save upwards of 100 pairs of data. 1 header row, and many detail rows. All within a transaction.

I think If we move an XML file to the SQL box, then do all the import/save work on the "Server" side it would be much better. Cutting the transaction time down a lot by not doing so many round-trips at network speed...

Thoughts?

View 1 Replies View Related

.mdf File In Virtual Directory And Physical Folder, Which One Should I Attach To Sql Server Management Studio Express

Jun 26, 2007

I have a an asp.net application running under IIS. this application is using two folders, the virtual directory under C:Inetpubwwwroot and the application phisical folder at C:.
I want to manage this applications .mdf database using microsoft sql server management studio express(iam running sql express with advanced services) which means i have to attach the database to Ms sql server managemet studio express tool.
What i dont know is if i should attach the .mdf file in the physical folder's App_Data folder at C:myapplicationFolderApp_data or the .mdf file in the virtual directory under C:InetpubwwwrootMyapplicationFolderApp_Data
I dont know which of the two i should attach. Where exactly is the running database, the .mdf file in the physical folder or the .mdf file in the virtual directory under wwwroot.

View 3 Replies View Related

SQL Server 2012 :: View To Concatenate File / Path Names Back To Root Directory?

Sep 25, 2014

We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root

Below is a sample:

Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4

iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.

I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.

Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)

The end result of my view on the above data should return:

Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)

View 5 Replies View Related







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