texts: Postgres query slow, shows long running active queries, statement_timeout does not work

texts:
I have a Postgres database that's accessed through a node.js api using a mobile react native app. Don't thing the code stack specifics matter, but essentially there are queries in the psql db that stick around as status = 'active' for days that are slowing the whole database way down. Using the following to check:

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Manually killing the active queries with

SELECT pg_cancel_backend(__pid__)

fixes the issue and gets the database back to normal speed. But, of course we want to make sure this doesn't necessarily happen intermittently and slows down our app for all users.

Another thing to mention is that I've attempted to put a time limit on the active queries with statement_timeout but I'm not sure I'm doing this correctly. Testing it with...

SET statement_timeout = 1;
SELECT * from "table_name";

does not work. That is... the SELECT statement above takes 400 milliseconds and doesn't terminate. Seems that I don't have this procedure right. The statement does work on

SET statement_timeout = 10000;
SELECT pg_sleep(15);

with it canceling the second statement. Does that mean that statement_timeout only works on idle processes and not execution time?

The specifics of the query (including just anything out of the ordinary, everything else is normal 'where' statements)

It's a select statement joining three tables together: two of them are normal, one is a zipcodes table with 40k+ rows indexed on the zipcode column.
There is a CASE statement in the SELECT piece (before where), like such

(CASE
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
                                        (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 5
                        THEN 0
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
                                        (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 10
                        THEN 1
                        WHEN
                            (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
                                        (SELECT lng from zipcodes where zipcode = '<zipcode>'))) / 1609.34) <= 20
                        THEN 2
                        ELSE (SELECT earth_distance(
                            ll_to_earth(z.lat, z.lng),
                            ll_to_earth((SELECT lat from zipcodes where zipcode = '<zipcode>'),
                                        (SELECT lng from zipcodes where zipcode = '<zipcode>'))))
                    END   )
                    AS distance

Yes, this definitely seems like it would take some time, however it does not for most users. And still, why would this query show as 'active' for days. For example, if it couldn't find the zipcode in the zipcodes table it would either not return anything or error out.

Any and all help is greatly appreciated! Even if you see anything out of place please feel free to comment! Forgive me for any simple things I missed :-)!


texts: How to rollback a SQL Server transaction and use output param?

texts:
I am trying to set an output param in a stored procedure, but I think that when I rollback the transaction I think I'm also rolling back the assignment to @out. Is that correct?

If so, how can I return a message and rollback a transaction? I am fetching the @out param from C#.

create PROCEDURE [dbo].[sp]
@out varchar(2000) output
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRANSACTION
    BEGIN TRY
        SET @OUT = "success";
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        set @out = 'not success';
        ROLLBACK TRANSACTION
    END CATCH
END

I was originally doing a

SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;

...but that didn't help, though I would prefer that method.


texts: What makes up "Queries per second avg" in MySQL

texts:
When running the status command in MySQL, one of the metrics that is sent back is "Queries per second avg: 5910.758"

I was wondering if anybody know what makes up that number?

Reason for asking is we tracked all the selects, updates, inserts from the performance_schema and we don't get anywhere near that number.


texts:
I just installed PostgreSQL 9.4 on Ubuntu 15.10.

I created a user with createuser -P myuser
I created a database with createdb -O myuser mydatabase
I edited pg_hba.conf and added local mydatabase myuser md5
I restarted PostgreSQL with sudo service postgresql restart

User myuser is a PostgresSQL user only and has no user account on Ubuntu.

When I try to connect to the database with psql -W mydatabase myuser it fails with psql: FATAL:  Peer authentication failed for user "myuser".

PostgreSQL is running …

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Thu 2016-03-03 09:53:00 CET; 9min ago
  Process: 22219 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 22219 (code=exited, status=0/SUCCESS)

Mar 03 09:53:00 SERVER01 systemd[1]: Starting PostgreSQL RDBMS...
Mar 03 09:53:00 SERVER01 systemd[1]: Started PostgreSQL RDBMS.

... and listening.

Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 localhost:postgresql    *:*                     LISTEN
tcp6       0      0 localhost:postgresql    [::]:*                  LISTEN
Active UNIX domain sockets (only servers)
Proto RefCnt Flags       Type       State         I-Node   Path
unix  2      [ ACC ]     STREAM     LISTENING     151534   /var/run/postgresql/.s.PGSQL.5432

What do I have to do to connect with user myuser to database mydatabase?


texts: I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.


texts: Custom personal monitor sql by writing the .psqlrc file

texts:
I want to custom my personaly monitor sql  by writing the  .psqlrc file ,  but I encounter some problems .

--tablespace

skypcsuit=> \db
                          List of tablespaces
       Name        |  Owner   |                Location
-------------------+----------+-----------------------------------------
 pg_default        | postgres |
 pg_global         | postgres |
 tbs_db_francs     | postgres | /database/pg92/pg_tbs/tbs_db_francs
 tbs_skypcsuit     | postgres | /database/pg92/pg_tbs/tbs_skypcsuit
 tbs_skypcsuit_idx | postgres | /database/pg92/pg_tbs/tbs_skypcsuit_idx

--.psqlrc

\set top10_ts_table 'select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb  where a.relkind in (\'r\', \'i\') and a.reltablespace=tb.oid and tb.spcname=\':v_spcname\' order by a.relpages desc limit 10;'

--call the sql but return null value

skypcsuit=> \set v_spcname tbs_skypcsuit_idx

skypcsuit=> \echo :v_spcname
tbs_skypcsuit_idx

skypcsuit=> :top10_ts_table
 relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
(0 rows)

I set the the value of variable v_spcname to 'tbs_skypcsuit_idx'。

-- csvlog

2014-07-24 01:10:00.091 PDT,"skypcsuit","skypcsuit",2123,"[local]",53d0bf4c.84b,3,"idle",2014-07-24 01:09:48 PDT,2/94,0,LOG,00000,"statement: select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb  where a.relkind in ('r', 'i') and a.reltablespace=tb.oid and tb.spcname=':v_spcname' order by a.relpages desc limit 10;",,,,,,,,,"psql"

From the log we can see that the values of variable v_spcname did not pass to the sql.   any one know that ?


texts:
Environment:

 Centos 6.5
 PostgreSQL 9.3
 shared_buffers = 3000 MB
 checkpoint_segments = 128

Top command: top -M (shift F -> N)

The checkpointer process uses 1.3 GB RAM

Question:
Why does checkpointer process use a lot of RAM ?

Referenced:
this article, postgresql doc, interdb.jb 9.7, this post


texts: Update query performance improvement (MS-SQL)

texts:
UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE
EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1
ON M.MARKID = S.MARKID AND
S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> 'Y')

The above query is currently updating around 414163 rows in 44 seconds, And I want to improve its performance by maybe doing it parallel or updating in batch?
I have tried with the below query-
UPDATE M SET ARCHIVE_FLAG = 'N' FROM MARKPOSTED_TMP M WHERE
EXISTS (SELECT 1 FROM SHFMKPSTMM S INNER JOIN SHIFT_TMP S1
ON M.MARKID = S.MARKID AND
S.SHIFTID = S1.SHIFTID AND S1.ARCHIVE_FLAG <> 'Y') OPTION (MAXDOP 8);

But only with 4-6 seconds better result
Execution plan suggests index scan non-clustered on SHFMKPSTMM  table is taking 49% cost. But I can't remove the index from that table.
Any help in the better query formulation to improve the performance?


texts: UPDATE to linked server (Pervasive Database) is failing using OPENQUERY

texts:
I have 2 database servers:

ServerA : Windows Server 2012 R2 Standard, SQL Server 2014 (SP3) (KB4022619) Standard Edition.

ServerB : Windows Server 2016 Datacenter, SQL Server 2016 (SP2-CU6) (KB4488536) Enterprise Edition

Both are x64 servers located at different DC, under different domain.

Purpose:

We are planning to migrate production databases from ServerA to ServerB.

Action Taken:
Prior to migrating production database, I wanted to validate it on development environment first.So,

All of the databases, linked server (with similar settings), logins etc. have been copied properly from ServerA to ServerB.

Installed ODBC driver of Pervasive database on ServerB (similar to ServerA).

Exported all the system DSNs from ServerA to ServerB.

Updated MSDTC settings on ServerB (similer to ServerA).

Note: linked server is referencing to pervasive database.

Problem Facing;

After all these users are able to login, databases are accessible, reports are running and the server (ServerB) is performing well, users are able to SELECT tables from Pervasive database using linked server.

But the problem is, "users are not able to execute update on pervasive database using the linked server on ServerB.

The update queries 1st & 2nd are failing with same error:

--1------------------------------------------------------------
UPDATE OPENQUERY (LinkedServerName, 'SELECT Column_1,Column_2,Column_3,Column_4 FROM TableName WHERE Column_3 = 5910 AND Column_4 = 2')
SET Column_1='2019-02-01',Column_2='2019-07-01';
--2------------------------------------------------------------
UPDATE OQ
SET Column_1='2019-02-01',Column_2='2019-07-01'
FROM OPENQUERY (LinkedServerName, 'SELECT * FROM TableName') OQ
WHERE Column_3 = 5910 AND Column_4 = 2;

Error1:

Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7343, Level 16, State 4, Line 4
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" could not UPDATE table "[MSDASQL]". Unknown provider error.

Error for 3rd query is quit different.

UPDATE LinkedServerName.DBName..TableName
SET Column_1='2019-02-01',Column_2='2019-07-01'
WHERE Column_3 = 5910 AND Column_4 = 2;

Error 2:

Msg 7399, Level 16, State 1, Line 26
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7343, Level 16, State 4, Line 26
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" could not UPDATE table "[LinkedServerName].[Database]..[TableName]". Unknown provider error.

Same query is executing successfully on ServerA.

Solution will be appreciated.

Thanks!


texts: Where is SQLCMD.EXE in SQL Server 2014 LocalDB or how to install it?

texts:
On a machine with SQL Server LocalDB 2014 installed I am looking for SQLCMD.EXE file but cannot find it anywhere. Is there a way how to install it separately?

Unfortunately I am not local admin of the machine, so I prefer no MSI...

Found this link https://www.microsoft.com/en-us/download/confirmation.aspx?id=36433, but cannot use MSI...  Perhaps copying executable files and dlls can do the trick.

After copying files SQLCMD.EXE, BATCHPARSER.DLL and SQLCMD.RLL from C:\Program Files\Microsoft SQL Server\110\Tools\Binn  from another computer with SSMS installed I still get the error:

  Microsoft SQL Server Native Client 11.0 : Data source name not found
  and no default driver specified.

running sqlcmd -S np:\\.\pipe\LOCALDB#F8040C8F\tsql\query.


texts:
The scenario is rather simple: A Job fails in production, giving off an error "ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' ...".

The question is, why does this problem appear in production, and not in any of our testing environments? Checking DBCC USEROPTIONS, the results are exactly the same across all databases. Also, the script itself contains no quotes at all. It simply rebuilds a few indexes and updates a few statistics with fullscan.

Using the exact same job on any other database, I can set QUOTED_IDENTIFIER ON, or OFF, or just not set it at all and let it run on connection defaults. And it'll always work regardless. But in production, it appears it either has to be set ON or OFF.

I'm asking this because time-wise I can't afford to test this once a day, I need to know why this error is happening and how to predict which setting is needed and when. The only visible difference between the test and production databases is collation, but even creating a testing database to match the collation in production, the Job still works regardless of the QUOTED_IDENTIFIER setting.

Thanks!


texts: How to show Large Objects (LOB) contents?

texts:
How could I get to have a look into LOB contents?

\d ticketgrantingticket

              Table "public.ticketgrantingticket"
          Column           |          Type          | Modifiers
---------------------------+------------------------+-----------
id                         | character varying(765) | not null
number_of_times_used       | numeric(10,0)          |
creation_time              | numeric(19,0)          |
last_time_used             | numeric(19,0)          |
previous_last_time_used    | numeric(19,0)          |
ticketgrantingticket_id    | character varying(765) |
expiration_policy          | oid                    |
authentication             | oid                    |
services_granted_access_to | oid                    |
expired                    | boolean                |
proxied_by                 | character varying(1)   |
Indexes:
    "ticketgrantingticket_pkey" PRIMARY KEY, btree (id)

SELECT expiration_policy,
       authentication,
       services_granted_access_to
FROM ticketgrantingticket LIMIT 2;

 expiration_policy | authentication | services_granted_access_to
-------------------+----------------+----------------------------
           1165742 |        1165743 |                    1165744
           1165757 |        1165758 |                    1165759
(2 rows)

This shows those numbers by default.

I so far found this which I think is not working properly in my client somehow:

SELECT expiration_policy,
       encode(authentication::bytea, 'escape'),
       services_granted_access_to
FROM ticketgrantingticket LIMIT 2;

ERROR:  cannot cast type oid to bytea
LINE 1: SELECT expiration_policy, encode(authentication::bytea, 'esc...

I can see the same in pgAdmin III.

I am expecting blobs there because IINM oid can be used as a large object reference. We are using oid because not knowing what we were doing. I have so far assessed that one of those three columns will be safely and profitably converted to bytea, but am being unable to spy the other two columns' contents in the source (non postgres) system, so I decide to spy them in this (postgres) target system.


texts: Index fragmentation increase as more row being deleted

texts:
I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday.  Index fragmentation was low.

This table has high transaction throughput. Every second it stores about 3 to 5 new records, so we know that clearing old records for this table needs to be fast.

The delete statement is like this:

Delete top 1000
From MyTable
Where CreationDate < 'Some Date'

Ideally, we keep running it until no more rows can be deleted.

For the first 6 million records the delete process goes well, but as time passes the delete starts slowing down until it impacts other application that access the same table. In addition, many foreign key indexes become fragmented.

My questions are:

Does deleting a lot of rows cause fragmentation on the foreign key indexes? (that is, indexes the dependent tables)
Does the deletion become slow because of the fragmented foreign key indexes? (slower reference data lookup)
Is there a balanced strategy that can keep the performance of both the deletes and the foreign key indexes high?

I'm on SQL Server 2005 Standard edition.

[Update] I have included more information here
The actual table name is called "VehicleLocation"
Key columns:

VehicleLocationKey (PK, char(36), not null)
AgencyVehicleKey (FK, char(36), not null)
AssignmentKey (FK, char(36, null)
EmployeeKey (FK, char(36), null)

Indexes

VehicleLocation_AssignmentKey (Non-Unique, Non-Clustered)
VehicleLocation_CreationDate (Non-Unique, Non-Clustered)
VehicleLocation_MessageGenerationDate (Non-Unique, Clustered)
VehicleLocation_pk (Unique, Non-Clustered)

Object Dependencies for VehicleLocation (~ 10.5 mil rows)

VehicleLocationAPC (~76000 rows)
VehicleLocationFare (0 rows)
VehicleLocationGF ( 0 rows)
VehicleLocationInpt (0 rows)
VehicleLocationOBD (~ 15000 rows)
VehicleLocationTP (~8.3 million rows)

All the above tables has indexes on their primary key and on VehicleLocationKey (FK) table.

In addition, we use GUID as primary key (bad idea, but it is legacy). On top of that I see that VehicleLocationTP index has a fragmentation of 96%, which is very high.


texts: SQL Server 2014 SQL Job CmdExec and access denied

texts:
I have a little trouble with SQL Agent Job.

It should execute program in network directory like \\x\y\z.exe.

Job Step uses proxy account with credentials of a user, which can full access that directory.

Unfortunately job still can't access network directory with error:

  Access is denied

If I set permission on directory to Everyone, there is no problem. Do I miss something?

I am using a UNC path. xp_cmdshell is enabled. Proxy account is fine. I am using it also to run SSIS packages, which doesn't show any problems.
CmdExec subsystem is set to proxy Account
Sharing and UAC permission on directory is set properly.

Is it because SQL Agent job is using not supported authentication type to access directory?


texts: setting timing on not getting time in log

texts:
I have made the sql query like:

\timing on
\echo
truncate table raw.decrement_m7_temp;

copy raw.decrement_m7_temp FROM PROGRAM 'zcat /path/filename' with(format csv,delimiter ',',header);

delete from raw.decrement_m7_temp where decrement_dt ilike '%dt%';
delete from raw.decrement_m7_temp where msisdn !~ E'^[0-9]+$' OR char_length(msisdn)>12;

I want to track the how much these query is taking time in log.
I am running the following code:

psql -U postgres -d databasename -a -f /path/fimename -L /path/abc.log

While running this query its display the time on black Unix screen but not track the time in log file. How it is possible to track the time in log file?


texts: restoring db getting "no files are ready to rollforward" error

texts:
When I try to restore a DB file I am getting following error:

 The log or differential backup cannot be restored because no files are ready to rollforward.

My Back up SQL

BACKUP LOG [ICT_IdealComputer]
TO DISK = 'C:\ICT\ICTBackup.bak'
WITH NAME = 'ICT-Transaction Log Backup',
SKIP,
NOUNLOAD,
STATS = 10

BACKUP DATABASE [ICT_IdealComputer]
TO DISK = 'C:\ICT\ICTBackup.bak'
WITH NAME = 'ICT-Full Database Backup',
SKIP,
NOUNLOAD,
STATS = 10

Restore statement:

USE [master]
GO

RESTORE DATABASE ICT_IdealComputer
FROM DISK = 'C:\ICT\ICTBackup.bak'
WITH REPLACE, NORECOVERY,
MOVE 'ICT_IdealComputer' TO   'C:\ICT\ICT_Ideal\App_Data\ICT_IdealComputer.mdf',
MOVE 'ICT_IdealComputer_log' TO 'C:\ICT\ICT_Ideal\App_Data\ICT_IdealComputer_log.ldf';

I did tried with different option, googled around but no luck. Any idea what is going wrong here?

Appreciate your help


texts: Scaling out MySQL & Redundancy-Speed tradeoff?

texts:
I'm building an e-commerce service for a group of sellers. They have a common HQ who manufactures their product.
Tables:

order (id, seller_id, timestamp)
order_products (order_id, product_id, seller_id, timestamp, pincode)
transaction (id, seller_id, timestamp)
transaction_products (transaction_id, product_id, seller_id, timestamp, pincode)
seller (id, pincode, name)
product(id, price)

Specifications:

There are 100 sellers
Each vendor performs 500 transactions per day
Each transaction has 4 products associated with it
Each Vendor places two orders per day to HQ
Each order have 50 products

HQ Requirements:

How many products were sold by which seller in a given month
How many products were sold in a given pincode in a given month
Orders placed by all sellers in a given month

Seller Requirements:

View cost of order placed by him/her (the seller)
View his/her sales of a given month

The product is ready and application works just fine. But, I'm concerned with the two things.

Scaling: Being really new, I don't know much about scaling out or sharding or clustering. How much time have I got until I can keep these aside?
Redundancy: As you can see in transaction_product & order_product, I've reused columns from transaction & order, respectively. The redundant columns are: timestamp, seller_id, pincode. My idea was to avoid joins. But I'm not sure if joins would be more expensive than current redundancy. Can anyone point me in the current direction?


texts: How to use a MySQL error handler in a stored procedure with prepare/execute

texts:
I have a procedure defined like this:

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE CONTINUE HANDLER FOR 1064
select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
    table_schema=db and table_name like pattern and
    create_time <= @owa;

prepare stmt from @str_sql;
execute stmt;
drop prepare stmt;
end$$

The idea is to drop tables matching a certain pattern that have a create_time <= a specified date.

When executed, I get an error:

mysql> call drop_tables_like(7, 'monkey%', 'daweb2012_civicrm');
ERROR 1243 (HY000): Unknown prepared statement handler (stmt) given to     EXECUTE
mysql>

How can I declare a handler for an exception in a procedure that is using a prepared statement?


texts: What is using up space in database file?

texts:
I am attempting to empty a database file

Data has been shifted from a database file by using the emptyfile shrink command and then rebuilding/reorganising all heaps and indexes so that the data is pushed to the other files in the filegroup.

The amount of lob data in the file group is approx 1GB, acccording to dm_db_partition_stats and yet according to the SpaceUsed file property, we are still using 40GB of the file that I am trying to empty.

What could be using up all this data?


texts: tempdb contention issue and excessive pagewrites/s

texts:
Still removing the rust from my SQL Server skills, so please bear with some stupid questions here and there...

Customer has a SQL Server 2008 R2 running in an Azure VM with 8 cores and 14GB RAM.  Disks are organized as follows:

Disk E (2-stripe, 1TB partition): DBF and 1GB tempdb;
Disk F (2-stripe, 1TB partition): Logs and 1GB tempdb;
Disk G (4-stripe; small 20GB partition): 2 1GB Tempdb files;
Disk H (Single stripe, 500GB partition): Backup files;

Customer has introduced a couple of new processes which have resulted in an excessive PageWrites/s (daily avg 370(!), max 1000), which, on occasions, brings server performance to a crawl.  Pagereads and Lazywrites are well within "best practices".

The introduced processes are 50% SELECTs, 30% UPDATEs, 20% INSERTs in nature.

The server started off with just 01 (I know...) Tempdb and I have been increasing its number of files, which improves performance, but it still doesn't bring PageWrites/s count down to the recommended 90-100.

What are the next steps?

Increase number of tempdb files up until number of cores? In which case, where should I place them?
I am now profiling the load to look for index creation opportunities.  Any tips on what to pay attention?
What else?

Thanks in advance,


texts: Avoid transaction when reading data through a linked server

texts:
This is SQL Server related.

Is there other techniques that I can use for this? (Read data from a procedure in another server.)

I'm trying to re-use a stored procedure from another sever instance through a linked server. The stored procedure selects a few columns and rows (single, small resultset). Let's say this procedure resides on "Sever A".

I'm able to run the procedure from "Server B" and get the data, however, I also need to join the retrieved data against local tables.

I tried

insert into @LocalTableVariable exec [LinkedServer].[remotebase].[dbo].[procedure]

but that generates the following error:

  The operation could not be performed because OLE DB provider
  "SQLNCLI11" for linked server "SERVER_A" was unable to begin a
  distributed transaction.

The linked served has RPC enabled and promote transactions enabled.

Also tried with table valued functions, but in this form:

select * from [SERVER_A].Database.dbo.table_function(parameter_1)

Produced the error:

  Remote table-valued function calls are not allowed.

At the end I was able to perform the insert by using Dynamic SQL combined with openquery, but I really want to avoid Dynamic SQL to diminish SQL injection and the maintenance difficulties related to double and quadruple quoting. quad-quotes ara almos always necessary as parameters must be passed to the remote procedure or function. For Openquery does not accept variables, in order to make use of it, a second "layer" is added with exec sp_executesql.

Here is an example of what I do to read data from table functions.

set @sentence = concat('select * from Openquery([SERVER_A], ''Select Col_1, Col_2, ... Col_n Database.dbo.table_function(''''',@parameter_1,''''', ''''',@parameter_2,''''', ''''',@parameter_3,''''')'')')

insert into @tableVariable ... exec sp_executesql @sentence

Here is another way of doing it, this time shown with a procedure call:

set @sentence = concat('exec Database.dbo.stored_procedure ''',@parameter_1,''', ''',@parameter_2,'''')

set @sentence = concat('select * from Openquery([SERVER_A], ''',replace(@sentence , '''', ''''''),''')') -- With this at least I reduce the risk of miscounting quad quotes.

insert into @table_variable (...) exec sp_executesql @sentence

Edit: Also tried the pattern proposed by Aaron:

declare @param_1 varchar(10) = '...', @param_2 varchar(1) = '.'
        declare @sentence nvarchar(4000) = concat('exec Database.dbo.stored_procedure''',@param_1,''', ''',@param_2,''';')

        insert into @table_variable (...) exec [SERVER_A].Database.sys.sp_executesql @sentence;

That resulted in the same error I mentioned before.

For the tasks related to the same project I also tried table valued functions, but those also failed with an error, and where only usable (remotely) by means of Dynamic SQL.

I cannot use a view in "Server A" because there is additional logic needed prior to selecting the final result set. This logic is what I want to encapsulate as it is needed in both servers


texts:
I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects execution plan. Here is the script that I used to generate random test data:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('t') AND type in (N'U'))
DROP TABLE t
GO

CREATE TABLE t
(
 c1 int IDENTITY(1,1) NOT NULL
,c2 int NULL
)
GO

insert into t
select top 1000000 a from
(select t1.number*2048 + t2.number a, newid() b
from [master]..spt_values t1
cross join  [master]..spt_values t2
where t1.[type] = 'P' and t2.[type] = 'P') a
order by b
GO

update t set c2 = null
where c2 < 2048 * 2048 / 10
GO

CREATE CLUSTERED INDEX pk ON [t] (c1)
GO

CREATE NONCLUSTERED INDEX i ON t (c2)
GO

Now, given this data, I invoked the following query:

select *
from t
where
      c2 < 1048576
   or c2 is null
;

To my great surprise, the execution plan that was generated for this query, was this. (Sorry for the external link, it's too large to fit here).

Can someone explain to me what's up with all these "Constant Scans" and "Compute Scalars"? What's happening?

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
       |--Merge Interval
       |    |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
       |         |--Compute Scalar(DEFINE:([Expr1013]=((4)&[Expr1012]) = (4) AND NULL = [Expr1010], [Expr1014]=(4)&[Expr1012], [Expr1015]=(16)&[Expr1012]))
       |              |--Concatenation
       |                   |--Compute Scalar(DEFINE:([Expr1005]=NULL, [Expr1006]=NULL, [Expr1004]=(60)))
       |                   |    |--Constant Scan
       |                   |--Compute Scalar(DEFINE:([Expr1008]=NULL, [Expr1009]=(1048576), [Expr1007]=(10)))
       |                        |--Constant Scan
       |--Index Seek(OBJECT:([t].[i]), SEEK:([t].[c2] > [Expr1010] AND [t].[c2] < [Expr1011]) ORDERED FORWARD)


texts:
I have used this article to detect the latest install .net version and here is the result:

Its .net 4.0 and I want to know if the SQL Server is using the latest installed version?

I want to create CLR function using .net 4.5 and the SQL Server must use this version either. If I install 4.5 version, will the server automatically start using it?


texts: Mongo Zone Sharding Implementation Issue

texts:
As I am new with MongoDB.

I have configured 2 config server, 2 shards and all are connected with mongod client. Also, One Mongos client is UP and Running.

I am currenctly implementing Sharding using Mongo Zone. As I have to store the data accourding to country/Zone. But facing below error in shards:

2018-05-03T16:22:40.200+0530 W SHARDING [conn13] Chunk move failed :: caused by ::
      NoShardingEnabled: Cannot accept sharding commands if not started with --shardsvr
2018-05-03T16:27:11.223+0530 I SHARDING [Balancer] Balancer move testdb.user:
      [{ zipcode: "380001" }, { zipcode: "39000" }), from rs1, to rs0 failed :: caused by ::
      NoShardingEnabled: Cannot accept sharding commands if not started with --shardsvr

I have already started server with --shardsvr and also mentioned in config file as well. But still I am facing issue.

Below are the all config files:

mongosconfig.cfg

systemLog:
  destination: file
  path: "c:\\data\\log\\mongos.log"
  logAppend: true
sharding:
  configDB: "rs0/192.168.1.113:27017,192.168.1.112:27017"
net:
  bindIp: 192.168.1.113
  port: 27018

mongoclientconfig.cfg - configserver1

systemLog:
  destination: file
  path: "c:\\data\\log\\mongoclient.log"
  logAppend: true
storage:
  dbPath: "c:\\data\\db\\configdb"
  journal:
    enabled: true
sharding:
  clusterRole: "configsvr"
net:
  bindIp: 192.168.1.113
  port: 27017
replication:
  replSetName: "rs0"

mongodconfig.cfg - shard1

systemLog:
  destination: file
  path: "c:\\data\\log\\mongod.log"
  logAppend: true
storage:
  dbPath: "c:\\data\\db\\clientdb"
  journal:
    enabled: true
sharding:
  clusterRole: "shardsvr"
net:
  bindIp: 192.168.1.113
  port: 27019
replication:
  replSetName: "rs0"

mongoclientconfig.cfg - configserver2

systemLog:
  destination: file
  path: "D:\\data\\log\\mongoclient.log"
  logAppend: true
storage:
  dbPath: "D:\\data\\db\\configdb"
  journal:
    enabled: true
sharding:
  clusterRole: "configsvr"
replication:
  replSetName: "rs1"
net:
  bindIp: 192.168.1.112
  port: 27017

mongodconfig.cfg - Shard2

systemLog:
  destination: file
  path: "D:\\data\\log\\mongod.log"
  logAppend: true
storage:
  dbPath: "D:\\data\\db\\clientdb"
  journal:
    enabled: true
sharding:
  clusterRole: "shardsvr"
net:
  bindIp: 192.168.1.112
  port: 27019
replication:
  replSetName: "rs1"

Below are the commands which i have used for zone wise sharding as per the mongodb documentation(official):

rs.initiate({_id: "rs0",members: [{ _id : 0, host : "192.168.1.113:27019" }]}) //for shard1
rs.initiate({_id: "rs1",members: [{ _id : 1, host : "192.168.1.112:27019" }]}) //for shard2

sh.addShard("rs0/192.168.1.113:27019"); //Adding shard1 from mongos client
sh.addShard("rs1/192.168.1.112:27019"); //Adding shard2 from mongos client
sh.addShardToZone("rs0","IND");
sh.addShardToZone("rs1","UK");
sh.addTagRange("testdb.user", {zipcode: "380001"}, {zipcode: "390000"},
"IND");
sh.addTagRange("testdb.user", {zipcode: "400000"}, {zipcode: "600000"},
"UK");
sh.shardCollection("testdb.user", { zipcode: 1 } );

Please help me get this resolve.

Thanks in Advance.


texts: Rolling Back DB Changes After Deployment Edge Cases

texts:
I manage a mixed DB environment and our programmers could use some good rollback options.  I was thinking of using SQL Compare, Red Gate Source Control, and a few other tools but I can't figure out what the best way would be to ensure rollbacks with minimal data loss.

Assuming we're in edge cases such as when a foreign key is added to another table which gets populated with customer data, and we need to roll back changes to the primary table.  What are some of the industry standard best practices to ensure smooth rollbacks outside of transaction log restores, snapshot restores, redgate source control, etc. to deal with these harder to recover from scenarios?

Thanks!


texts: How to join two columns and find unique combinations

texts:
I have the following topological structure (topo) with their types:

id(string) |   id_from(string) | id_to(string) | type(string)
 '111'     |    'aaa'          |   'bbb'       |   'type1'
 '222'     |    'bbb'          |   'ccc'       |   'type2'
 '333'     |    'ddd'          |   'bbb'       |   'type3'

Now I want to find all distinct connected type-combinations

id_from = id_to OR
id_to = id_to OR
id_from = id_from

The result should look like this:

type_from(string) | type_to(string)
    'type1'       |      'type2'
    'type2'       |      'type3'
    'type1'       |      'type3'

My first approach is to create an index on id , id_from , id_to. The SQL query i tried is:

WITH p AS
(
   SELECT DISTINCT ON ( id ) id , from , to , type FROM topo
)
SELECT DISTINCT ON( p.type , t.type )
p.type AS type_a , t.type AS type_b FROM p
JOIN topo t ON
t.id_from = p.id_to OR
t.id_to = p.id_to OR
t.id_from = p.id_from

It works but it takes several minutes without an result. Any way to achieve this (faster)?

EDIT: I'm not even sure if this query can be done much faster for several million rows. My current best solution is to compute this query once and to store it as an combination table. Whenever I need this values, I just query the combination table.


texts: Sql Server Performance Degradation in moving from 2005 to 2008

texts: I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. Sql server memory allocation is also same in both servers. But my benchmarking results are worse on 2008 than 2005. Will somebody help me to find the reasons for performance degradation in sql server 2008. What are the thing I need to take care, or parameters to set for getting the better or same result sets.


texts: Prevent postgres function of running more than 1 instance at the same time

texts:
DBMS: Postgres 9.3.4
OS: Debian 7

I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 minutes, it CAN'T run until the last execution has finished.

I could think in more than 1 workaround, but I was wondering if there is some best known solution for this. Maybe something native on plpgsql syntax.


texts: Are the contents of all control files the same?

texts: I understand that one needs to multiplex and back up the control file. Also one can have multiple control files in the database instance parameter setting. If one of these files get corrupted or is missing, the database will not be able to mount. My question is; the contents of all the control files, are they the same since one can simple do a copy of a good one, renaming it to that of a lost or corrupted one, and the database will startup?


texts: Finding fuctional dependencies and candidate keys

texts:
I have number of files, each containing all reviews for a specific hotel (one file corresponds to one hotel, e.g. hotel_2194.dat). Each review is of the type:

<Author>PERKEISHA
<Content> I had a great time at...
<Date>Mar 8, 2008
<No. Reader>-1
<No. Helpful>-1
<Overall>1
<Value>1
<Rooms>1
<Location>5
<Cleanliness>5
<Check in / front desk>5
<Service>5
<Business service>1

I constructed the following relation:

Review (Hotel_ID, Author, Content, Date, No. Readers, No. Helpful, Overall, Value, Rooms, Location, Cleanliness, Check in, Service, Business service)

However, I am having trouble identifying the functional dependencies and candidate keys. I was thinking of something like (Hotel_ID, Author, Content, Date) for a primary key but then I realized that the same Author may post the same Review (in terms of contents) about the same Hotel on the same Date and just change his overall mark. Am I thinking too hard about this? I'd be grateful if someone could suggest some functional dependencies and keys.


texts: How to handle a many to many relationship with varying number of columns needed?

texts:
I'm designing a database that tracks hikes I have gone on. There is a Hikes table I have columns that include HikeID, LocationID, Type, Summit, #Friends. I then have a table called Friends which documents friends I've gone on hikes with.

What is the best way to document which friends have gone on which hikes without a host of null values?

My current idea is to create an intermediary table called FriendCombinations with a number of columns equal to the most friends I'd expect to ever hike with and then have an ID row in this table to reference in the Hikes table. Obviously this will produce null values but I'd rather have those be in the FriendCombinations table rather than the hikes table.

Is there a simpler and more elegant solution I'm just not realizing? Thanks.


texts: System.OutOfMemoryException in client app

texts:
I have a .NET client application running a very large batch of queries. I have SQL Server's max server memory configured, however when I run the query I get several messages like:

  An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

When I look at the server it has 20GB of memory free and SQL Server is only using 4.8GB (and that number is stable).

I've tried putting some GO statements in, but to no avail. I don't know where it is hitting the memory issues. None of the queries return more than a few rows.

How can I get around this memory issue?

I see someone posted a similar issue but I'm not sure how to allow SQL to write the results to a file rather than trying to put it all in the output window.

Responses to comments

The memory is set to 22528000.
A user is running a .NET application (which "calls" a SELECT statement) on his desktop. His desktop configuration is i7 processor with 32gb.
I'm not running it on SSMS.
The DBMS is SQL server 2012, SP3.


texts:
Whenever a table crashes, MySQL returns as error: Table xxx crashed. Please repair the table.

Now I was wondering, what will happen if I repair a table that is healthy? Will it be ignored? Can I damage the table?

Reason: In the past days different tables have crashed and I'm considering issuing a repair on all tables as preventive measure.


texts: Different results from sys.dm_db_file_space_usage with database prefix

texts:
To make it simple:

According to MSDN (link) among other sources, you can get the amount of current free space in tempdb by using the following bit of TSQL:

-- MSDN version
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

-- The "other" version
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Now, we're using a scheduled script that dynamically does what it can without going overboard with space usage. Prior to today, the MSDN version worked fine in environments using clustered SQL Server 2008's, but today I found out that one of our new environments with SQL Server 2012 and AlwaysOn Availability Groups behaves differently.

There, the "MSDN version" returns almost no free space at all, while the "other" version correctly shows the available free space in tempdb.

Can someone explain this to me? Is there a shared tempdb among all the AlwaysOn replica groups so that by default, not specifically defining tempdb somehow defaults to some local version of tempdb, or what is really going on here? Can I trust that the queries run against the db actually utilize tempdb.sys.dm_db_file_usage_space as opposed to whatever it is that regular sys.dm_db_file_usage_space is pointing out to?

I'm trying to find out more about this myself, but we need an answer quite urgently, so I'm asking here just to be sure. Thanks!


texts:
At first I would like to say hello to all of you, since it is my first post here on DB Administrators.

I am writing an app which store drinks and their data. At the beginning it was storing all in the local SQLite database and it was all updated by downloading newer version of app by the user. Since DB is still growing, I figured out that it would be nice if user may add a entry himself. So I decided to create MySQL db and sync it with local SQLite. And here are my issues:

How to download only the newer and updated entries, and avoid downloading same entry once again?
How to provide edit entry possibility to db? Is it better to create new table with proposal entries and after verification, move them to main table? Or maybe should I create additional columns, which would be proposals for new values?
What if I would get lots of changes proposals? Is it better to store only the last one? Or maybe block edit possibility if a proposal is already set?
How to avoid cheaters and scammers? Some bored people may want to try make a mess, editing multiple entries with fake data. How to avoid such kind of events? Grant edit access only to verified people?
If I would like to store images of items, is it better to store it in db or use some kind of flicker or instagram instead?
Is it better to save new entry in SQLite instant after edit? Or just post it to MySQL and add it to SQLite only after verification and only via SQLite and MySQL sync?
Is it good idea to set key value to EAN code? At least it is constant and connected to only one product. There will be no items containing no EAN.

Thanks for you replies. And also I would like to apologise if some of my questions doesn't fit in DBA rules. I am not db administrator.... yet ;)


texts: Check existence on other tables: performance advice

texts:
This is my first post.
I have this data model:

I want fetch some profile, with checking if they have any page or user assigned or not. I have tried multiple queries and finally came to these tows:

Query 1:

DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

SELECT TOP ( 1 )
        p.[ProfileId] AS [ProfileId] ,
        p.[Username] AS [Username] ,
        p.[UsernameCleaned] AS [UsernameCleaned] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Users] u WITH ( NOLOCK )
                             WHERE  u.UserId = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsUser] ,
        CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                    1
                             FROM   [dbo].[Pages] pg WITH ( NOLOCK )
                             WHERE  pg.[PageId] = p.[ProfileId] ) )
             THEN CAST(1 AS BIT)
             ELSE CAST(0 AS BIT)
        END AS [IsPage]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token

Query 2:

DECLARE @Token NVARCHAR(256);
SET @Token = N'amiry';

DECLARE @ProfileId BIGINT= NULL ,
    @Username NVARCHAR(256)= NULL ,
    @UsernameCleaned NVARCHAR(256)= NULL ,
    @IsPage BIT= 0 ,
    @IsUser BIT= 0;

SELECT TOP ( 1 )
        @ProfileId = p.[ProfileId] ,
        @Username = p.[Username] ,
        @UsernameCleaned = p.[UsernameCleaned]
FROM    [dbo].[Profiles] AS p WITH ( NOLOCK )
WHERE   p.[UsernameCleaned] = @Token;

IF @ProfileId IS NULL
    BEGIN
        RETURN
    END

SELECT  @IsUser = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Users] u WITH ( NOLOCK )
                                       WHERE    u.UserId = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT  @IsPage = CASE WHEN ( EXISTS ( SELECT TOP ( 1 )
                                                1
                                       FROM     [dbo].[Pages] pg WITH ( NOLOCK )
                                       WHERE    pg.[PageId] = @ProfileId ) )
                       THEN CAST(1 AS BIT)
                       ELSE CAST(0 AS BIT)
                  END;

SELECT TOP ( 1 )
        @ProfileId AS [ProfileId] ,
        @Username AS [Username] ,
        @UsernameCleaned AS [UsernameCleaned] ,
        @IsUser AS [IsUser] ,
        @IsPage AS [IsPage];

Which one would be better and would have a higher performance?

p.s.
I'm not an exact SQL-guy. So, it's hard for me to determine which query would have a better performance and executes faster -I mean as fast as possible. Any help/advise would be absolutely helpful. Thanks in advance.


texts: What is the significance of the trailing dollar sign `$` on identifiers in SQL Server?

texts:
Dumping some internal views, such as sys.system_objects as done in this question I see that some identifiers end with a dollar-sign $

CREATE VIEW sys.system_objects AS
    -- cutout
    FROM sys.sysschobjs$ o -- XXX: HERE
    LEFT JOIN sys.syspalnames n
    -- not relevant

And, this works too

SELECT * FROM sys.sysschobjs$;

Even if only by convention, what is the rule here regarding this? Should I be using this in my own code?

Searching I see a lot of stuff about Excel (which is unrelated), and Money literals (which is also unrelated).

I posted another question which I thought was unrelated, but I believe this terminal $ will shed a lot more light on it. Feel free to check that question for an example of some confusion


texts: Derived value not calculating as expected using combination of FK and Triggers (MySQL)

texts:
I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints.

There are 3 tables involved in this arrangement

responses
------------------------
id_responses
text
n_comments

responses_has_comments
------------------------
id_responses
id_comments

comments
------------------------
id_comments
text

the link table - reviews_has_comments (rhc) exists because comments can be added to several other types of content.

A cascading foreign key constraint on rhc removes rows when a comment or response has been deleted. This is working as expected. I have written a trigger to increment/decrement n_comments on responses when a change occurs in rhc.

The incrementing trigger is working fine, but the decrementing trigger is not. Here are the triggers.

CREATE TRIGGER `inc_n_comments_in_responses`
    AFTER INSERT ON `responses_has_comments` FOR EACH ROW
        BEGIN
            UPDATE responses
            SET n_comments = n_comments + 1
            WHERE id_responses = NEW.id_responses;

        END;
 $$

 CREATE TRIGGER `dec_n_comments_in_responses`
    AFTER DELETE ON `responses_has_comments` FOR EACH ROW
        BEGIN
            UPDATE responses
            SET n_comments = n_comments - 1
        WHERE id_responses = OLD.id_responses;
    END;
$$

Can anyone cast their eye over this and point me in the right direction? As I understand it, the BEFORE and AFTER commands happen before and after the CASCADE.

UPDATE

OK - this is a case of RTFM (although in this instance the manual sucks....)

MySQL documentation on this matter shows that Foreign Key constraints do NOT activate triggers.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

So - let's look at the options:

1: I can increase the scope of the delete trigger thus:

CREATE TRIGGER `dec_n_comments`
    AFTER DELETE ON `comments` FOR EACH ROW
    BEGIN
        SELECT rshc.id_responses INTO @id_responses
        FROM responses_has_comments AS rshc WHERE id_comments = OLD.id_responses
        IF @id_responses IS NOT NULL THEN
            UPDATE responses
            SET n_comments = n_comments - 1
            WHERE id_responses = @id_responses;
        END IF;
        -- rinse and repeat for all linked "commentable" table data...
    END;
$$

My problem with this is that as the types of content to which comments can be added increases, so to will the size of this IF/ELSE (and it's bigger brother, the UPDATE trigger)

I could possibly remove the multiple IF/ELSE statements this points to
if I write a PROCEDURE to return a placeholder table name and identifier to
use in the subsequent UPDATE clause. This would require an additional field
on the comments table in order to get the relevant table type - but I'm
personally suspicious of referencing table meta data in this way...

I could possibly replace the resource/link/comment paradigm with a one to many
relationship - suffixing each type of comment with it's parent resource: i.e response_comments

Finally - I could just sack this off and keep it in the application - which at the moment
is really looking like the most likely option.


texts:
I was planning to buy the SQL Server 2012 standard edition for developing reports and SSIS packages. My company does not have a client-server architecture and the developers are working on their individual machines connected through the Lan.

I am really confused now on how should I configure the installation so that the developers will be able to connect to the server. Do I need to buy an individual licence for each machine (which is quite costly). Is there any alternative to the same?

I have searched the internet for similar solutions but was unable to find any. Please guide me in the right direction.

Thanks in advance.


texts:
I know what table scan, clustered index scan and index seek is but my google skills let me down to find a precise explanation into non clustered index scans. Why and when a query uses a non clustered index scan?

Thank you.


texts: postgres promote hot_standby (archive_command = ':') to master (archive_command = '')

texts:
Assume we have

master server (archive_command = '')
hot_standby server (archive_command = ':')
pg_receivewal server (from master slot)

What would be the correct procedure to promote hot_standby server to master, change archive_command to '' and change pg_receivewal slot?
My concern is a duplicate wal file mess between all 3 servers if done incorrectly.


texts: Checking if the query is made async or sync on SQL Server 2008 R2

texts:
I have tried to make async calls to my SQL Server instance as you may see on my SO post:

ASP.NET MVC 4 asyncronous database call: never returns the HTTP response

I did a benchmarking and the sync version is better than async one when there is even lots of concurrent loads. I am not sure if I got this right and opened the connection as async to my SQL Server instance.

The query does not take too much time and considering the fact that asynchronous code has much more overhead than the sync one, this might be the issue here as well. On the other hand, honest to say, I am not sure how to check if I opened the connection successfully as async or not.

Is there any chance I could check if the query is successfully completed as asynchronously?


texts: Sql developer how to copy to another tablespace

texts:
When copying database using SQL Developer it complaints that the source tablespace does not exist on destination database.

How can I copy schema and data from Database A tablespace t1 into Database B tablespace t2?

Note: I do not wish to use impdp.


texts:
I am trying to optimize a query and the sort operation (91% cost) is the costliest operation. Any help will be appreciated.

Query is :

SELECT TOP 1
    ArchiveType,
    CONVERT(NVARCHAR(30), A.StartTime, 20),
    DateDiff(mi, A.StartTime,
             IsNull(A.EndTime, CASE
                                 WHEN ArchiveConfig.STATUS = 1
                                    THEN GetDate()
                                    ELSE ISNULL(B.StartTime, A.StartTime)
                               END)) TimeTaken,
    CASE
       WHEN A.Error IS NULL AND ArchiveConfig.STATUS <> 1
         THEN - 1
         ELSE A.Error
    END
FROM
    A WITH (NOLOCK)
INNER JOIN
    ArchiveConfig WITH (NOLOCK) ON Type = ArchiveType
LEFT JOIN
    B WITH (NOLOCK) ON LogID = A.ID
WHERE
    Type = 4
ORDER BY
    A.ID DESC, B.ID DESC

Table definition:

CREATE TABLE dbo.A
(
    ID int IDENTITY(1,1) NOT NULL,
    ArchiveType int NOT NULL,
    StartTime datetime NULL,
    EndTime datetime NULL,
    Error int NULL,
    NextAction int NULL,

    CONSTRAINT PK_ARCHIVELOG
       PRIMARY KEY NONCLUSTERED (ID ASC)
             WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                   IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
);

Table definition b:

CREATE TABLE dbo.B
(
    ID int IDENTITY(1,1) NOT NULL,
    LogID int NOT NULL,
    StartTime datetime NULL,
    ObjectType int NOT NULL,
    ActionType int NOT NULL,
    TableName varchar(500) NOT NULL,
    PatchNumber int NOT NULL,
    NumberRow int NULL,
    EndTime datetime NULL,
    Error int NULL,

    CONSTRAINT PK_ARCHIVELOGHISTORY
       PRIMARY KEY NONCLUSTERED (ID ASC)
              WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
                    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
                    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
);

CREATE NONCLUSTERED INDEX [NonClusteredIndex_20160205-153718]
ON dbo.b (LogID)
INCLUDE (ID, StartTime);

The ArchiveConfig table:

CREATE TABLE dbo.ArchiveConfig
(
    ID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_ARCHIVECONFIG
        PRIMARY KEY NONCLUSTERED
    , Type int NOT NULL
    , PatchSize int NOT NULL
    , FailureAction int NOT NULL
    , RetryCount int NULL
    , ServerName nvarchar(500) NULL
    , DatabaseName nvarchar(500) NULL
    , AuthenticationType int NULL
    , LoginName nvarchar(500) NULL
    , Password nvarchar(500) NULL
    , Runprearchive int NOT NULL
    , Prearchive nvarchar(4000) NULL
    , Runpostarchive int NOT NULL
    , Postarchive nvarchar(4000) NULL
    , ServerIP varchar(20) NOT NULL
    , Status int NOT NULL
    , LinkSrv varchar(500) NULL
);


texts:
I have a sequence of insert statements which I want to run on a Postgres database. It looks something like this -

INSERT INTO test_admin.test_admins (id,
                                    "name",
                                    description,
                                    start_date,
                                    end_date)
        VALUES (
                  3,
                  'admin',
                  NULL,
                  TO_TIMESTAMP ('10/01/2015 00:00:00.00 -0500',
                                'MM/DD/YYYY fmHH24fm:MI:SS.FF Z'),

Due to the extra spaces preceding each line, neither pgadmin 3, nor the postgres console would accept this query and give syntax errors. What is the best way to remove these?


texts: Oracle 11g XE - Using a stored procedure to grant privileges to a table

texts:
I need to write a stored procedure that will grant certain privileges over a table to a user.

However it appears that GRANT cannot be used within a Stored Procedure.

My Curent code is as follows:

CREATE OR REPLACE
PROCEDURE "setUserPermissions" (u  VARCHAR2, l  INTEGER, tbl VARCHAR2)
AS
BEGIN
    DELETE FROM "userLvl" WHERE "user" = u;
    INSERT INTO "userLvl" VALUES(u,l);
    IF l>0 AND l<6 THEN
        GRANT SELECT ON tbl TO u
        IF l>1 THEN
            GRANT UPDATE ON tbl TO u
        END IF;
        IF l>2 THEN
             GRANT DELETE ON tbl TO u
        END IF;
    END IF;
END;


texts: Change value lower_case_table_names on a running docker container

texts:
I have a running docker image with a database where, for some reason, the columns are in camel-case. I have been searching how to change the value of lower_case_table_names to 1.

How can I make this change without having to recreate the docker image?


texts: How to select from table by comparing multiple rows with same key

texts:
I have below table KEY_STATUS.

|| KEY || STATUS   ||
| 0001 | FAILED    |
| 0001 | COMPLETED |
| 0002 | FAILED    |
| 0003 | COMPLETED |
| 0002 | FAILED    |
| 0004 | STARTED    |

I would like to select keys with only FAILED state. That means I will have to exclude 0001 as it has both FAILED and COMPLETED, also exclude 0003 as it has COMPLETED state.
Output should be

|| KEY || STATUS ||
| 0002 | FAILED  |

What approach should I take in Oracle to achieve above?


texts: Execution plans behavior after a restart in SQL server

texts:
I have been thinking about how the execution plans act in SQL SERVER after a restart of service. I know that in each query execution in the cache memory exist a process to parse, compile and optimize the requested query applying execution plans based on statistics and other parameters in SQL. All those execution plans are stored in buffer pool area in the storage engine. Actually, after each execution, those plans are updated to let the SQL SEVER learn itself about new improvements.

Here is my doubt: what happens with the execution plans in cache memory each time that I decided to restart my SQL service? All the improvements in the buffer pool area are stored and reloaded to be available in new executions? All execution plans are reloaded as last versions before the restart, or instead, SQL will need to create new execution plans library in order to keep on improving the executions?

Please, guys if you can explain that behavior I will really appreciate. thanks so much


texts: MongoDB Replica Set: Can't Add Member/Connect

texts:
I'm building a MongoDB replicaset w/ three Ubuntu 14 servers.

I'm connected to the primary server and authenticated w/ userAdminAnyDatabase and clusterAdmin privileges.

When I run rs.add(xx.xx.xx.xx:27017), I get this error:

exception: set name does not match the set name host xx.xx.xx.xx:27017 expects

I've tried using the IP address, external hostname, and internal hostname.

I have xx.xx.xx.xx rs2 in my /etc/hosts file and use rs2 in the config file of the server I want to add (replSet=rs2). No matter what I try, it says the name doesn't match.

What am I doing wrong?


texts: Philosophy on stored procedures that perform updates: is a 'granular' update approach sufficient?

texts:
I am embarking on on a large project and have an inquiry about stored procedures in general.
Let's say I have a User table.
UserKey IDENTITY NOT NULL PRIMARY KEY
FirstName nvarchar(50)NOT NULL
LastName nvarchar(50) NOT NULL
Email nvarchar(50) NOT NULL
Birthdate date NULL
EffectiveFrom datetimeoffset NOT NULL
EffectiveThru datetimeoffset NULL

In reality, there are some more fields here, but for the sake of brevity I have skimmed it down to the basics.
Now, I am faced with creating stored procedures to allow basic CRUD functionality for this table.
The Insert sproc is simple enough.
usp_INSERT_User:
CREATE PROCEDURE [dbo].[usp_INSERT_User]
(
    --@UserKey BIGINT,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(50),
    @BirthDate DATE = NULL,
    @EffectiveFromDtTm DATETIMEOFFSET(7),
    @EffectiveThruDtTm DATETIMEOFFSET(7) = NULL,
)

My main inquiry is how I should write an update sproc.
Since there can be pretty granular updates, should I create an update stored procedure for each of those granular updates?
For example, let's say I only want to update the user's email after processing a verified email update for the user, would I write a sproc with one parameter?
usp_UPDATE_UserEmail @NewEmail
If the user updated their profile, I could write a sproc like:
usp_UPDATE_User @UserKey, @FirstName, @LastName, @Email, @Birthdate
So, to cover my most common update operations, I might end up with a few sprocs to cover use cases of updates to the User table. Obviously, the larger the table and/or the higher number of business requirements would have a direct correlation to the number of update sprocs written for a particular table. This gets me thinking about maintainability of a ton of sprocs.
Yesterday, I went down the rabbit hole of

"oh, why don't I just write ONE end-all-be-all update sproc that can take whatever parameter I want to update via an XML param"

Luckily, without using dynamic sql, I was able to create that monstrosity. And it works.
I then decided to test speeds between my fancy xml sproc and just a regular update sproc (with multiple params) with very similar updates.
So, I created a mock user table with 12 million records and tested both approaches. Unsurprisingly, the XML approach on average, took 7x longer than the vanilla update sproc.
There is also additional overhead on the other side of the wire serializing parameters into XML, which could be of concern with a large update batch.
Now, I say all of that to wrap up and ask which type of approach in your experience is most often (if at all) seen? Is is most common practice to have granular sprocs like usp_UPDATE_Field1 / usp_UPDATE_Field2 / usp_UPDATE_Field3&4 OR usp_UPDATE_whateveryouwant
I like the simplicity of having a golden sproc that can take however many number of parameters you want to pass it and update them accordingly, but there are also performance drawbacks.
To give more context: running SQL Server 2019. I am also pretty new at this stuff.
Thanks for any help!


texts: oracle to PostgreSQL database conversion

texts:
In oracle:

select owner, constraint_name, table_name, column_name, position
from dba_cons_columns;

Is there any table like dba_cons_columns in PostgreSQL database that we get the same information?


texts: MySQL 5.6 upgrade - reducing code changes / developer burden

texts:
Does anyone know the key things that should be done when moving to MySQL 5.6 to reduce the amount of code changes required? I found that new installations of it have STRICT_TRANS_TABLES set for sql_mode by default.

I plan to go through the release notes and upgrade guides on the MySQL site in more depth but during a past upgrade which was only a point-release change, there was a backwards incompatible change that wasn't called out in the release notes

I wanted to see if anyone had anything noteworthy to mention from experience.


texts:
I implemented partitions on one of the tables our DEV/TEST environment, and modified some of the procedures to use the partition key when accessing this table.

How can I verify that partition elimination is actually occurring in the DB?

I tried checking it with this query.
But I saw only that "partitioned = true" is mentioned, but no how many partitions were accessed.

SELECT
  eqs.execution_count,
  CAST((1.)*eqs.total_worker_time/eqs.execution_count AS NUMERIC(10,1)) AS avg_worker_time,
  eqs.last_worker_time,
  CAST((1.)*eqs.total_logical_reads/eqs.execution_count AS NUMERIC(10,1)) AS avg_logical_reads,
  eqs.last_logical_reads,
    (SELECT TOP 1 SUBSTRING(est.text,statement_start_offset / 2+1 ,
    ((CASE WHEN statement_end_offset = -1
      THEN (LEN(CONVERT(nvarchar(max),est.text)) * 2)
      ELSE statement_end_offset END)
      - statement_start_offset) / 2+1))
    AS sql_statement,
  qp.query_plan
FROM sys.dm_exec_query_stats AS eqs
CROSS APPLY sys.dm_exec_sql_text (eqs.sql_handle) AS est
JOIN sys.dm_exec_cached_plans cp on
  eqs.plan_handle=cp.plan_handle
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) AS qp
WHERE est.text like '%<TABLE_NAME>%'
OPTION (RECOMPILE);

Do you have any other query that can help me identify if partition elimination occurs in my DB?

Thanks !

Roni.


texts: Simulating having multiple Control Flows in a SSIS package

texts:
I have a SSIS package that in its Control Flow I have quite lot of Data flows. To be able to organise them, I have wrapped the related Data flows into Groups. For example I have 5 groups as below which each one has got on average 8 Data flows. For example:

Students Group (includes a number of Data flows)

Courses Group (includes a number of Data flows)

Institutes Group (includes a number of Data flows)

Enrollments Group (includes a number of Data flows)

...

Now I see the need for adding more and more Data flows to each group is growing! Each of these groups are already big and adding more Data flows only make them more crowded and more difficult to maintain.

Based on my research there is no way to have multiple Control flows in a package, otherwise I would put each of the Data flows above in a Control flow, e.g.: one Control flow for Students, one for Courses, etc.

I know I can create a package for each area, for example one package for Students, etc, but I prefer to avoid adding two many packages as much as possible.

Do you have any idea how I can organise them in a package in a sustainable and clean manner?

Thanks.


texts: Usage of NON Clustered Index in SQL Server

texts:
I have 2 Index:

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
    [Column1] ASC,
    [Column2] ASC
)

CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA]
(
    [Column3] asc
)

Now I have 2 update:

Update TableA SET Column3='' where Column1=''

the above Update was using Index 1st and then Key lookup to clustered Index

Update TableA SET Column3='' where Column3=''

This update  was using  2nd index

Based on the Update I thought I can delete 2nd index and update the first one with

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
  [Column1] ASC,
  [Column2] ASC
)
INCLUDE(Column3 asc)

So with the updated index the First Update works fine,it uses the updated Non Clustered Index , but for second update it ask me to create a non clustered index on column3.

Then I tried

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
  [Column1] ASC,
  [Column2] ASC,
  [Column3] asc
)

But still for second update it ask me to create a new non clustered index.

But why it ask me to create new, because what I understand is Column1, Column2, Column3 will be present in Root level for non clustered Index


texts:
I am trying to wrap all interactions with inventory items into one transactional table. Currently I calculate QoH by querying multiple fact tables (production, acquisition, shipment, return order, and stocktake). I want to be able to calculate QoH, without rewriting code anytime  a transaction type is added or removed, rather only update a record in a table. I also want the ease/completeness of querying the transaction table once and running a sum.

Each of these fact tables contains their own private relations to other tables. For example, order return, ties product received into inventory to a previously shipped order.

Do I essentially copy the data I need from the original fact tables and place it into the transaction table - creating redundant information. And supertype the fact tables to have a common id for use as a FK in the transaction table? Or is there something I am missing?

In summary, I need to have a common key amongst the various fact tables to isolate interactions with an inventory item to one table. I also need to maintain the private data/relations from each of the fact tables.


texts:
How I can configure AutoMySQLBackup to backup some selected tables ones per week while the other tables will be backed up every day?

Thanks.

P.S. My google search ninja skills is not working. :)


texts: SELECT from slave and INSERT into master in MySQL

texts:
I'm using PHP/Laravel and MySQL databases.
Briefly, I want to use slave databases for reads and master database for writes.
What Laravel config, Laravel package, or database tool do you suggest to handle my issue?


texts: postgresql compare column value from first row only

texts:
This is how my history table looks like

     start_date        |      end_date          |   name
-----------------------+------------------------+----------
2016-11-05 02:26:00+00 | 2016-11-10 01:55:00+00 | premium
2016-10-01 00:40:00+00 | 2016-11-30 01:55:00+00 | free

Records are ordered by start_date in descending order. I want to get the first row and then check if its end_date is greater then now(), if yes then return true else false.

I have written the below query by looking at some tutorials and its working fine but i don't know if this is efficient or not and how to write an optimised query for my question above.

SELECT history.end_datetime FROM history,
(SELECT id, end_datetime from history ORDER BY start_datetime
DESC LIMIT 1) subhistory WHERE history.id = subhistory.id AND
subhistory.end_datetime > now();


texts:
I have a table with two columns name and value.

There are many rows with the same name, but with different values.

I want to create a function that returns a table with 2 columns.  The first column is the name and the second is a character, either yes or no, which refers to whether a name has a specific value or not.

If a name  has the specific value at least at one of its values to return yes and if it does not have the specific value to return no.

With the answers so far, I return the same name many times with the corresponding values like

('BOB'-'NO','BOB'-'NO','BOB'-'NO','BOB'-'NO','BOB'-'YES','JAME'-'NO','JAME'-'NO','JAME'-'NO','JAME'-'NO','JAME'-'NO') for example.

I want to return a table like that:

('BOB'-'YES','JAME'-'NO')

because bob has a specific value that I want at least one time at his values and JAME has not the specific value in a row.


texts:
I am failing to understand a few concepts about FKs, i have some really big tables (dozens of million rows) with lots of FKs and i want to delete old data, i can disable FKs -- delete Data -- enable FKs to do that, i fear they will become untrusted and the parent tables will be in conflict.

I read about "ON CASCADE DELETE" but i think that only works for children tables.

Example table:

I had one where there was only one FK and i used the following query:

DELETE

FROM    guide_category

WHERE

id_guide NOT IN

        (
        SELECT  id_guide
        FROM    guide
        )

This could work but i don't its feasible with many FKs.

The data importance is actually zero (test DB) i just want small DB and all tables to be trusted for better performance.

Am i missing an easy solution to this problem? or maybe even seeing a problem that isn't there.


texts: We use WUXGA displays (1920 x 1200), and have lots of SSRS reports. Since these reports need to be printed once in a while the layout is adjusted to A4. However when the users look at the reports on the display around 40% of the screen is blank. Absolut waste of screen space. How can I adjust SSRS to use the full screen size for reports ?


texts: Rows do not match Subscriber and Publisher

texts:
I have an transactional replication setup with updateable subscription that is utilized by an Application. When I insert a project into the application, the msrepl_tran_version is different on the publisher and the subscriber. Hence, when I go to perform the update it does not update as the rows do not match.

Is this happening due to a trigger?
How can I resolve this issue. As mentioned it only happens when I update certain combobox from the application which is 3 columns from this table.

How can I fix this issue.!!! Even when I run the distribution agent, it will fix it but then when the user performs a new insert the problem occurs again.

I know this is similar to my other post but this provides more details.


texts: Migrate sharepoint database from SQL Server 2012 express to SQL Server 2012 Standard

texts:
My sharepoint is using SQL Server 2012 R2 Express as a database Server, and I already have reached the limit of space (10GB). In the other hand I have a SQL Server 2012 Standard with proper license.

Would it be possible to migrate my database to SQL Server Std?
Can I install my SQL Server 2012 Std on the same server I have of my SQL Server Express?
Is there any risk doing that in terms of sharepoint compatibility?

  NOTE: I have installed SQL Server 2012 Std on same server but the db size limit still persist, is this normal?


texts:
when I run the following query from Glenn Berry

-- Hardware information from SQL Server 2016  (Query 18) (Hardware Info)
SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],
committed_target_kb/1024 AS [Committed Target Memory (MB)],
max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type],
softnuma_configuration_desc AS [Soft NUMA Configuration],
sql_memory_model_desc -- New in SQL Server 2016 SP1
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);
------

when I use powershell I get more details though:

I have been reluctant to learn powershell, but I plan to accept it soon...

meanwhile, the question is:

Is there any way in sql server to find out the speed of the processor (in this case 2.60 Ghz)?


texts: Understanding many-to-many ternary relationship sets

texts:
I am new to entity-relationship diagrams. I'm a bit confused when it comes to interpreting ternary relationship sets such as this:

Does this signify that every relationship instance of Party will have one fighter, one mage, and one healer? If we were simply dealing with binary relationship sets without key or total constraints, every instance of the relationship would be linked to an entity from each entity set.
But in the case above, isn't it possible to not have one instance from a particular entity set? E.g. if the Party instance only had one fighter and one healer (and no mage)?


texts:
What is the best way to be store, query & index a column containing around 100k bits?
I am attempting to do it creating a table like so:
CREATE TABLE public.search_bf
(
    id serial,
    bf integer ARRAY[3125]
)
WITH (
    OIDS = FALSE
);

So column bf would have 3125 32bit integers which would give me the neccessary bits. I'm now trying to find a way of indexing and querying this.
I would like to use intarray but this doesn't work for bits.
I could use intarray if I just created a column integer ARRAY[100000] but this would be really inefficent in terms of storage.


texts:
In theory, there is a possibility to use coloured prompts in psql.  I decided (not the first time :) that I need them, so I thought I would give them a try.  So I have in my .psqlrc the following:

\set prompt_colour '%[%033[1;33m%]'
\set reset_colour '%[%033[0m%]'

\set PROMPT1 :prompt_colour'%n@%/%R%#%':reset_colour

This is very nice, I have a fancy yellow prompt.  Everything is good until I don't start walking back in the command history and hit a long enough command (in my experiments, 37 is OK, 38 is too long and multiline commands of any length do the same) - when I do, the Home button does not go back to the end of the row, it stops (in this case) 11 characters to the right.  If now I try to edit the command, it gets edited in the wrong positions.

I try to depict this:

# SELECT 123456789012345678 FROM a_table;
[here is an error, of course, but it doesn't count]
# SELECT 123456789012345678 FROM a_table;
[ cursor is  ^ here after pressing Home ]
[ hitting Enter gives the error ]
ERROR:  syntax error at or near "ELECT"
LINE 1: ELECT 123456789012345678 FROM a_table;

(From the documentation it looks like this version should also work but here the control characters aren't parsed, so I have no coloured prompt by the two expressions echoed:

\set PROMPT1 "%:prompt_colour:%n@%/%R%#%:reset_colour:"

but this results in

%[%033[1;33m%]postgres@postgres=#%[%033[0m%]

)

So does anybody has any idea how to do this properly?

Somehow I managed to omit the versions so here they are:

ubuntu 12.10
psql 9.1.9 (as far as I know, it is installed from a standard ubuntu package)
both libreadline5 and 6 are installed, also libedit2

My findings:

Following Peter's advice, I tried modifying the pg_wrapper script to link against libreadline.so.6 - in itself it works but the error is still there.  The same with libreadline.so.5 (which is the default one, I think).  With libeditline.so.0 the situation is much worse.


texts: SELECT rows based on indefinite number of filters

texts:
How do I create a function which takes indefinite parameters? And then finds all game_ids in a table where each parameter matches a different row (but same game_id)?
Example
Table:
create table tags (
    tag_id    serial primary key,
    game_id   int, -- references games(game_id),
    tag_name  text,
    tag_value text
)

Sample data:
 tag_id | game_id | tag_name  |      tag_value
--------+---------+-----------+----------------------
     55 |       6 | Event     | EUR-ASIA Rapid Match
     58 |       6 | Round     | 5
    400 |      38 | Event     | EUR-ASIA Rapid Match
    403 |      38 | Round     | 4

Example request: Let's say I want all game_ids where
Event (a tag_name) = 'EUR-ASIA Rapid Match' (a tag_value)
AND
Round (a tag_name) = '5' (a tag_value)

A hardcoded solution for that exact scenario only might look like this:
with m1 as (
  select game_id from tags
    where tag_name = 'Event'
    and tag_value = 'EUR-ASIA Rapid Match'
), m2 as (
  select game_id from tags
    where tag_name = 'Round'
    and tag_value = '5'
) select * from m1 intersect select * from m2;

Except I want an indefinite number of tag matches. Can I create a function that takes an arbitrary number of tag names/values and returns a set of game_id matching all? The call might look this (pseudo-code):
select * from get_games_by_tags('{Event,EUR-ASIA Rapid Match}', ...)


texts: Microsoft SQL Server SQL Injection Through .NET Even With Quote Escaping

texts:
I've been reading up on SQL Injection as part of a security audit for a fairly large web service.  I've been googling and read all the posts I could find here and on SO and have a fairly solid understanding of how dynamic queries really should be constructed and some of the differences between MySQL and MSSQL.  I eventually came across this page published earlier this year by Microsoft titled SQL Injection.  In the section titled 'Filtering Input', it states:

  Filtering input may also be helpful in protecting against SQL injection by removing escape characters. However, because of the large number of characters that may pose problems, this is not a reliable defense. The following example searches for the character string delimiter.

private string SafeSqlLiteral(string inputSQL)
{
  return inputSQL.Replace("'", "''");
}

Writing Secure Code Second Edition also discusses this on page 401-402, but it looks like the author didn't actually try the C# examples (or maybe the SQL version he used back in 2003 doesn't handle the escaped strings correctly).  It states that the string input will lead to an invalid query (in reality the escaping just queries for a value with a single quote in it), and then states that it could also be attacked with using the value for an unquoted numeric field (though it can't because the field in the example comes through a local variable of type int, which can't possibly contain the information needed for an injection).

I don't doubt that there is a possible attack vector here, but I haven't been able to find any explanations of how this might be accomplished.  From my other research, it looks like one possible way might involve strange byte sequences in non-Unicode strings.  In my case, I'm working with .NET, so there is no possibility of such sequences, and I would think that either ADO.NET, SQL Server, or both would properly handle Unicode strings with any other types of characters inside the single-quotes.  (Though interestingly the sample code appears to be in C#).

I'm also well aware of the other methods of SQL injection listed on Microsoft's page, and for the purposes of this question, I'm not worried about those.  Also, I'm only concerned about properly typed string literal inputs here.  This code always parses the incoming types into their appropriate runtime type before composing the SQL queries, so the quote escaping only applies when the data is literal string data.  So in my case, the function actually looks more like:

private string SafeSqlStringLiteral(string inputString)
{
  return "'" + inputString.Replace("'", "''") + "'";
}

This thing that concerns me most is the following phrase in the documentation:

  However, because of the large number of characters that may pose problems, this is not a reliable defense.

Is this just referring to strange single-byte encodings, or is there a Unicode character that this applies to (I haven't been able to find one programmatically)?

The code I'm reviewing is extensive, and uses this type of escaping in many places (and in a way that's not trivial to fix). I'm not asking for an exploit directly (that's frowned upon IIRC), but I'd like to get some more-expert-than-me clarification on this so that I might be able to convince myself that this isn't an issue, or convince the team leader that it is an issue so that we can budget the significant amount of time necessary to fix these issues.

So, my question is, assuming we're not playing any games with PInvoke or unsafe code to create single-byte strings and call SQL Server with those, is there any way using .NET to inject SQL through this type of string literal escaping (as in SafeSqlStringLiteral above) when the string literals are only used in comparisons to string columns and don't involve LIKE clauses, making queries against Microsoft SQL Server 2012+?


texts: Backup to unc path in workgroup environment

texts:
I am having a problem backing up the database to the network path using dbatools.io Backup-DBAdatabase command.
This is a workgroup environment. The SQL Server runs under the NT Service\MSSQLSERVER account. The location I am trying to backup is a standalone  NAS drive.
I mapped the network folder to the SQL Server machine but when I backup to that drive it fails with

WARNING: [14:25:08][Backup-DbaDatabase] Backup Failed |
System.Data.SqlClient.SqlError: Cannot open backup device
'K:\BackupTest\15Jan2021_12_34\full\sdsd_180_infras_b18c6_202101151425.bak'.
Operating system error 3(The system cannot find the path specified.).

If I use the UNC path I get

WARNING: [14:24:04][Backup-DbaDatabase] Backup Failed |
System.Data.SqlClient.SqlError: Cannot open backup device
'\\XX.XX.XX.XX\backup\BackupTest\15Jan2021_12_34\full\XX_180_infras_b18c6_202101151424.bak'.
Operating system error 1326(The user name or password is incorrect.).

I am aware the issue is due to the SQL Service account's lack of permission on the NAS folder not sure how can resolve it.
I really appreciate your help


texts: Delete interlinked tables data using one query

texts:
I want to write the database query to delete the data from multiple interlinked tables.
Below is the structure of the database:

CREATE TABLE TABLE1 (
  ID NUMBER(10),
  TABLE2_ID NUMBER(10),
  TABLE3_ID NUMBER(10),
  DATA VARCHAR2(200)
);

INSERT INTO TABLE1
  (ID, TABLE2_ID, TABLE3_ID, DATA)
VALUES
  ('1', '1', '1', 'TEST');

CREATE TABLE TABLE2 (
  ID NUMBER(10),
  DATA VARCHAR2(200)
);

INSERT INTO TABLE2
  (ID, DATA)
VALUES
  ('1', 'TABLE 2 DATA');

CREATE TABLE TABLE3 (
  ID NUMBER(10),
  DATA VARCHAR2(200)
);

INSERT INTO TABLE3
  (ID, DATA)
VALUES
  ('1', 'TABLE 3 DATA');

CREATE TABLE TABLE1_TABLE4_JOIN_TABLE (
  TABLE1_ID NUMBER(10),
  TABLE4_ID NUMBER(10)
);

INSERT INTO TABLE1_TABLE4_JOIN_TABLE
  (TABLE1_ID, TABLE4_ID)
   SELECT '1', '1' FROM dual UNION ALL
   SELECT '1', '2' FROM dual UNION ALL
   SELECT '1', '3' FROM dual;

CREATE TABLE TABLE4 (
  ID NUMBER(10),
  TABLE5_ID NUMBER(10),
  DATA VARCHAR2(200)
);

INSERT INTO TABLE4
  (ID, TABLE5_ID, DATA)
   SELECT '1', '1', 'TABLE 4 DATA 1' FROM dual UNION ALL
   SELECT '2', '2', 'TABLE 4 DATA 2' FROM dual UNION ALL
   SELECT '3', '3', 'TABLE 4 DATA 3' FROM dual;

CREATE TABLE TABLE5 (
  ID NUMBER(10),
  DATA VARCHAR2(200)
);

INSERT INTO TABLE5
  (ID, DATA)
   SELECT '1', 'TABLE 5 DATA 1' FROM dual UNION ALL
   SELECT '2', 'TABLE 5 DATA 2' FROM dual UNION ALL
   SELECT '3', 'TABLE 5 DATA 3' FROM dual;

TABLE1:
+----+-----------+-----------+------+
| ID | TABLE2_ID | TABLE3_ID | DATA |
+----+-----------+-----------+------+
|  1 |         1 |         1 | TEST |
+----+-----------+-----------+------+

TABLE2:
+----+--------------+
| ID |     DATA     |
+----+--------------+
|  1 | TABLE 2 DATA |
+----+--------------+

TABLE3:
+----+--------------+
| ID |     DATA     |
+----+--------------+
|  1 | TABLE 3 DATA |
+----+--------------+

TABLE1_TABLE4_JOIN_TABLE:
+-----------+-----------+
| TABLE1_ID | TABLE4_ID |
+-----------+-----------+
|         1 |         1 |
|         1 |         2 |
|         1 |         3 |
+-----------+-----------+

TABLE4:
+----+-----------+----------------+
| ID | TABLE5_ID |      DATA      |
+----+-----------+----------------+
|  1 |         1 | TABLE 4 DATA 1 |
|  2 |         2 | TABLE 4 DATA 2 |
|  3 |         3 | TABLE 4 DATA 3 |
+----+-----------+----------------+

TABLE5:
+----+----------------+
| ID |      DATA      |
+----+----------------+
|  1 | TABLE 5 DATA 1 |
|  2 | TABLE 5 DATA 2 |
|  3 | TABLE 5 DATA 3 |
+----+----------------+

I have not shown the foreign key constraints in the schema above but all foreign keys are set up in the database.

I want to delete everything which is linked to table1.id = [someid]

select TABLE1.*,TABLE2.*,TABLE3.*,TABLE4.*,TABLE5.*,TABLE1_TABLE4_JOIN_TABLE.* from TABLE1
inner join TABLE2 on TABLE1.TABLE2_ID = TABLE2.ID
inner join TABLE3 on TABLE1.TABLE3_ID = TABLE3.ID
inner join TABLE1_TABLE4_JOIN_TABLE on TABLE1.ID = TABLE1_TABLE4_JOIN_TABLE.TABLE1_ID
inner join TABLE4 on TABLE4.ID = TABLE1_TABLE4_JOIN_TABLE.TABLE4_ID
inner join TABLE5 on TABLE5.ID = TABLE4.TABLE5_ID
where TABLE1.ID =1;

I tried the above select query with delete from but it's not deleting the data.

Please suggest the best way to delete these.


texts: Is there a way to speed up an ALTER statement in SQL Server

texts:
I have an existing table with millions of rows in it where I have a column which is the SMALLMONEY data type and I want to ALTER it to be MONEY. To make things a bit more complicated it has an index on it which which INCLUDE (s) that column so I need to remove that prior to the ALTER and then add it in again after the ALTER.

I have been running through my test deployment and each of these operations (index removal, table alter and index addition) takes about 4 - 6 minutes each.

I really need to come up with some way to do this faster.

This table is constantly being inserted into and so the shorter the duration the better.

Any pointers would be appreciated.

I am using SQL Server 2008 Enterprise.


texts: Msg 8905: DBCC CHECKALLOC Error Messages

texts:
We recently experienced a database corruption and run integrity checks. DBCC CHECKDB returned a lot of consistency errors and 2 allocation errors.
We have fixed consistency errors by moving data, droping and recreating related objects.

Now we have left 2 allocation errors and we don't know how to resolve them. Can anyone advice us on how to find the cause of these errors?

Below is the code we run;

DBCC CHECKALLOC([DBNAME]) WITH NO_INFOMSGS

The results;

  Msg 8905, Level 16, State 1, Line 1 Extent (1:9612464) in database ID
  22 is marked allocated in the GAM, but no SGAM or IAM has allocated
  it.

  Msg 8905, Level 16, State 1, Line 1 Extent (1:9617504) in database ID
  22 is marked allocated in the GAM, but no SGAM or IAM has allocated
  it.

  CHECKALLOC found 2 allocation errors and 0 consistency errors not
  associated with any single object.

  CHECKALLOC found 2 allocation errors and 0 consistency errors in
  database 'DBNAME'.

  repair_allow_data_loss is the minimum repair level for the errors
  found by DBCC CHECKALLOC (DBNAME, noindex).


texts: LOG_BACKUP wait type for Transaction log file of a simple recovery model database

texts:
A few minutes ago I ran into a strange situation with my prod database. I have db X with simple recovery model. SCOM gave me an alert that the log disk is running out of space. I then checked the disk and saw that db X's log file is 11 Gb even though the data file is 300 Mb and the recovery model is simple.

Then I checked DBCC OPENTRAN but there were no open transactions, neither on the database nor on the server.

Then I tried to shrink the log file, but it said that there was no empty space.
I checked the log_reuse_wait_type column and it was LOG_BACKUP. I know that, by definition, it should be impossible to see that wait type for a database using the SIMPLE recovery model, but there it was. I double checked all the steps that I mentioned above and nothing changed. Then I changed the recovery model of db X to FULL and set it back to SIMPLE. After that log_reuse_wait_type reported as NOTHING.

In the end I solved the problem but still wonder why it happened. Has anyone encountered a similar problem?


texts: Check if this model is correct from the text

texts:
I am trying to make an conceptual view of the following text:

Each office has a Manager; several taxi owners, drivers and administrative staff. The Manager is responsible for
the day-to-day running of the office. An owner provides one or more taxis to Huber Taxi and each taxi is allocated
for use to a number of drivers. The majority of owners are also drivers.
HuberTaxi taxis are not available for hire by the public hailing a taxi in the street but must be requested by first
phoning the company to attend a given address.
There are two kinds of clients, namely private and business. The business provided by private clients is on an ad
hoc basis. The details of private clients are collected on the first booking of a taxi. However, the business provided
by business clients is more formal and involves agreeing a contract of work with the business. A contract stipulates
the number of jobs that Huber Taxi will undertake for a fixed fee.
When a job comes into Huber Taxi the name, phone number and contract number (when appropriate) of the client
is taken and then the pick-up date/time and pick-up/drop-off addresses are noted. Each job is allocated a unique
jobID. The nearest driver to the pick-up address is called by radio and is informed of the details of the job.
When a job is completed the driver should note the mileage used and the charge made (for private clients only). If
a job is not complete, the reason for the failed job should be noted.

This is my attempt of making the model, but i am not quite sure if the model is correct.


texts: Transactions Log fails to shrink / release vfl

texts:
The past two nights, our nightly index job has caused our log drive to fill up.   We complete log file backups every 15 minutes, and the log file still grew to 250GB.  This is a SQL Server 2008R2 SP3 instance.

Our DBCC LOGINFO shows 1500 rows, with all but 6 with a status of 2.

There are no open transactions.  (dbcc opentran)
The wait_type_desc = 2 / Backup Log
The database used to replicated, (long ago). Even ran the command to turn off replication.

However, completing a log backup does not seem to clear the VLFs.   The only way I've been able to clear is to convert the database to simple recovery and then shrink the logfile.   Which, obviously, this is not desirable.

I'm not sure where else to look.  Any help would be appreciated.


texts: Phpmy admin not showing my table but mysqldump

texts: I have a db running and suddenly when I go into my phpmyadmin under the db it shows no tables and when I tried to do dump it shows me some of my tables are crash. What is best survival in this case?


texts:
I've discovered that my company's ETL proccess has a major bug in it around a process that checks if the ID in the staging database has already been used in the real database. In theory this processes should iterate through until it find an ID that has not been used, however on study it doesn't do that, only iterating once before stopping.

BEGIN
-- there is only ever 1 row in staging.
declare id1 char(12) default (select id from stg.extraction);

loop_label: Loop

if id1 not in (select id from main.extraction) Then
   leave loop_label;

end if;
   set id1=id1+1;
   leave loop_label;
 End Loop;

update stg.extraction
set id=id1;

END

I've tried several possible solutions but none of them seem to change things. I think I need to convert it to a while loop but I'm not sure.


texts: Scalar function used in calculated column, what's the cleanest way to update the function?

texts: I want to update a scalar function in our database.  This function is used for several calculated columns in a key table so I get a dependency error if I try to update it.  I can explicitly remove the columns, update the function and re-add the columns but, without a lot of extra fannying about, that will change column orders and might have other unintended consequences.  I wondered if there was a cleaner way of doing it?


texts: Count specific query occurred in Redshift

texts:
I wanted to count the number queries executed over one day. I have this working version :

SELECT DATE_TRUNC('day', starttime) AS date,
       COUNT(*) AS COUNT
FROM stl_query
WHERE querytxt LIKE 'COPY sensor_%_temp FROM%'
  AND starttime BETWEEN '2017-01-24 00:00:00' AND '2017-01-25 00:00:00'
GROUP BY 1;

Is there a better way of doing it?


texts: Querying a SQL Server's CNAME alias instead of the actual instance name

texts:
We don't have a DBA here and our infrastructure analyst is trying his best to give us a test instance on a server using a CNAME alias which neither he nor I have done before.

The actual database instance being used is called AN-UATSQL03.domain.com but it has been set up with a host name of [ALT-STG-SQL-ATLNTA1].

Connection on management studio requires a server name of [ALT-STG-SQL-ATLNTA1\DWH] to work. The underlying server being used does have an instance of \DWH but this wasn't needed in the DNS setup, however we can only connect if the instance part of \DWH is added on at the end. That's one weird occurrence.

Right now, if we query on this new instance like so;

SELECT *
FROM [ALT-STG-SQL-ATLNTA1\DWH].[Database].[dbo].[Column]

It doesn't work, even though on Management studio we connected to ALT-STG-SQL-ATLNTA1\DWH as the Server name. It's like the alias isn't recognised in the catalogue and the actual instance that the alias is pointed to in the DNS is what the Query also wants to have referenced.

While [server] in a SQL query from within that same server isn't really necessary, we do that in cases where we are joining across linked servers to resources that have very similar structures, so we know which is which.

We're out of our depth here really in trying to make this work. We need to test the migration of objects from an existing SQL server to this new one via the UAT server that the host name points to in the DNS. We wanted to be able to test it, if it works we can migrate to a live server after altering any SQL code to use the new host name of [ALT-STG-SQL-ATLNTA1] for the server which would stay the same.

Any advice would be so much appreciated!


texts: How to add a PostgreSQL 10 identity column to an existing table with rows?

texts:
I have a table with some rows and I try adding an identity column using the command:

ALTER TABLE sourceTable
    ADD COLUMN ogc_fid int
        GENERATED BY DEFAULT AS IDENTITY;

The command above is suggested as answer to this question:

How to add a PostgreSQL 10 identity column to an existing table?

But I get:

  ERROR:  column "ogc_fid" contains null values
  SQL state: 23502

It looks like the existing rows of the table do not get the identity auto generated but result in this error (the command works fine if the table has no rows). Any ideas? Do I have to first generate values for the column I'm adding maybe using DEFAULT nextval('some_sequence')? Or is there a better approach in PostgreSQL 10?


texts:
I am using PostgreSQL12 on Cent0S8, and I am able to connect to PostgreSQL using the command:
sudo -u postgres psql
However, when I attempt to connect using:
psql -U postgres -h localhost
I recieve the following error:
psql: error: count not connect to server: FATAL: Ident authentication failed for user "postgres"
Every option is set to trust within my pg_hba.conf file, and I successfully restarted the server after making these changes.
Does anyone know of what I would need to change to be able to login using the psql -U postgres -h localhost command?