This was one interesting excercise we did where we moved the objects from the VAR layer to the BUS layer for one of the live implementations. The challenge was that we wanted to retain all the data and all the references.
1. To begin with we had two installations once was the dev instance where the objects were in the bus layer and the other was the live instance where the objects were in the var layer.
2. We know that while the no sequence of the objects starts from 30000 onwards in the var layer it starts from 20000 onwards in the bus layer. So once we resoted the bus layer (aod) the existing object id's in the 30,000 series became irrelevant. So we deleted all the entries in the sql dictionary table between 30000 and 40000, backup the records from the dev environment where tableid was between 20000 and 30000and restored it on the live.
3. We also know that when the table names are more than 30 chars the synchronize routine replaces the last 4 chars of the table with the objectid. So we manully looked for such objects and changed the tableid suffix in such objects from the 30000 series to the 20000 series.
4. Once we did the above we were getting a duplicate record while entering data into most of the table it was no long before we realized that the systemsequences table contains the last record id and is used to generate the record if for the new record. In this case as the object ids changed a corresponding entry for them was required in the systemsequences table with the new object ids. I wrote a script to generate a script for the right updates.
select ' insert into systemsequences
( ID,NEXTVAL,MINVAL,MAXVAL,CYCLE,NAME,TABID,DATAAREAID,RECVERSION,RECID ) '
+ 'select '
+ ' -1, isnull( max( recid ) + 1, 1 ) , 1, 9223372036854775807, 0, ''SEQNO'', ' + cast( SD.TableID as varchar) + ',''dat'', 1, -1'
+ ' from ' + SD.SQLName
from sqldictionary SD
left join systemsequences SS
on SS.tabid = SD.tableID
where 1=1
and SS.Name is null
and SD.tableid between 20000 and 30000
and SD.fieldid = 0
Sunday, September 26, 2010
Thursday, September 09, 2010
Remote scan during linked table Update
Faced this issue while building an interface from a SQL Server to a remote MYSQL database. We had a set of insert, update statements to synchronize a set of table however the update statements were taking forever to execute. On analyzing the execution plan we got to know that there was a remote scan being performed for each update which was slowing down the update as for each row update a remote scan for 6500 rows was being performed. On investigating further i found that this is a documented behaviour and what MSDN says is:
For linked server DELETEs or UPDATEs, SQL Server retrieves data from the table, performs any filtering that is necessary, and then performs the deletes or updates through the OLEDB rowset. This processing can result in a round-trip to the remote server for each row that is to be deleted or updated
SQL Server 2000 adds the ability to send a DELETE or UPDATE to a linked server as a single SQL statement; however, this feature only covers linked servers to another SQL Server 2000 or SQL Server 7.0 instance
Please refer knowledge base article
http://support.microsoft.com/kb/309182
So the only option we have to update without any performance overhead on the remote server is if we use stored procedures for insert and udpates so that the actual filtering and update is actually performed on the remote server rather then in a rowset at the local server.
For linked server DELETEs or UPDATEs, SQL Server retrieves data from the table, performs any filtering that is necessary, and then performs the deletes or updates through the OLEDB rowset. This processing can result in a round-trip to the remote server for each row that is to be deleted or updated
SQL Server 2000 adds the ability to send a DELETE or UPDATE to a linked server as a single SQL statement; however, this feature only covers linked servers to another SQL Server 2000 or SQL Server 7.0 instance
Please refer knowledge base article
http://support.microsoft.com/kb/309182
So the only option we have to update without any performance overhead on the remote server is if we use stored procedures for insert and udpates so that the actual filtering and update is actually performed on the remote server rather then in a rowset at the local server.
Friday, July 09, 2010
“System does not support setup 'continuous' of number sequence
Realized that the issue was that the getNewNum function was called without transaction processing
PYLEmplLeaveDetailsTable.LeaveAppNum = NumberSeq::newGetNum(HRMParameters::numRefLeaveAppNum()).num();
just got resolved by encapsulating the same within a transaction
ttsbegin;
PYLEmplLeaveDetailsTable.LeaveAppNum = NumberSeq::newGetNum(HRMParameters::numRefLeaveAppNum()).num();
ttscommit;
Deploying AOD files directly
One of the methods of deploying customizations onto an AX installation is by carrying the aod files and replacing them on the target installation. However there is one issue with this approach if the newly updated aod file contains some new security and configuration keys the same would not get registered in the security metadeta of AX which i believe is stored in the database.
So we might land up with a menu with missing menu items as the security is not recognized for them even for the Administrator.
the way to get around this issue is to disable and enable the concerned configuration key doing so will get it registered in the AX Security related tables
Thursday, July 08, 2010
Macros
For a long time i wondered if string substitution is all that the macros offer for until i realized the power of macros when it comes to a programming language.
Macros are strings which are substituted by the pre-compiler before they reach the compiler and it is this behavior which makes macros extremely powerful.
Hence macro substitutes a string in the source code which is then compiled and processed by the compiler.
A classic example of this behavior is the pack unpack pattern applied in AX. It starts with the definition of a macro which refers to all the memory variables we need to maintain state for. The pack method returns a container effectively before this source reaches the compiler the macro is substituted and the container list contains all the memory variables which are processed by the compiler and the actual memory values are composed into the container.
similarly in the unpack method when we say
[version,#CurrentList] = _packedClass;
before the above statement is processed by the compiler the macro is substituted by all the memory variables which then get initialized as this statement is executed.
Ideally the source code written once should not be repeated elsewhere coz as per the best practices it should be reused however if for some reason a piece of code needs to be duplicated through a routine the macros are the ideal candidates to maintain a single central copy of this source.
Macros are strings which are substituted by the pre-compiler before they reach the compiler and it is this behavior which makes macros extremely powerful.
Hence macro substitutes a string in the source code which is then compiled and processed by the compiler.
A classic example of this behavior is the pack unpack pattern applied in AX. It starts with the definition of a macro which refers to all the memory variables we need to maintain state for. The pack method returns a container effectively before this source reaches the compiler the macro is substituted and the container list contains all the memory variables which are processed by the compiler and the actual memory values are composed into the container.
similarly in the unpack method when we say
[version,#CurrentList] = _packedClass;
before the above statement is processed by the compiler the macro is substituted by all the memory variables which then get initialized as this statement is executed.
Ideally the source code written once should not be repeated elsewhere coz as per the best practices it should be reused however if for some reason a piece of code needs to be duplicated through a routine the macros are the ideal candidates to maintain a single central copy of this source.
MorphX has a built-in macro-preprocessor. The purpose of macros is to make statements easy to reuse and you can declare macros wherever you can write X++ statements.
There are basically three kinds of macros: macros in macro libraries, stand-alone macros and (local) macros inside methods. All three types of macros have identical syntax and functionality. The only differences are that
A stand-alone macro is created using the macro-node of the Application Object Tree
A macro library is a stand-alone macro, which contains (local) macros
A local macro is declared within a method, or stand-alone macro
X++ has these macro constructs:
#DEFINE | This statement defines a macro variable, which can be used as a constant or to make macros perform differently according to the macro variable value. |
#UNDEF | This statement cancels a macro variable. |
#MACROLIB | Loads a macro library. Libraries must be loaded before the macros within them can be used. |
#GLOBALMACRO | Declares a global macro (header). A global macro can be used everywhere in MorphX after declaration. |
#LOCALMACRO | Declares a local macro (header). |
#ENDMACRO | Signals the end of a macro declaration. |
#IF | Tests if the macro variable following the #IF has as given value. Includes the statements between #IF and #ENDIF. |
#IF.EMPTY() | Tests the argument within the brackets, and includes the statements between #IF and #ENDIF if the argument is empty. |
#IFNOT.EMPTY() | Tests the argument within the brackets, and includes the statements between #IF and #ENDIF if the argument is not empty. |
#ENDIF | Signals the end of a conditional macro declaration (#IF). |
#LINENUMBER | Returns the current line number. Mainly useful for debugging |
Using define
You can use the define macro construct to declare symbolic constants in your program as follows:
#Define.MaxLength(100)
int intarray[#MaxLength]
Macro declaration
Macros are declared by this syntax:
Macrodeclaration = #localmacro. Macroname { text } #endmacro
Where text can be anything. Within the macro declaration, it is possible to use parameters. The parameters are named %1, %2, %3 etc. All macros are considered as text, and are unfolded before compilation. This means that there is no means of checking syntax or functionality within macros (all that appears is a compiler error in the macro with no specification of error location).
A local macro, called AnExample is declared as follows:
#localmacro.AnExample
// Some statements or text
#endmacro
A more complex macro using parameters is declared as follows:
#localmacro.ComplexMacro
#DEFINE.ARG(%1); // Defines a macro variable ARG
#IF.EMPTY(%1) // If parameter1 is empty
print “The first parameter is empty (not used)”;
#ENDIF
#IFNOT.EMPTY(%1) // If it is not empty
print “The first parameter is NOT empty, it is %1”;
#ENDIF
%2 = %3 + %4; // param2 is assigned
// param3+param4
#IF.ARG(1) // IF parameter1 is 1
print “Parameter1 has the value 1”;
#ENDIF
#UNDEF.ARG // The macro variable ARG is undefined
#endmacro
Referencing macros
You reference macros by their name prefixed with a hash mark (#). To reference the complex macro shown above, you would write:
#complexMacro(parameter1, parameter2, parameter3, parameter4)
The parameters (1-4) have a story: Parameter 1 is optional, as it is only used in the macro, if it exists (#IFNOT.EMPTY) whereas parameters 2, 3 and 4 are used unconditionally (in the statement %2 = %3 + %4) - and therefore must be included in the call. Assuming you have declared a variable called A, a valid reference would be):
ComplexMacro(1,a,3,4);
Which would expand the macro into the statements below:
#DEFINE.ARG(1); // Defines a macro variable
#IF.EMPTY(1) // If parameter1 is empty
print “The first parameter is empty (not used)”;
#ENDIF
#IFNOT.EMPTY(1) // If it is not empty
print “The first parameter is NOT empty, it is 1”;
#ENDIF
A = 3 + 4; // param2 is assigned param3+param4
#IF.ARG(1) // IF parameter1 is 1
print “Parameter1 has the value 1”;
#ENDIF
#UNDEF.ARG // The macrovariable ARG is undefined
The results of the above statements are two prints and an assignment.
A is assigned the value 7 and two lines are printed on the screen: “The first parameter is NOT empty, it is 1” and “Parameter1 has the value 1”.
If you referenced the complex macro without parameters 2-4, the result would have been:
= + ; // param2 is assigned param3+param4
And hence a compilation error occurs though you cannot see that the macro is actually expanded in this way (which makes the error harder to find).
Using macro libraries
To use a macro library, you must create the library (say TestMe) using the Application Object Tree and then load the macro using the following construct:
#TestMe;
A more preferred way of referring macro libraries is using the #macrolib keyword the advantage of using this keyword is that you will be able to reference macors with special characters and multiple lines look at the example below where a special character (") doube quote had to be used in a macro
1. #define.TableRead(select * from EmplTable where EmplTable.EmplCode == %1;)
#TableRead("E0006")
info( emplTable.EmplCode );
2. #localmacro.TableRead
select * from EmplTable where EmplTable.EmplCode == "%1";
#endmacro
#macrolib.training
#TableRead(E0006)
info( emplTable.EmplCode );
The second way is the preferred way of creating multi line macros also if the macro includes some special symbols like ) or " then the second method is preferred if you notice in the first method the double quote are passed in the parameter to the macro as they could not be a part of the macro as in the macro definition the macro would end at the first instance of the double quote.
Inheritance and Macros
The macros defined in the class declaration of the base class are available in the sub classes however if one of the class in the hierarcy undefines the macro then that point onwards the macro are not available in the child classes.
Sunday, July 04, 2010
37 "Signals" From 37 Signals
1) Great businesses have a point of view, not just a product or service.
2) Writing a plan makes you feel in control of things you don’t actually control.
3) You have the most information when you’re doing something, not before you've done it.
4) Stuff that was impossible just a few years ago is simple today.
5) Failure is not a prerequisite for success.
6) Don’t make assumptions about how big you should be ahead of time.
7) Don’t sit around and wait for someone else to make the change you want to see.
8) When you build what you need, you can assess quality directly instead of by proxy.
9) Solving your own problem lets you fall in love with what you’re making.
10) What you do matters, not what you think or say or plan.
11) When you want something bad enough, you make the time.
12) The perfect time to start something never arrives.
13) Start a business, not a startup.
14) You need a committment strategy, not an exit strategy.
15) Huge organizations talk instead of act, and meet instead of do.
16) Build half a product, not a half-assed product.
17) Getting to greatness starts by cutting out stuff that’s merely good.
18) The real world isn’t a place, it's an excuse. It's a justification for not trying.
19) The big picture is all you should be worrying about in the beginning. Ignore the details.
20) Decide. You’re as likely to make a great call today as you are tomorrow.
21) The longer it takes to develop, the less likely it is to launch.
22) It’s the stuff you leave out that matters.
23) Focus on substance, not fashion. Focus on what won't change.
24) When good enough gets the job done, go for it.
25) When you make tiny decisions, you can't make big mistakes.
26) Pour yourself into your product.
27) You rarely regret saying no but you often regret saying yes.
28) Better your customers grow out of your product, than never grow into them.
29) You can’t paint over a bad experience with good marketing.
30) All companies have customers. Fortunate companies have audiences too.
31) Instead of out-spending your competitors, out-teach them.
32) Let customers look behind the curtain.
33) Leave the poetry in what you make, there is beauty in imperfection.
34) Marketing is not a department, it's the sum total of everything you do.
35) Don’t hire for pleasure; hire to kill pain.
36) Don’t make up problems you don’t have yet.
37) A business without a path to profit is a hobby.
2) Writing a plan makes you feel in control of things you don’t actually control.
3) You have the most information when you’re doing something, not before you've done it.
4) Stuff that was impossible just a few years ago is simple today.
5) Failure is not a prerequisite for success.
6) Don’t make assumptions about how big you should be ahead of time.
7) Don’t sit around and wait for someone else to make the change you want to see.
8) When you build what you need, you can assess quality directly instead of by proxy.
9) Solving your own problem lets you fall in love with what you’re making.
10) What you do matters, not what you think or say or plan.
11) When you want something bad enough, you make the time.
12) The perfect time to start something never arrives.
13) Start a business, not a startup.
14) You need a committment strategy, not an exit strategy.
15) Huge organizations talk instead of act, and meet instead of do.
16) Build half a product, not a half-assed product.
17) Getting to greatness starts by cutting out stuff that’s merely good.
18) The real world isn’t a place, it's an excuse. It's a justification for not trying.
19) The big picture is all you should be worrying about in the beginning. Ignore the details.
20) Decide. You’re as likely to make a great call today as you are tomorrow.
21) The longer it takes to develop, the less likely it is to launch.
22) It’s the stuff you leave out that matters.
23) Focus on substance, not fashion. Focus on what won't change.
24) When good enough gets the job done, go for it.
25) When you make tiny decisions, you can't make big mistakes.
26) Pour yourself into your product.
27) You rarely regret saying no but you often regret saying yes.
28) Better your customers grow out of your product, than never grow into them.
29) You can’t paint over a bad experience with good marketing.
30) All companies have customers. Fortunate companies have audiences too.
31) Instead of out-spending your competitors, out-teach them.
32) Let customers look behind the curtain.
33) Leave the poetry in what you make, there is beauty in imperfection.
34) Marketing is not a department, it's the sum total of everything you do.
35) Don’t hire for pleasure; hire to kill pain.
36) Don’t make up problems you don’t have yet.
37) A business without a path to profit is a hobby.
Unlock a protected excel sheet
I had this excel sheet which was a RFP document and needed to copy a part of it as a response but this sheet was protected and selection was disabled on the sheet for the desired columns
Came across this amazing macro which would disable the password and make the columns selectable.
http://mcgimpsey.com/excel/downloads/allinternalpasswords.xls
basically when this excel sheet was opened a new addin button is created which could be used to unlock the sheets.
Came across this amazing macro which would disable the password and make the columns selectable.
http://mcgimpsey.com/excel/downloads/allinternalpasswords.xls
basically when this excel sheet was opened a new addin button is created which could be used to unlock the sheets.
Sunday, June 13, 2010
SID in AX Security Tables
The problem arose where we are trying to restore the live production databases into a test environment
Historically DAX has had its own users when it was integrated to windows security model the windows user's SID (SID is a unique identifier for a user or a group in Windows) was used to act like a user.
SIDs are specific for a particular domain, i.e. domain admin accounts thus in two different domains the same user will have different SIDs.
The problem is that when the DAX database is moved from the production environment to a test environment it carries the SID of the production environment in the security tables which is different from a SID of an account that is being used in the test environment.
The solution is as below:
* find out the SID of the user you use to run a DAX client after DB migration
o you can use whoami tool shipped with Windows - just run whoami /user in a console and you'll see a long string like S-1-5-21-15052... next to your login;
o if you use WinXP or an earlier Windows version you'll have to install Windows XP Support Tools to get the tool and afair it has a slightly different command line params... you'll need to use /sid option instead of /user
* run a tool like Ms SQL Management Studio and connect to the restored DAX database
* navigate to the UserInfo table, open it in a grid and update the SID value for user "admin" with the one you've found out previously
* start DAX client - you should login as an admin now
Historically DAX has had its own users when it was integrated to windows security model the windows user's SID (SID is a unique identifier for a user or a group in Windows) was used to act like a user.
SIDs are specific for a particular domain, i.e. domain admin accounts thus in two different domains the same user will have different SIDs.
The problem is that when the DAX database is moved from the production environment to a test environment it carries the SID of the production environment in the security tables which is different from a SID of an account that is being used in the test environment.
The solution is as below:
* find out the SID of the user you use to run a DAX client after DB migration
o you can use whoami tool shipped with Windows - just run whoami /user in a console and you'll see a long string like S-1-5-21-15052... next to your login;
o if you use WinXP or an earlier Windows version you'll have to install Windows XP Support Tools to get the tool and afair it has a slightly different command line params... you'll need to use /sid option instead of /user
* run a tool like Ms SQL Management Studio and connect to the restored DAX database
* navigate to the UserInfo table, open it in a grid and update the SID value for user "admin" with the one you've found out previously
* start DAX client - you should login as an admin now
Thursday, May 27, 2010
SQL Server sp_execute
We commonly come across this statement when profiling SQL Server i have wondered a lot about what it is and there was some explanation i found for it in the BOL.
The ODBC API defines prepared execution as a way to reduce the parsing and compiling overhead associated with repeatedly executing a Transact-SQL statement. The application builds a character string containing an SQL statement and then executes it in two stages. It calls SQLPrepare once to have the statement parsed and compiled into an execution plan by the database engine. It then calls SQLExecute for each execution of the prepared execution plan. This saves the parsing and compiling overhead on each execution. Prepared execution is commonly used by applications to repeatedly execute the same, parameterized SQL statement.
thus sp_execute is a system stored procedure used with "prepared" statements from a client. The number you see is an internal pointer to the execution plan on the server. The values following that number are the parameters for a particular invocation of the prepared statement
Now when we see sp_execute in the profile and we need to know the text behind it then first thing we can do it to check if there is an "sp_prepare" with the same number in the thread, if there is then you include sql_handle in the trace and you look can up the text for the cache entry using the following statement
select text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(plan_handle)
where session_id = [spid]
you can also enable Prepare SQL event in the profiler to get the handle of the execution plan
I found this problem where a prepared sql was taking much longer to execute when i executed the same sql in the management studio it was actually fast so i am wondering what could make a unprepared sql faster then a prepared sql?
I thought maybe the statistics are out so created the statistics for this table
with a FULLSCAN option and that really helped still not convinced why? maybe i will have to study the execution plan in both the cases and spot the difference i believe that different execution plans are being used in these cases.
The ODBC API defines prepared execution as a way to reduce the parsing and compiling overhead associated with repeatedly executing a Transact-SQL statement. The application builds a character string containing an SQL statement and then executes it in two stages. It calls SQLPrepare once to have the statement parsed and compiled into an execution plan by the database engine. It then calls SQLExecute for each execution of the prepared execution plan. This saves the parsing and compiling overhead on each execution. Prepared execution is commonly used by applications to repeatedly execute the same, parameterized SQL statement.
thus sp_execute is a system stored procedure used with "prepared" statements from a client. The number you see is an internal pointer to the execution plan on the server. The values following that number are the parameters for a particular invocation of the prepared statement
Now when we see sp_execute in the profile and we need to know the text behind it then first thing we can do it to check if there is an "sp_prepare" with the same number in the thread, if there is then you include sql_handle in the trace and you look can up the text for the cache entry using the following statement
select text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(plan_handle)
where session_id = [spid]
you can also enable Prepare SQL event in the profiler to get the handle of the execution plan
I found this problem where a prepared sql was taking much longer to execute when i executed the same sql in the management studio it was actually fast so i am wondering what could make a unprepared sql faster then a prepared sql?
I thought maybe the statistics are out so created the statistics for this table
with a FULLSCAN option and that really helped still not convinced why? maybe i will have to study the execution plan in both the cases and spot the difference i believe that different execution plans are being used in these cases.
Saturday, May 22, 2010
Bring a Standby Database online
When Transaction Log Shipment is setup a copy of the transaction database in maintained in read-only and standby mode on a pre-configured server.
If for any reason the primary server fails and the standby server needs to be promoted use the following command to make the database available
RESTORE DATABASE [databasename] WITH RECOVERY
If for any reason the primary server fails and the standby server needs to be promoted use the following command to make the database available
RESTORE DATABASE [databasename] WITH RECOVERY
Dynamics AX Application file types
There are lots of files with different file extensions in the Application\Appl\Std folder. Got these details from Harish Mohanbabu's blog quite a handy information
Yes. There are quite a lot of files in Axapta Application\Appl\Standard folder. Please note that all these files will be updated whenever a new version is released. Most important them are -
1. .aod - Acronym for Application Object Data file. Each Application object layer is saved in a separate file called
"Ax< layer >.aod". For example, Axsys.aod for the SYS layer, Axusr.aod for the USR layer and so on.
2. label files : If we look at our label files in the AOT we see 4 different extensions for our labels
· ALI Axapta Label Index
· ALC Axapta Label Comments
· ALT Axapta Label Temp, Store
· ALD Axapta Label Dictionary
The ALD file is readable/editable with a text editor. General speaking you only need the ALD file. When the AOS is restarted the ALI and ALC will be generated on the fly. (or updated when the time stamp of the ALD file is bigger than the timestamp of the ALC or ALI file)
Next, a developer creates new labels. These labels will be stored in the ALT file. Not yet in the ALD file. When the final AOS will stop. AX will update the ALD file this way. It will copy the ALD file to an ALB file. Next the changes in the ALT file will be stored in the ALB file. Finally this ALB file is placed back in the ALD file and the ALB file will be deleted. (HINT: make the ALD file read only and you will see it your self)
When your AOS has creased the changes are not stored in the ALD file. Even when you start and stop the AOS again the file is not updated. To solve this issue start an AX client search for the label in the Label editor. Next stop your client and the AOS. Now the label file is updated.
3. .udb - Acronym for Axapta User Database. As the acronym indicates, Axapta stores its user details in this file. Also this file is responsible for allocating session ID to users. If this file is deleted, then Axapta would regenerate whenever the system is started.
Tip : Some times in Axapta 3-tier installations, when you look at online users form, it could return false information. For example, let us assume 5 users are currently online. But this form, might show 7 or 8 users online. Or some times, though you have enough licenses, you might receive "maximum users reached" error message from Axapta.
In such cases, the solution would be deleting the "axdat.udb" file. But before deleting it, make sure that all users are exited and AOS is properly shut down. Once this is done and the file is deleted, as mentioned above, Axapta would regenerate the "Axdat.udb" file. Please note that you might not even come across this problem. But to be on the safer side, particularly on 3-tier installations, it is always advisable to regularly delete "axdat.udb" file.
But in some special conditions, online user form may be empty in Axapta ver 2.5. This condition may happen if your license has an expiry date. Even though your license may not have expired, you might still come across this condition. In such cases please go to Technet website and do a search with this key words "The online user form is empty". You would come across an Export file. Import that export file in your installation. That would solve the problem.
4. .ktd - Acronym for Kernel Text Data.
Tip : Some times when you upgrade your installation (say for example you have installed a new SP), you might not be able to see the new features of the installed SP after the upgrade is complete.
The solution for this would be manually copying the .ktd files from Client\Bin\ directory to Axapta Application\Bin directory. The reason being - when Service pack is installed, for some reasons files in Axapta Application\bin directory are not updated. Only the files that are there in Client\Bin are updated.
5. .aoi - Acronym for Application Object Index. As the name indicates this is an index file for the Application objects.
Tip : Some times you may get funny error messages. Though you might have hard disk space, sometimes the following error might popup -
Error in file: ...\standard\axapd.aoi while reading in record ...
Error code: 38 = hard disk is full
For all the error messages involving axapd.aoi, the solution would be deleting the "axapd.aoi" file from Axapta Application\Appl\Standard directory. When you restart the system again, Axapta would rebuild this index file.
If the problem persists even after deleting the file, then check whether "Open application files in exclusive mode" is enabled (Axapta Configuration Utility --> Under General Tab). If enabled, then disable it. This should solve the problem.
6. What is "Master.aoc" file and how can I create it ?
aoc stands for Axapta Object Cache. This functionality was introduced in Axapta ver 2.5 mainly intended for best performance possible.
When running a 3-Tier Navision Axapta 2.5 client, we will cache different objects as we use the application. All objects are cached in the memory on the client machine. When the client is shutdown, the cache objects are written to disk, in a file with the extension .aoc (Axapta Object Cache). The next time a user starts the client, the executable ax32.exe checks for the .aoc file and if one exists it is loaded into memory. This gives us a performance gain, as we do not have to cache these objects again.
How to create a preconfigured cache file?
This is very simple. We configure a 3-tier client and go through the parts of the application all clients will use. Ex - General Ledger, Sales Order and Accounts Receivable. Doing this will create an .aoc file, with the following naming convention - "ax_AOS [Instance name]@[Hostname]_[username].aoc". In my case, I got a file like this -
ax_Annai@Himalaya_Harish.aoc. This file, if used as the preconfigured cache file, must be renamed to "master.aoc". Please note that this naming convention of the .aoc file would be different if you have configured an Object Server Cluster. (Ex - ax[cluster name][username].aoc)
When installing the Navision Axapta 2.5 client software, the setup.exe program will look for the master.aoc file in the directory where setup.exe is located. After completing the installation of the client, the setup.exe program will, if a master.aoc file exists, copy this file to the \Log directory, located in the default directory structure of the installed client.
When a user starts the client software for the first time, the executable as32.exe, will look for a cache file that belongs to the user, who is currently logged in. If one exists then this cache file is loaded into memory. If none exists, then the ax32.exe will look in the \Log directory for the master.aoc file. If one exists, the objects in this cache file would be loaded into memory.
Yes. There are quite a lot of files in Axapta Application\Appl\Standard folder. Please note that all these files will be updated whenever a new version is released. Most important them are -
1. .aod - Acronym for Application Object Data file. Each Application object layer is saved in a separate file called
"Ax< layer >.aod". For example, Axsys.aod for the SYS layer, Axusr.aod for the USR layer and so on.
2. label files : If we look at our label files in the AOT we see 4 different extensions for our labels
· ALI Axapta Label Index
· ALC Axapta Label Comments
· ALT Axapta Label Temp, Store
· ALD Axapta Label Dictionary
The ALD file is readable/editable with a text editor. General speaking you only need the ALD file. When the AOS is restarted the ALI and ALC will be generated on the fly. (or updated when the time stamp of the ALD file is bigger than the timestamp of the ALC or ALI file)
Next, a developer creates new labels. These labels will be stored in the ALT file. Not yet in the ALD file. When the final AOS will stop. AX will update the ALD file this way. It will copy the ALD file to an ALB file. Next the changes in the ALT file will be stored in the ALB file. Finally this ALB file is placed back in the ALD file and the ALB file will be deleted. (HINT: make the ALD file read only and you will see it your self)
When your AOS has creased the changes are not stored in the ALD file. Even when you start and stop the AOS again the file is not updated. To solve this issue start an AX client search for the label in the Label editor. Next stop your client and the AOS. Now the label file is updated.
3. .udb - Acronym for Axapta User Database. As the acronym indicates, Axapta stores its user details in this file. Also this file is responsible for allocating session ID to users. If this file is deleted, then Axapta would regenerate whenever the system is started.
Tip : Some times in Axapta 3-tier installations, when you look at online users form, it could return false information. For example, let us assume 5 users are currently online. But this form, might show 7 or 8 users online. Or some times, though you have enough licenses, you might receive "maximum users reached" error message from Axapta.
In such cases, the solution would be deleting the "axdat.udb" file. But before deleting it, make sure that all users are exited and AOS is properly shut down. Once this is done and the file is deleted, as mentioned above, Axapta would regenerate the "Axdat.udb" file. Please note that you might not even come across this problem. But to be on the safer side, particularly on 3-tier installations, it is always advisable to regularly delete "axdat.udb" file.
But in some special conditions, online user form may be empty in Axapta ver 2.5. This condition may happen if your license has an expiry date. Even though your license may not have expired, you might still come across this condition. In such cases please go to Technet website and do a search with this key words "The online user form is empty". You would come across an Export file. Import that export file in your installation. That would solve the problem.
4. .ktd - Acronym for Kernel Text Data.
Tip : Some times when you upgrade your installation (say for example you have installed a new SP), you might not be able to see the new features of the installed SP after the upgrade is complete.
The solution for this would be manually copying the .ktd files from Client\Bin\ directory to Axapta Application\Bin directory. The reason being - when Service pack is installed, for some reasons files in Axapta Application\bin directory are not updated. Only the files that are there in Client\Bin are updated.
5. .aoi - Acronym for Application Object Index. As the name indicates this is an index file for the Application objects.
Tip : Some times you may get funny error messages. Though you might have hard disk space, sometimes the following error might popup -
Error in file: ...\standard\axapd.aoi while reading in record ...
Error code: 38 = hard disk is full
For all the error messages involving axapd.aoi, the solution would be deleting the "axapd.aoi" file from Axapta Application\Appl\Standard directory. When you restart the system again, Axapta would rebuild this index file.
If the problem persists even after deleting the file, then check whether "Open application files in exclusive mode" is enabled (Axapta Configuration Utility --> Under General Tab). If enabled, then disable it. This should solve the problem.
6. What is "Master.aoc" file and how can I create it ?
aoc stands for Axapta Object Cache. This functionality was introduced in Axapta ver 2.5 mainly intended for best performance possible.
When running a 3-Tier Navision Axapta 2.5 client, we will cache different objects as we use the application. All objects are cached in the memory on the client machine. When the client is shutdown, the cache objects are written to disk, in a file with the extension .aoc (Axapta Object Cache). The next time a user starts the client, the executable ax32.exe checks for the .aoc file and if one exists it is loaded into memory. This gives us a performance gain, as we do not have to cache these objects again.
How to create a preconfigured cache file?
This is very simple. We configure a 3-tier client and go through the parts of the application all clients will use. Ex - General Ledger, Sales Order and Accounts Receivable. Doing this will create an .aoc file, with the following naming convention - "ax_AOS [Instance name]@[Hostname]_[username].aoc". In my case, I got a file like this -
ax_Annai@Himalaya_Harish.aoc. This file, if used as the preconfigured cache file, must be renamed to "master.aoc". Please note that this naming convention of the .aoc file would be different if you have configured an Object Server Cluster. (Ex - ax[cluster name][username].aoc)
When installing the Navision Axapta 2.5 client software, the setup.exe program will look for the master.aoc file in the directory where setup.exe is located. After completing the installation of the client, the setup.exe program will, if a master.aoc file exists, copy this file to the \Log directory, located in the default directory structure of the installed client.
When a user starts the client software for the first time, the executable as32.exe, will look for a cache file that belongs to the user, who is currently logged in. If one exists then this cache file is loaded into memory. If none exists, then the ax32.exe will look in the \Log directory for the master.aoc file. If one exists, the objects in this cache file would be loaded into memory.
Friday, May 21, 2010
Dynamics AX unpack type mismatch
I had this strange problem where in when a form loaded it gave an error on the unpack method stating type mismatch. The code was find and was not able to find any issues with it until i realized that i had done a xpo import recently which means the pack was done on the previous version and now the system was trying an unpack on a new version after the import.
in this new version there was a new parameter added which was not packed thus i had to clear the cached data and the issue was resolved.
The cached data could be cleared using the link
Tools
in this new version there was a new parameter added which was not packed thus i had to clear the cached data and the issue was resolved.
The cached data could be cleared using the link
Tools
Wednesday, May 19, 2010
Trigger to Stop Database Drop
CREATE TRIGGER [ddl_trig_Prevent_Drop_Database]
ON ALL SERVER
FOR DROP_DATABASE
AS
--log attempt to drop database
DECLARE @db VARCHAR(209)
SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +
' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))
RAISERROR(@db, 16, 1)WITH LOG
--prevent drop database
ROLLBACK
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--turn on trigger
ENABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
--test trigger
CREATE DATABASE test1
DROP DATABASE test1
Msg 50000, Level 16, State 1, Procedure ddl_trig_Prevent_Drop_Database, Line 11
Database Dropped Attempted by TestSQLUser executing command: DROP DATABASE test1
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
--turn off trigger
DISABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO
/****** Object: DdlTrigger [ddl_trig_Prevent_Drop_Database] Script Date: 01/11/2010 19:22:28 ******/
IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'ddl_trig_Prevent_Drop_Database')
DROP TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO
--cleanup current errorlog
sp_cycle_errorlog
ON ALL SERVER
FOR DROP_DATABASE
AS
--log attempt to drop database
DECLARE @db VARCHAR(209)
SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +
' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))
RAISERROR(@db, 16, 1)WITH LOG
--prevent drop database
ROLLBACK
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
--turn on trigger
ENABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
--test trigger
CREATE DATABASE test1
DROP DATABASE test1
Msg 50000, Level 16, State 1, Procedure ddl_trig_Prevent_Drop_Database, Line 11
Database Dropped Attempted by TestSQLUser executing command: DROP DATABASE test1
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
--turn off trigger
DISABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO
/****** Object: DdlTrigger [ddl_trig_Prevent_Drop_Database] Script Date: 01/11/2010 19:22:28 ******/
IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'ddl_trig_Prevent_Drop_Database')
DROP TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO
--cleanup current errorlog
sp_cycle_errorlog
List of Actively running commands by SPID in SQL Server
This following code snippet would list all the actively running commands by SPID
select session_id, Text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) t
select session_id, Text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) t
SQL Server Transaction Log
DBCC LOG([,{0|1|2|3|4}])
0 - Basic Log Information (default)
1 - Lengthy Info
2 - Very Length Info
3 - Detailed
4 - Full
Example:
DBCC log (MY_DB, 4)
And it displays the following transaction log information:
* Current LSN
* Operation (string starts with LOP_XXXXX)
* Context (string starts with LCX_XXXX)
* Transaction ID
* Tag Bits (Hex)
* Log Record Fixed Length (int)
* Log Record Length (int)
* Previous LSN
* Flag Bits (Hex)
* Description (string)
* Log Record (Hex)
There is another command which is used to read the transaction log ::fn_dblog. I found an interesting hack by Paul S Randal on how to find which user dropped an object using the transaction log.
SELECT [Transaction Id], [Begin Time], [UID], [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
The (NULL, NULL) is the starting LSN and ending LSN to process - NULL means process everything available.
Now, this only shows us that a table was dropped, not which table it was. There's no way to get the name of the table that was dropped, only the object ID - so you'll need to have some other way to determine what the table ID is if there are multiple table drops and only one of them is malignant.
For SQL Server 2000, the code to find which object ID we're talking about is as follows (dropping the Transacation Id into the WHERE clause):
SELECT DISTINCT [Object Name] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:000000e0'
AND [Context] = 'LCX_IAM';
GO
Object Name
--------------------
(2009058193)
The object ID in parentheses is the ID of the table that was dropped.
For SQL Server 2005 and 2008, the code is as follows (with the same Transaction Id substitution):
SELECT TOP (1) [Lock Information] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:00000587'
AND [Lock Information] LIKE '%SCH_M OBJECT%';
GO
Lock Information
--------------------------------------------
ACQUIRE_LOCK_SCH_M OBJECT: 8:2073058421:0
The 8:2073058421 is the database ID and object ID of the table that was dropped.
PS If you find the you don't get enough info from ::fn_dblog, try turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.
0 - Basic Log Information (default)
1 - Lengthy Info
2 - Very Length Info
3 - Detailed
4 - Full
Example:
DBCC log (MY_DB, 4)
And it displays the following transaction log information:
* Current LSN
* Operation (string starts with LOP_XXXXX)
* Context (string starts with LCX_XXXX)
* Transaction ID
* Tag Bits (Hex)
* Log Record Fixed Length (int)
* Log Record Length (int)
* Previous LSN
* Flag Bits (Hex)
* Description (string)
* Log Record (Hex)
There is another command which is used to read the transaction log ::fn_dblog. I found an interesting hack by Paul S Randal on how to find which user dropped an object using the transaction log.
SELECT [Transaction Id], [Begin Time], [UID], [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
The (NULL, NULL) is the starting LSN and ending LSN to process - NULL means process everything available.
Now, this only shows us that a table was dropped, not which table it was. There's no way to get the name of the table that was dropped, only the object ID - so you'll need to have some other way to determine what the table ID is if there are multiple table drops and only one of them is malignant.
For SQL Server 2000, the code to find which object ID we're talking about is as follows (dropping the Transacation Id into the WHERE clause):
SELECT DISTINCT [Object Name] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:000000e0'
AND [Context] = 'LCX_IAM';
GO
Object Name
--------------------
(2009058193)
The object ID in parentheses is the ID of the table that was dropped.
For SQL Server 2005 and 2008, the code is as follows (with the same Transaction Id substitution):
SELECT TOP (1) [Lock Information] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:00000587'
AND [Lock Information] LIKE '%SCH_M OBJECT%';
GO
Lock Information
--------------------------------------------
ACQUIRE_LOCK_SCH_M OBJECT: 8:2073058421:0
The 8:2073058421 is the database ID and object ID of the table that was dropped.
PS If you find the you don't get enough info from ::fn_dblog, try turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.
Install DAX without Domain Controller
The DAX installer compares current user's domain with the local machine netbios name. If they match the installer thinks that it has been run under a local user and throws an error
«You are logged on with a local computer account. You must be logged on with a domain account to run Microsoft Dynamics AX Setup».
To avoid this check change local machine netbios name in the registry (HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName) AND after that change UserDnsDomain environment variable to the same value prior to running the setup.
You can run cmd.exe, set the environment variable and run the setup from the command line so that it can "see" the changed value. After installation change ActiveComputerName in the registry back to its original value.
«You are logged on with a local computer account. You must be logged on with a domain account to run Microsoft Dynamics AX Setup».
To avoid this check change local machine netbios name in the registry (HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName) AND after that change UserDnsDomain environment variable to the same value prior to running the setup.
You can run cmd.exe, set the environment variable and run the setup from the command line so that it can "see" the changed value. After installation change ActiveComputerName in the registry back to its original value.
Saturday, May 15, 2010
Transaction log not truncating
I landed into this strange problem where i was not able to truncate the log of the database i tried the backup log and then shrinkfile a no of times with no luck.
Then i remembered that the database was being replicated using transactional log shipments so maybe there was some issue there. Thus started to look for any open transactions which might be holding the transaction logs used the command
DBCC OPENTRAN(db_name)
Transaction information for database 'BATEELNAV5'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (28941:199:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Got a message which confirmed that there was a replication transaction which was holding the database I got rid of the same using
use
go
sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
go
I was then able to truncate the log successfully.
Then i remembered that the database was being replicated using transactional log shipments so maybe there was some issue there. Thus started to look for any open transactions which might be holding the transaction logs used the command
DBCC OPENTRAN(db_name)
Transaction information for database 'BATEELNAV5'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (28941:199:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Got a message which confirmed that there was a replication transaction which was holding the database I got rid of the same using
use
go
sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
go
I was then able to truncate the log successfully.
Monday, April 19, 2010
AX 2009 Document Management FilePath
We are doing this project where it is required that the attachments are stored into a folder on the HDD now thatz a default configurable parameter in AX the complication is that in this case we are expecting the attachment size to be huge so much so that it exceeds the storage capacity of the server.
Well in the modern world no one takes a decision of making a change in the system to accomodate for such a scenario as the easiest option is to increase the storage space but in this case it seems that we do not have an option to further expand the storage on the server and once the server disk is filled up the only option is to either get a new server which has more slots to push more HDD's or to backup the files onto a different server.
AX allows us to configure a folder path for each file type but the issue is that it only maintains the file name in the transaction tables and the path is maintained in the parameter table which means if the path is changed at a later date AX will not be able to trace the old attachements coz when those were made the path was different
I found where my attachments for a customer were being stored and realized that the path was being referred to from the parameters table and the full file name with path was being build in a function in the docuref table called completeFileName
select DV.FileName, DV.FileType, DR.* from docuref DR
inner join docuvalue DV
on DR.ValueRecId = DV.RecId
where RefTableId = 77
and PartyId = 191
so the solution in this case is that we decide to store the file path with the file when the attachments are made so that the system remembers multiple paths for the files.
Well in the modern world no one takes a decision of making a change in the system to accomodate for such a scenario as the easiest option is to increase the storage space but in this case it seems that we do not have an option to further expand the storage on the server and once the server disk is filled up the only option is to either get a new server which has more slots to push more HDD's or to backup the files onto a different server.
AX allows us to configure a folder path for each file type but the issue is that it only maintains the file name in the transaction tables and the path is maintained in the parameter table which means if the path is changed at a later date AX will not be able to trace the old attachements coz when those were made the path was different
I found where my attachments for a customer were being stored and realized that the path was being referred to from the parameters table and the full file name with path was being build in a function in the docuref table called completeFileName
select DV.FileName, DV.FileType, DR.* from docuref DR
inner join docuvalue DV
on DR.ValueRecId = DV.RecId
where RefTableId = 77
and PartyId = 191
so the solution in this case is that we decide to store the file path with the file when the attachments are made so that the system remembers multiple paths for the files.
Saturday, April 10, 2010
Create Clustered Index Script
I had this requirement where we identified that a no of tables in AXapta were missing the clustered index which was resulting in some performance issues. It was then recommended by Microsoft to create clustered indexes on all these tables this is what we did.
--list of all tables missing an clustered index
select SO.name, SI.*
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'
--script to generate the script
select 'create clustered index [IDX_' + SO.name + '] on [' + SO.name + '] (recid, dataareaid )'
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'
another important query is to list all the columns in a index in this case clustered index
--list of columns in the clustered index
select OBJECT_NAME(SI.id) TableName, SI.name IndexName, SIK.indid IndexId, SC.name ColName
from sysindexes SI
inner join sysindexkeys SIK
on SI.id = SIK.id
and SI.indid = SIK.indid
inner join syscolumns SC
on SC.id = SIK.id
and SC.colid = SIK.colid
where SIK.id = OBJECT_ID( 'ContactMapping' )
and SIK.indid = 1
order by SIK.indid, SC.colorder
--list of all tables missing an clustered index
select SO.name, SI.*
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'
--script to generate the script
select 'create clustered index [IDX_' + SO.name + '] on [' + SO.name + '] (recid, dataareaid )'
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'
another important query is to list all the columns in a index in this case clustered index
--list of columns in the clustered index
select OBJECT_NAME(SI.id) TableName, SI.name IndexName, SIK.indid IndexId, SC.name ColName
from sysindexes SI
inner join sysindexkeys SIK
on SI.id = SIK.id
and SI.indid = SIK.indid
inner join syscolumns SC
on SC.id = SIK.id
and SC.colid = SIK.colid
where SIK.id = OBJECT_ID( 'ContactMapping' )
and SIK.indid = 1
order by SIK.indid, SC.colorder
Thursday, April 08, 2010
Space used by each Table in SQL
SQL 2005 Version
================
BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);
INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END
FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,tablename;
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch
Previous Versions of SQL
========================
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
================
BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);
INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END
FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,tablename;
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch
Previous Versions of SQL
========================
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
Subscribe to:
Posts (Atom)