ftp task

Is is possible by ftp task to unzip the file and take the respective text file and place it in the local system.

sureshv wrote:

Is is possible by ftp task to unzip the file and take the respective text file and place it in the local system.

No. Unzipping a file has nothing to do with FTP and never will be included in such a task.

Use an Execute Process Task after the FTP task to unzip it.

Use a File System Task after the Execute Process task to move the resulting file, if needed. You usually can use command line arguments to the unzip process to direct the ZIP utility where to place the unzipped file.|||

what i am trying to do is i get a zip file (which has a text file) .

This zipfile is located on a different server.I want to transfer text file located with in the zip to a different server and then i run my package.

i want to setup my packages in such a way that they run automatically.

so in this case i guess i need to use ftp task to get the zip file to local server and then unzip it as u said..

|||

sureshv wrote:

so in this case i guess i need to use ftp task to get the zip file to local server and then unzip it as u said..

Correct. I've spelled out the steps you need.

FTP & File System Task

I'm just learning 2005, so apologies in advance for the newb questions.

I am facing the following situation: Each day I have to upload an assortment of .csv files with variable names (eg. FileOneYYYYMMDD.csv, FileTwoYYYYMMDD.csv, etc.) to an FTP site, from the following directory structure:

Directory1

SubDirectoryA

SubDirectoryB

SubDirectoryC

I have accomplished this by setting up a package with three sequence containers (one for each subdirectory), each of which holds a Foreach loop (with a file enumerator configured as *.csv), each of which holds an FTP Task. This may not be the best way to do it, but it works. But if there's a better way I'd like to know!

Anyway, the wall I've run into is trying to move the .csv files to an archive directory after they've been uploaded. The wildcard variable doesn't seem to work with the File System Task, so I'm having a hard time figuring out how to move a bunch of variably named .csv files at different depths of a directory structure to an archive directory.

I've searched but can't seem to find a solution.

Why not add a file system task after the FTP task (hook them up together with a precedence constraint) to move the file after the FTP task completes?|||

Nah, that sounds too easy.

On a more serious note, it seems I had to also set DelayValidation = True on the File System Task.

|||

I have a similar process.

I'm grabbing csv's that contain a date in the name from an ftp site and dropping them locally in the "Processing" folder. Once the file processes completely I move the file to the "Processed" folder. If it errors then I move it to the "Error" folder and have appended to the name of the file the date & time it errored.

In addition, before I kick off this whole process I use a script component to check if a file exists either in the "Processed" or "Processing" folder before bothering to ftp it.

As far as the errored files go, since I make the name unique with the timestamp i can attempt the uploads infinite number of times.

Also, I'm using a For Look container with Package level variables so I can access the variables at any time throughout the process.

Frustrating: Can't get to Provider Options in SQL 2005 Express?

What are you trying to achieve?
Look at the sp_addlinkedserver system procedure.
"Thou shall lear to type. Clicking is the work of the Devil."
-- undisclosed apostle
;)
ML
http://milambda.blogspot.com/Thanks, but I have already used sp_addlinkedserver.
I am trying to set the "allow inprocess" provider options... according to
research in the NG's and BOL (see "Linked Server Properties (Provider
Options Page)"), this is set from a dialog that I am unable to access. I
can't see it when adding a new linked server, editing an existing one, or
right-clicking on the provider in Management Studio.
Any ideas?
Thanks,
Mike
"ML" <ML@.discussions.microsoft.com> wrote in message
news:99625079-C5D6-430C-89AB-8AD8421A2731@.microsoft.com...
> What are you trying to achieve?
> Look at the sp_addlinkedserver system procedure.
> "Thou shall lear to type. Clicking is the work of the Devil."
> -- undisclosed apostle
> ;)
>
> ML
> --
> http://milambda.blogspot.com/|||Hi,
I'm trying to get to the provider options screen in SQL2K5 Management
Studio, however I am unable to find it anywhere. This is really
frustrating... Here is what I am trying to do:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on a provider and select Properties.
However, all I can see when I right-click is "Refresh" ... How can I access
the provider options? Is this a limitation of the Express database? Is there
a stored proc I can use?
Thanks,
Michael|||What are you trying to achieve?
Look at the sp_addlinkedserver system procedure.
"Thou shall lear to type. Clicking is the work of the Devil."
-- undisclosed apostle
;)
ML
http://milambda.blogspot.com/|||Thanks, but I have already used sp_addlinkedserver.
I am trying to set the "allow inprocess" provider options... according to
research in the NG's and BOL (see "Linked Server Properties (Provider
Options Page)"), this is set from a dialog that I am unable to access. I
can't see it when adding a new linked server, editing an existing one, or
right-clicking on the provider in Management Studio.
Any ideas?
Thanks,
Mike
"ML" <ML@.discussions.microsoft.com> wrote in message
news:99625079-C5D6-430C-89AB-8AD8421A2731@.microsoft.com...
> What are you trying to achieve?
> Look at the sp_addlinkedserver system procedure.
> "Thou shall lear to type. Clicking is the work of the Devil."
> -- undisclosed apostle
> ;)
>
> ML
> --
> http://milambda.blogspot.com/

Frequent SqlException

system.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)
can you help with the above errorHi
You may want to look at tracing the data access at:
http://msdn2.microsoft.com/en-us/library/ms971550.aspx
Are you using connection pooling? It may be that you are trying to re-use a
connection that has been closed. You could try changing the keepalive
interval (in Configuration Manager) so it is less likely to be closed.
John
"Windows 2003 user" wrote:

> system.Data.SqlClient.SqlException: A transport-level error has occurred
> when sending the request to the server. (provider: TCP Provider, error: 0
-
> An existing connection was forcibly closed by the remote host.)
> can you help with the above error

Frequent SqlException

system.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)
can you help with the above errorHi
You may want to look at tracing the data access at:
http://msdn2.microsoft.com/en-us/library/ms971550.aspx
Are you using connection pooling? It may be that you are trying to re-use a
connection that has been closed. You could try changing the keepalive
interval (in Configuration Manager) so it is less likely to be closed.
John
"Windows 2003 user" wrote:
> system.Data.SqlClient.SqlException: A transport-level error has occurred
> when sending the request to the server. (provider: TCP Provider, error: 0 -
> An existing connection was forcibly closed by the remote host.)
> can you help with the above error

Frequent SqlException

system.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)
can you help with the above error
Hi
You may want to look at tracing the data access at:
http://msdn2.microsoft.com/en-us/library/ms971550.aspx
Are you using connection pooling? It may be that you are trying to re-use a
connection that has been closed. You could try changing the keepalive
interval (in Configuration Manager) so it is less likely to be closed.
John
"Windows 2003 user" wrote:

> system.Data.SqlClient.SqlException: A transport-level error has occurred
> when sending the request to the server. (provider: TCP Provider, error: 0 -
> An existing connection was forcibly closed by the remote host.)
> can you help with the above error

Frequent generated log files takes up the whole system drive (C:\)

This happend twice on two different servers. A 33MB reporting service log was
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
......
Thanks,
MichaelCheck out this hotfix:
http://support.microsoft.com/kb/885286/
This problem should be fixed in SP2.
--
Adrian M.
MCP
"Michael" <missisummer@.community.nospam> wrote in message
news:C1FE7688-A420-49E1-A17C-0FC736DECC7D@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
> was
> generated every 3 minutes until it took up the entire C:\ drive. The log
> file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
> C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael|||Hi Michael,
Yes, as Adrian M said this is a known issue for us now. A supported hotfix
is now available from Microsoft, but it is only intended to correct the
problem that is described in this article. Only apply it to systems that
are experiencing this specific problem. This hotfix may receive additional
testing. Therefore, if you are not severely affected by this problem, we
recommend that you wait for the next Microsoft SQL Server 2000 Reporting
Services service pack that contains this hotfix.
To resolve this problem immediately, contact Microsoft Product Support
Services to obtain the hotfix. For a complete list of Microsoft Product
Support Services phone numbers and information about support costs, visit
the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Note that if will be a *free* incident if you only asking for the hotfix
from PSS :)
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||As a quick fix, you can delete old logs using a scheduled job. Check
http://solidqualitylearning.com/blogs/dejan/archive/2004/12/19/225.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Michael" <missisummer@.community.nospam> wrote in message
news:C1FE7688-A420-49E1-A17C-0FC736DECC7D@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
was
> generated every 3 minutes until it took up the entire C:\ drive. The log
file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael

Frequent generated log file takes up the entire system drive

This happend twice on two different servers. A 33MB reporting service log was
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
.......
Thanks,
Michael
This is a known bug fixed in the imminent SP2 for Reporting Services.
http://support.microsoft.com/kb/885286/
You can get a hotfix, details are in the KB article. What've I've tended to
do is just set up a scheduled task to check the size of the logs folder and
if it gets over a certain size start deleting old logs.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Michael" <missisummer@.community.nospam> wrote in message
news:6A7FD5FA-AA33-4B15-8472-D204B6D8B75A@.microsoft.com...
> This happend twice on two different servers. A 33MB reporting service log
> was
> generated every 3 minutes until it took up the entire C:\ drive. The log
> file
> has a header then repeat some message as in the following exerpt for 30+
> thousand times. Then it starts another log file endlessly until the whole
> C:\
> drive is used.
> It has caused the interruptions of programs hosted on the server. After
> delete the logs the reporting service is back to normal. But we need to
> prevent this from happening again.
> <Header>
> <Product>Microsoft SQL Server Reporting Services Version
> 8.00.743.00</Product>
> <Locale>en-US</Locale>
> <TimeZone>Eastern Standard Time</TimeZone>
> <Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
> <SystemName>WSReport</SystemName>
> <OSName>Microsoft Windows NT 5.2.3790.0</OSName>
> <OSVersion>5.2.3790.0</OSVersion>
> </Header>
> ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
> Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiring old execution log entries
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Expiration of old execution log entries is complete. Removed 0 entries.
> ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
> Cleaned
> 0 broken snapshots, 0 chunks
> ......
> Thanks,
> Michael
|||Jasper,
My DBA is looking for this. Would you be able to send me the code for this
scheduled task. I'd apprciate it.
Bill..
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%238P0SFuNFHA.604@.TK2MSFTNGP10.phx.gbl...
> This is a known bug fixed in the imminent SP2 for Reporting Services.
> http://support.microsoft.com/kb/885286/
> You can get a hotfix, details are in the KB article. What've I've tended
> to do is just set up a scheduled task to check the size of the logs folder
> and if it gets over a certain size start deleting old logs.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Michael" <missisummer@.community.nospam> wrote in message
> news:6A7FD5FA-AA33-4B15-8472-D204B6D8B75A@.microsoft.com...
>
|||> Jasper,
> My DBA is looking for this. Would you be able to send me the code for
this
> scheduled task. I'd apprciate it.
> Bill..
You can use something I create couple of months ago for diferent purposes,
but can apply to this problem as well:
http://solidqualitylearning.com/blog...2/19/225.aspx.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Freqent generated log files took up the entire system drive

This happend twice on two different servers. A 33MB reporting service log was
generated every 3 minutes until it took up the entire C:\ drive. The log file
has a header then repeat some message as in the following exerpt for 30+
thousand times. Then it starts another log file endlessly until the whole C:\
drive is used.
It has caused the interruptions of programs hosted on the server. After
delete the logs the reporting service is back to normal. But we need to
prevent this from happening again.
<Header>
<Product>Microsoft SQL Server Reporting Services Version
8.00.743.00</Product>
<Locale>en-US</Locale>
<TimeZone>Eastern Standard Time</TimeZone>
<Path>C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\LogFiles\ReportServerService__03_29_2005_ 18_55_53.log</Path>
<SystemName>WSReport</SystemName>
<OSName>Microsoft Windows NT 5.2.3790.0</OSName>
<OSVersion>5.2.3790.0</OSVersion>
</Header>
ReportingServicesService!runningjobs!118c!3/29/2005-18:55:53:: i INFO:
Execution Log Entry Expiration timer enabled: Cycle: 25446 seconds
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiring old execution log entries
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO:
Expiration of old execution log entries is complete. Removed 0 entries.
ReportingServicesService!dbcleanup!1a30!3/29/2005-18:55:53:: i INFO: Cleaned
0 broken snapshots, 0 chunks
.......
Thanks,
Michael
Hi Michael,
I found you have another two posts with the same topic in
microsoft.public.sqlserver.reportingsvcs and
microsoft.public.sqlserver.tools, both has response from MVP and community
members. To keep the integrity of newsgroup and benefit others. I will
follwo up the questions in microsoft.public.sqlserver.reportingsvcs
instead of these two.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

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 search not working after SQL 2005 upgrade

*** See next post as I have found it is nothing to do with freetext search I just assumed it was this ***

Hi

I have upgraded our test system to SQL 2005 from 2000 and restored a production database.

I have rebuilt the freetext index and it would seem that if you query this from an .ASP page using OLEDB it does not return any results even though there are some. If you then run the same query in Management studio you do get the expected results. The code worked perfectly under SQL 2000. Also all other queries that run from the .ASP page work correctly that do not involve a freetext seach.

I have made sure the client is using the lastest MDAC pack 2.8 sp1

The query is

SELECT title
FROM catalog
WHERE CONTAINS(title, 'green*')

Connection string in .ASP page

Provider=SQLOLEDB;Data Source=192.168.0.5;Initial Catalog=DVD;User Id=XXXXXX;Password=XXXXXX;

Thanks in Advance

MatRight after more digging I have found out it is NOT the freetext seach at all. I simplified the query above and removed a left join when I posted the question as I thought it could not be this.

I have now just created two simple tables in my database
linkme
linkme2

both of which have one column called [catalog-no]

I have put one record in linkme

select * from linkme -- pulls back one record correctly via .asp and management studio
select * from linkme left join linkme2 on linkme.[catalog-no] = linkme2.[catalog-no] -- pulls back one record in management studio and 0 records in .asp connection !!!

HELP|||Just to make sure I was not going mad I have created the same two table in SQL 2000 and pointed the .asp script to this and sure enough i get 1 record ?

Free text search for 2 million records

Hi

I have a new client with an existing system that has just over 2 million business listings in one table. Each business listing is associated with one business category.

* Company Table (around 20 fields):

companyID
companyName
categoryID
state
postCode
etc.

* Category Table (5 fields)

categoryID
categoryName
etc.

We are using MSSQL 2005 Express Edition with Advanced Services

A free text search needs to be performed on the companyName and categoryName limited by region (state and or postcode).

1) What kind of response times should I expect for the free text search (I have not used the free text search before)

2) How should I index the companyName and categoryName so they are both used in a joined query? i.e. Do I just configure the free text search index on each field separately and it should work?

Any suggestions appreciated.

Best Regards

Kevan1) Impossible to tell. It highly depends on the memory, disk solution, allocated memory to SQL Server, processor, operating system configurations and more.

2) Indexing the keys used in a join (that is: used in the on clause of the join) may improve performance.|||Thanks for that.

1)

Yes, that makes sense. I was really after a very general idea of other peoples experience when dealing with free text search on a couple of million records. What sort of average response times have other people achieved with simple free text searches on this number of records. We would most likely be using a low level hardware solution on a hosted server.

2)

Thanks

Fragmented System Tables

I have found that a bunch of our tables were highly fragemented. (in some cases as much as 80%)
I used DBCC dBReindex to correct all the user tables, but how do I defrag the system table indexes?
Thanks--all except clustered indexes on sysobjects and sysindexes
dbcc dbrepair(@.dbname, repairindex, @.tabname, @.indid)

-- And an example
exec sp_dboption 'YourDatabase','single user','true'
dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'

-- And better encapsulated in undocumented MS SP
exec sp_dboption 'YourDatabase','single user','true'
exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'|||Originally posted by ispaleny
--all except clustered indexes on sysobjects and sysindexes
dbcc dbrepair(@.dbname, repairindex, @.tabname, @.indid)

-- And an example
exec sp_dboption 'YourDatabase','single user','true'
dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'

-- And better encapsulated in undocumented MS SP
exec sp_dboption 'YourDatabase','single user','true'
exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'

Thank you very much,

2 additional related questions

1) Can this be set to run weekly via a scheduled Job?
2) Can this be done with users still connected

(we have users connected 24/7 via internet)

Thanks again.|||Q1) Can this be set to run weekly via a scheduled Job?
A1) Yes. But you must add a script for disconnecting users.

Q2) Can this be done with users still connected
A2) No. The database must be in a single user mode.

I must be a funny job to maintain a living database :)
Let me know how you solved it.

Good luck !|||If you have set up your web site to have one place where the connections are made AND if the database is not THAT huge, you could replicate it, run the corrections, point the connections to the "new" database, replace the old one and go on.

That should be something that could certainly be automated. Might be more work than its worth tho...