Translate

Tuesday, April 5, 2011

SQL Server 2005 - Unattended installation

Server and Client Components using .ini File

Step 1
Place the installation DVD in the DVD drive or copy the entire DVD on to a network shared drive and share that network drive.
Step2
Create the following folders. Refer Fig 1.0.
MKDIR  C:\Binaries
MKDIR C:\DSDEVICE
MKDIR C:\ASDEVICE
MKDIR C:\INSTALL
http://www.databasejournal.com/img/2006/07/mak_unattended3_image001.jpg Fig 1.0
Step 3
Create C:\install\setup.ini file with the following information. Refer Fig 1.1.
[Options]
ADDLOCAL=SQL_Engine,Analysis_Server, AnalysisSharedTools,Client_Components, Connectivity,SQL_Documentation,SQL_Tools90
INSTALLSQLDIR=C:\Binaries
INSTALLSQLSHAREDDIR=C:\Binaries
INSTALLSQLDATADIR=C:\DSDEVICE
INSTALLASDATADIR=C:\ASDEVICE
INSTANCENAME=JANE
SECURITYMODE=SQL
SAPWD= KuR0Z@w@
SQLACCOUNT=kurozawa\SQLServer
SQLPASSWORD=SQL@dd!ct
AGTACCOUNT=kurozawa\SQLServer
AGTPASSWORD=SQL@dd!ct
ASACCOUNT=kurozawa\SQLServer
ASPASSWORD=SQL@dd!ct
SQLBROWSERACCOUNT=kurozawa\SQLServer
SQLBROWSERPASSWORD=SQL@dd!ct
SQLCOLLATION=Latin1_General_CI_AS
ASCOLLATION=Latin1_General_CI_AS
DISABLENETWORKPROTOCOLS=0
http://www.databasejournal.com/img/2006/07/mak_unattended3_image002.jpg Fig 1.1
Step 4
Note: Please update the information like Instance name, collation, login and passwords in C:\Install\setup.ini according to your requirements.
Click on the Start button on the task bar, click run, type the command CMD.exe, which opens the MS-DOS shell command prompt.
Step 5
Type the following command if you are using the shared drive. Refer Figure 1.2.
H:\Servers\Setup.exe /settings C:\install\setup.ini /qb
http://www.databasejournal.com/img/2006/07/mak_unattended3_image003.jpg Fig 1.2
Note: H:\ is the drive letter of my computer's DVD drive.
Note: If you are using UNC path the command should be:
\\Fileserver\sharedrive\Servers\Setup.exe /settings C:\Install\setup.ini /qb
\\Fileserver should be replaced with your file server name and shared drive should be replaced with your actual shared drive name.
You would see the following screen. [Fig 1.3, Fig 1.4, Fig 1.5, Fig 1.6 and Fig 1.7]
http://www.databasejournal.com/img/2006/07/mak_unattended3_image004.jpg Fig 1.3
http://www.databasejournal.com/img/2006/07/mak_unattended3_image005.jpg Fig 1.4
http://www.databasejournal.com/img/2006/07/mak_unattended3_image006.jpg Fig 1.5
http://www.databasejournal.com/img/2006/07/mak_unattended3_image007.jpg Fig 1.6
http://www.databasejournal.com/img/2006/07/mak_unattended3_image008.jpg Fig 1.7
You would see the following items in the Add/Remove programs under control panel. [Fig 1.8]
http://www.databasejournal.com/img/2006/07/mak_unattended3_image009.jpg Fig 1.8
Verify the log, "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\summary.txt," after the installation. The log looks similar to the text shown below.
Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version      : Microsoft Windows Server 2003 family, Enterprise Edition Service Pack 1 (Build 3790)
Time            : Mon Jun 26 19:13:34 2006

KUROZAWA : Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. 
Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove
Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a
list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.
Machine         : KUROZAWA
Product         : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_SQLSupport_1.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft Office 2003 Web Components
Product Version : 11.0.6558.0
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_OWC11_1.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_BackwardsCompat_1.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_MSXML6_1.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_SQLSupport_2.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_SQLNCLI_1.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft Office 2003 Web Components
Product Version : 11.0.6558.0
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_OWC11_2.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_SqlWriter_1.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_BackwardsCompat_2.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_MSXML6_2.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_SQLSupport_3.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_SQLNCLI_2.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft Office 2003 Web Components
Product Version : 11.0.6558.0
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_OWC11_3.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server 2005 Backward compatibility
Product Version : 8.05.1054
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_BackwardsCompat_3.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_MSXML6_3.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server 2005
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_SQL.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server 2005 Analysis Services
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_AS.log
--------------------------------------------------------------------------------
Machine         : KUROZAWA
Product         : Microsoft SQL Server 2005 Tools
Product Version : 9.00.1399.06
Install         : Successful
Log File        : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup0001_KUROZAWA_Tools.log
--------------------------------------------------------------------------------

 Setup succeeded with the installation; inspect the log file completely for the status on all the components.
Time            : Mon Jun 26 19:19:22 2006
You can verify all the log files for installation status and history details in:
"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files"
Please refer Fig 1.9.
http://www.databasejournal.com/img/2006/07/mak_unattended3_image010.jpg Fig 1.9
Now let us look at the services. Click start, click Run and type services.msc and you could see the following services. Refer Figure 2.0.
http://www.databasejournal.com/img/2006/07/mak_unattended3_image011.jpg Fig 2.0
In addition, you could see the following programs under program files. Refer Fig 2.1.
http://www.databasejournal.com/img/2006/07/mak_unattended3_image012.jpg Fig 2.1
You could also see new folders and files are created under C:\Binaries C:\ASDEVICE C:\DSDEVICE. Refer Fig 2.2.
http://www.databasejournal.com/img/2006/07/mak_unattended3_image013.jpg Fig 2.2
Click on Configuration manager and make sure protocols are enabled. Fig 2.3.
http://www.databasejournal.com/img/2006/07/mak_unattended3_image014.jpg Fig 2.3
Try to connect to the database Engine instance, "JANE." Refer fig 2.4.
http://www.databasejournal.com/img/2006/07/mak_unattended3_image015.jpg Fig 2.4
Try to connect to the Analysis Services instance, "JANE." Refer Fig 2.5.
http://www.databasejournal.com/img/2006/07/mak_unattended3_image016.jpg Fig 2.5
If you use /qn as the switch, instead of /qb, then setup suppresses all Setup dialog boxes and error messages. If the /qn switch is specified, all Setup messages, including error messages, are written to Setup log files.
Delete the folder c:\install.
Empty the recycle bin.
Conclusion
SQL Server 2005 can be installed using the GUI, the .ini file or just run setup.exe with a bunch of parameters.
Posted by at 0 comments

Sunday, April 3, 2011

Query Ranges in X++

Expressions in query ranges
One of least understood but most powerful Axapta features is the so-called Expressions in query ranges syntax. This is not the same as simply using a QueryBuildRange object in a query and specifying a criteria for a single field.

Introduction

This is a method of specifying ranges on queries which allows you to perform complex comparisons, and create complex join situations which would be impossible using the standard syntax.


Syntax

To use the special syntax, you should first add a range to your QueryBuildDataSource object in the normal way. Note that for this special syntax, it does not matter which field you use to add the range.
To specify the range value itself, certain rules must be followed:
§  The entire expression must be enclosed within single-quotes, not double-quotes
§  The entire expression must be enclosed in parenthesis (brackets)
§  Each sub-expression must be enclosed in its own set of parenthesis
§  For fields in the current table, simply the field name can be used
§  For fields in other tables, a prefix of the relevant datasource name must be added. This is not always the same as the table name.
§  String values should be surrounded by double-quotes, and wrapped in a call to queryValue()
§  Enum values should be specified by their integer value
§  Date values should be formatted using Date2StrXpp()
§  Blank string like ' ' will not work as expected, use sysquery::valueEmptyString().


Examples

In the example below, we construct a query and add a single datasource.
The range is then added, using the DataAreaId field on each table. Any field can be used, but using an unusual one such as DataAreaId helps remind a casual reader of the code that it's not a normal range.

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable));
 
// Add our range
queryBuildRange = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
Given the above, the following are valid range specifications:

 

Simple criteria

Find the record where ItemId is B-R14. Take note of the single quotes and parenthesis surrounding the entire expression.
queryBuildRange.value(strFmt('(ItemId == "%1")', queryValue("B-R14")));
Find records where the ItemType is Service. Note the use of any2int().
queryBuildRange.value(strFmt('(ItemType == %1)', any2int(ItemType::Service)));
Find records where the ItemType is Service or the ItemId is B-R14. Note the nesting of the parenthesis in this example.
queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))', 
    any2int(ItemType::Service),
    queryValue("B-R14")));
Find records where the modified date is after 1st January 2000. Note the use of Date2StrXpp() to format the date correctly.
queryBuildRange.value(strFmt('(ModifiedDate > %1)', Date2StrXpp(01012000)));
Find records where the Field is blank (null) or an empty string. For more see Sys::Query Docs
qbrStatement = this.query().dataSourceName("BankAccountTrans2").addRange(fieldnum(BankAccountTrans,AccountStatement));
//qbrStatement.value("!?*");//this is the old way that may not work in future versions of AX
qbrStatement.value(sysquery::valueEmptyString());//this is the new way

 

Complex criteria with combined AND and OR clauses

Find all records where the ItemType is Service, or both the ItemType is Item and the ProjCategoryId is Spares. This is not possible to achieve using the standard range syntax.
Note also that in this example, we are using the fieldStr() method to specify our actual field names and again, that we have nested our parenthesis for each sub-expression.
queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',
    fieldStr(InventTable, ItemType),
    any2int(ItemType::Service),
    any2int(ItemType::Item),
    fieldStr(InventTable, ProjCategoryId),
    queryValue("Spares")));

 

WHERE clauses referencing fields from multiple tables

For this example below, we construct a query consisting of two joined datasources (using an Exists join). Note that we specify the datasource names when adding the datasources to the query.
The ranges are then added, using the DataAreaId field on each table as described in the earlier example.
query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), tableStr(InventTable));
dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), tableStr(InventItemBarCode));
dsInventItemBarCode.relations(true);
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);
 
// Add our two ranges
queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));
Find all records where a bar code record exists for an item and was modified later than the item was modified.
In this example, we are using the range on the BarCode table. Therefore the unqualified ModifiedDate reference will relate to InventItemBarCode.ModifiedDate. The other field is a fully-qualified one, using the DatasourceName.FieldName syntax.
queryBuildRange2.value(strFmt('(ModifiedDate > InventTable.ModifiedDate)'));
Note that if we had added our InventTable datasource using the following code
dsInventTable = query.addDataSource(tableNum(InventTable), "InventTableCustomName"); // Note that we are manually specifying a different datasource name
then the query range would need to appear as follows
queryBuildRange2.value(strFmt('(ModifiedDate > InventTableCustomName.ModifiedDate)'));

 

Conditional joins

We will modify our previous example slightly, to remove the automatic addition of relations for the join.
query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), "InventTable");
dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), "InventItemBarCode");
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);
 
// Add our two ranges
queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));
We can now use the query expression to specify whatever we like as the join criteria.
Find all records where either the ItemType is Service, or the ItemType is Item and a barcode exists. The join criteria is only applied in the second half of the expression, so all Service items will appear irrespective of whether they have a bar code. Again, this is not possible to achieve using the standard query ranges.
queryBuildRange2.value(strFmt('((%1.%2 == %3) || ((%1.%2 == %4) && (%1.%5 == %6)))',
    query.dataSourceTable(tableNum(InventTable)).name(), // InventTable %1
    fieldStr(InventTable, ItemType), // ItemType %2
    any2int(ItemType::Service), // %3
    any2int(ItemType::Item), // %4
    fieldStr(InventTable, ItemId), // ItemId %5
    fieldStr(InventItemBarCode, ItemId))); // %6
Using the techniques above, it is possible to create queries with almost as much flexibility as using SQL statements directly.
Filter on array fields
queryBuildRange.value(strFmt('((%1.%2 == "%4") || (%1.%3 == "%5"))', 
    queryBuildDataSource.name(),
    fieldid2name(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension), Dimensions::code2ArrayIdx(SysDimension::Center))), 
    fieldid2name(tablenum(<table>), fieldid2ext(fieldnum(<table>, Dimension), Dimensions::code2ArrayIdx(SysDimension::Purpose))), 
    "some dim2 value", 
    "some dim3 value"));
Note: you must always specify the datasource name if you use Query Expression syntax to filter on array fields. See also Limitations section at the bottom of the page.

 

Using wildcards and comma-separated range values

Again, the previous example here was using standard syntax, not the special syntax using expressions. It's not possible to modify the above examples to work with wildcards.
The above statement applies to AX versions < 5.0
AX 5.0 introduced solution to wildcards - while you still cannot directly use wildcards in ranges, now it supports the 'LIKE' keyword.
(AccountNum LIKE "*AA*" || Name LIKE "*AA*")
Posted by at 10 comments
Subscribe to: Posts (Atom)