Get Next Number: page

Showing posts with label page. Show all posts

Wednesday, March 21, 2012

Front Page 2002 and SQL Query

I am using a very basic query in Front Page 2002 against a SQL table in
SQL 2000

SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)

and f_docnumber is a search form field.

When I run I get

Description: The precision is invalid.
Number: -2147467259 (0x80004005)

The field is defined in the table as numberic 9(10,0)

Any ideas?(webforcam@.hotmail.com) writes:
> I am using a very basic query in Front Page 2002 against a SQL table in
> SQL 2000
> SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)
> and f_docnumber is a search form field.
> When I run I get
> Description: The precision is invalid.
> Number: -2147467259 (0x80004005)
> The field is defined in the table as numberic 9(10,0)

I have used FrontPage to author the articles on my web site, but I
have ot idea how you query databases from the tool. So maybe you ask
this question in a FrontPage newsgroup. That much I can say is that
the error does not come SQL Server, but from the OLE DB provider.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

front end for cubes

currently i use MSInsight as my cubes front end. its an asp page that users can build reports from cubes. its pretty old, and since ive started upgrading to AS2005, it doesnt work properly, leading me to think that it is not compatiable with AS2005 cubes. so im wondering, whats a good front end that can be used by many users? this rules excel out, as i need a front end that can be used my many users.

I can't speak to whether it's good, as I haven't used it, but here's one:

http://www.radarcube.com/products/radaraspnet_msas.aspx

their WinForms OLAP viewer control looked decent when I evaluated it a couple months ago.

|||

There's a lot out there to choose from; see

http://www.mosha.com/msolap/util.htm

as a good starting point. What kind of front end are you after?

You should also check out what's coming in the medium term with PerformancePoint, which is based in part on the technology that Microsoft acquired from Proclarity last year. See

http://office.microsoft.com/en-us/performancepoint/FX101680481033.aspx

Regards,

Chris

from view inserts the value of the last inserting at page reload a second time

Hi folks,

After getting a form view inserting some values into a mdb file, it inserts the same values a second time on page reload.

How may I cure this? Any suggestions?

VB Code is below.

many thanks in advance

Rosi

1<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="km_Eingabe.aspx.vb"Inherits="km_Eingabe" title="km-Eingabe" %>2<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">3 <table style="width: 750px; height: 210px">4 <tr>5 <td style="height: 38px">6 </td>7 <td style="height: 38px">8 <asp:DropDownList ID="dropdownlist1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource2"9 DataTextField="polKennz" DataValueField="polKennz">10 </asp:DropDownList>11 </td>12 <td style="height: 38px">13 </td>14 </tr>15 <tr>16 <td style="height: 235px">17 </td>18 <td style="height: 235px" valign="top">19   20 <asp:FormView ID="FormView1" runat="server" CellPadding="4" DataSourceID="SqlDataSource1"21 ForeColor="#333333">22 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />23 <EditRowStyle BackColor="#999999" />24 <EditItemTemplate>2526 </EditItemTemplate>27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />29 <EmptyDataTemplate>30 keine Daten vorhanden31 <br />32 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>33 </EmptyDataTemplate>34 <InsertItemTemplate>35 Datum:36 <asp:TextBox ID="DatumTextBox" runat="server" Text='<%# Bind("Datum", "{0:d}") %>'>37 </asp:TextBox>38 <br />39 Fahrer:40 <asp:TextBox ID="FahrerTextBox" runat="server" Text='<%# Bind("Fahrer") %>'>41 </asp:TextBox>42 <br />43 polKennz:44 <asp:TextBox ID="polKennzTextBox" runat="server" Text='<%# Bind("polKennz") %>'>45 </asp:TextBox>46 <br />47 neuer_Eintrag:48 <asp:TextBox ID="neuer_EintragTextBox" runat="server" Text='<%# Bind("neu") %>'></asp:TextBox>49 <br />50 aktuell:51 <asp:TextBox ID="aktuellTextBox" runat="server" Text='<%# Bind("aktuell") %>'></asp:TextBox> <br />52 <br />53 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"54 Text="Einfügen" OnClick="InsertButton_Click">55 </asp:LinkButton> 56 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"57 Text="Abbrechen">58 </asp:LinkButton>59 </InsertItemTemplate>60 <ItemTemplate>61 Datum:62 <asp:Label ID="DatumLabel" runat="server" Text='<%# Bind("Datum") %>'></asp:Label><br />63 Fahrer:64 <asp:Label ID="FahrerLabel" runat="server" Text='<%# Bind("Fahrer") %>'></asp:Label><br />65 polKennz:66 <asp:Label ID="polKennzLabel" runat="server" Text='<%# Bind("polKennz") %>'></asp:Label><br />67 neu:68 <asp:Label ID="neuLabel" runat="server" Text='<%# Bind("neu") %>'></asp:Label><br />69 lfdNr:70 <asp:Label ID="lfdNrLabel" runat="server" Text='<%# Eval("lfdNr") %>'></asp:Label><br />71 aktuell:72 <asp:Label ID="aktuellLabel" runat="server" Text='<%# Bind("aktuell") %>'></asp:Label><br />73 Dienststelle:74 <asp:Label ID="DienststelleLabel" runat="server" Text='<%# Bind("Dienststelle") %>'></asp:Label><br />75 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>76 </ItemTemplate>77 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />78 </asp:FormView>79 </td>80 <td style="height: 235px">81 </td>82 </tr>83 <tr>84 <td>85 </td>86 <td>87 </td>88 <td>89 </td>90 </tr>91 </table>92<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"93 SelectCommand="SELECT DISTINCT [polKennz] FROM [qry_KennzeichenAlle_ohne_ausgesondert]">94 </asp:AccessDataSource>95 <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"96 SelectCommand="SELECT DISTINCT [Datum], [Nutzer], [Fahrer], [polKennz], [aktuell], [neu], [gefahren] FROM [qry_Fahrtenbuch_letzter_Eintrag_pro_Kfz] WHERE ([polKennz] = ?)">97 <SelectParameters>98 <asp:ControlParameter ControlID="DropDownList1" Name="polKennz" PropertyName="SelectedValue"99 Type="String" />100 </SelectParameters>101 </asp:AccessDataSource>102 <asp:SqlDataSource ID="SqlDataSource1" DataSourceMode="DataSet" ConflictDetection="CompareAllValues" InsertCommandType="Text" runat="server" ConnectionString="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString %>" ProviderName="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString.ProviderName %>"103 InsertCommand="INSERT INTO Tab_import_Fahrtenbuch([Datum], [Fahrer], [polKennz], [neu], [aktuell]) VALUES (@.Datum, @.Fahrer, @.polKennz, @.Eingabe_neu, @.Eingabe_aktuell )" >104 <SelectParameters>105 <asp:ControlParameter ControlID="dropdownlist1" Name="newparameter" PropertyName="SelectedValue" />106 </SelectParameters>107 <InsertParameters>108 <asp:FormParameter FormField="DatumTextBox" Name="Datum" Type="string" />109 <asp:FormParameter FormField="FahrerTextBox" Name="Fahrer" Type="string" />110 <asp:FormParameter FormField="polKennzTextBox" Name="polKennz" Type="string" />111 <asp:FormParameter FormField="neuer_EintragTextBox" Name="neu" Type="Int32" ConvertEmptyStringToNull="false" />112 <asp:FormParameter FormField="aktuellTextBox" Name="aktuell" Type="Int32" ConvertEmptyStringToNull="false" Direction="Input" />113 </InsertParameters>114 </asp:SqlDataSource>115     116</asp:Content>117118

Hi Rosi,

It seems that the code has been re-executed when the page re-loads. So you may use "IsPostBack" property to provent the re-execute phenomenon.

If this does not answer your question, pls feel free to reply. Thanks！

|||

HiNai-Dong Jin

Thanks for your answer.

I'm doing my first steps with this stuff. How to use that "IsPostBack"?

Many thanks for any suggestions.

Rosi

|||

Hi Rosi,

Can you show me the km_Eingabe.aspx.vb(code-behind file) ?

Thanks!

|||

RosiRonja:

Hi folks,

After getting a form view inserting some values into a mdb file, it inserts the same values a second time on page reload.

How may I cure this? Any suggestions?

VB Code is below.

many thanks in advance

Rosi

1<%@. Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="km_Eingabe.aspx.vb"Inherits="km_Eingabe" title="km-Eingabe" %>
2<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
3 <table style="width: 750px; height: 210px">
4 <tr>
5 <td style="height: 38px">
6 </td>
7 <td style="height: 38px">
8 <asp:DropDownList ID="dropdownlist1" runat="server" AutoPostBack="True" DataSourceID="AccessDataSource2"
9 DataTextField="polKennz" DataValueField="polKennz">
10 </asp:DropDownList>
11 </td>
12 <td style="height: 38px">
13 </td>
14 </tr>
15 <tr>
16 <td style="height: 235px">
17 </td>
18 <td style="height: 235px" valign="top">
19   
20 <asp:FormView ID="FormView1" runat="server" CellPadding="4" DataSourceID="SqlDataSource1"
21 ForeColor="#333333">
22 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
23 <EditRowStyle BackColor="#999999" />
24 <EditItemTemplate>
25
26 </EditItemTemplate>
27 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
28 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
29 <EmptyDataTemplate>
30 keine Daten vorhanden
31 <br />
32 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>
33 </EmptyDataTemplate>
34 <InsertItemTemplate>
35 Datum:
36 <asp:TextBox ID="DatumTextBox" runat="server" Text='<%# Bind("Datum", "{0:d}") %>'>
37 </asp:TextBox>
38 <br />
39 Fahrer:
40 <asp:TextBox ID="FahrerTextBox" runat="server" Text='<%# Bind("Fahrer") %>'>
41 </asp:TextBox>
42 <br />
43 polKennz:
44 <asp:TextBox ID="polKennzTextBox" runat="server" Text='<%# Bind("polKennz") %>'>
45 </asp:TextBox>
46 <br />
47 neuer_Eintrag:
48 <asp:TextBox ID="neuer_EintragTextBox" runat="server" Text='<%# Bind("neu") %>'></asp:TextBox>
49 <br />
50 aktuell:
51 <asp:TextBox ID="aktuellTextBox" runat="server" Text='<%# Bind("aktuell") %>'></asp:TextBox> <br />
52 <br />
53 <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
54 Text="Einfügen" OnClick="InsertButton_Click">
55 </asp:LinkButton> 
56 <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
57 Text="Abbrechen">
58 </asp:LinkButton>
59 </InsertItemTemplate>
60 <ItemTemplate>
61 Datum:
62 <asp:Label ID="DatumLabel" runat="server" Text='<%# Bind("Datum") %>'></asp:Label><br />
63 Fahrer:
64 <asp:Label ID="FahrerLabel" runat="server" Text='<%# Bind("Fahrer") %>'></asp:Label><br />
65 polKennz:
66 <asp:Label ID="polKennzLabel" runat="server" Text='<%# Bind("polKennz") %>'></asp:Label><br />
67 neu:
68 <asp:Label ID="neuLabel" runat="server" Text='<%# Bind("neu") %>'></asp:Label><br />
69 lfdNr:
70 <asp:Label ID="lfdNrLabel" runat="server" Text='<%# Eval("lfdNr") %>'></asp:Label><br />
71 aktuell:
72 <asp:Label ID="aktuellLabel" runat="server" Text='<%# Bind("aktuell") %>'></asp:Label><br />
73 Dienststelle:
74 <asp:Label ID="DienststelleLabel" runat="server" Text='<%# Bind("Dienststelle") %>'></asp:Label><br />
75 <asp:LinkButton ID="NewButton" runat="server" CommandName="New" Text="Neuer Eintrag"></asp:LinkButton>
76 </ItemTemplate>
77 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
78 </asp:FormView>
79 </td>
80 <td style="height: 235px">
81 </td>
82 </tr>
83 <tr>
84 <td>
85 </td>
86 <td>
87 </td>
88 <td>
89 </td>
90 </tr>
91 </table>
92<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"
93 SelectCommand="SELECT DISTINCT [polKennz] FROM [qry_KennzeichenAlle_ohne_ausgesondert]">
94 </asp:AccessDataSource>
95 <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/KfzDaten_Ansicht.mdb"
96 SelectCommand="SELECT DISTINCT [Datum], [Nutzer], [Fahrer], [polKennz], [aktuell], [neu], [gefahren] FROM [qry_Fahrtenbuch_letzter_Eintrag_pro_Kfz] WHERE ([polKennz] = ?)">
97 <SelectParameters>
98 <asp:ControlParameter ControlID="DropDownList1" Name="polKennz" PropertyName="SelectedValue"
99 Type="String" />
100 </SelectParameters>
101 </asp:AccessDataSource>
102 <asp:SqlDataSource ID="SqlDataSource1" DataSourceMode="DataSet" ConflictDetection="CompareAllValues" InsertCommandType="Text" runat="server" ConnectionString="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString %>" ProviderName="<%$ ConnectionStrings:KfzDaten_Ansicht_mdbConnectionString.ProviderName %>"
103 InsertCommand="INSERT INTO Tab_import_Fahrtenbuch([Datum], [Fahrer], [polKennz], [neu], [aktuell]) VALUES (@.Datum, @.Fahrer, @.polKennz, @.Eingabe_neu, @.Eingabe_aktuell )" >
104 <SelectParameters>
105 <asp:ControlParameter ControlID="dropdownlist1" Name="newparameter" PropertyName="SelectedValue" />
106 </SelectParameters>
107 <InsertParameters>
108 <asp:FormParameter FormField="DatumTextBox" Name="Datum" Type="string" />
109 <asp:FormParameter FormField="FahrerTextBox" Name="Fahrer" Type="string" />
110 <asp:FormParameter FormField="polKennzTextBox" Name="polKennz" Type="string" />
111 <asp:FormParameter FormField="neuer_EintragTextBox" Name="neu" Type="Int32" ConvertEmptyStringToNull="false" />
112 <asp:FormParameter FormField="aktuellTextBox" Name="aktuell" Type="Int32" ConvertEmptyStringToNull="false" Direction="Input" />
113 </InsertParameters>
114 </asp:SqlDataSource>
115     
116</asp:Content>
117
118

try to remove your cache,
<meta http-equiv="Cache-Control" content="no-cache"/>

here is the way to of ispost back

If Page.IsPostBack Then
/// place business logic

else

End If

|||

Hi folks

I did it with Response.Redirect("km_Eingabe.aspx")

It works well that way.

1Protected Sub SqlDataSource1_Inserted(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Inserted2 Response.Redirect("km_Eingabe.aspx")34End Sub

Thekm_Eingabe.aspx.vb (code behind file)in not more then this and works.

Either way I'll try the  other solutions (cache and postback) too.

What did you think is the best way to do?

Many Thanks

Rosi

sql

Monday, March 19, 2012

From MySQL to MSSQL

Hi, all,
I have a simple jsp page which conneced to mysql database with no problem, but
after I changed to production database server, MSSQL, I got nothing returned,
nothing displayed on web browser. I did changed jdbc drive, etc., please help.
Thanks in advance.
For example:
in MySQL:
20040301 010203
in MSSQL
<%@. page contentType="text/html; charset=UTF-8" language="java"%>
<%@. page import="java.sql.*" %>
<%
String connectionURL =
"jdbc:microsoft:sqlserver://DBServer:1433/Tracking;User=user;Password=pw";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
String sSQLString="SELECT * FROM Tracking";
%>
<html>
<head>
<title>
Report
</title>
</head>
<body bgcolor="#ffffc0">
<hr>
<table width="100%" border="1">
<% try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver").newInstance();
connection = DriverManager.getConnection(connectionURL);
statement = connection.createStatement();
rs = statement.executeQuery(sSQLString);
while (rs.next()) { %>
<tr>
<td><tt><% out.print("FromDate"); %></tt></td>
<td><tt><% out.print("FromTime"); %></td>
</tr>
<% }
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (rs != null) rs.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} %>
</table>
</body>
</html>
Check your connection string. You are specifying the server name and port
number, but is Tracking supposed to be a named instance or your database
name? If it is supposed to be a named instance, then you need to use
something like the following:
String connectionURL =
"jdbc:microsoft:sqlserver://DBServer\\Tracking;User=user;Password=pw";
or
String connectionURL =
"jdbc:microsoft:sqlserver://DBServer:xxxx;User=user;Password=pw";
where "xxxx" is the port of the Tracking instance. If Tracking is supposed
to be your database name, then you need to specify "DatabaseName=Tracking;"
somewhere in your connection string.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| NNTP-Posting-Date: Tue, 30 Mar 2004 23:19:43 -0600
| From: Jason <jhsu8999@.yahoo.com>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: From MySQL to MSSQL
| Date: Tue, 30 Mar 2004 22:21:36 -0700
| Message-ID: <o7lk60loka08go7pm9al9utlqv8nlmb8nb@.4ax.com>
| X-Newsreader: Forte Agent 2.0/32.640
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Lines: 63
| NNTP-Posting-Host: 67.161.247.249
| X-Trace:
sv3-6XJo0FwryFfK+LFOSREhLrCcGZ+gMm/b2KdpOiD28o2vJX0FScRdwjqwqv9Brbe8pNEmK9fe
wv5hG1t!8LtCcuWLwevoq0HwtKMPyWl0BjtkH9ET1C4yeP4WyG jJdEdXfEAQgaTlk2TtdUmMBJYn
/iIOTA==
| X-Complaints-To: abuse@.comcast.net
| X-DMCA-Complaints-To: dmca@.comcast.net
| X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
| X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your
complaint properly
| X-Postfilter: 1.1
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
m!border1.nntp.ash.giganews.com!border2.nntp.sjc.g iganews.com!border1.nntp.s
jc.giganews.com!nntp.giganews.com!local1.nntp.sjc. giganews.com!nntp.comcast.
com!news.comcast.com.POSTED!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:5821
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi, all,
| I have a simple jsp page which conneced to mysql database with no
problem, but
| after I changed to production database server, MSSQL, I got nothing
returned,
| nothing displayed on web browser. I did changed jdbc drive, etc., please
help.
| Thanks in advance.
|
| For example:
| in MySQL:
| --
| 20040301 010203
|
| in MSSQL
| --
|
|
| <%@. page contentType="text/html; charset=UTF-8" language="java"%>
| <%@. page import="java.sql.*" %>
|
| <%
| String connectionURL =
| "jdbc:microsoft:sqlserver://DBServer:1433/Tracking;User=user;Password=pw";
| Connection connection = null;
| Statement statement = null;
| ResultSet rs = null;
|
| String sSQLString="SELECT * FROM Tracking";
| %>
|
| <html>
| <head>
| <title>
| Report
| </title>
| </head>
| <body bgcolor="#ffffc0">
| <hr>
|
| <table width="100%" border="1">
| <% try {
|
|
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver").newInstance();
|
| connection = DriverManager.getConnection(connectionURL);
| statement = connection.createStatement();
| rs = statement.executeQuery(sSQLString);
|
| while (rs.next()) { %>
| <tr>
| <td><tt><% out.print("FromDate"); %></tt></td>
| <td><tt><% out.print("FromTime"); %></td>
| </tr>
| <% }
| } catch (Exception ex) {
| ex.printStackTrace();
| } finally {
| if (rs != null) rs.close();
| if (statement != null) statement.close();
| if (connection != null) connection.close();
| } %>
| </table>
| </body>
| </html>
|
|

Friday, March 9, 2012

Freeze Report Header

Is there a way to freeze the report header so when a user is looking at the
report, on the web, and they scroll down the page that they can always see
the header?
Thanks,
Jasonthis is not yet acheived by microsoft but it is to be done by them (as
they said so ) in any next version wait till that|||Yes, fixed headers will be available in SQL Server 2005 Reporting Services.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"** Spirits **" <muhammadhammad78@.gmail.com> wrote in message
news:1113385952.494987.10490@.z14g2000cwz.googlegroups.com...
> this is not yet acheived by microsoft but it is to be done by them (as
> they said so ) in any next version wait till that
>

Wednesday, March 7, 2012

Freetext box + SQL

I have a vb page, which is a simple front end so I can edit text from anywhere.

The person who is going to use it knows no html, and so I am trying to get freetextbox to work.

I am getting the following error:
Server Error in '/' Application.
------------------------

ExecuteNonQuery: CommandText property has not been initialized
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized

Source Error:

Line 81:
Line 82: mySelectCmd.Connection.Open()
Line 83: mySelectCmd.ExecuteNonQuery()
Line 84: MyConnString.Close()
Line 85:

The pages code is below:


<%@. Page Language="VB" Debug="True" validateRequest="False"%>
<%@. Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As StringSub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
ReadMyData()
end if
end sub
Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable
dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))
mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()
dr = dt.NewRow()
dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")
dt.Rows.Add(dr)
End While
Finally
myReader.Close()
MyConnString.Close()
End Try
Pubs.DataSource = DT
Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
myExecuteQuery= "Update content_text Set title=@.title, text=@.text, section_no=@.section_no where id=@.id"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
dim e_id as integer
dim e_title, e_text, e_section_no as string
e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), TextBox).Text
mySelectCmd.Connection.Open()
mySelectCmd.ExecuteNonQuery()
MyConnString.Close()
pubs.EditItemIndex = -1
ReadMyData()
End Sub
</script>
<html>
<head>
<title>Untitled Document</title
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}
-->
</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%" align="left">Word</td>
 <td width="15%" align="left">Title</td>
 <td width="70%" align="left">Text</td>
 <td width="10%" align="left">Section Number</td>
 </tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?src=images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
 <td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
 <td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
 <td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
 </tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?src=images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
 <td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
 <td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
 <td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
 </tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html

You do this:

myExecuteQuery= "Update content_text Set title=@.title, text=@.text, section_no=@.section_no where id=@.id"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)

Note you set myExecuteQuery, but you then pass mySelectQuery to the SqlCommand constructor. I strongly suggest using Option Explicit On, sinc ein this case I think it would have caught your error.|||Stupid mistake!

anyways, made that change and now get this error

Must declare the variable '@.title'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@.title'.

Any idea's?

<code>
<%@. Page Language="VB" Debug="True" validateRequest="False"%>
<%@. Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As String
dim myExecuteCmd As SqlCommand

Sub Page_Load(Sender As Object, E As EventArgs)

If Not Page.IsPostBack Then
ReadMyData()
end if
end sub

Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")

Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable

dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))

mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()

dr = dt.NewRow()

dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")

dt.Rows.Add(dr)

End While
Finally
myReader.Close()
MyConnString.Close()
End Try

Pubs.DataSource = DT
Pubs.Databind()

End Sub

Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub

Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub

Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)

MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")

myExecuteQuery= "Update content_text Set title=@.title, text=@.text, section_no=@.section_no where id=@.id"
myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)

dim e_id as integer
dim e_title, e_text, e_section_no as string

e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), TextBox).Text

mySelectCmd.Connection.Open()
mySelectCmd.ExecuteNonQuery()
MyConnString.Close()

pubs.EditItemIndex = -1

ReadMyData()

End Sub
</script>
<html>
<head>
<title>Untitled Document</title
<style type="text/css">

</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left">Word</td>
<td width="15%" align="left">Title</td>
<td width="70%" align="left">Text</td>
<td width="10%" align="left">Section Number</td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?src=images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
<td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?src=images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
<td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html>
</code|||You need to set @.title, etc. parameters.

Here is some information on parameters|||Thanks for that, made those changes, but now getting an error as below, which is strange as the code is similar to what I used to add entries

Sorry to be a pain!

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30456: 'Text' is not a member of 'String'.

Source Error:

Line 74: myExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
Line 75: myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
Line 76: myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
Line 77: myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
Line 78: myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)


<%@. Page Language="VB" Debug="True" validateRequest="False"%>
<%@. Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As String
dim myExecuteCmd As SqlCommandSub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
ReadMyData()
end if
end sub
Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable
dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))
mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()
dr = dt.NewRow()
dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")
dt.Rows.Add(dr)
End While
Finally
myReader.Close()
MyConnString.Close()
End Try
Pubs.DataSource = DT
Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
dim e_id as integer
dim e_title, e_text, e_section_no as string
myExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)
e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), e_TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), e_FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), e_TextBox).Text
myExecuteCmd.Connection.Open()
myExecuteCmd.ExecuteNonQuery()
MyConnString.Close()
pubs.EditItemIndex = -1
ReadMyData()
End Sub
</script>
<html>
<head>
<title>Untitled Document</title
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}
-->
</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%" align="left">Word</td>
 <td width="15%" align="left">Title</td>
 <td width="70%" align="left">Text</td>
 <td width="10%" align="left">Section Number</td>
 </tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="http://pics.10026.com/?src=images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="http://pics.10026.com/?src=images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
 <td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
 <td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
 <td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
 </tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
 <tr>
 <td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="http://pics.10026.com/?src=images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="http://pics.10026.com/?src=images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
 <td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
 <td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
 <td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
 </tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html

|||Well, what do YOU think that error means?

Look at this code:


dim e_title, e_text, e_section_no as stringmyExecuteQuery= "Update content_text Set (title, text, section_no) values (@.title, @.text, @.section_no) where id=@.id"
myExecuteCmd = New SqlCommand( myExecuteQuery, MyConnString)
myExecuteCmd.Parameters.Add( "@.title", e_Title.Text )
myExecuteCmd.Parameters.Add( "@.text", e_Text.Text )
myExecuteCmd.Parameters.Add( "@.section_no", e_Section_no.Text)

e_title is a string, and so, look at the docs for string. Do you see a .Text property of string? No, you do not. So, the error message was telling you EXACTLY what the problem is. You can just pass e_Title, since that is a string and that is what you are passing as the @.Title.

Sunday, February 26, 2012

Free space on an index

On SQL 2000, how would you determine:
1. the percent free on an index?
2. the available page size?
Thanks
Message posted via http://www.sqlmonster.com
Indexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:44a0ef20a81f45cb9afa95ac557ba389@.SQLMonster.c om...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.sqlmonster.com
|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
Message posted via http://www.sqlmonster.com

Free space on an index

On SQL 2000, how would you determine:
1. the percent free on an index?
2. the available page size?
Thanks
--
Message posted via http://www.sqlmonster.comIndexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:44a0ef20a81f45cb9afa95ac557ba389@.SQLMonster.com...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.sqlmonster.com|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
--
Message posted via http://www.sqlmonster.com

Free space on an index

On SQL 2000, how would you determine:
1. the percent free on an index?
2. the available page size?
Thanks
Message posted via http://www.droptable.comIndexes don't have "free space" - when pages need to be added to the index,
they are allocated and linked in.
In SQL Server the page size is always the same, 8KB. If you're referring to
the available free space on a particular page, there isn't any documented
way to do this. Perhaps you're talking about FILLFACTOR? You can specify a
FILLFACTOR for index creation that will leave a percentage of space on the
leaf level index pages free, but it is only maintained at index creation
time, not during normal operations. You can use INDEXPROPERTY (<table>,
<index>, 'IndexFillFactor') to get this value...
If I'm misunderstanding your questions, please let me know.
Thanks.
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:44a0ef20a81f45cb9afa95ac557ba389@.SQ
droptable.com...
> On SQL 2000, how would you determine:
> 1. the percent free on an index?
> 2. the available page size?
> Thanks
> --
> Message posted via http://www.droptable.com|||Okay. Thanks for getting me straight.
What is happening is I have inserts into a table that are timing out, but
updates on the table are proceeding. Is there a possibility of the index
size growing, or new pages being added to the index that may be causing
these insert timeouts?
Message posted via http://www.droptable.com

Free Reporting software

Hi,

are there any free module to export an HTML page to PDF ?

Thanks

I forgot to mention, that I also need a module to export a table like gridview or something like that to an excel.

Sunday, February 19, 2012

Fragmented Indexes

I have a query to find all indexes that are fragmented over 10%. The query
also excludes any where the page count is less than 8 and the index size is
smaller than 64k. Immediately after running my maintenance script that
reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
returns quite a few fragmented indexes. I've also insured that all tables
have a clustered index (most of them on an identity column). Why do I still
show fragmented indexes? Is there some reason they all won't defragment?
Any help is greatly appreciated!Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
calculated, things like extent order etc. As the index gets to some realistic size, you will not be
affected by this "misreport".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>I have a query to find all indexes that are fragmented over 10%. The query
> also excludes any where the page count is less than 8 and the index size is
> smaller than 64k. Immediately after running my maintenance script that
> reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> returns quite a few fragmented indexes. I've also insured that all tables
> have a clustered index (most of them on an identity column). Why do I still
> show fragmented indexes? Is there some reason they all won't defragment?
> Any help is greatly appreciated!|||Thanks for your quick response! Any chance you have a link to a page that
shows the 1000 pages MS recommends? Any other MS recommendation pages?
"Tibor Karaszi" wrote:
> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
> calculated, things like extent order etc. As the index gets to some realistic size, you will not be
> affected by this "misreport".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >I have a query to find all indexes that are fragmented over 10%. The query
> > also excludes any where the page count is less than 8 and the index size is
> > smaller than 64k. Immediately after running my maintenance script that
> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> > returns quite a few fragmented indexes. I've also insured that all tables
> > have a clustered index (most of them on an identity column). Why do I still
> > show fragmented indexes? Is there some reason they all won't defragment?
> > Any help is greatly appreciated!
>|||Below might have to 1000 pages recommendation:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> Thanks for your quick response! Any chance you have a link to a page that
> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>
> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation
>> is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will not
>> be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!|||Or then again, you might check this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It specifically says:
Generally, you should not be concerned with fragmentation levels of indexes
with less than 1,000 pages. In the tests, indexes containing more than
10,000 pages realized performance gains, with the biggest gains on indexes
with significantly more pages (greater than 50,000 pages).
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Below might have to 1000 pages recommendation:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is due
>> to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do I
>> > still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>|||Thank you both for your input! I'll change my maintenance script, and I'm
sure you've just saved us several thousand reindexing jobs that aren't
necessary on a weekly basis! Thanks again!
"Kalen Delaney" wrote:
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes
> with less than 1,000 pages. In the tests, indexes containing more than
> 10,000 pages realized performance gains, with the biggest gains on indexes
> with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Below might have to 1000 pages recommendation:
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tony" <Tony@.discussions.microsoft.com> wrote in message
> > news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> >> Thanks for your quick response! Any chance you have a link to a page
> >> that
> >> shows the 1000 pages MS recommends? Any other MS recommendation pages?
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Ignore indexes with less than at least 500 pages (MS recommends 1000
> >> pages). You can, with only two
> >> extents, still have reported fragmentation after a rebuild. This is due
> >> to the way fragmentation is
> >> calculated, things like extent order etc. As the index gets to some
> >> realistic size, you will not be
> >> affected by this "misreport".
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >> >I have a query to find all indexes that are fragmented over 10%. The
> >> >query
> >> > also excludes any where the page count is less than 8 and the index
> >> > size is
> >> > smaller than 64k. Immediately after running my maintenance script
> >> > that
> >> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
> >> > query still
> >> > returns quite a few fragmented indexes. I've also insured that all
> >> > tables
> >> > have a clustered index (most of them on an identity column). Why do I
> >> > still
> >> > show fragmented indexes? Is there some reason they all won't
> >> > defragment?
> >> > Any help is greatly appreciated!
> >>
> >
>
>|||Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I have no idea why I posted
an URL to the I/O basic paper...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000
> pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with
> the biggest gains on indexes with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way
>> fragmentation is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will
>> not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!
>>
>|||You probably have them all in a list, just like I do, and you grabbed the
wrong one. I just made absolutely sure that I grabbed the right one!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23JZtwKMeIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I
> have no idea why I posted an URL to the I/O basic paper...
> :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
>> Or then again, you might check this paper:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> It specifically says:
>> Generally, you should not be concerned with fragmentation levels of
>> indexes with less than 1,000 pages. In the tests, indexes containing more
>> than 10,000 pages realized performance gains, with the biggest gains on
>> indexes with significantly more pages (greater than 50,000 pages).
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is
>> due to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do
>> > I still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>>
>>
>

Fragmented Indexes

fragmentation question

Hi friends,
Is it possible that over any period of time page split occur thus increasing
logical fragmentation, but Avg. Page density whis is shown in DBCC
SHOWCONTIG remains relatively at the same level ? In another words, what is
exactly relationship between those two?
Thanks in advance
AlexAlex
Read this article
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"Alex" <me@.isp.net> wrote in message news:9Q9If.17$Nr5.1@.clgrps13...
> Hi friends,
> Is it possible that over any period of time page split occur thus
> increasing logical fragmentation, but Avg. Page density whis is shown in
> DBCC SHOWCONTIG remains relatively at the same level ? In another words,
> what is exactly relationship between those two?
> Thanks in advance
> Alex
>|||Yes. Imagine you start with 75% full pages. As a page becomes full, it is split onto two half full
pages. So, assuming the data is spread evenly across the pages, you will end up with the middle of
100% and 50% full pages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <me@.isp.net> wrote in message news:9Q9If.17$Nr5.1@.clgrps13...
> Hi friends,
> Is it possible that over any period of time page split occur thus increasing logical
> fragmentation, but Avg. Page density whis is shown in DBCC SHOWCONTIG remains relatively at the
> same level ? In another words, what is exactly relationship between those two?
> Thanks in advance
> Alex
>|||Thanks Uri
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23GZtGOTMGHA.1536@.TK2MSFTNGP11.phx.gbl...
> Alex
> Read this article
> http://www.sql-server-performance.com/rd_index_fragmentation.asp
>
>
> "Alex" <me@.isp.net> wrote in message news:9Q9If.17$Nr5.1@.clgrps13...
>> Hi friends,
>> Is it possible that over any period of time page split occur thus
>> increasing logical fragmentation, but Avg. Page density whis is shown in
>> DBCC SHOWCONTIG remains relatively at the same level ? In another words,
>> what is exactly relationship between those two?
>> Thanks in advance
>> Alex
>>
>|||That means that table can have Avg Page Density(or internal fragmentation)
within desirable range (say 88-92) being at the same time somewhat heavily
defragmented (so called external fragmentattion in terms of the article Uri
refered) when logical fragmentation is way more than 10, right?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTYgJVUMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Yes. Imagine you start with 75% full pages. As a page becomes full, it is
> split onto two half full pages. So, assuming the data is spread evenly
> across the pages, you will end up with the middle of 100% and 50% full
> pages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alex" <me@.isp.net> wrote in message news:9Q9If.17$Nr5.1@.clgrps13...
>> Hi friends,
>> Is it possible that over any period of time page split occur thus
>> increasing logical fragmentation, but Avg. Page density whis is shown in
>> DBCC SHOWCONTIG remains relatively at the same level ? In another words,
>> what is exactly relationship between those two?
>> Thanks in advance
>> Alex
>>
>|||Yes. "Internal fragmentation" (how full your pages are) basically has no relationship with external
fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <me@.isp.net> wrote in message news:aSoIf.1213$n67.425@.edtnps89...
> That means that table can have Avg Page Density(or internal fragmentation) within desirable range
> (say 88-92) being at the same time somewhat heavily defragmented (so called external
> fragmentattion in terms of the article Uri refered) when logical fragmentation is way more than
> 10, right?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uTYgJVUMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>> Yes. Imagine you start with 75% full pages. As a page becomes full, it is split onto two half
>> full pages. So, assuming the data is spread evenly across the pages, you will end up with the
>> middle of 100% and 50% full pages.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Alex" <me@.isp.net> wrote in message news:9Q9If.17$Nr5.1@.clgrps13...
>> Hi friends,
>> Is it possible that over any period of time page split occur thus increasing logical
>> fragmentation, but Avg. Page density whis is shown in DBCC SHOWCONTIG remains relatively at the
>> same level ? In another words, what is exactly relationship between those two?
>> Thanks in advance
>> Alex
>>
>|||thanks you guys for clarification, - you confirmed my thoughts.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OUBlLCZMGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Yes. "Internal fragmentation" (how full your pages are) basically has no
> relationship with external fragmentation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alex" <me@.isp.net> wrote in message news:aSoIf.1213$n67.425@.edtnps89...
>> That means that table can have Avg Page Density(or internal
>> fragmentation) within desirable range (say 88-92) being at the same time
>> somewhat heavily defragmented (so called external fragmentattion in terms
>> of the article Uri refered) when logical fragmentation is way more than
>> 10, right?
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uTYgJVUMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>> Yes. Imagine you start with 75% full pages. As a page becomes full, it
>> is split onto two half full pages. So, assuming the data is spread
>> evenly across the pages, you will end up with the middle of 100% and 50%
>> full pages.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Alex" <me@.isp.net> wrote in message news:9Q9If.17$Nr5.1@.clgrps13...
>> Hi friends,
>> Is it possible that over any period of time page split occur thus
>> increasing logical fragmentation, but Avg. Page density whis is shown
>> in DBCC SHOWCONTIG remains relatively at the same level ? In another
>> words, what is exactly relationship between those two?
>> Thanks in advance
>> Alex
>>
>>
>

Wednesday, March 21, 2012

Front Page 2002 and SQL Query

front end for cubes

from view inserts the value of the last inserting at page reload a second time

Monday, March 19, 2012

From MySQL to MSSQL

Friday, March 9, 2012

Freeze Report Header

Wednesday, March 7, 2012

Freetext box + SQL

Sunday, February 26, 2012

Free space on an index

Free space on an index

Free space on an index

Free Reporting software

Sunday, February 19, 2012

Fragmented Indexes

Fragmented Indexes

fragmentation question

fragmentation question

fragmentation question

Get Next Number

Blog Archive

About Me