Problems integrating reporting services into a web app

Hello

I have an existing web app on my web server, and reporting services on my app server. I'm trying to integrate the reports into the web app.

The problem is, when the user clicks 'execute report', the app, predictably, falls over, because the aspx page thinks it's on the same server as reporting services, whereas it's not.

Does anybody know how these requests from the aspx page can be redirected to a different server? The way I see it, if I can't get the integration working through url access, I'll have to use the soap api, which will involve dynamically creating drop downs for the report parameters, etc, which is moving away from utilising the advantages that reporting services brings!

Any thoughts would be greatly appreciated.

Thanks in advance
Dominic

Hi Dominic,

this article will help you embed the report in your web application:
http://msdn2.microsoft.com/en-us/library/ms153563.aspx

regards
Helen|||

Thanks for the reply helen. My problem is that in our architecture the client can't access the report server directly. I've decided to go with the reportviewer control instead.

Cheers

Dominic

Problems instancing Reporting Services

I set up Reporting Services as a Web Reference and when I try to instance it, I get a "type expected" error message. Let's say the web reference is named SQLRS. When I do a DIM rs as NEW SQLRS, a blue squiggly appears under SQLRS and it displays the type expected error message.

What am I missing? Anyone know or have a link to an example I could look at? Thanks for any help.Additional information:

When I bring up the Reporting Services as a web reference, I don't get a drop down list of the methods and properties that are listed in the Add a Web Reference dialogue box and from what I've seen in examples online. For instance, there should be a ListChildren method, but I don't see it when intellisense kicks in.

Example: Let's say I name the web service ReportingService. If I try to instance it by going DIM rs as NEW ReportingService, I get the blue squiggly line complaining that "Type expected". If I type ReportingService. intellisense kicks in, but what appears in the drop down box is not what I expect. There are no ListChildren, ListEvents, ListJobs, etc.

What I suspect is that I need to DIM the ReportingService with one of the selections from the intellisense dropdown box, but I have no idea which one as all the examples I've seen online show differently. Has there been changes to this with service pack 2 that I'm not aware of? Any help would be appreciated.|||Ok, I just love answering my own questions.

It turns out that all the examples I've been trying to follow are a bit off. To create an instance of the Reporting Services as a web service, first add the web service as a web reference to your project. (i.e., right click on references in the solution explorer and type in the web address of the reporting services: http://myserver/ReportServer/ReportService.asmx)

Then, to create an instance, use something like:

DIM rs as NEW ReportingService.ReportingService

Now you will have an object called rs that will have the properties and methods of the ReportingService web service such as ListChildren, ListJobs, Render, etc.

Problems installing/accessing SQL server via web

Hello,
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to connect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald Wheeler
What error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I get the standard server not found or access denied error - both via ASP calls and the SQL Server Query analyzer. I don't think that it's an authentication problem because I've tried using both valid and invalid passwords, as well as trying to the connec
t to the Northwind database, and received the same error
Donald
|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 database on a machine that already has a set of active IIS directories for webmail. I think the firewall is ok but the connections are getting 'swallowed' by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler
sql

Problems installing/accessing SQL server via web

Hello,
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to c
onnect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald WheelerWhat error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I get the standard server not found or access denied error - both via ASP ca
lls and the SQL Server Query analyzer. I don't think that it's an authentica
tion problem because I've tried using both valid and invalid passwords, as w
ell as trying to the connec
t to the Northwind database, and received the same error
Donald|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 dat
abase on a machine that already has a set of active IIS directories for webm
ail. I think the firewall is ok but the connections are getting 'swallowed'
by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler

Problems installing SSRS on web server

Hi gang -
I've just recently begun playing around with SQL Server Reporting
Services (SSRS), and I was blown away by it. I had no problem
installing it to my development machine (XP Pro, IIS 6, SQL Server 2000
Dev Ed), but I'm having a lot of trouble setting it up in the
production environment.
I am incredibly frustrated by the documentation on this, and from what
I've seen, no one else has had this problem.
When I'm attempting to install to the first WEB SERVER(IIS 5.0), I get
a warning message during the install that I don't have the Visual
Studio IDE installed (which shouldn't be a problem--I mean, hey, it's a
server, right?). Fine.
The next page or so is the list of which components you want to
install--and Tada, NO SERVER COMPONENTS are in the list. The first time
this happened, I shrugged, and went through the rest of the set up
process. I didn't recieve any of the other setup pages (virtual
directories, SQL Server connection info, etc). It ended up installing a
handful of other dlls (everything under the client components branch
was available for install).
Stumped? I am.
Here's the last little bit of info: I'm attempting to install this
software over a terminal connection. The servers are co-located, and I
was hopeing to install this without taking the neccessary road trip. I
doubt this is the problem, but hey, you never know.
If anyone else has experienced this problem and has a resolution,
please, please let me know.
Thanks much,
Mark S. Milley, MCADI have found a post that had a similar problem until they upgrade to
SP4 on the web server. I'm going to see if that corrects the problem.
In addition, there is no Default Web Site on the server, however, my
symptoms so far are different than what others have experienced. I'll
keep you posted...|||Ignore warning about VS, that is only for development tools, not production.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark Milley - BinarySwitch" <mark.milley@.binaryswitch.com> wrote in message
news:1126879087.836704.12350@.z14g2000cwz.googlegroups.com...
> Hi gang -
> I've just recently begun playing around with SQL Server Reporting
> Services (SSRS), and I was blown away by it. I had no problem
> installing it to my development machine (XP Pro, IIS 6, SQL Server 2000
> Dev Ed), but I'm having a lot of trouble setting it up in the
> production environment.
> I am incredibly frustrated by the documentation on this, and from what
> I've seen, no one else has had this problem.
>
> When I'm attempting to install to the first WEB SERVER(IIS 5.0), I get
> a warning message during the install that I don't have the Visual
> Studio IDE installed (which shouldn't be a problem--I mean, hey, it's a
> server, right?). Fine.
> The next page or so is the list of which components you want to
> install--and Tada, NO SERVER COMPONENTS are in the list. The first time
> this happened, I shrugged, and went through the rest of the set up
> process. I didn't recieve any of the other setup pages (virtual
> directories, SQL Server connection info, etc). It ended up installing a
> handful of other dlls (everything under the client components branch
> was available for install).
> Stumped? I am.
> Here's the last little bit of info: I'm attempting to install this
> software over a terminal connection. The servers are co-located, and I
> was hopeing to install this without taking the neccessary road trip. I
> doubt this is the problem, but hey, you never know.
> If anyone else has experienced this problem and has a resolution,
> please, please let me know.
> Thanks much,
> Mark S. Milley, MCAD
>

Problems connecting to SQL 2005 DB on server

Hi - I have Web Developer, SQL Express 2005 running locally on my pc, and on my remote server.

Everything is fine locally, but when I copy my files over to my server (have setup a virtual dir, and ensured it is running .net2) I get the following message when trying to connect:

An attempt to attach an auto-named database for file C:\websites\sickness\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I have tried attaching and detaching the database from within SSME on the server - my connection string is:

<connectionStrings>
<add name="SicknessConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

I've tried it with and without the User Instance = True set

Has anyone got an actual fix for this?

Thanks, Mark

Hi - I'm sorry for bumping this, but I'm sure I can't be the only person with this problem - is there a more appropriate place I should look for an answer?

Kind regards,

Mark|||

mtait wrote:

is there a more appropriate place I should look for an answer?

Yes, I've now moved this question from the Getting Started forum to this SQL Server forum.|||I am pretty sure the problem is that you are missing the Database attribute for your connection string. I always usehttp://www.connectionstrings.com/ as a connection string reference. Drill down under SQL Server 2005 and look at the 2 SQL Server Express connection string examples. I think this will help you.|||

Hi - thank you - I was using connectionstrings.com - but each one fails.

I've now reinstalled SQL Express, and allowed Mixed Mode.

From SSME I've set the sa password to 'Bob99' and use this connection string:

<connectionStrings>

<add name="SicknessConnectionString" connectionString="Server=.\SQLExpress;Database=sickdb;User ID=sa;Password=Bob99;Trusted_Connection=False"

providerName="System.Data.SqlClient" />
</connectionStrings>

...and now the error is:

System.Data.SqlClient.SqlException: Cannot open database "sickdb" requested by the login. The login failed.
Login failed for user 'sa'.

I've checked, and rechecked the sa password - again, thanks for any help on this.

Mark

|||

Try detaching the database. You've permanently attached it, or you already have a database named 'database' on the other server. I notice that now you are calling it 'sickdb'. You could try renaming the database.mdf to sickdb.mdf as well. Then go back to your original connection string (of course you will need to change the reference from database.mdf to sickdb.mdf).

Just an FYI - your last error message is saying the userid/password is fine, but that user has no access to the 'sickdb' database. You can of course at this point go forwards and assign sa rights to the database, or you can go back and fix the original problem and not worry about permissions at all.

|||

Hi - thanks again for the help.

I think I've messed things up somewhere - I'm trying to change too many things to make this work, and I still end up with the same error messages.

If I uninstall SQL Express 2005, and do a clean install, with mixed mode authentication, is there a step by step guide anywhere which will advise how to:

a) setup a new user from within SSME
b) whether to attach the database file or not
c) which section of SSME to change to allow the user to connect to the local (database is on the same remote server as IIS) database - whether in Security/Logins or Database/Properties
d) advise a connection string which would work given the above steps#

Sorry - I know people are trying to help me, but I've just got myself completely mixed up here.

Thanks, Mark

sql

Problems connecting to MSDE on Windows 2003

I have installed MSDE 2000 SP3a on Windows 2003 Server Web edition.
I am trying to connect to this DB using enterprise manager from another
machine (Windows 2000 Server where SQL Server is installed) with no luck. I
am getting: SQL Server does not exist or access is denied. ConnectionOpen
(Connect())".
I have tried connecting using both Windows Authentication mode and using the
sa account (which I am not sure I know the password for the MSDE).
Can anyone help ?
Thanks
ra294@.hotmail.comCheck whether the instance is listening on any network protocols using
Server Network utility and enable them appropriately.
Thanks,
Bala.
This posting is provided "AS IS" with no warranties, and confers no
rights.
"ra294" <ra294@.hotmail.com> wrote in message
news:%23qkr5RW%23DHA.3808@.TK2MSFTNGP09.phx.gbl...
> I have installed MSDE 2000 SP3a on Windows 2003 Server Web edition.
> I am trying to connect to this DB using enterprise manager from another
> machine (Windows 2000 Server where SQL Server is installed) with no luck.
I
> am getting: SQL Server does not exist or access is denied. ConnectionOpen
> (Connect())".
> I have tried connecting using both Windows Authentication mode and using
the
> sa account (which I am not sure I know the password for the MSDE).
> Can anyone help ?
> Thanks
> ra294@.hotmail.com
>

Problems activating a second Report server using rsactivate (Web f

1. I cannot get rsactivate to activate a second RS node on a web farm.
rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config"
Failure starting the web service:
The local NT Service is not activated
2. rskeymgmt on the Original RS node 1 does not work either.
rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
Are you sure you want to extract the key from the report server (Y/N)? y
The local NT Service is disabled 0x80090005
Environment -
The two RS Servers are both Windows 2003 Servers.
SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
RS Node1: Works fine.
RS Node 2: Activation fails.
ReportServer service login account for both nodes: "NT
Authority\NetworkService"
SQL Server access - using a "sql admin" account.
IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
Web Servers on both RS nodes are working fine and the ReportServer service
starts as well.To activate a web farm, you will need to run rsactivate -m <name of node 2>
from node 1.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"VChandy" <VChandy@.discussions.microsoft.com> wrote in message
news:539D88F5-35FE-44E7-B2A5-79AC74E363D9@.microsoft.com...
> 1. I cannot get rsactivate to activate a second RS node on a web farm.
> rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> Failure starting the web service:
> The local NT Service is not activated
> 2. rskeymgmt on the Original RS node 1 does not work either.
> rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
> Are you sure you want to extract the key from the report server (Y/N)? y
> The local NT Service is disabled 0x80090005
> Environment -
> The two RS Servers are both Windows 2003 Servers.
> SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
> RS Node1: Works fine.
> RS Node 2: Activation fails.
> ReportServer service login account for both nodes: "NT
> Authority\NetworkService"
> SQL Server access - using a "sql admin" account.
> IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
> Web Servers on both RS nodes are working fine and the ReportServer service
> starts as well.
>|||Figured it out from an earlier post on 8/17 from Brian Hartman [MSFT]
Did the following -
1. rskeymgmt -d on the orignal RS Node 1.
2. Stopped W3Svs and RS Service on Node 2. (** important)
3. From Node1 ran: rsactivate -mNode2 -c "C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"
4. Re-started W3Svc and RS Service on Node 2.
5. Deleted and re-deployed all my RDLs to RS Node 1 as the security
information for the Data sources & other stuff got clobbered by rskeymgmt -d
6. Both RS nodes 1 & 2 can access all reports.
7. SSL works fine as well - next need to upgrade both nodes to RS Service
pack 1 and work on an NLB cluster solution ...
"VChandy" wrote:
> 1. I cannot get rsactivate to activate a second RS node on a web farm.
> rsactivate -c "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> Failure starting the web service:
> The local NT Service is not activated
> 2. rskeymgmt on the Original RS node 1 does not work either.
> rskeymgmt -e -fc:\rsdbkey.txt -pxxxxx
> Are you sure you want to extract the key from the report server (Y/N)? y
> The local NT Service is disabled 0x80090005
> Environment -
> The two RS Servers are both Windows 2003 Servers.
> SQL Server is on a third computer: SQL 2000 Server Enterprise Edition.
> RS Node1: Works fine.
> RS Node 2: Activation fails.
> ReportServer service login account for both nodes: "NT
> Authority\NetworkService"
> SQL Server access - using a "sql admin" account.
> IIS (W3Svc) on both RS nodes - "LocalSystemAccount".
> Web Servers on both RS nodes are working fine and the ReportServer service
> starts as well.
>

Problem: Stored Procedures not completing from Web Application

I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?

Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.

Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.

Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?

30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?

There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.

Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.

Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon

Simon,

Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.

The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.

Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.

To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?

With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.

If there is no blocking, use the Profiler to see where the process gets
stuck.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I had experienced issues with a store proc executing very quickly in Query Analyzer, but not from my ASP app against a development database. I noticed recently the same issue with the procedure not executing quickly in either QA or the web app.

After checking my table indexes on the dev database I found one joined table that had no index for the fields I was joining on. The index existed in the production database. Once adding that index. the procedure executed in < 1 second in QA and only a couple seconds from the web app.

Hope this helps anyone who's frustrated with similar issues.

Dave

Problem: Stored Procedures not completing from Web Application

I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?

Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.

Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.

Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?

30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?

There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.

Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.

Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon

Simon,

Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.

The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.

Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.

To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?

With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.

If there is no blocking, use the Profiler to see where the process gets
stuck.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Problem: Global DB connection using web.config

I spent a lot of time trying to fix this. I followed example from the web and I ALWAYS GET THE SAME PROBLEM

System.InvalidOperationException: The ConnectionString property has not been initialized.

What I am missing? I really need to fix this. Can anyone help me?

Thanks

In my web.config
<appSettings
 <add key="connectionstring" value="server=homesvr02;uid=sa;pwd=database=Property"/>
</appSettings
In my asp.net page
<%@. Page Language="VB" %>
<%@. Import Namespace="System.Configuration" %>
<%@. import Namespace="System.IO" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SQLClient" %
 Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")

MY ERROR

System.InvalidOperationException: The ConnectionString property has not been initialized.Please show the rest of your code.

Are you creating an instance of SqlConnection?

Are you setting it's ConnectionString property?|||Little error :-)

See this line:

::<add key="connectionstring"
::value="server=homesvr02;uid=sa;pwd=database=Property"/
and then this line:

::Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")

I know you come from a VB background - in XML, though casing does matter.

connectionstring != ConnectionString.|||good call there thona. I would have missed that. I think everyone proper cases everything.|||1.
<thona>
::Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")

I know you come from a VB background - in XML, though casing does matter.

connectionstring != ConnectionString.
</thona
<KraGiE>
good call there thona. I would have missed that. I think everyone proper cases everything.
</KraGiE
* Well, a quick test using VB.NET and C# said otherwise.

Tricky or Inconsistency. Who would expect that? :-D

* C#
Response.Write(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);

* VB.NET
Response.Write(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))

Both gives

server=homesvr02;uid=sa;pwd=database=Property

2. Can you guys do a quick test to see too?

3. web.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <appSettings>
 <add key="connectionstring" value="server=homesvr02;uid=sa;pwd=database=Property"/>
 </appSettings>

|||phouc is right. on both 2002 and 2003. impressive. twice in one day. remarkable

problem(Unable to add data connection)

Im having trouble creating a new connection through Visual Studio Web Developer. When I try to add a new connection all it gives me is an error-

Unable to add data connection
The DataConnectionDialog could not be intialized

When I test my page it seems alright going to the localhost but when I try to add a connection it wont let me.

What could be the cause?

And how can I fix this problem?

I want to connect to the sql server on my computer.. I cant because of the error above

Hi Leo,

I'm going to claim "not my technology" and send you to another forum. The place that is specifically about SQL Express in VWD is at http://forums.asp.net/54/ShowForum.aspx and I think you'll get a better answer there.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Problem with Website and SQL database synchronization

We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".
Found this error message where? Your application? SQL Server logs?
Windows Event Viewer? Something in sharepoint?
I've never seen that message before, but if you can give us better context
(where the error came from, I'm suspecting SharePoint), as well as anything
abnormal that might have been happening on the box around the same time as
the error message (e.g. backup, restore, DTS, log shipping, long-running
job, high volume).
And also the symptom of the problem -- is the problem that there is an error
message, or is something else going wrong with the application that you are
attributing to the error message?
Is it possible that a table or view in Sharepoint became corrupt? Does
Sharepoint have any repair or self-healing tasks you can run? Not being
familiar with Sharepoint, I recommend that you pursue this in a Sharepoint
newsgroup (microsoft.public.sharepoint.teamservices) where you will be much
more likely to find Sharepoint experts with direct experience with this
problem, or at least a better knowledge base to help you out.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Srichandan T." <srichandan.tirumalai@.wipro.com> wrote in message
news:9f1601c43454$cc4b4a40$a501280a@.phx.gbl...
> We have one web server, which is running on Win2K server,
> MS-SQL and Sharepoint. Last week it went down and i found
> this error message "The list or view of a list that is
> referenced here no longer exists".
> Kindly help us.
>
> "The list or view of a list that is referenced here no
> longer exists".

Problem with Website and SQL database synchronization

We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".Found this error message where? Your application? SQL Server logs?
Windows Event Viewer? Something in sharepoint?
I've never seen that message before, but if you can give us better context
(where the error came from, I'm suspecting SharePoint), as well as anything
abnormal that might have been happening on the box around the same time as
the error message (e.g. backup, restore, DTS, log shipping, long-running
job, high volume).
And also the symptom of the problem -- is the problem that there is an error
message, or is something else going wrong with the application that you are
attributing to the error message?
Is it possible that a table or view in Sharepoint became corrupt? Does
Sharepoint have any repair or self-healing tasks you can run? Not being
familiar with Sharepoint, I recommend that you pursue this in a Sharepoint
newsgroup (microsoft.public.sharepoint.teamservices) where you will be much
more likely to find Sharepoint experts with direct experience with this
problem, or at least a better knowledge base to help you out.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Srichandan T." <srichandan.tirumalai@.wipro.com> wrote in message
news:9f1601c43454$cc4b4a40$a501280a@.phx.gbl...
> We have one web server, which is running on Win2K server,
> MS-SQL and Sharepoint. Last week it went down and i found
> this error message "The list or view of a list that is
> referenced here no longer exists".
> Kindly help us.
>
> "The list or view of a list that is referenced here no
> longer exists".

Problem with Website and SQL database synchronization

We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".Found this error message where? Your application? SQL Server logs?
Windows Event Viewer? Something in sharepoint?
I've never seen that message before, but if you can give us better context
(where the error came from, I'm suspecting SharePoint), as well as anything
abnormal that might have been happening on the box around the same time as
the error message (e.g. backup, restore, DTS, log shipping, long-running
job, high volume).
And also the symptom of the problem -- is the problem that there is an error
message, or is something else going wrong with the application that you are
attributing to the error message?
Is it possible that a table or view in Sharepoint became corrupt? Does
Sharepoint have any repair or self-healing tasks you can run? Not being
familiar with Sharepoint, I recommend that you pursue this in a Sharepoint
newsgroup (microsoft.public.sharepoint.teamservices) where you will be much
more likely to find Sharepoint experts with direct experience with this
problem, or at least a better knowledge base to help you out.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Srichandan T." <srichandan.tirumalai@.wipro.com> wrote in message
news:9f1601c43454$cc4b4a40$a501280a@.phx.gbl...
> We have one web server, which is running on Win2K server,
> MS-SQL and Sharepoint. Last week it went down and i found
> this error message "The list or view of a list that is
> referenced here no longer exists".
> Kindly help us.
>
> "The list or view of a list that is referenced here no
> longer exists".

Problem with Website and SQL Data base synchronization

Problem with Website and SQL synchronization
We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".
I'm almost certain that error message was not generated by SQL Server. Where does the error appear? Try using SQL Profiler to see whether the sql box does actually generate an error.
Alicia
saw this http://www.sqlporn.co.uk - made me laugh anyway!

Problem with Website and SQL Data base synchronization

Problem with Website and SQL synchronization
We have one web server, which is running on Win2K server,
MS-SQL and Sharepoint. Last week it went down and i found
this error message "The list or view of a list that is
referenced here no longer exists".
Kindly help us.
"The list or view of a list that is referenced here no
longer exists".I'm almost certain that error message was not generated by SQL Server. Where
does the error appear? Try using SQL Profiler to see whether the sql box do
es actually generate an error.
Alicia
saw this http://www.sqlporn.co.uk - made me laugh anyway!

Problem with website and db

I am new to .NET and the Visual Web Developer Express and what not...I have created a website on my machine which works wonderfully...however, and I'm sure this is somewhere but I can't find it, I cannot get it to work on my host server...the Default page comes up, but when I try to sign in it says:

"An attempt to attach an auto-named database for file c:\hosting\webhost4life\member\harrison0801\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

The host support tells me to change the db name from aspnetdb to something else...I do this and when I run the site again or try to log in, I get the exact same error message...then they go on to tell me that I have to remove the LocalSqlServer in the web.config file which now looks like this:

<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|aspnetdb.mdf;User instance=true;" providerName="System.Data.SqlClient" />

<add name="Example" connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|example.mdf;User instance=true;Initial Catalog=example;" providerName="System.Data.SqlClient" />

</connectionStrings>

Of course this isn't working either...otherwise I would off playing on my server, and not posting here. Also, when I set up all the roles, users, and what not on my machine...does this carry over to the host machine?

Desperately needing massive help here...

Not a DBA and no expert in .NET

Thank you very much in advance,

Darren

Rename the .mdf file from aspnetdb.mdf to harrison0801.mdf and change your connection string to

<add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|harrison0801.mdf;User instance=true;" providerName="System.Data.SqlClient" />

|||

Okay...renamed the ASPNETDB.MDF and changed the config connection string to the above...hit CTRL F5 to run it...and guess what magically appeared? ASPNETDB without regard to the one I just changed the name on.

Also, when I run the ASP NET Configuration utililty in the VWD...walks me through all the roles, users, etc...it loads all that info into the ASPNETDB...is there something in the default config for the DB creator that can be changed? Perhaps this is something that the ASPNET creators can work out...maybe like allow the developers to name there own DB in the process of creating websites? Would be nice...less of a headache.

Problem with View migrated from SQL Server 2000 into 2005

We are in the process of migrating from SQL Server 2000 to 2005. We encountered a problem with one of our web applications (ASP) when attached to the new 2005 database. We do not get this error when the application is attached to the 2000 database.

During execution of the following code:

--

sub OpenRS_TicketDetails(iTicketID)

strSQL="SELECT * from vwexTicketDetails WHERE TicketID =" & iTicketID

rs.open strSQL, cnReadWrite, adOpenStatic, adLockOptimistic

end sub

We encountered the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e23'

Row handle referred to a deleted row or a row marked for deletion.

The following is the select statement related to the view:

SELECT T.TicketID, T.TicketDate, T.Problem, T.Technician_Assigned, T.Closed_Date, T.PersonID, T.SiteId, T.ProgramId, T.StatusId, T.PriorityId, T.CategoryId,
CMT.Comment, RTRIM(P.LastName) + ', ' + RTRIM(P.FirstName) AS FullName, P.Phone, P.WorkLocation, CT.CategoryName AS Category,
PR.priorityDesc AS Priority, ST.statusDesc AS Status
FROM dbo.dtTickets AS T LEFT OUTER JOIN
dbo.dtComments AS CMT ON T.TicketID = CMT.TicketID LEFT OUTER JOIN
DIVCommon.dbo.dtPersonnel AS P ON T.PersonID = P.PersonID INNER JOIN
dbo.vtCategory AS CT ON T.CategoryId = CT.CategoryID INNER JOIN
dbo.vtPriority AS PR ON T.PriorityId = PR.priorityId INNER JOIN
dbo.vtStatus AS ST ON T.StatusId = ST.statusId

We tracked the problem to the dtComments table and were able to come up with a workaround to our problem. When we added a primary key to the dtComments table, the application ran fine.

CREATE TABLE [dbo].[dtComments](

[CommentId] [int] IDENTITY(1,1) NOT NULL,

[TicketID] [int] NULL,

[Comment] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastModUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[LastModDate] [datetime] NULL)

Can someone explain to me why we are experiencing this problem in the 2005 environment and if there is a better solution. Please let me know if you need additional information about this situation.

Thanks, Doug

I can't say why it is a problem in 2005 rather than 2000. But I can explain why the problem, its not simple and to do with some inner workings of ADO, metadata and optimistic locking

You are specified to have a optimistic locked recordset based on a view. Firstly you shouldn't do this with views because you can end up with orphaned rows if you try and insert data.

How ADO handles the optimistic locking is it needs to know how to identify the row that is being updated and also how to identify what determines the record has changed so that it can verify the update. However your dtComments table doesn't have a PK thus the problem. ADO does try and obtain the information needed in the absence of a PK but this is what is causing the problem.

Putting a PK should be your answer, all tables should have one unless a specific reason not to.

You could also try changing it to a readonly recordset.

problem with UPDATE statement

i am using visual web developer 2005 and SQL Express 2005 and VB as the code behind

i am using the following statement to update details in the database.it doesn't work

Protected Sub processbutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles processbutton.Click
Dim update As New SqlDataSource()
update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
update.UpdateCommandType = SqlDataSourceCommandType.Text
update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = 11 , fromdesignlink = " + TextBox1.Text.ToString() +
"WHERE order_id =" + ordersid.ToString()
update.Update()
End Sub

the value of TextBox1 is obtained like this

Protected Sub uploadbutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles uploadbutton.Click
If FileUpload1.HasFile Then
FileUpload1.SaveAs("G:\project\My Project\OTS\ots\design\uploads\" + FileUpload1.FileName)
HyperLink1.Text = FileUpload1.FileName
HyperLink1.NavigateUrl = "uploads\" + FileUpload1.FileName
TextBox1.Text = FileUpload1.FileName
End If
End Sub

my ultimate aim is to upload the file and store the path in the database

what is wrong in my code ?

please help me

try this remember to put single quotes around text values and to have space between value and where clause (add extra space at the begin of every string you add when you build your query). I inserted quotes in red around your fromdesignlink field and I put space after it before where clause:

Protected Sub processbutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles processbutton.Click
Dim update As New SqlDataSource()
update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
update.UpdateCommandType = SqlDataSourceCommandType.Text
update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = 11 , fromdesignlink ='" + TextBox1.Text.ToString() +
"' WHERE order_id =" + ordersid.ToString()
update.Update()
End Sub