Can anyone tell me the length and Field Types for the Logon Tracker SQL table.

[Davit2005]

The create database failed and I now can't logon to HAP anymore.

 

I am getting the error message

String or binary data would be truncated. The statement has been terminated.

 

This is one of many errors, so near yet so far away.

 

Is there any way of re-running the SQL table creater. Also what are the SQL permissions supposed to be, I have created the HAP user in SQL.

 

Any help would be appreciated.

[nickbro]

Open the hapConfig.xml file, change the provider back to XML.

 

Drop the database and recreate it

[Davit2005]

Thanks for quick response.

 

We are using v7.9.0105.2130

 

We don't seem to have the ~/app_data/hap.sql to create the tables. I have created the HAP user as documented, I've done a bit of SQL database building before but no a lot.

 

This is the current error message when I run the upgrade to covert from XML2SQL

 

Cannot open database "hap" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\HAP'.

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: Cannot open database "hap" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\HAP'.

 

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): Cannot open database "hap" requested by the login. The login failed.

Login failed for user 'IIS APPPOOL\HAP'.]

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009598

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234

System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275

System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +35

System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) +183

System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +239

System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +195

System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +232

System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +185

System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +33

System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +524

System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66

System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +479

System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +108

System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +126

System.Data.SqlClient.SqlConnection.Open() +125

System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +378

HAP.Data.SQL.Tracker.UpgradeFromXML() +702

HAP.Web.Tracker.XML2SQL.upgrade_Click(Object sender, EventArgs e) +12

System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118

System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10

System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13

System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

 

 

 

 

--------------------------------------------------------------------------------

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

[nickbro]

SQL Server Management Console

Security

Logins

Right Click

New Login

Search

IISApp Pool\HAP

Search the local machine

ok

User Mappings

Tick the HAP Database

Select db owner role

Ok

[Davit2005]

Thanks again, think we are getting there, the end users will love it like I do (hope).

 

Within the app_data folder I can't find the XML.SQL as per documentation '4. Execute ~/app_data/hap.sql to create the tables'

 

hence when I run the upgrade I get the error 'Invalid object name 'dbo.TrackerEvents'.'

 

When looking in the HAP database on the SQL I can only see the System Tables.

 

Regards,

David

[nickbro]

Sorry about that, seems that Codeplex isn't syncing it.

 

Here's the correct script

USE [hap]
GO
/****** Object:  Table [dbo].[WebTrackerEvents]    Script Date: 02/29/2012 15:30:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WebTrackerEvents](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [DateTime] [datetime] NOT NULL,
   [username] [nvarchar](50) NOT NULL,
   [ComputerName] [nvarchar](50) NOT NULL,
   [EventType] [nvarchar](50) NOT NULL,
   [iP] [nvarchar](50) NOT NULL,
   [browser] [nvarchar](50) NOT NULL,
   [OS] [nvarchar](50) NOT NULL,
   [Details] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_WebTrackerEvents_1] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TrackerEvents]    Script Date: 02/29/2012 15:30:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TrackerEvents](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [LogonDateTime] [datetime] NOT NULL,
   [username] [nvarchar](50) NOT NULL,
   [ComputerName] [nvarchar](50) NOT NULL,
   [LogoffDateTime] [datetime] NULL,
   [domainname] [nvarchar](50) NOT NULL,
   [ip] [nvarchar](50) NOT NULL,
   [logonserver] [nvarchar](50) NOT NULL,
   [os] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_TrackerEvents_1] PRIMARY KEY CLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO