How to troubleshoot COPY FROM inserting less rows then expected? I have a CSV (tab-delimited in fact) file with 7,590,051 lines, according to wc -l, which I want to import using COPY FROM. I removed the table's primary key, so it has no constraints.

I ran COPY customer FROM '.../customer.dsv' WITH DELIMITER E'\t' CSV HEADER; and it reported 7,588,671 rows imported and no errors, so it's missing 1,379 rows (already discounting the header line).

Since PostgreSQL didn't report any errors, how do you suggest I troubleshoot which rows are missing (and why they are missing)?

It could be normal if there are newlines in certain text fields. Newlines are allowed when the value in the field is enclosed by double quotes.
And obviously that makes the number of lines in the file greater than the number of records.

Example :

$ cat file.csv
1,"ab
cd",2
3,"efgh",4

$ wc -l file.csv
3 file.csv

=> create table csvtest(a int, b text, c int);
=> \copy csvtest from 'file.csv' with csv

=> select * from csvtest;
 a |   b   | c
---+-------+---
 1 | ab    | 2
   : cd
 3 |  efgh | 4
(2 rows)


SELECT...FOR UPDATE - Will only an UPDATE release the lock? I'm using a SELECT ...FOR UPDATE in a Java PreparedStatement.  I may or may not need to execute the update depending on the results of the SELECT.

If I execute a rs.close(); or pStmt.close(); without doing an update, will MySQL release the lock? Is there another way to release it, or do I have to execute the update?

Same question if I used Postgres, too.

Not sure if it matters, but I plan to use rs.updateRow(); for the update.
Also, does MySQL support column-level locking?

Basically, I want to increment a counter in the selected row, but only if the row's status column is ACTIVE.  If it is not ACTIVE, then I just want to release the lock without an update so other threads/processes can read/write that row.

Don't know about MySQL, but in Postgresql, the lock is only valid for the duration of the current transaction, so:

you must have started a transaction
the lock will be released if you commit or rollback the transaction

If you don't start an explicit transaction, then the lock will be released right away.


Is partial synchronisation possible between two databases on different servers? Imagine a scenario where you have a primary instance of a database and a secondary instance that is automatically synchronised - pretty much as with Availability Groups.
My question is whether I could add additional objects to the secondary instance - in particular a materialised (indexed) view - without needing that view to synchronise back to the primary.
Is this possible?
("Why?", I hear you ask? Because I don't want the performance hit of maintaining the view to impact the primary instance. It would be find for the secondary to use async-commit mode, if needed.)

The only built-in data synchronization technology that meets your requirements, I think, is Transactional Replication.
See the FAQ on replication:

How do I add or change indexes on publication and subscription databases?
Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

This means you can have different objects (indexes, views, etc) on each replica.


Receiving error Error: 18059, Severity: 20, State: 1 in the SQL Logs I'm using SQL Server 2008 Sp3

Lately I'm having errors in the SQL Logs:

  Error: 18059, Severity: 20, State: 1.

      The connection has been dropped because the principal that opened it subsequently
      assumed a new security context, and then tried to reset the connection under its
      impersonated security context. This scenario is not supported. See "Impersonation
      Overview" in Books Online.

I have found via Profiler the relevant originated server and application.

I searched and found someone that says that I need to change the connection pooling in the application's connection string.

Below is the relevant Web.Config file:

<?xml version="1.0"?>
<appSettings>
    <add key="Main.ConnectionString" value="Data Source=db-002\db2;Initial
Catalog=APPDB;TRUSTED_CONNECTION=YES"/>
    <add key="ConnectionHash" value="==QMr1meolHO3YWdTNFOzcTO"/>
    <add key="Database" value="[APPDB]"/>
    <add key="UseApprole" value="True"/>
</appSettings>

I changed it to the following in our test environment:

<?xml version="1.0"?>
<appSettings>
    <add key="Main.ConnectionString" value="Data Source=db-002\db2;Initial
Catalog=APPDB;TRUSTED_CONNECTION=YES;Pooling=false"/>
    <add key="ConnectionHash" value="==QMr1meolHO3YWdTNFOzcTO"/>
    <add key="Database" value="[APPDB]"/>
    <add key="UseApprole" value="True"/>
</appSettings>

I've reset the application, services and IIS. Checked the application and it works.

For some time I didn't receive these errors anymore, but after 3 hours they reverted.

Can anyone give me an idea what can I do?

The post that Roni Vered referred to in 2013 did not say simply say: "change the connection pooling in the application's connection string."

The text said to that there was a problem with using "an Application Role ... with connection pooling."  The answer was: Turn Off Connection Pooling.

This could be done in the application's connection string by including "; Pooling=False".

This worked back in the mid-2000's. Likely it will still work for you if you are still using SQL Server 2008.

The no longer correct path to the original post was: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=473777&SiteID=1

A suggested workaround from 2007: On each call if the approle is already in use, then skip the setting of the approle again. Sample code:

IF NOT EXISTS (SELECT * FROM sysusers
    WHERE name = USER_NAME()
     AND isapprole = 1)
 EXEC sp_setapprole ...


Sql Group by on subquery rows I have this table :
ArretProductionJournee(Id, DateArret,HeureDebut,HeureFin,EnumArret)
Example :
DateArret   ||HeureDebut                 ||HeureFin                 ||EnumArret
2020-11-30  ||2020-11-30 14:00:00.000    ||2020-11-30 15:00:00.000  ||PS
2020-11-30  ||2020-11-30 16:00:00.000    ||2020-11-30 17:00:00.000  ||HI

i want to sum the datediff(HeureDebut,HeureFin) in columns for each EnumArret
so i run this query :
SELECT ArretProductionJournee.DateArret,
(select
sum (datediff(minute,ArretProductionJournee.HeureDebut,
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'HI')as HI,

(select
sum (datediff(minute,ArretProductionJournee.HeureDebut,
ArretProductionJournee.HeureFin))
where ArretProductionJournee.EnumArret Like 'PS') as PS

FROM            dbo.ArretProductionJournee
                where ArretProductionJournee.EnumArret Like 'HI'OR
                ArretProductionJournee.EnumArret Like 'PS'
group by  ArretProductionJournee.EnumArret, dbo.ArretProductionJournee.DateArret

Result :
DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   NULL
2020-11-30  ||60   ||   NULL
2020-11-30  ||NULL ||   60

The result i want is Grouping the sum by the date and the enumArret:
DateArret   ||HI   ||   PS
2020-10-30  ||12   ||   0
2020-11-30  ||60   ||   60

Rather than subqueries, use conditional aggregation:
SELECT
      DateArret
    , SUM(CASE EnumArret WHEN 'HI' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS HI
    , SUM(CASE EnumArret WHEN 'PS' THEN DATEDIFF(minute,HeureDebut, HeureFin) ELSE 0 END) AS PS
FROM dbo.ArretProductionJournee
WHERE EnumArret IN('HI', 'PS')
GROUP BY
    DateArret
ORDER BY
    DateArret;

Below is more complete sample data and schema to match your desired result.
CREATE TABLE dbo.ArretProductionJournee(
     ID int NOT NULL IDENTITY CONSTRAINT PK_ArretProductionJournee PRIMARY KEY
    ,DateArret date NOT NULL
    ,HeureDebut datetime2(3) NOT NULL
    ,HeureFin datetime2(3) NOT NULL
    ,EnumArret char(2) NOT NULL
);
INSERT INTO dbo.ArretProductionJournee(DateArret, HeureDebut, HeureFin, EnumArret)
VALUES
     ('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:05:00.000', 'HI')
    ,('2020-10-30', '2020-10-30 15:00:00.000', '2020-10-30 15:07:00.000', 'HI')
    ,('2020-11-30', '2020-11-30 14:00:00.000', '2020-11-30 15:00:00.000', 'PS')
    ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'HI')
    ,('2020-11-30', '2020-11-30 16:00:00.000', '2020-11-30 17:00:00.000', 'XX');


postgresql - how and why indexes are bigger than their tables I'm using postgresql 9.3 and trying to understand how and why indexes are bigger than their tables.

Sample output:

 database_name | database_size |                          table_name                          | table_size | indexes_size | total_size
---------------+---------------+--------------------------------------------------------------+------------+--------------+------------
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_sends"                       | 57 MB      | 131 MB       | 189 MB
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_started"                      | 17 MB      | 39 MB        | 56 MB
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_stopped"                      | 16 MB      | 35 MB        | 51 MB

I'm running the following query to get the table and index sizes.

SELECT
    table_catalog AS database_name,
    pg_size_pretty(pg_database_size(current_database())) As database_size,
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_catalog,
        pg_database_size(current_database()) AS database_size,
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name, table_catalog
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

Is my query correct? What would cause indexes to be bigger?

Possible reasons:

Numerous and probably overlapping indexes on the table; have a look with \d
Bloat due to high update churn can sometimes affect indexes more than tables, depending on update patterns. Examine the size of each individual index to see if it makes sense.
GiST indexes, if used, can be quite large

Unlike what I originally thought this is not an issue with TOAST out-of-line storage not being counted, since pg_table_size includes TOAST tables.

Note that if you're concerned about index bloat and decide to REINDEX some of all of the involved indexes, consider setting a non-default FILLFACTOR first if the table is subject to lots of updates (or inserts+deletes). Otherwise you'll take a write performance hit because the index doesn't have any space to insert new values so it'll force lots of page splits and be less efficiently structured.


Dumps after CHECKDB error I have a little problem with a two-node mirror and customer requirements:

There is a maintenance window every night from 01:30 to 03:00, within which I have to run rebuild/reorg and checkdb (physical_only). If Murphy happens both jobs run on the same index/table and SQL Server creates a (mini-)dump. Due to the dump, the principal doesn't respond within the set timeout period and the mirror roles change, leading to app problems.

We just have that 90 minute window during the night and customer/PFE wants us to run both jobs in this window. DBCC runs for ~80min and rebuild/reorg for ~30min.

Any suggestions to get around those Murphy-moments or to avoid that dump?

SQL Server 2012 SP3 CU7 used by an older Axapta Installation DB-Size
900GB-1TB (data files on SSDs)

Index Maintenance with Ola Hallengren:

EXECUTE dbo.IndexOptimize @Databases = 'PROD',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@SortInTempdb = 'Y',
@MaxDOP = 0,
@LogToTable = 'Y',
@TimeLimit = 3600

DBCC CHECKDB also from Ola Hallengren with Physical_only option.

I don't know if there is a "real" need to run those rebuild/reorgs every night but the customer told us to do so (also suggested by MSFT PFE).

We're in communication with the customer about expanding the maintenance window, but I want to know if there are other suggestions/tips.

The actual DBCC CHECKDB error message is:

  DBCC CHECKDB (XXXX) WITH all_errormsgs, no_infomsgs, physical_only executed by Username found 2 errors and repaired 0 errors.
  Table error: Object ID 111391516, index ID 2, partition ID 720 57595795734528, alloc unit ID 72057595826864128 (type In-row data), page (7:14130686).
  Test (IS_OFF (BUF_IOERR, p BUF->bstat)) failed.
  Values are 133129 and -4

Defragmenting indexes when those indexes are stored on Flash/SSD is close to pointless.  I'd recommend performing the reindex far less frequently.  See Paul's answer here regarding page density and fill factor for mitigating the effects of index fragmentation.

Updating statistics, on the other hand, will provide important clues to the query optimizer.  If auto-update-stats is disabled for the given database, ensure you are updating statistics via a nightly job.

Ensure you run DBCC CHECKDB as often as possible to ensure corruption does not negatively affect your RPO.  You may consider offloading DBCC CHECKDB to a non-production instance.  This consists of automated backup-and-restore to the non-production instance, then automatically running DBCC CHECKDB on that non-production instance.  You'll probably need to license the machine where you run DBCC CHECKDB since it would be dealing with production data, however I'd check with your Microsoft rep to be certain.

If you're seeing SQL Server creating memory dumps, that indicates a problem with SQL Server that should be brought to the attention of Microsoft Tech Support - they may either have a hotfix available to resolve the issue, or they may offer some further advice about how to resolve that issue.  The problem may indeed be a bug that needs to be fixed.


Why does mysql binlog use different isolation level? Default isolation level:

mysql> SELECT @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

Binlog:

mysqlbinlog mysql-bin.000078 --base64-output=DECODE-ROWS
....
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

What is the reason for using different level isolation in binlog?
How it can affect an application code written for REPEATABLE READ isolation level?

It seems like there are no concurrent threads during replication or data recovery process, so a lower isolation level helps us to avoid unnecessary locks and speed up the process.


Is there a way in PostgreSQL to set an default owner for all future create table statements in a session We have a program that transfers cobol data (index files) to a database.

Until now we transferred the data to an oracle database.

In the beginning of the program I logged in with user sys and then I altered the current schema to ink. I did this in this way, because ink has no privileges to create a table.

Then I created a table and the table owner was automatically ink, not sys.

We recently have switched databases from Oracle to Postgres.

Here I log in as "postgres", then I switch to the "ink" schema with:

set search_path to 'ink';

Then I create a table and the table owner automatically is postgres, not ink.
So this behavior is reverse.

What do I have to do in order to get the same behaviour as with the oracle database:

In my scenario the table owner should automatically be ink, not postgres.

It would be nice if I would not have to write

ALTER TABLE ... OWNER TO ink;

after each create table command.

Be warned: I'm relatively new to PostgreSQL and so I'm not sure if this is a good practice or not, but...

One way I can think this can be addressed is by executing a REASSIGN OWNED statement at the end of the process that has created all your objects in the database, like this:

REASSIGN OWNED BY CURRENT_USER TO ink;

This way, even if the tables are momentarily owned by the postgres user, you'll see them owned by ink once the creation process ends.


how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server How can I compare two Oracle database servers which are on two different machines?

We need to ensure that the new server is setup correctly.

For example, one machine is running Oracle 10g on Windows, and the second machine is running Oracle 11g on Linux.

How can I ensure that we have correctly set up all the parameters on the new 11g machine?

Some of the items I need configured are:

Temp Tablespace size
SGA size
any other particular fine tuning that has been applied on the Oracle 10g machine.

You don't want them to be running with the same parameters in the first place. Very likely that you want to enable the 11g features. Things that you might want to make sure to be at least equal size are the memory footprint, temp tablespaces and undo tablespaces.

In Oracle 11g you might be tempted to use Autmatic Memory Management. There are some issues with it so it might be smarter to configure your shared pool and database buffer according to the 10g database. In the run check if you are not over or undersized.

Best is to hire a dba to help you with the setup, next best is to dive into Oracle® Database 2 Day DBA 11g Release 2 (11.2) There is a lot to learn from.


RaiseError on SQL Server stored procedures ALTER PROCEDURE sp1
as
begin
IF (@PersonId=0)
    RAISERROR(N'Error already raised. See previous errors.', 16, 1);
    break
end

I want something like the following.

If no value in the variable, how to break the procedure in SQL Server?

If you want to raise error and exit from the procedure, you may want to try return. Example below.

alter PROCEDURE sp1
@PersonID int
as
begin
   IF (@PersonId is null)  --you can check 0, if that is the intention
   begin
       RAISERROR(N'Error already raised. See previous errors.', 16, 1);
       return;
   end;
   print '@PersonId is not null';  --Condition when error is not raised
end;


Create t2 instance and connect via Wokrbench I've created an t2 RDS instance inside a VPC. The instance is set to Publicly avaiable: No. I wasn't able to connect to the instance using MySQL Workbench. Is there an option to connect to the instance in that state?

I also tried to create with Publicly available: Yes, but I get the following error upon creation:

  Cannot create a publicly accessible DBInstance because customer VPC does not support DNS resolution and/or hostnames.

How can I solve this? -- I want to be able to create a database in t2 and connect to a t2 RDS instance from Workbench.

customer VPC does not support DNS resolution and/or hostnames.

A quick search for the error message uncovers what it means: your VPC (Amazon Virtual Private Cloud) is not configured to support DNS resolution and/or hostnames, both of which are required to make an RDS instance inside VPC publicly accessible.

The solution appears to be to configure the VPC with these capabilities enabled, after, of course, considering why they might have been disabled to begin with.

http://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/vpc-dns.html

https://forums.aws.amazon.com/thread.jspa?threadID=153914


Copy tables to another SQL Server keeping the identity property Objective
I want to make a exact copy of 10 tables from a database on a SQL Server into my local SQL Server. The local copy is used for testing.
Problem
The copied tables all have the identity property on their ID column set to 'no'. As far as I understand, that setting allows the column to auto-increment (I get an error when I try to insert values without giving a value for ID).
The tables on the server all have this setting set to 'yes' and now when I try to set it to 'yes' on the local copies, my software (SQL Server Management Studio) asks me to drop and re-create the whole table and therefore losing all the data.
What I've tried
The following solutions all copy the tables with their data, but don't copy the identity property:

Copy the tables each with a query (using linked servers): SELECT * INTO NEW_DB.dbo.tblCopy FROM OPENQUERY([server], 'select * from OLD_DB.dbo.tblData')
Import the data with Task -> Import Data... (Using this tutorial)

I tried this both with "Enable identity insert" checked and unchecked

Create the table on the destination database and simply copy/paste the data into it (this did not work at all - no data was inserted)

Notes

I would rather not rely on third-party tools. I am using SQL Server Management Studio 2018.
I am aware of this question on Stack Overflow which talks about the exact same problem. But as already stated above, enabling "enable identity insert" did not copy the identity property.
I am also aware of this other question asked on Server Fault, both of its answers are about a third-party tool though, which I do not want to use.
I only have read permission on the source server/database.
The tables contain up to 6,000 rows.
The source database contains several more tables, I only want to copy 10 specific tables.

It might be easiest for a one-off exercise to use the Generate Scripts SSMS feature to script out the tables. Right-click the database, choose Tasks, then Generate Scripts.
Be sure to check the advanced options so you script exactly what you need. That will create the tables (and, optionally, things like indexes) including the IDENTITY property.
You can then load the tables using any convenient method, including the Import and Export Data Wizard you already have experience with. You will need to select the "enable identity insert" option because the destination tables will have that property now.
Yes, it would be nice if the import and export wizard handled identity, but it is a general tool designed for multiple types of data source, and identity is proprietary.


What mechanisms do you use to notify third parties of future schema changes? How do you notify third parties of future SQL Server schema changes other than through external documentation?

I've started implementing a deprecation warning extended property on objects that will be going away in the future but wanted to see if anyone had success through another approach.

We always strived to make schema changes backward compatible. There are probably situations where this isn't possible, and obviously situations where they want to know so they can take advantage of new features, but we were able to do this consistently without breaking anything. I wrote a series of tips on mssqltips.com about this (look under "Database Development" here).

Extended properties aren't a very useful mechanism for this, IMHO. Since nobody is required to ever look at these, and since many tools ignore them completely, you may as well write out sticky notes and paste them on their monitors. How you would notify them in specific cases I guess depends on what impact the change will have on them and whether or not there are other ways you can implement the change to reduce or eliminate that impact.


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

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

2 options;

Build the container and ADD or COPY a my.cnf file into place with your desired options.
Bind mount a config file into the container as part of the run command

I think there’s possibly a 3rd but I’m not in the position to test but you can possibly add startup options to the docker run command e.g.

docker run -d {other necessary options} mysql/mysql-server:latest —lower-case-table-names


Native replication from Percona XtraDB Cluster to a standard stand-alone slave I'm replicating from one node of a three-member Percona XtraDB cluster to a separate slave, using regular MySQL replication.  The node is set to write binlog in ROW mode, with log_slave_updates=true specifically for this.  Replication seems to work fine.  Still I wonder, given that XtraDB is an extension of InnoDB which the slave is using, is there a risk of some updates not replicating?  Is it worth it to replicate into an XtraDB slave, and how would that need to be configured?  I want the native async replication to the slave as it is an analytics DB and the goal is to offload the production Percona cluster from analytics queries, so it should not be just a regular cluster member.

The binlog doesn't contain different structures when the storage engine is XtraDB vs InnoDB.

In the sense of interoperability, XtraDB is not an "extension" of InnoDB -- it's a fully-compatible replacement, that handles some internal operations differently but is still very much "InnoDB" wherever it is exposed outside the server core.

For what it's worth,  I've taken "datadir" -- including ibdata1 and the ibd files from a stopped MySQL server, put them on a MariaDB server (which also uses XtraDB instead if InnoDB) and had zero compatibility issues.

The only caveat that comes to mind is the same as any MySQL async replication setup: the slave must be running the same or newer version of MySQL as its master (which, by the way, can be any one of thr PXC machines).

The reason for this "slave version the same or newer" rule is that the binlog format is forward-extensible only: when new capabilities are added to the binlog format, a newer master cam break an older slave, since the slave may not always understand what it is reading from the binlog, and replication will hit a hard stop (not a soft fail).  A newer slave should never fail to understand what an older master writes to its binlog, since, officially, replication is supported across major releases (e.g., 5.1 to 5.5) but in practice, 5.1 to 5.6 also seems completely compatible.

The parallel minor version or newer, of MySQL Server, MariaDB, or -- seemingly the most foolproof -- Percona Server... should work fine as an async slave.

If you start out the slave with Identical data, and you replicate every table and schema, missing transactions should never happen. Don't even try to filter what replicates and try to replicate a subserlt of your schemata/tables unless and until you really understand the issues this can encounter.  Replicating everything is the default behavior.

Yes... it is very much worth it to set up async slaves for reporting.  The cluster members should already have the config they need for this to work (othet than the user account the slave will use to connect).


Phantom comparison data in SQL Server Reporting Services - How do I remove it? I'm trying to write a query that will allow me to include or exclude a filter in my where statement based on whether a parameter passed in is set to true or false.  So far my statement is

IF @IncludeAttended = 'true'
begin
    SELECT        id, fullName, Address, PhoneNumber, NeverAttended
    FROM            Guests_View
    WHERE        (GroupID = @groupid)
end else begin
    SELECT        id, fullName, Address, PhoneNumber, NeverAttended
    FROM            Guests_View
    WHERE        (GroupID = @groupid)
    AND NeverAttended = 'false'
end

So I'm trying to filter based on whether or not the guest has ever attended an event.  Running the above SQL in Management Studio will get me appropriate results with no issues, but when I try to run this in SSRS, I get an error message Conversion failed when converting the varchar value 'AM13425' to data type int.

I have tried several different statements and methods to find where this string might be, but I can't find it anywhere in the database, nor can I find it anywhere in the project itself, so as far as I can tell, the data doesn't exist.  Wiping the table and repopulating it with fresh test data doesn't help either, the message just resurfaces.  Also, if I take out the @IncludeAttended parameter, and just run only one half of the statement above (among various iterations), I can get the results back, but I need this report to allow this filtering.  What else can I try, and where can I look for this phantom data that's causing me the problem?

Could you check the data type of "id" in the report dataset?

Maybe when the NeverAttended = 'false' is used, the data returned is having text data whereas the dataset is having Id as integer type.


Dirty Read on SQL Server The SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is at different part of the "BEGIN".  Is the below two query behave the same?
Will the views, viewOne and viewTwo, inherit the dirty read?  Or the view needs to have an explicit "NOLOCK"

Query 1

set transaction isolation level read uncommitted
begin
    select col1 from viewOne
    select * from viewTwo
    select * from table1
end

Query 2

begin
    set transaction isolation level read uncommitted
    select col1 from viewOne
    select * from viewTwo
    select * from table1
end

From this documentation - SET TRANSACTION ISOLATION LEVEL - under REMARKS

  Only one of the isolation level options can be set at a time, and it
  remains set for that connection until it is explicitly changed. All
  read operations performed within the transaction operate under the
  rules for the specified isolation level unless a table hint in the
  FROM clause of a statement specifies different locking or versioning
  behavior for a table.

You can use this query to determine the isolation level you are currently at:

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID


Many to Many Table with multiple same-column references I'm designing a document translation DB and struggling to connect the non-generic many-to-many table.

Each document has a title and summary.

Translated Document table:

CREATE table translated_documents (
  document_id         bigint references documents (id),
  source_language_id  int not null,
  source_title_hash   uuid, -- md5 hash cast to uuid
  source_summary_hash uuid,
  PRIMARY KEY (document_id)
);

CREATE TABLE translations (
  content_hash            uuid not null, -- holds either source_title_hash or source_summary_hash
  translation_language_id int  not null,
  content_translation     text not null,
  PRIMARY KEY (content_hash, translation_language_id)
);

An attempt at a many-to-many table, trying to reference the content_hash column twice.

CREATE TABLE translated_documents_translations (
  document_id         bigint references translated_documents (document_id),
  source_title_hash   uuid not null references translations (content_hash),
  source_summary_hash uuid not null references translations (content_hash),
  PRIMARY KEY (source_title_hash),
  CONSTRAINT same_hash_not_allowed CHECK (source_title_hash <> source_summary_hash)
);

My thinking is that documents can be translated into many languages, and translations can work for many documents with the same title or summary. Is there a better way to set this up?

I think unless you have to save space or share a lot of translations you could just do one-to-many mapping documents -> document_translations

CREATE TABLE document_translations
(
    document_id BIGINT NOT NULL,
    language_id INT NOT NULL,
    title TEXT,
    summary TEXT,
    CONSTRAINT document_translations_pk PRIMARY KEY (language_id, document_id)
);

Then you could use external translation memory to help with translations.

If you want to share translations then I don't think you need translated_documents_translations as many-to-many table. Just use translated_documents like this:

CREATE TABLE translated_documents
(
  document_id BIGINT NOT NULL
    CONSTRAINT translated_documents_pkey
    PRIMARY KEY
    CONSTRAINT translated_documents_documents_id_fk
    REFERENCES documents,
  language_id INTEGER,
  title UUID,
  summary UUID,
  CONSTRAINT translated_documents_title_fk
  FOREIGN KEY (title, language_id) REFERENCES translations,
  CONSTRAINT translated_documents_summary_fk
  FOREIGN KEY (summary, language_id) REFERENCES translations
);

CREATE TABLE translations
(
  content_hash UUID NOT NULL,
  language_id INTEGER NOT NULL,
  translation TEXT,
  CONSTRAINT translations_content_hash_language_id_pk
  PRIMARY KEY (content_hash, language_id)
);

And JOIN documents -> documents_translations -> translations


T SQL, Get new customer added or deleted from historical table I have a Table containing historical data. Every day the same staff list will be added with the new dates (between 1200-12500 staff each day however only a few staff might be newly added todaysDate or they may have left the company yesterday and not showing on todays list).
I want to know each day which staff wasn't on yesterday list, it is new staff and the staff was showing yesterday but not today because they left company. I have tried the following but no joy please help
my historical table is like below and I need a script to get the rows for ID4 and ID5 only.

StaffID
StaffName
CensusDate

ID1
Staff1
02/03/2021

ID1
Staff1
03/03/2021

ID2
Staff2
02/03/2021

ID2
Staff2
03/03/2021

ID3
Staff3
02/03/2021

ID3
Staff3
03/03/2021

ID4
Staff4
02/03/2021
not showing today

ID5
Staff5
03/03/2021
is newly added today

ID6
Staff6
02/03/2021

ID6
Staff6
03/03/2021

ID7
Staff7
02/03/2021

ID7
Staff7
03/03/2021

SELECT StaffID, StaffNAME, CensusDate
FROM table1 AS A
LEFT JOIN table1 AS B

ON A.StaffID = B.StaffID AND A.date = GETDATE())  AS date)
and B.date = CAST(DATEADD(day, -1, convert(date, GETDATE())) AS date)
AND A.staffID IS NULL

Not sure I completely understand your intention or the difference between Table1 and Table2.
If you are trying to find just the IDs that only occur once in the last two days.
SELECT StaffID, count(StaffID)
FROM Table1
WHERE CensusDate >= cast(dateadd(day, -2, getdate()) as date)
Group By StaffID


What is a Storage Definition Language (SDL)? In my Databases lecture, the teacher mentioned that SQL is not just a data definition language (DDL), data manipulation language (DML) and view definition language (VDL), but also a storage definition language (SDL). The last usage doesn't come with any further explanations or examples.

Searching for extra information on the internet regarding using SQL as a SDL yields no relevant results. Could anyone give an example of using SQL as a storage definition language?

Edit:

Would a specification such as MySQL's SET storage_engine=MYISAM; count as a storage definition? It doesn't really relate to the data being stored, but rather, how it's stored.

You may want to ask your teacher to define "SDL".  I don't think the term appears in the SQL standard.  I found this reference (see page 3), and I disagree: SQL does not define the physical structure, the physical bytes per field, the physical field order, the physical sorting, or "mixed records", whatever that might mean.  SQL doesn't define fields at all, or records, or say anything about how the data are stored on the disk if they're stored on the disk.

That said, every vendor does provide some means for the administrator to define where the data will be kept, and many offer varying levels of control over physical storage aspects (e.g. ISAM, clustered indexes).  AFAIK all that stuff lies outside the SQL standard.  I've used DBMSs that understand SQL DML, but completely separate mechanisms for defining tables and storage.


Do user database settings affect maintenance job times for system databases? I have an SQL Server 2008 and for maintenance solutions I use Ola Hallengren scripts. Recently, out of 15 databases I have on this server, I made two changes in three of them (not system databases, but user databases). I removed mirroring and changed recovery model from full to simple. This done running sql commands on SQL Server Management Studio.

From the day I made these changes, I noticed strange behavior on Ola Hallengren's sql job for integrity check and full backup on system databases. To understand, while 2 minutes were enough to run and finish the job, now it needs approximately 10 minutes for the same job. As a result, at this time I/O increases on SQL server and it gives timeouts.

I don't know and I didn't find anywhere if running the following commands could possible influence system databases.

ALTER DATABASE <database_name> SET PARTNER OFF
and
USE master ;
ALTER DATABASE <database_name> SET RECOVERY SIMPLE ;

where database_name is the users database I made the change, not the systems.

Is it possible made those changes affect anything on system databases?

Those changes will have no affect on the amount of time system backups take to run.

As a first step I would change the job schedules so that the Integrity checks and System backups are not running at the same time.

Also make sure that there are no other jobs running at the same time as either of these.

If you don't have monitoring in place, jump on the server when the backups are running, have a look and see what load the server is under. You can also try moving the Fulls back by an hour or so and see if the time change makes a difference.


Survey database design: first version. Are there errors? I have to represent surveys with questions like these ones:

Studying those groups of questions I found these rules:

A survey has questions' groups.
A questions' group has a name and questions (all question will have
the same number of options).
A question has a question text and a group of possible options to
answer it.
An option has a name and a type.
A type could be: Text, Yes/No or Checkbox.
An answer is a value that user gives to an question's option for a
given survey.

Do you think that those requisites are OK?

With, those requisites I have design the following conceptual model (here I call form instead of survey):

I need to make surveys dynamics. I'm going to reuse group of question, questions and/or options.

I offer a more complete model, from an actual solution.

The 'type' is associated to the question per-se (not to its items, as stated at the fourth item from the question).

A question may be of single-line or multiple-line textual box or composed by several items. For each item may be assigned a complementary question (eg. 'Country?' - 'Other' - 'Which: ___').

HOW the question and its items will be presented to the user depends on their type.

The model has been made flexible enough to represent several structures of questions.

The 'FormQuestionType' entity has an attribute to identify the ASCX (ASP.net component) responsible of rendering questions of this type.

It's possible to make a question dependent of one or more of the previous ones (eg. 'How many children?' may be made dependent of 'Do you have any children?').

There's prevision for data validation, from 'required' constraint to regular expressions.

For every major entity has been defined the mandatory and unique attribute named 'identification', allowing queries based upon textual codes instead of numerical ids (eg. 'How many children?' question, of id '351', is also identified by 'HOW_MANY_CHILDREN').

And, for the items of a given questions, there may be defined an external source of data (eg. 'city' question has its options retrieved live from corporate 'city' table).

The actual model provide versioning, but, for clarity's sake, I present a stripped down version of it.


DB2: Is it good to create the LONG table space to use 32 KB data page Our DB2 database (V9.7) has only defined one table space to store all the big objects like XML.
And xml size could be different. Some are about 2kb average. And some are 7kb average.
So does it make sense to set the table space to use 32kb data page?
How to judge it? I have once seen that one data page can only contain one xml row.
Is that true?

Check out the general answer I gave to you on your other question.

If you are storing your XML as a LOB, then I guess I'd see what is the common average size per table. If the average size is 4MB or less (which it sounds like), then you'd only need a 4K tablespace (see Space requirements for table, table 1).

If you are storing the XML as an XML data type in DB2, then you actually get some performance gains over LOB, especially if you inline the XML (and aren't doing updates to it all that often). This may be the best since you mention sizes less than 32K. You may wish to check out XML storage page for in-lining XML within the table vs outside for XML storage.


how to implement read-only rows or snapshots of rows In my application I have objects with 3 levels of hierarchy. To model this in SQL Server, I have a ParentObject table with an Id column, ChildObject table with an Id and a foreign key ParentObjectId, and a GrandchildObject table with an Id and a foreign key ChildObjectId. A ParentObject may have many ChildObjects and a ChildObject may have many GrandchildObjects.
At any point in time, users would like to take a snapshot of the current state of a specific ParentObject. A snapshot of a ParentObject may not be modified after it has been created but the snapshot ParentObject can be deleted. The ParentObject snapshot needs to retain the entire object hierarchy, i.e. include ParentObject, ChildObjects, GrandchildObjects. There can be multiple snapshots of a particular ParentObject. All snapshot ParentObjects and the current version of ParentObject must be SELECTable. How can this be implemented using SQL Server 2019?
Users do not have direct access to my database. Instead they call a REST API which has its own account to access my database. Since there is only 1 account accessing the database, I don't think Row-Level Security can fulfil my requirements.
One implementation I have considered is to have a [ParentObject].[SnapshotTime] DATETIME2 NULL column and for the 3 object tables to each have a [IsReadOnly] BIT NOT NULL column. When a user wants to snapshot a ParentObject, I copy the ParentObject row to a new row, setting newRow.SnapshotTime = currentTime and newRow.IsReadOnly = 1. I then cascade copy the ChildObjects and GrandchildObjects, setting the new rows's IsReadOnly = 1. When an UPDATE or DELETE is requested, I check whether a row's IsReadOnly = 0 before performing or denying the action.
Is this a sensible approach? Are there any built-in SQL Server features to achieve my aims? Are there better/faster/more efficient approaches?

You could make them Temporal Tables, and add a ParentObject.SnapshotTime column.  Then every time you update that column, you'll get a new version of the parent object, and you can issue temporal queries on the related tables.


How do you remove a mirrored database SQL Server 2012 I have mirrored databases (synchronized), how do I delete such a database correctly? I tried

use master
alter database test0224aEd62 set single_user with rollback immediate
drop database test0224aEd62

by get error

  Msg 1468, Level 16, State 1, Line 2 The operation cannot be performed
  on database "test0224aEd62" because it is involved in a database
  mirroring session or an availability group. Some operations are not
  allowed on a database that is participating in a database mirroring
  session or in an availability group. Msg 5069, Level 16, State 1, Line
  2 ALTER DATABASE statement failed. Msg 3752, Level 16, State 1, Line 3
  The database 'test0224aEd62' is currently joined to an availability
  group.  Before you can drop the database, you need to remove it from
  the availatility group.

The operation cannot be performed on database "test0224aEd62" because it is involved in a database mirroring session or an availability group.

The error is self explanatory .. you cannot drop a database if it is part of DB mirroring, AlwaysON or replication.

I normally avoid using GUI when I have option of doing same thing using TSQL ...

For future readers, you can break database mirroring using below TSQL command (run it on the secondary server)

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Once mirroring is broken, then you can drop that database using

alter database <<db name>> set single_user with rollback immediate
go
drop database <<db name>>
go


Is Postgres sharding available on Aurora (AWS) I need to shard and/or partition my largeish Postgres db tables. The Postgres partitioning functionality seems crazy heavyweight (in terms of DDL). I see talk from <=2015 about pg_shard, but am unsure of the availabilty in Aurora, or even if one uses a different mechanism.

I would like something on the order of a "Key" Redshift-style distribution method. Furthermore, it would be great if there was some way to do it on a multi-colum basis (I can make a "fake concatenated column" if need be)

From @nathanlong's comment to my question:

  On Sep 25, 2018, they announced "Amazon Aurora with PostgreSQL Compatibility Supports PostgreSQL 10" - https://aws.amazon.com/about-aws/whats-new/2018/09/amazon-aurora-postgresql-compatibility-supports-postgresql104/. They explicitly mentioned "native table partitioning", though there are features in PosgreSQL 11 that presumably are not included; see https://pgdash.io/blog/postgres-11-sharding.html.


ORA-00937 when calculating a sub-value in an aggregate select statement The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question.

I want to use an aggregate-function in a select-statement which needs some additional calculation to yield the result. My main goal is to keep the whole calculation in one statement since the result is to be displayed verbatim in the backend software.

Let there be a table T defined by this DDL

create table T(
  ID number,
  NUM number,
  TXT varchar(32)
);

insert into T (ID, NUM, TXT) values (1, 1, 'Text1');
insert into T (ID, NUM, TXT) values (2, 2, 'Text2');
insert into T (ID, NUM, TXT) values (3, 0, 'Text3');

Now the objective is to sum all values of the num-attribute for all selected rows. I can do this by using

select sum(num) from T

Now for the tricky part, assume there is a second table from which we calculate an additional value to be combined with the result of our first select. The select involved will return a single number by being an aggregated value by itself. (To keep the schema simple for this demonstration, I use the same table, but the described problem remains regardless of the second table being used)

select case (select count(*) from T) when 0 then 1 else 0 end from dual;

When combined, we will leave out the select ... from dual part which is only used to get the intermediate result displayed. Together the query will be

select (case (select count(t1.num) from T t1) when 0 then 1 else 0 end)*sum(t2.num) from T t2;

where we assign aliases to each table to make the usage clear.

The result of this query is an Oracle exception: ORA-00937: not a single-group group function. Since the case-expression yields a single value and no reference is made to any resources outside the expression, I wonder what basic principle I violated by this select-statement. In addition to that, MySQL and PostgreSQL yield the desired result.

As I was searching for a solution, I also tried this select-statement:

select (case when (exists (select 1 from T where NUM = 0)) then 0 else 1 end)*sum(NUM) as p from T where NUM > 0;

Here I disposed of the inner aggregate-function count(t1.num) to rule out problems with nested aggregations. This statement gave the same Oracle error message, while the calculation of the additional value caused no trouble:

select (case when (exists (select 1 from T where NUM = 0)) then 0 else 1 end) as p from dual;

Question: Can someone please explain what goes wrong? (And especially I wonder if Oracle does not implement standard SQL, or of MySQL and PostgreSQL extend the standard).

Technically the solution to my problem would be a join where both, the aggregate and the additional value are computed separately, and then combined in the outer select-statement. My focus does not lie on how one can solve this problem, but on am I missing something in Oracle, or is Oracle missing something?.

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression.  As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

or

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end


Will my sqlite database occupy less space if I insert nulls instead of zeros I have a table where most of the values inserted in one of the columns are 0. Should the database be smaller if I insert nulls instead of zeros?

In the SQLite record format, both NULL and the integer 0 need only a type code and no storage for the actual value itself.

In any case, you should use whatever value works best with your queries, and optimize only when needed.


Combining Tables in MySQL I have two tables, which are very similar, in MySQL.  I want to combine them into a single table with new column names.  Some of the column names in Table1 or Table2 may not be used in the new combined table.  The data types for the similar columns (the ones which will be combined) are the same. I also want to be sure to capture any duplicates from both tables.  Each table has it's own auto-incremented primary key.

Yes, it is possible to do. There is a CREATE TABLE ... SELECT clause in MySQL that is designed specifically for that. I recommend you start with a simple SELECT statement that clearly defines what the new table would contain. E.g. if you have a mammals table:

CREATE TABLE mammals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO mammals (name) VALUES
    ('dog'),('cat'),('whale');

and a birds table

CREATE TABLE birds (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO birds (name) VALUES
    ('chicken'),('duck');

You can build this query:

SELECT name FROM mammals
UNION ALL
SELECT name FROM birds;

The result will contain all animals. However, you may need to keep the animal type in the new table. Modify the query like this:

SELECT name, 1 as type FROM mammals
UNION ALL
SELECT name, 2 as type FROM birds;

and create a new table

CREATE TABLE animaltype (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     type CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animaltype (type) VALUES
    ('mammal'),('bird');

Now, when you are completely happy with the result execute:

CREATE TABLE animals AS
SELECT name, 1 as type FROM mammals
UNION ALL
SELECT name, 2 as type FROM birds;

ALTER TABLE animals ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Optionally:

DROP TABLE mammals;
DROP TABLE birds;

You can also create a foreign key for the animaltype.type column


Meaning of query in pgbouncer.log I could not find any documentation about pgbouncer log, I'm assuming it has the same meaning as the SHOW STATS ?

req/s - number of requests since last stat period
in b/s - Bytes per sec received ?
out b/s - Bytes per sec sent ?
query - Total query duration in micro secs ou avg query duration in micro secs ?

I need to better understand this query column to implement reliable log monitoring.

Another issue I'm facing is the fact that I seldom see more than 0 requests on log lines and most of the times the query value is greater than 10s.

After checking pgbouncer source code in stats.c file, I realized that values shown in the log are all average values for that logging period.

Avg number of requests is mostly zero because there's less than 60 queries in a logging period which is about 1 minute.


reduce number of innodb pages read by a query In my mariadb I have a large innodb table (600 millions rows). Simple queries on the table using a secondary index perform slowly so I wanted to check the number of innodb pages read to fulfill one of these queries. For that I compared the status variable innodb_pages_read before and after the execution of a query. In average 230 pages are read to fulfill a query that will return 200 rows.

The question is: is it normal that each query must read so much pages ? I have another similar table (but smaller with only 300 millions rows) and it takes on average only 30 pages read to fulfill a query that returns 200 rows. How could I reduce the number of pages read (and thus I/O) ?

Short Answer:  It depends.

Let's start by assuming "simple query on secondary key", leads to one row and you have

INDEX(x)
SELECT * FROM t WHERE x = 123

Then, for 600M rows, and a cold cache (buffer_pool), here is the analysis:

Drill down the BTree for INDEX(x) looking for 123:  about 5 blocks need to be read because the BTree will be about 5 levels deep.  The leaf node will give you the primary key column(s) for the one (as assumed) row with x=123.
Drill down the BTree for the PRIMARY KEY and data:  also about 5 blocks.

Total 10 blocks.

Let's expand that to saying that there are 200 rows with x=123.

5 blocks to get started in the index.
Scan forward in the index, perhaps 2 more blocks.  (Rule of Thumb:  100 rows per block.)
For each of the 200 x=123 index entries, drill down the PK to find a row.
The first one (assuming cold cache) needs to read about 5 blocks (as before).
The other 199 will need fewer than 5 blocks.  For one thing, the root node is already there.  Are the x=123 values randomly scattered?  Or is x, say, a TIMESTAMP that marches in sync with the PK (which might be an AUTO_INCREMENT?)  So, these 199 could average as low as nearly 1.

Total:  ~20 to ~600.

Now let's change to steady state:

If your table will fit in cache, then eventually the number of blocks read will be zero.  But, let's say your 600M rows is too big for that.

Again, I invoke a Rule of Thumb:  "Ignore the drilldown, but not the leaf node".

for 1-row:  2 blocks.
for 200 rows:  3+200 blocks -- 3 in the index; 200 (or fewer, depending on randomness of x versus PK) in the data.

Rick's "Rules of Thumb".

Text/Blob

If there are large text or blob columns and you are fetching them, then "off-block" storage will be hit.  This will add perhaps at least 200 more reads -- depending of number/size/existence of the wide columns in each row.


SQL Server URL for DBEdit2 There is a cool program out there called DBEdit2, which can be used for SQL Server. Here is where you can get it free:

http://dbedit2.sourceforge.net/

The problem is that I can't make it work! It gives me the following error (conf info crossed out):

This is what I put in the login details:

And this is what I get if I click details:

Any ideas at all? Can anyone else make this program work?

I've made sure that I have my SQL Server set to allow both SQL Server and Windows authentication mode.

I've ensured that the server is running and that I have browser running.

I don't know.

As @a_horse_with_no_name points out, your connection string is incorrect.  The java jdbc driver needs to know the instance name ( it doesn't play well with the backslash notation ), which will be overridden if you're specifying the port ( 1433 is the default instance port - you might not have to specify the instance name at all if that's what you're trying to connect to ).

Try something along the lines of this instead for your connection string:

  jdbc:jtds:sqlserver://JT__428/j___test_db;instance=J__MS14


Foreign keys to primary tables or nested table In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the first diagram OR is it OK for those columns to reference the ProductUserComment table as shown in the second diagram?

I like how it's setup in the second diagram as it reduces the spider web in visualizations.

Are there any downsides to this second approach?

Versus

I think the 2nd is better.  Because I guess you should not allow a ProductUserCommentAction if there is no ProductUserComment.


Clustered Index - Query Performing Index Scan Why does the below query perform an Index Scan rather than an Index Seek?

I would have thought the Index would act as a covering index?

CREATE NONCLUSTERED INDEX [IX_CustomerFileDate]
ON [dbo].[UserData]
(
    [CustomerID] ASC,
    [FileDate] ASC
)
INCLUDE ([UserID],[User])

-- query
SELECT c.User, c.UserID
FROM ClientData c
WHERE CustomerID = @CustomerID
AND FileDate >= @StartDate
AND FileDate <= @EndDate

Execution Plan posted to dropbox

Thanks!

The Execution plan shows a non clustered index scan:

Because you have a implicit conversion of your first index seek column SQL Server decided to do a full non clustered index scan. It's not doing a clustered index scan because the non clustered index is a covering index. 1)

You can see that the column customerID needs to be converted to a NVARCHAR(50) datatype.(red conversion). Because the column first needs to be converted, it can't be used. Since it's the first column in your index, it effectively makes the index useless for seeks or estimates.

Why is SQL Server doing a implicit conversion of the column? Because on the right side of the = sign you feed it a Nvarchar. Your not doing this explicit, since also the parameter @CustomerID first gets implictly converted to a NVARCHAR(3) (blue conversion).

I presume this is because your are filling @CustomerID with a Unicode value. However, It would suprise me that you would use a unicode values and yet have non unicode datatypes defined in your table. Maybe somebody else could confirm the reason for the blue conversion.

1) I deleted a wrong assumtion about the relevance of the amount of rows returned that Martin Smith was nice enough to point out to me.


Alter table Statement Failed -Msg 207, Level 16, State 1 I wrote a script to add a column in a table and on successful execution update its values.
I know it’s just a simple script but what is wrong with the ‘IF NOT EXISTS’ statement why script between BEGIN and END is not executing and return error . I verified there is no Database level DDL trigger to stop adding column in database.

  Msg 207, Level 16, State 1, Line 24
  Invalid column name ColumnName.

DECLARE @CloumnAdded bit
SET @CloumnAdded = 0

IF NOT EXISTS ( SELECT  1
                FROM    Sys.Columns
                WHERE   [Object_ID] = Object_ID('DBName.dbo.TableName')
                        AND Name = 'ColumnName' )
    BEGIN

        ALTER TABLE TableName  ADD ColumnName varchar(120)

        SET @CloumnAdded = 1

        select @CloumnAdded, 'step 1 '
    END

IF ( @CloumnAdded = 1 )
    BEGIN
        select @CloumnAdded,'step 2'

        UPDATE  TableName
        SET     ColumnName = '2669'

        select @CloumnAdded,'step 3'
    END

GO

Your prolem occurs because of the different steps involved in query execution.  First comes parsing, then binding and finally execution.  In parsing the submitted text is verified as valid SQL and converted to an internal representation.  Binding resolves the names from the submitted SQL to objects inside SQL Server's catalogue.  Unfortuately TableName.ColumnName does not exist when binding is attempted so that part fails.

There are two things you can do*.  First, split the query into two batches, one to create the column, one to update it.  Only run the second if the first succeeds.  Second, put the update in dynamic SQL and run it via exec sp_executesql.  This effectively creates a second batch internally.

*That I can think of right now.  I'm sure wiser heads could suggest others.


group tick data to minute data using MySQL I have the following table, Input. Do you know how to group the tick data into minute interval as shown in the table, Desired Output using mySQL? I read the data directly from csv to mySQL

Criteria

It will take the last price within the minute bar and output as its minute bar. ie If I have the these two tick data, 06:04:59   1444.25, 06:04:59   1444.00, my output will be 0604 1444.00.
Convert the date, 2013-01-02 to 20130102

 Input
        row Date        Time        Price   Vol
        1 2013-01-02    06:04:59    1444.25 3
        2 2013-01-02    06:04:59    1444.00 2
        3 2013-01-02    06:05:00    1446.25 1
        4 2013-01-02    06:05:01    1446.00 2
        5 2013-01-02    06:05:02    1446.35 4
        6 2013-01-02    06:07:01    1444.00 2
        7 2013-01-02    06:07:02    1444.25 4
        8 2013-01-03    16:08:31    1434.00 2
        9 2013-01-03    16:08:52    1434.25 4

Desired output

    row Date        Time        Price   Vol
    2 20130102  0604        1444.00 2
    5 20130102  0605        1446.35 4
    7 20130102  0607        1444.25 4
    9 20130103  1608        1434.25 4

I have an idea but I not sure how to put into mySQL code.
1. Convert the date to YYYYMMDD and time to HHMM
2. Remove all the duplicates having the same YYYYMMDD and HHMM and only keep the last entry

Thank you for your help and advices
L

(from Comment)

CREATE TABLE `es_test2` (
   `xdate` date NOT NULL,
   `time` time DEFAULT NULL,
   `price` decimal(8,2) DEFAULT NULL,
   `vol` int(8) unsigned DEFAULT NULL,
   `xcol` int(10) unsigned DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

What you want is something like this which uses the extract(HOUR_MINUTE..)

SELECT t1.date, t1.time, t2.price, t2.vol
FROM (
  SELECT date,
    extract(HOUR_MINUTE from time) AS time,
    max(id) AS maxid
  FROM tbl
  GROUP BY date, extract(HOUR_MINUTE from time)
) AS t1
JOIN tbl AS t2
  ON t1.maxid = t2.id;

The inner selects and groups by the date,hour/min, and grabs the max(id) and count(*). The outer joins back to the table and resolves to find the price.

In other news, why are you storing date and time separately? You should be using a timestamp.


Can't run digest() on PosrgreSQL 9.3 on RDS Following the advice given on another answer, I tried using the digest() function on PostgreSQL 9.3 on AWS RDS:

devdb=> SELECT digest('blah', 'sha1');
ERROR:  function digest(unknown, unknown) does not exist
LINE 1: SELECT digest('blah', 'sha1');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Even though pgcrypto exist:

devdb=> SHOW rds.extensions;

  btree_gin,btree_gist,chkpass,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,ltree,pgcrypto,pgrowlocks,pg_trgm,plperl,plpgsql,pltcl,postgis,postgis_tiger_geocoder,postgis_topology,sslinfo,tablefunc,tsearch2,unaccent,uuid-ossp

How can I enable the digest() function?

Updates

I have co-asked on the AWS RDS forum, will posts mutually updated.

digest() does not seem to exist on the db:

devdb=> \df digest
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

The question was answered at the AWS forum by Shawn@AWS - The rds.extension list shows available, rather than installed, modules.

Thank you for using RDS Postgres. The "rds.extension" parameter shows you what extensions are available in RDS Postgres. You still need to execute the "create extension" command.
Using your test case:

postgres=> show rds.extensions;
... ,pgcrypto, ...
postgres=> create extension pgcrypto;
CREATE EXTENSION

postgres=> SELECT digest('blah', 'sha1');
digest
\x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)

Addendum
pg_available_extensions shows which extensions are available and which are already installed:
postgres=> SELECT * FROM pg_available_extensions;

          name          | default_version | installed_version |                               comment
------------------------+-----------------+-------------------+---------------------------------------------------------------------
 chkpass                | 1.0             |                   | data type for auto-encrypted passwords
 xml2                   | 1.0             |                   | XPath querying and XSLT
 plpgsql                | 1.0             | 1.0               | PL/pgSQL procedural language
 pgcrypto               | 1.0             |                   | cryptographic functions
 postgres_fdw           | 1.0             |                   | foreign-data wrapper for remote PostgreSQL servers
...


How to set up data entry in many-to-many database in Access 2010 with forms/subforms? I have a simple two table database in Access 2010 tracking landowners and properties.  They are related in a many-to-many (M:M) relationship with a link table.  Forms for viewing data have been generated from two queries following tutorial here.  There are two forms with subforms:
'landowners and their associated properties'
and 'properties and associated landowners'.

I would like the option for users to either (1) add a new record to the subform (creating a relationship record in the link table and a new record in the source table for the subform)  or (2) select from a list of existing records to relate to the property / landowner.

So far, I have (1) working.  In the subform, if a new record is inserted it populates the appropriate table and creates a relationship.  If the record is deleted from the subform, the relationship is deleted from the link table but the record is NOT deleted in the subform's source table (child table) -- as desired.

I have attempted a drop-down combobox providing the option to select from existing records in the subform's source table but I get a duplicate error.  Any help?

The general idea is that you create a subform that's bound to your link table, and make one of the fields in the subform a combo box bound to the child record ID that's in your link table. Fill the combo box using records in the child table.

Then you handle the NotInList event on the combo box to prompt the user to add the record to the child table (or whatever is appropriate for your application).

NotInList Event

Then if you want to actually remove rows from the child table when they are no longer referenced by the link table, handle the appropriate deletion event on your subform, and check for unreferenced records.

I've implemented something like this once, but I think it was in Access 2000 on a machine I don't have with me at the moment. I'll try to check it later out of curiosity.


MS Access linked table displays short text instead of Decimal data type I have linked tables in MS Access databases (various versions, 2007-2019) which are connected to SQL server 2017 via Sql server 2017 drivers (eg, msodbcsql_17.2.0.1_x64). Fields that are of data type Decimal(38,20) in SQL server are showing as type Short Text in the linked tables.

According to the Documentation, the data types are directly comparable.

Why might this be coming through as a different data type?

I have discovered that even though the data types are directly comparable, the data precision is not. Access can handle a max precision of 28, hence why the conversion into short text.


pg_restore into a new database I'm using Heroku's Postgres option and have a backup I downloaded from it. I'd like to restore it into a fresh database so I can look at it. I've been trying different commands to little avail. My first try:

$ sudo -u postgres psql < db/backups/myapp_2018-05-27.pg.dump
The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.

So I try:

$ sudo -u postgres pg_restore -d myapp3 -C db/backups/myapp_2018-05-27.pg.dump
pg_restore: [archiver (db)] connection to database "myapp3" failed: FATAL:  database "myapp3" does not exist

I don't really understand the -C option.

  -C
    --create

  Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

  When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

It would seem logical to me to restore into the new database. I digress… (and perhaps that is properly explained here)

I tried creating a fresh database from the template of another, earlier one with the same schema:

$ sudo -u postgres createdb -T myapp myapp3

$ sudo -u postgres pg_restore -d myapp3 -C db/backups/myapp_2018-05-27.pg.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:

<snip>

WARNING: errors ignored on restore: 5

I get a lot of errors about a user that doesn't exist and no data inserted.

I also tried using template0 but that brings up errors about the schema, as I'd expect.

In summary, I'd like to restore from a Heroku Pg backup into a fresh database. I don't know how I can avoid errors about indexes and missing users etc and have the schema ready.

I also tried -O because of this line in the docs:

  With -O, any user name can be used for the initial connection, and this user will own all the created objects.

I seem to have misunderstood that too because, again, the screen was filled with errors.

Edit: Based on the -C linked answer I managed to restore into a cryptically named database, but when I run the rename database command (alter database rtyghjkuyhgh rename to my_app3) it empties all the data :(

Any help or insight, even just a point in the right direction would be very much appreciated.

Postgres version 9.5.12

Let's define a couple of variables to make the rest easier to copy/paste (using names from the original question)

old_db=myapp
new_db=myapp3
db_dump_file="backups/myapp_2018-05-27.pg.dump"

The following assumes that your backup was created with the "custom" format like this:

pg_dump -U postgres -F custom "$old_db" > "$db_dump_file"

To restore $db_dump_file to a new database name $new_db :

dropdb   -U postgres --if-exists  "$new_db"
createdb -U postgres -T template0 "$new_db"

pg_restore -U postgres -d "$new_db" "$db_dump_file"


Get table name from union query? This is my query

SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename3 where Active =1

It is working properly.

Now I want to fetch the table name for the respective productName in future.

So how can I fetch it?

I tried AS.. like this:

SELECT Id, productName, Largeimagepath, Discount, Price, Image
FROM tablename3 AS tablename
where Active = 1;

but didn't get output.

How shall I correct the query and also Improve the performance of query?

Typically when using UNION and you need to know what table a specific row comes from, you'd use a hard-coded value stored in a column similar to:

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1
where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2
where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3
where Active =1;

This will return a new column called Source with an identifier that shows which table the row came from.

As @ypercube suggested in the comments you may also want to consider altering this to a UNION ALL - this will include duplicate but you'll also have an identifier on which table it came from. Using a UNION ALL will eliminate the performance impact of removing dups.

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1
where Active =1
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2
where Active =1
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3
where Active =1;


Drop Tables with Bigger size We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that state for a long time. Is there a solution to drop these tables without mysql hanging. We have a machine which has a memory of 122GB and swap of 60 GB. We use an InnoDB Engine Hope the information suffices kindly help us with this issue

This is an old verified bug in MySQL for InnoDB on larger tables.
As this has been patched quite some time ago, perhaps it's not the same situation, but it sure seems similar.

The workarounds for the older versions are:

Defragment in the filesystem. When you extend many tables the chunks for each end up very fragmented, so the defragmenting can be of benefit for table scans as well as dropping speed. If you don't have a defragmenting program, copying the files somewhere and copying them back will do the job.
When dropping, create a filesystem link to the IBD file. Drop the table in MySQL and the OS won't drop the file in the filesystem because the link exists, so the drop will be fast within MySQL. Remove the link and the OS will finish the deleting work in the background.

If that doesnt work... The answer in this question will allow you to delete ABOUT half of your data.
The idea being that you can remove enough data to stop InnoDB from hanging when you drop the table.


unmanaged data in union Forgive me if this question already ask,not much of db guy here ,
here is what i tried,

select row_number() over (partition by name order by challanto_date) , *
from (
    select
        rma,
        p.id,
        p.name,
        challanto_date,
        CURRENT_TIMESTAMP as fromDate
    from challan_to_vendor cv
    left join challan_to_vendor_detail cvd on cv.id = cvd.challan_to_vendor_id
    inner join main_product p on p.id = cvd.product_id
    union all
    select
        rma,
        p.id,
        p.name,
        CURRENT_TIMESTAMP as toDate,
        challan_date
    from challan_from_vendor cv
    left join challan_from_vendor_detail cvd on cv.id = cvd.challan_from_vendor_id
    inner join main_product p on p.id = cvd.product_id
) as a

Here is my create table script :

challan_from_vendor

  CREATE TABLE public.challan_from_vendor
  (
  id character varying NOT NULL,
  date_ad date,
  rma integer DEFAULT 1,
  CONSTRAINT psk PRIMARY KEY (id)
  )

challan_from_vendor_detail

CREATE TABLE public.challan_from_vendor_detail
 (
 id character varying NOT NULL,
 challan_from_id character varying,
 product_id character varying,
 CONSTRAINT psks PRIMARY KEY (id),
 CONSTRAINT fsks FOREIGN KEY (challan_from_id)
     REFERENCES public.challan_from_vendor (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE NO ACTION
 )

challan_to_vendor;

CREATE TABLE public.challan_to_vendor
(
 id character varying NOT NULL,
 date_ad date,
 rma integer DEFAULT 1,
 CONSTRAINT pk PRIMARY KEY (id)
)

challan_to_vendor_detail

CREATE TABLE public.challan_to_vendor_detail
 (
 id character varying NOT NULL,
 challan_to_id character varying,
 product_id character varying,
 CONSTRAINT pks PRIMARY KEY (id),
 CONSTRAINT fks FOREIGN KEY (challan_to_id)
    REFERENCES public.challan_to_vendor (id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
 )

product

    CREATE TABLE public.product
 (
 id character varying NOT NULL,
 product_name character varying,
 CONSTRAINT pks PRIMARY KEY (id)
 )

Here is my table structures and desire output.

challan_from_vendor

| id       | rma | date |
|:-----------|------------:|:------------:|
| 12012       |        0001 |     2018-11-10
| 123121       |        0001 |     2018-11-11

challan_to_vendor

| id       | rma | date |
|:-----------|------------:|:------------:|
| 12       |        0001 |     2018-12-10
| 123       |        0001 |     2018-12-11

challan_from_vendor_detail

 | id       | challan_from_vendor_id | product_id |
|:-----------|------------:|:------------:|
| 121       |  12012       | 121313
| 1213       |   12012     |  131381

challan_to_vendor_detail

challan_from_vendor_detail

| id         | challan_to_vendor_id | product_id |
|:-----------|------------------------|:------------:|
| 121        |  12                    | 121313
| 1213       |   123                  |  131381

product

  | id         | product_name |
|:-----------|------------:|
| 191313       |    apple   |
| 89113       |      banana |

Output

| ram         | product_id | challan_from_date | challan_to_date|
|:-----------|------------:|:-----------------:|:--------------:|
| 0001       |       191313|     2018-11-10    |2018-11-11      |
| 0001       |       89113 |     2018-12-10    |2018-12-11      |

I think best of it is still to came , but i feel comfortable with **full join **.

SELECT
a1.ram,
a1.product_id,
a1.date_ad as fromDate,
a2.date_ad as toDate
FROM
 (
   select
    ram,date_ad, cvd.product_id
from challan_from_vendor cv left join challan_from_vendor_detail cvd on cv.id = cvd.challan_from_id
group by 1,date_ad, cvd.product_id
) as a1
FULL JOIN
(
 select
    ram, date_ad, cvd.product_id
from challan_to_vendor cv left join challan_to_vendor_detail cvd on cv.id = cvd.challan_to_id
group by 1, date_ad,cvd.product_id
) as a2 ON (a1.product_id=a2.product_id)
ORDER BY 1;


Log-shipping : Broken Chain If a write on log shipping transaction backup gets corrupted on one of the transaction file due to network or media issue, and hence the log shipping chain gets broken, is there any other way to restore the database to the point in time ? I understand one could do full backup or diff backups but to restore to point in time, how can we get the consecutive tran logs to be restored?
Any pointers on this will be beneficial.
Thanks
(Even if the solution is to contact Microsoft or only God can help you, I will buy that :-))

If you are saying your actual backup file is corrupted (the .trn file) but that the backup was successful from SQL Server's perspective, and the source DB is still online, I would take a full backup as soon as possible so you don't lose any data.

You will have to rebuild your log shipping if you've got a corrupt .trn, but your main concern should be having the ability to restore your source data at all if you've got a bad .trn in your chain.


Where does SQL Server store the SA password and SQL Server password policy? On Linux, where does SQL Server store the "SQL Server password policy" and the SA user's password? I get it, "password" isn't secure.

  ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters..

and then,

  ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols..

I'm guessing the policy is actually compiled into the database? And the password gets stored in an obscure location?

The sa user is a SQL Server login and its password is encrypted and stored in the DMV sys.sql_logins (Database Management View) in the master database.
Reference: sys.sql_logins (Transact-SQL)
You might notice that these views can only be found in the following branch:
Server | Databases | master | Views | System Views | ....

The DMVs reference some system base tables, which can be accessed (but shouldn't):
Reference: System Base Tables
The policy you are looking for is built in to the code of SQL Server and is set per default for each new account. When creating a SQL Server Login you can decide to turn off the defaults:

To enforce password policy options for complexity and enforcement, select Enforce password policy. For more information, see Password Policy. This is a default option when SQL Server authentication is selected.

... via CHECK_POLICY=OFF. See the full syntax:
CREATE LOGIN login_name { WITH <option_list1> | FROM <sources> }

<option_list1> ::=
    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
    [ , <option_list2> [ ,... ] ]

<option_list2> ::=
    SID = sid
    | DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language
    | CHECK_EXPIRATION = { ON | OFF}
    | CHECK_POLICY = { ON | OFF}
    | CREDENTIAL = credential_name

<sources> ::=
    WINDOWS [ WITH <windows_options>[ ,... ] ]
    | CERTIFICATE certname
    | ASYMMETRIC KEY asym_key_name

<windows_options> ::=
    DEFAULT_DATABASE = database
    | DEFAULT_LANGUAGE = language

Reference: CREATE LOGIN (Transact-SQL)
If the SQL Server is a member of a Windows Domain, then it will retrieve the password policy from Active Directory. Otherwise the defaults are:

Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the following guidelines:

The password does not contain the account name of the user.
The password is at least eight characters long.
The password contains characters from three of the following four categories:
- Latin uppercase letters (A through Z)
- Latin lowercase letters (a through z)
- Base 10 digits (0 through 9)
- Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%).
Passwords can be up to 128 characters long. You should use passwords that are as long and complex as possible.

Reference: Password Policy


Importing Chinese characters via SSIS I'm trying to import file that contains Chinese characters into a table. File encoding is Big5 (Traditional).

Here is a sample file like one that I need to import: https://www.pastiebin.com/5d7782d9b63fa

Table where file has to be imported into has such a structure:

create table dbo.Test (
      AccountId   numeric(18, 0) not null
    , Province    nvarchar(50)       null
    , City        nvarchar(50)       collate Chinese_Hong_Kong_Stroke_90_CI_AI
    , Country     nvarchar(50)       null
    , Gender      nvarchar(50)       null
)

If I import using OPENROWSET/BULK then all the data is transferred correctly:

select AccountId, Province, City, Country, Gender
from openrowset (
      bulk 'C:\chinese_sample.dat'
    , firstrow = 1
    , formatfile = 'C:\chinese_sample.xml'
) t

Here is a format file that I use: https://www.pastiebin.com/5d7783396f9e4

But if I try to import file using SSIS, then Chinese characters are not parsed correctly.

In Flat File Source I use DataType string [DT_STR] and CodePage 950. Then convert it to Unicode string [DT_WSTR].

Here is how text is imported into table:

As we can see some characters are parsed correctly and some are not.
What am I missing?

The problem appears to be that the lines that are not importing correctly have characters that, encoded in code page 950, having a trailing byte of 0x7C, which is valid, but also happens to be the pipe symbol, which you are using as a delimiter. For example:

SELECT CONVERT(VARBINARY(20), CONVERT(VARCHAR(20), N'四會'
                                      COLLATE Chinese_Hong_Kong_Stroke_90_CI_AI));
-- 0xA57C B77C

Each of those two characters has a trail-byte of 0x7C. The parser seems to be viewing that as a delimiter instead of part of a 2-byte sequence for a code page 950 character. This is why you get a "?" in both "City" and "Country" with the remaining input line in the "Gender" column. The "?" in those two columns is due to the lead bytes 0xA5 and 0xB7 not being valid on their own.

The next line has the same issue:

SELECT CONVERT(VARBINARY(20), CONVERT(VARCHAR(20), N'元朗大坑|'
                              COLLATE Chinese_Hong_Kong_Stroke_90_CI_AI))
-- 0xA4B8 AED4 A46A A77C 7C

This time I kept the delimiter that comes at the end of the "City" value (so it would be clearer that the delimiter is the same byte value as the trail byte of that 4th character). The 4th character, 坑, is encoded as 0xA77C in code page 950. This is why only the 4th character shows up as "?".

So, this might be a bug in SSIS. Or, perhaps it's a configuration issue. Is there a way to indicate that the entire file, not just this one column, is code page 950? Dealing with code pages on a column-by-column basis, in terms of a text file, makes no sense. The entire file is encoded as code page 950, not just that one column. If it's possible to change the delimiter that might help, but it most likely just delays the problem as any delimiter can be encoded as a valid trail-byte value for double-byte characters. Given that OPENROWSET works correctly, I have to believe that this also can (although you aren't setting the file's encoding for OPENROWSET, just the column, so this could still be an SSIS bug).

Please try the following to set the file's encoding within SSIS:

Go to the Flat File Connection Manager Editor (General Page)
Under "Code Page", enter in 950.
Make sure that "Unicode" is not selected.

Also, check the "CITY" column under "Output Columns" and make sure that it is using "Standard Parse" and not "Fast Parse" (since "Fast Parse" is locale-insensitive).


hostname incorrect in sysprocesses I'm running SQL Server 2000. While doing some profiling to trace activity, I noticed some hostnames showing up that were mysterious -- names that shouldn't exist on the network anymore. I got the MAC address for one of them from master.dbo.sysprocesses, looked up the IP address in the ARP cache, and then used nbtstat to resolve that IP to a network name. The name it resolved to was not the name in sysprocesses, nor was the name in sysprocesses resolvable on the network.

So where is this name coming from? Does SQL Server have a hostname cache somewhere that needs to be cleared? How can I fix this so that the correct network hostname shows in my traces?

This is set by the client.

It is passed in via the "Workstation ID" property in the connection string. So I presume that you will need to make some configuration updates on your client machines.


what exactly does an RMAN backup do? I have a database with multiple schemas and data in the tables. I create an rman backup and backup archivelogs. I drop 90% of the tables. I do rman restore and recovery with controfile from autobackup. Checking database tables, nothing has been recovered?

I noticed that if I remove actual oracle database files(.ctl,.dbf/etc) the rman restores those, but I thought the tables would automatically be restored as well.

Please let me know if more info is needed, new to Oracle and Rman.

In the simplest case, when you restore a database, you tell RMAN to recover it to the most recent point in time by applying all the redo (archived and online) that is available.  That will re-apply any statements that were executed during normal database operations including things like dropping tables and deleting data.  If you want to restore a database to a point in time before something bad happened, you'd need to do a point-in-time recovery with something like

RUN
{
    SET UNTIL SCN 1000;
  # Alternatives:
  # SET UNTIL TIME 'Nov 15 2004 09:00:00';
  # SET UNTIL SEQUENCE 9923;
  RESTORE DATABASE;
  RECOVER DATABASE;
}

Normally, you'd specify an UNTIL TIME or UNTIL SCN that was just before the first erroneous command.

In general, though, you would try to avoid using RMAN to recover from human errors.  Generally, doing a full database restore is a last resort and generally there are other transactions going on that you don't want to lose.  If a DBA inadvertently drops a table, for example, you don't want to restore the database and lose all the transactions that other people had made after the table was dropped.  You would generally do a FLASHBACK DROP

FLASHBACK TABLE table_I_should_not_have_dropped
       TO BEFORE DROP

to restore the table from the recycle bin (which is almost instantaneous).  If you deleted


How can I list database names within a mysqldump file? I have a large mysqldump and I need to create the databases before importing them one-by-one. How can I search and list only the database names using the linux or mysql terminal?

PROPOSED SOLUTION
DUMPFILE=mydump.sql
grep "^USE " ${DUMPFILE} | sed 's/;//' | sed 's/`//g' | awk '{print $2}'

EXAMPLE
# grep "^USE " dump.sql | sed 's/;//' | sed 's/`//g' | awk '{print $2}'
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
redwards
scmp_lab
topup_lab
wsms2_lab
zenith_lab
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
redwards
scmp_lab
topup_lab
wsms2_lab
zenith_lab
#

You could collect them in a variable (Note: You must escape the backquote)
# DBLIST=`grep "^USE " dump.sql | sed 's/;//' | sed 's/\`//g' | awk '{print $2}'`
# echo ${DBLIST}
CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine redwards scmp_lab topup_lab wsms2_lab zenith_lab CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine redwards scmp_lab topup_lab wsms2_lab zenith_lab
#

GIVE IT A TRY !!!


Getting postgreSQL binaries without third party tools Where can I download postgreSQL binaries without third party tools like pgAdmin, stackbuilder?

From licencing point of view, can I download binaries from https://www.enterprisedb.com/download-postgresql-binaries and delete pgAdmin, stackbuilder folders and distribute the remaining folders with my product?

Is there any other source where I can download only postgreSQL binaries for linux?

Is there any other source where I can download only postgreSQL binaries for linux?

You can use BigSQL's command line client for that:

-bash-4.2$ python -c "$(curl -fsSL https://s3.amazonaws.com/pgcentral/install.py)"

Downloading BigSQL PGC 3.3.6 ...
Unpacking ...
Cleaning up
Setting REPO to https://s3.amazonaws.com/pgcentral
Updating Metadata

BigSQL PGC installed.  Try 'bigsql/pgc help' to get started.

-bash-4.2$ cd bigsql
-bash-4.2$ ./pgc download pg10

-bash-4.2$ ll -h
total 19M
drwxrwxr-x 4 zaphod users  115 Aug 29 15:50 conf
drwxr-xr-x 3 zaphod users   18 Aug 29 15:49 data
drwxrwxr-x 5 zaphod users   47 Mar  1 22:41 hub
drwxr-xr-x 2 zaphod users   27 Aug 29 15:49 logs
-rw-r--r-- 1 zaphod users  19M Aug 29 15:50 pg10-10.5-1-linux64.tar.bz2
-rwxrwxr-x 1 zaphod users 1.7K Mar  1 22:41 pgc
-bash-4.2$

The archive pg10-10.5-1-linux64.tar.bz2 contains the following top-level directories:

pg10/lib/
pg10/init/
pg10/bin/
pg10/include/
pg10/share/

This can also be used to download a ZIP archive for Windows. "Installation" instructions for BigSQL on Windows can also be found in the above link.


Unable to login to SQL Server + SQL Server Authentication i forget my sql server username and password(sql server 2005).therfore i didn't get into sql server.So please give some answers to me.how can i solve this problem?

Run Sql Server locally with -m and -f flags and login to it as local admin

OR

login as domain administrator


MySQL bug - decimal comparison I'm currently writing unit tests for a stored procedure that calculates the amount of taxes and the tax rate based on the country/province the customer lives in.

Somehow, MySQL considers that "14.975" is different than "14.975".

If I try taxRate < 14.975 or taxRate = 14.975, it does not enter the condition; but taxRate <> 14.975 and taxRate > 14.975 does.

I have the feeling I'm missing something obvious, but I just can't figure it out.

My test code:

  DECLARE totalTax DECIMAL(20,6) DEFAULT 0;
  DECLARE taxRate DECIMAL(10,3) DEFAULT 0;

  SET @roundMode = 0;
  SET @roundPrecision = 2;

  /* caculate TPS only*/

  CALL calculateTotalTaxOnAmount (200, 4, NULL, totalTax, taxRate);

  IF (totalTax <> 10) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_AMOUNT_CANADA';
  ELSEIF (taxRate <> 5) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_RATE_CANADA';
  END IF;

  /* caculate TPS and TVQ */

  CALL calculateTotalTaxOnAmount (100, 4, 87, totalTax, taxRate);

  IF (totalTax <> 14.98) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_AMOUNT_CANADA_QUEBEC';
  ELSEIF (taxRate <> 14.975) THEN

    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'INVALID_TAX_RATE_CANADA_QUEBEC';
  END IF;

My function:

CREATE PROCEDURE calculateTotalTaxOnAmount (
    IN amount DECIMAL(20,6),
    IN countryID INT(10),
    IN stateID INT(10),
    OUT totalTax DECIMAL(20,6),
    OUT totalTaxRate DECIMAL(10,3)
)
BEGIN
    DECLARE break BOOLEAN DEFAULT FALSE;
    DECLARE taxBehavior INT(10);
    DECLARE taxRate DECIMAL(10,3);

    DECLARE TaxCursor CURSOR FOR
        SELECT (t.`rate` / 100),
            tr.`behavior`
        FROM `ps_tax_rule` tr
        JOIN `ps_tax` t
            ON tr.`id_tax` = t.`id_tax`
        WHERE tr.`id_country` = countryID
        AND tr.`id_state` IN (0, stateID)
        AND t.`active` = 1
        ORDER BY tr.`id_state`;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET break = TRUE;

    SET totalTax = 0;
    SET totalTaxRate = 0;

    OPEN TaxCursor;

    tax_loop: LOOP
        FETCH TaxCursor INTO
            taxRate,
            taxBehavior;

        IF (break) THEN

            LEAVE tax_loop;
        END IF;

        IF (taxBehavior = 2) THEN /* cumulative tax */

            SET totalTax = totalTax + (amount + totalTax) * taxRate;

        ELSE /* standalone tax */

            SET totalTax = totalTax + (amount * taxRate);
        END IF;
    END LOOP tax_loop;

    CLOSE TaxCursor;

    IF (totalTax <> 0) THEN

        SET totalTaxRate = totalTax * 100 / amount;

        SET totalTax = roundMoney(totalTax);
    END IF;
END //

Note: If you want to test it locally, replace roundMoney(totalTax) by round(totalTax, 2)

I've made some tests and manage to find some interesting results.

You can use the casting operation to solve the problem:

SET totalTaxRate = CAST(totalTax * 100 / amount AS DECIMAL(10,3));
You can use the truncate operation to solve the problem:

SET totalTaxRate = TRUNCATE(totalTax * 100 / amount, 3);
Somehow, using a select after changing the value of the variable seems to also solve the problem...

SET totalTaxRate = totalTax * 100 / amount;
SELECT totalTaxRate;

I'd rather not use this solution as it returns a recordset.
Using a select into also solve the problem

SELECT totalTax * 100 / amount INTO totalTaxRate;

Note: You can use the truncate or casting operation either inside or outside the procedure (even both), but I'd suggest to do it inside so you don't have use the truncate/casting operation on the output every time you call the procedure.

I still can't assert for sure the root of the problem but, at least, there are some ways to work around it.


At What Point Does Having an Index Become Efficient I've found a lot of resources that mention that adding an index to a table makes searches faster and inserts slower, but only if the table is large.  This creates a tradeoff, which is a design decision, but there should be an approximate table size before which using an index is absurd. (10 rows, for example, is probably way beneath that limit)

Does anybody know about where this limit would be, or know of a resource that would point me in the right direction?

The exact limit is really hard to determine ahead of time.

One thing most people underestimate is the high requirements that an index must fulfill, before it becomes a candidate to be used in a query.

An efficient (nonclustered) index

offers great selectivity, e.g. returns only a very small percentage (< 1%, < 2%) of the total rows. If the selectivity is not a given - SQL Server's query optimizer will most likely ignore this index
should ideally cover the query, i.e. return all teh columns required by the query. If you can create an index that has 1 or 2 index columns, and includes another handful (2-4) columns as included columns and thus you can cover a query - then chances are the query optimizer will use this index. Which also means: if your code is always using SELECT * ..... to fetch all columns, the likelihood of indices being used goes down - quite dramatically, actually

I'm sure there are a ton of other criteria as well - but I would believe these two are the most critical ones. Of course, you should always keep your indices properly maintained (reorganize, rebuild) and make sure the statistics associated with your indices are up to date.

PS: nonclustered indices on foreign key columns are a special case; by default, I would always recommend adding those, since they help speed up both referential integrity checks, as well as JOIN's on those FK constraints. But even here, it's absolutely valid to "extend" those FK column indices by adding some additional "include" columns to make them even more useful.