Estimated Query Plan

Hi,

I am writing a client application that shows estimated queries plans and statistics. I know how to obtain estimated plans by using SQL Server Management Studio. But is it possible to obtain by using database functions?

I have found sys.dm_exec_query_plan, but it seems that this function can only be used for executed (or executing) queries...

Thanks

SET SHOWPLAN_XML ON

go

exec YourSP

go

SET SHOWPLAN_XML OFF

go

|||

I do not want to obtain the execution plan for a stored procedure. I do want to use a stored procedure (that may be invoked by a client application) to obtain the execution plan for a user-submmitted query. (It is just like SQL Manager Studio does).

Thanks

Estimate Log Space for Alter Table Statement

My client's website database is hosted by a third party. I need to alter one of the column definitions for the largest table in the database. Unfortunately, the transaction log fills up if I try to alter the table. I've done all the usual stuff like truncating the log, etc., but the simple fact is that the operation requires more log space than we have available. Therefore, we need to purchase additional disk space for the database.

What I'm looking for is a way to roughly estimate how much log space will be required to alter this table so that we purchase enough but not too much additional space. The table has an identity primary key and 4 other single column indexes: one int, one datetime and two varchar(30) columns.

Any suggestions? Thanks in advance.

You can change the truncate log setting in a database if you don't need it to recover in time of emergency it is a very complicated subject so run a search for truncate log on checkpoint option for your database setting in the BOL(books online). The links below are basic guidelines from Microsoft. Hope this helps.

http://support.microsoft.com/kb/873235

http://msdn2.microsoft.com/en-us/library/aa933068(SQL.80).aspx

|||

If I remember correctly when you alter column in table, SQL server renames old one, created new table with new structure and next moves data into new table. So in your case you need at least amount of space your data table takes for log because all process has to be done at one shot.
But maybe you can do it yourself: create table with new structure and move data from old table to new table with quantity like 100 or 1000 records in one shot? Log file will be small and you should succeed. You only have to take care about identity insert if you have one in your table.

Thanks

|||

Thanks jpazgier, that's a great suggestion! I usually do the alter using Enterprise Manager directly for this client but I have scripted this type of change for other clients. I never thought about taking the generated script and breaking up the insert part of it into multiple inserts and performing the truncate log after each insert. That would reduce the log space required.

If I don't delete the old records as I go, I still need twice the data just for the old and new copies but I wouldn't need so much log space. If I trust my alterations, then I could delete the old records as they get moved into the new copy and really reduce the free space required. Not as safe but probably sufficient.

Thanks

|||

(1) You could keep the DB in simple mode while you do the data transfer.

(2) You can also have a job running to truncate the log during the transfer and let the job run every 1 minute, so you dont have to do it yourself manually.

(3) You can use bcp/Bulk Insert to transfer the data.

Errors when I use the "Delete" function

I've been trying to build a data table that allows a client to add, edit and delete his itinerary. I've been using the products table in Northwind to get up to speed on this functions and everything works fine - (ie-connects to the database, shows the data I need, allows me to edit the data) but when I try to use the delete function, I keep getting the error below.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.
The statement has been terminated.

Can anybody help me with this problem?

thanx

Code used is below:

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Gigs.aspx.cs"Inherits="Gigs" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @.ProductID"InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@.ProductName, @.SupplierID, @.CategoryID, @.QuantityPerUnit, @.UnitPrice, @.UnitsInStock, @.UnitsOnOrder, @.ReorderLevel, @.Discontinued)"SelectCommand="SELECT * FROM [Products]"UpdateCommand="UPDATE [Products] SET [ProductName] = @.ProductName, [SupplierID] = @.SupplierID, [CategoryID] = @.CategoryID, [QuantityPerUnit] = @.QuantityPerUnit, [UnitPrice] = @.UnitPrice, [UnitsInStock] = @.UnitsInStock, [UnitsOnOrder] = @.UnitsOnOrder, [ReorderLevel] = @.ReorderLevel, [Discontinued] = @.Discontinued WHERE [ProductID] = @.ProductID"><DeleteParameters><asp:ParameterName="ProductID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/><asp:ParameterName="ProductID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ProductName"Type="String"/><asp:ParameterName="SupplierID"Type="Int32"/><asp:ParameterName="CategoryID"Type="Int32"/><asp:ParameterName="QuantityPerUnit"Type="String"/><asp:ParameterName="UnitPrice"Type="Decimal"/><asp:ParameterName="UnitsInStock"Type="Int16"/><asp:ParameterName="UnitsOnOrder"Type="Int16"/><asp:ParameterName="ReorderLevel"Type="Int16"/><asp:ParameterName="Discontinued"Type="Boolean"/></InsertParameters></asp:SqlDataSource>

</div><asp:GridViewID="GridView1"runat="server"AllowPaging="True"AutoGenerateColumns="False"CellPadding="4"DataKeyNames="ProductID"DataSourceID="SqlDataSource1"ForeColor="#333333"GridLines="None"><FooterStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><Columns><asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="ProductName"HeaderText="ProductName"SortExpression="ProductName"/><asp:BoundFieldDataField="SupplierID"HeaderText="SupplierID"SortExpression="SupplierID"/><asp:BoundFieldDataField="CategoryID"HeaderText="CategoryID"SortExpression="CategoryID"/><asp:BoundFieldDataField="QuantityPerUnit"HeaderText="QuantityPerUnit"SortExpression="QuantityPerUnit"/><asp:BoundFieldDataField="UnitPrice"HeaderText="UnitPrice"SortExpression="UnitPrice"/><asp:BoundFieldDataField="UnitsInStock"HeaderText="UnitsInStock"SortExpression="UnitsInStock"/><asp:BoundFieldDataField="UnitsOnOrder"HeaderText="UnitsOnOrder"SortExpression="UnitsOnOrder"/><asp:BoundFieldDataField="ReorderLevel"HeaderText="ReorderLevel"SortExpression="ReorderLevel"/><asp:CheckBoxFieldDataField="Discontinued"HeaderText="Discontinued"SortExpression="Discontinued"/></Columns><RowStyleBackColor="#FFFBD6"ForeColor="#333333"/><SelectedRowStyleBackColor="#FFCC66"Font-Bold="True"ForeColor="Navy"/><PagerStyleBackColor="#FFCC66"ForeColor="#333333"HorizontalAlign="Center"/><HeaderStyleBackColor="#990000"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"/></asp:GridView></form>

</

body>

</

html>

aspx.cs code used below:

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;

public

partialclassGigs : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

}

}

Hello my friend,

Your code is fine. It is not allowing you to delete the product from the product table because a related record also exists in the Order Details table. If you want to delete the product and its related records in the other table, go to the relationship in the database and choose the "Cascade Delete Related Records" option.

However, I would not do it this way. If I buy something from your website I will have a record in the Order Details table that links back to a product. If you delete this product and it cascades to the Order Details table, the record of my order is also lost. Instead of deleting products, create a field called "Active" in the product table that will contain either 1 or 0. Then configure your website to only show products with Active=1. A benefit of this approach is that you can always set something back to Active if you make a mistake.

Kind regards

Scotty

|||

Looks like product ID is used as foreign Key in some orders and you can not delete product as long as this reference exists, to delete record from product table you have to delete all orders in which it is included or remove connection between Order details and products table.

But I think that in case like this you should display message to user : You can not delete this product because it was used in some orders!!!

errors setting up SQL Mail

I get the following error when I test my SQL Mail configuration. MS Outlook is the default mail client so I'm not sure what else could be wrong:

" Error 18030 : xp_test_mapi_profile : Either there is no default mail
client or the current mail client cannot fullfill the messaging request.
Please run Microsoft outlook and set it as the default mail client"

Thanks.
JoeI know there was a bug with the length of a profile - how long is your profile ? Also, have you started the mail service using xp_startmail ? When are you receiving this message ?|||From Ent Mgr / Support Services / SQL Mail / properties I type in what I think is the profile (DBA). (I have set up a mail account in Outlook called DBA.) I get the error when I press the TEST button.

What mail service are you referring to and how do I knkow if it's running?|||More info:

When I bring up the SQL Mail/propertes I should see a drop down menu with all the profiles listed. I don't see anything listed even though I have created a profile. This appears to be my problem - why isn't SQL Mail recognizing the profile I created in Outlook?

I can send and receive email thru Outlook just fine.|||I just bounced the MSSQLServer service and the following profile appeared in the drop down list in SQL Mail/propertes: "Microsoft Outlook Internet Settings". The test button then worked. I'm not sure why the DBA profile doesn't appear but I think I may be ok.

Errors deploying and running reports

I am developing reports on my PC and deploying them to our server. My
PC only has reporting services client components while our server has
the client and server components installed.
Problem 1 :
when I deploy the solution to the server I get this error message:
"The underlying connection was closed: Could not establish trust
relationship with remote server."
Problem 2:
I have created a ASP.NET application on my PC that runs reports on the
server using the reporting service's web service. When I try and
render a report, I get this error message:
The request failed with HTTP status 404: Not Found.
Any help would really be appreciated
CraigIs your report server configured to use SSL? If so, you will need to use
FQDN (full domain name) of
the server in the report project's TargetServerURL property.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Craig HB" <craighb@.bigfoot.com> wrote in message
news:3ba2292b.0407130651.71bf5d0c@.posting.google.com...
> I am developing reports on my PC and deploying them to our server. My
> PC only has reporting services client components while our server has
> the client and server components installed.
> Problem 1 :
> when I deploy the solution to the server I get this error message:
> "The underlying connection was closed: Could not establish trust
> relationship with remote server."
> Problem 2:
> I have created a ASP.NET application on my PC that runs reports on the
> server using the reporting service's web service. When I try and
> render a report, I get this error message:
> The request failed with HTTP status 404: Not Found.
> Any help would really be appreciated
> Craig