Convert 1-column result set to 3 columns

I have a one-column result from a SELECT statement that I wish to
spread out into three columns, i.e.
Col
--
A
B
C
D
E
Turns into:
C1 C2 C3
-- -- --
A B C
D E
There is no identity column so I couldn't try using MOD() to come up
with a solution -- any ideas how this might be possible, preferably not
using a temp table?What are the criteria for this denormalisation - i.e. how do you determine
which values belong in which column?
What are you trying to achieve (on a more global scale)? It would help if
you could give us more information.
Also google for "cross-tab query".
ML
http://milambda.blogspot.com/|||ML wrote:
> What are the criteria for this denormalisation - i.e. how do you determine
> which values belong in which column?
There is no extra logic, I simply need to put a set of 1-column data
into three columns from left to right, top to bottom.

> What are you trying to achieve (on a more global scale)? It would help if
> you could give us more information.
The reason is that I am designing a report using SQL Reporting Services
where I need to put data in three columns across the page. Reporting
Services supports 'table' element which displays results in rows.
However each of my result is quite narrow so I need to fit 3 of them in
one row to save paper. And because this is not possible using report
designer, I decided to do it at SQL side.|||I've come up with a solution which uses temp table -- it works, but
since my team thinks temp table is absolutely evil it'd be great if
this can be converted into a more streamlined version!
-- Create data table --
drop table t
create table t
(
C char NOT NULL
)
insert into t values('A')
insert into t values('B')
insert into t values('C')
insert into t values('D')
insert into t values('E')
insert into t values('F')
insert into t values('G')
insert into t values('H')
select * from t
-- using temp table to reorganize result
drop table #tt1
create table #tt1
(
id int identity,
c char not null
)
-- Data
insert into #tt1 select c from t
-- Helper data used for incomplete last row
insert into #tt1 (c) values (' ')
insert into #tt1 (c) values (' ')
insert into #tt1 (c) values (' ')
-- convert one-column table into a three-column one
SELECT t1.c, t2.c, t3.c
from #tt1 t1
join #tt1 t2 on t2.id=t1.id+1
join #tt1 t3 on t3.id=t2.id+1 or (t2.id = ' ' and t3.id=' ') or (t3.id
= ' ')
where
t1.id % 3 = 1
and t2.id % 3 = 2
and t3.id % 3 = 0|||"Anything shall be evil in hands of the evil."
-- less known Apostle
If your team thinks using temporary tables is evil, then what do they say
about the fact that you're denormalizing data purely for formatting purposes
?
As long as the temporary tables are dropped after use and global (##)
temporary tables are used only when several processes use the same data, and
if there is a sufficient contextual isolation of these processes, then there
is nothing evil about temporary tables. Is an AK-47 evil by itself?
ML
http://milambda.blogspot.com/|||Can there be duplicates in the values?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try,
create table dbo.t (
C char NOT NULL
)
insert into dbo.t values('A')
insert into dbo.t values('B')
insert into dbo.t values('C')
insert into dbo.t values('D')
insert into dbo.t values('E')
insert into dbo.t values('F')
insert into dbo.t values('G')
insert into dbo.t values('H')
go
select
max(
case when rank % 3 = 1 then c end
) as c1,
max(
case when rank % 3 = 2 then c end
) as c2,
max(
case when rank % 3 = 0 then c end
) as c3
from
(
select
count(*) - ((count(*) - 1) % 3) as pk,
count(*) as rank,
t1.c
from
dbo.t as t1
inner join
dbo.t as t2
on t2.c <= t1.c
group by
t1.c
) as a
group by
pk
order by
pk
go
drop table dbo.t
go
AMB
"ak199" wrote:

> I have a one-column result from a SELECT statement that I wish to
> spread out into three columns, i.e.
> Col
> --
> A
> B
> C
> D
> E
> Turns into:
> C1 C2 C3
> -- -- --
> A B C
> D E
> There is no identity column so I couldn't try using MOD() to come up
> with a solution -- any ideas how this might be possible, preferably not
> using a temp table?
>

Conversion issues on Output Columns with Script Task

I am not sure which type to use for my Script Transformation Editor output fields. I'm getting errors based on the Data Type I'm specifying for my fields.

Print Screens:

http://www.webfound.net/script_task.jpg

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61

Error at Import Maintenance (mnt) File [DTS.Pipeline]: "component "Split HeaderRows into Columns" (5176)" failed validation and returned validation status "VS_ISBROKEN".

Error at Import Maintenance (mnt) File [DTS.Pipeline]: One or more component failed validation.

Error at Import Maintenance (mnt) File: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I'm not sure if this is needed but here's the script I coded in my script task also:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim strWholeRow As String = Row.OutputHeaderRows

Row.BatchDate = CStr(strWholeRow.Substring(0, 8))

Row.NotUsed = CStr(strWholeRow.Substring(9, 32))

Row.TransactionCode = CStr(strWholeRow.Substring(33, 34))

Row.GrossBatchTotalAmount = CDbl(strWholeRow.Substring(35, 44))

Row.NetBatchTotalAmount = CDbl(strWholeRow.Substring(45, 54))

Row.BatchTransactionCount = CDbl(strWholeRow.Substring(55, 59))

Row.PNETID = CStr(strWholeRow.Substring(60, 63))

Row.PartnerCode = CDbl(strWholeRow.Substring(64, 67))

Row.Filler = strWholeRow.Substring(68, 100)

End Sub

End Class

Looking at the screenshot and the code it looks like you're trying to put a decimal number into an integer column and you simply can't do that. You'll have to change either the type of the output column (try using DT_DECIMAL) or change CDbl to CInt.

-Jamie

conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
SridharThis script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =
char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =
END +=20
' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo=20
--=20
Keith
<anonymous@.discussions.microsoft.com> wrote in message =
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
>=20
> We are using sql server 2000..We intend to change the=20
> char/varchar columns to nchar/nvarchat.Our application=20
> contains lot's of dynamic tables.Hence there are no=20
> standard no of tables and indexes in all sites..Can any=20
> one help me out in writing scripts which queries the=20
> dictionary objects and gives scripts which would work fine=20
> in all sites...?
>=20
>=20
> Thanks in advance
>=20
> Sridhar
>|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
fine[vbcol=seagreen]
>.
>|||SQL Server stores this information in system tables, like sysindexes, syscom
ments etc. You can read off of
these and use that information to re-generate the statements needed to re-cr
eate your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/in...ate_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.p
hx.gbl...[vbcol=seagreen]
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> (10), thircol char(10))
> nvarchar ' END +
> ('varchar', 'char')
> select statement
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> fine

conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
SridharThis script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =END + ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo
-- Keith
<anonymous@.discussions.microsoft.com> wrote in message =news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
> > We are using sql server 2000..We intend to change the > char/varchar columns to nchar/nvarchat.Our application > contains lot's of dynamic tables.Hence there are no > standard no of tables and indexes in all sites..Can any > one help me out in writing scripts which queries the > dictionary objects and gives scripts which would work fine > in all sites...?
> > > Thanks in advance
> > Sridhar
>|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
>> Hi,
>> We are using sql server 2000..We intend to change the
>> char/varchar columns to nchar/nvarchat.Our application
>> contains lot's of dynamic tables.Hence there are no
>> standard no of tables and indexes in all sites..Can any
>> one help me out in writing scripts which queries the
>> dictionary objects and gives scripts which would work
fine
>> in all sites...?
>>
>> Thanks in advance
>> Sridhar
>.
>|||SQL Server stores this information in system tables, like sysindexes, syscomments etc. You can read off of
these and use that information to re-generate the statements needed to re-create your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.phx.gbl...
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> >--Original Message--
> >This script should get you started:
> >
> >use tempdb
> >GO
> >CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
> (10), thircol char(10))
> >go
> >
> >
> >SELECT 'ALTER TABLE ' + table_name +
> > ' ALTER COLUMN ' + COLUMN_NAME +
> > CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
> nvarchar ' END +
> > ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> > char(13) + char(10) + 'GO'
> >FROM information_schema.columns WHERE DATA_TYPE IN
> ('varchar', 'char')
> >
> >--now execute the statements that are returned from the
> select statement
> >
> >go
> >DROP TABLE foo
> >
> >
> >
> >
> >--
> >Keith
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> We are using sql server 2000..We intend to change the
> >> char/varchar columns to nchar/nvarchat.Our application
> >> contains lot's of dynamic tables.Hence there are no
> >> standard no of tables and indexes in all sites..Can any
> >> one help me out in writing scripts which queries the
> >> dictionary objects and gives scripts which would work
> fine
> >> in all sites...?
> >>
> >>
> >> Thanks in advance
> >>
> >> Sridhar
> >>
> >.
> >

conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
Sridhar
This script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =
char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =
END +=20
' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo=20
--=20
Keith
<anonymous@.discussions.microsoft.com> wrote in message =
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
>=20
> We are using sql server 2000..We intend to change the=20
> char/varchar columns to nchar/nvarchat.Our application=20
> contains lot's of dynamic tables.Hence there are no=20
> standard no of tables and indexes in all sites..Can any=20
> one help me out in writing scripts which queries the=20
> dictionary objects and gives scripts which would work fine=20
> in all sites...?
>=20
>=20
> Thanks in advance
>=20
> Sridhar
>
|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...[vbcol=seagreen]
fine
>.
>
|||SQL Server stores this information in system tables, like sysindexes, syscomments etc. You can read off of
these and use that information to re-generate the statements needed to re-create your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/inf...te_script.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> (10), thircol char(10))
> nvarchar ' END +
> ('varchar', 'char')
> select statement
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> fine
sqlsql

Control Report Parameters Display

Is there a way to control the way report parameters are displayed in terms of number of columns etc. The reason we need that is we have like 15 parameters in one of the report and then it just looks ugly.

Thanks.

Not if you are viewing the Reports via Report Manager. If you are using the Viewer Controls that ship with VS 2005 you could but it would mean writing your own parameter area.

Continuous data on report

Hi,
I have a report that has loads of rows but only few narrow columns.
The preferred way to do the layout would be:
Landscape
and having two identical tables next to each other. The one on the left
would display the first 50 rows or so, the one to the right the next
50.
Is that possible in RS and if yes, how'?
Thanks for any help,
DomI don't really have a solution ... but if you really really can't find a way
to do it, you could simply use two dataset query. One using TOP 50 and the
other BOTTOM 50.
But whatever you do, I have had a problem using two tables side by side. In
the preview tab everything displays fine, but when published and viewed
through an asp reportviewer control there is always a breakline after the
first table and they cannot be displayed side by side.
"DominicB" wrote:
> Hi,
> I have a report that has loads of rows but only few narrow columns.
> The preferred way to do the layout would be:
> Landscape
> and having two identical tables next to each other. The one on the left
> would display the first 50 rows or so, the one to the right the next
> 50.
> Is that possible in RS and if yes, how'?
> Thanks for any help,
> Dom
>|||Yes you can set up a multi column report... I have an example on
www.msbicentral.com
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"DominicB" <dbuschi@.gmail.com> wrote in message
news:1124375773.648448.12870@.g44g2000cwa.googlegroups.com...
> Hi,
> I have a report that has loads of rows but only few narrow columns.
> The preferred way to do the layout would be:
> Landscape
> and having two identical tables next to each other. The one on the left
> would display the first 50 rows or so, the one to the right the next
> 50.
> Is that possible in RS and if yes, how'?
> Thanks for any help,
> Dom
>|||Thanks both for your help!
Wayne, where on your site can I find that example (I registered, but
couldn't find it)?
Thx,
Dom

CONTAINSTABLE v/s FREETEXT

Hi All,
I have one table Customers in Northwind database.
While performing FreeText search I want to give weightage ON Columns for
a searched phrase.
CompanyName weight (1) ContactName weight
(.7)
ContactTitle weight (.5) Address
weight (.5)
If I Searched for "Christina" phrase then results will be displayed base
on weightage as well as rank.
i.e. results having "Christina" in CompanyName should be displayed
first. If I found "Christina" only in Address then this should be last.
Any Ideas?
Regards
AbhijeetAbhijeet,
First of all, all of the tables in Northwind are much too small for proper
consideration of the understanding of CONTAINS* vs. FREETEXT*, in the same
manner as you would not use the Northwind database for performance
benchmarking of production databases. Note, FREETEXT ignores Boolean
comparisons. You need a statistically significant number of rows (100K+) as
well as a significant number of non-noise, unique words for your testing to
give valid results.
Also, since you're trying to "weightage ON Columns", you should review KB
articles: 286787 (Q286787) FIX: Incorrect Results From Full-Text Search on
Several Columns
http://support.microsoft.com/default.aspx?scid=kb;en-us;286787 and 294809
(Q294809) FIX: Full-Text Search Queries with CONTAINS Clause Search Across
Columns http://support.microsoft.com/default.aspx?scid=kb;en-us;294809. This
is the default behavior for SQL Server 2000 as well.
Regards,
John
PS: for the fastest (and best) responses, you should post these specific FTS
related questions to the newsgroup: microsoft.public.sqlserver.fulltext
only.
"Abhijeet Raje" <abhijeet2804@.hotmail.com> wrote in message
news:%23HO5DZsQDHA.1988@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I have one table Customers in Northwind database.
> While performing FreeText search I want to give weightage ON Columns
for
> a searched phrase.
> CompanyName weight (1) ContactName
weight
> (.7)
> ContactTitle weight (.5) Address
> weight (.5)
> If I Searched for "Christina" phrase then results will be displayed
base
> on weightage as well as rank.
> i.e. results having "Christina" in CompanyName should be displayed
> first. If I found "Christina" only in Address then this should be last.
> Any Ideas?
> Regards
> Abhijeet
>

ContainsTable not returning rows

Hello folks,
I have a table with several FT-enabled varchar columns, in SQL Server
2000.
If I ask for SELECT * FROM tbl WHERE CONTAINS(*, SearchStringA) I get
several rows. (as expected)
If I ask SELECT * FROM tbl WHERE CONTAINS(*, SearchStringB) I also get
several rows and they are an overlapping set of rows. (also as expected)
However, if I ask for SELECT * FROM tbl WHERE CONTAINS(*, SearchStringA AND
SearchStringB) I get no rows. The two seperate searches find matches in
different columns. Is this the problem, and if so, what is the best way to
get a correct result?
Thanks in advance,
Martin
If you want to look across columns you would have to do a FreeText query,
however with contains you would have to do this
select * from tablename where contains(col1, 'SearchPhraseA') or
contains(col2,'SearchPhraseB')
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
"Martin Hungerford" <Martin Hungerford@.discussions.microsoft.com> wrote in
message news:4E584970-A671-460B-A6AE-B65ECC68DBF9@.microsoft.com...
> Hello folks,
> I have a table with several FT-enabled varchar columns, in SQL Server
> 2000.
> If I ask for SELECT * FROM tbl WHERE CONTAINS(*, SearchStringA) I get
> several rows. (as expected)
> If I ask SELECT * FROM tbl WHERE CONTAINS(*, SearchStringB) I also get
> several rows and they are an overlapping set of rows. (also as expected)
> However, if I ask for SELECT * FROM tbl WHERE CONTAINS(*, SearchStringA
AND
> SearchStringB) I get no rows. The two seperate searches find matches in
> different columns. Is this the problem, and if so, what is the best way to
> get a correct result?
> Thanks in advance,
> Martin

containstable across columns

We're having some difficulty getting adequate search results. We're trying to search across 3 columns, "author", "title", and "subtitle" (no other columns are part of FT index). The problem relates to searching for an author/title phrase at the same tim
e. Say the user types "chemistry brown" into a search box, (the Author being "Brown" and the title "Chemistry"--but there's no way to make that distinction at run time). If you use CONTAINSTABLE, (...from containstable(<ourtable>,*,'"brown" near "chemis
try"') it only return rows where "brown" and "chemistry" are in the SAME column (where what we need is for it to return high rankings where "brown" and "chemistry" are in different columns but the same rows). Freetexttable is no better, since again it's
ranking based on the number of occurrences of a word in a SINGLE column, not in all the FT-indexed columns per row.
The key factor is that the type of word (author, title, subtitle) is not known at search time, which it difficult. Any suggesstions?
Platform: SQl 2K sp3, Windows Server 2000 Sp4
you could try this
select * from containstable(fulltext,*,'chemistry or brown')
or
select * from containstable(fulltext,*,'"chemistry" or "brown"')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John C" <John C@.discussions.microsoft.com> wrote in message
news:36E8719A-769E-45D1-A05B-FD8BC0B170E5@.microsoft.com...
> We're having some difficulty getting adequate search results. We're
trying to search across 3 columns, "author", "title", and "subtitle" (no
other columns are part of FT index). The problem relates to searching for
an author/title phrase at the same time. Say the user types "chemistry
brown" into a search box, (the Author being "Brown" and the title
"Chemistry"--but there's no way to make that distinction at run time). If
you use CONTAINSTABLE, (...from containstable(<ourtable>,*,'"brown" near
"chemistry"') it only return rows where "brown" and "chemistry" are in the
SAME column (where what we need is for it to return high rankings where
"brown" and "chemistry" are in different columns but the same rows).
Freetexttable is no better, since again it's ranking based on the number of
occurrences of a word in a SINGLE column, not in all the FT-indexed columns
per row.
> The key factor is that the type of word (author, title, subtitle) is not
known at search time, which it difficult. Any suggesstions?
> Platform: SQl 2K sp3, Windows Server 2000 Sp4
|||Hilary,
While the below containstable query examples provide a solution, it does not
meet John's requirement of 'search across 3 columns, "author", "title", and
"subtitle"' and I submit that using the following FT-enabled Northwind table
(Employees) and multiple column specific column names (vs. using "*" or
asterisk for all FT-enabled columns) does meet John's request, for example:
use Northwind
go
SELECT LastName, FirstName, Title, Notes from Employees
/* returns:
LastName FirstName Title
-- -- --
Davolio Nancy Sales Representative
Fuller Andrew Vice President, Sales
Leverling Janet Sales Representative
Peacock Margaret Sales Representative
Buchanan Steven Sales Manager
Suyama Michael Sales Representative
King Robert Sales Representative
Callahan Laura Inside Sales Coordinator
Dodsworth Anne Sales Representative
(9 row(s) affected)
*/
-- an expanded version of your example. Note, that this query will return
all 9 rows that contain Fuller (LastName column) OR Sales (Title column)
SELECT e.LastName, e.FirstName, e.Title
from Employees AS e,
containstable(Employees,*,'Fuller or Sales') as A
where
A.[KEY] = e.EmployeeID
--or this example that will also return all 9 rows that contain Fuller
(LastName column) OR Sales (Title column)
SELECT e.LastName, e.FirstName, e.Title
from Employees AS e,
containstable(Employees,*,'"Fuller" or "Sales"') as A
where
A.[KEY] = e.EmployeeID
However, John wants to 'search across 3 columns, "author", "title", and
"subtitle"' and the user types "chemistry brown" into a search box, (the
Author being "Brown" and the title "Chemistry"). Note the request is for an
AND'ing between the FT-enabled columns and not an OR between the columns.
This can be satsified via using multiple CONTAINSTABLE clauses and AND'ing
the join between Containstable predicates, for example
SELECT e.LastName, e.FirstName, e.Title
from Employees AS e,
containstable(Employees, LastName, 'Fuller') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID AND
B.[KEY] = e.EmployeeID
go
/* -- returns:
LastName FirstName Title
-- -- --
Fuller Andrew Vice President, Sales
*/
John, you can also use the workarounds that are documented for this issue in
SQL Server 7.0 KB article "286787 (Q286787) FIX: Incorrect Results From
Full-Text Search on Several Columns" at
http://support.microsoft.com/default...b;en-us;286787 as well as
SQL Server 7.0 KB article "294809 (Q294809) FIX: Full-Text Search Queries
with CONTAINS Clause Search Across Columns" at
http://support.microsoft.com/default...;en-us;294809. You should
note that while these SQL Server 7.0 specific KB articles are "fixes" for
SQL Server 7.0, this is the default behavior for SQL Server 2000 and SQL
Server 7.0 was "fixed" to comply with the default behavior of SQL Sever
2000. The workarounds in the KB articles will also work in SQL Server 2000
as well as the above solution that I provided above.
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#Zt$PpiZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> you could try this
> select * from containstable(fulltext,*,'chemistry or brown')
> or
> select * from containstable(fulltext,*,'"chemistry" or "brown"')
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "John C" <John C@.discussions.microsoft.com> wrote in message
> news:36E8719A-769E-45D1-A05B-FD8BC0B170E5@.microsoft.com...
> trying to search across 3 columns, "author", "title", and "subtitle" (no
> other columns are part of FT index). The problem relates to searching for
> an author/title phrase at the same time. Say the user types "chemistry
> brown" into a search box, (the Author being "Brown" and the title
> "Chemistry"--but there's no way to make that distinction at run time). If
> you use CONTAINSTABLE, (...from containstable(<ourtable>,*,'"brown" near
> "chemistry"') it only return rows where "brown" and "chemistry" are in the
> SAME column (where what we need is for it to return high rankings where
> "brown" and "chemistry" are in different columns but the same rows).
> Freetexttable is no better, since again it's ranking based on the number
of
> occurrences of a word in a SINGLE column, not in all the FT-indexed
columns
> per row.
> known at search time, which it difficult. Any suggesstions?
>
|||Thank you John. However, we've tried using multiple containstable and the problem is that you get, as you noted, an ANDing of your results. Returning to our previous example (user types "brown chemistry" in search box, with "brown" the implicit author a
nd "chemistry" the implicit title), we'd like to have chemistry books written by Brown as the highest ranked results, but also return other chemistry books (not by Brown) and books authored by Brown (but w/o "Chemistry") in the title. The JOINing of 2 co
ntainstable queries rules that out. When reading your reply I first thought that using a LEFT join would solve that problem, but that assumes you know which search term(s) are the most significant (i.e., which side to make the right, and which to make th
e left).
John
"John Kane" wrote:

> Hilary,
> While the below containstable query examples provide a solution, it does not
> meet John's requirement of 'search across 3 columns, "author", "title", and
> "subtitle"' and I submit that using the following FT-enabled Northwind table
> (Employees) and multiple column specific column names (vs. using "*" or
> asterisk for all FT-enabled columns) does meet John's request, for example:
> use Northwind
> go
> SELECT LastName, FirstName, Title, Notes from Employees
> /* returns:
> LastName FirstName Title
> -- -- --
> Davolio Nancy Sales Representative
> Fuller Andrew Vice President, Sales
> Leverling Janet Sales Representative
> Peacock Margaret Sales Representative
> Buchanan Steven Sales Manager
> Suyama Michael Sales Representative
> King Robert Sales Representative
> Callahan Laura Inside Sales Coordinator
> Dodsworth Anne Sales Representative
> (9 row(s) affected)
> */
> -- an expanded version of your example. Note, that this query will return
> all 9 rows that contain Fuller (LastName column) OR Sales (Title column)
> SELECT e.LastName, e.FirstName, e.Title
> from Employees AS e,
> containstable(Employees,*,'Fuller or Sales') as A
> where
> A.[KEY] = e.EmployeeID
> --or this example that will also return all 9 rows that contain Fuller
> (LastName column) OR Sales (Title column)
> SELECT e.LastName, e.FirstName, e.Title
> from Employees AS e,
> containstable(Employees,*,'"Fuller" or "Sales"') as A
> where
> A.[KEY] = e.EmployeeID
> However, John wants to 'search across 3 columns, "author", "title", and
> "subtitle"' and the user types "chemistry brown" into a search box, (the
> Author being "Brown" and the title "Chemistry"). Note the request is for an
> AND'ing between the FT-enabled columns and not an OR between the columns.
> This can be satsified via using multiple CONTAINSTABLE clauses and AND'ing
> the join between Containstable predicates, for example
> SELECT e.LastName, e.FirstName, e.Title
> from Employees AS e,
> containstable(Employees, LastName, 'Fuller') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID AND
> B.[KEY] = e.EmployeeID
> go
> /* -- returns:
> LastName FirstName Title
> -- -- --
> Fuller Andrew Vice President, Sales
> */
> John, you can also use the workarounds that are documented for this issue in
> SQL Server 7.0 KB article "286787 (Q286787) FIX: Incorrect Results From
> Full-Text Search on Several Columns" at
> http://support.microsoft.com/default...b;en-us;286787 as well as
> SQL Server 7.0 KB article "294809 (Q294809) FIX: Full-Text Search Queries
> with CONTAINS Clause Search Across Columns" at
> http://support.microsoft.com/default...;en-us;294809. You should
> note that while these SQL Server 7.0 specific KB articles are "fixes" for
> SQL Server 7.0, this is the default behavior for SQL Server 2000 and SQL
> Server 7.0 was "fixed" to comply with the default behavior of SQL Sever
> 2000. The workarounds in the KB articles will also work in SQL Server 2000
> as well as the above solution that I provided above.
> Regards,
> John
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:#Zt$PpiZEHA.2216@.TK2MSFTNGP10.phx.gbl...
> of
> columns
>
>
|||You're welcome, John
You can also "OR" the multiple containstable clauses, for example:
-- Note, the OR and the addition of distinct e.LastName and searching on
'Inside' from the Title column.
SELECT distinct e.LastName, e.FirstName, e.Title
from Employees AS e,
containstable(Employees, LastName, 'Fuller') as A,
containstable(Employees, Title, 'Inside') as B
where
A.[KEY] = e.EmployeeID OR
B.[KEY] = e.EmployeeID
/* -- returns:
LastName FirstName Title
-- -- --
Callahan Laura Inside Sales Coordinator
Fuller Andrew Vice President, Sales
*/
However, I believe that by " highest ranked results", you are really looking
for cross-column FT Search results and for what is also know as "best bets"
or forcing you're artificial or "editorial best selection" and that you
would like this to be keyword and column specific. While SharePoint Portal
Server has such as concept as best bets, SQL Server FTS does not directly
support this without having to pay a scalability penalty. However, I've
recently worked out a single column keyword specific solution to this, for
details see my replies under the fulltext newsgroup subject thread
"Full-text SharePoint". I just recently worked out the details to a scalable
single table, single column keyword-specific KeywordRank "booster" that can
also work with the ExtendedRank value in the example provided in the
"Full-text SharePoint" thread. Please, review this thread and post back here
any questions you might have about the procedures. What would be required of
you or your users, is a keyword or phrase which can be linked to each of row
in your FT-enable table, this can also be linked back to a query log table
that your searchers are using that will record the keywords that they are
using and what pages they have clicked on. Let me know if you want to pursue
this further.
Thanks,
John
"John C" <John C@.discussions.microsoft.com> wrote in message
news:1BDCCD8F-EA03-463E-BA6C-8D62BB0AE0E8@.microsoft.com...
> Thank you John. However, we've tried using multiple containstable and the
problem is that you get, as you noted, an ANDing of your results. Returning
to our previous example (user types "brown chemistry" in search box, with
"brown" the implicit author and "chemistry" the implicit title), we'd like
to have chemistry books written by Brown as the highest ranked results, but
also return other chemistry books (not by Brown) and books authored by Brown
(but w/o "Chemistry") in the title. The JOINing of 2 containstable queries
rules that out. When reading your reply I first thought that using a LEFT
join would solve that problem, but that assumes you know which search
term(s) are the most significant (i.e., which side to make the right, and
which to make the left).[vbcol=seagreen]
> John
> "John Kane" wrote:
not[vbcol=seagreen]
and[vbcol=seagreen]
table[vbcol=seagreen]
example:[vbcol=seagreen]
return[vbcol=seagreen]
an[vbcol=seagreen]
columns.[vbcol=seagreen]
AND'ing[vbcol=seagreen]
issue in[vbcol=seagreen]
as[vbcol=seagreen]
Queries[vbcol=seagreen]
should[vbcol=seagreen]
for[vbcol=seagreen]
2000[vbcol=seagreen]
(no[vbcol=seagreen]
for[vbcol=seagreen]
"chemistry[vbcol=seagreen]
If[vbcol=seagreen]
near[vbcol=seagreen]
the[vbcol=seagreen]
where[vbcol=seagreen]
number[vbcol=seagreen]
not[vbcol=seagreen]

Contains(*) question

When I do a full text index on 2 columns, then do a query like below, it
appears to only match rows where 1 column of the index satisfies the
criteria. I want the query to return all rows where a combination of the 2
columns satisfy the query. Do I have something set up wrong?
SELECT * FROM <table>
WHERE CONTAINS(*,'"lord","rings","dvd"')
For the following data, no row is returned, but I want it to be
column 1 contains 'lord' and 'rings'
column 2 contains 'dvd'
For the following data, a row is returned.
column 1 contains 'lord' and 'rings' and 'dvd'How about :
SELECT * FORM <table>
WHERE CONTAINS(*, '"lord" OR "rings" OR "dvd"')
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Brian Kitt wrote:

>When I do a full text index on 2 columns, then do a query like below, it
>appears to only match rows where 1 column of the index satisfies the
>criteria. I want the query to return all rows where a combination of the 2
>columns satisfy the query. Do I have something set up wrong?
>SELECT * FROM <table>
>WHERE CONTAINS(*,'"lord","rings","dvd"')
>For the following data, no row is returned, but I want it to be
>column 1 contains 'lord' and 'rings'
>column 2 contains 'dvd'
>For the following data, a row is returned.
>column 1 contains 'lord' and 'rings' and 'dvd'
>
>|||But I need the results to contain all 3 terms. An 'or' would return results
that contain 1 of the 3.
"Mike Hodgson" wrote:

> How about :
> SELECT * FORM <table>
> WHERE CONTAINS(*, '"lord" OR "rings" OR "dvd"')
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Brian Kitt wrote:
>
>|||On Sun, 9 Oct 2005 19:17:01 -0700, Brian Kitt wrote:

>When I do a full text index on 2 columns, then do a query like below, it
>appears to only match rows where 1 column of the index satisfies the
>criteria. I want the query to return all rows where a combination of the 2
>columns satisfy the query. Do I have something set up wrong?
>SELECT * FROM <table>
>WHERE CONTAINS(*,'"lord","rings","dvd"')
>For the following data, no row is returned, but I want it to be
>column 1 contains 'lord' and 'rings'
>column 2 contains 'dvd'
>For the following data, a row is returned.
>column 1 contains 'lord' and 'rings' and 'dvd'
Hi Brian,
I don't know much about full text indexing, so this one is a shot in the
dark - but would this work?
SELECT col01, col02, ...
FROM YourTable
WHERE CONTAINS (*, '"lord" AND "rings"')
AND CONTAINS (*, '"dvd"')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry, I misunderstood what you were trying to achieve. It seems like
the media type ought to be in its own column and referenced with normal
string operators rather than the CONTAINS() predicate. Something like:
select MediaTitle, MediaType, ... from Media
where CONTAINS (MediaTitle, '"lord" AND "rings"')
and MediaType = "dvd"
With a nonclustered index on the MediaType column, that would work much
more efficiently than a couple full-text searches. If you cannot change
the design then Hugo's suggestion looks like it should work, but that's
a really poor design (just having all the metadata jumbled together like
that) - you may as well just have a bunch of text files containing the
search terms in a directory structure and use the Windows explorer
search function to trawl through the text files. Why store data in a
relational database if it's not relational data?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Brian Kitt wrote:
>But I need the results to contain all 3 terms. An 'or' would return result
s
>that contain 1 of the 3.
>"Mike Hodgson" wrote:
>
>|||Brian,
This is a FAQ in the fulltext newsgroup, so I've blogged about how to do FTS
across columns - "SQL Server FTS across multiple tables or columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.e
ntry
Enjoy,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OYUovffzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Sorry, I misunderstood what you were trying to achieve. It seems like
> the media type ought to be in its own column and referenced with normal
> string operators rather than the CONTAINS() predicate. Something like:
> select MediaTitle, MediaType, ... from Media
> where CONTAINS (MediaTitle, '"lord" AND "rings"')
> and MediaType = "dvd"
> With a nonclustered index on the MediaType column, that would work much
> more efficiently than a couple full-text searches. If you cannot change
> the design then Hugo's suggestion looks like it should work, but that's
> a really poor design (just having all the metadata jumbled together like
> that) - you may as well just have a bunch of text files containing the
> search terms in a directory structure and use the Windows explorer
> search function to trawl through the text files. Why store data in a
> relational database if it's not relational data?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Brian Kitt wrote:
>
>

CONTAINS with AND across multiple Columns

How come when I am doing a CONTAINS search across multiple columns on
a table that I have full text indexed I don't get any matches when one
word is contained in one column and the other word is contained in the
other column in the same row of data? Here is a query where first
name is in one column and last name is in another column. Is the only
option to physically store this information concatenated together so
my search will behave as expected?
SELECT *
FROM dbo.Person
WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')
This is by design. In SQL 2000 a freetext search could look across columns.
http://www.zetainteractive.com - Shift Happens!
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
"Spencer" <spencer@.tabbert.net> wrote in message
news:227246c4-bc9e-40b8-a64b-1dd2c2ae8df6@.g21g2000hsh.googlegroups.com...
> How come when I am doing a CONTAINS search across multiple columns on
> a table that I have full text indexed I don't get any matches when one
> word is contained in one column and the other word is contained in the
> other column in the same row of data? Here is a query where first
> name is in one column and last name is in another column. Is the only
> option to physically store this information concatenated together so
> my search will behave as expected?
> SELECT *
> FROM dbo.Person
> WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')

CONTAINS on sql2005

I've just discovered that the CONTAINS clause on sql2005 can search
multiple columns - great!
Is there a way to return which column the search word(s) were found in?
Dunc
Regretably not.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Dunc" <duncan.welch@.gmail.com> wrote in message
news:1140444800.738404.90590@.g47g2000cwa.googlegro ups.com...
> I've just discovered that the CONTAINS clause on sql2005 can search
> multiple columns - great!
> Is there a way to return which column the search word(s) were found in?
> Dunc
>
|||On what edition did it work - just enterprise edition or even on standard
(and if so what is required to get it going?)
Jan
"Dunc" <duncan.welch@.gmail.com> schrieb im Newsbeitrag
news:1140444800.738404.90590@.g47g2000cwa.googlegro ups.com...
> I've just discovered that the CONTAINS clause on sql2005 can search
> multiple columns - great!
> Is there a way to return which column the search word(s) were found in?
> Dunc
>

CONTAINS and WHERE Clause Combination taking too long

Hi,

I have a table with 3 columns and 20 million records.
first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000).
I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column.
Now when i try to search

SELECT

TOP 20

col1,
col3

FROM

tbl

WHERE

col1 = '1234'

AND

CONTAINS(col3,'"market*"')

I am facing following problems
1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second.
2- Some time it show criteria is too complex, although i am only requesting a single word in col3.

I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information.

Please assist.

As of now, Fulltext index runs separately from the SQL Engine which means that you cannot influence the to be parsed subset of data on the fulltext catalog. Although the data from the relational query will only bring back 1 row, the whole fulltext will be parsed to findt he appropiate matches although they will be discarded later upon joining the two resultsets.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hi Jens,

Thanks, i was already affraid that it will be the case.
In this case what you suggest? Because LIKE '%%' is killing my performance.
Any suggestion will be appreciated.

|||

Make sure you optimized the speed of Fulltext (like separate spindles etc).

Jens K. Suessmeyer

http://www.sqlserver2005.de

contact details

I have a database which has contact column eg. Mr Peter Smith

I am writing a new database which is to have three seperate columns.. saluation, first name and surname. What would be the best way to split the column up?? I was thinking on concentrating on the spaces??

Note: some conacts may not have saluation inc in the contact column, and in this case the saluation column should be blank...

ThanksNote: some conacts may not have saluation inc in the contact column, and in this case the saluation column should be blank...

That bit should make it interesting.
Do you have a list somewhere of "valid" salutations?
What will you do with the extra space in:
Mr Peter Smith Jr.

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.
Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.
|||Thanks John..
Is it possible to find out the indexes on char columns

>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'[vbcol=seagreen]
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
and
>
>.
>
|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> and c.colid = s.colid
> char%'
> and

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.|||Thanks John..
Is it possible to find out the indexes on char columns

>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
and[vbcol=seagreen]
>
>.
>|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> and c.colid = s.colid
> char%'
> and

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.|||Thanks John..
Is it possible to find out the indexes on char columns
>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
>> hI,
>> Is there is a way to find constraints on columns char
and
>> varchar alone..(through query)
>> Sridhar.
>
>.
>|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
--
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> >--Original Message--
> >Hi
> >
> >You could try something like:
> >
> >SELECT object_name(c.id), c.name, t.name, s.*
> >FROM sysconstraints s JOIN syscolumns c on c.id = s.id
> and c.colid = s.colid
> >JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
> char%'
> >
> >John
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:91d001c43330$171e8760$a001280a@.phx.gbl...
> >> hI,
> >>
> >> Is there is a way to find constraints on columns char
> and
> >> varchar alone..(through query)
> >>
> >> Sridhar.
> >
> >
> >.
> >

Constraints

Can any one tell me regarding the foreign key constraints like I wanna
findout which columns doesn't contain the foreign key constraint so that I
can create it...
Thanks in advance.
Hi Joh,
Why do you want create Foreign key constrain for all the columns in a table
, Probably i understood your question wrongly.
What you could do is all the constrains specificatins can be view in the
below information schema view. Have a look into it and analyze ur
requirement.
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Thanks
Hari
SQL Server MVP
"Joh" <joh@.mailcity.com> wrote in message
news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Can any one tell me regarding the foreign key constraints like I wanna
> findout which columns doesn't contain the foreign key constraint so that I
> can create it...
>
> Thanks in advance.
>
|||No you took my question wrongly... I want to make sure Primary Key &
Foreign Key constraints are there in my database... nothing would be missed
for instance...
Emp Dept
EmpID DeptID
DeptID DeptName
EmpName
According to the above situation I have to make sure in the emp table there
should be a foreign key constraint on DeptID column.. Is there any query
through which I can make sure about it... hope now you understand my
question.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> Hi Joh,
> Why do you want create Foreign key constrain for all the columns in a
table[vbcol=seagreen]
> , Probably i understood your question wrongly.
> What you could do is all the constrains specificatins can be view in the
> below information schema view. Have a look into it and analyze ur
> requirement.
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> Thanks
> Hari
> SQL Server MVP
> "Joh" <joh@.mailcity.com> wrote in message
> news:eFoPhsfYFHA.1796@.TK2MSFTNGP15.phx.gbl...
I
>
|||Hi,
I dont have a query for this with me. I personally recommend you to use MS
Visio / Erwin to do the logical / physical data model with all relationship.
But for validating you could use INFORMATION_SCHEMA.TABLE_CONSTRAINTS system
view.
Thanks
Hari
SQL Server MVP
can you please work with the system view
INFORMATION_SCHEMA.TABLE_CONSTRAINTS? This will help
"Joh" <joh@.mailcity.com> wrote in message
news:OAQTHygYFHA.3364@.TK2MSFTNGP12.phx.gbl...
> No you took my question wrongly... I want to make sure Primary Key &
> Foreign Key constraints are there in my database... nothing would be
> missed
> for instance...
> Emp Dept
> EmpID DeptID
> DeptID DeptName
> EmpName
> According to the above situation I have to make sure in the emp table
> there
> should be a foreign key constraint on DeptID column.. Is there any query
> through which I can make sure about it... hope now you understand my
> question.
> Thanks
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u8VinYgYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> table
> I
>

Constraint or Trigger

Hi Guys,

I am not sure if this is the correct forum but i thought i'd ask and see if you can help me!

I have a table with 2 columns:

1st column will house numbers from 1 to 50

2nd column will be date

I want the users to be able to pick a number for certain date and enter it to the table, however I don't want the system to allow the same number for the same date. I was looking at constraints and triggers but can't make out what exactly i should use and how. The Insert will be initiated from ASP page on our intranet. Please help!!!

Regards

SD

See responses to your question in the Tools Forum.