Histórico da Página
| HTML |
|---|
<div id="main-content" class="wiki-content group">
<h1 id="MigraçãoeConversãodeCollation(SQLServer)-Índice">Contents</h1>
<p>
<style type='text/css'>/*<![CDATA[*/
div.rbtoc1412695589165 {
padding: 0px;
}
div.rbtoc1412695589165 ul {
list-style: none;
margin-left: 0px;
}
div.rbtoc1412695589165 li {
margin-left: 0px;
padding-left: 0px;
}
/*]]>*/
</style>
<div class='toc-macro rbtoc1412695589165'>
<ul class='toc-indentation'>
<li><span class='TOCOutline'>1</span> <a
href='#MigraçãoeConversãodeCollation(SQLServer)-Objetivo'>Objective</a></li>
<li><span class='TOCOutline'>2</span> <a
href='#MigraçãoeConversãodeCollation(SQLServer)-ProcedimentodeMigraçãoeConversãodeDados'>Data
Migration and Conversion Procedure</a></li>
</ul>
</div>
</p>
<h1 id="MigraçãoeConversãodeCollation(SQLServer)-Objetivo">Objective</h1>
<p>The objective of this guide is to indicate a method of migrating
data from a base with collation different than the standard
"Latin1_General_CI_AS" required for the proper use of the Fluig base.</p>
<h1
id="MigraçãoeConversãodeCollation(SQLServer)-ProcedimentodeMigraçãoeConversãodeDados">Data
Migration and Conversion Procedure</h1>
<p>
<strong><br /></strong>
</p>
<div class="aui-message warning shadowed information-macro">
<p class="title">Attention!</p>
<span class="aui-icon icon-warning">Icon</span>
<div class="message-content">
<p>This operation must take place before the conversion of a
TOTVS | ECM base to Fluig. Even with modification of the default
collation, the existing fields remain incorrect, and can therefore
result in unexpected behavior by the platform.</p>
</div>
</div>
<p> </p>
<p>The following is a checklist for Fluig conversion. </p>
<p> </p>
<ol>
<li>Check procedures for the service/instance of the SGBD
Microsoft SQL <span
style="font-size: 10.0pt; line-height: 13.0pt;">Server,
describe in the following page: <a href="73078837.htmlhttp://tdn.totvs.com/pages/viewpage.action?pageId=185741478">Configuration
of the SQL Server data bank</a>.
</span><br /> <br />
</li>
<li>Create a new data base with <strong>grouping </strong>(Collate/Collation)
Latin1_General_CI_AS.<br /> <span
style="font-size: 10.0pt; line-height: 13.0pt;"><br /></span></li>
<li><p>
Immediately after creation, <strong>with no connection</strong> in
this data base, execute the command:
</p>
<p>
<br /> <br />
</p>
<div class="code panel pdl" style="border-width: 1px;">
<div class="codeContent panelContent pdl">
<pre class="theme: Eclipse; brush: sql; gutter: false"
style="font-size: 12px;">ALTER DATABASE [fluig] SET READ_COMMITTED_SNAPSHOT ON;</pre>
</div>
</div>
<p>
<br />The configurations for grouping of characters (<em>Collate/Collation</em>)
of the data bases may still be queried through the following
instruction:
</p>
<p>
<span style="font-size: 10.0pt; line-height: 13.0pt;"><br /></span>
</p>
<div class="code panel pdl" style="border-width: 1px;">
<div class="codeContent panelContent pdl">
<pre class="theme: Eclipse; brush: sql; gutter: false"
style="font-size: 12px;">SELECT [name] , [is_read_committed_snapshot_on] , [collation_name] 
FROM [sys].[databases]
WHERE [name] LIKE 'fluig%';</pre>
</div>
</div>
<p>
<br /> <br />
</p></li>
<li>Install the latest version of Fluig and configure it to point
to this data base (fluig).<br /> <br />
</li>
<li>Start the Fluig service, and observe the LOG to make sure
that <span style="font-size: 10.0pt; line-height: 13.0pt;">the
service started correctly, without any serious errors, such as
failure to </span><span
style="font-size: 10.0pt; line-height: 13.0pt;">connect with
SGBD.</span><br /> <span style="font-size: 10.0pt; line-height: 13.0pt;"><br /></span>
</li>
<li>After the service has finished starting up (with the message
"== FLUIG STARTED AND RUNNING RIGHT NOW ==" reported in the LOG),
interrupt the Fluig service.<br /> <br />
</li>
<li>Generate a DUMP of this data base, with the entire structure
of tables and <span>fields. </span><br /> <span>For
the SGBD Microsoft SQL Server, use the following </span><span>procedure:</span><br />
<ol>
<li style="list-style-type: none;"><ol>
<li>Move the mouse over the database and click on the right
button.</li>
<li>In the drop-down menu, choose <strong>Tasks >
Generate Scripts...</strong></li>
<li>Click on <strong>Next ></strong> to see the
presentation <span>Introducing <strong>Generate
and Post Scripts</strong> wizard.
</span></li>
<li><span>Check <strong>Generate script for
entire data </strong></span><span><strong>base</strong> and <strong>all
data base objects</strong> and click </span><span><strong>Next
></strong>.</span></li>
<li><span>In the following screen, in the <strong>Output
Type</strong> group, and in the group below, 
</span><span>select <strong>Save</strong> in the new query window.
</span></li>
<li><span>Click on <strong>Advanced</strong>.
</span></li>
<li><span>In the <strong>Advanced Script
Options</strong> screen, change the option 
</span><span><strong>Grouping Scripts</strong> to <strong>True</strong>
and click on <strong>OK</strong>.</span></li>
<li><span>When returning to the <strong>Generate
and Post Scripts </strong>screen, click 
</span><span>on <strong>Next ></strong>.
</span></li>
<li><p>
In the next step, adjust the configurations to generate a script
of the base. Click on <strong>Next ></strong>.
</p></li>
<li><p>Go back to the Microsoft SQL Server Management
Studio window.</p></li>
<li><p>
<span>In the new window open with the script,
comment/remove the first few </span><span>lines, up to the
<strong>USE [fluig]</strong> instruction.
</span>
</p>
<p>
<span><br /></span>
</p></li>
</ol></li>
</ol>
</li>
<li><p>Select and delete all tables in this new database.</p>
<p> </p></li>
<li><p>
Execute the script DUMP generate in this new database in order to
create the entire structure with <strong>no</strong> records.
</p>
<p> </p></li>
<li><p>Proceed with constraints in the database tables. For
the SGBD Microsoft SQL Server, use the following instruction:</p> <br />
<ol>
<li style="list-style-type: none;"><ol>
<li><p>
Open a New Query window (menu <strong>File > New >
Database Mechanism Query</strong>);.
</p></li>
<li><p>Insert the query below and execute it in the new
database.</p>
<div class="code panel pdl" style="border-width: 1px;">
<div class="codeContent panelContent pdl">
<pre class="theme: Eclipse; brush: sql; gutter: false"
style="font-size: 12px;">DECLARE @TABLENAME VARCHAR(8000)
DECLARE @TABLENAME_HEADER VARCHAR(8000)
DECLARE TNAMES_CURSOR CURSOR FOR
SELECT [name] from [sys].[tables]
OPEN TNAMES_CURSOR
FETCH NEXT FROM TNAMES_CURSOR INTO @TABLENAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SELECT @TABLENAME_HEADER = 'ALTER TABLE ' + RTRIM(UPPER(@TABLENAME)) + ' NOCHECK CONSTRAINT ALL;'
--Change NOCHECK to CHECK and then enable constraints
PRINT @TABLENAME_HEADER
END
FETCH NEXT FROM TNAMES_CURSOR INTO @TABLENAME
END
SELECT @TABLENAME_HEADER = '--************* End of Tables *************--'
PRINT @TABLENAME_HEADER PRINT ' '
DEALLOCATE TNAMES_CURSOR </pre>
</div>
</div></li>
<li><p>Copy the result, paste it in a New Query window
and execute it (in the new database).</p>
<p> </p></li>
</ol></li>
</ol></li>
<li><p>With the Fluig services interrupted, import the data
from the old database, using, for example, an import and export data
wizard. For the SGBD Microsoft SQL Server, use the
following procedure:</p> <br />
<ol>
<li style="list-style-type: none;"><ol>
<li><p>Move the mouse over the new database and click on
the right button.</p></li>
<li><p>
In the drop-down menu, select <strong>Tasks > Import
Data...</strong>
</p></li>
<li><p>
In the introductory screen for the SQL Server Import and Export
Wizard, click on <strong>Next ></strong>.
</p></li>
<li><p>
In the field <strong>Data</strong> source, select <strong>SQL
Server Native Client</strong> (the version number is displayed at the
end, use the same number as the data bank service version).
</p></li>
<li><p>
In the field <strong>Server</strong> name, enter the server
(enter or select from drop-down box) and the instance that has
the previous database (which must be reconciled /
normalized). 
</p></li>
<li><p>
In the <strong>Authentication</strong> group, use the method
that allows access to all data from this previous database. A
user with <strong>db_owner</strong> permission is recommended.
</p></li>
<li><p>
In the field <strong>Database</strong>, select or enter the
previous base and click on <strong>Advance ></strong>.
</p></li>
<li><p>
In the screen with the header <strong>Choose a
Target</strong>, the fields with access information to this new database
should be automatically filled out. Correct any incorrect or
incomplete information and click <strong>Next ></strong>.
</p></li>
<li><p>
In the screen with the header <strong>Specify Copy</strong> or <strong>Table
Query</strong>, select <strong>Copy</strong> data from one or more
tables or views and click <strong>Next ></strong>.
</p></li>
<li><p>
In the screen with the header <strong>Select Tables</strong> and
<strong>Source Views</strong>, click on the first selection box
in the headers of the <strong>Tables and views</strong> list.
</p></li>
<li><p>
With all options checked, click <strong>Edit
Mappings...</strong>
</p></li>
<li><p>
In the box <strong>Transfer Settings, </strong>check the box <strong>Enable
insertion of identity</strong> and OK.
</p>
<div class="aui-message warning shadowed information-macro">
<p class="title">Attention!</p>
<span class="aui-icon icon-warning">Icon</span>
<div class="message-content">
<p>Make sure to check this option to avoid failure in data
copying.</p>
</div>
</div></li>
<li><p>
Back to the previous window, <strong>Select Tables</strong> e <strong>Source
Views</strong>, click on <strong>Next ></strong>.
</p></li>
<li><p>
In the screen with the header <strong>Review Data</strong> <strong>Type
Mapping</strong>, click <strong>Next</strong>.
</p></li>
<li><p>
In the screen with the header <strong>Execute Package,
</strong>check <strong>Execute immediately</strong> and click <strong>Next
></strong>.
</p></li>
<li><p>
In the screen with the header <strong>Conclude Wizard</strong>,
review the actions taken and click <strong>Next</strong>.
</p></li>
<li><p>The following screen, displaying execution of the
actions, should also display a message that import was
successful.</p></li>
</ol></li>
</ol>
<p> </p></li>
<li><p>Perform access permissions transfer procedure to the
appropriate users in the new database. These permissions may have
been lost. </p>
<p> </p></li>
<li><p>
Delete the temporary directories (tmp, log and data), located in the
path <em><INSTALL_FLUIG>/jboss/standalone</em>.
</p>
<p> </p></li>
<li><p>
In the setting file, located in the path <em><INSTALL_FLUIG>/jboss/standalone/standalone.xml</em>,
change the setting for access to the database to use of the new
database.
</p>
<p> </p></li>
<li><p>Restart the service.</p>
<p> </p></li>
<li><p>Perform the desired validation and use the new
installation.</p></li>
</ol>
<p> </p>
<p> </p>
</div>
|
Visão Geral
Import HTML Content
Conteúdo das Ferramentas
Tarefas