texts: Two table left has multiple rows, right has 1 row. Need right table to return value only in left table 1 time

texts:
Newbie Here, looking for guidance.  Please and Thank you!
I have two tables where I am looking to see where Alice (left & right) is forecasted and to what role(s) and to what client(s) (left) and how much availability she has left to take on more work (right).
Note:  Fcst + Avail = Total Working days per month  e.g. Mar=23, Apr = 22, Jun = 22....not required for my query)
(Left) Forecast_Table:
Emp_ID, Emp_Name, Role,      Client_Name,  Fcst_Mar, Fcst_Apr, Fcst_Jun
123,     Alice,   Developer,  Alpha, LLC,    10,        12,          18
123,     Alice,   Analyst,    Alpha, LLC,     1,         2,           0
123,     Alice,   Analyst,    Beta Corp,      2,         2,           0
123,     Alice,   Analyst,    Gamma Inc,      6,         2,           0

(Right) Availability Table:
Emp_ID, Emp_Name, Avail_Mar, Avail_Apr, Avail_Jun
123,     Alice,      4,        4,          2

Right now, I am getting the following as a result:
Emp_ID, Emp_Name, Role,      Client_Name,  Fcst_Mar, Avail_Mar, Fcst_Apr, Avail_Apr, Fcst_Jun, Avail_Jun
123,     Alice,   Developer,  Alpha, LLC,    10,         4,        12,       4,         18,        2
123,     Alice,   Analyst,    Alpha, LLC,     1,         4,         2,       4,          0,        2
123,     Alice,   Analyst,    Beta Corp,      2,         4,         2,       4,          0,        2
123,     Alice,   Analyst,    Gamma Inc,      6,         4,         2,       4,          0,        2

I would like the following result:
Emp_ID, Emp_Name, Role,      Client_Name,  Fcst_Mar, Avail_Mar, Fcst_Apr, Avail_Apr, Fcst_Jun, Avail_Jun
123,     Alice,   Developer,  Alpha, LLC,    10,         4,        12,       4,         18,        2
123,     Alice,   Analyst,    Alpha, LLC,     1,        Null,       2,       Null,       0,      Null
123,     Alice,   Analyst,    Beta Corp,      2,        Null,       2,       Null,       0,      Null
123,     Alice,   Analyst,    Gamma Inc,      6,        Null,       2,       Null,       0,      Null

Also, I use MS Access and I'm not great with SQL, so have been using Design View.  Hoping you all can help!  Thank  you in advance.


texts: Online Learning: Different User Roles Different Fields

texts:
I'm trying to build an online learning system ( similar to Udemy ), where users have different types ( or roles ).

For now "roles" are : students , instructors  and admins ,and it may increase in the future.Where each role has its own permissions.

A user can have 1 role or more, so
a user can be a student of some course and an instructor of ( another course )

see below:

the problem :
now, each role may have different fields ,for example:
an instructor role has a unique field like "biography" which no any of the other user roles has.

how can this be accomplished with this database design , I was thiking of creating tables like:

**instructors_users Table**
user_id
some unique fields for instructors role

**students_users Table**
user_id
some unique fields for students role

Is this right ?


texts: Export each row of a table into a separate text file

texts:
I have a table in my database that contains data with three columns: id, name, and notes. The notes column contains unstructured text data that I want exported for each row as its own text file. This means if I had 5 rows in my table, then I would export 5 .txt files. Here is my attempt so far:

do $$
  declare
    arow record;
    files varchar;
  begin
    for arow in
    select id, name, note from multi_text
    loop
      files := '/tmp/'||arow.id||'_'||arow.name||'.txt';
      COPY (select note from multi_text where id = arow.id) TO files (FORMAT CSV);
    end loop;
  end;
$$;

Currently, there is a ERROR:  syntax error at or near "files". I am using PostgreSQL in pgadmin. Any help is much appreciated!


texts: SQLite - is it faster to use SELECT NOT EXISTS / EXISTS or IN / NOT IN in a WHERE clause?

texts:
In SQLite's query planners is it generally faster to do this kind of thing?

SELECT foo.bar,foo.baz
FROM foo
WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha)

... or ...

SELECT foo.bar,foo.baz
FROM foo
WHERE haha NOT IN (SELECT haha FROM lol)

They are functionally equivalent but I'm wondering whether the query planner handles them differently. I know in larger databases like PostgreSQL there are IN / NOT IN optimizations that make the latter as fast or faster than the former.


texts: Using a type in place of a column definition list?

texts:
Running the below, I get "a column definition list is required for functions returning "record"".

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');

That's fine. I know what it wants.

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}')
  AS (a int, b int, c int, d int);

Also, In PostgreSQL, all tables already have a type created by the same name.

CREATE TABLE foo(a,b,c,d)
  AS VALUES
    (1,2,3,4);

That creates an internal type foo linked to the newly created table foo. I can easily create a similar type, bar explicitly though.

CREATE TYPE bar AS (a int, b int, c int, d int);

It would be great to be able to cast the record returned by json_to_record() to bar.

SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}')
  AS foo; -- bar? anything?

Is there anyway to satisfy a column definition list with a type?


texts: Incorrect syntax error when creating view

texts:
Error message returned:

  Msg 102, Level 15, State 1, Procedure vw_IVStockAging, Line 12
  Incorrect syntax near '–'.

When I execute this:

CREATE VIEW vw_IVStockAging
AS SELECT IV00101.ITEMNMBR AS ITEMNUMBER,
IV00101.ITEMDESC AS ITEMNAME,
...
(IV10200.QTYRECVD – IV10200.QTYSOLD) AS QTYAVAILABLE
FROM ...


texts: After change to 13306, Galera logs error: Slave I/O: error connecting to master 'repuser@:3306'

texts:
Working with Galera 25.3.23 on RHEL 7.3

Galera works good before. After changing the MySQL server port from 3306 to 13306, one of the node report error, after restart:

Slave I/O: error connecting to master 'repuser@<IP>:3306' - retry-time: 60  maximum-retries: 86400  message: Can't connect to MySQL server on '<IP>' (111 "Connection refused"), Internal MariaDB error code: 2003

The other 2 nodes works fine after the restart.

Googled the web, but don't find the way to specify the port number.

Also, if possible, please share the usage of the "repuser" ID.


texts:
I have used SQL Servers Import Data wizard to import data from an Excel spreadsheet.

The Import Data Wizard worked fine to import the data.

It also worked fine to save the SSIS package.

I can then log in to Integration Services on the server and run the package from within Integration Services.

However, when I try to run the package from SQL Server Agent, I get the error message:

  The Excel Connection Manager is not supported in the 64-bit version
  of SSIS, as no OLE DB provider is available.

I've seen suggestions to set the "Use 32 bit runtime" option, but this had no effect.  I've seen suggestions to set something similar in BIDS, but I didn't use BIDS to generate this package.  I've seen options to use the 32-bit version of DTExec but I don't think the 32-bit version is installed on the server.

SQL Agent Job definition:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'import data',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'SSIS',
        @command=N'/SQL "\"\ExcelImportPackage\"" /SERVER MyDBServer /X86  /CHECKPOINTING OFF /REPORTING E',
        @database_name=N'master',
        @flags=0

Given that the package works both from the Wizard and from Integration Services I believe the server has everything needed to run the package from SQL Server Agent, but I cannot see how to do it.


texts:
I have a table

  PUR# EID PID  CID         QTY  PTIME     TOTAL_PRICE
---------- --- -----       ---- --------- -----------
100001 e01 p002 c001          1 12-AUG-17      211.65
100002 e01 p003 c001          1 20-SEP-17       118.4
100003 e02 p004 c002          5 08-OCT-17        4.95
100005 e04 p007 c004          1 15-OCT-17       119.2
100006 e03 p008 c001          1 12-OCT-17       349.3
100007 e03 p006 c003          2 10-SEP-17       35.91
100008 e03 p006 c005          1 16-AUG-17       17.96
100009 e03 p001 c007          1 12-OCT-17        8.99
100010 e04 p002 c006          1 19-SEP-17      211.65
100011 e02 p004 c006         10 16-OCT-17         9.9
100012 e02 p008 c003          2 18-SEP-17       698.6

I want to find

its pid and cid as well as the number of different customers (header: “number of customers”) who have purchased it in October 2017. If a product has not been purchased by anyone in October 2017, number 0 should be shown. Use column format to change the header to “number of customers”.

P.S: It is not a homework and I am working on a project so finding it difficult


texts: Psycopg2 Errors on SQL statement when trying to copy data from CSV file into PostgreSQL database

texts:
I am not a developer or PostgreSQL DB admin, so this may be basic questions.
Logistics: Windows 10 server / pgAdmin 4 / Postgres 10 / Python 2.7.13

I'm using a python script to ingest external data, create a CSV file and copy that into Postgres 10. I keep getting the following error:
Psycopg2.ProgrammingError: syntax error at or near "VALUES"

I have a two part question - 1) I can not see the syntax error in the following sql statement

def insert_csv_data(sqlstmt):
with get_conn('pg') as db:
    cur = db.cursor()
    sqlcopy = "COPY irwin (fire_id,name,type,acres,date_time,state,county,admin_unit,land_cat,commander,perc_cntnd,cont_date,gacc,lat,long,geom,updated,imo) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,ST_SetSRID(ST_MakePoint(%s, %s),4326)%s,%s) FROM STIN DELIMITER ',' CSV HEADER"

    with open(csv_file, 'r') as f:
        #next(f)# Skipping the first line header row
        cur.copy_expert(sqlcopy, f, size=8000)
        db.commit()
        cur.close()

And 2) Once that is resolved I'm expecting to get an error about the geometry column in postgres. If someone would also peek at the code snippets and let me know if anything jumps out I would SO APPRECIATE IT!

This snippet pulls the external data in order but I don't think I've coded this correctly to pull the lat/long into the geom field.

            # Lat 15 - double
        if not attributes['InitialLatitude'] is None:
            lat = str(attributes['InitialLatitude']).replace('\n', '')
        else:
            lat = '0'

        #Long 16 - double
        if not attributes['InitialLongitude'] is None:
            long = str(attributes['InitialLongitude']).replace('\n', '')
        else:
            long = '0'

        # geom is not defined - script is dumping the geometry into the IMO field
        geom = str(attributes['InitialLatitude']) + ' ' + str(attributes['InitialLongitude'])

I added a Geom header to the csv data. Please help - thanks!


texts: Should I clean up my old table statistics?

texts: I've been cleaning up my indexes but now realized I have over 150 statistics that remain that are not used.  Should I drop them?  I'm not noticing any performance issues from them.  I have 9 indexes and 159 statistics.


texts: Extracting week from date seems to give incorrect value at the start of the year

texts:
I have a query about extract(week from {date}) - when switching over to subsequent years, where I would expect it to return 1, it tends to return 53. To illustrate this point, consider the following bit of code:

with
    __steps as(
        select
            unnest(array['2015-12-20', '2015-12-27', '2016-01-03', '2016-01-10', '2016-01-17']::date[]) as step_from,
            unnest(array['2015-12-26', '2016-01-02', '2016-01-09', '2016-01-16', '2016-01-23']::date[]) as step_to
    )
select
    __steps.step_from,
    __steps.step_to,
    extract(week from __steps.step_from) as week
from
    __steps
order by
    __steps.step_from asc

This returns the following results

step_from   step_to     week
----------  ----------  ----
2015-12-20  2015-12-26  51
2015-12-27  2016-01-02  52
2016-01-03  2016-01-09  53
2016-01-10  2016-01-16  1
2016-01-17  2016-01-23  2

I would expect week starting Jan'03 to be week 1, but as you can see it's not. Is this an international standards thing? Or do I just have my Postgres not quite setup right?

show timezone
-------------
Europe/London

PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit


texts:
In my current role I have to write down all my projects on pre-made grid on a dry erase board.  As I finish a project I erase it but as the board reaches its capacity I have to manually shift everything by re-writing so space can be made for new projects.

How can I re-create this in MS Access or Excel?


texts:
How do I write my calling query (without modifying V_SPECIAL_SAMPLES) to use the index on sample.sample_date (filtering by date first)?  I believe it's the TO_CHAR and TO_DATE conversions that prevent the use of the index.

Calling query:

SELECT *
FROM V_SPECIAL_SAMPLES
WHERE TO_DATE ("DATE SAMPLED", 'mm/dd/yyyy  hh:mi am') > (SYSDATE - 1)

Table: sample

sample_id     NUMBER -- PK
test_type_id  NUMBER -- FK (test_type.test_type_id)
sample_date   DATE   -- indexed

View: V_SPECIAL_SAMPLES

SELECT sample_id,
       TO_CHAR(sample_date, 'mm/dd/yyyy  HH:MI AM') "DATE SAMPLED"
FROM sample
WHERE test_type_id = 5 /* 5: SPECIAL */
ORDER BY "DATE SAMPLED" DESC


texts: Insert results of spBlitzIndex stored procedure into table

texts:
I would like to create a weekly to monthly job to execute sp_BlitzIndex during the night and insert the results into a table so that I can consume the results.

Here's what I've tried:

INSERT INTO dbo.[IndexBlitzResults]
           ([PK_IndexBlitzID]
           ,[Priority]
           ,[Finding]
           ,[DatabaseName]
           ,[Details]
           ,[Definition]
           ,[SecretColumns]
           ,[Usage]
           ,[Size]
           ,[MoreInfo]
           ,[URL]
           ,[CreateTSQL])
exec    [ozar].[sp_BlitzIndex]
        @Mode = 0,
        @GetAllDatabases = 1,
        @BringThePain = 1

And here's the error that it throws:

  Starting run. sp_BlitzIndex(TM) v5.4 - June 03, 2017 Create temp
  tables. Adding UQ index on #IndexSanity (database_id, object_id,
  index_id)

  redacted_db Inserting data into #IndexColumns for clustered indexes
  and heaps Failure populating temp tables. Last @dsql: SET TRANSACTION
  ISOLATION LEVEL READ UNCOMMITTED;
                  SELECT 29,
                    s.name,
                      si.object_id,
                      si.index_id,
                      sc.key_ordinal,
                      sc.is_included_column,
                      sc.is_descending_key,
                      sc.partition_ordinal,
                      c.name as column_name,
                      st.name as system_type_name,
                      c.max_length,
                      c.[precision],
                      c.[scale],
                      c.collation_name,
                      c.is_nullable,
                      c.is_identity,
                      c.is_computed,
                      c.is_replicated,
                      c.is_sparse,
                      c.is_filestream,
                      CAST(ic.seed_value AS BIGINT),
                      CAST(ic.increment_value AS INT),
                      CAST(ic.last_value AS BIGINT),
                      ic.is_not_for_replication
                  FROM    [redacted_db].sys.indexes si
                  JOIN    [redacted_db].sys.columns c ON
                      si.object_id=c.object_id
                  LEFT JOIN [redacted_db].sys.index_columns sc ON
                      sc.object_id = si.object_id
                      and sc.index_id=si.index_id
                      AND sc.column_id=c.column_id
                  LEFT JOIN [redacted_db].sys.identity_columns ic ON
                      c.object_id=ic.object_id and
                      c.column_id=ic.column_id
                  JOIN [redacted_db].sys.types st ON
                      c.system_type_id=st.system_type_id
                      AND c.user_type_id=st.user_type_id
                JOIN [redacted_db].sys.objects AS so  ON si.object_id = so.object_id
                                                                          AND so.is_ms_shipped = 0
                JOIN [redacted_db].sys.schemas AS s ON s.schema_id = so.schema_id
                  WHERE si.index_id in (0,1) OPTION (RECOMPILE);

  Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1427 [Batch
  Start Line 2] redacted_db database failed to process. An INSERT EXEC
  statement cannot be nested. Msg 3915, Level 16, State 0, Procedure
  sp_BlitzIndex, Line 1431 [Batch Start Line 2] Cannot use the ROLLBACK
  statement within an INSERT-EXEC statement.

I looked into using openrowset, but from what I could tell you can't pass parameters to the stored procedure.

So, how can I insert the results of this stored procedure into the table?


texts:
lets say I create a type like this:

CREATE TYPE books AS (
  book_id NUMERIC
, row_num NUMERIC
);

I want to write a query that returns me the attributes (book_id, row_num) of books.

I am not sure how to do this?


texts: Implementing a one to one (1:1) relationship between Person and User

texts:
Scenario description
I have this person table as super parent (or supertype),
id
firstname
lastname
email
telephone
...
...

and user table as a child (or subtype)
id
person_id (FK)
password
username
screenname
...
...

They must be in a one-to-one (1:1) relationship, because an user cannot be repeated twice, and so a particular email value in a given person row must not be repeated twice.
Then I have this message table which stores messages from anyone,
id
firstname
lastname
email
telephone
subject
content
...
...

but you can see that firstname, lastname, email, telephone are duplicated in the message table.
So, I am thinking to refer it to person table like this below,
id
person_id
subject
content
...

but then it does not seem right, as a person with the same email, name, etc. can send a message to me as many times as they want, so the details he/she provides can be repeated.
Questions

So, should I make message as a child of person the parent or they should be separate entities?

Or, are there any better suggestions to solve this problem?


texts:
I've been giving the task of taking SQL DDL from data modeler and to transcribe it into mySQL for an internship. The only thing I cannot figure out is how to take

Created_By VARCHAR2(30) DEFAULT USER,

and turn that into sql my resolution was just to do a trigger but now the trigger doesn't work

    /* Triggers for User Creation */
DELIMITER //
CREATE TRIGGER menu_creator_tr
BEFORE INSERT ON Menu
FOR EACH ROW BEGIN
    NEW.Created_By = USER();
END
DELIMITER;

it states that NEW.Created_By is missing a colon. I am very confused now. Just started learning sql and so i am lost. Some help for both problems is what I am looking for to learn, but a help on either or will help me finish this. I really need to know this though for I have more columns that are similar.


texts: unable to add a range to a tag using sh.addTagRange command

texts:
Have setup a 4 node test shard cluster:

1- mongos
1- config server
2- shard nodes hosting 1 shard each for a collection.

Cluster is working perfectly, but when trying to run the following command:

mongos> sh.addTagRange("HTMLDumps.HTMLRepository", { ShardMapId: "7" }, { ShardMapId: "11" }, "NRT")

 "errmsg" : "min: { ShardMapId: \"7\" } should be less than max: { ShardMapId: \"11\" }",
        "code" : 9,
        "codeName" : "FailedToParse",

Below is the shard status output:

databases:
        {  "_id" : "HTMLDumps",  "primary" : "shard0001",  "partitioned" : true }
                HTMLDumps.HTMLRepository
                        shard key: { "ShardMapId" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000       2
                                shard0001       2
                        { "ShardMapId" : { "$minKey" : 1 } } -->> { "ShardMapId" : "" } on : shard0001 Timestamp(3, 1)
                        { "ShardMapId" : "" } -->> { "ShardMapId" : "1" } on : shard0001 Timestamp(1, 2) jumbo
                        { "ShardMapId" : "1" } -->> { "ShardMapId" : "7" } on : shard0000 Timestamp(2, 0)
                        { "ShardMapId" : "7" } -->> { "ShardMapId" : { "$maxKey" : 1 } } on : shard0000 Timestamp(3, 0)

                             tag: NYC  { "ShardMapId" : "1" } -->> { "ShardMapId" : "7" }

What am I missing?

Complete sh.status output:


texts: What is the best solution to prevent picking the same value (by multiple requests)?

texts:
Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick up next tour with highest priority for the given color.

This is easy, but the challenge is to completely prevent picking up the same tour multiple times, which can surely happen in live system.

Current approach is:

SELECT tour_id based on (color, priority, pickedUp = false)
UPDATE tour set PickedUp=true
Return tour_id to the calling application

Obviously, since we have SELECT and UPDATE as separate requests, another request can come at this time and pick up the same tour.

I know it's possible to have a single UPDATE + SELECT query, but: I still need to return the picked up tour_id to the caller, how would I do this? Is it possible with SQL to execute SELECT after UPDATE + SELECT in one query?


texts: Are there other ways to select a dynamic list of columns?

texts:
I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that.

1. Using refcursors

CREATE OR REPLACE FUNCTION selecttestwithcolumnlist(
ticker character varying,
columnlist character varying)
  RETURNS refcursor AS
$BODY$
DECLARE
  ref1 refcursor;
BEGIN

OPEN ref1 FOR EXECUTE
'select ' || ColumnList || ' from Prices WHERE Ticker=$1;'
USING     Ticker;
RETURN ref1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE

This function is very easy to invoke from my Ado.Net client. All I need to do is pass the parameters. However, if I want to test this function from pgAdmin, the result set is open on screen only if I keep my transaction open. This is inconvenient. Of course, it is easy to expose the data as an HTML table or an Excel spreadsheet, but this is kind of a minor inconvenience.

2. Using setof records

CREATE OR REPLACE FUNCTION SelectPrices(colList VARCHAR)
 RETURNS SETOF record AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || ' FROM prices ORDER BY Ticker, ASOfDate';

END
$func$ LANGUAGE plpgsql;

Unfortunately, this complicates my client code. I cannot issue a simple SELECT like this:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') ;

I must explicitly provide the structure of my result set:

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker')
AS f(price NUMERIC,AsOfdate TIMESTAMP,ticker VARCHAR);

This is doable, but inconvenient.

Are there other ways to return dynamic column lists?

Edit to protect against SQL injection, I typically split the comma-separated list and join it against a system view. Anything that is not an actual column name is not returned. I did not mention that originally, just to keep the question short.


texts: Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than the regular cells?


texts: All actions denied on MS SQL Server on Go Daddy Hosting

texts:
I have a website hosted on GoDaddy and I need to create a database for it.

I headed to the Databases section and created a SQL Server Database, then on my local pc I tried to access the database via SQL Server Management Studio. I was able to login to the database but I cannot make any operations. I get a message saying "it does not exist or you do not have permission".

I decided to go to the Security tab, then Login and tried to change my username that I'm using to systemadmin role but received:

  Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

What could be the problem? There are no other accounts on it. The default sa account is disabled and I can't enable it because it will prompt no permissions etc.

I don't understand it. Why does GoDaddy allow me to create a database but with no permissions or rather I cannot alter it?


texts:
I have a initial table where I have the following rows: ID_0, geom and utilisatio (where the value varies from 1 to 7).

I am trying to create a table where I would have the count of a specific utilisatio value (associated to an ID_0) for every ID_0 that is within a range of 400m. So far I have come up with:

Select a.id_0, a.geom, count (b.utilisatio) as util_1
into new_table
from first_table as a
    left join first_table as b ON ST_Dwithin (a.geom, b.geom, 400)
where a.id_0 <> b.id_0
group by a.id_0, a.geom
;

Now my problem is that I don't actually know where to add the following constraint to get only the specified utilisation code:

where utilisatio = 1

I am a beginner self-taught user of Postgres so I am sorry if this question is basic.


texts: What are good resources on data modelling for business intelligence?

texts: We are a SaaS startup offering purchase & procurement solutions. The data model for operational transactions has been created (relational DB). We also want to offer analytics on those transactions to decision makers but using ad hoc queries to get this data out of the existing tables would be inefficient. What resources are out there on data modelling for business intelligence / data warehousing?


texts: What is the limit on the number of INSERT statements you can paste into SSMS and run?

texts:
I am currently creating a stored procedure that populates a table with a list of insert statements so that my DBAs can export the contents of the table to a text file and then paste the statements into SSMS and run (it is to copy data from test environment to live enviroment - I am constricted by the fact the 2 environments cannot see each other, bcp is not configured on the environment

My question is that the table I am going to export could have up to 2,000,000 insert statements. Is there a limit to what can be pasted in and run?


texts: Where can I find a list of reserved logins?

texts:
I have a web page where the user can configure a SQL Server login and password. I am trying to implement validation for this page.

I looked up the requirements for a SQL Server login, and found this Microsoft article. According to that article, SQL Server logins cannot be a reserved login name. "sa" and "public" are provided as examples. A full list of reserved login names is not provided, and I was not able to find one by searching.

Is there a list of reserved SQL Server login names? If so, where can I find it?


texts: How safe is it to widen a column in MySQL?

texts:
Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot.

A MySQL database I volunteered to help maintain has a table that looks like this:

CREATE TABLE person_per (
   [...]
   per_EnteredBy smallint(5) unsigned NOT NULL default '0',
   per_EditedBy smallint(5) unsigned default '0',
   [...]
) TYPE=MyISAM;

This works fine, except that recently our user IDs started to be larger than 65535, which caused the values stored into the above fields to be incorrect (i.e. large values get capped at 65535 rather than the expected value).

What I'd like to do is 'widen' those two columns of the table so that user IDs greater than 65535 can be correctly stored in them.  I think the commands to do it are:

ALTER TABLE person_per MODIFY per_EnteredBy MEDIUMINT unsigned NOT NULL default '0';
ALTER TABLE person_per MODIFY per_EditedBy MEDIUMINT unsigned default '0';

My questions are:

Are the ALTER TABLE commands above correct?  (In particular I'm not sure if I should append a number-in-parenthesis to MEDIUMINT, and if so, what number it should be)
Will this alteration correctly preserve the data that is already stored in those two columns?  I don't want to lose it.
Are there any obvious 'gotchas' that I might run into if I widen these columns?  AFAICT SQL queries don't depend too much on integer columns being a particular width, but perhaps there is some non-obvious dependency that I should be aware of?


texts: Is there a way to throw an error on transaction commit, if a specific stored procedure was not called as part of the transaction?

texts:
I want a transaction commit to fail if this special stored procedure was not executed before the commit.

I have achieved this in the past using SQL Server (I forget how), and it this special stored procedure wrote various meta data about the transaction commit (eg, the commit time) to an audit table. This was enforced at the database level so that if a user or an application did not call this special stored procedure before the transaction commit, an error was thrown.

We added an additional control to enforce that changes to specific tables could not be made outside of a transaction.

This allowed us to track changes to data even if applications did not behave as intended.

I want to achieve the same thing with PostgreSQL.


texts: Efficiently querying a very large MySQL table with C#

texts:
I am issuing a relatively simple SQL query in my C# app:

select * from myTable where myValue > 10 order by myValue;

There are ~180 million rows in the table and there is an index on myValue. When I run the query in MySQL Workbench, it returns the first 1000 rows in 0.015 seconds.

When I run the query in C#, it times out.

I suspect that the MySqlDataReader and/or the MySQL engine is attempting to find all matching rows before returning.

What I really want is read one row at a time from my C# code. I want to queue up this query and just pull rows off the top one at a time as they're needed.

Is there an appropriate programming paradigm for this in MySQL?


texts: Oracle client throwing ORA-12541: TNS:no listener

texts:
when i was trying to connect my remote server as-

sqlplus SYSTEM/oracle@192.168.0.171:1521/kannel

it shows me this error-

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 15 15:36:52 2013
copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

I have checked my listener, it is running

[root@roracle bin]# ./lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-NOV-2013 15:42:24

 Copyright (c) 1991, 2011, Oracle.  All rights reserved.

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Linux Error: 111: Connection refused
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
 TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Linux Error: 111: Connection refused

tnsnames.ora file contain -

KANNEL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = kannel)
     )
 )

listener.ora file contain

SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
   (GLOBAL_DBNAME = kannel)
   (ORACLE_HOME = /opt/u01/app/product/11.2.0/dbhome_1)
   (SID_NAME = kannel)
 )
)

LISTENER=
 (DESCRIPTION_LIST =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   )
  )
 )

sqlnet.ora file contain-

SQLNET.AUTHENTICATION_SERVICES= (ALL)

NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)

Oracle home path -

$ORACLE_HOME=/opt/u01/app/product/11.2.0/dbname_1

Oracle base path -

$ORACLE_BASE=/opt/u01/app

i have set environment variable

[root@roracle bin]# env | sort

}
_=/bin/env
CVS_RSH=ssh
G_BROKEN_FILENAMES=1
HISTCONTROL=ignoredups
HISTSIZE=1000
HOME=/root
HOSTNAME=roracle
LANG=en_IN
LESSOPEN=|/usr/bin/lesspipe.sh %s
LOADEDMODULES=
LOGNAME=root
LS_COLORS=rs=<......something........>:
MAIL=/var/spool/mail/root
module=() {  eval `/usr/bin/modulecmd bash $*`
MODULEPATH=/usr/share/Modules/modulefiles:/etc/modulefiles
MODULESHOME=/usr/share/Modules
OLDPWD=/root
ORACLE_BASE=/opt/u01/app/
ORACLE_HOME=/opt/u01/app/product/11.2.0/dbhome_1
ORACLE_SID=kannel
PATH=/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
PWD=/opt/u01/app/product/11.2.0/dbhome_1/bin
QTDIR=/usr/lib64/qt-3.3
QTINC=/usr/lib64/qt-3.3/include
QTLIB=/usr/lib64/qt-3.3/lib
SHELL=/bin/bash
SHLVL=1
SSH_CLIENT=192.168.0.83 35604 22
SSH_CONNECTION=192.168.0.83 35604 192.168.0.171 22
SSH_TTY=/dev/pts/0
TERM=xterm
USER=root

When i access database as a localhost -

./sqlplus / as sysdba[oracle@roracle bin]$ ./sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 16:04:02 2013

 Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 Connected to an idle instance.

 SQL> select v$database;
 select v$database
 *
 ERROR at line 1:
 ORA-01034: ORACLE not available
 Process ID: 0
 Session ID: 0 Serial number: 0

I Don't understand where i am mistaking.....Can somebody help?


texts: Why can't I create a MySQL user even if i have 'all priviliges'?

texts:
I am trying to resolve issues created when my ISP moved my database from a shared server to dedicated cloud storage. I am reaching it remotely via SQLyog. They say they have given all privileges to all users (I'm the only user)

Issuing the sql

SHOW GRANTS

returns

GRANT ALL PRIVILEGES ON `my_database_name`.* TO 'myusername'@'my ip address'

but if I try to create a user for my database using command such as

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

or

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

I get the error

Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

and if I try to see exactly what privileges I have using

SELECT USER FROM mysql.db WHERE db = 'my_database_name';

I get the error

SELECT command denied to user 'myusername'@'my ip address' for table 'db'

I can't select from several other system tables either, eg user

Should I be able to do these things if I really have 'ALL privileges'?

How can I list exactly which privileges I have instead of just getting 'ALL' ?

Can anyone suggest a way I can try to track down what is wrong?

BTW I don't know if they executed flush privileges after doing the grant or not. would this have made a difference given that SHOW GRANTS appears to show that I have all privileges?


texts: How to break down MySQL big table into multiple to solve operational issue

texts:
This is my current table schema in MySQL
we are not facing any performance issue in our application but there are operational issue .
The size of this table is 7 TB (6TB data and 1 TB index ) and has 4 Billions rows .
Because of the this one table having this big size we are not able to do any alter table on this table .
We have to use percona which takes 1 week to complete .
so to handle this we have decided to break this table in to 3 .
There are two columns which stores xml file that we want to move to separate table and this two column alone takes 2.5 TB of storage .
DETAILS longtext, and SUMMARY varchar(4000) DEFAULT NULL,
Along with it we also want to move few more columns to another table so that all three tables will become lighter
like third table we want to move USES_TYPE varchar(255) NOT NULL,  STEP_TYPE varchar(255) NOT NULL, NAME varchar(1500) DEFAULT NULL, and REMARKS varchar(1000) DEFAULT NULL,
CREATE TABLE `app_data` (
`ID` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`STEP_TYPE` varchar(255) NOT NULL,
`CUST_ID` varchar(255) DEFAULT NULL,
`DETAILS` longtext,
`DATE_TIME` datetime(6) DEFAULT NULL,
`GROUP_ID` varchar(255) DEFAULT NULL,
`SYSTEM_ID` varchar(255) DEFAULT NULL,
`NAME` varchar(1500) DEFAULT NULL,
`CUSTOMER_ID` varchar(255) DEFAULT NULL,
`REMARKS` varchar(1000) DEFAULT NULL,
`SUMMARY` varchar(4000) DEFAULT NULL,

PRIMARY KEY (`ID`),
KEY `IDX_APP_DATA_CID_OT` (`CUST_ID`,`USES_TYPE`) USING BTREE,
KEY `IDX_APP_DATA_SYSTEM_ID` (`SYSTEM_ID`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Now my final table will be comething like

Table one

CREATE TABLE `app_data_table1` (
`ID` varchar(255) NOT NULL,
`DETAILS` longtext,
`SUMMARY` varchar(4000) DEFAULT NULL;
)

Table Two would be like

CREATE TABLE `app_data_table2` (
`ID` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`STEP_TYPE` varchar(255) NOT NULL,
`NAME` varchar(1500) DEFAULT NULL,
`REMARKS` varchar(1000) DEFAULT NULL;
)

and table three

CREATE TABLE `app_data` (
`ID` varchar(255) NOT NULL,
`CUST_ID` varchar(255) DEFAULT NULL,
`DATE_TIME` datetime(6) DEFAULT NULL,
`GROUP_ID` varchar(255) DEFAULT NULL,
`SYSTEM_ID` varchar(255) DEFAULT NULL,
`CUSTOMER_ID` varchar(255) DEFAULT NULL,

PRIMARY KEY (`ID`),
KEY `IDX_APP_DATA_CID_OT` (`CUST_ID`) USING BTREE,
KEY `IDX_APP_DATA_SYSTEM_ID` (`SYSTEM_ID`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

I am new to data base but this is what i am coming up i know this is not optmised so my humble request is to pleae guide me on this.
Once we do this we need to use join to dispaly on UI or where ever we fetch so will that be slower ?


texts: Which would be some benefits of storing date ranges instead of single dates?

texts:
I need to store large number of dates, along with with a price that corresponds to each date. I am considering two options: One is (a) to store a row for each date and price pair; the other is (b) to store a row with date range and price. For example:

Option a. A table that stores individual dates

CREATE TABLE Foo
(
    ID    INT,
    Date  SMALLDATETIME,
    Price DECIMAL
);

Option b. A table that stores date ranges

CREATE TABLE Bar
(
    ID         INT,
    RangeStart SMALLDATETIME,
    RangeEnd   SMALLDATETIME,
    Price      DECIMAL
);

Is option (b) really a good idea? The main reason for this is to decrease number of rows INSERTed. In this case, if a price is the same throught a year I would have, e.g.:

ID     RangeStart    RangeEnd     Price
--     ----------    ----------   -----
 1     2017-01-01    2017-12-31     100

instead of

ID      RangeStart    Price
---     ----------    -----
  1     2017-01-01      100
  2     2017-01-02       100
...........................
365     2017-12-31       100

Is there really a need for this, since there would be an additional overhead of converting date ranges to dates when used at an application program business layer?


texts: What are the advantages and disadvantages of Firebase for a database?

texts:
I am currently developing a site to track credits gained from exams, and how these stack up against the user's goal credits.

I am looking for a database system that will store all the possible exams someone could take in NCEA (New Zealand's qualification system), and store the user's goals and actual results for exams.

I have considered using Firebase, mySQL and JSON storage with GET and POST requests in order to store and change data.

What are the advantages and disadvantages of using Firebase as a database for this?


texts:
I'm running a fantasy game where the ratings for each player are calculated in the following way:

Each round (gameweek) a player plays zero, one or more games. The rating for that round is the average rating scored in all games (stored in match_player)
The total season points for each player is the sum of all season round ratings.

I'm trying to list the season points for each player with this query:

SELECT
    `player`.`name`,
    COALESCE(
        (
        SELECT SUM(points)
        FROM
            (
            SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
            FROM `match_player`
            JOIN `match` ON `match`.`id` = `match_player`.`matchID`
            JOIN `round` ON `round`.`id` = `match`.`roundID`
            WHERE `match_player`.`playerID` = `player`.`id`
               AND `round`.`competitionID` = 1
               AND `round`.`season` = 2018
               AND `match`.`status` = 'finished'
            GROUP BY  `match`.`roundID`
        ) AS roundPoints
    ), 0) AS seasonPoints
FROM `player`

Byt MySQL is throwing this error:

#1054 - Unknown column 'player.id' in 'where clause'

Is there any way to group by round, calculate average rating and the sum all values for each player?

SQL Fiddle: http://sqlfiddle.com/#!9/bebd2/1

Alternative approach:

SELECT
        `player`.`name`,
        COALESCE(
           SUM(
                (
                  SELECT ROUND(AVG(`match_player`.`points`)) AS `points`
                FROM `match_player`
                JOIN `match` ON `match`.`id` = `match_player`.`matchID`
                JOIN `round` ON `round`.`id` = `match`.`roundID`
                WHERE `match_player`.`playerID` = `player`.`id`
                   AND `round`.`competitionID` = 1
                   AND `round`.`season` = 2018
                   AND `match`.`status` = 'finished'
                GROUP BY  `match`.`roundID`
              )
        ), 0) AS seasonPoints
    FROM `player`

This time, the error returned is:

Subquery returns more than 1 row

http://sqlfiddle.com/#!9/bebd2/2


texts:
I need to create a procedure to make statistics on my database.
First, I decided to create a temporary table to "simplify" my work.
It contains all the relevant actions (FormIdNb) of the working units (NumCavalier) on one day.
-- Every action from all units will be stored in this temp table
-- DateHeure = Time of action
-- NumCavalier = ID of working unit
-- FormIdNb = Action type
CREATE TABLE #tmp_DailyMissions (
DateHeure datetime,
NumCavalier  nchar(3),
FormIdNb int
)

INSERT INTO #tmp_DailyMissions
SELECT        DateHeure, Cavalier.NumCavalier, FormIdNb
FROM            ECN4, Cavalier
WHERE        (DateHeure BETWEEN @StartTime AND @EndTime) AND (Cavalier.Terminal = @terminal) AND (Cavalier.NumCavalier = ECN4.NumCavalier)

Problem is, I'm not able to make the request to make what I want :

I need to split the results by time interval (15min for 1 day)
I need to split again on the maximum value of 'FormIdNb' of each working unit 'NumCavalier' as :

FormIdNb > 3 ==> The working unit was active on that time interval
FormIdNb = 3 ==> The working unit was idle on that time interval (if not active on that same time interval)
FormIdNb < 3 ==> The working unit was inactive on that time interval (if not active or idle on that same time interval)

And finally, make a count for each time interval of working units depending the previous value.

I've already done this, it works partially, but it does not take into account the values of the preceding joins (for example : a working unit can appear active and idle at the same time, and so on...).
I think that I have to make a lot of changes to make it work :
;WITH
time_cte(StartTime, EndTime) AS
(
SELECT @starttime StartTime, DATEADD(mi, 15, @starttime) EndTime
UNION ALL
SELECT EndTime, DATEADD(mi, 15, EndTime) FROM time_cte
WHERE EndTime < @EndTime
)

SELECT StartTime, Count(DISTINCT CavsTerminal_ACTIVE.NumCavalier),
Count(DISTINCT CavsTerminal_IDLE.NumCavalier),
Count(DISTINCT CavsTerminal_INACTIVE.NumCavalier)
FROM time_cte
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) > 3
) CavsTerminal_ACTIVE ON  CavsTerminal_ACTIVE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) = 3
) CavsTerminal_IDLE ON  (CavsTerminal_IDLE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime)
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) < 3
) CavsTerminal_INACTIVE ON  (CavsTerminal_INACTIVE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime)
GROUP BY StartTime


texts: Why is my biggest segment resulting in an empty table?

texts:
I'm trying to identify top tables by size in my Oracle DB, I tried with

select  segment_type,
        segment_name,
        sum(bytes/1024/1024) MB

from    user_segments s

where   segment_type like 'TABLE%'

group by segment_type,segment_name
order by 3 desc
;

and my top biggest table is ABC_TABLE. Querying USER_EXTENTS for the table

select  *
from    user_extents e
where   segment_name = 'ABC_TABLE'
;

I get 218 records, and querying USER_SEGMENTS again for this specific table this is what I get:

 SEGMENT_NAME   | PARTITION_NAME | SEGMENT_TYPE | SEGMENT_SUBTYPE | TABLESPACE_NAME | BYTES      | BLOCKS | EXTENTS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | MAX_SIZE   | RETENTION | MINRETENTION | PCT_INCREASE | FREELISTS | FREELIST_GROUPS | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE
 ABC_TABLE      |                | TABLE        | ASSM            | ABC             | 2341470208 | 285824 | 218     | 65536          | 1048576     | 1           | 2147483645  | 2147483645 |           |              |              |           |                 | DEFAULT     | DEFAULT     | DEFAULT

What I don't understand is that by querying ABC_TABLE no records are shown, it seems to be empty. What am I getting wrong?


texts: MDX calculation of an Average Over Time with delta update on the fact table

texts:
I am having a problem creating this MDX calculation :

Average Price of products over time knowing that my fact table gets price updates incrementally;

In order to calculate the average price of products at a certain point in time I have to lookup back to the latest prices of all the products (not all of the products necessarily get price updates at that point in time).

My fact table looks something like this :

F_Products

(Product_key,Date_Key,Price,flag_PriceChanged)

Dim_Products

(Product_key,ProductID,Price)

I had a success solving this using Chris Webb's last ever non empty solution but it gets dramatically slower whenever I apply it on large set of products or over a large period of time.

The Calculations that I've used look something like this :

CREATE MEMBER CURRENTCUBE.[Measures].DAYSTODATE AS
IIF([Measures].[Price]=NULL, NULL,
COUNT(NULL:[Date].[Date].CURRENTMEMBER)-1);

CREATE MEMBER CURRENTCUBE.[Measures].HADSALE AS
IIF([Measures].[Price]=NULL, NULL, MEASURES.DAYSTODATE);

SCOPE([Measures].MAXDATE, [Date].[Date].[Date].MEMBERS);
    THIS = MAX(NULL:[Date].[Date].CURRENTMEMBER, MEASURES.HADSALE);
END SCOPE;

CREATE MEMBER CURRENTCUBE.[Measures].LASTSALE AS
IIF(ISEMPTY([Measures].MAXDATE), NULL,
([Measures].[Sales Price],
[Date].[Date].[Date].MEMBERS.ITEM(MEASURES.MAXDATE)));

CREATE MEMBER CURRENTCUBE.[Measures].[Average Price Incl. VAT]
AS
IIF( [Measures].HADSALE = NULL,NULL,
    AVG([Dim Products].[Product ID].[Product ID],MEASURES.LASTSALE*1.25));

Any help Would be appreciated.


texts: Pitfalls of creating a foreign key constraint on a system table?

texts:
I have a number of tables which hold metadata about the tables and packages in the system. This is intended to be a resource for analysts and documentation so it is not used to create data.

One of the columns that appears in a few of these tables shows the user or "Owner" of the object.  The data should only contain user accounts in the database and it seems like a good practice to have a foreign key constraint from the metadata tables to  sys.user$.

However I have always approached SYS tables with caution and I was surprised to see that there is no primary key for SYS.USER$ only a unique index on the NAME column.

As far as I know I cannot create a foreign key on sys.all_users.  Or I could create a table as Select username, user_id from all_users  so the data entry is checked on insert but it all seems very clumsy.

Is there a better way to access a table that has a primary key and the names of all schemas?

I ended up adding a check constraint as a hard coded solution that won't mess with system files.  I still think there should be way to get a list of the schemas or "owners" that can be used as a foreign key constraint.

@Phil, why don't you post your comment as an answer?


texts:
I need to restore a SQL Server database with/without the log file. This is for a reporting system. When I restore the log file it is sometimes 8x larger than the data file. I am restoring this once a week for a reporting system that will not need to be restored (if it fails I will restore it from the OLTP system).

Is there any reason I should just restore it without the log and keep the recovery model simple so the log file does not grow too much?

On the source the log file is not being backed up. A different group is managing that system.


texts: Query to generate a new column containing the value of the "previous" column

texts:
I have a MySQL table that has data similar to this:

id   #        current
--   -----    -------
1    item1    50
2    item2    60
3    item1    70
4    item2    40
5    item2    80
6    item2    33
7    item1    55

I need to analyze the data per each item and compare it to the previous value of the same item.

So how can I write query in order to retrieve the data like shown below?

id   #        current       prev
--   -----    -------       ----
1    item1    50            -
2    item2    60            -
3    item1    70            50
4    item2    40            60
5    item2    80            40
6    item2    33            80
7    item1    55            70

If no, then what is the most efficient way to achieve this?

The only thing that comes to my mind is query all and for loop on the results keeping array for previous value of each item. I am a little worried about performance as this is going to be a big table with a lot of rows and a lot of items. Any other option?


texts: Can a column be referenced from multiple table as a foreign key

texts:
Suppose I have a table named Service.
But I have other tables that have a similarities with that table
like RoomService, ConsultatonService, etc.

Can the Id of RoomService and ConsultationService keep a foreign key reference to Service table's ServiceId column but being identifiable by the Type column in the Service table?


texts: Query to get count of inserted records hourly wise in postgresql?

texts:
I have a table that looks like:
Table name Demo
id         creation_date
1          2021-03-06 12:27:48.811
2          2021-03-06 03:56:48.875
3          2021-03-06 03:58:23.567

I need the count of records hourly wise in PostgreSql.


texts: what is the quickest way to delete duplicate rows?

texts:
I need to delete duplicate rows from a large table.
what is the best way to achieve that?

currently I use this algorithm:

declare @t table ([key] int  )

insert into @t select 1
insert into @t select 1
insert into @t select 1
insert into @t select 2
insert into @t select 2
insert into @t select 3
insert into @t select 4
insert into @t select 4
insert into @t select 4
insert into @t select 4
insert into @t select 4
insert into @t select 5
insert into @t select 5
insert into @t select 5
insert into @t select 5
insert into @t select 5
insert into @t select 6
insert into @t select 6
insert into @t select 6
insert into @t select 7
insert into @t select 7
insert into @t select 8
insert into @t select 8
insert into @t select 9
insert into @t select 9
insert into @t select 9
insert into @t select 9
insert into @t select 9

select * from @t

; with cte as (
    select *
        , row_number() over (partition by [Key] order by [Key]) as Picker
    from @t
    )
delete cte
where Picker > 1

select * from @t

when I run it on my system:

;WITH Customer AS
    (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AccountCode ORDER BY AccountCode ) AS [Version]
    FROM Stage.Customer
    )
    DELETE
    FROM    Customer
    WHERE   [Version] <> 1

I found that <> 1 is better than > 1.

I could create this index, currently not present:

USE [BodenDWH]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Stage].[Customer] ([AccountCode])
INCLUDE ([ID])
GO

Is there any other way to get this done?

On this occasion this table is not big - about 500,000 records on the live system.

the delete is part of a SSIS package, it runs daily and deletes about 10-15 records a day.

there are problems in the way the data is structured, I just need one AccountCode for each customer but there could be duplicates and if they are not removed, they break the package on a later stage.

It was not me who developed the package, and my scope is not to re-design anything.

I am just after the best way to get rid of the duplicates, in the quickest possible way, without having to refer to index creation, or anything, just the T-SQL code.


texts: How to get past account lock information in oracle database?

texts: Is it possible to query the history of previously locked/unlocked Oracle database user accounts? I've tried dba_user but there is no option to get history.


texts:
We have a web application based on the famous triad apache + php + mysql which we sell to our customers and gets installed on their servers.

Currently, we are using MySQL 5.1.41 which has only a single user registered, root, with is password and access allowed only from localhost.

The problem is, if someone creates its own mysql installation, and then copies our database from its original location to that independent installation, s/he would be able to access its content.

Is there a way to prevent also this kind of improper access to our webapp database? Can it be encorder MySQL-side or must it be something in our own application?


texts: Distributed Database Architecture Using MySQL Replication

texts:
I've been task to move our web servers to the cloud for all countries that use our services for localization.

The issue is, they must share the same data and therefore I need to set up some database architecture that reads/writes locally but is shared amongst other servers.

I found an article that is pretty much what I want to do. Example below.

http://www.compassitesinc.com/blogs/distributed-database-architecture-using-mysql-replication/

However my DBA says this is not possible. So my question is, is this possible? And if so, how?

We are a LAMP shop (Redhat, Apache, PHP5, MySql 5.5).

Thank you.


texts: How would one create an SSRS datasource to quickbooks?

texts: My team is stuck. We are attempting to create reporting in SSRS to connect to quick books. We were successful in creating DAO connections in ms access, however we want to have reporting in SSRS and not ms access.


texts: Is there, currently a practice, science, discipline that approaches the analysis of the meta-data of an organization?

texts:
I am trying to catalog the data in our organization. I just realized that once that data is cataloged -- essentially meta-data -- we can run analytics on the data itself.

What I have in mind

Let us say I have information about the size, statistics of different tables and their columns
I would like to perform analytics across my collection of meta-data

Some Analytics that come to mind are:

Rate of growth of a particular concept (column or collection of columns)
Analysis of who uses similar concepts to satisfy queries
Types of changes between versions of tables

Concrete Examples

An analysis shows that a certain type of concept is being added to various tables -- digging could reveal that there is an emerging business dynamic which the rest of the organization needs to be aware off.
An analysis shows that several organs of the organization use a certain type of concept but are totally unaware that the others do so -- could one connect them or make them aware of that fact so they could share ideas knowledge
An analysis shows that a certain concept is used over and over by different parts of the organization -- could this concept be centralized to achieve efficiencies
An analysis shows that the Personally Identifiable Information is being joined with other data in a way that violates our advertised terms of service

Question

Is there, currently a practice, science or discipline that approaches the analysis of the meta-data from a perspective similar to the one written above?

If you are aware of it, I would appreciate any references to it.


texts: jsonb_array_elements() in the select seems to turn a left join into an inner join

texts:
PostgreSQL 9.6.

create table jon.vins (vin citext primary key);
insert into jon.vins values
('3GNAXUEV1KL221776'),
('3GNAXHEV2KS548975');

CREATE TABLE jon.describe_vehicle (
  vin citext primary key,
  response jsonb);

jon.describe_vehicle contains data for only 1 vin, 3GNAXHEV2KS548975;

This query:

    select a.vin,
      b.response->'attributes'->>'bestMakeName' as make
    from jon.vins a
    left join jon.describe_vehicle b on a.vin = b.vin;

Returns what I expect, one row for each vin in jon.vins:

        vin        |   make
-------------------+-----------
 3GNAXUEV1KL221776 |
 3GNAXHEV2KS548975 | Chevrolet
(2 rows)

But this query:

    select a.vin,
      jsonb_array_elements(b.response->'style')->'attributes'->>'name' as style
    from jon.vins a
    left join jon.describe_vehicle b on a.vin = b.vin;

Returns:

        vin        |      style
-------------------+------------------
 3GNAXHEV2KS548975 | FWD 4dr LS w/1LS
(1 row)

It is as if the jsonb_array_elements in the select turns the left join into an inner join.

I expected to see a row for vin 3GNAXUEV1KL221776 with a null value for style.

What am I doing wrong?


texts: Location of Maintenance Plan's Back Up Database Tasks information (SQL Server 2005)

texts:
I would like to know where in the database or on the file system the information about Back Up Database Task in the Maintenance Plans Tasks.

I can find the Job in msdb.dbo.sysjobs
I can find the Subplan in msdb.dbo.sysmaintplan_subplans

But I need to find where and how the Task is being stored.

Any help will be greatly appreciated.


texts: How to get N rows after the first row matching a condition?

texts:
I am implementing a cursor based pagination.

The requirement is to sort a dataset by an arbitrary condition (e.g. description) and return N rows after X row (where X represents the last item on the last page).

The first page is simple:

SELECT
  id
FROM probe
ORDER BY description ASC
LIMIT 5

Assuming this query returns IDs 4, 5, 2, 1, 3, then the next query must produce 5 next results from the same query after id 3.

Note: We cannot use LIMIT ... OFFSET after the first page because the offset is relative to the position of the referenced result that we do not know.

The only way I can think of solving this problem is:

Find row number in the dataset for ID 3:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.row_number
FROM data_index di1
WHERE di1.id = 3;

Then use position to offset the dataset.

All together:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.id
FROM data_index di1
WHERE di1.row_number > (
  SELECT
    di1.row_number
  FROM data_index di1
  WHERE di1.id = 3
)
LIMIT 10;

The downside of this approach is that data_index needs to load the entire sub-query into memory. Is there a more efficient way?


texts:
The log file for my SQL Server 2014 database is growing rapidly (currently in 32 GB in COMPLETE RECOVERY mode).  This is creating storage issues on my server.
The database has a set of job's with large SELECT - UPDATE - DELETE from another DB occurring at different times of the day.

How do I avoid rapid growth of the log file?
What practices should I use to minimize log growth?
COMMIT TRANSACTION is generally not used in STORE PROCEDURES, is it essential to add them as part of improvement?
Researching I read that I should use TRUNCATE TABLE, WITH (NOLOCK) as part of optimization, I would like to know your opinions.

Sorry for my English, thank you for your support.


texts: Would querying from a partitioned table be faster than from multiple tables with the same table definition?

texts:
I have a table that contains about 50 million rows that I have partitioned by 5 million rows per partition. I have manually partitioned it into Table_1, Table_2, Table_3, etc., not using MySql's built-in partition feature using the PARTITION keyword because I didn't know of this feature when I partitioned my table.

A lot of the times, I'll have to retrieve 10 million worth of rows, which would mean I'll have to query 2 partitions worth of data.

It may take anywhere from 2-10 seconds to retrieve the entire data (5 million rows) from just 1 table, which is pretty slow. Though after the initial query, the same query would be very fast (a few hundred ms).

Would partitioning my table(s) using the built in MySql partition feature help querying faster in my case?


texts: plan_generation_num keeps incrementing, why?

texts:
I have a query that is executed using sp_executesql

code is as follows

DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3);
SET @ppCityID = CAST(@cityID AS NVARCHAR(3));

exec sp_executesql N'Insert Into #FeaturedAdIDs Select
    fa.AdID,fa.PreviousHourViewCount,fa.IsFromCP,fa.SubCategoryID
    from FeaturedAds fa
            INNER JOIN #FilteredFeaturedAdIDs ffa
            ON fa.AdID = ffa.AdID
                    and fa.SubCategoryID = ffa.SubCategoryID
                    and fa.RecordStatusKey = @pRsk
    Where fa.CityID = @pCityID'
    , N'@pRsk INT, @pCityID INT'
    , @pRsk = @rsk
    , @pCityID = @ppCityID;

This piece of code is inside a stored proc.

When I look at the sys.dm_exec_cached_plans it shows that every time I execute this query, with OR without the same parameters, it keeps increasing the plan_generation_num and the exec_count stays at 1.

How can I stop recompiles of this statement?


texts: expdp Hangs at DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM in oracle 12c

texts:
Expdp Hangs at Below Stage for long time even in structure backup also .

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM

Database Version: -Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

so please let me know to get solution & if it is 12c Bug then please mention bug number also

P.S :-
here is expdp full script

 expdp username/password full=Y CONTENT=METADATA_ONLY directory=DIR1 dumpfile=structure_backup.dmp logfile=structure_backup.log KEEP_MASTER=Y

Database Size :- 10 GB


texts: Possible to update rows with data only accessible via API call?

texts:
I'm not good at what I'm working on so apologies if some of my terminology is off. Also not sure if this belongs here, or in a more programming oriented area.

I am looking for options to update rows of a mysql database with data only accessible via REST api.

In other words... I will create a row with some of the data needed. Then I have 4-5 columns I'd like to update with data that I need to gather from the output from an API call


texts:
I run a website with fairly high traffic. I've been having a lot of issues lately with "too many connections" or "could not connect" to MySQL and I think it's because of bots but I'm not 100% positive. I connect to the database using one username. So anyone who visits the site connects with the same script (and MySQL user).

If I have 1,000 people on the site at one time and I show processlist, the threads_connected is always 1. Is that normal? Could this be the cause of the too many connections issue?

The strange thing is, I can have 20,000 people on the site and it runs fine. But MySQL has been crashing a lot lately with an average of 200 people on the site - which my Amazon instance can EASILY handle. And there are always bots in the access_log when it goes down. I have tried so many things to stop MySQL from crashing and nothing is working.


texts: SQL Server Developer Edition 2012 or 2014 - Download

texts: I've heard (I am pretty sure) that Developer Edition is free of charge and for downloading it registration is not required. But when I started looking for it today I could not find where to get it from. Is my understanding wrong or right? Sorry I didn't find a similar question here. Any ideas?


texts: Connecting SQL Server Developer Edition to Visual Studio Windows Forms Application

texts:
I downloaded the free SQL Server Developer Edition in hopes of being able to use SQL Server for my Visual studio project, but I can't seem to understand how to connect it. :(

In my SQL Management Studio I never received login credentials to log in with, for the server named LAPTOPXXXX/OwnerName, so I can't connect through SQL Authentication in Visual Studio. Nor is there a create new credentials option anywhere to be found. Also when trying to connect to the server using my computer name or IP address it also fails to connect to the server I downloaded.

How do I make log in credentials for my free server and how do I connect it using like a web.config or app.config file's connectionstring (but winforms version)?


texts:
http://dev.mysql.com/doc/refman/5.5/en/handler.html

Seems to be very useful for PK lookups, but I haven't seen much information regarding this. What's the pros and cons of the handler syntax vs. ordinary SQL select for simple key-value queries? How does it compare (performance- and consistence-wise) to HandlerSocket?


texts: Oracle to PostgreSQL: Create type object

texts:
In Oracle:

create or replace type tr_del as object (row_id varchar2(18), pk_id number);

select tr_del(last_name, employee_id)
from employees where
department_id=30;

output:

(Raphaely, 114)
(Khoo, 115)
(Baida, 116)

How can I achieve the same in PostgreSQL?


texts:
I have a very simple query that is showing in Activity monitor and other statistics as having the most logical reads on my entire DB server.

SELECT MAX(RESULT_DATE) FROM TABLEX mm WITH (NOLOCK)
JOIN TABLEX_RESULTS mr WITH (NOLOCK) on mr.ID = mm.ID
WHERE DAYS IS NOT NULL AND mm.ORDER_ID = 12345

TABLEX has about 28 million rows

TABLEX_RESULTS has about 6 million rows

What can I do here to reduce the number of logical reads on this query?  I'm kind of confused on how such a simplistic query can have such a massive number of logical reads.

Thanks

Index definition (from comment)

tableX
IndexName                    PK     Type    Key1            Key2
IDX_MP_MEDS                  0      B AA    ORDER_ID        MEDPASS_DATE
IDX_MP_MEDS_ID_AND_ORDER_ID  0      B A     MEDPASS_DATE
IX_MP_MEDS_ROOT_ORDER_ID     0      B DA    ROOT_ORDER_ID   MEDPASS_DATE
PK_MP_MED PK                 1      C A     MEDPASS_MEDS_ID

tablex_resulsts


texts:
This tablespace is in my case 8GB.
I'm on oracle 10g and  got critical warning:

Tablespace usage critical: UNDOTBS (96%)

I also executed this command:

select sum (bytes/1048576) MB
FROM
DBA_SEGMENTS
WHERE
tablespace_name = 'UNDOTBS' ;

It gives me 7625,625MB.

Could I extend this tablespace with this command:

ALTER TABLESPACE "UNDOTBS" ADD DATAFILE '/path/to/ts.db' SIZE 4194304000;

Can I do it on running system and wont I destroy anything?


texts: Sql Server fails to use index on simple bijection

texts:
This is another query optimizer conundrum.

Maybe I'm just over-estimating query optimizers, or maybe I'm missing something - so I'm putting it out there.

I have a simple table

CREATE TABLE [dbo].[MyEntities](
  [Id] [uniqueidentifier] NOT NULL,
  [Number] [int] NOT NULL,
  CONSTRAINT [PK_dbo.MyEntities] PRIMARY KEY CLUSTERED ([Id])
)

CREATE NONCLUSTERED INDEX [IX_Number] ON [dbo].[MyEntities] ([Number])

with an index and some thousand rows in there, Number being evenly distributed in the values 0, 1 and 2.

Now this query:

SELECT * FROM
    (SELECT
        [Extent1].[Number] AS [Number],
        CASE
        WHEN (0 = [Extent1].[Number]) THEN 'one'
        WHEN (1 = [Extent1].[Number]) THEN 'two'
        WHEN (2 = [Extent1].[Number]) THEN 'three'
        ELSE '?'
        END AS [Name]
        FROM [dbo].[MyEntities] AS [Extent1]
        ) P
WHERE P.Number = 0;

does an index seek on IX_Number as one would expect.

If the where clause is

WHERE P.Name = 'one';

however, it becomes a scan.

The case-clause is obviously a bijection, so in theory an optimization should be possible to deduct the first query plan from the second query.

It's also not purely academic: The query is inspired by translating enum values to their respective friendly names.

I'd like to hear from someone who know what can be expected from query optimizers (and specifically the one in Sql Server): Am I simply expecting too much?

I'm asking as I had cases before where some slight variation of a query would make an optimization suddenly come to light.

I'm using Sql Server 2016 Developer Edition.


texts: What does this deadlock report from MySQL mean?

texts:
Here is the output:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-12-23 05:57:47 7f23d73c6700
*** (1) TRANSACTION:
TRANSACTION 32990178, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 314344, OS thread handle 0x7f23d6480700, query id 99645000 104.197.97.45 dsprod update
insert into pings (lastProcessTime, id, type) values (1450845, '52c7e28eebb26a86fb1505b5', 'INCIDENT')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 4 n bits 376 index `PRIMARY` of table `alerts`.`pings` trx id 32990178 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 24; hex 353363336635646430636632336133633062623934613866; asc 53c3f5dd0cf23a3c0bb94a8f;;
 1: len 3; hex 415050; asc APP;;
 2: len 6; hex 000001f76076; asc     `v;;
 3: len 7; hex 5a000001e10b80; asc Z      ;;
 4: len 4; hex d67a36f8; asc  z6 ;;

*** (2) TRANSACTION:
TRANSACTION 32990184, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 314341, OS thread handle 0x7f23d73c6700, query id 99645014 104.197.97.45 dsprod update
insert into pings (lastProcessTime, id, type) values (1450848, '52c7e28eebb26a86fb1505b5', 'INCIDENT')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 17 page no 4 n bits 376 index `PRIMARY` of table `alerts`.`pings` trx id 32990184 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 24; hex 353363336635646430636632336133633062623934613866; asc 53c3f5dd0cf23a3c0bb94a8f;;
 1: len 3; hex 415050; asc APP;;
 2: len 6; hex 000001f76076; asc     `v;;
 3: len 7; hex 5a000001e10b80; asc Z      ;;
 4: len 4; hex d67a36f8; asc  z6 ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 17 page no 4 n bits 376 index `PRIMARY` of table `alerts`.`pings` trx id 32990184 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 24; hex 353363336635646430636632336133633062623934613866; asc 53c3f5dd0cf23a3c0bb94a8f;;
 1: len 3; hex 415050; asc APP;;
 2: len 6; hex 000001f76076; asc     `v;;
 3: len 7; hex 5a000001e10b80; asc Z      ;;
 4: len 4; hex d67a36f8; asc  z6 ;;

*** WE ROLL BACK TRANSACTION (2)

And here is the code that produces this:

@Autowired
private PingRepository repository;

@Transactional
public void processPing(Ping ping)
{
    Ping storedPing = repository.findById(ping.id);

    if (storedPing == null)
    {
        repository.save(ping);
    } else
    {
        if (ping.lastProcessTime > storedPing.lastProcessTime)
        {
            storedPing.setLastProcessTime(ping.lastProcessTime);
        }
    }
}

public interface PingRepository extends CrudRepository<Ping, PingKey>
{
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Ping findById(PingKey id);
}

Some questions:

What exactly is going on in the deadlock report?
it feels like txn 2 is trying to acquire a lock it already has. why?
this is the only txn in my code that accesses the pings table. why does this code deadlock at all?

Update:

Adding table schema:

CREATE TABLE `pings` (
  `id` varchar(255) NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT '',
  `lastProcessTime` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


texts: Bulk insert into SQL Server from VMWare guest using distributed switch

texts:
This is mostly likely not a SQL server issue but the setup seems to only be affecting BULK INSERTS to SQL Servers.

We have recently moved VM Hardware and all the guests that were moved had their virtual switches changed from standard to distributed.

I then started receiving

  A fatal error occurred while reading the input stream from the
  network. The session will be terminated (input error: 64, output
  error: 0)

on a two SQL servers during BULK INSERT operations. One of the SQL servers was a VM with the new configuration and the other was a physical server. Both BULK INSERT operation originated from a VM with the new configuration. The BULK INSERTs would not fail every time, it was very random when it would.

When we changed the virtual switch to be a standard switch instead of a distributed switch the issue goes away.

I am looking for more of an explanation to why it doesn't work with a distributed switch instead of a resolution. My guess would be that the BULK INSERT operation is serial and with a distributed switch the packets are being routed through different hosts, some of which may be busier than others, and are arriving at the destination server beyond some latency threshold. (note: there is nothing in the windows event log at the times of the errors on either the source or destination server)

UPDATE: turns out the issue was due to the NIC. All of our VMs were configured with an E1000 NIC which functioned well enough on standard switches. Once we moved to distributed switches, we started seeing issues with large data transfers, not just sql queries. Changing the NIC to VMXNET3 seems to have resolved the issue.


texts: Unable to pass scema.object to the query shortcut in SSMS 2016

texts:
I am having a query shortcut that is generating create table in SQL Server Management Studio 2016. This works fine when I am not trying to select schema.table, but once I am trying to select such object I am getting error Incorrect syntax near '.'.

My query shortcut looks like:

exec sp_executesql 'huge sql oneliner', N'@table_name SYSNAME', @table_name =

Any idea how I can pass objects like that to the query shortcut?

update 1:

The only workaround I kind of found is to wrap the table name with the single quotes, which is a bit annoying.

update 2:

I simplified my shortcut a bit for debugging purposes:

exec sp_executesql N'select @table_name;', N'@table_name varchar(max)', @table_name =

and when I ran it this is what I captured in the Profiler:

exec sp_executesql N'select @table_name;', N'@table_name varchar(max)', @table_name = dbo.Categories

which makes sense for the workaround I found earlier, but still would love to make it work without surrounding it in single quotes by myself


texts: MySQL on MACOS Sierra secure-file-priv setting

texts:
I cannot find where to set secure-file-priv. I get this message:

  1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

when running this statement:

SELECT COLUMN_NAME, data_type FROM tabledefs INTO  OUTFILE  'xpeter'

If I remove OUTFILE 'xpeter'  it works.  OUTFILE '/tmp/xpeter does not work either.

I tried to add --secure-file-priv="/tmp" , no change. Variable stays as NULL:

 $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" --secure-file-priv="/tmp"    $other_args >/dev/null &

/usr/local/mysql/bin/mysqld --verbose --help | grep secure-file
  --secure-file-priv=name
secure-file-priv                                             NULL

There is no my.cnf file.  I run through whole filesystem, looking for any *.cnf file.  Could not find any other than: ./mysql-5.7.17-macos10.12-x86_64/data/auto.cnf and ./mysql-5.7.17-macos10.12-x86_64/support-files/my-default.cnf.  The line $bindir/mysqld...  is in support-files/my-default.cnf.

I rerun the query without OUTFILE 'xpeter',  query OK, no messages in log files except apache2 log.  Then I run

SELECT COLUMN_NAME, data_type INTO  OUTFILE  'xpeter' FROM tabledefs

no errors in  logfiles, only in phpmyadmin (where I run the query statement from, logged in as root): same message as before.

I tried the etc/my.cnf and also:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
      <key>ProgramArguments</key>
        <array>
   <string>--secure-file-priv=/mysql_exp</string>
        </array>
    <key>WorkingDirectory</key>  <string>/usr/local/mysql</string>
</dict>
</plist>

In both cases after reboot I was still not allowed to write a file.

I did not change the folder owner. If I add --secure-file-priv=/mysql_exp to Daemons and reboot MySQL does not run at all.


texts:
I have the below select stmts but I get an error message when i try to combine them. I want to convert diag_1 varchar datatype for example 250.8 to an integer and then round down to the nearest full number eg 250.

Also the data contains values with V25 aswell as numeric.  Is it possible to remove the 'V' from some of the data?

Select encounter_id,diag_1,diag_2,diag_3,
Try_convert(float,[diag_1]) as [float1],
Try_convert(float,[diag_2]) as [float2],
Try_convert(float,[diag_3]) as [float3]
from dbo.encounters;

Select round(float1,1) as roundvalue
  from dbo.encounters;


texts: Should I create seperate referenced table or go with serialized data in a column?

texts:
I have a table with name app_client with the below fields

  id, user_id, name, age, phone, gender, related_entities_json (long text datatype),
  created_at, updated_at

I have 200k+ records in this table and I found that the size is mainly increased (7 GB ) due to related_entities_json field.

So I want to know that should I move related_entities_json to a seperate table? And then access it's data when needed!

I don't want to open another question for this so i would also like to know that Will this increase performance during fetching/inserting data?

Entry in related_entities_json from a row

https://pastebin.com/y4XDCK4Y


texts: Query specific key fields from x-number of tables?

texts:
I'm building an insurance database and I need some help with best practice on fast quering data.
Let's say I have three insurance types ... Car, Home and Travel.
Common for all three types, I have the "Insurance" table:
[dbo].[Insurance] (
    [InsuranceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [CustomerID] [uniqueidentifier] NOT NULL,
    [CreatedDate] [datetime] NOT NULL
)

And then I have the three insurance types:
[dbo].[InsuranceCar] (
    [InsuranceCarID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Model] [uniqueidentifier] NOT NULL
)

[dbo].[InsuranceHome] (
    [InsuranceHomeID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Address] [uniqueidentifier] NOT NULL
)

[dbo].[InsuranceTravel] (
    [InsuranceTravelID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [InsuranceID] [uniqueidentifier] NOT NULL,
    [Destination] [uniqueidentifier] NOT NULL
)

All three insurance types have an "key search field" that I use for quering an insurance - in "Car" it is "Model", in "Home" it is "Address" and in "Travel" it is "Destination".
So the way I have solved it, is to create a "_Search" column in the "Insurance" table ...
[dbo].[Insurance] (
    [InsuranceID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [CustomerID] [uniqueidentifier] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [_Search] [nvarchar(MAX)] NULL
)

... and when any of the insurance type table rows is created/updated, then I copy the "key seach field" from the incurance type table into my "Insurance" tables "_Search" column (using trigger).
This way I can quickly query any key fields from the three insurance type tables by quering the "_Search" field in the "Insurance" table.
Problem is that it takes up more space in the database to do it like this.
Any better way to do this? Best practice?
Thanks.


texts: Disable Oracle auditing for a particular user?

texts:
I have one application user in my Oracle 11g DB which does logins/logouts very often.

Since this user is not a human, but an application I would like to exclude the login and logout actions of this particular user from my audit log.

How can I do that?


texts: Need SQL Server VLAN advice.. is it best practice to have all SQL Servers under 1 VLAN/network zone?

texts:
Looking at the network placement of my SQL Servers. Looking for answers:

Is it a best practice to have all SQL Servers in 1 or 2 VLANs within your businesses network? however, if so, wont there potentially be performance issues on the NICs that manage that VLAN because there are so many SQL Servers in one VLAN?

Also on firewalls between the SQL Servers & application servers - this is not recommended right, As there will be a bottleneck from the firewall?

So going from the above comment, is it simply best practice to have the SQL servers in the same VLANs as the application servers that interact with the databases?


texts: sp_BlitzIndex - "Cannot Insert the value null into column 'index_sanity_id'..."

texts:
Hi I'm getting this error message from the latest version of sp_BlitzIndex on one of my test servers:

  database failed to process. Cannot insert the value NULL into column
  'index_sanity_id', table
  'tempdb.dbo.#IndexSanitySize____________________________________________________________________________________________________00000002626F'; column does not allow nulls. INSERT fails.


texts: Unlink Windows login from database user in SQL Server contained database

texts:
I'm migrating databases from SQL Server 2012 to Server Server 2017, and switching SOME of them to partially contained.  I've done SQL Server backups and restores to actually move the databases, and switched them to partially contained.  When I then recreate the Windows-based logins at the server/instance level, they are automagically reconnected to the database users - in both the partially contained and non-contained databases.  For the users in the partially contained databases, I would like to break the link between the instance-level Windows login and database user, so that the authentication happens at the database level (but it's still a Windows login). I don't want to drop the instance-level Windows login because it is needed for other databases.

sp_migrate_user_to_contained is only for SQL Server logins, not Windows-based logins.  I could try one of the scripts out there for cloning a user and basically drop the existing user and recreate it, but I'm hoping not to.

Is what I'm trying to do not really supported, or maybe just pointless?


texts:
Two examples:

Table users, each user may have or not have some of ~10 possible permissions (like "can login, can post, can edit, can delete" etc).

Table articles, each article may have or not have some of ~10 possible attributes (like "for children", "18+", "less than 10 minutes to read", "long read").

What is the canonical \ more efficient \ fastest way to store it in the database?

I can think of two ways:

Option 1:
Bit-like kind of column, have column "permissions"\"attributes" and store a value which can be bit-interpreted like "0101100010", i-th bit is the flag for i-th attribute\permission

Option 2:
Relationship. Create table user_permissions, put 10 values with their IDs there, then create table user_permissions_map and keep this many to many (M:N) relationship in this table.

I'm a bit afraid of option 2 because it looks like it will require additional queries\lookups in permission_map table each time I need to check user's permission. When with option 1 it's just a column belonging to the user, makes it much simpler to check the permissions.


texts: How can I get a single row on a inner join not to double my sum

texts:
I have two tables (DEP_DEZ & CAR_EXP):

ID  FK_ID_CHESTIONAR    TOTAL_E TOTAL_F TOTAL_STOC
261 644                 87      152     48

ID  MASA_TOTALA FK_ID_CHESTIONAR
405 20          644
406 45          644

if I make an:

SELECT CHT.ID,
       SUM(DEP_DEZ.TOTAL_E+DEP_DEZ.TOTAL_F+DEP_DEZ.TOTAL_STOC)
       FROM CHEST cht
            INNER JOIN DEP_DEZ
               ON DEP_DEZ.FK_ID_CHESTIONAR = CHT.ID
   GROUP BY CHT.ID

The result is 644 and 287 (87+152+48). And it is ok, but if I make:

SELECT CHT.ID,
       SUM(DEP_DEZ.TOTAL_E+DEP_DEZ.TOTAL_F+DEP_DEZ.TOTAL_STOC),
       SUM(CAR_EXP.MASA_TOTALA),
       FROM CHEST cht
            INNER JOIN DEP_DEZ
               ON DEP_DEZ.FK_ID_CHESTIONAR = CHT.ID
            INNER JOIN CAR_EXP
               ON CAR_EXP.FK_ID_CHESTIONAR = CHT.ID
   GROUP BY CHT.ID

the result is 644 and 574 (287*2 - not ok) and 65 (45+20 - ok)

How can I make not to double my "287" in sum?


texts: How to migrate data between "all-surrogate" databases?

texts:
With some periodicity this needs to be done:

Inserting data from a production database into a test/development database, so that programmers have fresh data to test on.
Migrating data from a database into another, for whatever reason.

Note: databases are relational.

The problem is:

Databases were modeled with a "all-PK-are-surrogate" policy.
Moving data from a database to another involves programming to avoid colision of PK values ( generated by sequences ).
A table pairing the values of the PKs of origin and destination database's tables is needed in order to do the "equivalency" between surrogate keys.
Said table has to be created in advance of migration, by matching business keys (which are not PK, so don't govern FKs)
Data migration even of a single table in not trivial, as opossed to tables with natural keys.

What's the easiest way of copying rows from one database to another when all tables have surrogates keys ?


texts: SQL Server Transaction Log Full affect on FileStream

texts:
These are general question I would like to receive some inputs from experts with BLOB SQL Data.

How will SQL Server Transaction Log (.ldf file) linked to SQL Server FileStream
Is it different from general structured data processing i.e., Log Records , VLFs and write ahead Logging . I mean when the Database is with FileStream will there be any additional T Log processing ? Would like to understand more !
What is the difference between T Log and $FSLOG ?
If SQL Server T Log is full and out of drive space , it affects all Database Engine related transactions including FILESTREAM correct ?
Please explain the flow of a FILESTREAM related Database engine transaction.

Best Regards


texts:
I am attempting to create a Pivot Table in SQL Server 2008, but I am getting an error of

  Incorrect syntax near the keyword 'FOR'

Below is sample DDL and the query I tried to execute - how should this query be changed so that it will execute succesfully?

      Declare @House Table
  (
    studID int
    ,sID int
    ,course varchar(500)
    ,cls int
    ,lv decimal(10,2)
  )

  Insert Into @House (studID, sID, course, cls, lv) VALUES
  (52, 52, 'Remaining Metrics (14th Century)', 31, '12.5')
  ,(52, 52, 'History Of The World (Part I)', 33, '200.0')
  ,(52, 52, 'Singing Socks In The Rain (Part III)', 12, '3.5')
  ,(11, 11, 'Remaining Metrics (14th Century)', 31, '2.5')
  ,(11, 11, 'History Of The World (Part I)', 33, '1.5')
  ,(11, 11, 'Singing Socks In The Rain (Part III)', 12, '2.4')

  Select studID from @House
  PIVOT (lv FOR course IN ([Remaining Metrics (14th Century)],[History Of The World (Part I)], [Singing Socks In The Rain (Part III)])) As Drafts


texts: How to identify which connection is causing error 17886?

texts:
I need a way to identify which connection is causing error 17886. I've researched and there are solutions to fix the error, but only if you know which connection is causing the issue.
I want to log errors so I don't have to sit and wait for it to happen - if it does again. Is there a way to use extended events to track:
Error #17886, connection, time it happened, etc.
Any ideas?


texts: Can I fail the current transaction from within a trigger?

texts:
Table DDL:

CREATE TABLE trigtest(a INTEGER IDENTITY(1,1) PRIMARY KEY, b INT)

I currently have

CREATE TRIGGER MaxDelete ON trigtest INSTEAD OF DELETE AS
IF @@rowcount < 1000
DELETE trigtest  WHERE a IN (SELECT a FROM deleted)

However, it requires me to write the last line in a customized manner for every table I apply it to

Instead of the above, is it possible to write something of the sort:

IF @@rowcount > 1000 (OR SELECT COUNT(*) FROM DELETED)
Kill statement
ELSE
Continue statement


texts: Oracle hierarchical query question (start with ... connect by ... )

texts:
From the Oracle documentation:

  To find the children of a parent row, Oracle evaluates the PRIOR
  expression of the CONNECT BY condition for the parent row and the
  other expression for each row in the table. Rows for which the
  condition is true are the children of the parent. The CONNECT BY
  condition can contain other conditions to further filter the rows
  selected by the query.

When Oracle select child node for a parent node, it selects from all rows except the current row right? Can a row appear multi times in the result set (I mean a row is both the child node of row A and row B)?


texts: Consistency in non-persistent computed columns within transactions

texts:
Is there a way to prevent a non-persistent computed column from updating within a transaction?

I have thrown together an example which shows that these columns can update when using non-deterministic functions (in this instance getutcdate()). Does this not break the consistency requirements of a transaction? I have tried using transaction levels to prevent this (serializable and snapshot) which has not helped.

Query:

set nocount on;

create table dbo.TransactionTest (
    [Timestamp] datetime,
    IsUpdated as case when datediff(second, [Timestamp], getutcdate()) >= 1 then 1 else 0 end
);

insert into dbo.TransactionTest ([Timestamp]) values (getutcdate());

begin tran

select  *
from    dbo.TransactionTest;

waitfor delay '00:00:01';

select  *
from    dbo.TransactionTest;

rollback

drop table dbo.TransactionTest;

Results:

Timestamp               IsUpdated
----------------------- -----------
2017-03-19 00:44:28.130 0

Timestamp               IsUpdated
----------------------- -----------
2017-03-19 00:44:28.130 1


texts: Sql Script takes much longer than estimated

texts:
In order to clean up a very large table holding binary Files in IMAGE Cols, I created the following sequence (based on this: Article) to get rid of a part of those image Blobs quickly:

Select * into temp table where (filtering a small part I want to keep)
Select the rest of rows WITH a NULL value for the BLOB into same temp table.
Truncate the original blob table
copy all records back from the temp table to the original table.

I did a test run on a local machine (notebook) and Step No. one took about 20 seconds for about 2 Milion records.

The whole script took about 4 minutes while the original table held about 2.5 Milion REcords.

In a staging server env. step No. one meanwhile takes > 25 Minutes to copy 3.2 Million records!!! And SSMS actually stopped interacting with me. I estimated (using calc.exe) it might take about 1 Minute...

Now I wonder what is wrong...?

TY


texts: Find time difference in different rows with a condition

texts:
I have a table showing statuses (working, not working) for several devices (ordered by device desc, datetime desc):

rownum device     datetime                  status
--------------------------------------------
1     device_1   2018-06-27 12:10:05.443       1
2     device_1   2018-06-27 12:02:31.467       0
3     device_1   2018-06-27 11:57:24.260       1
4     device_2   2018-06-27 11:57:24.260       1
5     device_2   2018-06-26 12:57:24.260       1
6     device_3   2018-06-26 09:57:24.260       1
7     device_3   2018-06-25 09:57:24.260       0
8     device_3   2018-06-26 09:57:24.260       0
9     device_3   2018-06-24 09:57:24.260       1
10    device_4   2018-06-27 09:57:24.260       0
11    device_4   2018-06-26 09:57:24.260       0

I need to get the amount of time during which a device was not available (status = 0).
So for device_1 it would be time difference between the date it was not working (row 2) and working (row 1).

Device_2 was always working.

Device_3 broke (row 8) and started working (row 6).

Device_4 was never working, so the amount of time would be from (row 11) to (row 10)

Query output table is to be:

device    amountoftimeinmins
----------------------------
device_x    0

I'd appreciate any help.


texts: Does the system process slow down my Database access?

texts:
I have a Fairly large DB containing mostly html documents. I wrote a python script that fetches always 1000 html documents at once from my database cur.execute("SELECT id,url,html_file FROM html ORDER BY id OFFSET %s LIMIT %s;" % (offset, limit)). Afterwards I do a bit of regular expression on the html documents. Because I have a lot html files to go through I track the time how long each step takes. For the first 4000 times retrieving the html documents from the database took around 3 seconds. Now it is up to 4 Minutes. I'm running a Windows 7 machine and took a look at the Resource Monitor. The Database is on its own HDD with nothing else on it. But in the Resource Monitor i could see that the System Process constantly reads something  out of my Postgres Folder

Image   PID File    Read (B/sec)    Write (B/sec)   Total (B/sec)   I/O Priority    Response Time (ms)
System  4   E:\PostgreSQL\data\base\10596207\10598404.1 13,855,193  0   13,855,193  Normal  9
System  4   E:\PostgreSQL\data\base\10596207\10598404.1 11,182,442  0   11,182,442  Normal  9

so the question is is this normal or is the system process the culprit here and how do I stop it? (malware and virus scanning are disabled as well as search indexing)

I followed the advice from Reaces and found that superfetch was already disabled. But I also downloaded the process explorer from sysinternals and there I observed something strange. After about the same amount of parsed html documents (about 4,513,000 fetched in package of a 1,000 from the database) the postgres process begins to write a lot of stuff to the HDD up to this point it did read ~3TB from disk and write ~100mb.

But now about 3000 html documents later the process explorer shows me that the postgress proces did write 10GB to the HDD with heavy write access for every cur.execute("SELECT id,url,html_file FROM html ORDER BY id OFFSET %s LIMIT %s;" % (offset, limit)) command

What is the database doing and how do I stop it? my best guess is that the database orders it self to be more efficient the next time but that does not help me now.


texts: Job completes successfully and logs the errors to a text file

texts: I am using Ola's scripts for our database backups, I am trying to come up with a way to be notified if any of the database backup fails. Job completes successfully and logs the errors to text file, Can we get a  notification\alert that it found errors.


texts: Why does DBCC CHECKTABLE WITH TABLERESULTS only report Error 8990 on corrupt tables?

texts:
I have a production VLDB that I'm using Paul Randal's guidance on to spread DBCC CHECKDB workload over a week. I'm logging the results of DBCC CHECKTABLE WITH TABLERESULTS and DBCC CHECKALLOC WITH TABLERESULTS to a table an noticed DBCC CHECKTABLE WITH TABLERESULTS was only returning a single row per table on non-corrupt tables. Here's the first few columns.

Error   Level   State   MessageText
2593    10      1       There are 57 rows in 1 pages for object "SmallTable".

But when I run DBCC CHECKTABLE WITH TABLERESULTS on a corrupt table in a test database, which I corrupted with this method, I get the following:

Error   Level   State   MessageText
8939    16      98      Table error: Object ID 565577053, index ID 1, partition ID 72057594041335808, alloc unit ID 72057594046971904 (type In-row data), page (1:312). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
8928    16      1       Object ID 565577053, index ID 1, partition ID 72057594041335808, alloc unit ID 72057594046971904 (type In-row data): Page (1:312) could not be processed.  See other errors for details.
8980    16      1       Table error: Object ID 565577053, index ID 1, partition ID 72057594041335808, alloc unit ID 72057594046971904 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:312) and previous child (0:0), but they were not encountered.
2593    10      1       There are 0 rows in 0 pages for object "tblWhoops".
8990    10      1       CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'tblWhoops' (object ID 565577053).

I'm curious as to why Error 8990 ins't returned in with the non-corrupt table? DBCC CHECALLOC WITH TABLERESULTS and DBCC CHECKDB WITH TABLERESULTS return this row when there aren't any allocation or consistency errors to report.

Bonus question, why doesn't DBCC CHECKCATALOG have a WITH TABLERESULT arguement?