Database options within Connection

[sjmcdond]

In an earlier reply to another person's post about a problem using the TMSChangeNotification component, you replied:

"You can set these options on the server or for each connection in your application."

I know how to set them on the server, but how do you do it within a connection? Placing them after a TMSQuery causes errors. The connection options are listed below.

Thanks!


SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ARITHABORT ON
GO

[AndreyZ]

Hello,

You can use the following code:

Code: Select all

MSConnection1.ExecSQL('SET ANSI_NULLS ON;' +
                      'SET ANSI_PADDING ON;' +
                      'SET ANSI_WARNINGS ON;' +
                      'SET CONCAT_NULL_YIELDS_NULL ON;' +
                      'SET QUOTED_IDENTIFIER ON;' +
                      'SET NUMERIC_ROUNDABORT OFF;' +
                      'SET ARITHABORT ON;', []);

, or using TMSQuery:

Code: Select all

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('SET ANSI_NULLS ON;');
MSQuery1.SQL.Add('SET ANSI_PADDING ON;');
MSQuery1.SQL.Add('SET ANSI_WARNINGS ON;');
MSQuery1.SQL.Add('SET CONCAT_NULL_YIELDS_NULL ON;');
MSQuery1.SQL.Add('SET QUOTED_IDENTIFIER ON;');
MSQuery1.SQL.Add('SET NUMERIC_ROUNDABORT OFF;');
MSQuery1.SQL.Add('SET ARITHABORT ON;');
MSQuery1.Execute;

[sjmcdond]

Well, you learn something every day

However, that didn't solve my problem: I still get the same error message. And I can't make the changes permanent (which DOES solve the problem), as there are a lot of legacy apps using this database, and I have no idea how the changes will affect them.

The relevant code is below. I appreciate your help!

object qReservations: TMSQuery
DataTypeMap = <>
Connection = DevConnection
SQL.Strings = (
'select Reservations.ReservationID as ID, '
''

' [Start] = DATEADD( day, 0, DATEDIFF( day, 0, Reservations' +
'.ArrivalDate ) ) +'

' DATEADD( day, 0 - DATEDIFF( day, 0, Reservation' +
's.ArrivalTime ), Reservations.ArrivalTime ),'
''
' [Finish] = DATEADD( minute, Reservations.Duration, '

' ( DATEADD( day, 0, DATEDIFF( day, 0, Reservati' +
'ons.ArrivalDate ) ) +'

' DATEADD( day, 0 - DATEDIFF( day, 0, Reservat' +
'ions.ArrivalTime ), Reservations.ArrivalTime ) ) ),'
' '
' GROUPS.[Name] as [Caption],'
' CASE '
' WHEN Reservations.AdultQuantity = 0 then '#39'No Adults'#39
' WHEN Reservations.AdultQuantity = 1 then '#39'1 Adult'#39

' WHEN Reservations.AdultQuantity > 1 then CAST( Reservat' +
'ions.AdultQuantity as VarChar ) + '#39' Adults'#39
' END + '#39' / '#39' +'
' CASE '
' WHEN Reservations.ChildQuantity = 0 then '#39'No Children'#39
' WHEN Reservations.ChildQuantity = 1 then '#39'1 Child'#39

' WHEN Reservations.ChildQuantity > 1 then CAST( Reservat' +
'ions.ChildQuantity as VarChar ) + '#39' Children'#39
' END as Location, '
' 0 as EventType,'
' 2 as [Options],'
' Reservations.ColorCode as LabelColor,'
' Reservations.Comments as [Message]'
'from Res_Reservations Reservations'

' Join Res_Groups Groups on ( Groups.GroupId = Reservations.G' +
'roupId )'
''

'Where ( Reservations.Cancelled = 0 ) and ( Reservations.ArrivalD' +
'ate >= (GetDate() -365 ) )'
'Order By Start')
Options.ReflectChangeNotify = True
ChangeNotification = MSChangeNotification1
Left = 112
Top = 16
end
object dsReservations: TMSDataSource
AutoEdit = False
DataSet = qReservations
Left = 193
Top = 16
end
object DevConnection: TMSConnection
Database = 'IWM'
Options.Provider = prNativeClient
Options.KeepDesignConnected = False
Options.NativeClientVersion = ncAuto
DataTypeMap = <>
Username = 'sa'
Password = 'qwertypoiuyt'
Server = 'RALPH'
AfterConnect = DevConnectionAfterConnect
BeforeDisconnect = DevConnectionBeforeDisconnect
LoginPrompt = False
Left = 25
Top = 16
end
object MSChangeNotification1: TMSChangeNotification
Enabled = False
OnChange = MSChangeNotification1Change
Left = 376
Top = 32
end

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

procedure TForm1.Button1Click(Sender: TObject);
begin
qReservations.Active := DevConnection.Connected;
end;

procedure TForm1.DevConnectionAfterConnect(Sender: TObject);
begin
DevConnection.ExecSQL('SET ANSI_NULLS ON;' +
'SET ANSI_PADDING ON;' +
'SET ANSI_WARNINGS ON;' +
'SET CONCAT_NULL_YIELDS_NULL ON;' +
'SET QUOTED_IDENTIFIER ON;' +
'SET NUMERIC_ROUNDABORT OFF;' +
'SET ARITHABORT ON;', []);
end;

procedure TForm1.DevConnectionBeforeDisconnect(Sender: TObject);
begin
MSChangeNotification1.Enabled := False;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
DevConnection.Connected := True;
MSChangeNotification1.Enabled := DevConnection.Connected;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
qReservations.Active := False;
DevConnection.Connected := False;
end;

procedure TForm1.MSChangeNotification1Change(Sender: TObject; DataSet: TCustomMSDataSet; NotificationInfo: TMSNotificationInfo; NotificationSource: TMSNotificationSource; NotificationType: TMSNotificationType);
begin
Memo1.Lines.Add( Format( 'Change Occurred on %s : %s : %s : %s', [ DataSet.Name,
GetEnumName( TypeInfo( TMSNotificationInfo ), Integer( NotificationInfo ) ),
GetEnumName( TypeInfo( TMSNotificationSource ), Integer( NotificationSource ) ),
GetEnumName( TypeInfo( TMSNotificationType ), Integer( NotificationType ) ) ] ) );
end;

[AndreyZ]

You didn't specify the error you encountered. Please specify the exact error that occurs when you use notifications.

[sjmcdond]

My apologies : The error message is "Multiple-step OLE DB operation generated errors. Check each OLE status value, if available. No work was done."

This occurs at the moment the ButtonClick event happens, where the TMSQuery is opened.

The TMSConnection is opened in FormCreate, along with enabling the TMSChangeNotification component.

The query runs fine without Notification Services, and was also tested in SQL Server Management Studio.

I'm using SQLServer 2005, Windows Server 2003 with an XP client. From dbMonitor, an Error condition is generated at the time the Query is executed. The TSQL statements are:

DECLARE @INVALID_SERVICE nvarchar(128);
SET @INVALID_SERVICE = N'SDAC_NS_119';
DECLARE @DROP_STATEMENT nvarchar(300);
WHILE @INVALID_SERVICE <> N''
BEGIN
SET @DROP_STATEMENT = N'DROP SERVICE ' + @INVALID_SERVICE;
EXECUTE sp_executesql @DROP_STATEMENT;
SET @DROP_STATEMENT = N'DROP QUEUE ' + @INVALID_SERVICE + N'_QUEUE';
EXECUTE sp_executesql @DROP_STATEMENT;
SET @INVALID_SERVICE = N'';
SELECT TOP(1) @INVALID_SERVICE = [name] FROM sys.services
WHERE
[name] LIKE 'SDAC_NS_%'
AND [name] not in (
SELECT 'SDAC_NS_' + CAST([spid] AS VARCHAR(32)) AS [name]
FROM master.dbo.sysprocesses WHERE [spid] >= 0 and spid <= 32767)
END;

-- 0 row(s) affected.

I really want to get this resolved so I can purchase the vcl components.

[AndreyZ]

I have reproduced the problem. The point is that notifications has several restrictions for the SELECT statement, you can find them here (in the "Supported SELECT Statements" section): http://msdn.microsoft.com/en-us/library ... l.90).aspx . Your SELECT statement does not meat them, it uses computed columns and nondeterministic functions. To solve the problem, you should rewrite your SELECT statement to meat all of notifications requirements. For example, the following SELECT statement can work with notifications:

Code: Select all

select Reservations.ReservationID as ID, 

[Start] = DATEADD( day, 0, DATEDIFF( day, 0, Reservations.ArrivalDate ) ) +
DATEADD( day, 0 - DATEDIFF( day, 0, Reservations.ArrivalTime ), Reservations.ArrivalTime ),

[Finish] = DATEADD( minute, Reservations.Duration, 
( DATEADD( day, 0, DATEDIFF( day, 0, Reservations.ArrivalDate ) ) +
DATEADD( day, 0 - DATEDIFF( day, 0, Reservations.ArrivalTime ), Reservations.ArrivalTime ) ) ),
 
GROUPS.[Name] as [Caption],
0 as EventType,
2 as [Options],
Reservations.ColorCode as LabelColor,
Reservations.Comments as [Message]
from dbo.Res_Reservations Reservations
Join dbo.Res_Groups Groups on ( Groups.GroupId = Reservations.GroupId )

Where ( Reservations.Cancelled = 0 )
Order By Start

This is SQL Server behaviour and we cannot influence it.

[sjmcdond]

While rewriting the query does solve the problem, it appears you have to set the database options on the physical database, and not through the connection as you described in an earlier post to this thread.

I also encountered a strange situation. When I add the following column references to the query: Reservations.MealID, Reservations.MovieID, Reservations.TourID, Reservations.ClassTypeID, and Reservations.ChildGrade I get the old "Multiple-step OLE DB operation generated errors. Check each OLE status value, if available. No work was done." error. The 1st 4 ..ID fields are all ints, and the ChildGrade column is a varchar(50). None of the columns are used in any constraint, nor part of an index.

I think in my situation, it would be best to have a "shadow" query that just returns the ID column from the reservation table, and refresh the real query on an OnChange event of the TMSChangeNotification component.

Makes my pine for my old Firebird database days

Thanks for all of your help! (And I will be making that purchase ).

[AndreyZ]

Feel free to contact us if you have any further questions about SDAC.