Column in contains clause?

Dear all,
Another question: I would like to get an overview of how often a number
of words, stored in a table KO, occur in a full text indexed column on
another table. I can do this seperately:
select * from names
id | name
--+--
1 | bush
2 | kerry
select count(*) from texts where contains(text, 'bush')
2,123
select count(*) from texts where contains(text, 'dean')
1,326
[numbers entiry fictional]
But since I have a large number of such names, I would like to just
join the count per name to the names table like so:
select name, count(*) from
names n, texts t
where contains(text, n.name)
group by name
but this returns an 'incorrect syntax near n'.
Why doesn't the above work? Is what I am trying to do possible using
the contains function? Is there another way to achieve this goal?
Thanks!
Wouter
It doesn't work because the Contains operator is expecting a single value
instead of a column - which is what you are passing.
I think what you need to do is something like this you might want to write a
cursor or perhaps a function that will return a table variable to accomplish
this.
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
"wouter" <wouter@.2at.nl> wrote in message
news:1108297074.753022.57670@.l41g2000cwc.googlegro ups.com...
> Dear all,
> Another question: I would like to get an overview of how often a number
> of words, stored in a table KO, occur in a full text indexed column on
> another table. I can do this seperately:
> select * from names
> id | name
> --+--
> 1 | bush
> 2 | kerry
> select count(*) from texts where contains(text, 'bush')
> 2,123
> select count(*) from texts where contains(text, 'dean')
> 1,326
> [numbers entiry fictional]
> But since I have a large number of such names, I would like to just
> join the count per name to the names table like so:
> select name, count(*) from
> names n, texts t
> where contains(text, n.name)
> group by name
> but this returns an 'incorrect syntax near n'.
> Why doesn't the above work? Is what I am trying to do possible using
> the contains function? Is there another way to achieve this goal?
> Thanks!
> Wouter
>

Collations and simple import from a csv-file

Hello. Here a two different problems that occur one and a while when I try to import a textfile to SQL Server 2005.

I have a flat file connection to a csv-file that originally is a export from an AS4000 DB2 database This csv-file is defined as a variable length file Why do SSIS automatically interpret the length of each column as varchar(50)? It does not matter if a define the same file as a fixed lenght file. The problem is that I will get a warning that information in columns will be truncated. I would like to do a direct export to the SQL Server 2005 table with shorter varchar fields. I can solve this by using the task for transforming data types but this only works on the text fields. This task cannot transform a string to a decimal or an integer column in the SQL Server 2005 table. Is there no other way than having a staging table between the text file and the SSIS-data pipe? I also get a lot of collation or code page errors even if we set the receiving columns to nvarchar and nchar. Is there any good article on this subject? Code page errors

Last question. Is there parameter support in the data reader source connection?

I appreciate all help.

Regards

Thomas Ivarsson

You can control the data types and lengths that SSIS uses for flat files by going to the Advanced area in the flat file connection manager. It will show you the columns defined, their data types, and their lengths, and you can alter the values to match what you want.

Parameter support in the data reader source: I don't believe that it does, but you can create the SQL that you want by building it in a variable, then assigning the variable to the Data Reader Source.SQL Command property using an expression.

|||

Thank's for the answere jwelch. I have actually tried the advanced properties and have set the flat file connection data types according to the target table data types.

Perhaps I have another error but this configuration will work?

I will have a look at your advice regarding the data reader source.

Regards

Thomas Ivarsson