FTP Task issue

The FTP Task I've created fails. It appears to be making the connection initially behind a firewall, but it suddenly switches from port 21 to port 3397. Anyone experience this issue? Since my firewall is restricting the connection to just port 21, I can't make a permanent connection.

FTP use 2 ports for it work - one for control connection (21) and one for data connection (any other, 3397 in your case, but may be other).|||When a ran a test comparison between the SSIS FTP Task and ftp command line, I only saw this difference when the SSIS FTP Task ran. Is this two-port usage specific to SSIS? I'm able to get out past the firewall using the ftp command without errors. I can't with SSIS.|||FTP use 2 ports by design (you can read RFC about it). Control connection used to transport commands and most simple responses, but file data transferred by data connection, which is open and live while particular transfer goes on.|||I ran a check again and did see windows command-line ftp sending the data transfer on port 20. Thanks.|||

Your problem is this: FTP normally uses ports 20 & 21. But, if you run in passive mode then the remote server will dynamically allocate a port number to you. So, the trick is to run in ACTIVE mode and then you will always run on ports 20 & 21.

If you are running on visual basic and I can let you have some source code to do this.

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

sql

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

FTP from a mainframe (z/os) box

Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.

Thanks!
HarryHarry,

We do not support paths that do not start with a /. Please feel free to file a bug about this.

Thanks,
Matt|||Same problem makes it impossible to pull a file off a VMS system. Ironic that in the land of the backslash, the Unix convention is mandatory ....
|||Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.
|||

Will Wilson wrote:

Well now, that's a big bummer. About all of my interfaces involve mainframe datasets.

I worked around this by installing IIS on the SQL Server 2005 and writing scripts on the VMS side to transfer the files into Windows instead. This is actually more flexible as the scripting capabilities of VMS (DCL) are far more powerful. You can then use the Foreach Loop container to iterate through the files in SQL Server 2005.

The downside is that I don't like having an FTP service running on my database server; also, this workaround requires you to have sufficient access to the mainframe to create the scripts etc. The latter is not an issue in my case as I administer both machines.

FT searching and weights

I have a couple questions about fulltext searching...
1) FULLTEXTTABLE appears to be my preference. I have 3 columns in my
catalog. I'm getting results, but I think the rankings should be different.
My lower rankings should be higher than the highest ranking results. Can I
give weight to specific columns with FULLTEXTTABLE? I don't see that option
available in BOL with FULLTEXTTABLE. If not, how could I skew the results by
saying one column is more important than another column?
2) Assume a scenario...
I index a table (10 columns, 10MB size, 100,000 rows)
Catalog is populated.
I empty and re-populate the table. Most of the data would be identical.
Can I get away with doing an incremental update? Would this be faster than a
re-population?
Or would it basically be the same thing as a full re-population?
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
Hi Shank,
#1. You can do something like the below CONTAINSTABLE (or FREETEXTTABLE as
there is no FULLTEXTTABLE ) query below using the Northwind employee table
and it's two FT-enabled columns Notes and Title with different weights:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as
B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Also, you should review SQL Server 2000 BOL title "Full-text Search
Recommendations" and the last paragraph on RANK as in order to get valid
RANK values you must have statisticlly significatntly number of rows
(10,000+) in your FT-enable table.
#2. Since you're using SQL Server 2000, I'd strongly recommend that you
consider using "Change Tracking" and "Update Index in Background" as
Incremental Populations can and do take as long to complete as Full
Populations, even with no changes. If you're emptying the table and
repopulating it, I'd recommend a Full Population over CT with UIiB and over
Incremental Pop as the Incremental will have to first delete all the entries
in the FT Catalog and then re-populate it, so a Full Population is best
given your situation.
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:#jZ5A47fEHA.2524@.TK2MSFTNGP09.phx.gbl...
> I have a couple questions about fulltext searching...
> 1) FULLTEXTTABLE appears to be my preference. I have 3 columns in my
> catalog. I'm getting results, but I think the rankings should be
different.
> My lower rankings should be higher than the highest ranking results. Can I
> give weight to specific columns with FULLTEXTTABLE? I don't see that
option
> available in BOL with FULLTEXTTABLE. If not, how could I skew the results
by
> saying one column is more important than another column?
> 2) Assume a scenario...
> I index a table (10 columns, 10MB size, 100,000 rows)
> Catalog is populated.
> I empty and re-populate the table. Most of the data would be identical.
> Can I get away with doing an incremental update? Would this be faster than
a
> re-population?
> Or would it basically be the same thing as a full re-population?
>
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.1 (Build 2600: Service Pack 1)
>

FT indexes are not populating

I've created a couple of full-text indexes using the FT wizards and/or sample
code from BOL. Everything appears to be correct and I've run a full
population on them without getting any errors. However, when I run my
queries to test the results I get no rows returned. When I look at the
properties of the FT Index, the item count states 0.
Did I miss something on setup?
Kevin Z
Are there any messages from MSSCI or MSSEach in the event log?
You also might want to review this kb article.
http://support.microsoft.com/default...b;en-us;317746
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
"kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
news:F1DD2400-556F-4E0A-998A-CF9A2C43F29F@.microsoft.com...
> I've created a couple of full-text indexes using the FT wizards and/or
sample
> code from BOL. Everything appears to be correct and I've run a full
> population on them without getting any errors. However, when I run my
> queries to test the results I get no rows returned. When I look at the
> properties of the FT Index, the item count states 0.
> Did I miss something on setup?
> --
> Kevin Z
|||Builtin\Administrator account had been removed causing the problem. KB
aritcle had a work around.
Additonal question? Will a FT Index process an entire 'text' datatype
column when building the index or is there a limit on the number of
characters it will process? On the FT index that I just created, I'm
searching the FT columnn that contains SQL scripts. The table name I'm using
in my contains clause is a pretty unique table name, but buried in a lengthy
script. When I run the query, I don't get any matches, but if I use a LIKE
clause with wildcards I can find it. Any suggestions?
Kevin Z
"Hilary Cotter" wrote:

> Are there any messages from MSSCI or MSSEach in the event log?
> You also might want to review this kb article.
> http://support.microsoft.com/default...b;en-us;317746
> --
> 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
> "kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
> news:F1DD2400-556F-4E0A-998A-CF9A2C43F29F@.microsoft.com...
> sample
>
>
|||From BOL entitled Filtering Supported File Types
Note For full-text indexing, a document must be less than 16 megabytes (MB)
in size and must not contain more than 256 kilobytes (KB) of filtered text.
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
"kzakoski" <kzakoski@.discussions.microsoft.com> wrote in message
news:1696B0D8-F132-4DAA-9099-44790BDADB65@.microsoft.com...
> Builtin\Administrator account had been removed causing the problem. KB
> aritcle had a work around.
> Additonal question? Will a FT Index process an entire 'text' datatype
> column when building the index or is there a limit on the number of
> characters it will process? On the FT index that I just created, I'm
> searching the FT columnn that contains SQL scripts. The table name I'm
using
> in my contains clause is a pretty unique table name, but buried in a
lengthy
> script. When I run the query, I don't get any matches, but if I use a
LIKE[vbcol=seagreen]
> clause with wildcards I can find it. Any suggestions?
> --
> Kevin Z
>
> "Hilary Cotter" wrote:
the[vbcol=seagreen]

Freetexttable

Okay I have finally found full text-searching and got it working with SQL2000.

Now it appears to be working correctly however I am having difficulty getting to grips with FREETEXTTABLE, and help/advice would be appreciated.

Now this is what I would like to do. One form field on a web page, passes over a search phrase which then want to use to search across all rows of my selected table and then return WEIGHTED results or RANKED results.

Now I have used CONTAINS etc and had fairly good results however assuming what I am reading via MSDN using FREETEXTTABLE will give me much better and fairly accurate results based on a whole search phrase entered by a user.

Incidentally should I use CONTAINSTABLE?

Quick Note: I am only wanting to search through one table, no more than 2000 rows.

So I have my table (lets call it Account_List) containing the following rows:

AccountID (int,Not Null) <-PrimaryKey
AccountName (char(50), Null)
AccountAddress1 (char(50), Null)
AccountAddress2 (char(50), Null)
AccountTown (char(50), Null)
AccountCounty (char(50), Null)
AccountPostcode (char(10), Null)
AccountTelephone (nvchar(50), Null)

I have a populated full-text catalog for the above table containing all of the rows.

Now what I want to do is say for example the user inputs the phrase 'Argos in Milton Keynes' I would like it to go an search each row and return weighted results based on that. In this case the key columns are AccountName and AccountTown.

Now the only example so far I have is:

USE Northwind
SELECT FT_TBL.CategoryName,
FT_TBL.Description,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
FREETEXTTABLE(Categories, Description,
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
GO

Microsoft standard example, however I don't get why I would have to do an INNER JOIN, what am I missing here? Like said before I am only using one table.

I would like a example of a simple string to search my database from the user input form.

Can anyone enlighten me :)As far as JOINing goes this is from Microsoft:

Queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates because qualifying rows returned by the functions must be explicitly joined with the rows in the original SQL Server table.

For more examples :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_1m9f.asp|||Got to the bottom of it!

The correct Query is as follows for anyone wanting to know :eek:

USE mydata
GO
SELECT *
FROM Account_List AS FT_TBL
INNER JOIN
FREETEXTTABLE(Account_List, *,
'Argos in Milton Keynes') AS KEY_TBL
ON FT_TBL.AccountID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

Oh how I like technology (when it works :mad: )

Thanks!

Fragmentation of Table Differs Between 2000 and 2005 Client on 200

I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
=============
VB .NET Developer
http://www.rocketscience.uk.com
Exactly which fragmentation figure are you referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in message
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com

Fragmentation of Table Differs Between 2000 and 2005 Client on 200

I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
--
=============
VB .NET Developer
http://www.rocketscience.uk.comExactly which fragmentation figure are you referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in mes
sage
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So whi
ch
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com

Fragmentation of Table Differs Between 2000 and 2005 Client on 200

I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
--
============= VB .NET Developer
http://www.rocketscience.uk.comExactly which fragmentation figure are you referring to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in message
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
> one should I believe?
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com