texts: Creating tablespace on network location in Postgres

texts:
I have a specific requirement where I need to store all the database files on a network drive.

I have tried creating a new tablespace using the following command

CREATE TABLESPACE test
  OWNER postgres
  LOCATION E'z:\\test';

Here z is a network mapped drive on a windows system. I can create a new tablespace on usb drive, but not on network drive. Is it at all possible to store database files on a network location for Postgres. I am getting permission denied error for network location.


texts: How to open SQL Server 2008 R2 Configuration manager in Windows 8.1

texts: In Windows 8.1, i can't find Configuration manager of SQL Server 2008R2. I have already followed the steps as specified in this question. But Configuration manager is not showing up even after entering 'SQLServerManager10.msc' in Search. Please let me know how to achieve it.


texts: MS Access 2010 report - SQL pivot query WHERE criteria not working

texts:
I have created a report that has a list of actions which are populated from an "Actions" table. (See Below)

However, each action will have multiple updates populated from an "Updates" tables and I need the updates to be pivoted as there is a common field between them all which is the Date of the Update. (See Below)

As you can see, I have managed to complete this as there are 2 updates in the sub table. However, I have had to manually stipulate the WHERE criteria because I am unable to assign the main report field "ID" (The fields under the Action Column are named ID in the main report) to the WHERE criteria.

My SQL code for what I have at the moment is:

TRANSFORM First(Updates.[Description]) AS FirstOfDescription
SELECT Updates.[ActionID]
FROM Updates
WHERE Updates.ActionID=2
GROUP BY Updates.[ActionID]
PIVOT Format([UpdateDate],"Short Date");

But I would assume all I would have to do is change the WHERE line so that it reads the following:

TRANSFORM First(Updates.Description) AS FirstOfDescription
SELECT Updates.ActionID
FROM Updates
WHERE (((Updates.ActionID)=[ActionPlanView].[ID]))
GROUP BY Updates.ActionID
PIVOT Format([UpdateDate],"Short Date");

NOTE: ActionPlanView is the name of the parent report where the sub report with the pivot table will be embedded.

But when I try this I get the following error message:

  The Microsoft Access database engine does not recognize
  '[ActionPlanView].[ID]' as a valid name or expression

What am I doing wrong? I just want the sub report to be filtered on the Action number (Field "ID") of the parent report.

I only appear to get this issue when I attempt to transpose the data (Pivot). If I leave it as the following SQL statement it works but it is not in the correct format:

SELECT Updates.ActionID, Updates.UpdateDate, Updates.Description
FROM Updates
WHERE (((Updates.ActionID)=[ActionPlanView].[ID]));

I am happy to provide more information if required but I am rather new to Databases so I apologies in advance if I am missing something.


texts:
I'm trying to do collection_manager in OVPI but I'm getting db error

  ORA-00379 no free buffers available in buffer pool default for block
  size 32k
  oracle 10.2

Thanks for any help in advance


texts: Why am I getting `Impossible WHERE noticed after reading const tables` in explain query?

texts:
I have a unique compound key like fr(fromid,toid) in the table, when I run the query with explain I get the following result:

Impossible WHERE noticed after reading const tables`

The query I ran:

explain SELECT rid FROM relationship WHERE fromid=78 AND toid=60

Any help?

EDIT1:
When I use the below query:

explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND is_approved='s'  OR is_approved='f' OR is_approved='t'

I see USING WHERE instead of the previous message, but when I use the below query:

explain SELECT rid FROM relationship WHERE fromid=60 and toid=78 AND (is_approved='s'  OR is_approved='f' OR is_approved='t')

I again get the first impossible ... message! What these parenthesis do here?

EDIT2:

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `fromid` mediumint(8) unsigned NOT NULL,
 `toid` mediumint(8) unsigned NOT NULL,
 `type` tinyint(3) unsigned NOT NULL,
 `is_approved` char(1) NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `fromid` (`fromid`,`toid`),
 KEY `toid` (`toid`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`fromid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`toid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

EDIT3:
As mysql site say:

  Impossible WHERE noticed after reading const tables

  MySQL has read all const (and system) tables and notice that the WHERE
  clause is always false.

But in the query I get the result I want, the WHERE part is not false. Is there someone who could explain this and shed some light on the subject?


texts: Seemingly equivalent queries take vastly different execution times

texts:
I am encountering an issue where two different ways of executing the same query are taking very different execution times. My guess is that in one way or another, the slow query is unable to utilize my index.
I have a large table of data points (Data)

TABLE(assetId INT, dataItemId INT,timestamp DATETIME(2),value REAL)

There is a full table unique clustered index here. assetId is the owner of the datapoint, dataItemId is other metadata for the datapoint, including a label. So there is a table DataItems

TABLE(dataItemId INT, labelId INT, unitId INT)

And finally a table of Labels (there is another table just like Labels for Units). I have FK constraints on the DataItems/Labels/Units tables.

TABLE(labelId INT, label VARCHAR(128))

I would like to make a query for data across a time range asking for all datapoints with a certain label for a particular asset. Something like:

--QUERY #1
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Label.label IN ('Eng_RPM','Eng_Load','Eng_OilTemp')
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

However, this query (returns 21000 rows) takes about 38 seconds to execute. So I tried an alternate query, where I use dataItemId instead of Labels:

--QUERY #2
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Data.dataItemId IN (
   SELECT dataItemId from DataItems
   JOIN Labels ON DataItems.labelId=Labels.labelId
   WHERE label in ('ENG1_Eng_Load','ENG2_Eng_Load','ENG3_Eng_Load')
   )
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

I didn't expect this to work any better, and it didn't. However, I then took the output of the nested select in the WHERE clause, and used it as a constant:

--QUERY #3
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Data.dataItemId in (618,654,690)
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

This query runs like a dream with <1s query times. I don't understand why though - it seems to me that the nested query for finding the dataItemId from a label should execute first, and I verified that it is a trivial query. From there, why is there a difference between the way #2 and #3 execute, and why does it take so much longer? I am new to databases in general, and to SQL server/ Management studio, so my apologies if I have overlooked some wonderful feature for analyzing this problem.

Edit:

Indices/PKs are as follows:

 --Data:
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20160725-145924] ON      [dbo].[Data]
 (
[assetId] ASC,
[dataItemId] ASC,
[timestamp] ASC,
[value] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--DataItems
ALTER TABLE [dbo].[DataItems] ADD PRIMARY KEY CLUSTERED
(
[dataItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--Labels
ALTER TABLE [dbo].[Labels] ADD PRIMARY KEY CLUSTERED
(
[labelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I have viewed the execution paths and I did find that the slow queries are not using the Clustered index on Data - they are using a different Non-clustered index that I did not intent to keep (on assetId,timestamp)


texts:
Let's say, I have a course which consists of N lessons. I want to offer it via email subscription. Each N days an email with content should be sent to all the users who've subscribed.
I want it to be sent to the users every 1, 2 or 3 days. For instance.
I'm looking into a way to create a database scheme where I'll able to store these time ranges, and of other types (weeks, months...), in the future.
Date-time ranges can be in minutes, hours, days, weeks and months. And there should be a possibility to specify at what time during a day it'll be sent, or which day of week, etc...
Note that each newly subscribed user should also go through the same process: he'll receive the same N lessons with the same frequency or date-time ranges, for instance: 1st lesson on day 1 at 15:00 o'clock UTC, 2nd on day 3 at 9:30 o'clock, 3rd on day 8 at 21:15 o'clock and so on.
This type of scheme isn't the same as the one for scheduling re-occuring tasks and cron in Linux.
And I want it to be flexible enough too.
I've began with these fields:
* frequency_unit: enum (minute, hour, day, week, month)
* frequency_value: int

How to create it?


texts: SQL server is there recursive reserve word?

texts:
Is there any reserve word 'RECURSIVE' while using CTE query. For example,

WITH RECURSIVE node_rec AS (
   (SELECT 1 AS depth, ARRAY[node] AS path, *
    FROM   nodes
    WHERE  parent IS NULL
    LIMIT  10
   )
    UNION ALL
    SELECT r.depth + 1, r.path || n.node, n.*
    FROM   node_rec r
    JOIN   nodes    n ON n.parent = r.node
    WHERE  r.depth < 4
)

How to achieve ?


texts: Restore a Database to a New database (T-SQL)

texts:
I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the following error, despite specifying REPLACE option. For example I used the links below:

Restore Database script

http://msdn.microsoft.com/en-us/library/ms190447(v=sql.105).aspx

Based on the posts I found on the internet, first I got the logical names of the backup file using script below:

RESTORE FILELISTONLY
FROM DISK = 'C:\Backups\SourceData_20121227.BAK'

Then used the logical names in the following script:

RESTORE DATABASE SourceData_1
FROM DISK = 'C:\DEV\SourceData_20121227.BAK'
WITH
   MOVE 'SourceDate' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1.mdf',
   MOVE 'SourceDate_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1_log.ldf',
   REPLACE
GO

Below is the error I'm getting:

  MODIFY FILE encountered operating system error 112 (failed to retrieve text for this error. Reason: 15105) while attempting to expand the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1_log.ldf'.
  RESTORE DATABASE is terminating abnormally.

I checked the disk space and can confirm it is fine. I assume the target database should exist before running the restore. Is that right?

Any suggestion how to fix this issue?

Thanks.


texts:
I have a backup of a SQL Server database that I would like to restore. The issue is that the server that I would restore it to already have a database with the same name, layout etc, which contains data.

Is there some easy way to merge data form the backup into the existing database so that only missing rows are inserted?


texts:
Is it possible to create a table with a clustered key from a query in a single step or do I have to do this:

select 1 as id into foo;
create unique clustered index pk_foo on foo(id)


texts: Is it possible to change the drive that Microsoft DPM backs up to?

texts:
A while ago our sysops guys created a 15 minute transaction log backup schedule using Microsoft DPM.  We often have an issue where there won't be enough space on the Log drive to perform a backup because DPM creates its backup on the same drive that the logs live on. Eventually, because DPM can't back up the logs, they get larger and larger and the problem gets worse and worse until I run a manual backup of the bloated logs through SQL Server (and then, sadly, I have to run Shrinkfile on the logs that bloated).

I can't find any setting or option in DPM that allows me to write the backups to a different drive with more space instead of the drive that the logs sit on.  Does anyone know if it's possible to do this and how?


texts:
I had a MySQL 5.7 instance on Google CloudSQL. Now i created a MySQL 8 instance.
The configuration is pretty much the same (except i use 2 CPUs instead of one, 3.75GB of RAM). Now the default config for MySQL Memory usage (like innodb_buffer_pool_size,..) seems to be the same.
I migrated about half of my applications to use this instance. What is happening now is, that the instance Memory usage goes above 3.XX GB and the service gets restarted. Which is super annoying because in that time my applications obviously crash. It seems like memory usage grows with every select statement, and everything is cached.
Here are some of the config values:
|                          key_buffer_size |           8.00  MB |
|                  innodb_buffer_pool_size |        1408.00  MB |
|                   innodb_log_buffer_size |          16.00  MB |
|                         sort_buffer_size |           0.25  MB |
|                         read_buffer_size |           0.125 MB |
|                     read_rnd_buffer_size |           0.25  MB |
|                         join_buffer_size |           0.25  MB |
|                             thread_stack |           0.273 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |          16.00  MB |

This makes CloudSQL pretty much unusable to me. I need MySQL 8 without crashing several times a day.


texts: SQL Server CDC (Change Data Capture) Existing Data Seed

texts:
I have an SQL Server Database with 100's of tables with as many rows in each and I'm turning on CDC for some reporting. Since data was created/inserted BEFORE turning on CDC, the CDC tables are current empty and I would like to get the data into the CDC tables to start reporting.

Is there a way to 'refresh' or seed the existing data into the CDC tables?

I've tried a simple update with existing data, however this doesn't actually cause a CDC entry. Presumably it isn't writing to the transaction log and triggering an write.

UPDATE table1 SET column1 = column1;

In my case, most of my tables currently have an UPDATEDON column which I could just update to a now timestamp, which will cause an update and subsequent entries in the CDC tables, but I'd prefer to retain the existing update time.

UPDATE table1 set UPDATEDON = getDateUtc();

Alternatively I could go through and move all the data into temporary tables, delete the data and re-insert it to cause updates, but surely there is a better way? How have others tackled this?


texts:
I am trying to write a query that will update my table #master12.cg when it is null.  So here are my steps needed:

  1) Check if #master12.uid exists in #check1.dasboot and if it does, then update #master12.cg with that value
  2) Check if #master12.uid exists in #check.uid and if it does update #master12.cg with the value in #check2.redbluegreen
  3) If #master12.uid does not exist in either table then just leave #master12.uid as null

I have tried this query

Update #master12
Set [cg] = case
            when exists (Select [dasboot] FROM [#check1] WHERE [#master].    [uid] = [#check1].[uid])
            when not exists (Select [redbluegreen] FROM [#check2] WHERE     [#master12].[uid] = [#check2].[uid]
       else [#master12].[cg]
       end
WHERE [#master12].[cg] IS NULL

However this query presents an error of:

  Msg 156, Level 15, State 1, Line 4
  Incorrect syntax near the keyword 'when'.
  Msg 156, Level 15, State 1, Line 5
  Incorrect syntax near the keyword 'else'.

How should I write this update statement so that the checks listed above are performed and my table is updated accordingly?


texts: SQL Server 2005: Full-text search space requirements

texts:
Is there a way to calculate the physical drive space required for a full-text search catalog?  Space is cheap, but I'd like to know what I'm getting into.

I'm looking at a table of about 200 articles ranging in length.  I'd like to index the title field, and the body of the article.

UPDATE:  I'm somewhat looking to predict the future.  For example, 200 articles, the title is varchar(500) and the article body is varchar(max).  So, without creating the index, can an estimate be done on how large the full-text search catalog may be?


texts: Instant File Initialization on Backup drive

texts: I know IFI (Instant File Initialization) is good to have enabled on SQL Servers. How about backup archive locations? We backup our DBs to a local SSD drive (so it backs up quickly), then throughout the day we slowly move the backups over to an archive location and perform restores and DBCCs on a non-prod server. I was curious if that archive location we copy the backups to should have IFI enabled?


texts: What is the best way to compare values of whole tables between many databases?

texts:
We are trying to compare the values of whole tables between many databases.
The user can enter in input the table names and the columns names, and the database names he wants to compare.

They can enter as many databases, tables and columns they wish. We want to compare rows per rows, only for the specified columns.

ex:
DatabaseA, DatabaseB, DatabaseC
Table1, Col1|Col2|Col3|Col4
Table2, Col1|Col4|Col5|Col6|Col20
...

So for an example, if I have:

DatabaseA
Table1, Col1|Col2|Col3|Col4 = 'Apple', 1, 10, 'ABC'

DatabaseB
Table1, Col1|Col2|Col3|Col4 = 'Banana', 1, 10, 'ABC'

There's a difference.

At first I was considering usign the CHECKSUM on each rows if each tables (excluding columns that where not specified by the user) and compare the CHECKSUM but I've read that it's not always unique.

Now I was thinking of using HASHBYTES instead.
Either by doing this:

    SELECT HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col1,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col2,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col3,'')) +
           HASHBYTES('sha2_512', CONVERT(NVARCHAR(MAX), ISNULL(col4,'')) +
    FROM Table1

Or by doing this:

   With Vals AS
   (
       SELECT CONVERT(NVARCHAR(MAX), ISNULL(col1, '')) +
              CONVERT(NVARCHAR(MAX), ISNULL(col2, '')) +
              CONVERT(NVARCHAR(MAX), ISNULL(col3, '')) +
              CONVERT(NVARCHAR(MAX), ISNULL(col4, ''))  AS Val
       FROM Table1
   )
   SELECT HASHBYTES ('sha2_512', Val) FROM Vals

What do you think? How would you do it? What would be the best way?

Thanks


texts: How can I return multiple rows of records in PL/pgSQL

texts:
I am trying to return multiple records using RECORD data type, is there a way I can append to RECORD and add/append a new value with each iteration to this RECORD.

that is, I want to append to rec so that rec becomes a set of rows when the loop is over, which I can just RETURN at the end of my function.
Currently, I am doing this -

SELECT temp_table.col1, temp_table.col2, temp_table.col3
      INTO rec
      FROM temp_table
      WHERE temp_table.col3 = false;

my full code is here:

CREATE OR REPLACE FUNCTION validation()
  RETURNS RECORD AS $$
DECLARE
        rec RECORD;
        temp_row RECORD;
BEGIN

  CREATE TEMPORARY TABLE temp_table (col1 TEXT, col2 INTEGER, col3 BOOLEAN) ON COMMIT DROP;

  FOR temp_row IN SELECT * FROM staging.validation
  LOOP

    RAISE NOTICE 'sql: %', temp_row.sql;

    EXECUTE format('INSERT INTO temp_table %s', temp_row.sql);

    IF (SELECT DISTINCT temp_table.col3 FROM temp_table WHERE temp_table.col3 = false)=false THEN
      RAISE NOTICE 'there is a false value';

      SELECT temp_table.col1, temp_table.col2, temp_table.col3
      INTO rec
      FROM temp_table
      WHERE temp_table.col3 = false;
    END IF;

  END LOOP;
  RETURN rec;
END; $$
LANGUAGE plpgsql;

Current output after SELECT validation();

validation
(crea_ddf,8095,f)

Desired Output

validation
(crea_ddf,8095,f)
(some_source_system,some_count,f)
(some_other_source_system,some_count,f)
(.....)


texts: Are there any unforseen down sides to using views to abstract away from cryptic table names?

texts:
We have to report against a very large and complex vendors HR/Payroll database which has cryptic table names such as A100M, C755V, X50AA etc.

In order to make writing queries more human friendly, I have suggested we create views with meaningful and descriptive names to abstract away from the cryptic table names using very simple views such as:

CREATE VIEW Employees
AS
SELECT
   *
FROM dbo.Y654AS

So I can then simply write a query such as:

SELECT
    EmployeeId
    ,Forename
    ,Surname
FROM dbo.Employees

Other than the vendor changing the table names in an update (which is very unlikely), are there any gotchas with doing this which I may have missed?


texts: How can I enable my-huge file on windows server?

texts:
I installed Windows Server 2008 and MySQL 5.5.
Now I need configure MySQL for huge InnoDB tables and a lot of connections.

I see that on my Windows Server, there is a my-huge file(configs files that come with mysql :my, my-huge,my-large .ini) with configuration inside the mysql directory.

How can the my-huge configuration be enabled?


texts: Are TCP loadbalancers an alternative to Postgres 9.X read slaves load balancers like pgPool II or pgBouncer?

texts:
To scale read traffic on PostgreSQL streaming replication slaves, I want to be able to load-balance the requests. Postgres documentation suggests using tools like pgPool and pgBouncer, but I am wondering if there is something wrong (in principle) with using a TCP load balancer (like HAProxy or AWS Elastic Load Balancer) fronting the postgres read slaves.

The load balancer serves as a single Read End Point for read requests that need to be made by clients. A significant advantage would be that read requests are not impacted when read slave servers go down, as other servers in the load balancer can pick up the load.


texts:
I've a table with many columns (~70) which contains computer's configuration. It contains approximately 50 000 rows.

This table is very requested and all these column too.

I use to run the following query to know missing index history:

-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011.

use myDatabaseName

SELECT TOP 25
    dm_mid.database_id AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
    + ']'
    + ' ON ' + dm_mid.statement
    + ' (' + ISNULL (dm_mid.equality_columns,'')
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
    '' END
    + ISNULL (dm_mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
ORDER BY Avg_Estimated_Impact DESC

GO

But for these specific table it gaves me twenty different indexes with an impact > 300 (impact = dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)).

I read somewhere that it's not relevant to have more than 10 indexes, is there any recommendations about it and how can I know if it's really usefull to define all these indexes?


texts:
I want to implement incremental ETL on some of my sql server tables, so I need a new column like UpdatedDate to track change dates of my records. I want to transfer only updated and inserted records, so a single column is enough. The question is should I use datetime column and populate it with triggers or it is better to user timestamp column? What are pros and cons?

In both cases I want to create nonclustered index on my columns to find rows from the last sync faster.


texts:
I am beginner in web hosting. I have some site which are created by php/codeigniter.. Today All custom sites (created by codeigniter) are showing these msg

Error Number: 1286

Unknown storage engine 'InnoDB'

Don't understand why this happened.. Give me idea why this happened and how to solve this issue.


texts: Query performance degrades with time and use

texts:
I have been fighting a problem for several weeks now where the performance of my SQL Server queries degrade over a few days of use. In addition every few days, a query will simply not return from my application's ODBC SQLExecute() call.

Rebuilding the indexes manually (in SQL Server Management Studio) "fixes" both problems.

Here is some more information;

DB is ~13 Gig in size
Using ODBC ver 3.5.1 in the app
The apps are C++ with ODBC interface to the SQL DB
Test case is on a static DB - no inserts, thus no fragmentation should be possible!
Small queries are initially less than 1 sec, and degrade to over 50 seconds
Have seen the problem on all (3) machines it's run

I'm having a hard time understanding why the same query works fine for a while, but then starts slowing waaay down??

Any ideas would be greatly appreciated.


texts: Images, PDF, audio and video files in MySQL

texts:
I am designing a database for an eCommerce website.

How can I store images, audio, video and PDF files which are uploaded from the online users, in a MySQL database?

I am using the InnoDB storage engine. While surfing on the Internet, I got the following answers:

I can store these files using BLOBs.
I can use the Varchar datatype to store the physical path to store these files.

According to my previous experience storing the files on the database has some issues while retrieving that files (sometimes I got broken files).

What is the best practice to store these files in my MySQL database? As I am beginner, I need to know the performance implications for my database in such situations.


texts: How do I deal with query getting slower when a select parameter is not specified inline?

texts:
I faced this problem twice with SQL Azure and two different queries so far.

Something like this:

select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here'

runs super fast but equivalent code (generated from Linq-to-SQL)

declare @accountId uniqueidentifier;
set @accountId = 'actual-guid-typed-in-here'
select top(100) * from SomeView WHERE AccountId=@accountId

runs super slow and upon careful analysis I see that the engine uses the wrong index in the latter case. Note that the latter case would also arise if I moved my code into a stored procedure.

The two queries are identical and so I expect them to be executed identically but the database engine thinks otherwise.

What's the typical solution from this case?


texts: Namespacing database names on my server in order to test multiple different SSIS packages

texts:
An SSIS package that I'm building has two OLE DB connections, to databases named "core" and "staging". I created test versions of these two databases on my local SQL Server installation. I named them "test1_core" and "test1_staging". The "test1_" namespace is necessary because I have many SSIS different packages in active development at one time, and many of them refer to the same production databases. But it need each local SSIS package to refer to its own version of the database.

This works okay until I need to refer to the database name in the package specifically like in an Execute SQL task that includes two connections.

Also, I have SQL scripts that say use [core] at the top and I wish I could just use them on my local server without always having to open them up and change that line to use [test1_core] first, then having to change it back.

Is there some way to namespace the database name from within SQL Server so I can just refer to the local database as "core" from the SSIS package?


texts: Convert spatial points data to line data for use in Line Layer in SQL Server Reporting Services (SSRS)

texts:
I have a query that returns the fields Order, Lat, Long, SpatialData, <OtherStuff>. SpatialData is a point.

I want to create a line layer that draws lines between my points based on the order field.

According to http://technet.microsoft.com/en-us/library/ee240828.aspx#LineLayer I need to do something like this:

SELECT N'Path' AS Name
    , CAST(
        'LINESTRING(
           -76.5001866085881 42.4310489934743,
           -76.4602850815536 43.4353224527794,
           -73.4728622833178 44.7028831413324)'
        AS geography) as Route

But how do I go from my data to something like that?


texts: Grouping SQL Server Connections By Connection Pool

texts:
In SQL Server 2014 is there a way to group connections by the connection pool that belong to? We are trying to find out if a specific application is leaking connections and hitting the default max pool size of 100.

In this doc on Connection Pooling it states that each pool is defined by the exact connection string used to establish the connection. However, I am unable to find anything in the sys table that allows me to see that.

The closest I have groups by loginname and program_name:

select  rtrim(p.loginame)
    ,p.program_name
    ,count(*)
from    sys.sysprocesses p
group by p.loginame, p.program_name
order by count(*) desc

Thanks in advance!


texts: Best practices for Feed restructuring in a social app database

texts:
I have a social app, where users can post to the school they chose on registration. Each post can have a hashtag and a school (all pointers, (hashtag is optional, school is not)).

Users see posts based on school or hashtag. Now we want to introduce a new feature, Polls. Polls are another type of post... Users will be seeing them in their feed among other posts! Besides other properties, they too have a hashtag and a school.

Right now when a user asks for posts, I'm just making a query to the Post collection, comparing only the school or hashtag field (depending on the screen they're at). So if they're on the Home screen, I'm going to have something like:

  find all documents in Post, where school equals <some_value>

And if they are on the Hashtag screen, we have something like:

  find all documents in Post, where hashtag equals <some_value>

Since we are going to have the new Poll collection, I'm wondering what implementation would be best... Here's what I have so far:

1st implementation

We keep the posts query as is and we add another one, pretty much the same, for the polls and we combine the results. Have in mind here, that our app fetches data with pagination, so we bring the posts 20-by-20. I'm saying this because in case we have a page and the responses of the 2 queries bring 20 documents each, we'll have to compare dates to know what the response should be... eg 16 posts and 4 polls.

2nd (and last) implementation

We create a new collection, Feed, which would combine both collections and it would look like this:

Feed

post: <Pointer to Post>
poll: <Pointer to Poll>
creator: <Pointer to User> // we need this for block checking or if you open your profile
school: <Pointer to School>
hashtag: <Pointer to Hashtag>

Every time a post or poll will be created, their triggers will create a Feed document, with all the necessary values.

I believe the second solution is the best, as it is much cleaner than the first. I also think they're pretty much the same complexity-wise.

Any ideas on which one is better? If you have any other implementation to suggest, please do so!

NOTE: All collections are indexed.


texts: What does the error "Ambiguous column name 'id'" mean?

texts:
I am getting an error with the following query that uses a right join.  My table has a column named 'id'.

SELECT TOP 6 id
FROM   dbo.tbldata
       RIGHT JOIN tblimg
               ON tbldata.id = tblimg.newsid
ORDER  BY createdDate DESC

The error is:

  Ambiguous column name 'id'


texts: Still i am not satisfying with Logfile Shrinking in SQL Server?

texts:
Already i asked this question but still i'm not getting the very clear answer.
In my server holds more than 20 live databases in that my logfile(.ldf) is use to grow more than 5 GB per day.

Using the below script i'm shrinking my logfile size on day-to-day basis.But after reading the articles i'll come to know my travel is in wrong way.Know i need to start the Logfile shrinking in a good way.

USE ABC;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ABC
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ABC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE ABC
SET RECOVERY FULL;
GO

My Database Recovery Model is 'FULL' because if i'm keeping my database recovery model in simple might be data loss can happen for that only i'm keeping my database in the Full recovery model.

Doing the above Querying is a bad idea. Here i need expertise suggestion to keep my database logfile in a best way.As well as i need to avoid the log file growing for each and every day.


texts: Limit access to specific database only, and restrict access to system tables

texts:
I have a user test, which can view data from the system tables such as pg_class. The idea is to isolate him to have access to a specific database only
and not have any access to system resources.

I don't want him having access to tables like pg_stat_replication, pg_stat_activity, or using any of the functions like pg_current_xlog_location();.

The question is, how can one achieve this?

For example:

Create user test with password test

I cannot create DB or create another user or role, however I have access to let's say pg_class (by default, even the path is not set to public it is set to test schema), which I actually would like to prevent in general.

In Oracle (where I come from), there is the principle of least privilege in other words you create a user and it does not have any privilege or role granted to him.


texts:
I have a following table with 1 million records :

CREATE TABLE `activity` (
  `id` int(9) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `subscription_id` int(10) unsigned NOT NULL,
  `vid` int(10) unsigned NOT NULL,
  `action` enum('init','confirm','suspend') DEFAULT NULL,
  `source` varchar(20) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `num` varchar(32) NOT NULL,
  `model` varchar(20) DEFAULT NULL,
  `price` float(5,2) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `num` (`num`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

I want to delete entries for same num value but that are old. So I came up with following query :

delete activity
 from activity
 inner join (
   select max(id) as maxid, num
     from activity
    group by num
   having count(*) > 1) duplic on duplic.num = activity.num
where activity.id < duplic.maxid;

The select query works fine, returns around 400.000 records. But if I try delete, the query executes infinitely. What might be wrong with this? How to investigate?


texts:
I want this trigger to be fired after an insert is made with the text married on the marital_status column, this is what I have so far

ALTER TRIGGER [dbo].[marriage]
ON  [dbo].[applicant_personal_info]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF (SELECT [marital_status] FROM inserted) = 'married'
 BEGIN
     INSERT INTO [dbo].[applicant_marriage_info]([dom])
     VALUES('abc')
 END
END


texts: SQL Server: what is the reason to favor customized database roles over fixed ones?

texts: This may be a bit vague, but I was pondering how much DBA's use fixed database roles controlling security of databases. So the question is, is there a reason to favor customized database roles over fixed database roles (db_datareader etc.)?


texts: tsql: table data size won't shrink after large row deletion

texts:
i have a table with 12 months+ data in it.
I backup the table then delete all rows keeping only rows inserted in the last month.
But the table size won't shrink, nor the underlying physical file.

dbcc showcontig ('mytable') with tableresults;

ObjectName  ObjectId    IndexName   IndexId Level   Pages   Rows    MinimumRecordSize   MaximumRecordSize   AverageRecordSize   ForwardedRecords    Extents ExtentSwitches  AverageFreeBytes    AveragePageDensity  ScanDensity BestCount   ActualCount LogicalFragmentation    ExtentFragmentation
mytable 478624748   PK_Log  1   0   21344   467392  94  8033    237 0   2675    12918   2862,346    64,6361996540647    20,6517532316743    2668    12919   77,8392053973013    71,7757009345794

exec sp_spaceused 'mytable', @updateusage = 'TRUE'

name    rows    reserved    data    index_size  unused
LogTrace    467392      8984976 KB  8930056 KB  21984 KB    32936 KB

These values means each row of the table is about 19MB!

I tryed to rebuild the index with no change:
    ALTER INDEX PK_myindex ON mytable REBUILD

Other things i tryed:

select
[FileSizeMB]    =
    convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB]   =
    convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
    convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
GrowthPct   =
    case when a.status & 0x00100000 = 1048576 then a.growth else null end,
GrowthMB    =
    convert(int,round(case when a.status & 0x00100000 = 1048576 then null else a.growth /128.000 end,0)),
[DBFileName]    = a.name
from
sysfiles a;

result

FileSizeMB  UsedSpaceMB UnusedSpaceMB   GrowthPct   GrowthMB    DBFileName
9742.38 9740.06 2.31    NULL    1   mytable
72.25   15.45   56.80   NULL    1   mytable_log
3.00    0.06    2.94    NULL    1   mytable_indexes

I don't know what to do more, any idea ?


texts: Specified key was too long; max key length is 1000 bytes in mysql 5.6

texts:
one of the application server is internally creating the database on my mysql  but when ever the following create table command gets executed

 CREATE TABLE ofRoster (

    rosterID              BIGINT          NOT NULL,
    username              VARCHAR(64)     NOT NULL,
    jid                   VARCHAR(1024)   NOT NULL,
    sub                   TINYINT         NOT NULL,

    ask                   TINYINT         NOT NULL,
    recv                  TINYINT         NOT NULL,
    nick                  VARCHAR(255),
    PRIMARY KEY (rosterID),
    INDEX ofRoster_unameid_idx (username),
    INDEX ofRoster_jid_idx (jid)

 )

i am getting following error

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

i have set my default engine to MyISAM because i was getting following error in InnoDB

specified key was too long max key length is 767 bytes

my current engines are as follows

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

now i really don't know how would i get rid off it as the application server itself is creating database automatically in mysql so i dont have control over it.

i am using Server version: 5.6.10 MySQL Community Server (GPL) version

character set of column

+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | latin1                                                 |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.6.10-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+


texts: How to store an array in a table column?

texts:
I have multiple contacts for a user and I want to store them in a single column.

uID |  Contact
----| ---------
2   | 1221
    | 1223
----|----------
3   | 1123

How can I do that?


texts: Problem Installing 11.2.0 on RHEL 7 64 Bit

texts:
I have problem in stalling Oracle ASM on RHEL 7, Please advice

 [root@localhost grid]# ./root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node lrma086 successfully pinned.
Adding Clusterware entries to inittab
ohasd failed to start
Failed to start the Clusterware. Last 20 lines of the alert log follow:
2015-10-19 10:19:43.306
[client(28082)]CRS-2101:The OLR was formatted using version 3.
2015-10-19 10:19:46.259
[client(28109)]CRS-1001:The OCR was formatted using version 3.
[client(28227)]CRS-10001:CRS-6021: No msg for has:crs-6021 [l][unlimited]
[client(28228)]CRS-10001:CRS-6021: No msg for has:crs-6021 [n][65536]

ohasd failed to start at /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl line 358, <ALERTLOG> line 6.
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I/u01/app/grid/product/11.2.0/grid/perl/lib -I/u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl execution failed


texts:
I have a table with more than 40 million rows and I want to know what is the best way I can optimize it for searches. The search will happen by the person's document number, and these document numbers are composed by nine numbers from 100000000 to 999999999.

My solution was to split this table into other tables identified by the two first numbers of document that those tables will store, something like, table_10 (documents from 100000000 to 109999999), table_11 (documents from 110000000 to 119999999), table_12, table_13, ..., table99. And when the user searches for the document 10... I will build the SQL to search in table_10.

I'm just a mortal programmer not experienced with large databases, and I don't know if my solution is good, but this is the only solution to make the system faster that I have found.  Do you have other recommendations?


texts:
I am trying to concatenates strings in T-SQL like

'This is a test ' + CHAR(13) + CHAR(10) + ' string'

When the string is long (I do not know the limit), I get an error:

  Some part of your SQL statement is nested too deeply. Rewrite the
  query or break it up into smaller queries.

How can I work around this? How is the SQL statement "nested"?

Should I switch to 0x???? notation if the string length is exceeding some value?

This question was asked 10 years earlier on

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e7244cf6-049e-4b8a-b230-226fdd96a694/large-string-concatenation?forum=transactsql

without getting a satisfactory answer.

edit

The total string to insert (with the concatenations and ':s) is 369711 chars long.
There are 6301 instances of char(13) and the same of char(10).
There are 19050 instances of +, not all necessarily used as concatenation operator.


texts: How do I query for fields that have AT LEAST a given range?

texts:
Say I have a table of the form:

+-----+-------+
| Key | Value |
+-----+-------+
| A   |     1 |
| A   |     3 |
| A   |     4 |
| B   |     2 |
| B   |     3 |
| C   |     3 |
| C   |     4 |
| C   |     5 |
+-----+-------+

I want to be able to return all of the keys that have at least a given range.

For example:

A query for a Value range of 2 would return A, B, C
A query for a Value range of 3 would return A, C
A query for a Value range of 4 would return A

Can this be done with SQL (specifically SQLite)? If so, how?


texts:
My environment consists of two RHEL7 nodes such as following:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.2 (Maipo)
# uname -a
Linux X 3.10.0-327.36.2.el7.x86_64 #1 SMP Tue Sep 27 16:01:21 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux
# rpm -q postgresql
postgresql-9.2.15-1.el7_2.x86_64
#

I'm trying to follow Streaming Replication - PostgreSQL wiki.

Step 12:

primary:

-bash-4.2$ psql -c "SELECT pg_current_xlog_location()"
 pg_current_xlog_location
--------------------------
 0/1C000318
(1 row)

-bash-4.2$

standby:

-bash-4.2$ psql -c "select pg_last_xlog_receive_location()"
 pg_last_xlog_receive_location
-------------------------------
 0/1D000000
(1 row)

-bash-4.2$ psql -c "select pg_last_xlog_replay_location()"
 pg_last_xlog_replay_location
------------------------------
 0/1D000080
(1 row)

-bash-4.2$

Please advise.


texts:
MySQL allows the use of wildcards for database names, in order to allow an user to operate only on a subset of databases:

GRANT ALL PRIVILEGES ON `foobar%`.* TO 'user'@'%' IDENTIFIED BY 'somepassword';

Is there a way to do the same for the CREATE grant, to allow (cf. the example above) user to create only databases whose name starts with foobar?

Otherwise said: is the CREATE grant global (i.e. an user with this privilege is allowed to create any database, without limitations) or it can be limited in some way?


texts:
is there a way to tell how much data MongoDB is reading from disc and how much from cache for a query ?
I looked at the explain()-Function but did not find this kind of informations.

Thanks, Andre


texts: Can't return data from SQL/Plus and Toad but can from SQL Developer

texts:
This one has me stumped. We are running an Oracle 11g on Amazon EC2 and I can connect and query fine with SQL Developer but with Toad and SQL/Plus I can connect but when I try to do a simple select out of v$session for example it gives me an ORA-03135 after a while. Here is what I have looked at/tried so far (in no particular order):

Looking in the listener.log file and seeing that all three connect fine
Turning the windows firewall completely off on the server (nothing changed)
TNSPing from my machine resolves
Port scanned to make sure port 1521 was open
Used telnet to make sure 1521 was open
Compared listener setup to oracle db on another project


texts: Failover Cluster and AlwaysOn Availability Groups across Data Centers

texts:
We have two 3 node clusters in separate data centers, let's call them ClusterP and ClusterS.

ClusterP is our primary and ClusterS is our secondary for disaster recovery. Each cluster has multiple failover cluster instances on it but they are completely segregated from each other.

We'd like to introduce AlwaysOn Availability Groups from ClusterP to ClusterS. I've read multiple guides to this and there is one question I can't really answer. We do NOT have the ability to have cross-datacenter shared storage (no SAN mirroring across datacenters). With this setup, is it possible to setup AlwaysOn Availability Groups?

I understand if we had a standalone server in the secondary datacenter, we could setup an AG with an FCI by adding that server as a node in WFCS, removing it as an owner from any of the FCIs on ClusterP, and setting it up as an AG member. This method does not use shared storage based on my research.

My confusion comes from having two-three node clusters and how they can work together with AGs. It seems to me I need to disband the cluster on ClusterS, add the nodes to ClusterP, add the FCIs needed then setup the nodes so that the physical nodes in location of ClusterS can only host those FCIs and the nodes in location of ClusterP can only host those FCIs. Will this work?

I'd appreciate any tips.


texts: How to use grouping sets in this query that shows database sizes - adding the last time it was updated

texts:
I have the following query that shows me the database sizes, including log dna data files.

SELECT [Database Name] = DB_NAME(s.database_id),

       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE s.Type_Desc END,

     --  LastUpdate= (select COALESCE(max(last_user_update),MAX(last_system_update))
        --             from sys.dm_db_index_usage_stats dius
                    --where dius.database_id = s.database_id),

       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
-- Uncomment if you need to query for a particular database
-- WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(s.database_id), s.Type_Desc),

                     (DB_NAME(s.database_id))

                     --(DB_NAME(s.database_id),s.database_id)

              )

ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

GO

and that gives me the following picture in one of my test servers:

I would like to add the last time the database was updated to the results of my query, as you can see on the commented lines in the query above, but that is giving me the following result set:

SELECT [Database Name] = DB_NAME(s.database_id),

       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE s.Type_Desc END,

       LastUpdate= (select COALESCE(max(last_user_update),MAX(last_system_update))
                     from sys.dm_db_index_usage_stats dius
                    where dius.database_id = s.database_id),

       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
-- Uncomment if you need to query for a particular database
-- WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(s.database_id), s.Type_Desc),

                     (DB_NAME(s.database_id)),

                     (DB_NAME(s.database_id),s.database_id)

              )

ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

GO

How can I change my query to display the last time the database was updated?


texts: Loop over string literals as input to function PostgreSQL 9.4

texts:
I have a function called update_total which looks like this:

CREATE OR REPLACE FUNCTION update_total (table_name CHARACTER VARYING, year CHARACTER(4), donor_type CHARACTER VARYING, donor_code CHARACTER(5)) RETURNS VOID AS $$

DECLARE

table_name ALIAS FOR $1;
year ALIAS FOR $2;
donor_type ALIAS FOR $3;
donor_code ALIAS FOR $4;
query_statement TEXT;

BEGIN

    query_statement :=
    '
    UPDATE gha.' || table_name  || '
    SET "' || year || '" = "' || year ||'_total"
    FROM gha.' || table_name || ' "' || year || '",
    (
        SELECT
        donor_type
        , COALESCE(SUM("' || year || '"), 0) AS "' || year ||'_total"
        FROM gha.' || table_name || '
        WHERE donor_type = ''' || donor_type || '''
        GROUP BY donor_type
    ) "new_data"
    WHERE
    gha.' || table_name || '.donor_code = ' || CAST(donor_code AS INT) || ';'
    ;

    EXECUTE query_statement;

END;
$$ LANGUAGE plpgsql;

It updates a row in a table that holds an aggregate value and I call it, for example, like this:

SELECT update_total ('bilateral_oda_dac_1', '1990', 'Multilateral', '20002');

The table it updates is set up so that there are columns for each year in a range, i.e.:

   Column   |       Type
------------+-------------------
 donor_code | smallint
 donor_name | character varying
 donor_type | character varying
 1990       | numeric
 1991       | numeric
 1992       | numeric
...

With this function I can only update the aggregate value in one column at at time, but I would like to be able to update a range of columns at once, something along the lines of:

CREATE FUNCTION update_all_total ()
    RETURNS void LANGUAGE plpgsql AS

$BODY$
BEGIN

FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', i, 'DAC', '20001');
END LOOP;

END;
$BODY$;

(I used this post to get me started: https://stackoverflow.com/questions/11164409/sql-call-function-multiple-times-in-a-loop-postgres-8-3). The code snipped above doesn't work because the function I am using takes a string as input and I'm passing it an integer. How can I set the loop up so that I pass strings to the function that I am calling inside it?


texts:
If I have a table such as this,

CREATE TABLE [dbo].[StreetLight](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](50) NOT NULL,
    [Shape] [geometry] NOT NULL,
 CONSTRAINT [PK_StreetLight] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

What structure is ideal to use to relationally link street lights together. For instance I have street light 1, 2, 3, 4, 5, 6, and I want to say that street light 6 and 2 are related. They might be on the same street for instance.

What are the ways I can do this?


texts: I have created a database on my remote hosting server. It has the same name as my local database on my laptop. I don't know how to create a script or transaction to copy all tables and tables' contents to my new remote database. This script or transaction should not create a new database, just create the tables and tables' contents the same as the local database. I am using SQL Server 2012.


texts: Determining SQL Server Server Edition without database access

texts:
Anyone have an idea how to get Microsoft SQL Server Edition (Express, Standard, Enterprise, Developer) without access to the database or registry?

I got the version like this:

$SQLFP = (Get-WmiObject -Class Win32_Service -ErrorAction Stop | Where-Object { $PSItem.Name -eq 'MSSQLSERVER' } | Select-Object -ExpandProperty PathName).Split('"')[1]
$SQLVer = ([System.Diagnostics.FileVersionInfo]::GetVersionInfo($SQLFP).ProductVersion).Split('.')[0]

Switch ($SQLVer) {
    "10" { "Version: Microsoft SQL Server 2008/R2" }
    "11" { "Version: Microsoft SQL Server 2012" }
    "12" { "Version: Microsoft SQL Server 2014" }
    "13" { "Version: Microsoft SQL Server 2016" }
    "14" { "Version: Microsoft SQL Server 2017" }
    Default { "Unsupported version." }
}

I found plenty of ways to determine the Edition, Patch Levels, Service Packs and stuff from Registry and via SSMS, but unfortunately I have access to neither of them. I do have permission to access the file system.


texts: Revised Question: How do I prevent Oracle 12c Database from starting on machine boot

texts:
I'd posted previously about srvctl, didn't realize it was only used if a Grid Infrastructure was installed, which in my case, is not.

Anyways, I have Oracle 12c Database installed on my Windows 8.1 pro personal machine, used for testing purposes (school work actually).  As such, I don't want it to be up every time I log into my PC.  And being forced to run SQL shutdown every time I've logged in is a hassle (I realize I could write a batch script but that's a last resort).

I can't figure out what service is starting an Oracle Instance at boot.  Does anyone know of what does?  I've been combing through the Oracle Doc's and can't seem to find out what it is.

TL;DR:  Just trying to stop my Oracle Database from initializing on a PC boot.


texts: Is there an easy way to trigger automatic failover of a single mirrored database?

texts:
I have 3 mirrored databases on a single instance of sqlserver2008-R2 in a high safety with automatic failover config. Is there a way to trigger automatic failover of just 1 database?

I want to simulate where the database actually is unreachable and not just hit the 'Failover' button from SSMS. I can stop the endpoint on the primary but that would make all 3 databases unreachable as they use the same endpoint.

How do I disable just 1 db?


texts: MongoDB - Replica sets - Syncing and DC issue

texts:
I have 3 instances of MongoDB running on my local machine.  One is native and the other two are on a Virtualbox Windows 7 VM.

LocalMongo = My local mongo.
VM1a and VM1b = My two instances of mongo running in my VM.

I was able to correctly setup a replica set within these and verified that they were operating correctly and able to sync to each other properly before I proceeded forward.  This is my test setup for a failure within this system:

Step 1: Start
LocalMongo (offline - the "original primary")
VM1a (online - new primary)
VM1b (online - secondary)

Step 2: Import data
LocalMongo (offline)
VM1a (online - push 1,000 documents into the collection "test")
VM1b (online)

Step 3: Reconnect "original primary" and sync to current primary
LocalMongo (online - secondary during the sync)
VM1a (online)
VM1b (online)

Step 4: Swap the "original primary" back into a primary
LocalMongo (online - primary)
VM1a (online - secondary)
VM1b (online - secondary)

Ok so the two things I noticed which seem like odd behavior to me are the following:

1) Right as the swap happens where LocalMongo becomes the primary again I notice that my connection to all of my MongoDB instances drop for 2-3 seconds.  When I try and run rs.status() I get:
    2014-09-17T14:56:15.632-0400 Socket recv() errno:10053 An established connection was aborted by the software in your host machine. 198.162.55.101:27017
    2014-09-17T14:56:15.647-0400 SocketException: remote: 198.162.55.101:27017 error: 9001 socket exception [RECV_ERROR] server [198.162.55.101:27017]
    2014-09-17T14:56:15.647-0400 DBClientCursor::init call() failed
    2014-09-17T14:56:15.647-0400 Error: error doing query: failed at src/mongo/shell/query.js:81
    2014-09-17T14:56:15.663-0400 trying reconnect to 198.162.55.101:27017 (198.162.55.101) failed
    2014-09-17T14:56:15.679-0400 reconnect 198.162.55.101:27017 (198.162.55.101) ok

Is this normal behavior during the transition where they have to take themselves offline?

2) I noticed that after my LocalMongo became the primary again, VM1b (always stays secondary) points to VM1a as it's syncing target and not the newly elected primary.  This seems bad to me but I'm still fairly new to Mongo so I don't know if this is how the algorithm works when determining syncing targets. Since both VM1a and VM1b exist within the VM is Mongo just determining that it's the "path of least resistance" and that's why it's a sync target?

{
    "_id" : 0,
    "name" : "198.162.55.1:27017",
    "health" : 1,
    "state" : 1,
    "stateStr" : "PRIMARY",
    "uptime" : 70,
    "optime" : Timestamp(1410979849, 637),
    "optimeDate" : ISODate("2014-09-17T18:50:49Z"),
    "lastHeartbeat" : ISODate("2014-09-17T18:56:59Z"),
    "lastHeartbeatRecv" : ISODate("2014-09-17T18:56:59Z"),
    "pingMs" : 1,
    "electionTime" : Timestamp(1410980169, 1),
    "electionDate" : ISODate("2014-09-17T18:56:09Z")
},
{
    "_id" : 1,
    "name" : "198.162.55.101:27017",
    "health" : 1,
    "state" : 2,
    "stateStr" : "SECONDARY",
    "uptime" : 4227,
    "optime" : Timestamp(1410979849, 637),
    "optimeDate" : ISODate("2014-09-17T18:50:49Z"),
    "infoMessage" : "syncing to: 198.162.55.1:27017",
    "self" : true
}
{
    "_id" : 2,
    "name" : "198.162.55.101:27018",
    "health" : 1,
    "state" : 2,
    "stateStr" : "SECONDARY",
    "uptime" : 4227,
    "optime" : Timestamp(1410979849, 637),
    "optimeDate" : ISODate("2014-09-17T18:50:49Z"),
    "lastHeartbeat" : ISODate("2014-09-17T18:56:59Z"),
    "lastHeartbeatRecv" : ISODate("2014-09-17T18:57:01Z"),
    "pingMs" : 1,
    "lastHeartbeatMessage" : "syncing to: 198.162.55.101:270

    "syncingTo" : "198.162.55.101:27017"
}

My only concern in this situation is what happens if VM1a goes down right after LocalMongo comes up?  Wouldn't that mean that the only place where data is actually being stored would be on the primary and that it would create a broken link where VM1b (which never went down) wouldn't sync to anything?

Thanks!


texts: Oracle expdp puts INDEXES to LOGGING mode

texts:
I've created a table in schema LARRY

create global temporary table TT1 (N number);
create index TT_IX1 on TT1 (N);

then export it, and try to import in schema LARRY2:

expdp LARRY/LARRY   directory=DATA_PUMP_DIR dumpfile=01.dmp logfile=01.dmp.exp tables=TT1
impdp LARRY2/LARRY2 directory=DATA_PUMP_DIR dumpfile=01.dmp logfile=01.dmp.imp remap_schema=LARRY:LARRY2 transform=DISABLE_ARCHIVE_LOGGING:Y,oid:N

But I've got an error in Oracle 12c:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX:"LARRY2"."TT_IX1" failed to create with error:
ORA-14451: unsupported feature with temporary table
Failing sql is:
 ALTER INDEX "LARRY2"."TT_IX1"  LOGGING

Also I've checked

SELECT force_logging FROM v$database;

It gives NO.

I tried to search for this problem in the Internet but didn't find the answer. I'm curious why do Oracle trying to put INDEXES (and indexes of TEMPORARY tables too) to the LOGGING mode? And how to fix it?


texts: Update a dynamic number of rows with an amount proportionally

texts:
Since my English are not perfect I will try explaining with an example

I have tableA that I want to update the Amount

groupID reservationID  Date        Amount
26      1              2018-06-01  NULL
26      2              2018-06-01  NULL
26      3              2018-06-01  NULL
27      4              2018-06-04  NULL
27      5              2018-06-04  NULL

And tableB that holds the total amount per Group

groupID  Date        Amount
26       2018-06-01      60
27       2018-06-04     100

I trying to find a way that

groupID 26 amount of 60 can updated the first table
groupID26 rows with 20 each (60 / 3)
groupID 27 amount of 100 can update the first table groupID27 rows with
50 each (100/2)

In other words a query that will be smart enough to divide the total amount in tableB  the in the number of rows in tableA where:

tableA.Date = tableB.Date
AND tableA.groupId = tableB.groupID

Thanx

Here is a sample code ddl

DECLARE @tableA TABLE
(group_id       numeric(10,0)   null,
 reservation_id numeric(10,0)   null,
 ondate         datetime        null,
 amount         numeric(10,2)   null)

DECLARE @tableB TABLE
(group_id       numeric(10,0)   null,
 ondate         datetime        null,
 amount         numeric(10,2)   null)

INSERT @tableA
SELECT 26,1,'2018-06-01',NULL
INSERT @tableA
SELECT 26,2,'2018-06-01',  NULL
INSERT @tableA
SELECT 26,3,'2018-06-01',  NULL
INSERT @tableA
SELECT 27,4,'2018-06-04',  NULL
INSERT @tableA
SELECT 27,5,'2018-06-04',  NULL

INSERT @tableB
SELECT 26,'2018-06-01',60
INSERT @tableB
SELECT 27,'2018-06-04',100


texts: Recommended distributor architecture for transactional replication?

texts:
We are planning to create a Transactional replication on 2 nodes of SQL Server 2008 R2, each node will be a publisher and a subscriber.

What is the best practice in order to have this scenario done? Microsoft always recommends that the distributor should be independent from the publisher or the subscriber, so it is obvious that a 3rd node is to be involved in the scenario. But, as each node will be at the same time publisher and subscriber, can the same (3rd node) be the distributor for the 2 publishers? Should I create 2 distributors, or can it still be on the same architecture, using only 2 nodes, each one acting as distributor as well as publisher?


texts: Should I stop my PostgreSQL server before reboot?

texts:
Every week I do an apt-get upgrade and dist-upgrade on the server where my PostgreSQL is running. After that, I do a sudo reboot now.

Should I stop the PostgreSQL service before this reboot?


texts: Autocomplete in Intellisense SSMS 2017. Where'd it go?

texts:
I recently got a new dev box, got my Visual Studio installed.  Got my SSMS installed.  Started writing some procs and tables... no intellisense help.
Whaaa??
All the boxes are ticked.  I'm in the right DB. I get the red squigglies if the table doesn't exist, but I have no flyout for object name completion.  Surely I messed something up because my googling tells me 2017 has much improved intellisense capabilities.
Nov 06 2018 Update
I removed my own answer saying that caching had been the culprit, because it still seems super flaky on what will and won't allow auto complete.  I can hop back to an earlier version of SMSS and get the kind of of snappy results I was used to until this version.
I've started using Azure Data Studio for day to day stuff.  It's super snappy. Lots of QoL improvements.  Not even close to enough features for server management, but lots better for general proc writing and data analysis.


texts: Mimicking cascading foreign key referencing system table

texts:
I'd like to create a table to store some additional metadata about tables, but I'm running into a number of roadblocks since foreign keys and triggers cannot be used with system tables.

On INSERT it seems easy enough to create a trigger on the metadata table that checks for a matching row in pg_class, but I'm struggling to come up with a way to delete the row when the table is dropped.

I've looked into event triggers, but I can't find a way to determine the oid of the dropped table. Based on the information on the event trigger page and here https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER it doesn't seem like there's any data provided about the object that the event affects.

Is there any way of ensuring maintained integrity between my metadata table and the referenced rows in pg_class?

edit:
I found https://www.postgresql.org/docs/current/functions-event-triggers.html so it looks like I should be able to get the oid from an event trigger.

Here's what I ended up using

CREATE OR REPLACE FUNCTION delete_table_metadata()
RETURNS event_trigger AS $$
DECLARE
    table_oid oid;
    schema_nm varchar;
BEGIN
    FOR table_oid, schema_nm IN SELECT objid, schema_name FROM pg_event_trigger_dropped_objects()
    LOOP
        IF schema_nm = 'myschema' THEN
            DELETE FROM table_meta WHERE table_meta_id = table_oid;
        END IF;
    END LOOP;
END
$$
LANGUAGE plpgsql;

People seem to be curious why I want to do this.  I have a need for dynamically generated tables and associated application data models.  The metadata I need to store isn't actually about the tables per se, it's for the application models.  Since the models aren't hardcoded I need a way to persistently store some information about them. There's an argument to be made that I should instead control the creation and dropping of this data from the application rather than the database, but this is more foolproof and I can foresee times where the tables might be manipulated directly from the database rather than the application during admin work.


texts: Is it possible to optimize a range query so an explain statement would state "using index" instead of "using index condition"?

texts:
I have a very large table where specific queries made by my application are timing out.  The table looks something like this:

LargeTable
------------
Id [BIGINT(20)]
UtcTimestamp [BIGINT(20)]
Pivot [BIGINT(20)]
Details [Text]

I have an index over 1) Pivot 2) UtcTimestamp.

Now, the query I'm running looks something like this:

SELECT Id, Pivot, UtcTimestamp, Details FROM
LargeTable WHERE
UtcTimestamp>=1380067200 AND UtcTimestamp<=1380153599 AND Pivot=2;

Since there is an index over the pivot and timestamp, this query should be blazing fast, but it doesn't appear to be.  I ran an explain on this query expecting "using index", but instead I get "using index condition". Here is the explain:

+----+-------------+-------------+-------+------------------------+-----------------------+---------+------+------+-----------------------+
| id | select_type | table       | type  | possible_keys          | key                   | key_len | ref  | rows | Extra                 |
+----+-------------+-------------+-------+------------------------+-----------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | LargeTable  | range | pivot_and_timestamp_id | pivot_and_timestamp_id| 18      | NULL |  963 | Using index condition |
+----+-------------+-------------+-------+------------------------+-----------------------+---------+------+------+-----------------------+

It's definitely selecting the right index, but why is this query not as fast as it should be?

The MySQL server version is 5.6.14.


texts: Will Postgresql statements execute synchronously if autocommit=true?

texts:
If a single application thread sends a series of INSERT statements and autocommit=true, will the statements be executed synchronously on the server?

I read that on the postgres server side, "there is one client process connected to exactly one server process",  but haven't found much more beyond that. I need synchronous execution to satisfy foreign key relationships, but I suspect the server (9.6) may run the statements concurrently.


texts: Looking for simple contains method when searching text in postgresql

texts:
Im new to the PostgreSQL world so I do apologize in advance for not knowing if there is an obvious answer to these questions.

Basically I'm looking for two things. First a simple "contains" based search where the python equivalent would be something like:

def find_all_containing( input_array=[], target ):
    output=[]
    for i in input_array:
        if target in i:
            output.append(i)
    return(output)

In the database that I'm currently building, logically, I'm inclined to believe that the SQL command would look a bit like this if I were trying to show all of the snippet titles that contained 'evil' in the description.

SELECT title FROM snippets WHERE 'evil' in description;
/* or */
SELECT title FROM snippets WHERE description CONTAINS 'evil';

I wrote an inline python function that returns a 'TRUE' or 'FALSE' to make the following work.

/* contains function here */
CREATE FUNCTION contains (input_text text, target text) RETURNS text AS $$
if target in input_text:
  return("TRUE")
return("FALSE")
$$ LANGUAGE plpythonu;

/* working command of the script */
SELECT title FROM snippets WHERE contains(description,'Evil')='TRUE';

This works and should stop me from asking, but I would really prefer to learn how the SQL world would solve this because the whole point of learning SQL was to learn how to work the database instead of just finding another way to mold python to recreate a nosql feel.

The second thing is, this isn't the first time I've thrown together some python snippet to bring more of a pythonic mentality to the database. For those who are well dug into the SQL world am I missing out on something by doing this? I get it for speed but if speed is ever an issue, I would assume that I just need to rewrite the implementation in C.

Keeping it as simple as I can has worked out pretty well so far and this works as is for now. But, when I find myself embedding another language into the code for nearly every solution, thats when I start asking if I'm missing a bigger picture.


texts: Retrieving cached plans fails due to cached objects of database Mirror

texts:
I'm trying to retrieve cached execution plans for a specific database object, but this fails because of cached plans which belong to a mirrored database in MIRROR state.

The below query, without any additional WHERE clauses is failing with the following SQL error:

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

SELECT  *
FROM    sys.dm_exec_query_stats AS deqs
CROSS APPLY     sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

I suspect what happens is the sys.dm_exec_query_plan function is first trying to parse all the plan handles from the cache, but fails on cached objects for a mirrored database.

Does anyone know if there are any ways to get around this, T-SQL wise?

Off course I could execute DBCC FREEPROCCACHE to clear the cache, however I'm hoping for other solutions. I'm a bit amazed this function is not discarding any objects from mirrored databases while trying to parse the plans.


texts: What is the recommended method to move databases?

texts:
I want to know the recommended method to move a database and data from dev to pre-prod to production in SQL Server. The databases are originally created in MS Access so some upsizing to SQL Server will be needed

The 3 options I've read about: (I'm sure there are others)

Use Detach and Attach - I've been led to believe this doesn't give a clean build and may cause issues but is easy to do as just moving/copying the .mdf, .ldf files
Script the database and objects into each environment - this provides a clean build but is more time consuming
Do a back-up/ restore - takes longer but appears to be a favored method.

Any advice most welcome.


texts: Does it make sense to define a new "Data type"? Is there a way to do so?

texts:
I'm basically new to SQL, and I was making my own project when this question came to my mind.
I'm making a database that has an entity where an attribute would be a "number" and a "year".
In many programming languages one would create a new object or data type that takes both the number and the year. (say a tuple with INT, YEAR)
I wanted to know if this makes sense in SQL or I should just go with an INT and YEAR attribute.
To put more details into the project, I'm creating a DB for my mom who runs a business that goes with "campaings" (the INT value) directly associated with the "Year", so one would put the campaign 09 for year 2020, and so on.
I wanted to create a new Data Type for the attribute so then I don't have multiple "campaign 09" with different years.
I hope my question is clear, thanks!
EDIT: Read the comments for the answer.


texts: regex character class :print: in postgres

texts:
I am trying to replicate an Oracle check constraint that prevents non-printing characters in postgres, and I am stumped. I think postgres may be treating :print: and :ascii: as synonymys?!?

select REGEXP_REPLACE('bla', '[^[:print:]]', '[X]','g') ;
 regexp_replace
----------------
 bla
(1 row)

select REGEXP_REPLACE('bla'||chr(10)||'bla', '[^[:print:]]', '[X]','g') ;
 regexp_replace
----------------
 bla[X]bla
(1 row)

select REGEXP_REPLACE('Ҕ', '[^[:print:]]', '[X]','g') ;
 regexp_replace
----------------
 [X]
(1 row)

select REGEXP_REPLACE('ñino', '[^[:print:]]', '[X]','g') ;
 regexp_replace
----------------
 [X]ino
(1 row)

Why would Ҕ and ñ be caught in that?

Any guidance is appreciated.


texts: Is there any prevalent logging packages in PL/SQL similar to java and .Net counterparts

texts:
Normally in java or C# , I would use a following construct when try and catch errors.

Bad Exception Handling in C# and Java

try
{
  // do something
}
catch(Exception ex)
{
}

Good Exception Handling in Java and C#

try
{

}
catch(ArgumentException ex)
{
  log(ex);
  // java throw
  throw ex;
 // in C#, throw only, is better since it keeps stack trace
 throw;

}
catch(Exception ex)
{
  // this is catch ALL others block
  log(ex);
  // java throw
  throw ex;
 // in C#, throw only, is better since it keeps stack trace
 throw;
}

In C# and java , I would use logging libraries.
java : log4j,
see others
https://stackoverflow.com/questions/3042450/what-log4j-alternative-logging-libraries-are-available

.Net : log4net, Nlog etc

In PL/SQL

Bad Exception Handling

BEGIN
-- Do Something
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN
   null;
   WHEN OTHERS THEN
   null;
END;

Good Exception Handling

BEGIN
-- Do Something
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN
   LOG
   RAISE

   WHEN OTHERS THEN
   LOG
   RAISE
END;

a simple google search brings log4plsql package : http://log4plsql.sourceforge.net/

Are there any other libraries?
Are they prevalent in PL/SQL programming?

Oracle Documentation for Error Handling does not give hints about logging.

It gives following sentence and uses dbms_output.put_line for logging:

  "Sometimes, you want to reraise an exception, that is, handle it
  locally, then pass it to an enclosing block. For example, you might
  want to roll back a transaction in the current block, then log the
  error in an enclosing block."


texts: Oracle: How to split records across days

texts:
I have data with two timestamp columns startdate and enddate. The data is produced continuously everyday and some records are such that the startdate is one day and the enddate is the nextday as it passes midnight. For example:

Original Record:

No.   StartDate                        EndDate
1    2014-01-10 20:50:04.45           2014-01-11 01:15:25.67

Transformed Record:

No.   StartDate                        EndDate
1     2014-01-10 20:50:04.45          2014-01-10 23:59:59.99
1     2014-01-11 00:00:00.00          2014-01-11 01:15:25.67

How do I do this in Oracle?


texts: Using multiple disks for large InnoDB database

texts:
I am in the process of setting up a large MySQL/InnoDB database on a server that has multiple physical hard drives.
My current configuration uses innodb_file_per_table, a setting I would like to keep.

Now, my database consists of hundreds of "small" tables, most a couple of megs to a few gigs in size.
All in all however, the database exceeds the size of the individual disks, so I would like to split the .ibd files across multiple drives automatically, preferably with the option to add more disks as needed - I would very much prefer not to be forced to manage these myself.

Are there options in mysql to have it split the files across drives or do I have to use something like LVM/software RAID0 to achieve this?


texts: Creating a helper UDF or stored procedure and limiting external access

texts:
I'm looking for a recommended best practice for dividing functions and stored procedures into a 'public API' and 'private implementation details'.

I'm working on a security system and I want client apps to only access a handful of top-level functions. For example:

CanUserEditObject(@personId, @objectId) -- called to see if an edit is possible
EditableBy(@personId) -- returns a list of editable object ids

These form the API I want client apps to use.

To implement that, I need some other functions which I don't want client apps to call;

GetLocationsAdministeredBy(@personId)
IsUserSysadmin(@personId)

Client apps have no need to call them, and I don't promise to support them going forward -- they're just an implementation detail. So I'd like to hide them. Ideally, any attempt to call it by a client application will fail. Ideally ideally, I'd be able to call them in SQL Server Management Studio to develop them.

Basically, this is inspired by the OO principle of information hiding and language constructs like public and private access modifiers in C++/Java/C#/etc.

I don't need it to be a guaranteed, hard-security kind of solution. I basically trust my clients here, but I just don't want some plucky developer to spot a function I've written and use it, then complain when I rewrite it later.

This is mainly for SQL Server 2014, but I'm interested if there are any 'standard' SQL techniques.

Also, we have a team where some of us are better at SQL than others. The SQL people would add their own objects, but they wouldn't use my 'private' functions. The other devs might discover existing functions rather than add them. I'm looking for a way to differentiate so that no-one accidentally relies on an unsupported function. Something as simple as a naming convention would do but I am interested how other people muddle through with an issue that most non-SQL languages have good support for.


texts:
How do I enable Parallel Data Warehouse on my Microsoft SQL Server. I am trying to run this SQL statement below and received this error.

ERROR:

Msg 11305, Level 15, State 10, Line 2
The Parallel Data Warehouse (PDW) features are not enabled.

CODE:

select A.gender, A.firstname, A.weight,
       SUM(a.weight) over (partition by a.gender
                           order by a.weight) as 'New Column'
 from ChildStat A
 order by a.gender, a.weight;


texts:
Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC.

I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference in performance when using EXEC vs SP_EXECUTESQL.


texts: How to avoid duplications using a function

texts:
I am using this function in a bulk insert to avoid duplication of url paths.

CREATE OR REPLACE FUNCTION "univ"."gc_landing"(IN _name text, OUT landing_id int4)
  RETURNS "int4"
AS $BODY$
DECLARE
  landing_name TEXT;
BEGIN
landing_name := _name;
LOOP
BEGIN
WITH sel AS (
  SELECT id
  FROM   univ.landings
  WHERE  name = landing_name
  )
, ins AS (
  INSERT INTO univ.landings (name)
  SELECT landing_name
  WHERE  NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
  )
SELECT id
FROM   sel NATURAL FULL OUTER JOIN ins
INTO   landing_id;

EXCEPTION WHEN UNIQUE_VIOLATION THEN
    RAISE NOTICE 'It actually happened!';
END;

EXIT WHEN landing_id IS NOT NULL;
END LOOP;
END$BODY$
LANGUAGE plpgsql;

A path looks like this:

%2faccessories%2fliners.html%26sa%3du%26ei%3df1n2voo3fjk1acowgnao%26ved%3d0cesq9qewbg%26usg%3dafqjcnhvnzccoijbs0zvswwjxexhyd-7xw

My table:

 id        | integer                     | not null default nextval('univ.seq_landings_id'::regclass) | plain
 name      | text                        | not null                                                   | extended
 created_at| timestamp without time zone | not null default now()                                     | plain
Indexes:
"landings_pkey" PRIMARY KEY, btree (id)
"uniq_index_landings_on_name" UNIQUE, btree (name)

I have an unique index on name, but the function is still duplicating. How can I make sure that there will be no duplications?


texts: Postgres 9.1.6 Error: index contains unexpected zero page at block 0

texts:
I have setup streaming replication on Postgres 9.1.6 running on a debian server and it's going on fine.

When I try to run a query on the replica DB I get the error below:

ERROR:  index "tbl_cust_id_idx" contains unexpected zero page at block 0
HINT:  Please REINDEX it.

What might be causing this error?


texts: EXPORT specific columns to INSERT statements

texts:
Is there a way to export specific columns and a fixed number of rows from a table to INSERT statements?
I am trying to generate these for unit testing based on a large production table so using something like "Generate Scripts" in SQL Server Management Studio is out of the question.
There are a few tools that might be able to do this, but something so trivial in MySQL should not be that complex in SQL Server (at least I would hope not).


texts: SQL 2016 Always Encrypted, options appear in one environment, not the other

texts:
We have two different environments that are replicas of each other - DEV/STG and PROD.  We recently upgraded both environments to SQL Server 2016 using the same upgrade process (we're on AWS, so it was as simple as spinning up a new instance), but are unable to find any SQL Always Encrypted options, configurations, menus, or wizards in our DEV/STG environment (single server, two instances).  I checked our PROD server and there they are, Always Encrypted Key, Encrypt Columns, etc.

Is there a configuration or module I need to install in our DEV/STG environment to allow it to utilize the Always Encrypted functionality?


texts: How to append datetime in filename in postgresql

texts:
How to append datetime in filename in below command:

COPY (select  query) To '/tmp/test.csv' DELIMITER ',' CSV HEADER;

I want my filename like test_date_tim.csv.


texts: Cannot connect Power BI Desktop to Azure SQL!

texts:
I'm trying to connect Power BI Desktop to Azure SQL, but I get "We couldn't authenticate with the credentials provided. Please try again."

it works fine if I try from Microsoft SQL Server Management Studio I can connect with no problems with the same username/password

what I'm missing?


texts: Usefulness of Foreign Key WITHOUT reference option

texts:
Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE

I think this will avoid INSERT of a value which does not exist in the parent table. Is there any other advantage?
Does this FOREIGN KEY assist a better JOIN on the corresponding key?
Without FOREIGN KEY a query merely deals with an independent table. Will FOREIGN KEY slow down the mysql performance (even trivial) due to the link with another table? e.g. in SELECT and INSERT.


texts:
I am but a modest SQL DBA. I would like to request your advice on the design of the central part of our new proposed database.

Brent Ozar had something to say about this:
Building A SQL table horizontally VS vertically.
Please can either Brent or someone with a good deal of experience give me their feedback or experience in this matter?

Some details about the central part of our database:

Will be replicated using Transactional Replication.
Is CDC enabled.
Is fact based (not transactional based) i.e. like the concept of Products.
We want to be able to add new items easily as and when the requirement arises. If we go with the horizontal approach, then there will always be the requirement to add either additional tables or columns to existing tables.
We could partition all of our Entity Attribute Value tables based on a certain criteria to reduce the workload on the accessing of data?

I have worked with a Metadata based system for only one part of the (ever changing) system. The advantage of this was such that the configuration of data and workflows did not require extra development every time we needed to configure certain details required. Also, I had ensured that the data was easily accessible through clever use of indexes and partitions.

I really appreciate and respect any advice you have to offer regarding this issue (and regarding any misconceptions I might have).


texts: On reboot listeners don't start but restarting the service after will start them

texts:
when I reboot ubuntu the listener service comes up but it doesn't seem to have any listeners

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-JUL-2014 15:34:43

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dex-ci.escalation-point.com)(PORT=1522)))
 STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                24-JUL-2014 15:33:58
Uptime                    0 days 0 hr. 0 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ip-10-113-43-249/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully

after a /etc/init.d/oracle-xe restart

 lsnrctl status | sed -e 's/^/     /'

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-JUL-2014 15:42:15

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dex-ci.escalation-point.com)(PORT=1522)))
 STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
 Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
 Start Date                24-JUL-2014 15:41:24
 Uptime                    0 days 0 hr. 0 min. 50 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Default Service           XE
 Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
 Listener Log File         /u01/app/oracle/diag/tnslsnr/dex-ci/listener/alert/log.xml
 Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8888))(Presentation=HTTP)(Session=RAW))
 Services Summary...
 Service "XE" has 1 instance(s).
   Instance "XE", status READY, has 1 handler(s) for this service...
 Service "XEXDB" has 1 instance(s).
   Instance "XE", status READY, has 1 handler(s) for this service...
 The command completed successfully

What this tells me is that my configuration is fine, so it's something going on a boot, maybe a race condition... I have looked at the logs and was unable to find anything interesting. What kinds of things could cause these services to not come up on a restart?


texts:
I used csvsql to generate CREATE statements, and it looks like it assumed trimmed fields though the data has some spaces padding the field:

ERROR:  value too long for type character varying(11)
CONTEXT:  COPY example, line 1314, column state: " South Korea"

I'm not entirely sure why the data has padding -- it definitely shouldn't but about half the values in the state column have some padding around them.

I could use ALTER to make more space in the field, and then UPDATE example SET state=trim(state); after I COPY the csv in, I'm wondering if there's a clean way to just trim the padding as I import the data?


texts: Hyphen in my computer's name causing T-SQL error

texts:
I’m a developer, not a DBA (which is showing, I’m afraid). I am trying to run Report Builder 3.0 with SQL Server 2014 Express on my home computer (named John-PC) and cannot run my reports.

I accidentally created a user/login combo of user = John-PC and login = John-PC\John. When I try to delete the entry with:

Drop Login John-PC\John

I get an error:

  Incorrect syntax near '-'.

I think the problem is the hyphen in my computer's name.

Is there a way to get around the syntax error?
Is there another way to change or drop the user (I tried dropping from sys.server_principals but received an error that I couldn’t make ad hoc changes).
Can I give Report Builder a new user/login name somehow?
If none of the above, can I change the name of my computer to John_PC or will that create a host of other problems that I can’t even imagine?


texts: Speed of MySQL query for the most recent 800 entries in chronological order

texts:
I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24x7). My column names are:

ip_address varchar(16),
status varchar(7),
timestamp datetime

Been running it for several weeks and it has grown (10,581,421+ rows). I run a query to return the most recent 800 results on a specific IP address:

SELECT *
FROM (
    SELECT Device_ip, Status, timestamp
    FROM ping_results
    where Device_ip = '192.168.1.1'
    order by timestamp desc
    LIMIT 800
) SUB ORDER BY timestamp asc;

It takes 10+ seconds to return the results. Is there anything I can do differently to speed this up?


texts:
I have a 10 million record table in a MySQL 5.5 InnoDB. It's running in a 16GB RAM server with good CPU and fast HD. When I run the following query

SELECT DISTINCT knowledge_id,
                tag_order,
                tag_weight,
                total_title_direct_words,
                total_title_parenthesis_words,
                tag_level
FROM   knowledge_tags
WHERE  ( tag_text = 'washington' )
       AND ( tag_level < 10 );

It will take around 9 seconds (first run) locally (localhost). The result count is 640. There is a Btree index for the fields 'tag_text, tag_level'. This query time isn't acceptable for what i want. What can I do?

Here is the EXPLAIN result:

1   SIMPLE  knowledge_tags  range   tags_by_word_text   tags_by_word_text   308 [null]  344 Using index condition; Using temporary

And the CREATE TABLE statement:

CREATE TABLE `knowledge_tags` (
  `tag_id` int(11) NOT NULL AUTO_INCREMENT,
  `knowledge_id` int(11) DEFAULT NULL,
  `dictionary_word_id` int(11) DEFAULT NULL,
  `tag_text` varchar(100) DEFAULT NULL,
  `tag_order` int(11) DEFAULT NULL,
  `tag_level` int(11) DEFAULT NULL,
  `knowledge_family_id` int(11) DEFAULT NULL,
  `tag_weight` double DEFAULT '1',
  `total_title_direct_words` int(11) DEFAULT NULL,
  `total_title_parenthesis_words` int(11) DEFAULT NULL,
  PRIMARY KEY (`tag_id`),
  KEY `tags_by_knowledge_id` (`knowledge_id`),
  KEY `tags_by_word_text` (`tag_text`,`tag_level`) USING BTREE,
  KEY `tags_by_family_dictionary` (`knowledge_family_id`,`dictionary_word_id`,`tag_level`) USING BTREE,
  KEY `tags_by_family_word_text` (`knowledge_family_id`,`tag_text`,`tag_level`) USING BTREE,
  KEY `tags_by_dictionary` (`dictionary_word_id`,`tag_level`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3316 DEFAULT CHARSET=utf8;


texts: How to deal with refrencing a foreign key to two tables?

texts:
I have an interesting situation where I am making a data base off someone else's design  that has to be as rule complaint as possible.

The issue I have is with the section shown above as it is a sub-class super class situation with a mandatory, or where you are meant to have 2 table, however the phone being multi valued adds another table with issues.
At the moment I have:
PersonalClient (ClientID, street, postcode, fName, lName, title, driversNum)
Primary Key ClientID

CompanyClient(ClientID, street, postcode, cName
Primary Key ClientID
Foreign key Representative_clientID references PersonalClient(ClientID)

PersonalPhone (ClientID, phone)
Primary Key ClientID, phone
Foreign Key ClientID references PersonalClient(ClientID)

CompanyPhone (ClientID, phone)
Primary Key ClientID, phone
Foreign Key ClientID references CompanyClient(ClientID)

However I need to turn the phone tables into one table, this is where the issues is. How do I tell the phone table which client table to look at for the foreign key? I understand that a trigger or a create table as select is probably part of the answer. I am not adverse to creating additional tables that may store additional data but there must only be one 'phone table' with both the client id and phone number (one of the rules is as little as possible data duplication). I also can't use 2 columns and a view to merge the columns as is too hard to implement across the database. In addition it would be best if the solution worked across the whole data base and was not specific to the phone table.
I know this is a lot Thanks.


texts:
In SQL Server, I am using the following command to see blocked sessions:

select * from sys.sysprocesses where blocked>0

This is super. The problem is I need to see what block sessions there were in the last 30 minutes.  This isn't easy because, the blocked sessions can be come unblocked.can go a so they won't be returned by :

select * from sys.sysprocesses where blocked>0

Any ideas?

Thanks