texts: Find all tables in a snapshot replication that have primary keys

texts:
I've been tasked with moving all tables in a single snapshot replication that have primary keys to the transaction replication.

I've tried to break it down into two steps, finding all tables in a snapshot replication, and then checking to see if those tables have a primary key.

I've tried to piece together a few different code samples, but I may need to start over, here's what I've got so far.

--=============================================================================================

SELECT  DB_NAME ()                  PublisherDB
  , sp.name                     AS PublisherName
  , sa.name                     AS TableName
  , UPPER (srv.srvname)         AS SubscriberServerName
  ,*
FROM    dbo.syspublications   sp
JOIN    dbo.sysarticles       sa ON sp.pubid = sa.pubid
JOIN    dbo.syssubscriptions  s ON sa.artid = s.artid
JOIN    master.dbo.sysservers srv ON s.srvid = srv.srvid;

--=============================================================================================
SELECT          DB_NAME ()                    AS db
              , SCHEMA_NAME (o.schema_id)     AS [Schema]
              , so.name                       AS table_name
              , so.type
              , CASE WHEN TABLE_NAME IN (
                              SELECT    TABLE_NAME
                              FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                              WHERE     CONSTRAINT_TYPE = 'PRIMARY KEY'
                          ) THEN 1 ELSE 0 END AS HasPrimaryKey
--INTO            #t2
FROM            sys.objects o WITH (NOLOCK)
INNER JOIN      sysobjects                                      so WITH (NOLOCK)
--INNER JOIN #t1 ON t1.
LEFT            OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t2 ON t2.TABLE_NAME = so.name ON so.id = o.object_id
WHERE           (
        (so.xtype = 'U') -- user table   xtype: docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-ver15
    OR  (so.xtype = 'V') -- view
    OR  (so.xtype = 'P') -- stored procedure
)
                AND so.category <> 2
                AND so.name IN (
                        SELECT  DISTINCT OBJECT_NAME (objid) FROM  dbo.sysarticles
                    )
ORDER BY        so.name
              , so.type;

--=============================================================================================

DECLARE @jobId UNIQUEIDENTIFIER;
DECLARE @jobName sysname;

SELECT  @jobId   = jobs.job_id
      , @jobName = jobs.name
FROM    msdb.dbo.sysjobs       jobs (NOLOCK)
JOIN    msdb.dbo.syscategories categories (NOLOCK) ON jobs.category_id = categories.category_id
WHERE   categories.name = 'REPL-Snapshot'
        AND jobs.name LIKE '%db-name%';

SELECT  @jobId
      , @jobName;

EXEC sp_start_job @job_id = @jobId;


texts:
I'm reading Oreilly's PostgreSQL: Up and Running, and in the section Organizing Your Database Using Schemas it says the search_path may be set at the database level:

  For example, if we wanted all objects in contrib to be accessible without schema qualification, we would change our database as follows:

  ALTER DATABASE mydb SET search_path="$user",public,contrib;

However, after typing the command above, I still can't refer to tables in schemas other then public without qualifying them:

ALTER DATABASE auth SET search_path="$user",public,staging;`
\d users

Gives:

Did not find any relation named "users".

But if I use \d staging.users, it works.

SHOW search_path;

Gives:

   search_path
----------------
 "$user",public

Does the default search_path from postgresql.conf override the per-database value set with ALTER DATABASE ... SET search_path ? If that's the case, what's the use of the per-database value?


texts: Should I migrate data using detach/copy/attach or through backup-restore-replay?

texts:
I am about to embark on migrating database files to a new SAN (from an old SAN) abd I have a couple of options to implement this. (1) It was suggested that I look into the level of effort of restoring a full backup to a new database on the server. However, (2) my original plan was to copy the files from the old SAN to the new SAN by detaching and then reattaching the database.

My gut tells me that I'd rather detach, copy, and attach since it seems more fail-safe, but that may just be my naïvety. I don't want to miss a transaction or somehow "break something" in the process of renaming databases.

I guess my question is whether or not I am justified in my skepticism of the BACKUP-RESTORE-Replay option and what are other merits or risks of that option?


texts: How to Join a table to a group_contact in mySql

texts:
I have this query

select concat('[',group_concat(inn.str),']') into string4  from (
        select concat(
            '{"idmm":',customer.id,
            ',"cd":"',customer.code,
            '","nm":"',customer.name,

            '"," Order":"',

            '","des":"', customer.description,
            '"}' )as a_string
        from
            customertable customer

    )

How do I join the Order table to have the order_name joined to the string?
In the middle of the above string you see '"," Order":"', right? I want to join the Order table to pull the order_name as the customertable has order_id and concatenate to the string.

The customertable has columns

id int(10)
order_id int(10)
code char(6)
name varchar(45)
description varchar(500)

and Ordertable has columns

order_id int(10)
order_name varchar(500)


texts: Prevent SQL Server publisher from running out of disk space

texts:
I have an issue with transactional replication which leads to running out of disk space.
The scenario is following.

Database A is replicated to another computer via local distributor with command retention set to 24 hours (customer requirement).
Subscriber computer gets disconnected for a longer time.
After few hours of high load undstributed commands are piling up on publisher computer causing lack of disk space.

At first I tried to limit distribution db size, but after it reached max size, the ldf file for database A is swelling.
I wouldn't want to set limits on ldf file since it can cause database A to stop functioning.

Is there any approach I could choose to prevent this situation? I don't care about replication failure, my primary concern is to keep the database available for clients.
The perfect option would be an automatic subcription expiration if reached given number of transactions or used given disk space.


texts: How do I make this Postgres view that performs a join updatable?

texts:
I've got two tables, products and subscriptions:

CREATE TABLE products (
    id bigint NOT NULL,
    title character varying(75),
    description text,
    manufacturer_id bigint,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    mpn text,
    visible boolean DEFAULT false NOT NULL
);

CREATE TABLE subscriptions (
    id bigint NOT NULL,
    product_id bigint NOT NULL,
    user_id bigint NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

In my application I usually need to know the number of subscribers for a product, and it is tricky sprinkling this logic in the application in all the correct places. So I would like to replace the products table with a view that already includes that information.  So I did this:

ALTER TABLE ONLY products
  RENAME TO products_raw;

CREATE VIEW products AS
  SELECT products_raw.*, COALESCE(a.subscriptions_count, 0) AS subscriptions_count
  FROM products_raw
  LEFT OUTER JOIN (
    SELECT b.product_id, COUNT(*) subscriptions_count
    FROM   subscriptions b
    GROUP BY b.product_id
  ) a ON a.product_id = products_raw.id;

However the products view is not auto-updatable due to the JOIN - I would like any INSERT/UPDATE/DELETE actions to instead be performed on the products_raw table, ignoring the virtual subscriptions_count when applicable.

This is the first time I've created a view or rule, but I attempted this rule:

CREATE RULE products_insert_rule AS ON INSERT TO products DO INSTEAD
  INSERT INTO products_raw VALUES(NEW.*);

However Postgres doesn't like that:

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 13:         INSERT INTO products_raw VALUES(NEW.*);
                                                 ^

I also tried writing an INSTEAD OF INSERT trigger:

CREATE FUNCTION products_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO products_raw VALUES (NEW.*);
  RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER products_insert INSTEAD OF INSERT ON products
  FOR EACH ROW EXECUTE PROCEDURE products_insert();

but got essentially the same error message:

PG::SyntaxError: ERROR:  INSERT has more expressions than target columns
LINE 1: INSERT INTO products_raw VALUES (NEW.*)
                                         ^
QUERY:  INSERT INTO products_raw VALUES (NEW.*)
CONTEXT:  PL/pgSQL function products_insert() line 3 at SQL statement
: INSERT INTO "products" ("title", "description", "created_at", "updated_at") VALUES ($1, $2, $3, $4)

I'm using Postgres 9.4.  Any tips would be appreciated.

Edit: I got a bit closer with this trigger, but it's still erroring due to me not being sure how to cast the products_raw rowtype to a products.  I also don't like the direction this is going with having to repeat default values (including the id sequence)...

CREATE FUNCTION products_insert() RETURNS trigger AS $$
DECLARE
    p products_raw%ROWTYPE;
BEGIN
  IF p.id IS NULL THEN
    p.id = NEXTVAL('products_id_seq');
  END IF;
  IF p.visible IS NULL THEN
    p.visible = false;
  END IF;
  INSERT INTO products_raw VALUES(p.*);

  RETURN p;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER products_insert INSTEAD OF INSERT ON products
  FOR EACH ROW EXECUTE PROCEDURE products_insert();

The error I get with the above trigger is

PG::DatatypeMismatch: ERROR:  returned row structure does not match the structure of the triggering table
DETAIL:  Number of returned columns (8) does not match expected column count (9).
CONTEXT:  PL/pgSQL function products_insert() during function exit
: INSERT INTO "products" ("title", "description", "created_at", "updated_at") VALUES ($1, $2, $3, $4)


texts: mysql stored procedure create date plus one day but time set to 00:00:00

texts:
In a mysql stored procedure, how can i put into a date variable:-

curdate()+interval 1 day but at midnight - e.g. today is 2014-09-14 13:15:23 so the variable will hold:-
2014-09-15 00:00:00

thanks


texts:
OK, I'm new to Oracle administration but I'm trying to import a previous dump (no pun intended) which was split into two files.

The import is happening from an Oracle 9i to 11gR2 on Windows

I used the following commands:

imp userid=olduser/oldpassword indexes=n log=import.log
file(Export_DB_776full_20130829_020000_01,Export_DB_776full_20130829_0200
00_02) fromuser=olduser touser=newadminuser commit=y feedback=100

Now this has progressed fairly well and has imported data from what seems to be the first file, peppering the console with a dot every 100 rows imported.  Then when it reaches the table where the export was split (into the second dmp file) its just paused.  I thought it was simply collecting its thoughts and left it overnight but with no progress

Trying to connect to Oracle from SQLPLUS on the command line, even as SYS or SYSDBA is not possible, and returns with a

"ORA-00257: archiver error. Connect internal only, until freed."

Googling has yielded no direct answers to this but what I did find at least yielded some access to the database to perform some alteration function. Some of the other posts I saw mentioned that the log files are filling up, but there is well in excess of 150 GB free on both drives (the tablespace is split across two drives)

A few things:

What is going on? - because the error message seems a bit cryptic to me -
How can I connect to the database?
How can I fix this?
How do I not fall into this trap again?

Thanks


texts:
I need to be able to swap values that are used in a primary key.  Meaning, in one update statement, I need to change id 1 to id 2, and id 2 to id 1.

In other relational databases, (Sql Server and Oracle), it seems that the transaction that does the update applies the Primary key contraint after checking the values if update succeeded.  This seems to be in line with what a transaction should do before it commits the change. In mySql, I get a duplicate entry error code.

Am I missing something?  Is there a way around this?  I'm using ver 5.1.60, InnoDB

CREATE TABLE testUpdateKeys (
 optId INT PRIMARY KEY
 ,_rowId INT
);

INSERT INTO testUpdateKeys SELECT 1,1;
INSERT INTO testUpdateKeys SELECT 2,2;
INSERT INTO testUpdateKeys SELECT 3,3;

SELECT * FROM testUpdateKeys;

-- this should fail
UPDATE testUpdateKeys
 SET optId = 2
WHERE _rowId = 1;

CREATE TABLE testUpdateKeys_oper ( _oper CHAR(1), _rowId INT, optId INT);

INSERT INTO testUpdateKeys_oper SELECT 'U', 2, 1;
INSERT INTO testUpdateKeys_oper SELECT 'U', 1, 2;

SELECT * FROM testUpdateKeys_oper;

-- This works in SQL Server and Oracle
UPDATE testUpdateKeys a, testUpdateKeys_oper b
 SET a.optId = b.optId
WHERE
 a._rowid = b._rowId
AND b._oper = 'U';

SELECT * FROM testUpdateKeys;

DROP TABLE testUpdateKeys;
DROP TABLE testUpdateKeys_oper;


texts: SQL Server Management Studio SSMS 18.0 General Availability release (GA) install fails

texts:
Requirement

I wish to install SSMS 18.0 GA (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017), on Windows 10 but get error:

Uninstall Preview

Attempt to uninstall 18.0 preview via Add/Remove programs:

Uninstall fails with:

"The feature you are trying to use is on a network resource that is unavailable"

"Click OK to try again, or enter an alternate path to a folder containing the installation package 'vs2017_isoshell_for_ssms.msi' in the box below"

Use source C:\ProgramData\Package Cache{D620DE9C-8A83-4BA5-83A8-E709A4150A47}v15.0.27248.2015\redist\

vs2017_isoshell_for_ssms.msi does not exist on my machine.

Other fixes attempted

I attempted to remove via other means:

I have run MicrosoftProgram_Install_and_Uninstall.meta.diagcab from https://support.microsoft.com/en-us/help/17588/windows-fix-problems-that-block-programs-being-installed-or-removed
And removed everything with SSMS or management studio in the name. After that, SSMS 18.0 preview still appears in add/remove programs and an uninstall gives the same error as above.
msiexec /x “{D620DE9C-8A83-4BA5-83A8-E709A4150A47}” brings up the same dialog so I cannot progress:

Reinstall SSMS 18.0 Public Preview 6 works fine from https://go.microsoft.com/fwlink/?linkid=2052501
Try and install SSMS 18 GA again and get a different error of:

Remove SSMS 18 preview 6 via Add/Remove programs, and get the same error:

Question

How can I remove SSMS 18 preview? I don’t have vs2017_isoshell_for_ssms.msi, a web search for this file gives nothing and a re-install of the preview version does not provide it.


texts: Are their any default privilege in Oracle for users?

texts:
I want to grant new users in Oracle the normal rules, for example, create a table, delete table update, in general, the default CRUD operations.
Every time I do this manually by running a grant script. Does Oracle come with default privilege group, for instance, create a user/schema with developer rules,
create a user with DBA rules, without using the grant statement?


texts: PaaS Server - Random row order while using aggregate function with group by

texts:
Result set of a query with an aggregate function and group by (no order by specified) returns different result or at least in different order almost in each execution.

I have a database on Azure PaaS server and it's behaving weirdly. The query in issue is something like this:

SELECT TOP 100 "Org Unit", count(*) FROM "paas-server-12032019"."dbo"."testTable" GROUP BY "Org Unit"

When I check the "Org Unit" order, it's different each time. If I omit, count(*) and just keep the query as

SELECT TOP 100 "Org Unit" FROM "paas-server-12032019"."dbo"."testTable" GROUP BY "Org Unit"

It works as expected. Order of the record is consistent each time.
What I noticed

It happened with a specific database, I have another one and in there it works as expected.
Secondly, I think it has something to do when the column in group by is nvarchar(max)

I have a limited access currently to DB setup but wondering if there is anything that can cause this issue.

Any sort of help/discussion is deeply appreciated!


texts: How to extract pipe-delimited data using BCP?

texts:
I tried the following command

USE <DBNAME>
SELECT 'exec master..xp_cmdshell'
+ ' '''
+ 'bcp'
+ ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' out'
+ ' D:\'
+ TABLE_NAME + '.csv'
+ ' -c'
+ ' -t,'
+ ' -T'
+ ' -S' + @@SERVERNAME
+ ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

I want to get the data in following format.

Format will be Pipe delimited text file, with double quote text qualifiers and no header row. How to modify or write the BCP command to get the required result.

My dataset is just a customer table.

CustomerID  CustomerName         ContactName   Address        City    PostalCode  Country
1           Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209       Germany

Required data format is

1|"Alfreds Futterkiste"|"Maria Anders"|"Obere Str. 57 Berlin"|12209|"Germany"


texts: SQL Server: Read Only replica of production server

texts: I have a production database which is being used by my primary application. Another department has created a new application for which I need to provide subset of my data from my database. The new application will have its own database server and just for few tasks they need my data (which will keep on updating through primary application). I tried creating a Transaction Replication and made the subscriber as read only mode but it failed as replication cannot be done on read only database. What options do I have in order to give them the data but also to ensure the data is not tampered at their end and database stays in read-only mode.


texts: Creating a clustered index and want to add log files

texts:
We are adding a clustered index to a 6 billion row table. This is an online operation (15 hours into it).

If we add another log file while this is taking place, does the alter database add log file block command applications?


texts: Import with Network link fails with ORA-01400

texts:
Oracle 12.1.0.2.0
Setup a simple Import using Network Link, into same database
Using REMAP_TABLE and REMAP_SCHEMA options.
Full import.prm file is:
  NETWORK_LINK=mylink
  NOLOGFILE=YES
  CONTENT=DATA_ONLY
  TABLES=SCHEMA_A.MY_TAB
  REMAP_TABLE=SCHEMA_A.MY_TAB:NEW_TAB
  REMAP_SCHEMA=SCHEMA_A:SCHEMA_B

Source table has 7 rows, target has 0.
Running it, results in error:
  ORA-31693: Table data object "SCHEMA_B"."NEW_TAB":"P1" failed to load/unload and is being skipped due to error:
  ORA-01400: cannot insert NULL into ("SCHEMA_B"."NEW_TAB"."LOAD_TIMESTMP")

The column: LOAD_TIMESTMP in both tables is defined as:
  LOAD_TIMESTMP     NOT NULL   TIMESTAMP(6)

Checking the source table, there is data in all 7 rows.
Doing a manual insert .. select .. works fine:
  SQL > insert into SCHEMA_B.NEW_TAB
    2  select * from SCHEMA_A.MY_TAB;

  7 rows created.

  Elapsed: 00:00:00.00
  SQL > commit;

  Commit complete.

  Elapsed: 00:00:00.00
  SQL >

Why would import fail doing this ?
But insert/select proves that there's nothing wrong with the data ??


texts:
Besides manually searching for all the dblink() used in triggers/functions/stored procedures is there a way to query for this information?

Example: There is a dblink inside of a trigger, can I select something from the postgres schema that would identify this?

I'm thinking this is a manual effort but asking just to make sure I'm not missing something.

NOTE: I have some logging where I am also looking for dblink()


texts:
We have two SQL 2014 servers, and I have created a Linked Server on one of them to the other. As part of a task I'm trying to run I need to convert a SQL UserID to a username on both the local and the linked server.

On the local server I can just call

SELECT SUSER_NAME(1)

which will return the string sa

Is there an easy way to run the function SUSER_NAME on the linked server to get a value back from there?

The other alternative I think I've found is to do a join to sys.server_principals and pull out the name from there, but SUSER_NAME seems like a cleaner approach.


texts:
Aloha DBA's,

I've got major DB Designers-block and I can't wrap my head around the proper way to design the database to meet my web application requirements. This is strictly a DB design question and I thank anybody who can help me out with this.

I'm building this web application in ASP.NET MVC and using MSSQL 2012 as my data back-end, but that's probably irrelevant for this design question.

The application will be a training/course tracker of sorts that will allow any employee with an account to keep track of their mandatory training. Each course should be able to target all employees or specific groups (all, group a, group b, group c). These courses can be one-time training or recurring (weekly, monthly, annually, etc...). Most of the courses will be online that provide certifications upon completion, but we also have on-site training that only requires acknowledgement that you were there, kind of like a check-in. For online training, users will upload their certificate and fill in a DateCompleted field.

So now that the description of the requirements is out of the way, I can discuss my major design areas that are giving me problems.

My current database schema looks like the following:

The problem with this schema is that I don't think it's flexible enough for me to be able to properly handle the requirements and I'm not sure how or if I should set up a training plan of sorts (e.g. add courses to a training plan table, then assign the training plan to a user?). And then there's handling offsite vs onsite, and the recurring courses that require re-certification at a specific interval. I'm not sure where I should be storing this...

Again, thank you for any help you can provide. I'm very appreciative. If there's anything else you need to know about my requirements, please let me know.

Mahalo!


texts: Why would Mysql's Round(N.DD,1) sometimes show no digits after the decimal place when using the Perl DBI?

texts:
We have a set of calculations that do something like

SELECT ROUND(SUM(ROUND(3*IP))/3,1) as IP_sum from ....

Say for example that IP_sum is exactly 180.

When we call from the mysql client we get 180.0 every time, but if we use the perl dbi, the result depends on IP's column type.

When IP is a decimal(7,1) we get one digit and IP_sum => '180.0'
When IP is a float(7,1) we don't get any digits => IP_sum => '180'

Is anyone aware of a new change or update to the Perl DBI that might be causing this?  We just updated some modules and programs this week.

Engine: Mysql 5.6,  mysql client, 5.5.


texts: COMMIT on top of the pt-query-digest output

texts:
I've been optimizing my MySQL server and after tweaking a few queries, I ended up with the COMMIT item on top of the pt-query-digest output, as shown in the extract below:

# 322s user time, 770ms system time, 71.75M rss, 223.13M vsz
# Current date: Sat Oct  1 11:12:58 2016
# Hostname: XXXX
# Files: /home/tools/Slow10.log
# Overall: 1.08M total, 1.47k unique, 61.74 QPS, 0.17x concurrency _______
# Time range: 2016-10-01 06:15:09 to 11:06:35
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          2913s     1us     14s     3ms    15ms    27ms   194us
# Lock time           123s       0      2s   113us   167us     3ms    42us
# Rows sent        1019.81k       0   8.58k    0.97    0.99   26.59       0
# Rows examine     314.86M       0   2.13M  305.82  234.30   7.13k       0
# Query size       321.03M       6  14.64k  311.81   1.14k  743.10   88.31

# Profile
# Rank Query ID           Response time   Calls  R/Call V/M   Item
# ==== ================== =============== ====== ====== ===== ============
#    1 0x813031B8BBC3B329 1676.7355 57.6%  70006 0.0240  0.02 COMMIT
#    2 0xBC10C51A724ECD57   61.8928  2.1%    211 0.2933  0.08 SELECT users

And the query is shown as:

# Query 1: 4.00 QPS, 0.10x concurrency, ID 0x813031B8BBC3B329 at byte 462955973
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2016-10-01 06:15:09 to 11:06:34
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          6   70006
# Exec time     57   1677s     9us      2s    24ms    56ms    24ms    16ms
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0 410.19k       6       6       6       6       0       6
# String:
# Databases    XXX_XX... (9419/13%)... 40 more
# Hosts        localhost
# Query_time distribution
#   1us  #
#  10us  #
# 100us  #
#   1ms  ####
#  10ms  ################################################################
# 100ms  #
#    1s  #
#  10s+
commit\G

Any word on how can I optimize this?

If further information is required please let me know!

Thanks in advance!

EDIT 2016-02-10: The first analysis before any query optimization looked like:

# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ================ ======= ====== ===== ==========
# 1 0x62057CDB69C17D23 18377.2125 46.3% 139328 0.1319 0.15 SELECT AAAA
# 2 0xEC77079791A0E274 6992.0443 17.6% 127446 0.0549 0.09 SELECT BBBB
# 3 0xB8A934DECC36D811 6283.1217 15.8% 247595 0.0254 0.03 UPDATE users_sessions
# 4 0x813031B8BBC3B329 2042.6094 5.1% 89046 0.0229 0.04 COMMIT

Server has 8Gb RAM with innodb_buffer_pool_size=5G with about 70 databases with 140 tables each.

EDIT 2016-10-06:

# grep innodb /etc/my.cnf
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=5G
innodb_log_buffer_size=4M


texts:
We have a 2GB MySQL DB.  We started a mysql dump of this DB this morning and the resulting dump file is 27GB! Can anyone shed some light on what's going on here?

Questions

Why is this file so large?
What is in this file that causes it to be so much larger than the in memory footprint?


texts: Is there a way I can speed up queries against this particular view?

texts:
I have three tables in a PostgreSQL database that I'm querying via a view and some joins.

CREATE TABLE network_info (
  network         CIDR          NOT NULL,
  some_info       TEXT          NULL,
  PRIMARY KEY (network)
);

CREATE TABLE ipaddr_info (
  ipaddr          INET          NOT NULL,
  some_info       INT           NULL,
  PRIMARY KEY (ipaddr, some_info)
);

CREATE TABLE ipaddrs (
  addr            INET          NOT NULL,
  PRIMARY KEY (addr)
);

CREATE VIEW ipaddr_summary AS
SELECT DISTINCT
  i.addr                  AS ip_address,
  a.some_info             AS network_info,
  COUNT(b.ipaddr)         AS ip_info_count
FROM ipaddrs AS i
LEFT JOIN network_info AS a
  ON (i.addr << a.network)
LEFT JOIN ipaddr_info AS b
  ON (i.addr = b.ipaddr)
GROUP BY i.addr, a.some_info
;

All of the tables have ~150K rows right now, and it takes a really long time (~3 hours) to run SELECT * from ipaddr_summary; on an Intel Pentium 4 2.8GHz dual core with 2G of memory running PostgreSQL 9.3.

Is there a way I can restructure or optimize this particular schema or view to make the query faster, or is it a hardware issue? I'm going to spin up a beefy VM in the cloud and test, but wanted to see if there's a way to optimize w/out just throwing more hardware at it.


texts: Deleting "child" records on delete (reverse foreign key constraint?)

texts:
To illustrate my problem, I have 3 tables.

Table A AND Table B both individually have FK C_ID that reference Table C's PK.

I want Table C's rows to be deleted if their related rows in Table A or Table B is deleted.

FK constraints seem to only work one way. How do I deal with this?

To clarify, A and B has a 1:1 relationship with C. To be more precise it's a (A,B)parent-child(C) relationship where the child's existence depends on the parent's.


texts:
I have two cases

case

create  proc    createEmployee
    @firstName varchar(255),
    @lastName varchar(255) = null
as
insert into Employee_Tbl values (@firstName, @lastName)

case

create  proc    createEmployee
    @firstName varchar(255),
    @lastName varchar(255) = null
as
insert into Employee_Tbl(FirstName) values (@firstName)

declare @id as int
set @id = SCOPE_IDENTITY()
if @lastName is not null
begin
   update Employee_Tbl
       set LastName  = @lastName
    where EmpId = @id
end

Of the two code blocks above, which one performs better?


texts:
First, I had no idea where to put this.  I have a docker container (minecraft server) where I am trying to issue commands from a script(written in python) using the os.system line.  Thats just background info
If I want to execute a command/key presses into the main docker process (PID 1) how would I go about doing that.  Docker exec makes a new process.  In other words I want to execute commands into the docker attach window and NOT make a new process.  Is this even possible?
Exactly what I am trying to do. This is just more info that is not needed as much.  I am trying to make a player an operator.  The way that I do that is I type op (Username) into the java console/vm that the process made.


texts: MySQL Workbench - Error Getting DDL Object (It doesn't exist?)

texts:
The problem started when a table, started not to fetch a single SELECT. It was only a SELECT * FROM table; and it started to run and never stopped. After several attempts, all of them failed, and then I tried to drop the table (it has backups), but the SAME problem happened.

My last, and failed attempt was to rename the table and then restore a backup table.

I tried to use MySQL Workbench feature to Alter Tables but, Windows error (I use Windows 7 at my job). Windows offered me to "Try to restore the program" and it indicates me it was an "Error Getting DDL Object". Then I checked the tables information, and DDL tab was empty.

So.... I don't what to do. Is there a way to change this DDL? Or maybe another way?


texts: How do I change the default "File Name" value in SQL Server's Generate Scripts wizard?

texts:
I'm on SQL Server 2012.

The "Generate and Publish Scripts" wizard seems to remember the first path I gave it in the Set Scripting Options → Save to File → File Name box, but will under no circumstance remember any subsequent path I give it.  Where the hey is it storing this thing, anyway?

Things I have tried:

Completing the wizard with a different value
Searching the entire registry for the path the wizard likes
Searching the entire filesystem for it
Capturing all events in SysInternals' ProcMon and Ctrl+F for it

Nothing turns up in any case.  (It's using magic-storage space!)


texts: Different plans or buffers for different connections?

texts:
We had an issue in our dev environment where a procedure call timed out from the web server after 30 seconds. I traced the query and ran it manually (same params and all) from SSMS and it executed in about 2 seconds. I then ran

dbcc dropcleanbuffers
dbcc freeproccache

and after that the call from the web server also completed in time just fine.

I would suspect that the same plan should be used since I was using the exact same parameters from both connections, but I'm not sure.

My question is: Can there be different plans or buffers for different connections? Or could it have been some other side effect caused by me running the above dbcc-commands?


texts: Any way to limit the number of connection to mysql for a command

texts:
I am on Centos 6 and mysql version is 5.6. -
I have several relatively large tables and when I try to run a command it takes up all the connections to the database so the site gets down and I'll get this error:

SQLSTATE[HY000] [1040] Too many connections ...

Lets say I want to run this command:

CREATE TABLE tbl_new AS SELECT * FROM tbl_old;

Is there anyway that I can limit the number of connection that this command takes to mysql?


texts:
I have a SQL Server database (2008 R2 SP1) that was about 15 gigs. It turns out maintenance hadn't been running in a while, so I created a maintenance plan to rebuild all the indexes, they were very fragmented.

The job finished and the fragmentation is gone, but now the database is over 120 gigs! I understand that it would have used extra space to do all the rebuilds, but now that the job is done, I would think all that space would be free space, but the free space only shows as 3 gigs, so 117 gigs is being used even though the index rebuild job is finished.

I'm very confused and could use some guidance, I have the get the db back to a reasonable size, we don't have the disk space for this.

Thanks in advance!

Here's the results of both the queries posted:

  log_reuse_wait_desc NOTHING

name    TotalSpaceInMB  UsedSpaceInMB   FreeSpaceInMB
LIVE_Data   152             123             28
LIVE_Log    18939           89              18849
LIVE_1_Data 114977          111289          3688

The 3rd file is an .ndf file, that's the one showing only 3688 in unused space, but 111289 in used for about 15 gigs of data.


texts: How to calculate geographic distances using MySQL

texts:
Given a MySQL table with a series of latitude and longitude points, stored as strings, how can I find the distances between each point in the table and a given point, call it "originlat, originlon" ?

I have found numerous post on SO but the values I get from this https://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points popular piece do not even remotely match up to the values I get from online tools, such as Google Maps and this JS based tool: http://www.csgnetwork.com/gpsdistcalc.html

I also read this but it didn't mean much to me: http://www.movable-type.co.uk/scripts/latlong.html


texts: Error during mysqldump on performance_schema.query_cache_info

texts:
On a production server MariaDB 10.2.13, I actived the plugin query_cache_info.
Suddendly for 5 days, during the daily batch doing dumps (with mysqldump), I have this error :

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `QUERY_CACHE_INFO`': Character set '#4294967295' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file (22)

I did

reset query cache

But the problem come back. I don't find the query which could be the problem.
I don't know how resolve this problem.

Do you have any idea ?
Thanks for your help.


texts: Should developers be allowed to use LocalDB vs a "development" instance?

texts:
Much like the vein of the question that was posted here previously around "Should developers be able to query production databases?" I wanted to get your thoughts on another particularly annoying topic!

Many companies prevent developers from install SQL Server Express and the like on development machines, instead promoting the use of centralised development SQL Servers.

Specifically this is done to ensure:

Patch level consistency between development servers and production
Ability to prove and validate any patches on the above
Data security; only data on the development servers gets used for development
Recoverability; data is recoverable and still backed up
Collation differences that can cause problems when moved to production

To me all these arguments are particularly invalid, with perhaps the exception of the patching ones; but if a database on a local machine is purely used for development activities, and not testing, then the patching would be proven out when an application progressed through Test / UAT etc to Production.

Collation does not appear to be a valid reason, as if this was a concern for the database it should be set when it's created anyway. Only SharePoint and SCCM have issues around this as far as I know ;)

Now, assuming it's ONLY for development, and the database will not be "moved" to production and the only movements would be:

Scripts that created the database being generated for deployment to production
Backups from "production" third party systems being restored and truncated where appropriate for validation and development

Can anyone see any issues? Am I missing something?

I guess one of the biggest concerns would be the ability for local db instances coming out of date, but thats a software management issue, not a DBA one IMO.


texts: grant access to user 'root' to all databases, but least specific one

texts:
I've got the users: root, lmanager.
And too, a few databases: db1, db2, db3,...
I want to grant all privileges to user 'root' over all databases, except for the db2.
The user 'lmanager' will get all permissions only to db2.

It is possible to make it work?
it is possible to make db2 'invisible' to user root?
If it's possible, how i can do it?
Thanks in advance!


texts: Does "Inheritance" Make Sense with This Requirement?

texts:
I'm using Oracle 12c. Given an existing table (we call it TBL_M), and based on the criteria below, I was wondering if using inheritance makes sense to lay out the entities:

Information for one group of data (we call it Group One):

RATE
AMOUNT
FREQUENCY

And information for another group of data (we call it Group Two):

RATE
AMOUNT
FREQUENCY
DEPTH

So the columns needed for this become:

TBL_P

TBL_P_ID
RATE
AMOUNT
FREQUENCY
TBL_M_ID (fk)

TBL_C

TBL_C_ID
DEPTH
TBL_P_ID (fk)

For Group Two, every time we want to make an entry, we populate both tables (TBL_P and TBL_C). However, when the information is for Group One, TBL_P.TBL_M_ID is going to be set to NULL redundantly.

Would it be more efficient to split this into two separate tables, one for Group One, and the other for Group Two? e.g.

TBL_ONE

TBL_ONE_ID
RATE
AMOUNT
FREQUENCY
TBL_M_ID (fk)

TBL_TWO

TBL_TWO_ID
RATE
AMOUNT
FREQUENCY
DEPTH
TBL_M_ID (fk)


texts: MySQL Available Cars With multiple owners

texts:
I have a complex SQL question. We are building a car rental system. We have a car model table, a owner table and a link_model_owner table. In addition our bookings are kept in another table.

Scheme looks like this:

Now I need a query to search all available cars of a certain model. I've tried the following Query. However, this query will give multiple results if a linkId is added to the booking database and startDate and endDate are empty there.

I just want to have a list of unique available linkId's where the car is not rented out (NOW () between startDate and endDate).

Bear in mind that the booking database will expect to grow with 1000's a day.

SELECT link_model_owner.id,
        owners.fullName
FROM link_model_owner
LEFT JOIN bookings ON link_book_user.id = bookings.bookLinkId
INNER JOIN owners ON link_book_user.userId = owners.id
WHERE link_model_owner.isActive = '1'
  AND link_model_owner.modelid = '252'
  AND COALESCE(NOW() NOT BETWEEN bookings.startDate AND bookings.endDate, TRUE);


texts: How can I get satisfactory random numbers from the RAND function (or elsewhere)?

texts:
I'm creating a pseudo-random data set for application users to train on.

I'm surprised that if I seed the RAND() function with 1, 2, 3, etc., I get very nearly the same result from the seeded function.  However, this seems to be followed by "properly random" but repeatable values when a seed is not supplied.

SELECT   RAND(1) AS R1A, RAND() AS R1B, RAND(2) AS R2A, RAND() AS R2B,
         RAND(3) AS R3A, RAND() AS R3B, RAND(4) AS R4A, RAND() AS R4B

0.713591993212924

0.472241415009636

0.713610626184182

0.217821139260039

0.71362925915544

0.963400850719992

0.713647892126698

0.708980575436056

At first sight, it appears that I can evaluate RAND(@seed) and discard the result, then evaluate RAND() to get several really "random" numbers for my training data - so far I've planned to use four per record; I might need some more.

Is that plan going to work properly?  And, what am I looking at, here?  And, should it be in the documentation?  I haven't found it.

Documentation says this, which may be meant as a clue:

  The RAND function is a pseudorandom number generator that operates in a manner similar to the C run-time library rand function.  If no seed is provided, the system generates its own variable seed numbers.

Does the rand function in C produce similar output for similar seed input?

I think the documentation could also state more clearly that RAND(@number) followed by RAND() always generates the same numbers.  But that is what I wanted and what any experienced computer programmer will expect.

I suppose I could fill a table with random data keys obtained from https://www.random.org/
to use for the purpose - but that has drawbacks.

Update, provisional conclusion

I have the following conclusions about RAND() and for now I think I'll continue with it, but keeping alternatives in mind.

RAND(@int) sets the seed of the random number generator using the integer value given, and returns a float result which is not statistically independent, inasmuch as RAND(@int) and RAND(@int+1) produce almost exactly the same result.

Of course, RAND(@int) always produces the same result.

RAND(-@int) and RAND(@int) produce the same result.

RAND(0) is an exception: there may be other exceptions.  RAND(0) always produces the result 0.943597390424144, but it is not similar to the result of RAND(1).

RAND() called n times after RAND(@int) always produces the same n numbers.  If we call the nth number "rand(@int, @n)" -

CREATE PROCEDURE sproc_rand(@seed int, @nth int, @rand float OUTPUT) AS
SET @rand = RAND(@seed);
WHILE ( @nth > 0 ) BEGIN SET @rand = RAND(); SET @nth = @nth - 1; END

The difference between rand(@int, @n) and rand(@int+1, @n) "modulo 1" -

(1.0 + rand(@int, @n) - rand(@int+1, @n) % 1

Is a constant or nearly constant; for @n = 1 it is approximately 0.75.  For @n = 5 it is 0.991.  For @n = 6 it is 0.91.  For @n = 100 it is 0.83.

So, no, these are not good "random" numbers when a simple incremented seed is used - although they bounce around quite well for @n IN (1, 2, 3, 4).

What I'm now considering as a mitigation is using not RAND(row_id) but,

RAND(row_id * @factor_1 + @factor_2)

where @factor1 and @factor2 are constant terms and @factor_1 is approximately 10,000.  And for different projects, different factors.

So far, if I want repeatable results, the other method available (besides the Multiplicative Inverse thing, which I haven't quite understood yet) is to create a table and fill it with non-repeating random numbers from one of the other methods, then draw the numbers from that table, when required.


texts: What's the impact of running sp_updatestats in production server?

texts:
Is it safe to run sp_updatestats on an SQL Server in the production environment?

Or rather, what is the impact of updating all statistics on a sql server? Can it "choke" the sql server while it runs and cause timeouts or other issues for the users?


texts: Unable to open/connect to Oracle 12c Enterprise Manager Express

texts:
I have a just installed a new single instance installation of Oracle 12c (12.1.0.2.0 on Windows x64) with ASM.  The database itself is working OK (can be accessed locally and remotely via port 1521), however I am unable to open Enterprise Manager Express.  (I have tried different browsers).

The listener is up and seems to be configured for Ent. Mgr. Express, plus I can telnet into the relevant ports (tcp:8080 and tcps:5500), however when connecting via a web browser, no data is returned (verified using Fiddler).

Because ASM is being used, there could be two listeners, however all services are actually exposed via the Grid listener, there is no listener configured against the database home.

Any advice would be appreciated as I am not sure where to look next.

"lsnrctl status", "lsnrctl services" output and listener.ora contents are given below.

Thanks,

Chris

"lsnrctl status" output is as follows:

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 15-JUN-2015 23:07:45

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cba***.cbailiss.com)(PORT=1521)))
STATUS of the LISTENER
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
Start Date                15-JUN-2015 23:02:00
Uptime                    0 days 0 hr. 5 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\grid\cbailiss\product\12.1.0\grid\network\admin\listener.ora
Listener Log File         C:\app\grid\cbailiss\diag\tnslsnr\cbaz***\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cbaz***.cbailiss.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.5)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=cbaz***.cbailiss.com)(PORT=5500))(Security=(my_wallet_directory=C:\APP\ORACLE\admin\orcl\xdb_wallet))(Pre
sentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=cbaz***.cbailiss.com)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+asm", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

listener.ora contents:

listener.ora Network Configuration File: C:\app\grid\cbailiss\product\12.1.0\grid\network\admin\listener.ora
Generated by Oracle configuration tools.

VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cbaz***.cbailiss.com)(PORT = 1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

"lsnrctl services" output:

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 16-JUN-2015 00:15
:04

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cbaz****.ad.cbailiss.com)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+asm", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:1 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: CBAZ****, pid: 1804>
         (ADDRESS=(PROTOCOL=tcp)(HOST=cbaz****.cbailiss.com)(PORT=51202))
The command completed successfully


texts: Creating a structure to allow for optional teams of people rather than just individual people

texts:
I currently have a database set up that has tables as follows:

Developer
    id (PK)

Project
    id (PK)
    developer_id (FK)

/* and a number of other first or second level tables related to the Project table. */

Currently each developer can have one or more projects in the project table.

I am now wanting to provide an option for having team management and a team of developers for companies that want to manage entire teams. However, still want the original method of doing things if teams are not needed.

So far I have come up with the idea to implement things like this:

Developer
    id (PK)

Company
    id (PK)
    name

CompanyDeveloper
    company_id (FK)
    developer_id (FK)
    manage_teams (bool) - allows user to create, delete, and manage teams and team members

Team
    id (PK)
    company_id (FK)
    name

TeamDeveloper
    team_id (FK)
    developer_id (FK)

ProjectDeveloper
    project_id (FK)
    developer_id (FK)
    view - allows the developer to view a particular project
    edit - allows the developer to edit a particular project

/* Modified project table to add team_id */
Project
    id (PK)
    developer_id (FK)
    team_id (FK) - can be null for those developers working by themselves and not in a team for a particular company.

Am I on the correct track of thinking here? Is there anything that I am doing wrong or that I could implement in a better way?

Requirements

As requested, my requirements are as follows:

Allowing individual developers to develop one or more projects (this is what I currently have in place)
Companies may purchase a team account in order to manage teams of developers.
One or more people in the company are able to manage teams which includes creation of teams, adding developers to teams, and assigning teams to projects.
Some team developers are able to view the project only, whereas others are able to edit them.

More detailed specs

As requested by SQLRaptor, here are some more specs:

Developers

A Developer is a Person which is identified by developer_id.
A Developer contributes to zero or more projects
A Developer may sign up as an individual developer or may be employed by a company. (I see this as a problem source)
A developer may be assigned to zero or more teams

Companies

A Company is a legal identity which is identified by company_id.
A Company hires teams of one or more Developers.
A Company owns Projects
Developers may be employed by Companies or may be signed up as an individual developer (again this is a problem source)

Teams

A Team is a logical container for Developers which is identified by team_id
A Team can be assigned to zero or more Developers.
A Team can be assigned to zero or more Projects.
A Team belongs to a Company.

Projects

A Project is a collection of tasks identified by id.
A Project may be assigned to zero or more Teams
A Project belongs to either a Company or individual Developer. (This is a problem source)

Questions in regards to the problem  sources:

Should individual developers be required to sign up as a Company?
Should individual developers be assigned a default Team? (In the real world, Teams only exist when there is a Company)?

If you have any other questions please ask.


texts:
In general when I design tables I always include a single unique field, even if it there is already a unique combination of columns available to create a primary key. This field is usually either a UUID (if application-generated) or a sequence (if database-generated).

Some questions:

are there any downsides to this approach (apart from storage)?
the sole exception to this rule is (for me) the join table that exists only to join other tables. Is there an argument to be made that a join table should also have a single field acting as primary key?


texts:
We had two MySQL 5.6.35 Server in our organization working as Master/Slave replication. The replication was working fine in this situation. But we have a new requirement to place another server which will be replicated from the slave server.

For example:

Server1 - Will work as master

Server2 - Will work as slave in Server1 perspective and will work as master in Server3

Server3 - Will act as slave and will receive replication from Server2

Kindly help me on that.


texts: Multiple SQL Server instance install question for AlwaysOn

texts:
I have a 2 node WSFC cluster with SQL Server 2014 installed on each for AlwaysOn.

This is working fine. My question is can I install another instance of SQL Server 2014 on these nodes for another AlwaysOn configuration.

I have a requirement that Skype for Business (SFB) has to be on its own instance to be supported and can't be installed on the existing AlwaysOn already up and running for some other DBs.


texts: Can I use an RMAN Backup of DB1 to replace DB2

texts:
I have a backup of an Oracle 11g database on Linux, that is a version that is 'prescrub' needed by other users.  This is for an ACTIVE database that is now scrubbed, so I do not want to use it as the 'active' database for the clone.

I have a second database that is no longer needed, to which I want to overwrite with the 'prescrub' backup of DB1.  Can I restore the PRESCRUB backup of DB1 over DB2?  If so, what are the appropriate commands or modifications needed?


texts: SQLCMD is not working in pre-deployment script in sql project

texts:
I have sql project in visual studio and I created pre-deployment script

/*
 Pre-Deployment Script Template
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script.
 Use SQLCMD syntax to include a file in the pre-deployment script.
 Example:      :r .\myfile.sql
 Use SQLCMD syntax to reference a variable in the pre-deployment script.
 Example:      :setvar TableName MyTable
               SELECT * FROM [$(TableName)]
--------------------------------------------------------------------------------------
*/

:setvar TableName MyTable
SELECT * FROM [$(TableName)]

but Iam getting an error at :setvar line

Incorrect syntax near ':'.

what am I missing ?


texts: What does OPTION FAST in SELECT statement do?

texts:
I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and I'm still confused about it. According to MSDN:

Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

For me that does not make much sense but basically the query can get the first XXX rows really fast then the rest at normal speed?
The Microsoft Dynamics query that got me thinking on this is:
select pjproj.project,pjproj.project_desc,pjproj.customer,pjproj.cpnyid
from pjproj WITH (NOLOCK)
where project like  '%'
order by project OPTION(FAST 500)

Can anyone explain exactly what this query hint is doing and its advantage over not using it?


texts: Help with understanding programming time/difficulty - SQL Data dump vs SSIS Packaged report

texts:
I manage a team of data reporting analysts.  One of our main sources of data is run by another segment.  It is a SQL database, but they will not allow direct access to the data.

We have 3 csv files that are run automatically every evening that provide us with the bulk of the data we need to run reports. Recently new fields were added to the database and we need access to the information in those fields.

The original plan was that they would add the new fields to our existing data dump files or provide us with an additional data dump file with the new information.

Today, I was informed that they do not have the capacity to create or modify the data dump files, but that they can provide SSIS reporting, allowing us to open the front end of the database and export the data.

I need to know if creating an SSIS Reporting package that is exportable from the front end of the database is truly easier and less time consuming to develop than modifying or adding a package that will dump the files into a folder.

The issue I have is that we are attempting to automate all of our work.  Direct access would be the easiest way to automate reporting,  csv files cause some issues with automation, but can be automated.  Having to open the front end database application and log in and then export the data to excel is crippling my ability to automate our processes.

My experience is that one automation or another didn't really make a difference, but it has been a long time since I managed a data collection application.
I would love to hear from anyone who currently works with data collection systems and could provide me with the knowledge needed to either accept this solution or push back with facts and solid information on the differences in creating an automated report or an automated data dump.

The team creating this extract specifically said they have  the capacity to build an "SSIS" report package so the data can be exported from the front end of the database, but they do not have the capacity to build a new data dump with the new data or append the new data to the existing data dump. The new fields are all associated with the user ID.

They will not allow me access, but if I were creating a database with normalized tables this would be a straight dump of the user data joined to the supervisor table - providing historical supervisor effective and end dates. When you create a data dump or an extract executed from the front end of the database, would you use the same SSIS package - the difference being that one is scheduled and one is running on live data on demand?


texts:
Hi I'm trying to find a way to ensure deadlines inputted are later than the current time and date.
Currently I'm attempting this with a rule.

CREATE OR REPLACE RULE "DeadlineInput" AS
    ON INSERT TO "ComLog"
   WHERE new."Deadline" < now() DO INSTEAD  SELECT "ThrowError"('New deadline must be after current time'::bpchar) AS "ThrowError";
COMMENT ON RULE "DeadlineInput" ON "ComLog" IS 'Ensures deadline input is after the current time.';

I'm using a separate function to try to throw an error as when I tried to do it within the Rule it failed as the language was wrong.
The following is my function.

-- Function: "ThrowError"(character)

-- DROP FUNCTION "ThrowError"(character);

CREATE OR REPLACE FUNCTION "ThrowError"("ErrorString" text)
  RETURNS void AS
$BODY$

BEGIN

    RAISE EXCEPTION '%', "ErrorString";

END;

$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;
ALTER FUNCTION "ThrowError"(character)
  OWNER TO reuben;

This always produces the error message regardless of the date and i don't know why. As a side question is there a way to make the string passed to my function contain the current time. Something like "ThrowError"('New deadline must be after current time %' now())

Thanks


texts: How to fix "Access denied for user 'root'@'localhost'" error for MySQL using Mac OS X?

texts:
After reading a few of the similar problems to mine and none of them working, I thought I'd attempt to be more specific to see if my root problem needed to be fixed differently than the others.

I've been using MySQL for about a year now and it's always worked fine -- but I got a new Macbook Pro (10.9.3) yesterday, and everything migrated and runs great except for MySQL. It's running:

My root user is enabled and I checked to see that the password is the same as my user login (and the same as it's always been) -- but every time I try to run it, whether it be through Terminal or through Sequel Pro, I always get the same:

Access denied for user 'root'@'localhost' (using password: YES)

I also traditionally use Sequel Pro -- which migrated over from my old computer without any problems, and retained the connection details its always had:

But I'm getting the same error message through Sequel Pro as I am through Terminal:

Does anybody have any ideas of things I can try? After attempting some of the other similar questions on here, I've unfortunately come up with nothing.

Thanks!


texts: CheckDB Failure on most databases in Dev instance

texts:
I have a maintenance plan on one of our infrequently used dev boxes which keeps failing on CheckDB on all user databases and most system databases. When trying to run CheckDB manually, I get the message below. I have tried to create a database snapshot and run CHECKDB from this however I am running on standard edition SQL 2014 so an error message is returned outlining that database snapshots are not supported on this version.

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

Model and TempDB when ran manually complete their checkdb fine however anyother database has issues. I have other instances on the same server which complete their checkdb routines without issue so it seems to be something isolated to the specific instance.


texts: What happens during RCSI while tempdb is being populated?

texts:
Our database/application utilizes Read Committed Snapshot Isolation so that readers are not blocked by writers, and writers are not blocked by readers. Of course that means when a row/page/table lock occurs, the current value of those rows has to be copied to tempdb.

My question is this - if a lock escalates to a table level lock on a large table, and SQL has to copy all of that data into tempdb, what happens when a SELECT statement happens on that table.

Scenario:

Thread 1 - TABLE_A - UPDATE WHERE X=Y (escalates to a table level lock)
Thread 2 - SELECT X FROM TABLE_A

Obviously if there was only 1 or 2 rows in this table, or if it was just a page lock, the select statement would execute pretty quickly. But what if TABLE_A had a few million records in it, and the table got locked. Would the select statement block while waiting for the UPDATE to populate tempdb for the current version?


texts:
I am having the following errors with the script below

  Msg 102, Level 15, State 1, Line 44
  Incorrect syntax near '?'.

  Msg 319, Level 15, State 1, Line 47
  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Can anyone help to resolve the issue, it also does not seem to loop around each db

DECLARE @command    NVARCHAR(MAX)
CREATE TABLE #worktable
(
    [Database]  SYSNAME
    ,SchemaName SYSNAME
    ,ObjectName SYSNAME
    ,StatsName  SYSNAME
    ,ColName    SYSNAME
    --,Command  VARCHAR(500)
)

exec [master].[sys].sp_MSForEachDB @command1="use [?]"
INSERT INTO #worktable
    SELECT
        '[?]'                                               AS [Database]
        ,sch.[name]                                     AS [SchemaName]
        --,OBJECT_NAME(o.[object_id],DB_ID(''[?]''))            AS [ObjectName]
        ,o.[Name]                                       AS [ObjectName]
        ,s.name                                             AS [StatsName]
        ,c.name                                             AS [ColName]
        --,''COMMAND''                                      AS [Command]
        FROM sys.stats AS s

    INNER JOIN sys.stats_columns AS sc
        ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id

    INNER JOIN sys.objects o
    INNER JOIN sys.schemas AS sch
        ON o.schema_id = sch.schema_id
        ON s.[object_id] = o.[object_id]
        AND o.is_ms_shipped = 0

    INNER JOIN sys.columns AS c
        ON sc.object_id = c.object_id AND c.column_id = sc.column_id

select * from #worktable
drop table #worktable

exec [MASTER].[sys].sp_MSForEachDB
INSERT INTO #worktable
    SELECT  'Test'      AS [Database]
        ,OBJECT_SCHEMA_NAME(sch.name ,DB_ID(''[?]''))   AS [SchemaName]
        ,OBJECT_NAME(ss.[object_id],DB_ID(''[?]''))     AS [ObjectName]
        ,ss.[name]                          AS [StatsName]
        ,''UPDATE STATISTICS ''+''[?]''+''.''+OBJECT_SCHEMA_NAME(ss.[object_id],DB_ID(''[?]''))+''.''+OBJECT_NAME(ss.[object_id],DB_ID(''[?]''))+'' ''+ss.[name]+'' WITH FULLSCAN;'' AS Command

    FROM [?].sys.stats ss

    CROSS APPLY [?].sys.dm_db_stats_properties(ss.[object_id],ss.stats_id) sp

    WHERE   (           (sp.last_updated < DATEADD(hh,-6,GetDate()))
            OR      (sp.[modification_counter] > (0.1*sp.[rows]))
            OR      (sp.[rows_sampled] <> sp.[rows])
        )

    ORDER BY sp.[rows] ASC -- does the small stuff first, makes the difference earlier'

DECLARE cmdlist CURSOR FOR SELECT Command FROM #worktable

-- Open the cursor.
OPEN cmdlist

-- Loop through the partitions
WHILE (1=1)
   BEGIN
        FETCH NEXT FROM cmdlist
           INTO @command

        IF @@FETCH_STATUS < 0 BREAK

        --EXEC (@command);

        PRINT N'Executed: ' + @command
    END;

CLOSE cmdlist
DEALLOCATE cmdlist

DROP TABLE #worktable
GO


texts: Convert hierarchy in an ordered list of rows

texts:
I tried to make a simple list that represents hierarchy of items.

Tags have simple structure like
   [Id]
  ,[Name]
  ,[ParentTagId]

I try to make the follwing result table:
ID, ParentID, Name, Lev, LevelDynamic, RowNumber
LevelDyanmic is for rendering. It contains a delta of level changes. The order of returned rows is important.

WITH cte (ParentID, ID, Name, lev, shift)
              AS(SELECT   ParentTagID,
                            ID,
                            Name,
                            1,
                            ID * 10000
                   FROM     dbo.Tag
                   where ParentTagID is null
                   UNION ALL
                   SELECT   e.ParentTagId,
                            e.ID,
                            e.Name,
                            lev + 1,
                            shift + lev * 100 + 1
                   FROM     cte
                            INNER JOIN dbo.Tag AS e ON e.ParentTagId = cte.ID
                 )
                  select cte.ID,
                     cte.ParentID,
                  cte.Name,
                  cte.lev,
                  cte.shift,
                  CAST(cte.lev - (LAG(cte.lev, 1, 0) OVER(ORDER BY cte.shift)) as int) as LevelDynamic,
                  CAST(ROW_NUMBER() OVER(ORDER BY cte.shift) as int) AS RowNumber
                  from CTE
                 order by shift asc

But that query returns me not correct results, the order of sibling elements is incorrect, it related to incorrect shift calculation. Could you help me with it ?


texts: Does TLS 1.2 for SQL Server require SHA2/SHA256 certificates?

texts:
After reviewing SQL Server compatibility with New TLS Standards and much Googling, I think we've got the updates and settings needed to disable TLS 1.0 on SQL Server 2008 R2 with .NET 3.5.1.

But I've found nothing definitive about whether our domain root certificate must be signed with SHA256, only a blog post that says:

  Also, you should be using a SSL certificate signed with SHA2/SHA256.

But is it nice-to-have or a showstopper? Don't some TLS 1.2 implementations tolerate SHA1 certificates?


texts:
In my database I have a set of custom field values that can be setup by the customer.  They can create labels for these fields and then assign values which are linked back to particular tables in the database.  I need to be able to report this information such that the labels appear as columns and the values are listed below.  I could do this with a PIVOT function except that the information in the values is not necessarily aggregatable.  (I'm making up new words)  I'm using the following code for testing.

create table custlabel (custlabelid int identity(1,1) not null, custlabel varchar(50))
CREATE table custvalue (custvalueid int identity(1,1) not null, custlabelid int not null, custvalue varchar(50), linkageid int not null)
create table linkage (linkageid int identity(1,1), blah varchar(50))

INSERT into linkage(blah)
values('test'), ('test2'), ('test3')

INSERT INTO custlabel(custlabel)
values('info1'), ('info2'), ('info3'), ('info4'), ('info5')

INSERT INTO custvalue(custlabelid, custvalue, linkageid)
VALUES(1, 'info1 value1', 1),
(1, 'info1 value2', 1),
(1, 'info1 value3', 1),
(2, 'info2 value1', 1),
(2, 'info2 value2', 1),
(2, 'info2 value3', 1),
(2, 'info2 value4', 1),
(3, 'info3 value1', 1),
(3, 'info3 value2', 1),
(3, 'info3 value3', 1),
(4, 'info4 value1', 1)

What I need to see come back is

blah, info1, info2, info3, info4, info5
test, info1 value1, info2 value1, info3 value1, NULL, NULL
test, info1 value2, info2 value2, info3 value2, NULL, NULL
test, info1 value3, info2 value3, info3 value3, NULL, NULL
test, info4 value1, NULL, NULL, NULL, NULL


texts: Database synchronization 2 times a day -MariaDB

texts:
I have 2 different servers. The first one is server1 and the other is server2. On servers, one works for websites (server1) and the other for mobile applications (server2) for security purposes. I want to add changes (insert, delete, update) to the database used on the websites to the database that the mobile application uses. I want to do this every afternoon and evening.

I've looked at other topics on this topic, but I couldn't find them.

I don't want to write the IP address of the other server (server2) to the server (server1) used by the websites (for security). That's why I can't replicate. So server2 will connect to server1 twice a day and update different data.

There can be close to 1 million records per day. I don't know if I can do it with Mysqldumps. insert and deleting all tables may be a problem.

Do you have any suggestions? How do I synchronize a database?


texts: Transaction log restore fails with error "Operating system error 5(Access is denied.)."

texts:
I have backed up transaction logs on my database and trying to restore them on another instance. But when trying to restore I receive an error: Operating system error 5(Access is denied.) and RESTORE LOG is terminating abnormally.

I guess, it means SQL Server user does not have permission for this operation, but I have already granted him public and sysadmin server roles. Could you please advise what could be the reason for this error?


texts: SSMS: set width for 'New Query' window (using MDI)

texts: Is there a way to prevent SSMS from opening an enormously wide window when I'm using 2 monitors and have SSMS spanning both monitors?  I only want the window as wide as a single monitor.  Still using SQL 2008 R2, so I have MDI enabled.


texts: Store two metric sets long or wide if frequently joined?

texts:
Given two similar tables, metrics_a and metrics_b, PostgreSQL 9.6:

create table metric_a (
    logdate date not null;
    point_id int4 not null;
    m00 numeric(9,5) not null;
    ...
    m23 numeric(9,5) not null;
    CONSTRAINT metric_a_pk PRIMARY KEY (logdate, point_id)
);
create index metric_a_point_idx on metric_a (point_id);

Each with about 150,000 point_id values with hourly measurements into m00, m01, etc. for 24 hours a day. That's about 55 million rows per year per table, and we have 10 years of data so far. There will always be only 2 metrics, a and b.

I assume I should partition by date range, which I'm working on. But in general, for queries where metrics like this are joined for at least 30 days of data, is it better to store them as:

one wide table -- 48 hourly columns, 24 for each metric
one "half-wide" table -- a point_id column and 24 hourly columns
one long table -- logdate, point_id, hour, metric_value
two tables, wide or long?
range partitioned by month, week, or metric? long or wide?

Of course the long table version is by far the easiest to deal with. It's much simpler to write queries to total the whole day, or compare several hours' metrics against each other. However, the table size for the long version could easily total over 13 billion rows with very inefficient use of storage.

I've tried using two wide tables and one combined 48-column wide table (neither partitioned), with little difference in performance.

Current queries look like this:

select a.point_id, count(*) tot_ct, :lim as lim,
    count(case when a.m00 <= :lim then 1 end) a_ct00,
    count(case when a.m01 <= :lim then 1 end) a_ct01,
    count(case when a.m02 <= :lim then 1 end) a_ct02,
    ...
    count(case when (a.m00 - b.m00) >= 4.25
        and b.m00 <= :lim then 1 end) w_ct00,
    count(case when (a.m01 - b.m01) >= 4.25
        and b.m01 <= :lim then 1 end) w_ct01,
    count(case when (a.m02 - b.m02) >= 4.25
        and b.m02 <= :lim then 1 end) w_ct02,
    ...
    avg(a.m00) av_a00, avg(a.m01) av_a01, avg(a.m02) av_a02, ...
    ...
from metrics_a a, metrics_b b
where a.logdate = b.logdate
    and a.point_id = b.point_id
    and a.logdate between '6/1/2017' and '7/31/2017'

The query above and most anything similar to it takes about 8 minutes to run. That's not too bad except I need to run several of these in a batch for different values of :lim and the 4.25 value and then combine and filter the results (currently in Python).

I can provide more details, but this post is already very long. Thanks for any help.


texts: Ownership of SQL Server Files, Register Entries & Executables

texts:
I'm working on a database server hardening project for a client of mine.  I'm getting into ownership issues when it comes to the following:

  .mdf & .ldf
  executables
  HKEY--> tree from Microsoft SQL Server on down

So my question is.....who should own the above items?  I do know that sa/dbo would be the easy answer, but I'm trying to give the least amount of privileges to these items.


texts:
I want to create a table with 6 columns : 1 blob, 5 text the number of entries can exceed 150,000 rows. Blob data will have size near 2Kb.
Is it a good idea to create this table? Would there be any performance issues?
What different methods should I use ?


texts: Looking for an efficient way to build some kind of "FIFO" table

texts:
I need to store measured data on a daily basis for a given period in the future, lets say 7 days. The current day should be always day 1.
Basic Example:

| id | day    | value |
|----|--------|-------|
| 1  | 1      | 10    |
| 2  | 2      | 20    |
| 3  | 3      | 30    |
| 4  | 4      | 40    |
| 5  | 5      | 50    |
| 6  | 6      | 60    |
| 7  | 7      | 70    |

So at night the next day (day 2) becomes day one and all data will move up one day, and new data can be added.

Basic Example:

| id | day    | value |
|----|--------|-------|
| 1  | 1      | 20    |
| 2  | 2      | 30    |
| 3  | 3      | 40    |
| 4  | 4      | 50    |
| 5  | 5      | 60    |
| 6  | 6      | 70    |
| 7  | 7      | 80    |

So the principle is kind of FIFO. I always look at 7 days in the future every night the perspective changes because tomorrow becomes today and day 7 can hold a new value.

What would be an efficient way to build something like this in postgres.
As it is now I would have to update all rows at night to move the values.
Is there a better way?


texts: Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?


texts:
SQL Server version (output from select @@VERSION): Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) (Build 14393: )

Installed sp_Blitz from Brent Ozar, stored procedure creates successfully.

Version info from the code: SELECT @Version = '7.93', @VersionDate = '20200217';

The instance has the 4 system DBs with collation SQL_Latin1_General_CP1_CI_AS and 2 user databases with collation SQL_Latin1_General_CP1_CS_AS

When I try to run sp_Blitz I get this:

  Msg 468, Level 16, State 9, Procedure sp_Blitz, Line 896 [Batch Start Line 0]
  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Appreciate any assistance in troubleshooting (I'm mainly an Oracle guy who's been handed a SQL Server for review and remembered this as a thing that might be a good start for obvious things to start with).


texts:
I have a date range column e.g. 1960-1970 in a table, I'm splitting these and copying them to new columns, I need to cast the string to an integer, the following code returns a generic syntax error, any ideas where I'm going wrong?

INSERT INTO rhp_tvp.time (
  visit_id_tmp,
  tvp_id_tmp,
  time_start,
  time_end)

SELECT
  v.visit_id,
  v.tvp_id,
  cast(LEFT(date_range, 4)AS int8),
  cast(RIGHT(date_range, 4) AS int8)
FROM tvp.visit v
WHERE  date_format = 'DMY' AND date_range NOT LIKE 'Pre%' OR date_range IS NOT NULL;

ERROR:  invalid input syntax for integer: "13/1"
SQL state: 22P02


texts: Standby logshipping fails when user is doing read operations on the database

texts:
I have a SQLSERVER 2008 instance with a standby configuration using log shipping.

The production server runs a LSBACKUP job every 30 minutes, ships it to the standby server and the standby will then LSRESTORE the shipped log.

The issue I'm having is the LSRESTORE job will fail if any user sessions are accessing the database, even though they don't have active transactions containing any write operations.

The databases are in (Standby / Read-Only) mode so there's no write operations that could be pending on it, simply developing the database tables in SSMS will block the LSRESTORE job with the following error log :

2017-02-14 09:29:22.87  *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

The only solution I have found is when this happens, I can set the database to single_user and back to multi_user to clear the error. I could also put the SQL commands in the SQLAGENT job in a step before the restore but it looks more like a hack than a fix !

ALTER DATABASE [db_name_goes_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [db_name_goes_here] SET MULTI_USER

Can my standby be protected from this issue in any way ?


texts: Tracking SQL queries and rows operated on for documentation/audit

texts:
Is there a way to record the SQL queries (and rows that were acted upon) in a session for documentation/audit purposes?

For example, in deleting duplicate rows from a table I use the following SQL which gives me a sequence of SQL that I can refer back to later in case of issues with the deletion.

select 'delete from table1 where rowid=''' || rowid || ''' and column1='''
|| column1 || ''' and column2=''' || column2 || '''' from table1 a where
rowid > (select min(rowid) from table1 b where a.column1 = b.column1 and
a.column2 = a.column2);

However this takes longer to build then just issuing the queries outright. I work across a lot of different tables which I don't own so adding triggers is unfortunately not an option.


texts: I am Trying to Write an Access Query to Get a person having a First Name and Last Name but not all People having any of those names

texts:
I am using Microsoft Access. In my search form I have 3 text boxes that I enter text to search for: First Name; Last Name; Last 4 of SSN. With the current query when I enter text into multiple boxes it finds all the people with those names and/or last 4 of SSN. For example, I want to find Bill James with last 4 0111: If I enter text into all 3 boxes I end up with all the people that match my search parameters.

First Name      Last Name      Last4
Bill            Atherton       0123
Bill            James          0111
Bill            Wallace        0122
Frank           James          0101
Henry           James          0011

So you see I get all the people having a name of Bill and all the people having a name of James. I want to write a query so that if I enter Bill in the "First Name" text Box AND James in the "Last Name" text box I should only get people named Bill James not the rest of the people having either of those names. But I still want to be able to enter into one text box if I want to.

Here's my code so far:

SELECT Appointment.[First Name], Appointment.[Last Name], Appointment.DOB, Appointment.Last4
FROM Appointment
WHERE Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value
            OR Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value
            OR Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value
ORDER BY [Last name], [First Name];


texts: Specify ICU collations as `ENCODING`, `LC_COLLATE`, and `LC_CTYPE` in Postgres 10

texts:
Postgres 10 gains the ability to use International Components for Unicode (ICU) collations rather than depending on host OS implementations. See More robust collations with ICU support in PostgreSQL 10 by Peter Eisentraut.
So how exactly does one specify an ICU collation when creating the database?
I did read a tip in the CREATE DATABASE to use template0 rather than template1 if template1 might have been created using encoding and collation other than what you want for your new database. And when using template0, you need to enable data connections to override the default.
And I read in Postgres 10 doc page for Collation Support that:

und-x-icu (for “undefined”)
ICU “root” collation. Use this to get a reasonable language-agnostic sort order.

So it seems und-x-icu might be a good value to use when establishing a new database that will include multi-lingual text. When a certain language is known to focus on a particular language, the SELECT command can specify another collation such as German de-x-icu. Otherwise, fallback to the default of und-x-icu.
So I tried the following SQL to create a database in Postgres 10 Beta 2.
CREATE DATABASE timepiece_
TEMPLATE 'template0'
ALLOW_CONNECTIONS TRUE
CONNECTION LIMIT -1
ENCODING 'UTF8'
LC_COLLATE 'und-x-icu'
LC_CTYPE 'und-x-icu'
;

Fails with an error:

[42809] ERROR: invalid locale name: "und-x-icu"

So I stuck on an appended .utf8 as seen in the doc for the old-fashioned collation syntax.
CREATE DATABASE timepiece_
TEMPLATE 'template0'
ALLOW_CONNECTIONS TRUE
CONNECTION LIMIT -1
ENCODING 'UTF8'
LC_COLLATE 'und-x-icu.utf8'
LC_CTYPE 'und-x-icu.utf8'
;

Fails just the same:

[42809] ERROR: invalid locale name: "und-x-icu.utf8"

Was my Postgres cluster built with ICU libraries? Here is the result of calling pg_config. The last line is 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'. I guess that means my build includes the ICU libraries but I am not sure.

/Library/PostgreSQL/10Beta2/bin/pg_config --configure
'--with-icu' '--prefix=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx' '--with-ldap' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--with-bonjour' '--with-pam' '--enable-thread-safety' '--with-libxml' '--with-uuid=e2fs' '--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include:/opt/local/Current/include/security' '--docdir=/mnt/hgfs/pginstaller.pune/server/staging_cache/osx/doc/postgresql' '--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 'CFLAGS=-isysroot /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.8.sdk -mmacosx-version-min=10.8 -arch i386 -arch x86_64 -O2' 'LDFLAGS=-L/opt/local/Current/lib' 'ICU_CFLAGS=-I/opt/local/Current/include' 'ICU_LIBS=-L/opt/local/Current/lib -licuuc -licudata -licui18n'

➠ How does one specify the ENCODING, LC_COLLATE, and LC_TYPE to use the new ICU collations?
➠ What ICU collation names you would suggest for English-oriented databases? For Western European multi-lingual databases?
➠ What are the possible ICU collation names?


texts: Should a table be split if only a particular column gets updates?

texts:
I have a table in my MySQL 5.7 database, which has 9 columns. One column is an AUTO INCREMENT PRIMARY KEY, where as the other 6 contain some data, which isn't going to be updated in a year. Now, the last 2 columns are of type JSON and they are going to be updated frequently, multiple times in a second(could be 5 times a second, and as system scales could be much more than that).

My question is, should i split my table into 2 tables, having a one to one relationship? So that all the non changing data goes in one table and the 2 columns which are to be updated frequently go to the other table.


texts: Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012

texts:
I've restored a database backup in a new SQL Server 2012 server from a .bak made in SQL Server 2008 R2, and one of the tables has it's primary key index disabled. This doesn't happen when I restore to a 2008R2 server.

The structure of the table is:

CREATE TABLE RouteSegment (
   RouteSegmentID bigint IDENTITY(1,1) NOT NULL,
   Representation geography            NOT NULL,
   LastTime                            int NOT NULL,
   SourceStop1ID                       bigint NULL,
   SourceStop2ID                       bigint NULL,
   Length  as ([Representation].[STLength]()) PERSISTED,
   CONSTRAINT PK_RouteSegment PRIMARY KEY CLUSTERED (RouteSegmentID ASC)
 )

Is this a known problem in SQL Server 2012, or is there something I've missed?

I know that I can fix the problem with an index rebuild, I ask because it's surprising behavior, and want to know how to prevent it.


texts: Tail-log backup options when restoring to a new database

texts:
I want to restore a sql server database(sql server 2014) to a new database to see if I can recover deleted data.

I need to restore from about 2 months ago, so I will need to use the timeline and pick the correct trn point in time.

I'm restoring to a new database and don't want to mess with or interrupt the source database at all.

So should I leave the options under Tail-log backup (Take tail-log backup before restore and Leave source database in the restoring state) unchecked or checked?

Thanks!


texts: Session ID in SQLCMD running multi-batch script

texts:
My script contains multiple GO keywords. I know that SQLCMD interprets GO as a batch delimiter, so the code is not run as a single block but instead each part between the delimiters (a batch) is run separately. My question is, are all batches executed in the same session?

I tried testing this using a simple script like this:

SELECT @@SPID
GO

SELECT @@SPID
GO

SELECT @@SPID
GO

I then ran the script from the command line using this command

SQLCMD -S MyServer\Instance -E -i MyScript.sql

and got this output:

------
    62

(1 rows affected)

------
    62

(1 rows affected)

------
    62

(1 rows affected)

It seemed to answer my question in the affirmative, but then I ran the script again and got exactly the same output, i.e. all values were 62 again. Apparently the two separate executions of SQLCMD could not possibly run in the same session, they just happened to receive the same session ID. But that in turn makes me think that the same @@SPID value return by a single run might not necessarily mean it was the same session either. Each batch could be executed in a different session that just happened to receive the same ID, similar to how different executions of SQLCMD were running in different sessions with the same ID.

Therefore, my question still stands: are multiple batches of the same script executed in SQLCMD in the same session or not? Is there a way to determine this?


texts:
I am not a DBA so please be patient with me...

Is it possible to turn on a trace on SQL Server which will tell me any SQL statement that takes longer than 2 seconds to execute, when it happened and what the SQL was? Is it possible to set the time to 2, 3 , 4 , whatever... ?

Thanks


texts: The right way to model multiple FACTs schema

texts:
Background
I'm in a process of designing a database (using a STAR schema).
There are three tables to model: products, tests, states.
The database will be used to store results of tests conducted on products (in a great simplification). There can be many tests pointing out to a single product but each test is unique (they are not shared among products). Besides, I need to record the current state of the product, at the time when the test was conducted. Let's assume that the state of a product describes its current location and owner, which are changing very often. That will most likely involve SCD lvl 2 - to track the history of state changes and to be able to locate a product with all its tests as well as the states it had during these tests.
Problem
I'm not entirely sure how to model this problem. It seems obvious to store every test in a FACT table. This table would then consist of thousands of transactions. On the other hand, there will also be hundreds (and later thousands) of products, so I should probably keep them in a second FACT table. Then, there will also be thousands of state changes, so in order to record their entire history, I would need to keep them in a ... FACT table as well? I've been told that FACT tables are typically used to store multiple-rows data but on the other hand where are the DIMs in this model?
I also don't know how to model the relationships between these tables. Products - states is a 1:* relationship. Products - tests is a 1:* as well. Finally, states - tests is a 1:* too. I would then go with linking products to states and then states to tests (products 1<-* states 1<-* tests), what would allow me to find all states for a particular product and all tests (in all states or in a selected state). What do you think about that? The problem here is that, as I keep adding states, I have two options: either keep duplicating products in the products table (with added "recorded_timestamp" column) or use a SCD lvl 2 in states table, pointing out to the products table with a FK, but this would effectively make the product table a DIM!
Any help here would be very appreciated.


texts: Is it possible to tell if an incoming connection is using an SQL Server Alias?

texts:
Using SQL Server Configuration Manager to Create a Server Alias for Use by a Client is cool tool used in many cases when it works

In my DBA position I do not have access to the application server, to see if the SQL Server Configuration Manager or the SQL Server Client Network Utility (not sure this is the correct link) was used to create an alias.

Is there any way I can use SSMS, or any logs, events, etc to identify that an incoming connection is connecting with an Alias?


texts:
I added a new user to posgresql (amanda) which is the same as my linux system user. At the console, I can open psql just fine -- I don't need a password because I'm already authenticated as amanda. But when I try to set up pgAdmin III without a password, I get an error:

An error has occurred:

01:18:47 PM: Error: Error connecting to the server: fe_sendauth: no password supplied

And if I provide my system password, I get a password error:

An error has occurred:

01:15:30 PM: Error: Error connecting to the server: FATAL:  password authentication failed for user "amanda"
FATAL:  password authentication failed for user "amanda"

How do I get pgAdmin3 talking to postgres as amanda?

This is all @ localhost. I read this:

PgAdmin III - How to connect to database when password is empty?

but the answer there discusses trust authentication. My read of the auth methods section of the manual suggests that what I want is for pgAdmin3 to use Ident Auth.


texts: Sql server database restore vs index rebuilds

texts:
In SQL Server, if Database Restore Time takes less time compare to Index Rebuilds OR Reorganize  . Can we  Choose Restore as a option for DE-fragmentation.
If its not a 24/7 database.


texts: What is a practical use of FOR STATEMENT triggers?

texts:
I understand very well the idea behind triggers, how and when to use them.

I also see the practical use of a FOR EACH ROW trigger.

But I lack to find a concrete example of when I would use a FOR STATEMENT trigger. What could I do with it?

Can anyone give me an example of a real life use of that?


texts: MS SQL - Find Stored Procedure (Dynamic) Related to Table in Database

texts:
I have stored procedures like this:

CREATE PROC spSample (@departmentId INT)
AS

DECLARE @q NVARCHAR(500)

SET @q = N'SELECT * FROM Employees WHERE 1=1'
IF @departmentId > 0
BEGIN
  SET @q = @q + ' AND DepartmentId = @departmentId'
END

EXECUTE sp_executesql @q, '@deparmentId INT', @departmentId

It is possible to find all related SPs like this one to the table "Employees"?


texts: Is it possible to join servers that are already in a database mirroring session into an Active Directory domain?

texts: We have created a SQL Server database mirroring session on two workgroup servers(not in any domain). Now we are asked to join those two servers into a domain. Is this possible without rebuild the whole mirroring architecture?


texts:
My databases have been working fine on my Windows Server 2016 standard. The database management system is Microsoft SQL Server 2017 Express.

Today I added a new table and content, all is fine. I tried to open the database diagram and received an error message:

And in case the image doesn't work, the message is:

  Cannot execute as the database principal because the permission "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517)

Nothing has changed on my server - no updates, etc.

I can access all the tables. When I look at the account I log in, I can see I am a the database owner, which other posts suggest to do - but I wasn't previously so unsure as to why this would help.

This issue affects every single database I have.

The only difference is I now create a daily back up from a scheduled task with the following script

BACKUP DATABASE DBName TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Backup\DBName .bak'
WITH NOFORMAT, INIT,  NAME = N'DBName Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The result of

SELECT ORIGINAL_LOGIN(), USER_NAME(), SUSER_SNAME();

shows


texts: SQL Server recovery with LVM snapshot as base

texts:
Is it possible to fully recover a database from a snapshot plus transaction log backups? By snapshot I mean a crash-consistent clone of the Server - in this case an LVM snapshot that is shipped offsite while the live server continues to operate and generate logs.

--edit

To clarify, the LVM snapshots are of the entire Virtual Machine including OS, SQL Server and everything else. This snapshot is then transported to a remote site and successfully started on the host. I know I can at this point restore a full backup and recover with the logs, but I wondered if it was possible to skip the full backup/restore step and use the snapshot as a base?


texts:
When I run this query from psql shell everything works fine:

ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'somePassword';

but when I try exactly the same query from python (with psycopg2 package)
authentication fails when I try to connect with the password.

what can go wrong? I tried simple passwords with no non-ansi characters etc...


texts: Can the replicated database be the publisher with SQL Server

texts:
I need to setup replication for a Client but their production database server doesn't seem to have the components installed to enable publishing.

Is it possible to setup what should be the subscriber as the publisher and pull changes from the master database? And is this going to cause problems?


texts: What's the easiest and most accurate way to visualize parallel thread usage in SQL Server?

texts:
Following up on my other question here, I'd like an easy way to visualize the answer.

I've tried using a query like this, but the number of "running" threads can exceed DOP sometimes.

SELECT    ost.session_id,
          ost.exec_context_id,
          ost.scheduler_id,
          qp.node_id,
          w.worker_address,
          qp.physical_operator_name,
          ost.task_state,
          qp.row_count
FROM      sys.dm_os_tasks AS ost
JOIN      sys.dm_os_workers AS w
    ON ost.worker_address = w.worker_address
LEFT JOIN sys.dm_exec_query_profiles AS qp
    ON w.task_address = qp.task_address
WHERE     ost.session_id = 57
AND       ost.task_state = 'RUNNING'
ORDER BY  ost.exec_context_id, ost.scheduler_id, qp.node_id, w.worker_address;

I've also tried using the query_thread_profile Extended Event, but for a plan with multiple parallel branches, it shows the same N number of Thread IDs. This seems contrary to the answer given, since DOP doesn't explicitly limit the total number of threads used. For a query running at DOP 4, it looks like this. I'm sure that the same four threads (plus coordinator) don't get used for all branches and operators.

So like, running a query like this at DOP 8 gives me three parallel branches.

SELECT COUNT(DISTINCT t1.Data)
FROM dbo.t1
JOIN dbo.t2
ON t1.Id = t2.f_Id
JOIN t3
ON t1.Id = t3.f_Id
OPTION(FORCE ORDER, HASH JOIN);

But sometimes my query returns this:

And sometimes it returns this:

Here's some sample data:

CREATE TABLE dbo.t1 ( Id INT IDENTITY PRIMARY KEY CLUSTERED, Data INT NOT NULL );

CREATE TABLE dbo.t2 ( Id INT IDENTITY PRIMARY KEY CLUSTERED, f_Id INT NOT NULL );

CREATE TABLE dbo.t3 ( Id INT IDENTITY PRIMARY KEY CLUSTERED, f_Id INT NOT NULL );

INSERT dbo.t1 WITH (TABLOCKX) ( Data )
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 2000
FROM (
SELECT TOP 1000000 1 AS num
FROM master..spt_values AS sv
CROSS JOIN master..spt_values AS sv2
CROSS JOIN master..spt_values AS sv3
) AS nums

INSERT dbo.t2 WITH (TABLOCKX) ( f_Id )
SELECT TOP 15000000 t.Id
FROM dbo.t1 AS t
CROSS JOIN dbo.t1 AS t2

INSERT dbo.t3 WITH (TABLOCKX) ( f_Id )
SELECT TOP 25000000 t.Id
FROM dbo.t1 AS t
CROSS JOIN dbo.t1 AS t2

Thanks!