Ftp

How can I do FTP(File transfer protocol) tasks in between 2-3 remote servers?I want to know in details.I have to do by writing SQL SERVER 2000 programming code.Originally posted by subhasishray
How can I do FTP(File transfer protocol) tasks in between 2-3 remote servers?I want to know in details.I have to do by writing SQL SERVER 2000 programming code.

This site shows a simple task. Get some direction from here:

http://www.nigelrivett.net/s_ftp_PutFile.html

thanks to nigelrivett|||Yeah,

Nigel's stuff is great!

Or you could just execute a bat file

master..xp_cmdshell 'D:\Somelocation\FTP_EVPDNS.bat'

Where that contains something like:

c:\winnt\system32\ftp -s:E:\Projects\GOCall\FTP\INST.ftp > INST_ftp.log
c:\winnt\system32\ftp -s:E:\Projects\GOCall\FTP\RETA.ftp > RETA_ftp.log

And the script file contains (
this is going to the mainframe)

open servername
<login>
<Password>
delete 'AD2AIC.EVPDNS.RETAIL.FEED.W0340'
quote site cyl pri=5 sec=2 lrecl=677 blksize=6770 recfm=fb retpd=60
put v:\data\Reta.txt 'AD2AIC.EVPDNS.RETAIL.FEED.W0340'
quit

There will be a log created in the location of the bat file which will show you the results...

But if it's SQL Server to SQL Server, why not create a linked server?

From DataSet To SqlDataReader in a CLR Stored Procedure

Hi all,
I'm writing a CLR stored procedure that just execute a query using 2 parameters.

SqlContext.Pipe.Send can send a SqlDataReader, but if I've got a DataSet?
How can I obtain a SqlDataReader from a DataSet?

Dim command As New SqlCommand(.......)
.....
Dim ds As New DataSet()
Dim adapter As New SqlDataAdapter(command)

adapter.Fill(ds, "MyTable")

... 'manipulating the ds.Tables("MyTable")

At this moment I have to send the table...but
ds.Tables("MyTable").CreateDataReader()
just give me a DataTableReader, and i can't send it with SqlContext.Pipe.Send(...

Help me please!
The DataSet.CreateDataReader method can be used to generate a data reader that reads data from a dataset.|||DataSet.CreateDataReader() is the same of dataSet.Table(x).CreateDataReader()...it returns a DataTableReader!
There is also an overload for dataSet.CreateDataReader that get an array of dataTable as parameter!|||Sorry about that. For some reason, I was quite convinced that SqlPipe.Send accepted an IDataReader rather than a SqlDataReader. Looks like you'll need to use SqlPipe.SendResultsRow. See http://msdn2.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx for details and sample code.|||Thank you, but i don't think that can be the "best practice", it's not in .net style!!!
I'll search better!|||? It used to accept IDataReader during the betas, all the way up to one of the final CTPs -- then that functionality was removed for some reason :( -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Nicole Calinoiu@.discussions.microsoft.com> wrote in message news:7725b945-d2c1-44d0-a698-fa9a344a48d4@.discussions.microsoft.com...Sorry about that. For some reason, I was quite convinced that SqlPipe.Send accepted an IDataReader rather than a SqlDataReader. Looks like you'll need to use SqlPipe.SendResultsRow. See http://msdn2.microsoft.com/en-US/library/microsoft.sqlserver.server.sqlpipe.sendresultsrow(VS.80).aspx for details and sample code.|||

Well, at least I wasn't imagining things. ;)

Based on the current implementation, I'd have to guess that the change may have been made for 2 main reasons: improved performance and enhanced metadata extraction. However, I can't see any reason why an overload that accepts IDataReader couldn't have been left in since the "improved" SqlDataReader implementation would still be used where possible. A wee bit odd...

FreeTextTable Search limitations

Hi,
I'm currently writing a search system in ASP with Javascript connected in to
SQL to search a product database. I'm using the FreeTextTable command to
search a number of fields and bring back results in rank order which is
working fine. However when the user submits a word in the noise list or
leaves the search blank and submits SQL returns a nice friendly ODBC
'80040e14' error.
I've read in FAQs and other posts etc to clear the noise filter and just
leave a space but if the user enters a space then hits search we have the
same issue. I'm not happy with that being the resolution and client side
code to correct this would have to cover the blank, space or multiple space
querystring. Instead of this is there not a way in the stored procedure to
throw something else back before returning what ever it does to bring up the
ODBC error? I'm new to stored procedures so not sure if there would be a way
to do this breaking out from a statement if it is yeilding a nasty ODBC
error. If anyone else has any suggestions or examples as to how they use the
FreeTextTable predicate to implement a search system through an ODBC driver
please let me know.
Regards
Nick Scott
MCSE 2003
Do a replace, replacing these illegal characters. Then check to see if the
resulting string is =0 characters, if so exit immediately with a return code
that will be interpreted by the calling application as an invalid search
string.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick Scott" <scottpin@.xXxhotmailxXx.com> wrote in message
news:uG5pVO1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm currently writing a search system in ASP with Javascript connected in
to
> SQL to search a product database. I'm using the FreeTextTable command to
> search a number of fields and bring back results in rank order which is
> working fine. However when the user submits a word in the noise list or
> leaves the search blank and submits SQL returns a nice friendly ODBC
> '80040e14' error.
> I've read in FAQs and other posts etc to clear the noise filter and just
> leave a space but if the user enters a space then hits search we have the
> same issue. I'm not happy with that being the resolution and client side
> code to correct this would have to cover the blank, space or multiple
space
> querystring. Instead of this is there not a way in the stored procedure to
> throw something else back before returning what ever it does to bring up
the
> ODBC error? I'm new to stored procedures so not sure if there would be a
way
> to do this breaking out from a statement if it is yeilding a nasty ODBC
> error. If anyone else has any suggestions or examples as to how they use
the
> FreeTextTable predicate to implement a search system through an ODBC
driver
> please let me know.
> Regards
> Nick Scott
> MCSE 2003
>
|||set a filter to stop certain charactors such as space.
ok?
"Hilary Cotter" <hilary.cotter@.gmail.com> д?
news:%233EnWf1oFHA.3120@.TK2MSFTNGP09.phx.gbl...
> Do a replace, replacing these illegal characters. Then check to see if the
> resulting string is =0 characters, if so exit immediately with a return
> code
> that will be interpreted by the calling application as an invalid search
> string.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick Scott" <scottpin@.xXxhotmailxXx.com> wrote in message
> news:uG5pVO1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> to
> space
> the
> way
> the
> driver
>
|||This would work, as long as the filter would permit these characters when
there was something in addition to them. So the phrase "this is a test"
would pass even though it contains the space characters. Whereas " " would
be filtered out as it only contains the space character.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"zgw" <gwdotnet@.hotmail.com> wrote in message
news:u2KqA8MpFHA.420@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> set a filter to stop certain charactors such as space.
> ok?
> "Hilary Cotter" <hilary.cotter@.gmail.com> д?
> news:%233EnWf1oFHA.3120@.TK2MSFTNGP09.phx.gbl...
the[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
just[vbcol=seagreen]
the[vbcol=seagreen]
side[vbcol=seagreen]
up[vbcol=seagreen]
a[vbcol=seagreen]
use
>

FREETEXT vs FREETEXTTABLE

1) The following query works fine and gives me the results I want. Is there
a better more efficient way of writing this?
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = ' "midler" '
SELECT Stock.OrderNo, Stock.Description, Stock.Category,
Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
Titles.Artist, Hardware.m_Specs, Stock.ManCode
FROM Stock LEFT OUTER JOIN
Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
Hardware ON Stock.OrderNo = Hardware.OrderNo
WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
FREETEXT(Stock.Description,@.SearchCriteria) OR
FREETEXT(Stock.Category,@.SearchCriteria) OR
FREETEXT(Stock.s_Type,@.SearchCriteria) OR
FREETEXT(Stock.Manuf,@.SearchCriteria) OR
FREETEXT(Stock.Label,@.SearchCriteria) OR
FREETEXT(Stock.ManCode,@.SearchCriteria) OR
FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
FREETEXT(Titles.Title,@.SearchCriteria) OR
FREETEXT(Titles.Artist,@.SearchCriteria)
2) As far as I can tell, RANK is not available with FREETEXT, but is
available with FREETEXTTABLE. How can I convert the above to make use of
FREETEXTTABLE?
thanks!
This should do it: NOTE: This assumes you want to search ALL fields that
are set up in the FULL-TEXT Search for that table.
DECLARE @.SearchCriteria varchar(100)
SET @.SearchCriteria = 'midler'
SELECT
Stock.OrderNo,
Stock.Description,
Stock.Category,
Stock.s_Type,
Stock.Manuf,
Stock.Label,
Titles.Title,
Titles.Artist,
Hardware.m_Specs,
Stock.ManCode
FROM
Stock
LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
ORDER BY
FS_TABLE.Rank DESC
"shank" <shank@.tampabay.rr.com> wrote in message
news:esmKmpsXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> 1) The following query works fine and gives me the results I want. Is
there
> a better more efficient way of writing this?
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = ' "midler" '
> SELECT Stock.OrderNo, Stock.Description, Stock.Category,
> Stock.s_Type, Stock.Manuf, Stock.Label, Titles.Title,
> Titles.Artist, Hardware.m_Specs, Stock.ManCode
> FROM Stock LEFT OUTER JOIN
> Titles ON Stock.OrderNo = Titles.OrderNo LEFT OUTER JOIN
> Hardware ON Stock.OrderNo = Hardware.OrderNo
> WHERE FREETEXT(Stock.OrderNo,@.SearchCriteria) OR
> FREETEXT(Stock.Description,@.SearchCriteria) OR
> FREETEXT(Stock.Category,@.SearchCriteria) OR
> FREETEXT(Stock.s_Type,@.SearchCriteria) OR
> FREETEXT(Stock.Manuf,@.SearchCriteria) OR
> FREETEXT(Stock.Label,@.SearchCriteria) OR
> FREETEXT(Stock.ManCode,@.SearchCriteria) OR
> FREETEXT(Hardware.m_Specs,@.SearchCriteria) OR
> FREETEXT(Titles.Title,@.SearchCriteria) OR
> FREETEXT(Titles.Artist,@.SearchCriteria)
> 2) As far as I can tell, RANK is not available with FREETEXT, but is
> available with FREETEXTTABLE. How can I convert the above to make use of
> FREETEXTTABLE?
> thanks!
>
|||The query works, but I'm getting all rows returned. The highest ranked are
at the top like expected, but it's also returning all rows in the Stock
table that have no match whatsoever. In my FREETEXT query, all the results
had a match. I don't get the concept of the FREETEXTTABLE. In my mind, I'm
expecting a temp table to be created with the results. However, the code
below is actually joining the created table. I don't get it.
How do I get only matching results in the FREETEXTTABLE?
thanks!
"news.microsoft.com" <spammehere@.arcaderestoration.com> wrote in message
news:e4dx4uuXEHA.3420@.TK2MSFTNGP12.phx.gbl...
> This should do it: NOTE: This assumes you want to search ALL fields that
> are set up in the FULL-TEXT Search for that table.
> DECLARE @.SearchCriteria varchar(100)
> SET @.SearchCriteria = 'midler'
> SELECT
> Stock.OrderNo,
> Stock.Description,
> Stock.Category,
> Stock.s_Type,
> Stock.Manuf,
> Stock.Label,
> Titles.Title,
> Titles.Artist,
> Hardware.m_Specs,
> Stock.ManCode
> FROM
> Stock
> LEFT OUTER JOIN Titles ON Stock.OrderNo = Titles.OrderNo
> LEFT OUTER JOIN Hardware ON Stock.OrderNo = Hardware.OrderNo
> LEFT OUTER JOIN FREETEXTTABLE(Stock, *, @.SearchCriteria) AS
> FS_TABLE ON FS_TABLE.[KEY] = Stock.OrderNo
> ORDER BY
> FS_TABLE.Rank DESC
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:esmKmpsXEHA.3676@.TK2MSFTNGP09.phx.gbl...
> there
>