 |
 |
ASNA DataGate® 7.2 for SQL Server®
Contents
Issue connecting to SQL Server after installing Windows XP Server Pack 2
DataGate for SQL Server 7.2
(7.210) now supports DbDirection(*Output) for SQL Server stored procedures. The
following is the documentation change (in blue) to support this.
DbDirection
Optional. DbDirection specifies whether the
parameter to a remote program call is an input-only parameter (*Input), an output-only
parameter (*Output) or an input and output parameter (*Both). When a large
amount of data is passed in only one direction - either to the called program,
or from the called program, DbDirection allows you to reduce the call
time by eliminating the transmission of unused data. *Both is the default.
The direction of the parameter is from the perspective of the
program being called. That is, *Input means the called program is expecting
data in the parameter, but doesn't need to return it to the caller. *Output
means the called program will send data back in the parameter to the caller,
but is not expecting data in the parameter as input to it.
DbDirection has specific meaning depending upon the type of the
Database to which the Call is being made.
· Calling AS/400 programs
- AS/400 programs do not specify whether the parameter is used by the program
for input or output, so the real value of DbDirection is in time savings when
the parameter is large. Specifying the proper direction when data is only
used in one direction saves time because the data in the parameter is only sent
to the AS/400 when the parameter is marked as *Input and only transmitted back
to the client if the parameter is marked for *Output.
· Calling
SQL Server stored procedures - SQL Server defines two kinds of parameters,
regular parameters and 'output' parameters. SQL Server regular parameters are
only received by the procedure and so must have a DbDirection of *Input.
What SQL Server means by an 'output' parameter is really a parameter that is
optionally received but always sent back to the client. Therefore, a SQL Server
'output' parameter may have a DbDirection of *Output if it is
certain the procedure does not use the received value. However, if *Output
is specified and the procedure does depend on a received value, unpredictable
results will occur. It is never wrong to specify a DbDirection of *Both
for a SQL Server 'output' parameter even if the stored procedure does not use
the received value.
DBDirection Example:
If a SQL Server Stored
procedure looks like the following:
CREATE PROCEDURE CountNewOrders
@WearhouseId as decimal(8,0) ,
@OrderCount as numeric (5,0) output
AS
select @OrderCount = count(distinct(OrderID)) from [Orders] where WId=@
WearhouseId
GO
Then the Visual RPG Call
should be:
CALL PGM (‘CountNewOrders’) DB (‘SQL’)
DCLPARM OrderNum DbDirection (*Input)
DCLPARM OrderCount DbDirection (*BOTH)
There are important issues,
limitations and considerations of SQL Server when it comes to objects, indexes,
data access, locking, fields, and join considerations. Please
refer to the following files for additional, important information.
Creating ‘Files’ in SQL
Server - Contains an overview of how physical
and logical files were implemented in DSS to permit as much manipulation as
possible of the underlying objects directly via native SQL commands.
Differences DG400 and DSS
for .NET.htm - Contains a listing of the Differences between DataGate, DataGate for
DB2/400 (DG400) and DataGate for SQL Server (DSS for .NET).
Porting AVR Applications to
DSS for .NET - Contains some SQL Server restrictions and the process to convert your
AVR applications to access SQL Server databases.
Visual RPG Programming Considerations -
Contains some programming considerations when converting your AVR applications
to support SQL Server.
There is an issue with
connecting to SQL Server after applying Microsoft's XP Pro Service Pack 2.
Installing Microsoft's XP Pro Service Pack 2, by default, enables the built-in Windows firewall settings which interfere with SQL Server connectivity. These settings, unless changed, won't let you connect to SQL Server 2000 (with either SQL Server's own Enterprise Manager or with ASNA's DataGate for SQL Server (DSS for .NET).
The Windows Firewall closes ports such as 445 that are used for file and printer sharing to prevent Internet computers from connecting to file and print shares on your computer or to other resources. When SQL Server is configured to listen for incoming client connections by using named pipes over a NetBIOS session, SQL Server communicates over TCP ports and these ports must be open. SQL Server clients that are trying to connect to SQL Server will be not be able to connect until SQL Server is set as an exception in Windows Firewall. To set SQL Server as an exception in Windows Firewall, use the steps that are listed here.
Error you will receive:
The error message shown below in Figure 1 is error message you'll receive if you encounter this error. You'll receive this error attempting to connect to SQL Server with either SQL Server's own Enterprise Manager or with ASNA's DataGate for SQL Server for .NET (DSS for .NET).
Resolution:
After upgrading XP Professional with Service Pack 2, there are changes that are need to be made to XP's firewall settings to allow SQL Server 2000 to be able to communicate with the Firewall features in XP Professional.
Please consult with Microsoft or your Network Security Administrator before making any changes. These changes impose security-related issues that should be fully assessed for your environment.
More Information:
Click here for the article on http://developer.asna.com.
Click here for more general information.
Click here for Microsoft's recommended steps to resolve this problem.
Database
Issues
Backup your Databases!!!
As with any other information you keep
on a PC, you should make sure that you keep 'current' backups of your
data. If you have ever run a program
like CHKDISK you will notice that sometime 'clusters' get damaged on FAT file
systems. Keeping a good backup outside
of your PC will enable you to recover from a disk failure.
There is a limitation of 32 Record Formats per Print File.
· References are lost when restoring a file that has referenced fields.