texts: Getting a SEEK rather than SCAN on an Aggregate

texts:
This is doing my headin, but I'm stuck on trying to get an Index Seek on the MAX aggregate within this query.

Here is the execution plan - and I'm wondering what I'm missing here, as currently as the query takes 3/4 seconds to complete which is too slow. I've indexed on StartDate, ClientID with no luck.

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

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

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


texts: garbd with proxysql and mysql replication

texts: Can be configured garbd with proxysql + MySQL Replication?, I installed proxysql with 1 master and two slaves using MySQL replication but I was disputed about to connect garb with proxysql how I was done with galera cluster


texts:
Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say:

select * into dbo.table2 from dbo.table1;

This is just a simple copy of the table; all of the indexes / constraints are missing. How can I copy a table structure (without using a backup)?

I am primarily looking to do this manually, but if that's not possible I'll accept any solution.


texts: Why do psql queries take longer when inserting many rows. Why is it non linear

texts:
So if I insert a single row into a postgres database it takes 18 ms. If I do this in a loop like this:

INSERT INTO contacts (numbers)
SELECT  distinct array[
        (random() * 99999999)::integer,
        (random() * 99999999)::integer
    ]
  FROM generate_series(1,4000000) AS x(id);

And I vary the number of rows inserted, the time is nonlinear. Here is the data:

-1 record - 18 ms
-20k records - 36 seconds
-50k records - 151 seconds
-100k records - 750 seconds

Why is this getting exponentially bigger? I need 10 Million records in my database for load testing and it seems to be faster to insert 50k rows and then reinsert the 50k again since 151 + 151 < 750

Any insight on this topic would be appreciated. I assume it is because postgres saves data to rollback in case the query critically fails or is cancelled by the user and postgres does not want to "half insert" the total request.


texts: Understanding SQL Server statistics better


texts: SQL Server AlwaysON : start sync with secondary for selected databases

texts:
I have an SQL Server 2012 AlwaysON cluster composed of :

win-mssql1 : witness file share

win-mssql2 (INSTANCE 1) : primary server

win-mssql3 (INSTANCE 2) : secondary server

Recently I had the host "win-mssql4 (INSTANCE 3)" as a new secondary replica. My goal is to :

Manually synchronise a huge database (let's call it "BaseSyncManual") with this new server.
2.Later, synchronize the other databases which are in the AG.

To do the first step, I have used the followings commands :

On primary server :

BACKUP DATABASE BaseSyncManual TO DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_full.bak';
BACKUP log BaseSyncManual TO DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_log_2.trn';

On secondary server :

  RESTORE DATABASE BaseSyncManual FROM DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_full.bak'
    WITH NORECOVERY

    RESTORE DATABASE BaseSyncManual FROM DISK = 'I:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\Backup\setra_log_2.trn'
    WITH NORECOVERY

On primary : start the wizard to add a replica and choose "skip initial data synchronisation"

On secondary : alter database BaseSyncManual set hadr availability group = AvailabilityGroup1;

Everything runs fine and the result is what I have expected :

Now I want to re-run the "add a new replica assistant" in order to synchronize the remaining databases (Base1,Base2,BaseSyncAuto) with the "full" parameter (automatically backup restore and join to AG). But there is no way to do that ! the replica as already been added to the AvailabilityGroup :

and I can't Add a database using "add database" because they are already member of the AvailabilityGroup on the fist secondary replicate :

What are my options to sync those databases with my new secondary replica ? Am I condemned to do it with manual backup-restore-join to AG ?


texts: Pushing or appending to JSON array in PostgreSQL 9.4

texts:
I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values.

Something like that:

CREATE OR REPLACE FUNCTION add_array(
    array_received json[])
  RETURNS void AS
$BODY$

    update table set _array_field = _array_field | array_received ...;

$BODY$
  LANGUAGE plpgsql VOLATILE;


texts: Getting formatted MDX values using OPENQUERY

texts:
Is there any way to convert a value in MDX to a string using its FORMAT_STRING?

I have code similar to the following:

SELECT *
FROM OPENQUERY(DWDB001AS, '
WITH MEMBER [Measures].[ROH Total Sold Gross]
    AS [Measures].[ROH Total Sold Dollars] - [Measures].[ROH Total Sold Cost]
        ,FORMAT_STRING=''Currency''
SELECT {
    [Measures].[ROH Total Sold Gross]
    } ON COLUMNS
FROM [ServiceDept]
')

I am trying to get it to return formatted strings, instead of numeric values.  The CSTR function doesn't seem to take the format string into account.

Converting them in SQL isn't an option here, because the rows have different format strings.  It might be possible to convert them based on the titles of the rows, but I'd prefer to have SSAS do the work for me.


texts: Multi master replication with different database

texts:
I have three database server.

ip=192.168.1.101 db=google
ip=192.168.1.102 db1=google db2=yahoo
ip=192.168.1.103 db1=google db2=yahoo

I want to do master replication between server1 and server2 for db=google
and
master master replication between server2 and server3 for db=yahoo

Can I have a suggestion or reference links to do that?


texts: Commit() takes 50 seconds after inserting about 100000 rows

texts:
I have a sqlite database that is, (I think), fairly small.

I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb.

I then open a transaction and insert about 100000 rows, the time it takes is about 10 seconds and I think this is acceptable for that number of rows, (and given the fact that my app does some 'other' processing.

My concern is when I do the 'Commit()' itself.

It takes around 50 seconds to write the data to disk... (around 10Mb is actually written).

This is what I do:

-- pseudo code for brevity...
Data Source=database.db;Version=3;Pooling=True;Max Pool Size=100;

PRAGMA journal_mode=WAL;
PRAGMA wal_autocheckpoint = 16;
PRAGMA journal_size_limit = 1536;
PRAGMA cache_size = 10000;
PRAGMA temp_store = MEMORY;

-- BEGIN TRANS

-- 100K Inserts/ 10K Deletes

-- COMMIT

Can anybody think why the commit would take so much longer than the entire transaction?

Any suggestions on how I could speed the commit up?


texts: Oracle RAC can you have a cluster that contains one node

texts: Have oracle RAC production system. Two nodes in the primary, two nodes in the secondary. Getting a test system.Can we have two nodes in the primary and one in the secondary? Or to put it another way  - can you have a one node cluster in standard oracle RAC?


texts: Postgresql Replication - Pacemaker vs DRBD

texts:
I need to setup a Highly available Postgresql Cluster. So I found Pacemaker and DRBD with pgpool load balancer are the best options.

So the architecture will look like this.

As of my knowledge, DRBD is for Block level replication (works on storage) and Pacemaker is Service Cluster (works on service level). So which is the best one to use and best HA solution?

And another question is Im using AWS EC2, So how can I manage VIP (I mean if the primary goes down how the PGpool will switch the VIP to Standby also its and in AWS)?


texts: Swapping TDE Cert Slow, any way to improve performance?

texts:
Fair warning, I am very new to encryption with DBs. Right now we use the following statement to swap certificates when in the process backing/restoring DBs between servers:

ALTER DATABASE ENCRYPTION KEY REGENERATE
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE....

This is working perfectly fine but the issue is that the amount of time it takes to change the certificate through this statement. I've noticed that it can take more than an hour to change the certificate for DBs that are >100GB.

Is there a faster way to change TDE Certificates that anyone knows of?


texts: MariaDB Incremental Backup always creates full backup (mariabackup/xtrabackup)

texts:
I am trying to set up an incremental backup solution for my MariaDB database (stored in MyISAM storage, which is listed as supported) using the included mariabackup tool (a version of xtrabackup).

The issue I'm having is that the 'incremental' backups are all identical in size to the primary full backup and to one another. The steps I am going through are as follows.

First, I make a full backup of the database:

mariabackup --backup --databases="database" --target-dir /some/place/backup/full/ \
    --user backupuser

This creates a full backup which can be successfully prepared and restored.

Next, I aattempt to make an incremental backup using the command:

mariabackup --backup --databases="database" --target-dir /some/place/backup/inc/1/ \
        --incremental-basedir /some/place/backup/full/ --user backupuser

This command runs without error but it creates a full backup, even though the from_lsn and to_lsn are both identical (the largest/latest lsn).

Regardless of whether the lsn has incremented since the last backup, the log always shows xtrabackup: Transaction log of lsn (x) to (x) was copied. where x is the database's current lsn number.

If no data has been entered in the database since the last backup, then looking at the xtrabackup_checkpoints files I see:

Full backup:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1706249
last_lsn = 1706249
recover_binlog_info = 0

Incremental backups:

backup_type = incremental
from_lsn = 1706249
to_lsn = 1706249
last_lsn = 1706249
recover_binlog_info = 0

In this case the incremental backups seem like they should be copying no data over.

If the lsn has increased since the last backup, I see:

Full backup:

backup_type = full-backuped
from_lsn = 0
to_lsn = 1706249
last_lsn = 1706249
recover_binlog_info = 0

Incremental backups:

backup_type = incremental
from_lsn = 1706249
to_lsn = 1706269
last_lsn = 1706269
recover_binlog_info = 0

In this case it looks like it should be copying only 1706249 to 1706269, but it copies the entire db. What am I doing wrong?


texts: How to validate negative values in query

texts:
Hi I am using postgresql in my project. I want to make a validation that if query returns negative then i want to change output to null.
(to_char (NULLIF(coalesce(0::numeric,0) -
(coalesce(26776.94::numeric,0) -
(coalesce(2760::numeric,0) + (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::character varying as clds

it is giving -6.17 as output , I want to make it null


texts: Cannot run 'uuid-ossp' functions even when it's activated

texts:
I want to generate a v4 uuid in PostgreSQL using the extension uuid-ossp, so in psql I ran:

CREATE EXTENSION "uuid-ossp";

Then I ran SELECT uuid_generate_v1(); or SELECT uuid_generate_v4(); but I always get this error:

ERROR:  function uuid_generate_v1() does not exist
LINE 1: select uuid_generate_v1();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

So how can I solve this error?


texts: How to backup MySQL accross multiple servers

texts:
I have a few questions related to MySQL security.

How do we backup the data in MySQL database? I know there's an output function where you can export your data in the db as sql statements. Is this the only way that we can backup the data?
Is there a way where we can backup the data automatically without manually does what I mentioned above?
What if I have two different servers. Let's call A and B. And A is the main server, B is the backup server. And how can I transfer my CRUD requests to the B server when A is down?


texts:
I am trying to setup handler socket to work with tokudb engine. Database I am running is percona server and i have got tokudb enabled and the tables all have tokudb engine.

I was wondering if anyone knows handler socket is compatible with tokudb aswell.

Any advise on this would be appreciated.

Thanks


texts: How do I determine the correct index on MS-SQL-Server based on a select?

texts:
I have two questions about the index management of MS-SQL-Server 2019

If the term "table scan" appears in the execution plan, is that an indication of a "full table scan", and that means that no index has been used here?

I'm never sure if MS-SQL will use my indexes. But I also don't know how to find out which index it actually uses.

For this join, I create an index on the F_Skonto table with the three columns gueltig_ab, gueltig_bis and CD_Kunde. Is that correct?
LEFT OUTER JOIN dbo.F_Skonto
    ON S631_BSA.SPTAG >= dbo.F_Skonto.gueltig_ab
    AND S631_BSA.SPTAG <= dbo.F_Skonto.gueltig_bis
    AND S631_BSA.PKUNRG = dbo.F_Skonto.CD_Kunde

And for this join I create an index with the two columns Monat and CD_Kunde on the table F_Umstellkosten. Is that correct?
LEFT OUTER JOIN F_Umstellkosten AS u1
    ON CONVERT(varchar(6), v1.Tag, 112) = u1.Monat
    AND v1.CD_Kunde = u1.CD_Kunde


texts: Odd behavior with SSRS 2016 drop down parameter list

texts:
I have a report that is "working" fine except for an issue.

I have four dropdown list parameters where I am getting the values from queries. When I select a value from a dropdown list, it selects the next value below what I wanted to select, and "removes" the first value as an option. For instance, one of the dropdowns holds a list of payrolls. When getting the list from a query, it returns payrolls 1 through 121(or so), which is perfect. However, if I try to select any of the payrolls, say payroll 10, it will show me that I selected payroll 11, and the option to select payroll 1 will no longer be available.

ALSO, possibly tied to the above, the report sometimes refreshes when I select "view report" and I have to try to choose the payroll again.

These are not cascading parameters. The values are all derived from two queries where I am selecting a distinct list of employees for two of the parameters and a distinct list of payrolls for the other two.

I am at a loss as to why this type of behavior is even possible.


texts: How to write stored procedure use SP_HELPTEXT for getting information of objects belong to another database

texts:
I am using SQL Server 2012.

I have a db lets called it A in that database I have procedure backup which get all creation information of another database which is B.

Now the problem when I am trying to use SP_HELPTEXT inside my procedure backup it give the information of database A. Any suggestion to make within database B?

Scenario:

I need to backup all functions in database B. My procedure which do that job is in database A. I used dynamic SQL to reach the names of functions when I want to get the body of them by using SP_HELPTEXT I stoped here


texts: MySQL event scheduler waiting on empty queue since server restarted 12 days ago

texts:
I noticed a process on the server which has been running for more than 12 days, which I think coincides with the last time MySQL was restarted.
mysql> SHOW FULL PROCESSLIST;

+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
| Id      | User            | Host      | db   | Command | Time    | State                  | Info                  |
+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
|       5 | event_scheduler | localhost | NULL | Daemon  | 1098372 | Waiting on empty queue | NULL                  |
| 1774483 | root            | localhost | NULL | Query   |       0 | starting               | SHOW FULL PROCESSLIST |
+---------+-----------------+-----------+------+---------+---------+------------------------+-----------------------+
2 rows in set (0.00 sec)

There are no events, and I haven't attempted to created any.
mysql> SELECT * FROM information_schema.EVENTS;

Empty set (0.00 sec)

This is actively using up to 8% of my server's CPU.
Is there a way of determining what this is, or why it was started? Will this try to run every time I restart MySQL? If so, what is it 'waiting' for and do I need to tweak my configuration at all to prevent this?
MySQL 8.0.21


texts:
I am getting the replication error 'Length of LOB data (2135980) to be replicated exceeds configured maximum 65536'.

sp_configure was previously applied to set it to max value (-1) and a query on sys.configurations confirms that the max value (-1) is in use.

EXEC sp_configure 'max text repl size', -1 ;
RECONFIGURE;
GO

select * from sys.configurations
where name like 'max text repl size%';
GO

I have tried using a large value for 'max text repl size' rather than max value and turning it off and on again, to no avail.

Is there something else that may be overriding the 'max text repl size' setting or anywhere else I can look?


texts: How to select distinct? not select duplicated row

texts:
I have 2 table

select DISTINCT (B.MAIN_ID), A.NAME,  A.NAME_ID, A.CREATED_DATE
 from FAMILY A, ADDRESS B where B.F_ID = A.N_ID

result:

MAIN_ID   |  Name     |  NAME_ID | CREATED_DATE
100       |  Erica    |  254     | 20190808-01:02:01
100       |  Erica    |  245     | 20190808-00:01:01
300       |  Eden     |  331     | 20190808-00:03:34

I want to be remove duplicated rows 'MAIN_ID' by created date, I need last created record. How can I do this? thanks

MAIN_ID   |  Name     |  NAME_ID | CREATED_DATE
100       |  Erica    |  254     | 20190808-01:02:01
300       |  Eden     |  331     | 20190808-00:03:34


texts: How to optimize and estimate DB recovery times?

texts:
Would like to optimize recovery time for db's after failover to passive node in high availability cluster.
Assuming full recovery model...
What are the steps to take to optimize DB recovery time for a given SQL server database?
Is there a good way to estimate how long recovery time will take for a given SQL server database?


texts: Should we still be using QUOTENAME to protect from injection attacks?

texts:
I was looking at an old stored procedure today and noticed it was using quotename on the input parameters. After doing some digging to figure out what that does exactly I came across this site. I now understand what it does and how to use it but the site says it is used as a mitigation from SQL Injection attacks. When I used to develop apps that directly queried a database, using asp.net, I would use ADO.Net parameters to pass user input in as a literal value and never really worried about protecting it in my stored procedures.

I am now writing a stored procedure that will be used by applications that I do not write so I do need to try and protect from injection attacks at the procedure level, is the quotename the best way to do this or is there a newer function/better method?

Code that got me on this thought pattern (@parm1 is a user input parameter):

'SELECT project [Project], project_desc [Description],
        customer [Customer], cpnyid [Company]
FROM PJPROJ (nolock)
where project like ' + quotename(@parm1,'''') + '


texts: autovacuum daemon considers pg_largeobject table?

texts:
I have a table which is having oid column and few oid column's data might be frequently modified from my application. I thought, it would rewrite the data and replace with new data (basically, thought it will not use additional space). But, its keep on generating new oid whenever modification being done. (pg_largeobject table entries increased)

To solve this issue, lo_manage(oid_column) trigger created for my table before any update or delete for each row. And it works (like, its removing old oids). pg_largeobject entries not increased. but pg_largeobject table size keep on increasing.

I read about autovacuum daemon, which will consider if the table is getting any update / delete transactions (autovacuum_vacuum_threshold - default 50) or reaches threshold table will be considered for autovacuum by the daemon.

I have not enabled this (autovacuum_vacuum_threshold is not assigned), so I am assuming its taking 50 by default. And I am sure atleast 200 oids being modified in my table. But I could not see any autovuum logs related to pg_largeobject table.

What would be the problem? Is there anyway to solve this problem?

Update 1:

mydb=# select * from pg_stat_all_tables where relname='pg_largeobject' and schemaname='pg_catalog';
-[ RECORD 1 ]-----+---------------
relid             | 2613
schemaname        | pg_catalog
relname           | pg_largeobject
seq_scan          | 0
seq_tup_read      | 0
idx_scan          | 38012971
idx_tup_fetch     | 51509025
n_tup_ins         | 11520615
n_tup_upd         | 0
n_tup_del         | 8569404
n_tup_hot_upd     | 0
n_live_tup        | 5453142
n_dead_tup        | 6774593
last_vacuum       |
last_autovacuum   |
last_analyze      |
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 0
autoanalyze_count | 0


texts: Getting the metadata about a Query and a Stored procedure in SQL Server

texts:
I would like to get all the names of the columns in a given query [SQL Textual Query] and also the columns that are in the SELECT clause in a Stored Procedure.

Is there any ways other than using REGEX to get these information from the SQL Server. I can give the name of the SP.

Kindly suggest me the best practice for getting this information while using SqlDataReader in C#.


texts:
Whenever I create a brand new database in PostgreSQL Maestro it creates the following list of default schemas:

Now from my understanding Schemas are like folders for organization, etc. So the question I have is are all these schemas needed when I create a new DB? If so what are they used for on PG side as I won't ever use them myself.

I can understand information_schema as this is default for an install of MySQL on a server, though I don't get why a database would need it's own as opposed to the entire server, but to each DB type his own I guess.


texts: Storing Different Products and Joining on table Names

texts:
I am setting up a database using mysql to store different products with different product types.

For example I could have Books, Software and clothes etc.

What I am think of is creating a parent table (product_table with the structure:

ID |TYPE|

Then Having a TYPE table which would be:

ID |TYPE |Product_ identifier_Table

So then the Product_ identifier_Table would contain the name of the table the which contains the product details.

Would this be the correct way of setting up the database?


texts: Verifying Oracle 12 client server encryption

texts:
I'm getting conflicting information on whether encryption is active between my Oracle server and a Linux client:

SQL> select NETWORK_SERVICE_BANNER from v$session_connect_info where SID =
sys_context('USERENV','SID');
NETWORK_SERVICE_BANNER
----------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 12.2.0.1.0 - Production
Encryption service for Linux: Version 12.2.0.1.0 - Production
RC4_40 Encryption service adapter for Linux: Version 12.2.0.1.0 Production
Crypto-checksumming service for Linux: Version 12.2.0.1.0 - Production
SQL>

Linux application server sqlnet.ora looks like below

SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA1)
SQLNET.CRYPTO_CHECKSUM_CLIENT = required
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
SQLNET.ENCRYPTION_CLIENT = required
TRACE_LEVEL_CLIENT=16

Server rebooted for good measure.

The traffic looks encrypted from a tcpdump's output, verified with wireshark:

tcpdump -n -A -vvv -i eth0 host client_IP and Oracle_server_IP -w ./output

Trace files (.trc) confirm the above too:

na_tns: Encryption is active, using AES256

Why is the NETWORK_SERVICE_BANNER not reporting it correctly?


texts: Grant usage to a schema from another database

texts:
In PostgreSQL, how can I GRANT USAGE to a certain schema for a certain user in a certain database while I am in another database?

I am connected to a database (let's say database first) as a user with CREATEDB privilege. I want to create another database second for user normal_user and grant a usage on public schema to this user. I can do CREATE DATABASE second; from my current connected database but AFAIK I can't do GRANT USAGE ON SCHEMA public TO normal_user; since that will only grant usage to the public schema in my current connected database.

I want to do this to automate database creation, user creation, and permission granting in a single SQL script.

I was hoping for a hacky way to do this. I want to do this to automate the db and user creation as well as permission granting. I am aware that some tools out there lime ansible or terraform might help in doing automation. But I am hoping if we can do this solely from Postgres script alone.

I am using AWS RDS as my Postgres and I don't think I have a way to ssh there to run the Postgres command from cmd line. I am trying to do this from dbeaver script page. So I think SQL is all I have to do this. If I can switch user or switch db easily using SQL, that helps.

After a thought, I think using psql -u user -p -h hostname I might be able to connect to the instance and switch around database in the shell. But let me know if I am mistaken.


texts: How do I go about creating a script to compare , update and match structure of two databases

texts:
I am comparing two databases using Opendiff which is similar to sqlCompare.

I have two databases which should match in structure so as I compare I would like to construct a script such that if the one DB has a specific table that the other DB doesn't have it creates that table so that the structure and collation in the two databases matches .

I am using SQL Server 2012.

Thanks


texts:
I am having trouble getting a trigger to work. What it needs to do is to prevent any customer with last name Glass from being inserted into the database. This is what I have:

CREATE TRIGGER no_last_name_glass_trg
ON customer
AFTER INSERT,UPDATE
AS
BEGIN
DECLARE @CUSTOMER_LAST VARCHAR;
SET @CUSTOMER_LAST= (SELECT INSERTED.customer_last FROM INSERTED);

  IF @CUSTOMER_LAST = 'Glass'
    BEGIN
    ROLLBACK
    RAISERROR('Applicant Glass Denied',14,1);
  END;
END;

The issue is when I add customers with last name of Glass, it inserts without throwing an error. Any ideas? I can make it work in Oracle, but the same method does not work in MS SQL.


texts: What are the pros and cons of storing two values in an array instead of two columns?

texts:
I have a project that plans to store an away team and a home team.  I'm wondering if it is better to store the teams in a single array column like this:

{Harvard,Colorado}

Or create away_team and home_team columns.  What are the pros and cons of each method?

It's likely that teams would need to be queried by array position on occasion (whether they are the home or away team in the array).


texts: How to find the schema name of a data pump dmp file?

texts:
I have been given a DMP data pump export file to import into my local Oracle instance.  I've tried running this command line:

impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp logfile=import.log

I get the errors:

ORA-31655: no data or metadata objects selected for job
ORA-39154: Objects from foreign schemas have been removed from import

And no data is imported.
From what I've Googled, one possible cause of this is that I need to specify remap_schama.  But I have no idea what the name is of the schema in the dmp file.  Any easy way to find out?
EDIT: I didn't find a solution to this question, but I did find a workaround... I tracked down the guy who made the DMP, and beat got the schema name out of him.  Specified remap_schema according to his definition, and Hey Presto!


texts: Critical error and failing to parse data returned by server

texts:
I am currently running Oracle 12c, but unfortunately I am getting this critical error message:

  Fail to parse data returned by server

I have never seen this error before and every attempt at finding a solution as been unsuccessful. I am rather a newbie in the world of Oracle DB.

Your help will be very much appreciated.

So far I am able to connect to the database with SQL Developer, but when Enterprise Manager is giving me the critical error message.


texts: "Procedure: XXXX has an unresolved reference to object XXXX" errors

texts:
After using for some time MS SQL Server Management Studio for creating and editing databases I tried to use Visual Studio 2010 "SQL Server 2008 Database Project" for this.

I used the SQL 2008 Wizard for importing existing database to VS2010. Everything went ok. Build succeeded as well. But when I try to deploy the database back to the server under another name I got lots of errors of type "Procedure: XXXX has an unresolved reference to object XXXX".

I'm surprised because all these stored procedures in fact work well and SQL Server Management Studio never complained about them.
What could be wrong?


texts: DBD::mysql complains "Character set 'latin1' is not a compiled character set"

texts:
I think this boils down to "Am I doomed to upgrading mysql hoping the problem goes away?". I've seen a few of the same issues around the internet but with no guidance on a fix other than "Did you turn it off and on again?"

I've install Perl's DBI::mysql, but most of the tests fail because there's a character set problem:

 t/10connect.t ........................ Character set 'latin1' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file

I wrote a script to investigate further:

use DBI;
use DBD::mysql;

print "Perl $^V\n";
print "DBI ", DBI->VERSION, "\n";
print "DBD::mysql ", DBD::mysql->VERSION, "\n";

$args{host} //= 'localhost';
$args{port} //= 3306;
$args{user} //= 'root';

my $dsn = "dbi:mysql:db=$args{database};host=$args{host};port=$args{port}";

my $db = DBI->connect( $dsn, $args{user}, $ARGV[0] );

The output has the same error from the tests:

Perl v5.18.0
DBI 1.631
DBD::mysql 4.027
Character set 'latin1' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file
DBI connect('db=;host=localhost;port=3306','root',...) failed: Can't initialize character set latin1 (path: /usr/local/share/mysql/charsets/) at test.pl line 14.

I thought turning on UTF-8 in the connect would bypass the Latin-1 problem, but it's the same problem:

Perl v5.18.0
DBI 1.631
DBD::mysql 4.027
Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file
DBI connect('db=;host=localhost;port=3306','root',...) failed: Can't initialize character set utf8 (path: /usr/local/share/mysql/charsets/) at test.pl line 14.

I think this is related to the MySQL bug Bug #59243:Character set 'latin1' is not a compiled character set and is not specified, but MySQL begs off responsibility for this and says it's a FreeBSD problem (and that's where I'm working!). This might be more appropriate for ServerFault if that's true.

This is MySQL:

$ mysql --version
 mysql  Ver 14.14 Distrib 5.5.8, for FreeBSD8.0 (amd64) using  EditLine wrapper

The MySQL docs (10.5 Character Set Configuration) say that I have to specify this set in Index.xml, and that's what the error message says, but Latin-1 is already in that file (and the file is at the path in the error messages):

<charset name="latin1">
  <family>Western</family>
  <description>cp1252 West European</description>
  <alias>csisolatin1</alias>
  <alias>iso-8859-1</alias>
  <alias>iso-ir-100</alias>
  <alias>iso_8859-1</alias>
  <alias>iso_8859-1:1987</alias>

They suggest getting a new Index.xml (although they don't say from where), so I copy the one I have on OS X (where all of this isn't a problem). That file was updated in 2012 whereas the one I had on FreeBSD was last updated in 2003. That didn't change the error message.

There's a FreeBSD bug ports/153588: Character set 'latin1' is not a compiled character set and is not specified, but it dies without any user guidance. I'd hate to take down everything to reinstall a FreeBSD port if this is a programming issue.

For what it's worth, this is only a problem because I'm installing on this on a different machine since my MySQL slave where I normally work is having all sorts of weird replication issues. I hopefully thought going right to the master (without creating a new user with the grants I need from where I am) would be easier than rebuilding the slave. Yeah, how's that working for me? :)


texts: RAISE NOTICE doesn't work as expected while using within Dynamic SQL block

texts:
I have a small piece of code to get all sequences and the current and maximum value of all sequences in a database. I have tried with recursive and some other alternatives through plain sqls, however I couldn't find it. So I decided to use the dynamic sql as below:

do
language plpgsql
$$
declare qy text;
        rs record;
begin
    for qy in select
    'select current_database(), iss.sequence_schema || ''.'' || ind.sequence_name,
    concat(''Cur'', ind.lvalue,'',Max:'',iss.maximum_value) from
    (select sequence_name, last_value lvalue from ' || quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) || ') as ind,
    information_schema.sequences iss where iss.sequence_name = ind.sequence_name and iss.sequence_schema = ''' || sequence_schema || '''' from information_schema.sequences
    loop
        execute qy into rs;
        raise notice '%', rs;
    end loop;
end
$$;

It works well in pgAdmin however, its not working in psql as expected. I do have two questions here:

Can I return the res values as result in dynamic sql? (I could if I do these operation via a function but I will not be having create / execute function privilege in the database since I will be using a guest user)
Why the RAISE NOTICE or RAISE DEBUG not showing anything in the console (using psql) even if I set the client_min_messages to debug or notice? I querying a rds instance. Is it something to deal with rds functionality?

Bottom of the line, I need to get all sequence's current value and max value along with schema name. It would be great if anyone shares an another way to achieve this without increasing the sequence value.

(I am using postgres 9.6)


texts: Robust way to build a DWH without downtime

texts:
I have a process that build a data warehouse using Postgres. The process runs every day at midnight and works in three steps:

Copy the production database (prod) to a temporary database (dwh_tmp) using pg_dump
Generate the fact and dimension tables in the temporary schema using the data from the production database
Rename the current DWH database (dwh -> dwh_old) and move in the temporary database instead (dwh_tmp -> dwh) using ALTER DATABASE dwh_tmp RENAME TO dwh

The problem is that the last step fails if there are still open connections to the dwh database. To get rid of these connections (e.g. using pg_terminate_backend()) I would need to run the DWH generation process with super-user privileges, which I'd like to avoid for security reasons.

Any suggestions on how to implement this in a more robust way? My main goal is to make sure that the DWH is always in a good state and the downtime is as small as possible, hence my idea of first building a temporary DWH and then renaming it. I'm not in direct control of the connecting processes that keep me from renaming the database (as they're openend from a different host).


texts: Extreme high index fragmentation in MS SQL Server

texts:
I have a problem with CLUSTERED INDEX, every one of my table have extemely high total fragmentation, around 92% with having page fullness in the same time at around 99%.

So in my understanding pages are out of order but are full so access to data is random, but if I generating report Disk Usage by Top Tables then I see that for example table with 136 million records have reserved 71 GB (9GB of Data, 50KB of index, and 61GB of Unused space)

I have questions:

How that table can take 61 GB of Unused space for every 9 GB of data
with having page fullness of 99.84%?
Why my clustered index is fragmented so high (91,77%)?
Why my table using 61GB of Unused space for every 9GB of data?

Enviroment:

table is stored on filegroup with 12 files
table has clustered index which is designed as (ID_SERVER, IDENTITY) so every server while inserting data requesting other page
without all servers waiting for same page with data inserts
data from table is removed in order of identity column then for every page data always should be removed from the left, without leaving holes in pages
mssql server 2016

EDIT:

CREATE TABLE [dbo].[table](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [id_data] [int] NOT NULL,
    [col1] [datetime2](7) NOT NULL,
    [col2] [datetime2](7) NOT NULL,
    [col3] [tinyint] NOT NULL,
    [col4] [tinyint] NOT NULL,
    [col5] [decimal](19, 9) NOT NULL,
    [col6] [decimal](19, 9) NOT NULL,
 CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED
(
    [id_data] ASC,
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


texts: How to fix below blocking problem on SQL server 2014

texts:
I am having a strange blocking problem which i am not able to understand.
During a slighter more volume due to some app pooling issues we see sleeping spids with open transactions, so those SP's tend to be lead blocker and blocks few delete statements.
Yes we are aware on sleeping SPID and working with app team to fix the issue, but i am not able to understand why on below
SP's which leave their session open say EXEC spleadblocker does not access any of the table underneath within its code i.e
it blocks a simple statement like below within an SP say spblocked
DELETE from dbo.table1 WHERE Col1=@col1
OPTION (QUERYTRACEON 9481)

spleadblocker does not have any thing to do with table "table1" and should not blocking therefore above statement
Waittype is LCK_M_X
and wait_resource is database.dbo.table1.PK_table1

table 1 has col 1  as PK /Clustered index created , which is of data
type bigint not null column

Why an SP with no correlation to this table will create blocking and is there a way i can avoid that PK scan from that delete statement to avoid being blocked?
UPDATE After more research below is what we found, say the very first SP which comes SP1leablocker for spid 66 and had its transaction open with status sleeping for about a min. This session block the delete statement above and yes they have table 1 in SP1leablocker code, so explains the blocking
Later, on above spid 66, multiple times we see different SP's running and creates a blocking chain for that delete statement of around 10 mins because that SPID 66 gets connection from various other SP's with status sleeping and open tran
Is there a way to avoid blocking on DELETE statement ?


texts:
I have a table (not designed by me) which has 20 variably named columns. That is, depending on what type of record you are looking at, the applicable name of the column can change.

The possible column names are stored in another table, that I can query very easily.

Therefore, the query I'm really looking for goes something like this:

SELECT Col1 AS (SELECT ColName FROM Names WHERE ColNum = 1 and Type = @Type),
       Col2 AS (SELECT ColName FROM Names WHERE ColNum = 2 and Type = @Type)
FROM   Tbl1
WHERE  Type = @Type

Obviously that doesn't work, so how can I get a similar result?

'
I've tried building a query string and EXECUTEing it, but that just returns "Command(s) Completed Successfully" and doesn't seem to return a rowset.

It turns out I was using an incorrect query to build the dynamic SQL and as such built an empty string. SQL Server definitely executed the empty string correctly.

Note that the reason I need this to occur, rather than simply hard coding the column names, is that the column names are user configurable.


texts: SQL Server Error "Database 'mssqlsystemresource' does not exist."

texts:
On Linux, whenever I run use mssqlsystemresource to get access to Resource Database, I get an error. How do I resolve this.

1> use mssqlsystemresource
2> go
Msg 911, Level 16, State 1, Server x230, Line 1
Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly.

When I try to log in with sqlcmd with DAC (admin:) targeting the database with -d

$ sqlcmd -I -S admin:localhost -U sa -d mssqlsystemresource -P Password0
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "mssqlsystemresource" requested by the login. The login failed..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'sa'..

When I try to log in with mssql-cli's -A, I get

$ mssql-cli -A -d mssqlsystemresource
Username (press enter for sa):
Password:

Connection did not succeed.
Error message: Cannot open database "mssqlsystemresource" requested by the login. The login failed.
Login failed for user 'sa'.
Unable to connect. Please try again


texts: MySQL: SELECT more data than necessary or use multiple SELECT queries?

texts:
I'm wondering if it would be better to use a single query and SELECT more data than I might need, or use multiple queries and SELECT only what I need when I need it. For example when the client submits a username and password, my PDO query string is this:

$sql = "SELECT passwordHash FROM users WHERE username=:username";

Then in PHP I verify the submitted password against the hash. If it succeeds, I do more queries to get more data like name and address. But would it be better to add those to my initial password hash query so that I'll already have it in the likely event that the password matches? That way I'd save a query. If the password is wrong though, I'd be wasting that lookup of extra information. In general, would it be better to do less queries and get more data each time, possibly more than necessary, or to do more queries and only get exactly the data I need each time?


texts: PostgreSQL: Pass table as argument in function

texts:
I am discovering TYPE in PostgreSQL. I have a TABLE TYPE that some table must respect (interface). For example:

CREATE TYPE dataset AS(
    ChannelId INTEGER
   ,GranulityIdIn INTEGER
   ,GranulityId INTEGER
   ,TimeValue TIMESTAMP
   ,FloatValue FLOAT
   ,Status BIGINT
   ,QualityCodeId INTEGER
   ,DataArray FLOAT[]
   ,DataCount BIGINT
   ,Performance FLOAT
   ,StepCount INTEGER
   ,TableRegClass regclass
   ,Tags TEXT[]
   ,WeightedMean FLOAT
   ,MeanData FLOAT
   ,StdData FLOAT
   ,MinData FLOAT
   ,MaxData FLOAT
   ,MedianData FLOAT
   ,Percentiles FLOAT[]
);

I can create table using this template with:

CREATE TABLE test OF dataset;

I have seen many options in the API, but I am a little lost. I would like to know if it is possible to assign this type to function INPUT/OUTPUT parameters.

Let say that I have a FUNCTION called process that receives a sample of records from a dataset TABLE source, processes them and then returns a TABLE sink with the same TYPE.

That is I would like to know if it is possible to create a TYPE that behaves like this:

CREATE FUNCTION process(
    input dataset
) RETURNS dataset
AS ...

And that can be called like this:

SELECT
    *
FROM
    source, process(input := source) AS sink;

I wonder that it is possible with PostgreSQL, and ask how to do so. Does anyone of you know?

Here is a MWE of what I am trying to do:

DROP TABLE IF EXISTS source;
DROP FUNCTION IF EXISTS process(dataset);
DROP TYPE dataset;

CREATE TYPE dataset AS (
    id INTEGER
   ,t  TIMESTAMP
   ,x  FLOAT
);

CREATE TABLE source OF dataset;
ALTER TABLE source ADD PRIMARY KEY(Id);
INSERT INTO source VALUES
    (1, '2016-01-01 00:00:00', 10.0)
   ,(2, '2016-01-01 00:30:00', 11.0)
   ,(3, '2016-01-01 01:00:00', 12.0)
   ,(4, '2016-01-01 01:30:00',  9.0)
   ;

CREATE OR REPLACE FUNCTION process(
    _source dataset
)
RETURNS SETOF dataset
AS
$BODY$
SELECT * FROM source;
$BODY$
LANGUAGE SQL;

SELECT * FROM process(source);

But it does not succeed, it is like source is perceived as a column instead of a SETOF RECORDS with the type of dataset.


texts:
OK. I'm in a bit of trouble.

I've got an Ubuntu, had Postgres 9.1 installed.

There was something wrong with it - I couldn't start it for some reason. Out of panic, I backed up /var/lib/postgres/9.1/main (my data directory) and did

apt-get purge postgres*

Which removed everything.

Then I did a fresh installation of Postgres 9.1

Now I tried just moving the old data directory to the new installation - there is an error.

I heard you can recover from a file in the pg_xlog.

What are my options?


texts:
select STUFF(
STUFF(RIGHT('000000' + CAST([run_duration] AS VARCHAR(6)),  6)
            , 3, 0, ':')
        , 6, 0, ':')
    AS [LastRunDuration (HH:MM:SS)]
 from sysjobhistory

The query I have given above works fine but I am trying to understand it more clear. My observations are:

First we cast the run duration into varchar and with a length 6 and append six 0's to right of it which becomes if its is 10 then 00000010
After we use the stuff function to insert :  in between this here it is at the 3rd position so the above one becomes 00:000010
Again we use stuff  to insert the : symbol to the sixth position so it becomes 00:00:0010

But when I run the query if there is a values 10 it shows as 00:00:10.

How that can be?


texts: pgsql Return Partial Text Field Ending In A Whole Word

texts:
I found this PHP code that returns a sub-string ending with a whole word (IE separated by a space):

if ( strlen( $body ) < $maxlength ) return $body;
$body = substr( $body , 0 , $maxlength );
$rpos = strrpos( $body , ' ' );
if ( $rpos > 0 ) $body = substr( $body , 0 , $rpos );
return $body;

Is it possible to achieve the same result using pgsql's string manipulation functions?


texts:
I just started the MS 70-463 Implementation of warehouse, on the video course the tutor is directing towards SQL Server 2012 Evaluation Edition, but I have already have a SQL Server 2012 Express.

Can someone tell me if that is enough for the course I want to follow please? Is there functionality that I won't be able to perform for the 70-463 course?


texts: How to move a table to secondary filegroup without losing data?

texts:
How to move a table to another filegroup?

How to move a Heap table to another filegroup?

Do we need to move the Non-Clustered indexes to secondary filegroup after moving the table?

Which SQL Server Editions support moving of table to different filegroup?


texts: How to tell what invoked a trigger in SQL Server?

texts:
I'm working on a legacy SQL Server application, with close to 3,000 tables and more than 10,000 stored procedures.

One of those stored procedures is deleting some needed data from a table. I don't know which one it is.

I created a trigger on the table which logs all deleted rows in another table, created just for debugging. It would be very helpful if the trigger could also log the name of the stored procedure which invoked it. Is such a thing possible in SQL Server 2008 R2 (v10.50)?

If not, is there any other information available from within a trigger which could help me identify why it was invoked?


texts: Oracle Temp Table Space - Alias clean up ORA-01119

texts:
How to drop the temp table space including alias.

SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP;
SQL> ALTER  TABLESPACE TEMPRM_TEMP ADD TEMPFILE '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf' SIZE 2M;
SQL> DROP TABLESPACE TEMPRM_TEMP INCLUDING CONTENTS AND DATAFILES;
SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP;
SQL> ALTER  TABLESPACE TEMPRM_TEMP ADD TEMPFILE '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf' SIZE 2M
         *
         ERROR at line 1:
         ORA-01119: error in creating database file
         '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf'
         ORA-17502: ksfdcre:4 Failed to create file
         +TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf
         ORA-15005: name "rm/datafile/TEMPRM_TEMPFILE_1.dbf" is already used by an
         existing alias

Please explain, how to clean Alias in Oracle ASM  ?
thanks


texts: What is the easiest way to get started using databases with real data?

texts:
I have a project that could benefit from using a database, but I have no experience with databases, don't have access to a server, and have relatively little experience working with things living server-side.

If I'm going to have to tackle a learning curve, I'd prefer to learn something with broad applicability (such as SQL) but would settle for learning something like Access if it is sufficiently powerful for the task I'm currently trying to tackle. Of course, I'd also rather not drop $150 on Access if it can be helped since I'm just tinkering.

I've downloaded LibreOffice Base as well as something called SQLiteBrowser, but I wanted to check first before I invest time learning those particular applications and their flavors of SQL whether those tools will be sufficient for what I want to do.

I want to be able to:

import data from a CSV or from Excel
run queries that equate to "select x where this is that and this contains that and any of these contain that"
write(?) a new field which indicates those results which match a given query

Again, I'm willing to learn, but it would be nice not to have to learn a bunch of intermediate stuff about IT before I can focus on learning databases and, if necessary, the particulars of a given application.


texts: Can I optimize a left join for a single match?

texts:
Consider a join A LEFT JOIN B ON A.id = B.a_id:

B.a_id is nullable -- most but not all B records point to an A record.
A can, at most, only have one matching B record (business logic).
Not all A records have a match in B.

Is there a way to tell MSSQL that there will never be more than one A:B match, thus allowing it to shortcut the join once a match is found for a given A record?

I would think that such an optimization could happen if B.a_id had a unique index, but that's not possible since it's nullable.

Complicating the issue: A and B are in different databases (same MSSQL instance).

If it matters, I'm using SQL Server 2008 R2.


texts:
Service application is nodejs It is using mysql connection pool.

Currently, we will plan to sell service to another country What is the best way to set timezone?

timezone setting execute, and query execute? Like "Set timezone='UTC'; SELECT * FROM user;"


texts: SET ANSI_NULLS at the database level versus setting it at the table level

texts:
I have generated schema-only script for my sql server 2008 R2, here is part of it:-

USE [master]
GO
/****** Object:  Database [****]    Script Date: 03/25/2015 12:17:09 ******/
CREATE DATABASE [****] ON  PRIMARY
( NAME = N'TMS', FILENAME = N'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\****.mdf' , SIZE = 11264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'TMS_log', FILENAME = N'L:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\****.ldf' , SIZE = 4672KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [****] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [****].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [****] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [****] SET ANSI_NULLS OFF
GO
ALTER DATABASE [****] SET ANSI_PADDING OFF
GO
ALTER DATABASE [****] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [****] SET ARITHABORT OFF
GO
ALTER DATABASE [****] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [****] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [****] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [****] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [****] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [****] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [****] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [****] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [****] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [****] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [****] SET  DISABLE_BROKER
GO
ALTER DATABASE [****] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [****] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [****] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [****] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [****] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [****] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [****] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [****] SET  READ_WRITE
GO
ALTER DATABASE [****] SET RECOVERY FULL
GO
ALTER DATABASE [****] SET  MULTI_USER
GO
ALTER DATABASE [****] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [****] SET DB_CHAINING OFF
GO
USE [****]
GO
/****** Object:  User [AD-ITSERVICES\TMSDB.user]    Script Date: 03/25/2015 12:17:09 ******/
CREATE USER [AD-ITSERVICES\test.user] FOR LOGIN [****\test.user] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  Table [dbo].[TechnologyTypes]    Script Date: 03/25/2015 12:17:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TechnologyTypes](
    [AssetTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [IncludedInSearch] [bit] NULL,
 CONSTRAINT [PK_AssetType] PRIMARY KEY CLUSTERED
(
    [AssetTypeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_AssetType] UNIQUE NONCLUSTERED
(
    [Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TechnologyStatus]    Script Date: 03/25/2015 12:17:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

now i am confused on why i go this sentence :-

ALTER DATABASE [****] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [****] SET ANSI_NULLS OFF

but when creating any sql table i will get :-

SET ANSI_NULLS ON

so what is the final result; is ANSI_NULLS will be equal to off or on ? from my understanding the "SET ANSI_NULLS" will be OFF at the database level, unless i set it ON at the table level ? is this correct ? any why i got the ANSI_NULLs set to off at the database level ?


texts:
One of our servers tells us it has Error 823 issues:

  TITLE: Connect to Server

  Cannot connect to ServerName.
  ADDITIONAL INFORMATION: Warning: Fatal error 823 occurred at Mar  1 2016 >
  10:45AM. Note the error and time,
  and contact your system administrator. (Microsoft SQL Server, Error:
  21)

The windows log is floated with the same message.

No connection to the sql server is possible. So DBCC CHECKDB cannot be used / executed.

I ran SQLIOSIM and it raised couple of errors that requests are outstanding for more than n sec on a specific drive.

A Similar issue happened last night on another system - and it looks like it was workarounded by a failover.

I don't know where to look at next since I cant even connect to the Sql Server.


texts: In what file does SQL Server Store the configuration data?

texts: Where does SQL Server 2014 store the configuration data? Such as the certificate references for SSL and the size limits and allocations?


texts: Very Simple "SELECT * FROM" Times Out (InnoDB)

texts:
Hello everyone and sorry for what may be a silly question, but I'm completely dazzled.

First thing is that I'm not sure that it's related to InnoDB, but I've tried this on MariaDB and on MySQL and results are the same.

Let's cut to the chase:

I have huge performance issues in the most unpredictable places. To illustrate it best, here is an example:

I have a table called 'image' which stores images as BLOBs as well as the images metadata like image_name or DPI. It's relatively small (~14000 rows) and most images are 250-1000 KB. In the example I will use LOWER() to get rid of index that I have on image_name column.

SELECT id FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

This is super fast, at worst couple milliseconds, we got result id = 1.

SELECT * FROM image WHERE id = '1'

This is fast again, most time spent transferring BLOB to me, no surprise (in the end, it's PRIMARY key).

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

Now this thing is just refuses to execute. And I mean it. In 95% of the cases I will receive a timeout and occasionally it will execute after 10 minutes or so.

EXPLAIN SELECT obviously tells me that it's simple SELECT. And I cannot run SHOW PROFILES because the request is never completed, so I cannot even think of where I can start debugging it.


texts: Rebuild and Reorganize index while altering index

texts:
Can someone please explain for example if i have a unique non-clustered index on a table with 3 columns.
due to some reason if i need to add one more column in the existing index .

Do i need to rebuild index ?


texts: How to check if staff member has end date with a Null value

texts:
I have a database that records employees tasks throughout the organisation and when they finish etc., I need to identify if each employee, has at least one row with a null value in either finish.

Basically - show me any employee_no that does not have at least one row with a null value in column Finish.

There can be more than one null value but as long as there's one.

Sample database:


texts: Check constraint references another column in same table

texts:
I have this table but check constraint is throwing me an error:

  Column CHECK constraint for column 'MedicamentRegulated' references another column, table 'Medicaments'.

I've seen this as allowed in SQL Server but I don't know why it doesn't work :/

CREATE TABLE Medicaments (
MedicamentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50),
DateValidity DATE,
MedicamentRegulated BIT CHECK((MedicamentRegulated = 1 AND DateValidity IS NOT NULL)
  OR MedicamentRegulated = 0)
)


texts: Does ORDER BY on a clustered key affect performance?

texts:
Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey, so I additionally always ORDER BY PrimaryKey in all queries.
Does this ORDER BY affect performance in any way or is it ignored by the profiler as the rows are already in this order?
I am using a SQL Server 2005 database.


texts: PostgreSQL, PostGIS: Copying a schema with a geometry column

texts:
According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn():

  AddGeometryColumn — Adds a geometry column to an existing table of
  attributes.

I am copying a schema from one PostgreSQL instance to another. I wonder if the SQL created by pg_dump is good enough - will it create the geometry columns the same way AddGeometryColumn() does?


texts: http://dev.mysql.com/downloads/utilities/ has MySQL Utilities 1.4.4 for Mac OS X 10.7.  Is this still updated and recommended to use along with MySQL Workbench? I'm at the latest MySQL Workbench 6.1 CE on Mac OS X 10.9.2.


texts: Searching for a string in multiple fields in a table

texts:
There's lots of examples of complex systems to find a string in all the columns in a table or DB. Mine has to look for a string in 10 fields out of about 100 in a particular table, so these solutions are too complex.

Right now I do the dumb thing:

SELECT * FROM sometbl WHERE FIELD0 LIKE 'zap' OR FIELD1 LIKE 'zap'...

But this results in really nasty-to-read SQL. So then I thought...

SELECT * FROM sometbl WHERE '%zap%' LIKE FIELD0+FIELD1+FEILD2...

I cannot convince myself this works. For one, is the LIKE bi-directional so that can I put the %'s on the LHS? Or do those %'s have to be on the RHS?

So then I thought...

SELECT * FROM sometbl WHERE CHARINDEX('zap',FIELD0+FIELD1+FEILD2...) > 0

Can anyone comment on these solutions, and whether I should use any of them?


texts: Cannot Check if the Access Database Engine is Installed and Configured

texts:
All, I want to check if the Access Database Engine is installed and linked to SQL Server. On my development machine (x64-bit) the following queries both show the Access Engine to be available as a linked server:

SELECT srv.provider
FROM sys.servers As srv
WHERE is_linked = 1;

and

EXEC sp_linkedservers;

but these do not work (that is they do not return the Access Engine as a linked server - it is though) on another x64-bit machine. Why?


texts: How should I log changes to an object when the datatype of the changed property can vary?

texts:
I am trying to create a log-table for storing events to a company-object, and I am afraid I might be taking the wrong route. I've arrived at the conclusion that I should log different data-types in the same column, and it doesn't feel right. I'll explain the basic use-case with 2 tables; company and user.

company
- id int
- name nvarchar
- description

user
- id int
- name nvarchar
- company int (references company.id)

Now, say I want to log different types of events on the company-object. I'd make a log-table called company_log and company_log_type and store events there. These tables would look like this:

company_log
- id int
- old_value nvarchar
- new_value nvarchar
- log_type int (references company_log_type.id)

company_log_type
- id int
- name

Examples of interesting events are

Company-name was changed
Company-description was changed
A user was added to the company
A user was removed from the company

For the two former events, I'd store the type of event, the old value and the new value. This works fine as long as I stop here. However, if I want to log the 2 former-events, I would need to be able to store different data-types in the same column. The changes would look something like this:

old_value     new_value     log_type
----------------------------------------------
Acme          PiedPiper     CompanyNameChanged
NULL          132           UserAdded
97            NULL          UserRemoved

At this point, I would need to store the textual old/new value of the company name, as well as the id of the recently added user.You might see that I'm already headed off in the wrong direction, and this is where I ask for help. I have 2 questions:

Should I just use sql_variant-datatype, or if this is considered poor design then what would be a sensible way to store these log-events?

Thanks in advance.


texts: how to add attachment(text file) to database mail?

texts:
I have scenario

Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates

I can send a mail to client that job is successfully completed - now i need to send him a text file as a attachment

Is there any way to send attachment through GUI (SQL Server Job setting)

I cann't run the script

EXEC sp_send_dbmail

I googled for this scenario but no information from GUI END - i could find from only with scripts


texts:
First observed few months ago at end-user's laptop (rather slow i3 CPU, 4GB RAM, SSD) with SQL 2014 Express. The problem persists with SQL 2019, even with clean Windows 10 1903 installation.
The database has 250MB so pretty small even for Express Edition. Application mostly works smooth, but there are time to time very looong pauses, while simple query which is usually finished within 2 secs timeouts after 5 mins! It's client machine, there are no other queries nor databases.
Recently the same problem start occurring on my dev machine too. I can confirm the memory pressure in Windows event log:
A significant part of sql server process memory has been paged out. This may result in a performance degradation.
Duration: 7204 seconds. Working set (KB): 203376, committed (KB): 577992, memory utilization: 35%%.

Based on this MSDN article about paging I should turn on Locked pages. There is a caveat that the hungry SQL Server can eat all free memory, so user can't work at all. Are my worries relevant?
What struggles me most is that it start happening quite recently (approx. 2 months ago) while there is no significant change at my workload as well as 16GB RAM. Second, why freeing few hundreds MBs is taking several minutes with NVMe drive (and in my case also quite performant CPU).
Are there some recent changes how Windows 10 / SQL server handles out of memory states?


texts: Is it possible to upgrade Oracle 8i to 12c?

texts:
We have an old server running Oracle 8i.

Is it going to be possible to directly update it to something newer like Oracle 12c, or would that require a complete uninstall and re-install?

If we had to completely uninstall and re-install, is it then a case of importing the database dump from Oracle 8i and it will be converted, or is that also a manual task?

Update

Thanks for everyones input. I have checked with our application vendor (the ones who originally installed Oracle 8i for us around 10 years ago) and their application that we use does work with Oracle 12c but they are not willing to do the update for us.

They have basically said that we should do the update, preferably on a new server and at that point they would be willing to get involved to check their application out using the new database version etc.

Is there some way to find Oracle experts capable of doing this job (preferably UK based)? I'm pretty new to Oracle (past experience is mainly Microsoft SQL Server), day to day management/security, scripting etc. is ok but I'd rather have someone in who has done this (many times?) before.

Thanks again!


texts: Can I grant a user ALTER TRACE permission to a single database?

texts: I have a development user who requires the ability to use SQL Profiler on a single database. Is there a way to enable this without granting a server-wide ALTER TRACE permission? (The user should not have any access to the other databases at all.) This is Microsoft SQL Server 2008 R2.


texts: Hi I am creating database for rating the items in my list but I want to ask what this if I have five stars rating how can I do it in database "rating" column should I use Int with check constraint  or user define type ?


texts: How to write this self join based on three columns


texts: How can I only load one function from an extension?

texts:
In PostgreSQL, I am loading the uuid-ossp extension so I can use the function that creates random UUIDs in my queries:

create extension if not exists "uuid-ossp";

However, as well as making available the one function I need (uuid_generate_v4), it also makes nine other functions that I don't need available:

(Using \df on the command-line shows the same data). Is there any way I can, using programming terms, "import" just the uuid_generate_v4 function into my "namespace", without importing the ones I'm not using?


texts:
You've stored the backup file in a safe place, but what kind of info do you store with it?

database type (SQL Server)
compatibility level (SQL Server 2008)
application name
application owner
application version

And ...?


texts: Should I extract / normalize the mailing address despite requirements limiting number / type of addresses?

texts:
I currently am working on a contacts application (similar to Outlook contacts combined with eVites and LinkedIn).  The requirements for the application are to limit the addresses (and phone / email addresses) to two - primary and secondary.

Normally, I would extract the addresses into their own table with a key back to the main profile along with the address type in the address table.  However, with the specific limitation of the number of addresses, I am considering having the addresses in their own table - with a foreign key from the profile table to the address table (one foreign key for primary and one for secondary).

Essentially, my question is:

Should I prepare for possible changes in the future to allow for the capability of more than two addresses in the future - or work within the limitations of the current requirements to specifically limit (via the database schema) which types and how many addresses can be associated?

Thank you for your help.

My two proposed schemas are as follows (please forgive the formatting - having trouble with the limitations of the WYSIWG editor):

First Schema:

NOTE: code / constraints / triggers would limit it to the primary / secondary rather than the database schema

Profile

(profile information fields)

Address

AddressId
ProfileId
AddressTypeId
Street Address
City
State
Zip

AddressType

AddressTypeId
AddressType

Second Schema

Limitations are through the profile schema

Profile

...
PrimaryAddressId
SecondaryAddressId

Address

(may be further normalized)

AddressId
Street Address
City
State
Zip


texts: Problem Installing PostgreSQL 12.1 Win8.1: "the database cluster initialisation failed"

texts:
I'm getting an error at the end of installing PostgreSQL 12.1 which is "the database cluster initialisation failed"

I have been searching all the day, so here what I tried:

1- Run the installation file as Administrator: didn't work.

2- Change the directory of "Data" to different place other than C:: didn't work.

3- Changed the whole installation directory to C:\Postgre or F:.... : didn't work

4- Turned off the KasperSky antivires: didn't work

5- Checked if there is an old Postgre user: not found

I checked the log file and I found the following :

  Script output:
   WScript.Shell Initialized...
  Scripting.FileSystemObject initialized...
  Called CreateDirectory(F:\PostgersSql\data)...
  Called CreateDirectory(F:\PostgersSql)...
  Called ClearAcl (F:\PostgersSql\data)...
      Executing batch file 'rad712E9.bat'...
  Called Die(Failed to reset the ACL (F:\PostgersSql\data))...
  Failed to reset the ACL (F:\PostgersSql\data)

Any one can tell me what is causing this error?


texts: How to build a database that stores huge data while no need of frequent query?

texts:
We want to separate our data into fresh data and history data. History data is rarely used so we decide to move them to a specific database that stores huge data while no need of frequent query nor very good speed.

What kind of database is most suitable for this case?


texts: parent child records - cascade deletes or transactions?

texts:
I'm about to create a parent and child table.  In planning how to do CRUD operations on this data, I was wondering what is better design when it comes to deleting data.

I'm using SQLite.  I know that with many databases, you can set up cascading deletes.  Assuming I set up the relationships properly to support this type of feature, my question is as follows:

Is it better to use cascading delete feature when deleting parent records, or could I stick to using transactions, and manually delete from the parent/child tables myself, with rollbacks when something fails?

The front end is a web application. Any suggestions / articles you can point me to would be great.


texts: Enable or disable a task based on parameter

texts:
I have an SSIS package called Main_Flow that executes several other packages in order.

What I'd like to do is to somehow parametrise the enabling/disabling of packages in the main flow. This way I can choose to skip certain tasks based on value of parameters upon execution.

How can I ensure that a task can be skipped if DisabeTaskA parameter is set to TRUE?

It is important to emphasize that if I disable a package in the middle of the linear sequence, although this task would be skipped this does not mean any of the next packages should be skipped (unless I opted to disable them). That is, the skip shouldn't break out of the sequence.


texts: Changing IP address of SQL Server 2008 R2

texts:
I would like to know if there are any dangers or relevant precautions before changing the IP address of a SQL Server 2008 R2 box.

We have built a virtual PC with Windows Server 2008 R2 and SQL Server 2008 R2. The purpose of this machine is run a job that restores a backup file (copied by a different server), manipulates it, backs it up again and then copies it out to other servers. We've run it a few times in our test/dev DMZ and are now considering deployment.

The simplest option would be to re-IP it. The server name would remain intact. My problem is a colleague has suggested this is unsafe (to re-ip a SQL Server). My question is

a) Is this true for the OS and SQL I'm using? (Win 2008 R2 & SQL 2008 R2?)

b) Is it true for any other combinations of OS and/or SQL?

c) Is there anything special we should do in preparation?

My research thus far indicates it will be fine but I trust the folks of DBA StackExchange more than those social MSDN people.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9844e0c1-b57e-4e0c-88cf-7cb51cc55e07/change-sql-server-ip-address

http://www.sqlservercentral.com/Forums/Topic1164450-391-1.aspx


texts: Query for View with unique entries from Database with repeated entries

texts:
I have a table with columns of Hotel names, city name and country name (of the respective hotels). I need to create a view of unique entries which will contain city name and country name columns. Any suggestions on how to go about it?
Eg: There may be 100 hotels with city_name = Miami and country_name = USA. The view should however contain only 1 city_name = Miami and country_name = USA.

I figured I'd need a loop to run through the table but still struggling to put together a query.

Thanks in advance!


texts: Cannot see new drive in SSMS maintenance wizard

texts:
I'm trying to setup a Backup Maintenance Plan in SSMS for Sql Server 2012, running in a failover cluster on Windows Server 2012.  The current plan backs up to the same cluster volume where the database data and transaction log files reside, and I want the backups going to a different volume, but the only volume that I can select in the maintenance plan configuration is the shared drive I am already using.  The same limitation exists when I try to manually run a backup from within SSMS - I can only see the one drive.

How can I get additional drives to show up?

I have:

Mapped the new cluster volume to a drive letter
Ensured that the sql server service account has full control permissions on that drive
Successfully run a backup to the new drive using T-sql

In the long term, I fully intend to move to t-sql scripted backups for the additional control and flexibility, but I'm new to this and want to get the backups on a separate drive right the heck now.


texts: How to resolve "unwanted" recursive relationship?

texts:
I'm currently designing a database for concerts, so I have created the following entity-relationship diagram (ERD):

Business domain characteristics

A concert can take place on a regular event or a festival. A festival "uses" all the attributes an event has, but it also holds some additional ones. The same goes with festival_concerts and concerts.

As festival_concerts and concerts are processed the same way in some places but completely different in other places, they need to be separated.

The question

Because of that, I planned to store the additional attributes in a second table that uses the concert primary key as primary key too. The other way was to store them in completely seperate tables. If using my plan, I'd like to clean up the ERD so this weird rectangle-relationship doesn't exist anymore.
Which way do you recommend?


texts: Store and search leveled categories with mysql json

texts:
I have leveled category triplets column, that tag the specific blacksmith with items he can produce:

blacksmith_id (primary) | blacksmith_name (varchar) | item_tags [json] | blacksmith_lvl (int)

Single tag structure looks like this:

Level 1 (category) : Level 2 (sub-category) : Level 3 (sub-sub-category)

For example, blacksmith with id 1 can produce following items:

Iron:daggers:curved
Iron:daggers:poisoned
Steel:daggers:curved
Golden:swords:curved
... etc

Each blacksmith can be tagged with multiple item tags.

I chose to go with JSON since item data that blacksmith produces is just informative, and does not have any specific value other than finding the specific blacksmith by his skills, neither does it change. So i see no use in creating multiple joined tables with row for each item that blacksmith uses.

What would be the best (time / resources) way to store and later search on by specific category levels to get list of blacksmits using mysql json.

My ideas so far:

1. Using WHERE JSON_CONTAINS

["iron:daggers:curved","iron_daggers:poisoned","steel:daggers:curved"]

In this case the only option would be to search for complete item hierarchy Level1:Level2:Level3

2. USING WHERE JSON_EXTRACT

{"i0":{"lvl1":"iron","lvl2":"daggers","lvl3":curved},"i1":{"lvl1":"iron","lvl2":"daggers","lvl3":"poisoned"}}

For searching just on specific levels, but i dont know if it is possible to iterate through all items without specifying the key (i0, i1) to find for example just blacksmiths which lvl2's are daggers and how time consuming this is for mysql.

Please suggest your own structures you think would be most effective.


texts: SQL Server Express 2008 installation problem

texts:
I just installed SQL Server Express 2008 on my Windows 2003 server.

The sql base is started, but I look in config manager and my SQL Server browser is not started. I can't start it. When I try to change properties, I got an error: there's no enable device associate (0x800704).

I will look for reinstalling SQL Server browser manually ( http://msdn.microsoft.com/en-us/library/ms181087.aspx ). I don't know if that will cause an error. Maybe the same thing I got before.

I try to reinstall/repair, but there's an error (my biggest problem is that I'm not the admin of this server, so I need to wait for the tech to be there if I want to do something. Last timed, I didn't have the time to get all information. I need to get all ideas and program before, because I will have not so much time to try to repair it. The server is used by other users (data), and I can't reboot often)

If you have any idea, what should I do, or Test, or hit?

thank you


texts: Data Collector Component Name Doesn't Change After Updating SQL Server Name

texts:
I cloned a SQL Server, and changed the name of the machine and SQL Server, but the old name continues to show up as a component name in the disk usage data collector, which I believe is what's causing that job to fail.

This is part of the error in the job history for that task that includes the old name:

SSIS error. Component name: TSQLQuery_OLD_NAME_MSSQL10_50_MSSQLSERVER_{}_1_Collect

I have already dropped the old name using sp_dropserver and added the new server name using sp_addserver. I verified that SELECT SERVERPROPERTY('MachineName') brings up the correct information, as does sp_helpserver. However, SELECT @@SERVERNAME brings up a NULL value, despite restarting the server.

How do I change the component name?


texts:
This is my current statement:

INSERT INTO groups (groupName, alias, isDefault, inheritance, permissions)
VALUES ('Admin', 'admin', '0', 'Player', 'test.test') ON DUPLICATE KEY
UPDATE alias = VALUES(alias), isDefault = VALUES(isDefault),
       inheritance = VALUES(inheritance), permissions = VALUES(permissions);

When I execute this, I get a single row that is expected. But when I execute this again, it creates another row. Why does it do that?

Between, I have another column called id, and it is auto-incremented.

EDIT: Results of show create groups:

CREATE TABLE 'groups' (
 'id' int(16) NOT NULL AUTO_INCREMENT,
 'groupName' varchar(64) NOT NULL,
 'alias' varchar(32) NOT NULL DEFAULT '',
 'isDefault' tinyint(1) NOT NULL DEFAULT '0',
 'inheritance' text NOT NULL,
 'permissions' text NOT NULL,
 PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1


texts: Why bulk loading a Vertica database table does not load all lines?

texts:
I have a small csv file I would like to bulk load into a Vertica 6.1 database table, this file has 92 lines.

dbadmin=> COPY games FROM '/tmp/games.csv' DELIMITER ',';
 Rows Loaded
-------------
          44

The file is 92 lines, but Vertica only loads 44 lines.
As a test, I tried loading the same file in MySQL, and all 92 lines loaded fine.

I verified the file has 92 lines.

[root@localhost tmp]# cat /tmp/games.csv | wc -l
92

I opened the file and verified it has no unusual characters.

Any ideas?


texts: I am using SQL Server 2005.My question is;I am looking for a query that  listing all rows and sizes of tables in a Database.


texts: MySQL - Table doesn't exists in engine after server restart

texts:
This is happening in a couple of our clients.

We installed our server at a local machine in one of our clients, with the MySQL server db installed in the same machine(Limited resources).

I saw an error that a Table doesn't exists in engine.

This issue repeated a couple of times on different tables and different clients.

We are using :
InnoDB 5.6.22-72.0
MariaDB 10.0.17

It is possible that our clients have made a restart on their server.

Why is that happening in MySQL, and how can we prevent that(Can it be solved without putting the db in its own server?). Or doe's MySQL doesn`t know how to handle server restarts?


texts: multiple count and use subsctract function in one query

texts:
I try to do more advance query in mysql but got error in query, i try to count and group by. here is my query:
"select department,
(select count(id) from mcu group by dept) as tot1,
(select count(status_mcu) from mcu where status_mcu='done' group by dept) as tot2,
(tot1 - sum(select count(status_mcu) where status_mcu='done') as tot3,
FROM mcu"

can you help me out ? what wrong with my query ? i do some search in other threads and this is what i got. try do query like :
"select department,
select count(id),
sum(case when status_mcu='done' then 1 end)
...
"

not worked too. Any help is appreciate.

my sql version is 5.5.25 – casthrotopes


texts:
I'm running a high transactional database (~175k transactions / Minute on average,
almost 9M records per hour added and removed)

Up till recently this hasn't been too much of an issue as we've been at ~7.5M records added and removed but with the latest influxes of data the ghost cleanup doesn't appear to be able to keep up with cleaning up the unused space on tables / indexes.

A few days ago we reached 53 GB of 'Unused Space' across 16 tables (mostly 2 of them) so as a result started looking into the ghost cleanup process to find it runs once every 5 seconds and runs over 10 pages.

My current solution is that early morning I am running three threads of the following command:

DECLARE @2hours datetime = dateadd(hour,2,getutcdate())

WHILE getutcdate() < @2hours
BEGIN
    DBCC FORCEGHOSTCLEANUP ('DBNAME') WITH  NO_INFOMSGS
END

to catch up with the backlog from the night before (when most of our deletes take place)

I'm wondering if there's any way to change the default settings from 5 seconds and 10 pages to say every second or run over 20 pages, is there any way of doing that or should I continue just spinning up multiple cleanup procs to clear out the data, or if there's any other actions that can assist with this

Re-indexing runs on most effected indexs atleast once a week (most are every other day)

SQL Server 2012 Enterprise SP3_CU8 (upgrading to CU9 tomorrow) on AlwaysOn High Availabilty Cluster also with replication (distribution on a separate server)


texts:
I would like to ask a question how to setup azure syncing of two database on azure

i need real time data replication to the other database


texts: Does ALTER INDEX ALL REBUILD use more transaction log space with simple recovery model than rebuilding each index individually?

texts:
An "ALTER INDEX ALL REBUILD" operation on SQL Server 2012 failed because the transaction log ran out of space. The indexes have never been reorganized or rebuilt, so fragmentation is over 80% on nearly all of them.

The DB uses simple recovery model. I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?

In other words, could I reduce transaction log growth by writing a script to perform each rebuild individually? Are there any other factors to consider?


texts: SQL Server Backups-Order of FULL & T-log backups

texts:
I have an elementary question. I just started as a DBA with no administration experience and upon reviewing the Backup schedule, I noticed on one instance that the maintenance plan that performs the backups is set up with 4 steps. The first and last steps are maintenance, but the second and third are to take Full backups (2nd step) and then take T-log backups (step 3) of all user DB's.

The job that calls the maintenance plan is run at 6AM and 4PM. I am confused why they would take the FULL backups first and then T-log backups directly after the FULL? It would make sense to me if it was the other way around.
This is how it makes sense to me at least:

->at 610am, FULL backup completes

->at 4PM, take t-log backup to backup the t-log since 6AM

->at 410PM, take FULL backup

->at 6AM, take t-log backup for data since yesterdays 410PM FULL backup

->at 610AM, Take full backup

Can anyone think of a good reason for taking a FULL and then a t-log directly after? I tried asking the employees that have been here but they are clueless.


texts: Get real error from backup job failure instead of backup terminating abnormaly

texts: I've had log backup jobs fail for a few different reasons lately.  When I go to the job it tells me backup terminated abnormally.  However, when I run the backup script in SSMS it will tell me the real reason such as the destination drive being out of space.  I put a try catch around my command but it gives the same error I see from the job.  How do I capture the error that SSMS is providing?


texts:
I want to fetch record where start_date is 01/01/2017 and to_date is 02/25/2018 problem is it cannot show id 2 record

date store in varchar MM/DD/YY

This is my query:

SELECT *
FROM `new_item`
WHERE  `from_date` >= '01/01/2017'
  AND `to_date` <= '02/25/2018'

id 2 record not shown

  id   from_date     to_date
   1   01/01/2017    02/05/2018
   2   01/20/2017    06/20/2017
   3   02/05/2018    02/25/2018
   4   02/02/2018    02/20/2018


texts: How to have row values as column headers with the associated values in postgresql?

texts:
This is hard to explain but I'll do my best. So to make it simple, I have 3 tables with the following format:

Creator table

id | name
 1 | jack
 2 | doge
 3 | cardano

roads

id | creator_id | length
 1 |     1      |   3
 2 |     1      |   2
 3 |     3      |   9
 4 |     1      |   3

Blocks

id | creator_id | length
 1 |    1       |   4
 2 |    1       |   4
 3 |    3       |   5
 4 |    1       |   3

I can't seem to pick out the rows into columns, otherwise I would be posting here but the query I use to bring the data together is:

select name, layer, sum(length)
from ( SELect a.name, 'road routes'::text as layer, length
       from creator a
       LEFT JOIN road_routes b on a.id = b.creator
       where a.id in (73, 35, 33, 31)
       group by a.name, layer, length
     union
       SELECT a.name, 'block routes'::text as layer, length
       from creator a
       LEFT JOIN block_route b on a.id = b.route_creator
       where a.id in (73, 35, 33, 31)
       group by a.name, layer, length) x
group by layer, name

The expected result and what I need is a table with the following output:

type   | jack | doge | cardano | total
roads  | 8    | 0    | 9       | 17
blocks | 11   | 0    | 5       | 16

How to sort the data like this?


texts: SQL DB design to support user feeds (in application like facebook)

texts:
I have a social network server with a MySql DB. I want to show the users feeds like done in Facebook. Example - UserX now Friend with userY, userX did like on postX etc.

Currently I have table: C1 : UserId C2 : LogType (now friend, did like etc) C3 : ObjectId (Can be userId or postId) - set depending on the LogType.

Currently to get all related logs to show to the user I do the following queries: 1. Get All user Friends userIds 2. Query all rows which C1 is in userIds (I query completed) 3. Scan the DB and see - if LogType equals DidLike, check if post's OwnerId is the userId - if yes add it to logs.

And so on.

Obvious this is not efficient at all.

I am looking for a better way. I thought I had in mind: Create a new table (in addition to the Log table) C1 : UserId C2 : LogId (from Log table) C3 : UserID of the one who did the action When querying logs - look in the table and get related Logs (by LogId) from LogTable.

Updating the table: Whenever user doing action that should be in the log: 1. Add the Log entry to LogTable. 2. Scan the DB and see which users are interested with the Log (Who my friends are, Who is the owner of the post) and add related entries to the new table. (must be done in BG). 3. If user UNFRIEND another user - then loko in the logs for all rows where C3 == UNFRIENDED user id and delete them.

Any opinions? Other suggestions?


texts:
I'm investigating column oriented databases and came across Vertica.

My need is to feed the Vertica database from C code. I don't succeed in grabbing this information from Vertica: I'm told to use "vsql" and the "copy" command. All I want is issue INSERT statements to my Vertica database.

Can this be done?

For instance, in PostgreSQL you can do "embedded SQL" by linking the Postgres ecpg library to your C binary. I have no idea if such thing exists for Vertica, and I know of no other way.

Any ideas?


texts: How to select the rows affected by an update

texts:
When performing an update query (the following is just an example; any update query could be used) such as:
update t1
inner join t2 on t1.id=t2.id
set t1.name="foo" where t2.name="bar";
Query OK, 324 rows affected (1.82 sec)

how do you see which rows have been affected (the 324 rows affected in the response)? I tried converting the expression to a select, such as
select * from t1
inner join t2 on t1.id=t2.id
where t1.name="foo";

but this also returns the rows that were already name="foo" before the update, too.
Conceptually, I would like to do something like
select * from rows_affected;

but of course this does not work. Is there a method that will allow the inspection/selection of rows affected by an update query? Or is the only solution to do the select before the update to see which rows will be affected?


texts: Does installing some PostgreSQL options create some related tables?

texts:
After restoring an old pgsql database tables I realized that it contains tables like pg_ts_cfg, pg_ts_dict, geometry_columns and spatial_ref_sys.

I can't remember creating those tables and I wonder if the presence of features like PostGIS and FTS automatically creates them, or adds them to template1?

Is that the most likely reason?


texts: why we can specify table names enclosing them with []

texts:
Is there a reason we can specify a tablename with [schema].[tableName] instead of just schema.tablename?

is it JUST to allow spaces in a tableName?


texts: Best high availability solution to avoid maintenance downtime?

texts:
We currently only have one production SQL Server 2008 server.  Anytime we need to perform maintenance or install patches, it causes downtime as the server reboots.  In addition, if the server ever fails, that will obviously cause a great deal of downtime.

What is the best solution to avoid this maintenance downtime?  We are looking for a solution that will automatically switch to a failover server so we can apply rolling updates.


texts:
Say I have a Function like this:

create function house_analysis(ingeo geometry)
  returns table(count_all numeric, count_important numeric) as
$$
  select count(*), count(*) filter (where h.import_flag)
  from house_table h where st_intersects(ingeo, h.geom)
$$ language sql stable;

And I define a View like this:

create or replace view postzone_analysis as (
  select p.zipcode, ha.count_all, ha.count_important
  from postzone_table p, house_analysis(p.geom) ha
);

The question is:

How can I query the system catalogs (pg_catalog.*) with my view (postzone_analysis or its oid) get a list of the functions used within it? Their pg_proc.oid values are fine.

I know the database keeps track, since I cannot drop the function, but I can't find the entries in pg_depend.

DB is PostgreSQL 9.5.

(The real-life case is a lot more complex - it's scoped down to be lowest viable example. The view calls like 6 analysis functions, which combines the data from difference sources, and there are multiple views that are based on different area classes.)


texts: Getting around MASTER_HOST length limit in 'CHANGE MASTER TO...'?

texts:
I'm trying to configure external slave connecting to Aurora RDS. To my delight, I run into a silly problem where the Aurora cluster name -
 assigned by AWS - exceeds permissible MASTER_HOST limit of 60 characters.

I want to use the cluster name (not IP) since it can be dynamically changed by Route53 on failover.

Can anyone suggest a solution?


texts: Cannot locally import UTF-8 encoded SQL database on Mac

texts:
I was importing a confirmed UTF-8 encoded SQL database into Sequel Pro on Mac and got thrown this error halfway through:

  An error occurred when reading the file, as it could not be read in
  the encoding you selected (Autodetect - Unicode (UTF-8)).

  Only 1273 queries were executed.

Is this because the database file was originally from a windows machine and it's linebreaks stuffed things up?

I imagine I will get this error even if I tried to send the same database over file transfer to a server so how can I fix it's encoding?

-- MySQL dump 10.13  Distrib 5.5.40-36.1, for Linux (x86_64)
--
-- Host: localhost    Database: *****
-- ------------------------------------------------------
-- Server version   5.5.40-36.1-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `******`
--


texts: Cannot drop anonymous user from mysql.user

texts:
I am trying to DROP the anonymous users from my mysql.users database. However, I have been getting odd behavior. When I enter the command:

DROP User ''@'WOPR';

I was getting a generic error message. So, I rebooted my machine, and tried it again. This time I got the response

Query OK, 0 rows affected.

But when I put in

SELECT User, Host, Password FROM mysql.user WHERE User='';

The return is:

+------+------+----------+
| User | Host | Password |
+------+------+----------+
|      | WOPR |          |
+------+------+----------+

(WOPR is my hostname)

I run the command

DROP User ''@'WOPR';

and get the same result.

I am running a fresh install of MySQL 5.5 on Arch Linux, kernel version 2.6.33.

Does anyone know what can cause this behavior?


texts: Alter column text to varchar SQL Server 2000

texts:
How can I alter a column of type text in a SQL Server 2000 table, and change it to varchar?

I tried with

ALTER TABLE myTable
ALTER COLUMN myColumn varchar(8000) NULL
GO

and the error is

  No se puede modificar la columna 'MyColumn' porque es 'text'


texts:
I am using event notifications to capture data and log file autogrowth events for all databases on my servers.  I'm using the data to for analysis of database storage configuration.

In looking at the data I've noticed that the average duration for transaction log growth is well above anything I would expect which leads me to think that I'm either misinterpreting the data or overlooking something related to how transaction log autogrowth works.

This is an example of a log file growth event that was captured today:

<EVENT_INSTANCE>
  <EventType>LOG_FILE_AUTO_GROW</EventType>
  <PostTime>2013-08-29T11:14:26.447</PostTime>
  <SPID>97</SPID>
  <DatabaseID>32</DatabaseID>
  <NTDomainName />
  <HostName>[cleansed]</HostName>
  <ClientProcessID>4884</ClientProcessID>
  <ApplicationName>.Net SqlClient Data Provider</ApplicationName>
  <LoginName>[cleansed]</LoginName>
  <Duration>4173000</Duration>
  <StartTime>2013-08-29T11:14:22.263</StartTime>
  <EndTime>2013-08-29T11:14:26.437</EndTime>
  <IntegerData>64000</IntegerData>
  <ServerName>[cleansed]</ServerName>
  <DatabaseName>MyDB</DatabaseName>
  <FileName>MyDB_log</FileName>
  <LoginSid>[cleansed]</LoginSid>
  <EventSequence>14637017</EventSequence>
  <IsSystem />
  <SessionLoginName>[cleansed]</SessionLoginName>
</EVENT_INSTANCE>

Since duration is reported in miliseconds I'm reading this as it taking 69.54 minutes to grow the file.  Autogrowth for this log file is set to 512MB (limited to 2TB)

SELECT
    growth AS GrowthPages,
    growth*8/1000 AS GrowthMB,
    max_size,
    is_percent_growth
FROM MyDB.sys.database_files
WHERE type=1

GrowthPages GrowthMB        max_size        is_percent_growth
64000       512             268435456       0

All of the databases log to the same volume which is connected to a SAN via fiberchannel.  (I'd have to get with our SAN admin for more details on the storage config if needed).

The instance is SQL 2012 Enterprise, server is Windows 2008 R2 Enterprise.

Why would it take over an hour to grow the log by 512MB?  We're not noticing a delay in operations on any of these databases (unless we're just overlooking it).  There are a handful of other databases with similar duration; their autogrowth settings are the same.  Other databases with smaller autogrowth settings have proportionally smaller durations.