Problems in Report Presentation

Hello:
We are running SQL Server 2000 RS. We are just starting to deploy reports
and we are running into a strange problem.
We have given users access to their reports via various Windows security
groups. When an Sysadmin runs a report, it's runs fine and displays
everything in a timely fashion.
When a regular user runs a report, there is a significant delay while the
Report is Being Generated icon spins. Finally, the report displays but some
graphics are not displayed and are replaced by a box with a red X.
This behavior is consistent on any user who is not a sysadmin.
Any suggestions on how to correct this problem would be appreciated.
ThanksBrennan,
Do the images exist on a location where the group to which the user's
belong does not have permission?
I bet if you set the permission on the image to 'Everyone'/Anonymous
you'll get the images in the report.
Andy Potter

Problems connecting to SSAS instance over HTTP in Excel 2003

Our Biz users are all running Excel 2003, and we've recently set up a bunch of cubes that we'd like to open up to them.

I followed the steps here: http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx
to set up HTTP access to SSAS2005. It works great from my machine, I figured because I had SQL 05 installed locally and all the requisite drivers, etc.

On our biz users' desktops, however, it wasn't as peachy.

I first figured out I had to install MSXML6 and the SSAS 9.0 driver on their machine to connect to the cube. I downloaded those from here:

http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Once I was able to set up the connection to the cube, and about to create a pivot table in Excel, the Query wizard failed, with this error:

"XML parsing failed at line 1, column 9: DTD is prohibited"

Googling didn't seem to uncover anything useful, has anyone seen this before?

Not sure how google works, but using Windows Live Search (www.live.com) discovered the following thread:

http://www.topxml.com/MS-XML-Analysis/rn-132023_HTTP-XML/A-access-to-AS2005-cause-an-error.aspx

In the thread user Jeje attached the HTTP trace, which is pretty helpful - it shows that first client components tried to use 9.0 driver, but then falled back to 8.0 driver, which is probably the reason for the error. Why did it fall back to 8.0 - perhaps a permission issue ?

|||Whoops, now I feel bad for not using Live . I'll have to be sure to use both now!

Mosha - security sounds like it could be a probable cause. I'll try experimenting with permission issues tomorrow. Also, on a related thread in the same forum a MS employee noted:

Sent date: 01/06/2006
From: "Akshai Mirchandani [MS]" <(email address - cut out)>
Message:
This is a known issue -- a hotfix should be available soon from support.

Thanks,

Akshai

(http://cc.msnscache.com/cache.aspx?q=4937159748319&lang=en-US&mkt=en-US&FORM=CVRE2)

Are you aware of any related hotfixes that address this issue?|||Argh, I tried completely loosening security on the olap/ folder and its children, but this doesn't seem to help at all. I checked our IIS logs and like in Mosha's link the client seems to be attempting the 9.0 driver and then falling back to the 8.0 driver, which of course doesn't work.

Anyone know if there's a hotfix for this?|||Interesting. Changing IIS auth to anonymous seems like it completely solves this problem...although I'm unsure of the security implications -

Client -> IIS (msmdpump.dll) -> SSAS

If IIS is not using windows auth, does SSAS just use the anonymous user's credentials (ie the IUSR_ acct) to authenticate?|||

If you run Profiler against the Analysis Server, you can see which user attempted to connect -- if it was the IUSR_xxx or similar user then you know it is a security issue and the Pump is not able to delegate the user credentials to the Analysis Server for some reason.

I believe the hotfix was about using Basic authentication which doesn't seem to be your scenario.

Thanks,

Akshai

|||

Hi all,

I've also been encountering issues with HTTP access to SSAS 2005, but with excel 2007.

Although it's working from excel 2003 (but only if the option to save the username/password is checked when creating the data source for the pivot table), it won't let me access it from excel 2007. I always get an access denied-like message when i try to set up the connection to the data source.

We haven't been able to identify where is the issue. We set the HTTP access as via the link mentionned before, but that won't let us access from excel 2007.

We've also been facing some backward compatibility issues using excel 2003 with OLE DB provider for analysis services 9.0 installed.

With it we can not connect to our SSAS 2000 cubes (again via http) anymore, the error is the response returned via the HTTP server is not valid (even though we try using the OPAL 8.0 driver).

Any idea how we could solve that issue? did we miss a component to install to ensure compatibility with both version of SSAS?

thanks in advance for your help.

Cheers, and happy new year!

sql

Problem with updating only one row in a SQL 2005 database

hi,

I have a problem with updating a row in my database. I don't know how to update and i need help.

I have a form where the users select a category and a sub category to enter a message.

I have two dropdown lists, one is for selecting the category and the other is for the sub-category.

In my database for the categories and the sub-categories, i have a variable (Quantity) where i keep the quantity for each table.

I just want to be able to increment this variable by 1 (for the selected category and sub-category) when a user enter a new message...

The insert procedure is working well and have no problem with it.

I use VWD 2005 Express with SQL 2005 express and i code in VB.

HELP!!!!!

Please post your table structure, some sample data and the update you are expecting to happen. Also the query you currently have would help.

|||

ok

Table Categories
------
CategoryID / int / Primary key
CategoryName / varchar(50)
Description / varchar (50)
Quantity / int

Table SubCategories
------
SubCategoryID / int / Primary key
CategoryID / int / Foreign key, linked to Table Categories-CategoryID
SubCategoryName / varchar(50)
Description / varchar (50)
Quantity / int

So when a user post a message, i want that the Quantity for table Categories and SubCategories increment by 1.

Portion of my code :
-------

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="SousCategoryName" DataValueField="SubCategoryID" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [SubCategoryID], [SubCategoryName] FROM [SousCategories] WHERE ([CategoryID] = @.CategoryID) ORDER BY [SubCategoryName]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||

With corrections!

----------

ok

Table Categories
------
CategoryID / int / Primary key
CategoryName / varchar(50)
Description / varchar (50)
Quantity / int

Table SubCategories
------
SubCategoryID / int / Primary key
CategoryID / int / Foreign key, linked to Table Categories-CategoryID
SubCategoryName / varchar(50)
Description / varchar (50)
Quantity / int

So when a user post a message, i want that the Quantity for table Categories and SubCategories increment by 1.

Portion of my code :
-------

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"
DataTextField="CategoryName" DataValueField="CategoryID">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
</asp:SqlDataSource>

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"
DataTextField="SubCategoryName" DataValueField="SubCategoryID" AutoPostBack="True">
</asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:databaseDB %>"
SelectCommand="SELECT [SubCategoryID], [SubCategoryName] FROM [SubCategories] WHERE ([CategoryID] = @.CategoryID) ORDER BY [SubCategoryName]">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

|||So any ideas?|||

Hi,

You can create a button_click event in your code-behind page. The following code (c#) is just a sample which can achieve your purpose.

protected void Button1_Click(object sender, EventArgs e) {/// Get the CategoryName value from DropDownList1string CategoryName =this.DropDownList1.SelectedItem.Value;/// Get the CategoryName value from DropDownList2string SubCategoryName =this.DropDownList2.SelectedItem.Value;/// in this sample,we get the message and the username value from textboxesstring Message =this.TextBox1.Text;string User=this.TextBox2.Text;/// connstr is the name of connection string you set in Web.configstring conn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; SqlConnection myconn =new SqlConnection(conn);/// write the message which user submit into YourTablestring insert_command ="Insert into YourTable (CategoryName,SubCategoryName,Message,Users) values('"+CategoryName+"','"+SubCategoryName+"','"+Message+"','"+User+"')"; myconn.Open(); SqlCommand mycomm =new SqlCommand(insert_command, myconn); mycomm.ExecuteNonQuery(); mycomm.Dispose();/// sql for updating the Quantity number in the Categories tablestring up_cate ="update Categories set Quantity = (Quantity+1) where CategoryName = '" + CategoryName +"'";/// sql for updating the Quantity number in the SubCategories tablestring up_subcate ="update SubCategories set Quantity = (Quantity+1) where SubCategoryName ='" + SubCategoryName +"'"; SqlCommand mycomm_1 =new SqlCommand(up_cate, myconn); SqlCommand mycomm_2 =new SqlCommand(up_subcate, myconn); mycomm_1.ExecuteNonQuery(); mycomm_2.ExecuteNonQuery(); mycomm_1.Dispose(); mycomm_2.Dispose(); myconn.Close(); }

Hope it helps. Thanks.|||

Sorry i code in VB...

I tried that and it's not working (on a click event) :

Dim myConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("FouinerDB").ConnectionString
Dim myConnectionDB As New Data.SqlClient.SqlConnection(myConnection)
Dim myDA As New Data.SqlClient.SqlDataAdapter()

Dim CatID As Integer = ddlCategories.SelectedValue
Dim SubCatID As Integer = ddlSousCategories.SelectedValue

myDA.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity=10 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)

|||

(1) Please use parameterized queries to prevent SQL injeection attacks.

I would recommend you do it via stored procedure as follows:

Update CategoriesSET Quantity = Quantity + 1WHERE Categoryid = @.CategoryidUpdate SubCategoriesSET Quantity = Quantity + 1WHERE SubCategories = @.SubCategoriesand Categoryid = @.Categoryid

If you had to do it via adhoc T-SQL, you'd be making 2 trips once for each Update. Through a stored proc, you can do it all in one trip.

|||Ok, but how do you call the stored procedure in the vb code.

I dont know nothing about it... ;-(|||I tired that for stored procedure and when i try it (execute), it doesn't change anything in my database (i also tried to change @.Categoryid and @.SousCategoriesID by values that i know are valid ) :

ALTER PROCEDURE

dbo.UpdateQuantity/*(@.parameter1 int = 5,@.parameter2 datatype OUTPUT)*/

AS

/* SET NOCOUNT ON */RETURN

BEGIN

DECLARE@.CategoryidintDECLARE@.SousCategoriesIDint

UPDATE

CategoriesSETQuantity = Quantity + 1WHERECategoryID = @.Categoryid

UPDATE

SousCategoriesSETQuantity = Quantity + 1WHERESousCategoriesID = @.SousCategoriesID

END

|||I don't understand why aSelectCommand works in my page and not anUpdateCommand...

Works!!!
myDA.SelectCommand =

New Data.SqlClient.SqlCommand("SELECT * FROM Annonces WHERE SousCategoryID = " & SubCatID.ToString(), myConnectionDB)

Dont works!!!
myDA.UpdateCommand =New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity = 10 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)
|||

Ok i finally got it!!!

It's theUpdateCommand.ExecuteNonQuery() that i missed. ;-)

I will also try with a Stored Procedure... See you soon!
-----------

Dim myConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("DatabaseDB").ConnectionString
Dim myConnectionDB As New Data.SqlClient.SqlConnection(myConnection)
myConnectionDB.Open()
Dim myDA1 As New Data.SqlClient.SqlDataAdapter()
Dim myDA2 As New Data.SqlClient.SqlDataAdapter()

Dim CatID As Integer = ddlCategories.SelectedValue
Dim SubCatID As Integer = ddlSousCategories.SelectedValue

myDA1.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE Categories SET Quantity = Quantity + 1 WHERE CategoryID = " & CatID.ToString(), myConnectionDB)
myDA2.UpdateCommand = New Data.SqlClient.SqlCommand("UPDATE SousCategories SET Quantity = Quantity + 1 WHERE SousCategoriesID = " & SubCatID.ToString(), myConnectionDB)
myDA1.UpdateCommand.ExecuteNonQuery()
myDA2.UpdateCommand.ExecuteNonQuery()
myConnectionDB.Close()

|||

It would be more like this: The parameters values would come from your VB application. For your update on SubCategories I also added the CategoryId= @.CategoryID condition because I assumed that the same subcategory could be present for multiple categories. So its important to also specify the Category level.

ALTER PROCEDURE dbo.UpdateQuantity ( @.Categoryidint , @.SousCategoriesIDinT)ASBeginSET NOCOUNT ON UPDATE CategoriesSET Quantity = Quantity + 1WHERE CategoryID = @.CategoryidUPDATE SousCategoriesSET Quantity = Quantity + 1WHERE SousCategoriesID = @.SousCategoriesIDAnd CategoryID = @.CategoryidSET NOCOUNT OFFEND

Your VB code could look like this:

Dim myCommandAs SqlCommandDim myParamAs SqlParametermyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="updateQuantity"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.Categoryid",SqlDbType.int))myCommand.Parameters("@.Categoryid").Value = 10myCommand.Parameters.Add(New SqlParameter("@.SousCategoriesID",SqlDbType.int))myCommand.Parameters("@.SousCategoriesID").Value = 1TryIf objCon.State = 0Then objCon.Open()mycommand.ExecuteNonQuery()Catch excAs ExceptionResponse.Write(exc)FinallyIf objCon.State = ConnectionState.OpenThen objCon.Close()End IfEnd Try

|||Hi,

In terms of process speed, wich one is the fastest?

Hard coding or Stored Procedure?|||

Whether you use T-SQL/stored proc the actual statements are executed at the DB. IF you had multiple T-SQL statements to be executed you are better off using stored procs as you can do it all in one trip. Also, if you have T-SQL all over your app it becomes a maintenance nightmare if your underlying schema changes in future. Stored procs provide a channelized way of accesssing the data. Also, for security reasons procs are a better choice. You'd have to give SELECT/UPDATE/DELETE rights to the userid you are using on the actual table if you use adhoc queries. If you do the same thing via procs, you just give the user permission to execute the proc. There are tons of other pros vs cons. You can also google (adhoc query vs stored proc) and see some good debate about this.