Adobe iFilter 5 & SQL Server 2000 full text indexing of PDFs?

hi all! i'm new and hoping you might help me - have searched E V E R Y W H E R E for this answer!!

--my ASP program is running on IIS box (call it "Lucy")

--my ASP program connects to SQL Server 2000 db on another box (call it "Linus") consisting of .doc, .xls, .PDF and the like

--all's well EXCEPT when PDFs are returned! sometimes browser gives blank screen instead of PDF <?> i figure iFilter will "cure" this.

--question:
WHICH SERVER should iFilter be installed on? Lucy (where IIS is running & ASP program is) or Linus (where SQL Server does its full text indexing)?

thanks in advance for any & all help!!
geekgirlYou will have to install IFilter on the box that runs index server. I am not sure why you should be worried about SQL server when you searching documents from a folder.

Are ur documents stored in a database?|||Originally posted by vibhu
You will have to install IFilter on the box that runs index server. I am not sure why you should be worried about SQL server when you searching documents from a folder.

Are ur documents stored in a database?

sorry, YES docs are stored directly in the SQL db. iFilter says to install it "where the index server is" -- but i don't know if that means the *** IIS Index Server*** or the ***SQL Server**** which is doing the full text indexing. <??>

ADO.NET Transaction Fails to update Database after .Commit()

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives?? Anyone else ever heard of such a thing. Please help, before I loose my sanity.I may need to be more specific about the problem, so here goes. I am running an ADO.NET SQLTransaction that is calling 3 different stored procedures. The first proc Inserts data into a MailMessage Table and returns the @.@.IDENTITY of the Insert. The next set of stored procedures are called in a loop to Insert into a MailboxMessageAddressees table that has a foreign key of the MailMessages table and the ID of the Addressee. This loop can run 1-50+ times to insert all the Addressees. In SQL Profiler, I can trace the connection I made to a database and it starts the transaction, executes all stored procedures, and then COMMITS. I recieve no errors in my transaction and no errors occur on SQLServer. But the data is never inserted into the database. Its almost like the ADO.NET Transaction didn't take place, or SQL Server is rolling back the transaction on its own without throwing any errors that I can see.

Needless to say, I have closed the connection and disposed of the command and connection. Is there something else I need to send to SQL Server other than COMMIT to let it know that I have completed my transaction. This is really perplexing because I can manually execute all the stored procedures in Entrerprise Manager. I get every statement executed from the SQL Profiler when I trace the Transaction and put it in Query Analyzer and it works fine, and all the data shows up in SQL Server. Is there something I am missing?

Here is the actual code, maybe someone can clue me into what I might be missing.

/// <summary>
/// Mail_DAL.AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,Wargame_ID)
/// ->Executes a ExecuteNonQuery using the Stored Procedure Mail_DeleteGroupAddressees and an ExecuteNonQuery using stored
/// procedure Mail_AddGroupAddressee to insert all addressees not in the Group.
/// </summary>
/// <param name="Mailbox_ID">Int: The Mailbox associated with the MailGroup.</param>
/// <param name="subject">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="body">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="recipientTo">ArrayList: An ArrayList of the To addresses Mailbox_IDs for the group.</param>
/// <param name="recipientCc">ArrayList: An ArrayList of the CC addresses Mailbox_IDs for the group.</param>
/// <param name="groupTo">ArrayList: An ArrayList of the To group addresses Mailbox_IDs for the group.</param>
/// <param name="groupCc">ArrayList: An ArrayList of the CC group addresses Mailbox_IDs for the group.</param>
/// <param name="files">ArrayList: An ArrayList of Files if there are any.</param>
/// <param name="Wargame_ID">Int: The Wargame_ID of any file to be put in Files</param>
public void AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,int Wargame_ID)
{
//Get To Addresses from Groups
OpenProcCommand(Conn,"Mail_GetCurrentGroupAddressees");
SqlCommand cmd = ProcCommand;
param = new SqlParameter();
SqlDataReader dr;
Int32 holder = 0;
if(groupTo.Count > 0)
{
foreach(Object item in groupTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientTo.Contains(holder))
{
recipientTo.Add(holder);
}
}
dr.Close();
}
}

//Get CC Addresses from Groups
if(groupCc.Count > 0)
{
foreach(Object item in groupCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientCc.Contains(holder))
{
recipientCc.Add(holder);
}
}
dr.Close();
}
}

//Get BCC Recipients for this Maillbox
ArrayList recipientAll = new ArrayList();
recipientAll.Add(Convert.ToInt32(Mailbox_ID));
foreach(Object item in recipientTo)
{
recipientAll.Add(item);
}
foreach(Object item in recipientCc)
{
if(!recipientAll.Contains(item))
{
recipientAll.Add(item);
}
}
string allRecipients = "";
foreach(Object item in recipientAll)
{
allRecipients += item + ",";
}
allRecipients = allRecipients.Substring(0,allRecipients.Length-1);
ArrayList recipientBcc = new ArrayList();
cmd.CommandText = "Mail_GetBccAddressees";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Addressees",SqlDbType.VarChar,1000);
param.Value = allRecipients;
dr = cmd.ExecuteReader();
//Add any Bcc Recipients to Bcc Recipients ArrayList
while(dr.Read())
{
recipientBcc.Add(Convert.ToInt32(dr["Addressee"].ToString()));
}
dr.Close();

// Start a local transaction.
SqlTransaction myTrans = Conn.BeginTransaction();
// Enlist the command in the current transaction.
cmd.Transaction = myTrans;

try
{
//Insert Message into Database
cmd.CommandText = "Mail_AddNewMessage";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MsgFrom",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.MsgSubject",SqlDbType.VarChar,100);
param.Value = subject;
param = cmd.Parameters.Add("@.MsgBody",SqlDbType.Text);
param.Value = body;
int msg_ID = Convert.ToInt32(cmd.ExecuteScalar());

//Add To Recipients
cmd.CommandText = "Mail_AddMessageAddressees";
foreach(Object item in recipientTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 1;
cmd.ExecuteNonQuery();
}

//Add CC Recipients
foreach(Object item in recipientCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 2;
cmd.ExecuteNonQuery();
}

//Add BCC Recipients
foreach(Object item in recipientBcc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 3;
cmd.ExecuteNonQuery();
}

//Add message to senders Mailbox in SentItems
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 4;
cmd.ExecuteNonQuery();

//Add files if there are any.
foreach(HttpPostedFile myfile in files)
{
//Get the file name
string FileName = myfile.FileName;
int strLoc = FileName.LastIndexOf("\\");
FileName = FileName.Remove(0,strLoc+1);

// Get size of uploaded file
int nFileLen = myfile.ContentLength;

// Allocate a buffer for reading of the file
byte[] myData = new byte[nFileLen];

// Read uploaded file from the Stream
myfile.InputStream.Read(myData, 0, nFileLen);
File file = new File();
int file_ID = file.UploadFile(cmd,3,FileName,nFileLen,myfile.GetType().ToString(),Wargame_ID,myData);

//Add the file message association
cmd.Parameters.Clear();
cmd.CommandText = "Mail_AddMessageFile";
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.File_ID",SqlDbType.Int);
param.Value = file_ID;
cmd.ExecuteNonQuery();
}
//Commit Transaction
myTrans.Commit();
}
catch (SqlException sqlex)
{
// Specific catch for deadlock
if (sqlex.Number != 1205)
{
myTrans.Rollback();
}
throw(sqlex);
}
catch(InvalidOperationException ex1){
string ex2 = ex1.ToString();
}
catch (Exception ex)
{
myTrans.Rollback();
throw (ex);
}
finally
{
myTrans.Dispose();
Conn.Close();
Conn.Dispose();
}

}|||First try to run a simple transaction with one stored proc and minimal .net code. This will make it easier to pinpoint your problem. Could be memory problem on server or some other potential problem you are not aware of.

This may not be a problem but I have encountered this one before. Are you returning more than one id with the @.@.IDENTITY call (i.e more than one insert command in a single transaction)? If you are you need to reset the sql connection or the same id is returned each time you call it if my memory serves me correctly.

Also, you can check the error status within a stored proc and return it to .net. I have used this before to pinpoint a problem.

Cheers

Mo|||Thanks Mo for the insight, unfortunatly I still have the same problems. Funny thing is I created a single stored procedure to do all the inserts, thinking that would solve the problem, but alas nothing. Getting different results, but same outcome. The database executes the procedure, which inserts data into the database, but for some reason after about 3 min or so the rows are rolled back. Running sp_lock I can see that the process that executed the command has a lock on all the tables, but it never gives them up. After that timeout, they are all rolled back. Is there some reason anyone can think of where this would happen. I can execute the stored procedure on the database using query analyzer and the data goes in fine and stays in there. Another thing to note, after the web application executes the stored procedure I cannot view the data in the tables through Enterprise manager. I can't even view the current activity, it sits there for a while then returns a 1222 Error. This has become a nightmare. Thanks

Chris|||Could be a bug between sql server and .net ?? Are both upto date with service packs?

Have you looked on http://support.microsoft.com

Very good area for problem solving, used it many times.

ADO Error codes...

Hi,

I have another question about ADO (using C++). I have searched the MSDN but I haven't found a answer to my question (maybe I'm just too stupid)... If I make a connection to my SQL Server there may occur some errors, but how to find out what went wrong ?

In terms of code I have e.g. something like that:
try
{
connection->Open(ConnectionString,Username,Password,ADODB::adConnectUnspecified);
...
}
catch (_com_error& e)
{
long numErrors = connection->Errors->Count;
for (long i=0; i<numErrors; i++)
{
ADODB::ErrorPtr pErr = connection->Errors->GetItem(i);
.....
}
}

Now I could get the error number by pErr->GetNumber().
But with which symbolic constant has this to be compared to find out which error occured ? I didn't find any...Or is there another better way to do this ?

P.S. I am using SQL Server 2005 Express

Take a look at this link for various ADO Error codes

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconadoerrorreference.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcsterrorvalueenum.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconadoerrors.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg03_59pr.asp

Hope this helps

|||Hi,

yes it helps a little. But I saw this error collection before, and I wondererd how to check if e.g. the authenticaton was wrong. I didn't saw any symbolic constant for this. That's the reason why I posted here...|||

Download MDAC 2.8 SDK and you should see oledberr.h which should have the symbolic constants like "DB_SEC_E_AUTH_FAILED" (authentication failed) etc;

MDAC 28 SDK link - http://www.microsoft.com/downloads/details.aspx?familyid=5067faf8-0db4-429a-b502-de4329c8c850&displaylang=en

Also take a look at this link and download Oledberr.exe - http://support.microsoft.com/default.aspx?scid=kb;EN-US;q168354&GSSNB=1

Hope this helps

|||Ah that's exactly what I've searched for. Thanks a lot.
I tested it by comparing this constants to the exceptions Error-value and it worked :)
I hope this is the right way

P.S. The second link doesn't work for me, but I think it's not that important. The first link was that what I was searching for.