OLAP and ASP.NEt

Hi,
I am looking at deploying an ASP.NET site which accesses an OLAP database - Microsoft Analysis Services
My question is how is this normally done:
Straight SQL / Stored Procedures3rd party Adhoc pluginAnything else
If there is anyone out there with experience in using ASP.NET andAnalysis Services I would be interested in hearing how they did it -
Database ArchitectureSystem Architecture
and anypitfalls etc...
Thanks in Advance
Jerry

Following article will provide you some initial thoughts;

http://www.aspfree.com/c/a/MS-SQL-Server/Accessing-OLAP-using-ASP-dot-NET/

|||Hey Jerry,
No stored procedures or SQL for OLAP databases.
We used Office Web COmponents which generates the multi-dimensional queries (MDX) for the user. The architecture is similar to the approach in the following article:
Build an OLAP Reporting App in ASP.NET Using SQL Server 2000 Analysis Services and Office XP
http://msdn.microsoft.com/msdnmag/issues/03/10/OLAP/default.aspx

Offiline synchro

Hi everyone,

I am working on a web site / back end for a food delivery service in the UK. I have designed a web site using ASP and a MS SQL server and am now designing the back end for office use in MS Access. I have linked the SQL tables into access for the app to read directly.

My client has informed me that he wants the back end to function without internet connectivity - so that he can still view existing orders if the net goes down.

I have been pondering the best way to do this and have come up with 2 solutions - either duplicate all the tables locally in access and write an import routine to copy data periodically, or set up a copy of SQL server in the office to synchronise the data independently and then tell the Access prog to read from it if it cannot access the web.

I would prefer the latter option as its neater and requires less programming, but I have never done this before (or know if its even possible). Does anyone have any opinion on how this problem would be best overcome, and if the latter solution is viable then how I would go about it?

All help is much appreciated, thank you for reading.

MarkYes, this is possible and usually it is easy.

Microsoft SQL Replication will definitely do what you want, but the exact deails will depend on which problems you want it to solve for you and which ones you want to solve for yourself. Without knowing more, I can't give you specifics.

I've got some strong opinions about ways that I'd set this up, but those are based on which problems suit my style... My users and I prefer dealing with nusiance grade problems every week or so as opposed to show-stoppers every year or so, and I don't know of any way to set up two different databases (which are both editable) that won't occaisionally have conflicts.

-PatP

Odd results from query

The environment is ASP.Net accessing the SQL server through IIS.
While, I've been told that .Net does not create a cache the behavior I'm
observing for intensive purposes looks as if there is a cache. I'm at a bit
of a loss to explain this behavior. Also, if I wait five or ten minutes, and
refresh I get the correct value.
I have a stored procdure that returns a series of rows, from which I sum a
particular number.
There is an action which causes another row to be added.
However, when I make this change to the database the change is not returned
in a query immediatly following the update. I have simultaneously run sql
query analyzer and verified that the table was updated correctly.
When the form is first displayed and then shortly after I perfomr the update
the value is recalculated via the following mechanism:
Using the global connection string a new SqlConnection object is built. Then
a new SqlCommand object is built using the new SqlConnection. The command
type is stored procedure. After the SqlCommand is setup (the command text and
parameters filled in), a new SqlDataAdapter is built using the SqlCommand.
The SqlDataAdapter fills a new DataSet, via the Fill method.
When I say that the object is new I mean that literally, as in
SqlCommand cmd = new SqlCommand (ConnectionString);
I would very much like to get the correct result right away.
Thanks,
Dave
I think you should share more details about the nature of your stored proc.
Are any parameters being passed to the procedure? When you say 5 or 10
minutes have passed before it returns the correct sum, are you using the same
active browser window, or are you starting a new browser (thus starting a new
session, and perhaps refreshing the state with the server, thus passing new
parameters to the procedure).
Just a guess - I'm probably way off and you probably already thought of all
that
Brian
"Dave" wrote:

> The environment is ASP.Net accessing the SQL server through IIS.
> While, I've been told that .Net does not create a cache the behavior I'm
> observing for intensive purposes looks as if there is a cache. I'm at a bit
> of a loss to explain this behavior. Also, if I wait five or ten minutes, and
> refresh I get the correct value.
> I have a stored procdure that returns a series of rows, from which I sum a
> particular number.
> There is an action which causes another row to be added.
> However, when I make this change to the database the change is not returned
> in a query immediatly following the update. I have simultaneously run sql
> query analyzer and verified that the table was updated correctly.
> When the form is first displayed and then shortly after I perfomr the update
> the value is recalculated via the following mechanism:
> Using the global connection string a new SqlConnection object is built. Then
> a new SqlCommand object is built using the new SqlConnection. The command
> type is stored procedure. After the SqlCommand is setup (the command text and
> parameters filled in), a new SqlDataAdapter is built using the SqlCommand.
> The SqlDataAdapter fills a new DataSet, via the Fill method.
> When I say that the object is new I mean that literally, as in
> SqlCommand cmd = new SqlCommand (ConnectionString);
> I would very much like to get the correct result right away.
> Thanks,
> Dave
sql

odd NETWORKIO lock problem after move to w2k3

I recently moved a hosted site to a new server, and now am getting NETWORKIO
locks, and am having a hard time resolving the issue.
The site is ASP/SQL Server 2000, with fairly low quality code. The ASP was
generated by Dreamweaver and in many cases copy/pasted from the web.
The site was originally running on a shared hosting web server, with its
database on a seperate shared hosting database server, both running w2k.
Due to the site's traffic, it was moved to a dedicated machine, with web and
database all running on the same w2k3 box, which is when the problem began.
The problem occurs when a particular insert is executed. That insert locks
the table, and then itself is blocked with a wait type of NETWORKIO. The
insert is a simple INSERT INTO (...) VALUES (...). This has a few triggers
to clean data, but nothing huge. The insert blocks many other processes
until ADODB times out, and then everything chugs along. This seems to be an
intermittent issue, sometimes easily reproducible, and other times working
very well. This problem began when the database was moved. ASP connects
using TCP to 127.0.0.1
My main question: Why is that insert getting blocked on NETWORKIO?
The old shared hosting server was a bit more powerful than the new dedicated
box, but would a better server solve deadlock issues?
I've loaded up the performance monitor, and the bandwidth available on
localhost gets pegged out occasionally, but not as frequently as we see this
problem.
I've been googling this problem for about a month and a half, any clues
would be greatly appreciated.try to do an
UPDATE STATISTICS
on the table for which the insert is causing the problem and see.
Hope this helps.|||
"Omnibuzz" wrote:

> try to do an
> UPDATE STATISTICS
> on the table for which the insert is causing the problem and see.
> Hope this helps.
That solved it briefly last w, but didn't fix it when it was reported
again today.
I've setup a maintanence plan to do this every sunday night, using a 10%
sample.
Do I need to use a bigger sample or something?|||"Ryan Davis" wrote:

> This problem began when the database was moved.
Do any other types of queries suffer the same problems, or only just the
inserts? When the database was moved, were the indexes rebuilt, or just plai
n
dropped? Is the log file on the same physical disk with the data files?|||Other queries seem fine, although I'm not sure how thoroughly my client has
tested their site, but there have been no other bug reports from their users
.
The indexes were rebuilt based on using the Index Tuning Wizard against a
trace of about an hours worth of site usage. The old indexes were dropped,
and the new ones put in place. A little later sp_updatestats was run, and
then the indexes really made a difference over the rest of the site.
The log and data files are on the same disk, which is a software RAID(1) dis
k.
"Mark Williams" wrote:

> "Ryan Davis" wrote:
>
> Do any other types of queries suffer the same problems, or only just the
> inserts? When the database was moved, were the indexes rebuilt, or just pl
ain
> dropped? Is the log file on the same physical disk with the data files?
>|||You can do a full scan (sampling 100%) for once and may be reduce the time
interval.
The way I see, sampling of 10 percent will give you the right picture only
for an equally distributed table with less insert operations.
Why don't you try to turn on the autostats for this particular table.
use SP_AUTOSTATS|||autostats are enabled for this table.
"Omnibuzz" wrote:

> You can do a full scan (sampling 100%) for once and may be reduce the time
> interval.
> The way I see, sampling of 10 percent will give you the right picture on
ly
> for an equally distributed table with less insert operations.
> Why don't you try to turn on the autostats for this particular table.
> use SP_AUTOSTATS|||then it might the fragmentation in the file system at the OS level :)|||autostats for that table are now disabled, and I'll run an UPDATE STATISTICS
on a schedule for that table.
One possibility is the index tuning wizard added 10 non-unique
non-clustered indexes, which should be slowing down INSERTs, but they work
consistently in other places in code, so I'm at a loss.
"Omnibuzz" wrote:

> then it might the fragmentation in the file system at the OS level :)
>|||Did you look at the fillfactor for the table and the indexes.
try DBCC showcontig and see the extent and logical framentation.
If its > 10 then do an index defrag.
And then see.

Odd ASP buffer/cache problem?

I am working with code I did not write for a web app that used to run
on an Access database. Due to performance problems I'm trying to move
the app to a different server running SQL Server 2000 SP4, running on
Server 2003 with MDAC 2.8 SP2.
On the new server, one of the queries is failing in an odd way.
Roughly, the query is
select * from a, b where a.1 = b.1 and b.2 = x
This is old fashioned ASP code using ADO.Recordsets. One of the columns
I should be getting from the query is called "Number_Sections".
sql = the query
set cRS = dbCon.exectue( sql )
somevar = cRS("Number_Sections")
cRS("Number_Sections") is returning NULL even though the column in the
database is not null. If I run the query using the SQL Analyer on the
server itself, the column is indeed populated as I expect.
cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
also return NULL.
However, while trying to debug I threw in this code:
For zz = 0 to cRS.Fields.Count-1
response.write cRS.Fields.Item(zz).Name & " = " &
cRS.Fields.Item(zz).value & "<br>"
Next
After running through this loop, which prints out all 47 column names
and values *correctly*, cRS("Number_Sections") suddenly works. Take out
the loop and it stops working again.
This sounds like some kind of buffering problem to me, but I'm not
familiar enough with Microsoft products to know where I should be
looking for some setting to fix the problem. Is it ASP and IIS? Is it
SQL Server? I haven't been able to find any settings that look like
they address this issue and searching the MS Knowledge base hasn't
turned up anything helpful either.
Has anyone else seen this behavior? Can anyone point me in the right
direction to find a more graceful solution that leaving the for loop in
and setting the string to some dummy variable instead of sending it to
the response object?
Thanks.
-SeanStop using SELECT *. Do you really need 47 columns? If this column exists
in both a and b, then either alias it or only include it once. Another
thing you can try is somevar = cRS(n) where n is the 0-based ordinal
position of that column (which will be fun to figure out if you continue to
insist using SELECT *).
I have never seen this issue but without a better example, DDL/sample
data/code and a repro, it's tough to guess at what is happening.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
>I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>|||First, what you are describing sounds like an ASP coding issue, and not a
SQL Server issue. You would have better luck posting on an ASP newsgroup.
However, if you post more of your code, starting with where you define
dbCon(less the actual connection string of course), and ending with closing
your connection, folks will be able to offer more advice.
It sounds to me like you are not properly looping through your dataset, but
I can only guess without seeing the actual code.
<usenet@.dezynworks.com> wrote in message
news:1138651333.119837.167610@.f14g2000cwb.googlegroups.com...
> I am working with code I did not write for a web app that used to run
> on an Access database. Due to performance problems I'm trying to move
> the app to a different server running SQL Server 2000 SP4, running on
> Server 2003 with MDAC 2.8 SP2.
> On the new server, one of the queries is failing in an odd way.
> Roughly, the query is
> select * from a, b where a.1 = b.1 and b.2 = x
> This is old fashioned ASP code using ADO.Recordsets. One of the columns
> I should be getting from the query is called "Number_Sections".
> sql = the query
> set cRS = dbCon.exectue( sql )
> somevar = cRS("Number_Sections")
> cRS("Number_Sections") is returning NULL even though the column in the
> database is not null. If I run the query using the SQL Analyer on the
> server itself, the column is indeed populated as I expect.
> cRS.Fields("Number_Sections") and cRS.Fields.Item("Number_Sections")
> also return NULL.
> However, while trying to debug I threw in this code:
> For zz = 0 to cRS.Fields.Count-1
> response.write cRS.Fields.Item(zz).Name & " = " &
> cRS.Fields.Item(zz).value & "<br>"
> Next
> After running through this loop, which prints out all 47 column names
> and values *correctly*, cRS("Number_Sections") suddenly works. Take out
> the loop and it stops working again.
> This sounds like some kind of buffering problem to me, but I'm not
> familiar enough with Microsoft products to know where I should be
> looking for some setting to fix the problem. Is it ASP and IIS? Is it
> SQL Server? I haven't been able to find any settings that look like
> they address this issue and searching the MS Knowledge base hasn't
> turned up anything helpful either.
> Has anyone else seen this behavior? Can anyone point me in the right
> direction to find a more graceful solution that leaving the for loop in
> and setting the string to some dummy variable instead of sending it to
> the response object?
> Thanks.
> -Sean
>

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx
"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David

ODBC vs OLEDB ?

I'm writing a couple asp (vbscript) pages and was wondering which is best,
ODBC or OLEDB ?
Are there any performance differences ?
thx"YTSE" <YTSE@.discussions.microsoft.com> wrote in message
news:E7121F78-D879-496C-A605-3993B477D91F@.microsoft.com...
> I'm writing a couple asp (vbscript) pages and was wondering which is best,
> ODBC or OLEDB ?
> Are there any performance differences ?
From vbscript your options are
VBScript>ADO>OleDB>OleDB Provider for ODBC>SQL Server ODBC driver
or
VBScript>ADO>OleDB>OleDB Provider for SQL Server
So OleDb should perform better since it provides a more direct mapping
between the driver and your client library.
David

ODBC SQL Server error

I have a ASP.Net application. I'm trying to access access data using SQL Server

my web config has

<addkey="connectionString"value="DRIVER={Sql Server};Server=(local);initial catalog=ymquizco_registration;User ID=sa;pwd=test"/>

MY CONNECTION STRING HAS THIS VALUE

"DRIVER={Sql Server};Server=localhost;initial catalog=ymquizco_registration;User ID=sa;pwd=test"

But the conn.Open();is throwing this error:

{"ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'WORK\\ASPNET'.\r\nERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute\r\nERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'WORK\\ASPNET'.\r\nERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute" }

My SQL Server is configured at mixed mode authentication. This seems like a install issue. Can someone think of something.

Fromhttp://www.connectionstrings.com, your connection string should look more like this:
Standard Security:

"Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"