texts: Database Migration - Create New Filegroup and Move Objects

texts:
We have built a new SQL Server Database server instance. I am migrating our databases from existing instance to the new server instance using Backup and Restore.

One existing database has a single FILEGROUP. Is it possible to create a new FILEGROUP and move the data to this?

I cannot move the existing data file to the new FILEGROUP. So is my only option to manually script all Database Objects to the new FILEGROUP? Can I then delete the now empty FILE after moving the objects?

Restore Script:

;RESTORE DATABASE [TestDB] FROM DISK = 'I:\testDB.bak'
    WITH REPLACE, FILE = 1, NORECOVERY, STATS=10,
    MOVE 'TestData' TO 'H:\Test.mdf',
    MOVE 'Test_log' TO 'E:\Test_log.ldf'
;RESTORE DATABASE [TestDB] WITH RECOVERY


texts: How to update Different Date formats to one specific datetime formate

texts:
I need advise on conversion of date time, below you may see the example data with images for current and expected output

Input Table

Expected Output


texts:
This is really a generic question.

Suppose I have a replication pattern of a single write DB and multiple read DB replication slaves - I do not have to have GUID's as my unique IDs fields. Is that a correct assumption?

If I have multiple DB instances where all are writeable and sync between themselves, GUID's in unique ID fields is mandatory. Is that also correct?

I am setting up PostgreSQL on Amazon RDS.

Thanks.


texts: How to get Business days per month between two dates?

texts:
I need to get the total Business Days Per MONTH between 2 dates. I would like to incorporate a Holidays table too.

For Example,

@StartDate = '12/26/2013'
@EndDate ='03/19/2014'

I need the following

Month          BusinessDays
December 2013       4
January 2014        21
February 2014       19
March 2014      13

I found this thread which does the days per month but not the BUSINESS/WOrking days.

How to get the total days per month between two dates?

Any Help would be greatly appreaciated.

thanks

Tania


texts:
I have a query which gives the output as shown below.

SELECT *,
       DATEADD(SECOND, ( DATEDIFF(SECOND, InTime, OutTime) ), 0) AS Total
FROM   TableXYZ

EmpName InTime  OutTime DayStart    NameOfMonth YearSet Total
ABC 12:11:32    21:53:24    Day1    November    2016    1/1/00 9:41
DEF 12:05:56    21:20:58    Day1    November    2016    1/1/00 9:15

I tried to remove date from Total but doesn't work. how can I do that and get the output below?

EmpName InTime  OutTime DayStart    NameOfMonth YearSet Total
ABC 12:11:32    21:53:24    Day1    November    2016    9:41
DEF 12:05:56    21:20:58    Day1    November    2016    9:15


texts: Transaction Log Shipping across multiple SQL Server versions

texts:
I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server.

What I'm trying to do is to maintain a reporting (read only) version of a production transactional SQL Server 2005 database (180 Gb) on on a SQL Server 2012 server (and in the case of other clients 2008 R2). I can do simple back up and restore at any time but to improve latency between refreshes of the database, I'd like to see if I can do something like transaction log shipping.

If TLS is not available in this scenario what do the panel suggest as an alternative strategy.


texts:
I installed mysql on ubuntu. mysqld does not startup with the following errors -

2017-04-16T00:22:35.320639Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-04-16T00:22:35.321014Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170415 19:22:35
2017-04-16T00:22:35.321036Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2017-04-16T00:22:35.321354Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-04-16T00:22:35.322301Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-04-16T00:22:35.322490Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-04-16T00:22:35.323247Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-04-16T00:22:35.323361Z 0 [Note] IPv6 is available.
2017-04-16T00:22:35.323379Z 0 [Note]   - '::' resolves to '::';
2017-04-16T00:22:35.323411Z 0 [Note] Server socket created on IP: '::'.
2017-04-16T00:22:35.323450Z 0 [ERROR] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
2017-04-16T00:22:35.323464Z 0 [ERROR] Unable to setup unix socket lock file.
2017-04-16T00:22:35.323476Z 0 [ERROR] Aborting

2017-04-16T00:22:35.323497Z 0 [Note] Binlog end
2017-04-16T00:22:35.323545Z 0 [Note] Shutting down plugin 'ngram'

Please help.

Thanks


texts: Benefits of not having a clustered index on tables (heaps)

texts:
What are the benefits of not having a clustered index on a table in SQL Server?
Will:
SELECT * INTO TABLE_A FROM TABLE_B

Be faster if TABLE_A is a heap?
Which operation(s) will benefit if the table is a heap?
I am quite sure UPDATEs and DELETEs will benefit from a clustered index. What about INSERTs? My understanding is that INSERT "might" benefit from the table being a heap, both in term of speed but also other resources and hardware (I/O, CPU, memory and storage...).
What is the most scarce resource in terms of hardware? In terms of storage is a heap going to occupy less space? Is disk storage not the least expensive resource? If so is it rational to keep table as heap in order to save disk space? How will a heap affect CPU and I/O with SELECT, INSERT, UPDATE and DELETE? What cost goes up when table is a heap and we SELECT, UPDATE and DELETE from it?


texts: MySQL: Delete all rows older than 30 days, but only if count more than one

texts:
I need to cleanup my table of over 14.000.000 rows inside. I want to delete all entries, that are older than 30 days, but only if number of grouped items is larger than one. So, at least one item will stay.

Getting items and deleting them by date is not that problem. But it will delete all.

DELETE FROM parsed
WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))

I thought something like that

SELECT *
FROM parsed
WHERE TIMESTAMP < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 8 DAY))
    AND (
        (
            SELECT COUNT(*)
            FROM parsed
            GROUP BY item_id
            ) > 1
        )

But this will not work. Thanks for help!


texts: INNER JOIN between 2 tables vs WHERE on one table performance

texts:
Suppose I have two tables:

CREATE TABLE `doctors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `clinic_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `clinics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The next queries returns the same data but for large tables wich is better for performance?

Query with INNER JOIN

SELECT doctors.*
  FROM doctors
 INNER JOIN clinics
    ON clinics.id = doctors.clinic_id
 WHERE clinics.id = 1;
Query only with WHERE

SELECT doctors.*
  FROM doctors
 WHERE doctors.clinic_id = 1;


texts: My availability group doesn't work correctly

texts:
I recently created an "availability group" on sql server, I created my Listener, all is well the high availability group appears well in the roles of my cluster. However I encounter a small problem, the two replicas are not really synchronized

, then when I open my dashboard I have the error message /i.stack.imgur.com/Gf9wK.png . why it does not work ? I have already done everything but nothing done.

I use windows server 2012 R2 and SQL SERVER 2016 Enterprise.


texts: Trigger to prevent column value from A to B, and from B to A

texts:
I'm trying to create a trigger that prevents a column to be updated as follows:
If column value is "A", it cannot be updated to "B",
and if column value is "B", it cannot be updated to "A".
CREATE OR REPLACE FUNCTION cancelled_order()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$BEGIN
    IF OLD.status = 'Cancelled' THEN NEW.status != 'Accomplished';
    END IF;
    IF OLD.status = 'Accomplished' THEN NEW.status != 'Cancelled';
    END IF;
    RETURN NEW;
END;
$function$;

CREATE TRIGGER No_cancelled_acomplished
    BEFORE UPDATE OF status
    ON clinical.tb_orders
    EXECUTE PROCEDURE cancelled_order();

The error that comes out is:

Sintaxis error: !=

Any idea, why?


texts: Can I improve the way pg_dump exports inherited tables?

texts:
Given a postgresql 12 database with some table inheritance, and the child table adds no additional columns:

CREATE TABLE parent (
    id integer NOT NULL PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE child () INHERITS (parent);

If I run pg_dump (with pg_dump foo), the tables look like this in the export:

CREATE TABLE public.parent (
    id integer NOT NULL,
    name text NOT NULL
);

CREATE TABLE public.child (
)
INHERITS (public.parent);

If I then detach and reattach the child:

$ psql foo
psql (12.1 (Debian 12.1-2))
Type "help" for help.

foo=# ALTER TABLE child NO INHERIT parent;
ALTER TABLE

foo=# ALTER TABLE child INHERIT parent;
ALTER TABLE

... and try another pg_dump, the export has changed. The child table now explicitly lists the columns that are inherited from the parent:

CREATE TABLE public.parent (
    id integer NOT NULL,
    name text NOT NULL
);

CREATE TABLE public.child (
    id integer,
    name text
)
INHERITS (public.parent);

Is there anything I can do to get pg_dump to output the child table with no explicit columns?

It sounds like a contrived example, but I have a multi-Tb database that with child tables that have been detached and reattached back in the mists of time, and I'd love the schema exports to go back to being as simple (and readable) as possible.

In particular, it'd be great for everyone to see at a glance that the child table adds no additional columns.


texts:
I have a SQL SERVER DB that i want to backup via automated schedule (using GUI maintenance plan)  . I have 2 choices when configuring a schedule  :

1) do not bother about log backup , full backup and diffrencial bakcup event time , as if all the 3 backups occur on same time , it makes no difference .

2) make sure that log backup, full backup and differential backup never occur in same time by configuring carefully the time of each backup.

Below is the image describing the 2 choices (Schedule1 for choice 1 and chedule2 for choice 2 )

Do you know which one is the best ?

Edit : Sechdule1 may face some errors when backups are overlaping . Here the backups are of type log and differential hapening same time at 5:30 as the backup whatever the type is will be appended to the existing backup file. So the file was in use by log backup and differential failed . Even if no data was lost.


texts: mongodump 3.4.1 error reading collection: Closed explicitly

texts:
[BACKUP COMMAND]: mongodump -u username -p password -d database --port port -o output path.

[ISSUE]: error writing data for collection to disk: error reading collection: Closed explicitly.

The storage requirements of my MongoDB database are constantly increasing. So far the database is using 20 GB of disk space and I am encountering errors when I want to dump the database. It always fails and the above issue is displayed in the terminal shell when I use the above mongodump command. I have to reissue the mongodump command again and again until the backup of the database finishes.

So my questions are:

Is there a good alternative database backup tool/command for MongoDB?
Is there a stable and fast backup command for large-scale MongoDB databases?
Am I using the correct procedure?


texts: If then else case PL/SQL if text is in list

texts:
I have a column that will return something like this:

01
05
Z7, Z4, 01
09,08

I then have two static lists:
list a = [01, 02, 03, 04]
list b = [Z7, Z2, 05]

and our case statement returns one of the following:

group a
group b
mixed
none

I need to write a CASE statement to first breakdown each row using a comma delimiter, then loop through each value to see if the codes are part of list a exclusively, list b exclusively, a mix of both, or neither.

To put this into practice, we'd look at the first item, 01.

It exists in list a, but not in list b, so the case would return group a

The second result, 05, exists in list b, but not in list a, so it would return group b.

The third result, Z7, Z2, 01 contains multiple codes.  Because at least ONE of these codes is in list b, and at least ONE of these codes exists in list a, we the case would return mixed.  The fact that it also contains a code that doesn't fit into either group doesn't matter because at least one code exists in each list.

The fourth result, 09,08 would return none because neither 09 nor 08 exist in either list.

Is this possible to do in PL/SQL?


texts:
I have a SQL Server 2000 database used by our ERP system.

There are many columns that always have the same value. A table row is really huge, potentially exceeding the maximum row size in SQL Server 2000 (8kB).

I'm thinking about replacing these columns with constant calculated columns. This should decrease the size of the table. I expect better performance, because there is less data to read from disk, when the server needs to load the table in memory.

Is this a good idea ? Will I obtain the expected performance gain ?


texts: When a DBMS fails over after a failure, if it fails over to a server in a different and unrelated data center, then the IP address of the subdomain name of that database will need to be changed in that name's DNS server, and due to DNS propagation delays, it could be days before all the DNS servers of the world have the IP address for the new master, during which time some clients would still be trying to access to old master.  What can be done to deal with this, other than changing the client software to fetch the IP address of the master from somewhere, or is that the only option?  Thanks.


texts: Converting Database from GUID to Integer Keys

texts:
I have a medium sized database (~100 tables) that uses GUIDs as primary keys in all tables.

In one of our most active tables the GUIDs are causing an unnecessary increase in storage requirements.  The table is essentially a primary key (GUID), 4 foreign keys (GUIDs) and two datetime fields.

I would like to change the primary key and all foreign keys to integers, thus dropping the table to about a third of it's current size. There isn't a huge amount of data in the system yet, so converting existing data shouldn't be a big issue.

My main question here is, what would be the best approach?

Convert all referenced tables (and most of the tables in my database) to using integer keys.
Add an additional autoincrement integer identity to each of the reference tables to use as a foreign key, and maintain the GUIDs in those tables for outside reference (use through APIs, etc).


texts: Anyone want to try a script bash or python?

texts:
I need to automate master binary log purging to occur so that purging never goes back further than the last slave has the updated info for.

These slaves are mobile and there are 3 of them, and sync whenever the users get around to it.

I can do this manually but how to set this up in a script eludes me.

Any ideas?


texts:
I am trying to model the following scenario:

  -I have multiple company bureaus.

  -Each Bureau has multiple offices

  -Each office has multiple divisions

  -Each Bureau has employees that only work at the bureau level (think CEO's,CIO's,etc) and employees that work at the many office level

  -Each office has people who only work at the office level (maybe managers, chief,s etc) and people who work at the many divisions levels

  - Each division has people who work only at the division level (division chiefs, for example) and employees who work on projects

  -  Also, each division has projects that are run by division staff (employees, not the high level divisions chiefs)

I want to be able to do the following with the data:

query the structure of the organization (what divisions are in what office, for example)
query the various C level people from each division, office, bureau, etc.
find projects by all levels ( if given a bureau, office, or division get projects associated)
find people who are working on a given project.  These projects will have multiple roles

What I currently have is this:

But I can't get who works at the bureau or office level if they aren't tied to a division.  Any help would be great.

thanks


texts:
I am using mongo 2.6.7 I deploy a sharding cluster follow the guide:
http://docs.mongodb.org/manual/tutorial/deploy-shard-cluster/

My sharding cluster include:
- two replica sets (one primary + one secondary for each replica set)
- three config servers
- two mongos

I make sure sh.getBalancerState() is true. I prepared a amount of data about 1G. When I recheck by sh.status()

--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "version" : 4,
        "minCompatibleVersion" : 4,
        "currentVersion" : 5,
        "clusterId" : ObjectId("54c2644bf2759e71913a3b59")
}
  shards:
        {  "_id" : "test_repl_set",  "host" : "test_repl_set/mongo1.example.com:27017,mongo2.example.com:27017" }
        {  "_id" : "test_repl_set1",  "host" : "test_repl_set1/mongo3.example.com:27017,mongo4.example.com:27017" }
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "linux",  "partitioned" : true,  "primary" : "test_repl_set" }
                linux.components
                        shard key: { "_id" : 1 }
                        chunks:
                                test_repl_set   1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : test_repl_set Timestamp(1, 0)
                linux.distro
                        shard key: { "name" : 1 }
                        chunks:
                                test_repl_set   1
                        { "name" : { "$minKey" : 1 } } -->> { "name" : { "$maxKey" : 1 } } on : test_repl_set Timestamp(1, 0)
        {  "_id" : "test",  "partitioned" : false,  "primary" : "test_repl_set1" }
        {  "_id" : "testdata",  "partitioned" : false,  "primary" : "test_repl_set1" }
        {  "_id" : "testshard",  "partitioned" : true,  "primary" : "test_repl_set1" }
                testshard.testdata
                        shard key: { "x" : 1 }
                        chunks:
                                test_repl_set1  1
                        { "x" : { "$minKey" : 1 } } -->> { "x" : { "$maxKey" : 1 } } on : test_repl_set1 Timestamp(1, 0)

I only get data of testshard from test_repl_set and there is no data of testshard from test_repl_set1. I don't know why testshard is not being migrated and why mongos tell me there is only one chunk on test_repl_set1 ?


texts: Set names to attributes when creating JSON with row_to_json

texts:
Is it possible to rename default f1, f2, f3... names when using row_to_json function for only some columns?

I can do

row_to_json(customers)

returning

{"id_customer":2,"first_name":"bla","last_name":"second_bla"}

But if I want only names without id_customer, I have to use

row_to_json(row(first_name, last_name))

and then I get

{"f1":"bla","f2":"second_bla"}

And I would like to get this result with either default column names or my own. I know I can create my own composite type and use

row_to_json(row(first_name, last_name))::my_custom_type

but isn't it possible to do it right in the query without creating that type?


texts: Create SP for inserting data from a table to a table

texts:
I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the same). To do these I had to make some selects but how can I do the stored procedure to do that in a easier way??? When there are 2 telephone number separated with ',' with the first SELECT I insert to table3 the row with the phone number before the ',':

SELECT DISTINCT SUBSTRING(Telefono, 1, CHARINDEX(',', Telefono, 0) - 1) AS Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov
INTO table3
FROM #table1
WHERE Telefono LIKE '%,%'

INSERT INTO table3
--the record with the phone number after the [,]
SELECT DISTINCT SUBSTRING(Telefono, CHARINDEX(',', Telefono, 0) + 1, LEN(Telefono)) AS Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov,Fax
FROM #table1
WHERE Telefono LIKE '%,%'

INSERT INTO table3
--insert all the remaining rows
SELECT DISTINCT Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov,fax
FROM #table1
WHERE Telefono NOT LIKE '%,%'


texts:
is it possible to create in Mysql an admin-user that can modify everything exept the mysql-root user?

Thanks,
starflighter


texts: How to make a query from a database with schemas to a database without schemas, and vice versa, with DBLINK in postgres

texts:
I have a DB (DB1) in postgres 9.4 who use DBLINK to query another DB (DB2), and vice versa, using views and functions.

But now i have to add 2 schemes to DB1 (CLIENT1 And CLIENT2) and i want when i make a query from DB1 to DB2 use the PUBLIC schema, eg: DB1.CLIENT1 to DB2.public

Also when i make a query from DB2 to DB1 use the scheme being used in DB1, eg: DB2.public to DB1.CLIENT1

My problem is i have inside the views and functions the scheme used and i don't know how to change it to use specify the schema used in DB1 from DB2 and vice versa.

i append a example of the view used in each DB:

View DB1 to DB2:

CREATE OR REPLACE VIEW "cliente1"."datos_hacienda_bovina" AS
 SELECT t1.tropa,
    t1.anio,
    t1.peso_promedio,
    t1.clasificacion,
    t1.cabezas_clasificacion,
    t1.cabezas,
    t1.tipo,
    t1.codigo_procedencia,
    t1.procedencia
   FROM cliente1.dblink(( SELECT ((((((('port=5432 host='::text || (configuracion_trazabilidad.config_servidor)::text) || ' dbname='::text) || (configuracion_trazabilidad.config_base_datos)::text) || ' user='::text) || (configuracion_trazabilidad.config_usuario)::text) || ' password='::text) || (configuracion_trazabilidad.config_clave)::text) AS configuracion
           FROM cliente1.configuracion_trazabilidad
          WHERE (configuracion_trazabilidad.config_tipo_stock = 0)
         OFFSET 0
         LIMIT 1), 'SELECT
ingreso_hacienda.inghac_tropa AS tropa,
to_char(ingreso_hacienda.inghac_fecha, ''yyyy'') AS anio,
ingreso_hacienda.inghac_peso_promedio AS peso_promedio,
clasificacion_animales.clasani_sigla AS clasificacion,
ingreso_hacienda_detalle.inghacd_cantidad AS cabezas_clasificacion,
(ingreso_hacienda.inghac_numero_cabezas - ingreso_hacienda.inghac_numero_caidos - ingreso_hacienda.inghac_numero_muertos) AS cabezas,
0 AS tipo,
procedencias.proc_codigo AS codigo_procedencia,
procedencias.proc_nombre AS procedencia
FROM
public.ingreso_hacienda
LEFT JOIN public.ingreso_hacienda_detalle ON ingreso_hacienda_detalle.inghac_id = ingreso_hacienda.inghac_id
LEFT JOIN public.clasificacion_animales ON ingreso_hacienda_detalle.clasani_id = clasificacion_animales.clasani_id
LEFT JOIN public.procedencias ON procedencias.proc_codigo = ingreso_hacienda.inghac_procedencia
ORDER BY inghac_fecha, inghac_tropa'::text) t1(tropa integer, anio text, peso_promedio numeric, clasificacion text, cabezas_clasificacion integer, cabezas integer, tipo integer, codigo_procedencia text, procedencia text);;

View DB2 to DB1:

CREATE OR REPLACE VIEW "public"."articulos_tipo" AS
 SELECT t1.artti_id,
    t1.artti_codigo,
    t1.artti_nombre
   FROM dblink(( SELECT ((((((('port=5432 host='::text || (configuracion_trazabilidad.config_servidor)::text) || ' dbname='::text) || (configuracion_trazabilidad.config_base_datos)::text) || ' user='::text) || (configuracion_trazabilidad.config_usuario)::text) || ' password='::text) || (configuracion_trazabilidad.config_clave)::text) AS configuracion
           FROM configuracion_trazabilidad
          WHERE (configuracion_trazabilidad.config_tipo_stock = 0)
         OFFSET 0
         LIMIT 1), 'SELECT clasani_id, clasani_codigo, clasani_nombre FROM "cliente1".clasificacion_animales'::text) t1(artti_id integer, artti_codigo text, artti_nombre text);;

I tried creating two empty schemes in DB2 and then taking the schema name in the DB1 views but does not help me to query from DB2 to DB1.

1- What would be the best way to work from a DB with schemes and other without schemes?

2- What changes should I do?


texts: Should a table be partitioned before or after an INSERT?

texts:
I have a fairly simple question that is proving hard to search for.

I need to create a table that will be partitioned and into which (lots of) data will be loaded.

Is it more efficient to create the table with the partitions or add the partitions after I have INSERTed the data?

ADDITION: To make sure I understand the complete picture, if I was to truncate this table to then load (lots of) data again, should I drop the partitions and then add after the load?

ADDTION #2: If I can INSERT the data in the order of the partitions, I assume that would be faster than not?


texts: How do I create a CLR UDF with external name specifying namespace, class, and subclass?

texts:
I am creating a CLR UDF using external name and second level nested class names seem not to be permitted. Is this the case? When using the following syntax and including a first level class name and associated method, I am able to bind a UDF to the method, but not if I included additional subclass levels.

    CREATE FUNCTION [dbo].[udf_test](@variable [nvarchar](MAX))
    RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [assembly].[ns.first.second].[method]
    GO

Thanks


texts:
I ran into problem of job stopped after the sql server restart.

Is there a way to auto resume an incomplete job after sql server restart?

Thanks!


texts:
I have a SQL Server 2014 test box with SSRS installed. The site loads ok and reports are quick. When I try to go to Subscribe the page takes over a minute to load. From there if I pick a schedule it also takes minutes to load. This is a fresh box with nothing else installed. There's not antivirus installed.

The box has 2 x xeon @ 2.66 and 6GB of memory. I'm the only user on this box as it is just for testing.


texts: Getting VLF info with least privileged to run across multiple servers of different version

texts:
Per our monitoring scripts i am thinking to add monitor the status of VLF for each database across different SQL servers in our organization:
We have mix of servers from 2012 to 2019: I've found a really good script here https://www.sqlserverscience.com/tools/detect-databases-high-vlf-count/ but there is a problem with which i need some inputs on how to achieve above
The login which executes any scripts across those 100's of SQL server is not a sys-admin. I know there is  a way here https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate?view=sql-server-ver15 -- but this seems tedious as i think it would need a change across the servers where i would like to run the script
Is there a way i can execute the script with non sys admin privilege to get the VLF info from SQL servers 2012+?
Thanks


texts: Run an SSIS package located on a linked server from a stored procedure

texts:
I am creating a stored procedure on my First machine named Server1 and

would like to call an SSIS package named mypackage.dtsx located on my

Second machine named Server2

Noting that I already linked my Second machine to my First
machine and I am using SQL Server 2012

on both of them. How can I accomplish this?


texts: SQL Server Assembly on Failover cluster after Framework update

texts:
We have database that relies on a CLR assembly. The assembly references other assemblies. After a Windows Framework update we need to drop and reinstall the assembly to have it work.

Now the issue is that the database is part of a failover cluster. Yesterday the secondary server was updated and with it came a framework update. The assembly no longer works on the secondary, but we cannot reinstall it because it is a read only.

The primary server hasn't been updated, so no point in reinstalling the assembly.

Does anybody has experience in dealing with these sort of issues? If so is there a recommended way of updating assemblies?

We could do a failover and update the assembly. However in this case the assembly doesn't match the Framework on the other server. I guess we need to do a failover, reinstall the assembly and update the other server to the same Framework?

Thanks!


texts: Do ORMs provide interfaces for working with window functions?

texts:
Many of my friends as well as previous workplaces emphasized use of ORMs over working with SQL directly. I've been exposed to Laravals ORM (Eloquent), Silverstripe's ORM, Active record, and Entity Framework (kinda).

Now I'm writing SQL code that uses the OVER clause quite frequently and wondering if that were possible from typical ORMs as mentioned above. Do ORMs make the OVER clause accessible - or are there any ORMS that do that?

If so, would those ORMs make accessible all the OVER clause's aruguments (which I guess would be database specific)?


texts: Does encapsulating a statement in a transaction include the triggers it fires?

texts:
I have an INSERT that will fire a TRIGGER which will UPDATE an attribute on another TABLE. I need to make sure that if the INSERT is successful and the UPDATE inside the TRIGGER isn't then the whole thing should be rolled back.

Does encapsulating the INSERT in a transaction guarantee this?

If not, how can I do this?


texts: Use a scalar hashing function in a computed column - non-deterministic?

texts:
I have a scalar function

select [dbo].[fn_get_hash_fields] ('blah','dbo')

to which is passed a table and a schema
and which returns an expression representing a concatenation of all column values in each row.  For example, if the table had the 4 columns below, the function would return (CR's added for readability):

ISNULL(RTRIM([string1]),'')
+ISNULL(RTRIM(CONVERT(varchar(10),[number1])),'')
+ISNULL(RTRIM(CONVERT(varchar(10),[number2])),'')
+ISNULL(RTRIM(CONVERT(varchar(10),number3])),'')

Using this function, I then want to add a computed column that will store a 32 byte hash value representing all the values in each row:

alter table dbo.blah add [checksum] as
(substring(lower(convert(varchar(32), HASHBYTES('MD5',
[dbo].[fn_get_hash_fields] ('blah','dbo')
),1)),3,32))

The column is added, but the hash value is exactly the same for each row.

How do I convince SQLS that the function is non-deterministic and make the function evaluated for each row, computing different hash values?

I know that in the computed column I can simply use the expression returned by the function, rather than a call to the function, and that works.  But its not dynamic.  If I add a column to the table, it won't be represented in the hash until I drop the computed column and add an updated one that takes the new column into account.

Is there a way around this?
Is there a better approach to store a hash in each row built from all the columnar values in that row?

Here is the function:

CREATE function [dbo].[fn_get_hash_fields] (@p_table_name
VARCHAR(100),@p_schema_name VARCHAR(20))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @szSqlString as varchar(max)
SET @szSqlString = ''
      SELECT @szSqlString = @szSqlString +
            CASE DATA_TYPE
                    WHEN 'int'           THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'tinyint'       THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'smallint'      THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'bigint'        THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'datetime2'     THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'datetime'      THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'smalldatetime' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'date'          THEN 'ISNULL(RTRIM(CONVERT(varchar(10),[' + COLUMN_NAME + '],112)),'''')'
                    WHEN 'bit'           THEN 'ISNULL(RTRIM(CONVERT(varchar(1),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'decimal'       THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),[' + COLUMN_NAME + '])),'''')'
                    WHEN 'numeric'       THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),[' + COLUMN_NAME + '])),'''')'
                    ELSE                      'ISNULL(RTRIM([' + COLUMN_NAME + ']),'''')'
                 END + '+'
            FROM   INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = @p_schema_name
            and TABLE_NAME = @p_table_name
            AND (COLUMN_NAME != 'HASHKEYID')
            AND [DATA_TYPE] NOT IN ( 'TEXT', 'IMAGE')

    RETURN LEFT(ISNULL(@szSqlString, ''),LEN(@szSqlString)-1)
END


texts: Unable to Watch Live Data on Extended Events Session

texts:
I am running SQL Server 2014 Developer Edition on my PC.  I am trying to view the data in the system_health session.  In SSMS, I have connected to the database, expanded the server / Management / Extended Events / Sessions.  I see AlwaysON_health (stopped) and system_health (running).

When I right-click on the system_health session, I get the following error:

  The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage)
     The Extended Events session named "system_health" could not be found. Make sure the session exists and is started. (Microsoft SQL Server, Error: 25728)

I expand system_health and see the targets package0.event_file and package0.ring_buffer.  If I right-click either target and choose "View Target Data", I get this error:

  The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage)
  Cannot view the function 'fn_MSXe_read_event_stream', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

The function does exist.  I can run it:

select * from fn_MSXe_read_event_stream('system_health', 0);

When I do, I get this error:

Msg 25728, Level 16, State 10, Line 6
The Extended Events session named "system_health" could not be found. Make  sure the session exists and is started.

I know the system_health session is there.  I see in in the list of sessions:

select * from sys.dm_xe_sessions

address name       name
------------------ -------------
0x00000001FF6510C1 system_health

I have tried this with my own custom event sessions.  I can't watch the live data on them, either.

I can query the system_health ring buffer target data from sys.dm_xe_session_targets.

Why can't I watch live data for any extended events session?

(Note that there is a Microsoft feedback item for this issue.)


texts: Statistics and Execution plan in SQL Server

texts:
I have table Reg_Stats table without any indexes. Statistics are not updating automatically but I have enabled the autoupdate statistics (is_auto_create_stats_on, is_auto_update_stats_on are both are on).

My second problem: once I updated statistics manually, it is showing correctly but in execution plan it is not showing properly.


texts:
I want to get parent and their child data which means, parents first, then children.
Below is the query that I tried to convert to parent and their children

SELECT a.FactorTitle,
       a.FactorCode,
       a.parentId,
       c.FactorColumnCode,
       c.FactorColumnTitle,
       c.FactorColumnValue,
       c.isFactorValue,
       c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
FROM FactorSetup a
INNER JOIN PDModelSetup b ON a.PDModelCode=b.PDModelCode
LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
WHERE a.PDModelCode=2
  AND c.isFactorValue <> 'Y'
  AND a.FactorType='4'

I try below query

WITH EntityChildren AS
  (
   SELECT  a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
   FROM FactorSetup a
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   UNION ALL
   SELECT a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType,

  (SELECT count(*)
   FROM FactorSetup
   WHERE parentId=a.FactorCode) AS childCount
   FROM FactorSetup a

   INNER JOIN EntityChildren e2 ON a.parentId = e2.FactorCode
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   )

SELECT * FROM EntityChildren

After executing  the query I got this error

  Msg 467, Level 16, State 1, Line 1
  GROUP BY, HAVING, or aggregate
  functions are not allowed in the recursive part of a recursive common
  table expression 'EntityChildren'.
  Msg 462, Level 16, State 1, Line 1
  Outer join is not allowed in the recursive part of a recursive common
  table expression 'EntityChildren'.

Then I change my query and remove count(*)

WITH EntityChildren AS
  (
   SELECT  a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType
   FROM FactorSetup a
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   UNION ALL
   SELECT a.FactorTitle,
           a.FactorCode,
           a.parentId,
           c.FactorColumnCode,
           c.FactorColumnTitle,
           c.FactorColumnValue,
           c.isFactorValue,
           c.FieldType
   FROM FactorSetup a

   INNER JOIN EntityChildren e2 ON a.parentId = e2.FactorCode
   LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
   )

SELECT * FROM EntityChildren

But I got this error

  Msg 462, Level 16, State 1, Line 1
  Outer join is not allowed in the
  recursive part of a recursive common table expression
  'EntityChildren'.


texts: Two queries run much longer when combined into one

texts:
The first two queries below are very similar. The third is practically the union of the first two:

sql> SELECT sigla_partido, SUM(receita_valor)
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_receita
     GROUP BY sigla_partido

[2016-03-07 22:54:55] 32 row(s) retrieved starting from 1 in 352ms (439ms total)

sql> SELECT sigla_partido, SUM(despesa_valor)
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_despesas
     GROUP BY sigla_partido

[2016-03-07 22:54:59] 32 row(s) retrieved starting from 1 in 1s 648ms (1s 832ms total)

sql> SELECT sigla_partido, SUM(receita_valor), SUM(despesa_valor)
     FROM prestacao_conta_eleicao_candidato_2014
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_receita
     NATURAL JOIN prestacao_conta_eleicao_candidato_2014_despesas
     GROUP BY sigla_partido

[2016-03-07 22:55:30] 32 row(s) retrieved starting from 1 in 18s 264ms (29s 932ms total)

I expected the runtime of the third query to be close to the sum of the execution times of the first two queries. That did not happen and the third query took much longer than expected. For what reason?


texts:
We run RMAN backups via CRON jobs, but find the out-of-the-box emails with CRON to almost do what we want.  I know we can channel the RMAN output to /dev/null so no email is sent, which for any successful run of RMAN is the desired outcome, but if the RMAN job runs into an error, I would like the entirety of the standard output sent via email to the DBA team for review.  I suspect I can wrap my RMAN scripts with a bash shell script that will pipe the standard output to cron only in the event of a failure, but is there a better way to do this?

To summarize:

Can I pipe standard output to an email in the event of an error (and
ignore the standard output if no error exists) so we can use the
default mail functionality within cron?
If not, should I just wrap my RMAN backups scripts with logic so
that it will only return output from the shell script if an error is
encountered?
Is there another approach that I'm not seeing or able to find online
that accomplishes this?

My apologies if this has already been asked.  I've found a number of one-off questions and scenarios, but nothing that I've felt match up exactly with my desired outcome.

Thanks!


texts:
I have set up merge replication web sync to sync a publication from a SQL Server 2012 database.

I am trying to make some parameterised filters using SUSER_SNAME() or HOST_NAME().

When I do a sync is it possible to find somewhere what the values of SUSER_SNAME() and HOST_NAME() actually were for that sync? E.g. in the system tables.

I will need to experiment with security to work out what settings will affect these values, but I will need to find out what these values actually are to verify my settings.


texts: Selecting from a function that returns a sys refcursor or an alternative

texts:
I have a query that returns a resultset of three columns, namely SSN,PAID_YEAR and PAID_TOTAL. From this query I can:

Create a view and then query it.
Create a function and return resultset

If I go the first way a simple query like the following takes more than 20 seconds:

SELECT PAID_YEAR,PAID_TOTAL FROM VIEW_1 WHERE SSN=12345678912882;

I know that is because when I query a view the engine first brings all the rows of the view and then it filters the rows for the criteria supplied.

If I go the second way I can send a parameter and make the engine look only for those rows that match the condition and return the recordset. But I do not know how to then SELECT from that returned resultset. I took a look at pipelined tables but didn't quite get how to benefit them. So my ultimate question is if it's somehow possible to select from the resultset that is returned from a function like this:

SELECT * FROM FUNCTION_1(12132323232).

If yes, then how, if no, what would be an alternative way?

EDIT:Here's the explain plan:


texts: PostgreSQL: pg_upgrade failing because hstore incompatible

texts:
I'm trying to migrate from PostgreSQL 9.4.5_2 -> 9.5.3

I'm trying to use pg_upgrade (from 9.5.3 distribution) and getting the following error:

pg_dump: [archiver (db)] query failed: ERROR:  incompatible library "/usr/local/lib/postgresql/hstore.so": version mismatch

Anyone know how to fix this? must I pg_dumpall and then reload? That has worked in the past. Trying to use pg_upgrade to save some steps.


texts: Differential backups in SQL Server 2008 R2

texts:
I created a maintenance plan to backup all user databases (full) and set them to expire after three days.

This has proved to be cost inefficient in regards to disk space so I want to switch to differential backups.

I then created a maintenance plan to backup all user databases (differential) and set them to expire after seven days.

I had thought the first time this job ran that the first backup would have to be full but it's not, it's significantly less than what a full backup should be.

I thought it would work like this:

Day 1: Full

Day 2-7: Differential

Then on day one it would do another full backup and start all over.

Am I just misunderstanding how differential backups work?

I mean what did the plan back up differentially if it didn't do a full backup first?

Thanks!


texts: mysql takes too long on "checking permissions"

texts:
We have about 20 databases with about 8000 tables each. Modifying the software is not an option (it is bought), and it seems most of the cpu is consumed by "checking permissions". While the s/w accesses the DB the DB cpus get all to 100%, while processlist report 99% of queries stuck in "checking permissions". Any idea on how to improve this, or how to disable permissions in mysql altogether?

Tables are innodb file_per_table, table_open_cache=60000,    table_definition_cache=50000 The database has only 3 users. Nearly    all of the queries are like the one below. DB runs on Centos 6.5

mysql> SHOW FULL TABLES FROM `XXXX` LIKE 'HISTORY_CONFIG';
+---------------------------------------+------------+
| Tables_in_XXXX (HISTORY_CONFIG) | Table_type |
+---------------------------------------+------------+
| HISTORY_CONFIG                        | BASE TABLE |
+---------------------------------------+------------+
1 row in set (0.12 sec)

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration   | Query                                                    |
+----------+------------+----------------------------------------------------------+
|        1 | 0.16267675 | SHOW FULL TABLES FROM `XXXX` LIKE 'HISTORY_CONFIG' |
+----------+------------+----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profile context switches;
+----------------------+----------+-------------------+---------------------+
| Status               | Duration | Context_voluntary | Context_involuntary |
+----------------------+----------+-------------------+---------------------+
| starting             | 0.000077 |                 0 |                   0 |
| checking permissions | 0.000014 |                 0 |                   0 |
| checking permissions | 0.000013 |                 0 |                   0 |
| Opening tables       | 0.000038 |                 0 |                   0 |
| init                 | 0.000017 |                 0 |                   0 |
| System lock          | 0.000015 |                 0 |                   0 |
| optimizing           | 0.000013 |                 0 |                   0 |
| statistics           | 0.000017 |                 0 |                   0 |
| preparing            | 0.000016 |                 0 |                   0 |
| executing            | 0.000015 |                 0 |                   0 |
| checking permissions | 0.111432 |               360 |                 189 |
| Sending data         | 0.000042 |                 0 |                   0 |
| end                  | 0.000017 |                 0 |                   0 |
| query end            | 0.000013 |                 0 |                   0 |
| closing tables       | 0.000012 |                 0 |                   0 |
| removing tmp table   | 0.000016 |                 0 |                   0 |
| closing tables       | 0.000013 |                 0 |                   0 |
| freeing items        | 0.050857 |               129 |                  59 |
| cleaning up          | 0.000043 |                 0 |                   0 |
+----------------------+----------+-------------------+---------------------+
19 rows in set, 1 warning (0.00 sec)

UPDATE
Solved by creating a mysql-proxy lua script which smartly caches successfull table hits and answers instantly. This was an application-specific fix though.


texts: Query to determine start and end dates based on time overlap


texts: Security implications of enabling CLR on shared instance

texts:
My database is currently isolated in its own instance and VM.  My customer is retiring that server and I need to migrate the DB to a new environment.  My DB uses CLR, so the new environment needs to have that enabled.

The customer proposed using an existing server with an existing SQLServer 2014 Standard instance hosting DBs from other software vendors on it.  I'm concerned about incurring a sort of "you break it, you bought it" liability, since I can't vouch for the security strategy of those other DBs or their front-ends.  I don't want to do something that would introduce a significant risk.

I run into similar scenarios with other customers from time to time.  I'm a database developer.  I occasionally provide DBA services pertaining to my own product for my small to medium business customers, but I am by no means a skilled DBA.  Each customer has different budgets, priorities, IT staff, and security policies, but generally none of them have IT/DBAs that can understand and/or make competent risk assessments at this level of detail.

How would enabling CLR on a shared instance affect the security of the other DBs?  (I am not asking about database-level permission_set levels, just enabling CLR itself).  I need to understand the risk (if there is any) well enough to be able to advise a 'best practice' and a customer-specific 'secure enough' alternative.


texts: Does SQL Server Query Store capture parameter values?

texts:
The new Query Store introduced in SQL Server 2016 is great. It's a nice replacement for much of what I used to do with the older Profiler tool. However, I haven't found a way to capture parameter values associated with individual calls to the high resource-consuming queries it sniffs out. Is this possible?

I understand that Query Store deals more with aggregated data vs. individual calls, so I suspect I might be out of luck here. When I find a slow query, I find it handy for troubleshooting to also have the parameters associated with one of its slowest calls. I'd like to know how to do this using the latest and greatest tools. (I do not miss using Profiler!)

Security-wise is Query Store any less locked down than Profiler? I would think it need to capture data from individual calls at some level in order to calculate aggregates. Just not sure if it stores any of it.


texts: SQL Authenticated Account is unable to select a View in MS SQL Server

texts:
I am trying to select a view using a SQL authenticated account. This view is actually reading a spreadsheet from a local drive (through a LinkedServer). I am getting below error:
Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.

Currently, the linked server is set to For a login not defined in the list above, connection will be made using the login's current security context. The spreadsheet is on the same server.
How may I resolve this error and select the view using the same account?
The account is added as db_owner.
P.S If I run the view using a windows authenticated account, it is working fine


texts: Average Time to Load 1.2 million records from Oracle INTO SQL SERVER 2008R2

texts:
I have an ssis package which loads 1.2 million records of data from oracle into SQL SERVER 2008R2(with 32 GB RAM) dataware house table once every week. (truncating and loading)

This process is taking 5 hours to complete the load of 1.2 million records.

If any body did the same thing,i wanted to know their average time to complete the load.

I am new to ssis. Please advice me. Is 5 hours an optimum time ??

Note: the destination table is de-normalized and no indexes are there on it because it is in the dataware house.


texts: how to downgrade an unknown (2005) interface name from SSIS 12 to 05

texts:
The namespace Microsoft.SqlServer.Dts.Runtime.Wrapper was research, but a suitable SSIS 2005 counterpart to a SSIS 2012 interface named IDTSConnectionManager100 was not found.
Any help for the concrete old interface name would be appreciated.
Or do you know a kind of mapping overview / website for this kind of issue?


texts: Display Monthly Attendance Report in MySql

texts:
I am doing a School Management System in php using Mysql DB. I am stuck in my Project.
Please anybody suggest what I am doing wrong.

I have two tables in my database; one is to store Students records another one is to store their attendance day wise

Now I want to display a report of all students in particular class for the current month whether they were present or absent. But I am capturing only Absent students details only in attendance table.

I have written sql query to display the result here is:

SELECT tab.class, attend, DATE, ta.rollno, ta.StdNm
FROM tbl_absentees tab, tbl_admission ta
WHERE ta.Cls = class
  AND ta.rollno = tab.rollno
  AND class =22
  AND attend =  'A'
  AND DATE =  '2013-06-07';

The result is:

Class Attend RollNo StudentName

But I want to display in 31 days table manner by taking only Date in attendance table
if attend=A display A for absent Days otherwise Display 'P' for remaining days

How can I do this in mysql? Can anyone suggest/give me an idea to achieve this.

Sorry for misclarification in my question. Actually I want to display an attendance report for a particular month where data comes from two tables:

the first table consists of StudentName, RollNo, Class
the second table consists of Date, Status, RollNo, Class

Now I want to display report like this.


texts: SQL Server Instance Root Directory & Shared Feature Directory on Partition with 64k Allocation Unit Size?

texts:
I am in the process of setting up new test servers to migrate to SQL Server 2017. For certain reasons I dont have the option to have a separate drive partition for just SQL binaries so my options are to put the SQL binaries on C:\ with default allocation unit size or G:\ with 64k allocation unit size along with the data files.

I would prefer to avoid C:\ and put the binaries on G:\ but am not sure if there would be any negative performance impact with them being on a disk with 64k format.

Is one better than the other or does it not really make any difference for the binaries?


texts: MySQL - How can I improve this query? EXPLAIN attached

texts:
I have a query that I feel should go much faster then it does. Right now it'll execute around 1,5xx secs each time. I don't have any advanced knowledge of looking at the EXPLAIN and draw conclusions. Maybe someone can help?

The query has all its ID-fields indexed and unsigned.

"reservations" contains around 100,000 records and "reservation_nights" around 180,000 records. All other tables have under 1000 records each.

"profiles" is LEFT JOIN:ed because not all reservations has profiles.

Here's the query:

SELECT DISTINCT(reservations.id), `reservations`.`referenceId`, `reservations`.`referenceStatus`, `reservations`.`status`, `reservations`.`firstname`, `reservations`.`lastname`, `reservations`.`dateArrival`, `reservations`.`daysToCheckIn`, `reservations`.`rateCode`, `reservations`.`roomCode`, `reservations`.`totalNights`, `reservations`.`rateRevenue`, `reservations`.`dailyRate`, `reservations`.`totalRevenue`, `reservations`.`foodBeverageRevenue`, `reservations`.`additionalRevenue`, `channels`.`name` AS `channelName`, `channelgroups`.`name` AS `channelGroupName`, `markets`.`name` AS `marketName`, `profiles`.`totalReservations`
FROM `reservation_nights`
INNER JOIN `reservations` ON reservations.id = reservation_nights.reservationId
INNER JOIN `channels` ON channels.id = reservations.channelId
INNER JOIN `channelgroups` ON channelgroups.id = channels.channelgroupId
INNER JOIN `markets` ON markets.id = reservations.marketId
LEFT JOIN `profiles` ON profiles.id = reservations.profileId
ORDER BY `reservation_nights`.`dateStayed` DESC
LIMIT 100

Attached is the EXPLAIN from PHPMYADMIN. Where should I start optimizing this query?


texts: Remote PostgreSQL DB with local buffer ... how could it look like

texts:
I have a sensor which is generating a data vector let's say every second (adjustable to up to 100 vectors per second) at a remote location. The location got 4G modem to send the data. Due to power limitations I have chosen to use a Raspberry Pi 3+ to receive the data from the sensor, decode it and send it to my VPS server.
On my server running Ubuntu 18.04 I have installed TimescaleDB on top of PostgreSQL which is a good combination to handle time-series data and I would like to store the sensor data in there and publish then on my website. I'm keen to hear your thoughts of how the data flow could look like and I have some questions.

What would be the best way of inserting the sensor data from the RPi into the server DB? Is it save to insert them directly from the RPi using the libpq library in my C software or should I transport the data to the server in a different way and then insert the data into the DB at the server side?

At the RPi side, I would like to have a local buffer to prevent losing data if the connection to the server breaks. I could simply use a circular buffer in my C program or a named pipe, or I could install PostgreSQL/TimescaleDB locally, insert the data there and then use postgres_fdw or dblink to transfer the data from there to my server DB and delete the entries from my local DB. What would you suggest?


texts: SQLCMD mode: environment variable syntax?

texts:
I assume the best way to set my SSMS (SQL Server 2005) output file path during runtime of T-SQL, is to use SQLCMD mode.

How do I use environment variables (e.g. %systemroot%) in the code? The following bombs:

:set mypath %systemroot%
:out $(mypath)"\the_result.txt"
select * from myTab

In other words, when using SQLCMD not from the command line, but inside my T-SQL code.
I just need to sort out the syntax.

(And also perhaps, though forum protocol is probably to ask it as separate questions:

Is there an environment variable for the folder of this .sql / the working folder?
How do I prevent the line counts to go to my output file also?
)

Thanks!


texts: How to automatically stagger transaction log shipping to minimize bandwidth usage peaks?

texts:
The following question pertains to Microsoft SQL Server transactional log shipping (TLS).
We're using SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions.
Background
I have a primary data center (A) and a secondary, disaster recovery data center (B).  Let's say I have 64 databases that need to be log shipped of varying sizes, but no one database exceeds 50GB.
I'm currently using the default SQL Agent Jobs that SQL Server automatically creates, so I have 64 SQL Agent Jobs.
I want to log ship every database every 15 minutes.  Assume that's possible to do if I just transferred all the files back-to-back.
Issue
Let's also say that the path from A to B travels over the public internet, and so I am paying for the bandwidth to ship these logs.  I'm metered at the 95th percentile.  I want to smooth out or optimize bandwidth so as to minimize the chance of paying overages. I'm using backup compression.
Possible Resolution
My current idea is to write a script that will customize the start time of each backup job automatically. This script could be run as frequently as desired to maintain optimal transfers. Having it run regularly will enable new log shipping backup jobs for newly added databases to be optimized without human intervention.
The current SQL Agent Jobs all start with the string "LSBackup" such that I can list them all using:
SELECT * FROM msdb..sysschedules WHERE name LIKE 'LSBackup%'

I can fetch the list of all the SQL Agent Jobs and store them in a table variable, then iterate in a WHILE loop calling EXEC msdb.dbo.sp_update_schedule to update the @active_start_time to space the jobs appropriately.
What value should I use for the start time for each job?
In order to answer this I need to know how big the log backup file might be in order to optimally space the jobs.  Can you predict how large a transaction log backup will be before you run the backup? Alternatively, I could look at the past days' log backups on the local file system to determine the relative weights for each database. However, that's not really going to work for a brand new database that has little to no Transaction Log backup history.
Assuming it is possible to determine how relatively large each backup will be, what's an optimal algorithm to space them out to achieve minimal bandwidth impact when being metered at the 95th percentile?


texts:
In our current process, log backup is done by log shipping backup job.

Is there any need to configure backup job for transaction log in next AlwaysOn environment?

If we must configure backup, we want to set “checkpoint” with log backup job.


texts: Identify cause of unusual transaction log growth

texts:
I have an application running on SQL Server 2008 (it's a cluster but it hasn't failed over.)

Previously, the transaction log used to grow to 10-20GB every 2-3 days. We would shrink it back (after backup) to let it fill up again. Over the last few weeks, the log has been growing at 20GB a day; we don't believe we have changed the application code, so are wondering what process is causing the increase in growth rate.

What is a good strategy to identify transactions causing the largest increase in transaction log size?

Backing up or shrinking the file will release the disk space. I just think that is just a band-aid over the actual problem.  The application is doing more of something; thereby using more resource than before.


texts: How to execute two transactions concurrently in PostgreSQL?

texts:
I want to execute two transactions concurrently in pgAdmin:

--The first transaction should modify the age of Paul
BEGIN;
DECLARE newage INTEGER;
--the original age of Paul is 32
newage := Select age FROM COMPANY WHERE name = Paul;
newage := newage+10;
update company set age := Sage where name = Paul;
commit;

--The second Transaction shows all information about Paul including his age
Begin
select * from company where name = 'Paul';
commit;

Both parts were written in the same SQL Editor. My first problem is that it doesn't compile, I obtain the message:

ERROR:  syntax error at or near "INTEGER"
LINE 2: DECLARE newage INTEGER;


texts: DBCC Check DB fails due to operating system error 665

texts:
We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665.
The fragmentation level of the disk drive is 0% as it is automatically defragmented weekly.
The database is 24/7 OLTP database. Only way the checkdb completes is when we turn off the application. Now online solution suggests

Increase the size of the disk drive,  I have 800gb free space on the drive which has a database with 550 gb used space
Defragment the data drive- Currently automatically defragment weekly
Break the database into smaller files- Currently not possible due to downtime
Consider placing the database files on ReFS volume which does not have the same ATTRIBUTE_LIST_ENTRY limits that NTFS presents. You must reformat the current NTFS volume using ReFS. – Again not possible due to downtime

Currently the checkdb checks with the option Physical only and using Ola Hallengreen script
My short term proposal is to do the checkdb into another server. But I am running out of ideas if there are other ways to cure the problem without any downtime.


texts: I have more then 1000 tables in my database. On few tables I found duplicate Indexes. Now I want to find duplicate indexes in the database and remove them. Means, if a table have 2 or more indexes on same set of columns then remove all except one.


texts: How to efficiently query relationships across multiple tables, stored in multiple rows

texts:
I'm having trouble conceptualizing how to query a set of tables that have various granularities of data, to get the information that I want.  Let me explain the general table structure:

Table 1: Recipes - this consists of two columns, that can be thought of as parent and child.  Two such relationships might be this set of rows:

Recipe      Ingredients

Salad       Lettuce
Salad       Dressing
Salad       Cucumbers
Salad       Chicken
Pasta       Spaghetti
Pasta       Tomato Sauce
Pasta       Chicken

As you can see, there might be "children" shared across multiple parents (in the example above chicken is in both my made up salad, and my made up pasta), but the full set of children uniquely defines a single parent (if you have the grouping of lettuce, dressing, cucumbers, and chicken, it is always salad - there's no ambiguity).

On the other hand, let's say I have a table that has a list of ingredients (IngredientList), some of which are just by themselves, and some of which are part of a recipe.  I can identify how things are grouped.  Conceptualize this information as the following example

Ingredient     IngredientGroup

Flour          1
Lettuce        2
Cucumbers      2
Dressing       2
Chicken        2
Butter         3
Ground Beef    4

The output I want collapse each ingredient group into either itself (if it isn't part of a recipe) or the recipe (if the ingredients are part of a recipe).  Thus, the output I want is:

Flour
Salad
Butter
Ground Beef

If ingredients did NOT share children, I know I could do something like this:

select coalesce(recipes.recipe, ingredientlist.ingredient)
from ingredientlist
left join recipes on recipes.ingredient = ingredientlist.ingredient
group by coalesce(recipes.recipe, ingredientlist.ingredient)

except, I know I will join chicken to both salad and pasta, thus getting "pasta" in my output - which I don't want.  I need some way to uniquely tie the set of ingredients (which is across four rows, and grouped together using the "IngredientGroup") to the recipe that I want.  Is there a way to do this effectively?


texts: Extract MySQL information from a compressed tar.gz file

texts:
I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it.

I've never had to do this before. The only way I can access the database is through phpmyadmin. There is an 'import' feature in phpmyadmin, but this doesn't accept files in the tar.gz format, only sql.

Is there any way I can convert from .tar.gz to sql? I know I can upload from an sql file without a problem.

I don't have access to any 'back-end' stuff on the web-server, so I'm limited with what I can do locally on my PC.

Any help is much appreciated.


texts: I have a table with over 70 million rows in and I have viewed the execution plan which has identified a missing index.  I took a snapshot of data a short while ago and created this index on a local test database (which was fast to create) and the improvement is exactly what is needed in the live system.  What I'd like to know is, is there anyway to tell how long it will take to create on the live system?


texts:
I had an issue after backing up and restoring the DB's from server to server, We have since had some issues with missing information.  Is there a way at this point in time to know if the databases were restored fully?
Are there log files anywhere?


texts:
There is a table called ta with schema db2admin.
When I ran db2 select tabname,tabschema from syscat.tables wheretabname='ta' and tabschema='db2admin', it returned the row, which means the table was there.

However, when I ran the query db2 select * From db2admin.ta under the same user account, this error was raised:

  SQL0204N  "DB2ADMIN.TA" is an undefined name.  SQLSTATE=42704

What is the reason for this?


texts: Filtered index not chosen, and rejected with hint

texts:
I'm trying to understand why a non-clustered index is not available for a given query when filtered.  The relevant part of my (large) query is this:

) results
JOIN BE_Insurance ins
ON results.PayorId = ins.Id

Up in the select, I'm only grabbing ins.name.  The index I initially created was this:

CREATE INDEX IX_BE_Insurance_PayorId_PayorName
ON      BE_Insurance (Id) INCLUDE (Name)
WHERE ParentId IS NULL

My query is such that only payorIds with a NULL parentId will be chosen, but I understood the optimizer's disinclination to choose it.  But when I added a hint to try to force the index, the whole thing error'd out.

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

I assumed it would follow my hint, and maybe error out at execution time if I wound up with some bad data and the index was missing some needed values.  Removing the filter from the index caused it to be chosen successfully by the query (even without the hint).

Are indexes with a WHERE clause ineligible for queries like this?  Are they eligible only if the optimizer can be guaranteed that the filter is valid and won't produce any missing values?

As requested, here's the whole query, which is still a work in progress.

SELECT results.*
FROM (
        SELECT auths.*,
               worked.WorkedHours,
               worked.WorkedUnits,
               worked.WorkedAmount,
               worked.ActiveClients
        FROM (
                SELECT PayorId,
                       SUM(AuthHours) AuthHours,
                       SUM(AuthUnits) AuthUnits,
                       SUM(AuthAmount) AuthAmount
                FROM (
                        --DYNAMIC TEMPLATE ----------------------------------------------------------------------------------
                        SELECT PayorId,
                               PayorName,
                               AuthHours AuthHours,
                               AuthUnits AuthUnits,
                               AuthAmount AuthAmount
                        FROM PayorAuthorizations_Level1Data_Authorizations auths WITH(NOEXPAND)
                        WHERE OrganizationId = @organizationId AND StartDate <= @endDate AND @startDate <= EndDate
                        --/DYNAMIC TEMPLATE----------------------------------------------------------------------------------

                        --INTERSECT / EXCEPT dynamically generated queries
                ) q
                GROUP BY PayorId
        ) auths
        JOIN (
                SELECT PayorId,
                       SUM(ISNULL(TotalWorkedHours, 0)) WorkedHours,
                       SUM(ISNULL(TotalWorkedUnits, 0)) WorkedUnits,
                       SUM(ISNULL(TotalWorkedAmount, 0)) WorkedAmount,
                       COUNT(DISTINCT clientId) ActiveClients
                FROM (
                        --DYNAMIC TEMPLATE ----------------------------------------------------------------------------------
                        SELECT PayorId,
                               TotalWorkedHours,
                               TotalWorkedUnits,
                               TotalWorkedAmount,
                               clientId clientId
                        FROM PayorAuthorizations_Level1Data_CurrentlyWorked worked WITH(NOEXPAND)
                        WHERE OrganizationId = @organizationId AND StartDate <= @endDate AND @startDate <= EndDate
                        --/DYNAMIC TEMPLATE----------------------------------------------------------------------------------

                        --INTERSECT / EXCEPT other dynamically generated queries
                ) q
                GROUP BY PayorId
        ) worked
        ON auths.payorId = worked.payorId
) results
JOIN BE_Insurance ins WITH (INDEX(IX_BE_Insurance_PayorId_PayorName))
ON results.PayorId = ins.Id
OPTION(FORCE ORDER, MERGE JOIN)


texts: Does RCSI use less or same space than SNAPSHOT? Why?

texts:
Trying to better understand row-versioning based transaction isolations - RCSI (Read Committed Snapshot Isolation and SNAPSHOT) - in SQL Server...

MSDN online article "Choosing Row Versioning-based Isolation Levels" states:

*"For most applications, read committed isolation using row versioning is recommended over snapshot isolation for the following reasons:

It consumes less tempdb space than snapshot isolation"*

which contradicts to the statement from Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation":

"Row Versioning in Read Committed Using Row Versioning
For queries that are running under read committed using row versioning, row versions are necessary only for the duration of each SELECT statement in the transaction. However, row versions are maintained until the end of the transaction. This is an important point to consider for tempdb storage. The storage impact will be the same as it would be for snapshot isolation."

So, why does RCSI use less (or same) space?

UPDATE:
Probably I formulated the original question bad but it implies that there are RTFM explanations and confirmations for both contradictory "theories" and I'd like to understand:
Which is correct?
Why is the one is really correct?
and
Why isn't another really correct (Why is another wrong?)?

SUBQUESTION:

In case of multiple updates of the same record in a single transaction, how many versions are being stored?


texts: Backup sqlserver 2019 and restore to Sqlserver 2017

texts:
I have only 1 table in SqlServer 2019 that I want to backup and restore into SqlServer 2017 in different server.
To SqlServer 2019 I have access to server and with SSMS but to SqlServer2017 I have access only with SSMS
How can I do that?


texts: SQL Agent job step fails after upgrading to Powershell 5

texts:
I've not seen this issue discussed on the web, and have searched far and wide. I have a brand new HP DL380 running SQL Server 2008R2 standard. It's not yet in production, and I'm testing SQL Agent jobs. The server is running Windows Server 2012R2 Standard, and came with Powershell 4 installed.

I upgraded to Windows Management Framework 5 (Powershell 5), ran the first script through Powershell ISE and everything was ok. Then I executed the same script from SQL Agent and it failed with: "The term 'New-Object' is not recognized as the name of a cmdlet, function, script file, or operable program."

The job step is a CmdExec type, uses a proxy account, and calls powershell.exe to execute the script. If I make the proxy account a member of the local administrators group it works. If I roll back to Powershell 4 it works (no other changes). But if I again upgrade to Powershell 5, it fails.

It's obviously some type of permissions issue, but there is no clue as to what part of the security universe to investigate.

Thanks in advance for any suggestions.


texts: Select distinct value from row if condition exists

texts:
I am new to SQL and I am having trouble constructing a SELECT statement.

What I would like to do is select distinct parts that have a location of A1 if it exists. If a part has no locations of A1 I would like to select distinct material with no condition of location.

Is there a way to make this select statement 1 query or do I need to break it up in selecting the distinct condition true values and selecting the distinct condition false values?

Sample data:

+------+------+----------+
| PART | TYPE | Location |
+------+------+----------+
| A    |    1 | A1       |
| A    |    2 | A1       |
| A    |    1 | A2       |
| A    |    2 | A2       |
| B    |    1 | A2       |
| B    |    1 | A3       |
+------+------+----------+


texts: How costly is opening and closing of a DB connection?

texts:
How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL

... when the DB software is on localhost?
... when the DB software is on another machine?


texts: SSRS managed shared subscriptions data from from database

texts:
While running an ETL process we need to pause the shared managed SSRS subscriptions  and then enable them and run them accordingly when our ETL has progressed. In order to do that we take a backup of the ReportServer.dbo.Schedule  table just before we pause the subscriptions and we compare it with the time that we reach the proper state of our ETL (just after we enable them back). Our reporting services are the ones from sql server 2012 embedded in Sharepoint.

The problem here is that it seems that ReportServer.dbo.Schedule some times has NULL in the NextRunTime even though it does have a schedule set.  From some testing we saw that if we connect to the sharepoint portal that we have with the ssrs reports then the nextruntime gets populated succesfully but if for example on a sunday , noone connects to that portal , then the nextRunTime remains with NULLs..

Anyone has encourred that before and has any idea on how to overcome that issue ??

Thanks for any help,
Dimitris


texts: Does PostgreSQL work well as a workstation-based DBMS?

texts:
I've been considering PostgreSQL for an application I am writing in .NET WinForms, and the app is intended to be standalone with occasional sync-ing with a web service.  I plan to use Sql Server for the web server (since I already have it available), but would like to use a DBMS with a smaller footprint on the client WinForms app -- thus PostgreSQL.

So is a good option for my intentions?


texts: all of the databases recovery models changed

texts:
i logged into a client's server today(which had log shipping for all of its db's), and found that someone changed all the databases recovery models to simple. so now i need to reconfigure the log shipping.
my client says he didn't touch the server in the error log i can see it was changed.
can it be some kind of a bug? the server wasn't restarted im lost...
can the recovery model be changed by itself or by an application?
thanks


texts:
I have a lot of database, a ton of partitioned tables and many processes running on them.
In a couple of occasions I hade problems for unusable indexes or partitions of them. A bugged procedure, or a human error of the dba.

I'd like to rise an alarm when an index became unusable.

Oracle is version 11.2, new installations 12. All enterprise.
Grid control is 12c.

I also have nagios on every machine

The only way I found is to use a nagios plugin to run custom query on database. Is this the best way?

The plugin: http://labs.consol.de/lang/en/nagios/check_oracle_health/


texts: PostgreSQL CREATE INDEX memory requirement

texts:
I've got 6GB of RAM on my laptop and for a test I'm creating a 50mil-rows table in PostgreSQL 9.3.  I then want to create an index on the table.

The table and the resulting index together (or twice the table total size) can fit into 5GB of RAM and I set maintenance_work_mem to 5GB, still CREATE INDEX uses external sort with about 1.4GB of temp files.  Why is that so?

Is my expectation that it should be able to sort in RAM unreasonable?

test=# set maintenance_work_mem to '5GB';
SET
test=# create table t1 as (select i::int, random() as f from generate_series(1, 50000000) i);
SELECT 50000000
test=# select pg_size_pretty(pg_relation_size('t1'));
 pg_size_pretty
----------------
 2111 MB
(1 row)

test=# create index on t1(f, i);
CREATE INDEX
test=# select pg_size_pretty(pg_relation_size('t1_f_i_idx'));
 pg_size_pretty
----------------
 1504 MB
(1 row)

In the server log:

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp22623.1", size 1073741824
STATEMENT:  create index on t1(f, i);
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp22623.2", size 327622656
LOG:  external sort ended, 171065 disk blocks used: CPU 6.78s/268.73u sec elapsed 313.18 sec

Is there a way to calculate CREATE INDEX memory requirement before actually running it?


texts: Catch OLE DB provider "SQLNCLI10" for linked server "[Some Server]" returned message "Query timeout expired"

texts:
Without changing the settings on servers, etc to the linked server connections, etc, is there a way to catch the dreaded?

"OLE DB provider "SQLNCLI10" for linked server "[Some Server here]" returned message "Query timeout expired""
"OLE DB provider "SQLNCLI11" for linked server "[Some Server here]" returned message "Query timeout expired""

and either have the procedure retry to run or continue on to the next process in a cursor?
We have a dynamic SQL Server system set up to call procedures based on a specific process we're running.  This works fine but every once in a while, one of the steps will throw the above error and of course a traditional Try...Catch block doesn't capture it.
I've searched for several hours on this and I need a way to be able to "capture" this error and either retry the procedure or skip the procedure but not exit the cursor (so go to the next process in line).  We have a 2016 and a 2008 R2 servers communicating with each other.
Right now we have a single cursor running with 20 such steps in it (we are trying to use this as a job task replacement) but eventually we will have 30 or 40 processes, with tasks totaling well into the hundreds using this system.
Please let me know if you have any ideas.


texts:
I am trying to recompile/update a stored procedure in SQL Developer that uses a few division operations to populate tables.

A sample section looks like this:

  Insert Into Production(A, B, C)
  SELECT A, B, (SELECT SUM(Month_Production)
  FROM Production / SELECT COUNT(Month_Production) FROM Production2)  FROM DUAL;

The problem I run into is in the subquery, it thinks the division symbol is an "END OF FILE" marker, and then can't process the rest of the procedure (There are about 10 of these types of insert statements in this procedure). I have been researching online how to "escape" this character.

I tried using a \ to escape it out,

   Insert Into Production(A, B, C)
   SELECT A, B, (SELECT SUM(Month_Production)
   FROM Production \/ SELECT COUNT(Month_Production) FROM Production2)
   FROM DUAL;

but this is not working to help compile. Any idea on how I can compile this while keeping the division operations in here?

Thanks!

edit:I rewrote the queries as I think the focus was on my structure and not the problem. The queries I posted were just quick write ups to demonstrate what I am having issues with. The queries in question are very long and work fine, I pulled a "Backup" query from the procedure but even when I rerun it it feels the / is the end of the file and not a division symbol. I've tried to rewrite it with various ways to surround the code with () but still am having issues.


texts:
I am running a real old version of mysql(3.23.56). Everything is good, expect when I want to delete all entries of a table. I have checked query, like drop, select,update, their behaviours are correct. Even I use sql like delete from t1 where id='5', I can get the right answer. But for delete from t1 (which would delete all entries in table t1), it shows

  ERROR 2013 at line 1: Lost connection to mysql server during query.

And mysqld restarted. Even this table has only 1 entry!

I checked the error log. It shows:

Number of processes running now: 0
 Fri Jul 24 13:31:00 CDT 2015
150724 13:31:10  mysqld started
/home/chenyuxi/Desktop/bugs/mysql_169/mysql-build/libexec/mysqld: ready for connections
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail

key_buffer_size=8388600
record_buffer=131072
sort_buffer=2097144
max_used_connections=0
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

I try to change global variable max_allowed_packet and net_read_timeout, it seems useless.

Any suggestions? Really urgent for me .

Thanks


texts: Detect if transaction request for a certain database Query has come from a Virtual Machine?

texts:
The application is deployed on different systems but I have to restrict the application to get run on VMs. Provided that I need not to formulate the strategy in code to detect it. Is there any way I can figure out at Database level if the transaction request is from VM to restrict application use?

Hint: I was googling and came across Logon Triggers. But I'm not sure if I could map this in my situation to figure out VM request!

Updated Actually the situation is: We have clients not paying their dues and still using our application illegally. We have built up the mechanism to restrict them on code level. But that mechanism,unfortunately, has been just merged in the code. The client could easily delude us by running old copy of our application on VM and they are good to go. We could have restricted them implementing firewall rules but they have their own network team. We are just taking protective measures to minimize such breaches at maximum.


texts:
I did Generate Script on a database and got over 1700 scripts. I've sorted them into folders below:

Each folder contains .sql files of that type, for example 06 UserDefinedFunction contains many .UserDefinedFunction.sql files.
I'm going to make a batch script that goes to each folder and runs sqlcmd on every file in that folder.
Does the execution order matter here, and do I have the right order?


texts: Which driver should I choose for ODBC connect from Windows 10 => SQL Server 2014

texts:
What driver to use for ODBC connection from Windows 10 host to SQL Server 2014?
I have a choice of 3 SQL Server clients:

ODBC Client 11
SQL Server
SQL Server Native Client.

I've tried the ODBC client and the native client they both seem to work.

Is there any reason to prefer one over the others?


texts: Table orders regarding Nested loop join in DB2

texts:
First I would like to cite the existing post Table order in Join Query

so the author is saying that the table order really doesn't make things(result, access plan)different. I have doubt on this regarding the access plan.

so the documentation of DB2 (TABLE JOIN) tells in the NLJoin, outer table is fixed and scanned only once, while the inner tables might be scanned multiple times depend on different scenarios, under this circumstance, I think the order of tables in the join really matters, we need to put big table as outer table, as we only want to scan this once.

is this the case, is this only for DB2? did I miss something?

+is there general guidance when writing SQL, how should we deal with the table join orders ?


texts:
I have received a new server. The server has the following specification.
It has 500 gig 7.2K RPM disk,
 SSD
 NVME
1.2 TB  10K RPM disk.

All are in RAID 1.

I am planning to install Windows Sever on the 7.2K RPM disks. I want to install SQL Server on the OS drive( i.e. 7.2K RPM drive). I am planning to keep log and data files on SSD and NVME. The 10k RPM drive will be used for backups.
NVME drive is faster than SSD.
My Windows team recommend that I should install Windows Server on the SSD or 10K drives for performance. But all I care is about SQL Server performance. I don't think that Windows Server will perform badly on the 7.2k drive.

Does anyone see a reason to install Windows Server on any drive other than the 7.2K RPM drives?


texts: sp_MS_marksystemobject doesn't appear to be working

texts:
I have been creating a stored procedure to automate the addition of "pseudo-rows" to dimension tables (e.g. -1 = 'Unknown', -2 = 'None', etc.)

Since we have several database that contain datamart data, I opted to make this a system stored procedure that can be shared from multiple databases.  The procedure queries the local database system tables (sys.tables, sys.columns, sys.schema) to construct a dynamic SQL statement that will then be executed.

However, the stored procedure is not working as intended:  despite marking the procedure a system stored procedure, it is working only in the context of the Master database.

Here's a bare-bones implementation that illustrates the issue:

 USE Master
 GO

 CREATE PROCEDURE dbo.sp_WhereAmI
 AS SELECT DB_NAME()
 GO

 EXEC sp_MS_marksystemobject 'dbo.sp_WhereAmI'
 GO

 USE SomeOtherDatabase
 GO

 SELECT DB_NAME()
 EXEC MASTER.dbo.sp_WhereAmI

...and my output looks like

  -----------------
  SomeOtherDatabase
  (1 row affected)

 ------------------
 master
 (1 row affected)

This is precisely NOT as expected.    By marking the stored procedure as a system procedure, I expected the CALLING database context to be used, but that's not happening.

Any idea what's going on here?


texts: Table Partitioning. What is the correct process for deleting .ndf and .ldf files?

texts:
Please go easy it’s a steep learn curve..

OK steps done so far

Create files,
Create file groups,
Create partition function,
Create partition scheme,
Partition table associate files with filegroups
Put data in the table.

All is good

Now to reverse

The drop data

Drop the partition and associations,
Drop partition scheme,
Drop partition function,
Drop file groups

But when I deleted the files from the folder in my SSMS, I than had problems validating my Databases.

I restored the files and SSMS is fine.

My question what is the correct process for deleting .ndf  and .ldf files?


texts: SELECT Query runs fine but throws an error when used as source for REPLACE INTO

texts:
I have a query, that runs fine on its own:

SELECT * FROM tabA WHERE colA != '--';

Now I would like to use it as a source for a REPLACE INTO statement. The query looks like the following:

REPLACE INTO tabB
SELECT * FROM tabA WHERE colA != '--';

Even though the SELECT part is exactly the same, now the query returns the following error:

  Error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect DOUBLE value: '--'

The column in question (tabA.colA) is defined as BIGINT(20) NOT NULL which means, it cannot be --. The condition should therefore always evaluate tom true

What is wrong with the query?

I'm using MySQL 5.7.

SQL Fiddle: https://www.db-fiddle.com/f/hajQcShWrpfT1VbFr1s4nc/0


texts: Blocking SPID has Zero Disk IO and Zero CPU Time

texts:
Running sp_who2 and following the BlkBy trail up to the root cause of blocking returns a SPID which has CPUTime and DiskIO values of 0; yet it's blocking 4 other SPIDs.

I'm confused by how this is possible; the CPUTime in particular seems odd as in order to obtain a lock you'd have had to have spent some time requesting resources / requesting the lock itself.
CPUTime is in milliseconds, so whilst it's possible that the requesting and locking of resources occurs fast enough to have a rounded down value here, that is a little surprising.

Also, these SPIDS are sometimes a few minutes old; yet appear to have done nothing beyond cause blocking.

Question

How is it possible for a SPID to cause blocking whilst having zero CPU Time?

I'm asking as I suspect something's lacking in my understanding of the CPU Time stat.  If anyone can advise on sensible steps to aid in investigating such issues though that would also be useful.


texts:
I was wondering if there is any way to determine whether is convenient or not to enable Buffer Pool Extension at one of the servers I am administering.

I was thinking about some way to measure average memory usage, memory usage peaks and so on, in order to determine if available RAM is enough of not.

Thanks in advance.


texts: Inserting data from one table, then taking the primary key of that insert and put it into another table

texts:
I have a quick question. I have a table users

 -----------------------------
| ID | first_name | last_name |
 -----------------------------

and another table like users.login

 ------------------
| users_ID | EMAIL |
 ------------------

My id's in the first table are auto incrementing from a certain point, say 160,000. How do I take the id from the first table, save it to a value, and then put that value into the id of the 2nd table? The id in users.login is foreign key to users.id.

So far i have

INSERT INTO public.users (first_name, last_name)
                VALUEs ($1,$2) RETURNING ID;
INSERT INTO public.users_login(email, ID)
                Values($1, id)

does this work? I'm new to postgres.


texts: Enable Oracle Database options in an existing database

texts:
I want to enable Oracle Real Application Testing option in my existing database, but I couldn't find the way to do that using Oracle Universal Installer (OUI) or Database Configuration Assistant (DBCA). Do I have to reinstall the Oracle software in order to enable the options that I didn't choose to install during Oracle software installation?

The version of the Oracle Database installed is 11.2.0.3.


texts:
I want ascending wise data CID wise
    select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from(
select e.Category,e.Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
select ca.CName as Category,a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
from ItemMasterFile a
inner join Catagory ca on ca.CID=a.CID
left join Bigbalprd b on a.CodeItem=b.CodeItem
 where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b')
  group by a.Descriptionitem, ca.CName
  ORDER BY CA.CID ASC)e
left join Dispatch_BD c on e.CodeItem=c.CodeItem
 where c.Delidd is null
group by e.Name,e.Category

)f

I tried below but error is coming (  ORDER BY CA.CID ASC)
Msg 1033, Level 15, State 1, Line 9
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.


texts: SQL extended event captured only the estimated execution plan but no actual execution plan

texts:
My SQL instance version is Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288)
I am troubleshooting the application time-out and the time-out SELECT query is quite simple. It only takes less than 1 second when I manually run it...
I used extended events query_pre_execution_showplan & query_post_execution_showplan to capture the estimated/actual execution plan. It shows only the estimated execution plan but without the actual execution plan.
Looks like SQL instance just generated the estimated execution plan...
Any thoughts?
Many thanks,


texts:
We have an AWS RDS with a MySQL database. Buffer Pool size is only 144GB out of the total available 244GB RAM. However, even at such a low RAM config, we see Swap Usage happening. Does anyone have any idea on how I can go about figuring out what is causing the swap usage?
We have Performance Insights enabled, and so also Enhanced monitoring. The Swap Committed value did go back down, but we are essentially wasting about 100GB of RAM, as increasing Buffer Pool size any further aggressively increases Swap usage.


texts: Full Text Index problem with strings beginning: NN + number

texts:
A full-text index stores number words twice, once as they appear in the text and secondly prefixed with "nn", for example, the number 14 will get stored as "14" and "nn14".  I'm not totally sure of the reason for this?!

This is problematic when trying to return genuine "nn14" strings as results include all records containing just the number 14 as well as the "nn14" string.  The data set I have indexed is UK address data so searching for the string "NN14" should ideally return just address with the postcode NN14, but I also get thousands of records just with the number 14 in them.

Is there any way to avoid this behaviour?  Ideally I'd like to have some control over the word breaker that builds the index so it doesn't save the "nn" prefix version.


texts: Force NOW to return new timestamps without committing the transaction

texts:
I'm aware that, in PostgreSQL, the return value of NOW() is the transaction begin timestamp. So if you use NOW() multiple times in the same transaction it will always return the same value.
And that's good in my book.
But I have a small issue with unit-tests in a client application with this and would love to be able to tell PostgreSQL to (temporarily) disable this if at all possible.
The reason I don't want to (can't) use another timestamp function like clock_timestamp() is because the function call to NOW() sits inside of a trigger, and in production code I want the "transaction-start" behaviour.
But in my unit-tests I'm patching the API-level "commit" function so I don't accidentally commit real data to the database during testing (don't worry, I don't use the production DB during testing). So during unit-tests the commit never hits the DB, so I don't get new transaction timestamps.
The database uses temporal tables, and new entries are only appended to the history tables if the timestamp changes to ensure we only consolidate one entry in the history table per transaction.
But when testing the temporal-table behaviour this now causes no entry to ever show up in the history tables. The key fragment of the temporal-table trigger is this:
new_validity_period = tstzrange(
    lower(OLD.validity_period),
    NOW(),
    '[)'
);
IF isempty(new_validity_period) THEN
    RAISE DEBUG 'New entry % will not introduce a new history item', OLD;
    RETURN OLD;
END IF;

So when I do an "insert" operation in my unit-test, and the transaction-time is '2020-01-01 01:02:03', then the validity-period for that entry will be [2020-01-01 01:02:02,). If, still in the same unit-test, I delete the entry (and to test whether it appears in the history table), the operation happens in the same TX, and the code above will read like this:
new_validity_perion = tstzrange(
    '2020-01-01 01:02:03', -- the lower-bound of the 'OLD' row
    '2020-01-01 01:02:03', -- the result of 'NOW()'
    '[)'
)
-- resulting in an empty range because the two timestamps are identical
IF isempty(new_validity_periond) THEN  -- <- Resulting to TRUE
    ...
    RETURN OLD;  -- returning here, not continuing to store the history entry
END IF;
-- code below here is skipped

Is there a way to configure PG to always return the wall-time when calling NOW()? The tests are run against a docker-container so I have full control over the server-config. But it would be even better if I could change the behaviour by setting the config via an SQL command, so I could only change the behaviour for the temporal-table tests.
If that is not possible I would need to use a different database-session/transaction setup for those tests. This is also fine, but I was wondering if I could control this PG behaviour.
Appendix: Unit-Test transaction isolation
This is the code I use to isolate the commit calls in user-code:
@fixture
def rb_session():
    """
    Returns a session which will always be rolled back.
    """

    engine = create_engine(Configurations.getenv("IPBASE_DATABASE_DSN"))

    # Get a *specific* connection from the pool
    connection = engine.connect()
    # Explicitly start a new connection on the connection we got
    # This makes the normal "session.begin()" and "session.commit()"
    # calls in SQLAlchemy no-ops as there is already a transaction
    # in progress.
    transaction = connection.begin()
    # Ensure we use the "primed" connection for all our SQLAlchemy
    # session needs in our unit-tests.
    session = Session(bind=connection)
    try:
        yield session
    finally:
        transaction.rollback()
        session.close()
        connection.close()