How to change field details?

Question

Post reply

How to change field details?

kash Yousaf

SSC Veteran

Points: 299
More actions
January 16, 2007 at 7:20 am

#175524

I have a table that has a field that contains the following details (in same format)
[Info] NumAttachments=1 [Attachments] Attachment1=65513|C:\Program Files\HEAT\HEATSelfService\attachments\Winter019997.htm

I need to run a package that will change the c:\ to h:\. How can this be done.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

What happens if there are more than one Attachment? What is the pattern?

Are you changing the c:\ to h:\ in place? DTS might not be the tool for you.

Can the string "c:\" occur multiple times in field? Is it associated with anthing that might not be changed to "h:\"?

Russel Loski, MCSE Business Intelligence, Data Platform

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 2

You don't need DTS for this if the data is already in a SQL table.

A simple answer is to execute an UPDATE statement in Query Analyzer:

UPDATE YourTable

SET YourFieldName = REPLACE(YourFieldName, 'c:\', 'h:\')

kash Yousaf SSC Veteran Points: 299 More actions · Answer 3

Reply to RLoski.

The field includes details of all attachments. I will be moving the attachments from a webserver to a shared drive (by DTS package for automation). Then I need to update the field in the database to point to the h:\ drive. I thought for automation that DTS is what to use?

luciano barretta Mr or Mrs. 500 Points: 563 More actions · Answer 4

you can realize a stored procedure that, using a cursor, analyze the field and update it as you want and then put the call to this procedure into an 'execute sql' step

Russel Loski SSCertifiable Points: 7059 More actions · Answer 5

I'm really not clear what you are trying to do, so it is very difficult to help you.

DTS can do a lot! I've seen it do things that could be done much easier with other products (VB, VBScript, Access, SQL Agent jobs, SQL Query Analyzer).

The heart of DTS, in my opinion, is transfering data to/from a database. If you have a file and want to pull it into SQL Server, DTS is your tool. Or you have data on one database server and you want to move that data to another, DTS is great.

It doesn't sound like you are moving data at all. You want to simply change the value that is currently in your database.

It looks like you needed to change the c:\ in a database field to h:\. Erik proposed an excellent solution (assuming that you need to change all c:\ to h:\) that you can run in Query Analyzer.

Russel Loski, MCSE Business Intelligence, Data Platform

Russel Loski SSCertifiable Points: 7059 More actions · Answer 6

Are you actually moving the files? How do you plan to move the files? I personally would use Access or VB to do this.

You need to read the database table row by row. You parse the field with attachment information. You then move the file from one place to another. You then update the database table with the revised location.

If this were a one time thing, I might simply use explorer to copy the files from c: to h: then I would use Query analyzer to change the pointer from c:\ to h:\.

Russel Loski, MCSE Business Intelligence, Data Platform

kash Yousaf SSC Veteran Points: 299 More actions · Answer 7

I was thinking of using a DTS package - using FTP! This is not a one off, this will need to be updating the database as new attachments come in.

When I try to update the field i get error,

Server: Msg 8116, Level 16, State 1, Line 1

Argument data type text is invalid for argument 1 of replace function.

Russel Loski SSCertifiable Points: 7059 More actions · Answer 8

UPDATE YourTable

SET YourFieldName = REPLACE(convert(varchar(8000), YourFieldName), 'c:\', 'h:\')

Russel Loski, MCSE Business Intelligence, Data Platform

Russel Loski SSCertifiable Points: 7059 More actions · Answer 9

You have several moving parts here. I don't think that I can answer this in the few moments that we have to respond to posts.

Russel Loski, MCSE Business Intelligence, Data Platform

kash Yousaf SSC Veteran Points: 299 More actions · Answer 10

Thanks I appreciate your responses to my questions. I have tested that and it does seem to work ok. I actually want to change the path of the file but not the filename,

c:\ProgramFiles\HEAT\HEATSelfService\attachments\Winter019997.htm

want it to be h:\2007\Winter019997.htm

Any thoughts?

luciano barretta Mr or Mrs. 500 Points: 563 More actions · Answer 11

may be this post is not in the right section: here is to talk about DTS and you ask for something that is better to do with other tools (query analyzer, stored procedure).

using CHARINDEX functions and a loop you can divide the name of the file from the path and build NEWPATH + FILENAME, and then you can update DB.