How to clear temp database content in SQL Server? As the title implies, I am going to clear the content of my temp database in SQL Server.  SolarWinds is generating a critical alert and I want to clear the tempdb workfiles and worktables.

How can I do this? Can I restart the MSSQLSERVER service to clear the content?

If you truly feel you need to clear it, then yes, restarting SQL Server will clear it.  However, that will likely be a temporary solution and you'll be back in the same condition after a few days.  Restarting SQL Server is the only way to clear the tempdb content from a SQL Server perspective.  Another possible alternative would be to restart the application that is creating the objects in tempdb.

It's impossible for us, given the bit of information that you provide, to know what the best course of action is.  Some things to consider:

Just because SolarWinds says something is a problem doesn't necessarily mean it's a problem.  Many monitoring tools are set with defaults that will send "critical" alerts for things that are 100% expected on a given server.  For example, I would expect a Dynamics AX SQL Server to trigger many of the default tempdb thresholds of most monitoring tools.  If there is no significant tempdb contention and it's not running out of space, then there's probably nothing critical.   Is the application that is using the SQL Server having performance issues?  Is anyone complaining about performance?  If not, then the SolarWinds alerts aren't reflecting reality and thresholds maybe need to be adjusted.  One thing to keep in mind is that many of these applications were initially coded and tested when storage was much slower.  If you have tempdb on SSDs, they can take much more beating than a couple of mirrored local SCSI disks that were probably used when SolarWinds decided what thresholds to use.
Do you have tempdb sized appropriately?  Some application make heavy use of tempdb and will need far more than other applications typically use.  If tempdb is just running out of space, you may just need larger files for tempdb
Do you have one tempdb database file for each processor, with a maximum of 8?
Do you have trace flags 1117 and 1118 enabled if you are using SQL Server 2014 or earlier?  If not, research them--if you have an application that has significant tempdb usage, you'll probably want to enable these (I see you have used the sql-server-2016 tag, so this is for the benefit of other readers).
Is there a defect in an application that is causing it to "leak" tempdb objects?  If the number of tempdb tables and usage is constantly climbing and never stabilizes, then it would be advisable to investigate this.

If you can't resist the urge to shrink it, shrink it with TRUNCATEONLY.  TRUNCATEONLY may shrink it a lot, or none--it just depends on where the last used extent is.  If you shrink it to a specified size, it will start reorganizing the data (i.e., fragmenting it) in order to move data from the end of the file to any free space that exists in the middle. In order to do that it will require locks and your users/application my have significant performance impact.  And you never know how long it's going to take to reorganize that data.

If you have more info to add to the question about the exact metric(s) SolarWinds is alerting on that would be helpful.


Using max result as a value for a predicate in SQL Say I want to find all the employees that earn more than all of the employees of Google, is it possible to use the result from max as if it was a single value and not a table like so?

select person-name
from Works
where salary > ( select max(salary)
        from Works
        where company-name == "Google" )

(Works has the columns person-name, salary and company-name)

Yes this is possible. Your query has several syntax errors however.

a dash in an identifier is invalid. I used an underscore instead in the below example. If you did create the columns that way, you will need to quote them, e.g. "person-name"
the equality operator in SQL is =, not ==
String constants need to be put in single quotes (') not double quotes

It is possible to use the result of the max subquery as a single values because it is a single value. To be accurate it is a subquery that because of the max() returns a single column and a single row.

select person_name
from Works
where salary > (select max(salary)
                from Works
                where company_name = 'Google');

You could use an alternative way with > ALL instead of >, with a subquery that doesn't have an aggregate function and returns many values (many rows but still a single column):

select person_name
from Works
where salary > ALL (select salary
                    from Works
                    where company_name = 'Google'
                      and salary is not null );

Note the salary is not null condition in this second query. Nulls must be taken out of the comparison because each of them would evaluate the > operation to unknown rather than true or false, and as a result the whole > ALL condition result would be unknown and you would get no rows in the output.


Create a custom db roles that uses a lookup table Can you create a custom DB_role like truncate, select, insert, delete permission to tables that are stored in another table(like lookup) rather than altering each table? just wondering I'm not a DBA sorry, the reason why is the old database has only one schema.
the only thing I can think of is creating an SP and do a loop on that table.

Thanks

Roles need to be explicitly defined. You can't tell the engine to just look up the role membership and/or rights in a table.

You'll need to manually create the roles/permissions with dynamic SQL from the table, and come up with some kind of plan to keep them in sync whenever the data in the table changes (you can use a trigger or, if a delay is possible, a scheduled job).

You mention schemas, and I'm not sure how relevant that is, but obviously an easier way to manage this would be to put all of the relevant users in a role, put all the relevant objects in a schema, and GRANT permissions on the schema to the role. If you have exceptions you can implement DENY.


Use plpgsql to count records selected for output AND output those records My query SELECTs 1 row from table1, which has a number of fields plus one very long text field. It JOINs with a second table2, that may have many matching corresponding rows.
I would like to be able to spread the long text field, from table1, out over multiple lines (the number determined by the matching rows in table2).

In the table above there is one row from Table1 where the key is table1_key = 'DU' and year = '2017'.  Then there are four rows from Table2 with matching keys that include the programme_code and course_code. The field long_text_field, which I want to spread over multiple lines, comes from Table1.

Is it possible to extract a total row count for records that have been selected for output and still output the rows?  Meaning that I want to see a count of 4 from the above and be able to work with this number in the displayed output.  So that the result looks something like this:

I don't get your example, but if that's just an illustration of the question in the heading, the answer is a window function:

SELECT /* all your columns and expressions */,
       count(*) OVER () AS total_count
FROM /* the rest of your query */

This will append a column to each result row with the total number of result rows.

The window function is called after GROUP BY, but before ORDER BY and LIMIT.


Cassandra querying without full primary key As far as I understand there are no secondary indices with multiple columns, only single columns.

So how do I solve the index problem I'm having? This is my table:

CREATE TABLE IF NOT EXISTS logstv.messages (
    timestamp timestamp,
    channelid bigint,
    userid bigint,
    message text,
    PRIMARY KEY ((channelid, timestamp), userid)
) WITH CLUSTERING ORDER BY (timestamp DESC);

But this is the error currently:
Missing CLUSTERING ORDER for column userid

I want these things:

SELECT WHERE with channelid, userid, timestamp with an order by timestamp and limit
SELECT WHERE with channelid, timestamp with an order by timestamp and limit

Is this possible?

The only option I currently see is setting timestamp as the primary key and then sorting through the rest in my software, which seems very expensive. Or having 2 tables for my data which i want to very much avoid because the data will grow very fast and get big.

Example queries:

SELECT message, timestamp
        FROM logstv.messages
        WHERE userid = ?
        AND channelid = ?
        AND timestamp >= ?
        AND timestamp <= ?
        ORDER BY timestamp DESC
        LIMIT 100;

SELECT message, timestamp
        FROM logstv.messages
        WHERE channelid = ?
        AND timestamp >= ?
        AND timestamp <= ?
        ORDER BY timestamp DESC
        LIMIT 100;

Yeah, It is possible with 'ALLOW FILTERING' option. BUT NOT ADVISABLE (well explained in this link). Schema design in Cassandra is majorly Query Specific (i.e) design your schema so that it is able to answer a query.
If you want to query on different attributes, go for individual schemas for each one.


is camel case for column names safe? I have a database where column names are all camel cased. No problems encountered.
Will that be a problem under some scenarios, as per recent versions of MySQL, Postgres?

The only cases I can think of for MySQL where case sensitivity could be an issue would be

Case #1 : Data Dictionary entries for tables by table_name (See my post Is it okay to have different lower_case_table_names value on master and slave?)
Case #2 : Migrating InnoDB tables with referential constraints from Linux to Windows (see my posts MySQL case sensitivity for table names and Is it possible to use the same directory for 2 MySQL servers?)

In those cases, the table name can cause problems. As for column names, it should not be an issue.


Fastest way to create local copy of live InnoDB MySQL database? We have a MySQL database (InnoDB) on our live Ubuntu 12.04 server which has grown quite large (25gb+).

Every month or so we need to copy this database to our development machines to work on locally.

Previously we have exported the database with MySQL Workbench, downloaded the .sql file to our development machines, and then imported it, again with MySQL Workbench. The problem is now the database is so large, we can't do the restore quick enough for it to finish over the weekend! So on Monday morning we have a half-imported database to work with.

What do other people do in this situation?

The only solution I can think of so far, is doing the restore to an unused machine, then copying the data to all our development machines (5 of them). Would this work?

You can take a backup from the live MySQL with Xtrabackup.

Percona provides deb repository for Ubuntu. To install the repo for Ubuntu 12.04 follow instructions:

Install the key:

# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Add the source file:

# cat /etc/apt/sources.list.d/percona.list
deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

Update the local cache:

# apt-get update

And install xtrabackup:

# apt-get install xtrabackup

To take a copy of the database run following:

    # innobackupex .

It will create a directory like "2010-03-13_02-42-44". Copy that directory to a development box, apply redo log:

    # innobackupex --apply-log /data/backups/2010-03-13_02-42-44/

Then the directory is ready to use. Copy it back to MySQL datadir and fix ownership:

    # cp /data/backups/2010-03-13_02-42-44/ /var/lib/mysql
    # chown -R mysql:mysql /var/lib/mysql

Then you may start MySQL.

More details you can find on http://www.percona.com/doc/percona-xtrabackup/2.1/how-tos.html


Why is a .bacpac file so small compared to a .bak file of the same database? I've been doing backups of my SQL Server 2014 Express databases for import to other servers and noticed a difference in file size between .bacpac and .bak.

Why is a .bacpac file so small compared to a .bak file of the same database?

Thanks for any insights!

A

.bacpac file = Schema + Data. Note that Data is BCP'ed out using Native format (not readable by Human).

You can rename the .bacpac to .zip to see the actual contents.
You can use ...DAC\bin\sqlpackage.exe commandline to extract the .bacpac contents programatically.
It is a snapshot that includes User data + Schema from SQL Server or Azure SQL Database.

-

Backup (commonly have .bak extension) = A FULL database backup contains a complete copy of database and provides a single point-in-time to which the database can be restored. A full backup contains some transaction log records so that the restored component (database, file, or filegroup) can be made transactionally consistent.

A BACPAC is not a substitute for a FULL backup. Its just a snapshot that can be used to move a database from one server to another (or to cloud) and archiving an existing database in an open format.

From my test, below are the results


SQL Server Management Studio (SSMS) is way too slow in its GUI I know this question has been asked before here and here. But none of them could sovle my problem. I have this environment:

Windows 10, build 1903 (freshly installed)
Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)   Mar 18 2018 09:11:49   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows 10 Enterprise 10.0  (Build 18362: )
SSMS v17.3 (14.0.17199.0)

Any activity I want to do in it, from opening, to connecting to a database engine, to right-clicking on a database, to creating a new database, to opening up a new query window, to browsing tables, any activity that is not query takes like 5 to 10 seconds to perform. It's clearly apparent that SSMS is doing something for each activity, and it gets stuck somewhere.

Here are the things I've done so far, without effect:

Blocked Microsoft's certificate URL (adding 127.0.0.1    crl.microsoft.com to hosts file)
Downloading certificate and installing it from http://crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl
Connecting to "local" instead of "."
Resetting user-defined settings in "C:\Users\user\AppData\Roaming\Microsoft\SQL Server Management Studio"
No antivirus is installed (only Windows Defender, the default of Windows)

It's a shame that a program from such a reputable company can't work smooth out of the box, and troubleshooting it is sooooooooooooooo difficult.

Could you please help. How can I diagnose what's wrong with SSMS.

Update: This problem exists even with SSMS v18.2 (15.0.18142.0)

I also had the mystery slow down in v14 and then upgraded to 18.5  Post upgrade 18.5 worked fast...a few days (and reboots)  later it bogged down again. Network trace showed a ton of IP6 traffic which we have not configured but somehow became a default 'on' in Windows 10.

Disabled Ip6 on the ethernet adapter and all working fine now.


Git corrupts SQLite database I use SQLite for websites I create and manage for my clients (I am a web developer).  They are very small and straightforward databases.

This is now happened twice, so it is pretty clear that it wasn't a fluke.  This last one was a clear set of steps that then corrupted the database:

Open the database in SQLite3 by linux command line
Make a mistake in the data
Ask Git to restore the released version of the file (this is reaching out to GitHub for the copy)
Open the restored version, and file is corrupt

I then struggle to get it back in order, nothing I do in git fixes it. I can luckily grab it from the live website (no updates there) and all is well.

My two questions:

Is there something I can do differently while getting the file from the git repository to revert, it is not clear to me why git would corrupt the file
Is there something I can do that is easier to restore the file other than get the copy from the live website

This was what I finally figured out, in case someone else comes along and hits the same problem. It was very confusing because the corruption was coming from git, so when I did something like rollback, it got corrupted then, not with my own activities.

I am running PHPStorm on Windows, with a shared directory on a virtual linux machine.  I need line endings to be LF, but since PHPStorm is running in Windows, the CRLF and LF battles ensue.

I had long ago fixed it with .gitattributes.  But when I switched from MySQL to SQLite, I had not added .db as a file not to be messed with when it came to line endings.

Git was replacing a CRLF with an LF in the .db binary file

In .gitattributes, you need:

# Set the default behavior to always be linefeed for linux
* text=auto

# Definitively text files
*.txt text eol=lf
*.php text eol=lf
*.html text eol=lf
...

# Ensure those won't be changed
*.jpg -text
*.png -text
*.db -text

I added the last line and all was well, git is no longer trying to be helpful with the binary file and corrupting it with LFs.

One last thing I learned, you can set the gitattributes file, then go to your root directory and run the command:

git add --renormalize .
git status

You will then see any file that will be converted from CRLF to LF, a good way to fix an entire directory as well as making sure it is not doing to the SQLite database.


How to know which DMVs relate to each other? Is there any way to know which DMVs/DMFs are related to each other?

Some of them are well known or obvious, like sys.dm_exec_connections JOIN sys.dm_exec_sessions ON session_id = session_id, or the DMVs for missing indexes.

However, for example, I would like to know what other DMVs sys.dm_exec_sessions joins with on session_id.   Session_id does not exist in sys.columns.  Also, the queries here do not list anything for DMVs.

A long, long time ago Microsoft published a large .pdf poster with all of the system views for SQL Server 2005, so I'm looking for something like the for DMVs/DMFs in SQL Server 2017.

You could cheat and use Glen Berry's. The docs list the relationship cardinalities at the bottom for sys.dm_exec_requests, sys.dm_exec_connections, sys.dm_exec_cursors, sys.dm_db_session_space_usage.

Here's the map for 2012 that you're looking for. You'd just need to account for new DMVs Here is a mapping of system tables to system views. - scsimon


How to get accurate row counts in partitions in a table I am trying to fetch row counts in each partition for a table. But I got confused as in sys.partitions row counts is on basis of indexes.

So how can I get exact row count for partitions in a table.

You need to ignore index_id with values that are not 0 (heap) or 1 (clustered index)

SELECT rows  FROM sys.partitions
WHERE object_id = object_id('name of table')
AND index_id < 2


How do you generate the DDL that created a DB2 LUW database? There are various options for db2look you can use to see the DDL for just about every object in the database except the command that created the database itself.  Is there any way with db2look, or perhaps with some other system command, where you can see the exact command structure that created a database?

The minimum requirement is to have DB2 10.1.4 or DB2 10.5 installed.

  -createdb: Generate the CREATE DATABASE command that was used to create the source database

db2look -d sample -createdb


Monitoring changes on a View Many DBMS implement views. However, do some of them implement change data capture on views (rather than actual tables), in a near real time fashion ?

I work mainly with Sql Server, but I'm not aware of any DBMS that can track changes on a 'view'.

I would suggest that a standard view is only a logical abstraction of physical tables and does not provide any actual data storage to change. Any changes done though the view would be applied to the actual physical tables. As I understand it, CDC uses the transaction log to process the changes, I think your answer is no. If you need to track the data changes, you would need to track the underlying tables.


regexp_replace() vs. replace() to replace all matches in a string By default, in PostgreSQL, regexp_replace() replaces the first occurrence of a substring in a string, while replace() replaces all occurrences of a substring in a string. Why is the default behavior different?

(I know that with regexp_replace one can use the g option to replace all occurrences of a substring in a string.)

Examples:

SELECT regexp_replace('hello world', 'o', 'z');  -- returns "hellz world"
SELECT regexp_replace('hello world', 'o', 'z', 'g'); -- returns "hellz wzrld"
SELECT replace('hello world', 'o', 'z'); -- returns: "hellz wzrld"

Why?

Because Postgres replace() is a standard SQL function that works the same as in other RDBMS. Example: replace() in SQL Server:

  Replaces all occurrences of a specified string value with another string value.

While regexp_replace() is used to ...

  Replace substring(s) matching a POSIX regular expression.

The handling of regular expressions is obviously guided by the POSIX standard and works the same as other tools implementing it. The manual:

  POSIX regular expressions provide a more powerful means for pattern
  matching than the LIKE and SIMILAR TO operators. Many Unix tools such
  as egrep, sed, or awk use a pattern matching language that is similar
  to the one described here.


School marks database with "mark categories" I'm trying to build a database to manage my school marks. Here in Germany we have different "categories". For example we have one category for all our written work and one for our tests. I want to have a defined number of "categories", each of them has its own list of marks. How do I have to design my database? I have tried the following:

CREATE TABLE [dbo].[Marks] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [CategoryId] INT NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Value] DECIMAL(18,3) NOT NULL
)

CREATE TABLE[dbo].[Categories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Average] decimal(18,3) NOT NULL
)

CREATE TABLE[dbo].[Subjects] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Teacher] NVARCHAR(MAX)  NOT NULL,
    [Average] decimal(18,3) NOT NULL,
    [TestsId] INT NOT NULL,
    [WrittenId] INT NOT NULL,
)

I come from C#, so I might not think like a SQL-Developer.

Marks are related to categories and subjects, so you need a SubjectId column in the Marks table so that you know what subject each record relates to.

CREATE TABLE [dbo].[Marks] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [CategoryId] INT NOT NULL,
    [SubjectId] INT NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Value] DECIMAL(18,3) NOT NULL
)

CREATE TABLE[dbo].[Categories] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Average] decimal(18,3) NOT NULL
)

CREATE TABLE[dbo].[Subjects] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR(MAX) NOT NULL,
    [Teacher] NVARCHAR(MAX)  NOT NULL,
    [Average] decimal(18,3) NOT NULL,
)

It seems as if you were envisioning creating separate category records for every subject (i.e., you would have a tests category for writing, and a tests category for math), but you should just need one category of each type of category (tests, written, lab, etc.).  The categories get tied to the subjects by the marks table, which links to both of the other tables.


Mysql query to find nth largest entry in a column I searched on internet to find the nth largest entry in a particular column for Mysql. I found the following query:

select salary from employee e1
where (n-1) = (select count(distinct(salary))
from employee e2 where e2.salary > e1.salary);

Here employee is my table name and I want to select nth highest salary from it. The query is running fine. But I don't understand how it's actually working.

Can anybody explain it?

SELECT salary FROM employee ORDER BY salary DESC LIMIT 7,1;

Will find the 8th highest salary.

Closer to what you did is

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 7,1;

To explain your extremely inefficient query,...

Foreach salary in employee (in any order), test (where) to see if the subquery (( select ... )) returns (n-1).  The subquery searches the same table again (that's called a "self join") to count how many different (distinct) salaries are bigger (where e2.salary > e1.salary).  If there are 1000 rows in the table, it will have to do 1000 scans of 1000 rows each -- a million operations.

If you need to find who has the nth largest salary, that is more complicated.  If you are not worried about dups, then it is simply:

SELECT name, ... FROM employee ORDER BY salary DESC LIMIT 7,1;

If you need the DISTINCT, the a subquery is necessary:

SELECT name, ...
    FROM ( SELECT DISTINCT salary
              FROM employee ORDER BY salary DESC LIMIT 7,1
         ) AS e2
    JOIN employee AS e1 USING(salary);


How to design a table with relationships to a group of users or individual users? I have a feature in my application that allows setting a set of permissions for groups of users or individual users. Considering that a group contains zero or more users and a user belongs to one or more groups, how should I design my permissions table so I'm able to reference the group id or individual user ids?

A permission set can have both groups and users referenced at the same time, this way if a user is added to a group that is part of a permission set, he/she automatically gets the same permission set.

you could use something like this:

create table users (
    userid int not null primary key
  )

create table groups (
    groupid int not null primary key
  )

create table permissions (
    permissionid int not null primary key
  )

create table users_groups (
    userid int not null
  , groupid int not null
  , constraint pk_users_groups primary key clustered (userid, groupid)
  , constraint fk_users_groups_userid foreign key references users(userid)
  , constraint fk_users_groups_groupid foreign key references groups(groupid)
  )

create table users_permissions (
    userid int not null
  , permissionid int not null
  , constraint pk_users_permissions primary key clustered (userid, permissionid)
  , constraint fk_users_permissions_userid foreign key references users(userid)
  , constraint fk_users_permissions_permissionid foreign key references permissions(permissionid)
  )

create table groups_permissions (
    groupid int not null
  , permissionid int not null
  , constraint pk_groups_permissions primary key clustered (groupid, permissionid)
  , constraint fk_groups_permissions_groupid foreign key references groups(groupid)
  , constraint fk_groups_permissions_permissionid foreign key references permissions(permissionid)
  )

and query it with something like this:

select p.permissionid --,p.other_permissions_columns
  from users_permissions up
    inner join persmissions p on p.permissionid = up.permissionid
  where up.userid = @userid
union
select p.permissionid --,p.other_permissions_columns
    from users_groups ug
      inner join groups_permissions gp on gp.groupid = ug.groupid
        and ug.userid = @userid
      inner join persmissions p on p.permissionid = gp.permissionid


Why does adding a transactional replication publication change all the tables and columns to "not for replication"? I'm trying to get replication set up - this is my first time ever doing it, so I'm probably making some rookie mistake here. Right now I'm just working in the dev environment. I've been following the example given in this MSDN tutorial.

I've setup the instance to be it's own distributor, then I go to create a publication (which includes all but a few tables, and all but one view). The publication creates just fine, but afterwards, all of the tables and columns have been set to "not for replication"! So of course when I go to the snapshot agent status, I see the error "Explicit value must be specified for identity column in table either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

The first time this happened, I figured I had done something stupid, so I rolled back by selecting "Disable Publishing and Distribution", and did some digging. Before I started enabling & setting up replication, all tables are columns had "not for replication" set to "false", which is what I would expect. I redid the publisher & distributor setup, which didn't change anything on the table/column settings. But then I created the publication, and they had all been modified. I undid again, which reverted them all back to 'for replication', then generated but did not run a publication script (with SQL Server Management Studio's GUI), and executed each command one at a time, checking the state of the "not for replication" setting along the way. It's the call to sp_addarticle that changes the setting.

Each article is being added with the option @identityrangemanagementoption = N'manual' and @schema_option = 0x000000000803509F, which translates to options 0x1, 0x2, 0x4, 0x8, 0x10, 0x80, 0x1000, 0x4000, 0x10000, 0x20000, and 0x8000000 (see this MSDN article for what they mean).

The environment is SQL Server 2012 - dev is Developer edition, and the eventual production environment is Standard edition. I'm using this script to check the state of the "not for replication" setting:

SELECT
    tables.name AS table_name,
    columns.name AS column_name,
    COLUMNPROPERTY(columns.object_id, columns.name, 'IsIdNotForRepl') AS IsIdNotForRepl
FROM sys.tables
    JOIN sys.columns
        ON tables.object_id = columns.object_id
ORDER BY 3 DESC, 1, 2

Why would creating a replication publication actively exclude the subject objects from replication, and how do I change this behavior?

Not sure if you have checked this; but there is an option in sp_addarticle called @identityrangemanagementoption. If set to manual, then it "Marks the identity column using NOT FOR REPLICATION". Pls check if this is the case.

Ref : https://msdn.microsoft.com/en-us/library/ms173857(v=sql.110).aspx


updating a field with a partial pass parameter I am working on a table that has 24 columns named  pp1, pp2... pp24.

I would like to pass in a parameter that would be used to help identify the column to be updated.

one parameter is the value (8)
one parameter is the column (5)
one parameter is the rowID (256)

short stored proc...

update 'pp'+ [column_parameter]
set value to [value_parameter]
where rowID = [rowID_parameter]

Can the concatenation of the partial column name be combined with a parameter?

That's really a bad idea, I don't understand why you need to do so, and it can't be done the way you are looking for, but if you going to do this, I suggest to declare 24 variable + 1 for the RowID.

For example:

CREATE PROCEDURE MyProc
  @RowID INT,
  @Col1 VARCHAR(45),
  @Col2 INT
  -- In your case you will need 24 variable, each one represent a value for a column
AS
BEGIN
  --Optional: Check if @RowID IS NULL and RAISERROR first
  DECLARE @SQL NVARCHAR(MAX) = N'UPDATE [T]
                                 SET [Col1] = ISNULL(@Col1, Col1),
                                     [Col2] = ISNULL(@Col2, Col2)
                                 WHERE [RowID] = @RowID';
  EXECUTE sp_executesql @SQL,
                        N'@Col1 VARCHAR(45), @Col2 INT, @RowID INT',
                        @Col1,
                        @Col2,
                        @RowID;
END

Then call your procedure, and pass NULLs for columns you don't want to be updated. That's the only way I can think of it.

Sample Demo

UPDATE:

You don't even need for DynamicSQL, so your SP may looks like

CREATE PROCEDURE MyProc
  @RowID INT,
  @Col1 VARCHAR(45),
  @Col2 INT
  -- In your case you will need 24 variable, each one represent a value for a column
AS
BEGIN
  --Optional: Check if @RowID IS NULL and RAISERROR ROLLBACK TRANSACTION
  UPDATE T
  SET Col1 = ISNULL(@Col1, Col1),
      Col2 = ISNULL(@Col2, Col2)
  WHERE RowID = @RowID;
END


Moved Model Database and now my SQL Server doesn't work I moved my Model data and log files:

ALTER DATABASE Model
MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\Data\model.mdf')

ALTER DATABASE Model
MODIFY FILE (NAME = 'modellog', FILENAME = 'L:\Log_Files\modellog.ldf')

But it seems it has failed and now my SQL Server connection is gone and now SQL Server doesn't seem to work and I would like to know who how I could rectify.

When I try to connect to SQL Server, the message is:

  Cannot connect to X.

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider).

I am using SQL Server 2012. Any help would be appreciated.

Thanks.

Did you follow the steps in the documentation for doing this?

Move System Databases - SQL Server 2012

  To move a system database data or log file as part of a planned
  relocation or scheduled maintenance operation, follow these steps.
  This procedure applies to all system databases except the master and
  Resource databases.

  For each file to be moved, run the following statement.

  ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
  Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Start, Stop, Pause, Resume,
  Restart the Database Engine, SQL Server Agent, or SQL Server Browser
  Service.
  Move the file or files to the new location.
  Restart the instance of SQL Server or the server. For more information, see Start, Stop, Pause, Resume, Restart the Database
  Engine, SQL Server Agent, or SQL Server Browser Service.
  Verify the file change by running the following query.

  SELECT name, physical_name AS CurrentLocation, state_desc
  FROM sys.master_files
  WHERE database_id = DB_ID(N'<database_name>');

  If the msdb database is moved and the instance of SQL Server is
  configured for Database Mail, complete these additional steps.

  Verify that Service Broker is enabled for the msdb database by running the following query.

  SELECT is_broker_enabled
  FROM sys.databases
  WHERE name = N'msdb';

  For more information about enabling Service Broker, see ALTER
  DATABASE (Transact-SQL).

  2. Verify that Database Mail is working by sending a test mail.

Critically, note that the command you ran doesn't actually move the physical data files.


bcp error: date invalid date format I'm a bcp newbie. I want to insert a large file. I'm having a hard time uploading datetime format.

Table SQL:

"Create Table dbo.my_test ( " + \
    "transaction_id BIGINT ," + \
    "transaction_no INT ," + \
    "store_no INT ," + \
    "register_no INT ," + \
    "transaction_date DATETIME," + \
    ");"

My csv file:

"771666690","346138","400","1","2015-06-18 00:00:00"

My format file:

13.0
6
1       SQLBIGINT           0       8       "\""      0     test_                                                       ""
2       SQLBIGINT           0       8       "\",\""   1     transaction_id                                                       ""
3       SQLINT              0       4       "\",\""   2     transaction_no                                                       ""
4       SQLINT              0       4       "\",\""   3     store_no                                                             ""
5       SQLINT              0       4       "\",""   4     register_no                                                          ""
6       SQLDATETIME         0       8       "\r\n"   5     transaction_date                                                     ""

My bcp command:

>bcp data_base.dbo.my_test in "test.csv" -f "test.fmt" -S localhost -U *** -P *****

Output

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

I am using SQL Server 2016. Default language is csv.

I slimmed down the type just to get a toy example up, here is the new scripts:

Table SQL:

"Create Table dbo.my_test ( " + \
    "transaction_date DATETIME," + \
    ");"

My csv file:

2015-06-18

My bcp command:

>bcp data_base.dbo.my_test in "test.csv" -f "test.fmt" -S localhost -U *** -P *****

Output:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

Your bcp format file has six columns defined, but the csv dataset and table have only five columns.

I was able to import your sample data successfully using the format file:

14.0
5
1       SQLBIGINT       0       8       ",\""     1     transaction_id      ""
2       SQLINT          0       4       "\",\""   2     transaction_no      ""
3       SQLINT          0       4       "\",\""   3     store_no            ""
4       SQLINT          0       4       "\",\""   4     register_no         ""
5       SQLDATETIME     0       8       "\"\r\n"  5     transaction_date    ""

This works regardless of whether the table columns are NULL or NOT NULL, though it is good practice to always specify this in your CREATE TABLE statements to avoid ambiguity.

That said, importing csv data prior to SQL Server 2017 can be difficult, or even impossible. From 2017 onward, you can use the RFC 4180 compliant:

BULK INSERT dbo.my_test
FROM 'C:\Temp\my_test.csv'
WITH (FORMAT = 'CSV');

See Using the Bulk-Load Tools in SQL Server by Erland Sommarskog for a comprehensive description of the topic, including the special problems posed by csv data.


Multiple SQL queries fired asynchronously progressively slow down We are using .NET Core Web API 2. We have a vue client that makes calls to this Web API 2 API method. We have a report dashboard page. On this page we have 13 charts which means 13 calls are made to an API endpoint to get the data for the charts.

If we run the 13 queries in SQL individually all of them run within a second or less. I created a console app in which I ran the 13 queries in a loop and captured some timings. Every query ran in less than 2 seconds.

I then captured some timings in the API method. I captured the start time, end time and duration. All the 13 calls were made within a second of each other. The bottle neck seemed to be around the the code that ran the SQL query. The query timings ranged from less than a second to over 11 seconds:

147
250
250
274
1499
6758
6795
7191
7671
7436
9275
9450
12481

Please note these are times in ms and is only capturing the time the query takes in the API method. I also used SQL Profiler and saw the timings there and they were similar.

It has something to do with making multiple API calls asynchronously. If I made an API call one at a time for each query it will give times of less than a couple of seconds.

I am running the code in C# in such a way that it does not lock the table / database for example I am using isolation level of snapshot. I have also tried to use isolation level or readuncommitted and the problem is still there.

We are using Windows Server 2019 with 2 processors and 10 GB Memory. SQL Server version is 2017.

I have done a few days of research and tried a few things but nothing seems to over come this problem. I needed some advice on what could be the cause of this issue and how to overcome it.

Just to add each query returns a single row of data that is consumed by the chart. I also ran the combined 13 queries in sql management studio and it took 8 seconds for the entire query to process.

I also noticed that when I access the dashboard the server cpu usage goes through the roof hitting 100% for a few seconds.

Update:

Execution plan when I run all 13 directly in SQL Management Studio:

https://www.brentozar.com/pastetheplan/?id=S1TFqgnL8

Execution plan for one of the queries that was triggered via the api code i.e. when I access the dashboard It took 12+ seconds to finish:

https://www.brentozar.com/pastetheplan/?id=HyIjKx3L8

Execution plan for the same query if I run it alone via sql it takes 2 seconds:

https://www.brentozar.com/pastetheplan/?id=ByRCcxnUU

In order to understand what is happening, I would gather more metrics. First, don't use Profiler, it adds it's own overhead. Assuming you're on 2012 or better use Extended Events. Further, using Extended Events create a session that not only captures rpc_completed and batch_completed for your queries, but also add wait_info_external and wait_completed. With all this add causality tracking. Here you'll be able to see exactly what the query is waiting on as well as how long it takes to run.

On top of this, get the execution plans for when it runs well and when it runs slowly. Compare the two to find any differences.

All this should show you where the problem lies.


Sql Server view with very limited access I have a requirement to limit a view in Sql Server 2008 R2 that user:

user has access to select from view.
user has not access to copy the result from select statement.
user has not access to join the view.

I have to use Sql Server view, is it possible?

As soon as someone has access to SELECT from a view, he can also copy the result set and join it with other views or tables, he has access to.

If you need to follow those requirements, you would need to create a custom application, that does not allow copying of data.


Connection string for MariaDB using ODBC What is the connection string for using MariaDB with ODBC?

I'm guessing it's something like

DRIVER={MariaDB ODBC 5.1 Driver}; SERVER=LOCALHOST; PORT=3306; DATABASE=DatabaseName; UID=root; PASSWORD=password;OPTION=3;

Connection strings seems to show strings for MySQL but not MariaDB.

MariaDB seems to have a lot of connection strings for Access, SQL Lite, excel, etc ...

What have I missed?

Although this is late answer, but MariaDB have that documented here

SQLWCHAR *ConnStr= L"Driver={MariaDB ODBC 2.0 Driver};Server=localhost;UID=odbc_user;PWD=odbc_pw;DB=odbc_test;Port=3306";


Import data extracted from website directly into PostgreSQL database without using any text or csv file I have a shell script which extracts data from the website. I know, I can store this data into text or csv file and then import it into database using PostgreSQL "copy" command.

My interest is not to use this text or csv file in the process. But, directly copy the data into database.

Is there anyway to do this? Any suggestion are appreciated.
Thank you

You can open a pipe and write your data to the pipe directly. Related answer with demo code:

Insert SQL statements via command line without reopening connection to remote database

Or you can use COPY with a program or standard input directly:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

  ...

  When PROGRAM is specified, the server executes the given command and
  reads from the standard output of the program, or writes to the
  standard input of the program. The command must be specified from the
  viewpoint of the server, and be executable by the PostgreSQL user.
  When STDIN or STDOUT is specified, data is transmitted via the
  connection between the client and the server.


SQL Server - Copy data from restored version to live database table I am using SQL Server 2014.
I have a database called LabDB.
I have a issue where some data was deleted in the database.
What I like to do is to restore the backed up version of the database in Object Explorer and then
copy the missing data from the backed up version to the live database.

Can somebody please guide me on how to do this.

Note: While I believe these steps to be accurate I take no responsibility for any lost data. Use at your own risk.

Right click on the databases folder in object explorer (it should be listed beneath your server name)
Choose device and click on the ellipsis button. Find the backup file you wish to restore
Change the destination database to another database name (you don't want to overwrite your existing database)
Choose files on the LHS. Make sure to change the Restore As field so that you do not overwrite your existing mdf or ldf files
Write queries to insert into  select * from


How to connect remotely to MSSQL database from local, using windows Authentication? I have to connect remotely to MSSQL database from my local machine, using remote server windows Authentication.

Assuming you are in a different domain, you can launch SSMS using the remote Windows commercials. Open a command prompt in the folder where ssms.exe lives, and run:

runas /netonly /user:remotedomain\remoteuser ssms.exe

You will be prompted for your password. SSMS will be using remote Windows credentials even though several UI elements will look like you're using the local ones.

This technique should work for launching other executables, like sqlcmd or powershell, but I haven't tested those. I know it works for SentryOne and Plan Explorer.

You can also use Credential Manager, which I wrote about (in addition to more details about runas) here:

Connect to SQL Servers in another domain using Windows Authentication


How to pivot a table for a set number of combinations? Ok so I have a table of components and a colleague needs some data from this table in a different format, now I could do it with some python or java code but wondered if there is a simpler way in MS-SQL with a PIVOT maybe?

Components Table:-

ID_RECIPE  COMPONENT    QTY
   1          CPA       1.00
   1          CPC       0.50
   2          CPA       10.0
   2          CPF       7.50
   2          CPH       3.00
   2          CPJ       2.25
   2          CPP       0.25

There is never more than 5 components to a recipe so I want to be able to output something like this:-

ID_RECIPE  COMP_1  COMP_2  COMP_3  COMP_4  COMP_5  QTY_1  QTY_2  QTY_3 QTY_4  QTY_5
   1        CPA     CPC     NULL    NULL    NULL   1.00   0.50   NULL  NULL   NULL
   2        CPA     CPF     CPH     CPJ     CPP    10.00  7.50   3.00  2.25   0.25

Any ideas would be appreciated.

Using Jonathan Fite's answer from this DBA.SE question and adapting it to your table & data

The CTE ranks the data prior to pivoting twice. You could change the order if you would like to order by something else.

    ;WITH CTE_Rank AS
    (
    SELECT ID_RECIPE,COMPONENT,QTY
        , sComponentName = 'COMP_' + CAST(DENSE_RANK() OVER (PARTITION BY ID_RECIPE ORDER BY COMPONENT) AS VARCHAR(10))
        , sQTY = 'QTY_' + CAST(DENSE_RANK() OVER (PARTITION BY ID_RECIPE ORDER BY COMPONENT) AS VARCHAR(10))
    FROM T
    )

    SELECT ID_RECIPE
    ,  MAX([COMP_1]) as Comp_1
    ,  MAX([COMP_2]) as Comp_2
    ,  MAX([COMP_3]) as Comp_3
    ,  MAX([COMP_4]) as Comp_4
    ,  MAX([COMP_5]) as Comp_5
    ,  MAX([QTY_1])  as QTY_1
    ,  MAX([QTY_2])  as QTY_2
    ,  MAX([QTY_3])  as QTY_3
    ,  MAX([QTY_4])  as QTY_4
    ,  MAX([QTY_5])  as QTY_5
FROM CTE_Rank AS R
    PIVOT(MAX(COMPONENT) FOR sComponentName IN ([COMP_1], [COMP_2], [COMP_3], [COMP_4], [COMP_5])) AS COMPONENTNAME
    PIVOT(MAX(QTY) FOR sQTY IN ([QTY_1], [QTY_2], [QTY_3], [QTY_4],[QTY_5])) AS QTYName
GROUP BY ID_RECIPE;

Result

ID_RECIPE   Comp_1  Comp_2  Comp_3  Comp_4  Comp_5  QTY_1   QTY_2   QTY_3   QTY_4   QTY_5
1           CPA      CPC    NULL    NULL    NULL    1.00    0.50    NULL    NULL    NULL
2           CPA      CPF    CPH     CPJ     CPP     10.00   7.50    3.00    2.25    0.25

Table + Data

CREATE TABLE T
(ID_RECIPE int,  COMPONENT char(3),    QTY numeric(10,2));
INSERT INTO T(ID_RECIPE,COMPONENT,QTY)
VALUES
( 1         ,'CPA',       1.00),
( 1         ,'CPC',       0.50),
( 2         ,'CPA',       10.0),
( 2         ,'CPF',       7.50),
( 2         ,'CPH',       3.00),
( 2         ,'CPJ',       2.25),
( 2         ,'CPP',       0.25);

You could remove the warning of the NULL values by checking prior to applying the MAX() (MAX() is there due to the grouping).

DB<>Fiddle


Design for m-to-n relation with unidirectional attribute(s) Assume I have a Table StackBox that holds items as Item. I allow StackBox to have multiple Item and I allow Item to be in different StackBox. It is obviously an m-to-n relation. Solving this with an according m-to-n mapping table is easy.

But my problem here is the thing that I want to store the information in which order many Item are stored inside a StackBox. I could e.g. add a column to the m-to-n mapping table. Is this a valid solution even though I never want to ask "At which places does Item occur"? I am only interested in "At which place inside StackBox is Item with id x"?

The other option would be to create two additional tables of course. One for the mappings from StackBox to Item that just contains two index tables and another one for the mappings from Item to StackBox that contains the two index columns plus e.g. an additional column position.

I hope my question is clear. Please let me know if I should clarify anything.

I could e.g. add a column to the m-to-n mapping table. Is this a valid solution?

Yes, this solution desrcibes best your scenario. A table with (StackBox, Item, Position) and a unique constraint on (StackBox, Position) (meaning only one item in each place/position) and probably another unique constraint on (StackBox, Item) (unless you can have an item twice or more in a stackbox).

The table would be like (I assume the other two tables have primary keys StackBoxID and ItemID and are INT, adjust accordingly to your design):

CREATE TABLE StackBoxItem
( StackBoxID INT NOT NULL,
  ItemID INT NOT NULL,
  Position INT NOT NULL,
  PRIMARY KEY (StackBoxID, Position),
  UNIQUE (StackBoxID, ItemID),
  FOREIGN KEY (StackBoxID)
    REFERENCES StackBox (StackBoxID),
  FOREIGN KEY (ItemID)
    REFERENCES Item (ItemID)
) ;


ssrs expression to display first 3 parameter values if "ALL" is selected in Parameter List I have a parameter to display a report, if the parameter is selected as "Select All" then all the values in the parameter list are selected.

But I need the report to display values only for the first 3 parameter values selected.

Is there any filter expression available for this?

I don't think you can influence the behaviour of the "Select All" check box, but you could achieve something similar using  Cascading Parameters.

This is a fairly messy solution, especially if you query for the parameter list is slow, but could be an acceptable solution for your end users.

For example, this could work for you.

First: Create a parameter of boolean type, in my example named "Top3":

Second: Add the same data set twice, in the screenshot below you see 2 datasets, Dataset and Dataset_Filter and 2 parameters, Top3 and Dataparameter

The 2 datasets are essentially the same, based on this query on the adventureworks 2014 database

SELECT  CurrencyCode, Name, ROW_NUMBER() OVER (ORDER BY CurrencyCode ASC) as RowNumber
FROM    Sales.Currency
ORDER BY CurrencyCode ASC

The row number is necessary to be able to select x number of rows by default.

The dataset_filter dataset has an additional filter expression added to it, Please note that it is of type integer

In the value you put whatever "top" number of values you want selected.

The trick lies in the expression, the expression is either the row number or something that will always be smaller than the number of values you want to select. In the expression I use the Top 3 parameter.

=IIF(Parameters!Top3.Value, Fields!RowNumber.Value,1)

So essentially this is saying, either the "rownumber has to be lower than or equal to 3" if  top 3 is true or "1 has to be lower than or equal to 3 if top3 is false"

Then on the DataParameter you say the "Available values" come from dataset:

And on the "Default values" you pic the Dataset_Filter dataset (remember, this will either be the entire dataset or only the first 3 rows)

Once you have that set up you end up with a report that has a parameter to allow you to select "top 3" or "select all" like this:

Top 3 selected:

Top 3 false:

Entire RDL XML can be found here on pastebin but requires adventure works 2014 to be installed on localhost.

If you only want to display the first 3 values the obvious dataset query would be

SELECT TOP 3 CurrencyCode, Name, ROW_NUMBER() OVER (ORDER BY CurrencyCode ASC) as RowNumber
FROM    Sales.Currency
ORDER BY CurrencyCode ASC


Does write stalls on separated MDF file affect performance My user database MDF file resides on separated disk. There is nothing else on the disk than this MDF file. I can see average write stalls higher then 20ms on the file using appropriate DMV. I assume that writes are only made assynchroniously by checkpoints or lazy writer.

I doubt whether there is any direct negative impact on the workload performance because of these write stalls? Or is there any good reason we should try to increase write performance of the disk?

The occasional disk write of 20ms or more on a volume hosting a database file is not normally a big deal.
The server-level Recovery Interval, or the database-level Target Recovery Time (aka Indirect Checkpoints, and the default for SQL 2016+) can trigger aggressive flushing, which can increase the total write IO cost of a workload by eliminating the write-coalescing behavior of the page cache.
If you set the Target Recovery Time and the "recovery writer" starts to fall behind, it will hijack your schedulers to help it catch up.  This will create "backpressure" to reduce your workload throughput to maintain your Target Recovery Time.  See, eg:

" If recovery writer starts falling behind resulting into long
DPLists, the individual worker threads running on the scheduler starts
playing the role of recovery writer to collect, sort and post write
for dirty pages. This allows recovery writer to scale dynamically on
heavy OLTP systems to ensure target_recovery_time interval set is met
all the time. While multiple recovery writer routine running
aggressively allows us to meet the target_recovery_time for
predictable recovery, the overall throughput of workload may degrade
under these circumstances due to additional overhead caused from each
running worker performing recovery writer activity."

Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler
Also if your workload requires data file reads, these will contend with the writes for IO.


Command not found: pg_ctl on Ubuntu PostgreSQL 9.4.8 Ubuntu 16.04 64 bit

I just want an explanation of what is happening here. Why is this command not found? Nothing else.

malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$ pg_ctl stop -m fast
pg_ctl: command not found
malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$ sudo pg_ctl stop -m fast
[sudo] password for malikarumi:
sudo: pg_ctl: command not found
malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$ ls
clusterdb   initdb             pg_ctl          pg_restore      postmaster
createdb    oid2name           pg_dump         pg_standby      psql
createlang  pg_archivecleanup  pg_dumpall      pg_test_fsync   reindexdb
createuser  pg_basebackup      pg_isready      pg_test_timing  vacuumdb
dropdb      pgbench            pg_receivexlog  pg_upgrade      vacuumlo
droplang    pg_config          pg_recvlogical  pg_xlogdump
dropuser    pg_controldata     pg_resetxlog    postgres
malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$

pg_ctl by design isn't in the path of the version of PostgreSQL Debian (and therefore Ubuntu) distributes. PostgreSQL is installed as a service in Debian/Ubuntu

sudo service postgresql {start|stop|restart|reload|force-reload|status} [version ..]

That command calls an init.d script which wraps around pg_ctlcluster. You can see that by opening /etc/init.d/postgresql. You can call pg_ctlcluster if you must. pg_ctrlcluster is a perl script that wraps around pg_ctl which isn't exposed (in the path) by design.

  pg_ctrlcluster: multiversion/cluster aware pg_ctl wrapper; this also supplies the correct configuration parameters to 'start', and makes sure that postgres really stops on 'stop'.

pg_ctl is technically distributed, but there is no reason to ever call it directly. To find it run

find /usr/lib/postgresql/ -name pg_ctl

You can call pg_ctl explicitly with the full path, however don't.

Also, going above and beyond and teaching you how to fish, you can always use apropos if you have these kind of questions later.

$ apropos pg_ctl
pg_ctl (1)           - initialize, start, stop, or control a PostgreSQL server
pg_ctlcluster (1)    - start/stop/restart/reload a PostgreSQL cluster


need help joining two tables with a having count clause I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of.  pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis.  I need to pull the names of patients who have one and only one diagnosis.  I can join the tables on patient_id.  here is what i wrote:

select
PTC_DIAGNOSIS.PATIENT_ID,
PT_BASIC.PATIENT_CODE,
PT_BASIC.NAME_FIRST,
PT_BASIC.NAME_LAST
from PTC_DIAGNOSIS
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015'
group by PTC_DIAGNOSIS.patient_id
having COUNT(*)=1

I get the error Column 'PT_BASIC.NAME_FIRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have moved columns around, tried having pt_basic first and re-written it 15 different ways with similar errors every time.  i feel i am close, but maybe not.

thank you

very close only the GROUP BY Columns forgotten

Try this:

select
PTC_DIAGNOSIS.PATIENT_ID,
PT_BASIC.PATIENT_CODE,
PT_BASIC.NAME_FIRST,
PT_BASIC.NAME_LAST
from PTC_DIAGNOSIS
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015'
group by
PTC_DIAGNOSIS.PATIENT_ID,
PT_BASIC.PATIENT_CODE,
PT_BASIC.NAME_FIRST,
PT_BASIC.NAME_LAST
having COUNT(*)=1


Determining what objects SHRINKDATABASE will free space from First off, yes, shrink is bad. Don't use it, ever. I understand. Unfortunately, it's kind of SOP and I'm not in a position to argue since it's usually a stopgap until we can get a maintenance window to allocate more space and rebuild/reorganize indexes.

We recently had an issue where we were switching over from traditional backups to a third-party tool. Due to the size of the database and some large objects we'd recently removed, it made sense to shrink it prior to taking the clean backup with the new tool before the cutover. I had some reservations, but we went ahead with it as it rarely causes problems. Obviously, clearing the logs isn't really an issue, but we were shrinking the mdf. This caused a lot of blocking on just a few large, highly-transactional objects and, rightly so, brought up some pretty serious questions about how we use this.

I've been doing a lot of research, but haven't yet found enough detail to my satisfaction.

Is there anywhere that actually describes the internals of how SHRINK is operating?
Is there a way to determine which objects are more fragmented than others and how it's freeing up space? Alternatively, does it not matter how fragmented a table is and it's more about the overall size of the object compared to others in the db?

Paul Randall talks a bit about it here, but it's not enough detail.

I know you could sort of base it on the dm index stats, but it's been pretty difficult to run on some of our larger databases due to the size and over-indexing. I'm also not sure if that's all I'd need to be concerned with. Main tables are stored as heaps and only the indexes are really fragmented, but I'm not sure how different partitions affect things or if other parts of the underlying DB could also be getting freed up.

I did also see this question, but I'm not sure if I'd be able to configure it to the kind of granularity we're looking for.

A few thoughts:

1. Never use SHRINKDATABASE, always use SHRINKFILE.

You want to be in control of which file it is taking space from, and exactly how much so you leave some space in reserve. Run a query like this to see the actual used/free space in each file:

SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
FROM sys.database_files WITH (NOLOCK)
ORDER BY type, file_id;

Let's say your primary data file is 100GB, but only 20GB is actually used, you might have a final target size of 25GB or 30GB to leave some internal free space. So you'd do a shrink something like:

DBCC SHRINKFILE (name='logical_name', size=30000)

2. Internal SHRINKFILE Behavior

Internally, shrinking a data file simply moves used pages one at a time from the end of the file to an open spot near the beginning until it has enough space at the end of the file to meet your target size.

It's a fairly simply brute-force method and pays no attention to the contents of the pages, which is why it frequently results in table fragmentation. From the Paul Randal article you linked:

  A data file shrink operation works on a single file at a time, and uses the GAM bitmaps to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on.

If the page it wants to move is locked by another process, it will be blocked.

3. SHRINKS keep their progress

If you start a shrink, and then have to stop it because of blocking or other conflicting activity, it will retain its progress. All the pages it successfully moved will stay moved. You can resume later to finish the work.

4. Finding out what tables will be impacted by a shrink

There might actually be some ways to do this, using queries on sys.allocation_units or sys.partitions or maybe using DBCC PAGE or something, but my recommendation is: don't bother, large databases will have the scattered remains of thousands of objects across the data file, most of which you don't care about.

If you're insistent, here are some advanced articles I found via search:

Stack Overflow: See what data is in what SQL Server data file?
Using DBCC PAGE to Examine SQL Server Table and Index Data by Armando Prato
Inside the Storage Engine: GAM, SGAM, PFS and other allocation maps by Paul Randal
Analyzing SQL Server Data File Anatomy by Murilo Miranda

You can already find out what tables/indexes are at play when it starts blocking, everything else is noise.

If that lock never clears to give you a chance to move the page, then you might have to schedule some downtime with no other activity.

5. When you are done, do a reindex to fix fragmentation

The data file might grow a bit again, but unless your database is one massive table, you'll still likely be left with a smaller end result than when you started.


MySQL 8.0 Triggers Current_User not working I have this trigger that inserts a row to a table

CREATE
    DEFINER=`root`@`localhost`
    TRIGGER `user_log_AFTER_INSERT`
    AFTER INSERT ON `user_log`
FOR EACH ROW
BEGIN
    INSERT INTO audit_trail
    values( aud_no,
           ( SELECT user_acc_id
             from user_accounts
             where user_username = REPLACE( CURRENT_USER, '@localhost', '')),
           ( SELECT user_fname
             from user
             where user_id = ( SELECT user_acc_id
                               from user_accounts
                               where user_username = REPLACE( current_user(), '@localhost', ''))),
           current_date(),
           current_time(),
           "logs in and out");
END

My problem here is that the CURRENT_USER is not working, when I tried to use another account it does not insert the right user account. What is it does is, it uses the account root@localhost instead of my CURRENT_USER.

So far I have tried changing the CREATE DEFINER=`root`@`localhost` instead of root I use a different account and it work. but What I want is to make it dynamic, It must insert the current_user who made changes to the database not root@localhost. Thanks

Since I cant get the right user inside the trigger using current_user() and user() but works fine on Stored Procedure. What I did is I create a Stored Procedure and call it inside the trigger.

Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_log`()
BEGIN
    INSERT INTO audit_trail
    values( aud_no,
           ( SELECT user_acc_id
             from user_accounts
             where user_username = REPLACE( user(), '@localhost', '')),
           ( SELECT user_fname
             from user
             where user_id = ( SELECT user_acc_id
                               from user_accounts
                               where user_username = REPLACE( user(), '@localhost', ''))),
           current_date(),
           current_time(),
           "logs in and out");
END

Trigger

CREATE DEFINER=`root`@`localhost` TRIGGER `user_log_AFTER_INSERT` AFTER INSERT ON `user_log` FOR EACH ROW BEGIN
        CALL insert_log();
END


How to restore the privileges of the user postgres in PostgreSQL? Someone logged in my PostgreSQL as the user postgres, because a lack of security in my configuration files. What he did is to create a lot of superusers, and then, he revoked SUPERUSER privilege from postgres.

I've corrected the configuration files to avoid more problems, but now the harm is done and I can only log in as postgres, who is no more a superuser.

I would like to grant SUPERUSER privileges again to postgres user, but of course as I can't log in as a superuser I can't do it. Which is the best way to achieve this? I prefer to avoid reinstalling postgresql, is there other way?

I've also glanced the PostgreSQL log and I've seen the moment when the users were created, and their passwords, but these ones are encrypted through MD5 and I'm not able to desencrypt them.

What would you do in this case? Thank you!

If you edit your pg_hba.conf to allow login as one of the other superusers, then you can login as one of those and then alter role postgres superuser.


Update only changed columns with Mysql prepared statements In the following procedure I have simple prepared "update" statement. I have some questions about using this.

Main question: How do I update only the changed columns in a stored procedure?  It seems like setting update on all columns will overwrite data if another user changes a different column at the right time.  In Zend Framework 2, I typically use TableGateway, which allows me to send in the form array and I think it will only update the columns that I have sent. How do I build a stored procedure to take a variable set to columns to update?
With the above in mind, I know I can build a statement with concat and skip the "prepare .. using" bound parameters, but if I do that, then I can't protect against SQL Injection, unless I build my own filtering functions. I'd really prefer to keep the bound parameters. How do I keep those, while still only building a statement that updates only changed columns?  Because I don't see how to change the USING portion to have dynamic input parameters.
Also, just a side question. Why is it in Mysql I have to make all those duplicate input parameters? ("set @col1 = v_val1")  Because I can't put v_val1 directly in the USING statement I have to make session variables. I think that really odd we can't put them in directly.
Last little question. Should I unset each session variable at the end of the procedure? "set @col1 = null" Wouldn't it be safer to clear those out? I do not use pooled connections now, but I may use this procedure from more than just PHP web server, and other systems may have pooled connections.

I'm probably missing something obvious in this, and/or I'm just asking too much. I do know there will always be some level of timing with user edits. That's not critical, and if it occurs in my current situation, I'll have a log to show users what and who changes things. But I do wonder what I can do at the Mysql procedure level to work around it. Also it seems like Mysql is really lacking in procedure capabilities compared to PostgreSQL or MSSQL, but I am stuck with Mysql for now because of backend system requirements.

Sorry about all the questions. Having hard time finding answers. Thanks so much for you help.

create procedure sp_update_mytable (
    in v_id int,
    in v_val1 varchar(10),
    in v_val2 varchar(10),
    in v_val2 varchar(10)
)
begin

    set @id = v_id;
    set @col1 = v_val1;
    set @col2 = v_val2;
    set @col3 = v_val3;

    set @sql = 'UPDATE mytable
                SET
                col1 = ?,
                col2 = ?,
                col3 = ?
                WHERE id = ?';

    prepare stmt from @sql;
    execute stmt using @col1, @col2, @col3, @id;
    deallocate prepare stmt;

end$$

Edit: A little background.
It occurs to me I may be trying to solve this problem the wrong way or wrong place. Here's why I am asking. In ZF2 I have TableGateway,and I can send just an array to update and only the columns I want to update. Its super easy and convenient. Example:

$result = $this->db->update('mytable',
            array('col2' => 'my new value 2'),
            array('id' => 2)
);

I'd like to make a stored procedure to do this update, but I can't send only the columns I want.  In one system I may want to send col1 and col3, in another, col1 only.  The columns to update are dynamic.

We are building systems that will integrate more than just a web server, which will include some ODBC, some Node.js maybe, possibly others. I wanted to be open to use other systems by building a Stored Procedure to handle certain table updates.

What's occurring to me now is that maybe I can't do this with Stored Procedures. Maybe I need "Middleware" to solve this.  However, the question still stands, is this possible to do in Mysql.  (if not, PostgreSQL?)

If you are concerned about issuing an UDPATE with lots of columns that don't happen to change, don't worry about it.

If you don't have all the possible "new values" and want to build a custom UPDATE every time, then do just that.

$sets = array();
$binds = array();

if (...) {
    $sets[] = "foo = :foo";
    $binds['foo'] = $foo_value;
}
if (...) {
    $sets[] = "bar = :bar";
    $binds['bar'] = $bar_value;
}
...

$sql = "UPDATE ... SET " .
       implode(', ', $sets) .
       " WHERE ...";
$sth = $pdo->prepare($sql);
$sth=>execute($binds);

Stored Procedure

When using CONCAT() to construct a query, be sure to use QUOTE() around any values that as suspect -- such as might have quotes in them, or might be subject to 'sql injection'.

See: String Functions in the MySQL 5.7 Reference Manual.


Why wouldn't statistics update if all rows in a table were replaced? We have several "reporting" tables in a SQL Server 2012 SP1 instance. Every hour, a job runs procedures that delete all the rows from these tables and then insert current data (involving lots of recursion and aggregations) into them. Each contains thousands of rows. Each such table is queried many times per day. The statistics on both the clustered and non-clustered indexes on these tables show outrageous things like 94,000% of rows updated, with the last statistics update being several days ago. Auto Update of statistics is set to true.

I would have thought that the statistics would get updated every time the table was queried for the first time after the contents were last replaced, but that apparently is not happening. Why?

Unfortunately after searching SolarWinds' KB for about 10 minutes I cannot find what "Ineffective Statistics" measures. That information would be helpful. However, based on your question I'll infer that it means that the Statistics objects may be out of date or have taken quite a few modifications and Database Performance Analyzer has not detected an update of some sort. Without more information from the product vendor, it's hard to tell.

Having said that, there are a few things that you should know.

First, you can verify what's going on with your server right now (e.g. when you get the alert) and compare it against your monitoring tool. Here's a script that I use:

use [mydatabasename];
go
select
    [schema_name] = sh.name,
    [table_name] = t.name,
    [stat_name] = s.name,
    [column_names] = stuff((
                              select ','+index_col(t.name,sc.stats_id,sc.stats_column_id)
                              from sys.stats_columns sc
                              where sc.object_id = s.object_id
                                and sc.stats_id = s.stats_id
                               order by sc.stats_column_id
                              for xml path('')), 1, 1, ''
                            ) ,
    s.stats_id,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.unfiltered_rows,
    [sample_rate] = (100.*sp.rows_sampled)/sp.unfiltered_rows,
    modification_counter,
    [default_mod_threshold] = case when sp.unfiltered_rows < 500
                                                   then 500
                                                   else ( .20
                                                          * sp.unfiltered_rows
                                                          + 500 )
                                              end ,
    [2371_mod_threshold] = sqrt(1000 * sp.unfiltered_rows),
    [default_mod_hit] = case when sp.modification_counter > case
                                                              when sp.unfiltered_rows < 500
                                                              then 500
                                                              else ( .20
                                                              * sp.unfiltered_rows
                                                              + 500 )
                                                              end then 1
                                           else 0
                                      end ,
    [2371_mod_hit] = case when sp.modification_counter > sqrt(1000
                                                              * sp.unfiltered_rows)
                                          then 1
                    end

from sys.stats s
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
cross apply sys.dm_db_stats_properties(s.object_id,s.stats_id) sp
order by t.name, s.name;

Most of it is self-explanatory. However, I added the modification threshold for a statistics object at default and with TF 2371 included. The default threshold is an interesting calculation, while TF 2371 is the square root of 1000 * Table Cardinality. I included a calculation to include whether or not each of these thresholds had been hit yet. So run that inside the alerted database and see if what SolarWinds is seeing comports with what is occurring on the server right now. If it isn't then there's probably a timing issue in there somewhere such that the alert was generated before a stats update but after a massive amount of data change.

Having said that, it's important to know how AUTO_UPDATE of statistics actually works. There isn't a clock-like sweep that goes through and constantly updates statistics. Instead, the statistics are updated when the statistic object is loaded to compile a query plan. If the statistic is rarely/never used then it won't be updated unless you manually update it or if it happens to get caught up in an index rebuild as a dependent object for the index. However, if you have an index that's never getting used then that's a different situation altogether.

So in summary:

Verify what the alert is telling you
Know the factors that lead to a stats update


Howto restore Trigger filesystem copy (*.TRG and *.TRN files) in addition to mysqldump? We use mysqldump for backup (without Trigger/Events/Routines included).

mysqldump --opt --flush-logs --single-transaction --ignore-table=mysql.event --all-databases

The triggers we copy with cp to backup folder. We use Galera 5.5.41-MariaDB-wsrep.

After restoring the mysqldump, howto restore the Triggers? Simple cp or are other tasks necessary?

# find . -name *.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/user_login.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/conversation.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/conversation_participant.TRG
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/accounttovacancyrelation.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user.TRG
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike.TRG

# find . -name *.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_conversation_participant_insert.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_conversation_update.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_update.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_user_login_insert.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_delete.TRN
./DB_SE_0_E7D2D18F_A20B_4FFF_89A7_5C10A103B505/trg_accounttovacancyrelation_insert.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_DELETE.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike_AFTER_INSERT.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcommentlike_AFTER_DELETE.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_INSERT.TRN
./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment_BEFORE_DELETE.TRN

example content of files

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment_BEFORE_DELETE.TRN
TYPE=TRIGGERNAME
trigger_table=eventcomment

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/user_AFTER_INSERT.TRN
TYPE=TRIGGERNAME
trigger_table=user

# cat ./DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED/eventcomment.TRG
TYPE=TRIGGERS
triggers='CREATE DEFINER=`test`@`%` TRIGGER `DB_SE_0_BEA81F9B_1391_4942_AC7D_F5CBD7CCBCED`.`eventcomment_BEFORE_DELETE` BEFORE DELETE ON `eventcomment` FOR EACH ROW\nBEGIN\n    DELETE FROM eventcommentlike where eventcommentlike_comment = OLD.eventcomment_id;\nEND'
sql_modes=0
definers='test@%'
client_cs_names='utf8'
connection_cl_names='utf8_general_ci'
db_cl_names='latin1_swedish_ci'

You shouldn't have to copy .TRN and .TRG files.

Just make sure when you run mysqldump to sue the --triggers option. It is already set by default. You also need the TRIGGER privilege in order to dump triggers.


Speed up 180M+ rows returned by simple SELECT with PostgreSQL I have performance issues on a SELECT statement which returns 180+ out of 220+ of millions of rows on my managed PostgreSQL. The table structure is the following:

CREATE TABLE members (
    id bigserial NOT NULL,
    customer varchar(64) NOT NULL,
    community varchar(64) NOT NULL,
    member varchar(64) NOT NULL,
    has_connected bool NULL DEFAULT false,
    CONSTRAINT members_customer_community_members_key UNIQUE (customer, community, member),
    CONSTRAINT members_pkey PRIMARY KEY (id)
);

The "guilty" SELECT query is:

SELECT
    community,
    member,
    has_connected
FROM
    members
WHERE
    customer = :customer;

I have already indexed the table:

CREATE INDEX members_idx ON members USING btree (customer, community, has_connected, member);

and the query behave well for most of the customer value. However, I have a customer, let's call it 1234, which represents 80 % of the table, so the query planner prefers to scan the whole table according to the following ̀explain analyze` result:

Seq Scan on public.members  (cost=0.00..5674710.80 rows=202271234 width=55) (actual time=0.018..165612.655 rows=202279274 loops=1)
  Output: community, member, has_connected
  Filter: ((members.customer)::text = '1234'::text)
  Rows Removed by Filter: 5676072
Planning time: 0.106 ms
Execution time: 175174.714 ms

As I said earlier, my PostgreSQL is a managed instance of PostgreSQL 9.6.14 hosted on Google Cloud Platform with 10 vCPUs and 30 GB RAM. I am rather limited to the available flags , so the only PostgreSQL options tuned on this instance are:

max_connections: 1000
work_mem: 131072 KB
maintenance_work_mem: 2000000 KB

What are my options to solve this issue and to strongly reduce the query time, preferably below 30 seconds if possible ?

As you mentioned you are trying to get 80% of the rows of your table and it is normal that postgreSQL optimizer decide to scan the table rather than get data from the index. Unfortunately postgres does not support queries with index hints like MS SQL server, so it is not possible to compare the results of index scan vs sequential scan but I am absolutely sure that you will not get better results with index scan.

Another question is why you need 180mln rows? What do you want to do with this data?
I assume that this is an analytical query so you can try some column store technologies.
Postgres have cstore_fwd extension available so you can try it out.


Switching values in a column with one update statement You find that an error in a system has been incorrectly naming men (M) as women (W) and vice versa in the database. The columns only allows for one character. Without using any temp tables, write one update query to resolve this.

This question was asked at a recent interview I had, and I'm going into more interviews that may have similar questions so I wanted to get an idea of how to handle this.

You want to use a CASE expression of some type.

In SQL Server the code would look like this:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  WHEN gender = 'W' THEN 'M'
                  ELSE gender END

Edit:   As stated in the comments (and some of the other answers) the ELSE isn't necessary if you put a WHERE clause on the statement.

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  WHEN gender = 'W' THEN 'M' END
WHERE gender IN ('M','W')

This avoids unnecessary updates.  The important thing in either case is to remember that there are options other than M & W (NULL for example) and you don't want to put in mistaken information.  For example:

UPDATE TableName
SET gender = CASE WHEN gender = 'M' THEN 'W'
                  ELSE 'M' END

This would replace any NULLs (or other possible genders) as 'M' which would be incorrect.

A couple of other options would be

/*Simple form of CASE rather than Searched form*/
UPDATE TableName
SET    gender = CASE gender
                  WHEN 'M' THEN 'W'
                  WHEN 'W' THEN 'M'
                END
WHERE  gender IN ( 'M', 'W' );

And a more concise

/*For SQL Server 2012+*/
UPDATE TableName
SET    gender = IIF(gender = 'M', 'W', 'M')
WHERE  gender IN ( 'M', 'W' );


PL/SQL operating system call I'm trying to lauch a system call from Oracle Apex, directly or indirectly. It seems to only accept PL/SQL procedures, so I iterated towards this:

begin

  :p3_request_id := sequence.nextval;

  insert into request (id, user, target, operator)
  values (:p3_request_id, :p3_user_id, :p3_target_id, :app_user);

  -- part that doesn't work:
  host('/home/oracle/scripts/dbq/fork.py ' || :p3_request_id);

end;

Unfortunately:

ORA-06550: line 9, column 3: PLS-00201: identifier 'HOST' must be declared
ORA-06550: line 9, column 3: PL/SQL: Statement ignored

Upon further research, I've come to discover that for this to work I must declare this procedure and run a background daemon script... This seems rather convoluted and fragile (one more process to worry about, monitor, etc.). Is there any other way to launch a system process?

Technically, you could create a Java stored procedure or a dbms_scheduler external job that you invoke from your APEX process.  Doing so, however, would almost certainly be a mistake.

First, you'd have the issue of concurrency.  If you did invoke your Python script from within your APEX process, for example, that process would start immediately and would not wait for your transaction to complete.  Assuming you made a synchronous call, when your script ran and accessed the database, the row you inserted into the request table would not be visible because your transaction would not have committed yet.  If you made an asynchronous call, the row might or might not be visible because the transaction may or may not have been committed yet.  Additionally, your APEX process might encounter an error later on in some other page process that would cause the transaction to be rolled back which would also cause the script not to be able to see the row.  Breaking up the transaction so that you commit before calling your script would ensure that the row was visible but would probably create a number of other data consistency and data quality issues in your application.

It would almost always make more sense to implement some sort of queuing mechanism instead.  Whether you use Oracle AQ or whether you just write into the request table and have separate job that reads from the table and runs your Python code is less important than the existence of some sort of queue.  That decouples the two pieces of code-- the code that writes requests can have proper transactional boundaries while the code that reads requests doesn't have to worry about trying to process requests that haven't been committed yet or that may have been rolled back.  That is not convoluted and fragile-- that is a much more robust approach.  Yes, you potentially need to monitor the job that is processing requests (you may be able to rely on existing monitoring of the Oracle alert log or of the log of whatever scheduling tool you already use).  But that's generally much easier than trying to debug why some requests didn't get processed or why the external script keeps failing to find rows.


Where and how to store my weird datamart I could really use some help here.

Here's my situation.

I'm building a datamart in SQL Server 2005, which will furnish a single report (for now).  We have 26 clients (medical organizations) that will be using this report. Each client will have between 1 and 2,000 users accessing it at any given time (most likely to average in the 50's but it needs to scale). There will be 26 versions of the same rdl, one for each client.  Each one will access its own respective client database.

The interface for said report is SSRS 2012.  This is our first 2012 report - the rest are still 2005, and the rdl will access a 2005 database.  I am not using OLAP; the SSRS report runs SPs and views.

We built a virtual server and installed SQL 2012 to host the report (both these virtual servers live on the same physical machine, in case that matters). Nothing else will be running on the SQL 2012 virtual server.

These are the facts about the environment...

Our system is not OLTP heavy - with one exception, which I'll describe below, it's all Read (except for the ETL of course).

We have one client-facing database per client, 26 altogether. In these we store transactional data, rolled-up data, some report-ready flat tables and a ton of T-SQL code that crunches numbers when clients pull up reports in SSRS.  I'll refer to these databases as "operational" because, for me, they will essentially function as ODS's.

The operational databases are loaded by a horrific ETL process (clients have varying load schedules - usually monthly or weekly).  I'll be building a mini ETL process (hopefully not so horrific) to populate the datamart from those operational databases.

All of the datamart's dimensions are conforming, but due to HIPAA restrictions, some of them (like Physician and Patient) can't be stored in a central database, and neither can the fact table. So there will need to be 26 versions of the same fact and dimension tables, one for each client.

There is a real-time component to our system. Physicians and nurses can enter transactional data via our site and all reports need to reflect the changes immediately. As for the datamart, they'll only affect the fact table. For what it's worth, this is why I decided not to use SSAS. I know diffs process really quickly but it just feels like too many moving parts.

I plan to create a trickle-in fact table with a view that combines it with the main fact table.  Again, I'll need 26 of these.  A pared down version of my new ETL process will need to run upon each user edit.

Here are my questions...

Where should I store the 26 sets of datamart tables?

On a dedicated 2005 server, away from the SSRS server and the operational databases?
On the same server as the operational databases but in dedicated dds databases?
Within the operational databases themselves?
On the SQL 2012 reporting server?
On the moon?  Other?

Where should I store the 26 trickle-in fact tables?

In the same database as the main fact tables?
On the same server as the operational databases but in dedicated DDS databases?
Within the operational databases themselves?  This seems logical to me given that they'll need to be unioned at runtime...

Should I create a central database for non-sensitive dimensions?

Maybe create a cloning process to copy them to the individual DDS's?
Or is it simpler to just have 26 of these darned things as well?

With all of these questions, I’m concerned about good design practice, but mostly about the performance of the report and the performance of the ETL that will need to run upon user edit.

I hope all of this made sense.  I would very much appreciate any feedback!

EDIT: @Jon Seigel - There will be 26 versions of the same rdl, one for each client.  Each one will access its own respective client database.

EDIT: @JNK - I merged accounts and read the faq's. Hopefully I'm responding correctly now.

My initial thoughts would be solution of two DBs per client, one for the datamart and one for the ODS. The ODS would be in full recovery mode as I don't think you can recreate the data here without retrieving it again from the upstream sources at your clients, if at all. The datamart may be in simple recovery mode as all the source data is coming from the ODS and can reloaded from scratch if needed, but the trickle-in fact table makes it complicated. I'm inclined to move the fact table into the ODS to have better options to enforce RI and other data cleansing needs before it goes to the datamart (via a DML trigger or seperate scheduled ETL perhaps). Also, if you can make the datamart DB read only in between ETL cycles, you'll benefit from not having SQL have to manage locks during the report requests.

I don't think you have an option to use a central schema/DB for the non-sensitive content. From a scalability perspective, having each client's data structures in their own containers allows you migration options to faster disks or another host if needed without impacting the other clients or sustaining a larger downtime. From a business logic perspective, if a particular client needs a business rule change that impacts the schema, will the central DB be able to accomodate it without negative impact? Also from scalability, think of the risks caused by locks on a table caused by one client impacting other clients concurrently connected.


Does VACUUM FULL need ANALYZE I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this.

I'm setting up a weekly DB maintenance job for a 9.3 server.

Part of this will be a VACUUM FULL. It's a smallish DB and I have a decent weekend maintenance window so I can run the FULL without any issues.

Is there any point in adding the ANALYZE option to the command?

According to the 9.3 documentation:

  VACUUM FULL rewrites the entire contents of the table into a new disk
  file with no extra space, allowing unused space to be returned to the operating system.

If the table is completely recreated then do I need to specifically ask for ANALYZE to happen or are the statistics updated automatically as the rows are written to the new version of the table?

The statistics are produced from the values in the columns. As these values are not changed by VACUUM FULL or even by VACUUM, a fresh ANALYZE is not specifically required.

If the database is in a maintenance window, it's a good opportunity to run it, though. Its execution time is likely to be insignificant compared to VACUUM FULL.

By default, a DBA will rely on autovacuum/auto-analyze rather that doing this manually, with the exception of the manual ANALYZE for when a new table that was just bulk-loaded or heavily modified is immediately involved in a query for which a good planning can't be obtained without good statistics.


How to pivot without fixed columns in TSQL? I'm struggling with pivoting a quite simple table. All examples and tutorials on the web, are not what I'm looking for, so maybe you guys can help me out here (I must say that my T-SQL knowledge isn't so good...)

Let me explain the situation:

I have a table Trucks

CREATE TABLE [dbo].[Trucks](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NOT NULL)

SET IDENTITY_INSERT [dbo].[Trucks] ON
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (1, N'AAA-BBB')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (2, N'AAA-CCC')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (3, N'BBB-WWW')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (4, N'SKL-POL')
INSERT [dbo].[Trucks] ([Id], [Name]) VALUES (5, N'QAS-ZSD')
SET IDENTITY_INSERT [dbo].[Trucks] OFF

Each truck has an amount of compartments. Each compartment has a name and a capacity

CREATE TABLE [dbo].[Compartments](
    [Id] [int]  IDENTITY(1,1) NOT NULL,
    [TruckId] [int] NOT NULL,
    [Compartment] [nvarchar](50) NOT NULL,
    [Capacity] [bigint] NULL)
SET IDENTITY_INSERT [dbo].[Compartments] ON
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (1, 1, N'C1', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (2, 1, N'C2', 4000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (3, 1, N'C3', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (4, 1, N'C4', 4000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (5, 1, N'C5', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (6, 2, N'Vak 1', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (8, 2, N'Vak 2', 6000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (9, 2, N'Vak 3', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (10, 2, N'Vak 4', 5000)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (11, 3, N'1', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (12, 3, N'2', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (13, 3, N'3', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (14, 3, N'4', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (15, 3, N'5', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (16, 3, N'6', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (17, 3, N'7', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (18, 3, N'8', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (19, 3, N'9', 500)
INSERT [dbo].[Compartments] ([Id], [TruckId], [Compartment], [Capacity]) VALUES (20, 3, N'10', 500)
SET IDENTITY_INSERT [dbo].[Compartments] OFF

I want to write a SP that returns a list of compartments for a certain Truck (based on the TruckId).
This is the easy part:

SELECT * FROM COMPARTMENTS WHERE TruckId = @p_TruckId

This gives me a table that looks like this:

What I want to do, is return a table that has the compartment name as header and the capacity as value.

This will be a table with only 1 row of values. The number of columns depends of the number of compartments for that given truck.

I looked into the PIVOT function, but you need to know the amount of columns in advance for that. I also found a dynamic PIVOT example, but I can't seem to make it work in my situation.

Can somebody assist me here? That would be great!

EDIT: A link to the example I've found

Prior to writing a dynamic SQL query, you should always write a version that is hard-coded so you can get the syntax correct. So the first thing you need to do is write a working PIVOT query for any of the TruckId values that you need.

Static Version:

Let's say you need TruckID = 3, your code for the PIVOT would be similar to the following:

select Name, TruckId, [1], [2], [3], [4], [5], [6]
from
(
  select Name, TruckId, Compartment, Capacity
  from Trucks t
  inner join Compartments c
    on t.Id = c.TruckId
  where t.Id = 3  -- your truck id here
) d
pivot
(
  max(capacity)
  for compartment in ([1], [2], [3], [4], [5], [6])  -- your Compartment values here
) p;

See SQL Fiddle with Demo

Dynamic Version:

Now, your problem is that you are going to have a variety of Compartment values for any of your TruckIds so you need to write something that will accept any TruckId and produce the results that you want.  The PIVOT function will work but first you'll need to concatenate a SQL string with all the Compartment values for each TruckId.

First, you'll define all your parameters:

DECLARE
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @ParmDefinition NVARCHAR(500),
  @TruckId as int;

set @TruckId = 3;  -- this would be the value you submit via your SP
set @ParmDefinition = '@id int';

Next, you'll concatenate the list of the new column headers, using FOR XML PATH and STUFF:

select @cols = STUFF((SELECT ',' + QUOTENAME(Compartment)
                    from #Compartments
                    where TruckId = @TruckId
                    group by id, Compartment
                    order by Id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');

Once you have the list of columns, you'll create the full sql string that will be executed. This should be similar to the static version above:

set @query
    = N'SELECT Name, TruckId, ' + @cols + N'
        from
        (
            select t.Name, c.TruckId, c.Compartment, c.Capacity
            from #Trucks t
            inner join #Compartments c
              on t.Id = c.TruckId
            where t.Id = @id
        ) x
        pivot
        (
            max(Capacity)
            for Compartment in (' + @cols + N')
        ) p ';

The last step is executing the sql string:

exec sp_executesql @query, @ParmDefinition, @id = @TruckId;

See a demo.  This gives a result of:

Name    TruckId 1   2   3   4   5   6   7   8   9   10
------- ------- --- --- --- --- --- --- --- --- --- ---
BBB-WWW 3       500 500 500 500 500 500 500 500 500 500