texts:
I've been doing a lot of database backups and restores of late, and I've been wondering how if the backup takes 10-15 minutes to capture, how it can still show a snapshot of the database at a split second in time?

How does Sql Server accomplish this?


texts: SQL Managment doesn't use all memory, queries fail due insufficiënt memory

texts:
In Microsoft SQL Server Management Studio 12.0.4100.1 I have a table with about 12.600.000 records. If I try to show the TOP 1000 of these records with an ORDER BY [bigint column], the query runs for a few minutes, then fails with a message of "There is insufficient memory available in the buffer pool."

However, if I look in the task manager, I see that the SQL Server Windows NT - 64 Bit process only uses a small part of the 32 GB that the computer has. If I run this query after a reset of the local server, I first see the proces using a fair amount of CPU and Disk capacity, while the memory usage steadily climbs. But after a few seconds the memory usage stops growing. Exactly at what level it stops changes every time I try it. Sometimes it's at 1.5 GB, sometimes around 3.5 GB. In either case, the memory usage no long changes, the Disk and CPU usage drop to 0, and the SQL query will keep executing for 2-4 more minutes before failing.

I checked the Max Memory of the local server in SQL Managment Studio, and it was set to the highest value. I actually tried lowering it to 16 GB, but the query still doesn't use nearly what I think it should be allowed to.

Am I missing something? Is this a limitation of SQL Server Express, which I am using at the moment?


texts: Where is the rest of the MySQL "HANDLER" documentation?

texts:
I've been a long time user of Handlersocket for MySQL / Percona, but other than MariaDB keeping it going, it's been dead for a long time now.  The final nail in the coffin seemed to be MySQL choosing to go with Memcached.

So that in mind, I was looking at all of my alternatives, and in all the noise out there relating to Handlersocket, I had missed the existence of MySQL's HANDLER capability which, other than the separate socket access, provides very similar features to HS.

The problem is, handler is such a ubiquitous term that I'm getting far too much static looking for documentation or examples.  The only page I've been able to locate was the original man page at https://dev.mysql.com/doc/refman/8.0/en/handler.html.  I've run across answers that skirt it like https://dba.stackexchange.com/a/4589/23271, but there doesn't seem to be anything more on this feature.

I have questions like:

There is a HANDLER .... READ .... request type, but is there a HANDLER
.... WRITE .... capability?
Is there a way to restrict the response to a specific selection of
the table fields? There was in handlersocket, but I don't see any
reference to it on the man page.
What other capabilities or features does HANDLER have?

If you can answer any of the above, I'd be grateful, or if you have links to any further resources, I'm happy to read up on my own. Thanks!


texts:
I upgraded my OS from redhat 2.6.3 to 2.6.5 after that
when I restarted my MySQL server but it not started,

error message is "150320 11:53:17 [ERROR] Error message file '/usr/share/mysql/english/errmsg.sys' had only 641 error messages,
but it should contain at least 728 error messages." and when I fired the command service mysql start it giving error "Starting MySQL......The server quit without updating PID file (/var/mysql-data/hostname.pid).                                            [FAILED]"

Please help..


texts: SQL Server: How to add a full join to two tables but have matches line up?

texts:
I am working with a system that has two databases. One for the ERP system and one for the website. Both DB's have tables for alternate codes. I am trying to write a query that will show me differences and matches between the two db's alternate code tables.

To setup my environment here is a small sample:

CREATE TABLE [dbo].[alt_code_ERP](
    [UID] [int] NOT NULL,
    [item_id] [nchar](10) NOT NULL,
    [alternate_code_ERP] [nvarchar](50) NOT NULL,
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[web_alt_code](
    [UID] [int] NOT NULL,
    [item_id] [nchar](10) NOT NULL,
    [web_alternate_code] [nchar](50) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (1, 'part1' , 'part1_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (2, 'part2' , 'part2a_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (3, 'part2' , 'part2b_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (4, 'part2' , 'part2c_alt')
INSERT INTO [dbo].[alt_code_ERP] ([UID],[item_id],[alternate_code_ERP]) VALUES (5, 'part10' , 'part10_alt')
GO

INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (1, 'part1' , 'part1_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (2, 'part2' , 'part2a_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (3, 'part2' , 'part2d_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (4, 'part8' , 'part8_alt')
INSERT INTO [dbo].[web_alt_code] ([UID],[item_id],[web_alternate_code]) VALUES (5, 'part10', 'part10b_alt')
GO

I want a result that will have the following:

List each alternate code from each table once.
If there are any matching alternate codes from the two tables, I want to align them side by side.
Any without a match in the other table to be paired with a NULL

Something that looks like the following:

 item_id      alternate_code_erp   web_alternate_code

part1       part1_alt   part1_alt
part2       part2a_alt  part2a_alt
part2       part2b_alt  NULL
part2       part2c_alt  NULL
part2       NULL        part2d_alt
part8       NULL        part8_alt
part10      part10_alt  NULL
part10      NULL        part10b_alt


texts: Enable SQL Server FILESTREAM - Windows Based Docker Container (not linux)

texts:
I have been struggling for a few days to spin up a windows based docker container running SQL Server 2017 on Windows Server Core 2016. I am using the following image as a starting point:
https://hub.docker.com/r/microsoft/mssql-server-windows-developer
I managed to successfully install the container and get it running perfectly. When I attempt to restore my database, I get an error stating that the FILESTREAM feature is not enabled. I understand that FILESTREAM is not supported on Linux containers but this is a windows based container.
I have been spending the last few days trying to enable filestream. As this is a server core OS, there is now UI. So using SQL server configuration manager is not an option. At least not locally. I tried connecting to the machine remotely using MMC to manage it but I get an error saying that the computer cannot be remotely managed due to COM+ Network Access (DCOM-In) and all rules in the Remote Event Log Management group not being enabled on the Windows Firewall. Trying to add these rules in the docker container just results in a bunch of errors. I read somewhere that the container actually using the firewall on the host machine. I tried adding the required firewall rules on both the container and the host machine with the following command:
Invoke-Command -ComputerName localhost {Set-NetFirewallRule -DisplayGroup 'Remote Event Log Management' -Enabled True -PassThru | select DisplayName, Enabled}

This only results in Access Denied errors on both machines. I tried enabling FILESTREAM on the container using the following script:
# Enable FILESTREAM

$username = "LocalOSUserAccount"
$password = ConvertTo-SecureString "LocalOSUserAccountPassword" -AsPlainText -Force
$psCred = New-Object System.Management.Automation.PSCredential -ArgumentList ($username, $password)

$instance = Get-SqlInstance -MachineName "localhost" -Credential $psCred
$wmi = Get-WmiObject -Namespace "ROOT\Microsoft\SqlServer\ComputerManagement11" -Class FilestreamSettings | where {$_.InstanceName -eq $instance}
$wmi.EnableFilestream(3, $instance)
Get-Service -Name $instance | Restart-Service

Set-ExecutionPolicy RemoteSigned
Import-Module "sqlps" -DisableNameChecking
Invoke-Sqlcmd "EXEC sp_configure filestream_access_level, 2"
Invoke-Sqlcmd "RECONFIGURE"

but this also ended in tears with a instance cannot be found error.
Out of pure frustration, I finally ended up referencing the docker file for this image and creating my own. The original file can be found here:
https://github.com/microsoft/mssql-docker/tree/master/windows/mssql-server-windows-developer
The only thing that I changed was to add the /FILESTREAMLEVEL and the /FILESTREAMSHARENAME to the command line install of SQL Server as per https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver15 in the dockerfile.
With this strategy, I managed to create the image successfully with /FILESTREAMLEVEL=1 and omitting the /FILESTREAMSHARENAME parameter (not required for /FILESTREAMLEVEL=1). When I run the image though, the container is created, but the MSSQLSERVER service fails to start and the container shuts down. I need to find a way to trace this error somehow which I will investigate. It has to be FILESTREAM causing the service start to fail.
When I use /FILESTREAMLEVEL=2 and a /FILESTREAMSHARENAME=MSSQLSERVER, the image fails to build saying that the share MSSQLSERVER could not be validated. I investigated this and it seems you have to ensure that the "Server" service is started, I don't see that service in my container. Another way to enable it is to enable "Printer and File Sharing". Attempting to do this also results in a bunch of Access Denied errors.
Am I missing something very basic? I am fairly new to docker but this is a huge amount of struggle to enable FILESTREAM? Any help would be very much appreciated.


texts: How to skip prefix column check in multi column index in MySQL?

texts:
Below is my demo table structure:

CREATE TABLE `table1` (
  `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0-for rent, 1-to rent',
  `postLocation` smallint(6) NOT NULL,
  `postArea` smallint(6) DEFAULT NULL,
  `postDetails` text,
  KEY `table1_postType_postLocation_postArea_index` (`postType`,`postLocation`,`postArea`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here i have index on column (postType, postLocation, postArea). My postType column value are fixed, it will be 0 or 1.

How can i use index (postType, postLocation, postArea) or skip postType column value and use other two index?

If i include 0, 1 in query is it gonna skip the postType column from index check? or will it check index first for postType column in 1 or 2 than will continue to next column in the index? Below is an example:

select *
from table1 t
where t.postType in (0, 1)
  and t.postLocation = 1
  and t.postArea = 15;

  Please suggest if i need to update my datatype for postType column.


texts: Mongodb deployment best practice for dual nodes?

texts:
I have two nodes and I'd like to deploy mongodb on them. It seems there're two methods:

Deploy as master-slave. But it's marked in the official document as "legacy".
Deploy as a replica set. But a 2-node replica may not function well.

Any suggestions?


texts: Why does the MS SQL Server change tracking table store all DML operations that occurred within the transactiton?

texts:
I have setup up SQL Server Change Tracking on a table t1 on MS SQL2008R2. Within a transaction, I insert a row into it and update it 'n' times. When I examine the rowcounts in the change tracking tables, it shows 'n' number of rows whereas the syscommitab shows just 1 row. Since the change tracking mechanism is a synchronous mechanism and per documentation, is expected to track only "net change" which occurred in a transaction, why is the change tracking table showing 'n' rows instead of just 1 ? Here is the SQL that was used to examine the rowcount.

SELECT  sct1.name as CT_schema,
    sot1.name as CT_table,
    ps1.row_count as CT_rows,
    ps1.reserved_page_count*8./1024. as CT_reserved_MB,
    sct2.name as tracked_schema,
    sot2.name as tracked_name,
    ps2.row_count as tracked_rows,
    ps2.reserved_page_count*8./1024. as tracked_base_table_MB,
    change_tracking_min_valid_version(sot2.object_id) as min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 on it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 on
sot1.schema_id=sct1.schema_id
JOIN sys.dm_db_partition_stats ps1 on
it.object_id = ps1. object_id
and ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 on
sot2.schema_id=sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2 on
sot2.object_id = ps2. object_id
and ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210)
and (sot2.name='t1' or sot1.name='syscommittab')

Above SQL courtesy of https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/


texts: How are entries in index leaf node ordered?

texts:
I am reading the book 'SQL performance explained' and when talking about indeces, it says that databases use doubly linked lists to connect index leaf nodes. Each node is stored in a database block and consists of different index entries.
It then says that the index order is maintained at two levels: one within each leaf node, and also within the different leaf nodes themselves (via the linked list), as you can see in the left side of the picture below.

My question is: I understand the advantages of using a doubly linked list for the blocks so that when inserting new blocks it's easier to maintain the order (it's a matter of moving around some pointers). However, within the block itself, if the order is maintained, how is that performant?   Assuming than in a block there are a lot of entries, if one were to insert a new entry in the block, wouldn't that be really unperformant (because there is no data structure such as a doubly linked list).


texts:
I created a maintenance plan a week ago and it's been executing fine every night. Now the Database Shrink task has stopped working and throws this error:

  Property Size is not available for Database '[foo]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

Yesterday I installed a couple of SQL Server hotfixes offered by Windows Update (4411 and 4457, if that matters). It's the only change I've possibly made.

My task looks like this:

Connection: Local server connection (sa)
Database(s): All databases All user databases
Shrink database when it grows beyond: 50 MB
Amount of free space to remain after shrink (10%)
Return freed space to operating system

Some of my findings:

If I generate the T-SQL code and execute it manually it runs flawlessly.
If I create a new maintenance plan with an identical Database Shrink task it runs flawlessly.

What can the problem be?


texts: Can't stop replication sp_dropdistpublisher responds "there are subscribers defined"

texts:
I am running into errors:

exec sp_dropdistpublisher @publisher='servername'

response:

  Msg 21047, Level 16, State 1, Procedure sp_dropdistpublisher, Line 145
  Cannot drop the local distribution Publisher because there are
  Subscribers defined.

There is no subscriber defined, and I don't know where to find the information that makes SQL Server think that there is a subscriber defined.

I'm open to any suggestions...


texts: Calculating tsrange based on parent/child created dates

texts:
Given the table:

CREATE TABLE node (
    id serial PRIMARY KEY,
    created timestamp without time zone NOT null,
    path ltree NOT null UNIQUE
);

And the records:

INSERT INTO node (id, path, created)
VALUES
  (1, '1', '2010-01-01 12:00'),
  (2, '1.2', '2010-01-01 13:00'),
  (3, '1.2.3', '2010-01-01 14:00');

How do I get the valid range for each node based on the node's created date and its' parent's created date?

For instance, the ranges for the given path 1.2.3 would be:

id | path  | valid
------------------------------------------------------------------
 1 |     1 | tsrange('2010-01-01 12:00', '2010-01-01 13:00', '[)')
 2 |   1.2 | tsrange('2010-01-01 13:00', '2010-01-01 14:00', '[)')
 3 | 1.2.3 | tsrange('2010-01-01 14:00', 'infinity', '[)')


texts: Memory utilization for multiple instance (SQL server 2008R2 standard edition)

texts: If SQL Server 2008 R2 standard edition is limited to 64 GB of memory, then is it required to restrict the max memory for multiple instances (3) which are running SQL server 2008 R2 standard edition with 256 GB of RAM installed on windows server 2012?


texts: why some one will use approximate data types for sales

texts:
I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have different prices)

The most exact data types are decimal and numeric, don't they?, instead they are using real data type an approximated data type which can't represent some values decimal values.

I know this will store this range of values

3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38 and also know it's for performance lookup since they are searching by name and not by value which is a very smart design msdn states it's 20 times faster than other types like int and you have to have speacial treatment in lookups comparisons and less space in disk to storage values.

my concern is what if i want some math on these values will my Accountant complaint i've lost any money or cents or what escenarios will i encounter to get data truncated or wrong values? some scales won't be represented as exact as decimal or numeric but i want to know wich ones are the special ones.

USE [KartrisSQL716]
GO

/****** Object:  Table [dbo].[tblKartrisVersions]    Script Date: 12/17/2015 18:23:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblKartrisVersions](
    [V_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [V_CodeNumber] [nvarchar](25) NULL,
    [V_ProductID] [int] NULL,
    [V_Price] [real] NULL,
    [V_Tax] [tinyint] NULL,
    [V_Weight] [real] NOT NULL,
    [V_DeliveryTime] [tinyint] NOT NULL,
    [V_Quantity] [real] NOT NULL,
    [V_QuantityWarnLevel] [real] NOT NULL,
    [V_Live] [bit] NOT NULL,
    [V_DownLoadInfo] [nvarchar](255) NULL,
    [V_DownloadType] [nvarchar](50) NULL,
    [V_OrderByValue] [smallint] NOT NULL,
    [V_RRP] [real] NOT NULL,
    [V_Type] [char](1) NULL,
    [V_CustomerGroupID] [smallint] NULL,
    [V_CustomizationType] [char](1) NOT NULL,
    [V_CustomizationDesc] [nvarchar](255) NULL,
    [V_CustomizationCost] [real] NOT NULL,
    [V_Tax2] [tinyint] NULL,
    [V_TaxExtra] [nvarchar](255) NULL,
 CONSTRAINT [Versions_PK] PRIMARY KEY NONCLUSTERED
(
    [V_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'n:NONE, i:Image, t:Text' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblKartrisVersions', @level2type=N'COLUMN',@level2name=N'V_CustomizationType'
GO

ALTER TABLE [dbo].[tblKartrisVersions]  WITH CHECK ADD  CONSTRAINT [FK_tblKartrisVersions_tblKartrisProducts] FOREIGN KEY([V_ProductID])
REFERENCES [dbo].[tblKartrisProducts] ([P_ID])
GO

and constraints go on and on...

thank you in advance


texts: Can't load local file into mysql database

texts:
So I will preface this by saying that I'm not all that skilled in MySQL (or regular SQL for that matter), but I'm trying to load data from a .txt file into my table that I keep for ticket sales. When I do that using:

LOAD DATA LOCAL INFILE '/path/presales1.txt' INTO TABLE presales

I get the '> prompter. And I have no idea what to do. I tried looking up solutions and the one I thought would work was:
--local-infile[=1]; because that is straight out the MySQL handbook. It didn't work (nor did any variation on it...such as local-infile=1;, local-infile=1 etc....)

Did I miss a simple step? My version of MySQL is 5.7


texts: How to toggle a boolean field by sql in postgresql?

texts:
There is a field show which is a boolean type in postgesql.

I want to write a sql to update that table, to toggle the value of show. If it's true, it becomes false, if it's false, it becomes true.

Is it possible?


texts:
I ran the below query and it shows list of currently running jobs. However, in Activity Monitor none of these are running. How do I verify that these are/are not running?

And how do I kill them?

    select sj.name
    ,sja.*
from msdb.dbo.sysjobactivity as sja
inner join msdb.dbo.sysjobs as sj on sja.job_id = sj.job_id
where sja.start_execution_date is not null
and sja.stop_execution_date is null


texts: Column not like any value in column in other table - function

texts:
The two tables in question are:

completed_types

id | type
---|--------
1  | XXX
2  | YYY

all_types:

id | type  | sub_type
---|-------|---------
1  | XXX X | aaa
2  | XXX-Y | bbb
3  | YYY-X | ccc
4  | YYY.Y | ddd
5  | QQQ-G | fff
6  | RRR+Q | ggg

What I want is to use the type column of completed_types to filter all_types.

Something like SELECT type FROM all_types WHERE type NOT LIKE ANY(SELECT type FROM completed_types.

The expected output based on the above tables would be:

id | type  | sub_type
---|-------|---------
5  | QQQ-G | fff
6  | RRR+Q | ggg

However it does not seem to remove any results from the output. I tried appending a wildcard '%' to the end of the subquery:

SELECT type FROM all_types WHERE type NOT LIKE ANY(SELECT type||'%' FROM completed_types)

But this has also not reduced the number of results.

My plan is to use this in a function, as all_types.type could grow quickly while completed_types.type will be slower, and will likely never be completely the same or even close, so if that would change your answer please let me know, but any help with this is appreciated.


texts: how to approach user acceptance testing for merged constituent records in a CRM system?

texts:
I've been Googling to read up and understand what UATs are and how to conduct them, but I'm not quite understanding how to apply it in this scenario.

My acceptance criteria would be that all historic data from duplicate profiles that are transactions, interactions, and custom fields like booleans and strings get merged into the master record.

I'm confused about the "testing" part of UAT...what is there to test? don't we just look at the profiles and confirm that the data is there? Does that count as testing?

I'm struggling to build a test plan because I don't really see what there is to test.

Any help/suggestions/readings/articles is immensely appreciated.

Background:
We have about 3 million records (record being the constituent profile in the CRM), and we have identified at least a quarter of the records are duplicates (common cases being a person has 5+ profiles).

We've gone through the activities of building our criteria and identifying which of these duplicates would be marked as the master record, the duplicate records would then be merged into the master record.

Our next step is to validate the master records to ensure the historic profile data from duplicate records remains intact (transactions, interactions, and any custom fields like booleans and strings that were added on as part of their registrations, merged into the master record).

Task:
I am to build a UAT plan for multiple teams to validate the merge (I did express that my background is not related to what is being asked...but I'll try, because no one else in the company has any idea what to do at this point, budget is really really tight so cannot hire contractors)


texts: Does Oracle PL/SQL have a standard ASSERT routine?

texts:
I'd like to use an ASSERT routine functionally similar to the one found in other languages, i.e. a construct (be it a procedure, syntax...)

ASSERT( <condition>, <msg>)

such that when the <condition> passed in the first argument is false an exception is raised with the specified <msg> descriptive message.

I know this is trivial to do by hand but I'm asking if there's a standard one provided with the DBMS.

Having to write my own one or import one from 3rdy-party packages would be impractical, since I'd need it to be completely portable and transparent to every project I'm working on.


texts: Which are the options I have to create a structured log of all INSERT, UPDATE and DELETE from all tables of my PostgreSQL database?

texts:
With MySql, I'm using replication api to record all changes from my database.
pymysqlreplication and https://github.com/nevill/zongji are what I'm using.
I wonder which are the options I have to do the same with PostgreSQL.
I'm looking for a simple solution, that doesn't depend on table schema or create trigger for every single table.


texts: Connect to Oracle database as apex_version user

texts:
I'm talking about schemas like APEX_040200, APEX_050100, APEX_180300, APEX_190100 etc.

I'm trying to install an APEX Plug-in that is being installed from a script, where developper commented Assumes you are running the script connected to SQL*Plus as the Oracle user APEX_040200 or as the owner (parsing schema) of the application. I have APEX 19 installed, so it's APEX_190100 then.

Empty password didn't do the trick, default password I don't know. Is there a way to change it's password harmlessly? Or are there any ways to install this plugin to the whole APEX instaince, not only to a certain workspace?


texts: SQL Server 2008: How do I change the default schema of the dbo?

texts:
When I attempt to change the default schema of the dbo in SQL Server Management Studio, the ALTER fails and states that the dbo account's schema cannot be changed. Are there any tricks that can make is possible to change the the dbo default schema of "dbo" to "xxx"?

Thank you!


texts: How to query with the condition with subquery

texts:
I have two tables table A and table B.

table B has a field called condition (where some sql conditions are stored).

Now i wanted to query like this

select *
from table_A  as a
where a.id = 1
  and (select b.condition from tableB as b)

I would like get the output for which of the condition satisfies.


texts: Backup a hot standby database (streaming replication) whilst it is in recovery

texts:
Synchronous Streaming Replicating is set up between a master and a hot standby database.

It seems like a good idea to perform daily backups on the standby database to avoid any performance issues on the master database.

The backup of the standby db was successfully restored using pg_basebackup. Archived WAL files were transferred locally and the restore_command was added in recovery.conf to point to them - the following errors were recorded in the log files whilst starting up the database:

cp: cannot stat ‘/archivedir/wal_arch/00000002.history’: No such file or directory
2016-05-12 17:33:52 BST LOG:  starting archive recovery
cp: cannot stat ‘/archivedir/wal_arch/0000000100000000000000E7’: No such file or directory
2016-05-12 17:33:52 BST LOG:  redo starts at 0/E7000CA0
2016-05-12 17:33:52 BST LOG:  consistent recovery state reached at 0/E7000D80
2016-05-12 17:33:52 BST LOG:  invalid resource manager ID 100 at 0/E7000D80
2016-05-12 17:33:52 BST LOG:  redo done at 0/E7000D48
cp: cannot stat ‘/archivedir/wal_arch/0000000100000000000000E7’: No such file or directory
cp: cannot stat ‘/archivedir/wal_arch/00000002.history’: No such file or directory
2016-05-12 17:33:52 BST LOG:  selected new timeline ID: 2
cp: cannot stat ‘/archivedir/wal_arch/00000001.history’: No such file or directory
2016-05-12 17:33:52 BST LOG:  archive recovery complete
2016-05-12 17:33:52 BST LOG:  MultiXact member wraparound protections are now enabled
2016-05-12 17:33:52 BST LOG:  database system is ready to accept connections
2016-05-12 17:33:52 BST LOG:  autovacuum launcher started

Is it a good idea to backup the standby database instead of the master?


texts: Could database with compat level 100 support columnstore index?

texts:
I have an instance which runs on SQL Server 2017 Developer Edition instance. I restored database ContosoRetailDW which had compatibility level 100. After restore operation, I run the following query:
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactOnlineSales ON dbo.FactOnlineSales

The columnstore index was successfully created.
My question is related to the compat level. As far as I know, the columnstore index concept itself was came out in SQL Server 2012. Moreover, the clustered columnstore index was released in SQL Server 2014. How come database with compat level 100 support clustered columnstore index? It seems that database still inherits features of the current instance.


texts: What's complexity of select * from table order by id desc limit a, b?


texts: How can I automate copying DB2 table data between remote Servers?

texts:
I have two IBM DB2 servers at separate remote locations.

I need to copy data from several tables on one database on the first server to another database in the second server.

Previously I've used the IBM data studio tool to export the data and Replace Load it into the other table in the second server.

I am in need of a way to automate this.  Probably through command line shell scripts.

How can I accomplish this?


texts: Storing data on separate storage volumes

texts:
I'm just looking for some design options around Postgresql.
I'm looking at using Postgresql as the backend for a SCADA system that I'd like to develop, this essentially means that it would have a combination of both OLTP loads (for instantaneous state / config of system) as well as quite a lot of OLAP involving time series data.
One particular item that I'd be worried about is performance, and in particular around disk I/O performance.
I see that Postgresql allows for Tablespaces to be used to put tables on different storage volumes, is there any way to do this with particular columns of data also?
My current schema approach would entail the following (exposed) tables:

Objects
Historical Data
Historical Events

For the Historical Data and Historical Events I'd ideally like to able to partition these across a number of different storage volumes, but not in time order, and instead perhaps by a hashing partition or similar.  I think this wouldn't be a problem, although it appears that it would require the use of Inherited tables (i.e. the Historical Data / Events being hidden Parent tables, real tables inheriting from these along some hash of the ObjectId FK or similar).
It's the Objects table that I'm most unsure about.  Each row would consist of something like:

ObjectId (Unique, Primary Key)
Security (ACL etc)
Data (JSON(B) format)
Config (JSON(B) format)

What I'd really like to be able to do is split out the Data column so that it's stored elsewhere.  It will be write heavy, and I would prefer not to have that same disk I/O impacting on the Object Config.
Is there any nice way to do this in Postgresql? (or should I be normalising the schema to have Data into a separate table? just with FK against ObjectId..)


texts: How to create Relationship between multiple databases?

texts:
I am working with multiple databases which have the same structure and are on the same server. All of them are MySQL databases and I'm using MySQL Workbench (community version!) to connect to them.

I need to retrieve data from all (or some) of them and compare the results. sometimes I need to store the retrieved data from those databases in another database.

How can I manage that?


texts: Checking whether my pgAdmin III is a 32 or 64 bits version

texts:
I need to check if my pgAdmin III is a 32 or 64 bits version. Where can I find this information? The About... menu in pgAdmin GUI does not mention it (only the version number like 1.20).

I've looked for this on the net, without success...

EDIT:

I get a win32 error when pgAdmin crashes (connection lost). See image below (sorry my Windows is a french version):


texts: I learned how to create tablespaces, rename it, assign an owner to it, and drop it. But I don't understand why I would even need it. A tablespace is simply a storage area on disk assigned to a postgresql object. It sounds no different from the data directory initialized with initdb. You can assign a data directory anywhere on your disk. So what makes a tablespace different and why would you need a tablespace?


texts:
I have my sort ordering in Sybase as:
45  190 altdict -  Alternate (lower-case first) dictionary ordering
Am I right that alternate means "aAbBcC..zZ" ordering?

What should I do to change ordering with "ORDER BY" as 'ABC..YZabc..yz' ? What sort ordering to choose?

Where may it be changed? In a session? In SQL statement?
as far as I know I need to change the whole server sort ordering and rebuilt indexes. But there are a bunch of databases with different owners and different users and it seems a bit extreme


texts: Is there a way to backup a mysql database without having to run mysqldump?

texts:
Is there a way to run a backup of a mysql database using sql? We have scripts which run updates against our databases. They only have mysql user credentials, not system login credentials.

Is there anyway to run backups of mysql via a mysql connection instead?


texts: How to convert following mysql query to oracle?

texts:
I have a mysql query like following, Column time contains date in long format.

SELECT FROM_UNIXTIME(CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i') AS Time,
SUM(CreatedInstanceCount) AS CreatedInstanceCount , SUM(InitializedInstanceCount) AS InitializedInstanceCount ,
SUM(ActiveInstanceCount) AS ActiveInstanceCount,
SUM(TerminatedInstanceCount) AS TerminatedInstanceCount
FROM MEMBER_COUNT WHERE ApplicationId='test'
AND Time > ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000-'604800000'
AND Time <= ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000)
GROUP BY FROM_UNIXTIME( CEILING( Time/('20160'*1000)) *'20160', '%Y:%m:%d %h:%i'), ApplicationId

So far my oracle query is like this but the results are not same.

SELECT to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI')  "Time", SUM(CreatedInstanceCount)
AS "CreatedInstanceCount", SUM(InitializedInstanceCount) AS "InitializedInstanceCount", SUM(ActiveInstanceCount) AS "ActiveInstanceCount",
SUM(TerminatedInstanceCount) AS "TerminatedInstanceCount" ,ApplicationId
FROM MEMBER_COUNT
WHERE ApplicationId='test' AND to_date('1970-01-01 00','yyyy-mm-dd HH24:MI') +(time)/1000/60/60/24 between sysdate-7 and SYSDATE
GROUP BY to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI'), ApplicationId
order by to_char(to_date('1970-01-01 00','yyyy-mm-dd hh24:mi') +(time)/1000/60/60/24 , 'yyyy-mm-dd HH24:MI') ;

This is my create table statement:

CREATE TABLE MEMBER_COUNT(Time number(20), ApplicationId VARCHAR2(150), ClusterAlias VARCHAR2(150), CreatedInstanceCount int, InitializedInstanceCount int, ActiveInstanceCount int, TerminatedInstanceCount int);

The problem is no of rows returned by oracle query are higher than the mysql query. I believe this has to do something with CEILING function.

Please help.


texts: what are the possible reasons for 'Arithmetic overflow error converting expression to data type int' in SQL server

texts:
When this command is run, there is SQL server error

  'Arithmetic overflow error converting expression to data type int'.

SELECT sum(size) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg WHERE sfg.groupname = 'PRIMARY' AND sf.groupid = sfg.groupid

Because the file size is less than 16TB, bigint is not required. Still I tried to execute cast(size as bigint), the error persists.

SELECT sum(cast(size as bigint)) FROM [dbname]..sysfiles sf, [dbname]..sysfilegroups sfg WHERE sfg.groupname = 'PRIMARY' AND sf.groupid = sfg.groupid

So the question is, what are the reasons for arithmetic overflow error other than exceeding the file size limit of 16TB.


texts: How to initialize a PostGIS database in PostgreSQL single-user mode?

texts:
I'm using PostgreSQL 9.2.15 with PostGIS 2.0.7 through the standard packages available on CentOS 7 with EPEL.

I have a server script to automatically setup a PostGIS database, by first executing initdb, then using postgres --single to create a new database with PostGIS extension. This works roughly like this:

initdb -D ...

postgres --single -D ... postgres <<'EOF'
    CREATE DATABASE mydb;
EOF

postgres --single -D ... mydb <<'EOF'
    CREATE EXTENSION postgis;
EOF

However, the last command produces the following strange error message:

ERROR:  type reclassarg[] does not exist
STATEMENT:  CREATE EXTENSION postgis;

If I instead run the database server and execute CREATE EXTENSION postgis via sql, everything works fine:

postgres -D ... &

# Wait until service it up and running

psql mydb <<'EOF'
    CREATE EXTENSION postgis;
EOF

But that is not suitable for a database initialization script.

So how can I initialize a PostGIS database in PostgreSQL single-user mode?

Update: There is  a thread in the pgsql-bugs mailinglist on that topic, but that doesn't answer my question. It just explains that it is not planned to improve the single-user mode of PostgreSQL. So there will always be missing PgSQL language features to execute the current PostGIS extension creation script. However, most other PostgreSQL extensions can be installed in single-user mode just fine. So maybe there is a way to reformulate the PostGIS creation script so it achieves the same thing without requiring those language features, i.e. to make it "more portable".

(This question originally appeared on StackExchange / GIS: https://gis.stackexchange.com/q/214449/82644)


texts: What are the sharding heuristics for MySQL - number of records per table & number of tables per instance?

texts: Designing an object store for ~10B objects and using mySQL for storing metadata. We know we need to shard and are looking for general heuristics to scale. Would appreciate pointers and hear about the back-of-the-envelope calculations you all used


texts: MySQL write performance after truncate & optimize

texts:
Once a year we truncate a table of  several GBs and millions of rows and then run optimize in order to shrink the size of the corresponding ibd file on the file system.
However, as this is write intensive table, we are wondering whether running optimize is actually a good idea. Maybe it’s smarter to leave the file size as is and let MySQL reuse already allocated disk space.
Is it more efficient in terms of write performance to optimize the table in order to shrink the file size and then let it progressively grow again, or just reuse space previously allocated?
We use InnoDB as storage engine and MySQL 5.7 as DBMS.


texts: is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?

texts:
I have a table that has an clustered unique index and a non clustered primary key with the same structure as the index.

IF OBJECT_ID('[dbo].[tblBAccountHolder]') IS NOT NULL
DROP TABLE [dbo].[tblBAccountHolder]
GO
CREATE TABLE [dbo].[tblBAccountHolder] (
[lngParticipantID]  INT                              NOT NULL,
[sdtmCreated]       SMALLDATETIME                    NOT NULL,
[strUsername]       VARCHAR(20)                          NULL,
[strPassword]       VARCHAR(20)                          NULL,
[tsRowVersion]      TIMESTAMP                        NOT NULL,
CONSTRAINT   [PK_tblAccountHolder]
PRIMARY KEY NONCLUSTERED ([lngParticipantID] asc),

CONSTRAINT   [IX_tblBAccountHolder__lngParticipantID]
UNIQUE CLUSTERED    ([lngParticipantID] asc)
WITH FILLFACTOR = 100)

Only one column as you can see on the definition:

 CREATE  UNIQUE CLUSTERED INDEX IX_tblBAccountHolder__lngParticipantID
 ON [dbo].[tblBAccountHolder] (  [lngParticipantID] ASC  )

I would like to drop the unique index, and alter the primary key so that it is CLUSTERED.
I will keep the same primary key, just change it from non clustered to clustered.

This table is part of transaction replication
I would get this done on the subscriber database only.Not in the publisher.

It is a table with over 9,293,193 rows.

  Will I mess up the replication?

the problem is that I have to drop the primary key constraint and re-create it as clustered.

this is what I would like to get done in the subscriber database:

drop INDEX IX_tblBAccountHolder__lngParticipantID
        ON [dbo].[tblBAccountHolder]
GO

ALTER TABLE [dbo].[tblBAccountHolder]
       drop CONSTRAINT [PK_tblAccountHolder]
GO

 ALTER TABLE [dbo].[tblBAccountHolder]
   ADD  CONSTRAINT [PK_tblAccountHolder]
 PRIMARY KEY CLUSTERED (  [lngParticipantID] ASC  )
  WITH (  PAD_INDEX = OFF,
          FILLFACTOR = 95,
          SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF ,
          STATISTICS_NORECOMPUTE = OFF , ONLINE = ON ,
          ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ]
GO


texts: Get maximum number with combination of columns

texts:
I have table with columns like AutoID, Number, Name, City, State, Country.

What I wanted is the maximum number entered in the “Number” column with the combination of Name, City, State and Country.

Example:

Name  City State Country
----- ---- ----- -------
Smith NY   NY    USA
John  NY   NY    USA
John  NJ   NY    USA

Now smith should get “Number” 1, John 2, and again John(in NJ) 1 as he is the first from NJ.

I can simply put a where clause in query and get the max number + 1. But the problem is that when I have huge amount of data and the number of users increases, my query will be really slow. I am also inserting data in the same table so it will keep on piling.

I hope I have made my self clear.


texts: Is it possible to `DELETE` in a subquery?

texts:
Is it possible to run a DELETE as a subquery of a SELECT? Eg:

SELECT * FROM posts where title = (DELETE FROM posts returning 'whoops');

When I try this, I get syntax error at or near "FROM", pointing to the FROM of the DELETE, so it doesn't seem possible, but maybe I'm just doing it wrong somehow.

Context: I'm trying to understand the possible damage of some SQL injection attacks. In this case, the overall query will be run in a prepared statement with no variable bindings. This means an attacker can't run multiple top-level statements, but I'm trying to assess the possible damage from subqueries.

Edit: I should have said, the querying code is doing something like:

 query_parameter = blindly_read_from_user_input()

 # this is just interpolated into a string
 db_query = "SELECT * FROM posts WHERE title = '#{query_parameter}'"

 connection.prepare("query_name", db_query)
 connection.exec_prepared("query_name")

So:

It's using a prepared statement, which means an attacker can't cause multiple top-level queries to run
It's not using bound parameters

I'm trying to see whether I can delete records based on SQL injection in this existing query structure.


texts: Is there any benefit of a primary key that comprises all columns of the table?

texts:
I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need to comprise all columns. Queries against the table will almost always be to pull back a single record, i.e. all columns will be filtered in the query.

Since every column will need to be searched, does having a primary key benefit me at all (besides enforcing uniqueness of records)?


texts: How to isolate single values from multiple records

texts:
Hello ladies and gentlemen.

If we have the following records (as example) from a query:

is it possible to present the following sql result with pl/sql oracle query ?

We need to have the single ALPHA CODE record, the analytic BETA CODE record related to the alpha code from the first query, and SUM of the VALUE record = 30 and not 90 (3 x 30).

Thank you very much for your help in advance.

Best regards

Tom


texts: CouchDB backup best practice for a 1Tb database

texts:
One of my users got 2 servers running couchdb under docker. A replication is in place between those 2 databases.
On my side I backup those servers using rsnapshot but I don't think this is the best solution as this database is around 1Tb and my rsnapshot needs to copy that daily.
As I don't know couchdb at all, I have several questions

On couch documentation they talk about replication as a backup solution. But what if there is a massive delete on one side, this will be replicated too isn't it? then impossible to recover things as they vanished on both sides?
Is my daily rsync reliable in the end?
what's the best practice to backup a 1Tb couch db database daily?

Thanks in advance for your help.


texts: Designing a database for Questions, Options and Answers

texts:
A practice in trying to adhere to relational database techniques.
Can anyone give me a little bump as to what I'm doing wrong/how this should be?

In Short All Questions have 5 Possible Answers. That's the whole database act.

My idea/what I've thought to do so far was create three tables:

questions

CREATE TABLE `questions` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 `question` mediumtext NOT NULL,
 PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

options

CREATE TABLE `options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 INDEX choice_index (id),
    FOREIGN KEY (`id`)
        REFERENCES questions(question_id),
 `answer_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`choices_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here's where I'm coming to a dead-end.

answers

CREATE TABLE `answers` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 ??`answer_id` int(11) NOT NULL
 //Foreign Key?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

A foreign key has to be an integer value1 -- but my answer choices are all varchar(255).

What is the proper way to link these three tables up?

1 This is an assumption, since most if not all relational schemas I've seen are "binded" by integer values, so that is where this assumption is coming from.


texts: MySql Vs. Postgresql for a blog application

texts:
I am designing a blog application. Which of these fits better?
In which use cases will mysql be good and for what use cases will postgresql be good?


texts: Create TRIGGER to insert after the insertion of specific value

texts:
I have the following

TABLE #1 id time sender receiver message status aux_id

TABLE #2 time_to_send sender receiver message aux_id

The TABLE #2 has a process that sends the data to an api which returns a Boolean.

So if the process success

INSERT INTO `TABLE #1` (time,sender,receiver,message,status,aux_id)
VALUES (CURRENT_TIMESTAMP(),'sender','receiver','message','OK','1')

If doesn't

INSERT INTO `TABLE #1` (time,sender,receiver,message,status,aux_id)
VALUES (CURRENT_TIMESTAMP(),'sender','receiver','message','ERROR','1')

I want to create a TRIGGER to REINSERT the data to TABLE #2 IF the process insert into the status field ERROR value.

I'm trying this without success

mysql> DELIMITER ||
mysql> USE db
mysql> CREATE TRIGGER resend
-> ON TABLE #1
-> AFTER INSERT
-> AS
-> IF EXISTS (SELECT * FROM TABLE #1 WHERE status LIKE 'ERROR' AND aux_id =! 2)
-> BEGIN
-> INSERTO INTO TABLE #2 (time_to_send,sender,receiver,message,aux_id)
-> VALUES (DATE_ADD(NOW(),INTERVAL 10 MIN),NEW.sender,NEW.receiver,NEW.message,'2')
-> END;||

Any ideas?


texts: SSIS hangs during For Each File enumeration

texts:
While debugging an SSIS package that loops over thousands of files, execution hangs with no errors and no failed tasks. Slightly simplified, the package does this:

For Each File                       (a directory full of pdf files, each ranging from 800 -
                                        2000 pages and 3 - 8MB filesize)
    *   Script Task                 (get file information)
    *   File System Task            (clear out temp folder)
    *   Execute Process Task        (use pdftk to break file into individual pages)
    *   For Each File               (each page in temp folder, 40 - 100KB each)
        *   Script Task             (parse pdf file)
        *   Execute Process Task    (pdftk to concatenate like pages)
        *   Execute SQL Task        (insert one row into staging table using OPENROWSET)
        *   File System Task        (delete inserted file)
        *   Script Task             (set variables for next iteration)
    *   Execute SQL Task            (execute proc to merge staging into prod)
    *   File System Task            (rename completed file)

When I execute the package (debug mode in BIDS), it runs as expected through the first file and well into the second, but eventually hangs between iterations of the inner loop. When it hangs, all tasks are green, and the two loop containers are yellow. The output window does generate any messages, and going through the process tab, each task has a matching number of starts and stops except for the two loops and the package itself.

My first thought was that I'd exceeded an iteration limit for the For Each File enumerator, but I cannot find any documentation for such a limit. What else can I check to determine why SSIS stops running?


texts: database design for car part site. (relational database)

texts:
I am working on a directory website for the car's part, in this site the shop owner can register themself and then the user can search for the part based on the year, model submodel and type of the engine of the car after he/she searched. the user chooses the specific part for example Door ..

in this case, the user should see the door only for the car which he/she already searched for (specific car search) .. now for the tables I have created are:

1- shop owner table like:

 user_Id, Name, Info, location

a car's part table which is 6 tables is like this:

 Door table :
 user_Id (reference from shop owner id),side, front/rear, color, price, car
 model, car submodel, car year.
 Fender table:
 user_Id (reference from shop owner id), side, front/rear, color, price, car
 model, car submodel, car year.

my question is: a
car model, car submodel, car year attribute for all the tables, is there any way to get rid of this redundancy (repeating attribute)?  I must use relational database


texts: Best database for a real time application

texts:
I am a student doing my Final Year Project where I need to build an android application for on the road navigation. The problem is the application has a feature that alerts the user of nearby incoming vehicles (that have the same system) when the user is near to a junction by updating and getting positions of vehicle in a database in real time. Hypothetically speaking, if there are millions of vehicle or user using the same system, what database can process these amounts of transactions very fast, let's say maximum response time is 1 second per application?

I have literally no knowledge regarding the types of database.


texts: SQL Server strategy for high availability with batch drop/create updates

texts:
Have a production SQL Server 2017 db that is populated nightly using sqoop from another source beyond our control. Would like to fully sync certain tables without bringing down the app (right now we upsert but this is insufficient as there are deletes in the original data source). The quantity of data is fairly small (a dozen tables totaling records in the single digit million rows)

Is there a replication strategy that will allow us achieve this? Maybe something along the lines of having a pair of rotating DBs, where at any point in time one of which is pointed to production and the other is getting batch updated.


texts: Why do primary keys have names of their own?

texts:
From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table property.

As consequence to change a primary key from nonclustered to clustered or vice versa, you have first to search for its name, than drop it and finally readd it.

Is there some advantage in using arbitrary names that I do not see or are there DBMS not using arbitrary names for primary keys?

Edit 2011-02-22  (02/22/2011 for those who don't want to sort there date):

Let my show the function, with which you can derive the names of a primary key from its tables name (using early sql-sever aka sybase system tables):

create function dbo.get_pk (@tablename sysname)
returns sysname
as
begin
    return (select k.name
    from sysobjects o
        join sysobjects k   on k.parent_obj = o.id
    where o.name = @tablename
    and o.type = 'U'
    and k.type = 'k')
end
go

As gbn states no people really like the generated name, when you do not supply an explicit name:

create table example_table (
    id int primary key
)

select dbo.get_pk('example_table')

I just got

PK__example___3213E83F527E2E1D

But why must names in sysobjects be unique. It would be perfectly OK to use exactly the same name for the table and its primary key.

Doing it that way, we didn't need to set up naming conventions, which could be accidentally violated.

Now answers to Marian:

I only used the task of changing a
clustered into a nonclustered
primary key as an example where I
need to know the actual name of the
pk to be able to drop it.
Things don't need to have proper
names, it is sufficient if they can
easily uniquely denoted. That is the
basis of abstraction. Object
orientated programming goes this
way. You need not use different
names for similar properties of
different classes.
It is arbitrary, because it is a
property of a table. The name of the
table is all you need to know if you
want to use it.


texts:
Im reading O'Relly "Learning MySQL" and it says that you can create a user in MySQL by doing just this:

mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY 'the_password';
Query OK, 0 rows affected (0.01 sec)

But if I try to do:

-> grant select on testgrounds.personas to ''@'localhost';
--> Error Code: 1133. Can't find any matching row in the user table

But it succeds if I use a password:

->grant select on testgrounds.personas to ''@'localhost' identified by 'pass';
--> 0 row(s) affected

I had to restore the privileges for user root because i was experimenting and i messed them up, so i think before doing that i could actually use the statement in the book (i dont remember 100%)

-> select current_user;
--> 'root@localhost'

-> show grants;
--> 'GRANT ALL PRIVILEGES ON *.* TO \'root\'@\'localhost\' IDENTIFIED BY PASSWORD \'*4ACFE3202A5FF5CF467898FC58AAB1D615029441\' WITH GRANT OPTION'
--> 'GRANT ALL PRIVILEGES ON `testgrounds`.* TO \'root\'@\'localhost\' WITH GRANT OPTION'
--> 'GRANT PROXY ON \'\'@\'\' TO \'root\'@\'localhost\' WITH GRANT OPTION'

Is there something wrong with my MySQL installation or set-up?


texts: Install SSDT for VS 2017 - Missing Integration Services

texts:
I have downloaded and installed SSDT from Microsoft docs. The install was successful and I now have SSDT for VS 2017 installed - version 15.8.9 (as per screenshot)

However, I cannot create an SSIS project. There is no option in SSDT to do so.

When I try to open an old version of an SSIS package I get the below 'Unsupported'...

What else am I missing?


texts: Managing timezones for user action datetimes

texts:
I hope this is the right place to ask the following question, and I make enough sense what is my concern.

I have a table: mood_tracker, where I save about the feeling user has each day.

CREATE TABLE `mood_tracker` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mood_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `mood_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mood_tracker_user_id_mood_date_unique` (`user_id`,`mood_date`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The thing is that user can use tracker app for each single day only once, so I depend on mood_date date field. But for example if I save date as UTC (any single static timezone) it will not work because users will be using different timezones.

My thing is I just got messed up with timezones, I'm building huge application where dates and times are very important things.

I'd really love to hear some suggestions how I can build schemas right way to make timezones flexible (adjust to user's timezone setting).

Thanks a lot.


texts: Output a 2 column table to a three column query

texts:
Hi I have a Table with 2 columns, one column for the employeename and another column for shift,  below is a sample.

EmployeeName          Shift
John                    1
Peter                   1
Mary                    2
Mike                    1
John                    2
Paul                    2
Mary                    1

I want to output it to query with 3 columns and count the number of shifts as show below:

EmployeeName         Shift1            Shift2
John                   1                 1
Peter                  1                 0
Mary                   1                 1
Mike                   1                 0
Paul                   0                 1


texts: Azure SQL Database Elastic Pools - TSQL to get current Elastic Pool

texts:
I would like to be able to fetch the name of the elastic pool that the current database belongs to using TSQL  but the only DMV I can find with a column containing elastic is sys.elastic_pool_resource_stats
& I don't see any reference to databases there. I also checked sys.databases to see if an extra column got sneaked on there but nothing I could recognise.
Ideally this should work like @@servername or db_name()
(when on earth will they get around to creating server_name() or @@dbname so that we can code consistently ?)
Does anyone know a suitable command ?
(btw no tag for "elastic pools")


texts: Best practices for history/temporal tables?

texts:
Suppose I have an object, with certain fields which I want to track history, and certain fields which I do not want to track history. From a normalization perspective, is the following schema alright:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

where MyObjectHistory contains the tracked fields for all except for the latest revision. Or, should all tracked fields be in one table, and all revisions including the latest be in that table, as in:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)


texts: Does DB2 support host based access control?

texts: Coming from MySQL I was wondering whether DB2 has something similar to MySQL's user@host access rights concept.


texts: Oracle silent install on Redhat Linux (The Global database name was left blank.)

texts:
I am installing Oracle 11g in silent mode using the response file that is in the database/response directory. It is three response files in that directory(i.e. dbca.rsp, db_install.rsp and netca.rsp). I am using db_install.rsp and customize its properties to use it in runInstaller command in database directory.

At first I want to know is it the right rsp file to install the Oracle server.

When I use this rsp file it seems it doesn't use the values I set in it fr example it asks me to give the My Oracle Support accounts while I set the DECLINE_SECURITY_UPDATES to TRUE. After I add that parameter in the command line as follow:

  ./runInstaller -silent responseFile=/home/oracle/db_install.rsp -showProgress DECLINE_SECURITY_UPDATES=TRUE

it asks me to specify the "Global database name" as follow:

  Starting Oracle Universal Installer...

  Checking Temp space: must be greater than 120 MB.   Actual 44950 MB
  Passed Checking swap space: must be greater than 150 MB.   Actual 8015
  MB    Passed Preparing to launch Oracle Universal Installer from
  /tmp/OraInstall2014-02-14_06-40-50PM. Please wait
  ...[oracle@somethin database]$ [FATAL] [INS-35071] Global database
  name cannot be left blank.    CAUSE: The Global database name was left
  blank.    ACTION: Specify a value for the Global database name.

I am sure I set that value in the rsp file. But it seems it doesn't recognize it. As I check the installation log in oraInventory/log directory it didn't recognize the SID value I already set.

Even before all the issues above it didn't recognize the ORACLE_HOME and ORACLE_BASE that I have had set in the sb_install.rsp and I set it in environment variables in bash_profile.

I guess it is the wrong rsp file and the runInstaller cannot recognize the parameters in it.  I need to know how to force runInstaller to read the install configuration from the rsp file.


texts: Lookup table for a one-to-many relationship

texts:
I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff

lookup
- outside_data_id
- ticket_id

I just need to know if the association exists (hence the lookup table) and has a one to many relationship. I know nothing else about the outside_data_id except the id itself and the related ticket_id.
Also I don't want to alter the tickets table as this will only affect a small amount of ticket_id's.
Example
outside_data_id = 1234

Can be related to:
ticket_id = 321
ticket_id = 322
ticket_id = 900

Would the below lookup table structure work or is there a better way?
CREATE TABLE lookup
(
   outside_data_id integer,
   ticket_id integer
)


texts: What happens if the mysql database's innodb log files are lost?

texts:
What I did was

/etc/init.d/mysql stop

then removed the files: ib_logfile0, ib_logfile1

then modified my.cnf file, variable: innodb_log_file_size

and then:

/etc/init.d/mysql start

and allowed the files to be recreated

I later discovered that the global variable innodb_fast_shutdown is set to "1"

The question is, how much data was lost ?

Note: I still have the old files ib_logfile0, ib_logfile1, not deleted yet.

And the website relying on the database appears to be working.


texts: Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a helpful action?


texts:
I have the following query:

SELECT *
FROM item_ledger
WHERE to_id='7'
AND (SELECT SUM(qty) AS qty_in
     FROM item_ledger
     WHERE from_item_ledger_id='5') < intransit_qty

the query works as expected. From that query, I also want to return qty_in to the main query. How to do that?


texts: Best relationship database design to manage types for different entities

texts:
Suppose we have to manage phone types for: Clients, Employees and  Suppliers.
Each of these can have multiple phone numbers, so for better management I would like to add phone type.
Each entity can have different phone types like:

Clients: Home, work , mobile
Employees: Home, mobile
Suppliers: Stock, Director, and so on.

Problem: I want to be able to show for clients only the possible client phone types, for suppliers only possible supplier phone types and so on...

Question:
What will be the best relationship database design to manage this kind of data?

Case 1 : To make one table PhoneType and then ClientsPossiblePhoneTypes, SupplierPossiblePhoneTypes , EmployeesPossiblePhoneTypes, or

Case2: to make table ClientPhoneType, EmployeePhonetype, SupplierPhoneType.


texts: SQL Server 2016: How to restrict memory and CPU for specific database

texts:
SQL Server 2016 Standard Ed on Windows 2016

We have an 8 core, 128GB machine (a VM, actually). We max this out for our prod db. (122GB for the instance, 8 cores for the instance)

We are setting up a 2nd db, to be on its own spindle set (slower disks), and do not want it to impact the prod db. This will be on the same VM (as we understand it, we can use the existing SQL license this way).

Must this 2nd db be served by its own SQL instance in order to:

Restrict RAM consumed (to very little, just 4 or 8 GB)
Restrict to 2 CPU cores

Or can we restrict this DB within the main instance?

(Note, Express Edition is not an option, our db will be over 10 GB)


texts:
I am trying to loop through my counter to create a dynamic sql query which should finally look like

I am trying to use this approach to get the final query but does not seems to work

declare @CurrentRow int
set @CurrentRow =0;
declare @RowsToProcess int
declare @FinalHistoricalQuery varchar(5000)
WHILE @CurrentRow<3
BEGIN
   SET @FinalHistoricalQuery =' select 11'+convert(varchar(20),@CurrentRow) + ' union '
   SET @CurrentRow=@CurrentRow+1
END

SET @FinalHistoricalQuery = left(@FinalHistoricalQuery,len(@FinalHistoricalQuery)-6)
exec (@FinalHistoricalQuery)

The final output that i am looking for is 110 111 112 but it comes as 112 Any suggestion would be helpful.


texts: How can I capture wait stats for a previous Actual Plan in SQL Server?

texts:
I have some SQL Server stored procedures that sometimes take too much time to run.
If I run them manually, I'm able to catch the Wait Stats. However, I would like to automatically capture all plans with wait stats reported.
I've dug into the plan cache, but it seems like it only stores the estimated plans, so, there are no executions metrics included.
Is there a way to capture all actual plans containing Wait Stats?


texts: Mirroring not working after a server crash

texts:
I'm using SQL Server 2008R2. Last night, the host machine on which my mirror database was running basically went belly up. Thankfully the principle database was on a different host server and so was fine. However...

The principle is working but says suspended. When i try to resume the mirroring, I get an error in the SQL error log giving an error number of 9004.

A quick Google of this error number come back with this article. (tl;dr: transaction log is damaged)

So, does this mean that the transaction log shipping between the principle and the mirror has somehow got screwed up? How do I fix this?

Is it as simple as doing a full backup on the principle and a full transaction log, then restoring them both on the mirror database with norecovery switch on and then set up the mirroring again?

Or will I need to do something more drastic?


texts:
We have a SQL database on FULL back up with a client  and they have NOT implemented a Log file maintenance and noticed that the log file grows exponentially, filling the 20GB they have allocated to it in a matter of days.

Once they switch into 'maintained log file mode' this problem goes away.

As we are not SQL experts, I am writing to you as to whether exponential growth is normal if you do not maintain the log file.

Thanking you in advance,

Steve


texts: How can a 'Request' store a 'Result' code and its associated 'Error' code and still enforce the relationship between Result and Error?

texts:
I'm an application developer creating the database structure to represent a flat file message format.  I'd like to ask the collective knowledge the best/correct way to represent the following scenario:
Request table (PK: RequestID) contains requests; a request has a Result property which is indeterminate (null) until the request has completed.
Result table (PK ResultID) is a lookup table containing (currently) two possible results:

Success (Result ID = 0)
Failure (Result ID = 1)

ErrorCode table (PK ErrorCodeID) is a lookup table containing error details and their parent ResultID:

No Error (ErrorCodeID = 0, ResultID = 0)
Generic Error (ErrorCodeID = 1, ResultID = 1)
Queue Full (ErrorCodeID = 2, ResultID = 1)
Unsupported Interface (ErrorCodeID = 3, ResultID = 1)
etc...

I've created a one to many relationship between Result (one) and ErrorCode (many).  A 'Success' Result can only have a 'No Error' Error Code, while a 'Failure' Result can have a single error code of 'Generic Error', 'Queue Full', 'Unsupported Interface', etc.
When the Request has completed, I need to store the result and its associated error code.
I've thought of combining the two tables but that strikes me as repeating columns.
I've also thought of having the Request table store the ResultID and the ErrorCodeID but this doesn't enforce the Result to ErrorCode relationship.
I'm a big believer in database that 'defends itself' from bad data so I want the relationship to reject a Result/ErrorCode combination that is invalid; a.k.a. a Result of 'Success' and an ErrorCode of 'Generic Error' or a Result of 'Failure' and an ErrorCode of 'No Error'.
I'm also a big believer in solid initial design so when changes come down the pike at a later date (as they always do) the structure will not need rework.
Thank you in advance for your time.
Regards,
John E.


texts: Error deleting SQL Server local database

texts:
I'm doing a project in Visual Studio 2012 and it involves working with the given database. As I add the database.mdf to the project (asp.data folder) I got a connection error, but nothing out of the ordinary. To "repair" the database we are supposed to enter this into cmd

cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"
SqlLocalDB.exe delete v11.0

At school everything worked fine, but at home I got this error in cmd

  Delete of LocalDB instance "v11.0" failed because of the following error:
  Requested operation on LocalDB instance cannot be performed because specified instance is currently in use. Stop the instance and try again.

What do I have to close/do?


texts: Invalid directory path error while connecting to database

texts:
I am using oracle 11g and using sqldeveloper to run/edit queries.

My table is:

ID     FIRST_NAME   LAST_NAME    MAJOR                 CURRENT_CREDITS
10008   A            Dd          Computer Science         2
10009   B            DD          History                  3
10010   D           dd           Computer Science         3
10011   C           ad           Economics                3
10012   D           Da           History                  3
10013   E           df           History                  3

Above table is in CSV format (lect.csv) stored in D:\1Deepak\Data.

I have created below procedure to import the csv file in oracle, but it’s not working getting below error :

  Connecting to the database r.

  ORA-29280: invalid directory path

  ORA-06512: at "SYSTEM.LOADLECTURER", line 39

  ORA-06512: at line 2

  Process exited.

  Disconnecting from the database r.

Code :

create user D identified by d
CREATE DIRECTORY log_dir AS 'D:\1Deepak\Data';
Grant all privileges to D;
Grant Read, Write on Directory log_dir to D;

CREATE TABLE lecturer (
  id               NUMBER(5) PRIMARY KEY,
  first_name       VARCHAR2(20),
  last_name        VARCHAR2(20),
  major            VARCHAR2(30),
  current_credits  NUMBER(3)
  );

CREATE OR REPLACE PROCEDURE Loadlecturer
AS
p_TotalInserted number;
  v_FileHandle UTL_FILE.FILE_TYPE;
  v_NewLine  VARCHAR2(100);  -- Input line
  myFirstName lecturer.first_name%TYPE;
  v_LastName lecturer.last_name%TYPE;
  v_Major lecturer.major%TYPE;
  v_FirstComma NUMBER;
  v_SecondComma NUMBER;
BEGIN
  v_FileHandle := UTL_FILE.FOPEN ('Log_dir','Lect.csv', 'r');
  p_TotalInserted := 0;
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_FileHandle, v_NewLine);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;

    v_FirstComma := INSTR(v_NewLine, ',', 1, 1);
    v_SecondComma := INSTR(v_NewLine, ',', 1, 2);

    myFirstName := SUBSTR(v_NewLine, 1, v_FirstComma - 1);
    v_LastName := SUBSTR(v_NewLine, v_FirstComma + 1,
                         v_SecondComma - v_FirstComma - 1);
    v_Major := SUBSTR(v_NewLine, v_SecondComma + 1);

    INSERT INTO lecturer (ID, first_name, last_name, major) VALUES (1, myFirstName, v_LastName, v_Major);

    p_TotalInserted := p_TotalInserted + 1;
    dbms_output.put_line(p_totalinserted);
  END LOOP;
  UTL_FILE.FCLOSE(v_FileHandle);
 Exception
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_FileHandle);
    RAISE;
END Loadlecturer;

From where is the above mentioned error is coming?


texts: Revoke alter permissions on service account

texts:
I had run the following statements to grant alter permissions on specific tables for the service account

ALTER AUTHORIZATION ON [PartyMsci] TO  "INT\svc-w-corerefdata-de";
ALTER AUTHORIZATION ON [Product] TO  "INT\svc-w-corerefdata-de";
ALTER AUTHORIZATION ON [SimpleIndustryPICS8] TO  "INT\svc-w-corerefdata-de";

How do I remove these permissions ?


texts: How to let two sessions read two different records

texts:
For example, there is one table T1 with clustered index. I have two sessions and I want to have session one return one record and session two returns another record. Is it possible?

I tried following codes in two sessions but the table scan will block another session.

BEGIN TRANSACTION
SELECT TOP 1 * FROM T1 WITH(UPDLOCK) ORDER BY NEWID()
-- ROLLBACK TRANSACTION


texts: Transformations and source info for data ditionary

texts: I am currently building our data dictionary for our data warehouse, but two things we would like to add is the source for the fields, and the transformations that were applied to them. Is there a way to get the info from our ssis server to be included in our dictionary.


texts: 1 out of 3 Transactions in DB2 fail with SQL 998, State 58005, error code 8, sub code 2

texts:
We have a few back-end applications using an XA switch module connected to a DB2 (10.5).
2 or 3 transactions will pass until this shows up:

I’ve found this document that talks about “Code 998, state 58005, reason code 9, subcode 2".
Unfortunately, all they say is:

  For reason code 9, subcode 02, ensure that the tp_mon_name configuration parameter contains the name of the dynamic library in the external product which has the ax_reg() function used for dynamic registration of transactions.

On the other hand, I found this document where, on the explanation of tm_database, they say "don't use tp_mon_name, if you are using XA switch module", which I am. But they don't explain tm_database.
I have two versions of the switch module, one with CONSTANT DYNAMICREGISTRATION "Y" and one with CONSTANT DYNAMICREGISTRATION "N".
The latter exists somewhere and will take me huge time to find.
Do you think it could be it?


texts: InnoDB: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better?

texts:
Say I have a table

(
person_id INT AUTO_INCREMENT,
birthday_timestamp BIGINT,
country,
state,
...,
PRIMARY KEY (person_id),
KEY (birthday_timestamp)
)

where people are added the instant they're born.  In other words, the order of person_id is the same as that of birthday_timestamp.  More formally, if I had two rows, A, and B,

A.person_id < B.person_id --> A.birthday_timestamp <= B.birthday_timestamp

Would it be reasonable to assume that performance on a WHERE by birthday_timestamp range should not be significantly worse than the equivalent WHERE on person_id range?

My rudimentary understanding of how this would be executed is the engine would fetch the list of person_id in the secondary index on birthday_timestamp and then fetch those results from the clustered index.  Since these person_ids will be 100% contiguous, this should lead to minimal fetching of clustered index pages, right?

In contrast, if instead of birthday_timestamp, I had something whose order was completely irrelevant...say, favorite_number, this wouldn't be the case.  In general, the person_id's corresponding to favorite numbers between 15 and 1,000,000 would be completely spread out and non-contiguous.

Back to the former case.  The one alternative I can think of that wouldn't involve a join at all is the following.  Say I want person_id, country, etc., for people born between 0 and 12345678.  I could do

select * from people where birthday_timestamp >= 0 and birthday_timestamp <= 12345678

Which is what I described above and I assume would necessitate a join since MySQL doesn't actually know the keys are parellel, OR I could do two (almost)-point queries into the secondary index to grab the primary key range myself, i.e.

start = $(select person_id from people where birthday_timestamp >= 0 order by birthday_timestamp asc limit 1)

end = $(select person_id from people where birthday_timestamp <= 12345678 order by birthday_timestamp desc limit 1)

select * from people where person_id >= start and person_id <= end

Which seems like it could be a bit more optimal, though not by an order of magnitude.  The downside is it's less concise.

Any thoughts?

Thanks


texts:
In collection A, we have

"batches" : [
    ObjectId("56fccffbe7c9f9cf7634dbfe")
],

and in Collection B, we have

{ "_id" : ObjectId("56fccffbe7c9f9cf7634dbfe") }

Can we perform a lookup between these 2 collections?


texts: What's the equivalent of --safe-updates of mysql in MS SQL Server?

texts:
In MySQL, you can use the feature called --safe-updates (--i-am-a-dummy) to limit the number of rows updated per query.

http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html#safe-updates

Is there such a thing in MS SQL Server?


texts: What causes Transactional SQL Server Replication to need to be rebuilt/re-initialised

texts:
In a company I've recently joined we have two SQL Server instances, connected via transactional replication.  I'm a developer and hadn't really come across SQL replication before so am a complete novice.  I'm looking at reducing the time it takes to deploy our products to the production environment and right now rebuilding replication is the worst offender.  A deployment often involves the standard SQL schema changes (new tables/indexes, modifications to existing tables/indexes) and we have bespoke SQL CLR code that is often needed.  At the moment if feels little like Russian roulette in that some changes can be applied without adversely affecting replication, whilst others (custom CLR code deploy) seems to require a rebuild (or is it the stored procs that must be dropped / recreated to access it?).

As a complete beginner with replication I was wondering if there is a resource or anyone knows a definitive list of changes that will force replication to need to be rebuilt?  Please don't ask why we've got replication, or SQL CLR code, neither should be needed but their removal is going to take a while and we need to get on top of the length of time deployments take first.  It would just be nice to understand before a release whether it was going to need to rebuild replication!


texts:
I am attempting to automate restoring .bak files on a secondary database in the case of a primary server failure.  Both servers are SQL server 2014.

Here is a sample SQL script:

USE master
GO

DECLARE @DBName varchar(50)
DECLARE @BackupFile varchar(256)

Set @DBName = 'test-db'
Set @BackupFile = 'D:\MSSQL\Backups\test-db\test-db_backup_2016_03_18_001622_2611392.bak'

-- Find Logical and Physical file names
declare @LogicalDataFile      sysname
        , @LogicalLogFile       sysname
        , @PhysicalDataFile     nvarchar(260)
        , @PhysicalLogFile      nvarchar(260)

-- Data file
select  @LogicalDataFile = name
        , @PhysicalDataFile = physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'ROWS'

-- Log file
select  @LogicalLogFile = name
        , @PhysicalLogFile = physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'LOG'

-- Print variables
Print 'Logical Data File: ' + @LogicalDataFile
Print 'Logical Log File: ' + @LogicalLogFile
Print 'Physical Data File: ' + @PhysicalDataFile
Print 'Physical Log File: ' + @PhysicalLogFile

-- Set as single user mode
EXEC('ALTER DATABASE [' + @DBName
+ '] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE')

--DB that will be over written
RESTORE DATABASE [@DBName]
--Restore file with data
FROM DISK = @BackupFile
WITH MOVE @LogicalDataFile TO @PhysicalDataFile,
MOVE @LogicalLogFile TO @PhysicalLogFile,
REPLACE,
STATS=10

-- Return to multi-user mode
EXEC('ALTER DATABASE [' + @DBName
+ '] SET MULTI_USER')

When I run the above script I get the following output:

Logical Data File: test-db
Logical Log File: test-db_log
Physical Data File: D:\MSSQL\Data\test-db.mdf
Physical Log File: D:\MSSQL\Data\test-db_log.ldf
Msg 1834, Level 16, State 1, Line 50
The file 'D:\MSSQL\Data\test-db.mdf' cannot be overwritten.  It is being used by database 'test-db'.
Msg 3156, Level 16, State 4, Line 50
File 'test-db' cannot be restored to 'D:\MSSQL\Data\test-db.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 50
The file 'D:\MSSQL\Data\test-db_log.ldf' cannot be overwritten.  It is being used by database 'test-db'.
Msg 3156, Level 16, State 4, Line 50
File 'test-db_log' cannot be restored to 'D:\MSSQL\Data\test-db_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 50
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 50
RESTORE DATABASE is terminating abnormally.

I wanted to make sure that the MOVE statements weren't being ignored for some reason, so I changed the variable names for the logical and physical file names - as expected, I got an error stating that the variables must be declared.

Is there something else that needs to be done to allow the database to be overwritten?  There are no other connections to this database.


texts: How can the LEFT JOIN return more results than the unjoined query?

texts:
Consider the following queries:

mysql> SELECT count(*) FROM list l WHERE l.source='blink';
+----------+
| count(*) |
+----------+
|     3372 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----------+
| count(*) |
+----------+
|     5116 |
+----------+
1 row in set (2.47 sec)

There were no INSERT or other queries run on the database between these two queries running. The second query seems more restrictive than the first query, so how could it return more rows? Note that the list table has over 2 million rows, and the cardinal table has about half a million rows.

EDIT: Adding EXPLAIN output:

mysql> EXPLAIN SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref         | rows   | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | c     | ALL    | NULL              | NULL    | NULL    | NULL        | 536258 | Using where |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,id,source | PRIMARY | 8       | direct.c.id |      1 | Using where |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select count(*) from list;
+----------+
| count(*) |
+----------+
|  2165664 |
+----------+
1 row in set (1.32 sec)

mysql> select count(*) from cardinal;
+----------+
| count(*) |
+----------+
|   537007 |
+----------+
1 row in set (0.23 sec)


texts: How can I display a mysql bit(64) column as a string of ones and zeros?

texts:
We have a MySQL 5.5 database  with a table that holds a number of 64-bit values which are used for calculating the hamming distance.  The columns for these are defined as BIT(64) and are populated using the bit-field literal syntax like this (note the 0b prefix):

create table hash_values ( hash bit(64) );
insert into hash_values;
  (0b0000000011111111000000001111111100000000111111110000000011111111)

For debugging purposes we need to display the bit values as strings of ones and zeros, but have only been able to do this with what feels like an ugly hack:

select conv(cast(hash as unsigned), 10, 2) hash from hash_values;
+------------------------------------------------------------------+
| hash                                                             |
+------------------------------------------------------------------+
| 0000000011111111000000001111111100000000111111110000000011111111 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)

This first uses CAST() to get an integer representation; then uses the CONV() method to covert it from base-10 back to base-2.  It seems to work, but I feel like we've overlooked a simple way to cast the bit value to a base-2 string.


texts:
I have a database that has log file of around 64 GB and the VLF count for the same is 500+. Almost all of them are active. I checked the open transactions and the oldest transaction on that database is from two days back. The session for that transaction is in SUSPENDED state. So, if I kill this SPID, will it mark the VLFs as INACTIVE ? Or will it go into roll back mode and run for another couple of days without marking the VLFs as INACTIVE?

I am not even able to identify from where this transaction is originated. The host name just displays a web server and the Program Name has .Net SqlClient Data Provider so this looks like some transaction initiated by some code in the application.


texts: How do I refresh SQL server cross database references after a database is reattached with a different name? (Are there hidden synonyms?)

texts:
I have 2 seperate SQL databases on the same SQL instance. Database1 references tables on Database2 in its views and stored procedures. Everything was working fine until we had to create a fresh version of Database2 and rename the current Database2 to Database3.

The DBA did the following:

Took Database2 offline, then reattached it as Database3. Database2 was then deleted from SQL.
A new version of Database2 was created.

The problem:

The view and stored procedures in Database1 are still referencing the tables on Database3 unless we add the server name to the FROM clause in any queries.

The following view on Database1 goes to Database3 in error:

select * from Database2.dbo.Projects

For the correct behaviour we specify the server name:

select * from ProductionDB.Database2.dbo.Projects

Why are the views and stored procs of Database1 still referencing Database3 (unless we specify server name)?

Is it because we renamed the database by re-attaching instead of backup and restore?

Are there internal references in Database1 that need updating?

Are there hidden database synonyms I can't find?

sql sql-server backup database-administration cross


texts:
What does the green and blue indicate from the below scrteenshots of disk activity on SQL Server database server?

Thanks!


texts: I need to hide an open Access table from users

texts:
For application startup I had to open a table as part of some DAO.recordSet code.

 DoCmd.OpenTable "User", acNormal, acReadOnly

On running user tests, I want to distribute the desktop application as a run-time file (.accdr) but the access table is visible. All I can find is how to hide navigation area objects.

I, of course, do not want the table visible to users, so my question is how do I hide it?


texts: Read GPS Coordinates into LineString from Table

texts:
I found this beautiful piece of code which takes a linestring and
I can get the points from it:

DECLARE @GeographyToConvert geography
SET     @GeographyToConvert = geography::STGeomFromText('LINESTRING (26.6434033 -81.7097817, 26.6435367 -81.709785, 26.6435783 -81.7098033, 26.6436067 -81.709825, 26.6435883 -81.709875, 26.64356 -81.7100417, 26.6434417 -81.710125, 26.6433167 -81.7101467, 26.643195 -81.7101033, 26.6431533 -81.7099517, 26.643175 -81.7097867, 26.643165 -81.7097917, 26.6431633 -81.7097367, 26.6431583 -81.7097083)',4326);
select @GeographyToConvert.STPointN(1)
select @GeographyToConvert.STPointN(2)
select @GeographyToConvert.STPointN(3)

Returns:

0xE6100000010C273339106D6D54C073A32314B6A43A40
0xE6100000010C3F8C101E6D6D54C0984638D2BEA43A40
0xE6100000010CF205D26A6D6D54C050EA268CC1A43A40

From here, I can do what I need to do via the mentioned piece of code.

Instead of the prepopulated demo Linestring however, I have a table with GPS Coordinates
which contain the data from which I need to populate the @GeographyToConvert geography

declare @Geo table(long float, lat float)
insert into @Geo(  long, lat ) values
               ( -121.527200, 45.712113 ),
               ( -121.517265, 45.714240 ),
               ( -121.511536, 45.714825)

Select * from @Geo

Can I please ask help in getting the @Geo table values
into the @GeographyToConvert geography


texts:
I want to select a record from one table (customers table) and join only with the last from other related table (sales table). I know two ways of doing it, but I don't know which one have better performance. What is the best approach?

Note: in my case I'll get only one row because I want the data for one specific customer but what if I want all the main rows, does it change something?

Option 1, using sub-query in WHERE:

SELECT c.id, c.name, s.id, s.cost
FROM
  customers as c
  LEFT JOIN sales as s ON s.customer_id = c.id
WHERE
  c.id = 42
  AND s.id = (SELECT MAX(s2.id) FROM sales as s2 WHERE s2.customer_id = c.id) ;

Option 2, using sub-query in FROM:

SELECT c.id, c.name, s.id, s.cost
FROM
  customers as c
  LEFT JOIN (
    SELECT s2.customer_id as cid, MAX(s2.id) as maxid
    FROM sales as s2
    WHERE s2.customer_id = 42
    GROUP BY s2.customer_id
  ) maxids ON c.id = maxids.cid
  LEFT JOIN sales as s ON s.id = maxids.maxid
WHERE
  c.id = 42 ;

Here are the tables CREATE:

CREATE TABLE `customers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(120) COLLATE utf8_spanish_ci NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22149 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_c

CREATE TABLE `sales` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `customer_id` int(11) DEFAULT NULL,
 `status` tinyint(3) unsigned DEFAULT NULL,
 `buydate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `cost` decimal(10,2) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `customer_key` (`customer_id`) USING BTREE,
 KEY `status_key` (`status`) USING BTREE,
 CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11555 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci

And here their EXPLAINS:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   PRIMARY c   NULL    const   PRIMARY PRIMARY 4   const   1   100.00  NULL
1   PRIMARY s   NULL    const   PRIMARY,customer_key    PRIMARY 4   const   1   100.00  NULL
2   DEPENDENT SUBQUERY  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Select tables optimized away

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   PRIMARY c   NULL    const   PRIMARY PRIMARY 4   const   1   100.00  NULL
1   PRIMARY <derived2>  NULL    system  NULL    NULL    NULL    NULL    1   100.00  NULL
1   PRIMARY s   NULL    const   PRIMARY PRIMARY 4   const   1   100.00  NULL
DERIVED s2  NULL    ref customer_key    customer_key    5   const   11  100.00


texts: Is it possible to schema-qualify an operator?

texts:
Let's say I create an extension, like PostGIS, in the schema postgis,

CREATE EXTENSION postgis WITH SCHEMA postgis;

Is it possible to fully qualify an operator like && in the below example,

SELECT box2d && ST_MakePoint(5,5)
FROM postgis.box2d(postgis.ST_MakeEnvelope(0,0,10,10));

But "postgis.&&" and even "postgis".&& and "postgis"."&&" is not working for me. The docs on "Operator Type Resolution" seem to hint at being possible,

  If a qualified operator name was given, only operators in the specified schema are considered.

Put another way, can I use an operator that is "not visible".

SELECT pg_type_is_visible(
  'postgis.&&(postgis.geometry,postgis.geometry)'::regoperator
);

Clearly, I can add postgis to my search_path but I'm looking to leave that unmodified and in ever other context we can in fact qualify everything.


texts: standard solution for storing units of measure

texts:
INTRODUCTION AND RELEVANT INFORMATION:

Let us say I have a table that stores height and weight of a person, something like this :

PersonTable < # ID int, Name varchar(50), Height double, Weight double >

Height is measured in centimeters and Weight in kilograms.

PROBLEM:

My problem is about storing unit of measure for the height and weight.

MY EFFORTS TO SOLVE THIS:

 I could try to implement EAV model.
I could modify the table by adding extra columns that hold unit of measure for Height and Weight ? Something like below:

PersonTable < # ID int,
                Name varchar(50),
                Height double, $ HeightUnit_Id int,
                Weight double, $ WeightUnit_Id int >

Units < #ID int, Desription varchar(20) >

HeightUnit_Id and WeightUnit_Id are foreign keys that reference Id from table Units.

QUESTION:

Is there a better solution than the obvious ones I mentioned above, since I dislike both ( EAV can get messy if done badly, and adding extra "unit columns" seems like a waste of space to me )?


texts: Handling case statement subquery returning multiple rows

texts:
select *
from temp tp
join a
on    tp.id = a.id
where (  a.enddate   between @startdate and @endDate
      or a.startdate between @startdate and @endDate)
and   a.id1 in (case when (isnull(@id,''<>'')
                     then (select distinct id from #temp2)
                     else a.id1
                end)

The #temp2 query return multiple rows.Is that possible to achieve through case stament or else please do suugest the best possible way.

UDPATE: I am using the case statement as when the @id is null it should return all the records (a.id1=1,2,3,4,5,6)but when the @id=1,2,3 is not null it should return only those records which have the a.id1=1,2,3.


texts: What is difference between JOIN and simple query

texts:
I have two tables in database one is Orders and another one is customers

Orders Table Contains : OrderID , CustomerID and OrderDate

Customers Table Contains: CustomerID , CustomerName , ContactName  and country

  Actually the "CustomerID" column in the "Orders" table refers to the
  "CustomerID" in the "Customers" table. The relationship between the
  two tables above is the "CustomerID" column.

Query with Join

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
    FROM Orders
    INNER JOIN Customers
    ON Orders.CustomerID=Customers.CustomerID;

Simple Query

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders,Customers
WHERE Orders.CustomerID=Customers.CustomerID;

Result of Both Queries is : OrderID , CustomerName and OrderDate

On both the above query i get the same result . I want to know the difference between these queries and if we can do it via SIMPLE query why use JOIN?


texts: 2 fast SQL queries get slow when bunched as subquery on a very large table

texts:
I'm trying to use a subquery to speed up a query on a very large table right now 30 million row it most likely going to end up close to 1 billion rows.

My table lnmntr is as follow

Lnmntr_ndx:  auto increment index,
Val: Numeric,
T_stamp: Time_Stamp indexed,
Row_id: Numeric

So my main query looks like this. This basically execute in no time. It select the 500 row following an autoincrement index

SELECT *
FROM CLD_DB.lnmntr
WHERE lnmntr_ndx >= 18411001
ORDER BY lnmntr_ndx ASC
LIMIT 500;

My subquery is used to find 1 row with the correct val. I've added a between timestamp here because I know around what time I'll find the row I need and since it's indexed it speed up thing. This query executes in about .4 seconds

SELECT Lnmntr_ndx
FROM CLD_DB.lnmntr
WHERE (t_Stamp BETWEEN '2016-06-05 16:22:04' AND '2016-06-05 16:23:04')
  AND (Row_id IN (0,500,1000,1500,2000,2500,3000,3500,4000,4500,5000,
                  5500,6000,6500,7000,7500,8000,8500,9000,9500)
  AND Val = 49092400);

but when I bunch the 2 queries together, I end up with a 20 second query

SELECT *
FROM CLD_DB.lnmntr
WHERE lnmntr_ndx >= (SELECT Lnmntr_ndx
                     FROM CLD_DB.lnmntr
                     WHERE (t_Stamp BETWEEN '2016-06-05 16:22:04' AND '2016-06-05 16:23:04')
                       AND (Row_id IN (0,500,1000,1500,2000,2500,3000,3500,4000,4500,5000,
                        5500,6000,6500,7000,7500,8000,8500,9000,9500)
                       AND Val = 49092400)
                     )
ORDER BY lnmntr_ndx ASC
LIMIT 500;

I could run the 2 queries back to back to speed up thing but it would be much easier to run a single query. And id really like to know why they get this slow when bunched together