texts: Comparing multiple tables in Sqlite 3 using python:

texts:
I am quite new to SQLITE3 as well as python. I am working on a project where I have one database with about 20 tables inside of it. One table is for user input and the other tables are pre-loaded with values. I want to be able to compare and match which values are in the pre-loaded tables with the user table. For example: Here is a link to the example tables

Now, I want to be able to compare each row in the Pre-Loaded Table to each row in the Users Table. They both have the Barcode column in common to be able to compare. As a result, during the query process, it should check each row: 1234 - milk - 1 (those columns are equal ) 5678 - butter - 1 ( those columns are equal) 9876 - sugar - 1 (those columns are equal) 1100 - - 1 ( this barcode does not exist in the Users Table)

so when a Barcode, in this case, 1100 doesn't exist in the Users Table, the code should print: You don't have all the items for the Pre-Loaded Table.

so far I have this:

import sqlite3 as sq

connect = sq.connect('Food_Data.db')
con = connect.cursor()

sql = ("SELECT Users_Food.Barcode, Users_Food.Item,     Recipe1.Ham_Swiss_Omelet FROM Users_Food INNER JOIN Recipe1 ON Users_Food.Barcode = Recipe1.Barcode WHERE Recipe1.Ham_Swiss_Omelet = '1'")
con.execute(sql)
data = con.fetchall()
print("You can make: Ham Swiss Omelet")
formatted_row = '{:<10} {:<9} {:>9} '
print(formatted_row.format("Barcode", "Ingredients", "Availability"))
for row in data:
    print(formatted_row.format(*row))
    #print (row[:])
    #connect.commit()


texts: Keeping my database updated every 30 minutes using Log files

texts: I have a situation where I get a full nightly backup and trans log backup every 30minutes starts at 7:00 am ends at 2:00 am. I need to restore them every 30 minutes into a database and replicate this data into another database, so every next 30 minutes I should be able to restore the log files and keep the database in usable state to replicate this data to another database. I think we cannot use the database while restoring log files, but is there any process to deal with this kind of situation?


texts: How to recover deleted rows data table in SQL Server without backup?

texts:
I accidentally deleted all data rows from a important table. But unfortunately, the last backup of the database was done last week.
I have tried several ways, namely by looking at the transaction id and returning it with a query. but from the review I can also be more dangerous because it can eliminate some data
How can I restore the data that I deleted?


texts:
I want to use Apache Cassandra to store a large amount of graph data according to a property graph model. The model contains the following entities:

Vertices: Contains a map of key/value pairs (properties). Some keys should be indexed for querying (see below).
Edges: Connects two vertices to each other in a given direction. Contains a label and possibly some edge data. The edge data is a map of key/value pairs, where some keys should also be indexed for querying.

Both vertices and edges have a unique primary key, which can be a string or integer value.

Example:

#A vertex
{node_type:'module',pk: 1,...}
#Another vertex
{node_type:'function',pk: 2,...}

#An edge
{incoming_vertex: 1,outgoing_vertex: 2,label: 'body',data : {}}

I want to perform the following types of queries on the graph:

Retrieve a list of vertices based on their primary key (e.g. "fetch the vertex with pk = a5f...") or the value of one or several indexed properties (e.g. "fetch all vertices with node_type = 'module' and ...").
Traverse the graph from a given vertex along its edges, using the edge label, direction and one or several indexed edge properties to determine the path taken (e.g. "fetch all vertices that are connected to vertex A through an outgoing edge with label body and property ... = ...).

In addition, I have the following requirements and boundary conditions:

Retrieving the list of edges for a given vertex should be as efficient as possible (O(1) ideally).
The number of edges will be much larger than the number of vertices in the graph.
The model should scale to several billion vertices and several hundred billion edges (appropriate hardware provided).
The graph data will usually be written only once and read many times, so the model can be optimized for query performance at the cost of write performance.

My initial idea for a data model is the following:

Use one column-family for vertices as well as edges respectively, where the row-key is the primary key of the vertex/edge and a single text column contains its JSON data. Indexes on vertex/edge properties are modeled as additional columns (whose data is denormalized and manually updated whenever the vertex/edge data changes)
Use one dynamic column family for managing the adjacency (edge) list for vertices, with a composite primary key that contains the primary key of the vertex, the primary key of the edge, the edge label and the edge direction (incoming or outgoing) for each vertex.

Is this a sensible data model? Any other suggestions on how to implement this?


texts: Statistical probability of an incomplete transaction committed by Postgres

texts:
tldr

After spending time with bad databases which I will not mention, I came to question even the point of a database.

I knew that they could serve a purpose to keep permanent data organized, but that was it.

After delving into Postgres with the great help of especially Craig Ringer & Erwin Brandstetter, I've now come to see what a database could and should be:

Convenient syntax
Customizable storage methods for performance relative to persistence
Organized writes and even reads through serialization
Confidence that writes should never be incomplete

...and so on.

The point

The crash course provided has made me just enough knowledgeable on 1) - 3) to be dangerous, but I know no details about 4).

It seems like well-written database software like Postgres are designed to ensure that a transaction will never commit incomplete data.

Speaking out of ignorance, nothing is perfect, so in that vein, how statistically likely is it that Postgres will incompletely commit a transaction and not be able to detect the malfunction?


texts:
I have a very weird situation.

One of our SQL Servers has a software update notification pending for Microsoft SQL Server 2008 R2 Service Pack 1 (KB2528583). However, this server is already at SP2. Here is the result of the @@Version query:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64).

If the SQL Server is already at SP2 why do I have an SP1 update notification?
Is there a way to ignore this update notification for just this one machine?


texts: Postgres could not receive data from client: Connection timed out

texts:
Am a sql DBA, learning postgres..In postgres logs i often receive "could not receive data from client: Connection timed out" I do not have any other logs other than this

Am not sure how to troubleshoot? I check the application logs and DBs logs to compare the time but I could not notice any abnormalities.

can someone guide me how to trace this issue.

Thanks


texts: Select only databases with a certain table name and specific columns

texts:
So my first query is this one:

SELECT *
FROM information_schema.columns
WHERE table_name='logsys' and
column_name in
('CD_LOG'
,'NM_USUARIO'
,'DT_ACAO'
,'DS_HISTORICO'
,'NOME_MAQUINA'
,'USUARIO_MAQUINA'
,'DATA_REFERENCIA'
,'ID_SISTEMA'
,'VERSAO'
,'LOCAL'
,'ACAO'
,'ID_LOG'
)

Then to run it in all databases, I made this:

DECLARE @command varchar(1000)
SELECT @command =

   'use [?] SELECT
   DB_NAME() as DatabaseName, *
FROM information_schema.columns
WHERE table_name=''logsys'' and  column_name in
(''CD_LOG'',''NM_USUARIO'',''DT_ACAO'',''DS_HISTORICO'',''NOME_MAQUINA'',''USUARIO_MAQUINA'',''DATA_REFERENCIA''
,''ID_SISTEMA'',''VERSAO'',''LOCAL'',''ACAO'',''ID_LOG'')'

EXEC sp_MSforeachdb @command

But I'm just confused about something. I want THESE specific columns. I want tables with all these columns. not more, not less. but with in, obviously it lists tabes that have at least one of these columns. I'm confused about this simple query. How can I use it, in all databases, to find only those tables that has all those columns?

Should I use and column = x, and column= y.... and so on?

And a bonus question. How can I insert foreachdb inside a table?

EDIT1:

Thanks for answers, for now, I have this:

--CRIA TABELA DO INSERT
CREATE TABLE TB_DB_LOGSYS
    (
        DATABASENAME VARCHAR(200),
        TABLE_CATALOG VARCHAR(200),
        TABLE_NAME VARCHAR(200),
        COLUMN_NAME VARCHAR(200),
        DATA_TYPE VARCHAR(100)
    )
GO
---------------------------------------------------------------------------
--PROCURA AS LOGSYS QUE POSSUEM 12 COLUNAS
TRUNCATE TABLE TB_DB_LOGSYS
DECLARE @command varchar(1000)
SELECT @command =

   'use [?] SELECT
   DB_NAME() as DatabaseName,  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.columns
WHERE table_name=''logsys'' and  column_name in
(''CD_LOG'',''NM_USUARIO'',''DT_ACAO'',''DS_HISTORICO'',''NOME_MAQUINA'',''USUARIO_MAQUINA'',''DATA_REFERENCIA''
,''ID_SISTEMA'',''VERSAO'',''LOCAL'',''ACAO'',''ID_LOG'')'

--INSERE O FOREACHDB NA TABELA
INSERT INTO TB_DB_LOGSYS
EXEC sp_MSforeachdb @command
---------------------------------------------------------------------------
SELECT * FROM TB_DB_LOGSYS

SELECT DATABASENAME, COUNT(COLUMN_NAME) AS [COUNT] FROM TB_DB_LOGSYS
GROUP BY DATABASENAME
HAVING COUNT(COLUMN_NAME)=12

but using having is not what I really want. maybe there's another column, and not the one I'm looking for.


texts: Examples of SQL transaction procedures for sales tracking or a financial database

texts:
I am making a database for an accounting/sales type system similar to a car sales database and would like to make some transactions for the following real world actions:

salesman creates new product shipped onto floor (itempk, car make, year, price).
salesman changes price.
salesman creates sale entry for product sold (salespk, itemforeignkey, price sold, salesman).
salesman cancels item for removed product.
salesman cancels sale for cancelled sale.

The examples I have found online are too generic, like "this is a transaction", I would like something resembling what I am trying to do to understand it.

Anybody have some good similar or related sql examples I can look at to design these? Do people use transactions for sales databases? Or if you have done this kind of sql transaction before could you make an outline for how these could be made?

My (closed as not a real question) thread so far on stack overflow: Need example SQL transaction procedures for sales tracking or financial database

Latest update, user will send new inputs/ changes /and cancellations from a c# application.
Application data:

Products On Display (this is the parent node which has 3 child nodes)
Sales(child node of Products On Display)
Product Custom Features(child node of Products On Display)
Product Price / current status (child node of Products On Display)

C# App will package that data into XML format and then execute some SQL stored procedures with transactions holding together the xml to table conversions into the SQL Tables designed with the same parent/child node structure using something like what is described by the answers to this related question on Stack Overflow:
https://stackoverflow.com/q/2756773/613799

I wish there was a book on designing multi-user sales databases, and the stored procedure transactions that will be used by the related user apps from scratch  app->xml->database. Please let me know if you know of a good one, or a chapter of a book.


texts: Recover SA Password - PSTools - Not working

texts:
I'm on a new company, and the first thing I saw is that they don't even know how many instances they have. they don't have passwords and the old DBA had no Idea what he was doing.

long story short, I could manage to create my own sysadmin account in almost every server ( I will move all databases to only 1 or 2 instances ) but there are some that I just can't.

In this Link by mssqltips there is a tool called PsExec. Following the tutorial I could run the program and connect to SQL Server with NT AUTHORITY\SYSTEM. But it's not working. NT AUTHORITY\SYSTEM is Public and I can't add my account with sysadmin. Does this program runs only with SQL 2008?

There's no way to stop servers because our databases aew running for all over Brazil.

Countless posts here, but almost everyone needs to restart SQL Server.

How do you reset the sa password?

How to add sysadmin to user in SQL Server 2008 when no sysadmin accounts exist ( This one says about the program, but as I said, it's not working for me (he is using 2008 )).

In this video  he's using the -M method. Does someone already tested it on SQL Server 2014? I would like to know and, I will try.

I can connect with the NT AUTHORITY\SYSTEM user  but when I try to create a sysadmin login or change sa password, it says I have no rights to do that. And then, looking at the nt rights, it's public. I could change the sa login with the -m trick on my machine, but I would like something that does not require to shutdown the SQL.


texts: How can I determine what table a foreign key is used in without a bunch of joins?

texts:
I have a few tables as detailed below:

Page:
page_id (PK), createdDateTime

Entry:
page_id (FK), entry_id (PK), order, parent, createdDateTime

Entry_(type)
entry_id (FK, PK), value ((, other columns as needed depending on the type))

Each page can have multiple entries that reference it, but each entry can only be referenced by one other table (Entry_(type), like Entry_text). Considering I plan on having at least 5 different Entry_(type) tables, it seems fairly inefficient to have a JOIN for each table, since some pages may only use one Entry_(type), while others could use dozens in the future.

How can I tackle this? I'm using Postgresql 9.6.1, Symfony, and PHP 7. Sorry if this is a fairly simple question, however after a few hours of searching, not ever using Postgres, and not having worked with db design in a few years - I needed some help.


texts:
Hi guys I have a query which is pulling data from database :

SELECT sandwich_table.id,
       `sandwich_name`,
       salad,
       COUNT(salad),
       salad_type.type,
       sandwich_type_table.type
FROM   `sandwich_table`
       LEFT JOIN urls
         ON sandwich_table.id = urls.sandwich_id
       LEFT JOIN salad_type
         ON urls.salad = salad_type.id
       LEFT JOIN sandwich_type_table
         ON `sandwich_table`.`sandwich_type` = sandwich_type_table.id
WHERE  `active` = 1
GROUP  BY sandwich_table.`id`,
          salad

Some of the data in salad_type.id is not represented in the COUNT(), as some types have not been established in the url table for example:

salad_type.id : 1,3 and 4 are in the url table but not 2.

I would like create a query to show all types whether there is a value or not? as this query only pulls the salad_type.id when it is established in the url table.

AS requested the table structures:

 CREATE TABLE `salad_type`
(
    `id` int(50) unsigned NOT NULL auto_increment Primary Key,
    `type` varchar(255),
    `name` int(10) default NULL,
    `date` int(10) default NULL
);
CREATE TABLE `urls`
    (
        id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `url` varchar(255) UNIQUE,
        `sandwich_id` int,
        `salad` int,
        `date_added` int,
        `username` int
    )
CREATE TABLE `sandwich_table`
    (
        id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `sandwich_name` varchar(255) UNIQUE,
        `active` int,
        `sandwich_type` int,
        `username` int
    )


texts: Is there a way to decrease the size of some indexes by trading off some precision?

texts:
I have a table that has a number of 32-bit int IDs that are used as foreign keys.

The actual cardinality is under a million unique values.

I want to reduce the size of these indexes so that they can all fit in a certain amount of RAM.

Is there a way I can tell postgres to not bother indexing every single value, and perhaps index only the lower 16 bits?

With a bit of testing, I noticed that a 16-bit index would be about 32% smaller.

I know I could create some expression indexes and modify my queries, but is there a way to do this transparently?

I already looked into BLOOM and BRIN indexes but didn't find a solution.


texts: Update excel sheets from SQL Server 2005 linked servers

texts:
I use SQL Server linked servers to connect to Excel 2003 multiple tabs workbook. After connected, I have not problem to use select statement like:

select * FROM OPENQUERY(TEST, 'select * from [fist day$]')

but I can not use normal delete and insert statement. I want to delete data from first row and insert one row of data into the last row to the excel spreadsheet.

I use:

delete top(1) FROM OPENQUERY(TEST, 'select * from [first day$]')

and I get

  OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST"
  returned message "Deleting data in a linked table is not supported by
  this ISAM.".
  Msg 7345, Level 16, State 1, Line 1
  The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST" could not delete
  from table "select * from [first day$]". There was a recoverable,
  provider-specific error, such as an RPC failure.


texts: Query notifications and connection options

texts:
I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition.

.NET errors show a problem reported from SqlNotificationInfo Enumeration (http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlnotificationinfo(v=vs.80).aspx ) as: Event Reason: Isolation.

During tracing I notice that the audit login event immediately preceding the Broker conversation shows:

 -- network protocol: TCP/IP
    set quoted_identifier on
    set arithabort off
    set numeric_roundabort off
    set ansi_warnings on
    set ansi_padding on
    set ansi_nulls on
    set concat_null_yields_null on
    set cursor_close_on_commit off
    set implicit_transactions off
    set language us_english
    set dateformat mdy
    set datefirst 7
    set transaction isolation level read uncommitted

So, my working theory is that the transaction isolation level used for the conversation is not supported. But where is this set and how can I change it?

I've read about a similar problem here:

http://www.roelvanlisdonk.nl/?p=3144
and

http://www.sqlskills.com/blogs/bobb/watch-the-sqlnotificationinfo-for-query-notifications/


texts: Can I cluster PostgreSQL by sharing a data directory over NFS?

texts: I created a PostgreSQL cluster in one host and shared it over NFS share to another host. Even though i can access the cluster using second host but can not use or log in to databases. Is it possible to use a single cluster by multiple hosts? so i can introduce it to clients as a single host and in case the first host that was used to create the cluster is down, the other host keeps the cluster running so clients would not face any connectivity or accessibility issue to that cluster.


texts: Have: Postgres database files. Need: Functioning database

texts:
I am running Ubuntu 14.04 with a postgres/postgis setup and in a weak moment removed PostgreSQL 9.1 (seemingly a legacy install from 12.04 from before I upgraded to 14.04), having erroneously convinced myself my current postgis setup relied on 9.3 alone.

That was not the case, and to make matters worse, I removed it while the instance was running, so I headed to the repositories to restore 9.1.

The repositories for Ubuntu don't have PostgreSQL 9.1 for 14.04, so I instead compiled from source so I could run pg_dump or pg_upgrade, which requires binaries from both.

But my issue then was that just because I now had a working version of 9.1 again, that didn't make my databases from earlier visible.

So, as far as I am aware, my database files are intact, but I need to 'hook' them up with the new 9.1 service I installed today. I hope it is trivial, but it has stumped me for hours. Any pointers for where to start with updating the config?


texts: How can I quantify the business value of normalization?

texts:
My company is developing an internal application with a SQL server database which was started earlier by another developer.
Now I am Looking at the model,the developer has stopped at 1NF. I proposed going all the way to 3NF but my manager said it would add no business value.
Is there a way to quantify the business values of normalization, specifically going to 3NF?


texts: Data replication between SQL Server 2016 and SQL Server 2008 R2 is valid?

texts:
We have two servers with two SQL Server instance. we have plan to make first one SQL Server 2016 for production database and the second one SQL Sever 2008 R2 for the same database as reporting side.

Can we create a replication solution between SQL Server 2016 & 2008 R2? If not: is there another solution to migrate updated data between these two instances?


texts: SQL Server in a state of confusion: does the database exist or doesn't it?

texts:
Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted.

First, we noticed that the database we created yesterday was missing. So, we looked around and found that it was still there, but detached. So, we tried to attach the mdf but got a message which was something like The file is currently in use.

I thought that was odd, so restarted SQL Server. Same thing... okay, time for drastic measures... so, I stopped the service, zipped up the mdf, started the service, unzipped it and then tried to restore it. The above message was gone, but then I got:

  Cannot attach a database with the same name as an existing database

Ouch. Of course it's not showing in the database explorer, so no idea what's going on... last resort:

DROP DATABASE [DatabaseName]

Of course that didn't work.. that tells me the database does not exist. So, I'm stuck... at one point SQL Server thinks the database does exist and at another point it thinks the db does not exist.. obviously it's in a state of confusion.

Has anyone seen this before? Got any ideas on how to fix it?


texts: For a complex query how do i increase the time SQL Server searches for the best execution plan

texts: Our system generates SQL with a very large number of tests. I suspect on some occasions it is taking too long to find the ideal execution plan. So SQL just picks the best plan it has found so far which then takes a long time to run. Is it possible to increase the time SQL searches for the best plan?


texts: Inventory database structure when inventory items have varying attributes

texts:
I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using device serial numbers as the primary key. The problem I'm having is that the other attributes for these devices vary and I don't want to have fields in the inventory table that are unrelated to other devices. Below is a link to an ERD of part of the database (some FK relations are not shown). I'm trying to set it up, for example, so a device with a workstation device type can't be put into the phones table. This seems to require the use of a lot of triggers to validate the device type or class, and new tables anytime a different device with different attributes will be tracked; not to mention all of the one-to-one relationships which will make joins a nightmare (there are more one-to-one relationships not shown).

I looked into setting up attribute tables that can be mapped to serial numbers, but that would allow attributes that do not apply to a device type to be assigned to a device, e.g., someone could assign a phone number attribute to a workstation if they wanted. I found an explanation on this site that gave the following structure:

This structure would work great if the attributes were all applicable to the items I am storing. For example if the database was storing only mobile phones, the attributes could be things like touchscreen, trackpad, keyboard, 4G, 3G...whatever. In that case, they all apply to phones. My database would have attributes like hostname, circuitType, phoneNumber, which only apply to specific types of devices.

I want to set it up so only the attributes that apply to a given device type can be assigned to a device of that type. Any suggestions on how to setup this database? I'm not sure if this is a proper use of one-to-one relationships, or if there is a better way to do this. Thank you in advance for taking the time to look into this.

Here are some of the other threads I read. They gave me some good insight, but I don't think they really apply:

https://stackoverflow.com/questions/9335548/how-to-structure-database-for-inventory-of-unlike-items

https://stackoverflow.com/questions/1249632/database-structure-for-items-with-varying-attributes

https://stackoverflow.com/questions/5559587/product-inventory-with-multiple-attributes

https://stackoverflow.com/questions/6613802/question-about-setting-up-inventory-database

https://stackoverflow.com/questions/514111/how-to-best-represent-items-with-variable-of-attributes-in-a-database


texts: TDE Performance and Cost Threshold for Parallelism

texts:
Is there any documentation, test data, or other evidence to suggest that the Cost Threshold for Parallelism (CTP) setting might impact performance when TDE is enabled on a database?  Without going into all of the details, we have seen some significant performance degradation with benchmark tests after enabling TDE on a database, and we are looking for any settings that might have an impact.  CTP is currently set to the default of 5, which I know is not ideal, so please resist the urge chastise me for it.

We will also add CPUs and test further, but if we can get better performance by changing CTP or another setting, that would of course be better than adding CPUs (due to the cost of the hardware, as well as SQL Server licensing per CPU).

I am pushing to change the CTP and test again, but I was hoping to see if anyone has already tested it so that we can prioritize the changes we want to test accordingly.

Thanks!


texts:
I have column document of data type json with logging data like:

CREATE TABLE foo(document)
AS
  VALUES (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288960": {
            "first_name": "First name"
        }
      }
     }'::jsonb
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288969": {
            "first_name": "Joe ZZZ"
        }
      }
     }'
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__2288169": {
            "first_name": "AAA ZZZ"
        }
      }
     }'
  ), (
    '{
      "blocks": {
        "participant-information_primary__Primary__39011__1288169": {
            "first_name": "kkk"
        }
      }
     }'
  );

Key name 'participant-information_primary__Primary__39011__2288960' is dynamic and can vary by the trailing integer number. I want to do something like:

ORDER BY document->'blocks'->'participant-information_primary*'->>'first_name'

How can I do it?


texts:
I'm using barman to take backups from postgresql. This is the result of list-backup:

prod 20190831T000005 - Sat Aug 31 00:04:58 2019 - Size: 16.0 GiB - WAL Size: 654.2 MiB
prod 20190830T000005 - Fri Aug 30 00:04:41 2019 - Size: 16.1 GiB - WAL Size: 2.1 GiB
prod 20190829T000004 - Thu Aug 29 00:04:10 2019 - Size: 16.0 GiB - WAL Size: 1.3 GiB
prod 20190828T000004 - Wed Aug 28 00:03:21 2019 - Size: 15.9 GiB - WAL Size: 872.6 MiB
prod 20190827T204148 - Tue Aug 27 20:48:11 2019 - Size: 15.9 GiB - WAL Size: 129.3 MiB

As you can see, there is a huge variance in the size of WALs reported here. What is the reason? And is it ok?

barman 2.9
postgresql 9.6


texts: How are precision and scale calculated when decimals are multiplied?

texts:
When decimals are divided, the precision and the scale of the result value are calculated due to this formula:

  max precision = (p1 - s1 + s2) + MAX(6, s1 + p2 + 1) -- up to 38

  max scale = MAX(6, s1 + p2 + 1)

What's the formula when decimals are multiplied?

For example:

IF OBJECT_ID('tempdb..#DataSource') IS NOT NULL
BEGIN
    DROP TABLE #DataSource;
END;

SELECT CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) AS C1
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * 100 AS C2
      ,CAST(1 AS DECIMAL(9,0)) / CAST(2 AS DECIMAL(9,0)) * CAST(100  AS DECIMAL (3, 0)) AS C3
INTO #DataSource;

EXEC tempdb.dbo.sp_help '#DataSource';

The precision and the scale of column C1 are calculated based on the specified formula:

p1 = 9
p2 = 9
s1 = 0
s2 = 0

max precision = (9 - 0 + 0) + MAX(6, 0 + 9 + 1) --> 19
max scale = MAX(6, 0 + 9 + 1) --> 10

When this result is multiplied by 100 (the value 100 is converted to DECIMAL(3,0)), the result has precision is 23 and scale 10.

If the same formula is applied, the values are as follows:

p1 = 19
p2 = 3
s1 = 10
s2 = 0

max precision = (19 - 10 + 0) + MAX(6, 10 + 3 + 1) --> 9 + 14 = 23
max scale = MAX(6, 10 + 3 + 1) --> 14

So, the precision is correct, but the scale is bigger - 14 instead 10.

Could anyone tell what the formula is for calculating precision and scale when decimals are multiplied?


texts: Does a numeric field always use the same disk space?

texts: When you have a numeric field in MySQL(MYISAM) (E.g. Integer) will it always use the same disk space, no matter what number is actually stored?


texts: Validation within a transaction with all violations in response

texts:
I'm trying to solve a problem with some sort of offline integration where data occasionally (once in several days) comes in a form of csv files. It can be new rows or updates for existing ones. Total amount can be around 1-2 million rows. And I need to guarantee the data consistency and referential integrity for every upload. And I need to provide a feedback about mistakes in incoming files.

I can do relatively memory efiecent check per row within the app that all required fields are provided and have expected types, but I want to do referential integrity in the DB.

So far I came up with following approach to do it within the transaction.

As example, the schema is:

CREATE TABLE IF NOT EXISTS sales_invoices(
    invoice_number TEXT PRIMARY KEY,
    billing_name TEXT
);

CREATE TABLE IF NOT EXISTS sales_invoice_lines(
    sales_invoice_line_id TEXT PRIMARY KEY,
    sales_invoice_fk TEXT NOT NULL,

    CONSTRAINT si_lines FOREIGN KEY (sales_invoice_fk) REFERENCES sales_invoices(invoice_number) DEFERRABLE
);

And the process is controlled from the app as follows:

START TRANSACTION; -- the app start transaction with
SET CONSTRAINTS ALL DEFERRED;

-- App does the batch insert for one table like
INSERT INTO sales_invoices (invoice_number, billing_name)
(SELECT r.invoice_number, r.billing_name
 FROM unnest($1::sales_invoices[]) as r)
ON CONFLICT (invoice_number)
DO UPDATE SET billing_name = EXCLUDED.billing_name;

-- And the batch insert for another table
INSERT INTO sales_invoice_lines (sales_invoice_line_id, sales_invoice_fk)
(SELECT r.sales_invoice_line_id, r.sales_invoice_fk
 FROM unnest($1::sales_invoice_lines[]) as r)
ON CONFLICT (sales_invoice_line_id)
DO UPDATE SET sales_invoice_fk = EXCLUDED.sales_invoice_fk;

-- And then select all violations like
SELECT * FROM sales_invoice_lines sil
LEFT JOIN sales_invoices si ON (sil.sales_invoice_fk = si.invoice_number)
WHERE si.invoice_number is NULL;

-- If something is fetched then the app calls explicit rollback
-- and returns the data as the report further;
-- Commit otherwise

So my question is if there a way to do it in more optimal way as it's a trade off from faster transactions. And/or what are ways to optimise the current approach?

I'm in the middle of thinking to denormalise the schema and bring say a company/org unit id to use for table partitioning if it's going to grow to something big.
And the target DB is Postgres 9.6.


texts: Mysqld process not showing all options on command line in CentOs 7

texts:
Mysqld process showed all config file options on command line in CentOs 5 and CentOs 6. But it's not showing the same in CentOs 7. I am using mysql 5.7.21 on CentOs 7 example stated below and 5.7.19 on CentOs 6 example stated below.

centos 6:

mysql@xxxxxxxx ==> ps -ef | grep 3306
mysql    23084     1  0 Jan16 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --socket=/datadata/mysql.sock --port=3306 --max_allowed_packet=100M --datadir=/datadata --pid-file=/datadata/mysqld-qa.pid --max_connections=500 --open-files-limit=8192 --thread_cache_size=64 --delayed_insert_limit=4096 --query_cache_limit=1M --query_cache_size=32M --query_cache_type=1 --log_queries_not_using_indexes=off --log-error=/datalogs/mysql-error.log --expire_logs_days=3 --sort_buffer_size=2M --join_buffer_size=4M --read_buffer_size=2M --read_rnd_buffer_size=2M --tmp_table_size=64M --max_heap_table_size=64M --lower_case_table_names=0 --myisam_sort_buffer_size=16M --innodb_file_per_table --innodb_buffer_pool_size=1G --innodb_open_files=600 --innodb_max_dirty_pages_pct=10 --innodb_flush_log_at_trx_commit=0 --innodb_log_file_size=64M --innodb_log_buffer_size=4M --innodb_log_files_in_group=3 --innodb_flush_method=O_DIRECT --binlog_cache_size=64K --transaction_isolation=REPEATABLE-READ --server-id=3664 --log-bin=mysqldb01.bin --log-slave-updates --binlog_format=statement --sysdate-is-now=1 --sync-binlog=1 --sync-master-info=1 --log-queries-not-using-indexes=1 --innodb_buffer_pool_instances=5 --innodb_concurrency_tickets=1000000 --auto_increment_increment=10 --auto_increment_offset=2 --binlog_checksum=none --sql_mode=STRICT_ALL_TABLES --explicit_defaults_for_timestamp --open_files_limit=10000 --max_connections=4000 --table_open_cache=400 --binlog_format=statement --log_timestamps=SYSTEM --explicit_defaults_for_timestamp --master_info_repository=table --relay_log_info_repository=table --relay-log=dbtemp02-relay-bin --mysqld_safe_log_timestamps=SYSTEM --slave_net_timeout=3600 --default_password_lifetime=0 --relay_log_recovery=ON
mysql    27230 23084  0 Jan16 ?        00:32:37 /usr/sbin/mysqld --basedir=/usr --datadir=/datadata --plugin-dir=/usr/lib64/mysql/plugin --max-allowed-packet=100M --max-connections=500 --thread-cache-size=64 --delayed-insert-limit=4096 --query-cache-limit=1M --query-cache-size=32M --query-cache-type=1 --log-queries-not-using-indexes=off --expire-logs-days=3 --sort-buffer-size=2M --join-buffer-size=4M --read-buffer-size=2M --read-rnd-buffer-size=2M --tmp-table-size=64M --max-heap-table-size=64M --lower-case-table-names=0 --myisam-sort-buffer-size=16M --innodb-file-per-table --innodb-buffer-pool-size=1G --innodb-open-files=600 --innodb-max-dirty-pages-pct=10 --innodb-flush-log-at-trx-commit=0 --innodb-log-file-size=64M --innodb-log-buffer-size=4M --innodb-log-files-in-group=3 --innodb-flush-method=O_DIRECT --binlog-cache-size=64K --transaction-isolation=REPEATABLE-READ --server-id=3664 --log-bin=mysqldb01.bin --log-slave-updates --binlog-format=statement --sysdate-is-now=1 --sync-binlog=1 --sync-master-info=1 --log-queries-not-using-indexes=1 --innodb-buffer-pool-instances=5 --innodb-concurrency-tickets=1000000 --auto-increment-increment=10 --auto-increment-offset=2 --binlog-checksum=none --sql-mode=STRICT_ALL_TABLES --explicit-defaults-for-timestamp --max-connections=4000 --table-open-cache=400 --binlog-format=statement --log-timestamps=SYSTEM --explicit-defaults-for-timestamp --master-info-repository=table --relay-log-info-repository=table --relay-log=dbtemp02-relay-bin --slave-net-timeout=3600 --default-password-lifetime=0 --relay-log-recovery=ON --log-error=/datalogs/mysql-error.log --open-files-limit=10000 --pid-file=/datadata/mysqld-qa.pid --socket=/datadata/mysql.sock --port=3306

centos 7:

[mysql@xxxxxx binlogs]$ ps -ef | grep mysql
mysql    16343     1 44 00:42 ?        00:53:00 /usr/sbin/mysqld --defaults-group-suffix=@0 --daemonize --pid-file=/var/run/mysqld/mysqld-0.pid

Any idea how to display all variables in CentOs 7.


texts:
Does 'LGWR' writes to online redo log files strictly in circular fashion? I saw this sentence from Oracle documentation.

  If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

Does that means some redo log files could be skipped when LGWR writes to them?

Thanks.


texts: Oracle PL-SQL "Regular expression" to replace each "(space)and(space)" with ',' in a string

texts:
I have a string like this x=y and g=h and u=1 and I need to replace each (space)and(space) with ,. I'm using the powerful regular_expression for this but it does not give me the desired result.

select regexp_replace('x=y and g=h and u=1','(^[[:space:]]*)AND(^[[:space:]]*)', ',')
from dual;

I was wondering if you could help me out here. thanks in advance.


texts: How to insert values into a table with default values from a select query in PostgreSQL?

texts:
Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null?

In my case, the SELECT statement is selecting from JSON.

In the attempts below, I was successful by explicitly retrieving the sequence but I am hoping there is a way to either INSERT using the DEFAULT values. Or select DEFAULT values without having to call the default functions explicitly.

-- Example table
create table animals
(
    id serial,
    nm character varying (30) NOT NULL, --name
    typ character varying(10),
    tvi integer,
    tvf numeric(8,3)
);

insert into animals VALUES (DEFAULT,'mouse','m',4,12.45);

select row_to_json(a) from animals a;

select * from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');

--All good.

-- Attempt #1
INSERT INTO animals
select id ,nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');

/*
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, mouse, m, 4, 12.450).
********** Error **********

ERROR: null value in column "id" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, mouse, m, 4, 12.450).
*/

-- Attempt #2
INSERT INTO animals
select DEFAULT,nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');

/*  I didn't  expect this to work, but it does illustrate what I am trying to accomplish
ERROR:  syntax error at or near "DEFAULT"
LINE 2: select DEFAULT,nm,typ,tvi,tvf from json_populate_record(null...
               ^
********** Error **********

ERROR: syntax error at or near "DEFAULT"
SQL state: 42601
Character: 28

*/

-- Attempt #3
INSERT INTO animals
select nextval('animals_id_seq'::regclass),nm,typ,tvi,tvf from json_populate_record(null::animals,'{"id":null,"nm":"mouse","typ":"m","tvi":4,"tvf":12.450}');

/*  This works, but I'm hoping for a way to accomplish this without knowing the underlying functions generating the default values.
Query returned successfully: one row affected, 11 msec execution time.
*/

select version();  --'PostgreSQL 9.5.1, compiled by Visual C++ build 1800, 64-bit'


texts:
A few times a day, it is not able to connect and the error mentions:

  Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.

There is is large Microsoft article that suggests: To resolve this issue, upgrade the .NET Framework 4.5.1 to the Microsoft .NET Framework 4.5.2

Our SQL Server 2012 is running on VMWare and we have not been able to get much help from them even though we provided a lot of info that points to their implementation of the vsocklib.dll being a non-IFS Winsock.

Have you guys ever had to deal with that particular scenario ? Has 4.5.2 this fixed the problem ?


texts: How do I find the FamilyGUID of an exsting database

texts:
When executing a RESTORE command, I understand that SQL Server ensures that an existing database is not being overwritten by a different database.  This check can be bypassed using REPLACE.

I understand that SQL Server uses the backup file's FamilyGUID to determine whether the database being restored is the same as the database being overwritten.  Is this correct?

I understand that the FamilyGUID of the backup file can be determined using

RESTORE headeronly FROM DISK = N'Q:\MyBackup.bak'

But how can I find the FamilyGUID of the database that is being overwritten?


texts: Alias Names and Two Table Query Problems

texts:
I'm quite new to querying databases, I've only been doing it for about 2 weeks in the current job that I'm in and have never touched SQL prior to this.

I'm working on trying to use alias names to pull two different sets of tables and display the fields in each table, all while ordering them by a specific field.  Here is the code I'm currently trying to use when to do that:

  SELECT ars.facility_id, ars.service_code, ars.coding_system,
  ars.result_label_seq, ars.display_seq, ats.facility_id,
  ats.service_code, ats.coding_system, ats.display_name, ats.test_name

  FROM anc_test_codes AS ats,
  anc_results_seqs AS ars

  WHERE ats.service_code = ars.service_code
  AND ars.coding_system = ats.coding_system
  AND ars.facility_id = ats.facility_id
  AND ats.facility_id = 'C' AND ats.service_code = 'CBC'
  ORDER BY ats.display_name

I'm sure my methods and style is totally hideous, so excuse this.  The error I'm getting is a generic Oracle SQL error: SQL Command Not Properly Ended - ORA-00933

Can anyone tell me what I'm doing wrong? Any help on this would be greatly appreciated.

Thanks!


texts: Should name suffix (Jr. Sr. III) be a separate field?

texts:
An existing data model defines a person's name as having the following attributes:

first_name
middle_name
last_name

The last_name also include various suffixes such as Jr. Sr. III, etc.

One issue with this is that a query such as where last_name = 'Brown' will miss Brown Jr.  Is this a good enough reason to have the suffixes in a separate nullable field? What would be the downside of doing that?


texts: How can I represent a list of words and related words in a relational structure?

texts:
I have a list of header words and words as below and I am trying to understand how best to represent these in a relational table:

Header      Other
Words       Words

abandon     abandoned, abandoning, abandonment, abandons
abstract    abstraction, abstractions, abstractly, abstracts
access      accessed, accesses, accessibility, accessible, accessing, inaccessible  access
accommodate accommodated, accommodates, accommodating, accommodation    accommodate
accompany   accompanied, accompanies, accompaniment, accompanying, unaccompanied    accompany
accumulate  accumulated, accumulating, accumulation, accumulates

The problem is that for every word I have additional information such as pronunciation.

What I am thinking of is two tables:

CREATE TABLE [dbo].[Word] (
    [WordId]         INT           IDENTITY (1, 1) NOT NULL,
    [Word]           NVARCHAR(50)  NOT NULL,
    [Pronounciation] NVARCHAR(100) NULL,
    [Other columns follow ...
)

CREATE TABLE [dbo].[WordRelationship] (
    [WordRelationshipId]  INT IDENTITY (1, 1) NOT NULL,
    [HeaderWordId]        INT NOT NULL,
    [OtherWordId]         INT NOT NULL,
)

I would like to get some suggestions from the members here


texts: How to model complex relationship between three Models in Django

texts:
I have to model the following entities: BuildingSite, Company, Worker.

Each entity has a many-to-many relationship with the other entities, and each relationship some attributes like start_date, finish_date, craft, contract, etc...
A naive approach coul be like that:

BuildingSiteCompanyRelationship:
  - building_site: ForeignKey(BuildingSite)
  - company: ForeignKey(Company)
  - start_date: Date
  - finish_date: Date

CompanyWorkerRelationship:
  - company: ForeignKey(Company)
  - worker: ForeignKey(Worker)
  - craft: String
  - contract: String

BuildingSiteWorkerRelationship:
  - building_site: ForeignKey(BuildingSite)
  - company: ForeignKey(Company)
  - worker: ForeignKey(Worker)
  - start_date: Date
  - finish_date: Date

Alternatively fot the third link I have thoght it can be interessant to connect both relationships, because so it is easier to follow one from another.

BuildingSiteWorkerRelationship:
  - building_site_company_relationship: ForeignKey(BuildingSiteCompanyRelationship)
  - worker: ForeignKey(Worker)
  - start_date: Date
  - finish_date: Date

Is it a good idea to model it in Django?


texts: How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures?

texts:
I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could also be used for extending the SQL Server base functionality. I am interested in knowing the following:

Are PostgreSQL extensions and SQL Server extended/CLR stored procedure having similar purpose? If not, why?
If answer to above is yes, then, which one is better - PostgreSQL extensions or SQL Server extended/CLR stored procedures? Why?

Thanks.


texts: Manually entering data using Management Studio

texts: How can I enter data through the GUI interface rather than an insert command in SQL Server Management Studio 2012? I can't find it anywhere whereas there used to be an option in the previous versions. There used to be a table GUI where you could point to a certain record and add/modify date. Can't find it in 2012.


texts: How to import data from Sqlite into SQL Server 2012

texts:
I need to import data from sqlite into SQL Server so we can include it in our data warehouse.

Based on previous system volume. Id expect there to be 50-100,000 rows after a couple of years. For simplicity id truncate the target table and reimport the data each day

Dbs exist on different Servers. Both servers are win 2012. SQL Server 2012 SP1.

Ideally id like to import directly from the source sqlite db into SQL Server. We use SSIS as our standard ETL tool elsewhere and so for consistency id like to use that if possible.

Id appreciate suggestions for a simple, robust & reliable approach. How can SQL connect? ODBC?

Im also  interested in practical alternatives. Plan B: CSV/BCP?


texts: Oracle 11gR2 => Oracle Data Pump (expdp, impdp) => can backup be safely taken during runtime

texts:
until now I create a daily Logical backup backup of my Oracle 11gR2 Database at midnight while the database is running but the client application is in an idle state so that no queries are executed on the database.
Now I also want to implement a second Backup during the Day while the Database and the Client application are both up and running and queries (select/update/insert/delete) are executed.
Because I have already well tested Backup and Restore Scripts I want to continue using expdp and impdp.
This second "during the day" backup would not be directly imported in the production system after a potential data loss. I would import it on a mirrored test system and then manually use OracleSqlExplorer to query the lost data.
This leads to the following questions:

If I use expdp to perform a backup during the database runtime is it guaranteed that the database where I take the backup from and SQL statements are executed during the backup process does contain its integrity and consistency ?
Do I need to add certain Parameters to the expdp command to guarantee consistency ? I found this:

"expdp options for creating a consistent export dump: FLASHBACK_SCN,
FLASHBACK_TIME, CONSISTENT=Y"

So far I use this linux shell script:
$ORACLE_HOME/bin/expdp \"$USERNAME/$PASSWORD as sysdba\" SCHEMAS=<csv list of schemas> REUSE_DUMPFILES=Yes DIRECTORY=backup DUMPFILE=$BACKUP_NAME.dmp

Can I use a backup create with expdp during the database runtime as a valid source for a impdp without having to worry about
integrity and consistency ? For question number 2 I found a thread
that says NO


texts:
I've looked at multiple other questions about querying linked servers, but nothing has worked for my setup.

Notes:

I'm not using localdb at all.  I just wanted to use SQLSMS for a remote cache database that is only accessible via the Intersystems ODBC driver.
I edited out the actual table names, but they all have periods in them.
MyDSN is not the actual DSN name.

Here is the structure and below the image are some of the things I've tried:

SELECT * FROM [MyDSN].[default].[Table.name]

  Invalid object name 'MyDSN.default.Table.name'.

SELECT * FROM [localdb].[MyDSN].[default].[Table.name]

  Could not find server 'localdb' in sys.servers.

SELECT * FROM [MyDSN].[default].dbo.[Table.name]

  The OLE DB provider "MSDASQL" for linked server "MyDSN" does not contain the table ""default"."dbo"."Table.name"".

SELECT * FROM [MyDSN]..[default].[Table.Name]

  The OLE DB provider "MSDASQL" for linked server "MyDSN" does not contain the table ""default"."Table.Name"".

SELECT * FROM [MyDSN].[default].[Table].[Name]

  Invalid object name 'MyDSN.default.Table.name'.

Also, if I right-click on one of the tables and pick "Script table as SELECT" I get the following error:


texts:
The question I have is

Why use:

QUOTENAME ('simple string', '''');

Instead of:

QUOTENAME ('simple string', ' ' ');

if I want to use single quotation as the delimiter?  I assume it has to do with escaping, but can't find any detailed explanation about this function.


texts: Function extract() from date returns double precision value

texts:
Why does the Postgres function extract() return double precision from date value?

Only reason I could imagine is, there are some locales using calendars (those not Gregorian calendars and supported by Postgres), calculate date/time differently and possible return float number.


texts: What are safe characters to use in SQL Server Full Text Search?

texts:
I've been playing with full text search on SQL Server 2012. I must say my attempts to target special characters in search queries were futile - SS does not understand them. What's more, sometimes the presence of special characters breaks the query entirely and SS reports an error. I haven't been able to understand when and how and what characters are okay and when they break things. I haven't found any way to target special characters in search short of escaping/replacing them with some other characters. I've also stumbled onto this article http://searchsqlserver.techtarget.com/tip/Using-full-text-search-for-symbols-in-SQL-Server which implies some special characters work like they do in regular expressions. I haven't been able to confirm this hypothesis in practice - escaping as suggested in the article does not work and those characters do not function as regex commands either.

Can anybody point me to a concise [working] explanation of how to deal with FTS and special characters? I haven't found anything useful on the web. I also have read through questions on SO, they were equally unhelpful.


texts: Unexpected Transaction Log Flush events and LOG_BACKUP errors

texts:
I recently had an unexpected Transaction Log event which I need assistance unpacking some of the event details. I have the what, but not the why...

The scenario is: Unattended application writing to Always On AG backed database, flushing about 55MB of Transaction logs every few minutes.

The database Transaction log size is 8GB, but <100MB is used. Log backups are every hour (as this is DEV) and this has been sufficient (up to now).

Within a 15 minute window, almost 20GB (unexpectedly) is flushed to the log. The drive the log is on fills up and "Full due to LOG_BACKUP errors" start spewing into the log.

The next scheduled log backup occurs at top of the hour, creates 3 GB compressed backup. The LOG_BACKUP errors stop, but transaction log used space remains at 20GB.

30 minutes later, Transaction log in use drops suddenly from 20GB to 1.5GB. No backup occurring during that time.

The application logs so far haven't been a huge amount of help in determining where those transactions have come from and why they flushed all at once to the log.

AlwaysOn Log and Redo queues also stayed at 0 during this event.

If the backup wasn't enough to free any space and all those transactions were still open and only rolled back 30 minutes (assuming they were as there was no log backup), why didn't the LOG_BACKUP errors continue after the backup?


texts: Lost connection to MySQL server during query issue

texts:
SELECT UNIX_TIMESTAMP(a.timestamp) AS timestamp, a.adid, a.adtitle, a.cityid, a.price,
a.area, a.addesc, a.subcatid, ct.cityname, p.picfile, scat.subcatname, cat.catid, cat.catname
FROM xzclf_ads a USE INDEX (id_index)
INNER JOIN xzclf_cities ct ON a.cityid = ct.cityid
INNER JOIN xzclf_subcats scat ON a.subcatid = scat.subcatid
INNER JOIN xzclf_cats cat ON scat.catid = cat.catid
LEFT OUTER JOIN xzclf_adxfields axf ON a.adid = axf.adid
LEFT OUTER JOIN xzclf_adpics p ON a.adid = p.adid AND p.isevent = '0'
LEFT OUTER JOIN xzclf_featured feat ON a.adid = feat.adid AND feat.adtype = 'A'
WHERE scat.catid = 10
AND a.enabled = '1'
AND a.verified = '1'
AND a.expireson >= NOW()
AND a.paid <> '0'
AND (feat.adid IS NULL OR feat.featuredtill < NOW())
GROUP BY a.adid
ORDER BY a.timestamp DESC
LIMIT 0, 20

It gives an error: Lost connection to MySQL server during query
Is anything wrong with this query?


texts: How can I create a Windows Firewall rule to allow only one IP address to connect MS SQL 2017 Express database in Windows 2016 server?

texts:
I use MS SQL 2017 Express database in Windows 2016 server, now my MS SQL 2017 express server is being attacked.

I hope to set a Windows Firewall rule to allow only one IP address (such as 45.23.56.12) to connect MS SQL 2017 Express database, how can I do ?


texts: Invalid length parameter passed to the LEFT or SUBSTRING function when inserting data into a table that has computed columns

texts:
I have a process that needs to split a string of key-value pairs. To do so, I'm using a combination of a temp table that has computed columns to calculate the key and the value from each key-value pair, and a string_split function in the INSERT statement to separate the key-value pairs into rows. This is how the table looks like:

CREATE TABLE #KeyValuePairs (
  [PK]       uniqueidentifier NOT NULL,
  [KeyValue] varchar(500) NOT NULL,
  [Key] AS CONVERT(varchar(35), SUBSTRING([KeyValue],1,CHARINDEX('=',[KeyValue])-1)) PERSISTED NOT NULL,
  [Value] AS SUBSTRING([KeyValue],CHARINDEX('=',[KeyValue]) + 1, LEN([KeyValue]) - CHARINDEX('=',[KeyValue])) PERSISTED NULL
);

The clustered index just helps me to find a specific combination of primary key and value key efficiently. The following is how I insert data into it:

INSERT #KeyValuePairs ([PK], [KeyValue])
SELECT T.[PK], kv.[value]
FROM BaseTable T
CROSS APPLY string_split([KeyValueStringColumn],'*') kv
WHERE [KeyValueStringColumn] != ''
  AND kv.[value] != ''

Occasionally, this query fails with the following error:

  Invalid length parameter passed to the LEFT or SUBSTRING function.

If I remove the computation from the table definition, leaving the fields NULL by the INSERT and instead run an UPDATE afterwards with the exact same formula, the UPDATE succeeds always.


texts: How to insert values in junction table for many to many relationships?

texts:
I have three tables in database:

 trips(trip_id(pk), trip_name(unique), user_id(fk))

 places(place_id(pk), place_name(unique))

 trips_places_asc(trip_id(fk), place_id(fk))

Since many trips can have many places, I have one junction table as above.

If a user inserts places to the trip, the places will be added to places table and the trip will be associated with the places in trips_places_asc table.

So, if I write  a query like:

INSERT INTO places (place_name)
VALUES ('XYZ')

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

then how to store trip_id and place_id in Junction or Association table trips_places_asc? Will I have to fire two queries?


texts: In MySQL, should I add an index even if the query that scans the table is only ran once a month?


texts: Difference between sql agent account logged in as NTLM and UPN?

texts:
Can someone please tell the difference between a sql agent account logged in as NTLM and UPN?

What is the difference if the sql agent account is logged in as:
domain\username and username@domain.com?

In what way will the functionality affect and where?
Would it affect 'execute as' statements in sql stored procedures?


texts: Is Postgres insert performance the same in the replica

texts:
We've got a Postgres database with a table that takes heavy select, update and inserts that needs some tuning (it has too many indexes).
We have a read replica, and I'm wondering if moving all of our SELECT queries over to the replica will be a quick win while we sort out the indexes performance. (Our application can be switched over to a replica fairly easily while sorting the indexes is going to take longer).
My understanding is that pointing read operations at the replica probably won't improve performance in this case, but wanted to check my understanding.
My understanding is that:

INSERT and UPDATE operations will return once they're committed to memory (which is then written to the WAL asynchronously), so they'll be not much faster.
The replica will stream the WAL and will have to update it's own copy of the tables and indexes. So SELECT operations on the replica will have to contend with the same IO and CPU demands that those updates impose on the primary, so will gain no performance.

If our app were simply streaming inserts, then I could see a performance improvement for the inserts, but in practice since the clients are performing SELECT/INSERT together, I can't see a real performance gain here in this use case?
Is this correct, or have I missed something?


texts: Extended events vs SQL Audit - performance implications

texts:
I would like to setup an audit trail kind of system on my database to monitor for UPDATE/INSERT statements on a specific table with very high activity. I have two options in front of me: using the SQL Server built-in Audit system or go with Extended Events.

As SQL Server Audit internally uses Extended Events, I assume there will be some kind of overhead when I use Audit instead of Extended Events directly.

Is there any way I can do some testing to analyze which system is impacting the server more? If I could know what actually happens when any XE session is created it would help me in analyzing the impact on the server.

We considered triggers and left out that option because of overhead. But that was just decided based on information from the Internet.


texts: Cassandra query performs differently at different times

texts:
I am using apache-cassandra-2.0.12 in production, with network topology strategy and ReplicationFactor : 3 in a cluster with  2 DC’s each contains 4 nodes

While analyzing the response time for the read requests, we found out that some queries are performing slower than it actually does.
Eg : Consider the following table

Create ColumnFamily "Employee"
(
  empID bigint,
  uniqueID text,
  col1 text,
  col2 text,
  col3 text,
  primary key (empID,uniqueID)
)

This CF contains data for more that 5K row entires and and each employee contains minimum of 100K columns and the maximum of 1000K columns

So in this CF the response time for the following queries differs drastically from time to time

SELECT * form "Employee" where empID = xxx and uniqueID = 'value';

Some times the response time for the above query is more than 3 sec, whereas it should actually take within 50 milliseconds

I have monitored the load (compaction time, disk utilizations etc ), CPU and the memory of the nodes at that time . All these params were normal.

Is there anything that I have missed or is this the normal behavior of cassandra ?

Note: I don't have any tombstone columns in this CF


texts: Security: writing to a file from MySQL CLI

texts:
I was testing writes to a file from MySQL CLI (locally & remotely) and I wonder what settings are preventing writes for remote users, basically I would like to make sure that it's set on all servers in case someone (devs) will allow remote access to MySQL:

Local (Writes allowed):

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> \P cat >> /etc/passwd
PAGER set to 'cat >> /etc/passwd'
mysql> SELECT CONCAT('test:x:5000:5000::/home/test:/bin/bash');

Remote (writes denied):

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.01 sec)

mysql> \P cat >> /etc/passwd
PAGER set to 'cat >> /etc/passwd'
mysql> SELECT CONCAT('test:x:5000:5000::/home/test:/bin/bash');
sh: /etc/passwd: Permission denied


texts:
I have upto 10 databases running on a single SQL Server instance on a single server.

I have read about best practice for storage and concluded the following...

1) Transaction Log on own RAID array (Raid 10) or at least on own physical disk

2) Data files(secondary filegroup) on own disk array (Raid 10 or 5)

3) Indexes on own filegroup (Raid 5)

4) Primary on Raid 1 or 5 (only system dbs)

5) Tempdb on Raid 5 or 10

So each of the above will be on separate physical disks?

Should I then have a separate disk for each secondary filegroup of each database? Or can they reside on the same disk?
 How would this impact on performance?

And similar again with each Primary filegroup - should each one be on its own disk? In this case 10 separate disks for each primary filegroup?

Thanks!


texts:
Which is a better way to install multiple instances of MySQL / MariaDB / etc on a single Windows box?

Manually setting different ports?
Docker?
Virtual Box?
Something else?  (Sandbox, mysql_multi, etc, seem to fail on Windows.)

Once they are installed, I intend to write a Perl script to iterate through the instances to get timings (etc) for comparison.  So, they need to be accessible in some manner from a single thread.

(I can figure out the settings, RAM, disk, etc; it's just the installs that I am looking for advice on.)


texts:
A          B          C
     1        132          1
     1        155          2
     1        132          3
     1        132          4
     1        155          5
     1        155          6
     1        132          7

select A, B, [...] as D
from myTable
order by C

Query must be ordered by C and should return:

     A          B          D
     1        132          1
     1        155          2
     1        132          3
     1        132          3
     1        155          4
     1        155          4
     1        132          5

All A and B pair groups must have unique D value.

Is it possible to calculate some kind of ID for groups based on order ?

Tried with RANK and GROUPING SETS but with not success.


texts: Will column size change when trying to use single unicode character in place of multiple ASCII characters?

texts:
I have a column in utf8_unicode_ci collation for text type in MySQL.

The content is in Unicode, I have used tags like {br}, {v}, {t}, {h} etc.., throught the database. I am now thinking of changing these to single characters from Unicode table like ⓑ, ⓥ, ⓣ, Ⓗ and so on and so forth.

Will this have any effect on the size of the table? (Increase or decrease) Should I be worried about any other side effect? Will this have any effect if type of the column was varchar?


texts:
Possible Duplicate:
  SQL 2008 Server agent cant see perfmon counters

I'm trying to setup SQL Server Perfmon counters on a Windows Server 2003 with SQL Server 2005 SP3.  I'd like to run counters on the following:

SQLServer:Buffer Manager - Buffer cache hit ratio
SQLServer:Buffer Manager - Page life expectancy
SQLServer:General Statistics - User Connections
SQLServer:Memory Manager - Memory Grants Pending

But SQL Server does not even show up as a "Performance Object" in the drop down box in Perfmon.  Does anyone know why?


texts: Cross Database transactions with Availability Groups

texts:
Recently we are working on a POC to get Always on work and happened to see this article in BOL

http://technet.microsoft.com/en-us/library/ms366279.aspx

This article suggests that there would be logical inconsistency when we are dealing with Synchronous mode too, but  will this actually be the case?

Consider for example databases A and B on which the transaction is running and A is in High-safety mode and B is not mirrored. The log of A has to go to Mirrored database then the Primary database commits eventually two phase commit(transaction on B) succeeds but article suggests that log will not be transferred in the first place and results in commit on B which is contradictory. Please help me in understanding Whether the statement suggested in above article is true. If yes how can it be :).

PS :Please let me know if I need to provide more information around this.


texts:
I normally connect to my Oracle database at work using a connection string. Other then the obvious answer of asking the DBA what it is, using SQL Developer or some other tool can I find out what the tns listener name is. This is an excerpt from a config file in .net where i use to connect:

  DATA SOURCE=myURL:port/servicename;PASSWORD=password


texts: PL/SQL - how to identify sequences used by tables?

texts: Is there a way to get a resultset from executing a query against an Oracle database that would return a listing of all the tables in the database and which sequence it is using (if any)?


texts:
I was given a RHEL 6.9 server with MySQL installed and I just wanted to check the MySQL version running on it.

When I tried the rpm -qa | grep mysql or mysql --version or SHOW VARIABLES LIKE "%version%"; commands they show the version as 5.6.36

However when I run yum info mysql-server it shows a different version (5.1.73) than the other commands.

What could be the reason for this.


texts: how do you convert a non Oracle Managed files into an Oracle Managed file


texts:
I have a linux box with centos 6.7 and postgres 9.3.9.

I have a foreign data wrapper throught an oracle 11.2 box and a jdbc driver.

It's working fine since several months, but yesterday I had to change pg_hba file and launch a "service postgres reload".

Postgres swith to recovery mode and in my postgresql logs I can find the cause :

#
# A fatal error has been detected by the Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x000000000064a661, pid=24074, tid=139895462369216
#
# JRE version: Java(TM) SE Runtime Environment (7.0_45-b18) (build 1.7.0_45-b18)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (24.45-b08 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C  [postgres: dpmmc_usr dpmmc XXXXXXXXX(54268) PARSE+0x24a661]  VirtualXactLockTableInsert+0x11
#
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
#
# An error report file with more information is saved as:
# /pgsql0/9.3/data/hs_err_pid24074.log
#
# If you would like to submit a bug report, please visit:
#   http://bugreport.sun.com/bugreport/crash.jsp
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#
2015-09-01 14:45:17 NCT @/ 00000 LOG:  server process (PID 24074) was terminated by signal 6: Aborted
2015-09-01 14:45:17 NCT @/ 00000 LOG:  terminating any other active server processes
2015-09-01 14:45:17 NCT @/ 00000 LOG:  server process (PID 24074) was terminated by signal 6: Aborted
2015-09-01 14:45:17 NCT @/ 00000 LOG:  terminating any other active server processes
2015-09-01 14:45:17 NCT orchestra_adm@XXXXXXXXX/orchestra 57P02 WARNING:  terminating connection because of crash of another server process
2015-09-01 14:45:17 NCT orchestra_adm@XXXXXXXXX/orchestra 57P02 DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
....
2015-09-01 14:45:17 NCT postgres@[local]/ccas 57P03 FATAL:  the database system is in recovery mode
2015-09-01 14:45:17 NCT abs_usr@XXXXXXXX/sirh 57P03 FATAL:  the database system is in recovery mode
....

My jdbc lib is /opt/jdk1.7.0_45/jre/lib/ext/ojdbc14.jar
(I also try with ojdbc7.jar)

After the postgresql restart, I try to execute another reload and I have the same issue. So It not seems to be a java memory leak cause...

Where can I find help on this issue ?

someone already experienced this ?

what can I do to avoid this ?

Edit :
Reload leads to a SIGHUP signal send to postmaster processus. Postmaster forward SIGHUP to all his child. And JVM considers SIGHUP as a violent terminaison so it sends exception to postmaster...

(hint on this : https://bugs.openjdk.java.net/browse/JDK-6740344?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel)

I try to give -Xrs JAVA_OPTS but it's never used... I don't know how to tell postgres to load java with options...
(references for -Xrs : http://linux.die.net/man/1/java-java-1.7.0-openjdk)

Thanks !


texts:
I have writen a trigger which will log every data updation and will log previous_value, new_value, field_name and many more. But the problem is i have 77 fields in my table. So, it's difficult to write IF ENDIF for each field so i want to know is it possible to write using loop?

I have tried so far::

BEGIN

IF(OLD.company_name != NEW.company_name) THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_name", OLD.company_name, NEW.company_name, CURRENT_TIMESTAMP(), NEW.company_id);
END IF;
IF(OLD.company_first_name != NEW.company_first_name) THEN
 INSERT INTO elm_activity_log (user_id, action_on, action, action_col, action_old_value, action_new_value, action_at, action_on_id) VALUES (NEW.updated_by, "company", "update", "company_first_name", OLD.company_first_name, NEW.company_first_name, CURRENT_TIMESTAMP(), NEW.company_id);
END IF;

END

Please help, or any suggestion will be very helpfull.

Thanks in advance.


texts: Send a trigger to an application upon a specific SQL Server server usage pattern

texts:
I've been dealing with a vendor's unconfigurable application which is, unfortunately, the most feature-complete in its niche. I'm looking to augment its capabilities with on-top screen display. Its data access is extremely predictable allows me to infer reliably from query patterns what I need to know to provide a user with additional relevant information pertaining to their current view within the application. Specifically, it always executes TSQL statement (which is the easily identifiable part) with a named parameter (which is the part that defines what the user is viewing) that I'd very much like to get at.

So, in short, I'm looking to create a mechanism that:
 - Hook the query pattern (trivially expressible via wildcards)
 - Extract the named parameter value
 - Sends a message to the (remote) overlay application with the parameter value

Does SQL server have any facilities for any portion of this undertaking? Is there a better way I should be going about this? (Possibly lower down in the network stack.)

Edit: We're using SQL Server 2005, but upgrading to SQL Server 2008/2008R2 isn't out of the question.


texts: Is smallest to largest the rule of thumb in a multi-table JOIN?

texts:
Given this query,

SELECT a.*
FROM a
LEFT JOIN b ON b.a_id = a.id
LEFT JOIN c ON c.a_id = a.id
HAVING COUNT(DISTINCT b.id) < COUNT(DISTINCT c.id)

if b is a significantly larger table than c, will there be performance implications to the order I do the joins? I read somewhere that the rule of thumb is to join smallest to largest, but that might've been referring to INNER JOINs.

I realize that the results will depend on the order of outer joins in many scenarios, but in one like the above where the order doesn't matter for veracity, will it likely affect performance?


texts: Why does Bitlocker intermittently require SQL service restart after sleep?

texts:
I have a development machine that has two drives:

C:\ - Operating system (Win 10 Ent) and SQL Server 2012 Standard installation
D:\ - SQL Server databases, backups, logs

I recently activated BitLocker on both drives, then activated auto-unlock because I realised that SQL Server can't access the databases unless the drive is unlocked.

Now with SQL (and only SQL, all other services/programs are fine), on an intermittent basis I see the following behaviours occur:

After waking from sleep mode, and then starting SSMS, I cannot log in (login fails with invalid credentials error).  This occurs for the Windows login account only
If login succeeds, or if I login using the sa account, then all databases are in the Pending Restore state

I guess both of these are due to the drive becoming locked during sleep (or perhaps not?)?    After waking from sleep, if I restart the SQL Server service, and then restart SSMS, everything works fine.

I wondered what might cause this to be intermittent please, or if my assumptions are wrong, what is actually causing this?

PS. Perhaps a BitLocker tag might be useful, as I see there are plenty of other questions relating to this???


texts: How to get current month and year of the database as a macro in SQL server?

texts:
I want to get the current month and year of the system into a macro variable and keep using that macro variable to create related new variables. The purpose is that if I run my script every month, I will have a new macro variable for each month. I know how to achieve this in SAS:

%let month = %sysfunc(today(),monyy5.);

%let Updated = Product_&month;

So far, I think in SQL server I can use GETDATE() to get the current date and then use CONVERT to convert in my desired format. I am not sure how the rest of it will be done. Any help/advice/suggestions/thoughts much appreciated.


texts: Drop/Create MongoDB TTL index vs. collMod in production

texts: I'm working with a MongoDB collection containing over 250 million documents that has an existing TTL index and is written to with a pretty high frequency. I'd like to reduce the TTL value for the index from 60 days to 30 days, while minimizing production impact. It seems that I have two options: dropping and recreating the index in the background or running the collMod command to modify the index. The simplicity of the collMod command is appealing, but I'm concerned about the impact it might have, especially since there appears to be no way to run it in the background. Would dropping/recreating the index lead to less lock contention and have less of an impact?


texts: which command to execute to run multiple OLAP queries?

texts:
I have 10 sql files that contains different OLAP queries.

I want to run them as a batch. Meaning by batch, Postgres will consider each file as a batch and execute the queries at once (depends on its own managing process)

The follow command, execute each query one by one

psql -h localhost -d databasename -U user -p port -a -q -f input.sql -o output.txt

Is that possible ? and how to calculate the time it takes to execute that batch sql queries ?


texts: mysqldump is ignoring option '--databases' due to invalid value

texts:
I have this MySQL database named capsule_corporation_database.
Then, I have this my.cnf file in my home directory:
~/.my.cnf
[client]
host=localhost
user=trunks
password=mai
database=capsule_corporation_database

Attempt to mysqldump
mysqldump > copy_of_capsule_corporation_database.sql

The backup did not pursue and logged the below message:
Warning: mysqldump: ignoring option '--databases' due to invalid value 'capsule_corporation_database'

What does it mean and how to resolve it?


texts: Update all values in a column with individual values

texts:
I ran into a problem where Excel and SQL Server have different "zero day" date conventions, so that all the dates currently in one of my tables are two days ahead of where they should be. Is there an easy way to update all the dates?

I know I can do something like

SELECT DatabaseID, P60_Date, DATEADD(day, -2, P60_Date) AS NEW_P60_Date FROM US_P60

But I don't know how to UPDATE all the values in P60_Date with the new dates.


texts: Disaster recovery/Server Migration for databases that are permanently in Standby

texts:
I've got an old 2005 server where 8/12 of the databases are in Standby/Read-Only. This server has 81 jobs, many of them with unique id's for names. One of these jobs is called Replicate, and it calls a .cmd file every 30 minutes. The .cmd calls a .pl that looks like some sort of home-brewed log shipping.

I'd like to bump the databases out of standby so I can back them up properly, but I'm worried it will break everything.

To make matters worse, while the server is a VM, there is (according to the VM admins) a problem with the drives where it sits, making it impossible to back up. They are worried that the server could die at any time, so we have to do something.

The databases are relatively small - I may be able to get in and out in the 30 minutes between the Replicate job. There's no test environment, though, so I can't find out easily.

We do have vendor support, but they don't seem to be very good. Am I better off just handing them the keys?

Question: How would you go about backing up this server without knocking it down in the process?


texts: Assistance creating SQL query in Oracle to split by weeks

texts:
I am building a report to show order statistics by week within our company.

Here is the SQL query that I've created:

Select Sum(ORDER_TOTALS.ORDER_TOT_NET)  As "ORDER_TOT_NET",
       Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT",
       Sum(ORDER_TOTALS.ORDER_TOT_QTY)  As "ORDER_TOT_QTY",
       Sum(ORDER_TOTALS.ORDER_TOT_SQM)  As "ORDER_TOT_SQM"
From ORDER ORDER,
     ORDER_TOTALS ORDER_TOTALS
Where ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NO
And   ORDER.DATE Between '14-DEC-15' And '18-DEC-15

As you can see this will show results for orders that were created last week however I do not understand now how to make it more advanced so that we can get each week of the current year for example.

Here are the current results:

ORDER_TOT_NET  |  ORDER_TOT_WGHT  |  ORDER_TOT_QTY  |  ORDER_TOT_SQM
1020304050.00  |  4567890         |  92328493       |  3923948

What I want is something around this idea:

          ORDER_TOT_NET  |  ORDER_TOT_WGHT  |  ORDER_TOT_QTY  |  ORDER_TOT_SQM
Week 1    1020304050.00  |  4567890         |  92328493       |  3923948
Week 2    1020304050.00  |  4567890         |  92328493       |  3923948
Week 3    1020304050.00  |  4567890         |  92328493       |  3923948

How could/should I create it so that I have these results but for Week 1 through to the current week? Is this something that I should build directly in to the SQL query?

I am using DBxtra reporting program.


texts: Storing/Retrieving Chinese/Unicode in PL/SQL

texts:
We are on Oracle9i Enterprise Edition. I tried the following in PL/SQL.
MANUFACTUERER Field is defined as VARCHAR2(75).  I tried similar table with NVARCHAR2(75) instead, and same result.

INSERT INTO TLP.part_extensions_tab(MANUFACTURER, MANUFACTURER_ADDRESS_LINE )
SELECT 'Neal Chinese Test 06', '统一通信系统）【功能性备注：集团电话' FROM DUAL;
commit;
select MANUFACTURER, MANUFACTURER_ADDRESS_LINE from TLP.part_extensions_tab
where MANUFACTURER like 'Neal Chinese Test 06' ;

The SQL runs, but returns the following question marks; so I'm not sure the insert worked, or if I have a problem displaying the text back in Chinese.

My other Googling lead me down this path (not sure if it is a dead end or not):

DECLARE
   MyLanguage nvarchar2(30);

BEGIN
  --EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''SPANISH''';
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE = ''SIMPLIFIED CHINESE_CHINA.ZHS16GBK''';

  select Value into MyLanguage from nls_database_parameters where Parameter = 'NLS_LANGUAGE';
  DBMS_OUTPUT.put_line('MyLanguage=' || MyLanguage);
  --DBMS_OUTPUT.put_line('NLS_LANG=' || NLS_LANG);
END;

--SELECT USERENV ('language') FROM DUAL;

But this gives me "ORA-12705; invalid or unknown NLS parameter value specified.  I don't know if that language needs to be installed on my client, my server, or my syntax is just wrong.
When I tried Spanish, the SQL ran without error, and it shows Spanish.American in the result.
emphasized text


texts: Why is the System tablespace in manual segment space management mode?

texts: Why has Oracle kept the SYSTEM tablespace in manual segment space management mode? I searched on forums but didn't find the answer.


texts:
I was reading through Kendra Little's post on www.brentozar.com

http://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/

In the post, Kendra mentioned that she is using SQL Server 2012. Kendra has displayed a execution plan mouse over pop up as shown below, with more information like Memory Grants.
.

I am also using SQL Server 2012 (Microsoft SQL Server 2012 - 11.0.5058.0 (X64)) .
But I am getting a different information as below.

Anybody have any idea what am my missing ?


texts:
In SSRS 2016 Mobile reports are a new option for Native mode report server. I have created several now and there appear to be consistencies between colors on different charts and graphs if the same columns are used in the setup of each element. However, I cannot find any way to consistently keep colors across slicer changes. Is there a work around, a setting a missed, a code behind, some way to ensure that when I change a parameter my chart legends do not suddenly swap colors?

This is with an "All" level setting on the parameters.

This is the new colors scheme after a parameter has been selected and the report updates with the new quantities. Totally unacceptable - this renders the report nearly worthless.

This behavior happens across reports and data sets for me. I would have tried to set an ordering as it appears to change based on quantity changes in each category, but there are no ordering settings for pie charts.


texts: Disk Size Considerations for a SQL Server 2008 R2 database

texts:
What's the rule of thumb for Disk size considerations for hosting a mirrored database that supports about 100 writes every second during peak. Current database full back up size is approx 150 MB.

Considering (separate) drives for

Data
Log
TempDb

Database size from exec sp_spaceused is (will take care of huge DB size, it's bloated because of mirroring I reckon)

database_name   database_size   unallocated space
-------------------------------------------------
APP_PROD        88026.31 MB     0.00 MB

reserved    data        index_size  unused
--------------------------------------------
122168 KB   118992 KB   2088 KB     1088 KB

Many thanks


texts: Does a simple select query acquire locks?

texts:
I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks.

Select * from Student;

Please consider the case where the statement would not be running inside a begin tran block.


texts: Monitoring CDC (Change Data Capture) performance

texts:
We currently have in place CDC, DW team is reading data, everyone happy. Then we needed to add couple new columns. Easy peasy following this answer... well, actually we had some hiccups but that's solved. But that was on a small environment just to check that the steps to follow are ok. Now is time to deploy that on a way bigger environment. So, question:

Is there a way to monitor CDC while the 2nd capture instance is running populating data in parallel with the old capture instance? Extended events maybe? Any recommendation on what to look? IO, log growth, storage, memory grants maybe?


texts: Selecting Values That Differ From Section Average


texts: Solving Long Running Query, number of executions problem

texts:
I have the following query:

ALTER PROCEDURE [dbo].[spSearchClient]
    @SearchWords NVARCHAR(MAX) = NULL,
    @LowerDate DATE = NULL,
    @UpperDate DATE = NULL,
    @UserCreated nvarchar(450)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @UserAccountID smallint
    DECLARE @SearchWordCount int

    SELECT @UserAccountID = dbo.fnGetUserAccountID(@UserCreated)

    CREATE TABLE #SearchWords
    (
        ID int IDENTITY(1,1),
        Word NVARCHAR(50)
    )

    INSERT INTO #SearchWords
    (
        Word
    )
    SELECT
        value
    FROM
        STRING_SPLIT(@SearchWords, ' ')
    WHERE
        TRIM(value) <> ''

    SELECT @SearchWordCount = @@ROWCOUNT;

    SELECT
        C.ClientID,
        C.FirstName,
        C.LastName,
        C.FullName,
        C.DateOfBirth,
        G.GenderName,
        G.GenderIcon,
        C.VerificationCode,
        V.LastVisitDate
    FROM
        Client C
    OUTER APPLY (
        SELECT MAX(StartDate) AS LastVisitDate
        FROM Visit AS V
        WHERE C.ClientID = V.ClientID
    ) AS V
    INNER JOIN LookUp.Gender G on
        C.GenderID = G.GenderID
    WHERE
        (
            EXISTS( -- if we have words
                    SELECT *
                    FROM #SearchWords s
                    WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                       OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                       OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
                )
            OR @SearchWordCount = 0 --if we don't have words
        )
        AND DateOfBirth BETWEEN ISNULL(@LowerDate,DateOfBirth) AND ISNULL(@UpperDate,DateOfBirth)

    INSERT INTO UserSearchLog
    (
        SearchWords,
        LowerDate,
        UpperDate,
        SearchResultsCount,
        UserCreated
    )
    VALUES
    (
        @SearchWords,
        @LowerDate,
        @UpperDate,
        @@ROWCOUNT,
        @UserAccountID
    )

    DROP TABLE #SearchWords
END

The execution plan is https://www.brentozar.com/pastetheplan/?id=BkGzfUeHz

The query works how it should but takes a good 3-7 seconds to run and it seems to all be due to the following which has an execution for every row in the Client table:

WHERE
    (
        EXISTS( -- if we have words
                SELECT *
                FROM #SearchWords s
                WHERE (c.FirstName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.LastName LIKE CONCAT('%',s.Word,'%'))
                    OR (c.VerificationCode LIKE CONCAT('%',s.Word,'%'))
            )
        OR @SearchWordCount = 0 --if we don't have words
    )

Wondering if anyone knew of a better more effective way of doing this that would be less time consuming?

If some sample data would be useful, please let me know.


texts: Get stopwords of a InnoDB fulltext index

texts:
I can set stopwords for a MySQL fulltext index with SET innodb_ft_user_stopword_table ... before creating the fulltext index.

But how can I figure out the existing stopwords of before created fulltext index?

I can request information_schema.INNODB_FT_DEFAULT_STOPWORD for the default stopwords of the server. But I would like to know the used stopwords for a specific table.

In the MariaDB knowlegebase, I found a description how to determine the stopwords: https://mariadb.com/kb/en/library/full-text-index-stopwords/#innodb-stopwords

But I can't set innodb_ft_user_stopword_table to a "normal" table with several fields. (I'm using MariaDB 10.1.39.)

A way to find out the used stopwords for MyISAM tables, I would like to know too.


texts: group by and order by making query very slow in mysql?

texts:
Query works fast:

select SQL_NO_CACHE
  users.id, users.name
from useres
  left join user_statistics
    on users.id = user_statistics.user_id
where users.is_deleted = 0
group by users.id
order by users.updated_at
  limit 50;

Slow Query:

select SQL_NO_CACHE
  users.id, users.name,
  max(user_statistics.access_time) as user_access_time
from useres
  left join user_statistics
    on users.id = user_statistics.user_id
where users.is_deleted = 0
group by users.id
order by user_access_time
  limit 50;

Explain plan of slow query

> *************************** 1. row ***************************
>            id: 1   select_type: SIMPLE
>         table: users
>          type: ref possible_keys: fk_users_is_deleted
>           key: fk_users_is_deleted
>       key_len: 4
>           ref: const
>          rows: 107696
>         Extra: Using where; Using temporary; Using filesort
> *************************** 2. row ***************************
>            id: 1   select_type: SIMPLE
>         table: user_statistics
>          type: ref possible_keys: fk_user_statistics_user_id
>           key: fk_user_statistics_user_id
>       key_len: 4
>           ref: users.id
>          rows: 1
>         Extra:

I am using group by and order by on different tables. How can I optimize the above slow query?


texts: Take a Column, Split it into New Columns

texts:
Example table:

  ID    CB        CB2   CB3   CB4
  ----  --------  ----  ----  ----
  1     亀 龜 龜
  2     竜 龒

Note: Each character is separated by a space.
Note: The number of characters in CB varies.

I would like to move each character in column CB (after the first) to its own, separate, column, so that no more than one character is in each column.

Like this:

  ID    CB    CB2   CB3   CB4
  ----  ----  ----  ----  ----
  1      亀    龜    龜
  2      竜    龒

(SQLite)


texts: what is the most simple and easiest way to backup and restore on oracle 11g?

texts:
Im new on oracle 11g i come from SQL Server administration and the process to
 back up and restore its very easy and intuitive.

I was searching how to make backup and restore on oracle 11g in many cases devolpers talk about RMAN but i want to know if there is a more simple and easy solution to restore all elements of my DW
I dont need to make a programmed back up at the moment
mainly i want a programtically solution to operate in a windows SO


texts: Can't connect to/from SQL Server 2008 from my Windows 2008 server

texts:
I have a server with:

Windows 2008 Server
SQL Server 2008 enterprise

From my server using SQL Server Management Studio, I can't logon to any SQL Server and vice verse, I can't connect to my SQL Server from any pc/laptop.

I checked and see that, the port 1433 closed, even if I turned of firewall, I still can't connect to it.

My server name is: S143343. I have 2 sql server instances is MSSQLSERVER and S143343 (the same name with server name for replication). But I can't start S143343.

One more thing, from my server, I just connect to sql server by server name is localhost or dot (.). No way to access by computer name or IP.

Can you show me the way to solve this problems?


texts: How to produce an output of fields that are NULL (combined from multiple columns+tables)?

texts:
I manage an application that has multiple users entering data through a web front-end into an MSSQL database. Each individual 'record' can have around 100 database columns across multiple tables (sometime multiple rows in the same table). It is relatively trivial to write SQL queries that will output 1 row per 'record' with all the columns we require for reporting purposes, for example:

Assessor  Date    Length  Colour  Weight
Steve     2/4/17  23.4    NULL    45
John      4/4/17  NULL    Blue    NULL
Brenda    4/4/17  NULL    NULL    NULL

I want to produce a simple output that lists everything where data was not recorded i.e fields that remain NULL. For example:

Assessor  Date    Field
Steve     2/4/17  Colour
John      4/4/17  Length
John      4/4/17  Weight
Brenda    4/4/17  Length
Brenda    4/4/17  Length
Brenda    4/4/17  Colour
Brenda    4/4/17  Weight

Currently I've tried something along these lines:

 select
  assessor
  ,date
  ,Field    =  'Length'
 from
 dbo.table1
 where [Length] is NULL
UNION ALL
 select
  assessor
  ,date
  ,Field    =  'Colour'
 from
 dbo.table1
 where [Colour] is NULL
UNION ALL
...

Is there any way to produce my required output without building such a long list of unions? I've looked into UNPIVOT however this appears to exclude NULLs.


texts: How can I control how Pentaho DI formats integers coming from a text file?

texts:
I have a simple pipe-delimited text file with integers recorded as a sequence of numbers:

012345|00678 |various|text|fields

There are some leading zeros, and some trailing white space, but no decimals, commas, internal spaces, or other formatting.  Should be simple, right?

Pentaho's field analysis concludes that the fields are of type "Number", format "#.#", and precision "0".  That format string makes me wonder if Pentaho thinks there might be decimals in the columns, but when I run Preview Rows, I get nice clean integers, without decimals, scientific notation, or leading zeros, exactly as I want it.

When I try to consume the data with a PostgreSQL Bulk Loader step, I get error messages like these:

Import Table.0 - ERROR {0} ERROR:  invalid input syntax for integer: "5.017645082E9"
Import Table.0 - ERROR {0} ERROR:  invalid input syntax for integer: "1.0"

It seems that Pentaho has taken it on itself to format the integers with scientific notation and/or fixed decimals, and naturally Postgres balks at importing this.

How can I stop Pentaho from formatting these integers?

I'm using Pentaho Data Integration v6.1, and Postgres 9.4.9.  The file uses Windows line breaks and ANSI text.  Changing the field format to "#" or "0" had no effect.  Using a Table Output instead of a PostgreSQL Bulk Load works, but it is much slower.


texts: Performance: ALTER TABLE with default value vs. iterating over all records to set new value

texts:
For adding values to a new column, I have the following options:

Using default to add value during ALTER TABLE command.
Doing ALTER TABLE without default, then fetching all the records and iterating over all of them to save them with the new value.

I am interested to know how much performance difference should I expect?

I am adding a new column to a table with 14+ million records.

The column needs a default, unfortunately, I'll need to change a lot of code to allow putting default value. However, it's easier to iterate over all the records and save them with the value for the new column.

Before making my decision, I am looking for metrics of performance differences between the two options.

I am working on an application to be used in multiple machines, different environments and DBMS. I wouldn't be installing the application, it's the client's job. The application has scripts to aid them with the same. I understand that without system specifications it's hard to make predictions.

Is making broad predictions possible? As in, accessing all records requires O(n) time, similarly information on time requirements of using default during ALTER TABLE and other processes will also help.


texts:
I get  0 rows updated message when I ran the query below.  Why? Any ideas?

Update salespersons set salespersons.salary = salespersons.salary * 1.12
where salespersons.empid =
(SELECT salespersons.empid
  FROM (SELECT *
          FROM (SELECT *
                  FROM (SELECT s.empid employeeid,
                               s.ename employeename,
                               SUM(i.price * oi.qty) - s.salary Topprofit
                               FROM salespersons s
                               LEFT JOIN orders o ON s.empid = o.empid
                               LEFT JOIN orderitems oi ON o.orderid = oi.orderid
                               LEFT JOIN inventory i ON oi.partid = i.partid
                        GROUP BY s.empid, s.ename, s.salary)
                ORDER BY Topprofit DESC)
         WHERE ROWNUM = 2
        ORDER BY Topprofit ASC)
 WHERE ROWNUM = 1 and Topprofit is not null);


texts: Can indexes be deactivated for particular queries in DB2?

texts:
I'm trying to evaluate the impact of a new index on various queries that run against a DB2 11.1 database.

It would be convenient to be able to force the optimizer to ignore the new index on a query-by-query basis. Oracle would let me do this through optimizer hints, for example NO_INDEX(EMPLOYEES EMPLOYEE_PK).

The convenience comes from not having to delete and re-create alternative indexes when comparing their effects on a query, from not requiring "stateful" configuration changes that you must remember to revert, and from avoiding interference with other users' queries.

Is there an analogous mechanism in DB2 for that?


texts: The Service Broker endpoint is in disabled or stopped state - AoAG - SQL Server 2012 - 15 minute interval

texts:
I receive this error message in SQL Server error log every 15 minutes. I have setup AOAG on this server. The database option "Service Broker - Broker Enabled" is turned on for the Blackberry related databases and MSDB databases on this instance. Blackberry support mentioned they do not need this enabled. I have other AOAG instance and they do not receive this error.

Please share your wisdom in this regard. I have not used service broker yet and did not find a similar post on stack exchange from my search.

Thank you


texts: Best way to associate multiple records with one another

texts:
What is the best database design to associate multiple rows with another another.

Let's say we have a table called businesses.

How would I associate Business A, Business B, and Business C, to the point where I could retrieve the related models from within each business.

i.e.
If a user is viewing Business C, I could retrieve Business A and Business B.
If a user is viewing Business B, I could retrieve Business A and Business C.
If a user is viewing Business A, I could retrieve Business B and Business C.

I've been thinking about many-to-many relationship, but I would have to create 3 records to store this relationship such as:

Business_1  | Business_2
--------------------------
Business A  | Business B
Business A  | Business C
Business B  | Business C

Is that the best way?