Problems installing SQL Reporting Services

I verified that the prerequisites MDAC, ASP, IIS 5 and MSDTC were all
installed, but I'm getting an error duing the install that says:
"IIS is either not installed or not configured for server component
installation"
Any ideas?
Carlton.
I just solved the same issue. Go to Administrative Tools, IIS Manager and
make sure the Default Web site is not stopped. If it is, then Start it and
then the Reporting Services install will not give you this error.
"Cwhitmore" wrote:

> I verified that the prerequisites MDAC, ASP, IIS 5 and MSDTC were all
> installed, but I'm getting an error duing the install that says:
> "IIS is either not installed or not configured for server component
> installation"
> Any ideas?
> Carlton.
>
|||Sunil,
The default website was running. I have serveral virtual directories that
run fine.
Any other ideas?
Carlton.
"Sunil Gulati" wrote:
[vbcol=seagreen]
> I just solved the same issue. Go to Administrative Tools, IIS Manager and
> make sure the Default Web site is not stopped. If it is, then Start it and
> then the Reporting Services install will not give you this error.
> "Cwhitmore" wrote:

Problems in executing SQL or Stored Procs with ASP

Hi experts,

I m working with MS SQL Server 2000 with ASP for my application. There're some Stored Procedures created for the new functions but seems I can't run these new SPs with my ASP pages. When I load that ASP page, it shows error message that can't find my SP. I've execute the SP alone in SQL Enterprise Manager and it works.

When I work with the SPs, I can connect with the DB with Enterprise Manager only without administrator right. As my SPs I suppose they should work with ASP but I just worry would it be the problem with my right granted in SQL server?

There are some other SPs running fine created by dbo but for my SPs not by dbo. Would there be any differences? Does it mean my SPs need to be granted by dbo instead of my current role? I m sure if I use the same ASP page then running another existing SPs, it works really smooth.

I also tried to make a SQL statement in my ASP page (e.g. an insert statement) but it seems nothing can be inserted. I got really screwed up!!!

Thanks in advance!!
Manfred

Manfred:

First, how are your ASP pages connecting to your database? Do you know they login, etc.? How do you give this login/user permissions to execute your stored procedure? Also, you might have an "owner" problem with the stored procedure object. Try running this query and posting the results:

select type,
uid,
left ([name], 40) as [name]
from sysobjects
where type = 'P'
and name = 'yourProcName'

-- - Sample Query Results: --

-- type uid name
-- - -
-- P 1 myProcName

Dave

|||

Thanks a lot for your advice Dave!

I got a dbo login name/password from my colleague and I've created a new SP under dbo login. It works! I can execute the stuff I want with the SP.

Here 's the query result

SELECT type, uid, LEFT(name, 40) AS name
FROM dbo.sysobjects
WHERE (type = 'P') AND (name = 'PROC_TESTDEPT')

-- type uid name
-- - -
-- P 1 proc_TestDept

Problems grasping ado.net - coming from asp background

Hi, i've downloaded a membership and roles provider but i am very new to ado.net. I have come fromasp background. Here's mycode:

public override void AddUsersToRoles(string[] usernames, string[] rolenames) {
// Validate arguments
foreach (string rolename in rolenames) if (!this.RoleExists(rolename)) throw new ProviderException("Role name not found");
foreach (string username in usernames) {
if (username.IndexOf(',') > 0) throw new ArgumentException("User names cannot contain commas.");
foreach (string rolename in rolenames) {
if (IsUserInRole(username, rolename)) throw new ProviderException("User is already in role.");
}
}

SqlConnection db = this.OpenDatabase();
SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserName, RoleName) VALUES (@.UserName, @.RoleName)", db);
cmd.Parameters.Add("@.UserName", SqlDbType.VarChar, 100);
cmd.Parameters.Add("@.RoleName", SqlDbType.VarChar, 100);
SqlTransaction tran = null;

try {
tran = db.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames) {
foreach (string rolename in rolenames) {
cmd.Parameters["@.UserName"].Value = username;
cmd.Parameters["@.RoleName"].Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch {
tran.Rollback();
throw;
}
finally {
db.Close();
}
}

private SqlConnection OpenDatabase() {
SqlConnection DB = new SqlConnection(this.connectionString);
DB.Open();
return DB;
}

The problem i have is that the table structure they provide is to have:

UsersInRoles
- UserName (foreign key to users table)
- RoleName (foreign key to roles table)

but the table structure i have is:

UsersInRoles
- UserID (foreign key to users table)
- RoleID (foreign key to roles table)

So what i need to do is lookup the UserID and RoleID from the appropriate tables based on the UserName and RoleName before doing the insert. However i am not familiar with the new syntax. I'm sure i could bodge something together but i assume this new syntax is something to do with running all the insert statements in one (transaction) so it doesn't have to keep taking round trips back to sqlserver.

Appreciate if someone could show me how this could be done. Thanks

If I understand you correctly, this issue does not differ from ASP to ASP.NET. You need to get UserName(RoleName) based on UserID(RoleID) and then insert the values into UsersInRoles table, right? If so, you can create a stored procedure like this:

CREATE PROCEDURE sp_InsUsersInRoles @.RoleID INT
AS
INSERT INTO UsersInRoles (UserName, RoleName)
SELECT UserName, RoleName
FROM Users join Roles ON Users.RoleID=Roles.RoleID
WHERERoles.RoleID=@.RoleID

Then you can easily call the stored procedure, just remember to set the SqlCommand.CommandType to StoredProcedure, and add parameter for it.

|||

Change:

SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserName, RoleName) VALUES (@.UserName, @.RoleName)", db);

to:

SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserID, RoleID)SELECT (SELECT UserIDFROM UsersWHERE Username=@.UserName),RoleIDFROM RolesWHERE RoleName=@.RoleName", db);

|||

Hi Motley worked a treat. However i also tried doing:

SqlConnection db = this.OpenDatabase();
SqlCommand cmd = new SqlCommand("INSERT INTO UserRoles (UserName, RoleName) VALUES (@.UserId, @.RoleId)", db);
cmd.Parameters.Add("@.UserId", SqlDbType.Int);
cmd.Parameters.Add("@.RoleId", SqlDbType.Int);
SqlTransaction tran = null;

try {
tran = db.BeginTransaction();
cmd.Transaction = tran;
foreach (string userName in userNames) {
SqlCommand cmd2 = new SqlCommand("SELECT UserId FROM Users WHERE UserName = @.UserName", db);
cmd2.Parameters.Add("@.UserName", SqlDbType.VarChar, 100).Value = userName;
int userId = (int)cmd2.ExecuteScalar();

foreach (string roleName in roleNames) {
SqlCommand cmd3 = new SqlCommand("SELECT RoleId FROM Roles WHERE RoleName = @.RoleName", db);
cmd3.Parameters.Add("@.RoleName", SqlDbType.VarChar, 100).Value = roleName;
int roleId = (int)cmd3.ExecuteScalar();

cmd.Parameters["@.UserId"].Value = userId;
cmd.Parameters["@.RoleId"].Value = roleId;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch {
tran.Rollback();
throw;
}
finally {
db.Close();
}

but it didn't work.

Any ideas what i did wrong. Cheers

|||

Is RoleName unique within Roles? If you are using the default asp.net membership database structure, then it is not. RoleName is only unique within an Application, and hence may appear in Roles multiple times for different applications. If you have multiple application support, then you will need to supply the application name or application id to your roleid lookup.

My code in this case will add the user to the RoleName of all applications in which the role appears. The same problem is true for UserName as well if you are using multiple applications, but my code will fail in that case as well.

Otherwise I see nothing wrong with your code.

|||

On the other hand, it could be transaction related. I don't normally use the transaction support in ASP.NET. I prefer to use SQL transactions for all my work since I tend to create large batches within my SQL, and wrap that rather than wrapping many SqlCommands. In any case, you might try adding the cmd2 and cmd3 to the transaction as well.

It would also help if you posted what error you are getting rather than just "It didn't work".

|||

Hi, i can't see how to track the error, i'm using visual studio web developer edition. I tried using the built in asp.net config tool to manage my users but when i put a break point in my code it does nothing. However to test if it was reaching this part of the code i did a dummy insert statement and it worked.

I also tried putting an exception on the catch part and doing console.writeline(ex.Message) but that didn't do anything either.

Appreciate your help once more. Thanks

Problems deploying website

My ASP.NET application runs fine within the VS2005 IDE; but, when I deploy to my local machine to try to run it under IIS and calling it up in a browser, I'm getting the following exception:

Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734979
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
System.Web.UI.WebControls.ListControl.PerformSelect() +31
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
System.Web.UI.Control.PreRenderRecursiveInternal() +77
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

I'm a novice at this and really need some help. What stupid newbie mistake am I making here? Thanks in advance for any guidance/assistance rendered.

When you deplyed the app you left the same SQL trusted connection, and most probably there is no local SQL server running in the same machine. Make sure to change the SQL string to the IP, username and password of the database and will work really well

Al

|||

I remember I got this problem too when I did my first try. It seems that your are using trusted connection, you need assign the domain user in your database with appropriate access right. Or you can create a database user in the database to use standard User/password connection method in your database is in mixed mode..

You can easily find the syntax for the connection string. As to create user in database, you need to create a login account under Security on the Server, in the database, you assign this user for access under database security tab.

Limno

Problems accessing data from recordset (SQL Server 2000)

I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
LeeCrazyAtlantaGuy wrote:
> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096
> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee

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 using SQLExpress on the server.

I have a small ASP.NET 2.0 that uses an instanse of SQLExpress. The connection string looks like this:

<connectionStrings>

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

</connectionStrings>

It works fine when I use it in file system. When I copy it to the server ( IIS6 on Windows Server ) it gives me an error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I tried changing the path to the local path on the server but it did not help.

Any help would be greatly appreciated.

With the way that you have the connection string configured you are using a user instance. What you sould do is make sure that sql express is installed on the server. Configure your web application directories and then install your application. On the server inside your web directories you should hava folder called app_data (The same as your local machine). Next I would perform an IISRESET once the app is loaded and the start the application up. You might find that the first time the app might time out on the database... just restart the browser session and it should fire up. You do not need to move the database to a different directory on the server as the connection string uses the datadirectory with is the app_data directory from your web solutions.