none
SQL 2008 全文检索 RRS feed

  • 问题

  • 我有一个WEB程序是用 visual studio 2003 SP1   英文版开发的,数据库用的是 SQL srver 2005 SP4 ,启用了全文目录;现在我把数据裤迁移到SQL srver 2008 SP1, 全文目录导入的时候选择了 “导入”。我突然发现在前台 WEB程序进行查询的时候,有的字段名查询变的很缓慢,然后出错。而同样的查询在 SQL server 2005 上却能通过。 不知道这是为什么?
    2013年2月26日 8:22

答案

全部回复

  • Better to rebuild full text index after upgrading.

    2013年2月26日 14:03
  • 升级之后重建一下索引看看性能会不会有提升。另外根据MSDN,使用import选项升级全文索引,导入的全文目录不能使用 SQL Server 2008 中引入的新的和增强的断字符,所以最好重新生成全文目录(rebuild)

    2013年2月27日 1:31
  • 我重新建立过索引,但是问题貌似没有得到解决,比如说,我在WEB程序里查询“ 季度”,全文检索正常,但是查询“二季度”,全文检索就很慢,然后程序出错,这个现象在原来的SQL 2005 SP4 的数据库集中 查询时,一切都很正常。
    2013年2月27日 1:53
  • Did you check item count in full text index properties?
    2013年2月27日 2:54
  • 能说的具体些吗?我检查过,产生的全文索引目录。 貌似没有问题,同时,我在 企业管理器中用SQL 语句检索“二季度”,也是没有问题。 但是一用前端WEB程序查就出错,但是前端连接SQL server 2005 SP4 查询又没有问题。我选得数据库排序方式是 chinese_prc_ci_SI 。
    2013年2月27日 3:00
  • Did you upgrade sql client on web server?
    2013年2月27日 3:40
  • 我WEB程序是用 visual studio 2003 SP1 下 用C#写的,我后台迁移数据库,貌似需要升级前台的SQL client ?, 再说配置的时候也没有 sql client 的。我是不是需要用visual studio 2008 进行重新编译?

    2013年2月27日 4:06
  • App needs sql connection component to access sql, correct? That's what sql client does.
    2013年2月27日 4:11
  • 我准备在visual studio 2008 上从新编译下程序看看

    2013年2月27日 5:21
  • VS2003用的应该是.NET1.0

    使用的ADO.NET的版本也比较旧,最好使用VS2008重新编译试一下


    给我写信: QQ我:点击这里给我发消息

    2013年2月27日 15:48
  • 问题依然没有得到解决, 今天子啊服务器上安装了 vs2008 + sp1, 然后从新编译,测试,发现,

    DbCommand dbcommand = db.GetSqlStringCommand("select count(*) from articles where IsDeleted=0" + condition);

    IDataReader dr = db.ExecuteReader(dbcommand); ---这一条语句检索“二季度”时没有在规定时间内得到响应。但是我在WEB程序上检索其他关键词又是好的,数据库里,我现在重新生成过 全文检索, 还是一样。

    2013年2月28日 7:26
  • Any msg in sql log? Any connection error in ring buffer?
    2013年2月28日 14:13
  • 日期  2013/3/1 9:36:34
    日志  Windows NT (Application)

    源  ASP.NET 2.0.50727.0
    类别  Web Event
    事件  2147484957
    计算机  WIN-OSNHOR4K813

    消息
    Event code: 3005

    Event message: 发生了未处理的异常。

    Event time: 2013/3/1 9:36:34

    Event time (UTC): 2013/3/1 1:36:34

    Event ID: 58c1f830de1045848c01914af679a33c

    Event sequence: 22

    Event occurrence: 1

    Event detail code: 0

     

    Application information:

        Application domain: /LM/W3SVC/2/ROOT-4-130065753496511150

        Trust level: Full

        Application Virtual Path: /

        Application Path: D:\nbtj0803\

        Machine name: WIN-OSNHOR4K813

     

    Process information:

        Process ID: 1404

        Process name: w3wp.exe

        Account name: IIS APPPOOL\nbtjj

     

    Exception information:

        Exception type: SqlException

        Exception message: 超时时间已到。在操作完成之前超时时间已过或服务器未响应。

     

    Request information:

        Request URL: http://10.33.98.90/search.aspx

        Request path: /search.aspx

        User host address: 10.33.101.123

        User:

        Is authenticated: False

        Authentication Type:

        Thread account name: IIS APPPOOL\nbtjj

     

    Thread information:

        Thread ID: 33

        Thread account name: IIS APPPOOL\nbtjj

        Is impersonating: False

        Stack trace:    在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       在 System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       在 System.Data.SqlClient.SqlDataReader.get_MetaData()
       在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       在 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       在 Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior)
       在 Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command)
       在 nbstats.search.Page_Load(Object sender, EventArgs e) 位置 D:\nbtj0803\search.aspx.cs:行号 127
       在 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       在 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       在 System.Web.UI.Control.OnLoad(EventArgs e)
       在 System.Web.UI.Control.LoadRecursive()
       在 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

     

    Custom event details:-----这个是在SQl 日志里查到的错误。

    using System;
    using System.Data;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using System.Text;
    using System.Data.Common;
    using System.Collections.Specialized;
    using Microsoft.Practices.EnterpriseLibrary.Data;

    namespace nbstats
    {
        public partial class search : System.Web.UI.Page
        {
            Database db = DatabaseFactory.CreateDatabase();
            myfuncs func = new myfuncs();
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!Page.IsPostBack)
                {
                    int p = 1;
                    if (Request.Params["p"] != null)
                    {
                        try
                        {
                            p = int.Parse(Request.Params["p"].ToString());
                        }
                        catch
                        {
                            Literal1.Text = "参数错误!";
                            return;
                          //  System.Web.HttpContext.Current.Response.Write("<script Language=JavaScript>alert('请合理使用查询条件!↓\\n\\n宁波市统计信息网!');window.location.href='/index.aspx';</" + "script>");
                        }
                    }
                    else {

                  //    System.Web.HttpContext.Current.Response.Write("<script Language=JavaScript>alert('请合理使用查询条件!↓\\n\\n宁波市统计信息网!');window.location.href='/index.aspx';</" + "script>");
                    //    Response.Write(p);
                    }


                    string query = "你妈妈的";//设置一个默认查询值,避免错误产生
                    string type = "1";
                    int rowcount = 10;
                    string catagory = "cata_001";
                    string begindate = "";
                    string enddate = "";
                    string condition = " and 1=1";

                    bool sqlIn = checksql(Request.Form, Request.QueryString);
                    if (sqlIn)
                    { //Response.Write("存在sql关键字!");
                        System.Web.HttpContext.Current.Response.Write("<script Language=JavaScript>alert('防注入程序提示您,请勿提交非法字符!↓\\n\\n宁波市统计信息网!');window.location.href='/index.aspx';</" + "script>");
                        return;
                    }
                    if (Request["query"] != null)
                    {
                        if (Request["query"].ToString() == "")
                        { //Response.Write("存在sql关键字!");
                            System.Web.HttpContext.Current.Response.Write("<script Language=JavaScript>alert('防注入程序提示您,请合理提交查询字符!↓\\n\\n宁波市统计信息网!');window.location.href='/index.aspx';</" + "script>");
                            return;
                        }

                    }

                    if (Request["query"] != null)
                    {
                        query = Request["query"].ToString().Trim();

                    }
                 
                        if (Request["type"] != null)
                       {
                           type = Request["type"].ToString();
                        }
          
                        if (type == "1") condition += " and (title like '%" + query + "%' or contains(content,'\"*" + query + "*\"'))";
                        else if (type == "2") condition += " and title like '%" + query + "%'";
                        else condition += " and contains(content,'\"*" + query + "*\"')";

                      if (Request["rowcount"] != null)
                        {
                            rowcount = int.Parse(Request["rowcount"].ToString());
                            int r;
                            //对输入值是否是数字进行判断
                            if (!int.TryParse(Request.Params["rowcount"].ToString(), out r))
                            {
                                System.Web.HttpContext.Current.Response.Write("<script Language=JavaScript>alert('防注入程序提示您,请勿提交非法字符!↓\\n\\n宁波市统计信息网!');window.location.href='/index.aspx';</" + "script>");
                                return;
                            }
                        }

                       if (Request["catagory"] != null)
                       {
                           catagory = Request["catagory"].ToString();
                           condition += " and cataid in(" + childids(catagory) + ")";
                        }
        
                       if (Request["begindate"] != null)
                       {
                            DateTime dt;
                            begindate = Request["begindate"].ToString();
                            if (DateTime.TryParse(begindate, out dt))
                            {
                                condition += " and convert(varchar(10),updatetime,120)>='" + dt.ToString("yyyy-MM-dd") + "'";
                            }
                       }

                       if (Request["enddate"] != null)
                       {
                            DateTime dt;
                            enddate = Request["enddate"].ToString();
                            if (DateTime.TryParse(enddate, out dt))
                            {
                                condition += " and convert(varchar(10),updatetime,120)<='" + dt.ToString("yyyy-MM-dd") + "'";
                            }
                        }

                        //分页导航
                        DbCommand dbcommand = db.GetSqlStringCommand("select count(*) from articles where IsDeleted=0" + condition);

                        IDataReader dr = db.ExecuteReader(dbcommand);
                        int resultcount = 0;
                        if (dr.Read())
                        {
                           resultcount = int.Parse(dr[0].ToString());
                            if (p > 1)
                            {
                                Literal2.Text = "<a href=\"search.aspx?query=" + Server.UrlEncode(query) + "&type=" + type + "&rowcount=" + rowcount.ToString() + "&catagory=" + catagory + "&begindate=" + begindate + "&enddate=" + enddate + "&p=" + (p - 1).ToString() + "\"><<上一页</a>&nbsp;&nbsp;";
                            }
                            if (int.Parse(dr[0].ToString()) > rowcount * p)
                            {
                                Literal2.Text += "<a href=\"search.aspx?query=" + Server.UrlEncode(query) + "&type=" + type + "&rowcount=" + rowcount.ToString() + "&catagory=" + catagory + "&begindate=" + begindate + "&enddate=" + enddate + "&p=" + (p + 1).ToString() + "\">下一页>></a>";
                            }
                        }
                        dr.Close();

                        if (resultcount > 0)
                       {
                            //新闻列表
                           StringBuilder sql = new StringBuilder();
                           sql.AppendFormat("select top {0} * from articles where IsDeleted=0 and id not in(select top {1} id from articles where 1=1 {2} order by createtime desc) {2} order by createtime desc",
                                rowcount, rowcount * (p - 1), condition);//修改updatetime 为createtime

                            string tempstr = "<table cellspacing=\"0\" cellpadding=\"0\" width=\"625\" border=\"0\">\n";
                            dbcommand = db.GetSqlStringCommand(sql.ToString());
                            dr = db.ExecuteReader(dbcommand);
                         while (dr.Read())
                          {
                              string cataname = "";
                               int catalength = 0;
                               dbcommand = db.GetSqlStringCommand("select * from article_type where id=@id");
                               db.AddInParameter(dbcommand, "@id", DbType.String, dr["cataid"].ToString());
                               IDataReader tmp = db.ExecuteReader(dbcommand);
                              if (tmp.Read())
                              {
                                 cataname = "[<a href=\"/read/board.aspx?id=" + tmp["id"].ToString().Substring(5, 3) + "\" target=\"_blank\">" + tmp["name"].ToString() + "</a>]";
                                 catalength = System.Text.Encoding.Default.GetBytes(tmp["name"].ToString()).Length + 2;
                                }
                              tmp.Close();

                              tempstr += "<tr>\n";
                              tempstr += "<td width=\"550\" height=\"25\" align=\"left\">·" + cataname + "<a href=\"/read/" + DateTime.Parse(dr["updatetime"].ToString()).ToString("yyyyMMdd") + "/" + dr["id"].ToString() + ".aspx\" target=\"_blank\">" + func.highlightkeyword_title(func.SetStrForLength(dr["title"].ToString(), 76 - catalength), query) + "</a></td>\n";
                              tempstr += "<td align=\"center\" width=\"75\">[" + DateTime.Parse(dr["createtime"].ToString()).ToString("yyyy-MM-dd") + "]</td></tr>\n";
                              tempstr += "<tr><td colspan=\"2\">\n";
                              tempstr += "<table cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" border=\"0\">\n";
                               tempstr += "<tr><td width=\"20\"></td><td>\n";
                              tempstr += "<span style=\"color: #666666\">" + func.highlistkeyword_contect(dr["content"].ToString(), query) + "</span>\n";
                              tempstr += "</td></tr></table>\n";
                              tempstr += "</td></tr>\n";
                             tempstr += "<tr><td colspan=\"2\" height=\"10\"></td></tr>\n";
                           }
                            dr.Close();

                            tempstr += "</table>\n";
                            Literal1.Text = tempstr;
                        }
                        else
                        {
                            string tempstr = "<table cellspacing=\"0\" cellpadding=\"0\" width=\"625\" border=\"0\">\n";
                           tempstr += "<tr><td width=\"625\" height=\"25\" align=\"left\">·没有找到相应信息!</td></tr>";
                            tempstr += "</table>\n";
                           Literal1.Text = tempstr;
                        }

                        //页面路径
                        Literal5.Text = "<a href=\"/index.aspx\">首页</a> - 站内搜索";

                        //热点搜索
                        Literal3.Text = "<script type=\"text/javascript\" src=\"/js/hot5.aspx?query=" + Server.UrlEncode(query) + "&type=" + type + "&rowcount=" + rowcount.ToString() + "&catagory=" + catagory + "&begindate=" + begindate + "&enddate=" + enddate + "\"></script>";

                        //热点推荐
                        Literal4.Text = "<script type=\"text/javascript\" src=\"/js/hot2.aspx\"></script>";
                  
               
                     }

              
            }

            private string childids(string id)
            {
                string s = "'" + id + "'";
                DbCommand dbcommand = db.GetSqlStringCommand("select * from article_type where parentid=@parentid");
                db.AddInParameter(dbcommand, "@parentid", DbType.String, id);
                IDataReader dr = db.ExecuteReader(dbcommand);
                while (dr.Read())
                {
                    s += "," + childids(dr["id"].ToString());
                }
                dr.Close();

                return s;
            }
           //SQL 监测
            public bool checksql(NameValueCollection FormNV, NameValueCollection QueryNV)
            {
                string Fy_getIn, Fy_postIn;
                int i;
                bool SqlIn = false;
               // Fy_getIn = "#|exec|insert|select|delete|update|%|chr|mid|master|truncate|declare|*|;|$|@|||<|>|'|(|)|+|CR|LF|,|.|script|document|eval|\"|exec|insert|select|delete|update|master|truncate|declare ";//get方法要过滤的
               // Fy_postIn = "exec|insert|select|delete|update|master|truncate|declare|*|;|$|@|||<|>|'|(|)|+|CR|LF|,|.|script|document|eval|\"#|exec|insert|select|delete|update|%|chr|mid|master|truncate|declare|*|;|$|@|||<|>|'|(|)|+|CR|LF|,|.|script|document|eval|\"  ";//post方法要过滤的
               // Fy_getIn = "#|insert|select|from|update|declare|exec|char|mid|master|truncate|declare|*|;|$|<|>|'|,|+|lf|document|eval|\"|@|cr|.|script|+|drop|";//get方法要过滤的
                Fy_postIn = "#|insert|select|from|update|declare|exec|char|mid|master|truncate|declare|*|;|$|<|>|'|+|lf|document|eval|\"|@|cr|.|script|+|drop|";//get方法要过滤的
                Fy_getIn = "#|insert|select|from|update|declare|exec|char|mid|master|truncate|declare|*|;|$|<|>|'|+|lf|document|eval|\"|@|cr|.|script|+|drop|";//post方法要过滤的
                   string[] Fy_postInf = Fy_postIn.Split('|');//分离攻击post字符
                   string[] Fy_getInf = Fy_getIn.Split('|');//分离攻击get字符
                //=============================post=================
                if (FormNV.Count != 0)
                {  //增加大小判断
                    foreach (string Fy_Post in FormNV.Keys)
                    {
                        for (i = 0; i < Fy_postInf.GetUpperBound(0); i++)//判断是否为sql注入单词
                            if (FormNV[Fy_Post].ToLower().IndexOf(Fy_postInf[i].ToLower()) != -1)
                            {
                                SqlIn = true; break;//POST方法存在SQL关键字
                            }
                    }
                }
                //================get====
                if (QueryNV.Count != 0 && !SqlIn)
                {
                    foreach (string Fy_Get in QueryNV.Keys)
                    { //增加大小判断
                        for (i = 0; i < Fy_getInf.GetUpperBound(0); i++)//判断是否为sql注入单词,如果是则保存攻击信息
                            if (QueryNV[Fy_Get].ToLower().IndexOf(Fy_getInf[i].ToLower()) != -1)
                            {
                                SqlIn = true; break;//GET方法存在SQL关键字
                            }
                    }
                }
                return SqlIn;
            }
        }
    }-----这个是我程序的源代码? 你有QQ或者 MSN? 能直接寻求你的帮助?

    2013年3月1日 1:54
  • 想问一下LZ使用的EnterpriseLibrary 用的是不是最新版本


    给我写信: QQ我:点击这里给我发消息

    2013年3月1日 3:20
  • 网上说这个应该是微软的问题

    给LZ两个网址参考:

    (事件类型: 警告;事件代码:3005)的解惑

    事件代码: 3005 问题


    给我写信: QQ我:点击这里给我发消息

    2013年3月1日 3:25