Combining full text search results with index server/service

I have a solutions database that I'm setting up Full text search on. Part of
the "solutions" is a huge folder of attachments on the lan.
I've done both FTS alone and also Index server alone.
Are there any whitepapers or good websites that talk about combining the
two? I'd like to conduct the searches via sql server - ideally expanding the
full text index to include the content of the files on the lan.
- Jack
Please refer to the above post.
There is no white paper per se focusing on this. However you might want to
check out this paper which does touch on it.
http://msdn.microsoft.com/library/de...filedatats.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"jack" <jack@.discussions.microsoft.com> wrote in message
news:10938D81-5E92-483F-ABC1-208DDF331112@.microsoft.com...
> I have a solutions database that I'm setting up Full text search on. Part
of
> the "solutions" is a huge folder of attachments on the lan.
> I've done both FTS alone and also Index server alone.
> Are there any whitepapers or good websites that talk about combining the
> two? I'd like to conduct the searches via sql server - ideally expanding
the
> full text index to include the content of the files on the lan.
> - Jack

Combining 3 SQL statements

Hey all. Ive got a big problem with an sql statement Im working on.

There are 2 tables with a master/detail relationship. The Header Table
is the master, the Line Table is the detail. So for each Header, there
are many Lines, but a Line can only reference one Header.
There is a Line Total and Line Cost in each Line Record. Each Line
Record has a type.
What I want to be able to do is, for each Header, I want to Sum each
corresponding Line's Total and Cost where the type is either one value
or another. If the type is, for example, 10, only sum the Total, if its
type 2, only sum the Cost.

Therefore, after the query is executed, you should have a result set
something like this

Job : Job1 (header id)
Desc : Job0001 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job1)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job1)
--------------------------------
Job : Job2 (header id)
Desc : Job0002 (header desc)
Cost : (sum of Line Costs where Line Type is 2 and header id is Job2)
Total : (sum of Line Totals where Line Type is 10 and header id is
Job2)
--------------------------------

etc.

Hope this makes sense. ThanksTry this one here:

Select
header_id,
header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers
INNER JOIN
line
ON line.header_id = header.header_id

HTH, jens Suessmeyer.|||Jens (Jens@.sqlserver2005.de) writes:

> Try this one here:
> Select
> header_id,
> header_desc,
> SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
> SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
> FROM headers
> INNER JOIN
> line
> ON line.header_id = header.header_id

Better:

SELECT h.header_id, h.header_desc,
SUM(CASE Line_type WHEN 2 THEN costs else 0 END),
SUM(CASE Line_type WHEN 10 THEN Totals else 0 END)
FROM headers h
JOIN line ON l.header_id = h.header_id
GROUP BY h.header_id, h.header_desc

Particularly that GROUP BY clause is quite important...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You are right, thats not even better that was missing in my example.|||Is there a way to say

SUM(CASE Line_type WHEN 10 THEN costs else 0 END),
SUM(CASE Line_type WHEN NOT 10 THEN Totals else 0 END) ?|||Its OK, I figured it out.
I ended up using

SUM(CASE WHEN Line_type = 10 THEN costs else 0 END),

SUM(CASE WHEN Line_type <> 10 THEN Totals else 0 END)

THanks for the help guys :)

Combination of Filegroup and Point in Time restore possible?

SQL2K5
Ive been doing some testing this morning and it appears that it is not
possible to combine PIT restores with Filegroup restores, is that correct? I
restore a Filegroup backup, and then the Trans Log backup, but there is no
option to stop at a specific time when doing this. It would sort of make
sense I suppose. It could be argued that doing so would leave the DB in an
inconsistent state if a table that resided outside of the restored Filegroup
had been updated before the restores began, but I just wanted to confirm my
findings(?).
I think the down side to this protection would be that if you are relying on
Filegroup backups/ restores, how would you recover a data loss that had
occured due to human error (someone deletes something they shouldn't have)?
After all, to bring the tables in the restored filegroup online I MUST take a
Trans Log backup and restore it, therefore my accidentally deleted data is
still not recovered.
Does this all sound right, or am I missing something here?
All insights are appreciated.Hi Chris
This sounds like your understanding is correct.
When doing a filegroup restore, SQL Server is assuming that other filegroups
are available, with transactions that might have occurred after the backup
of the filegroup that you are restoring. Restoring a FG requires that you
apply transaction logs to bring the FG up to the same point as the rest of
the database.
The point of FG restore is to recover from hardware errors on a single file
or fg. If you need to recover from user error, you'll need to restore the
whole db.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9D74B6E1-CE97-4B5F-9E97-C0F946ECA811@.microsoft.com...
> SQL2K5
> Ive been doing some testing this morning and it appears that it is not
> possible to combine PIT restores with Filegroup restores, is that correct?
> I
> restore a Filegroup backup, and then the Trans Log backup, but there is no
> option to stop at a specific time when doing this. It would sort of make
> sense I suppose. It could be argued that doing so would leave the DB in an
> inconsistent state if a table that resided outside of the restored
> Filegroup
> had been updated before the restores began, but I just wanted to confirm
> my
> findings(?).
> I think the down side to this protection would be that if you are relying
> on
> Filegroup backups/ restores, how would you recover a data loss that had
> occured due to human error (someone deletes something they shouldn't
> have)?
> After all, to bring the tables in the restored filegroup online I MUST
> take a
> Trans Log backup and restore it, therefore my accidentally deleted data is
> still not recovered.
> Does this all sound right, or am I missing something here?
> All insights are appreciated.|||Thanks!
"Kalen Delaney" wrote:
> Hi Chris
> This sounds like your understanding is correct.
> When doing a filegroup restore, SQL Server is assuming that other filegroups
> are available, with transactions that might have occurred after the backup
> of the filegroup that you are restoring. Restoring a FG requires that you
> apply transaction logs to bring the FG up to the same point as the rest of
> the database.
> The point of FG restore is to recover from hardware errors on a single file
> or fg. If you need to recover from user error, you'll need to restore the
> whole db.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:9D74B6E1-CE97-4B5F-9E97-C0F946ECA811@.microsoft.com...
> > SQL2K5
> >
> > Ive been doing some testing this morning and it appears that it is not
> > possible to combine PIT restores with Filegroup restores, is that correct?
> > I
> > restore a Filegroup backup, and then the Trans Log backup, but there is no
> > option to stop at a specific time when doing this. It would sort of make
> > sense I suppose. It could be argued that doing so would leave the DB in an
> > inconsistent state if a table that resided outside of the restored
> > Filegroup
> > had been updated before the restores began, but I just wanted to confirm
> > my
> > findings(?).
> >
> > I think the down side to this protection would be that if you are relying
> > on
> > Filegroup backups/ restores, how would you recover a data loss that had
> > occured due to human error (someone deletes something they shouldn't
> > have)?
> > After all, to bring the tables in the restored filegroup online I MUST
> > take a
> > Trans Log backup and restore it, therefore my accidentally deleted data is
> > still not recovered.
> >
> > Does this all sound right, or am I missing something here?
> >
> > All insights are appreciated.
>
>

Column does not allow nulls

Hi,

This should be straight forward, but I've searched high and low on the net.

I have a FORM which allows me to INSERT data, SQL Server 2005 backend. I populate the all the mandatory fields.

But when I click on the Insert button, it won't let me save and says:

Column 'PERSONAL_ID' does not allow nulls

I'musing tableadapters, business logic layers etc. and pausing clearlyshows that the values from the form are being passed to the procedurethat I have created "AddNewRecord". And PERSONAL_ID is definitely notNULL!

It fails on

Line 971: Me.Rows.Add(row)

PERSONAL_IDis a primary key which I generate. The pause also shows that forPERSONAL_ID it says" {"Conversion from type 'DBNull' to type 'String'is not valid."}

Function AddNewRecord looks like this:

<System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public FunctionAddNewRecord(ByVal PERSONAL_ID As String, ByVal SURNAMEAs String, ByVal CHRISTIAN_NAME As String, ByVal SEX As String, _
ByVal FAMILY_POSITION As String, ByVal FAMILY_ID As String, ByValADDRESS_1 As String, ByVal ADDRESS_2 As String, _
ByVal ADDRESS_3As String, ByVal ADDRESS_4 As String, ByVal ADDRESS_5 As String, ByValADDRESS_6 As String, ByVal POSTCODE As String, ByVal COUNTRY As String,ByVal ORG_ID As String) As Boolean
' create a new details row instance
Dim details As New smDetails.smTbl_DetailsIDDataTable
Dim detail As smDetails.smTbl_DetailsIDRow = details.NewsmTbl_DetailsIDRow

details.AddsmTbl_DetailsIDRow(detail)
.
.
.
my formview code is this:

<InsertItemTemplate>
PERSONAL_ID:
<asp:TextBox ID="PERSONAL_IDTextBox" runat="server"
Text='<%# Bind("PERSONAL_ID") %>' AutoPostBack="True" />
<br />
SURNAME:
<asp:TextBox ID="SURNAMETextBox" runat="server" Text='<%# Bind("SURNAME") %>'
AutoPostBack="True" />
<br />
CHRISTIAN_NAME:
<asp:TextBox ID="CHRISTIAN_NAMETextBox" runat="server"
Text='<%# Bind("CHRISTIAN_NAME") %>' />
<br />
SEX:
<asp:TextBox ID="SEXTextBox" runat="server" Text='<%# Bind("SEX") %>' />
<br />
FAMILY_POSITION:
<asp:TextBox ID="FAMILY_POSITIONTextBox" runat="server"
Text='<%# Bind("FAMILY_POSITION") %>' />
<br />
FAMILY_ID:
<asp:TextBox ID="FAMILY_IDTextBox" runat="server"
Text='<%# Bind("FAMILY_ID") %>' />
<br />
ADDRESS_1:
<asp:TextBox ID="ADDRESS_1TextBox" runat="server"
Text='<%# Bind("ADDRESS_1") %>' />
<br />
ADDRESS_2:
<asp:TextBox ID="ADDRESS_2TextBox" runat="server"
Text='<%# Bind("ADDRESS_2") %>' />
<br />
ADDRESS_3:
<asp:TextBox ID="ADDRESS_3TextBox" runat="server"
Text='<%# Bind("ADDRESS_3") %>' />
<br />
ADDRESS_4:
<asp:TextBox ID="ADDRESS_4TextBox" runat="server"
Text='<%# Bind("ADDRESS_4") %>' />
<br />
ADDRESS_5:
<asp:TextBox ID="ADDRESS_5TextBox" runat="server"
Text='<%# Bind("ADDRESS_5") %>' />
<br />
ADDRESS_6:
<asp:TextBox ID="ADDRESS_6TextBox" runat="server"
Text='<%# Bind("ADDRESS_6") %>' />
<br />
POSTCODE:
<asp:TextBox ID="POSTCODETextBox" runat="server"
Text='<%# Bind("POSTCODE") %>' />
<br />
COUNTRY:
<asp:TextBox ID="COUNTRYTextBox" runat="server" Text='<%# Bind("COUNTRY") %>' />
<br />
ORG_ID:
<asp:TextBox ID="ORG_IDTextBox" runat="server" Text='<%# Bind("ORG_ID") %>' />
<br />
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
CommandName="Insert" Text="Insert" />
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</InsertItemTemplate

Any pointers in the right direction would be appreciated.

Thanks in advance

Tushar

Can you post the stored procedure/sql statement that performs the insert?

|||

INSERT INTO smTbl_DetailsID (PERSONAL_ID, SURNAME, CHRISTIAN_NAME, SEX, FAMILY_POSITION, FAMILY_ID, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, ADDRESS_5, ADDRESS_6, POSTCODE, COUNTRY, ORG_ID)VALUES (@.PERSONAL_ID,@.SURNAME,@.CHRISTIAN_NAME,@.SEX,@.FAMILY_POSITION,@.FAMILY_ID,@.ADDRESS_1,@.ADDRESS_2,@.ADDRESS_3,@.ADDRESS_4,@.ADDRESS_5,@.ADDRESS_6,@.POSTCODE,@.COUNTRY,@.ORG_ID)

Execute mode is Scalar

thanks

Tushar

|||

I've found the solution is to replace

details.AddsmTbl_DetailsIDRow(detail)

inFunction AddNewRecord in the BLL

with

Adapter1.InsertQuery_TAM(PERSONAL_ID, SURNAME, CHRISTIAN_NAME, SEX, _
FAMILY_POSITION, FAMILY_ID, ADDRESS_1, ADDRESS_2, _
ADDRESS_3, ADDRESS_4, ADDRESS_5, ADDRESS_6, POSTCODE, COUNTRY, ORG_ID)

i.e. my user defined insert query.

For some reason adding a new row directly causes the SELECT routine to run, which expects a value for the PERSONAL_ID (primary key). I'm sure I'll discover why in due course.

Also the above is not really very clear in the tutorials.

HTH

Tushar