Hi, We had a simple application which compared schemas on local servers. We
are updating it to compare schemas across servers. Unfortunately the query
which refers to the Information_Schema fails, and I cant find any syntax to
make it work!
Here is the simplified version of the query.
SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].INFORMATION_SCHEMA.TABLES
tried the following combinations out of frustration, but none worked.
SELECT 1 FROM [Matrix].[ReviewRecorder].[INFORMATION_SCHEMA].TABLES
SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].[INFORMATION_SCHEMA].[TABLES]
Obviously Matrix is the name of the remote Database Server, and it has been
linked already to the local Database.
The error i get is
--
The object name 'Matrix.ReviewRecorder.DBO.INFORMATION_SCHEMA.' contains
more than the maximum number of prefixes. The maximum is 3.
--
sp_linkedservers shows
--
Matrix SQLOLEDB SQL Server Matrix NULL NULL NULL
--
HELP !
Thanks
MohammedHi
Have you tried using select * from
{RemoteSvr}.{dbname}.information_schema.tables
Substitute the name in {RemoteSvr} and {dbname}
John
"MLokhandwala" wrote:
> Hi, We had a simple application which compared schemas on local servers. W
e
> are updating it to compare schemas across servers. Unfortunately the query
> which refers to the Information_Schema fails, and I cant find any syntax t
o
> make it work!
> Here is the simplified version of the query.
> SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].INFORMATION_SCHEMA.TABLES
> tried the following combinations out of frustration, but none worked.
> SELECT 1 FROM [Matrix].[ReviewRecorder].[INFORMATION_SCHEMA].TABLES
> SELECT 1 FROM [Matrix].[ReviewRecorder].[DBO].[INFORMATION_SCHEMA].[TABLES]
> Obviously Matrix is the name of the remote Database Server, and it has bee
n
> linked already to the local Database.
> The error i get is
> --
> The object name 'Matrix.ReviewRecorder.DBO.INFORMATION_SCHEMA.' contains
> more than the maximum number of prefixes. The maximum is 3.
> --
> sp_linkedservers shows
> --
> Matrix SQLOLEDB SQL Server Matrix NULL NULL NULL
> --
> HELP !
> Thanks
> Mohammed|||Yes,
I have tried all combinations, including dropping the owner name etc. but no
luck.
Any other suggestions are welcome.
Still awaiting a solution.
Mohammed
"John Bell" wrote:
> Hi
> Have you tried using select * from
> {RemoteSvr}.{dbname}.information_schema.tables
> Substitute the name in {RemoteSvr} and {dbname}
> John
> "MLokhandwala" wrote:
>|||Hi
It seems 4 part naming only works in master!!!! You could try either calling
a stored procedure in the remote database or creating a view e.g.
-- On Remote Server database run:
CREATE VIEW MyTables AS SELECT * FROM INFORMATION_SCHEMA.TABLES
-- From Local Server Access Remove server
SELECT * FROM Matrix.ReviewRecorder.dbo.MyTables
John
"MLokhandwala" wrote:
> Yes,
> I have tried all combinations, including dropping the owner name etc. but
no
> luck.
> Any other suggestions are welcome.
> Still awaiting a solution.
> Mohammed
>
> "John Bell" wrote:
>|||If you are willing to use Java, there is a free open-source tool called
SchemaCrawler on SourceForge that can compare schemas between databases
on two different servers. Download SchemaCrawler from:
http://sourceforge.net/project/show...group_id=148383
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
information on securing SQL Server
My company is going to be rolling out a .Net application soon. I have been tasked with assisting on the security side of things. Basic setup for now will be a web server (IIS) in the DMZ, Cisco pix firewall then the SQL Server databases (2000, sp3a).
Can you all point out any good documentation that would cover things such as
a. using a Cisco Pix firewall with SQL Server
b. using client protocal encryption with a SQL Server website
c. testing how secure your SQL Server website really is?
Thanks all!
frank
For b), check the following links:
http://support.microsoft.com/default.aspx?scid=kb;en-us;316898
http://blogs.msdn.com/sql_protocols/archive/2005/10/4.aspx
For c), take a look at MBSA:
http://www.microsoft.com/technet/security/tools/mbsahome.mspx
Thanks
Laurentiu
Monday, March 26, 2012
INFO Requested Distributed DB Design
I am in a Process of designing database deployment layout for a system.
There are 4 major subsystem in the application each uses its own database.
Database :
1) Primary DB
2) Processing A DB
3) Reporting DB
4) Processing B DB
The Purpose of Primary is to hold all the Input data in unmodified state.
Processing A DB is a Exact copy of Primary DB.
In Processing A DB the the computation & Validation happens .The Results (
More than one Table )of Processing A is updated in Primary DB.
This Updates needs to be transfered to Reporting DB , Processing B DB.
In Processing B DB some status column is updated and this Updates needs
to be propogated back to the Primary
I am Looking at transactional Replication as a Option for this . Let me
know your views on this
You are trying to do work flow. Replication is not an easy fit with this.
Basically with a replication solution you need a server which will act as
the clearing house figuring out what goes where. With sql2000 you do not
have this - with SQL 2005 you sort of do with the peer to peer replication
model with transactional replication.
It seems that your data flow is this
Primary -> Processing A -> Reporting
-> Processing B -> Primary
Primary <- (with filtering) Processing A
By what criteria does data flow from Processing A back to Primary and how
are you going to prevent data from Processing B flowing back to Processing
A?
If you can filter this data, you could use transactional replication from
one node to another. If you can't I don't think transactional replication
will be a good fit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:DF063537-EE93-4742-AFBB-8BBAF557CEE4@.microsoft.com...
> I am in a Process of designing database deployment layout for a system.
> There are 4 major subsystem in the application each uses its own
database.
> Database :
> 1) Primary DB
> 2) Processing A DB
> 3) Reporting DB
> 4) Processing B DB
>
> The Purpose of Primary is to hold all the Input data in unmodified
state.
> Processing A DB is a Exact copy of Primary DB.
> In Processing A DB the the computation & Validation happens .The Results (
> More than one Table )of Processing A is updated in Primary DB.
> This Updates needs to be transfered to Reporting DB , Processing B DB.
> In Processing B DB some status column is updated and this Updates needs
> to be propogated back to the Primary
> I am Looking at transactional Replication as a Option for this . Let me
> know your views on this
>
|||Thanks for your response.
Well I have a Status Column on the Primary field based on which i Push the
(Set of tables) data to the Processing A DB .
Process A DB does some computation and Updates Data & the staus field on th
Tables .
My Question is whats the Best way of Pushing this data back(from Processing
A) to the Primary server .
There are around 30 table which gets updated .
I am currently looking at DTS/ Transactional Replication to move the
tables back to the Primay DB. I feel there would be Cyclic Updates ?
any advice on this
Best Reagrds
"Hilary Cotter" wrote:
> You are trying to do work flow. Replication is not an easy fit with this.
> Basically with a replication solution you need a server which will act as
> the clearing house figuring out what goes where. With sql2000 you do not
> have this - with SQL 2005 you sort of do with the peer to peer replication
> model with transactional replication.
> It seems that your data flow is this
> Primary -> Processing A -> Reporting
> -> Processing B -> Primary
> Primary <- (with filtering) Processing A
> By what criteria does data flow from Processing A back to Primary and how
> are you going to prevent data from Processing B flowing back to Processing
> A?
> If you can filter this data, you could use transactional replication from
> one node to another. If you can't I don't think transactional replication
> will be a good fit.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:DF063537-EE93-4742-AFBB-8BBAF557CEE4@.microsoft.com...
> database.
> state.
>
>
|||I think you want to replicate these tables back, but ensure that the
replication stored procedures have logic built in them to only execute is
the status column is an update.
For instance going from primary to processing a the status column might have
a value of 0, when processed on A it is updated to 1.
In your update replication procs have a wrapper which looks like this going
back to Primary.
if StatusColumn=1 then
do work
else
return 0
In your update replication procs have a wrapper which looks like this going
to PRocessing A.
if StatusColumn=0 then
do work
else
return 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:2242A1DF-2047-4C2B-A8F6-A63835767DF5@.microsoft.com...
> Thanks for your response.
> Well I have a Status Column on the Primary field based on which i Push the
> (Set of tables) data to the Processing A DB .
> Process A DB does some computation and Updates Data & the staus field on
th
> Tables .
> My Question is whats the Best way of Pushing this data back(from
Processing[vbcol=seagreen]
> A) to the Primary server .
> There are around 30 table which gets updated .
> I am currently looking at DTS/ Transactional Replication to move the
> tables back to the Primay DB. I feel there would be Cyclic Updates ?
> any advice on this
> Best Reagrds
>
> "Hilary Cotter" wrote:
this.[vbcol=seagreen]
as[vbcol=seagreen]
replication[vbcol=seagreen]
how[vbcol=seagreen]
Processing[vbcol=seagreen]
from[vbcol=seagreen]
replication[vbcol=seagreen]
system.[vbcol=seagreen]
Results ([vbcol=seagreen]
DB.[vbcol=seagreen]
needs[vbcol=seagreen]
me[vbcol=seagreen]
|||Thanks Hilary
Well I have not yet decided on the mechanism to updating the data back to
primary
Server .
I am considering to create a Publication on the Processing A server and
let Primary server subscribe this datas ( seems to me a cyclic and bit over
head) .
Note : there will be more that 30 table updates which needs to be send
back-forth
do you have any suggesstion
"Hilary Cotter" wrote:
> I think you want to replicate these tables back, but ensure that the
> replication stored procedures have logic built in them to only execute is
> the status column is an update.
> For instance going from primary to processing a the status column might have
> a value of 0, when processed on A it is updated to 1.
> In your update replication procs have a wrapper which looks like this going
> back to Primary.
> if StatusColumn=1 then
> do work
> else
> return 0
> In your update replication procs have a wrapper which looks like this going
> to PRocessing A.
> if StatusColumn=0 then
> do work
> else
> return 0
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:2242A1DF-2047-4C2B-A8F6-A63835767DF5@.microsoft.com...
> th
> Processing
> this.
> as
> replication
> how
> Processing
> from
> replication
> system.
> Results (
> DB.
> needs
> me
>
>
|||it will be cyclic unless you can figure out some criteria to use as a basis
for not applying the transaction. Use this criteria as a basis as to whether
the proc applies the data on the subscriber/publisher or not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:127378BB-BBAB-4F77-9A10-817170920414@.microsoft.com...
> Thanks Hilary
> Well I have not yet decided on the mechanism to updating the data back to
> primary
> Server .
> I am considering to create a Publication on the Processing A server and
> let Primary server subscribe this datas ( seems to me a cyclic and bit
over[vbcol=seagreen]
> head) .
> Note : there will be more that 30 table updates which needs to be send
> back-forth
> do you have any suggesstion
>
> "Hilary Cotter" wrote:
is[vbcol=seagreen]
have[vbcol=seagreen]
going[vbcol=seagreen]
going[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
?[vbcol=seagreen]
act[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
unmodified[vbcol=seagreen]
B[vbcol=seagreen]
Updates[vbcol=seagreen]
Let[vbcol=seagreen]
|||Thanks for the info .
Can you please suggest me a mechanism for updatating the data back to the
primary server from the Processing A DB .
I am planning to use replication from Processing A back to the primary
server ... any suggestion here
Raj
"Hilary Cotter" wrote:
> it will be cyclic unless you can figure out some criteria to use as a basis
> for not applying the transaction. Use this criteria as a basis as to whether
> the proc applies the data on the subscriber/publisher or not.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:127378BB-BBAB-4F77-9A10-817170920414@.microsoft.com...
> over
> is
> have
> going
> going
> the
> on
> ?
> act
> not
> and
> unmodified
> B
> Updates
> Let
>
>
|||replication with custom stored procedures.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:501BF2AB-FF83-4EE4-9927-F80606684F85@.microsoft.com...[vbcol=seagreen]
> Thanks for the info .
> Can you please suggest me a mechanism for updatating the data back to the
> primary server from the Processing A DB .
> I am planning to use replication from Processing A back to the primary
> server ... any suggestion here
> Raj
> "Hilary Cotter" wrote:
basis[vbcol=seagreen]
whether[vbcol=seagreen]
back to[vbcol=seagreen]
and[vbcol=seagreen]
bit[vbcol=seagreen]
execute[vbcol=seagreen]
might[vbcol=seagreen]
this[vbcol=seagreen]
this[vbcol=seagreen]
Push[vbcol=seagreen]
field[vbcol=seagreen]
the[vbcol=seagreen]
Updates[vbcol=seagreen]
with[vbcol=seagreen]
will[vbcol=seagreen]
you do[vbcol=seagreen]
Primary[vbcol=seagreen]
Primary[vbcol=seagreen]
replication[vbcol=seagreen]
for a[vbcol=seagreen]
own[vbcol=seagreen]
..The[vbcol=seagreen]
DB.[vbcol=seagreen]
Processing[vbcol=seagreen]
this .[vbcol=seagreen]
There are 4 major subsystem in the application each uses its own database.
Database :
1) Primary DB
2) Processing A DB
3) Reporting DB
4) Processing B DB
The Purpose of Primary is to hold all the Input data in unmodified state.
Processing A DB is a Exact copy of Primary DB.
In Processing A DB the the computation & Validation happens .The Results (
More than one Table )of Processing A is updated in Primary DB.
This Updates needs to be transfered to Reporting DB , Processing B DB.
In Processing B DB some status column is updated and this Updates needs
to be propogated back to the Primary
I am Looking at transactional Replication as a Option for this . Let me
know your views on this
You are trying to do work flow. Replication is not an easy fit with this.
Basically with a replication solution you need a server which will act as
the clearing house figuring out what goes where. With sql2000 you do not
have this - with SQL 2005 you sort of do with the peer to peer replication
model with transactional replication.
It seems that your data flow is this
Primary -> Processing A -> Reporting
-> Processing B -> Primary
Primary <- (with filtering) Processing A
By what criteria does data flow from Processing A back to Primary and how
are you going to prevent data from Processing B flowing back to Processing
A?
If you can filter this data, you could use transactional replication from
one node to another. If you can't I don't think transactional replication
will be a good fit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:DF063537-EE93-4742-AFBB-8BBAF557CEE4@.microsoft.com...
> I am in a Process of designing database deployment layout for a system.
> There are 4 major subsystem in the application each uses its own
database.
> Database :
> 1) Primary DB
> 2) Processing A DB
> 3) Reporting DB
> 4) Processing B DB
>
> The Purpose of Primary is to hold all the Input data in unmodified
state.
> Processing A DB is a Exact copy of Primary DB.
> In Processing A DB the the computation & Validation happens .The Results (
> More than one Table )of Processing A is updated in Primary DB.
> This Updates needs to be transfered to Reporting DB , Processing B DB.
> In Processing B DB some status column is updated and this Updates needs
> to be propogated back to the Primary
> I am Looking at transactional Replication as a Option for this . Let me
> know your views on this
>
|||Thanks for your response.
Well I have a Status Column on the Primary field based on which i Push the
(Set of tables) data to the Processing A DB .
Process A DB does some computation and Updates Data & the staus field on th
Tables .
My Question is whats the Best way of Pushing this data back(from Processing
A) to the Primary server .
There are around 30 table which gets updated .
I am currently looking at DTS/ Transactional Replication to move the
tables back to the Primay DB. I feel there would be Cyclic Updates ?
any advice on this
Best Reagrds
"Hilary Cotter" wrote:
> You are trying to do work flow. Replication is not an easy fit with this.
> Basically with a replication solution you need a server which will act as
> the clearing house figuring out what goes where. With sql2000 you do not
> have this - with SQL 2005 you sort of do with the peer to peer replication
> model with transactional replication.
> It seems that your data flow is this
> Primary -> Processing A -> Reporting
> -> Processing B -> Primary
> Primary <- (with filtering) Processing A
> By what criteria does data flow from Processing A back to Primary and how
> are you going to prevent data from Processing B flowing back to Processing
> A?
> If you can filter this data, you could use transactional replication from
> one node to another. If you can't I don't think transactional replication
> will be a good fit.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:DF063537-EE93-4742-AFBB-8BBAF557CEE4@.microsoft.com...
> database.
> state.
>
>
|||I think you want to replicate these tables back, but ensure that the
replication stored procedures have logic built in them to only execute is
the status column is an update.
For instance going from primary to processing a the status column might have
a value of 0, when processed on A it is updated to 1.
In your update replication procs have a wrapper which looks like this going
back to Primary.
if StatusColumn=1 then
do work
else
return 0
In your update replication procs have a wrapper which looks like this going
to PRocessing A.
if StatusColumn=0 then
do work
else
return 0
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:2242A1DF-2047-4C2B-A8F6-A63835767DF5@.microsoft.com...
> Thanks for your response.
> Well I have a Status Column on the Primary field based on which i Push the
> (Set of tables) data to the Processing A DB .
> Process A DB does some computation and Updates Data & the staus field on
th
> Tables .
> My Question is whats the Best way of Pushing this data back(from
Processing[vbcol=seagreen]
> A) to the Primary server .
> There are around 30 table which gets updated .
> I am currently looking at DTS/ Transactional Replication to move the
> tables back to the Primay DB. I feel there would be Cyclic Updates ?
> any advice on this
> Best Reagrds
>
> "Hilary Cotter" wrote:
this.[vbcol=seagreen]
as[vbcol=seagreen]
replication[vbcol=seagreen]
how[vbcol=seagreen]
Processing[vbcol=seagreen]
from[vbcol=seagreen]
replication[vbcol=seagreen]
system.[vbcol=seagreen]
Results ([vbcol=seagreen]
DB.[vbcol=seagreen]
needs[vbcol=seagreen]
me[vbcol=seagreen]
|||Thanks Hilary
Well I have not yet decided on the mechanism to updating the data back to
primary
Server .
I am considering to create a Publication on the Processing A server and
let Primary server subscribe this datas ( seems to me a cyclic and bit over
head) .
Note : there will be more that 30 table updates which needs to be send
back-forth
do you have any suggesstion
"Hilary Cotter" wrote:
> I think you want to replicate these tables back, but ensure that the
> replication stored procedures have logic built in them to only execute is
> the status column is an update.
> For instance going from primary to processing a the status column might have
> a value of 0, when processed on A it is updated to 1.
> In your update replication procs have a wrapper which looks like this going
> back to Primary.
> if StatusColumn=1 then
> do work
> else
> return 0
> In your update replication procs have a wrapper which looks like this going
> to PRocessing A.
> if StatusColumn=0 then
> do work
> else
> return 0
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:2242A1DF-2047-4C2B-A8F6-A63835767DF5@.microsoft.com...
> th
> Processing
> this.
> as
> replication
> how
> Processing
> from
> replication
> system.
> Results (
> DB.
> needs
> me
>
>
|||it will be cyclic unless you can figure out some criteria to use as a basis
for not applying the transaction. Use this criteria as a basis as to whether
the proc applies the data on the subscriber/publisher or not.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:127378BB-BBAB-4F77-9A10-817170920414@.microsoft.com...
> Thanks Hilary
> Well I have not yet decided on the mechanism to updating the data back to
> primary
> Server .
> I am considering to create a Publication on the Processing A server and
> let Primary server subscribe this datas ( seems to me a cyclic and bit
over[vbcol=seagreen]
> head) .
> Note : there will be more that 30 table updates which needs to be send
> back-forth
> do you have any suggesstion
>
> "Hilary Cotter" wrote:
is[vbcol=seagreen]
have[vbcol=seagreen]
going[vbcol=seagreen]
going[vbcol=seagreen]
the[vbcol=seagreen]
on[vbcol=seagreen]
?[vbcol=seagreen]
act[vbcol=seagreen]
not[vbcol=seagreen]
and[vbcol=seagreen]
unmodified[vbcol=seagreen]
B[vbcol=seagreen]
Updates[vbcol=seagreen]
Let[vbcol=seagreen]
|||Thanks for the info .
Can you please suggest me a mechanism for updatating the data back to the
primary server from the Processing A DB .
I am planning to use replication from Processing A back to the primary
server ... any suggestion here
Raj
"Hilary Cotter" wrote:
> it will be cyclic unless you can figure out some criteria to use as a basis
> for not applying the transaction. Use this criteria as a basis as to whether
> the proc applies the data on the subscriber/publisher or not.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Raaj" <Raaj@.discussions.microsoft.com> wrote in message
> news:127378BB-BBAB-4F77-9A10-817170920414@.microsoft.com...
> over
> is
> have
> going
> going
> the
> on
> ?
> act
> not
> and
> unmodified
> B
> Updates
> Let
>
>
|||replication with custom stored procedures.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raaj" <Raaj@.discussions.microsoft.com> wrote in message
news:501BF2AB-FF83-4EE4-9927-F80606684F85@.microsoft.com...[vbcol=seagreen]
> Thanks for the info .
> Can you please suggest me a mechanism for updatating the data back to the
> primary server from the Processing A DB .
> I am planning to use replication from Processing A back to the primary
> server ... any suggestion here
> Raj
> "Hilary Cotter" wrote:
basis[vbcol=seagreen]
whether[vbcol=seagreen]
back to[vbcol=seagreen]
and[vbcol=seagreen]
bit[vbcol=seagreen]
execute[vbcol=seagreen]
might[vbcol=seagreen]
this[vbcol=seagreen]
this[vbcol=seagreen]
Push[vbcol=seagreen]
field[vbcol=seagreen]
the[vbcol=seagreen]
Updates[vbcol=seagreen]
with[vbcol=seagreen]
will[vbcol=seagreen]
you do[vbcol=seagreen]
Primary[vbcol=seagreen]
Primary[vbcol=seagreen]
replication[vbcol=seagreen]
for a[vbcol=seagreen]
own[vbcol=seagreen]
..The[vbcol=seagreen]
DB.[vbcol=seagreen]
Processing[vbcol=seagreen]
this .[vbcol=seagreen]
info about sql server 2005 everywere
Hi ,
I must to realize a database application for an Automation system consisting of a Industrial PC with Operative System microsoft windows embedded.
I must to realize a database application for an Automation system consisting of a Industrial PC with Operative System microsoft windows embedded.
1° question: Is Microsoft sql server 2005 everywere compatible for Microsoft windows embedded?
(I ask this because in specific of its is not clear)
2° question: In the case that it's compatible, if I instal it in a industrial Pc can it to act by database server or necessity of a remote server?
Tanks
Yes, SQL Everywhere can run on Windows XP Embedded (I think that is what you are asking). It can be used as a local database for your embedded application without ever synchronizing with a server, and it can also use two data synchronization technologies called RDA and Merge Replication (included with SQL Everywhere) to achieve data synchronization.
Darren
Wednesday, March 21, 2012
Indexing Word Docs
We currently have an application that OCRs a tif image and places the
recognized text in a SQL table.
The table is then indexed by the FTS service.
The app then allows you to search for any of the text and display the
corresponding tif image in a viewer.
I would also like to be able to search WORD docs for their contents using
the same catalog.
What is the proper manner to have the WORD docs indexed by the FTS service?
Do I need to extract the text from the WORD doc and store it in the table
much like the recognized text
from the OCR process?
Thanks
John,
What is the relationship between FTS and Indexing Service?
It looks like the Indexing Service maintains a catalog much the same as FTS.
We have support for WORD in our app already by storing the WORD doc in our
file warehouse on the file system.
We can display the .doc file in our viewer the same as a .tif image.
We currently don't have functionality to search for data in the WORD docs,
only text from the OCR process.
Since the WORD file is already stored in the file system and referenced by
our application, I was wondering about the feature that is titled "Full-text
Querying of File Data"
It looks like it uses the Index Service to allow searching for data in files
on the file system.
Wouldn't that work for my scenario?
It appears that when we want to search for data contained in a WORD doc, we
would use the SCOPE function in our query. Otherwise, we continue to search
for text from the OCR process.
Can you provide some insight?
Thanks
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> Binder,
> What version of SQL Server (2000 or 7.0) and on what OS platform (NT4.0,
> Win2K, or Win2003) is it installed? Could you post the full output of
> SELECT @.@.version -- as this is helpful to answering your question.
> If you are using SQL Server 2000, you can use it's new feature (this
feature
> is not present in SQL 7.0) - from SQL Sever 2000 BOL title "Filtering
> Supported File Types". This feature allows you to store the binary version
> of the MS Word document and then in your table define a file extension
> column and populate it with the correct values ("doc" for MS Word
document)
> and then run a Full Population and then you can use the CONTAINS or
FREETEXT
> quires to FTS the contents of these files stored in a sql table>
> If you are using SQL Server 7.0, you will need to setup a process to
extract
> the MS Word text and then store this text in a TEXT column and the FT
Index[vbcol=seagreen]
> that column, much as you do for your OCR'ed data.
> Regards,
> John
>
> "Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
> news:eIXu546VEHA.2716@.tk2msftngp13.phx.gbl...
using[vbcol=seagreen]
> service?
table
>
|||System Parameters:
Windows 2000 Server
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:OcUOqQGWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> John,
> What is the relationship between FTS and Indexing Service?
> It looks like the Indexing Service maintains a catalog much the same as
FTS.
> We have support for WORD in our app already by storing the WORD doc in our
> file warehouse on the file system.
> We can display the .doc file in our viewer the same as a .tif image.
> We currently don't have functionality to search for data in the WORD docs,
> only text from the OCR process.
> Since the WORD file is already stored in the file system and referenced by
> our application, I was wondering about the feature that is titled
"Full-text
> Querying of File Data"
> It looks like it uses the Index Service to allow searching for data in
files
> on the file system.
> Wouldn't that work for my scenario?
> It appears that when we want to search for data contained in a WORD doc,
we
> would use the SCOPE function in our query. Otherwise, we continue to
search[vbcol=seagreen]
> for text from the OCR process.
> Can you provide some insight?
> Thanks
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> feature
version
> document)
> FREETEXT
> extract
> Index
> using
> table
>
|||Binder,
Q. What is the relationship between FTS and Indexing Service?
A. While they use the same underlying Microsoft Search Technology, they full
text index different servers. Indexing Service handles the server's files on
its local disk drive, while FTS (or really the "Micrsoft Search" service
[mssearch.exe]) full text indexes textaul (char, nvarchar, text, etc.)
columns in SQL Server tables. Yes, it seems to me that using the Indexing
Service, should work for you.
What is the name of your app? Does it support SQL Server 2000? If so, does
it support the storage of MS Word documents in columns that are defined with
the IMAGE datatype? Is the feature that is titled "Full-text Querying of
File Data", a feature of your app, or are you referring to the feature of
SQL Severer (version) ?
In addition to SQL Server's Full-text Search (FTS) component, you can also
define a "Linked Server" to the Indexing Service via using MSIDX, the "OLE
DB Provider for Microsoft Indexing Service". You would define this linked
server via sp_addlinkedserver. Below is an example from SQL Server 2000
Books Online:
G. Use the Microsoft OLE DB Provider for Indexing Service
This example creates a linked server and uses OPENQUERY to retrieve
information from both the linked server and the file system enabled for
Indexing Service.
EXEC sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'yEmployees')
DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
(
id int NOT NULL,
lname varchar(30) NOT NULL,
fname varchar(30) NOT NULL,
salary money,
hiredate datetime
)
GO
INSERT yEmployees VALUES
(
10,
'Fuller',
'Andrew',
$60000,
'9/12/98'
)
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'DistribFiles')
DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles
AS
SELECT *
FROM OPENQUERY(FileSystem,
'SELECT Directory,
FileName,
DocAuthor,
Size,
Create,
Write
FROM SCOPE('' "c:\My Documents" '')
WHERE CONTAINS(''Distributed'') > 0
AND FileName LIKE ''%.doc%'' ')
WHERE DATEPART(yy, Write) = 1998
GO
SELECT *
FROM DistribFiles
GO
SELECT Directory,
FileName,
DocAuthor,
hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO
Regards,
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:OcUOqQGWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> John,
> What is the relationship between FTS and Indexing Service?
> It looks like the Indexing Service maintains a catalog much the same as
FTS.
> We have support for WORD in our app already by storing the WORD doc in our
> file warehouse on the file system.
> We can display the .doc file in our viewer the same as a .tif image.
> We currently don't have functionality to search for data in the WORD docs,
> only text from the OCR process.
> Since the WORD file is already stored in the file system and referenced by
> our application, I was wondering about the feature that is titled
"Full-text
> Querying of File Data"
> It looks like it uses the Index Service to allow searching for data in
files
> on the file system.
> Wouldn't that work for my scenario?
> It appears that when we want to search for data contained in a WORD doc,
we
> would use the SCOPE function in our query. Otherwise, we continue to
search[vbcol=seagreen]
> for text from the OCR process.
> Can you provide some insight?
> Thanks
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> feature
version
> document)
> FREETEXT
> extract
> Index
> using
> table
>
recognized text in a SQL table.
The table is then indexed by the FTS service.
The app then allows you to search for any of the text and display the
corresponding tif image in a viewer.
I would also like to be able to search WORD docs for their contents using
the same catalog.
What is the proper manner to have the WORD docs indexed by the FTS service?
Do I need to extract the text from the WORD doc and store it in the table
much like the recognized text
from the OCR process?
Thanks
John,
What is the relationship between FTS and Indexing Service?
It looks like the Indexing Service maintains a catalog much the same as FTS.
We have support for WORD in our app already by storing the WORD doc in our
file warehouse on the file system.
We can display the .doc file in our viewer the same as a .tif image.
We currently don't have functionality to search for data in the WORD docs,
only text from the OCR process.
Since the WORD file is already stored in the file system and referenced by
our application, I was wondering about the feature that is titled "Full-text
Querying of File Data"
It looks like it uses the Index Service to allow searching for data in files
on the file system.
Wouldn't that work for my scenario?
It appears that when we want to search for data contained in a WORD doc, we
would use the SCOPE function in our query. Otherwise, we continue to search
for text from the OCR process.
Can you provide some insight?
Thanks
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> Binder,
> What version of SQL Server (2000 or 7.0) and on what OS platform (NT4.0,
> Win2K, or Win2003) is it installed? Could you post the full output of
> SELECT @.@.version -- as this is helpful to answering your question.
> If you are using SQL Server 2000, you can use it's new feature (this
feature
> is not present in SQL 7.0) - from SQL Sever 2000 BOL title "Filtering
> Supported File Types". This feature allows you to store the binary version
> of the MS Word document and then in your table define a file extension
> column and populate it with the correct values ("doc" for MS Word
document)
> and then run a Full Population and then you can use the CONTAINS or
FREETEXT
> quires to FTS the contents of these files stored in a sql table>
> If you are using SQL Server 7.0, you will need to setup a process to
extract
> the MS Word text and then store this text in a TEXT column and the FT
Index[vbcol=seagreen]
> that column, much as you do for your OCR'ed data.
> Regards,
> John
>
> "Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
> news:eIXu546VEHA.2716@.tk2msftngp13.phx.gbl...
using[vbcol=seagreen]
> service?
table
>
|||System Parameters:
Windows 2000 Server
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:OcUOqQGWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> John,
> What is the relationship between FTS and Indexing Service?
> It looks like the Indexing Service maintains a catalog much the same as
FTS.
> We have support for WORD in our app already by storing the WORD doc in our
> file warehouse on the file system.
> We can display the .doc file in our viewer the same as a .tif image.
> We currently don't have functionality to search for data in the WORD docs,
> only text from the OCR process.
> Since the WORD file is already stored in the file system and referenced by
> our application, I was wondering about the feature that is titled
"Full-text
> Querying of File Data"
> It looks like it uses the Index Service to allow searching for data in
files
> on the file system.
> Wouldn't that work for my scenario?
> It appears that when we want to search for data contained in a WORD doc,
we
> would use the SCOPE function in our query. Otherwise, we continue to
search[vbcol=seagreen]
> for text from the OCR process.
> Can you provide some insight?
> Thanks
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> feature
version
> document)
> FREETEXT
> extract
> Index
> using
> table
>
|||Binder,
Q. What is the relationship between FTS and Indexing Service?
A. While they use the same underlying Microsoft Search Technology, they full
text index different servers. Indexing Service handles the server's files on
its local disk drive, while FTS (or really the "Micrsoft Search" service
[mssearch.exe]) full text indexes textaul (char, nvarchar, text, etc.)
columns in SQL Server tables. Yes, it seems to me that using the Indexing
Service, should work for you.
What is the name of your app? Does it support SQL Server 2000? If so, does
it support the storage of MS Word documents in columns that are defined with
the IMAGE datatype? Is the feature that is titled "Full-text Querying of
File Data", a feature of your app, or are you referring to the feature of
SQL Severer (version) ?
In addition to SQL Server's Full-text Search (FTS) component, you can also
define a "Linked Server" to the Indexing Service via using MSIDX, the "OLE
DB Provider for Microsoft Indexing Service". You would define this linked
server via sp_addlinkedserver. Below is an example from SQL Server 2000
Books Online:
G. Use the Microsoft OLE DB Provider for Indexing Service
This example creates a linked server and uses OPENQUERY to retrieve
information from both the linked server and the file system enabled for
Indexing Service.
EXEC sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'yEmployees')
DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
(
id int NOT NULL,
lname varchar(30) NOT NULL,
fname varchar(30) NOT NULL,
salary money,
hiredate datetime
)
GO
INSERT yEmployees VALUES
(
10,
'Fuller',
'Andrew',
$60000,
'9/12/98'
)
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'DistribFiles')
DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles
AS
SELECT *
FROM OPENQUERY(FileSystem,
'SELECT Directory,
FileName,
DocAuthor,
Size,
Create,
Write
FROM SCOPE('' "c:\My Documents" '')
WHERE CONTAINS(''Distributed'') > 0
AND FileName LIKE ''%.doc%'' ')
WHERE DATEPART(yy, Write) = 1998
GO
SELECT *
FROM DistribFiles
GO
SELECT Directory,
FileName,
DocAuthor,
hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO
Regards,
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:OcUOqQGWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> John,
> What is the relationship between FTS and Indexing Service?
> It looks like the Indexing Service maintains a catalog much the same as
FTS.
> We have support for WORD in our app already by storing the WORD doc in our
> file warehouse on the file system.
> We can display the .doc file in our viewer the same as a .tif image.
> We currently don't have functionality to search for data in the WORD docs,
> only text from the OCR process.
> Since the WORD file is already stored in the file system and referenced by
> our application, I was wondering about the feature that is titled
"Full-text
> Querying of File Data"
> It looks like it uses the Index Service to allow searching for data in
files
> on the file system.
> Wouldn't that work for my scenario?
> It appears that when we want to search for data contained in a WORD doc,
we
> would use the SCOPE function in our query. Otherwise, we continue to
search[vbcol=seagreen]
> for text from the OCR process.
> Can you provide some insight?
> Thanks
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> feature
version
> document)
> FREETEXT
> extract
> Index
> using
> table
>
Monday, March 12, 2012
Indexing an ASP.NET web application
Hi,
I'm looking to index my db-driven ASP.NET website to drive a search
feature. I would like to know if there is a way to give greater weight
to search terms when they appear in the page's headers. In other words
if my search term was "foo", the following instance:
<h1>Foo</h1>
would have more weight than
<p>Foo</p>
I'm obviously looking for greater, more granular control of the rank
of pages that are returned by my fulltext query, so if anyone could
point me in the right direction or to some resource that would help me
out, I'd be extremely grateful!
Regards,
ChrisHi Chris
"hardieca@.hotmail.com" wrote:
> Hi,
> I'm looking to index my db-driven ASP.NET website to drive a search
> feature. I would like to know if there is a way to give greater weight
> to search terms when they appear in the page's headers. In other words
> if my search term was "foo", the following instance:
> <h1>Foo</h1>
> would have more weight than
> <p>Foo</p>
> I'm obviously looking for greater, more granular control of the rank
> of pages that are returned by my fulltext query, so if anyone could
> point me in the right direction or to some resource that would help me
> out, I'd be extremely grateful!
> Regards,
> Chris
>
I don't know of a way out of the box although I am not a FT expert! I guess
you could roll your own and separate key words from the headers/body and
store them in a table.
John
I'm looking to index my db-driven ASP.NET website to drive a search
feature. I would like to know if there is a way to give greater weight
to search terms when they appear in the page's headers. In other words
if my search term was "foo", the following instance:
<h1>Foo</h1>
would have more weight than
<p>Foo</p>
I'm obviously looking for greater, more granular control of the rank
of pages that are returned by my fulltext query, so if anyone could
point me in the right direction or to some resource that would help me
out, I'd be extremely grateful!
Regards,
ChrisHi Chris
"hardieca@.hotmail.com" wrote:
> Hi,
> I'm looking to index my db-driven ASP.NET website to drive a search
> feature. I would like to know if there is a way to give greater weight
> to search terms when they appear in the page's headers. In other words
> if my search term was "foo", the following instance:
> <h1>Foo</h1>
> would have more weight than
> <p>Foo</p>
> I'm obviously looking for greater, more granular control of the rank
> of pages that are returned by my fulltext query, so if anyone could
> point me in the right direction or to some resource that would help me
> out, I'd be extremely grateful!
> Regards,
> Chris
>
I don't know of a way out of the box although I am not a FT expert! I guess
you could roll your own and separate key words from the headers/body and
store them in a table.
John
Indexing an ASP.NET web application
Hi,
I'm looking to index my db-driven ASP.NET website to drive a search
feature. I would like to know if there is a way to give greater weight
to search terms when they appear in the page's headers. In other words
if my search term was "foo", the following instance:
<h1>Foo</h1>
would have more weight than
<p>Foo</p>
I'm obviously looking for greater, more granular control of the rank
of pages that are returned by my fulltext query, so if anyone could
point me in the right direction or to some resource that would help me
out, I'd be extremely grateful!
Regards,
ChrisHi Chris
"hardieca@.hotmail.com" wrote:
> Hi,
> I'm looking to index my db-driven ASP.NET website to drive a search
> feature. I would like to know if there is a way to give greater weight
> to search terms when they appear in the page's headers. In other words
> if my search term was "foo", the following instance:
> <h1>Foo</h1>
> would have more weight than
> <p>Foo</p>
> I'm obviously looking for greater, more granular control of the rank
> of pages that are returned by my fulltext query, so if anyone could
> point me in the right direction or to some resource that would help me
> out, I'd be extremely grateful!
> Regards,
> Chris
>
I don't know of a way out of the box although I am not a FT expert! I guess
you could roll your own and separate key words from the headers/body and
store them in a table.
John
I'm looking to index my db-driven ASP.NET website to drive a search
feature. I would like to know if there is a way to give greater weight
to search terms when they appear in the page's headers. In other words
if my search term was "foo", the following instance:
<h1>Foo</h1>
would have more weight than
<p>Foo</p>
I'm obviously looking for greater, more granular control of the rank
of pages that are returned by my fulltext query, so if anyone could
point me in the right direction or to some resource that would help me
out, I'd be extremely grateful!
Regards,
ChrisHi Chris
"hardieca@.hotmail.com" wrote:
> Hi,
> I'm looking to index my db-driven ASP.NET website to drive a search
> feature. I would like to know if there is a way to give greater weight
> to search terms when they appear in the page's headers. In other words
> if my search term was "foo", the following instance:
> <h1>Foo</h1>
> would have more weight than
> <p>Foo</p>
> I'm obviously looking for greater, more granular control of the rank
> of pages that are returned by my fulltext query, so if anyone could
> point me in the right direction or to some resource that would help me
> out, I'd be extremely grateful!
> Regards,
> Chris
>
I don't know of a way out of the box although I am not a FT expert! I guess
you could roll your own and separate key words from the headers/body and
store them in a table.
John
Indexing an ASP.NET web application
Hi,
I'm looking to index my db-driven ASP.NET website to drive a search
feature. I would like to know if there is a way to give greater weight
to search terms when they appear in the page's headers. In other words
if my search term was "foo", the following instance:
<h1>Foo</h1>
would have more weight than
<p>Foo</p>
I'm obviously looking for greater, more granular control of the rank
of pages that are returned by my fulltext query, so if anyone could
point me in the right direction or to some resource that would help me
out, I'd be extremely grateful!
Regards,
Chris
Hi Chris
"hardieca@.hotmail.com" wrote:
> Hi,
> I'm looking to index my db-driven ASP.NET website to drive a search
> feature. I would like to know if there is a way to give greater weight
> to search terms when they appear in the page's headers. In other words
> if my search term was "foo", the following instance:
> <h1>Foo</h1>
> would have more weight than
> <p>Foo</p>
> I'm obviously looking for greater, more granular control of the rank
> of pages that are returned by my fulltext query, so if anyone could
> point me in the right direction or to some resource that would help me
> out, I'd be extremely grateful!
> Regards,
> Chris
>
I don't know of a way out of the box although I am not a FT expert! I guess
you could roll your own and separate key words from the headers/body and
store them in a table.
John
I'm looking to index my db-driven ASP.NET website to drive a search
feature. I would like to know if there is a way to give greater weight
to search terms when they appear in the page's headers. In other words
if my search term was "foo", the following instance:
<h1>Foo</h1>
would have more weight than
<p>Foo</p>
I'm obviously looking for greater, more granular control of the rank
of pages that are returned by my fulltext query, so if anyone could
point me in the right direction or to some resource that would help me
out, I'd be extremely grateful!
Regards,
Chris
Hi Chris
"hardieca@.hotmail.com" wrote:
> Hi,
> I'm looking to index my db-driven ASP.NET website to drive a search
> feature. I would like to know if there is a way to give greater weight
> to search terms when they appear in the page's headers. In other words
> if my search term was "foo", the following instance:
> <h1>Foo</h1>
> would have more weight than
> <p>Foo</p>
> I'm obviously looking for greater, more granular control of the rank
> of pages that are returned by my fulltext query, so if anyone could
> point me in the right direction or to some resource that would help me
> out, I'd be extremely grateful!
> Regards,
> Chris
>
I don't know of a way out of the box although I am not a FT expert! I guess
you could roll your own and separate key words from the headers/body and
store them in a table.
John
Indexing ?
I have a simple table that includes an ID and 8 user-defined data fields. Th
e
fields are nvarchar(255) and the application allows the user to store any
data they like in them. The application also allows searching on any
combination of up to 5 of the fields, with an AND or OR logical combination.
The query returns the result set unordered.
Does anyone have any thoughts on an indexing scheme for this situation? I'm
not quite sure what is best given that the user may search on any combinatio
n
of fields. Some obvious thoughts are:
1. An separate index on each column.
2. A single index covering all columns.
3. A separate index on each of the 8 choose 5 combinations of columns
(whoah!).
Any thoughts?Although I do not condone this, creating an index on each of the columns
would allow SQL Server to use Index Joining / Intersection to perform the
search. Creating an Index on all the columns would only benefit the
statements that search on the columns in the order of the index.
"Ken" wrote:
> I have a simple table that includes an ID and 8 user-defined data fields.
The
> fields are nvarchar(255) and the application allows the user to store any
> data they like in them. The application also allows searching on any
> combination of up to 5 of the fields, with an AND or OR logical combinatio
n.
> The query returns the result set unordered.
> Does anyone have any thoughts on an indexing scheme for this situation? I'
m
> not quite sure what is best given that the user may search on any combinat
ion
> of fields. Some obvious thoughts are:
> 1. An separate index on each column.
> 2. A single index covering all columns.
> 3. A separate index on each of the 8 choose 5 combinations of columns
> (whoah!).
> Any thoughts?
>
e
fields are nvarchar(255) and the application allows the user to store any
data they like in them. The application also allows searching on any
combination of up to 5 of the fields, with an AND or OR logical combination.
The query returns the result set unordered.
Does anyone have any thoughts on an indexing scheme for this situation? I'm
not quite sure what is best given that the user may search on any combinatio
n
of fields. Some obvious thoughts are:
1. An separate index on each column.
2. A single index covering all columns.
3. A separate index on each of the 8 choose 5 combinations of columns
(whoah!).
Any thoughts?Although I do not condone this, creating an index on each of the columns
would allow SQL Server to use Index Joining / Intersection to perform the
search. Creating an Index on all the columns would only benefit the
statements that search on the columns in the order of the index.
"Ken" wrote:
> I have a simple table that includes an ID and 8 user-defined data fields.
The
> fields are nvarchar(255) and the application allows the user to store any
> data they like in them. The application also allows searching on any
> combination of up to 5 of the fields, with an AND or OR logical combinatio
n.
> The query returns the result set unordered.
> Does anyone have any thoughts on an indexing scheme for this situation? I'
m
> not quite sure what is best given that the user may search on any combinat
ion
> of fields. Some obvious thoughts are:
> 1. An separate index on each column.
> 2. A single index covering all columns.
> 3. A separate index on each of the 8 choose 5 combinations of columns
> (whoah!).
> Any thoughts?
>
Friday, March 9, 2012
Indexes with Server Cursor
I am working with an application that uses a server cursor on SQL 2000. Whenever the program tries to search for a record, the search takes forever. I was hoping I could just search for the record within the entire dataset without doing a sql select command. It almost seems like the server cursor isn't using the indexes I have setup. Does anyone know if the server cursor will take advantage of the indexes?You may need to recompile the stored proc if you created the index after the fact. Or if the index has been around for a while you may need to run a DBCC REINDEX. If the index is out of date or bad SQL Server will automatically quit using it.
Make sure you are not overriding SQL with a HINT.
If this doesn't help can you post the CREATE TABLE and a sample of the SELECT. Maybe you don't have the WHERE clause covered by the INDEX and SQL thinks it can do better without it.|||As referred make sure to run DBCC checks to fine tune the performance. Also runn PROFILER during this operation and see where it lacks, submit the trace to INDEX TUNING WIZARD for any recommendation on indexes that may help.|||You may want to reconsider using a cursor -- there may be better ways to obtain the data you are seeking.|||Unfortuntely I don't know of any other way, the main obstacle with this program is that I need to have all the data there for the user to see. Currently I do have the searches returning subsets with a sql statement, but I would very much like to be able to use a locate command instead.
I tried reindexing but that didn't work either. Thanks for all your replies, any other suggestions? I'm stumped :)
Originally posted by MattR
You may want to reconsider using a cursor -- there may be better ways to obtain the data you are seeking.|||Try using QUERY Execution plan in QA using the query.
BTW what is the SQL version & SP level and how do you connect from application to database?
Make sure you are not overriding SQL with a HINT.
If this doesn't help can you post the CREATE TABLE and a sample of the SELECT. Maybe you don't have the WHERE clause covered by the INDEX and SQL thinks it can do better without it.|||As referred make sure to run DBCC checks to fine tune the performance. Also runn PROFILER during this operation and see where it lacks, submit the trace to INDEX TUNING WIZARD for any recommendation on indexes that may help.|||You may want to reconsider using a cursor -- there may be better ways to obtain the data you are seeking.|||Unfortuntely I don't know of any other way, the main obstacle with this program is that I need to have all the data there for the user to see. Currently I do have the searches returning subsets with a sql statement, but I would very much like to be able to use a locate command instead.
I tried reindexing but that didn't work either. Thanks for all your replies, any other suggestions? I'm stumped :)
Originally posted by MattR
You may want to reconsider using a cursor -- there may be better ways to obtain the data you are seeking.|||Try using QUERY Execution plan in QA using the query.
BTW what is the SQL version & SP level and how do you connect from application to database?
Friday, February 24, 2012
Indexes and Filegroups
I'm fishing for some advice here.
I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.
I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.
My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.
At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)
I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?
I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.
So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?
The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.
many thanks
SteveI've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.
if data set weighs in at around 6Gb assignation each table into its own filegroup not requeried
The Access tables don't have primary keys. MS SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.
add new field int type with identity and set it as primary key
if you wont make quick application - do not use access 97 oledb connection to Mssql server. rebuld it to accessXP ADP project
I'm porting an old Access 97 application to SQL Server 2000. The Access app uses 9 separate files for a series of linked tables (one table in each file to get around the 1Gb Access file size limit). The tables vary in size form 2Mb to 800Mb so the whole data set weighs in at around 6Gb.
I've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.
My next problem is the indexes. The Access tables don't have primary keys. My SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.
At present I have all the table indexes in the PRIMARY filegroup (which in tunr just has the default MDF file in it)
I've built a small version of the DB for testing various triggers and new views and the DTS import packages and the indexing for the 300Mb of data I have now is obviously fairly quick. I am wondering if I should split the indexes out into each of the separate MDF files that I am storing the table data or should I split the indexes into their own files?
I want this thing to be fast. The VBA app that will be plugging into the DB has a huge amount of code and currently struggles especially when several dozen people are all connected to the same tables.
So any advice? Indexes in separate files? Or in with the data? or all together in one index file? Any performance impacts I should be aware of?
The DB is running on its own dedicated box. Its not huge 1Gb Ram, 30Gb drive and a 3Ghz P4. But given that it isn't running anything else it should be up to the job. It should certainly be faster than the current shared drive that the access app runs from.
many thanks
SteveI've prepared tables for the data in SQL Server and assigned each table into its own filegroup each of whihc has its own file so I can separate the data out and also keep an eye on the amount of data.
if data set weighs in at around 6Gb assignation each table into its own filegroup not requeried
The Access tables don't have primary keys. MS SQL tables do (Large Integers) but each table is mainly indexed on an account number which is an 11 char alphanumeric. This is non unique so I can't use it as a primary key.
add new field int type with identity and set it as primary key
if you wont make quick application - do not use access 97 oledb connection to Mssql server. rebuld it to accessXP ADP project
Subscribe to:
Posts (Atom)