Hi,
How do I change the default server collation of my SQL server ?
I would hate to do a complete reinstall :-)
Thanks in advance...
Kind regards
Soren
Default collation for what?
When you do CREATE DATABASE, default collation is the collation you have for the system databases.
This you picked when you installed SQL Server. You change it using rebuildm.exe (which scratches
everything in the system databases).
When you CREATE TABLE, default collation for the string columns is the default collation you have
for the database (see above). You can change default column for a database using ALTER Database,
which doesn't change collation for existing tables. For that you need to use ALTER TABLE ... ALTER
COLUMN.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How do I change the default server collation of my SQL server ?
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
>
|||> Default collation for what?
For my server (of course) !!
When first installing the server you are asked for a default collation which
is used when creating new databases (when you are not able to specify
perferred collation for the database you are creating) - say SAP Business
One creates it's own databases in the server, and the collation on each
alfanumeric field will be the default *server* collation, as its not
possible to specify the perferred collation for the database !!!!
I'll have a look at rebuildm.exe, thanks...
Soren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> Default collation for what?
> When you do CREATE DATABASE, default collation is the collation you have
> for the system databases. This you picked when you installed SQL Server.
> You change it using rebuildm.exe (which scratches everything in the system
> databases).
> When you CREATE TABLE, default collation for the string columns is the
> default collation you have for the database (see above). You can change
> default column for a database using ALTER Database, which doesn't change
> collation for existing tables. For that you need to use ALTER TABLE ...
> ALTER COLUMN.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>
|||Hi
As Tibor pointed out you will be able to change a collation on database
level or even column level.
Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
commands in the BOL.
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> For my server (of course) !!
> When first installing the server you are asked for a default collation
which[vbcol=seagreen]
> is used when creating new databases (when you are not able to specify
> perferred collation for the database you are creating) - say SAP Business
> One creates it's own databases in the server, and the collation on each
> alfanumeric field will be the default *server* collation, as its not
> possible to specify the perferred collation for the database !!!!
> I'll have a look at rebuildm.exe, thanks...
> Soren
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
system
>
|||Hi Uri,
Yes - I know I need to run these scripts to change collations on the
objects. But to change default collation for the server I need something
like the rebuildm.exe tool (as Tibor also pointed out)
My problem was not collation on my objects, but default collation for the
server!!
Soren
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:OqcUqlIgFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi
> As Tibor pointed out you will be able to change a collation on database
> level or even column level.
> Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
> commands in the BOL.
>
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> which
> system
>
|||Hi
You can look it up in Books On Line. Search for Collation or look under
Installing SQL Sever ->Collation Options for International Support ->
Changing Collation Settings after setup.
Regards
Steen
Soeren S. Joergensen wrote:
> Hi,
> How do I change the default server collation of my SQL server ?
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
Showing posts with label advance. Show all posts
Showing posts with label advance. Show all posts
Thursday, March 22, 2012
Change default server collation
Hi,
How do I change the default server collation of my SQL server '
I would hate to do a complete reinstall :-)
Thanks in advance...
Kind regards
SorenDefault collation for what?
When you do CREATE DATABASE, default collation is the collation you have for the system databases.
This you picked when you installed SQL Server. You change it using rebuildm.exe (which scratches
everything in the system databases).
When you CREATE TABLE, default collation for the string columns is the default collation you have
for the database (see above). You can change default column for a database using ALTER Database,
which doesn't change collation for existing tables. For that you need to use ALTER TABLE ... ALTER
COLUMN.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
>|||> Default collation for what?
For my server (of course) !!
When first installing the server you are asked for a default collation which
is used when creating new databases (when you are not able to specify
perferred collation for the database you are creating) - say SAP Business
One creates it's own databases in the server, and the collation on each
alfanumeric field will be the default *server* collation, as its not
possible to specify the perferred collation for the database !!!!
I'll have a look at rebuildm.exe, thanks...
Soren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> Default collation for what?
> When you do CREATE DATABASE, default collation is the collation you have
> for the system databases. This you picked when you installed SQL Server.
> You change it using rebuildm.exe (which scratches everything in the system
> databases).
> When you CREATE TABLE, default collation for the string columns is the
> default collation you have for the database (see above). You can change
> default column for a database using ALTER Database, which doesn't change
> collation for existing tables. For that you need to use ALTER TABLE ...
> ALTER COLUMN.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> How do I change the default server collation of my SQL server '
>> I would hate to do a complete reinstall :-)
>>
>> Thanks in advance...
>> Kind regards
>> Soren
>|||Hi
As Tibor pointed out you will be able to change a collation on database
level or even column level.
Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
commands in the BOL.
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> > Default collation for what?
> For my server (of course) !!
> When first installing the server you are asked for a default collation
which
> is used when creating new databases (when you are not able to specify
> perferred collation for the database you are creating) - say SAP Business
> One creates it's own databases in the server, and the collation on each
> alfanumeric field will be the default *server* collation, as its not
> possible to specify the perferred collation for the database !!!!
> I'll have a look at rebuildm.exe, thanks...
> Soren
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> > Default collation for what?
> >
> > When you do CREATE DATABASE, default collation is the collation you have
> > for the system databases. This you picked when you installed SQL Server.
> > You change it using rebuildm.exe (which scratches everything in the
system
> > databases).
> >
> > When you CREATE TABLE, default collation for the string columns is the
> > default collation you have for the database (see above). You can change
> > default column for a database using ALTER Database, which doesn't change
> > collation for existing tables. For that you need to use ALTER TABLE ...
> > ALTER COLUMN.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> > news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How do I change the default server collation of my SQL server '
> >>
> >> I would hate to do a complete reinstall :-)
> >>
> >>
> >> Thanks in advance...
> >>
> >> Kind regards
> >> Soren
> >>
> >
>|||Hi Uri,
Yes - I know I need to run these scripts to change collations on the
objects. But to change default collation for the server I need something
like the rebuildm.exe tool (as Tibor also pointed out)
My problem was not collation on my objects, but default collation for the
server!!
Soren
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:OqcUqlIgFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi
> As Tibor pointed out you will be able to change a collation on database
> level or even column level.
> Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
> commands in the BOL.
>
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
>> > Default collation for what?
>> For my server (of course) !!
>> When first installing the server you are asked for a default collation
> which
>> is used when creating new databases (when you are not able to specify
>> perferred collation for the database you are creating) - say SAP Business
>> One creates it's own databases in the server, and the collation on each
>> alfanumeric field will be the default *server* collation, as its not
>> possible to specify the perferred collation for the database !!!!
>> I'll have a look at rebuildm.exe, thanks...
>> Soren
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev
>> i
>> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
>> > Default collation for what?
>> >
>> > When you do CREATE DATABASE, default collation is the collation you
>> > have
>> > for the system databases. This you picked when you installed SQL
>> > Server.
>> > You change it using rebuildm.exe (which scratches everything in the
> system
>> > databases).
>> >
>> > When you CREATE TABLE, default collation for the string columns is the
>> > default collation you have for the database (see above). You can change
>> > default column for a database using ALTER Database, which doesn't
>> > change
>> > collation for existing tables. For that you need to use ALTER TABLE ...
>> > ALTER COLUMN.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> > Blog: http://solidqualitylearning.com/blogs/tibor/
>> >
>> >
>> > "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
>> > news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>> >> Hi,
>> >>
>> >> How do I change the default server collation of my SQL server '
>> >>
>> >> I would hate to do a complete reinstall :-)
>> >>
>> >>
>> >> Thanks in advance...
>> >>
>> >> Kind regards
>> >> Soren
>> >>
>> >
>>
>|||Hi
You can look it up in Books On Line. Search for Collation or look under
Installing SQL Sever ->Collation Options for International Support ->
Changing Collation Settings after setup.
Regards
Steen
Soeren S. Joergensen wrote:
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Sorensql
How do I change the default server collation of my SQL server '
I would hate to do a complete reinstall :-)
Thanks in advance...
Kind regards
SorenDefault collation for what?
When you do CREATE DATABASE, default collation is the collation you have for the system databases.
This you picked when you installed SQL Server. You change it using rebuildm.exe (which scratches
everything in the system databases).
When you CREATE TABLE, default collation for the string columns is the default collation you have
for the database (see above). You can change default column for a database using ALTER Database,
which doesn't change collation for existing tables. For that you need to use ALTER TABLE ... ALTER
COLUMN.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
>|||> Default collation for what?
For my server (of course) !!
When first installing the server you are asked for a default collation which
is used when creating new databases (when you are not able to specify
perferred collation for the database you are creating) - say SAP Business
One creates it's own databases in the server, and the collation on each
alfanumeric field will be the default *server* collation, as its not
possible to specify the perferred collation for the database !!!!
I'll have a look at rebuildm.exe, thanks...
Soren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> Default collation for what?
> When you do CREATE DATABASE, default collation is the collation you have
> for the system databases. This you picked when you installed SQL Server.
> You change it using rebuildm.exe (which scratches everything in the system
> databases).
> When you CREATE TABLE, default collation for the string columns is the
> default collation you have for the database (see above). You can change
> default column for a database using ALTER Database, which doesn't change
> collation for existing tables. For that you need to use ALTER TABLE ...
> ALTER COLUMN.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> How do I change the default server collation of my SQL server '
>> I would hate to do a complete reinstall :-)
>>
>> Thanks in advance...
>> Kind regards
>> Soren
>|||Hi
As Tibor pointed out you will be able to change a collation on database
level or even column level.
Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
commands in the BOL.
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> > Default collation for what?
> For my server (of course) !!
> When first installing the server you are asked for a default collation
which
> is used when creating new databases (when you are not able to specify
> perferred collation for the database you are creating) - say SAP Business
> One creates it's own databases in the server, and the collation on each
> alfanumeric field will be the default *server* collation, as its not
> possible to specify the perferred collation for the database !!!!
> I'll have a look at rebuildm.exe, thanks...
> Soren
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> > Default collation for what?
> >
> > When you do CREATE DATABASE, default collation is the collation you have
> > for the system databases. This you picked when you installed SQL Server.
> > You change it using rebuildm.exe (which scratches everything in the
system
> > databases).
> >
> > When you CREATE TABLE, default collation for the string columns is the
> > default collation you have for the database (see above). You can change
> > default column for a database using ALTER Database, which doesn't change
> > collation for existing tables. For that you need to use ALTER TABLE ...
> > ALTER COLUMN.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> > news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How do I change the default server collation of my SQL server '
> >>
> >> I would hate to do a complete reinstall :-)
> >>
> >>
> >> Thanks in advance...
> >>
> >> Kind regards
> >> Soren
> >>
> >
>|||Hi Uri,
Yes - I know I need to run these scripts to change collations on the
objects. But to change default collation for the server I need something
like the rebuildm.exe tool (as Tibor also pointed out)
My problem was not collation on my objects, but default collation for the
server!!
Soren
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:OqcUqlIgFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi
> As Tibor pointed out you will be able to change a collation on database
> level or even column level.
> Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
> commands in the BOL.
>
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
>> > Default collation for what?
>> For my server (of course) !!
>> When first installing the server you are asked for a default collation
> which
>> is used when creating new databases (when you are not able to specify
>> perferred collation for the database you are creating) - say SAP Business
>> One creates it's own databases in the server, and the collation on each
>> alfanumeric field will be the default *server* collation, as its not
>> possible to specify the perferred collation for the database !!!!
>> I'll have a look at rebuildm.exe, thanks...
>> Soren
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev
>> i
>> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
>> > Default collation for what?
>> >
>> > When you do CREATE DATABASE, default collation is the collation you
>> > have
>> > for the system databases. This you picked when you installed SQL
>> > Server.
>> > You change it using rebuildm.exe (which scratches everything in the
> system
>> > databases).
>> >
>> > When you CREATE TABLE, default collation for the string columns is the
>> > default collation you have for the database (see above). You can change
>> > default column for a database using ALTER Database, which doesn't
>> > change
>> > collation for existing tables. For that you need to use ALTER TABLE ...
>> > ALTER COLUMN.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> > Blog: http://solidqualitylearning.com/blogs/tibor/
>> >
>> >
>> > "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
>> > news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>> >> Hi,
>> >>
>> >> How do I change the default server collation of my SQL server '
>> >>
>> >> I would hate to do a complete reinstall :-)
>> >>
>> >>
>> >> Thanks in advance...
>> >>
>> >> Kind regards
>> >> Soren
>> >>
>> >
>>
>|||Hi
You can look it up in Books On Line. Search for Collation or look under
Installing SQL Sever ->Collation Options for International Support ->
Changing Collation Settings after setup.
Regards
Steen
Soeren S. Joergensen wrote:
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Sorensql
Change default server collation
Hi,
How do I change the default server collation of my SQL server '
I would hate to do a complete reinstall :-)
Thanks in advance...
Kind regards
SorenDefault collation for what?
When you do CREATE DATABASE, default collation is the collation you have for
the system databases.
This you picked when you installed SQL Server. You change it using rebuildm.
exe (which scratches
everything in the system databases).
When you CREATE TABLE, default collation for the string columns is the defau
lt collation you have
for the database (see above). You can change default column for a database u
sing ALTER Database,
which doesn't change collation for existing tables. For that you need to use
ALTER TABLE ... ALTER
COLUMN.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
>|||> Default collation for what?
For my server (of course) !!
When first installing the server you are asked for a default collation which
is used when creating new databases (when you are not able to specify
perferred collation for the database you are creating) - say SAP Business
One creates it's own databases in the server, and the collation on each
alfanumeric field will be the default *server* collation, as its not
possible to specify the perferred collation for the database !!!!
I'll have a look at rebuildm.exe, thanks...
Soren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> Default collation for what?
> When you do CREATE DATABASE, default collation is the collation you have
> for the system databases. This you picked when you installed SQL Server.
> You change it using rebuildm.exe (which scratches everything in the system
> databases).
> When you CREATE TABLE, default collation for the string columns is the
> default collation you have for the database (see above). You can change
> default column for a database using ALTER Database, which doesn't change
> collation for existing tables. For that you need to use ALTER TABLE ...
> ALTER COLUMN.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>|||Hi
As Tibor pointed out you will be able to change a collation on database
level or even column level.
Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
commands in the BOL.
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> For my server (of course) !!
> When first installing the server you are asked for a default collation
which
> is used when creating new databases (when you are not able to specify
> perferred collation for the database you are creating) - say SAP Business
> One creates it's own databases in the server, and the collation on each
> alfanumeric field will be the default *server* collation, as its not
> possible to specify the perferred collation for the database !!!!
> I'll have a look at rebuildm.exe, thanks...
> Soren
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
system[vbcol=seagreen]
>|||Hi Uri,
Yes - I know I need to run these scripts to change collations on the
objects. But to change default collation for the server I need something
like the rebuildm.exe tool (as Tibor also pointed out)
My problem was not collation on my objects, but default collation for the
server!!
Soren
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:OqcUqlIgFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi
> As Tibor pointed out you will be able to change a collation on database
> level or even column level.
> Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
> commands in the BOL.
>
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> which
> system
>|||Hi
You can look it up in Books On Line. Search for Collation or look under
Installing SQL Sever ->Collation Options for International Support ->
Changing Collation Settings after setup.
Regards
Steen
Soeren S. Joergensen wrote:
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
How do I change the default server collation of my SQL server '
I would hate to do a complete reinstall :-)
Thanks in advance...
Kind regards
SorenDefault collation for what?
When you do CREATE DATABASE, default collation is the collation you have for
the system databases.
This you picked when you installed SQL Server. You change it using rebuildm.
exe (which scratches
everything in the system databases).
When you CREATE TABLE, default collation for the string columns is the defau
lt collation you have
for the database (see above). You can change default column for a database u
sing ALTER Database,
which doesn't change collation for existing tables. For that you need to use
ALTER TABLE ... ALTER
COLUMN.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
>|||> Default collation for what?
For my server (of course) !!
When first installing the server you are asked for a default collation which
is used when creating new databases (when you are not able to specify
perferred collation for the database you are creating) - say SAP Business
One creates it's own databases in the server, and the collation on each
alfanumeric field will be the default *server* collation, as its not
possible to specify the perferred collation for the database !!!!
I'll have a look at rebuildm.exe, thanks...
Soren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
> Default collation for what?
> When you do CREATE DATABASE, default collation is the collation you have
> for the system databases. This you picked when you installed SQL Server.
> You change it using rebuildm.exe (which scratches everything in the system
> databases).
> When you CREATE TABLE, default collation for the string columns is the
> default collation you have for the database (see above). You can change
> default column for a database using ALTER Database, which doesn't change
> collation for existing tables. For that you need to use ALTER TABLE ...
> ALTER COLUMN.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:e7O%237RIgFHA.2548@.TK2MSFTNGP10.phx.gbl...
>|||Hi
As Tibor pointed out you will be able to change a collation on database
level or even column level.
Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
commands in the BOL.
"Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> For my server (of course) !!
> When first installing the server you are asked for a default collation
which
> is used when creating new databases (when you are not able to specify
> perferred collation for the database you are creating) - say SAP Business
> One creates it's own databases in the server, and the collation on each
> alfanumeric field will be the default *server* collation, as its not
> possible to specify the perferred collation for the database !!!!
> I'll have a look at rebuildm.exe, thanks...
> Soren
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
> en meddelelse news:OWjQUaIgFHA.3912@.tk2msftngp13.phx.gbl...
system[vbcol=seagreen]
>|||Hi Uri,
Yes - I know I need to run these scripts to change collations on the
objects. But to change default collation for the server I need something
like the rebuildm.exe tool (as Tibor also pointed out)
My problem was not collation on my objects, but default collation for the
server!!
Soren
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:OqcUqlIgFHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi
> As Tibor pointed out you will be able to change a collation on database
> level or even column level.
> Please take a look at ALTER DATABASE ... and ALTER TABLE ...ALTER COLUMN
> commands in the BOL.
>
>
> "Soeren S. Joergensen" <nospam@.nodomain.com> wrote in message
> news:%23U5kohIgFHA.3940@.tk2msftngp13.phx.gbl...
> which
> system
>|||Hi
You can look it up in Books On Line. Search for Collation or look under
Installing SQL Sever ->Collation Options for International Support ->
Changing Collation Settings after setup.
Regards
Steen
Soeren S. Joergensen wrote:
> Hi,
> How do I change the default server collation of my SQL server '
> I would hate to do a complete reinstall :-)
>
> Thanks in advance...
> Kind regards
> Soren
Change Default Font "Arial" to "Gotik"?
Hello,
is it possible to change the default font "Arial" to an other font (for example "Gotik")?
where can i do this?
Thanks in advance,
RalphOn Oct 1, 7:36 am, "Ralph H=FCttenmoser" <spam_no_s...@.bluewin.ch>
wrote:
> Hello,
> is it possible to change the default font "Arial" to an other font (for e=xample "Gotik")?
> where can i do this?
> Thanks in advance,
> Ralph
If I understand you correctly, you can either set the fonts via
selecting the Properties Window (F4) once selecting a cell/etc. Then
select Font and Font Family. -or- You can select the Layout tab in
BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
Environment -> Fonts and Color -> then Font. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi EMartinez,
The default font for designing reports in the layout is set to Tahoma. This
font should be changed to different font family. So that all the text of
report header, column headers, data text will be shown in the selected font
by default. This can not be achieved by following the steps specified below.
Is there any way to achieve it?
Thanks!
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191381078.124018.119610@.k79g2000hse.googlegroups.com...
On Oct 1, 7:36 am, "Ralph Hüttenmoser" <spam_no_s...@.bluewin.ch>
wrote:
> Hello,
> is it possible to change the default font "Arial" to an other font (for
> example "Gotik")?
> where can i do this?
> Thanks in advance,
> Ralph
If I understand you correctly, you can either set the fonts via
selecting the Properties Window (F4) once selecting a cell/etc. Then
select Font and Font Family. -or- You can select the Layout tab in
BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
Environment -> Fonts and Color -> then Font. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Oct 4, 7:09 am, "Venky Dosap" <ven...@.translogicsys.com> wrote:
> Hi EMartinez,
> The default font for designing reports in the layout is set to Tahoma. Th=is
> font should be changed to different font family. So that all the text of
> report header, column headers, data text will be shown in the selected fo=nt
> by default. This can not be achieved by following the steps specified bel=ow.
> Is there any way to achieve it?
> Thanks!
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1191381078.124018.119610@.k79g2000hse.googlegroups.com...
> On Oct 1, 7:36 am, "Ralph H=FCttenmoser" <spam_no_s...@.bluewin.ch>
> wrote:
> > Hello,
> > is it possible to change the default font "Arial" to an other font (for
> > example "Gotik")?
> > where can i do this?
> > Thanks in advance,
> > Ralph
> If I understand you correctly, you can either set the fonts via
> selecting the Properties Window (F4) once selecting a cell/etc. Then
> select Font and Font Family. -or- You can select the Layout tab in
> BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
> Environment -> Fonts and Color -> then Font. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
As far as I know, controlling the default font is not feasible. Sorry
that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
is it possible to change the default font "Arial" to an other font (for example "Gotik")?
where can i do this?
Thanks in advance,
RalphOn Oct 1, 7:36 am, "Ralph H=FCttenmoser" <spam_no_s...@.bluewin.ch>
wrote:
> Hello,
> is it possible to change the default font "Arial" to an other font (for e=xample "Gotik")?
> where can i do this?
> Thanks in advance,
> Ralph
If I understand you correctly, you can either set the fonts via
selecting the Properties Window (F4) once selecting a cell/etc. Then
select Font and Font Family. -or- You can select the Layout tab in
BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
Environment -> Fonts and Color -> then Font. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi EMartinez,
The default font for designing reports in the layout is set to Tahoma. This
font should be changed to different font family. So that all the text of
report header, column headers, data text will be shown in the selected font
by default. This can not be achieved by following the steps specified below.
Is there any way to achieve it?
Thanks!
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1191381078.124018.119610@.k79g2000hse.googlegroups.com...
On Oct 1, 7:36 am, "Ralph Hüttenmoser" <spam_no_s...@.bluewin.ch>
wrote:
> Hello,
> is it possible to change the default font "Arial" to an other font (for
> example "Gotik")?
> where can i do this?
> Thanks in advance,
> Ralph
If I understand you correctly, you can either set the fonts via
selecting the Properties Window (F4) once selecting a cell/etc. Then
select Font and Font Family. -or- You can select the Layout tab in
BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
Environment -> Fonts and Color -> then Font. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Oct 4, 7:09 am, "Venky Dosap" <ven...@.translogicsys.com> wrote:
> Hi EMartinez,
> The default font for designing reports in the layout is set to Tahoma. Th=is
> font should be changed to different font family. So that all the text of
> report header, column headers, data text will be shown in the selected fo=nt
> by default. This can not be achieved by following the steps specified bel=ow.
> Is there any way to achieve it?
> Thanks!
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1191381078.124018.119610@.k79g2000hse.googlegroups.com...
> On Oct 1, 7:36 am, "Ralph H=FCttenmoser" <spam_no_s...@.bluewin.ch>
> wrote:
> > Hello,
> > is it possible to change the default font "Arial" to an other font (for
> > example "Gotik")?
> > where can i do this?
> > Thanks in advance,
> > Ralph
> If I understand you correctly, you can either set the fonts via
> selecting the Properties Window (F4) once selecting a cell/etc. Then
> select Font and Font Family. -or- You can select the Layout tab in
> BIDS. Then select the 'Tools' drop-down tab -> 'Options...' ->
> Environment -> Fonts and Color -> then Font. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
As far as I know, controlling the default font is not feasible. Sorry
that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
Tuesday, March 20, 2012
Change Database Password
I have a SQL database hosted on a server. This server supports SQL Sever 2000. How do I change the password to the password?
Thanks in advance,
I found out the answer. sp_password 'currentPSWD', 'newPSWD'
sqlSunday, March 11, 2012
Change color in a chart
Does anyone know how to change a chart color in reporting services?
I need a different color from colors available in the palette.
Thanks in advance,
ELEN.type into the related property box the color rather than use the pallet
eg Beside BackgroudColor there might be a [] Transparent
just type in #12ff21
ELEN wrote:
> Does anyone know how to change a chart color in reporting services?
> I need a different color from colors available in the palette.
> Thanks in advance,
> ELEN.|||You also need to make sure you have SP2 installed
I need a different color from colors available in the palette.
Thanks in advance,
ELEN.type into the related property box the color rather than use the pallet
eg Beside BackgroudColor there might be a [] Transparent
just type in #12ff21
ELEN wrote:
> Does anyone know how to change a chart color in reporting services?
> I need a different color from colors available in the palette.
> Thanks in advance,
> ELEN.|||You also need to make sure you have SP2 installed
Wednesday, March 7, 2012
Challenging Search Engine Like Stored Porcedure Needed
(DML below)
First, thank you in advance for your help. Here's one for those of you that
like a little challenge, or perhaps this is a cake walk for you (I'm jealous
).
This DB is to hold information so that our helpdesk can search it for
support information. The front end will be a Visual Basic application
calling stored procedures.
Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
document. We will Full Text Indexed both of those columns so as to enable a
granular search. I'm guessing the "freetext" t-sql command is the tool for
this job to create a search engine like stored procedure.(if there's a bette
r
way, please suggest it).
The Front End VB application will have the following interface, so the
Stored Procedure will need to accomodate the following:
1) A text box to enter the search terms which searches the
tblDocument.document and tblSystem.Document columns for words that are
entered.
2) Dropdown box: Choice between a System Document (from tblSystem), or a
support document (from tblDocument). If System is choosen, it only searches
tblSystem. If Document is choosen, it only searches tblDocument. If left
blank will search both.
3) Dropdown box: Choice of a category to search (from tblcatagories), If a
category is choosen, the stored procedure will only return those documents
meeting that criteria. if left blank will search all categories.
4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories),
If Subcategory is choosen, the stored procedure will only return those
documents meeting that criteria. if left blank will search all SUBcategorie
s.
5) Dropdown box: Ability to choose whom submitted the document. If
SubmitterName is choosen, the stored procedure will only return thos documen
t
OR systems meeting that criteria. If left blank will search all systems AND
documents from all submitters.
The Stored Procedure will need to return results that look like this:
(if search terms hit a document) DocumentID, Title, SubmitterName
(if search terms hit a system) SystemID, SystemName, SubmitterName
Thanks again
Jeff
CREATE DATABASE [eDOC] ON (NAME = N'eDOC_Data', FILENAME =
N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME =
N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGROWT
H
= 10%)
GO
exec sp_dboption N'eDOC', N'autoclose', N'false'
GO
exec sp_dboption N'eDOC', N'bulkcopy', N'false'
GO
exec sp_dboption N'eDOC', N'trunc. log', N'false'
GO
exec sp_dboption N'eDOC', N'torn page detection', N'true'
GO
exec sp_dboption N'eDOC', N'read only', N'false'
GO
exec sp_dboption N'eDOC', N'dbo use', N'false'
GO
exec sp_dboption N'eDOC', N'single', N'false'
GO
exec sp_dboption N'eDOC', N'autoshrink', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI null default', N'false'
GO
exec sp_dboption N'eDOC', N'recursive triggers', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
GO
exec sp_dboption N'eDOC', N'concat null yields null', N'false'
GO
exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
GO
exec sp_dboption N'eDOC', N'default to local cursor', N'false'
GO
exec sp_dboption N'eDOC', N'quoted identifier', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
GO
exec sp_dboption N'eDOC', N'auto create statistics', N'true'
GO
exec sp_dboption N'eDOC', N'auto update statistics', N'true'
GO
use [eDOC]
GO
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'
GO
CREATE TABLE [dbo].[tblCategories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [char] (100) ,
[CreattionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblDocument] (
[DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[SubCategoryID] [int] NOT NULL ,
[Title] [char] (100) ,
[KeyWords] [char] (100) ,
[SubmitterID] [int] NOT NULL ,
[CreationDate] [datetime] NULL ,
[RevisedBy] [int] NOT NULL ,
[RevisedDate] [datetime] NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSubCategories] (
[SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[SubcategoryName] [char] (100) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSubmitter] (
[SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
[SubmitterName] [char] (40) NOT NULL ,
[Username] [varchar] (20) NULL ,
[Password] [varchar] (20) NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSystem] (
[SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
[SubmitterID] [int] NOT NULL ,
[SystemName] [char] (100) NOT NULL ,
[RevisedDate] [datetime] NOT NULL ,
[RevisedBy] [char] (100) NOT NULL ,
[Document] [text] NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
(
[DocumentID]
) ON [PRIMARY]
GO
USE edoc
EXEC sp_fulltext_database 'enable'
go
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'
GO
exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
N'DocumentFullText', N'PK_tblDocument'
GO
exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033
GO
exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate'
GO
ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoyID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
(
[SubmitterID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
(
[SystemID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
[CreattionDate]
GO
ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [dbo].[tblDocument] ADD
CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[tblCategories] (
[CategoryID]
),
CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
(
[SubCategoryID]
) REFERENCES [dbo].[tblSubCategories] (
[SubCategoyID]
),
CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
(
[SubmitterID]
) REFERENCES [dbo].[tblSubmitter] (
[SubmitterID]
)
GO
ALTER TABLE [dbo].[tblSubCategories] ADD
CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[tblCategories] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[tblSystem] ADD
CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
(
[SubmitterID]
) REFERENCES [dbo].[tblSubmitter] (
[SubmitterID]
)
GO
use edoc
go
INSERT into tblcategories(CategoryName) VALUES ('hardware')
insert into tblcategories(CategoryName) values ('Software')
insert into tblsubcategories(categoryID,subcategoryn
ame) values ('1','Dell
Server')
insert into tblsubcategories(categoryID,subcategoryn
ame) values
('2','Outlook')
insert into tblsubmitter(submittername, username, [password]) values ('Joe
Williams', 'WilliamsJ', 'password')
insert into tblsubmitter(submittername, username, [password]) values ('Mike
Smith', 'SmithM', 'password')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('1', 'SQL Server', '2', 'Long HTML document')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('2', 'Exchange', '1', 'Very Long HTML document')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('1', '2', 'Outlook Crashes',
'run32.dll abend', '1', '2', 'Outlook document goes here')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('2', '1', 'Dell Post Error 151',
'0x8765432', '2', '1', 'Dell document goes here')
goOn Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:
>(DML below)
Hi Jeff,
Thanks for supplying DDL and sample data!
>Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
>document.
Prefixing table names with "tbl" is considered a bad habit in the world
of relational databases. I'd rename these tables "Systems" and
"Documents" (since they store information about more than one system and
more than one document).
> We will Full Text Indexed both of those columns so as to enable a
>granular search. I'm guessing the "freetext" t-sql command is the tool for
>this job to create a search engine like stored procedure.(if there's a bett
er
>way, please suggest it).
I have no experience with full text indexing. From the sound of it, it's
probably the tool for the job, though.
Note that there is a special group where the full text experts prefer to
hang their hats: microsoft.public.sqlserver.fulltext.
>The Front End VB application will have the following interface, so the
>Stored Procedure will need to accomodate the following:
>1) A text box to enter the search terms which searches the
>tblDocument.document and tblSystem.Document columns for words that are
>entered.
That would be a job for full text search, I guess.
>2) Dropdown box: Choice between a System Document (from tblSystem), or a
>support document (from tblDocument). If System is choosen, it only searche
s
>tblSystem. If Document is choosen, it only searches tblDocument. If left
>blank will search both.
My choice would be to have two stored procedures. One searches the
Systems table, the other searches the Documents table. The front end
uses this dropdown box to decide whether to call the first, the second,
ot both and merge the results in the front end. (The added advantage is
that your front end has no trouble figuring out if a returned row refers
to a system or to a document).
>3) Dropdown box: Choice of a category to search (from tblcatagories), If a
>category is choosen, the stored procedure will only return those documents
>meeting that criteria. if left blank will search all categories.
>4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories)
,
>If Subcategory is choosen, the stored procedure will only return those
>documents meeting that criteria. if left blank will search all SUBcategori
es.
>5) Dropdown box: Ability to choose whom submitted the document. If
>SubmitterName is choosen, the stored procedure will only return thos docume
nt
>OR systems meeting that criteria. If left blank will search all systems AN
D
>documents from all submitters.
For all these three requirements: www.sommarskog.se/dyn-search.html.
(big snip)
>CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
>GO
* If not all categories have names with a length of >90 characters,
change the datatype to varchar(100).
* An identity can never be the only key of a table. You'll need a
natural key as well, to prevent duplicates. Assuming that no two
categories share the same name, add a UNIQUE constraint on CategoryName.
* Oh, and fix the typo in CreattionDate before deploying your system. It
is still an easy fix now.
* Why is there no NOT NULL constraint for CategoryName?
>CREATE TABLE [dbo].[tblDocument] (
> [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubCategoryID] [int] NOT NULL ,
> [Title] [char] (100) ,
> [KeyWords] [char] (100) ,
> [SubmitterID] [int] NOT NULL ,
> [CreationDate] [datetime] NULL ,
> [RevisedBy] [int] NOT NULL ,
> [RevisedDate] [datetime] NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblDocument] ADD
> CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
> (
> [SubCategoryID]
> ) REFERENCES [dbo].[tblSubCategories] (
> [SubCategoyID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* Change char to varchar in this table as well (and in the remainging
tables of course - I won't repeat this advice)
* Since there are no tables that refer to this table, there's no need
for a surrogate key. You should remove the identity column.
* I don't know your data well enough to propose what set of columns make
up the business key in your business, but I'm sure you do. Maybe just
"Title"? Maybe the combination of Category, Subcategory and Title?
* If subcategory S1 belongs to Category C1, is it then possible for a
document to belong to subcategory S1, but to category C2? If not, then
the column CategoryID should not be in this table at all, unless you
have compelling reasons (probably performance-driven) to use a partly
denormalized design - and in that case, you should probably add some
extra constraints to make sure that you'll never get any data like the
example I gave. (Suggestion: use a foreign key on (Category,
Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
the Subcategories table for that)
* Why is there no NOT NULL constraint for Title, Keywords, and Document?
* Why is there a NOT NULL constraint for RevisedBy, but none for
RevisedDate? I think RevisedBy shouyld be NULLable as well?
* Why is there no foreign key constraint for RevisedBy?
>CREATE TABLE [dbo].[tblSubCategories] (
> [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubcategoryName] [char] (100) NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
>GO
* Assuming that two subcategories of the same category can't have the
same name, but two subcategories of different categories can, the real
key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
duplicates.
>CREATE TABLE [dbo].[tblSubmitter] (
> [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SubmitterName] [char] (40) NOT NULL ,
> [Username] [varchar] (20) NULL ,
> [Password] [varchar] (20) NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
>GO
* Storing passwords in a table is not really safe.
* The UNIQUE constraint should go on SubmitterName, I guess.
* Why is there no NOT NULL constraint for Username and Password?
>CREATE TABLE [dbo].[tblSystem] (
> [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
> [SubmitterID] [int] NOT NULL ,
> [SystemName] [char] (100) NOT NULL ,
> [RevisedDate] [datetime] NOT NULL ,
> [RevisedBy] [char] (100) NOT NULL ,
> [Document] [text] NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* Most of the comments that relate to the Documents table relate to this
table as well. I won;t repeat them here.
* Why the non-standard seed and increment for the IDENTITY property? Is
this table part of some distributed replication setup?
* Why did you define RevisedBy as int in the Documents table, but as
char(100) in this table? Having two columns with the sme name but with
different datatypes (and different contents) will cause you a wealthy
supply of bugs!
Best, Hugo
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I really appreciate your input. In short to your comments, some were errors
,
some are on my to-do list, like adding the constraints, some are due to the
evoluation of the DB and we need to go back and fix, and the rest are things
that I aparently need to further learn.
And I especially appreciate the advice on the best way to handle the stored
procedure which I where I'm stuck since DBs are not my strong point as you
can tell. I'm not a programmer, I'm a newtorking guy that only dabbles in
this.
But where I'm really at a loss is the stored procedures themselves. So how
can I turn the below basic procedure into one that will accept the various
options and return results. Obviously, as it stands now, I can't leave a
variable blank...
@.searchterm must have something entere in it
@.Category must accept either a 1, 2, or be left blank to return both 1 & 2.
@.Subcategory same as above
@.Submitter same as above
Thanks again in advance for all your help!
CREATE PROCEDURE SearchDocument
(
@.SearchTerm char (200),
@.Category int,
@.Subcategory int,
@.Submitter int
)
as
SELECT dbo.tblDocument.DocumentID,
dbo.tblDocument.Title,
dbo.tblSubmitter.SubmitterName
FROM dbo.tblDocument join dbo.tblSubmitter
on (tbldocument.submitterid = tblsubmitter.submitterid)
where freetext (document, @.searchterm) and
dbo.tblDocument.SubCategoryID = @.subcategory AND
dbo.tblDocument.CategoryID = @.category AND
dbo.tblDocument.SubmitterID = @.submitter
"Hugo Kornelis" wrote:
> On Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:
>
> Hi Jeff,
> Thanks for supplying DDL and sample data!
>
> Prefixing table names with "tbl" is considered a bad habit in the world
> of relational databases. I'd rename these tables "Systems" and
> "Documents" (since they store information about more than one system and
> more than one document).
>
> I have no experience with full text indexing. From the sound of it, it's
> probably the tool for the job, though.
> Note that there is a special group where the full text experts prefer to
> hang their hats: microsoft.public.sqlserver.fulltext.
>
> That would be a job for full text search, I guess.
>
> My choice would be to have two stored procedures. One searches the
> Systems table, the other searches the Documents table. The front end
> uses this dropdown box to decide whether to call the first, the second,
> ot both and merge the results in the front end. (The added advantage is
> that your front end has no trouble figuring out if a returned row refers
> to a system or to a document).
>
> For all these three requirements: www.sommarskog.se/dyn-search.html.
> (big snip)
> (...)
> * If not all categories have names with a length of >90 characters,
> change the datatype to varchar(100).
> * An identity can never be the only key of a table. You'll need a
> natural key as well, to prevent duplicates. Assuming that no two
> categories share the same name, add a UNIQUE constraint on CategoryName.
> * Oh, and fix the typo in CreattionDate before deploying your system. It
> is still an easy fix now.
> * Why is there no NOT NULL constraint for CategoryName?
>
> (...)
> (...)
> * Change char to varchar in this table as well (and in the remainging
> tables of course - I won't repeat this advice)
> * Since there are no tables that refer to this table, there's no need
> for a surrogate key. You should remove the identity column.
> * I don't know your data well enough to propose what set of columns make
> up the business key in your business, but I'm sure you do. Maybe just
> "Title"? Maybe the combination of Category, Subcategory and Title?
> * If subcategory S1 belongs to Category C1, is it then possible for a
> document to belong to subcategory S1, but to category C2? If not, then
> the column CategoryID should not be in this table at all, unless you
> have compelling reasons (probably performance-driven) to use a partly
> denormalized design - and in that case, you should probably add some
> extra constraints to make sure that you'll never get any data like the
> example I gave. (Suggestion: use a foreign key on (Category,
> Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
> the Subcategories table for that)
> * Why is there no NOT NULL constraint for Title, Keywords, and Document?
> * Why is there a NOT NULL constraint for RevisedBy, but none for
> RevisedDate? I think RevisedBy shouyld be NULLable as well?
> * Why is there no foreign key constraint for RevisedBy?
>
> (...)
> (...)
> * Assuming that two subcategories of the same category can't have the
> same name, but two subcategories of different categories can, the real
> key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
> duplicates.
>
> (...)
> * Storing passwords in a table is not really safe.
> * The UNIQUE constraint should go on SubmitterName, I guess.
> * Why is there no NOT NULL constraint for Username and Password?
>
> (...)
> (...)
> * Most of the comments that relate to the Documents table relate to this
> table as well. I won;t repeat them here.
> * Why the non-standard seed and increment for the IDENTITY property? Is
> this table part of some distributed replication setup?
> * Why did you define RevisedBy as int in the Documents table, but as
> char(100) in this table? Having two columns with the sme name but with
> different datatypes (and different contents) will cause you a wealthy
> supply of bugs!
> Best, Hugo
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Jeff (Jeff@.discussions.microsoft.com) writes:
> But where I'm really at a loss is the stored procedures themselves. So
> how can I turn the below basic procedure into one that will accept the
> various options and return results. Obviously, as it stands now, I
> can't leave a variable blank...
> @.searchterm must have something entere in it
> @.Category must accept either a 1, 2, or be left blank to return both 1 &
> 2.
> @.Subcategory same as above
> @.Submitter same as above
You cannot leave it blank, but you can set it to NULL, and you can even make
that a default value:
@.SearchTerm char (200),
@.Category int = NULL,
@.Subcategory int = NULL,
@.Submitter int = ULL
> where freetext (document, @.searchterm) and
> dbo.tblDocument.SubCategoryID = @.subcategory AND
> dbo.tblDocument.CategoryID = @.category AND
> dbo.tblDocument.SubmitterID = @.submitter
Then you can say
AND (dbo.tblDocument.SubCategoryID = @.subcategory OR @.subcategory IS NULL
Note that if any of the columns are indexed, those indexes will not be
used. If you want an index on, say, CategoryID to be used, you will
need to use IF statements that leads to different SELECT statements,
or use dynamic SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh yes! I remember that trick of setting the variables to null now...
Thanks!!
"Jeff" wrote:
> (DML below)
> First, thank you in advance for your help. Here's one for those of you th
at
> like a little challenge, or perhaps this is a cake walk for you (I'm jealo
us).
> This DB is to hold information so that our helpdesk can search it for
> support information. The front end will be a Visual Basic application
> calling stored procedures.
> Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
> document. We will Full Text Indexed both of those columns so as to enable
a
> granular search. I'm guessing the "freetext" t-sql command is the tool fo
r
> this job to create a search engine like stored procedure.(if there's a bet
ter
> way, please suggest it).
> The Front End VB application will have the following interface, so the
> Stored Procedure will need to accomodate the following:
> 1) A text box to enter the search terms which searches the
> tblDocument.document and tblSystem.Document columns for words that are
> entered.
> 2) Dropdown box: Choice between a System Document (from tblSystem), or a
> support document (from tblDocument). If System is choosen, it only search
es
> tblSystem. If Document is choosen, it only searches tblDocument. If left
> blank will search both.
> 3) Dropdown box: Choice of a category to search (from tblcatagories), If
a
> category is choosen, the stored procedure will only return those documents
> meeting that criteria. if left blank will search all categories.
> 4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories
),
> If Subcategory is choosen, the stored procedure will only return those
> documents meeting that criteria. if left blank will search all SUBcategor
ies.
> 5) Dropdown box: Ability to choose whom submitted the document. If
> SubmitterName is choosen, the stored procedure will only return thos docum
ent
> OR systems meeting that criteria. If left blank will search all systems A
ND
> documents from all submitters.
>
> The Stored Procedure will need to return results that look like this:
> (if search terms hit a document) DocumentID, Title, SubmitterName
> (if search terms hit a system) SystemID, SystemName, SubmitterName
>
> Thanks again
> Jeff
>
> CREATE DATABASE [eDOC] ON (NAME = N'eDOC_Data', FILENAME =
> N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME
=
> N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGRO
WTH
> = 10%)
> GO
> exec sp_dboption N'eDOC', N'autoclose', N'false'
> GO
> exec sp_dboption N'eDOC', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'eDOC', N'trunc. log', N'false'
> GO
> exec sp_dboption N'eDOC', N'torn page detection', N'true'
> GO
> exec sp_dboption N'eDOC', N'read only', N'false'
> GO
> exec sp_dboption N'eDOC', N'dbo use', N'false'
> GO
> exec sp_dboption N'eDOC', N'single', N'false'
> GO
> exec sp_dboption N'eDOC', N'autoshrink', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'eDOC', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'eDOC', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'eDOC', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'eDOC', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'eDOC', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'eDOC', N'auto update statistics', N'true'
> GO
> use [eDOC]
> GO
> if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> exec sp_fulltext_database N'enable'
> GO
> if not exists (select * from dbo.sysfulltextcatalogs where name =
> N'DocumentFullText')
> exec sp_fulltext_catalog N'DocumentFullText', N'create'
> GO
> CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblDocument] (
> [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubCategoryID] [int] NOT NULL ,
> [Title] [char] (100) ,
> [KeyWords] [char] (100) ,
> [SubmitterID] [int] NOT NULL ,
> [CreationDate] [datetime] NULL ,
> [RevisedBy] [int] NOT NULL ,
> [RevisedDate] [datetime] NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSubCategories] (
> [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubcategoryName] [char] (100) NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSubmitter] (
> [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SubmitterName] [char] (40) NOT NULL ,
> [Username] [varchar] (20) NULL ,
> [Password] [varchar] (20) NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSystem] (
> [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
> [SubmitterID] [int] NOT NULL ,
> [SystemName] [char] (100) NOT NULL ,
> [RevisedDate] [datetime] NOT NULL ,
> [RevisedBy] [char] (100) NOT NULL ,
> [Document] [text] NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
> GO
>
> USE edoc
> EXEC sp_fulltext_database 'enable'
> go
> if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> exec sp_fulltext_database N'enable'
> GO
> if not exists (select * from dbo.sysfulltextcatalogs where name =
> N'DocumentFullText')
> exec sp_fulltext_catalog N'DocumentFullText', N'create'
> GO
> exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
> N'DocumentFullText', N'PK_tblDocument'
> GO
> exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033
> GO
> exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate'
> GO
> ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
> [CreattionDate]
> GO
> ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
> CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
> GO
> ALTER TABLE [dbo].[tblDocument] ADD
> CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
> (
> [SubCategoryID]
> ) REFERENCES [dbo].[tblSubCategories] (
> [SubCategoyID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
> GO
> ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
> GO
> ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )|||Jeff,
> But where I'm really at a loss is the stored procedures themselves...
So, the column document "freetext (document, @.searchterm)" is the only
column that is FT-enabled. Correct?
Could you provide more details on the SQL Server version (@.@.version) as well
as the OS platform, and language of text in the document column? As all of
these variables are important for functionality as is the number of rows in
your FT-enabled table.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:BDC416B4-331A-46C4-A6C3-85F9844DC90C@.microsoft.com...
> Oh yes! I remember that trick of setting the variables to null now...
> Thanks!!
> "Jeff" wrote:
>|||On Sun, 11 Sep 2005 11:22:04 -0700, Jeff wrote:
(snip)
>But where I'm really at a loss is the stored procedures themselves. So how
>can I turn the below basic procedure into one that will accept the various
>options and return results. Obviously, as it stands now, I can't leave a
>variable blank...
>@.searchterm must have something entere in it
>@.Category must accept either a 1, 2, or be left blank to return both 1 & 2.
>@.Subcategory same as above
>@.Submitter same as above
(snip)
Hi Jeff,
I attempted to answer that question by providing a link to Erland's page
about this: http://www.sommarskog.se/dyn-search.html. Sure, it's a long
read, but (IMO) well worth the time you spend reading it. You'll find
that Erland presents many ways to achieve what you are trying to do
(searching with optional search arguments), and elaborates on the pros
and cons of all the various methods.
I see that Erland now has also posted a solution taht uses one of the
method's described on his page. I trust that this was enough to get you
going (but feel free to ask again if it's not!).
But I still recommend that you take the time to read the complete
article - as I said: time well spent!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> This DB is to hold information so that our helpdesk can search it for sup
port information. <<
Have you looked into a Help Desk package instead of writing your own?
First, thank you in advance for your help. Here's one for those of you that
like a little challenge, or perhaps this is a cake walk for you (I'm jealous
).
This DB is to hold information so that our helpdesk can search it for
support information. The front end will be a Visual Basic application
calling stored procedures.
Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
document. We will Full Text Indexed both of those columns so as to enable a
granular search. I'm guessing the "freetext" t-sql command is the tool for
this job to create a search engine like stored procedure.(if there's a bette
r
way, please suggest it).
The Front End VB application will have the following interface, so the
Stored Procedure will need to accomodate the following:
1) A text box to enter the search terms which searches the
tblDocument.document and tblSystem.Document columns for words that are
entered.
2) Dropdown box: Choice between a System Document (from tblSystem), or a
support document (from tblDocument). If System is choosen, it only searches
tblSystem. If Document is choosen, it only searches tblDocument. If left
blank will search both.
3) Dropdown box: Choice of a category to search (from tblcatagories), If a
category is choosen, the stored procedure will only return those documents
meeting that criteria. if left blank will search all categories.
4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories),
If Subcategory is choosen, the stored procedure will only return those
documents meeting that criteria. if left blank will search all SUBcategorie
s.
5) Dropdown box: Ability to choose whom submitted the document. If
SubmitterName is choosen, the stored procedure will only return thos documen
t
OR systems meeting that criteria. If left blank will search all systems AND
documents from all submitters.
The Stored Procedure will need to return results that look like this:
(if search terms hit a document) DocumentID, Title, SubmitterName
(if search terms hit a system) SystemID, SystemName, SubmitterName
Thanks again
Jeff
CREATE DATABASE [eDOC] ON (NAME = N'eDOC_Data', FILENAME =
N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME =
N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGROWT
H
= 10%)
GO
exec sp_dboption N'eDOC', N'autoclose', N'false'
GO
exec sp_dboption N'eDOC', N'bulkcopy', N'false'
GO
exec sp_dboption N'eDOC', N'trunc. log', N'false'
GO
exec sp_dboption N'eDOC', N'torn page detection', N'true'
GO
exec sp_dboption N'eDOC', N'read only', N'false'
GO
exec sp_dboption N'eDOC', N'dbo use', N'false'
GO
exec sp_dboption N'eDOC', N'single', N'false'
GO
exec sp_dboption N'eDOC', N'autoshrink', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI null default', N'false'
GO
exec sp_dboption N'eDOC', N'recursive triggers', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
GO
exec sp_dboption N'eDOC', N'concat null yields null', N'false'
GO
exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
GO
exec sp_dboption N'eDOC', N'default to local cursor', N'false'
GO
exec sp_dboption N'eDOC', N'quoted identifier', N'false'
GO
exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
GO
exec sp_dboption N'eDOC', N'auto create statistics', N'true'
GO
exec sp_dboption N'eDOC', N'auto update statistics', N'true'
GO
use [eDOC]
GO
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'
GO
CREATE TABLE [dbo].[tblCategories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [char] (100) ,
[CreattionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblDocument] (
[DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[SubCategoryID] [int] NOT NULL ,
[Title] [char] (100) ,
[KeyWords] [char] (100) ,
[SubmitterID] [int] NOT NULL ,
[CreationDate] [datetime] NULL ,
[RevisedBy] [int] NOT NULL ,
[RevisedDate] [datetime] NULL ,
[Document] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSubCategories] (
[SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NOT NULL ,
[SubcategoryName] [char] (100) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSubmitter] (
[SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
[SubmitterName] [char] (40) NOT NULL ,
[Username] [varchar] (20) NULL ,
[Password] [varchar] (20) NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSystem] (
[SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
[SubmitterID] [int] NOT NULL ,
[SystemName] [char] (100) NOT NULL ,
[RevisedDate] [datetime] NOT NULL ,
[RevisedBy] [char] (100) NOT NULL ,
[Document] [text] NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
(
[DocumentID]
) ON [PRIMARY]
GO
USE edoc
EXEC sp_fulltext_database 'enable'
go
if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'
GO
if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'
GO
exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
N'DocumentFullText', N'PK_tblDocument'
GO
exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033
GO
exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate'
GO
ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoyID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
(
[SubmitterID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
(
[SystemID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
[CreattionDate]
GO
ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO
ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO
ALTER TABLE [dbo].[tblDocument] ADD
CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[tblCategories] (
[CategoryID]
),
CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
(
[SubCategoryID]
) REFERENCES [dbo].[tblSubCategories] (
[SubCategoyID]
),
CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
(
[SubmitterID]
) REFERENCES [dbo].[tblSubmitter] (
[SubmitterID]
)
GO
ALTER TABLE [dbo].[tblSubCategories] ADD
CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
(
[CategoryID]
) REFERENCES [dbo].[tblCategories] (
[CategoryID]
)
GO
ALTER TABLE [dbo].[tblSystem] ADD
CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
(
[SubmitterID]
) REFERENCES [dbo].[tblSubmitter] (
[SubmitterID]
)
GO
use edoc
go
INSERT into tblcategories(CategoryName) VALUES ('hardware')
insert into tblcategories(CategoryName) values ('Software')
insert into tblsubcategories(categoryID,subcategoryn
ame) values ('1','Dell
Server')
insert into tblsubcategories(categoryID,subcategoryn
ame) values
('2','Outlook')
insert into tblsubmitter(submittername, username, [password]) values ('Joe
Williams', 'WilliamsJ', 'password')
insert into tblsubmitter(submittername, username, [password]) values ('Mike
Smith', 'SmithM', 'password')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('1', 'SQL Server', '2', 'Long HTML document')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('2', 'Exchange', '1', 'Very Long HTML document')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('1', '2', 'Outlook Crashes',
'run32.dll abend', '1', '2', 'Outlook document goes here')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('2', '1', 'Dell Post Error 151',
'0x8765432', '2', '1', 'Dell document goes here')
goOn Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:
>(DML below)
Hi Jeff,
Thanks for supplying DDL and sample data!
>Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
>document.
Prefixing table names with "tbl" is considered a bad habit in the world
of relational databases. I'd rename these tables "Systems" and
"Documents" (since they store information about more than one system and
more than one document).
> We will Full Text Indexed both of those columns so as to enable a
>granular search. I'm guessing the "freetext" t-sql command is the tool for
>this job to create a search engine like stored procedure.(if there's a bett
er
>way, please suggest it).
I have no experience with full text indexing. From the sound of it, it's
probably the tool for the job, though.
Note that there is a special group where the full text experts prefer to
hang their hats: microsoft.public.sqlserver.fulltext.
>The Front End VB application will have the following interface, so the
>Stored Procedure will need to accomodate the following:
>1) A text box to enter the search terms which searches the
>tblDocument.document and tblSystem.Document columns for words that are
>entered.
That would be a job for full text search, I guess.
>2) Dropdown box: Choice between a System Document (from tblSystem), or a
>support document (from tblDocument). If System is choosen, it only searche
s
>tblSystem. If Document is choosen, it only searches tblDocument. If left
>blank will search both.
My choice would be to have two stored procedures. One searches the
Systems table, the other searches the Documents table. The front end
uses this dropdown box to decide whether to call the first, the second,
ot both and merge the results in the front end. (The added advantage is
that your front end has no trouble figuring out if a returned row refers
to a system or to a document).
>3) Dropdown box: Choice of a category to search (from tblcatagories), If a
>category is choosen, the stored procedure will only return those documents
>meeting that criteria. if left blank will search all categories.
>4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories)
,
>If Subcategory is choosen, the stored procedure will only return those
>documents meeting that criteria. if left blank will search all SUBcategori
es.
>5) Dropdown box: Ability to choose whom submitted the document. If
>SubmitterName is choosen, the stored procedure will only return thos docume
nt
>OR systems meeting that criteria. If left blank will search all systems AN
D
>documents from all submitters.
For all these three requirements: www.sommarskog.se/dyn-search.html.
(big snip)
>CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
>GO
* If not all categories have names with a length of >90 characters,
change the datatype to varchar(100).
* An identity can never be the only key of a table. You'll need a
natural key as well, to prevent duplicates. Assuming that no two
categories share the same name, add a UNIQUE constraint on CategoryName.
* Oh, and fix the typo in CreattionDate before deploying your system. It
is still an easy fix now.
* Why is there no NOT NULL constraint for CategoryName?
>CREATE TABLE [dbo].[tblDocument] (
> [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubCategoryID] [int] NOT NULL ,
> [Title] [char] (100) ,
> [KeyWords] [char] (100) ,
> [SubmitterID] [int] NOT NULL ,
> [CreationDate] [datetime] NULL ,
> [RevisedBy] [int] NOT NULL ,
> [RevisedDate] [datetime] NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblDocument] ADD
> CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
> (
> [SubCategoryID]
> ) REFERENCES [dbo].[tblSubCategories] (
> [SubCategoyID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* Change char to varchar in this table as well (and in the remainging
tables of course - I won't repeat this advice)
* Since there are no tables that refer to this table, there's no need
for a surrogate key. You should remove the identity column.
* I don't know your data well enough to propose what set of columns make
up the business key in your business, but I'm sure you do. Maybe just
"Title"? Maybe the combination of Category, Subcategory and Title?
* If subcategory S1 belongs to Category C1, is it then possible for a
document to belong to subcategory S1, but to category C2? If not, then
the column CategoryID should not be in this table at all, unless you
have compelling reasons (probably performance-driven) to use a partly
denormalized design - and in that case, you should probably add some
extra constraints to make sure that you'll never get any data like the
example I gave. (Suggestion: use a foreign key on (Category,
Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
the Subcategories table for that)
* Why is there no NOT NULL constraint for Title, Keywords, and Document?
* Why is there a NOT NULL constraint for RevisedBy, but none for
RevisedDate? I think RevisedBy shouyld be NULLable as well?
* Why is there no foreign key constraint for RevisedBy?
>CREATE TABLE [dbo].[tblSubCategories] (
> [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubcategoryName] [char] (100) NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
>GO
* Assuming that two subcategories of the same category can't have the
same name, but two subcategories of different categories can, the real
key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
duplicates.
>CREATE TABLE [dbo].[tblSubmitter] (
> [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SubmitterName] [char] (40) NOT NULL ,
> [Username] [varchar] (20) NULL ,
> [Password] [varchar] (20) NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
>GO
* Storing passwords in a table is not really safe.
* The UNIQUE constraint should go on SubmitterName, I guess.
* Why is there no NOT NULL constraint for Username and Password?
>CREATE TABLE [dbo].[tblSystem] (
> [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
> [SubmitterID] [int] NOT NULL ,
> [SystemName] [char] (100) NOT NULL ,
> [RevisedDate] [datetime] NOT NULL ,
> [RevisedBy] [char] (100) NOT NULL ,
> [Document] [text] NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* Most of the comments that relate to the Documents table relate to this
table as well. I won;t repeat them here.
* Why the non-standard seed and increment for the IDENTITY property? Is
this table part of some distributed replication setup?
* Why did you define RevisedBy as int in the Documents table, but as
char(100) in this table? Having two columns with the sme name but with
different datatypes (and different contents) will cause you a wealthy
supply of bugs!
Best, Hugo
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I really appreciate your input. In short to your comments, some were errors
,
some are on my to-do list, like adding the constraints, some are due to the
evoluation of the DB and we need to go back and fix, and the rest are things
that I aparently need to further learn.
And I especially appreciate the advice on the best way to handle the stored
procedure which I where I'm stuck since DBs are not my strong point as you
can tell. I'm not a programmer, I'm a newtorking guy that only dabbles in
this.
But where I'm really at a loss is the stored procedures themselves. So how
can I turn the below basic procedure into one that will accept the various
options and return results. Obviously, as it stands now, I can't leave a
variable blank...
@.searchterm must have something entere in it
@.Category must accept either a 1, 2, or be left blank to return both 1 & 2.
@.Subcategory same as above
@.Submitter same as above
Thanks again in advance for all your help!
CREATE PROCEDURE SearchDocument
(
@.SearchTerm char (200),
@.Category int,
@.Subcategory int,
@.Submitter int
)
as
SELECT dbo.tblDocument.DocumentID,
dbo.tblDocument.Title,
dbo.tblSubmitter.SubmitterName
FROM dbo.tblDocument join dbo.tblSubmitter
on (tbldocument.submitterid = tblsubmitter.submitterid)
where freetext (document, @.searchterm) and
dbo.tblDocument.SubCategoryID = @.subcategory AND
dbo.tblDocument.CategoryID = @.category AND
dbo.tblDocument.SubmitterID = @.submitter
"Hugo Kornelis" wrote:
> On Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:
>
> Hi Jeff,
> Thanks for supplying DDL and sample data!
>
> Prefixing table names with "tbl" is considered a bad habit in the world
> of relational databases. I'd rename these tables "Systems" and
> "Documents" (since they store information about more than one system and
> more than one document).
>
> I have no experience with full text indexing. From the sound of it, it's
> probably the tool for the job, though.
> Note that there is a special group where the full text experts prefer to
> hang their hats: microsoft.public.sqlserver.fulltext.
>
> That would be a job for full text search, I guess.
>
> My choice would be to have two stored procedures. One searches the
> Systems table, the other searches the Documents table. The front end
> uses this dropdown box to decide whether to call the first, the second,
> ot both and merge the results in the front end. (The added advantage is
> that your front end has no trouble figuring out if a returned row refers
> to a system or to a document).
>
> For all these three requirements: www.sommarskog.se/dyn-search.html.
> (big snip)
> (...)
> * If not all categories have names with a length of >90 characters,
> change the datatype to varchar(100).
> * An identity can never be the only key of a table. You'll need a
> natural key as well, to prevent duplicates. Assuming that no two
> categories share the same name, add a UNIQUE constraint on CategoryName.
> * Oh, and fix the typo in CreattionDate before deploying your system. It
> is still an easy fix now.
> * Why is there no NOT NULL constraint for CategoryName?
>
> (...)
> (...)
> * Change char to varchar in this table as well (and in the remainging
> tables of course - I won't repeat this advice)
> * Since there are no tables that refer to this table, there's no need
> for a surrogate key. You should remove the identity column.
> * I don't know your data well enough to propose what set of columns make
> up the business key in your business, but I'm sure you do. Maybe just
> "Title"? Maybe the combination of Category, Subcategory and Title?
> * If subcategory S1 belongs to Category C1, is it then possible for a
> document to belong to subcategory S1, but to category C2? If not, then
> the column CategoryID should not be in this table at all, unless you
> have compelling reasons (probably performance-driven) to use a partly
> denormalized design - and in that case, you should probably add some
> extra constraints to make sure that you'll never get any data like the
> example I gave. (Suggestion: use a foreign key on (Category,
> Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
> the Subcategories table for that)
> * Why is there no NOT NULL constraint for Title, Keywords, and Document?
> * Why is there a NOT NULL constraint for RevisedBy, but none for
> RevisedDate? I think RevisedBy shouyld be NULLable as well?
> * Why is there no foreign key constraint for RevisedBy?
>
> (...)
> (...)
> * Assuming that two subcategories of the same category can't have the
> same name, but two subcategories of different categories can, the real
> key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
> duplicates.
>
> (...)
> * Storing passwords in a table is not really safe.
> * The UNIQUE constraint should go on SubmitterName, I guess.
> * Why is there no NOT NULL constraint for Username and Password?
>
> (...)
> (...)
> * Most of the comments that relate to the Documents table relate to this
> table as well. I won;t repeat them here.
> * Why the non-standard seed and increment for the IDENTITY property? Is
> this table part of some distributed replication setup?
> * Why did you define RevisedBy as int in the Documents table, but as
> char(100) in this table? Having two columns with the sme name but with
> different datatypes (and different contents) will cause you a wealthy
> supply of bugs!
> Best, Hugo
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Jeff (Jeff@.discussions.microsoft.com) writes:
> But where I'm really at a loss is the stored procedures themselves. So
> how can I turn the below basic procedure into one that will accept the
> various options and return results. Obviously, as it stands now, I
> can't leave a variable blank...
> @.searchterm must have something entere in it
> @.Category must accept either a 1, 2, or be left blank to return both 1 &
> 2.
> @.Subcategory same as above
> @.Submitter same as above
You cannot leave it blank, but you can set it to NULL, and you can even make
that a default value:
@.SearchTerm char (200),
@.Category int = NULL,
@.Subcategory int = NULL,
@.Submitter int = ULL
> where freetext (document, @.searchterm) and
> dbo.tblDocument.SubCategoryID = @.subcategory AND
> dbo.tblDocument.CategoryID = @.category AND
> dbo.tblDocument.SubmitterID = @.submitter
Then you can say
AND (dbo.tblDocument.SubCategoryID = @.subcategory OR @.subcategory IS NULL
Note that if any of the columns are indexed, those indexes will not be
used. If you want an index on, say, CategoryID to be used, you will
need to use IF statements that leads to different SELECT statements,
or use dynamic SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh yes! I remember that trick of setting the variables to null now...
Thanks!!
"Jeff" wrote:
> (DML below)
> First, thank you in advance for your help. Here's one for those of you th
at
> like a little challenge, or perhaps this is a cake walk for you (I'm jealo
us).
> This DB is to hold information so that our helpdesk can search it for
> support information. The front end will be a Visual Basic application
> calling stored procedures.
> Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
> document. We will Full Text Indexed both of those columns so as to enable
a
> granular search. I'm guessing the "freetext" t-sql command is the tool fo
r
> this job to create a search engine like stored procedure.(if there's a bet
ter
> way, please suggest it).
> The Front End VB application will have the following interface, so the
> Stored Procedure will need to accomodate the following:
> 1) A text box to enter the search terms which searches the
> tblDocument.document and tblSystem.Document columns for words that are
> entered.
> 2) Dropdown box: Choice between a System Document (from tblSystem), or a
> support document (from tblDocument). If System is choosen, it only search
es
> tblSystem. If Document is choosen, it only searches tblDocument. If left
> blank will search both.
> 3) Dropdown box: Choice of a category to search (from tblcatagories), If
a
> category is choosen, the stored procedure will only return those documents
> meeting that criteria. if left blank will search all categories.
> 4) Dropdown box: Choice of a SUBCategory to search (from tblsubcategories
),
> If Subcategory is choosen, the stored procedure will only return those
> documents meeting that criteria. if left blank will search all SUBcategor
ies.
> 5) Dropdown box: Ability to choose whom submitted the document. If
> SubmitterName is choosen, the stored procedure will only return thos docum
ent
> OR systems meeting that criteria. If left blank will search all systems A
ND
> documents from all submitters.
>
> The Stored Procedure will need to return results that look like this:
> (if search terms hit a document) DocumentID, Title, SubmitterName
> (if search terms hit a system) SystemID, SystemName, SubmitterName
>
> Thanks again
> Jeff
>
> CREATE DATABASE [eDOC] ON (NAME = N'eDOC_Data', FILENAME =
> N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME
=
> N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGRO
WTH
> = 10%)
> GO
> exec sp_dboption N'eDOC', N'autoclose', N'false'
> GO
> exec sp_dboption N'eDOC', N'bulkcopy', N'false'
> GO
> exec sp_dboption N'eDOC', N'trunc. log', N'false'
> GO
> exec sp_dboption N'eDOC', N'torn page detection', N'true'
> GO
> exec sp_dboption N'eDOC', N'read only', N'false'
> GO
> exec sp_dboption N'eDOC', N'dbo use', N'false'
> GO
> exec sp_dboption N'eDOC', N'single', N'false'
> GO
> exec sp_dboption N'eDOC', N'autoshrink', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI null default', N'false'
> GO
> exec sp_dboption N'eDOC', N'recursive triggers', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
> GO
> exec sp_dboption N'eDOC', N'concat null yields null', N'false'
> GO
> exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
> GO
> exec sp_dboption N'eDOC', N'default to local cursor', N'false'
> GO
> exec sp_dboption N'eDOC', N'quoted identifier', N'false'
> GO
> exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
> GO
> exec sp_dboption N'eDOC', N'auto create statistics', N'true'
> GO
> exec sp_dboption N'eDOC', N'auto update statistics', N'true'
> GO
> use [eDOC]
> GO
> if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> exec sp_fulltext_database N'enable'
> GO
> if not exists (select * from dbo.sysfulltextcatalogs where name =
> N'DocumentFullText')
> exec sp_fulltext_catalog N'DocumentFullText', N'create'
> GO
> CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblDocument] (
> [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubCategoryID] [int] NOT NULL ,
> [Title] [char] (100) ,
> [KeyWords] [char] (100) ,
> [SubmitterID] [int] NOT NULL ,
> [CreationDate] [datetime] NULL ,
> [RevisedBy] [int] NOT NULL ,
> [RevisedDate] [datetime] NULL ,
> [Document] [text] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSubCategories] (
> [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryID] [int] NOT NULL ,
> [SubcategoryName] [char] (100) NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSubmitter] (
> [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
> [SubmitterName] [char] (40) NOT NULL ,
> [Username] [varchar] (20) NULL ,
> [Password] [varchar] (20) NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblSystem] (
> [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
> [SubmitterID] [int] NOT NULL ,
> [SystemName] [char] (100) NOT NULL ,
> [RevisedDate] [datetime] NOT NULL ,
> [RevisedBy] [char] (100) NOT NULL ,
> [Document] [text] NOT NULL ,
> [CreationDate] [datetime] NOT NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
> GO
>
> USE edoc
> EXEC sp_fulltext_database 'enable'
> go
> if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> exec sp_fulltext_database N'enable'
> GO
> if not exists (select * from dbo.sysfulltextcatalogs where name =
> N'DocumentFullText')
> exec sp_fulltext_catalog N'DocumentFullText', N'create'
> GO
> exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
> N'DocumentFullText', N'PK_tblDocument'
> GO
> exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033
> GO
> exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate'
> GO
> ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
> [CreattionDate]
> GO
> ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
> [CreationDate]
> GO
> ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
> CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
> GO
> ALTER TABLE [dbo].[tblDocument] ADD
> CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
> (
> [SubCategoryID]
> ) REFERENCES [dbo].[tblSubCategories] (
> [SubCategoyID]
> ),
> CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
> GO
> ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
> GO
> ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )|||Jeff,
> But where I'm really at a loss is the stored procedures themselves...
So, the column document "freetext (document, @.searchterm)" is the only
column that is FT-enabled. Correct?
Could you provide more details on the SQL Server version (@.@.version) as well
as the OS platform, and language of text in the document column? As all of
these variables are important for functionality as is the number of rows in
your FT-enabled table.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:BDC416B4-331A-46C4-A6C3-85F9844DC90C@.microsoft.com...
> Oh yes! I remember that trick of setting the variables to null now...
> Thanks!!
> "Jeff" wrote:
>|||On Sun, 11 Sep 2005 11:22:04 -0700, Jeff wrote:
(snip)
>But where I'm really at a loss is the stored procedures themselves. So how
>can I turn the below basic procedure into one that will accept the various
>options and return results. Obviously, as it stands now, I can't leave a
>variable blank...
>@.searchterm must have something entere in it
>@.Category must accept either a 1, 2, or be left blank to return both 1 & 2.
>@.Subcategory same as above
>@.Submitter same as above
(snip)
Hi Jeff,
I attempted to answer that question by providing a link to Erland's page
about this: http://www.sommarskog.se/dyn-search.html. Sure, it's a long
read, but (IMO) well worth the time you spend reading it. You'll find
that Erland presents many ways to achieve what you are trying to do
(searching with optional search arguments), and elaborates on the pros
and cons of all the various methods.
I see that Erland now has also posted a solution taht uses one of the
method's described on his page. I trust that this was enough to get you
going (but feel free to ask again if it's not!).
But I still recommend that you take the time to read the complete
article - as I said: time well spent!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> This DB is to hold information so that our helpdesk can search it for sup
port information. <<
Have you looked into a Help Desk package instead of writing your own?
Subscribe to:
Posts (Atom)