texts: Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

texts:
So we had a long running proc causing problems this morning (30 sec + run time).  We decided to check to see if parameter sniffing was to blame.  So, we rewrote the proc and set the incoming parameters to variables so as to defeat parameter sniffing.  A tried/true approach.  Bam, query time improved (less than 1 sec).  When looking at the query plan the improvements were found in an index the original wasn't using.

Just to verify that we didn't get a false positive we did a dbcc freeproccache on the original proc and reran to see if the improved results would be the same.  But, to our surprise the original proc still ran slow.  We tried again with a WITH RECOMPILE, still slow (we tried a recompile on the call to the proc and inside the proc it'self).  We even restarted the server (dev box obviously).

So, my question is this... how can parameter sniffing be to blame when we get the same slow query on an empty plan cache... there shouldn't be any parameters to snif???

Are we instead being affected by table stats not related to the plan cache.  And if so, why would setting the incoming parameters to variables help??

In further testing we also found that inserting the OPTION (OPTIMIZE FOR UNKNOWN) on the internals of the proc DID get the expected improved plan.

So, some of you folks smarter than I, can you give some clues as to whats going on behind the scenes to produce this type of result?

On another note, the slow plan also get's aborted early with reason GoodEnoughPlanFound while the fast plan has no early abort reason in the actual plan.

In summary

Creating variables out of incoming parameters (1 sec)
with recompile (30+ sec)
dbcc freeproccache (30+ sec)
OPTION (OPTIMIZE FOR UKNOWN) (1 sec)

UPDATE:

See slow execution plan here:  https://www.dropbox.com/s/cmx2lrsea8q8mr6/plan_slow.xml

See fast execution plan here:  https://www.dropbox.com/s/b28x6a01w7dxsed/plan_fast.xml

Note: table, schema, object names changed for security reasons.


texts: Do I need to rebuild indexes when upgrading a DB from 2008 to 2012?

texts:
I can't find anything on BOL that indicates I need to rebuild indexes or statistics after the migration and compatibility level upgrade of a database. But I have read a few articles/answers around the net that indicate this would be a wise move. Kins great answer here mentions statistics rebuilds but it is for 2000 to 2008

Is this something that was a recommended practice with earlier upgrades or is it something we should still be doing?


texts: mysql privilege to kill processes of other users

texts:
I have a percona mysql database.  The version is 5.5.29-29.4-log.

I have created a user called admin which should be able to kill processes initiated by other users, i.e worker.

GRANT SELECT, INSERT, UPDATE, DELETE, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON *.* TO 'admin'@'ip_' IDENTIFIED BY PASSWORD 'password';

It does had the process privilege, which I thought would do the trick. But, I can see this error

/* SQL Error (1095): You are not owner of thread 7480126 */

Would anyone be able to help?


texts: Mysql Fragmentation. How bad is too bad?

texts:
I ran a table status for my companies production site and its showing close to around '49085939712'  for just about every table in my database and we have around 400 tables.

I am not a DB administrator and my mysql skills are not the best when it comes to optimizing. To me it looks pretty bad. but I need everyone else's oppinion.

My question is how bad is too bad. What is an on average threshold to know when its time to optimize a single table.


texts: Is there a performance gain in specifying the table name in joins and using explicit cast?

texts:
Is there any performance gain by the interpreter of the query is I specify the table on a join and make the cast explicit?

Instead of

...
join table_join on field1_from = field1_join and field2_join = '2'
...

using

...
join table_join on table_from.field1_from = table_join.field1_join and table_join.field2_join::text = '2'::text
...

It seems that this should make the job of the parser a little easier


texts: Running a CTE query in a loop using PL/PGSQL

texts:
Possible Duplicate:
  Running a CTE query in a loop using PL/pgSQL

(Cross-post from https://stackoverflow.com/q/12625914/1555778)

I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude coordinates of grids, I pass the top left and bottom right latitude / longitude values into my CTE in order to display the amount of requests (hourly) made within those coordinates for given two timestamps-. However, I cannot display the results of my CTE and I get the following error message:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "inline_code_block" line 6 at SQL statement

What should I change here in order to make the entire query work as needed? My code is as below:

DO $$
<<outer_scope>> DECLARE
  coords RECORD;
BEGIN
    FOR coords IN
        SELECT topleftlat, topleftlon, bottomrightlat, bottomrightlon
        FROM coordinates
    LOOP
        WITH cal AS (
        SELECT generate_series('2011-02-02 00:00:00'::timestamp ,
                   '2012-04-01 05:00:00'::timestamp ,
                   '1 hour'::interval) AS stamp
    ),
    qqq AS (
      SELECT date_trunc('hour', calltime) AS stamp, count(*) AS zcount
      FROM mytable
      WHERE calltime >= '2011-02-13 11:55:11'
        AND calltime <= '2012-02-13 01:02:21'
        AND (calltime::time >= '11:55:11'
        OR calltime::time <= '01:02:21')
        AND lat BETWEEN coords.bottomrightlat AND coords.topleftlat
        AND lon BETWEEN coords.topleftlon AND coords.bottomrightlon
     GROUP BY date_trunc('hour', calltime)
    )
    SELECT cal.stamp, COALESCE (qqq.zcount, 0) AS zcount
    FROM cal
    LEFT JOIN qqq ON cal.stamp = qqq.stamp
    WHERE cal.stamp >= '2011-02-13 11:00:00'
      AND cal.stamp <= '2012-02-13 01:02:21'
      AND (
        extract ('hour' from cal.stamp) >= extract ('hour' from '2011-02-13 11:00:00'::timestamp) or
        extract ('hour' from cal.stamp) <= extract ('hour' from '2012-02-13 01:02:21'::timestamp)
      )
    ORDER BY stamp ASC;

    END LOOP;
END;
$$;


texts:
When I try to run the following command:

my_db> \copy \dt *. to /home/john/list.txt

I get an error:

\copy: parse error at "*"

Why? How can I save the output to disk?


texts: MySQL Workbench sync keeps requesting the same changes

texts:
I am using MySQL Workbench, and when I try to "synchronize" it with my remote database, it keeps detecting some changes to make.

Specifically, the most recurrent ones are:

Dropping foreign keys and re-creating the same ones again
Changing the collation

I was compliant and executed all the queries given to me (and added the semi-colon that they forgot). MySQL didn't complain and executed them.

However it didn't help, I can run it 20 times in a row, it will still ask the same useless changes.


texts: Batch update timestamp records to correct timezone

texts:
I'm looking to batch update some timestamp records in postgresql.

My app was storing timestamps to the timestamp column (without timezone) in the the wrong timezone (BST). I only noticed a while after DST kicked in (25th March) so not all the records are 'wrong'.

     timestamp
-------------------
2018-04-13 00:02:01
2018-04-11 20:22:32
2018-04-10 12:00:56
2018-04-10 11:25:56
2018-04-10 01:57:05
2018-04-07 03:38:32
2018-04-05 12:25:10
2018-04-05 09:09:49

I want to convert the range of records in BST to UTC. What's the best way to go about doing this?


texts: Two nullable columns one required to have value

texts:
No-Explanation Question:

Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have a value

Problem Description:

Let's say I have table called Expense

and have 2 dates :

prevision_expense_expiration_date DATE NULLABLE
expense_payment_date DATE NULLABLE

the logic of those 2 columns is the following:

I made a purchase of something and I know I have to pay for it, some date, like a phone bill. I will enter this as an expense with a expense_payment_date. This date is the supposed date I should pay but not the actual date of the payment, like the expiration date of the invoice.

In other situation I sell a gift card of some provider for it's service. I may have the expense of buying to my provider the service transfered to my client only if the client redeem the card. Hence the gift card has an expiration date, I want to do a prevision for that 'expense' without inserting as an expense for the time the gift card is valid, if the gift card expires, that 'expense' should not enter into the account system.

I know I can have 2 equally tables called prevision_expense and confirmed_expense but doesn't sounds right so I have in the same table, 2 dates, nullable, but I want to constrain or something so that one is always required.

There's another posible strategy:

payment_date DATE NOT NULL
is_prevision_date BOOL NOT NULL

So, in this case, if the date is prevision bool value would be 1, otherwise will be 0. No null values, all is good. except that I want the option to store BOTH values when first I have a prevision date and THEN (lets says two days later) have a confirmed date for that expense, in which case with strategy 2 i won't have that option.

Am I doing everything wrong in the database design ? :D


texts:
I am Transferring data from sql server source db to sql server destination db (cross db).
but some times getting error "ssis ado.net destination has failed to acquire the connection.The connection may have been corrupted"


texts: How can I connect to an Oracle Autonomous Data Warehouse with a 3rd party IDE (DataGrip)?

texts:
I'm trying to connect to a Oracle Autonomous Data Warehouse database with Jetbrains DataGrip. Oracle provides me with a wallet file (a zip), with contains tnsnames.ora, a keystore, ojdbc.properties, and some other files.

I'm having a lot of trouble using this information to connect to the database using DataGrip. I found a thread on the DataGrip support forums, but I'm not having any luck with that either.

Jetbrains support thread: https://intellij-support.jetbrains.com/hc/en-us/community/posts/360001792539-Connect-with-Oracle-Cloud
Relevant Oracle documentation: https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/adwud/connect-using-client-application.html

What I did:
1. Created the 'TNS_ADMIN' environment variable and set it to:
C:\\Users\\xxx\\Documents\\[folder with wallet files]
2. Added the Oracle JDBC driver files (ojdbc8.jar, osdt_cert.jar, oraclepki.jar, osdt_core.jar) to the standard Oracle driver in DataGrip
3. edited the 'sqlnet.ora' file to include the path to the wallet files
4. Added the following to the Data Source VM Options:

-Doracle.net.tns_admin=C:\\Users\\xxx\\Documents\\[folder with wallet files]
-Djavax.net.ssl.trustStore=truststore.jks
-Djavax.net.ssl.trustStorePassword=[password]
-Djavax.net.ssl.keyStore=keystore.jks
-Djavax.net.ssl.keyStorePassword=[password]
-Doracle.net.ssl_server_dn_match=true
-Doracle.net.ssl_version=1.2

Set connection type to URL only
Tried different connection strings in the URL field:

jdbc:oracle:thin:@//adb.eu-frankfurt-1.oraclecloud.com:1522/xxxxxx_adw1_high.adwc.oraclecloud.com?TNS_ADMIN=C:\\Users\\xxx\\Documents\\[folder with wallet files]

jdbc:oracle:thin:@xxxxxx_adw1_high.adwc.oraclecloud.com?TNS_ADMIN=C:\\Users\\xxx\\Documents\\[folder with wallet files]

jdbc:oracle:thin:@//adb.eu-frankfurt-1.oraclecloud.com:1522/mnr6yzqr22jgywm_adw1_high.adwc.oraclecloud.com

Result:

Connection to ADW1 failed.
[08006][17002] IO Error: Got minus one from a read call, connect lapse 32 ms.,
Authentication lapse 0 ms.

I have also tried using the 'Service name' and 'TNS' connection types and filled in the info from tnsnames.ora. No dice, same error.

Also tried explicitely setting the 'tcp.validnode_checking' parameter to null.

(The connection works fine with sqldeveloper)

What's the proper way to do this?


texts: Parsing COPY's binary format to access a tsrange


texts: Stored procedure deployment - push or pull into database?

texts:
Using stored procedures as part of an ETL process to populate tables in another database, which method would be considered a best practice (if any) in terms of deployment, maintenance, visibility and any other reasons for adopting a push or pull approach.

For example:
Push approach, the stored procedure would be in the Staging database and would push data into a table in the destination database.

Pull approach, the stored procedure would be in the destination database and would pull data from the Staging database.

Both approaches would involve cross-database queries and neither option appears to be perfect, though I prefer to keep all my ETL stored procedures and supporting objects in the Staging database away from others.

What approaches are people generally implementing?


texts:
I was told I can use exp to export a certain USER, then DROP the user and with the imp command I can recreate that USER with all of its tables.

Is it true?
How do I do it?

I already have an ADMIN user with just about all privileges (there are 204 of them if it matters).

I tried various options of exporting the USER with the ADMIN account or with the USER's account, but no matter what I do the imp command fails if I try to run it when the USER has been dropped.

Example command:

-- From CLI:
exp USERID=MYUSER/PASS@INSTANCE FILE=mydump.dmp FULL=y
-- From SQLPLUS with admin user
DROP USER MYUSER CASCADE;
-- From CLI:
imp ADMIN/PASS@INSTANCE FILE=mydump.dmp FULL=y
-- The above command will put the contents of MYUSER into the ADMIN account ...
imp ADMIN/PASS@INSTANCE FILE=mydump.dmp FROMUSER=MYUSER TOUSER=MYUSER
-- The above command fails with  the below message:

. importing MYUSER's objects into MYUSER
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
Import terminated successfully with warnings.


texts: Behavior of Select expressions containing select

texts:
Consider the following setup:

CREATE TABLE NAMES(Id integer PRIMARY KEY, Name text);

INSERT INTO NAMES VALUES(1,'Tom');
INSERT INTO NAMES VALUES(2,'Lucy');
INSERT INTO NAMES VALUES(3,'Frank');

CREATE TABLE fields(fid integer, fname text);
INSERT into fields values(1, "Id");
insert into fields values(2, "Name");

SELECT name, (SELECT "Name") FROM NAMES;

The last statement will output

Tom|Tom
Lucy|Lucy
Frank|Frank

However, although select fname from fields where fid=2 yields ("Name") (as a single-cell table), replacing the last statement in the above query by

SELECT name, (select fname from fields where fid=2) FROM NAMES;

gives

Tom|Name
Lucy|Name
Frank|Name

Why? Is this intended?

Note: This has been tested using SQLite v3.20


texts: Mysql binlogs exists, show binary logs is empty

texts:
I am working on a replication setup and have turned on bin-logs. I have moved my mysql datadir to a new location as well as the binlog path

From my.cnf

datadir = /vol/data/mysql
log-bin=/vol/data/mysql/mysql-bin
binlog_format=mixed

When I start mysqld and check the status:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000315 |      106 |              |                  |
+------------------+----------+--------------+------------------+

When I check the contents of /vol/data/mysql, I can see a lot of bin logs, but specifically, I can see this file was just created:

mysql-bin.000313
mysql-bin.000314
mysql-bin.000315
mysql-bin.index

If I tail the index file:

$ sudo tail mysql-bin.index
/vol/data/mysql/mysql-bin.000313
/vol/data/mysql/mysql-bin.000314
/vol/data/mysql/mysql-bin.000315

In mysql, there are no binary logs at all.

mysql> SHOW BINARY LOGS;
Empty set (0.00 sec)

This means that purging binary logs in mysql has no affect. I checked the permissions of the directory and files and all belong to mysql user and group. Why can't mysql see and/or purge those binary logs?

mysql> PURGE BINARY LOGS BEFORE NOW();
ERROR 1373 (HY000): Target log not found in binlog index


texts:
Currently writing a query which will be used to see where abouts on our production line a certain item is by showing what the last completed 'Resource' was. (resources on our system are just different areas of the assembly line eg, metal fabrication, spray shop, electrical wiring, testing etc.)

I've written a query that gives me ALL completed resources on unfinished jobs, but as each job has several completed resources on it the table that gets output still needs refining.

SELECT
T0."DocNum",
T2."U_CustomerRef",
T1."DocNum",
T6."ItemCode",
CONCAT(T5."firstName", T5."lastName"),
T4."U_Time",
T4."U_Date",
T4."U_StopTime",
T4."U_StopDate"
FROM
ORDR T0  INNER JOIN RDR1 T2 ON T0."DocEntry" = T2."DocEntry"
LEFT OUTER JOIN "NL_LIVE"."OHEM" "OHEM" ON T0."OwnerCode"="OHEM"."empID"
LEFT OUTER JOIN OWOR T1 ON T1."OriginAbs" = T0."DocEntry" AND T2."ItemCode" =T1."ItemCode"
INNER JOIN WOR1 T6 ON T1."DocEntry" = T6."DocEntry"
INNER JOIN OITM T3 ON T2."ItemCode" = T3."ItemCode"
LEFT OUTER JOIN "NL_LIVE"."@OCHLABBOOK" T4 ON T4."U_ProdOrderNo" = T1."DocNum" AND T4."U_ProdOrderLine"=T6."LineNum"
LEFT OUTER JOIN "NL_LIVE"."OHEM" T5 ON T4."U_StartEmp"=T5."U_UniqueID"
WHERE
T0."DocStatus"='O'
AND T6."ItemType"=290
AND T4."U_Action"<>'Started' AND T4."U_Action"<>'Stopped' AND T4."U_Action"<>'Paused'

I've tried doing SELECT MAX on the results using the date and time, but that leaves me with only one result in the table as there aren't any groupings for each area it just gives me the latest results for the whole table not each individual job/Document. In this image you can see I've drawn red borders showing that certain parts should be grouped, essentially what i need is a way of selecting the most recent date from each smaller group of data within the Query. Each subset needs to be grouped by Document number (column 3 document number not column 1)

Large shaded red area is just to cover employee names and isn't relevant to the question

Thanks in advance for any help given. If more information is required I can  go into more detail of table structures etc.


texts:
When loading data into a table, I get the following error:

ERROR:  row is too big: size 8680, maximum size 8160

The table has 1000+ columns in it, which appears to be the problem.  The general internet advice is "refactor!" or "normalize!".  For instance, this post.  Unfortunately, I don't believe such advice applies to my situation.

The table is to store data collected from a device.  The device produces a PNG image as part of an analysis.  The PNG consists of 1024 pixels.  Each pixel has an associated numeric value.  Along with the pixel data are various other fields related to the analysis.  Breaking the table into parts doesn't really make sense.  The fields are all logically associated with the particular object being analyzed.

Postgres doesn't seem to like that each pixel has its own field.  The table has fields of the form: pixel_1, pixel_2, ..., pixel_1024.  Note that this is fundamentally different from the usual example of phone_number_1, phone_number_2, etc.  Each pixel is a unique object by virtue of its location.  pixel_1 has a different position than pixel_123 and each pixel has an associated value.  The common aspect between them is that they both are used to describe the same analysis object.  They are the quantitative analog to the visual representation given in the PNG.

Is there a way to increase the row size?
If the table simply cannot have 1000+ columns, how could I refactor this?
Assuming the first two answers are "No.", should I just stick the 1024 columns into an XML and throw that in a text field?

I hope I have made the context clear. I have tried to boil the problem down to its essence, but I suspect some clarification may be needed.  Please let me know if clarification is needed.

EDIT: As an experiment, I tried breaking the pixels into a separate table.  That seems to be the only possible way to refactor.  But the 1024 columns produces the same error.


texts: Temp DB is growing continuously in SQL server

texts:
I have used temp tables and drop the same in the every stored procedure in my database. When I try to check my tempdb data using Select * from tempdb.sys.tables, I am getting many tables with names like #A002FF4B,#A08A53F2...etc (name with some hex characters).

These tables are continuously getting added in my tempdb and it is getting full. So sometimes my application stops due to it is not able to access SQL server once tempdb is full.

How can I resolve this issue?


texts: How to anonymize pg_dump output before it leaves server?

texts:
For development purposes, we dump the production database to local. It's fine because the DB is small enough. The company's growing and we want to reduce risks. To that end, we'd like to anonymize the data before it leaves the database server.

One solution we thought of would be to run statements prior to pg_dump, but within the same transaction, something like this:

BEGIN;
UPDATE users
SET email = 'dev+' || id || '@example.com'
  , password_hash = '/* hash of "password" */'
  , ...;
-- launch pg_dump as usual, ensuring a ROLLBACK at the end
-- pg_dump must run with the *same* connection, obviously

-- if not already done by pg_dump
ROLLBACK;

Is there a ready-made solution for this? Our DB is hosted on Heroku, and we don't have 100% flexibility in how we dump.

I searched for postgresql anonymize data dump before download and variations, but I didn't see anything highly relevant.


texts: How to change the schema of stored procedure without recreating it

texts:
I have some Stored procedure with "SalesTraining" schema.
I want to change all SalesTraining schemas to "Sales".
Is there any better way than recreating the stored procedures?
thnx


texts: Oracle driver and language specific chars

texts:
I am developing an Excel / VBA - Application that connects to an Oracle database (v 11.2.0.4) instance. I need to query a customers table and search by name. Customers names will contain german umlaut chars (äöüÄÖÜ and ß). I issue sql queries like "... where c.custname = 'Müller'".

The behaviour I am observing is that on my dev system all works well as well as on several users' systems. On few other systems, the application fails with "no customers found", my own error message if the name lookup mentioned before fails (which should not happen as it works at another system).

Further investigation using the PL/SQL Developer tool revealed that on the affected systems the names for customers are returned without umlaut. "Müller" becomes "Muller". However, querying for "Muller" doesn't return anything - so "Müller" is really converted to "Mu[unprintable char]ller" - I guess.

I am quite familiar with Microsoft SQL Server, Versions, Languages, Drivers... but for Oracle I have no experience at all. Can someone advice where / how to check what driver version is installed on a system, how it is configured (NLS-settings?) and how to make sure all systems are set up the same, in this aspect?

Is there a way to totally clean a system of all oracle related drivers, files and registry settings to get a clear start and have only one, the most recent driver, available?

Further information (to make things worse):

My VBA-macro uses a base class which is in use "forever" in my company. It connects through OLEDB and builds it's connection string using "Provider=msdaora.1" meaning it is using the Microsoft provided Oracle driver intended for Oracle around version 7 (?).
When using PL/SQL Developer to test, it takes a "real" oracle driver installed. For my system, there is a choice from "10.2.0/client_1", a "12.2.0/client" and "12.2.0/client_2"

I am going to try and compare my Microsoft Oracle driver to the non-working systems, just to see what it is.

I will try to upgrade the base class to stop using the obsolete Microsoft driver and use the recent oracle driver. However, manual queries using PL/SQL Developer showed the same basic problem... therefore I am not sure I will solve the issues.

I'd appreciate any idea where to look, what to remove or install or any other idea!


texts: Execute SSIS (package) in SSMS using T-SQL and return a value

texts:
Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I successfully achieved this using Script Task and Dts.Variables and F5/Run to see successful results. What I want to achieve now is to return (like an out parameter) an Id returned to me by the Web API. I want to have this Id returned in the SSMS Results window when I execute the SSIS script in SSMS.
Here is what I have done:

VS > Open dtsx file in Designer and in the "Control Flow" tab, right clicked to add variable:

Right clicked the Script Task I had created and added the variable created above in the ReadWriteVariables section:

Using "Edit Script" to code the Web API call and return a value and set it as follows:

//MessageBox.Show(id);
Dts.Variables["User::Id"].Value = Convert.ToInt32(id);

The MessageBox was just to test if the id is returned by the Web API and I get an alert of the "id" when I Run/F5 the SSIS project in Visual Studio. The second line is where I have set the Variable.

I execute the SSIS in using T-SQL in SSMS from an example here:

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'ssisWAP.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIFolder', @project_name=N'SampleProject', @use32bitruntime=False, @reference_id=Null

Select @execution_id

DECLARE @var0 sql_variant = N'username'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Username', @parameter_value=@var0

DECLARE @var1 sql_variant = N'password'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Password', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id

The above executes properly, calling the API and an out @execution_id is shown in Result window. But no sign of the variables User::Id I created earlier. Is it possible to show that value in the Result window? If not, is there a way to see that the data in that variable in SSMS after SSIS execution (even from designer)?
I already had a look at this Stack Overflow Q & A where the solution is to use "Execute SQL Task" and "pass" the variable into a procedure. This I guess is one way to do it but I want to just get the Id in the return when I execute the script as shown above.


texts:
I have set up a MySQL database. My problem now is I can't add new users with the user I have created.

I try to grant access but I can't still add new users to my system. I use Workbench to add new users, so here is my screenshot:

Now I need to get access to my user account, but I can't remember how I can do this from MySQL command line.


texts: InfluxDB - Tag vs Field for simple, time series data

texts:
We are new to InfluxDB, and are struggling to understand the query performance difference between a "tag" and a "field" for storing simple, time-stamped measurement values (literally, time series data).  I have to imagine this is one of the most common applications for Influx, yet still I'm not clear what is the smartest method.
We have previously logged data to MySQL and similar databases, using a structure that had columns of name/timestamp/value/unit.  Each measurement became one row in that table.  Obviously, this has some performance drawbacks, so we are looking for a better way.
A new InfluxDB will be installed on a project.  We have some number of sensors on the project, each of which has a unique identifier (i.e. "TT-001" might be Temperature Sensor #1) and produces a single measured value (i.e. 104.6 degF) with a timestamp.  These measurements are taken at regular intervals (i.e. every second).  There are perhaps 500 individual measurements, all of which will be stored for periods of perhaps 10 years.
Summarizing the question:  described as above, will logging data to InfluxDB as follows will result in the fastest-loading queries?

Measurement = "Project_Name"

Field Key => Measurement Name (i.e. "TT-001")
Field Value => Measured Value (i.e. 104.6)

The queries are almost exclusively "select (tags) from [start date to end date]".  Literally, 99% of these queries will be graphs of data over time using Grafana.
For this application, where does the "tag" fit in, or is it not useful?
Before anybody asks:  I have read the documentation, and continued looking into examples, forums, blogs, etc.  I have yet to find a concise, clear answer as to the highest-performant method for storing measurements as described above, where I do not believe that "metadata" is particularly useful for our application.
Really appreciate some input on this...the StackExchange community has always been able to help when I hit these kinds of road blocks!


texts: getting MySQL error 1064 but very (absurdly) simple query in MySQLWorkbench

texts:
I'm getting a MySQL 1064 error for the simplest query when run with \G at the end. This works:

select * from locations;

and this doesn't:

select * from locations\G

hmmm... is there any way to see what the proper vertical representation is?


texts: Does a whole-server backup guarantee a reliable backup of running databases?

texts:
Many hosts offer a full-server backup once per day. However, I've been told that a backup of this kind doesn't guarantee a reliable backup of running databases, because even if the database uses transactions and is ACID compliant, what can possibly happen is:

The backup procedure starts reading the database file, but when it's eg. half way through..
A transaction to the database is made that changes something both at the beginning of the database file (which has already been read by the backup), and at the end (which is yet to be read).
The backup procedure continues reading the file, and therefore gets the beginning of the file before the transaction, and the end of the file after the transaction - which spells trouble.

I've been told the only way to reliably backup a database is to use the database's in-built backup procedure. Those backups can be stored on the server, and they in turn can be backed up but the whole-server backup.

Is this all correct?

The reason I ask is the host is offering whole-server backups only, and I need to know if I this is sufficient, or if I need to sort out something separate for the database. The database is MySQL (InnoDB) - though I'm interested if the logic applies to all databases.


texts: Datetime Clustered Index Keeps getting fragmented

texts:
I have a large table (4 million Records) named CDR, which is used for storing CDRs (Call Detail Record)s from a Cisco router in a VoIP system, the table is constantly growing and will never have a record inserted out of sequence. Nor do we update or delete rows.

Since most of our queries are based on the time a call was initiated, I created my main clustered index on this column (DATETIME data-type). However, after a brief time the index becomes fragmented and after just one week it has over 70 percent of fragmentation so we need to rebuild it. I don't understand why this keeps happening since we insert sequentially, and don't update or delete rows.

Any suggestions to reduce or remove the fragmentation will be much appreciated.

I'm using a SQL Server 2014 Enterprise version on an old Xenon server with Windows Server 2012.


texts:
we run  Microsoft SQL Server 2008 R2 on production server.
We need create new tables and load a big amount of data on them (roughly 1 TB!)

I am wondering if it is possible to insert this dataset on a second test database server to avoid overloading the production server.

Once completed i would like to move the newly create tables in the production db in a "clever" way, avoiding to perform again the inserts.

What do you suggest?
For instance, Is it possible to load the new dataset on separate filegroup and then move the files to the production db?

Thanks


texts: Sampled rows is very less for auto update stats in SQL Server 2016

texts:
I have SQL Server 2016. One of the table is having 9.24 million records. One of the stats (no filter on stats) on it is getting auto updated by SQL Server with sampling rate of 1 % (120023 rows).
As per the formula mentioned in below URL, sampling % should be around 5 % but that is not the case. I want to know why? Am I missing something? Or there is no standard formula for it?
https://matthewmcgiffen.com/2017/08/15/automatic-sample-sizes-for-statistics-updates/
Also just wanted to verify the threshold (modification counter) for updating the stats in SQL Server 2016 is (mentioned below) or something else will cause the delay / premature update of stats (I know REBUILDING INDEXES will update stats indeed)?
Threshold = √((1000)*Current table cardinality)
https://www.sqlshack.com/sql-server-statistics-and-how-to-perform-update-statistics-in-sql/


texts: Single-column junction table - does it make sense?

texts:
I have inherited a database schema, which looks similar to the one below:

CREATE TABLE Products (
    ProductID int not null PRIMARY KEY,
    StoreGroupID int not null,
    -- product properties...
)

CREATE TABLE Stores (
    StoreID int not null PRIMARY KEY,
    StoreGroupID int not null,
    -- store properties...
)

The idea is that there's a 1-* correspondence between a product and a group of stores (a product is always carried by a single group of stores, and a group of stores can carry multiple products).

However, the current database does not define any sort of "group of stores" entity - the StoreGroupID is instead assigned by the business logic code from a sequence, completely arbitrarily and with no foreign key constraint.

Does it make sense to create a StoreGroup table, even if the only column it could carry would be the StoreGroupID? Or is there another way to model such a relationship?


texts: Finding and replacing non-specific text to lowercase variant

texts:
I've been cracking my brain on a problem that I had at work. In one of my tables is a bunch of rows, with a certain column that contains XML data that has several properties like this: (simplified)

<file data_first="1" data_second="2">

Now the problem is that some of the properties in columns are not in lowercase, causing some problems in the software:

<file Data_First="1" Data_Second="2">
<file dATa_THird="3" DATA_FoURTH="4">

Simply put, I want to do the following for all columns:

Iterate through these rows
Find strings that match "%data_%=" in any case
Replace that part of the column with it's lowercase counterpart so the above XML's properties become:

data_first="1" data_second="2"

data_third="3" data_fourth="4"

I tried a bunch of things, but ultimately didn't get too far. Hopefully this is in the right direction:

UPDATE tablename
SET xmlcolumnname = REPLACE(xmlcolumnname, SUBSTR('', ), BINARY LOWER(SUBSTR()));

I added the BINARY to make sure it's inserted with case sensitivity intact.

Thank you very much for your time.


texts: Error AWR Operation failed: CATPROC not valid when creating database with dbca

texts:
I have a fresh installation of Oracle 11g enterprise edition on CentOS 6, both 64 bits. I have installed Oracle and so far everything looks fine. I have already added an started a listener. The problem I have now is when I try to create a test db using dbca. Everything goes well until the post creation DB part which popups an error. The error shows the same text as the /opt/app/oracle/cfgtoollogs/dbca/testingDB/postDBCreation.log:

   utl_recomp_begin: 09:34:38
   utl_recomp_end: 09:34:42
   BEGIN dbms_swrf_internal.cleanup_database(cleanup_local => FALSE); END;

  *
  ERROR at line 1:
  ORA-13516: AWR Operation failed: CATPROC not valid
  ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 206
  ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 239
  ORA-06512: at line 1

The creation of the DB cannot continue and it stops here...

I have searched everywhere in internet but not luck so far. I have no clues what it can be. Any suggestions?

Thanks in advance,


texts: How many stored procedure parameters is too many?

texts:
I've just started writing a stored procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too many parameters?

For context... this procedure will essentially INSERT a single row into a single table. There would also be a very similar; albeit somewhat smaller; version that performs an UPDATE on the same table. Most columns are relatively small with a mix of int and strings (varchar(200)).

What are the issues; good or bad; to having a procedure with a large number of parameters and what is the threshold where I should start considering other patterns?


texts: SQL Server 2008 R2: query RECENT locks, NOT current locks

texts:
The database had average lock wait time of 30 seconds for a period of about a minute.
I didn't run my lock query in time to see what was happening.
I can't seem to find anything that shows recent locks, just current locks.


texts: How do you figure out what planner cost constants to use in Postgres?

texts:
I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I am not an expert at all in this stuff.

I've noticed that the manual mentions a few times that a good way to improve performance is to use better cost constants for the query planner, but it also says that there is no simple way to figure out what cost constants to use.

I think that the constants might be problematic right now, since estimated costs don't seem to be a steady multiple of actual execution time - it varies from 30x to about 600x, even after running VACUUM ANALYZE recently. (I don't know  whether this is a valid way to check try to see if the constants are set well, correct me if I'm wrong on that)

So, I'd like to set better constants for the query planner. How do I go about doing this? Should I just tweak things up and down at random until it seems faster, or is there anything I should do that's more formal? Are there any guidelines based on hardware or anything?

By the way, if the answer is "Don't worry about it, other things will be way more important to improve first," that's fine - for my actual real-world case, I'll work on other stuff. But for the sake of others, it would still be good to know how one would improve the constants if everything else has been improved.


texts: query on 2 fields but 1 is a unique value

texts:
postgresql 9.6, windows 7

Here is an example of a database :

fld1 fld2
abc  D123
xyz  D123
abc  D789

how can I select rows with fld1='abc' if/only for fld2 value exists just one time in the database ?
In my example, I only want to select the third row.


texts: Sql Server 2008r2 Replication Snapshot Agent Hang

texts:
I am setting up a new merge publication, but I can not get the snapshot agent to run successfully.  In fact, I can not run any snapshot agents for any of the other publications on this server now.  This is effecting transactional and merge publications

When running a snapshot, I get the "Starting agent." status returned and then the occasional "agent has not logged a message in 10 minutes" messages.

I've also tried generating a snapshot from the command line:

I never get any errors nor is the c:\repllog.txt log file ever created.  It feels like something mysterious is blocking the snapshot executable pretty much on start up.  Any ideas?


texts: Updating a Table Based on Latest Record From Other Table

texts:
I have two tables as follows:

Table 1:

ID    Employee_ID     Date
1          1         1-1-2017
2          1         3-6-2017
3          2         4-5-2017
4          2         6-6-2017
5          3         5-8-2017

Table 2:

ID      Employee_ID    tbl1_ID
1           1           Null
2           2           Null
3           3           Null

No I want to get the ID of each employees latest record from Table 1 and set it in Table 2's tbl1_ID for those that tbl1_ID is null.

I wrote the below query for that but it takes a lot of time. Additionally in the first table I have 10 Million Records and the second one has 200K records.
Good to mention that PostgreSQL is tuned and the server holds good specs.

update table_2 tbl2
   set tbl1_id = (select id
                  from table_1 tbl1
                  where tbl1.employee_id = tbl2.employee_id
                  order by tbl1.date desc limit 1)
where employee_id is not null
and tbl1_id is null;


texts: Is there any way to optimize my "random data storage" table?

texts:
I have a table like this for "general storage" of less-important random things here and there, which don't require their own dedicated table and which would be a lot of work to code as well as a lot of storage to store when I only need "the latest values", etc.:
CREATE TABLE "general data"
(
    id                  serial,
    "timestamp"         timestamp with time zone NOT NULL DEFAULT now(),
    key                 text NOT NULL,
    value               text,
    "last requested"    timestamp with time zone,
    PRIMARY KEY         (id),
    UNIQUE              (key)
)

I constantly do queries such as:
SELECT value FROM "general data" WHERE key = $1;

Or:
SELECT "last requested" FROM "general data" WHERE key = $1;

Or:
UPDATE "general data" SET value = $2 WHERE key = $1;

Or:
UPDATE "general data" SET "last requested" = now() WHERE key = $1;

Or:
INSERT INTO "general data" (key, value) VALUES ($1, $2);

The text "key" column can be something like: "Twitchbot_Harrybot has spoken in channel id #24223442", etc. In other words, arbitrary, long strings. Which of course PG has to somehow look up every time.
Does actually making that text column an index have any purpose in this context? I don't see how, but maybe it's the case?


texts: Plan Cache Pages by Database in SQL Server 2008R2

texts:
Is it possible to get a break down of how many plan cache pages are being used per database? Perfmon seems to only have the total for the server, but maybe there is a DMV with more detail?

Also, does the buffer pool (sys.dm_os_buffer_descriptors) include the plan cache or is that separate?


texts: How to filter hstore where values are not null in a trigger?

texts:
The easiest way to explain the question is in the usage of an audit trigger.

However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*). What is the simplest / fastest way to do this?

Source: https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql#L134

audit_row.row_data = hstore(NEW.*) - excluded_cols;

This obviously doesn't work but explains, hopefully, what the answer will look like.

audit_row.row_data = hstore(hstore(NEW.*) FILTER (WHERE value IS NOT NULL)) - excluded_cols;

Preference will go to answer that doesn't use an user created function, e.g. use only Postgres functions / operators.

This is for >= PostgreSQL 11.

I am looking for a solution with hstore, not json or jsonb. Alternative ways with json or jsonb may be included, but hstore is preferred.


texts: DB2 Alter column type and index state RBDP vs. "needs a rebuild"

texts:
I'm new to DB2 and databases overall.

I'm building a script to alter several column data types in several tables of which some are part of indexes and some not. So after ALTER-statements I assumed I have to rebuild the indexes. According this source:
"When the data type of the ACCTID column is altered from DECIMAL(4,0) to INTEGER, the IX1 index is placed in a REBUILD-pending (RBDP) state."

My  questions:
1. If index is in RBDP state, does it mean it has to be rebuild and how should I return the index to accessible state?
2. How can I find the indexes which are now in RBDP state? I tried this after a altering a column type:

SELECT tabname, indname, index_requires_rebuild
FROM TABLE(sysproc.admin_get_index_info('I','','')) AS t
WHERE index_requires_rebuild = 'Y';

but I still can't find any indexes requiring a rebuild, so I guess RBDP state and "requires rebuild" are different animals. So what is the difference and how can I be certain the index is still working as it should be after my ALTER statements?

The statements I use are in this form:

ALTER TABLE TABLE1 ALTER COLUMN COLUMN1 SET DATA TYPE BIGINT
GO
CALL SYSPROC.ADMIN_CMD('REORG TABLE TABLE1')


texts: Can I Avoid Joining The Same Table 3 Times?

texts:
I have this database schema on a Oracle 12c database :

I'm trying to answer this question :

What is the game in which Dallas Mavericks had the biggest percentage of successful 3-point shots ?

I've managed to answer this question with this query:

SELECT HOME_TEAMS.TEAM_NAME                    "HOME TEAM",
       AWAY_TEAMS.TEAM_NAME                    "AWAY TEAM",
       GAMES.GAME_DATE                         "DATE",
       TEAMS.TEAM_ID                           "GENERIC ID",
       HOME_TEAMS.TEAM_ID                      "HOME_ID",
       AWAY_TEAMS.TEAM_ID                      "AWAY_ID",
       GAME_STATISTICS.X3POINTSHOTS_PERCENTAGE "3 POINTS PERCENTAGE"
FROM GAMES_TEAMS_STATISTICS
         INNER JOIN GAMES ON GAMES_TEAMS_STATISTICS.GAME_ID = GAMES.GAME_ID
         INNER JOIN TEAMS ON GAMES_TEAMS_STATISTICS.TEAM_ID = TEAMS.TEAM_ID
         INNER JOIN TEAMS HOME_TEAMS ON GAMES.HOME_TEAM = HOME_TEAMS.TEAM_ID
         INNER JOIN TEAMS AWAY_TEAMS ON GAMES.AWAY_TEAM = AWAY_TEAMS.TEAM_ID
         INNER JOIN GAME_STATISTICS ON GAMES_TEAMS_STATISTICS.STATS_ID = GAME_STATISTICS.STATS_ID
WHERE TEAMS.TEAM_ACRONYM = 'DAL'
ORDER BY GAME_STATISTICS.X3POINTSHOTS_PERCENTAGE DESC
    FETCH FIRST 1 ROWS WITH TIES;

However, to answer this question , I join the teams table 3 times. Is there any way I can write this query in a more efficient way , avoiding so many joins ?


texts: How to ensure each user only answers a question once on the db side?

texts:
I am designing a database for a custom software application. The part I am focused on now is the ability to add custom questions to ask users.

I want to ensure that each user can only answer a question once. In this case, a user is a 'Provider'. How do I ensure each user only answers a question once?


texts: SQL query to return columns per value in value range?


texts: I have been asked this question in an interview that how much would be the DB size for a standalone SQL server if 650MB of data is processed every month. I couldn't give any answer to this question in my interview and later on didn't find any answer either. Any help would be highly appreciated.


texts:
InnoDB offers table compression for Barracuda-type tables and the recommendation seems to be to "adjust" buffer_pool_size. The documentation is rather vague about the definite impact of compression on memory usage. The article on compression internals is just stating:

  a configuration with more memory dedicated to the InnoDB buffer pool tends to run better when using compressed tables than a configuration where memory is highly constrained.

Which translates to "more is better than less" for me - pretty useless in most scenarios.

So in a non-CPU-bound workload where the buffer pool size is significantly smaller than the database size but "meets the needs" (i.e. has an acceptable hit rate), will enabling compression on tables without changing the buffer pool size hurt or benefit the buffer pool hit rate?

The aforementioned compression internals article is elaborating that both, the compressed as well as the uncompressed versions of a page need to be present in memory for the page to be available to the engine, thus potentially increasing memory usage. On the other hand, the eviction algorithm seems to first evict uncompressed pages, leaving the compressed versions in the pool and offering the advantage that more pages might fit into the buffer pool if the system would be able to keep up with decompressing and re-evicting pages.


texts: SQL Server 2008 Cluster Across Two VMWARE Hosts

texts:
Vmware ESX 5 from a Vcenter

Two Windows 2008 R2 VMs

I am attempting to setup Failover Clustering using two VMs from within a Vcenter. Each virtual machine resides on a seperate physical host following this schema:

The instructions for adding a disk to the first machine go as follows:

In the vSphere Client inventory, select the newly created virtual machine and select Edit Settings. The Virtual Machine Properties dialog box appears.
Click Add, select Hard Disk, and click Next.
Select Raw Device Mappings and click Next.
Select an unformatted LUN and click Next.
Select a datastore and click Next.

This datastore must be on a SAN because you need a single shared RDM file for each shared LUN on the SAN.

My question is as follows. When two Windows 2008R2 servers are hosted on two different physical VMWare servers within a VCenter, is it absolutely necessary to have an unformatted LUN for the shared storage of a Windows Failover Cluster?

If not, what would be an alternative?

iSCSI is not an option.
Both servers must be installed on two seperate physical hosts.

Here is a link to the documentation.

http://www.vmware.com/pdf/vsphere4/r41/vsp_41_mscs.pdf

Goto page 21.


texts: Can I turn auto-shrink off while on production server with people connected?

texts: I'm going to turn auto-shrink off on our SQL Server 2008, but I was wondering if I need to wait until the end of the day when no one is on the server? Or, can I just make the change now? Thanks!


texts:
I have a multi-tenant app in PostgreSQL that has a repeated tenant_id column in each table to help isolate the tenants from each other.

My question is, is there any sort of way that I can use database constraint to make sure the tenant_ids are consistent with their parents.

For example, imagine a table blog_posts that has many comments. I would like to ensure that for any comment, the tenant_id is the same as the tenant_id for the given blog_post_id.

Can this be done in a simple and maintainable way?


texts: Setting constraint so that new value in “Table A” must already exist in “Table B”, else return error?

texts:
I'm trying to set up some kind of constraint so that I can only enter values in "Table A", if that value exists in "Table B".

I want this to act sort of like a selection list, where there are only a few values that exist in "Table B", which then restricts the values that can be entered into "Table A". (Kind of like a drop down list/ selection list)

I'm using pgAdmin 4 to access a PostGIS DB


texts: Partitioning mysql table on the basis of fulltxt(datatype:text) column

texts:
I want to partition below mysql table on the basis of fulltxt field having datatype is text. Here is my table structure.

CREATE TABLE links_copy (
  link_id int(11) NOT NULL AUTO_INCREMENT,
  site_id int(11) DEFAULT NULL,
  url varchar(255) NOT NULL,
  fulltxt text,
  indexdate date DEFAULT NULL,
  PRIMARY KEY (link_id),
  KEY url (url),
  KEY md5key (md5sum),
  KEY fulltext2 (fulltxt(100)),
  KEY indexdate (indexdate)
);
Partition By range(fulltxt)(
PARTITION p0 VALUES IN ('California'),
PARTITION p1 VALUES IN ('City'),
PARTITION p2 VALUES IN ('Council'),
PARTITION p3 VALUES IN ('Meeting'),
PARTITION p4 VALUES IN ('Agenda'));

Question:
Is it possible to partition on the basis of fulltxt, not on basis of primary key link_id?


texts: Converting Python's date format to a MySQL

texts:
First of all I hope this is the right place to ask.
I have a Python script which parses data from a few links. In the parsers I parse also the date of the last modified article.

This is the output of the date in the code;

2012-08-07T09:00:00 # Year, month, day, hour, minute, seconds

I have to throw this output to a Mysql database with the type DATETIME
When I throw this to the database, I check the field later on, I only have;

0000-00-00 00:00:00

(Although I am using SQLALchemy, it gives me the warning when inserting to the table);

/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py:331: Warning: Out of range value for column 'cdate' at row 1
  cursor.execute(statement, parameters)
/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py:331: Warning: Out of range value for column 'mdate' at row 1
  cursor.execute(statement, parameters)

Well, clear enough I am not putting the right content inside, so I wonder if there is any converter in Mysql or you know something in Python that will work this out for me.
I guess formating the whole string will be a bit of a pain and "useless" if there is something like that already.

Cheers.
- Ephexeve


texts: How to create automatic partitions on a non-time based column in Postgres?

texts: I want to create a partition on a column which is of type serial. A hash based partition seems appropriate here. But from what I understand I will need to create all the partitions myself (based on remainder). Is there a way to create all the partitions (say 10 partitions) using some automated extension like pg-partman?


texts:
My query can be simplified as this:

SELECT a.id, a.name, b.* FROM a LEFT JOIN b ON a.id=b.id

Which b is from group concat select (very dynamic column). Sometimes there are 3 columns in b, sometimes 10 columns depend on filtering.

What I want is something like this:

SELECT a.id, a.name, IFNULL(b.*,0) FROM a LEFT JOIN b ON a.id=b.id

I already looked here and here and the answer is always repeating per column. But I can't apply ifnull per column because I don't know what the columns are. Is there any other approach for replacing null values from left join?

All columns are of the integer type.


texts: How to convert different string value to respective 'YYYY' int format?

texts:
I'm inserting values into table from xml as:

DECLARE @XMLRESULT XML

DECLARE @tbl_XMLResult TABLE
(
    RowID INT IDENTITY(1,1),
    Status VARCHAR(50),
    Address VARCHAR(250),
    ListPrice INT,
    SoldPrice INT,
    YearBuilt INT
)

EXEC usp_GetReportResult @query = @query, @ReportName = @ReportName, @XMLResult = @XMLRESULT OUTPUT

INSERT INTO @tbl_XMLResult
(
    Status,
    Address,
    ListPrice,
    SoldPrice,
    YearBuilt
)
SELECT
    L.value('(ListingStatus/text())[1]','VARCHAR(50)') AS Status,
    L.value('(PMCReportData/Listing_StreetAddress/text())[1]','VARCHAR(250)') AS Address,
    L.value('(ListPrice/text())[1]','INT') AS ListPrice,
    L.value('(ClosePrice/text())[1]','INT')AS SoldPrice,
    L.value('(YearBuilt/text())[1]','INT')AS YearBuilt
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing')AS Result(L)

But as shown above for YearBuilt column the datatype is int in which we are storing just year on which property is built. And accordingly subsequent calculations are done. But some of the time the values for YearBuilt comes from xml as in format

"New"
"0-5 Yrs"
"70+"

But when above type of result for YearBuilt comes in xml we are getting conversion error.
Need to get values from above result as if YearBuilt as:

"New"     - Here we want consider current year and store result as 2016 current year.
"0-5 Yrs" - 0-5Yrs. Here consider upper limit, i.e. 5. So store result as 2011 as(2016 - 5 = 2011)
"70+"     - consider 70 as => 2016 - 70 Year = 1946

How we can do this to store appropriate result into @tbl_xmlResult


texts: MySQL replication working fine, but still shows "Waiting for master to send event" at 280000 seconds

texts:
To repeat the title: MySQL replication working fine, but mytop still shows a thread "Waiting for master to send event". It has been like this for a few days, and is now at 280000 seconds. But replication is just fine.

I can see another thread which is doing the replication work, right now it is sitting on "Slave has read all relay log; waiting for the slave I/O thread to update it" but it has been running queries, and I can verify that the slave is up to date just by viewing the data.

Can I kill this process?

I recently had to re-set up replication, and that involved creating a new replication user, so I'm not sure if this is what has caused this "ghost replication process".


texts:
Is it possible to create a new function from another plpgsql function? Something like this:

CREATE OR REPLACE FUNCTION func_test()
RETURNS VOID AS
$BODY$
BEGIN
  CREATE OR REPLACE FUNCTION func_test2()
  RETURNS INT AS
  BEGIN
  $$
    SELECT 1;
  $$
  END
END
$BODY$
LANGUAGE plpgsql;

When I execute the code above I get:

ERROR:  unexpected end of function definition at end of input
LINE 13: $BODY$`


texts: SQLAgent Job Step- Database field blank, but not

texts:
In looking at a historical SQL Agent job on my employers server I noted a job with 4 steps. The Databasefield was set as master for the step 1 and not set at all for the other 3 steps. Yet each of those 3 steps contained simple SQL statements (exec SPs, inserts etc.) that all worked on a specific database.

When I scripted the job out to a query window each of those 3 steps had the element:

@database_name=N'NameOfMyDatabase',

So it looks like the database context was set in the 'background'. What would lead to this circumstance? (Of the GUI showing blank) It's a minor mystery that is nagging at me now.


texts: Oracle Enterprise Manager 11g dbconsole "Internet Explorer cannot display the webpage"

texts:
The dbconsole is configured and started, but when I navigate to it, Internet Explorer shows the page stating that "Internet Explorer cannot display the webpage". This is the error that means that IE received no response from the server at all. The firewall is completely disabled.

I've tried -deconfig and -config to no avail. Any thoughts that could possibly lead me down the path to fixing this are appreciated.

OS: Windows Server 2003 32-bit
DB: 11gR2 Enterprise Edition 11.2.0.3.0
EM: Whatever comes with DB 11gR2 11.2.0.3.0

UPDATE:
I'm using the link that is automatically created for you when you run emctl -config dbconsole db. The URL is https://corp-svr-001-xxxxxx.xxxxx:5500/em where x represents an alphanumeric character removed for privacy. The reason it configures at port 5500 is that there is a 10g instance on the same server that already has port 1158. We recently updated it to 11g but haven't uninstalled 10g yet.

I've tried changing the URL to use localhost and the network IPv4 address to no avail.


texts: Howto reliably ship WAL log files between master and slave

texts:
I have a replication master and a slave on two different computers. Actually both databases are running in docker containers, and their WAL archive directories are exposed to the host machine using docker's bind mount. They are also connected with streaming replication, but the network connection between them is not reliable. Sometimes the network is down for a minute or so. Usually the speed of the network is fine, but there are outages a couple of times per day.

I just can't mount the slave's directory on the master, because of the unreliable network. The master postgres should be able to execute the archive command quickly, even if the network is down. I need a good utility that allows me to reliably ship log files between the two computers? E.g. start the transfer of a file, retry until it succeeds, then delete the original file after the transfer has been completed. rsync is not an option, because it does not move files, only copies them. (At least I don't know how to use it for this purpose.)

Does anyone know a solution that can be used out of the box? I can write my own program for this, but I don't want to reinvent the wheel.


texts:
I'm converting a sql server database to postgresql 9.4. I'm stuck on something that is really really slow.

I use copy to bulk insert 200 files of 5000 lines each into 200 tables (one file per table).

Each table have a before trigger that use pgcrypto to encrypt two bytea column from the files and 3 default text into 3 additional column

The trigger looks like this :

select password into var_pwd from temp_table;  --The password is put into a temporary table before the copy.

new.bytea1 : = pgp_sym_encrypt_bytea(bytea1,var_pwd);
new.bytea2 : = pgp_sym_encrypt_bytea(bytea2,var_pwd);
new.text1 : = pgp_sym_encrypt('default text1',var_pwd);
new.text2 : = pgp_sym_encrypt('default text2',var_pwd);
new.text3 : = pgp_sym_encrypt('default text3',var_pwd);

It takes 55 minutes to load everything with the trigger(3 minutes without it, so the encryption is the problem).

It takes around 7 minutes to insert and encrypt everything in Sql Server (if it is of any use, i use a symmetric key, the EncryptByKey command and an instead of trigger. Everything else is the same)

The data needs to be decrypted, so a two way encryption is needed.

Is there any alternative i could try to speed up the query (another encryption extension, some postgresql.conf settings i should look into / etc)?

Thanks in advance.


texts: how to find the error description for the error codes found in the extended events file XEL generated by SSAS Tabular query end event

texts:
how can I translate the error codes logged into XEL files to the corresponding description?
I'm importing the XEL files to a SQL Table using the sys.fn_xe_file_target_read_file function, but the returned xml does not contain the error description, only the error code
this is an example of errors I get in the XML
  <data name="Severity">
    <value>3</value>
  </data>
  <data name="Success">
    <value>0</value>
  </data>
  <data name="Error">
    <value>-1055129590</value>
  </data>


texts: Postgres not using indexes when ORing across join

texts:
I'm doing a query with a join on two tables, where I need to do an ILIKE filter on a field in each of the tables with wildcards at both ends, and it should return a row if either of them match.

Approx:

SELECT * FROM "tableA" LEFT OUTER JOIN "tableB" ON
"tableA"."tableB_id" = "tableB"."id"
WHERE "tableA"."name" ILIKE '%rich%' OR "tableB"."name" ILIKE '%rich%';

As both of these tables are quite large I decided to try using trigram indexes to speed things up. However, this didn't appear to help, and having done an EXPLAIN ANALYZE I discovered that the indexes are not used. They are used if the filters are combined with AND instead of OR, and they are used if I do an OR between two indexed columns in the same table, just not with an OR between indexed columns in different tables.

Anyone have any ideas why?


texts: Get the current upper limit value for nvarchar2 in an Oracle 12c db

texts:
Starting from 12c, Oracle Database provides a way to control the max byte-size of a NVARCHAR2 column. To use up to 32767 bytes instead of 4000, one has to set the parameter max_string_size to EXTENDED.

Now, let's say I create dynamically tables from a Java application and I always want to use the max size for an NVARCHAR2 column, how can I get the max current value?

I know, I can do a select like

SELECT value
  FROM v$parameter
 WHERE name = 'max_string_size'

and check if the value is EXTENDED, but to me it looks more professional and robust if I can replace a statement like

CREATE TABLE mytable (mycolumn NVARCHAR2(32767))

with

CREATE TABLE mytable (mycolumn NVARCHAR2($system_var_for_max_size))

So, is there any system variable that stores the max size that I can use as in the previous example?


texts: Filestream File Share does not allow files to be created

texts:
When attempting to create a file or folder in a SQL filestream file share, Windows presents the following dialog:

For those who cannot see the image, the error reads "The file 'New Text Document.txt' is too large for the destination file system."

The create filetable script is as follows:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ARITHABORT ON
GO

CREATE TABLE [dbo].[Media] AS FILETABLE ON [DboData] FILESTREAM_ON [FileStreamData]
WITH
(
FILETABLE_DIRECTORY = N'ShareName', FILETABLE_COLLATE_FILENAME =     SQL_Latin1_General_CP1_CI_AS
)
GO

This is a newly created filetable and I am unaware how to troubleshoot it.  Google returns almost no relevant results.

EDIT: As it turns out, files greater than 0 bytes can be copied into the filetable without error, but renaming the file still causes the above error.

EDIT2: The error occurs whenever a file is moved.  Create and Delete is successful.


texts:
This is my table design:

  CREATE TABLE IF NOT EXISTS `items` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL,
      `description` mediumtext NOT NULL,
      `user_id` bigint(100) NOT NULL,
      `to_read` tinyint(1) NOT NULL DEFAULT '0',
      `added_at` datetime NOT NULL,
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      PRIMARY KEY (`id`),
      KEY `user_id` (`user_id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(100) NOT NULL,
  `item_id` int(11) NOT NULL,
  `tag` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `user_id` (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My two queries:

SELECT items.*,
    (SELECT GROUP_CONCAT(tags.tag)
       FROM tags
      WHERE tags.item_id = items.id) AS tags
FROM items WHERE items.user_id = 0;

Three-table layout with a tags_items table:

SELECT items.*,
    (SELECT GROUP_CONCAT(tags.tag) FROM tags
       WHERE tags.id = tags_items.tag_id) AS tags
  FROM items
  LEFT JOIN tags_items
         ON tags_items.item_id = items.id
 WHERE items.user_id = 0;

Question:

Is it good to go with 2 table design, or 3 table design?

When I tried out 3 table design, my second query, rewritten for 3-table design doesn't work properly, it simply doesn't concatenate properly like the 2nd table query.


texts: Why does mongodb fail my json schema validation?

texts:
I am using mongodb 3.6 and have below validator for the students collection:

rs1:PRIMARY> db.getCollectionInfos({name: 'students'})
[
    {
        "name" : "students",
        "type" : "collection",
        "options" : {
            "validator" : {
                "$jsonSchema" : {
                    "properties" : {
                        "name" : {
                            "bsonType" : "string",
                            "description" : "name"
                        },
                        "age" : {
                            "bsonType" : "int",
                            "minimum" : 10,
                            "maximum" : 30
                        }
                    },
                    "required" : [
                        "name",
                        "age"
                    ],
                    "bsonType" : "object"
                }
            },
            "validationLevel" : "strict",
            "validationAction" : "error"
        },
        "info" : {
            "readOnly" : false,
            "uuid" : BinData(4,"111r7z+rQpGoebZ1jAHO4g==")
        },
        "idIndex" : {
            "v" : 2,
            "key" : {
                "_id" : 1
            },
            "name" : "_id_",
            "ns" : "test.students"
        }
    }
]

it requires two properties name and age. Name must be a string and age must be between 10 and 30. But why does below insert fail?

rs1:PRIMARY> db.students.insert({age: 11, name: 'Mike'})
WriteResult({
    "nInserted" : 0,
    "writeError" : {
        "code" : 121,
        "errmsg" : "Document failed validation"
    }
})


texts: Sync two MySQL databases in two different locations

texts: I have two identical MySQL databases one in an internal server and the other in a web hosting server. I want to update the database on the web host each day with the database on the internal server. Is there a way to automate this process also how can I do this manually? If I'm to do it manually, does it require me to get a SQL dump of the Database on the internal server and then import it on the database on the web host? Can someone advice please.


texts: MySQL SELECT query: i want show duplicated entries

texts:
Example

My query is:

SELECT code_CAT FROM categories WHERE id_code IN(14,14,14,14);

output is:

53724

Only one row.

Instead I want this output:

53724
53724
53724
53724

How can I achieve this? I want this because I need to use the records on an Excel Spreadsheet.


texts:
I have a table employee

id    name   salary  city
1     ram    50000   c1
2     sham   20000   c2
3     jadu   80000   c1
4     madhu  90000   c4
5     hari   10000   c2
6     gopal  34000   c3
7     komal  55000   c3
8     bappa  98000   c4

query is which city earning the highest.
i tried

SELECT city, SUM(salary) AS maxSalary
FROM employee GROUP BY city ORDER BY salary DESC LIMIT 1;

it works fine but if there are more than one max earning cities then it doesn't output other max cities, only the first one.

so i tried this query

SELECT city, MAX(totalSalary) maxSalary
FROM( SELECT city, SUM(salary) AS totalSalary FROM employee GROUP BY city  ) AS tempTable

it is giving

city  max
c1    188000

but correct is

city  max
c4    188000

It means it is outputting the first most city name of the table which is c1 but not the correct max earning city name which is c4.
What is the right query?


texts:
Error log is :

016-02-21 21:22:34 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
....
....
2016-02-21 21:22:37 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
2016-02-21 21:22:37 GET FATAL:  password authentication failed for user "postgres"
2016-02-21 21:22:37 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
2016-02-21 21:22:37 GET LOG:  CreateProcess call failed: A blocking operation was interrupted by a call to WSACancelBlockingCall.

     (error code 299)
2016-02-21 21:22:37 GET LOG:  could not fork new process for connection: No error
2016-02-21 21:22:37 GET LOG:  incomplete startup packet
2016-02-21 21:22:38 GET FATAL:  password authentication failed for user "postgres"
2016-02-21 21:22:38 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
2016-02-21 21:22:42 GET FATAL:  password authentication failed for user "postgres"
2016-02-21 21:22:42 GET DETAIL:  Connection matched pg_hba.conf line 83: "host    all             all             ::1/128                 md5"
2016-02-21 21:24:26 GET LOG:  could not receive data from client: An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.
2016-02-21 21:24:26 GET LOG:  incomplete startup packet
2016-02-21 21:24:29 GET FATAL:  password authentication failed for user "postgres"
2016-02-21 21:24:45 GET PANIC:  could not write to log file 000000010000000100000041 at offset 4259840, length 8192: Invalid argument
2016-02-21 21:24:45 GET STATEMENT:  COMMIT
2016-02-21 21:24:53 GET LOG:  server process (PID 470944) was terminated by exception 0xC0000409
2016-02-21 21:24:53 GET DETAIL:  Failed process was running: COMMIT
2016-02-21 21:24:53 GET HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2016-02-21 21:24:53 GET LOG:  terminating any other active server processes

Can anyone help to solve the problem ,I spent many hours but dont find solution. PG VERSION is 9.4
 Operating System: Windows

PostgreSQL.conf: is :

listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 128MB
dynamic_shared_memory_type = windows

pg_hba.conf is :

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5


texts: How to synchronize a local database with an external database continuously without replication

texts:
I created a database on our internal network (localDB), it receives data via CSV (LOAD DATA INFILE) coming from our ERP, it is continuously updated
I have an external database (remoteDB) with which our clients interact (several more external databases are coming soon)
I need to send all data changes made on our local database to our external database continuously
Example: on table_1 of localDb we add a row or we modify a row (via the LOAD DATA INFILE), this will be automatically replicated on table_1 of remoteDb
I first thought about replication, alas on our external server we are using PLESK, which does not allow replication (https://support.plesk.com/hc/en-us/articles/360003100973-Is-it-possible-to-setup-MySQL-replication-in-Plesk-)
I am therefore looking for a simple and free solution to synchronize my databases (if possible open source in order to avoid malicious code), because we will have to create several external databases for several websites, all connected to different tables of our internal database
Ease of management is essential to manage everything easily
I thought about MySQL Workbench but this one only syncs the model ...
PS: I don't want to do our LOAD DATA INFILE directly on the external database


texts:
Under RR in transaction T1 I do a
SELECT * FROM mytable WHERE city='London'

In another T2 I do
UPDATE mytable SET price=100 WHERE city='London' and id=5000

Is it possible that T2 reads,updates and commits the row in question BEFORE T1 gets to read it ?
1.If that is so,then RR would not have an effect,or as a matter in this scenario no isolation level would have an effect becasue isolation concerns only rows that they've been ALREADY read,not those not yet read.True?
2.Is however SERIALIZIBLE different? Does it take key range locks which means it locks the rows BEFORE it has read them? So would SERIALIZIBLE in the code example above
SELECT * FROM mytable WHERE city='London'

take any key ranges locks that would prevent the scenario of T2 getting to the row and updating before it is read?


texts: Is the data fetched by an explicit cursor immutable in PL/SQL?

texts:
This seems like an obvious question, but Googling has so far failed me...

Say I have a view which delineates updates that I need to apply and, to this end, I have an SP that opens the view as a cursor, iterates through each row and makes the appropriate change. The thing is, the updates made by the SP will alter the contents of the view (e.g., once the inconsistency has been dealt with, the row will disappear from the view).

Will this be reflected in the open cursor (in the SP), or is the data that it fetches fixed when it is first opened?

What I've read suggests that the cursor data is immutable once it's been opened -- which is good -- however, I've yet to find a straight answer.


texts: How do I disable MySQL on Linux from starting on boot or statup?

texts:
After adding MySQL to Ubuntu, it automatically starts up on server boot. I would like it to not start up with the system.

How can I disable it and manually start it?


texts:
I am unable to start then SQL Server Agent. I am getting the below error message when I tried to start the agent:

  The SQL Server Agent service (DB_NN) on local machine was started and then  stopped. Some services automatically stop if they are not used by any service or program

SQL version: SQL Server 2008R2 Standard Edition(RTM)
Windows:Windows server 2008 R2 enterprise

I verified below things, currently SQL agent is running through service account. I changed to local and tried to start. But agent didn't started.

How to fix this issue?


texts:
I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync?

Is this safe?

I know we need to do a full mysqldump/reload for a migration for InnoDB tables but I didn't see any notes about significant changes to the MyISAM format between the two.

It would make things a little nicer for a rather large MyISAM db that would take several days to complete with a mysqldump.


texts: How to export dump of some schemas exclude some tables

texts:
I need to find the way make an quick and light weight dump export. It is the reason why I want to export only specific scheme and to exclude large tables.

I use Enterprise Manager for it. Data Movement > Move Row Data > Export to Export Files > Schemas > add need schemas > Show Advanced Options > Exclude Only Objects Specified Below > Add Another Row > select table and type need table names > next..next..next

EM generate next PL\SQL

declare
  h1 NUMBER;
begin
  h1 := dbms_datapump.open(operation =>'EXPORT', declare h1 NUMBER;
  begin
    h1 := dbms_datapump.open(operation => 'EXPORT',
                             job_mode  => 'SCHEMA',
                             job_name  => 'FAST_TRY_6',
                             version   => 'COMPATIBLE');
    dbms_datapump.set_parallel(handle => h1, degree => 1);
    dbms_datapump.add_file(handle    => h1,
                           filename  => 'efast.LOG',
                           directory => 'backup',
                           filetype  => 3);
    dbms_datapump.set_parameter(handle => h1,
                                name   => 'KEEP_MASTER',
                                value  => 0);
    dbms_datapump.metadata_filter(handle => h1,
                                  name   => 'SCHEMA_EXPR',
                                  value  => 'IN(''MYSCHEMA1'',''MYSCHEMA2'')');
    dbms_datapump.add_file(handle    => h1,
                           filename  => 'efast181015.DMP',
                           directory => 'backup',
                           filetype  => 1);
    dbms_datapump.set_parameter(handle => h1,
                                name   => 'INCLUDE_METADATA',
                                value  => 1);
    dbms_datapump.set_parameter(handle => h1,
                                name   => 'DATA_ACCESS_METHOD',
                                value  => 'AUTOMATIC');
    dbms_datapump.metadata_filter(handle      => h1,
                                  name        => 'EXCLUDE_NAME_EXPR',
                                  value       => 'IN (''"MYSCHEMA1"."MYTABLE1"'',''"MYSCHEMA2"."MYTABLE2"'')',
                                  object_type => 'TABLE');
    dbms_datapump.set_parameter(handle => h1,
                                name   => 'ESTIMATE',
                                value  => 'BLOCKS');
    dbms_datapump.start_job(handle       => h1,
                            skip_current => 0,
                            abort_step   => 0);
    dbms_datapump.detach(handle => h1);
  end;
  /

However, exported whole schemas with MYTABLE1 and MYTABLE2.


texts: Issues with a Table having 120 fields in mysql

texts:
I am creating a table with 120 fields in MySQL. It will have 50 varchar(255) fields, other are maximum fields with char(1) , 1 date field and 5 double fields and others are int(11) fields.

My question is - will it cause any problem in future?


texts: MySQL Import CSV with Multiple Field Separators

texts:
I'm trying to import this CSV file into MySQL that appears to be optionally enclosed by more than one character. Unfortunately, MySQL only supports one character as a field separator.

I am stuck doing this entire process in SQL as it is part of a larger program- so scripting is out of the question.

Example of the data:

   reportdata, commission, total, chargeback, company
   ",123,""$116.00 "",""$604.00 "",""($88.00)"", foo

I'm stumped apart from attempting to do a bunch of string operations on a temp table.

Any ideas?


texts: Is it a good practice to create a database for each year?

texts:
In a HR database i need to do statistics for each year, is it a good practice to create a database for each year?

Thank you.


texts:
I want to convert a readable timestamp to UNIX time in Amazon Athena (Presto).

For example: I want to convert 2018-08-24 18:42:16 to 1535136136000.

Here is my syntax:

    TO_UNIXTIME('2018-08-24 06:42:16') new_year_ut

My error is:

   SYNTAX_ERROR: line 1:77: Unexpected parameters (varchar(19)) for function to_unixtime. Expected: to_unixtime(timestamp) , to_unixtime(timestamp with time zone)


texts: Why is SSMS inserting new rows at the top of a table not the bottom?

texts:
Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using identity columns and this results in things like

id  row
42 first row
1 second row
2 third row

When rows are fetched and not explicitly ordered. This results in a different appearance when the rows are fetched for the web app and changes what a TOP 1 query returns.

I know I can order by them, but why is this happening? Most of my data is inserted through a web application, all inserts from this application result in a First In First Out ordering, e.g. latest insert is at the bottom, so the ids are all in a row. Is there some setting in the server or Management Studio that causes this improper ordering?


texts:
I am trying to create an XML file from a SQL query.
The out put should look like the image below

Where the tag BranchID appears again as SubParentBranchID if there are more than one SubBranchID associated with BranchID. In the image below BranchID 94 has two SubBranchID 63 and 64.

I've manged this SQL query

SELECT
a.[heading_id] as BranchID,
c.[name] as BranchName,
a.[business_id] as SubBranchID,
a.[heading_id] as SubParentBranchID,
b.[name] as SubBranchName
   FROM [BUSINESSHEADINGLINK] as a
   join [BUSINESS] as b on a.business_id = b.business_id
   join [HEADING] as c on   a.heading_id = c.heading_id
FOR XML PATH ('Branch'), ROOT('BranchInfo')

This gives me the following XML

Here you can see that neither do i have the tag <SubBranches> nor <SubBranch>. Also i do not have <SubParentBranchID>. The XML file should be like:

Can anyone help me with the SQL code?

Also i would like to save it on C:/temp after execution.


texts: EXPLAIN output suggests that my index is not being used

texts:
I have set up my table with an index only on done_status(done_status = INT):

When I use:

EXPLAIN SELECT * FROM reminder  WHERE done_status=2

I get this back:

id  select_type  table     type  possible_keys  key  key_len  ref  rows  Extra
1   SIMPLE       reminder  ALL   done_status    NULL NULL     NULL 5     Using where

But when I issue this command:

EXPLAIN SELECT * FROM reminder  WHERE done_status=1

I get the following returned:

id  select_type  table     type  possible_keys  key          key_len  ref   rows  Extra
1   SIMPLE       reminder  ref   done_status    done_status  4        const 2

The EXPLAIN shows me that it uses 5 rows, the second time 2 rows.

I don't think the index is used, if I understood it right first time it should give me 3 rows. What do I do wrong?

SHOW INDEX FROM reminder:

Table    Non_unique  Key_name     Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null  Index_type  Comment  Index_comment
reminder 1           done_status  1             done_status  A          5            NULL      NULL          BTREE

explain extended:

id  select_type  table     type  possible_keys  key          key_len  ref    rows  filtered  Extra
1   SIMPLE       reminder  ref   done_status    done_status  4        const  2     100.00

show warnings didn't show anything of interest.


texts: Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output?

texts:
I have two tables with below structures:
create table TABLE_LC
( lc_num   NUMBER,
  brn_code NUMBER,
  brn_role NUMBER)

create table BRN
( brn_code NUMBER,
  brn_name VARCHAR2(50))

Some example data for each table is this:
"TABLE_LC":

lc_num    |  brn_code  |  brn_role
------------------------------------
  1       |     1      |  1010
  1       |     2      |  2020

"BRN":

brn_code  |  brn_name
---------------------
  1       |   BAHAR
  2       |    KAJ

I need To have one record for each lc_num in TABLE_LC and the desired output based on the given tables and data is this:
lc_num    |  Advising_Bank  |  Issuing_Bank
-------------------------------------------
  1       |     BAHAR       |  KAJ

What I've written is below query which gives me the correct output,considering the fact that we know brn_role=1010 represents Advising Bank and brn_role=2020 represents Issuing Bank
select t1.lc_num,
       t2.brn_name "Advising Bank",
       t4.brn_name "Issuing bank"
  from TABLE_LC t1
 inner join BRN t2
    on t1.brn_code = t2.brn_code
   and t1.brn_role = 1010
 inner join TABLE_LC t3
    on t3.lc_num = t1.lc_num
 inner join BRN t4
    on t4.brn_code = t3.brn_code
   and t3.brn_role = 2020

As you can see, I have joined the base table twice, I want to know if there are better ways of  getting the desired output ?
Thanks in advance


texts: why a convert from varchar do date fails on a different row everytime it runs?

texts:
I have a database with a field like this (Dont know why it's varchar):

  19950105

i'm trying to convert it to DATE as DDMMYYYY.

I could do this with:

convert(varchar(10), cast(The_ugly_field as date),103)

Then my date is:

  05/01/1995

1) Why I can't convert it directly to date? if I use:

select convert(date,The_ugly_field,103) from Ugly_Table

it returns 1900-01-01 and it doesnt respect the 103. any number there returns the same format YYYY-MM-DD.

2)Now the real question. when I run

select  convert(varchar(10), cast(The_ugly_field as date),103)
From Ugly_Table
order by The_ugly_field

after some time it returns this error:

  Msg 241, Level 16, State 1, Line 29  Conversion failed when converting
  date and/or time from character string.

but the funny part is, it doesnt looks like the errors appears in the same row. If I run this code like 4 times, every time it returns the error, it says a different row. I mean, I execute it, then in the "results" tab I can see that it returned 1.234.555 rows. If I run it again, after the error I return to the "result" table and now the query ran until 1.556.345.

and please. how the hell can I convert this 20140101 in a elegant way to 2014/31/12? Running convert(date,E1_EMISSAO,103) doesn't seems to respect the 103 and it returns 2017-02-24 and not 24/02/2017.

tons of questions and answers but it's all about a simple convert().


texts: db2 connecting to a db via alias and storing user name and password

texts: I created a TCP node and I catalogued a db into it. The catalogued db needs user name and password to be accessed. Is there anyway I can store username and password in the cataologue (or other way)? I use the catalogue in an Excel spreadsheet (OLEDB connection) and every time I need to insert username and password.


texts: Delete rows in a table that has multiple FKs

texts:
I need to delete all the rows in the ContentData table that have the word 'DV95' in the htmldata column.

Here is what I am using:

BEGIN TRANSACTION

DELETE Contentdata
FROM Contentlinks INNER JOIN contenttasks
   ON contentdata.contentid = contenttasks.contentid
WHERE contentdata.htmldata LIKE '%DV95%'

Here are my questions:

Is there problem in the syntax?
It keeps showing me a number of FK contraint issues due to number of table dependencies. How do I delete these rows?


texts: Calculating average with AVG for a list of numbers

texts:
This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL
like:

SELECT 2+3+4

How can I calculate the average of 2, 3 and 4 without inserting them to a table first?

I have tried different kinds of variations like:

SELECT 2 AS foo UNION 3 AS foo UNION 4 AS foo
  foo
  integer
1 2
2 3
3 4

but:

SELECT AVG(SELECT 2 AS foo UNION 3 AS foo UNION 4 AS foo)

  ERROR:  syntax error at or near "select"

I just can't seem to find the right syntax. I'm on PostgreSQL at the moment if it matters.


texts:
Based on order history, I'm trying to calculate the daily average sales for a given product form a vendor.

I have a table containing the line-item detail for the sales order. The relevant columns are:

item_id, vendor_id, ship_qty, post_date

I want to get a daily total of product sales for a given item. Item id's can be duplicated across vendors, but not within the same vendor id.  With that total I'm trying to derive the average quantity sold per day.

I started by creating a view that provides the sum by item on a given day:

daily_sales_by_item as SELECT  SUM(ship_qty)
AS item_total, item_id,vendor_id, post_date FROM order_history
GROUP BY vendor_id, item_id, post_date

Then I'm selecting an average from this view and grouping by the vendor_id and item_id as a sub-query:

SELECT item_id,vendor_id from daily_sales_by_item
d where d.item_id = o.item_id and d.vendor_id = o.vendor_id

I feel like I must be way of using this approach, because the numbers I'm getting are totally inaccurate.

Any help is greatly appricated.


texts: Steps to apply windows updates to sql failover cluster?

texts:
What are the steps to apply windows updates to a active/passive two node sql failover cluster?  My initial thought was to apply windows updates to passive node/reboot and then failover and repeat process but not sure.  Do I need to stop the db's and sql agent jobs before failover?
sql2014
windows 2012r2


texts: how to check which background process are running in my oracle database?

texts:
To check all background process in oracle we can see using v$bgprocess view. I want to check which background process are active. Can anybody please help ?

Thanks


texts:
This question may be too easy to ask, but I can't understand how to do it.
I am new in SQL.
I have two tables.

Table I
book_id (AI & Primary Key)
book_name
book_year

Table II

writer_id (AI & Primary Key)
writer_name
writer_bio

I want to create a relation between book_id and writer_id. When I add a new book, I don't want to store the writer's name for the every single time, I want to connect it to a specific writer_id. That's how relational databases work, right?
A writer could be written more than one book, but a book also can be written by more than one writers. How can I make the relation?
I also appreciate any kind of link to understand how this works.


texts: What is "good" single-block read latency?

texts:
Is there a reference anywhere that I can go and see what kind of single-block read latency I can expect from Oracle given a certain hardware configuration?

I can run a query to show the current performance (select * from V$SYSMETRIC_HISTORY where metric_id=2144 order by 1) but what I don't understand is how to evaluate the result - what is "good" or "bad" given a certain set of hardware?  And how would load impact that?


texts: Why does this implicit join get planned differently than an explicit join?

texts:
In this answer I explain SQL-89s implicit syntax.
But I noticed different query plans while playing around:

EXPLAIN ANALYZE
  SELECT *
  FROM (values(1)) AS t(x), (values(2)) AS g(y);

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.052 ms
 Execution time: 0.020 ms
(3 rows)

Versus this:

EXPLAIN ANALYZE
  SELECT *
  FROM (values(1)) AS t(x)
  CROSS JOIN (values(2)) AS g(y);
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Subquery Scan on g  (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.075 ms
 Execution time: 0.027 ms
(4 rows)

Why would one of them show Subquery Scan?
Isn't the implicit syntax just rewritten to be the same as the explicit syntax?


texts: Is it a good idea to add a reserved field to a large MySQL table?

texts:
As you may know adding a new column to a MySQL table with millions of records is  hard and in some cases impossible.

Is it a good idea to add a text column to each table (say, meta) to save some headache in future? Of course, they are empty in most cases.

These data can be stored as JSON. I know this field can not be accessible easily but it can avoid another join and it's important for this performance-sensitive app.

It uses InnoDB.


texts: Combining a parent table with multiple child tables


texts: SSMS 2016, Availability Groups, database is encrypted error

texts:
While evaluating possibility for rolling upgrade of SQL Server 2012 instances hosting AGs to SQL Server 2016 we ran into strange issue that does not quite make sense. One of the easier way to demonstrate is as follows:

Let’s say you want to add database to AG (2012 instance is being used). SSMS 2012 or SSMS 2014 wizard shows no surprises:

SSMS 2016 however tells completely different story:

For databases that are already part of AG it says “Does not meet prerequisites” instead of “Already part…” and for those not in AG it says “Password required” with the following explanation:

"This database is encrypted by database master key, you need to provide valid password when adding it to the availability group."

Problem with this message is that none of the databases use any encryption.

Same issue manifests itself if you restore database from 2012 AG backup on 2016 instance and try to add it to 2016 AG using SSMS 2016 wizard.


texts:
I have table "tbl_data", where have a lot of rows (3 - 5 million).
I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table.
If I start *.jar file (for example, "run.jar") and after INSERT/UPDATE process I start "run.jar" sequentially, there no problems - works OK.
If I start "run.jar" and then start "run_01.jar" (jar file with the similar logic: INSERT/UPDATE into "tbl_data"), I can get error:

  com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
  Deadlock found when trying to get lock; try restarting transaction

All actions at "run.jar" executes in transaction, like:

  START TRANSACTION; INSERT INTO tbl_data ...;UPDATE tbl_data ...; COMMIT;

So, I need to have ability make INSERT/UPDATE from several sources at the same time without blocking table for modification (Every jar file UPDATES/INSERTS its own rows in "tbl_data" so it is no problem for data in modification data at the same time).


texts: Problem using merge into a remote table over dblink

texts:
I'm attempting to perform a merge into a remote table, let's say on server A (over a dblink), using a local table on server B. Source and target databases are both on Oracle 10.2.0.3.

We already have a similar existing merge that works fine (but with the query running on server A and merging into a table on server B) so I'm a bit flummoxed as to what could be causing the problem.

I've reduced the case to the simplest - having identical tables and using the entire content of the local table for the merge, and still the merge fails with ORA-02064 "distributed operation not supported":

MERGE INTO attendance@dblnk tgt
USING (
  SELECT *
  FROM attendance
  -- WHERE TRUNC(in_date) = TO_DATE('01.09.2013', 'DD.MM.YYYY')
) src
ON (tgt.emp_no = src.emp_no AND tgt.in_date = src.in_date)
WHEN NOT MATCHED THEN
  INSERT (emp_no, in_date, out_date)
  VALUES (src.emp_no, src.in_date, src.out_date)

Any idea what I should be checking next? Many thanks in advance


texts: Cannot log in using SA with SQL Authentication

texts:
I can not log in to SQL Server 2008 using SQL Server authentication.

I can only log in using Windows Authentication Mode.

I chose only defaults while installing SQL Server 2008 R2.


texts: Error updating zoned field from SQL Server stored procedure linked server to iSeries

texts:
I'm trying to update a table on an iSeries (AS400) within a SQL Server stored procedure using linked server, but it is failing when trying to update a column defined as zoned.
In my stored procedure the value is defined as integer, and on the iSeries is defined as Zoned 6 long with no decimals.
I've tried casting the integer as follows:
cast(@SysDate as NUMERIC(6))

but I'm still getting error:

Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "PD999 F98OWSEC" could not UPDATE table.
The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

I don't get this error when removing the zoned fields from the update so I'm pretty sure it's a type conversion issue.
Linked server is defined as OLE DB Provider for ODBC drivers and provider string is MSDASQL.1
Any help here would be greatly appreciated


texts:
I have a 5 node Galera 3 Cluster running MySQL 5.6 on Ubuntu 16.04 (I tried running 5.7 but ran into the AppArmor issue).

I'm trying to add another node to the cluster but it continually fails. The cluster has about 50gb of data. There are 4 nodes that are being used while I use 1 node as the donor.

I turn on (sudo systemctl start mysql) the new node to connect it the cluster and the rsync process begins but fails. I get the following error:

Aug 09 23:19:55 nyc-01 systemd[1]: mysql.service: Unit entered failed state.
Aug 09 23:19:55 nyc-01 systemd[1]: mysql.service: Failed with result 'timeout'.

The process restarts and the rsync still runs but it always fails eventually.

How can I add another node to a Galera Cluster? Would doing a mysqldump then importing it on the new node THEN connecting it to the cluster be a better idea?

Donor Node:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

max_connections=4096
wsrep_slave_threads=1000

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="Cluster"
wsrep_cluster_address="gcomm://xxx.xxx.xxx.xxx"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="xxx.xxx.xxx.xxx"
wsrep_node_name="SFO-05"

New node:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

max_connections=4096
wsrep_slave_threads=1000

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="Cluster"
wsrep_cluster_address="gcomm://xxx.xxx.xxx.xxx"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="xxx.xxx.xxx.xxx"
wsrep_node_name="NYC-01"
wsrep_sst_donor="SFO-05"