Shared Connection logs

Can you verify in the application log if you can find an error? It's in the menu Help->Application log.

Hi David

This is the error that I am getting:


[6/6/2011 3:06 PM]ERROR SILENT System.Data.SqlClient.SqlException: Invalid column name 'CustomerID'.
Invalid column name 'UserInfoID'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteNonQuery(String sql, IDbDataParameter[] parameters)
at Devolutions.RemoteDesktopManager.Business.DataSources.SqlServerLogSubDataSource.InsertLog(LogConnectionOpenedInfo logConnectionOpenedInfo)

Gaz

Hum,
This might explain your problem, I don't know why but it seems that some fields are missing from the database. Can you run a script:


IF NOT EXISTS (select * FROM syscolumns where name = 'LoggedUserName' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD LoggedUserName varchar(256) NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'GroupName' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD GroupName varchar(512) NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'StartDateTimeUTC' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD StartDateTimeUTC datetime NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'EndDateTimeUTC' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD EndDateTimeUTC datetime NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'Cost' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD Cost money NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'Comment' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD Comment text NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'Prompt' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD Prompt text NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'Data' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD Data text NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'Settings' AND id = object_id(N'[UserGroupInfo]'))
ALTER TABLE UserGroupInfo ADD Settings text NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'IsTemplate' AND id = object_id(N'[UserInfo]'))
ALTER TABLE UserInfo ADD IsTemplate bit NOT NULL default(0);

IF NOT EXISTS (select * FROM dbo.sysobjects where id = object_id(N'[Customer]')) BEGIN
CREATE TABLE Customer (
ID uniqueidentifier DEFAULT newid() NOT NULL,
SecurityGroup uniqueidentifier null FOREIGN KEY REFERENCES GroupInfo(ID),
Data Text,
PRIMARY KEY (ID)
);
END;

IF NOT EXISTS (select * FROM syscolumns where name = 'CustomerID' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD CustomerID uniqueidentifier null FOREIGN KEY REFERENCES Customer(ID);

IF NOT EXISTS (select * FROM syscolumns where name = 'CustomerID' AND id = object_id(N'[Connections]'))
ALTER TABLE Connections ADD CustomerID uniqueidentifier null FOREIGN KEY REFERENCES Customer(ID);

IF NOT EXISTS (select * FROM dbo.sysobjects where id = object_id(N'[Todo]')) BEGIN
CREATE TABLE Todo (
ID uniqueidentifier DEFAULT newid() NOT NULL,
ConnectionID uniqueidentifier null FOREIGN KEY REFERENCES Connections(ID),
CreationDate datetime not null,
DueDate datetime,
Name varchar(250),
Priority int,
Data Text,
PRIMARY KEY (ID)
);
END;

IF NOT EXISTS (select * FROM dbo.sysobjects where id = object_id(N'[Inventory]')) BEGIN
CREATE TABLE Inventory (
ID uniqueidentifier DEFAULT newid() NOT NULL,
ConnectionID uniqueidentifier null FOREIGN KEY REFERENCES Connections(ID),
CreationDate datetime not null,
Data Text,
PRIMARY KEY (ID)
);
END;

IF NOT EXISTS (select * FROM dbo.sysobjects where id = object_id(N'[Monitoring]')) BEGIN
CREATE TABLE Monitoring (
ID uniqueidentifier DEFAULT newid() NOT NULL,
ConnectionID uniqueidentifier null FOREIGN KEY REFERENCES Connections(ID),
CreationDate datetime not null,
Data Text,
PRIMARY KEY (ID)
);
END;
GO

-- Version 1.13

IF NOT EXISTS (select * FROM syscolumns where name = 'CustomerID' AND id = object_id(N'[todo]'))
ALTER TABLE todo ADD CustomerID uniqueidentifier null FOREIGN KEY REFERENCES Customer(ID);

GO

-- Version 1.14

IF NOT EXISTS (select * FROM syscolumns where name = 'ManualEndDateTime' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD ManualEndDateTime datetime NULL;

GO

-- Version 1.15

IF NOT EXISTS (select * FROM syscolumns where name = 'UserInfoID' AND id = object_id(N'[Todo]'))
ALTER TABLE Todo ADD UserInfoID uniqueidentifier null FOREIGN KEY REFERENCES UserInfo(ID) ON DELETE CASCADE;

IF NOT EXISTS (select * FROM syscolumns where name = 'UserInfoID' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD UserInfoID uniqueidentifier null FOREIGN KEY REFERENCES UserInfo(ID);

IF NOT EXISTS (select * FROM syscolumns where name = 'ManualClosedBy' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD ManualClosedBy varchar(256) null;
GO

-- Version 1.16

IF NOT EXISTS (select * FROM syscolumns where name = 'SecurityGroup' AND id = object_id(N'[todo]'))
ALTER TABLE todo ADD SecurityGroup uniqueidentifier null FOREIGN KEY REFERENCES GroupInfo(ID);

GO

-- Version 1.17

IF NOT EXISTS (select * FROM syscolumns where name = 'SecurityGroup' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD SecurityGroup uniqueidentifier null;
GO

-- Version 1.18

IF NOT EXISTS (select * FROM syscolumns where name = 'SupportClose' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD SupportClose bit null;
GO

-- Version 1.19

IF NOT EXISTS (select * FROM syscolumns where name = 'OpenMode' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD OpenMode int null;

IF NOT EXISTS (select * FROM syscolumns where name = 'CloseMode' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD CloseMode int null;

GO

-- Version 1.20

IF NOT EXISTS (select * FROM syscolumns where name = 'OpenMode' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD OpenMode int null;

IF NOT EXISTS (select * FROM syscolumns where name = 'CloseMode' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD CloseMode int null;

IF NOT EXISTS (select * FROM syscolumns where name = 'HostName' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD HostName varchar(100) null;
GO

-- Version 1.21

IF NOT EXISTS (select * FROM syscolumns where name = 'IsEmbedded' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD IsEmbedded bit null;
GO

-- Version 1.22

IF NOT EXISTS (select * FROM syscolumns where name = 'ConnectionType' AND id = object_id(N'[Connections]')) BEGIN
ALTER TABLE Connections ADD ConnectionType int NULL
END;

IF NOT EXISTS (select * FROM syscolumns where name = 'ConnectionSubType' AND id = object_id(N'[Connections]')) BEGIN
ALTER TABLE Connections ADD ConnectionSubType VARCHAR(100) NULL
END;

IF NOT EXISTS (select * FROM syscolumns where name = 'GroupName' AND id = object_id(N'[Connections]')) BEGIN
ALTER TABLE Connections ADD GroupName VARCHAR(400) NULL
END;

IF NOT EXISTS (select * FROM syscolumns where name = 'Name' AND id = object_id(N'[Connections]')) BEGIN
ALTER TABLE Connections ADD Name VARCHAR(200) NULL
END;
GO

-- Set the database version
UPDATE DatabaseInfo SET DatabaseVersion=22
GO

Hi David

I ran the script and got this error:

Msg 1776, Level 16, State 0, Line 32
There are no primary or candidate keys in the referenced table 'GroupInfo' that match the referencing column list in the foreign key 'FK__Customer__Securi__267ABA7A'.
Msg 1750, Level 16, State 0, Line 32
Could not create constraint. See previous errors.
Msg 4902, Level 16, State 1, Line 5
Cannot find the object "todo" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 5
Cannot find the object "Todo" because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 5
Cannot find the object "todo" because it does not exist or you do not have permissions.

(1 row(s) affected)

Gaz

Hi,
There is some error in the script, but I think that the connectionlog table is now created successfully, can you confirm?

Hi David

No still having the same issues.

[6/7/2011 8:45 AM]ERROR SILENT System.Data.SqlClient.SqlException: Invalid column name 'CustomerID'.
Invalid column name 'UserInfoID'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteNonQuery(String sql, IDbDataParameter[] parameters)
at Devolutions.RemoteDesktopManager.Business.DataSources.SqlServerLogSubDataSource.InsertLog(LogConnectionOpenedInfo logConnectionOpenedInfo)

Gaz

Hi David

Any news on this? I am still having the same issues.
I have found that if I export the session list and import it into a new database it seems to work ok but I loose all the security setting we have setup.

Gaz

Hi,
Sorry I missed you answer:

IF NOT EXISTS (select * FROM syscolumns where name = 'CustomerID' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD CustomerID uniqueidentifier null;

IF NOT EXISTS (select * FROM syscolumns where name = 'UserInfoID' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD UserInfoID uniqueidentifier null;

This will at least add the missing columns.

Hi David

That seems to have got it to start logging the connections but it does not log the End date/Time.


[6/9/2011 11:03 AM]ERROR SILENT System.Data.SqlClient.SqlException: Invalid column name 'ManualClosedBy'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteNonQuery(String sql, IDbDataParameter[] parameters)
at Devolutions.RemoteDesktopManager.Business.DataSources.SqlServerLogSubDataSource.LogConnectionClosed(LogConnectionClosedInfo logConnectionClosedInfo)


Gaz

Ok,
one missing field again



IF NOT EXISTS (select * FROM syscolumns where name = 'ManualEndDateTime' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD ManualEndDateTime datetime NULL;

IF NOT EXISTS (select * FROM syscolumns where name = 'SecurityGroup' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD SecurityGroup uniqueidentifier null;


-- Version 1.18

IF NOT EXISTS (select * FROM syscolumns where name = 'SupportClose' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD SupportClose bit null;
GO

-- Version 1.19

IF NOT EXISTS (select * FROM syscolumns where name = 'OpenMode' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD OpenMode int null;

IF NOT EXISTS (select * FROM syscolumns where name = 'CloseMode' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD CloseMode int null;

GO

-- Version 1.20

IF NOT EXISTS (select * FROM syscolumns where name = 'OpenMode' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD OpenMode int null;

IF NOT EXISTS (select * FROM syscolumns where name = 'CloseMode' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD CloseMode int null;

IF NOT EXISTS (select * FROM syscolumns where name = 'HostName' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD HostName varchar(100) null;
GO

-- Version 1.21

IF NOT EXISTS (select * FROM syscolumns where name = 'IsEmbedded' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD IsEmbedded bit null;
GO

-- Version 1.22

IF NOT EXISTS (select * FROM syscolumns where name = 'ConnectionType' AND id = object_id(N'[Connections]')) BEGIN
ALTER TABLE Connections ADD ConnectionType int NULL
END;

IF NOT EXISTS (select * FROM syscolumns where name = 'ConnectionSubType' AND id = object_id(N'[Connections]')) BEGIN
ALTER TABLE Connections ADD ConnectionSubType VARCHAR(100) NULL
END;

IF NOT EXISTS (select * FROM syscolumns where name = 'GroupName' AND id = object_id(N'[Connections]')) BEGIN
ALTER TABLE Connections ADD GroupName VARCHAR(400) NULL
END;

IF NOT EXISTS (select * FROM syscolumns where name = 'Name' AND id = object_id(N'[Connections]')) BEGIN
ALTER TABLE Connections ADD Name VARCHAR(200) NULL
END;
GO

-- Set the database version
UPDATE DatabaseInfo SET DatabaseVersion=22
GO

Hi David

I have run the script and it ran without error but it has still not resolved the problem.

I have cleared out the log and closed Remote Desktop Manager and then started it and connected to a Server and disconnected, the connection gets logged but the disconnection does not.
Here is the logs from when I closing RDM to opening a new session and then closing it:


[6/9/2011 2:30 PM]ERROR SILENT System.Data.SqlClient.SqlException: Invalid column name 'ManualClosedBy'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteNonQuery(String sql, IDbDataParameter[] parameters)
at Devolutions.RemoteDesktopManager.Business.DataSources.SqlServerLogSubDataSource.LogConnectionClosed(LogConnectionClosedInfo logConnectionClosedInfo)
[6/9/2011 2:30 PM]ERROR SILENT System.Data.SqlClient.SqlException: Invalid column name 'ManualClosedBy'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteNonQuery(String sql, IDbDataParameter[] parameters)
at Devolutions.RemoteDesktopManager.Business.DataSources.SqlServerLogSubDataSource.LogConnectionClosed(LogConnectionClosedInfo logConnectionClosedInfo)
[6/9/2011 2:31 PM]ERROR SILENT System.Data.SqlClient.SqlException: Invalid column name 'ManualClosedBy'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.ExecuteNonQuery(String sql, IDbDataParameter[] parameters)
at Devolutions.RemoteDesktopManager.Business.DataSources.SqlServerLogSubDataSource.LogConnectionClosed(LogConnectionClosedInfo logConnectionClosedInfo)


Regards
Gaz

IF NOT EXISTS (select * FROM syscolumns where name = 'ManualClosedBy' AND id = object_id(N'[ConnectionLog]'))
ALTER TABLE ConnectionLog ADD ManualClosedBy varchar(256) null;
GO

Hi David

That looks like it has fixed it, thanks for all your help.

Gaz