texts: Query to find rows containing ASCII characters in a given range

texts:
I am using some scripts from another topic, but the accepted answer isn't working for all my data scenarios.  I would have asked my question on the original How to check for Non-Ascii Characters post, but I don't have enough reputation to comment or up-vote as of yet.

Questions:

Why is the accepted answer not working for char(150)?
Is there a way to fix the accepted answer?

My Testing

I created SQL Fiddle with sample data, the stored procedure from one of the answers, and queries to demonstrate the issue.

  Query 1: sample_table

-- Note: The "bad dash" row has char(150)

SELECT * FROM sample_table;

+-------------------+
|    DataColumn     |
+-------------------+
| test - good dash  |
| test – bad dash   |
+-------------------+

  Query 2: The other answer by John shows the "bad dash" row containing char(150):

SELECT dbo.Find_Invalid_Chars(DataColumn) [Invalid Characters]
FROM sample_table
WHERE dbo.Find_Invalid_Chars(DataColumn) IS NOT NULL;

+----------------------+
|  Invalid Characters  |
+----------------------+
| test [150] bad dash  |
+----------------------+

  Query 3: The accepted answer by Martin Smith returns no results:

SELECT DataColumn AS [Bad Data]
FROM sample_table
WHERE DataColumn LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_100_BIN2;

+------------+
| [Bad Data] |
+------------+

-- No rows returned.

Conclusion

Unfortunately, I often need to find characters within (or outside of) a range in databases I can't create stored procedures in.  I'd really like to find a fix for the accepted answer or a simple script that wouldn't require creation of any objects (including temp tables).

Any suggestions?  Thanks in advance.

EDIT 1: The solution cannot modify or add any objects or settings in the database. I'm looking for a self-contained query that will select rows with one or more characters in a range between two CHAR() numbers, regardless of the ASCII or Extended ASCII number supplied.

EDIT 2: The DataColumn can be in either VARCHAR or NVARCHAR. I have no control over this, so I'm hoping to figure out a self-contained query that works for both. The purpose of the query is to find characters in the source table/column that are not handled correctly by some software applications. The applications are interpreting the source correctly, but sometimes have issues with characters outside "standard" ranges, though the ranges vary by application.


texts: How do I get the date that each user last logged onto the server / database in SQL Server 2005

texts:
Currently, I have the following code that gets the DBNAME, USERNAME, ROLE_NAME for each user on the database. I need to get the last logged in date for each user to fill in the
LASTLOGIN_DATE.

ALTER PROCEDURE [dbo].[ZZ_USER_ROLES]
AS
BEGIN

DECLARE @DBNAME VARCHAR(200)

DECLARE @MSQL1 VARCHAR(8000)
DROP TABLE #DBROLES
CREATE TABLE #DBROLES
( DBNAME SYSNAME NOT NULL,
  USERNAME SYSNAME NOT NULL,
  ROLE_NAME VARCHAR(20) NOT NULL,
  LASTLOGIN_DATE DATETIME NULL )

DECLARE DBNAME_CURSOR CURSOR FOR
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME NOT IN ('MSSECURITY','TEMPDB')
ORDER BY NAME

OPEN DBNAME_CURSOR
FETCH NEXT FROM DBNAME_CURSOR INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @MSQL1 = ' INSERT INTO #DBROLES ( DBNAME, USERNAME, ROLE_NAME)

  SELECT '+''''+@DBNAME +''''+ ' AS DBNAME ,USERNAME, '+CHAR(13)+ '
  MIN(CASE WHEN (ROLENAME = ''DB_OWNER'' OR  ROLENAME = ''DB_ACCESSADMIN '' OR ROLENAME =  ''DB_SECURITYADMIN'' OR ROLENAME =  ''DB_DDLADMIN'' ) THEN ''1''
  WHEN ROLENAME =  ''DB_DATAWRITER''  THEN ''2''
  WHEN ROLENAME =  ''DB_DATAREADER''  THEN ''3''
  ELSE ''4''
  END) AS ROLE
  FROM (SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME
  FROM ' + @DBNAME+'.dbo.SYSMEMBERS A '+CHAR(13)+
  ' JOIN '+ @DBNAME+'.dbo.SYSUSERS B '+CHAR(13)+
  ' ON A.MEMBERUID = B.UID AND B.isntuser = 1 JOIN '+@DBNAME +'.DBO.SYSUSERS C
  ON A.GROUPUID = C.UID)S
  GROUP BY USERNAME
  ORDER BY USERNAME'
  --PRINT @MSQL1

  EXECUTE (@MSQL1)

 FETCH NEXT FROM DBNAME_CURSOR INTO @DBNAME
END
CLOSE DBNAME_CURSOR
DEALLOCATE DBNAME_CURSOR

SELECT DBNAME,USERNAME,CASE ROLE_NAME WHEN '1' THEN 'POWER' WHEN '2' THEN 'READWRITE' WHEN '3' THEN 'READ' WHEN '4' THEN 'NOPERMISSIONS' END ROLE,CUR_DATE FROM #DBROLES

END

I'd greatly appreciate if someone could suggest a solution and add the logic to  get the last logged in date for each user in the code above.It's a bit urgent. I've looked at some articles online but did not find them to be suiting my needs. Thanks a lot!


texts: Very fast individual queries but when joined super slow

texts:
I've got query 1:

select department_id from USER_ACCESS
  where USER_ACCESS.USER_ID = 13170
  AND USER_ACCESS.MANAGEMENT = 1

Takes about 0.475 seconds, which is not a lot for the data set I m trying to build. Basically USER_ACCESS consists of this structure:

USER_ID | DEPARTMENT_ID | LOCATION_ID | MANAGEMENT | SENIOR_MANAGEMENT | DIRECTOR |

It's basically a fine grained control over the actions in the system, whether a user has the authority to do something for a given department/location or both or sometimes if you're senior management and which department it is as long as you're tied to the location you're good to do whatever you want to do.

Now there is query 2:

select review.department_id from review
inner join sale on sale.department_id = review.department_id
where review.year_id = 10301

which takes about 0.136 seconds, however when I join these two on the department_id the query takes about 7 seconds.

I tried putting on or the other as a subquery, I also tried using WITH clause but it all turned out more less the same performance-wise.

To add more caveats, the USER_ACCESS has multiple department_id, so query 1 could potentially return many of the same department_id, also the same case goes for review it could return many duplicate department_id.

When I use distinct(department_id) on both queries I get slightly better performance but not by a lot, my question is why are they executing so slow when they are joined and other thing is how can I make em faster?


texts: Add Shapefile loader plugin to PgAdminPortable

texts:
I'm using PgAdminPortable and I'd like to enable « PostGIS Shapefile and DBF loader » plugin. The issue is that the files are not structured as in a PostgreSQL complete install (see below).

I have tried the following steps:

Add the lines below (see end of post) into D:\PgAdminPortable\App\PgAdmin\plugins.d\pugins.ini file. The plugin appears now correctly inside the plugins dropdown within pgAdmin, but when clicking it, I'm asked several DLLs files.
Create a postgisgui folder within D:\PgAdminPortable\App\PgAdmin.
Copy - paste missing DLLs files from a local PostgreSQL install into D:\PgAdminPortable\App\PgAdmin(I have no bin directory within PgAdminPortable folder). There are about 10 files missing. Once copied, I get a new error message saying that the program could not start...

;
;PostGIS shp2pgsql-gui (Windows):
;
Title=PostGIS Shapefile and DBF loader
Command="$$PGBINDIR\postgisgui\shp2pgsql-gui.exe" -h "$$HOSTNAME" -p $$PORT -U "$$USERNAME" -d "$$DATABASE" -W "$$PASSWORD"
Description=Open a PostGIS ESRI Shapefile or Plain dbf loader console to the current database.
KeyFile=$$PGBINDIR\postgisgui\shp2pgsql-gui.exe
Platform=windows
ServerType=postgresql
Database=Yes
SetPassword=Yes


texts:
I am looking for alternative to create index on long column:

create table line
(
    field_key   integer not null,
    value       varchar(4000),
    ...
);

create index key_value_idx on line (field_key, value);

results DB2 SQL Error: SQLCODE=-614, SQLSTATE=54008. Documentation says: The sum of the stored lengths of the specified columns must not be greater than 1024. For such cases in MySQL there is syntax:

create index key_value_idx on line (field_key, value(1000));

and HSQLDB just works without any limitations.

What is the analogue for DB2?


texts: How can I improve my table design for different types of an entity?

texts:
Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables for different types of Client, each having fields applicable to the respective type and have one master table referencing all of them and have fields applicable to all types.

Currently, I come up with the following design:

But I don't think my design is efficient enough (or even correct and free of errors). What would you suggest?
Also, if this is important in any way, I am planning to utilize MariaDB.


texts:
We are currently experiencing an issue with SQL Server migration. There is a legacy product and we don't want to change connection strings due to various reasons.
Thus, we would like to keep existing Listener name.

My question is, Is it possible to create one or more CNAME aliases for AG Listener Name? Is there any danger in doing this?


texts: Best practice for storing image order number in a database

texts:
I am designing a social networking website (mysql with InnoDB engine + php) where I need to store image sequence in a database. The table structure is as follows:

PicId (BIGINT, AUTO INCREMENT, PK)
Path (VARCHAR) : Path To Image
AlbumId (Foreign Key to Table containing album information, of which the photo belongs to).
Sequence (Image index or order in the album)
Status (CHAR) eg. u:uploading, a: available ,etc.
.. Other information

Now, suppose a user is uploading multiple photos at once and the order in which they arrive at the server is known only by the index value which I send to the server. So, what should be the best practice to store this order, subject to the following constraints:

Suppose there are already some images, so I need to number the current sequence after the existing ones (i.e. max(Existing Sequence)+ <current squence> where albumid=<the desired album>). So, one of the solution is to use an int column.
But, whenever the user would reorder the image, or insert into the middle, the whole set of rows from insertion point till the end would need to be updated. So, I am thinking of using float as an alternative. So, Sequence number of new image=(Sequence no of Old+Sequence no. of next)/2 . But this would lead to floating point comparisons as well as calculations.
Another approach may be link-list approach i.e. Instead of Sequence no., I can use Next Image, but in that case, I would not be able to get the PicId of the next Image if it has not been uploaded yet!!!

Please help me regarding this.


texts: Replacing MYSQL table with data from another table - no downtime

texts:
I have two tables with identical structures:

Table1 and Table2

id INT PRIMARY KEY,
a TINYINT,
b TINYINT,
c TINYINT

Table1 is actively being used by queries from other sessions; Table2 is not referenced/accessed by anything else.  My goal is to end up with just Table1, but having the data from Table2.
(Note that Table2 and Table1 do not have the same set of id-PRIMARY KEY.)

What is the best way to do this without risking Table1 not existing momentarily for other sessions?

I know I can do something like:

DROP TABLE Table1;
ALTER TABLE Table2 RENAME Table1;

But am not sure how to ensure nothing tries to query Table1 while it doesn't exist or is empty.  Do I use transactions or lock the tables or something else?  (Ideally, I'd want any other sessions' queries to return either old Table1 or new Table1 data rather than failing.)

I am using CodeIgniter in PHP to make the calls.

UPDATE:
Looks like
this post answers it using a method from the MySQL RENAME TABLE Documentation:

RENAME TABLE Table1 TO Table1_old, Table2 To Table1;

And then I can DROP Table1_old at my leisure


texts: Carry forward non-null value with a windowing function?

texts:
In SQL server, I often see this referred to as a "smear" and there's blog posts about using a "quirky update" to do it. But how would I do it with windowing functions. For example, if I have:

name     date   happiness
----     ----   ---------
John     1/3    20
John     1/4    NULL
John     1/5    NULL
John     1/6    NULL
John     1/7    18
Mary     1/2    35
Mary     1/3    NULL
Mary     1/4    -15
Mary     1/6    NULL
Mary     1/7    0

Is there a way to do something like:

SELECT
    [name],
    [date],
    CARRY([happiness]) OVER (PARTITION BY [name] ORDER BY [date])

So that the intermediate results look like:

name     date   happiness
----     ----   ---------
John     1/3    20
John     1/4    20
John     1/5    20
John     1/6    20
John     1/7    18
Mary     1/2    35
Mary     1/3    35
Mary     1/4    -15
Mary     1/6    -15
Mary     1/7    0

CARRY isn't a real function clearly, just want that functionality.


texts: I am migrating to a docker MariaDB setup and all my users were created w/ "Localhost". From what I read and tested ironically '%' excludes localhost. I want to copy my existing users adding a second entry utilizing '%' as the hostname. Any suggestions?


texts: MySQL Read Speed and Partitioning on Separate Drives

texts:
Introduction

Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was partitioned for a fixed field -21 partitions, avg 1Mln Row/Partition- like this:

CREATE TABLE `my_table`  (
 `id` int(10) unsigned NOT NULL,
 `fieldA` char(40) NOT NULL,
 `fieldB` char(40) NOT NULL,
 `fieldC` char(32) DEFAULT NULL,
 -- some other fields
 PRIMARY KEY (`id`,`fieldC`)
 -- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
  partition pNULLorEMPTY VALUES IN(NULL,''),
  partition pValueA VALUES IN('valueA'),
  partition pValueB VALUES IN('valueB'),
  partition pValueC VALUES IN('valueC')
  -- other partitions
);

The root of the queries are always "WHERE fieldC = ? ... AND ..." so I always guaranteed access to a partition.

This table can be only MyISAM so I have installed the MySQL server 5.6.11

  Prior to MySQL 5.6.6, a SELECT from a partitioned MyISAM table caused
  a lock on the entire table;  in MySQL 5.6.6 and later, only those
  partitions actually containing rows that satisfy the  SELECT
  statement's WHERE condition are locked. This has the effect of
  increasing the speed  and efficiency of concurrent operations on
  partitioned MyISAM tables.

The setup consist on a Master/Slave topology with same HW (I can distribuite reads across Master and Slave)

Possible solution

I have tree choice for the hardware architecture(6 slot disk 3.5''):

Soloution #1

1xraid1 for OS, /tmp and binlog
1xraid1 partition for mysql data (ex: /diskA)
1xraid1 additional partition for mysql data (ex: /diskB)

Soloution #2

1xraid1 OS, /tmp and binlog
1xraid10 mysql data

Soloution #3 (2 disk waste for tmp!)

1xraid1 OS and binlog
1xraid1 partition for mysql data
1xraid1 /tmp

The first choice can help me to spread the I/O over two disk using the:

CREATE TABLE `my_table`  (
 `id` int(10) unsigned NOT NULL,
 `fieldA` char(40) NOT NULL,
 `fieldB` char(40) NOT NULL,
 `fieldC` char(32) DEFAULT NULL,
 -- some other fields
 PRIMARY KEY (`id`,`fieldC`)
 -- some other index
) ENGINE=MyISAM
PARTITION BY LIST COLUMNS(`fieldC`) (
  partition pNULLorEMPTY VALUES IN(NULL,'')
    DATA DIRECTORY = '/diskA/customer'
    INDEX DIRECTORY = '/diskA/customer',
  partition pValueA VALUES IN('valueA')
    DATA DIRECTORY = '/diskB/customer'
    INDEX DIRECTORY = '/diskB/customer',
  partition pValueB VALUES IN('valueB')
    DATA DIRECTORY = '/diskA/customer'
    INDEX DIRECTORY = '/diskA/customer',
  partition pValueC VALUES IN('valueC')
    DATA DIRECTORY = '/diskB/customer'
    INDEX DIRECTORY = '/diskB/customer'
  -- other partitions
);

The second choice improve the fault tolerance level, but consider that I have a Master/Slave configuration so, in every moment, I can promote the Slave as a Master.

What kind of architecture you advice?

There are problems that at the moment I do not see?

Any other kind of suggestions would be greatly appreciated. :)

Thanks in advance

crx


texts:
The following oddly does work, but shows an error in PhpMyAdmin...  (see image below)

(in the definition block as seen in the image)

BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2))
SECOND);
END

END has a red X.  The syntax is "Unrecognized statement type. (near END)

WHAT I HAVE TRIED TO FIX THIS

SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2))
SECOND);

The errors go away in phpMyAdmin but when I click GO it gives a huge error so that's obviously not correct.

I have read people are using define when more than 1 statement is needed so I tried...

DELIMITER $$
BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) SECOND);
END$$

DELIMITER ;

I get unexpected characters ;
then I tried...

DELIMITER $$
BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND)$$
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) SECOND)$$
END$$

DELIMITER ;

Which doesn't work and gives me the original error before I tried fixing anything.  So, I don't know what to say.  I am obviously a novice to all this.  I just spent over an hour reading about triggers, mysql docs, but I don't know what I am doing wrong.  I spent another 30 min reading other questions about similar things to see if I can spot the syntax problem I am causing by looking at other peoples examples in how they create triggers in this.   I don't know what I am doing wrong.  :)

Maybe it's valid but it gives an error in PhpMyAdmin incorrectly!!?!??!

Any ideas?  Thank you!

It works... but, maybe it's not supposed to.  I don't know the proper syntax to do this.  Any tips would be appreciated.

I wrote all this because I want to do things properly so I figured I would ask someone.

Thank you for your time.


texts: Upgrading Postgres 12 to 13: 'pg_upgrade.exe' fails

texts:
I'm attempting to upgrade a Postgres instance from version 12 to version 13, following the steps outlined at https://www.postgresql.org/docs/13/pgupgrade.html.
From an elevated command prompt, I'm running (under Windows 10):

SET PATH=%PATH%;C:\Program Files\PostgreSQL\13\bin;
pg_upgrade.exe --old-datadir "E:\Programs\PostgreSQL\12\data"
--new-datadir "E:\Programs\PostgreSQL\13\data" --old-bindir "C:\Program Files\PostgreSQL\12\bin" --new-bindir "C:\Program
Files\PostgreSQL\13\bin" --old-port=5431 --new-port=5432 --jobs=5

The directories appear to be all correct, however the command returns:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server_start.log" or "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5431?
could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5431?

could not connect to source postmaster started with the command:
"C:/Program Files/PostgreSQL/12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "E:/Programs/PostgreSQL/12/data" -o "-p 5431 -b " start
Failure, exiting

pg_upgrade_server_start.log says:

command: "C:/Program Files/PostgreSQL/12/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "E:/Programs/PostgreSQL/12/data" -o "-p 5431 -b " start >> "pg_upgrade_server_start.log" 2>&1
The program "postgres" was found by "C:/Program Files/PostgreSQL/12/bin/pg_ctl"
but was not the same version as pg_ctl.
Check your installation.

I'm at a loss; what might be wrong, and how can I successfully perform this procedure?


texts: How can I help SQL Server recognize my indexed view column is NOT NULL-able?

texts:
I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes):

CREATE VIEW dbo.balances
WITH SCHEMABINDING
AS
SELECT
      user_id
    , currency_id

    , SUM(transaction_amount)   AS balance_amount
    , COUNT_BIG(*)              AS transaction_count
FROM dbo.transactions
GROUP BY
      user_id
    , currency_id
;
GO

CREATE UNIQUE CLUSTERED INDEX UQ_balances_user_id_currency_id
ON dbo.balances (
      user_id
    , currency_id
);
GO

user_id, currency_id, and transaction_amount are all defined as NOT NULL columns in dbo.transactions. However, when I look at the view definition in Management Studio's Object Explorer, it marks both balance_amount and transaction_count as NULL-able columns in the view.

I've taken a look at several discussions, this one being the most relevant of them, that suggest some shuffling of functions may help SQL Server recognize that a view column is always NOT NULL. No such shuffling is possible in my case, though, since expressions on aggregate functions (e.g. an ISNULL() over the SUM()) are not allowed in indexed views.

Is there any way I can help SQL Server recognize that balance_amount and transaction_countare NOT NULL-able?
If not, should I have any concerns about these columns being mistakenly identified as NULL-able?

The two concerns I could think of are:

Any application objects mapped to the balances view are getting an incorrect definition of a balance.
In very limited cases, certain optimizations are not available to the Query Optimizer since it does not have a guarantee from the view that these two columns are NOT NULL.

Is either of these concerns a big deal? Are there any other concerns I should keep in mind?


texts: converting then outputting varbinary(max) FOR XML PATH

texts:
I have a vendor application that has stored some of its text data in a varbinary(max) column. I am exporting this data (from several tables with different data types), ideally using FOR XML PATH. What is the best strategy for converting this column's data prior/during (function? convert?) export to XML.

Thanks!


texts: Any existing programs that track the origin of data in your database?

texts:
I am doing some integrity work at the moment, investigating cases when something goes wrong with incoming data.

The system I am working with involves multiple servers, including Sybase and SQL Sever 2016. The data is generally pulled from a stream, uploaded into a database in the first Server, cleaned and sent to a database in the next server, etc. Data is also sometimes manually uploaded or manually updated, or entered in through other processes.

Ultimately what this means is if something is wrong with the data, which is only captured/logged in the final server (SQL Server) before being sent out, there are numerous fail points where it can happen. Even different cells in the same row can have different origins by the time the data reaches the final server.

My question is: Is there any existing software which could actually track the origin of a given piece of data? If something goes wrong I would like to have an easier time backtracking the path the data took, to indicate whether the issue was a glitch in our cleaning process or if the data itself had issues from the moment it entered our system.

Alternatively are there any general systems/practices of data management which would make this tracking process either, rather than having to check every step of the way to see where the failure occurred?


texts: change column type from string to numeric

texts:
so I have a table with 5.2 million records. 3 columns in this table are for salaries. Those column are currently stored as string and I'd like to change the data type to numeric (I understood while researching it that numeric is better than float).

I'm trying to run this command:

ALTER TABLE lca_test ALTER COLUMN prevailing_wage TYPE numeric(10,0)
USING prevailing_wage::numeric;

But I'm getting the error

  ERROR:  invalid input syntax for type numeric: "40768,43"

I'm not sure how I can make it work. Any idea?


texts:
Tables:

Clients (20+ fields)
  Id int,
  Name varchar,
  [State] varchar,
  Address varhcar

ClientCheckout
  ClientId
  CheckedOutAt Datetime null

I'm using a separate table for managing the checkout process to avoid putting any locks on the Clients table.

I need to ensure that a client can't be checked out more than once.  Once it is checked out I need to return the id of the client that was checked out.

This is what I came up with but I feel like there should be a better way

Procedure:

Create procedure (@state varchar(2))
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DECLARE @clientid INT

SELECT TOP 1
    @clientid = CustomerId
FROM ClientCheckout
WHERE CheckedOutAt IS NULL AND [state] = @state

BEGIN TRANSACTION T

    UPDATE ClientCheckout
    SET CheckedOutAt = getdate()
    WHERE ClientId = @clientid;
commit transaction t
SELECT
@clientid

I left the initial selection of @clientid outside of the transaction so as not to lock the Clients table.

This seems to work but it doesn't feel quite right.  Suggestions?


texts: Can different tables have columns with the same name if those columns are not primary keys?

texts: I know it's a simple enough question but my textbook is very confusing about it.


texts: Problem with reaching second normal form in a database

texts:
I'm currently taking an Databases/Database server course as a part of my education, and for this week we have been assigned a simple project to complete. I'm facing a problem regarding normalization. I thought I knew how to do it, buy my teacher is saying what I'm doing is wrong and he have a hard time explaining me why it is wrong.

I have four tables in the database:

ZIPCODE
MEMBER
SCORECARD
ROUNDS

This question is only about SCORECARD and ROUNDS. The SCORECARD table has the following attributes:

scoreCardID (Primary key)
memberID (Foreign key to MEMBER table)
date

The ROUNDS table has the following attributes:

scorecardID (Foreign key to SCORECARD table and primary key)
roundID (Primary key, together with scorecardID)
shot1
shot2
shot3
shot4
shot5

There can be between 1-8 rounds on a scorecardID, and each round has five shots. My teacher is now saying, that my ROUNDS table is causing me to be unable to obtain second normal form, which I don't understand. What he says is, that I would have to create another table, that contains the shots, which is then linked to the ROUNDS table.

As I understand the second normal form is, that no non-key attribute can be dependant on any other attribute that is not a part of the key.

Can anyone verify that I'm correct, or if I'm wrong explain to why my ROUNDS table is wrong regarding the second normal form?


texts: Query for total downtime of site using datestamps and status columns

texts:
what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime.

I want to get a year to date for total downtime for any particular site (website). I'm using a sitemonitor to register downtime with a timestamp called datetime. It's the start of the downtime and the following up is when the downtime ended.

There can be multiple down times. The table is an example of what the data looks like.

The downtime may not be in 15 second increments. It could be 10 second to 45 seconds. It would be the difference between the downtime and the following uptime in the table. Is this even possible to be done?


texts: Using a 512 bit binary column as a bit field

texts:
I'm designing a table to keep track of a value that has to represent the on-off setting of several hundred states. I would expand this to 512, to give room for future growth and be a power of two.

So I want a 64 byte = 512 bit column. My problem is that SqlServer bitwise operations don't support right operands larger than int, so there would be no way to access any bits past 32.

How can I handle this issue?


texts: Replicate data between MySQL servers with a different structure

texts:
As part of a system upgrade I need to sync a database between 2 different servers (master-master). The catch is that the structure is slightly different between the 2.

Most of the differences are:-

Columns that exist in 1 but not the other and need populating based on other fields/tables
Type changes where some integer fields are now enums with text values that will need mapping from a static list
Multiple tables have been merged into 1

So far I've been thinking about setting triggers up which would catch all INSERT/UPDATE/DELETE statements and insert them into a "queue" table. A PHP script would then convert the data and write the changes to the other server.

All queries in the system are executed through a central database object so I could potentially add some code to parse each query, create a new query that works with the other server and execute both queries together.

There's also the possibility to only use the new database and use query parsing code to re-format the data on each read/write to make it compatible with the old system.

Data has to be copied both ways (master-master) and it has to be as close to realtime as possible. Has anyone got any better suggestions or advice on how to do this?


texts: Are OLAP / NoSql / In-Memory database mutually exclusive concepts or can there be overlap between them?

texts:
I'm a data scientist with little knowledge of DB technology. I keep hearing 3 types of databases mentioned as key technologies for analytics and modeling solutions:

NoSql databases
OLAP cubes
In-Memory databases

NoSql seems to be anything that isn't relational, so in theory pure OLAP cubes would qualify as NoSql (pure OLAP, not star-schema ROLAP).

But at the same time, I heard a discussion recently by an Oracle engineer about how Oracle spent 2 years trying to convert one of its pure OLAP solutions into NoSql and failed, and that had me confused - if NoSql is anything that is non relational, then it seems to me that pure OLAP is already NoSql, so how can they convert it to NoSql?!?

On a similar note, I hear In-Memory mentioned often as a type of Database that is popular for analytics and modeling solutions, but In-Memory seems to refer to the hardware architecture (using RAM instead of Disk for storage) as opposed to a specific way of representing the data, which seems to me like the key factor in determining whether a database technology is suitable for analytics or not.

So my question is: Are these concepts mutually exclusive or not? Can an OLAP cube be NoSql and in memory as well?

If so, what are some examples?


texts: Why `distinct` and `count` operations generate `"op": "command"` in system.profile

texts:
According to documentation count and distinct queries should result in op = count and op = distinct in system.profile. However when I tried to run such queries in shell both were generated as op = command.

> db.p.count()
23
> db.p.distinct("item")
[ "pencil", "eraser" ]
> db.system.profile.find().sort({$natural: -1}).limit(2).pretty()
{
    "op" : "command",
    "ns" : "test.p",
    "command" : {
        "distinct" : "p",
        "key" : "item",
        "query" : {

        }
    },
    (...)
}
{
    "op" : "command",
    "ns" : "test.p",
    "command" : {
        "count" : "p",
        "query" : {

        },
        "fields" : {

        }
    },
    (...)
}

For remove this works perfectly fine, I execute remove and I get op = remove in system.profile.

> db.p.remove({item: "pen"})
WriteResult({ "nRemoved" : 0 })
> db.system.profile.find().sort({$natural: -1}).limit(1).pretty()
{
    "op" : "remove",
    "ns" : "test.p",
    "query" : {
        "item" : "pen"
    },
    (...)
}


texts: Migration of table from MySQL to Oracle using GoldenGate

texts:
I have a table of size 120 GB in MySQL 5.7 which I need to migrate to Oracle 12c. I am using Oracle GoldenGate for initial load of the table.

I tried with initial load of full table but it consumed complete RAM and I had to cancel it.

Even trying to run with primary key range initial load for 1/20th of table at a time, but it abends after certain time with:

  ERROR   OGG-01233  Send TCP params error: TCP/IP error 110 (Connection timed out)

Is there any better approach to get this completed?


texts:
When I alter a table in DB, I need to reorg it:

Call Sysproc.admin_cmd ('reorg Table myTable');

if I had done many modifications, how can I know which tables need a reorg?


texts: Using PostgreSQL what is the difference between a smallint and a bool for storing boolean?

texts:
What is the difference between the smallint type and the bool type for storing boolean values?

This question arose in the comments to a question on Geographic Information Systems Stack Exchange.


texts: Dynamically creating a new table from a given table name without return type using a PostgreSQL function?

texts:
I want to try using PostgreSQL to create a function which given a table name, an existing column name, and a new column name, it selects all data from this table, applies a function to this column name, and adds this new column. The caveat is that I can't specify a "fixed" return type in the sense that the structure of the table will always be different. Although if it helps, I am managing some metadata about these tables which I can probably supply.

Anyway, keeping my example simple, here it is in PLPGSQL:

CREATE OR REPLACE FUNCTION add_new_col(from_tbl anyelement,some_col anyelement, new_col_name anyelement)
  RETURNS SETOF anyelement AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT *,transformation_func('||some_col||') AS '|| new_col_name || ' FROM '||from_tbl;
END
$$ LANGUAGE plpgsql;

Yeah this is basically a wrapper to a select statement, but I am just using it to highlight my point. If it is really needed to specify the schema of the table which the function will return, can I do this within the function itself? I am not in a position to create a function for each different table structure.

I understand that it is very important for the query to know the table structure in advance. Is there a way to specify it outside the function call? Again, my point is not to create many functions for each table structure.

Is what I am trying to achieve impossible through a function? I get the error ERROR: could not determine polymorphic type because input has type unknown.

If it helps, I am not limited to PLPGSQL and can even use C. But I really need to specify a function/procedure.

EDIT: I found this Are there other ways to select a dynamic list of columns? to be quite similar to my requirements, I am however quite curious if something changed within these 7 years! I hope to call such a function from a CTE, and I also hope to actually be able to CREATE TABLE the result. I wonder how I can achieve that?

In fact if I could do something with CTAS syntax like CREATE TABLE AS select add_new_col(...) would be very helpful.


texts: Physical memory usage approaching 100%(96%)

texts:
Possible Duplicate:
  SQL Server 2008 R2 “Ghost Memory”?

Context:

Windows Server 2008 R2;
32GB RAM;
Application: SQL Server 2008 R2, several Connectors to Write Data into SQL Server(Write Frequency: 0.5HZ);
The Server has not been restart for more than half a year;
About 15G Data per month;
Support Several Applications hosted on other Server(Web Applicaion, Socket Application);

Edit:

Problem(From the Task Manager): In the Performance Tab, The Memory Display and the Physical Memory Usage History Chart both show that the memory usage is approaching 100%(96%); While in the Process Tab, in which the Memory(Private Working Set) Column is shown by default, and the Memory(Private Working Set) Usage of sqlservr.exe is realtively small(200-300M), the total Memory(Private Working Set) usage of all the Processes added up is normal as well(less than 10%).
Maybe the problem is that I just check the Memory(Private Working Set) Column, but I don't have access to the Server right now


texts: Linked Server to Oracle seems to be slow

texts:
we have a linked server between a SQL Server 2008 R2 instance and an Oracle 11.1.0.7 instance using OraOLEDB.Oracle Provider.

The enabled options for provider are:

Dynamic parameter
Nested queries
Allow inprocess
Supports "Like" Operator

The options of the linked server are:

@optname=N'collation compatible', @optvalue=N'false'
@optname=N'data access', @optvalue=N'true'
@optname=N'dist', @optvalue=N'false'
@optname=N'pub', @optvalue=N'false'
@optname=N'rpc', @optvalue=N'true'
@optname=N'rpc out', @optvalue=N'true'
@optname=N'sub', @optvalue=N'false'
@optname=N'connect timeout', @optvalue=N'0'
@optname=N'collation name', @optvalue=null
@optname=N'lazy schema validation', @optvalue=N'false'
@optname=N'query timeout', @optvalue=N'0'
@optname=N'use remote collation', @optvalue=N'true'
@optname=N'remote proc transaction promotion', @optvalue=N'true'

The processes using this linked server just take orders from Oracle, do what it has to do and then sends back to Oracle informations about what have been done.

According to the end users, it seems to be slow.

How can I ...

Assess that the configuration is done appropriately?
How can we make sure that performance is good ? Monitoring queries that goes through it, etc.


texts: gcache.page_sizes and gcache.size are set at the same time. When will gcache.pagebe used?

texts:
After the power failure and restart, the slave node creates a large number of gcache.page, which occupies the disk.

I would like to know if there is already gcache.cache, when will gcache.page be created?

gcache.keep_pages_size = 0;
gcache.mem_size = 0;
gcache.page_size = 128M;
gcache.size = 128M;


texts: What's the best way to track Updated By, Updated On

texts:
In MS SQL Server, what's the best way to update an Updated By, Updated On set of fields on a table?  I've seen it done it triggers, in the code. etc.  The upside I've seen to triggers is that it all happens in the same place.  On the downside there are occasions when an administrator has to bulk fix a table and doesn't want to obliterate the username/time of the last user update.

Note:  I don't want a timestamp.  I want the windows user ID and a human readable date/time of the last change.

Looking for what you recommend and WHY it's a good choice.


texts: Need an Oracle query to get the Oracle Home directory for a particular instance

texts: I am searching for a query to get the Oracle Home for a particular instance name, say Eagle (if there are more than one instance on the host).


texts: "The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'." after adding indexes to temp tables

texts:
Recently,  I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed due to this error and the server needed to be restarted. Can this be related to adding the indexes or it could be something totally else?

Client's server version:

Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64)
Enterprise Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Testing server version:

Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Developer Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)


texts:
how can to prevent duplicate inserts for every Execute?

insert into  IQ (XMLDT)values('<row>sdsdf</row>')


texts: Postgresql Logical Replication Initial Table Sync Stage Restarting

texts:
I'm using logical replication on a Postgres 10 DB (Amazon Linux 2) to Aurora Postgres 11 RDS.
There are 256 tables that need replicating and a few are pretty large (millions to 100s of millions of rows)
Watching the postgresql.log output in Cloud Watch for Aurora Postgres I can see when the synchronization workers start and finish on a per table basis
For example,
2020-11-05 16:09:44 UTC::@:[35430]:LOG: logical replication table synchronization worker for subscription "big_kahuna", table "url" has started

... an hourish later

2020-11-05 17:04:30 UTC::@:[35430]:LOG: logical replication table synchronization worker for subscription "big_kahuna", table "url" has finished

Some of my tables show starting again telling me it's failing and retrying
However, one in particular restarted after about 7 hours which I've been watching progress via the following query on the publishing instance.
SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age
FROM pg_stat_activity
WHERE state <> 'idle'
    AND query NOT LIKE '% FROM pg_stat_activity %'
    AND query LIKE 'COPY%'
ORDER BY age;

    datname     |  pid  | state  |                      query                       |       age
----------------+-------+--------+--------------------------------------------------+-----------------
theusername     | 38228 | active | COPY public.thetable TO STDOUT                   | 07:51:48.124183
(3 rows)

I'm wondering what can be causing the table to have to restart the initial table sync?
I'm closely watching memory consumption and thats no where near being reached (system is at about 100 GB of 512 available RAM) and the same setup and server spec has successfully done this previously.


texts: Can an index be added to a PRIMARY with a null key?

texts:
I have a query that takes a long time to return, roughly 12-15 seconds, which I'd like to optimize.  The query below is the result of running the original query with SHOW WARNINGS.  If I run EXPLAIN EXTENDED on this query (and it's predecessor) it shows me that a PRIMARY select_type is derived, has null possible keys, null key, null key_len, and 314883 rows (see below).

Here's my query:

SELECT DISTINCT `steps`.`TestStepName` AS `TestStepName`
    ,`test`.`measurements`.`MeasurementName` AS `MeasurementName`
FROM `test`.`measurements`
JOIN (
    SELECT `test`.`teststeps`.`TestSteps_ID` AS `TestSteps_ID`
        ,`test`.`teststeps`.`TestStepName` AS `TestStepName`
    FROM `test`.`teststeps`
    JOIN (
        SELECT `dutdataassembly`.`AssyInTest` AS `AssyInTest`
            ,`test`.`testinformation`.`TestInformation_ID` AS `TestInformation_ID`
        FROM `test`.`testinformation`
        JOIN (
            SELECT `test`.`dutdata`.`AssyInTest` AS `AssyInTest`
                ,`test`.`events`.`Event_ID` AS `Event_ID`
            FROM `test`.`dutdata`
            JOIN `test`.`events`
            WHERE (
                    (`test`.`events`.`EventType_ID` = 7)
                    AND (`test`.`events`.`Event_ID` = `test`.`dutdata`.`Event_ID`)
                    AND (
                        `test`.`dutdata`.`AssyInTest` = '41528F'
                        )
                    )
            ) `dutdataassembly`
        WHERE (`test`.`testinformation`.`Event_ID` = `dutdataassembly`.`Event_ID`)
        ) `testinfo`
    WHERE (
            (`test`.`teststeps`.`TestInformation_TestInformation_ID` = `testinfo`.`TestInformation_ID`)
            AND (`test`.`teststeps`.`NumberOfMeasurements` > 0)
            )
    ) `steps`
WHERE (
        (`test`.`measurements`.`TestSteps_TestSteps_ID` = `steps`.`TestSteps_ID`)
        AND (`test`.`measurements`.`MeasurementName` <> '')
        )
ORDER BY `steps`.`TestStepName`
    ,`test`.`measurements`.`MeasurementName`

EXPLAIN statement:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, <derived2>, ALL, , , , , 314883, 100.00, Using temporary; Using filesort
1, PRIMARY, measurements, ref, fk_Measurements_TestSteps1_idx,idx_Measurements,idx_measurementName,idx_justNames, fk_Measurements_TestSteps1_idx, 4, steps.TestSteps_ID, 1, 100.00, Using where
2, DERIVED, <derived3>, ALL, , , , , 16237, 100.00,
2, DERIVED, teststeps, ref, fk_TestSteps_TestInformation1_idx,idx_testinformationid, fk_TestSteps_TestInformation1_idx, 4, testinfo.TestInformation_ID, 9, 100.00, Using where
3, DERIVED, <derived4>, ALL, , , , , 16237, 100.00,
3, DERIVED, testinformation, ref, fk_TestInformation_Events1_idx, fk_TestInformation_Events1_idx, 4, dutdataassembly.Event_ID, 1, 100.00, Using index
4, DERIVED, dutdata, ref, fk_dutdata_Events1_idx,dutdata_index, dutdata_index, 138, , 33116, 100.00, Using where
4, DERIVED, events, eq_ref, PRIMARY,fk_Events_EventType1_idx, PRIMARY, 4, test.dutdata.Event_ID, 1, 100.00, Using where

The table measurements has hundreds of thousands of entries in it and has been indexed.  The table teststeps has an order of magnitude fewer entries and has also been indexed.

The desired result of this query is a one to many table where each teststep can have many measurements associated with it.

I hope that's enough information to go on.


texts: How to query all rows of a table into single json Postgresql?

texts:
I am trying to generate a single json by querying from a table with id as key and email as value like this

{1: "abc@example.com", 2: "abcd@example.com"}

My attempt to create is:

select json_agg(json_build_object(id, email))
from auth_user
where id in (4733, 3625, 4961, 4237);

which results me

[{4733: "abc@example.com"}, {3625: "abcd@example.com"}]


texts: Returning data based on if another column in another table is NULL

texts:
I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been billed. In a separate table is a list of reports, once a report has been billed a number is populated in the Report_Billing_Num column. How do i get the below code to show non-billed orders?

SELECT
      [Call_Num],
      [Call_Cust_Num],
      [Call_Cust Name],
      [Call_Status]
FROM tableCall

JOIN tableREPORTS
ON tableCALL.Call_Num = tableREPORTS.Report_Call_Num

WHERE tableCALL.Call_Status = 'COMP' AND tableREPORT.Report_Billing_Num IS NULL

ORDER BY Call_Status

This code kind of works, however in the tableREPORTS table several rows can be in the table for the same Call_Num which seems to be showing duplicated when i run this script. How would i have it so duplicated aren't shown? Would it be better to use a NOT IN or NOT EXISTS query?


texts:
I have an MS Access query that connects to an Oracle Express database via an ODBC connection.

I've figured out a way to trace the SQL that is sent from MS Access to the ODBC connection. This is what the ODBC SQL looks like (I've formatted it a bit for readability):

SQLExecDirect:
SELECT
    "INSP_ID" ,
    "ROAD_ID" ,
    "INSP_DATE" ,
    "CONDITION"
FROM
    "USER1"."ROAD_INSP" "b"
WHERE
    ("INSP_DATE" = (
            SELECT MAX("a"."INSP_DATE" )
            FROM "USER1"."ROAD_INSP" "a"
            WHERE   (("a"."ROAD_ID" = "b"."ROAD_ID" )
                AND ("a"."INSP_DATE" BETWEEN  ? AND  ? ))))   <-------- Question marks

As you might have noticed, the last line has question marks instead of values.

This is because the MS Access query prompts the user to enter values via parameter prompts.

However, I'm not sure what these question marks are supposed to represent. I know that they originated from the MS Access parameter prompts, but what do they mean to Oracle, and how does Oracle get the parameter values?


texts: What is most efficient way to compare two large result sets in SQL Server 2012

texts:
Current advice for the most efficient way to compare two large result/row sets seems to be to use the EXCEPT operator. This self contained SQL script below gets very inefficient as row sizes increase (change @last values). I have tried to find unique entries in a combined table but with no improvement.

DECLARE @first AS INT, @step AS INT, @last AS INT;

-- This script is comparing two record sets using EXCEPT
-- I want to find additions from OLD to NEW
-- As number of rows increase performance gets terrible
-- I don't have to use two tables. I could use one combined table but I want the same result as quickly as possible

-- Compare 100 to 110 rows - 0 seconds
-- Compare 1000 to 1010 rows - 1 seconds
-- Compare 10000 to 10010 rows - 16 seconds
-- Compare 100000 to 100010 rows - ABORT after 8 minutes (tables are populated in 18 seconds)

DECLARE @temptableOLD TABLE ([Result1] int);
SET @step = 1;  SET @first = 1; SET @last = 100000
WHILE(@first <= @last) BEGIN INSERT INTO @temptableOLD VALUES(@first) SET @first += @step END

DECLARE @temptableNEW TABLE ([Result1] int);
SET @step = 1;  SET @first = 1; SET @last = 100010
WHILE(@first <= @last) BEGIN INSERT INTO @temptableNEW VALUES(@first) SET @first += @step END

select * from @temptableNEW
except
select * from @temptableOLD


texts: DB2 9.7 LUW - How do I SET SESSION_USER when I am DBADM,SECADM?

texts:
Based on a previous question I have asked, I am attempting to work on a solution as recommended by mustaccio's answer, whereby I have another ID to create my DDL objects.

I have been trying to follow an IBM developer works article called "DB2 security, Part 8: Twelve DB2 security best practices" in my attempt to do it. I have noticed it appears the article was written for DB2 8.2 at the time. I am currently working on DB2 9.7 fix pack 4 ESE for LUW. Specifically I am on AIX. I am currently testing out how to do this on my local system, which is Windowx XP SP3 and using DB2 9.7 fix pack 4 Express-C for LUW.

I have my local user account (which is the instance owner) called db2admin in this case (not so on our AIX systems). This ID belongs to the DB2ADMNS group. Since it is different from my Windows account I make sure I attach to the instance first and then create my database to assure that db2admin is granted DBADM,SECADM,DATAACCESS,ACCESSCTRL on my database.

db2 attach to DB2 user db2admin using ********
db2 create database SECTEST automatic storage yes on 'C:\' dbpath on 'C:\'
  using codeset UTF-8 territory US collate using system

After that I connect to the system as db2admin

db2 connect to sectest user db2admin using ********

Then to test things out, I made sure I granted CONNECT and SECADM to my Windows account

db2 grant connect,secadm on database to user myid

And then I wished to set up my bogus ID to create objects under. I intentionally create this ID as nine characters to help with preventing connection to DB2 from the command line, but still allow it via SET SESSION_USER. For the sake of simplicity I have named my ID bobabob97.

So I have done the following:

db2 grant CREATETAB,IMPLICIT_SCHEMA,BINDADD,CREATE_NOT_FENCED_ROUTINE,
  CREATE_EXTERNAL_ROUTINE on database to user bobabob97
db2 grant use of tablespace userspace1 to user bobabob97

At this point I think I have everything set up correctly. I have explicitly not granted bobabob97 CONNECT on the database as I do not want anyone to be able to connect to the database with this ID.

So now I try to switch over to that ID to create test creating my objects and owning them under a generic ID.

db2 set session_user=bobabob97

But then I get the following error:

  DB21034E  The command was processed as an SQL statement because it was
  not a valid Command Line Processor command.  During SQL processing it
  returned: SQL0552N  "DB2ADMIN" does not have the privilege to perform
  operation "SET SESSION AUTHORIZATION".  SQLSTATE=28000

So, now I am puzzled. The ID I am currently connected to the database as (db2admin) has DBADM,SECADM,DATAACCESS,and ACCESSCTRL on the database (and of course it implicitly holds CONNECT,CREATETAB,BINDADD,QUIESCE_CONNECT,IMPLICIT_SCHEMA,LOAD,CREATE_NOT_FENCED_ROUTINE, and CREATE_EXTERNAL_ROUTINE). I am essentially "god" on the database. Why can I not switch to this ID?

I tried playing with GRANT SETSESSIONUSER, but I know SECADM can't grant to itself. What do I do here?


texts:
I have 2 VMs, both are identical in structural terms (came from the same snapshot, nothing installed, upgraded or removed).

One VM is "clean", never used.

The other VMs was heavily used and it has some data stored in the Postgres DB.

Is there a way to migrate the Postgres database from the heavily used VM to the clean VM just by copying the DB files from a specific folder?

For instance, let's say that Postgres (I'm assuming this part) writes all its files in the directory XYZ. If I copy all files from that XYZ directory to the clean VM, will Postgres work?

I'm not worried if that is the correct way to do that or not. This is just a PoC and I need to do it with the least effort possible and copying files around seems to be the best fit here.


texts: Executing the same SQL statement for different tables

texts:
I have a stored procedure that deletes data from multiple tables.  The SQL is all the same, except for the name of the table.

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table1 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table2 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

...

UPDATE desttable
SET desttable.reportarea = esz2reportarea.reportarea
FROM table10 AS desttable JOIN esz2reportarea ON desttable.subdivisn = esz2reportarea.esz
WHERE desttable.reportarea = ''

I'd like to rewrite this to use some kind of loop, so that the SQL is only written once.  (This is for SQL Server 2008R2.)

The list of tables that need to be updated will remain static; if there are any changes to the list of tables to be updated, it would be due to an application upgrade, and I'd have to redetermine which tables need to be modified at that time.  The list of tables to be updated will be placed into a temporary table.

SELECT * INTO #tablenames
FROM (VALUES ('table1'), ('table2'), ('table3')) AS tables(tablename)


texts: Azure Elastic Job step to a privatelink sqldatabase target fails with "target principal name is incorrect" (Trust Server Certificate works in SSMS)

texts: My first Elastic Job Agent jobstep to a privatelink sqldatabase target fails with "target principal name is incorrect." I can connect to servername.privatelink.database.windows.net and the specific database in SSMS with the same SQL login if I check "Trust Server Certificate" on the connection options, so the elastic job login, credentials, user are set up correctly. I don't see a way to specify Trust Server Certificate when setting up the jobstep.  The Azure sql server has transparent data encryption enabled via a service managed certificate, as do all the databases on the sql server (via inheritance, i assume).  Is there any way to force the job step to trust server cert?  Thanks!


texts: Troubleshooting Preemptive_Xe_Dispatcher wait type

texts:
While looking for a reason behind slowness of the database server I accidentaly came across Preemptive_Xe_Dispatcher wait.

Could you please suggest how this type of wait should be troubleshooted? Information coming from google is very generic and disn't provide any practical solutions.


texts: Maximum number subscribers node in PostgreSQL logical replication?

texts: Does anyone know what is the maximum of subscriber nodes with PostgreSQL 11 logical replication ?


texts: Advantages/Disadvantages of using a multicolumn clustering index

texts:
What are the advantage/ disadvantages of using a primary key that is a combination of multiple columns?

I have come across a table that has a unique non clustered index as a GUID but the clustering index is applied on the combination of 9 columns. In total the table has 29 columns.

Can anyone help me understand what might be the benefits of having such a clustering key? As per Kimberly Tripp  || Index Debate by Kimberly Tripp the clustering Index should be narrow, unique and static, but this case is totally opposite to what Kimberly states:

the primary key is susceptible to changes so its not static
It is definitely not narrow as it spans multiple columns and other non clustering indexes refer to it for mapping. so it is definitely expensive for IAM/PFS.


texts: How do I run a SQL Server 2005 job on Windows 7?

texts:
I have Windows 7 and SQL Server 2005 Version 9.00.1399.

I can successfully create an SSIS Package, however I am unable to execute it successfully as a scheduled job. When I run the job, SQL Agent reports Unable to start execution of step 1 (reason:Could not get proxy data for proxy_id = 1). The step failed.

All SQL services are running successfully.

The same kind of activity on Windows Server 2008 R2 Edition and SQL Server 2005 Version 9.00.4035 works successfully.

Does anyone know why this wouldn't work on Windows 7?


texts: Query store works in dev, but not production in Amazon RDS

texts:
I'm having an issue getting query store to collect and record data on our production workload, however, it works as expected on our non-production instances (development and staging).
The only difference between these three environments is that production is a multi-AZ RDS instance, while dev and staging are single AZ.

All instances are on 2016 SP1 CU7.

The query store settings are identical across all servers and databases, and are as follows:

actual state: read_write
flush_interval: 15 mins
query_capture_mode: all
size_based_cleanup: auto
max_plans_per_query: 200
The max storage size varies based on the database activity level; the minimum I have set is 500mb, and the max is 2gb.

It may also be worth noting that our staging server creates daily from a snapshot of production, and query store seems to be working perfectly on that after being created.

Does anyone have any suggestions on what I may be able to try next, or know of any caveats with query store in RDS?


texts:
We have a 1TB table that gets queried a lot.  I have suggested we use Data Compression to save on space as well as help put more in memory.  Because of the concern of CPU overhead, I have been tasked with proving our system can handle the rise in CPU.
I have got a test server that matches our memory of the production server where the 1TB resides.  My plan has been to use a compressed and uncompressed version of the database on the test server to use the most active stored procedure against this 1TB table to capture Logical Reads, Physical Reads and CPU to use as comparisons for data compression efficiency.
I have captured this stored procedure using an RPC Completed Extended Event and exported to a table so that I have the statements to use on the testing server.
Questions

Should/Can I convert Extended Events data from a source into a Trace File to use with Distributed Replay?

Rather than using thousands of the sample stored proc, should I simply focus on running 1 stored procedure call say 10 or 15 times using the non-compressed db and then the compressed db and compile the results this way?

If there is a better solution or recommendation to this problem, I would like any suggestions.
SQL Server 2012 Enterprise Edition.  The 1TB is all clustered and non-clustered indexes.  This is a table holding all of our products within our catalog so most heavily used table within the database. The table receives a lot of reads as well as inserts and updates.


texts: Azure SQL Db Linked Server - Secured Connection Methods?

texts:
The developers want a linked server from our on-prem server to our Azure SQL Db (DBaaS) in order to complete a nightly data pull into our data warehouse.

I'd much rather go the route of SSIS but don't have the time to properly develop the package or teach them how to support it.

My main concern at this point is ensuring the data is encrypted in transit.

Once the linked server is in place along with a corresponding Agent job, my options appear to be:

sqlcmd (with the -N switch, "...used by the client to request an encrypted connection."
Invoke-SQLcmd Azure PowerShell cmdlet (with the -EncryptConnection switch)
OR... Azure SQL Data Sync (Hub to Member), which as of this writing is still in preview but looks promising!

Are there any gotchas or special considerations for these methods that I should be aware of?


texts: Windows native backups makes SQL Server think that databases backup has been done

texts:
community.
I've Windows Server 2008 R2 which hosts few virtual machines.
One of machines is Windows 2008 R2 server with SQL server 2008 R2 Express installed.
Here's the thing:
I wrote a script that backups databases at 05:00AM everyday.
Mon, Wed, Fri - Full backups.
Tue, Thu, Sat, Sun - Transaction log backups.

A few days ago i had to try to restore data from backups and i couldn't. I recieved an error message that my Transaction log is to recent to use. It was on Tuesday. So basicaly i had to restore monday's full backup and tuesday's early morning transaction log backup.

I started to research the cause of it and soon discovered that everyday at 04:00AM and 11:00PM sql server backups all the databases to some VIRTUAL_DEVICE with two different SIDs.
I realized that at 11:00PM starts Windows Server Backups on that concrete virtual machine. Backup contains only Bare metal recovery + System state + C:
Later i understood from where comes backup at 04:00AM. There's similar story. At 04:00AM starts backup of Hyper-v Host with the same parameters(it's strange but sql server somehow realizes that machine is being backed up.

So we have:

Backup of Virtual machine at 11:00PM
Backup of Hyper-V host at 04:00AM

After procedures described above EventLog recieves message that SQL server has been backed up. But There's no physical place where those "backups" are stored. Since SQL Server thinks that he'd been backuped he change LSNs so any correct backups of transaction logs can't realy be restored after another correct full backup.

Please note: Hyper-v host and virtual machine itself are being backed up to the personal colume for backups. After assigning letter to the volume i can see VHDs of disk that had been backed up. And no sign of sql databases backup inside.

The main problem is to stop SQL from reacting on system backups.

Looking forward for your replies and thanks in advance,
Alexey.


texts: How do I select the count of a specific value without grouping in postgresql?

texts:
I have a view that's a result of a UNION of two selects. It has contents similar to this:

id foreignkey
1  a
2  a
3  b
4  c

I'm looking to add a column in the view's definition that will show a count for the values, but without aggregating them. Somthing like this:

id foreignkey count
1  a          2
2  a          2
3  b          1
4  c          1

my existing query is basically:

create or replace view x as
select * from a
union
select * from b

I want the count on the result of the union but I"m not sure how to do that.

EDIT - made some progress, with a normal table I can do this:

SELECT *,
 (select count(id) from mytable where t.data = data)
FROM mytable as t

The problem comes into play because the table is really a union of two tables. Not sure how to do it without doing the union twice?


texts: How can I run a pgAgent job every 10 second?

texts:
I have a PostgreSQL 11.3 Database. I installed pgAgent for creating jobs. Now I want to run my job every 10 second, but in GUI there is possibility to write with precision of minutes, not with precision of second.

Is it possible to run job created by pgAgent faster than every minute?


texts: Unable to browse to folder on SSRS 2012 through report url

texts:
I have installed SSRS 2012 in native mode on windows server 2012 r2 and deployed reports to the server successfully. I am having difficult browsing to any report folder through the report URL. I can browse to the report server home folder using the following URL:

http://192.168.8.1/My_ReportServer

From this URL I can then successfully navigate through the folders and run the reports correctly.

If I try to navigate directly to one of the folders in the home folder through URL.

http://192.168.8.1/My_ReportServer/test

I receive the following message:

Server Error in '/My_ReportServerApplication.

The resource cannot be found.
  Description: HTTP 404. The resource you are looking for (or one of its dependencies) could have been removed, had its name changed, or is temporarily unavailable.  Please review the following URL and make sure that it is spelled correctly.

Requested URL: /My_ReportServer/test.

Does anyone know the cause of this problem? or point me in the right direction?

Many thanks


texts: MYSQL Social Networking website - Database Design, Data Growth and Hardware

texts:
Introduction

I have a system around one million users (community based). It has around 6000 groups (fixed , no change). But there is no discussion board for these groups yet.

Building discussion forum

We are building a discussion forum for the above groups. Each group has 12 categories (fixed, never change). The discussion forum is like Facebook wall. We will have 12 Facebook walls for each group (one wall for one category).

Database Design

For the above mentioned features I have 12 main tables (for each category) and 12 comments tables (for each category). Total 24 tables.

All the tables have similar structure (main and comments table).

CREATE TABLE `tdb_forum` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
   group_id mediumint unsigned NOT NULL,
 ..... Other columns .....
 )
 CREATE TABLE `tdb_forum_comment` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
    forum_id int unsigned NOT NULL ,
  ..... Other columns ..... )

I am going to use MySQL 5.5 and InnoDB

Data growth expectations

Since one million users are ready to use it immediately after launching, average 1 entry for person in main table per day and average 4 entries for person in  comments table. So per month comments table reach 60 million records in month.

Hardware I am planning

Base Hardware

Intel Xeon E3-1230 (3.20GHZ 8MB Cache)
16GB DDR3 ECC
Primary Disk: Hardware Raid-1
2x2000GB 7.2k RPM SATAII RE4 64MB Cache

Additional Options

  Hardware RAID Controller : Adaptec 2405 - 128MB Cache - 4 Ports
    Chassis : Standard SuperMicro Rackmount
    Public Port Speed : 100Mbs Public
    Public Bandwidth : 10TB Premium Transfer
    IP Addresses : 8 IP Addresses
    Control Panel : cPanel / WHM

Questions I have

Since the data growth is 60 Million per table (per month), should I create a separate database for each group (6000 databases) having all the 12 tables, so in future I don't have to go for any partitioning or sharding? It will be straight forward in reading and writing if my code handles all the database connections well. Even for future growth (max 15 million users), I will not have any problems. But having 6000 databases, do you think any issues like for back up and for replication?
For the main tables and comments table, is it OK to have int as an ID or can I change it to BigInt  or any other suggestions?
Do you think my hardware is good for initial one million users for an average use? Or am I renting too much? If so, what is the best hardware for my case, at least for first year?


texts: Is it possible to reuse an alias more than once in MySQL query?

texts:
Is there a way to reuse an alias more than once in MySQL? I need to CONCAT several aliases into a new column (Ref). The values from columns Option, Make, and Model have been formulated earlier in my query using CASE statements. The Ref column would CONCAT Qty, Option, Make and Model. I'm running MySQL version 5.7.29.
Thank you in advance!

`Qty` | `Option` | `Make`| `Model` | `Ref`
******************************************************
1     | Red      | VW    |         | Red / VW
2     | Blue     | POR   |  G4     | 2x Blue / POR  G4

The goal is to re-use an alias (e.g. Make) from the CASE statement

CASE
  WHEN `order_product`.`name` LIKE '%Volkswagen%' THEN 'VW'
  WHEN `order_product`.`name` LIKE '%Volvo%' THEN 'VOL'
  ELSE `order_product`.`name`
END AS `Make`,


texts: Can you set up a SQL server maintenance plan to restore a database?

texts:
I am looking to setup a maintenance plan that makes a backup of a database (Database1) and then restores to another database (Database2) to confirm that it is able to restore successfully.

Can I do this as part of a maintenance plan and have SQL server mail me if it fails either the backup or the restore?


texts: Cannot log in with default user on Mac 10.10

texts:
I am running PostgreSQL on a Mac 10.10

  PostgreSQL 9.3.5 on x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn), 64-bit

I am having trouble connecting to the _postgres Mac account. As far as I remember, the under Mac created account is not postgres but _postgres. I want to log in as that account to access Postgres and create a new role/database for some user on the system.

sudo -u _postgres psql

Throws me this:

  psql: FATAL:  role "_postgres" does not exist

While:

sudo -u postgres psql

gives me this:

  sudo: unknown user: postgres

So, how to login as the default Postgres user?!


texts:
Right,

I powered off development server because it was hanged, when restarted directory services database was corrupted, so fixed it, then figured out that IIS Manager's metadata.xml is messed up, so fixed it using backup too.

But now SQL Server service isn't starting giving this error,

  Cannot recover the master database. SQL Server is unable to run.
  Restore master from a full backup, repair it, or rebuild it. For more
  information about how to rebuild the master database, see SQL Server
  Books Online.

Right I already tried adding network service permission to all files and databases still no luck, i also read after googling that compressing it might work but it don't know what they mean.

How can I fix it ?

I tried

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap>start /wait setup.exe /
qb InstanceName=MSSQLSERVER SQL_Engine REBUILDDATABASE=1 SAPWD=mypassword REINST
ALLMODE=omu

and

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap>start /wait setup.exe /
qb  /action=rebuilddatabase /quiet /InstanceName=MSSQLSERVER /sqlsysadminaccount
s=domainName\userName /sapwd=123

but no gain only pain.


texts: Is there a way to pass two SQL tables w/o JOIN for processing into R using MS SQL Server 2016?

texts:
Is there a way to pass two SQL Server tables without a JOIN for processing into R using MS SQL Server 2016?

For Example, @input_data_1=TABLE, passes the 'TABLE' as a dataframe into R with default variable name InputDataSet.

Is there a way to somehow access other tables residing in SQL Server through R?


texts: How should I distribute my tables for better performance from fragmentation point of view

texts:
I have a database on SQL Server 2008 R2 with about 20 non-indexed tables that are continuously  undergoing INSERTs AND DELETEs.  There are about 400,000 records @ 4KB/record being inserted or deleted each time.

This much action on the tables will undoubtedly cause fragmentation.

Along with the previous tables, I have about other 20 tables that hardly change inside the same filegroup.  They are historic tables and grow month after month by approximately 400,000 records @ 4K/record per month.

Even if I make the filegroup files big enough to hold the data and reduce physical fragmentation by disabling auto growth, the internal fragmentation will eventually get out of control, causing performance to drop.

I am not talking about physical fragmentation of the database files either, nor vertical/horizontal fragmentation of tables.

Should I make one filegroup for the small change tables and one for the high change tables or should I distribute the tables evenly?  For example; 10 high change tables with 10 small change tables in one group and the other half in a second group?

I want to reduce performance losses due to fragmentation of the tables (heaps).


texts: What variable(s) to look to maintain MySQL speed of scanning through all database tables while it starts?

texts:
We have few thousand small databases with total .ibd files of about 800000.

We noticed that MySQL takes couple of minutes to stop but about two hours to start.

For e.g. while MySQL is starting and it's process ID is 26980 and if we look at following command we can see MySQL scanning through all database tables alphabetically.

watch -n 1  'ls -l /proc/26980/fd | grep .ibd'

In first few minutes speed of mysql is about 2 databases per second but after about an hours speed gradually drops to 1 database per 10 seconds, because it keeps getting slower, maybe there is some kind of cache that has exhausted.

My questions is, What variable(s) to look for to maintain MySQL speed of scanning through all database tables while it starts?

We tried playing with table_open_cache, table_definition_cache, tmp_table_size but it does not seems to affect this speed.

Update 1

We copied all data files to an testing server and installed Percona Server 56-5.6.15 in place of Percona Cluster. Server start time is normal, it is starting in few minutes whereas all nodes of our Cluster takes about 2 hours to start.

We tried starting cluster in an isolated mode where it does not connect with other nodes in cluster. Startup time is still 2 hours.

Update 2

We installed Percona Cluster on a testing server with same data. It worked normally and server started in few minutes even though there were same number of databases. Testing server is a small VPS whereas main server is huge. We copied my.cnf from main server to VPS to ensure all variables same.

After Update 2, we have no idea why all our nodes are behaving like this.


texts:
I want to test the restore of erroneously dropped partitions of a subpartitioned table.
This restore would consist of three steps:

restore the database (or at least the necessary tablespaces) to the needed point in time to a different space.
extract the dropped partitions from this auxiliary database created from the backup
import the partitions to the original database where they were erroneously dropped

The restore step 1  is not difficulty  but I have problems when I test the import/export.

For this I create a schema in a test database where I can simulate the drop and the restore.
It is a large database and the restore should be fast, this means parallel import with direct read.
Therefore  I cannot import in an existing table because it does not allow parallel import. So  have to create a new table. Then I do a switch partition to get the imported partitions into the table where I dropped them.
I already have a schema with the same name in the test database and I do not want to touch this schema so I create a new one  for this test.

I do the following:

I create the necessary tablespaces on the test database.
I create a user named RESTORE_USER on the test database with appropriate grants and tablespace quotas.
I export some partitions from the ORIGINAL_USER.ORIGINAL_TABLE of production database.

DUMPFILE=...
LOGFILE=...
DIRECTORY=...
TABLES=RESTORE_USER.RESTORE_TABLE
CONTENT=METADATA_ONLY

I import this dump file into the test database.

DUMPFILE=...
LOGFILE=...
remap_schema=ORIGINAL_USER:RESTORE_USER
DIRECTORY=...
TABLES=ORIGINAL_USER.ORIGINAL_TABLE

I drop some partitions on the test database

alter table ORIGINAL_USER.ORIGINAL_TABLE drop partition PART_20141101;
alter table ORIGINAL_USER.ORIGINAL_TABLE drop partition PART_20141102;
...

Now I have the structure on the test database.

I export the partitions that I have dropped from the production database

DUMPFILE=...
LOGFILE=...
DIRECTORY=...
PARALLEL=8
TABLES=ORIGINAL_USER.ORIGINAL_TABLE:PART_20141101,ORIGINAL_USER.ORIGINAL_TABLE:PART_20141102,...

I import this on the test database and the problems start.

DUMPFILE=...
LOGFILE=...
remap_table=ORIGINAL_USER.ORIGINAL_TABLE:RESTORE_TABLE
remap_schema=ORIGINAL_USER:RESTORE_USER
DIRECTORY=...
TABLE_EXISTS_ACTION=APPEND
PARALLEL=8
TABLES=ORIGINAL_USER.ORIGINAL_TABLE:PART_20141101,ORIGINAL_USER.ORIGINAL_TABLE:PART_20141102,...

The import of the partitions works fine and was done in parallel as expected. But  the creation of the indexes was not possible and the following error messages could be found in the log file:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RESTORE_USER"."RESTORE_TABLE":"PART_20141106"."PART_20141106_01"  1.234 GB 123456 rows
...
...
...
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
Failing sql is:
CREATE INDEX "RESTORE_USER"."ORIGINAL_INDEX" ON
"RESTORE_USER"."ORIGINAL_TABLE" ...

The creation of the indexes was not possible because the indexes weren't remapped to the new table names.
Does anyone know how I have to parametrize the import that this will work?

I already imported the indexes to an SQL file. And there I also can see that they were not remapped. I do not want to sue the statements from the sql file to create the indexes. I want to solve it by running impdp.

regards guenter


texts:
I am completely new to Oracle DB Administration an PL/SQL.

I get an "invalid character" error trying to execute the following code in Oracle SQL Developer.
This is my first statement in PL/SQL.

SET SERVEROUTPUT ON

DEF username = 'cuser2'

DECLARE

   i_matching NUMBER(1) := null;
  --  username Varchar(20) := 'cuser2';

BEGIN

   SELECT COUNT (1) INTO i_matching FROM dba_users WHERE username = UPPER('&username');

  DBMS_OUTPUT.put_line(i_matching);

   IF i_matching = 0 THEN

    EXECUTE IMMEDIATE ('
    create user &username IDENTIFIED BY &username;
    GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO &username;
    ALTER USER &username DEFAULT TABLESPACE USERS;
    ALTER USER &username TEMPORARY TABLESPACE TEMP;
    ');

   END IF;

END
;
/

I definitely lack knowledge about the PL/SQL syntax and created this code from examples in the web.
I guess the error is because of a misplaced ';' somewhere but I don't get it.
It would be great to get a hint on what I am missing here.

This kind of code pretty much seems to be the only way to implement a 'create and alter user if not exists' behaviour. At least it's the only method I found on the web.

Coming from sripting languages I naively expected something like the following to work but I guess nothing similar is implemented in Oracle:

-- create and alter user if not exists
IF (NOT EXISTS user cuser1) THEN

create user cuser1 IDENTIFIED BY cuser1;
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO cuser1;
ALTER USER cuser1 DEFAULT TABLESPACE USERS;
ALTER USER cuser1 TEMPORARY TABLESPACE TEMP;

END IF;

Thank you in advance for any hints.


texts: Postgres function to merge two json objects with overlapping keys into one object

texts:
I've got the following JSON object:

{
    "a" : {
        "0" : 2,
        "1" : 4,
        "3" : 6,
    },
    "b" : {
        "2" : 8,
        "1" : 10, /*note this key exists in "a" too*/
        "4" : 12,
    }
}

I'd like to generate the following object and then be able to extract an element from it like so:

{
        "0" : 2,
        "1" : 10,
        "2" : 8,
        "3" : 6,
        "4" : 12,
}

Extraction: object->>'1' should return '10'

Basically, I have two arrays with potentially overlapping keys and I want to merge the two, giving one array precedence.

How can I accomplish this? Ideally I'd call a function like arrayMerge(a, b) and it gave 'a' higher precedence than 'b'


texts: Error when start to attach mdf - : parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control)

texts:
Got the following error when first attempted to attach a database

  parameter name: ncolindex actual value was -1 (Microsoft.Sql Server.Grid Control)

What does this mean and how do I correct ?


texts: Group by unique pairs with no multiplicity

texts:
I have a table AreaLocation with AreaID as the PKey and Location whereby there are many locations for each AreaID. Correspondingly there is another table Books with:
BookingID | DriverID | BLocation | Miles | Fare

I'm trying to get the average efficiency of each driver by area, i.e. I'd looking for a view with
| DriverID | AreaID | AVERAGE(Fare/Miles) |

where I'd like to group by distinct DriverID/AreaID however I'm not sure whether the following will work:
CREATE VIEW EFFICIENCY AS
 SELECT AreaID, Bookings.DriverID, AVERAGE(Bookings.Fare/Bookings.Miles)
 FROM AreaLocations
 RIGHT JOIN Bookings ON
 ( AreaLocations.Location = Bookings.Location )
 GROUP BY DISTINCT (AreaID, Bookings.DriverID)

as I don't think distinct works on pairs.


texts: SQL Server 2005 and Windows 8.1 - Getting error 1053 when trying to start sqlexpress service

texts:
We've been running SQL Server 2005 and Windows 8.1. Today for the first time we can't get sqlexpress running in services. It was running fine yesterday.

Getting

  error 1053 - service did not respond to the start or control request.

What steps should I take to troubleshoot this?  Thanks.


texts: Why is the install of SQL Server 2012 in Windows Server 2012 taking over previously accessible hard drive's?

texts:
This is a harder one to ask since I don't really have a clear picture of what I'm asking.

But, in a plain vanilla install of Windows Server 2012 (with only Web Server role/features enabled), installing SQL Server 2012 on top of it is taking over drive's (i.e. I get the "Access Denied" message when trying to open them up through explorer).

I have 3 drive's, C, D and E. During the installation of SQL Server 2012, I specify to install the core files to C:\Program Files... (i.e. the default destination), the temp database and tempdb log to D:\ (root) and the user databases and user db logs to E:\ (root). Before install, I can access the drives as expected but after the SQL Server 2012 installation completes I can no longer access the drives. i.e. Can't open them through explorer and the usage (space used) bar indicator doesn't display either.

Does anyone know what the cause of this is? Is it because I'm specifying installs to the root's of D and E (C still operates normally)? Is it something with SQL Server 2012 security? The install was done through an Admin account and I'm going back and trying to access the drive's through an Admin account.

It doesn't inhibit the actual usage of the database or creating new databases, as everything seems to function properly, it just bugs me that this is happening and don't want to use this for a production web machine (used just for IIS 8 and SQL Server) until I at least get a better understanding of this.

Any clarification is greatly appreciated.


texts: Last record by date given a foreign table

texts:
Consider this 2 tables:

CREATE TABLE massifs (
id uuid primary key,
name text unique);

CREATE TABLE records (
id uuid primary key,
massif_id uuid  not null references massif(id)
record_date timestamptz not null
some_usefulldata text)

I'm trying to get the last result for each massif. Something with a subquery ?

For example a result could be

massifs.name | record_date::date | some_usefulldata
mont-blanc   | 2020-02-10        | 10
aravis       | 2019-03-10        | 12

I think that the question is the same if I add this 2 tables : https://www.postgresql.org/docs/current/tutorial-fk.html and if I ask this question "give me the last weather records for each cities".


texts: How to install SQL Server on a different machine and set it to be a live replica of the first

texts:
Configuration:

I've a SQL Server 2008 R2 on a windows 2003 server.
It handles quite well a 300 GB db.

The Problem:

The problem is we are planning to implement a feature in the customer's software that really will over-stress the sql server and our test convinced us it is probably time to migrate from a single r/w node to a r/w node _plus a read only node to do a minimum of load balancing.

It is a completely new scenario for me, and I've no idea of where to start.

I can install another sql server, I've already done it.

Question:

But how to install a SqlServer on a different server and set it to be a live replica of the first (without turn off the first one) ?

I am not a DB Admin, I inherited this 'problem'.


texts: How can I set a JSON value to a boolean in SQLite?

texts:
I know that SQLite doesn't have a boolean data type, but for a JSON column I'd like to set the value to a real boolean instead of a 0/1 faux-boolean, but SQLite always seems to set it to an int.
Example:
sqlite> create table test (j text);
sqlite> insert into test values ('{"b":true}');

Make sure we've got valid JSON and extract the value:
sqlite> select json_valid(j) from test;
1

sqlite> select json_extract(j, '$.b') from tests;
1

Okay, so extracting a value converts it to a "native" SQLite data type, which makes sense and is documented in the json_extract() docs, but setting it also does, which is a bit annoying:
sqlite> select json_set(j, '$.b', true) from test;
{"b":1}

sqlite> select json_set(j, '$.b', 'true') from test;
{"b":"true"}

I can write some code in my application to recognize 0/1 as boolean and convert it, but I'd prefer not to just because one database migration needs to move some JSON fields from one column to the other as it introduces some complexities down the line (I need to change some types and then also modify some generic functions which currently accept only a bool type to be polymorphic).
Is there any way to set this to a real JSON boolean like {"b": true}?
I am using SQLite 3.34.1 (3.35 changelog doesn't mention anything about JSON, so I assume it's unchanged in the latest version).

My current workaround is to use a string replace after settings the values:
update test set j = replace(replace(j,
    '"b":0', '"b":false'),
    '"b":1', '"b":true');

But meh.


texts: Can I edit queued MySQL binary logs for replication?

texts:
I have a simple Master-Slave replication set up, but Slave keeps falling behind do to what appears to be a simple checkout system. Master can keep up because it runs multi-threaded, but since the checkout is on a single table on a single database, the slave cannot multi-thread to catch up.

Consequently my binary logs are full of queries like:

UPDATE db.foo SET process_id = 123 WHERE id = 456 AND process_id IS NULL;
UPDATE db.foo SET process_id = 234 WHERE id = 567 AND process_id IS NULL;

I've analyzed the general_log (turned on slave temporarily) to estimate 9/10 queries are a regex of the above query.

Can I prevent these queries from being logged? How?
Can I scrub them from log before it's relayed to slave? How?
Any other suggestion?


texts: Maintenance Plan Job running at additional unscheduled times?

texts:
I have a situation that has left me really scratching my head. This is a SQL Server 2008r2 instance, used as the database server for a substantial corporate SharePoint service. As my company is providing the DB support for it, one of the things I did was to setup some maintenance plans to take care of the usual things, including nightly full backups of all of the databases at 7PM.

This all works fine, except that it has occasionally also been running sometime in the early morning.

So far this has happened three times, once 11 days ago at 2:47AM, once 4 days ago at 3:43AM and once 2 days ago at 3:49AM. I can find no reason why it should be running at these sporadic random times.

This is causing significant problems for us, as the early morning is when the server hosting provider runs their backup and maintenance jobs. Their execution takes many times longer than normal and are sending out alerts and other notices for this, long IO times and various errors that appear to be because of conflicts with the host SAN/NAS mirroring and backups.

To summarize what I have already checked:

This is the SQL Agent Job running an extra unscheduled time, and NOT just the maintenance plan running twice within the same job.
This does not appear to be to be a VM clone of this node with a SQL Agent running a duplicate of this job against the real SQL Server. I conclude this because the executions are showing up in our SQL Agent's log. (if that's not definitive, let me know how to check it)
This is not a case of the Job just running over for a long time. The regular job runs and completes at the expected scheduled time every day (7PM).
Neither the Server, nor the SQL Server instance have been restarted during the last month.
The SQL Server Job in question has only one schedule, that is set for 7PM every day.
The Maintenance Plan has only one subplan, that shows exactly the same schedule as the Job does.

Any help greatly appreciated.

At Aaron's suggestion, I have added the following Execute SQL as the first task in the sub-plan:

IF (DATEPART(HOUR, GETDATE()) > 2) AND (DATEPART(HOUR, GETDATE()) < 6)
    RAISERROR('Job Running at the Wrong Time!', 16, 1) WITH LOG


texts: How to change only time while keeping the date same in timestamp type

texts:
I have timestamp column used for scheduling calendar events. I would like to change only the time part while retaining the date part untouched.

How can I possibly do it? I'm on postgresql

Current:

2017-07-01 00:00:00

To:

2017-07-01 17:00:00


texts: MySQL 5.6 performance_schema not showing queries from application

texts:
I'm seeing a strange problem with perfromance_schema... it seems queries are not getting logged as I expect them to be. For example, this is what I get:

mysql> SELECT `SCHEMA_NAME`, `DIGEST_TEXT`, `COUNT_STAR`, `FIRST_SEEN`, `LAST_SEEN`
       FROM `performance_schema`.`events_statements_summary_by_digest`
       WHERE `DIGEST_TEXT` NOT IN ('COMMIT' , 'START TRANSACTION')
           AND `DIGEST_TEXT` NOT LIKE 'SHOW %'
           AND `SCHEMA_NAME` LIKE 'mydbase'
           AND `DIGEST_TEXT` NOT LIKE 'SET %'
           ORDER BY `LAST_SEEN` DESC LIMIT 5;
+-------------+--------------------------------------------------------------------------------------------------------------------------
| SCHEMA_NAME | DIGEST_TEXT                                                    | COUNT_STAR | FIRST_SEEN          | LAST_SEEN           |
+-------------+--------------------------------------------------------------------------------------------------------------------------
| mydbase     | SELECT `SCHEMA_NAME` , `DIGEST_TEXT` , `COUNT_STAR` , .......  |          6 | 2016-05-04 22:56:20 | 2016-05-04 22:56:42 |
| mydbase     | SELECT `st` . * FROM `performance_schema` . `events_statement`.|         39 | 2016-05-02 10:55:30 | 2016-05-04 16:06:48 |
| mydbase     | SELECT `st` . * FROM `performance_schema` . `events_stages_hi  |         39 | 2016-05-02 10:55:30 | 2016-05-04 16:06:48 |
| mydbase     | SELECT `st` . * FROM `performance_schema` . `events_waits_his  |         39 | 2016-05-02 10:55:30 | 2016-05-04 16:06:48 |
+-------------+--------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.00 sec)

If I run the query inside the database from mysql client (CLI), I can see queries appearing in there, but those from the actual application are missing?

I'm running MySQL 5.6.30 from IUS repository (CentOS 6).

Any ideas?


texts: Clustered columnstore index performance SQL Server 2014

texts:
I'm setting up an OLAP database using SQL Server 2014. The core fact table has about 40,000,000 rows, 225 columns with an average row size of 181 bytes. I've been toying around with the clustered columnstore index a bit without much luck. In general I find query performance more than 4 times slower with the new technology.

One particular example - selecting a single row using an int32 primary key now takes 12 seconds ... this is a sub-second operation on a rowstore table (of course it had a unique index on the PK, which is not allowed in conjunction with the clustered columnstore index).

I'm trying to figure out what I'm doing wrong - from the MS docs it sounds like this is ideal technology for this task; maybe I'm missing something.

I'm running SQL 2014 Enterprise on Windows 8.1 64-bit with 128GB RAM and SSD for data storage.  The data is read only for this app.


texts: Which reporting method has better performance, using linked server or using lookup in SSRS?

texts:
I've been using the new lookup function in Reporting Services 2008 R2 when using different servers where I don't have the option of a linked server. However, the DBA just created the a link to that server and I'm wondering if I can get better performance changing my query/report to use it instead of the lookup function.

I definitely have a lot more flexibility going linked server route but I'm not sure about performance.


texts: How can I prevent our IT admins with sysadmin to read data stored inside an encrypted database?

texts:
I built a web application that stores sensitive data into a SQL Enterprise 2014 database with TDE encryption. My key is safe; I know if someone steals the database file from disk the data is protected. However, if you run SQL Management Studio on the database server, you can query and read the database.

I believe this is because the SQL database loads the database with the masterkey, thus decrypting the DB and making queries readable. But at the same time, I cant prevent an IT admin with sysadmin rights to run queries on the data. If I remove their access to a database table, they could easily re-grant themselves the rights they need to read the data.

Is there anything I can do to prevent this from happening? I thought of saving the text itself in an encrypted format but that will affect my queries to run efficiently.


texts: Fast way to import 2GB csv file into SQL Server 2008

texts:
I am new to database,I am using SSMS 2008 R2 to import 2GB of data from a .csv file.

It is taking around 115mins to import.

Can anybody let me know any better way to perform this import.


texts: Postgres: ERROR: operator does not exist: point  point

texts:
Using PostgreSQL 11 on Windows.

Both cube and earthdistance installed and verified by pg_available_extensions.

Restarted PostgreSQL.

[42883] ERROR: operator does not exist: point <@> point.

Setup:

CREATE EXTENSION IF NOT EXISTS cube SCHEMA temp;
CREATE EXTENSION IF NOT EXISTS earthdistance SCHEMA temp;

Tried the following code from StackOverflow

create table temp.lat_lon (
  city varchar(50) primary key,
  lat float8 not null,
  lon float8 not null
);

insert into temp.lat_lon values
('London, GB', 51.67234320, 0.14787970),
('New York, NY', 40.91524130, -73.7002720);

select
  (
  (select point(lon,lat) from temp.lat_lon where city = 'London, GB') <@>
  (select point(lon,lat) from temp.lat_lon where city = 'New York, NY')
  ) as distance_miles;

Throws:

[42883] ERROR: operator does not exist: point <@> point.

Extensions installed and Postgres was restarted.

select * from pg_available_extensions where name IN ('cube', 'earthdistance');

cube            1.4     data type for multidimensional cubes
earthdistance   1.1     calculate great-circle distances on the surface of the Earth

Is this because of PostgreSQL v11 on Windows? Yes, Lat/Long in correct order (long is first).

Note: Table F.6 Point-based Earthdistance Operators

Update in regards to: schema / search path:

SELECT extname, extnamespace::regnamespace FROM pg_extension
WHERE  extname IN ('cube', 'earthdistance');

cube            temp
earthdistance   temp

SHOW search_path;

temp

Note: I CREATE EXTENSION hstore SCHMEA temp; and can use hstore and its operators. So doesn't seem to be all extensions.


texts: What are the risks for logging across databases via a trigger?

texts:
I've searched for this online and had mixed or unclear responses, and after posting on superuser, was directed over here.

On SQL Server 2005, we currently log certain table changes via triggers using the inserted/deleted tables. Currently, our log tables exists in the same database as the primary tables and I was thinking that from a management perspective, moving the log tables to a different database has advantages. In the very least, log tables tend to grow at a factor of 10 for the source table, so managing the different types of tables is along different paths and separating them by db may be helpful.

If we go this route, the trigger has to log across db boundaries (same server though). What problems would this create that I don't have now?

So far, the main one seems to be that someone could take down a db independently of another (rare), and then the logging would be lost and insert would be kept, or inserts would fail b/c the trigger fails, depending on how the trigger was written.

Are there other risks?

Another solution that was offered was to log to the same db and have a job move (delete and copy) the records over to the other db. However, I still don't know why that's a better solution.


texts: Should credits be stored as negative numbers in a double-entry bookkeeping database?

texts:
Assuming you use a table structure like:

Journal Entries

JournalEntryID
AccountingPeriodID
TimeStamp

Journal Entry Lines

JournalEntryLineID (UID)
JournalEntryID (Foreign to Journal Entries)
AccountID
Amount

Does it make sense to use negative numbers in the amount column to represent credits? If you used negative numbers for credits and positive numbers for debits, it would be easy to check the integrity of all the records just by getting the SUM() of Amount for all records in the Journal Entry Lines table (it should be zero at all times).

Paper journals don't use signed numbers though since the sign is implicit. And other systems I've looked at have used positive amounts for both. Would it be good practice to use signed amounts or are there pitfalls to this approach?


texts: High Disk I/O from sql server or is High disk I/O slowing sql server?

texts:
I've been arguing with a DBA and a couple hardware guys about performance issues on our SQL server.  Normally everything is fine, however over the past few weeks we have been having huge lag spikes in sql server.  Its clear that SQL Server is waiting on disk I/O.  But I keep getting told that it is beacuse SQL Server is asking for abnormally high I/O.  Which isn't the case.  I can see from what is running that there is nothing out of the normal, and all the DBA cares to look at is what is causing the blocking and so on, which is useless.  For instance the major thing we see backing up is operation on the ASPState database, which we are using to manage the ASP Session State on the web servers.  These operations are normally never seen on Sp_who2 active results because they occur so quickly.  The database is in simple recovery mode and logging is miminal.  However during these lag spikes we can see alot select and update operations on the database being blocked or waiting.   I'm sure what is going on is that someone or some job is running something that is causing heavey disk usage on the raid arrays used for that databases log and data files.  The problem is proving it, since no one wants to admit they are doing something that is killing our website.

My question is what performance counters or whatever can I log that will help show that SQL server is waiting on I/O, but not because its asking for more than normaly, instead beacuse the disk is to busy to respond to the requests from sql server as quickly as it normally would?