WRITETEXT from file - how? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

WRITETEXT from file - how?

Author

Topic

Pontiac_CZ
Starting Member

5 Posts

Posted - 2011-03-22 : 12:01:54

Hello,

I need to modify content of a table field. But this field is of "text" data type and I have figured out I cannot change it directly using SQL Management Studio.

So now it's SQL language's turn. I looked around and found out I would have to deal with pointers.

I created this:


DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(Data)
FROM KART_CUSTOMERS KC
WHERE KC.CUSTOMER_ID = 'T0000' AND ID = 0;

WRITETEXT KART_CUSTOMERS.Data @ptrval C:\temp\input.html;

But I am not sure about the filename part - when I run this I get error:

Msg 186, Level 15, State 1, Server HP14281105721, Line 7
Data stream missing from WRITETEXT statement.

If I put the filename into quotes, it would insert the filename itself.

Do you know how to do it?

I have read the official manual page for WRITETEXT (http://msdn.microsoft.com/en-us/library/aa260673%28v=sql.80%29.aspx) but with no help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-22 : 12:49:13

You can't use WRITETEXT with a file directly. You can do the following:

CREATE TABLE #INPUT (b TEXT NULL)
BULK INSERT #INPUT FROM 'C:\temp\input.html' WITH (fieldterminator='', rowterminator='')

UPDATE KC SET Data=b.b
FROM KART_CUSTOMERS KC CROSS JOIN #INPUT b
WHERE KC.CUSTOMER_ID = 'T0000' AND KC.ID = 0;

DROP TABLE #INPUT

Pontiac_CZ
Starting Member

5 Posts

Posted - 2011-03-24 : 06:45:30

Thank you so much,

I did a run on a test database and it went alright. I am going to deploy the update to the production db tonight.

What would be the command chain if I wanted a REVERSE direction - output the table field into the file? I tried simple SELECT with standard output redirection into a file, but the output was trimmed after about 40 lines.

Pavel

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 07:41:34

The bcp utility can export that data, you'll probably want to use the queryout option, otherwise you'll get the whole table. Books Online has the syntax and usage.

Pontiac_CZ
Starting Member

5 Posts

Posted - 2011-03-24 : 11:40:41

Thanks for the info about bcp, it is a useful utility. I studied http://msdn.microsoft.com/en-us/library/aa174646%28v=sql.80%29.aspx (I guess this is the mentioned "books online") and created following command:

bcp VWEB."SELECT Data FROM KART_CUSTOMERS KC WHERE KC.CUSTOMER_ID = 'T0000' AND KC.ID = 0" queryout C:\temp\output.txt -U user -P password -e C:\temp\errors.txt

But I get an error:

quote:

SQLState = 37000, NativeError = 156
Error = [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyw
ord 'SELECT'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prep
ared.

I cannot figure out what is wrong. I specified the database and the SELECT query, separated with a dot. Then queryout, filename and some switches.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 11:48:56

bcp "SELECT Data FROM VWEB..KART_CUSTOMERS KC WHERE KC.CUSTOMER_ID = 'T0000' AND KC.ID = 0" queryout C:\temp\output.txt -U user -P password -e C:\temp\errors.txt

Pontiac_CZ
Starting Member

5 Posts

Posted - 2011-03-31 : 04:26:06

Robvolk: I am still struggling with the BULK INSERT code.

It ran alright with my local SQL 2005. But when I run it on older SQL 2000 on the server, the result I get is this:


(0 rows affected)

and, as expected, the table doesn't get changed.

I run the code you created (your first post in this thread) using SQLCMD on my PC because SQLCMD is not present in SQL 2000 installation on the server. I guess there should be no problem in this. The SQLCMD starts, takes upload.sql as an argument and runs the SQL commands against the SQL 2000 server.

No errors, just doesn't change the target field.

Pavel

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-31 : 09:35:41

Maybe the WHERE clause is incorrect? Either that or the file didn't BULK INSERT correctly, and left the temp table empty.

For SQL 2000 you can use the osql utility, it's equivalent to sqlcmd.

Pontiac_CZ
Starting Member

5 Posts

Posted - 2011-05-19 : 05:39:55

I'd just like to end this topic and thank you so much for your help, robvolk. I finally managed to upload my file to the database.

I guess the troubles were due to the input file itself. It was in UTF-8 encoding and I found in http://msdn.microsoft.com/en-us/library/ms188365.aspx that SQL server doesn't support this encoding. I converted the file into CP1250 and improved the BULK INSERT statement so the whole script looked like this:

CREATE TABLE #INPUT (b TEXT NULL)
BULK INSERT #INPUT FROM 'G:\temp\op.htm' WITH (CODEPAGE='1250', FIELDTERMINATOR='', ROWTERMINATOR='')

UPDATE KC SET Data=b.b FROM KART_CUSTOMERS KC CROSS JOIN #INPUT b WHERE KC.Customer_ID = 'T0000'

DROP TABLE #INPUT

Then it worked. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources