Help with connectivity - SQL Server does not exist or access denied

am using VS2003 asp.net 1.1 on Windows 2003 sever to access another network server SQL Db.

This is the error message...

SQL Server does not exist or access denied.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Source Error:

Line 34: 'execute query and return data readerLine 35: TryLine 36: objHelperConnection.Open()Line 37: RunSPReturnDR = objHelperCommand.ExecuteReader(CommandBehavior.CloseConnection)Line 38: 'clean up

Source File:c:\inetpub\wwwroot\Protraxx\Scripts\Includes\dbhelper.vb Line:36

Stack Trace:

[SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.CreateConnection() System.Data.SqlClient.ConnectionPool.UserCreateRequest() System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() chef.dbhelper.DBTools.RunSPReturnDR(String strSP, SqlParameter[] params) in c:\inetpub\wwwroot\Protraxx\Scripts\Includes\dbhelper.vb:36 chef.Login.Authenticate(String sUserName, String sPassWord) in c:\inetpub\wwwroot\Protraxx\Login.aspx.vb:168 chef.Login.btnSubmit_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\Protraxx\Login.aspx.vb:90 System.Web.UI.WebControls.Button.OnClick(EventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()

Version Information: Microsoft .NET Framework Version:1.1.4322.2300; ASP.NET Version:1.1.4322.2300

Ps : This app works on Windows XP and W2k

Thanks for any suggestions.

This can be caused by a couple of things. Microsoft has a comprehensive article on their site descibing the causes and fixes of your error:http://url123.com/nwsp4

sql

help with connectionString

Hello,

I have just tried to deploy my app from my local pc to our server and I have been getting this error.

Server Error in '/' Application.

---

Configuration Error

Description:An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message:The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty.

Source Error:

Line 164: <providers>Line 165: <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/"Line 166: type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />Line 167: <add name="AspNetWindowsTokenRoleProvider" applicationName="/"Line 168: type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

Source File:C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config Line:166

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

I am new to SQLExpress 2005 and asp.net 2.0. I created some database files on my local pc then copied the whole app to the server. Not sure what to do.

Any help would be greatly appreciated or direction to info.

Thanks Matt

Matt,

Please make sure the membership tables already created on your production server.
Also, check your web or virtual directory is running on top of .Net 2.0

Choirul|||

You need to deploy your database to the remote server as well with the web application. You also need to update the connection string in your web.config file. For example:

<add name="LocalSqlServer" connectionString="Data Source=yournewremotelocationdatabase;Initial Catalog=table;Persist Security Info=True;User ID=yourid;Password=yourpassword" />

Hope that helps.

|||

Thanks for the help. I looked at my application and I have to we.config files. One for the entire application and one for the secured pages under the members folder.

I changed the connectionStrings in the application web.confin but there is no connectionString in the members section.

<?

xmlversion="1.0"encoding="utf-8"?>

<

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<

system.web>

<

authorization>

<

denyusers="?" />

</

authorization>

</

system.web>

</configuration>

Using VWD it says

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem:The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty. (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 140)

When I try to choose the data provider it tells me:

Could not establish a connection to the database.
If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.

I then tried to run the aspnet_regsql and got:

Setup failed.

Exception:
Unable to connect to SQL Server database.

------------
Details of failure
------------

System.Web.HttpException: Unable to connect to SQL Server database. --> System.Data.SqlClient.SqlException: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
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.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
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 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
-- End of inner exception stack trace --
at System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString)
at System.Web.Management.SqlServices.SetupApplicationServices(String server, String user, String password, Boolean trusted, String connectionString, String database, String dbFileName, SqlFeatures features, Boolean install)
at System.Web.Management.SqlServices.Install(String database, SqlFeatures features, String connectionString)
at System.Web.Management.ConfirmPanel.Execute()

So at this point I guess I am lost. I would appreciate any help. Can you recommend a good resource for SQLEpress learning?

Thanks Again Matt

|||The problem should be on your connectionstring. It fails searching LocalSqlServer, which is the default connectionstring name for membership database.Make sure your connectionstring name is "LocalSqlServer". If you want to use another name, you can specify it on connectionStringName of membership section in web.config.

<membership defaultProvider="AspNetSqlMembershipProvider">
<providers>
<clear/>
<add name="AspNetSqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="myConnectionString"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
requiresUniqueEmail="false"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="4"
minRequiredNonalphanumericCharacters="0"
passwordAttemptWindow="10"
passwordStrengthRegularExpression=""
applicationName="/myapp"/>
</providers>
</membership
It will help if you can post your application web.config here too :)|||

Thanks for the help. Here is the app web.config. Below is the connectionStrings for my App_Data folder.

<?

xmlversion="1.0"?>

<!--

Note: As an alternative to hand editing this file you can use the

web admin tool to configure settings for your application. Use

the Website->Asp.Net Configuration option in Visual Studio.

A full list of settings and comments can be found in

machine.config.comments usually located in

\Windows\Microsoft.Net\Framework\v2.x\Config

-->

<

configurationxmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">

<!--

The configSections define a section for ASP.NET Atlas.

-->

<

configSections>

<

sectionGroupname="microsoft.web"type="Microsoft.Web.Configuration.MicrosoftWebSectionGroup">

<

sectionname="converters"type="Microsoft.Web.Configuration.ConvertersSection"requirePermission="false"/>

<

sectionname="webServices"type="Microsoft.Web.Configuration.WebServicesSection"requirePermission="false"/>

<

sectionname="authenticationService"type="Microsoft.Web.Configuration.AuthenticationServiceSection"requirePermission="false"/>

<

sectionname="profileService"type="Microsoft.Web.Configuration.ProfileServiceSection"requirePermission="false"/>

</

sectionGroup>

</

configSections>

<!--

The microsoft.web section defines items required for the Atlas framework.

-->

<

microsoft.web>

<

converters>

<

addtype="Microsoft.Web.Script.Serialization.Converters.DataSetConverter"/>

<

addtype="Microsoft.Web.Script.Serialization.Converters.DataRowConverter"/>

<

addtype="Microsoft.Web.Script.Serialization.Converters.DataTableConverter"/>

</

converters>

<

webServicesenableBrowserAccess="true"/>

<!--

Uncomment this line to enable the authentication service.

<authenticationService enabled="true" />

-->

<!--

Uncomment these lines to enable the profile service. To allow profile properties to be retrieved

and modified in Atlas applications, you need to add each property name to the setProperties and

getProperties attributes. If you intend for all properties to be available, you can use "*"

as a shorthand rather than enumerating each property

-->

<!--

<profileService enabled="true"

setProperties="propertyname1;propertyname2"

getProperties="propertyname1;propertyname2" />

-->

</

microsoft.web>

<

appSettings/>

<

connectionStrings>

<

addname="MSFADatabaseConnectionStringDistrict1Blog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="MSFADatabaseConnectionStringDistrict2Blog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="MSFADatabaseConnectionStringDistrict3Blog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="MSFADatabaseConnectionStringDistrict4Blog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="MSFADatabaseConnectionStringAtLargeBlog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="MSFADatabaseConnectionStringPresidentBlog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="MSFADatabaseConnectionStringSecretaryTreasurerBlog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

addname="MSFADatabaseConnectionStringVicePresidentBlog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

</

connectionStrings>

<

system.web>

<

authorization>

<

allowroles="administrator" />

</

authorization>

<

roleManagerenabled="true" />

<

authenticationmode="Forms" />

<

pages>

<

controls>

<

addnamespace="Microsoft.Web.UI"assembly="Microsoft.Web.Atlas"tagPrefix="atlas"/>

<

addnamespace="Microsoft.Web.UI.Controls"assembly="Microsoft.Web.Atlas"tagPrefix="atlas"/>

</

controls>

</

pages>

<!--

Set compilation debug="true" to insert debugging

symbols into the compiled page. Because this

affects performance, set this value to true only

during development.

-->

<

compilationdebug="true">

<

buildProviders>

<

addextension=".asbx"type="Microsoft.Web.Services.BridgeBuildProvider"/>

</

buildProviders>

</

compilation>

<!--

ASMX is mapped to a new handler so that proxy javascripts can also be served.

-->

<

httpHandlers>

<

removeverb="*"path="*.asmx"/>

<

addverb="*"path="*.asmx"type="Microsoft.Web.Services.ScriptHandlerFactory"validate="false"/>

<!--

The MultiRequestHandler enables multiple requests to be handled in one

roundtrip to the server. Its use requires Full Trust.

-->

<

addverb="*"path="atlasbatchcall.axd"type="Microsoft.Web.Services.MultiRequestHandler"validate="false"/>

<

addverb="*"path="atlasglob.axd"type="Microsoft.Web.Globalization.GlobalizationHandler"validate="false"/>

<!--

The IFrameHandler enables a limited form of cross-domain calls to 'Atlas' web services.

This should only be enabled if you need this functionality and you're willing to expose

the data publicly on the Internet.

To use it, you will also need to add the attribute [WebOperation(true, ResponseFormatMode.Json, true)]

on the methods that you want to be called cross-domain.

This attribute is by default on any DataService's GetData method.

<add verb="*" path="iframecall.axd" type="Microsoft.Web.Services.IFrameHandler" validate="false"/>

-->

<

addverb="*"path="*.asbx"type="Microsoft.Web.Services.ScriptHandlerFactory"validate="false"/>

</

httpHandlers>

<

httpModules>

<

addname="ScriptModule"type="Microsoft.Web.Services.ScriptModule"/>

<

addname="BridgeModule"type="Microsoft.Web.Services.BridgeModule"/>

<

addname="WebResourceCompression"type="Microsoft.Web.Services.WebResourceCompressionModule"/>

</

httpModules>

<!--

The <authentication> section enables configuration

of the security authentication mode used by

ASP.NET to identify an incoming user.

<authentication mode="Windows"/>

-->

<!--

The <customErrors> section enables configuration

of what to do if/when an unhandled error occurs

during the execution of a request. Specifically,

it enables developers to configure html error pages

to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">

<error statusCode="403" redirect="NoAccess.htm"/>

<error statusCode="404" redirect="FileNotFound.htm"/>

</customErrors>

-->

</

system.web>

<!--

Uncomment this if your site globally denies access to anonymous users. The

authentication service and profile service are located under the virtual

"ScriptServices" directory. Since you normally call the authentication web

service with an un-authenticated user, the following location tag can be used

to grant access to anonymous users. If you use anonymous profile support

you will also need to grant access to anonymous users.

-->

<!--

<location path="ScriptServices">

<system.web>

<authorization>

<allow users="*" />

</authorization>

</system.web>

</location>

-->

</

configuration>

Thanks Matt

|||Hi,

You're missing the connection string for ASP.NET membership. The default name is "localSqlServer". Try to add this conn string:

<addname="localSqlServer"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myWebApp\AtlasMSFA\App_Data\MSFADatabase.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

also, make sure you've added membership tables in MSFADatabase.mdf.

If you want another name instead of "localSqlServer", please refer to my previous post :)

|||

Thanks for the Help. That seamed to fix that problem. I really appreciate it. My Default.aspx page came up. But when I try to navigate to a page containing a database this is what I get.

Server Error in '/' Application.

---

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

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file C:\myWebApps\AtlasMSFA\App_Data\MSFADatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

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): An attempt to attach an auto-named database for file C:\myWebApps\AtlasMSFA\App_Data\MSFADatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +437 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.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

And also when I try to login in I get this.

Server Error in '/' Application.

---

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

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): Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857306 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734918 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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +367 System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +85 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 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

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

Thank you again for the help.

Thanks Matt

|||hi,
Make sure SQLExpress is running on your deployment server, and asp.net membership tables is already exists in MSFADatabase.mdf.
You may need to change the path for database file as follow, so it will search your deployement folder accordingly, instead of hardcoded the database file location.

<

addname="MSFADatabaseConnectionStringDistrictBlog"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|MSFADatabase.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True"providerName="System.Data.SqlClient" />

<

|||

Thank you again for the help. I have tried your suggestion and it did not seam to work. Also I have checked my application and the tables all appear to be there. It looks like all the data copied correctly from my local pc to my server. I am able to right click on the asnet_Membership table, select "Show Table Data", and view the users I have created. Also the MSFADatabase.mdf table are there and the data.

I have tried to use the ASP.NET Website Administration Tool. When I click the security tab this is the error I get,

There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem:Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

When I choose the data store it is able to successfully connect toAspNetSqlProvider.

I am not sure what is going on.

How can I check to make sure SQLExpress 2005 is running?

Thanks you again.

Matt

|||hi,

run sqlcmd -L in your command prompt, it will display any sql server instance in your network. Make sure sqlexpress is up in production server.

About error regarding to reole provider, add this section in your web.config:

<roleManager enabled="true">
<providers>
<clear/>
<add connectionStringName="yourconnectionstringname" applicationName="/yourapplicationname" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
<remove name="AspNetWindowsTokenRoleProvider"/>
</providers>
</roleManager
don't forget to repace connstringname and application name accordingly|||

Once again thank you for the help. Running sqlcmd-L helped with the one part. I appreciate all your help. I still can seem to get the login in to work. I am still getting:

Server Error in '/' Application.

---

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

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): Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857306 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734918 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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +367 System.Web.Security.SqlMembershipProvider.CheckSchemaVersion(SqlConnection connection) +85 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 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

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

I have checked the event log on my server and am getting a couple of errors.

Event ID: 17401, MSSQL$SQLEXPRESS and Event ID: 1309 ASP.NET 2.0.50727.0

If I am correct this has something to do with the SQLExpress being idle? I don't believe that it is a timeout problem as the error comes up immediately.

Any Ideas?

Thanks Matt

|||Did you check if'dbo.aspnet_CheckSchemaVersion' really exists in your database?|||

No. I am not sure what that is? It does not look like it is there if I explore the database.

Thanks Matt

Help with connecting to the object explorer ON Microsoft SQL Server Management Studio Expr

Hi All,

I have just installed Microsoft SQL Server Management Studio Express.. Once I did this first it was working fine, but now their is this error each time I try to connect the object explorer.. the error is as follows :-

Could not load file or assembly 'System.Data, Version=2.0.0.0, Culture=neutral publickeyToken=b77a5c561934e089' or one of its dependencies. Signature specified is zero-sized. (Exception from HRESULT: 0x80131237).

Could anybody help me with getting rid of this error. I have no clue what to do?Try closing everything, uninstall SMSS, reboot, reinstall. Make sure only necessary programs and network connections are on when you try to open SMSS again.sql

HELP with case statement [Divide by zero error encountered.] !

Hi,
I have a case statement that has been giving me hell for the past day, can
anyone suggest another way of validating is those field have zeros or not.
The field types are numberic, looking at the table their is no Null values i
n
there a few hundred zeros (0, .000). I have tried this statement in many wa
y
still the same result.
Divide by zero error encountered.
CASE when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end as [dollar_turns],in your case statement you r not checking for dids.supplier_cost = 0
and in your else clause you are divinding by
( (dihs.qty_total) * dids.supplier_cost )
hope thishelps
rgds
abhishek
"ITDUDE27" wrote:

> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],
>|||By any chance are you rinning SQL 2005? I seem to recall someone else
having a similar issue where the optimizer evaluated the whole
statement rather than one test at a time.
Stu
ITDUDE27 wrote:
> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],|||Hi
Your assumption is wrong :)
Let me explain it.
For simplicity I am showing the columns as a(supplier_cost)
,b(qty_total),c(revenue)
lets say the table is like this...
a b c
1 1 1
0 0 0
For all the cases the sum is going to be 1 (for all three columns)
But analyze this..
sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
its actually 1/(1*1) + 0/(0*0)
I guess maybe you are trying for something like this in your else clause..
sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
If not change your logic accordingly.
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This thread illustrates exactly why one of the best ways to get help is to
include the *ACTUAL* table DDL code *AND* some sample data INSERT
statements.
No so many back-and-forths just trying to understand the issue.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6DEF9528-1748-4905-ACEE-0567A384126B@.microsoft.com...
> Hi
> Your assumption is wrong :)
> Let me explain it.
> For simplicity I am showing the columns as a(supplier_cost)
> ,b(qty_total),c(revenue)
> lets say the table is like this...
> a b c
> 1 1 1
> 0 0 0
> For all the cases the sum is going to be 1 (for all three columns)
> But analyze this..
> sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> its actually 1/(1*1) + 0/(0*0)
> I guess maybe you are trying for something like this in your else clause..
> sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
> If not change your logic accordingly.
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>sql

HELP with case statement [Divide by zero error encountered.] !

I am getting the same result pandey, I might have left that of by accident.
I
have tried it many ways.
case when sum (dids.supplier_cost) = '0'
then '0'
when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
THEN '0'
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end [dollar_turns],
"Abhishek Pandey" wrote:
> in your case statement you r not checking for dids.supplier_cost = 0
> and in your else clause you are divinding by
> ( (dihs.qty_total) * dids.supplier_cost )
> hope thishelps
> rgds
> abhishek
> "ITDUDE27" wrote:
>i am not sure what exactly are you trying to do here..
in your case statement you are checking "sum(dids.supplier_cost)" and other
things for 0 and handling it accordingly but in else clause you are divindin
g
by dids.supplier_cost.
say for example
dids.supplier_cost has three values
1
0
3
while sum(dids.supplier_cost) <> 0 but this doest garuntee that individual
values are also not zero.
so in this case when it goes to else clause sql server experience a divide
by zero error coz what it will try to do is this
sum( somethingvalue / somenonzerovalue + somevalue/ 0 + somevalue/somevalue)
i hope this is clear enough.
You gotta design your query in a better way. if you are having a hard time
try to post your FULL query and some1 would be able to help you out.
regards
Abhishek
"ITDUDE27" wrote:
> I am getting the same result pandey, I might have left that of by accident
. I
> have tried it many ways.
> case when sum (dids.supplier_cost) = '0'
> then '0'
> when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> THEN '0'
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end [dollar_turns],
> "Abhishek Pandey" wrote:
>

help with arithmetic overflow error with insted of update trigger

Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
When I perform an update on certain records, i.e.:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
I am finding that *some* user id's result in the follwing error:
"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."
mytable view has a number of data fileds. All the data fields are of type
smalldatatime.
When i compare the user record of a userid that causes an error to one that
doesnt cause an error, the dates do vary, where some date fields have NULL's
or correctly formated smalldatatime values (yes I know about the restriction
of smalldatetime to range between 1900 and 2079).
The odd thing is that even if i am updating a non-date field within the
view, the above arithmetic error still occurs.
My trigger looks like the following:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
DECLARE @.mydate datetime
SELECT @.mydate = GETDATE()
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0),
date1 = inserted.date1,
date2 = @.date+30
FROM inserted
WHERE mytable.userid = inserted.userid
Am i getting this error because i am mixing a date2 fiels (which is of type
smalldatetime) with @.date (which is of type datetime) ?
Any help most appreciated.Do you need the extra ms or time range? If not try:
DECLARE @.mydate smalldatetime
SELECT @.mydate = GETDATE()
SELECT @.mydate
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||Also, drop the SELECT @.mydate -- was just for testing. Basically using the
SMALLDATETIME data type instead of DATETIME.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
> Do you need the extra ms or time range? If not try:
> DECLARE @.mydate smalldatetime
> SELECT @.mydate = GETDATE()
> SELECT @.mydate
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Sorry, I meant to say:
mytable view has a number of *date* fields. All the *date* fields are of
type
smalldatatime.
.. late night
cheers, peter
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||thanks ill try that when i get to work
I hope its as obvious as changing smalldatetime to datetime!
cheers, peter

> Also, drop the SELECT @.mydate -- was just for testing. Basically using
> the SMALLDATETIME data type instead of DATETIME.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
>

Help with Arithmetic Overflow error

Hi. I could use some help troubleshooting an error in a stored
procedure. The SP was developed some time ago and recently started
thowing this error:
Arithmetic overflow error converting numeric to data type numeric.
This error doesn't seem to make much sense since it happens when
inserting data into a physical table from a temp table in the stored
procedure. I've been pulling my hair out trying to figure out a way to
fix it. I'm pasting the code below along with my print statement and
comment showing where the error occurs. Any help is GREATLY
appreciated. Thanks!
--ALTER PROCEDURE [dbo].SP_OCCalculationMVTriggerTest
declare @.dtAsOfdate DATETIME
set @.dtAsOfDate = '2006-04-16';
DECLARE @.RC INTEGER
--
-- 1) Eligible Investments:
--
-- Input: @.SPVId - SPV we are running process for
-- @.Yes - value of enum CCPEnum::eYesNoYes (get by
lookup).
-- Output: Recordset (temp table) of Collaterals that are eligible for
MV Test (#MVTriggerInvestments).
DECLARE @.Yes INTEGER
EXEC @.RC = [dbo].CPLookupVal 'YesNo', 'Yes', @.Yes OUTPUT
IF (@.RC<>0)BEGIN
RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes
enum', 16, 1) WITH SETERROR
END
drop table #MVTriggerInvestments
BEGIN
SELECT dbal.SPVId,
dbal.CusipId,
dbal.GroupId,
@.dtAsOfDate AS AsOfDate,
dbal.NormalOCRate,
dbal.SteppedUpOCRate,
dbal.AllocMarketValue AS MarketValue,
dbal.NbrDays,
dbal.PriceChangeRatio
INTO #MVTriggerInvestments
FROM DailyCollateralBalance dbal
JOIN CollateralGroupIncludeInOC gin
ON dbal.SPVId = 2
AND gin.SPVId = 2
AND dbal.AsOfDate = '2006-04-16'
AND @.dtAsOfDate BETWEEN gin.EffectiveFrom AND
gin.EffectiveTo
AND dbal.GroupId = gin.GroupId
AND gin.IncludeInOC = @.Yes
END
select * from #MVTriggerInvestments
print 'end #1'
--select * from #MVTriggerInvestments --looks ok
---
-- 2) Calculate Weighted Average Price change ratio Market Value (by
Group):
-- PCRMV - Price Change Ratio Market Value
---
-- Input : Recordset of collaterals (having New/Old prices, MarketValue
defined)
-- Output: Recordset Aggregated by Group (#GroupOCRate)
drop table #MVTriggerGroup
BEGIN
SELECT A.SPVId,
A.AsOfDate,
A.GroupId,
A.NormalOCRate,
A.SteppedUpOCRate,
A.MarketValue,
[dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue,
B.MarketValueForPeriod, 0.00) as PriceChangeRatio,
CAST (0 AS NUMERIC(12,9))
AS OCRate,
CAST ('' AS VARCHAR(6))
AS OCRateType,
CAST (0 AS NUMERIC(18,2))
AS DiscMarketValue,
CAST (0 AS NUMERIC(18,2))
AS InterestAccrued
INTO #MVTriggerGroup
FROM
(
SELECT SPVId,
AsOfDate,
GroupId,
NormalOCRate,
SteppedUpOCRate,
SUM(MarketValue) AS MarketValue
FROM #MVTriggerInvestments
GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate,
SteppedUpOCRate
) A --works up to here
JOIN
(SELECT SPVId,
SUM(AllocMarketValue) AS MarketValueForPeriod,
SUM(AllocMarketValue * PriceChangeRatio) as
PriceChangeRatioMarketValue,
GroupId
FROM T_DailyCollateralBalance
WHERE SPVId = 2
AND AsOfDate between '2006-03-17' and '2006-04-15'
AND IsBusinessDay = 1
GROUP BY SPVId, GroupId
) B
ON A.SPVId = B.SPVId
AND A.GroupId = B.GroupId
END
print 'end #2'
---
-- Calculate OCRate to apply for each group.
---
BEGIN
UPDATE #MVTriggerGroup
SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND
ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
ELSE NormalOCRate
END),
OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND
ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
ELSE 'normal'
END)
END
print 'end #3'
--
-- Calculate discounted Market Value
--
UPDATE #MVTriggerGroup
SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
print 'end #4'
--
-- Insert data from temp tables
--
-- 1)
select * from #MVTriggerInvestments
print 'begin tran'
BEGIN TRAN
DELETE T_MVTriggerInvestments
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
print 'DELETE T_MVTriggerInvestments'
--error is
here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
INSERT T_MVTriggerInvestments
(
SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
OldPrice ,
NewPrice ,
PriceChangeRatio
)
SELECT SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
0.00 ,
0.00 ,
PriceChangeRatio
FROM #MVTriggerInvestments
print 'end mvtriggerinv select'
COMMIT TRAN
--end
error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!
-- 2)
print 'begin tran 2'
BEGIN TRAN
DELETE T_OCTestGroup
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
INSERT T_OCTestGroup
(
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
SectionA ,
CPFace ,
IntExpense ,
Fees ,
SectionB ,
Receivables ,
IntReceivables ,
CashBalance ,
Investments ,
SectionC ,
ExcessCollateral,
MaxCPAllowed
)
SELECT
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0
FROM #MVTriggerGroup
print 'end tran 2'
COMMIT TRANThis is what the data looks like:
2 3128HDZR3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .15469
2 3128JLT66 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 22764923.64 693 .40260
2 3128JLVQ9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 33325930.88 693 .12272
2 3128JMY50 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 480 -.37161
2 3128NCB86 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 3128NCNV2 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 19 .00000
2 31295KSK1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 -.10075
2 31295NBU1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .97381
2 31295NCW6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .23484
2 31295NDA3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3445254.54 956 .51738
2 31295NMK1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .18800
2 31295NNJ3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4723304.57 956 -.06034
2 31295NUE6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 10974246.83 956 -.33549
2 31295NUG1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .09947
2 31295NUR7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3840958.96 956 -.41991
2 31295NZ76 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4679164.24 956 -.24881
2 31390YZH4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3333555.16 956 -.11334
2 31402C6D9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 46830311.23 689 .74464
2 31402DB25 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 689 .27197
2 31402DBZ2 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 689 .78266
2 31404EXL5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7785530.05 721 -.22302
2 31404LPV6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7814559.22 755 .72999
2 31404MBN7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 721 .03317
2 31405JJ21 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 480 -.26118
2 31409ARA9 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409BB71 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409BGD3 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 31409DSH7 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409DSM6 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 7 3083.50000
2 31409DVN0 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 31409UYN9 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 36225CWV4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 1575644.70 956 -.14477
2 36225DAY0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 8995207.86 693 -.17400
2 31337NND6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .00290
2 31349SEA7 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 26777158.30 875 -.32595
2 31349SHH9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 19822156.78 840 -.22582
2 31387A3M5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .50317
2 31391BKL0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4267353.02 956 -.98672
2 31391CJA4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7446628.34 956 -1.00060
2 31391NM65 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 2783766.24 956 -.33597
2 31404E2E5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 23112826.15 689 -.16876
2 31404EEJ1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 12078241.02 784 .10121
2 31404JRP2 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 16699637.14 755 .28834
2 31404JVR3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 10781379.91 755 .43313
2 31404JWZ4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 11040810.52 662 .46652
2 31404JXW0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3883162.72 721 .27610
2 31404JXZ3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .06 662 .00905
2 31404KRS3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 693 -.20539
2 36225C4Y9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 784 -.04323
2 36225C6Y7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 721 -.13550
2 36225C7B6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 19935755.89 721 -.15476
2 36225DAV6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 11457579.74 693 -.15476
Wish I could upload a spreadsheet...sql

Help with an expression

The error is:
The value expression for the textbox â'BillAddressâ' contains an error:
[BC30201] Expression expected.
=If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value) = 0 AND
Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line2.Value) = 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line1.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.Value)= 0 AND
Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
" " + Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value
Else If Len(Fields!BillTo_Line1.value)= 0 AND
Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
Fields!AcctName.Value + Chr(10) +
"No Address Available" + Chr(10) +
"ID: " + AccountNumber
Many ThanksJeff,
I thought VB was the scripting language to be used in expressions. Why
are you concatinating with '+' operators, when you should be using '&'
operators?
thx
-jsh
"Jeff Metcalf" wrote:
> The error is:
> The value expression for the textbox â'BillAddressâ' contains an error:
> [BC30201] Expression expected.
>
> =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr (10) +
> Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line3.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
>
> Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line1.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> Fields!AcctName.Value + Chr(10) +
> Fields!BillTo_Line2.Value + Chr(10) +
> Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> "ID: " + Fields!AccountNumber.Value
> Else If Len(Fields!BillTo_Line1.value)= 0 AND
> Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> Fields!AcctName.Value + Chr(10) +
> "No Address Available" + Chr(10) +
> "ID: " + AccountNumber
> Many Thanks|||Beats me...I'm just trying to get it to work. The '+' operator works for
for concatinating in other SRS reports and in SQL. I'm not a VB or .Net
programmer, so I have no idea if it's right or not, lol.
"jsh02_nova@.hotmail.com" wrote:
> Jeff,
> I thought VB was the scripting language to be used in expressions. Why
> are you concatinating with '+' operators, when you should be using '&'
> operators?
> thx
> -jsh
> "Jeff Metcalf" wrote:
> > The error is:
> >
> > The value expression for the textbox â'BillAddressâ' contains an error:
> > [BC30201] Expression expected.
> >
> >
> > =If Len(Fields!BillTo_Line1.Value)= 0 And Len(Fields!BillTo_Line2.Value) > 0
> > And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr (10) +
> > Fields!BillTo_City.value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line3.Value)= 0 And Len(Fields!BillTo_Line1.Value)
> > > 0 And Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value) = 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 And Len(Fields!BillTo_Line3.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line3.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> >
> > Else If Len(Fields!BillTo_Line2.Value) = 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line1.Value)> 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line1.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.Value)= 0 AND
> > Len(Fields!BillTo_Line3.Value)= 0 AND Len(Fields!BillTo_Line2.Value) > 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > Fields!BillTo_Line2.Value + Chr(10) +
> > Fields!BillTo_City.Value + ", " + Fields!BillTo_StateOrProvince.Value +
> > " " + Fields!BillTo_PostalCode.Value + Chr(10) +
> > "ID: " + Fields!AccountNumber.Value
> >
> > Else If Len(Fields!BillTo_Line1.value)= 0 AND
> > Len(Fields!BillTo_Line2.Value)= 0 AND Len(Fields!BillTo_Line3.value) = 0 Then
> > Fields!AcctName.Value + Chr(10) +
> > "No Address Available" + Chr(10) +
> > "ID: " + AccountNumber
> >
> > Many Thanks|||I'm not sure if this is what's causing the problem but SRS uses an
"inline if" -IIF- instead of IF THEN ELSE.
The syntax is =IIF(test expression,true action, false action).
Using your expression from above it would be something like this:
=IIf( Len(Fields!BillTo_Line1.Value)= 0 And
Len(Fields!BillTo_Line2.Value) > 0
And Len(Fields!BillTo_Line3.Value) > 0 ,
Fields!AcctName.Value + Chr(10) +
Fields!BillTo_Line2.Value + Chr(10) +
Fields!BillTo_Line3.Value + Chr (10) +
Fields!BillTo_City.value + ", " +
Fields!BillTo_StateOrProvince.Value + " " +
Fields!BillTo_PostalCode.Value + Chr(10) +
"ID: " + Fields!AccountNumber.Value ,
IIF( Len(Fields!BillTo_Line2.Value)= 0 And
Len(Fields!BillTo_Line1.Value) > 0 And Len(Fields!BillTo_Line3.Value) >
0 , and so on
Nested IIFs can get really ugly. If I were you and if it's possible
I'd do as much as you can with case statements in SQL.
Good luck

Help with an error message

I keep getting this error message when I try to restore a database backup that I have made.

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Databases\GTT_Data.MDF" failed with the operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)

What I have done is created a backup of my sql sever database and I am now trying to restore it into sql server express for someone to play around with without it affecting my actual database. This is where the error messages are coming from.

Please help

Are you getting this during the restore ? If the machines are not the same for backup and restore you might not have the drives / folders available as they are on the original system. If so you either have to create those folders or Restore the database with the WITH MOVE option to specify a new destination. (thats because the original location is tored in the backup file).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Yes its during the restore. Yes the machines are not the same. Please could you give me further details on how to restore the databse with the WITH MOVE option.

Thanks for your help

|||Hi,

look in your BOL (Books online, the Online help of SQL Server)

There is a point Restore a database and move Files which will help you

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/877ecd57-3f2e-4237-890a-08f16e944ef1.htm

RESTORE DATABASE MyAdvWorks
FROM MyAdvWorks_1
WITH NORECOVERY,
MOVE 'MyAdvWorks' TO
'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf',
MOVE 'MyAdvWorksLog1'
TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf'
RESTORE LOG MyAdvWorks
FROM MyAdvWorksLog1
WITH RECOVERY

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Help with an error I don''t understand

Hey everyone,

I'm trying to make an image appear differently depending on the values of certain data. The statement that I am using is:

= IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)>15,"redball",IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)<8,"greenball","yellowball"))

And the error that I get is:

[rsRuntimeErrorInExpression] The Value expression for the image ‘image4’ contains an error: Argument 'Interval' is not a valid value.

Preview complete -- 0 errors, 1 warnings

When I preview the report I just get the typical white box with a red x where the image should be. I don't understand.

Thanks in advance,

Keith

Nevermind, argument needs to be "d". Thanks anyway!

-Keith

Help with an error I don''t understand

Hey everyone,

I'm trying to make an image appear differently depending on the values of certain data. The statement that I am using is:

= IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)>15,"redball",IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)<8,"greenball","yellowball"))

And the error that I get is:

[rsRuntimeErrorInExpression] The Value expression for the image ‘image4’ contains an error: Argument 'Interval' is not a valid value.

Preview complete -- 0 errors, 1 warnings

When I preview the report I just get the typical white box with a red x where the image should be. I don't understand.

Thanks in advance,

Keith

Nevermind, argument needs to be "d". Thanks anyway!

-Keith

sql

Help with an error 823

See error below-
I am getting these almost constantly now. Running a Raid5 Server there
doesn't appear to be any "apparent" hardware failures but maybe I am missing
somthing. Any advice?
--
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 4/11/2005
Time: 4:20:15 PM
User: NT AUTHORITY\SYSTEM
Computer: LISTSERVER
Description:
Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x00000000bbe000 in
file 'd:\SQLServer\MSSQL\data\msdbdata.mdf'.
Data:
0000: 37 03 00 00 18 00 00 00 7......
0008: 0b 00 00 00 4c 00 49 00 ...L.I.
0010: 53 00 54 00 53 00 45 00 S.T.S.E.
0018: 52 00 56 00 45 00 52 00 R.V.E.R.
0020: 00 00 05 00 00 00 6d 00 .....m.
0028: 73 00 64 00 62 00 00 00 s.d.b...CP wrote:
> See error below-
> I am getting these almost constantly now. Running a Raid5 Server there
> doesn't appear to be any "apparent" hardware failures but maybe I am
> missing somthing. Any advice?
> --
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 4/11/2005
> Time: 4:20:15 PM
> User: NT AUTHORITY\SYSTEM
> Computer: LISTSERVER
> Description:
> Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset
> 0x00000000bbe000 in file 'd:\SQLServer\MSSQL\data\msdbdata.mdf'.
This might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433
David Gugick
Imceda Software
www.imceda.com

Help with an error 823

See error below-
I am getting these almost constantly now. Running a Raid5 Server there
doesn't appear to be any "apparent" hardware failures but maybe I am missing
somthing. Any advice?
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:4/11/2005
Time:4:20:15 PM
User:NT AUTHORITY\SYSTEM
Computer:LISTSERVER
Description:
Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x00000000bbe000 in
file 'd:\SQLServer\MSSQL\data\msdbdata.mdf'.
Data:
0000: 37 03 00 00 18 00 00 00 7......
0008: 0b 00 00 00 4c 00 49 00 ...L.I.
0010: 53 00 54 00 53 00 45 00 S.T.S.E.
0018: 52 00 56 00 45 00 52 00 R.V.E.R.
0020: 00 00 05 00 00 00 6d 00 .....m.
0028: 73 00 64 00 62 00 00 00 s.d.b...
CP wrote:
> See error below-
> I am getting these almost constantly now. Running a Raid5 Server there
> doesn't appear to be any "apparent" hardware failures but maybe I am
> missing somthing. Any advice?
> --
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 4/11/2005
> Time: 4:20:15 PM
> User: NT AUTHORITY\SYSTEM
> Computer: LISTSERVER
> Description:
> Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset
> 0x00000000bbe000 in file 'd:\SQLServer\MSSQL\data\msdbdata.mdf'.
This might help:
http://support.microsoft.com/default...b;en-us;826433
David Gugick
Imceda Software
www.imceda.com

Help with an error 823

See error below-
I am getting these almost constantly now. Running a Raid5 Server there
doesn't appear to be any "apparent" hardware failures but maybe I am missing
somthing. Any advice?
--
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 4/11/2005
Time: 4:20:15 PM
User: NT AUTHORITY\SYSTEM
Computer: LISTSERVER
Description:
Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x00000000bbe000 in
file 'd:\SQLServer\MSSQL\data\msdbdata.mdf'.
Data:
0000: 37 03 00 00 18 00 00 00 7......
0008: 0b 00 00 00 4c 00 49 00 ...L.I.
0010: 53 00 54 00 53 00 45 00 S.T.S.E.
0018: 52 00 56 00 45 00 52 00 R.V.E.R.
0020: 00 00 05 00 00 00 6d 00 .....m.
0028: 73 00 64 00 62 00 00 00 s.d.b...CP wrote:
> See error below-
> I am getting these almost constantly now. Running a Raid5 Server there
> doesn't appear to be any "apparent" hardware failures but maybe I am
> missing somthing. Any advice?
> --
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 4/11/2005
> Time: 4:20:15 PM
> User: NT AUTHORITY\SYSTEM
> Computer: LISTSERVER
> Description:
> Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset
> 0x00000000bbe000 in file 'd:\SQLServer\MSSQL\data\msdbdata.mdf'.
This might help:
http://support.microsoft.com/defaul...kb;en-us;826433
David Gugick
Imceda Software
www.imceda.com

Help with a(n) MSSQL error

Log SQL Server (Current - 2/5/2007 2:00:00 AM)

Source spid90

Message
Replication-Replication Merge Subsystem: agent 0042007B-0032-0042-3700-390038004400 failed. The reconciler process was stopped because the Subscriber that initiated the synchronization is no longer connected. If this failure continues, restart the Merge Agent with higher value for -InternetTimeout.

Any help would be appreciated!

This is a replication error, as I'm sure you can tell from the message. It sounds like the partner in replication couldn't be reached, so that's where you should start.

There are a few places you can check on this error, starting with Books Online and "Troubleshooting Replication".

Also, edit the merge agent job step and add these parameters:

-OutputVerboseLevel 2 -Output EnterYourFileName

Then run the merge agent job again and look the error details in the output file. You'll find more detail there.

HELP WITH A TRIGGER?

Hello.
Just curios about something.

I am trying to track down an error, and the details are boring, but what would help is this.
Can I do something in SQL Server 2005 that would inform me of ANY change in any field in any table in the database?

You should build a trigger in each table to do that.

You can check this also

User Tips: Receiving an Email When Database Data is Changed

Regards

help with @@IDENTITY

hello,

I've got a problem reading the @.@.identity in vb.net
I tried it the way below and get the error: Public member 'EOF' on type 'Integer' not found.
(--> means with rsLastIdent)

comm_user = "SET NOCOUNT ON; INSERT INTO user (firstname, lastname, company, emailAddress) VALUES ...); SELECT @.@.IDENTITY AS Ident;"
comm = new SqlCommand(comm_user, dbConnection)
dbConnection.Open()
Try
 rsLastIdent = comm.ExecuteNonQuery()
 Catch ex As Exception
 Response.Write("Exception:")
 Response.Write(ex.ToString)
 End Try
if NOT rsLastIdent.EOF then
feed_userID = rsLastIdent.Fields.Item("Ident").Value
end if

The sql-statement is correct - I tried it on SQL Server and got the correct result, so something is wrong with my vb.net code...

Please can anybody help me and tell me how to declare my rsLastIdent or another way to code it in vb.net to get the @.@.identity?

Thanks a lot!

timWhat does ExecuteNonQuery return? It is not what you are trying to get at.

Try .ExecuteScalar() and cast the returned value to an int.

Dim rsLastIdent as integer
rsLastIdent=Integer.Parse(comm.executeScalar().ToString())

There are more elegant ways to do this, I expect.|||Douglas,

Thanks for your help!

executeScalar() was the function I needed! It works perfect now!

Regards,
Tim

Help with "Problem generating manifest" error

Hi!

I'm using VS 2005 SP 1 on my first project with CE. I've recently run into the problem that if I do anything with my SDF file in Visual Studio I get the following error when I run the project (in debug mode from VS):

The process cannot access the file 'C:\xxx\MySDF.sdf' because it is being used by another process.

The solution is to close the project and reopen it however this is getting painful. Anyone else seen this problem?

Thanks!

This problem hasn't gone away....was hoping someone else has seen it and has a solution.

Thanks!

|||

Do you have the connection open in Server Explorer or any other place. Basically as long as there is some connection open to that database, you can not delete it. However, you can open multiple connection to the same database. We support multiple connecitons.

Thanks,

Laxmi

Help with "Problem generating manifest" error

Hi!

I'm using VS 2005 SP 1 on my first project with CE. I've recently run into the problem that if I do anything with my SDF file in Visual Studio I get the following error when I run the project (in debug mode from VS):

The process cannot access the file 'C:\xxx\MySDF.sdf' because it is being used by another process.

The solution is to close the project and reopen it however this is getting painful. Anyone else seen this problem?

Thanks!

This problem hasn't gone away....was hoping someone else has seen it and has a solution.

Thanks!

|||

Do you have the connection open in Server Explorer or any other place. Basically as long as there is some connection open to that database, you can not delete it. However, you can open multiple connection to the same database. We support multiple connecitons.

Thanks,

Laxmi

Help with "start job" error

good morning everyone,
I have created some scheduled jobs which basically are transfer data from Oracle DB to SQL server through DTS package. I created DTS pacakages and right click to schedule the job for daily run.
I was able to execute DTS package to transfer data w/o problems at all. However it seemed scheduled job could not run automatically nor I kick off the job manually.
I have attached the error message below. It seemed I need some components be installed. Would someone take a look and let me know what is it, name of the components, probably cost information? Thank you very much for the help in advance.When you manually execute a DTS job, it runs under your login and with the resources of your local machine. When the scheduled job is run by the SQLServer Agent, it runs under the permissions of the login property that was specified in the Administrative Services tab with the physical resources (disk drives, folder mappings, etc.) of the SQL Server that the agent resides on.

The attached error looks like the Oracle components are missing from the SQL Server. Once you have those installed, you might want to also verify that the login used by SQL Server Agent has permisisons on the Oracle server.|||check to make sure your Sqlagent startup acct has proper permission. If the job is invoked by non-admin, agent proxy acct will be used. Take a look at the following to set the proxy acct.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp