Accessing FTP site from sql server

How to create DTD compatible XML file from result set returned from a query.

Can we access FTP site and upload this xml ?

Is it possible to do from Tsql?

I suggest that you might use CLR stored procedures to accomplish what you desire. You can write C# code to extend the functionality of SQL Server and have the client call a custom stored procedure to execute that C# code. This should enable you to populate a document in any format from data stored in the server and connect over FTP to upload the file.

Hope this helps,

John

|||

http://msdn2.microsoft.com/en-US/library/aa197263(SQL.80).aspx

Maybe this site can help you with witing the extended store procedure to accomplish what you want to do.
Please that DTD is deprecated , you may want to look into using XSD.

Accessing FTP site from sql server

How to create DTD compatible XML file from result set returned from a query.

Can we access FTP site and upload this xml ?

Is it possible to do from Tsql?

I suggest that you might use CLR stored procedures to accomplish what you desire. You can write C# code to extend the functionality of SQL Server and have the client call a custom stored procedure to execute that C# code. This should enable you to populate a document in any format from data stored in the server and connect over FTP to upload the file.

Hope this helps,

John

|||

http://msdn2.microsoft.com/en-US/library/aa197263(SQL.80).aspx

Maybe this site can help you with witing the extended store procedure to accomplish what you want to do.
Please that DTD is deprecated , you may want to look into using XSD.

Access to TEXT column

Hi,

I have a table with a TEXT column and this column contains an XML document. I'm developing a TSQL stored procedure which reads the content of this column and accesses the values in the XML elements but I have a lot of problems...

1) How can I read the text column and store the value in a variable? I tried

declare @.a varchar(2048)
set @.a = (SELECT TEXT_COLUMN FROM MY_TABLE)

but it returns

Server: Msg 279, Level 16, State 3, Line 2
The text, ntext, and image data types are invalid in this subquery or aggregate expression.

I tried also with the READTEXT function but I just can't find how to store data read in a variable...

2) which length should I use for the VARCHAR variable which will store the data? Is it possible not to specify a length with SQL Server 7 or 2000?

Thanks!

Andrea

In SQL Server 7/2000 local variables cannot have a text data type. My advice would be if you know the length of the xml data is not going to be greater than 4000 chars (aprox.), use an NVARCHAR, that is going to allow you to be more flexible with your code.

Hope this helps,

Roberto Hernández-Pou
http://community.rhpconsulting.net

|||

you can try this

declare @.a varchar(8000)

set @.a = (SELECT convert(varchar(8000),text1) FROM texttest where text1like'some%' )

select @.a

You 'll be able to use text column in procedures but it has to converted to an accepted datatype in procedures

|||Just keep in mind that a decent sized XML document can easily go past 8000 characters causing any extra data to be truncated.|||

Hi,

thanks Gopi, Roberto and Whitney! Useful answers and comments!

Andrea