tag:blogger.com,1999:blog-19489201087112137082024-03-13T09:02:31.582-07:00Database Knowledgeタオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.comBlogger390125tag:blogger.com,1999:blog-1948920108711213708.post-55736048721168163362015-04-12T21:55:00.001-07:002015-04-12T21:55:45.383-07:00Life Quote<a href="http://languagexyz.appspot.com/LifeQuote">Life Quote</a>: The difficulties, hardships and trials of life, the obstacles...are positive blessings. They knit the muscles more firmly, and teach self-reliance. ~ William Matthewsタオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-20172375919141293812015-04-10T08:59:00.002-07:002015-04-10T08:59:43.499-07:00SAP FI PracticeIf you are working with SAP in the module FICO, you can use this one to practice <a href="http://languagexyz.appspot.com/sapfi">http://languagexyz.appspot.com/sapfi</a><br />
It looks like the following and you can run on your smartphone or tablet.<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="http://languagexyz.appspot.com/images/SAPFI.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://languagexyz.appspot.com/images/SAPFI.png" height="640" width="392" /></a></div>
<br />タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-5462743326215143762014-02-22T20:19:00.001-08:002014-02-22T20:22:21.897-08:00Better Decisions with Smarter Data | MIT Sloan Management Review<a href="http://sloanreview.mit.edu/article/better-decisions-with-smarter-data/?utm_source=facebook&utm_medium=social&utm_campaign=sm-direct">Better Decisions with Smarter Data | MIT Sloan Management Review</a>:<br />
<br />
<ol>
<li><b>Accurate </b>– data must be what it says it is with enough precision to drive value. Data quality matters. </li>
<li><b>Actionable </b>– data must drive an immediate scalable action in a way that maximizes a business objective like media reach across platforms. Scalable action matters. </li>
<li><b>Agile </b>– data must be available in real-time and ready to adapt to the changing business environment. Flexibility matters.</li>
</ol>
タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-55794930328248684032014-01-22T07:36:00.001-08:002014-01-22T07:36:51.999-08:00Agile Principles and Values, by Jeff Sutherland<a href="http://msdn.microsoft.com/en-us/library/dd997578.aspx">Agile Principles and Values, by Jeff Sutherland</a>タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-34289929678453625272013-01-01T08:48:00.000-08:002013-01-01T08:48:37.403-08:00PostgreSQL: Replace multiple spaces with oneIf you want to use a single query to replace multiple spaces with one on SQL Server, you can read the articles here http://www.sqlservercentral.com/articles/T-SQL/68378/<br />
However, it's very simple in PostgreSQL by using Regular Expression<br />
<span style="color: blue;"><b>SELECT </b></span>regexp_replace('This sentence contains multiple spaces', '\s+', ' ', 'g');<br />
<br />タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com6tag:blogger.com,1999:blog-1948920108711213708.post-8398519711484707842012-12-30T20:08:00.004-08:002012-12-30T20:08:53.717-08:00PostgreSQL - Copy CSV to table with psql - part 2<br />
<pre><span style="color: blue;">CREATE</span> <span style="color: blue;">TABLE</span> tbl_test
(
a text,
b text,
c text
)
</pre>
Content of test.csv, and it put in /Database folder<br /><i>
a1,b1,c1<br />
a2,b2,c2<br />
a3,b3,c3</i><br />
<br />Instead of using COPY command directly, you can modify the test.cvs file to include it, as<br />
<i>COPY tbl_test FROM STDIN WITH CSV;</i><br />
<i>a1,b1,c1<br />a2,b2,c2<br />a3,b3,c3</i><br />
<i>\.</i><br />
<br />
Note that you have to include a carriage return in the last line of the file (after '\.')<br />
Then, run the following command to import<br />
<br />
<pre class="code-java">MyComputer$ psql -h localhost -p 5432 -U postgres testdb -f ~/Database/test.csv</pre>
タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-19479236655771495942012-12-13T07:41:00.002-08:002012-12-13T07:41:47.778-08:00PostgreSQL - Copy databaseSometimes, instead of back up and restore to a new db and wait your time if you only want to copy a database and put in the same server, you can use the following query:<br />
<pre><span style="color: blue;">CREATE</span> <span style="color: blue;">DATABASE</span> newdb <span style="color: blue;">WITH TEMPLATE </span>originaldb OWNER dbuser;</pre>
Note that this query only successfully if no sessions existing in originaldb. So, you can check originaldb sessions and kill them with this function <b>pg_terminate_backend(procpid)</b>タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-94148502057251982012-12-13T06:43:00.003-08:002012-12-13T06:43:59.981-08:00PostgreSQL - Kill idle processesSometimes, you need to kill all unnecessary processes running on your database server. You can do that by using the following query:
<br />
<pre><span style="color: blue;">SELECT</span>
procpid,
(
<span style="color: blue;">SELECT</span> pg_terminate_backend(procpid)
) <span style="color: blue;">AS</span> killed
<span style="color: blue;">FROM</span> pg_stat_activity
<span style="color: blue;">WHERE</span> current_query <span style="color: blue;">LIKE</span> '%idle%' <span style="color: blue;">AND</span> datname = 'YourDBName';
</pre>
タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-47174682035314257612012-12-09T07:54:00.000-08:002012-12-09T07:54:01.952-08:00PostgreSQL - Copy csv file to table with psql<pre><span style="color: blue;">CREATE</span> <span style="color: blue;">TABLE</span> tbl_test
(
a text,
b text,
c text
)
</pre>
Content of test.csv<br />
a1,b1,c1<br />
a2,b2,c2<br />
a3,b3,c3<br />
<br />
psql to COPY<br />
psql -h localhost -U postgres testdb<br />
testdb=# COPY tbl_test FROM 'D:\Temp\test.csv' WITH DELIMITER ',' CSV; タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-10136234386004779592012-12-09T06:35:00.003-08:002012-12-09T06:35:50.341-08:00PostgreSQL - VACUUM ANALYZE EXPLAIN NOTIFYSelect the correct statement that records the space occupied by deleted or updated rows for later reuse, and also updates statistics. <br /><br />A. VACUUM <br />B. VACUUM ANALYZE <br />C. EXPLAIN <br />D. EXPLAIN ANALYZE <br />E. NOTIFY<br />
F. None of the above <br />
<br />
Answer: <span style="color: white;">[B]</span><br />
Highlight to find out the answer.タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-463989117732618392012-12-09T06:15:00.000-08:002012-12-09T06:15:28.662-08:00PostgreSQL - ANALYZE quizWhat does the following command do?<br />
Choose the correct answer below. <br />Note: "psql=#" is the command prompt for psql. <br />psql=# ANALYZE xyz; <br /><br />A. Collects statistical information related to the content of the database xyz. <br />B. Collects statistical information related to the content of the table xyz. <br />C. Outputs statistical information related to the content of the table xyz. <br />D. No ANALYZE command in PostgreSQL, error will occur. <br />E. None of the above<br />
F. Both B & C<br />
<br />
Answer: <span style="color: white;">[B]</span><br />
Highlight to find out the answer.タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-91871393069903204662012-12-07T09:14:00.001-08:002012-12-07T09:49:22.811-08:00PostgreSQL – Convert rows to string<pre><span style="color: blue;">SELECT</span> array_to_string(
array(
<span style="color: blue;">SELECT</span> name
<span style="color: blue;">FROM</span> pg_settings
<span style="color: blue;">WHERE</span> setting = 'off' <span style="color: blue;">AND</span> context = 'postmaster'
), ',');
<span style="color: blue;">SELECT</span> setting, array_to_string(array_agg(name), ',')
<span style="color: blue;">FROM</span> pg_settings
<span style="color: blue;">WHERE</span> setting <span style="color: blue;">IN</span> ('on', 'off')
<span style="color: blue;">GROUP</span> <span style="color: blue;">BY</span> setting;
</pre>
タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-11022523269502157102012-11-24T09:02:00.002-08:002012-11-24T09:02:50.016-08:00PostgreSQL - Query data from another database/serverAssume that, you have two databases:<br />
1. db1 with two schemas: public and sch1<br />
2. db2 with two schemas: public and sch2<br />
<br />
In PostgreSQL, you can access a table from a different schema by using SELECT * FROM schema_name.table_name such as SELECT * FROM db1_sch.employee;<br />
However, you cannot access a table on the different database with explicit way like SELECT * FROM db2.sch2.employee;<br />
<br />
The question is how can we access like SQL Server or some other DBMS.<br />
Fortunately, PostgreSQL support dblink to do that.<br />
<br />
First, install extension dblink on the database you want to be able to access to other<br />
CREATE EXTENSION dblink;<br />
<br />
If db2 is in the same server, you can query table in db2 like that:<br />
SELECT *<br />
FROM dblink('dbname=db2', 'SELECT employee_id, employee_name FROM sch2.employee')<br />
AS employee(employee_id integer, employee_name text);<br />
<br />
If db2 is in the different server, you can query:<br />
SELECT *<br />
FROM dblink('hostaddr=172.x.x.x dbname=db2 user=admin password=pwd port=5432', 'SELECT employee_id, employee_name FROM sch2.employee')<br />
AS employee(employee_id integer, employee_name text);タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com1tag:blogger.com,1999:blog-1948920108711213708.post-66159542371179386852012-11-05T07:20:00.001-08:002012-11-05T07:25:01.704-08:00PostgreSQL - get table structure<span style="color: blue">SELECT</span> ordinal_position <br /> , column_name <br />, <span style="color: blue">CASE</span> data_type <span style="color: blue">WHEN</span> 'character varying' <span style="color: blue">THEN</span> data_type || '('||character_maximum_length || ')' <span style="color: blue">ELSE</span> data_type <span style="color: blue">END</span> data_type <br /> , column_default <br />, is_nullable <br />, character_maximum_length <br />, numeric_precision <br /><span style="color: blue">FROM</span> information_schema.columns <br /><span style="color: blue">WHERE</span> table_catalog = 'database_name' <br /><span style="color: blue">AND</span> table_schema = 'schema_name' <br /><span style="color: blue">AND</span> table_name = 'table_name' <br /><span style="color: blue">ORDER</span> <span style="color: blue">BY</span> ordinal_position; タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-36708013130296439282012-10-19T01:02:00.001-07:002012-10-19T01:03:39.443-07:00PostgreSQL: check the core settings of server<p><span style="color: blue">SELECT</span> name, context, unit, setting, short_desc </p> <p><span style="color: blue">FROM</span> pg_settings </p> <p><span style="color: blue">WHERE</span> name in ('listen_addresses' ,'max_connections' ,'shared_buffers' ,'effective_cache_size' , 'work_mem' , 'maintenance_work_mem') </p> <p><span style="color: blue">ORDER</span> <span style="color: blue">BY</span> context,name; </p> <p><a href="http://lh3.ggpht.com/-uZiMNAnlje0/UIEJB3H-pCI/AAAAAAAAF9k/f8TEQW6iznw/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-Rm1fSzzWkoo/UIEJC4u5uMI/AAAAAAAAF9o/gnDtPDLgtOc/image_thumb%25255B1%25255D.png?imgmax=800" width="613" height="131" /></a></p> <p>If <strong>context</strong> is set to <strong><em>postmaster</em></strong>, it means changing this parameter requires a restart of the postgresql service. If <strong>context</strong> is set to user, changes require at least a reload. Furthermore, these settings can be            overridden at the database, user, session, or function levels.</p> <p><strong>unit</strong> tells you the unit of measurement that the setting is reported in. This is very important for memory settings since, as you can see, some are reported in 8 kB and some in kB</p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-24135065625436449312012-10-18T00:36:00.001-07:002012-10-18T00:37:16.420-07:00PosgreSQL: get main configuration files<span style="color: blue">SELECT</span> name, setting <span style="color: blue">FROM</span> pg_settings <span style="color: blue">WHERE</span> category = 'File Locations'; <p><a href="http://lh5.ggpht.com/-NloE7tGMdLg/UH-xZ1tCt0I/AAAAAAAAF9E/zZOrJnXvAsw/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-e47bysCwrIc/UH-xbd9LMBI/AAAAAAAAF9I/n8amPHk1tro/image_thumb%25255B2%25255D.png?imgmax=800" width="447" height="113" /></a></p> <p><strong>postgresql.conf</strong> controls general settings, such as how much memory to allocate, default storage location for new databases, which IPs PostgreSQL listens on, where logs are stored, and so forth.</p> <p><strong>pg_hba.conf</strong> controls security. It manages access to the server, dictating which users can login into which databases, which IPs or groups of IPs are permitted to connect and the authentication scheme expected.</p> <p><strong>pg_ident.conf</strong> is the mapping file that maps an authenticated OS login to a PostgreSQL user. This file is used less often, but allows you to map a server account to a PostgreSQL account. For example, people sometimes map the OS root account to the postgre’s super user account. Each authentication line in pg_hba.conf can use a different pg_ident.conf file</p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-68386558734986101582012-10-17T23:39:00.001-07:002012-10-17T23:39:29.301-07:00PostgreSQL quiz: stored procedure execution<p>Assume that you have a table contact and a stored procedure called insert_contact as the following screenshots</p> <p><a href="http://lh3.ggpht.com/-AwhngJRi9ms/UH-kDBy3AaI/AAAAAAAAF8U/wxDOPTNUUWA/s1600-h/image%25255B6%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-7NItBTn65HQ/UH-kEm-EHKI/AAAAAAAAF8c/bdHhHshXum4/image_thumb%25255B2%25255D.png?imgmax=800" width="239" height="100" /></a></p> <p><a href="http://lh6.ggpht.com/-lJPqnuQeFvY/UH-kGCTiUAI/AAAAAAAAF8k/5Ro-pwfwtyY/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-nlNnYRy0DAo/UH-kHZB13fI/AAAAAAAAF8s/LrjaBJXrwLY/image_thumb%25255B1%25255D.png?imgmax=800" width="368" height="106" /></a></p> <p>Which one below is correct?</p> <p>A. CALL insert_contact(‘John’,'Tran’);</p> <p>B. EXECUTE insert_contact(‘John’,’Tran’);</p> <p>C. SELECT insert_contact(‘John’,’Tran’)</p> <p>D. Both A & B</p> <p>E. Error in the store procedure code</p> <p>F. None of the above</p> <p> </p> <p>Answer:<font color="#ffffff"> [C]</font></p> <p>Highlight to find out the answer.</p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-83848229328795211732012-10-16T00:44:00.001-07:002012-10-16T00:44:11.859-07:00PostgreSQL license<p>Select the most suitable statement about the PostgreSQL license from below.</p> <p> <br />A. PostgreSQL is distributed under the GPL license. <br />B. PostgreSQL is distributed under the PostgreSQL license. <br />C. PostgreSQL is distributed under the LGPL license. <br />D. PostgreSQL is distributed under the BSD license. <br />E. PostgreSQL is distributed under the X11(MIT) license.</p> <p> </p> <p>Answer:<font color="#ffffff"> [D]</font></p> <p>Highlight to find out the answer.</p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-39694282010392421412012-10-15T22:55:00.001-07:002012-10-15T23:02:29.890-07:00Convert date time values to string in PostgreSQL<p><span style="color: blue"><font color="#000000">This is a view extracted from OpenERP.</font></span></p> <p><span style="color: blue">CREATE</span> <span style="color: blue">OR</span> REPLACE <span style="color: blue">VIEW</span> crm_claim_report <br /><span style="color: blue">AS</span> <br /><span style="color: blue">SELECT</span> min(c.id) <span style="color: blue">AS</span> id <br />, to_char(c.date, 'YYYY'::text) <span style="color: blue">AS</span> name <br />, to_char(c.date, 'MM'::text) <span style="color: blue">AS</span> month <br />, to_char(c.date, 'YYYY-MM-DD'::text) <span style="color: blue">AS</span> day <br />, to_char(c.date_closed, 'YYYY-MM-DD'::text) <span style="color: blue">AS</span> date_closed <br />, to_char(c.date_deadline::timestamp with time zone, 'YYYY-MM-DD'::text) <span style="color: blue">AS</span> date_deadline, c.state, c.user_id <br />, c.stage_id, c.section_id, c.partner_id, c.company_id, c.categ_id, count(*) <span style="color: blue">AS</span> nbr, c.priority <br />, c.type_action, date_trunc('day'::text, c.create_date) <span style="color: blue">AS</span> create_date <br />, avg(date_part('epoch'::text, c.date_closed - c.create_date)) / (3600 * 24)::double precision <span style="color: blue">AS</span> delay_close <br />, ( <span style="color: blue">SELECT</span> count(mailgate_message.id) <span style="color: blue">AS</span> count <span style="color: blue">FROM</span> mailgate_message <br /><span style="color: blue">WHERE</span> mailgate_message.model::text = 'crm.claim'::text <span style="color: blue">AND</span> mailgate_message.res_id = c.id <span style="color: blue">AND</span> mailgate_message.history = true) <span style="color: blue">AS</span> email <br />, ( <span style="color: blue">SELECT</span> avg(crm_case_stage.probability) <span style="color: blue">AS</span> avg <span style="color: blue">FROM</span> crm_case_stage <br /><span style="color: blue">WHERE</span> crm_case_stage.type::text = 'claim'::text <span style="color: blue">AND</span> crm_case_stage.id = c.stage_id) <span style="color: blue">AS</span> probability <br />, date_part('epoch'::text, c.date_deadline::timestamp without time zone - c.date_closed) / (3600 * 24)::double precision <span style="color: blue">AS</span> delay_expected <br /><span style="color: blue">FROM</span> crm_claim c <br /><span style="color: blue">GROUP</span> <span style="color: blue">BY</span> to_char(c.date, 'YYYY'::text), to_char(c.date, 'MM'::text), to_char(c.date, 'YYYY-MM-DD'::text), c.state, c.user_id, c.section_id <br />, c.stage_id, c.categ_id, c.partner_id, c.company_id, c.create_date, c.priority, c.type_action, c.date_deadline, c.date_closed, c.id; </p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-66259427706089024992012-10-14T04:24:00.000-07:002012-10-14T04:24:02.013-07:00PostgreSQL ContactenationWhich query is correct in PostgreSQL?<br />
<br />
A. SELECT 'Test' . 'query';<br />
B. SELECT cat('Test','query') FROM dual;<br />
C. SELECT 'Test' + 'query';<br />
D. SELECT 'Test' + 'query' FROM dual;<br />
E. SELECT 'Test' || 'query';<br />
<br />
Answer:<span style="color: white;">[E]</span><br />
Highlight to find out the answer.タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-2882718632141193992012-10-12T10:51:00.001-07:002012-10-12T10:51:14.897-07:00Same query for Fiscal and Normal calendar<p>tblFiscalMapping</p> <p><a href="http://lh4.ggpht.com/-TwoqW9VFGUY/UHhYin05fkI/AAAAAAAAF70/HNja7RS4sDc/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-KBEy30Vv5Ao/UHhYjw4RmzI/AAAAAAAAF78/qsS0tXK4_8E/image_thumb%25255B1%25255D.png?imgmax=800" width="296" height="103" /></a></p> <p>Assume that you have a mapping table between Fiscal and Normal calendar above.</p> <p>Sometimes, you need to write one stored procedure with a param @IsFiscal to determine when to return order info on Fiscal year or Normal calendar year.</p> <p>You can implement as the following</p> <p>DECLARE @StartMonth SMALLDATETIME;    <br />DECLARE @EndMonth SMALLDATETIME;</p> <p>IF ISNULL(@IsFiscal,0) = 0 <br />    SELECT @StartMonth = CAST(CAST(@Year AS VARCHAR) + '-01-01' AS SMALLDATETIME); <br />ELSE <br />    SELECT @StartMonth = CAST(CAST([Year] AS VARCHAR) + '-' + CAST(NormalMonth AS VARCHAR) + '-01' AS SMALLDATETIME) <br />    FROM dbo.tblFiscalMapping <br />    WHERE FiscalYear = @Year;</p> <p>SET @EndMonth = DATEADD(m, 11, @StartMonth);</p> <p>SELECT OrderID, OrderNumber, OrderDate, CustomerID, TotalAmount</p> <p>FROM dbo.tblOrder</p> <p>WHERE DATEDIFF(m, @StartMonth, OrderDate) >= 0 <br />                                AND DATEDIFF(m, @EndMonth, OrderDate) <= 0</p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-36611244428132487552012-10-12T08:52:00.001-07:002012-10-12T08:54:48.457-07:00Create a Virtual Active Directory Domain Controller with VirtualBox<p>I need to query Active Directory by using SQL Server, so I created virtual Active Directory Domain Controller to do that.</p> <p>1. Install Windows Server virtual machine, such as Windows Server 2003</p> <p>2. Install Client virtual machine, such as Windows XP</p> <p>3. Set up network</p> <p><a href="http://lh3.ggpht.com/-tHRTpW_PcrI/UHg8X7j_haI/AAAAAAAAF50/FPvdkOV7vYY/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-IUtkwAymirQ/UHg8ZnxBH0I/AAAAAAAAF58/HwlTE5fb-OA/image_thumb%25255B2%25255D.png?imgmax=800" width="266" height="235" /></a></p> <p><a href="http://lh5.ggpht.com/-dWwJeA80hiE/UHg8bDWQcfI/AAAAAAAAF6E/tVXIRzMmOiE/s1600-h/image%25255B10%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-062Q5vMK2VM/UHg8c56s-CI/AAAAAAAAF6M/yQv4-1WYbLI/image_thumb%25255B6%25255D.png?imgmax=800" width="451" height="529" /></a></p> <p>4. Set up new Adapter for each machine, use the adapter set up on Step 3</p> <p><a href="http://lh4.ggpht.com/-u_kati9BwUM/UHg8evehMmI/AAAAAAAAF6U/TPm0c97SyMg/s1600-h/image%25255B15%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-wlIb23AdYs8/UHg8gT7-4GI/AAAAAAAAF6c/1GbLDJbgNLw/image_thumb%25255B9%25255D.png?imgmax=800" width="502" height="374" /></a></p> <p>5. Run Windows Server machine</p> <p>6. Install Active Directory by following steps after running <strong>dcpromo</strong></p> <p><a href="http://lh4.ggpht.com/-5FutRw1bAlo/UHg8hYVy_PI/AAAAAAAAF6k/DQYE6aQT1yU/s1600-h/image%25255B18%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-CMuQImYwRyI/UHg8i7iqMGI/AAAAAAAAF6s/yiKb7LvWxRs/image_thumb%25255B10%25255D.png?imgmax=800" width="244" height="132" /></a></p> <p>7. Set up IP Address on Windows Server, note that IP address must be in the range set up in Step 3</p> <p><a href="http://lh6.ggpht.com/-TSDvLN9i4EY/UHg8kDnN8dI/AAAAAAAAF60/zf1j0k8wswE/s1600-h/image%25255B22%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-DHrZESkEjMI/UHg8mZS180I/AAAAAAAAF68/X1a0UD9vPz8/image_thumb%25255B12%25255D.png?imgmax=800" width="339" height="360" /></a></p> <p>8. Set up IP Address on Client machine</p> <p><a href="http://lh4.ggpht.com/-gxkoZPlFMk0/UHg8oNgXF1I/AAAAAAAAF7E/XdAaGa2yon8/s1600-h/image%25255B27%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-JGXTsdr92b8/UHg8p8oiw5I/AAAAAAAAF7M/YVqsantiWUM/image_thumb%25255B15%25255D.png?imgmax=800" width="338" height="382" /></a></p> <p>9. Create a domain user for client machine in Domain Controller</p> <p>To allow domain user as local admin, <a href="http://publib.boulder.ibm.com/infocenter/tivihelp/v4r1/index.jsp?topic=%2Fcom.ibm.tpc_V33.doc%2Ffqz0_t_granting_admin_privileges_domain_account.html" target="_blank">please visit this link</a></p> <p>10. Register client machine in domain controller which is defined in Step 6, then input user name/pwd defined in Step 9</p> <p><a href="http://lh4.ggpht.com/-Ei7OKXJAicY/UHg8rDg3apI/AAAAAAAAF7U/Sl0lqjJjgoE/s1600-h/image%25255B31%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-Fis-tD9rp6E/UHg8s-MgOOI/AAAAAAAAF7c/DMraSHFLpMM/image_thumb%25255B17%25255D.png?imgmax=800" width="302" height="360" /></a></p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-54237828240207307912012-10-09T08:34:00.001-07:002012-10-09T08:38:03.548-07:00Create SQL Server linked server to PostgreSQL database<p>1. Download ODBC Driver at this address <a title="http://www.postgresql.org/ftp/odbc/versions/msi/" href="http://www.postgresql.org/ftp/odbc/versions/msi/">http://www.postgresql.org/ftp/odbc/versions/msi/</a></p> <p>Choose the suitable version with your PostgreSQL database. I’m using PostgreSQL 9, so I chose latest version. (^_^)</p> <p><a href="http://lh5.ggpht.com/-OyRb7mPyZtQ/UHRDhGnAr0I/AAAAAAAAF3E/o-kAHyoP2nE/s1600-h/image%25255B3%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-5bG-XPDAjr4/UHRDkOoRtqI/AAAAAAAAF3M/AHaGrzFxnSs/image_thumb%25255B1%25255D.png?imgmax=800" width="437" height="466" /></a> </p> <p>2. Install ODBC Driver for PostgreSQL</p> <p><a href="http://lh4.ggpht.com/-RUeTIY8t_m0/UHRDlieFPZI/AAAAAAAAF3U/PIfQhh_wu5E/s1600-h/image%25255B7%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-eXmSyA-UBYU/UHRDne1CClI/AAAAAAAAF3c/s9LTmvCmJdY/image_thumb%25255B3%25255D.png?imgmax=800" width="401" height="50" /></a> </p> <p></p> <p><a href="http://lh5.ggpht.com/-amGmlIqpsAg/UHRDpMo8pAI/AAAAAAAAF3k/hgVk424_i4Y/s1600-h/image%25255B11%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-uS_xSWlOm8c/UHRDr7mrsaI/AAAAAAAAF3s/Vy54SppvHRI/image_thumb%25255B5%25255D.png?imgmax=800" width="416" height="327" /></a> </p> <p>3. Create connection to PostgreSQL database with ODBC Driver</p> <p><a href="http://lh6.ggpht.com/-VAKL-V6uEng/UHRDtUAMdaI/AAAAAAAAF30/fQ-ezGgZ_04/s1600-h/image%25255B19%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-IwgIVeHDRk0/UHRDvaZnUrI/AAAAAAAAF38/eI0L_H14M90/image_thumb%25255B9%25255D.png?imgmax=800" width="265" height="138" /></a> </p> <p><a href="http://lh4.ggpht.com/-8KJZNvFRwvQ/UHRDw4IIvJI/AAAAAAAAF4E/WrL5qVgKW4g/s1600-h/image%25255B15%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-AZJlt6ubIBc/UHRDzn3x3-I/AAAAAAAAF4M/GmC4O7U8bmA/image_thumb%25255B7%25255D.png?imgmax=800" width="404" height="336" /></a> </p> <p><a href="http://lh5.ggpht.com/-G3sq7POziM8/UHRD1gSObFI/AAAAAAAAF4U/EXWH8-CdMz8/s1600-h/image%25255B23%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-z3uU6qUsBI4/UHRD4Abe9TI/AAAAAAAAF4c/jy56_o2rHmM/image_thumb%25255B11%25255D.png?imgmax=800" width="409" height="304" /></a> </p> <p><a href="http://lh3.ggpht.com/-8rMQQ6_vHs0/UHRD6F8mgSI/AAAAAAAAF4k/4HPbbQznySQ/s1600-h/image%25255B27%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-v0grdAo5MFM/UHRD707zHPI/AAAAAAAAF4s/4caBLWp1IWg/image_thumb%25255B13%25255D.png?imgmax=800" width="410" height="234" /></a> </p> <p></p> <p></p> <p></p> <p>4. Now, it’s time to create linked server to PostgreSQL database:</p> <p>You can create with GUI</p> <p><a href="http://lh4.ggpht.com/-n5iJPvvKms0/UHRD-aAGtVI/AAAAAAAAF40/yOqyILmcNaU/s1600-h/image%25255B36%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-ahTK4pYMMgg/UHREAZDKiBI/AAAAAAAAF48/dqZK01TxaAQ/image_thumb%25255B18%25255D.png?imgmax=800" width="443" height="379" /></a> </p> <p>Or can create with scripts</p> <p>EXEC master.dbo.sp_addlinkedserver @server = N'OPENERP', @srvproduct=N'PostgreSQL', @provider=N'MSDASQL', @datasrc=N'OpenERP';</p> <p>GO <br />EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OPENERP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;</p> <p>GO</p> <p>Finally, you can query PostgreSQL database inside SQL Server</p> <p><a href="http://lh4.ggpht.com/-qPrfNsnysFw/UHREBwdocRI/AAAAAAAAF5E/fKSWIxcmrkw/s1600-h/image%25255B31%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-UnzWPqf5h2M/UHREEJF97WI/AAAAAAAAF5M/JFHxat8uL8A/image_thumb%25255B15%25255D.png?imgmax=800" width="367" height="411" /></a> </p> <p></p> <p>5. Verify the linked server</p> <p><a href="http://lh4.ggpht.com/-7A6YCJF80cQ/UHREy4PwngI/AAAAAAAAF5U/OZeR-1j5CRg/s1600-h/image%25255B40%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-R3Hj5CP9LtE/UHRE1w1sZRI/AAAAAAAAF5c/LkbV0-zzsgs/image_thumb%25255B20%25255D.png?imgmax=800" width="496" height="232" /></a></p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com1tag:blogger.com,1999:blog-1948920108711213708.post-69650655112058705892012-10-09T05:46:00.001-07:002012-10-09T07:31:46.954-07:00Query Excel spreadsheet on SQL Server 2008 64 bit with Linked Server<p>For SQL Server 64 bit, if you use Microsoft.Jet.OLEDB.4.0, you may experience this issue:</p> <p><em>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.</em></p> <p>To fix this, please follow the following:</p> <p>1. Download and install Microsoft Access Database Engine 2010 Redistributable at this address <a title="http://www.microsoft.com/en-us/download/details.aspx?id=13255" href="http://www.microsoft.com/en-us/download/details.aspx?id=13255">http://www.microsoft.com/en-us/download/details.aspx?id=13255</a> </p> <p><a href="http://lh3.ggpht.com/-0y1ElfQjIXI/UHQce3_XTOI/AAAAAAAAF2o/I8JCXOyA5wo/s1600-h/image%25255B3%25255D.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-Vy6wxzeXwpM/UHQchJu4mpI/AAAAAAAAF2w/dedd0es2vtM/image_thumb%25255B1%25255D.png?imgmax=800" width="531" height="228" /></a> </p> <p>2. After installing, create a linked server to your Excel file</p> <p>EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL_LINKED_SERVER_NAME' <br />                    , @srvproduct=N'Excel' <br />                    , @provider=N'Microsoft.ACE.OLEDB.12.0' <br />                    , @datasrc=N'C:\Your_Excel_File.xls' <br />                    , @provstr=N'Excel 12.0';</p> <p>GO</p> <p>EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL_LINKED_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;</p> <p>GO</p> <p>3. Run OPENQUERY to get data from your Excel file</p> <p>SELECT * FROM OPENQUERY(EXCEL_LINKED_SERVER_NAME,'SELECT * FROM [Sheet1$]')</p> タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0tag:blogger.com,1999:blog-1948920108711213708.post-35101078734415062642012-10-08T08:11:00.000-07:002012-10-08T08:11:02.928-07:00Get current queries executing on PostgreSQL<span style="color: blue;">SELECT</span> datname "Database Name"<br />
, usename "User Name"<br />
, procpid "Process ID"<br />
, client_addr "Client IP Address"<br />
, waiting "Is waiting"<br />
, query_start "Start Time"<br />
, current_query "Query Text"<br />
<span style="color: blue;">FROM</span> pg_stat_activity;タオ チューンhttp://www.blogger.com/profile/04936460665153762540noreply@blogger.com0