ASP and SQLServer 2k Issue

All,

I have scoured all over and I think I have a problem that is a bit more complex. I have a basic 3tier app running on Win2k, SQLServer 2k and asp.net. I set the aspnet account up with access to the db. I have the following code in my form:

<code>

Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As EventArgs) Dim MyConn as SQLConnection= NEw SQLConnection("server=xxx,1433;database=bsld_demo;uid=bsld;password=bsld") Dim MySQL As SQLCommand, TID as string, TParam as SQLParameter MySQL= New SqlCommand("InsertRecord", MyConn) MySQL.CommandType = CommandType.StoredProcedure MySQL.Parameters.Add(New SQLParameter("@.compmodule", compmodule.text)) MySQL.Parameters.Add(New SQLParameter("@.customername", customername.text)) TParam=MySQL.Parameters.Add(New SQLParameter("@.ID", 0)) TParam.Direction=ParameterDirection.Output MyConn.Open() MySQL.ExecuteNonQuery TID=TParam.Value MyConn.Close() End Sub

</code>

When I click on the button I get:

Login failed for user 'bsld'.

I am not sure what to do. The odd thing is on my server I have two instances of sqlserver running. Instance 1 is where I am having the problem. Instance works fine. Anybody run into this before? Thanks

Cordell

It is actually very simple the first thing you do is to register one with the other, by right clicking at the top of Enterprise Manager and registering the instance 1 with the instance. Now to your error it means Asp.net does not have an acount in the instance you are trying to connect to. There are two permissions in SQL Server, the server permission under security in the Management section of Enterprise Manager and the database permission by right click on your database go to all tasks. Then you adjust your Web.Config as needed. In the future it is better to backup and restore so everything including permission is moved so you just adjust the Web.Config. Hope this helps.|||

Sorry for the delay. Here is what I have:

<config file >

<configuration>

<appSettings/><connectionStrings>

<addname="bsld_demoConnectionString"

connectionString="Data Source=server\instance1;Initial Catalog=bsld_demo;Integrated Security=True"

providerName="System.Data.SqlClient"/>

</connectionStrings>

<system.web>

<customErrorsmode="Off"/>

<

authenticationmode="Windows"/>

<!--

</system.web>

</

configuration>

<config/>

<script>

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Dim MyConnAs SqlConnection =New SqlConnection("server=server,1433;Integrated Security=SSPI;database=bsld_demo")Dim MySQLAs SQLCommand, RIDAsString, TParamAs SQLParameter

MySQL =

New SqlCommand("InsertRecord", MyConn)

MySQL.CommandType = CommandType.StoredProcedure

MySQL.Parameters.Add(

New SqlParameter("@.CompModule", CompModule.text))

MySQL.Parameters.Add(

New SqlParameter("@.CustomerName", CustomerName.text))

TParam = MySQL.Parameters.Add(

New SqlParameter("@.ID", 0))

TParam.Direction = ParameterDirection.Output

MyConn.Open()

MySQL.ExecuteNonQuery()

RID = TParam.Value

EndSub

<script/>

The server\ASPNET account has access on the server and to the bsld_demo db. I still get this error:

Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails.
Login failed for user 'server\ASPNET'.

Source Error:

Line 26: Line 27:Line 28: MyConn.Open()Line 29: Line 30: MySQL.ExecuteNonQuery()

Source File:D:\SOS\ConnectionTest\Default.aspx Line:28

Stack Trace:

[SqlException (0x80131904): Cannot open database requested in login 'bsld_demo'. Login fails.Login failed for user 'server\ASPNET'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735251 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 ASP.default_aspx.Button1_Click(Object sender, EventArgs e) in D:\SOS\ConnectionTest\Default.aspx:28 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

What do I do now? I have tried everything else. Thanks,.|||

You need to go back to my original post about how you create a SQL Server account for Asp.net because of the text below.

(Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails.
Login failed for user 'server\ASPNET')

And clean up your connection string with the link below. Hope this helps.

http://www.carlprothman.net/Default.aspx?tabid=86

|||Thanks for the suggestions. I cleaned my string. However, I do have a SqlServer account for ASP.Net on the SQLServer. I guess that's why I am confused. In SQLServer I have an account set up as 'server\ASPNET' with server being my server name. I granted that user access to the bsld_demo db.|||

No each instance of SQL Server is a separate server permissions so check the Master database of the instance you are trying to run the application to see if Asp.net account was created in Syslogin table, if it is there try to connect to it if you cannot do that then the permissions is orphaned, that is covered in the second link, it usually involve editing the Master, most places I have worked this require a lot of Management signatures.

http://msdn2.microsoft.com/en-us/library/ms178593.aspx

http://msdn2.microsoft.com/en-us/library/ms175475.aspx

Here is Microsoft covering remote connection see if that makes a difference. Hope this helps.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

Thanks Caddre. I tried the links and suggestions and nothing worked. I even did the following: I turned Anon access off and tried to log in and got the same "Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails."
error. This time I got "Login failed for user 'AD\abcxyz'." which AD\abcxyz is my Network ID.

On thin I found interesting is that if I create the connection string in VWD, I can do datagrid functions like edit, delete and insert. But if I try to create a sub procedure and try to link the database that way, this is when I encounter the error. Do you or anyone else know of a way or some examples where I can create a button and code it to do the same thing as the default "Insert" feature in VWD? If I can find this, I'll avoid this issue.

|||

EDIT

I am glad you posted this database was VWD created and that makes it a user instance database, you can fix the problem by backing up the database and restore and it will become regular database. The link below covers it. Post again and let me know how it goes.

http://msdn2.microsoft.com/en-gb/library/ms143684.aspx

|||Hey Caddre. I tried the suggestion in the link and nothing. I thought I would try and create a tbl in another db and point the page to it. I get the same error. I'm almost tempted to reload the .net framework to see if that fixes it but I don't want to screw up the other instance. This is just the craziest thing I have ever seen. You have any other suggestions? You have been awesome in helping just so you know.. :-)|||

EDIT

Try backup and restore first, then the framework and this is from Microsoft support you should change from Windows Authentication to Mixed authentication

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

asp / sql server dates in recordsets

Hey,

I've got an Classic ASP app pulling some records out of a DB, which
worked fine when it was Access. When we upped to SQL Server, all of a
sudden, when the Recordsets came back, all the dates were empty, even
though the data was showing up in the DB.

About a year ago (only code asp apps once and a while), I remember
seeing this and finding something that just had something to do with the
order in which something was being referenced, but I just can't for the
life of me remember what it was.

The Dates are set as datetime, length of 8.

Anyone know why this happens and a solution?

many thanks,

rob"rob" <rob@.pleaseremovespamcherny.com> wrote in message
news:XYadnScIEbMPLpvdRVn-uA@.giganews.com...
> Hey,
> I've got an Classic ASP app pulling some records out of a DB, which
> worked fine when it was Access. When we upped to SQL Server, all of a
> sudden, when the Recordsets came back, all the dates were empty, even
> though the data was showing up in the DB.
> About a year ago (only code asp apps once and a while), I remember
> seeing this and finding something that just had something to do with the
> order in which something was being referenced, but I just can't for the
> life of me remember what it was.
> The Dates are set as datetime, length of 8.
> Anyone know why this happens and a solution?
> many thanks,
> rob

It's not really possible to give a good answer without more information. Do
you have an example of a query which returns data in Query Analyzer, but not
in ASP?

You might also look at www.aspfaq.com, if you haven't already - there are
quite a lot of articles about handling dates.

Simon

asp - vb6 communication

Hi,
I have created vb6 app to connect to sql server 2000 db, and now i have a new requirement: to publish this app on web, but I should use an exising vb6 application that includes all buisness logic and all communication with database: quieries, stored procedures, etc.
To implement this, i create a client :HTML interface and server: asp file that receives an input from client and calls vb6 functions with parameters he received from client. My question is: how can i call from asp application a vb6 application?The best thing would be to convert your vb6 app to a COM object.

AS2005 Member Properties - MDX Query

Hi,

I am having a bit of a problem with retreiving member properties from our AS2005 cube. I'm using MDX query's in a VBA Excel app via ADO (not ADOMD). I've been prototyping the queries in the SQL Server Management Studio.

I currently have the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This works fine, however I now want to CrossJoin the measures [CR 01 Net Skw Adj USD] and [JTD 01 USD] with a dimension called CS Tenor in order to give me a seperate value for each Tenor.

So far the only way I can do this is with the following query:

WITH

MEMBER [Measures].[Portfolio] AS '[Trade].[Trade By Source System].CurrentMember.Properties("Portfolio")'

SELECT NON EMPTY {

[Measures].[Portfolio],

[Measures].[CR 01 Net Skw Adj USD],

[Measures].[JTD 01 USD]

} * {[CSTenor].[CS Tenor].MEMBERS}

ON COLUMNS,

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso]))

ON ROWS

FROM [GCD]

WHERE ([Close Date].[Close Date].[Day].[01 Dec 2006], [Trade].[Trade By Business Unit].[Book Group].[LNCT])

This has the unfortunate side effect of CrossJoining the Portfolio member property with Tenor as well, resulting in 9 unwanted Portfolio columns (there are 9 members in CS Tenor). In reality the problem is more severe as I have around 10 member properties in the query, I reduced it to 1 here for simplicity.

My question is: Can I bring back member properties on the Rows axis? and a seperate question is: Can I CrossJoin some measures and not other on the columns axis, for example, could I CrossJoin [CR 01 Net Skw Adj USD] with CS Tenor, but not [JTD 01 USD] ?

Many thanks!

I don't believe you can cross join some measures and not other on the column axis but assuminng portfolio is an attribute of your Trade dimension the you can try this on your rows axis:

NonEmpty(Exists({[Trade].[Trade By Source System].[Trade Id].MEMBERS},[Trade].[Trade By Source System].[Source System].&[Calypso],strtomember("[Trade].[Portfolio].[" + [Trade].[Trade By Source System].CurrentMember.Properties("Portfolio") + "]") ))

Hope this helps.

V

|||

Actually, you should be able to do what you want on the column axis by simply constructing the full set of tuples that you want back. Here's an example from Adventure Works:

select

{ [Date].[Calendar Year].Members } on rows,

{ { [Measures].[Internet Sales Amount] } * { [Product].[Category].Members }, ( [Measures].[Internet Gross Profit], [Product].[Category].[All Products] ) } on columns

from

[Adventure Works]

In this query, I'm just pulling back calendar years on rows. On the columns, I'm pulling back the [Internet Sales Amount] measure cross joined with the product categories along with the [Internet Gross Profit] measure -- but with it coming back only with [All Products], not all the product cateogries. The only caveat is that all the tuples returned have to have the same dimensionality. Thus the need to return [Internet Gross Profit] with a member (in this case, the [All Products] member) of the same hierarchy used in the cross join that generates the first set of tuples.

In your case, you'd want something like this (not knowing exactly what your [All] member is, I just called it [All CS Tenors]):

{ { [Measures].[CR 01 Net Skw Adj USD] * [CSTenor].[CS Tenor].Members }, ( [Measures].[JTD 01 USD], [CSTenor].[CS Tenor].[All CS Tenors] ) } on columns

HTH,

Dave Fackler

|||

Hi,

Sorry for the delay in replying, I've been on vaction.

Many many thanks for this solution, it works perfectly!

Happy New Year,

Stuart

Arithmetic operation resulted in an overflow. (System.Data) - Connection error with SQL Se

We've been using SQL Server 2005 for a while as the db for our web app. Everything has been working fine, until yesterday when we started getting a "Arithmetic operation resulted in an overflow. (System.Data)" error message when trying to connect from SQL Server Management Studio or from our web app. This only happens when trying to connect remotely, although remote connections have worked for us perfectly in the past. The full error message is reproduced below. Thanks ahead of time for any help.

===================================

Cannot connect to serverName

===================================

Arithmetic operation resulted in an overflow. (System.Data)

----------
Program Location:

at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Hi sergeipetrov,

Could you please elaborate your question a little more? I'm afraid we cannot help you just from your description above. "Arithmetic Overflow" is not particularly related to sql server, it could be caused by some other reasons. And by the way, from your code stack it seems the error happends while the data connection. So, maybe some connection code of your application would be helpful. thanks

Are these the correct steps? Encrypted Connection String and Security

Hi,

Assume I have an asp.net/sql server 2000 web app in a shared hosting
environment. I then encrypt the connection string using
ProtectSection("DataProtectionConfigurationProvider") in the page load
of my default.aspx page.

Am I understanding the following concepts then correctly?

1. I upload the site to the shared hosting server.
2. The first time I run the app eg. www.whatever.com/default.aspx,
the ProtectSection method above is executed.
3. Now the conn string area of my web.config is encrypted, and
asp.net will decrypt as needed.

4. If someone were to hack the server and view the web.config --
whether via getting into the server or via ftp, they would see an
encrypted connection string.

Thanks very much!If the hosting server is shared then there is a risk, if not you have to rely upon the ISP.

are there any MSDE T-SQL code limitations?

I am writing a database app which will run under MSDE on the user's systems.

Other than limitations as to the number of concurrent users, are there any
issues that I need to be aware of when programming the database using SQL
Server 2000? i.e., are there T-SQL programming statements that will run
under SQL Server 2000 as part of my development enviornemnt that won't run
under MSDE on the end users machines, or will MSDE handle anything that SQL
Server can from the database engine standpoint?

Thanks!hi Mike,
"Mike N." <BadDog@.thepound.com> ha scritto nel messaggio
news:r3erb.5780$942.4765@.newssvr25.news.prodigy.co m...
> I am writing a database app which will run under MSDE on the user's
systems.
> Other than limitations as to the number of concurrent users, are there any
> issues that I need to be aware of when programming the database using SQL
> Server 2000? i.e., are there T-SQL programming statements that will run
> under SQL Server 2000 as part of my development enviornemnt that won't run
> under MSDE on the end users machines, or will MSDE handle anything that
SQL
> Server can from the database engine standpoint?

all T-SQL statements SQL Server 2000 supports are supported by MSDE...
you only (only...) have database limitation size, limited replication
support, no mail support...
for further info please visit
http://www.microsoft.com/sql/msde/p...fo/features.asp

hth
Andrea Montanari (Microsoft MVP - SQL Server)
andrea.sql@.virgilio.it
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.4.0 - DbaMgr ver 0.50.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply