texts: Oracle 10 xe; backing up oradata folder enough to recreate a duplicate later?

texts:
Let us say, I backup of the the oradata folder.

Is that enough to recreate the exact same database?

E.g., I reinstall oracle xe 10 on the same pc, using the same installer, into the same folder, and the replace its oradata folder with one i have?

And also is it the exact duplicate in terms of the database?


texts: Unexplained connections from one SQL server to another

texts:
I just installed 2 SQL Server 2016 Enterprise and configured a High Availabilty Group. As a test object for the HA-Group I used a database I restored from another server. It's a database called "dbtools" which only contains objects related to Ola Hallengren's Maintenance Solution, i.e. four StoredProcedures and a Log Table. And Oh - it also contains 2 SP's from BrentOzar (BlitzIndex) and one sp named sp_WhoIsActive which I found somewhere not so long ago.
I have looked at these sp's and found no reference to any server.

Can anyone give me any ideas how to start looking for the reason for these Connections?

oh, I just realized: The Cluster witness is a shared folder on this other server - but I can't see why master and msdb databases should be queried?

sp_Who2 gives these lines:

edit: explanation for the image: the results from sp_who2 is from "serverA", a standalone SQL2016 - "clusternode" is Another server, namely the one holding the HA Group in my Always On cluster. I don't understand why one part of a two-node cluster would take such interest in a server outside the cluster?


texts: Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

texts:
We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column):

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE ( to_days(end_time))
(PARTITION p20130521 VALUES LESS THAN (735375) ENGINE = InnoDB,
....
PARTITION p20130603 VALUES LESS THAN (735388) ENGINE = InnoDB,
PARTITION p20130604 VALUES LESS THAN (735389) ENGINE = InnoDB,
PARTITION p20130605 VALUES LESS THAN (735390) ENGINE = InnoDB,
PARTITION p20130606 VALUES LESS THAN (735391) ENGINE = InnoDB,
PARTITION p99991230 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Starting with MySQL 5.5, it is now possible to avoid the to_days and partition by real dates. Is there any (speed) advantage to doing so?


texts: Backup SQL Server database on UNC path using SA

texts:
We need to backup some database to an unc path, the problem it's that it's a SQL Server instance that has to execute with SQL Server autentication (sa user).

How can we configure the plan task in SQL Server to make it able to have access to the UNC folder? I attach the state of our SQL Server Services in SQL Server Configuration Manager.

Thanks in advance and regards,

Pedro Sánchez.


texts: How to get a list/result of best selling items

texts:
I'm trying to get the results of my best selling products using SQL.

My table is order_items which has the following columns.

id|product_id|qty

As there is a QTY field I have to somehow count or group each by product ID then sum the qty, then only return the top 5 selling items.

So the data looks kind of like below.

id|product_id|qty
-------------------------
 1|   1      |   1
 2|   1      |   3
 3|   2      |   3
 4|   2      |   1
 5|   1      |   7

... and so on

Not to sure about how to go about this, any help is much appreciated.

Database system used is MySQL


texts:
Trying to figure out a proper way to clean old WAL files located in archive directory for point in time recovery purposes.
I know I can use hard-coded name of WAL segment something like this:

pg_archivecleanup.exe H:\Backup\pgArchive 0000000100000001000000C1

but apparantly I would like to automate this process.

Thank you.


texts: ORACLE execute error: ORA-01950: no privileges on tablespace 'PDATA'

texts:
I'm new in Oracle, so maybe my question could be stupid. I use Oracle only for data storage. I have made some research but I'm blocked. I use Oracle 12c. I created a PDB with admin user PEEI_SYS like this:
create pluggable database PEEI admin user PEEI_SYS identified by PEEI
roles = (DBA);
-- open PDB PEEI
alter pluggable database PEEI open read write;`

I have created another user called PEEI which should only do select, update, insert on tables owned by PEEI_SYS. I have created the user PEEI like this:
CREATE USER "PEEI" IDENTIFIED BY "PEEI" DEFAULT TABLESPACE PDATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK;`
Now I would like that the user PEEI could insert rows in the table PEEI_SYS.PEEI_P_TRACKING. This table is created like this:
 `CREATE TABLE PEEI_SYS.PEEI_P_TRACKING
(
"CODE_WORKFLOW" VARCHAR2(30 BYTE),
"STATUS" VARCHAR2(15 BYTE),
"DATE_UPDATE" DATE,
"USER_UPDATE" VARCHAR2(20 BYTE),
"DEB_WORKFLOW" DATE,
"FIN_WORKFLOW" DATE,
"TIME_SECOND" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PDATA" ;
GRANT SELECT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_READ;
GRANT DELETE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;
GRANT INSERT ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;
GRANT UPDATE ON PEEI_SYS.PEEI_P_TRACKING TO ROLE_PEEI_WRITE;

When I got the error I granted unlimited privileges to PEEI user on PDATA tablespaces like this: ALTER USER PEEI QUOTA UNLIMITED ON PDATA;
I have still the error. Could you please help me ?
Thank you very much in advance.
Kind regards,


texts:
What is wrong in the below trigger syntax, I am getting this error An unexpected token "INT" was found following " DECLARE NULL_ROW_ID".  Expected tokens may include:  "END-OF-STATEMENT".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61

CREATE TABLE A (
    A_ID INT NOT NUll,
    A_CODE VARCHAR(100) NOT NULL,
    PRIMARY KEY(A_ID)
);

CREATE TABLE B (
    B_ID INT NOT NUll,
    B_CODE VARCHAR(100) NOT NULL,
    A_ID INT,
    PRIMARY KEY (B_ID),
    FOREIGN KEY (A_ID) REFERENCES A (A_ID)
);

CREATE OR REPLACE TRIGGER B_TRIGGER
AFTER UPDATE OF A_ID ON B
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
    WHEN (N.A_ID IS NULL)
    BEGIN ATOMIC
        DECLARE NULL_ROW_ID INT;
        SET NULL_ROW_ID = (SELECT A_ID FROM A WHERE A_CODE = 'Custom Null String');
        IF NULL_ROW_ID IS NULL THEN
            SIGNAL SQLSTATE '70000' SET MESSAGE_TEXT = 'Custom Null String row not found.';
        END IF;
    SET N.A_ID = NULL_ROW_ID;
    END
@

It's on DB2 Express-C 10.5


texts:
I used Hyper-v to install one windows 2008 server machine, and copy the image to create another one and change the computer name, both of them are in the same domain, and I use a domain user as administrator to configure them.

After installing SQL server 2008, I tried T-SQL below:

SET XACT_ABORT ON
GO

BEGIN DISTRIBUTED TRANSACTION
EXEC CatalogSQLServer.Catalog.dbo.Hello
ROLLBACK

The first time I tried，I got the error information like:

  OLE DB provider 'SQLNCLI' for linked server '' returned message 'No transaction is active

I am 100% sure that I have configure DTC,Linked server and allow all inbound and outbound rule of firewall.  I configured MSDTC based on these instructions.

after that, I tried use command:

msdtc -uninstall
msdtc -install

...and then restart dtc to re-install msdtc on CatalogSQLServer.  But this time, when I execute the test script, SSMS will keep executing for a very long time but didn't get any result.  Does anybody know how to fix this issue?


texts: How can I aggregate multiple results from another table using something like BOOL_AND?

texts:
I'm using PostgreSQL as my database.

CREATE TABLE users (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE groups (
  id        SERIAL        NOT NULL,
  name      TEXT          NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE associations (
  user      INT           NOT NULL,
  group     INT           NOT NULL,
  admin     BOOLEAN       NOT NULL DEFAULT false,
  PRIMARY KEY (user, group),
  FOREIGN KEY (user)
    REFERENCES users(id),
  FOREIGN KEY (group)
    REFERENCES groups(id)
);

Similar to another question I posted here, how can I compare the result of multiple rows from another table and aggregate them in a view, perhaps using something like BOOL_AND?

CREATE VIEW user_info AS
  SELECT DISTINCT
    u.id              AS id,
    u.name            AS name,
    EXISTS (
      SELECT 1 FROM associations AS a
      WHERE (u.id = a.user AND a.admin = true)
    )                 AS is_admin,
    BOOL_AND(???)     AS is_complete_admin
  FROM users AS u
;

Here, I'm wanting the is_complete_admin to be true if every row in associations for a particular user has admin = true.

Again, I think BOOL_AND is an option here, but I'm just not sure how to put it all together.


texts: uninstalling SSRS and SSIS from clustered SQL Server 2008 R2 SP1

texts:
Due to the security recommendation, i will need to uninstall the unused SSRS and SSIS component from a production cluster SQL Server 2008 R2 SP1, so what is the caveats and what do I require to do to ensure the Database engine still up and running ?

Thanks,
Al


texts: Alternative to EAV to store data with an unknown structure

texts:
For my bachelor thesis I have to build a reseach database which has to meet the requirement of provenance. In the present case it means that among other things I have to store the results of my queries and the query itself in the database.

The problem I encounter is the fact that I can get results which structure is not known before. I can get scalar result, e.g. an average value, I can get an n-tuple as a result or even tables as a result of a table function. The user is allowed to perform any operation on the raw data and the result has to be stored in the database.

My thesis tutor suggested me to use the EAV schema. I have read a couple of posts on DBA and have seen that this can get extremely complicated, especially when you have something like nested datatypes in the result (e.g. A graph as a result has points, a point has X- and Y- coordinates, the coordinates have float-values. I implemented an EAV schema in MySQL and have seen the complexity to store this). Nearly everyone disadvised to use EAV on DBA.

Is there a useful alternative to this EAV schema in my case? I have read some papers about some approaches with NoSQL databases but the problem is that I have to use MySQL because the institution which takes care on my thesis uses MySQL for their curated research databases.


texts: Database design: Is there a much better way on this current relationships?

texts:
I have a facultyand studenttable to store each information, respectively. I made a contacttable to store the contacts of either faculty or student. I would like to use the contact table as a generic one, means it can be used by either faculty or student.

My table diagram is something like this.

I compressed faculty and student into a USER table and have an account type in that table to know whether it is a faculty or student type of user. Then the USER table will have a one is to many relationship with the contact table.

  Student

  Faculty

  Contacts

  Student & Faculty = User table  User table -----<- Contact table


texts:
I'm trying to move a database from a Windows2003Server with mysql 5.0.27-community-nt to a new server with CenTOS. When using mysqldump on the database on the windows server, one table (i haven't started checking how widespread the problem is) is being dumped with quite some deleted records in it.
After restoring the dump on the new server, I can easily spot records that exist on the new server but not on the old one. However, when I do a select count(*) both tables show the same record.
The dump is created with --opt, and I also tried dropping the new server's table before a restore. Reading through the dump (I did a table exclusive dump) I can spot the inserts for the records that exist in one database but not the other one.
I also tried doing a flush tables before dumping the table, and tried using SQLyog's export to SQL function, all with the same result.

What can be going on?


texts:
I have a MySQL table, which stores exchange rates for two currencies, with a specific date. This table is updated every 15 second by an external script, and the data it contains is rather large, and will get even larger in time (since 2017 september, we have gathered ~8 million rows, but the updates at the beginning where with a 2 minute time scale).

The tables structure looks as follows:

 |---------------------------------------------------------|
 | RateId | RateTime | BaseCurrency | QuoteCurrency | Rate |
 |---------------------------------------------------------|

There are the following indexes in place on this table:

PRIMARY         BTREE   Yes No  RateId        7534199   A   No
BaseCurrency    BTREE   No  No  BaseCurrency  5199      A   No
                                QuoteCurrency 17938     A   No
QuoteCurrency   BTREE   No  No  QuoteCurrency 14        A   No
RateTime        BTREE   No  No  RateTime      753419    A   No

When I query current data, for example:

SELECT  *
    FROM  ExchangeRates
    WHERE  BaseCurrency = 'EUR'
      AND  QuoteCurrency = 'USD'
    ORDER BY  RateTime DESC
    LIMIT  0,1

the query is very fast, around 0.0029 seconds.

When I try to query historical data, like:

SELECT  *
    FROM  `ExchangeTable`
    WHERE  CurrencyFrom = 'EUR'
      AND  CurrencyTo = 'USD'
      AND  ExchangeTime <  '2018-01-29 12:00:00'
    ORDER BY  ExchangeTime DESC
    LIMIT  0,1

it is still fast, around 0.0008 seconds.

The problem starts when I try to query data, that we are not refreshing anymore, but I would still need historical information about. For example, we stopped gathering the rate for EUR->RON 6 months ago. If I would like to do the following query:

SELECT  *
    FROM  `ExchangeTable`
    WHERE  CurrencyFrom = 'EUR'
      AND  CurrencyTo = 'RON'
      AND  ExchangeTime <  '2018-01-29 12:00:00'
    ORDER BY  ExchangeTime DESC
    LIMIT  0,1

it takes around ~9.5897 seconds to execute, with the row found dated in 2017-08-25 13:07:00.

Why is this query so slow, while the rest of the queries are fast? What could I do to improve these kind of queries?

We are using MariaDB, with InnoDB storage engine.


texts: How do you version your Oracle database changes?

texts: I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc.  Do you use flat files with an external version control system?  Triggers? Other software?


texts:
I'm a little bit confused about how query optimizer eliminates unnecessary columns from view's query when you need to select only specific one.

Here is my view:

CREATE VIEW Schema1.Object1
AS
  SELECT Object2.Column1  AS Column2,
         Object2.Column3  AS Column4,
         Object3.Column5,
         Object3.Column6,
         Object4.Column1  AS Column7,
         Object5.Column1  AS Column8,
         Object5.Column9  AS Column10,
         Object5.Column11 AS Column12,
         Object5.Column13 AS Column14,
         Object5.Column15 AS Column16,
         Object5.Column17 AS Column18,
         Object5.Column19 AS Column20,
         Object5.Column21 AS Column22,
         Object5.Column23 AS Column24,
         CASE
           WHEN Object2.Column25 >= Object5.Column25
             THEN Object2.Column25
           ELSE Object5.Column25
         END              AS Column25,
         Object2.Column26
  FROM   Schema1.Object6 AS Object2
         CROSS JOIN Schema2.Object7 AS Object4
         JOIN Schema1.Object8 AS Object3
           ON Object3.Column5 = Object2.Column5
              AND Object3.Column7 = Object4.Column1
         LEFT JOIN Schema1.Object9 AS Object5
           ON Object5.Column4 = Object2.Column3
         JOIN Schema3.Object10 AS Object11
           ON Object5.Column26 = Object11.Column27

Now, I just want to do a query like:

SELECT Column2
FROM Schema1.Object1

...but the estimated plan includes all columns from a view even though the query doesn't return them.

Why has this happened? Can I avoid this?

Here is a link for query plan: https://1drv.ms/u/s!AhdjYi359YDTgYF6sLo8fBsj5H6Ilg

also available at https://www.brentozar.com/pastetheplan/?id=S1MhIB3Ff


texts:
In this post the writer runs a query several times.  I notice the logical reads vary a little across executions.  There is a difference of about 2 pages in a total of a few thousand pages read.  It seems clear to me from the context that there would be no write activity in between times.  If the plan had changed I would have expected a larger variation than a fraction of a percent.

Q: what factors will cause SQL Server to report different logical read counts for the same query in the absence of data writes?


texts: Does the SQL Server Service Account have to have access to a network share to perform a backup?

texts:
I've been provided a specific domain account to handle backups from my sysadmins. I am slightly confused as I've never been provided a separate domain account to handle backups in the past. I still want to use Agent to handle this, so I am thinking that they are thinking the below:

  If you are running the job under a proxy account, the proxy must be a
  domain account. Add the account to the sysadmin server role and grant
  it full control of the directory and the network share.

So I've done what I thought the above is describing, I added the domain account as a login, I created a credential & proxy, and I created the job step as CmdExec to run under the proxy, but I still get:

  Operating system error 5(Access is denied.).

It isn't until I give access to the Service Account that is running the SQL Server Service that I am able to backup to this network share. I'm also concerned about restores and I wanted to utilize Agent to handle cleanup tasks & moving files around to various directories as they age.

So I'm not sure what's going on here, but I think the service account at a very minimum needs write access? One sysadmin said he was aware of someone doing the backup process as a "separate service", which I don't quite understand, unless its something like ApexSQL backup or something...


texts:
For a given ID, I'm trying to get the next row or if not present (end of list), return the first one.
ID (primary key)    value
 1                   John
 3                   Bob
 9                   Mike
 10                  Tom

If we look for 5, it should return the row with ID 9.
If we look for 10, it should return the row with ID 1.

Currently, I simply run two queries, first getting the next:
SELECT * FROM table WHERE id > 5 order by id limit 1;

and then if this returns no result, return the first:
SELECT * FROM table WHERE id = 1;

But I was wondering if there was a way to do it in one go?


texts: Passing parameters for dynamic SQL in a function

texts:
I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In outline, it looks something like this:
CREATE OR REPLACE FUNCTION return_result_table(
    _param1 integer[],
    _param2 text[])
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE
AS $BODY$
DECLARE
    _table_name TEXT;
    _select TEXT;
BEGIN
    _table_name := '_XYZABC_' || replace(current_date::text,'-','_') || '_' || 'ZXCVBN'; -- not the real code
    _select := '(select * from some_other_function(_param1, _param2))';
    execute 'create table some_schema.' || _table_name || ' as ' || _select;
    return _table_name;
END;
$BODY$;

The code is accepted, and it appears to be forming the command correctly as
execute 'create table some_schema._XYZABC_2020_10_07_ZXCVBN as (select * from some_other_function(_param1, _param2))'

but when I try to execute it with:
SELECT return_result_table(ARRAY[0,1,5,19],ARRAY['AA,'BB','CC'])

I get the error

ERROR:  column "_param1" does not exist

LINE 1: ...ect * from return_result_table(_param1...

It appears that the code is trying to substitute in the value of a column (of what?) rather than using the parameter.
How can I prevent this translation happening, so that I can pass the parameters through into the called function?


texts: PostgreSQL to SQL Server Migration Error Caused by Default Value Expression

texts:
I am working on integrating databases from both MySQL and Postgres into SQL Server. My data migration from Postgres to SQL Server is failing because of the default value expression in Postgres.

This is the default value expression: date_part('epoch'::text, now())

Does anyone know what the SQL Server equivalent is? I managed to successfully migrate the data using using date_part('epoch'::text, now())=1 though I do not know if this is the real equivalent?

Can someone please tell me if this is in fact the SQL Server equivalent?


texts: slow log contains query, but query time is less than long_query_time


texts: Direct database interaction with memory - how it works?

texts:
I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies.

I've noticed that the DBMS interacts directly with memory, as opposed to asking OS to interact with virtual memory. Is that correct? How exactly does this direct communication takes place? How much of memory is DBMS allowed to allocate? I've never been exposed to such low level function calls and I'd love to see some explanations and examples of it.

Getting this kind of info is kinda troublesome to find on google if you don't know the correct keywords to search for.


texts: ORA-03113 when starting up an Oracle 11g database

texts:
I have read "ORA-03113: end-of-file on communication channel" on startup and others from this site to solve my problem but I still have not solved it. All I get is the following:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jun 28 17:28:20 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
SQL> alter system set db_recovery_file_dest_size=35G scope=both;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  209235968 bytes
Fixed Size                  1332188 bytes
Variable Size             125832228 bytes
Database Buffers           75497472 bytes
Redo Buffers                6574080 bytes
Database mounted.
Database opened.
SQL> select instance_name from v$instace;
select instance_name from v$instace
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 43880
Session ID: 170 Serial number: 5

SQL>

Environment: Windows 7 64 bit, Oracle 11g.

Here are the details from alerts.log:

Sat Jun 28 17:32:26 2014
Flush retried for xcb 0x8a6711a8, pmd 0x8afba968
Doing block recovery for file 3 block 2686
Block recovery from logseq 13, block 68 to scn 132386303
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13 Reading mem 0
  Mem# 0: E:\APP\TRUCNGUYEN\ORADATA\XDLL\REDO01.LOG
Block recovery completed at rba 13.69.16, scn 0.132386305
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_pmon_55932.trc  (incident=36103):
ORA-00600: internal error code, arguments: [4194], [22], [28], [], [], [], [], []
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_pmon_55932.trc:
ORA-00600: internal error code, arguments: [4194], [22], [28], [], [], [], [], []
PMON (ospid: 55932): terminating the instance due to error 472
Sat Jun 28 17:32:27 2014
Errors in file e:\app\trucnguyen\diag\rdbms\xdll\xdll\trace\xdll_q001_79168.trc:
ORA-00472: PMON  process terminated with error
ORA-1092 : opidrv aborting process Q001 ospid (2836_79168)
Sat Jun 28 17:32:27 2014
ORA-1092 : opidrv aborting process W000 ospid (2836_56844)
Sat Jun 28 17:32:29 2014
ORA-472 : opidrv aborting process S000 ospid (2836_85052)
Instance terminated by PMON, pid = 55932

I'm a newbie in Oracle. I would really appreciate any direction and especially step by step instructions to fix this.

I have run the commands:

recover database;
alter database open;

but it still has the same error when I run:

select instance_name from v$instance;


texts:
I've inherited multiple servers, each containing one database and one instance of SQL Server 2008 R2 SP2. Each server is set to use 12GB min/max memory and has Lock Pages in Memory enabled. The servers have a total of 16GB memory, so 4GB is left for the OS and anything else.

My question is - Is this best practice? Should the servers be set to the same value for min/max memory or will this cause performance issues? I'm investigating a sudden increase in CPU usage across multiple servers and want to rule this out if possible.


texts: Read on MDF file failed with reason 15105

texts:
The log shipping backup job for one of our databases is failing with the error message:

  Read on "D:\Data\Filename.mdf" failed: 1(failed to retrieve text for this error. Reason: 15105)

Attempting to run the job out of schedule (to see if this gleans any additional data) results in a failure with the same message.

What is the cause of this message and how would I resolve it?

Select @@VERSION:

  Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) Mar 19 2015
  12:32:14 Copyright (c) Microsoft Corporation Standard Edition (64-bit)
  on Windows NT 6.1  (Build 7601: Service Pack 1)

DB Compatibility Level: SQL Server 2008 (100)

Recovery model: Bulk-logged

Results of DBCC CHECKDB:

DBCC CHECKDB ('DatabaseName') WITH PHYSICAL_ONLY reports:

DBCC results for 'DatabaseName'.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112497) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112498) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112499) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:2112500) with latch type SH. 1(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 4 consistency errors not associated with any single object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112497) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112498) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112499) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:2112500) allocated to object ID 557349150, index ID 1, partition ID 72057601138884608, alloc unit ID 72057601177681920 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'schemaname.tablename' (object ID 557349150).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'DatabaseName'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DatabaseName).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


texts: Auto Increment in MS SQL letter and number

texts:
I have one question, how to achieve auto increment in MS SQL. the one way i know so far is to perfix in the table eg

CREATE TABLE Customer (
    CUSId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
    ,CUSKey AS 'Cus' + RIGHT('000' + CONVERT(VARCHAR(5), CUSId), 6) PERSISTED
    ,CusName VARCHAR(50)
    ,mobileno INT
    ,Gender VARCHAR(10)
    )

i will get something like

Cus0001
Cus0002

what after Cus9999 ?

some how can i achive auto increment like this

CUSAB000001
CUSAB000002
CUSAB000003
CUSAB000004
CUSAB000005
CUSAB000006
CUSAB000007
CUSAB000008
CUSAB999999
CUSCD000001
CUSYZ999999.


texts: How do I load data from a single table to multiple tables & keep referential integrity

texts:
Using SQL Server 2008R2.

I have a single table with a bunch of columns.  I have built a new database with multiple tables to copy the data too.  How do I copy the data, and still keep the relationships?

For example, I have a table with columns such as: StudentFirstName, StudentLastName, TeacherName, Class1, class2, class3, class4, AdvisorName, etc.

I want to copy that to multiple tables, such as:

Student - StudentId, FirstName, LastName

Teacher - TeacherId, FirstName, LastName

StudentTeacher - Id, StudentId, TeacherId

etc.

When I insert the data into the Student and Teacher tables, I need to grab the ID fields from them (StudentId, TeacherId) and load those into the the StudentTeacher table, in the correct order.

Is this best done with SSIS? If so, will I need a bunch of data flows because of the "one source, one destination" thing?

If I do it with a script, I'm thinking the OUTPUT clause might help, but I have not yet figured out how to make that work.

I have tried this:

INSERT INTO Student (Name)
OUTPUT Inserted.StudId INTO dbo.StudentTeacher (StudentId)
VALUES ('Jimmy'), ('Bobby'), ('Nanacy'), ('Suzie')

INSERT INTO Teacher(Name)
OUTPUT Inserted.TeachId INTO dbo.StudentTeacher (TeacherId)
VALUES ('Mr Jim'), ('Mr Bob'), ('Ms Nancy'), ('Ms Suzie')

But that doesn't load at the same time, so I get multiple rows, one with a studentid, one with a teacherid.  How do I get them to load at the same time?

Just looking for some direction


texts: Returning a result set with multiple rows based on max date

texts:
I have a child table that is something like this:

[Cust Date Table]

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 |
|           1 | 2012-03-31 |   50.00 |
|           2 | 2012-04-30 |    0.00 |
|           2 | 2012-03-31 |   10.00 |
|           3 | 2012-03-31 |   60.00 |
|           3 | 2012-02-29 |   10.00 |

I would like to be able to get a result set like this - one record for each client with the latest date:

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           1 | 2012-04-30 |   20.00 |
|           2 | 2012-04-30 |    0.00 |
|           3 | 2012-03-31 |   60.00 |

I know that I can do this for each individual "Customer ID" with the following SQL (SQL Server syntax):

select top 1  [Some Date], [Customer ID], [Balance]
from [Cust Date Table]
where [Customer ID] = 2
order by [Some Date] desc

| Customer ID | Some Date  | Balance |
+-------------+------------+---------+
|           2 | 2012-04-30 |    0.00 |

But I'm not sure how to get all three of the records I want.  I'm not sure if this is a situation that calls for a sub-query or something else.

Please note that the max date can be different for any given [Customer ID], (in this example, customer 3's maximum date is 2012-03-31 whereas the other records have a max date of 2012-04-30).
I have tried

select [Customer ID], MAX([Some Date]) AS [Latest Date], Balance
from [Cust Date Table]
group by [Customer ID], Balance;

The problem is this doesn't return just the one row for each customer - it returns multiple rows.


texts: Need help with my MySQL PROCEDURE #1193 - Unknown system variable

texts:
I've this simple table:

CREATE TABLE IF NOT EXISTS `testing`
(
    `testing_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `meter` INT NOT NULL,
    `price` DECIMAL(12,2) NOT NULL,
    `price_per_meter` DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (`testing_ID`)
)

And I've written this MySQL PROCEDURE:

DELIMITER $$
CREATE PROCEDURE `price_per_meter_calc` (price DECIMAL(12,2))
BEGIN
    SET price_per_meter = price/meter;
END
$$
DELIMITER ;

but I've got an error #1193 - Unknown system variable 'price_per_meter'. I believe it's pretty straightforward code. What I want is simply MySQL to automatically calculate price_per_meter given the user will only insert price and meter values.


texts: I cannot grasp the definition of serializable

texts:
Everybody says that

  "Serializable" means that for any given set of concurrent transactions, there is a serial ordering of those transactions which produces the same result.

They also give an example

  suppose you have an attribute whose initial value is 10; and you have two transactions - one which doubles the value, the other which adds seven. Two different clients initiate the two transactions. Under isolated semantics, the only acceptable values for this attribute are 27 (the result of doubling first then adding 7) and 34 (the result of adding 7 first and then doubling). Any other value (such as 20 or 17) would be an error.

Which "same result" do we have in the example? Which reordering can we have to produce the same result?


texts: Import .sql file into multiple databases

texts:
How to import .sql file into multiple databases at one shot.

I have tried this:

mysql -uroot -p -all-databases < test.sql

But it asks for database name. Whatever there in test.sql file should reflect in all the databases/Schemas.


texts:
With my data I am only wanting to show the SUM(ISNULL(dolamt,0)) for the MIN(woID) - however my query shows the SUM(ISNULL(dolamt,0)) for every row.  Below is sample DDL -

Declare @Info Table
(
    customer varchar(500)
    ,dolamt decimal(10,2)
    ,pddate date
)

Declare @Custs Table
(
    customer varchar(500)
    ,active varchar(10)
)

Declare @WorkOrders Table
(
    customer varchar(500)
    ,woID int
    ,sitename varchar(250)
    ,companycost decimal(10,2)
)

Insert Into @WorkOrders (customer, woID, sitename, companycost) Values
('Customer A', 1, 'Site 1', '10.23'), ('Customer A', 2, 'Site 1', '23.33'),
('Customer A', 3, 'Site 1', '44.12'), ('Customer B', 4, 'Site 2', '44.33'),
('Customer B', 5, 'Site 2', '12.12'), ('Customer C', 6, 'Site3', '22.11'),
('Customer D', 7, 'Site 4', '22.11'), ('Customer D', 8, 'Site 5', '100.12'),
('Customer D', 9, 'Site 6', '23.14')

INSERT INTO @Custs (customer, active) Values
('Customer A', 'Yes'), ('Customer B', 'Yes')
,('Customer C', 'Yes'), ('Customer D', 'Yes')
,('Customer E', 'No'), ('Customer F', 'No')

Insert Into @Info (customer, dolamt, pddate) Values
('Customer A',  '3286.12',  '7/17/2016')
,('Customer A', '9665', '11/25/2016')
,('Customer A', '154.13',   '6/29/2016')
,('Customer B', '73575.37', '12/30/2016')
,('Customer A', '134491.15',    '9/30/2016')
,('Customer A', '112.5',    '11/30/2016')
,('Customer C', '1140.9',   '8/31/2016')
,('Customer C', '5512', '2/8/2016')
,('Customer C', '82323',    '7/31/2016')
,('Customer C', '450',  '10/23/2016')
,('Customer D', '6050.91',  '3/13/2016')
,('Customer B', '520.84',   '8/19/2016')
,('Customer D', '1554.35',  '8/31/2016')

Select
cs.Customer
,[Work Order ID] = COALESCE(wo.woID,0)
,[Company Cost] = SUM(ISNULL(wo.companycost,0))
,[Total Amt] = COALESCE((Select Top 1 SUM(ISNULL(dolamt,0)) FROM @Info nf WHERE nf.Customer = wo.Customer),0)
FROM @WorkOrders wo
RIGHT JOIN @Custs cs
ON cs.Customer = wo.customer
GROUP BY cs.Customer, wo.woID, wo.Customer
ORDER BY cs.Customer ASC

Which produces this result set

I want this to be returned result set (only update MIN(woID))

What do I need to change in my query so that I get the desired result set returned?


texts:
I am trying to add constraints and I think i have an error in the code as its saying i cant add mutlple constrints. What is best way to add mutlple constraints

I am using MSQL Server 2008 r2

CREATE TABLE [erp].[table](
    [SysRevID] [timestamp] NOT NULL,
    [SysRowID] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (CONVERT([uniqueidentifier],CONVERT([binary](10),newid(),(0))+CONVERT([binary](6),getutcdate(),(0)),(0))),
    [Company] [nvarchar] (8) NOT NULL DEFAULT (''),
    --THE ABOVE ARE DEFAULT FIELDS.  LEAVE THEM ALONE.  ADD FIELDS BELOW
    [AffilationNum] [int] NOT NULL DEFAULT ((0)),
    [VendorNum] [int] NOT NULL ,
    [CreationClass]   [nvarchar] (10),
    FOREIGN KEY ( VendorNum ) REFERENCES Vendor (VendorNum),
    PRIMARY KEY (AffilationNum),
CONSTRAINT [idxEvAffiliation] PRIMARY KEY CLUSTERED
(
    [Company] ASC,
    [AffilationNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

go

 CREATE NONCLUSTERED INDEX [idxAffilationInterestedParty] ON [erp].[EvAffiliation]
 (
 [Company] ASC,
 [VendorNum] ASC
 )

GO

ALTER TABLE [erp].[EvAffiliation] SET (LOCK_ESCALATION = DISABLE)
GO

Edit
 I am not sure how to create an additional index i dont want the second one to be on primary key its just an additional non clusted index i want for the second one.

This is the errror i am getting i am trying to create one pimary index and one non clustered index

  Msg 8110, Level 16, State 0, Line 2
  Cannot add multiple PRIMARY KEY constraints to table 'erp.EvAffiliation'.


texts: PUBLIC privilege potential security threat - Oracle database

texts:
Recently, audit findings at my site found that PUBLIC being by default assigned to all the schemas that are created is a big risk since when a new user is created and even if we were to assign only CONNECT and RESOURCE roles, the schema can still insert/update/delete the tables that of even system schemas' objects.

Even though while querying dba_sys_privs, there are no system privileges assigned to that grantee 'PUBLIC' whereas dba_tab_privs shows otherwise, which in itself is a risk. But, Oracle says in a metalink that, revoking PUBLIC has its own ramifications.

I did try to insert a row into one of the SYSTEM tables using SCOTT schema, and it did go through.

How much of a threat does revoking PUBLIC from normal schemas affect the functioning? How big is the risk if this is not taken care of? or should I carefully revoke them at granular level for application schemas other than default schemas?

What is the very use of PUBLIC being assigned by default if this is a security flaw?


texts: I need to make a trigger about who and when created a view. I have some groundwork about it.. So, when some user is running a script for create view, trigger is crushing because this user doesn't have a permission to insert.  When I use trigger with EXECUTE AS OWNER, there is a problem with it. Problem - OWNER is not a valid parameter for EXECUTE AS in the context of server and database level triggers.


texts: How should I model an "either/or" relationship?

texts:
Say I have an entity named Software and two sub-types FreeSoftware and NonFreeSoftware. The NonFreeSoftware entity has attributes such as purchase date, vendor, etc.. The FreeSoftware entity has attributes such as license, source code url, etc.

So if I want to model another entity, OperatingSystem, how should I do it? There is an "is a" relationship to Software but an "either/or" relationship to FreeSoftware and NonFreeSoftware.

I think I'm missing something in the way I'm analyzing this hierarchy.


texts: How can I find out which statistics are evaluated by the optimizer?

texts:
My enterprise has a farm of many nearly-identical, isolated production environments.  Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas.  Ideally each database would have the same schema.  Enhancements to the schema would be managed centrally, in a model schema.  The schema changes would be coordinated with rollout of new versions of the client software.

Unfortunately, as time has passed, sysadmins have added ad-hoc statistics entities to some of these databases, and as a result, we see inconsistent performance among our production systems.

We would like to either incorporate the ad-hoc statistics into the model schema, or rid them from the production servers.  However we cannot confidently do either unless we know what effect they are having on query performance.

It seems that there should be a way to see what the Query Optimizer is doing, in terms of accessing statistics entities as it evaluates different execution plans.  Is this information available anywhere?

Is there a SQLTrace option which will report it?

Something like sys.dm_db_index_usage_stats would be helpful.  Is there a DMV like this which counts statistics usage instead of index usage?


texts:
I just noticed that PostgreSQL allows repeating foreign key (and possibly other constraint) names across the database as long as they are on a different table.

So if the parent table is Foo and it has two child tables BarX and BarY, the foreign keys on both tables can be named FK_fooid.

This, IMO, is a terrible design because

select * from information_schema.referential_constraints
where constraint_name = 'FK_fooid'

will return two identical rows with no ability to differentiate which one corresponds to which table/key.

Is there a way to disable repetition of constraint names across a database in Postgres and to enforce that each constraint has a unique name?


texts: Referencing multiple tables from a single table and store additional data

texts:
I have a database where I want to store lap time information from a racing game. I have tables for car, track, game, tuning and upgrade data and want to put all these together to create a single dataset, so that I can e.g. display all times for a track, or detailed information about a lap time on a specific track in a specific car.

My schema is:

Game
-------------
GameID (PK), GameName

Cars
-------------
CarID (PK), Manufacturer, Model, GameID (FK)

Tracks
-------------
TrackID (PK), TrackName

Parts
------------
PartID (PK), PartName, GameID (FK)

Tuning
-----------
TuneID (PK), ElementName, GameID (FK)

TrackGame (-- A Track can appear in multiple Games)
-----------
TrackID (FK), GameID (FK)

To achieve that, I created a table TimeSet where I want to put everything together, but since a car can have multiple Parts installed, and many different Tuning values, I'm having a hard time integrating this properly into a TimeSet. I though of splitting the data over three tables (see below), but I don't think this looks like a good solution, because inserting data cleanly will probably be difficult to do.

TimeSet
-----------
TimeSetID (PK), GameID (FK), TrackID (FK), CarID (FK), (TimeSetID, TuneID) (FK), (TimeSetID,PartID) (FK), time

Time_Tune
-----------
(TimeSetID (FK), TuneID (FK)) (PK), value

Time_Part
-----------
(TimeSetID (FK), PartID (FK)) (PK)

What is the proper way to do this? I'm working with SQLite 3 on Android.


texts: Most efficient data type for a column storing numeric values between 0 and 100

texts:
On my MySQL DB I was thinking of using TINYINT (Unsigned)?

Would you use Byte on SQL Server?


texts: Mysql - How can I replication structure, not data

texts:
I have master with 2 slaves (4 but other 2 not relevant).  One slave is full replication, ability to switch to be master if needed.  Second slave was designed to replicate certain tables and leave out key data.  Purpose of that slave is to use it for refreshing a development database at any time so developers have what is in production to work on, but without customer sensitive data.  It is using a number of replicate-ignore-table.

For whatever reason, I usually think of that as ignoring replication of DATA to the table, not table structure changes.  I'm obviously incorrect.  Structure changes are also not replicated as per visual evidence and "not to replicate any statement that updates the specified table".  (Maybe I'm too used to thinking "update" is table data, not table_schema).

Other than removing replicate-ignore-table lines and then running an event very often to truncate a list of tables, what other way might there be to have the table structure changes (including trigger changes to a table) be replicated but to NOT replicate the data being put in the database?  Triggers for each table isn't a solution as this db needs to be as much a copy of what is in master as possible with regards structure and various customer configurations (which changes frequently).

I have everything automated from doing daily auto sanitize and dump, transfer to file storage.  For refresh, just run a script to fetch latest dump, do backup of existing developer db, load prod version and all is almost good (need to update some unreleased changes to the db).  And then now realize replicate-ignore-table ignores structure too.

Suggestions?


texts:
I want to shrink the transaction log file of several databases using a maintenance plan.
Those databases are mirrored, therefore the shrink task cannot be executed on the MIRROR databases, but the maintenance plan has to be configured on both servers.
I'm using SQL Server 2012 Standard (SP1) on our development system but the code needs to work on 2008 R2 too.

I tried to use the following script, which handles one database called DR, within an Execute T-SQL Statement Task:

IF EXISTS (SELECT NULL FROM sys.databases WHERE [name] = 'DR' AND [state_desc] = 'ONLINE')
BEGIN
    PRINT 'test';
    USE DR;
    DBCC SHRINKFILE (DR_log, 2048);
END
GO

This works fine on the PRINCIPLE, however it results in the following error message on the MIRROR:

Msg 954, Level 14, State 1, Line 4
The database "DR" cannot be opened. It is acting as a mirror database.

It seems the IF block is ignored by the USE command.
The Print is not executed if USE is commented out. The Shrink Database maintenance task which is available in SSMS provides an option to ignore databases where the state is not online. However, the log file does not reveal how this is accomplished. This Task cannot be used because it also shrinks data files, not only log files.

How does one make sure the USE is only executed if the database is in Online state? Another option I can think of would be the use of dynamic SQL, which I'd like to avoid.

Note: This task will not be executed frequently but rather subsequent to an exceptionally big transaction which caused the log file to grow beyond it's usual size.

Solution: we used dynamic sql to workaround this issue, see also the answer provided by @Aaron Bertrand


texts:
I'm using SharePoint 2013 with SQL Server 2012 as persistence layer. I need to encrypt the data stored in the database. I stumbled upon the SQL Server feature Transparent Data Encryption (TDE).

What will happen to the files (Office Documents) from the document libraries, when TDE is used? Are the files encrypted also or will only tables be encrypted?


texts: How to get a group where the count is zero?

texts:
I'll try to make a graph from the data from my SQL server database. I'll have all streets with the count of the users who are living in this street even the count is zero.
For this I've tried this query:
Create table Streets(
  ID int IDENTITY  primary key,
  Name varchar(100)
);

create table users(
  ID int IDENTITY  primary key,
  Username varchar(100),
  StreetID int references Streets(id)
);

insert into streets values ('1st street'), ('2nd street'), ('3rd street'),
                           ('4th street'), ('5th street');
insert into users values ('Pol', 1), ('Doortje', 1), ('Marc', 2), ('Bieke', 2),
                         ('Paulien', 2), ('Fernand', 2), ('Pascal', 2), ('Boma', 3),
                         ('Goedele', 3), ('Xavier', 4);

select s.name as street, count(s.name) as count
from users u inner join streets s on u.streetid = s.id
group by s.name

And it gives me this output:

ID
street
count

1
1st street
2

2
2nd street
5

3
3rd street
2

4
4th street
1

The problem is that the 5th street, where no user lives, doesn't appear on the result. Could I do this with SQL server? Here you've got a fiddle
Update: If I do right join, I've got this result:

ID
street
count

1
1st street
2

2
2nd street
5

3
3rd street
2

4
4th street
1

5
5th street
1

See this fiddle.


texts: Failed to populate indices of table table_name

texts:
I need to design my PostgreSQL database and I use SQL Power Architect to do it.

But after I import it to SQL Power Architect and drag the table to workspace, I got error  message : Failed to populate indices of table <table_name>, I explore the error, and got Caused by : org.postgresql.util.PSQLException: ERROR: column i.indproc does not exist.

Actually I never use column i.indproc in my database, and this error appear when I drag each tables to workspace (for the first time, after I delete the table from workspace and drag it again, the error not appear)

Here the message

  java.lang.RuntimeException: ca.sqlpower.sqlobject.SQLObjectException:
  Failed to populate indices of table login     at
  ca.sqlpower.sqlobject.SQLObject$1.run(SQLObject.java:193)     at
  java.awt.event.InvocationEvent.dispatch(Unknown Source)   at
  java.awt.EventQueue.dispatchEventImpl(Unknown Source)     at
  java.awt.EventQueue.access$500(Unknown Source)    at
  java.awt.EventQueue$3.run(Unknown Source)     at
  java.awt.EventQueue$3.run(Unknown Source)     at
  java.security.AccessController.doPrivileged(Native Method)    at
  java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown
  Source)   at java.awt.EventQueue.dispatchEvent(Unknown Source)    at
  java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown
  Source)   at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)  at
  java.awt.EventDispatchThread.run(Unknown Source) Caused by:
  ca.sqlpower.sqlobject.SQLObjectException: Failed to populate indices
  of table login    at
  ca.sqlpower.sqlobject.SQLTable.populateIndices(SQLTable.java:550)     at
  ca.sqlpower.sqlobject.SQLTable.populateColumns(SQLTable.java:369)     at
  ca.sqlpower.sqlobject.SQLTable.populateImpl(SQLTable.java:1335)   at
  ca.sqlpower.sqlobject.SQLObject.populate(SQLObject.java:186)  at
  ca.sqlpower.sqlobject.SQLTable.getChildren(SQLTable.java:1963)    at
  ca.sqlpower.sqlobject.SQLObject.getChildren(SQLObject.java:285)   at
  ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.ensurePopulated(PlayPen.java:1599)
    at
  ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.doStuff(PlayPen.java:1574)
    at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:104)    at
  java.lang.Thread.run(Unknown Source) Caused by:
  org.postgresql.util.PSQLException: ERROR: column i.indproc does not
  exist     at
  org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    at
  org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    at
  org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    at
  org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    at
  org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
    at
  org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:236)     at
  org.postgresql.jdbc2.AbstractJdbc2DatabaseMetaData.getIndexInfo(AbstractJdbc2DatabaseMetaData.java:3735)
    at
  ca.sqlpower.sql.jdbcwrapper.DatabaseMetaDataDecorator.getIndexInfo(DatabaseMetaDataDecorator.java:426)
    at
  ca.sqlpower.sql.jdbcwrapper.PostgresDatabaseMetaDataDecorator.getIndexInfo(PostgresDatabaseMetaDataDecorator.java:173)
    at
  ca.sqlpower.sqlobject.SQLIndex.fetchIndicesForTableAndUpdatePK(SQLIndex.java:827)
    at ca.sqlpower.sqlobject.SQLTable.populateIndices(SQLTable.java:538)
    ... 9 more

Please help me to solve this problem


texts:
I've been tasked to prepare Azure SQL database deployment plan and I am struggling with it...

I've been given following information:

2 million transactions will arrive to database daily throughout the day (~278 tr/sec on average).
Transactions will arrive from 1000 different locations (onprem SQL Servers generating 2000 transactions daily each)
All transactions will have a fixed size of 12KB. Therefore I am expecting to get about about 22-23 GB of data daily.
All data will be regularly aggregated and accessed from 100 different locations for BI purposes.
Updated data (configurations) will be sent back to aforementioned 1000 locations

I've tried to use Azure SQL DTU calculator, but it requires to run PowerShell script on existing server and I don't have one... This a new system, that is currently in development and it is planned to run on Azure SQL.

I also tried to find the way to convert transactions to DTU or Vcore, but failed to find any.

Can anyone please help me with that? Or point me in to right direction to choose correct plan to suffice my needs based on info provided.

Thank you in advance.
Mike


texts: Cannot change logical file name: The logical file name "PROD1" is already in use. Choose a different name

texts:
I made an error in the naming of a physical data file. So I dropped the file from within SQL Server, and wanted to use the same logical file name again. But now SQL Server 2014 refused. So for the moment I created it as PROD0, and tried a rename afterwards that.

ALTER DATABASE [PROD] MODIFY FILE (NAME=N'PROD0', NEWNAME=N'PROD1')
Msg 1828, Level 16, State 3, Line 3
The logical file name "PROD1" is already in use. Choose a different name.

If I select:

select name,physical_name
from sys.database_files
order by physical_name

this logical filename does not appear:

name        physical_name
PROD        D:\MSSQLSERVER\Data\PROD.mdf
PROD0       D:\MSSQLSERVER\Data\PROD0.ndf
PROD2       D:\MSSQLSERVER\Data\PROD2.ndf
PROD3       D:\MSSQLSERVER\Data\PROD3.ndf
PROD4       D:\MSSQLSERVER\Data\PROD4.ndf
PROD5       D:\MSSQLSERVER\Data\PROD5.ndf
PROD6       D:\MSSQLSERVER\Data\PROD6.ndf
PROD7       D:\MSSQLSERVER\Data\PROD7.ndf
PROD_log    E:\MSSQLSERVER\Data\PROD_log.ldf

So even if I would like to drop it, I can't, because it does not exist.

If it might be important: this is part of an Availability Group (2 nodes only). I made the changes on the primary of course. The changes were correctly propagated to the secondary, so also there the file 'PROD1' is not there.

How can I succeed in renaming PROD0 to PROD1?

Edit after the answer of SQLBoy: Sorry, that did not work; same error. The logical Filename PROD1 seems to be hidden somewhere deep in the datadictionary.


texts: Replication between SQL Server 2005 - 2005

texts:
I'm hoping to get some help with a SQL Server 2005 replication issue.  In short, in replication monitor, (On the Publisher), on the 'Distributor to Subscriber History' tab, I see two lines under 'Sessions of the Distribution Agent'.  One is running fine, while the 2nd one is showing the action message 'The process is running and is waiting for a response from the server'.  I now have 338694 undelivered commands 'supposably' according to this query:

Select sum(UndelivCmdsInDistDB) FROM [distribution].[dbo].[MSdistribution_status];

But - I just executed counts on one of the replicated tables on both servers, and the are spot on.  Therefore, I do believe the agent that is hung simply needs to be killed.

Tammy

P.S. Replication stopped at 10pm last night ; I believe that when I paused and restarted the SQL Server agent this morning on the Subscriber, the 2nd distribution agent session started and picked up with delivering the commands to the subscriber.

P.S.S - I have also been working to try to create some sort of alert for this situation, any thoughts on that?


texts: How to use mysql live update on a mysql table between localhost and remote server?

texts:
I have mysql table created/modifying in localhost, and I need to update those in my remote database.

I have gone through lot of post regarding this scenario and I found out that I need to use mysql replication for overcome this situation.I went through some posts about mysql replication but I couldn't understand how would I do this.

Is this the method I need to use? If yes, please mention the simplest way on how to use this method?

  NOTE:- my localhost contains Inventory stock system that I have
  coded.I just need to link or maintain the finish good list on my
  remote server as product list. Any update of finish good must update in
  remote server,this is what I need to do.

Thank a lot.


texts: Why can every PostgreSQL user see all databases and their tables?

texts:
I'm running a PostgreSQL database server, version 10 on Linux, for multiple users. Today I found out by surprise that any user can see all other users' databases and their tables, columns and other properties, but not the data. I'm wondering why this is the case? Shouldn't a user be restricted to their own databases? Why is this access granted at all and how can I remove it?

The users were created with this command: createuser "user1"

The databases were created with this command: createdb -O "user1" "user1_db1"

I've read something about the CONNECT privilege in the Postgres documentation but I couldn't find out how to list all the privleges that a user currently has. An article suggests revoking all privileges and granting CONNECT only on the user's database. I don't want to do that before I know what other privileges the user possibly has. Also, is this required for each new database created for the user? The documentation doesn't seem to cover this topic at all.


texts: Is there a way to see the code that creates a view in SQL server management studio?

texts:
I want to see the underlying code for how a view is created in SQL server management studio. Is there a way to do this?

I want to see any date filters, etc, applied to this view in hard code.


texts: Difference Between SELECT and select in oracle sql

texts:
The algorithm used to find SQL in the library cache is based on the
  ASCII values of the characters that make up the statement. Means The
  SELECT instead of select statement will be parsed again in library
  cache.

  But Some where i read that the oracle treat's lowercase select as
  upper SELECT automaticaly in a statement

Which on is true above those two...


texts: Is there a way to create tablespace in MySQL RDS

texts: I'm using AWS RDS MySQL instance and using it. Now I want to create table space and assign table space to tables. Is it possible in MySQL RDS


texts: database structure - complicated requirements

texts:
I have a project to build a website but it's complicated and I'm having trouble figuring out what the best way to build the database would be to handle these particular requirements.

The site is for a local builders and farmers (and anyone else who uses heavy equipment) to rent their machinery amongst themselves. Users should be able to sign up and list an item of equipment which is then searchable and bookable by other users of the site.

So a builder might sign-up and upload a listing for his concrete mixer. Then another user can search for concrete mixers to hire between 2 dates and place a booking for the mixer through the site.

So far so good.

Problem is that the builder should be able to set a default per-day rate but they should also be able to say that through-out the month of July, or on the last two weekends in August the mixers default daily rate is different. So basically everyday could end up having a different rate and I'm having trouble figuring out what is the most efficient way to structuring the database and how to calculate the total costs of renting for several days if there's potentially a different rate every day.

At the moment I'm imaging having to loop through a 365 sized array but that can't be right. I'm a bit new to this so I'm probably just confused.


texts: Should I use MyISAM over InnoDB to save disk space?

texts:
I have a table that consists of an int primary key and text column and serving primarily in a "read" role. There are currently only about 40,000 rows, but in time, I expect there to be an additional 500,000 rows per year. This is on a system with perhaps 10 active users per day, so very low-impact.
With MyISAM, the table is less than 4MB. With InnoDB, it is over 7MB.
If disk space is a concern, is MyISAM really so untrustworthy that I should use InnoDB over MyISAM?


texts: SSIS: Slowly changing dimension with lists (one-to-many relationship)

texts:
Slowly changing dimension (specifically type 2) is a brilliant concept for being able to keep historical periodic (e.g. daily) data without the storage overhead of taking an entirely new snapshot of the data every time it is updated. However, all examples I have seen so far assume my data for every element I am modelling is contained in one single row. For example, a person, and their name, age, and address. Now what happens when my source data has a multi-valued dimension (or in other words, a list)? Let's say I want to keep track of the number of cars someone owns over time. Expressed as tables, my source data would look like:

                         Car
Person                   ---
------                   CarID
PersonID <-------------> PersonID
First Name               Year
Last Name                Make
Age                      Model
Street Address           Color
City
State

Yesterday, a person had 3 cars. Today, they have four. Maybe in the future, they have 2, but 2 that are completely different from the original 4 cars. How can I use SCD2 (Historical) to track this one-to-many relationship?

I can't find a lot online about how to do this, which probably means I am not using the correct search terms because I am positive others have solved this problem. Thinking of my own solution, I could imagine my Target SCD data is modeled as such:

PersonScd
---------               CarScd
PersonBK                ------
CarListSK <-----------> CarListSK
First Name              CarID
Last Name               Year
Age                     Make
Street Address          Model
City                    Color
State
EffectiveDate
ExpirationDate

This is a hybrid approach where if any item in a list changes from one time to the next, the list is completely rewritten with all the new details, and each list has its own ListSK. Therefore, in this example design:

Yesterday a person had 3 cars. This person has one row in the PersonScd table with an ExpirationDate of 9999-12-31. The CarScd table has three rows, each with the same CarListSK. The other details of each row in the CarScd table match those of the source Car table for this person. The person's CarListSK column holds the value of CarListSK that all three of the cars have in the CarScd table.
Today, this person now owns a fourth car. This means yesterday's row for this person expires (the ExpirationDate is now set at yesterday's date), and a new row is written to the PersonSK table for that person - the EffectiveDate is set to today and the ExpirationDate is 9999-12-31. Now, 4 new entries are written to the CarScd table, and all of these entries share a new CarListSK. Aside from the effective and expiration dates, the only difference between this person's row yesterday and today is the CarListSK column.
Now, as long as I join the PersonScd table to the CarScd table using the CarListSK column, I can know what cars a person owned on a certain date.
If any other attribute about this person changes in the future, such as his/her address, the new row written will retain the same CarListSK with 4 corresponding entries since his/her cars did not change.
If in the future the person now owns only 2 cars (whether or not they were the same as the previous 4), 2 totally new rows will be written in the CarScd table with a new common CarListSK, which the new PersonScd row will have for that person.

Assuming this is a feasible design, how do I build this behavior in SSIS? SCD in SSIS compares one row at a time, not lists at a time. I imagine I'd have to just use a script component.

If this design is flawed, what is the correct way to use Slowly Changing Dimension Type 2 with one-to-many lists?


texts:
I have a plpgsql function that returns a table. One of its columns is dynamically generated based on the parameters passed to the function. Is there a possibility to have the user have the ability to update the non-dynamically generated columns of that returned table, like with an instead of update trigger with a view?

Here's a simplified version:
In reality, the function is a lot more complex, and the value of the calculated column for the currently processing row is dependent on the previous rows, which is why I have to use a plpgsql function.

create table test(id integer, startdate date);

CREATE FUNCTION test2 (IN compare_date date)
    RETURNS TABLE(id integer, startdate date, is_before boolean)
    LANGUAGE plpgsql
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT id, startdate FROM test LOOP
        IF rec.startdate < compare_date THEN
            test2.is_before := true;
        ELSE
            test2.is_before := false;
        END
        test2.id := rec.id;
        test2.startdate := rec.startdate;
        return next;
    END LOOP;
END;

The user just accesses the function, I want him to be able to update the columns in the original table. With a view, this could be done using a trigger, but I can't have dynamically generated columns in a view, can I?


texts: Syntax error with concat in trigger near signal statement

texts:
Please help me with syntax for MySQL 5.6.

For some reason I get the error:

ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near '('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is nul'

When trying to run this SQL code:

create table table_social (
        sid varchar(250) primary key,
        auth char(32) not null,
        social integer,               -- to be checked by triggers
        female boolean,
        given varchar(250) not null,
        family varchar(250) null,
        photo varchar(1000) null,     -- to be checked by triggers
        place varchar(250) null,
        ip varchar(250) not null,
        uid integer not null references
            table_users(uid) on delete cascade
);

delimiter //

create trigger social_insert_trigger before insert on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                signal sqlstate '45000'
                set message_text = concat('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                signal sqlstate '45000'
                set message_text = concat('WRONG PHOTO: ', new.photo);
        end if;
end
//

create trigger social_update_trigger before update on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                signal sqlstate '45000'
                set message_text = concat('WRONG SOCIAL: ', new.social);
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                signal sqlstate '45000'
                set message_text = concat('WRONG PHOTO: ', new.photo);
        end if;
end
//

I tried removing concat, replacing single quotes by double quotes, splitting signal line in two (as above) and back - and still can not find out, what is wrong.


texts:
I have been tasked to migrate a database from PostgreSQL 9 to SQL Server 2008 R2, and I have found some issues I need to address. I have quite limited experiences with PostgreSQL I'm afraid.

Most keys are BigInt. In PostgreSQL a function is called to get the next ID and this is done in advance in the code for whatever reason.  Can this be done in the same manner in SQL Server or am I forced to create an key index table to keep track of the current ID. IDENTITY(1,1) will afaik deliver a new ID on the insert.
There are some stored procedures that returns NEW as a TRIGGER. What would be the equivalent in the SQL Server language?
What impact has the difference in triggers given that PostgreSQL triggers are row based?


texts: PostgreSQL - pgAdmin error creating a server

texts:
I have the latest versions of PostgreSQL and its GUI-frontend, pgAdmin, installed.
When I try to register a new server a fatal error appears when pgAdmin tries to connect to the newly created server - password authentication failed for user "postgres".
I'm using a non-default port which was properly updated in the postgresql.conf file so I don't believe that is causing problems (restarted postgres too, as it so politely asks you to).
postgres processes are running properly:

Here you can examine the configuration settings I am using to register the server:

I've tried a few passwords, including my root password but none of them appear to be working. Maybe the user has to be created separately?

This is the first time I'm encountering anything besides SQL Server or Access so please bear with me if the question and its possible solution seem far too trivial.
I'm running Linux Mint 15, 32-bit.
I will provided additional information, if needed of course.


texts: Azure sql database grant select on certain tables

texts:
I have created a user in a Azure sql database in SSMS. Now trying to give select permission to certain tables in a db to this user. The security control options seems quite different than on premise sql server. How can I do these on a Azure db?

I have tried this on a on premise sql server and it works fine. But got error when run these on the azure sql db.

    USE [master]
    GO
    CREATE LOGIN [jnj_user] WITH PASSWORD=N'P2ssw0rd123', DEFAULT_DATABASE=[CRM], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [CRM]
    GO
    CREATE USER [jnj_user] FOR LOGIN [jnj_user]
    GO

error:

    Msg 40517, Level 16, State 1, Line 3
    Keyword or statement option 'default_database' is not supported in this version of SQL Server.
    Msg 40508, Level 16, State 1, Line 5
    USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

also this does not work on Azure db either:

    GRANT SELECT ON [dbo].[JJVCEtrialOB] TO  jnj_user

error:

    Cannot find the user 'jnj_user', because it does not exist or you do not have permission.


texts: How to alter tables safely to set row_format as Compressed or Dynamic in Mariadb Galera Cluster?

texts:
We have some tables with row_format=Compact. The biggest table is around 30Gb, so how safely i can alter tables and set the row format as Compressed for the big table and Dynamic for other three tables which are under 10GB each? It is a cluster with three nodes with mariadb v10.4.12 and Galera 4. Running DDL / alter tables and change the row format can leads to cluster blocking and maybe it took long time!

Can anyone help and guide me how i can do it? Do you think using RSU mode helps here and is it safe since we talking about changing the row format?


texts:
In a conditional UPDATE like this:

UPDATE MY_TABLE
SET my_coulmn = "my value" -- many other updates
WHERE ... -- many conditions

Do I have any guarantee that I will only update those records satisfying
my list of conditions (I mean no race conditions with concurrent updates) , whatever it is the DBMS I'm using ?


texts: is it safe to change the database page verify option from torn_page_detection to checksum?

texts:
We have been alerted to a page verify configuration error for the following databases (eIQSystem, eIQLegislation, eIQMeta and 000Company) on server SERVER01.

This monitor checks the Page Verify setting for the database mentioned above.

The Page Verify Setting discovers damaged database pages caused by disk I/O path errors.

Disk I/O path errors can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk.

The databases option has been set to TORN_PAGE_DETECTION. Microsoft recommends that the setting must change to checksum as part of best practice. https://msdn.microsoft.com/en-us/library/bb402873.aspx.

there is this discussion between checksum x page torn detection but there is no objective conclusion.

there is a good article regarding
performance comparison between checksum and page torn

Can we change the setting to checksum? is it safe? (would it affect any application)?


texts:
I am trying to emulate row level security on a webapp I am developing using MySQL.

Using this method:
Creating a database with the the required tables where the data pertaining to all the users will be stored with proper indexing of columns of the tables.

Creating mysql 'views' to specific users based on the user-id.

To achieve row level security I will also have to create mysql account for every user to and set 'grant' permissions on the views.

For the web interface a PHP based MVC framework will be used.

But, according to my research:

1] Having separate mysql account per user "make the webapp less secure".
2] Having separate mysql account per user "increases the disk I/O".

Questions:
1] How does creating mysql user per webapp user make the webapp less secure?
2] Does the disk I/O increase considerably?
3] Is there a better way to implement row-level-security in MySQL?
4] What are the pros/cons of implementing row-level-security by the above method?

Why am I looking at Row Level Security?
I need row level security because there are rows which will be shared between multiple users & have 1 or 2 owners to it. Only these owners can delete/modify them.

I am not able to add tags like 'row-level-security' 'disk-io' as my reputation points are less. If anyone else can, please do.


texts: Unable to restore indexes with mongorestore command in MongoDB

texts:
I am unable to restore indexes with mongorestore command while restoring the collection i am getting the collection but i am unable to restore the indexes with it so whats the solution to restore the indexes with collection

This is the command i am using to restore

mongorestore -h ip:portnumber -u name -p passwd -d databasename -c collectionname --authenticationDatabase admin /d/collection.Bson

my Mongodb version is 3.6


texts: How to safely populate pg_notify payload?

texts:
Data makes its way into my application with prepared statements and stored procedures, so I'm not too worried about injection. When a new user signs up, I want to send an activation email. Presently I'm doing this with a trigger and a notification. It looks something like this:

CREATE FUNCTION on_sign_up() RETURNS trigger as $$
  DECLARE
  BEGIN
    PERFORM pg_notify('sign_ups', NEW.user_id || ',' || NEW.email || ',' || NEW.activation_code);
    RETURN new;
  END;
$$ language plpgsql;

CREATE TRIGGER on_sign_up_trigger AFTER INSERT ON user_account
  FOR EACH ROW EXECUTE PROCEDURE on_sign_up();

My problem is the notification payload. If the email contains a comma, then I can't just split the string on ','. How do I escape anything and put it into the payload? I can add my own custom CSV escaping function and invoke it, but any time I think something like that is the right answer, I'm usually missing something.

It doesn't necessarily need to be CSV, I just thought that'd be the easiest.

Note: there are other solutions - e.g. strip the "prefix" and "suffix" and then treat the rest as the email. I'm more interested for this general class of sanitization/escaping from within PostgreSQL itself.

The table I'm trying to extract is as follows:

                  Table "user_account"
     Column      |  Type   |         Modifiers
-----------------+---------+----------------------------
 user_id         | integer | not null
 email           | citext  | not null
 password_hash   | text    | not null
 activated       | boolean | not null default false
 activation_code | uuid    | default uuid_generate_v4()


texts: How SELECT loaded into Memory - If the table is huge

texts:
I want to understand one basic thing in relation to databases specifically on SQL Server. SQL Server will perform any actions in-memory.
But lets say if we have a 100GB table on the Server with 2GB, if we do select * from table then how will it handle?
My assumption:
it pushes the data to in-memory, once the allocated memory gets full, then it serve that data to the client, when client receiving the data(lets say 100MB received) then the sent data will flush and read 100MB more data from the disk.


texts: How can I backup & restore a single FILEGROUP in Sql Server 2008

texts:
Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup.

Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:-

Processed 1895080 pages for database 'XWing', file 'XWing' on file 1.
Processed 4 pages for database 'XWing', file 'XWing_log' on file 1.
The database cannot be recovered because the log was not restored.
The database cannot be recovered because the log was not restored.
The roll forward start point is now at log sequence number (LSN) 221218000000010400001. Additional roll forward past LSN 221218000000010400001 is required to complete the restore sequence.
This RESTORE statement successfully performed some actions, but the database could not be brought online because one or more RESTORE steps are needed. Previous messages indicate reasons why recovery cannot occur at this point.
RESTORE DATABASE ... FILE=<name> successfully processed 1895084 pages in 69.504 seconds (213.014 MB/sec).

I used the following Sql code...

alter Database [XWing] SET SINGLE_USER With ROLLBACK IMMEDIATE

restore database [XWing] filegroup = 'PRIMARY'
FROM  DISK = N'C:\Temp\XWing Manual Full Primary Filegroup.bak'
with
    MOVE N'XWing' TO N'D:\XWing.mdf',
    MOVE N'XWing_log' TO N'L:\XWing_Log.ldf',
    replace, recovery

So I'm assuming that the DB wasn't backed up correctly?

This is the script I sed to backup the PRIMARY FILEGROUP

BACKUP DATABASE [XWing] FILEGROUP = N'PRIMARY'
TO  DISK = N'F:\Sql DB Backups\XWing Manual Full Primary Filegroup.bak' WITH NOFORMAT, INIT,
NAME = N'XWing-Full Filegroup Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'XWing' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'XWing' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''XWing'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'F:\Sql DB Backups\XWing Manual Full Primary Filegroup.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

Questions

Is that Restore syntax correct?
What about my backup syntax?

Cheers!


texts: How to migrate between schemas in bulk in SQL Server?

texts:
We currently have multiple databases but would like to combine them and, instead, separate our domain contexts using schemas.

In MS SQL Server 2008 R2, how can I relocate all the contents of one schema into another in bulk?

For example, all of the tables, views, procedures, indices, etc... that we created into the dbo schema will now live in the foo schema.

EDIT: I wanted to clarify based on AaronBertrand's great comments. This is not a multi-tenancy situation. Our situation is where internal tools plugins were developed in isolation by developers who didn't merge their tables into the tool's database.


texts: How to stop a job for the rest of the day after the relevant condition has been satisfied once?

texts:
I have a job that runs every 5 minutes to check an IF condition. If the condition is true, it will send an email, if not, it won’t do anything.

My problem is that once the IF condition is true, it will remain true for the whole day and, as the job is running after every 5 minutes, it will keep sending the email after every 5 minutes.

I need to stop the job for the whole day once the email has been sent once. Is there a way to do so?


texts: Can OPENROWSET parameters be built dynamically?

texts:
This feels like an ‘obvious “yes” ‘ kind of question, but nevertheless: would it not be possible with OPENROWSET to have sub-queries regarding the file path that import for particular procedurally-named files, such as: [Bus] + ‘-‘ + [School] + ‘-‘ + [Time (AM/MD/PM)] + ‘.xml’ ?

So for...

CREATE TABLE Route (Bus (int, 4), School (string, 4), Time (string, 2) )
INSERT INTO Route (0804,CRES,PM),(0758,CRES, MD),(1106,LHS,MD),(0804,CHMS,PM)
GO

CREATE TABLE Excel (Name (char,16))
INSERT INTO Excel (0804-CRES-PM.xml,0758-BVES-AM.xml,1106-LHS-MD.xml)
GO

UPDATE SeatingChart
FROM OPENROWSET (...’[Bus] + ‘-‘ [School] + ‘-‘ + [Route] + ‘.xml’
SET [Route]Seat = Excel.[Route]Seat
WHERE Name = [Bus] + ‘-‘ + [School] + ‘-‘ + [Time] + ‘.xml’

The file being imported from has columns for both the AM & PM seating charts in the same spreadsheet, so I’m not sure know how to about writing the generalized statement that will input into the


texts: Storing 5TB web server access log vs OLAP DB

texts:
We have over 5TB compressed web server log in a HDFS and we often analyse using Hadoop.

It is painful to run map reduce on 5TB of data and most importantly, not many developer are familiar with it.

I am thinking if we should store the data in columnar database such as Greenplum or other MySQL column store which are aimed to store the analytical data in a efficient manner but able to support raipid query, which is quite important lately.

What are base you would recommend? Anything I should consider before the move? (I will do my own test anyway)


texts:
Here is my doubt. Is it possible to apply a partition to one table depending in the partitions values of another one?

Why I am asking?
I have one table where information headers are persisted, and other table where information details are stored.
As you can imagine, the second one is much bigger than the first one. The one containing headers, has an automatic partition by date, so I guess it would be desirable to partition the one with the details in order to perform queries.
Here the example:

  HEADER_TABLE ID    DATE
  ----------------------------- 1   10/10/2010 2   10/10/2010 Partition 1 3   10/10/2010
  ----------------------------- 4   11/10/2010 5   11/10/2010 Partition 2
  -----------------------------

  DETAIL_TABLE ID  HEADER_FK
  ----------------------------- 01     1 02     1 03     2      Partition 1 04     2 05     3 06     3
  ----------------------------- 07     4 08     4      Partition 2 09     5 10     5
  -----------------------------

One way I think this could be done, is adding the date field to the details table, and to partition it in the same way than the header one, but the thing is that altering the details table in production and updating the rows (hundred millons) could be a problem.

Any idea?


texts: SQL server AlwaysOn alternate option for readintent=true

texts:
I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly"
Now my question is , if some application don't have have these newest drivers and I(DBA) want to block this application's read only connections to primary and want the readonly requests of this application always routes to a AlwaysOn secondary replica then how this can achieved without "applicationIntent= readonly" option? Does anyone have any handy script to achieve this?


texts: ERROR: Arithmetic overflow error converting float to data type numeric

texts:
I unfortunately have some bad data and need to work around it. I have a Parcel column in a spreadsheet with some parcels that have inadvertently been converted to floating point numbers. I posted a question to solve this problem Handling sql column with mixed varchar and float data types
However, now I am getting an error when I try the following code:

SELECT CAST(CAST(CAST([Parcel Number 2(max)] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
FROM XLSIMPORT WHERE [Parcel Number 2(max)] = '1.4515020001e+012'

The error I'm getting is:

Msg 8115, Level 16, State 6, Line 41
Arithmetic overflow error converting float to data type numeric.


texts: Preventing replication of alter table command to change engine

texts: I am in the process of switching a MySQL table from MyISAM to InnoDB and I do not want the slave to perform the alter table command until I execute it manually. The intention is to have a backup for a few hours in case there is an issue that I have not addressed already. Is there a way to tell the MySQL slave to not process alter table commands from the Master for a period of time?


texts:
We're redoing what is and isn't being auditing in a database my team has been put in charge of. Old audit settings were producing ~2M audit entries a day. Audit_trail parameter is set to DB.

So we wipe everything that is being auditing out with NOAUDIT commands, run the audit commands of our bare minimum, and then see how many entries are created come the next day. It's the same rate with the same ratio of specific kinds of entries (ie. hundreds of thousands of successful select statements). In this case we audited select table whenever not successful to start out, so the successful selects should = 0.

Dba_obj_audit_opts is empty and dba_priv_audit_opts is what we expected. Dba_stmt_audit_opts is what we expect, which should have produced infinitely less audit records than the previous settings. sys.audit$ perfectly mirrors dba_stmt_audit_opts.

So to test whether I misunderstood how some of the audit settings worked (ie some of the system privilege auditing), we simply wipe out all of the audit options again with NOAUDIT statements. sys.audit$ is down to 1 entry auditing PUBLIC's MANAGE ANY FILE GROUP usage, which I'm assuming is some default setting I should leave alone. Wait a few minutes, and the same ratio and kinds of entries are being created in the sys.aud$ table with the newest happening a second before I queried the sys.aud$ table.

I'm sure it's something simple, but I'm stumped as to what is telling Oracle what to audit that is superseding what the audit options are telling it to audit.


texts:
Sorry to keep bugging you guys, but SQL is still new to me. I have these tables:
Store_location, Product, Sizes, Sells, Available_in, Offers, and Currency.
The goal is to be able to run one query with multiple subqueries (a WHERE clause is required) that will return only products that are sold in ALL store locations, and nothing else. It also has to be scalable so that if any stores open or close, the code does not need to be altered. I have these ramblings to get me started, but I do not know where to go from there:
The first select statement below is what needs to be displayed when the query succeeds.
    SELECT Store_location.store_name,Product.product_name,Sizes.size_option,
    COUNT(store_location.store_id) AS store_count

JOIN Sells ON Sells.store_location_id = Store_location.store_location_id
JOIN Product ON Product.product_id = Sells.product_id

JOIN ON Available_in.product_id = Product.product_id
JOIN ON Available_in.sizes_id = Sizes.sizes_id

I tried to get the joins done to show where the tables I need to use have foreign key constraints. IF you need any additional information, I can provide it. I added a link showing the contents of all the involved tables. I know I need to embed at least one subquery in a WHERE statement, but not sure what to put in there. I know it's a lot of information to go over, and I understand if nobody has the time to help, but any guidance at all would be appreciated.

I realize it's a late request, but if anyone can help me with a way to also use EXISTS on this I'd be very appreciative.


texts: I'm seeing inconsisntecies in table data after exporting from one server and importing to another, why is this?

texts:
I'm quite new to the process of importing/exporting databases.
I'm using Maria DB, and HeidiSQL to run queries, etc.

I exported an entire database from our dev server, and loaded it into our live server. The script ran without a hitch. No errors, everything appeared to go as planned.

However, when I open two instances of HeidiSQL and compare the details of the tables side by side there are some weird looking discrepancies.

For example, in some cases tables on the newly migrated version say they have more data than the database they were exported from. How is this possible? does HeidiSQL just 'estimate' the amount of rows?

For example, according to HeidiSQL table 1 on the dev server contains 177 rows of data, with a total size of 64.0 KiB.
Where as the same table 1 on the live database contains 219 rows of data, with a total size of 64.0 KiB.

Even more weird, table 2 contains 2,254,576 rows of data, with a total of 232.9 MiB, while the same table on the live database contains 2,334,922 with a total of 224.0 MiB.
There are also tables showing less data on the live database version than on the dev database version.

Why is this?

The SQL file creates the copy of the database from scratch on the live database server, it didn't delete or overwrite an older version or anything, and there were no errors on completion.

EDIT:

If I run SELECT COUNT(*) FROM table_2 both databases return 2,326,087 rows.
So it appears to be an issue with displaying the correct count in HeidiSQL.


texts: Time Difference Between actual total time and execution time in EXPLAIN ANALYZE

texts:
I have the following output of EXPLAIN ANALYZE query on PostgreSQL 11.5

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT actor.actor_id, actor.first_name, act.num
FROM actor
  INNER JOIN (
    SELECT  actor_id, COUNT(DISTINCT film_category.category_id) as num
    FROM film_actor
      INNER JOIN film_category ON film_actor.film_id = film_category.film_id
    GROUP BY actor_id
  ) act ON act.actor_id = actor.actor_id
ORDER BY actor.actor_id ASC;

Merge Join  (cost=527.43..591.41 rows=200 width=18) (actual time=320.130..324.861 rows=200 loops=1)
  Output: actor.actor_id, actor.first_name, (count(DISTINCT film_category.category_id))
  Inner Unique: true
  Merge Cond: (actor.actor_id = film_actor.actor_id)
  Buffers: shared hit=9 read=35
  ->  Index Scan using actor_pkey on public.actor  (cost=0.14..16.16 rows=200 width=10) (actual time=77.146..77.272 rows=200 loops=1)
        Output: actor.actor_id, actor.first_name, actor.last_name, actor.last_update
        Buffers: shared hit=2 read=3
  ->  GroupAggregate  (cost=527.28..570.25 rows=200 width=10) (actual time=242.973..247.346 rows=200 loops=1)
        Output: film_actor.actor_id, count(DISTINCT film_category.category_id)
        Group Key: film_actor.actor_id
        Buffers: shared hit=7 read=32
        ->  Sort  (cost=527.28..540.94 rows=5462 width=4) (actual time=242.932..243.781 rows=5462 loops=1)
              Output: film_actor.actor_id, film_category.category_id
              Sort Key: film_actor.actor_id
              Sort Method: quicksort  Memory: 449kB
              Buffers: shared hit=7 read=32
              ->  Hash Join  (cost=28.50..188.22 rows=5462 width=4) (actual time=17.034..216.640 rows=5462 loops=1)
                    Output: film_actor.actor_id, film_category.category_id
                    Hash Cond: (film_actor.film_id = film_category.film_id)
                    Buffers: shared hit=4 read=32
                    ->  Seq Scan on public.film_actor  (cost=0.00..84.62 rows=5462 width=4) (actual time=0.019..195.884 rows=5462 loops=1)
                          Output: film_actor.actor_id, film_actor.film_id, film_actor.last_update
                          Buffers: shared hit=2 read=28
                    ->  Hash  (cost=16.00..16.00 rows=1000 width=4) (actual time=16.964..16.965 rows=1000 loops=1)
                          Output: film_category.category_id, film_category.film_id
                          Buckets: 1024  Batches: 1  Memory Usage: 44kB
                          Buffers: shared hit=2 read=4
                          ->  Seq Scan on public.film_category  (cost=0.00..16.00 rows=1000 width=4) (actual time=0.017..16.431 rows=1000 loops=1)
                                Output: film_category.category_id, film_category.film_id
                                Buffers: shared hit=2 read=4
Planning Time: 966.447 ms
Execution Time: 436.348 ms

On the first line, the actual total time is 324.861 ms but on the last line the execution time is 436.348 ms. What are the causes of this differences?


texts: pgAdmin-III - Option to load a favorite automatically when opening query window

texts:
pgAdmin-III - Query window option

Does pgAdmin-III has any option to load a default favorite file automatically when opening new query window? This would be much useful to have commit/rollback statements by default when executing random sql statements.

Example:

BEGIN;
SAVEPOINT my_savepoint;
-- Todo Queries
-- Ex. UPDATE users SET firstname = 'new first name' WHERE lastname = 'lastname';

-- Either one below
-- COMMIT;
-- ROLLBACK TO my_savepoint;

I want this always to be loaded when opening a new query window from pgAdmin-III.
Any suggestions?


texts:
I have two tables, division & template, in a MySQL database.

division
    - DivisionID
    - DivisionName

template
    - TemplateID
    - TemplateName

Here is an example of my current data set.

division
    - 1, Marketing
    - 2, Commerce
    - 3, Infrastructure

template
    - 1, Awareness
    - 2, AAR Summary
    - 3, Release Acceptance

I'm looking for a result set of:

Marketing Awareness
Marketing AAR Summary
Marketing Release Acceptance
Commerce Awareness
Commerce AAR Summary
Commerce Release Acceptance
Infrastructure Awareness
Infrastructure AAR Summary
Infrastructure Release Acceptance

Everything else I've seen that is similar to this is using a PHP or other script to accomplish. How would I achieve something like this using pure MySQL?


texts: Stored procedure parameters using tsql functions?

texts:
Environment sql server 2005 sp3

I have a stored proc that takes an INT as input. I want to CAST a CHAR to an INT during the call to the stored proc. it seems I cannot do that. I get a syntax error before @foo. I do not see it can someone help me find it please. Thank you very much.

CREATE PROCEDURE testme
@test AS INT
AS
BEGIN
SELECT @TEST
END

    DECLARE @foo AS CHAR(6)
set @foo = '11test'
EXEC testMe @test = CAST(Substring(@foo,1,2) as int)


texts: Why does Oracle consider a larger number to be smaller?

texts: For some reason when I try to do greater than or less than, it says that 100 is less than 2. I think it's only reading the first digit when it determines the greater/less value. Not sure why or if its suppose to be like this? Trying to create a Short Ship report for my database course.


texts: multiple ids table for accounting schema

texts:
I am trying to make a small accounting db and I have hit a dead end, my search bared no results as of how to make this idea work.

In the picture below you can see that I am trying to link multiple tables to one table CreditDebit in this case, so I can use CreditDebit's ID (CD_ID) in the BondDetails table as the Creditor_CD_ID and Debtor_CD_ID

what I am trying to achieve is to have correct structure so that I could create a query that would tell me which table the creditor or debtor belong to based on their id and show their name (box_name, emp_name, customer_name)

NOTE: CreditDebit table would have only one of the three ids (box_name, emp_name, customer_name)


texts: Using gMSA account for SQL server reporting services

texts:
I am trying to configure SSRS on my VM:s via gMSA account which does not seem to work and unable to access the reporting url ( Web Portal URL) link  to check configuration.
The requirement is to have services hosted on different VM's than its report server and report server TempDB databases. So on server A and B simply running SSRS services fails when using gMSA during the configuration steps in reporting server configuration utility. I am using same gMSA on 2 other database servers as well.

Error" The report server cannot connect to report server database due
to logon failure. User name and password incorrect"

Using setup as mentioned here: Configure a Native Mode Report Server Scale-Out Deployment
Is gMSA supported for scale out or there is something I am missing here?


texts: Is Overwriting the PFILE with the database on dangerous?

texts: Quick question that I cannot seem to find the answer to. I have some indexes that I want to rebuild at a scheduled interval, but they are fairly large and fill up the archive_log_dest_3 if I leave them unattended. So, I wrote a script that changes the dest_3 parameter in the pfile to a much larger mount point, and then changes it back afterwards, but the script overwrites the PFILE with an updated version. Would overwriting the PFILE while the database is on and doing these index rebuilds be dangerous or not recommended? Thanks!


texts: Why take full backups when you can do COPY_ONLY?

texts:
With a SQL Server 2012 availability group, you can only take COPY_ONLY full backups on replica. You can make regular BACKUP LOG.

As it's possible to restore logs backups after a database restoration from a COPY_ONLY dump, do we really need to take full (non-COPY_ONLY) backups?

Yeah, it creates a new LSN sequence, but if we're not doing DIFFERENTIAL backups, is it something we have to think about?

By using only COPY_ONLY backups, I would hope to gain the ability to perform all backups on the read-only replica. My AG is asynchronous, so the backups might be behind the primary, but that is an acceptable risk.

I do take log backups. According to my tests, I'm able to restore any log backup on a database (restored from a COPY_ONLY or not) as soon as the log backup LSN is more recent than the full backup. The full backup changes the database_backup_lsn, the COPY_ONLY doesn't, but it seems it doesn't affect log restore. It seems I just can't restore DIF backups, but I don't need that.

There's a good explanation in the dba.se Q &A:

SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

...but it does not answer my question. For now, my conclusions are: Except for the first backup (without which you will not be able to take log backups), we just can work with COPY_ONLY backups if we don't use differentials.

My (general) question is: Do we really need non-COPY_ONLY backups if we don't take differentials, and if yes, why?