texts: When does it make sense to duplicate data for querying

texts:
I have the following table design.

an Event table, with helper tables to hold the event meta data.

each event row has an event type column (regular_event, recurring_route, timed_messages, and more.)

each event type has its own tables to define different things regarding the event - including users. The thing is that it might be referenced directly by a many to many table (users_regular_events) or by a third table or forth table (events -> recurring_routes -> recurring_routes_stations -> recurring_stations -> recurring_stations_users -> users).

would it make sense to create a users_events table that duplicates relations between certain events? because, currently if I want to show each user his events I'll have to join multiple tables. Maybe I need to rethink my entire design?


texts:
I want to get "OrderNumber" field value from below mentioned json string. Pleas suggest best way to get the same

DECLARE @JSON NVARCHAR(MAX)=

  '{"ErpOrderResponses":[{"OrderSubmitStatus":"Ok","TransactionId":"{5EAB3ADC-EB9B-47F7-98B8-1449E2CC0A6B}","OrderNumber":7000027934,"CustomerNumber":80038707,"Description":"Request Successful","ResponseCode":"Success","ResponseType":"None","Errors":[]}],"IsErpUnavailable":false,"IsSuccessful":true,"IsPartiallySuccessful":false,"IsValidationFailed":false,"IsFailed":false}'

    DECLARE @OrderNumber BIGINT =
    (SELECT REPLACE(REPLACE(SUBSTRING(@JSON,CHARINDEX('OrderNumber',@JSON)+1,
                                                        CHARINDEX(',',@JSON,
                                                                        CHARINDEX('OrderNumber',@JSON)+1
                                                                )-CHARINDEX('OrderNumber',@JSON)
                                                        )
                                                        ,'RDERNUMBER":',''),',','') )
    --SELECT CAST( @OrderNumber AS BIGINT)

    SELECT  @OrderNumber


texts: Upgrade my server or migrate it to a new server

texts:
I have a two servers, one is application server that runs with Windows Server 2003 and one SQL server runs with SQL 2008 and windows server 2003 OS. I would like to upgrade the both servers' OS to Windows Server 2012 and the SQL server to use SQL 2016.

My understanding is that I can just upgrade the OS and the SQL server, but my IT said we need to buy new servers with new OS and new SQL server version and then migrate from the old to new ones.

Just ask which way is better?

Thank you


texts: Oracle file permissions expdp vs dbms_datapump

texts:
We have written our own export function using calls to dbms_datapump. In some systems the export fails due to insufficient file permissions on the chosen directory.
However, using expdp to perform the export to the same directory works.

I'm looking for an explanation for this different behaviour. Does the expdp session run under a different OS user than the database server? And what user would that be?

Edit: We have seen the same kind of difference between file writes using utl_file running in a user session and running in a scheduled job.


texts: Having multiple ResultSets in parallel on same connection.. Will it cause any problem? [Mysql/Postgres]

texts:
I have tried out the following in my local machine using jdbc/mysql:

step 1: conn = getNewConnection
step 2: stmt1 = "new statement" using connection 'conn'
step 3: stmt2 = "new statement" using the same connection 'conn'
step 4: resultSet1 = execute stmt1
step 5: resultSet2 = execute stmt2
step 6: operating on both resultset 1 and 2
step 7: proper closing of all resources

worked fine in my machine. Can I be sure of this behaviour always (Mysql/Postgres with JDBC )? Will there be any issue?


texts: how can I add user who can read all tables in database and have permission to alter only one table

texts:
I am creating a database using SQL server express 2016 for a college project and
need to learn how to add a user whom can have access to all tables, however, only be able to alter one of the tables, update, delete, etc... I'm using sql server
express 2016 management studio


texts: Does auto increment on Primary Key prevent concurent inserts?

texts:
I have a Sql Server logging table, that continuously is being inserted into, I want to revise the design of this table.

Currently, we are using a GUID as the PK,  because the software can generate this and we just insert it, and hopefully, multiple inserts won't block each other. However, UniqueId is not a very efficient PK for a table and our indexes are very large and fragmented.

If I change this column to a long with auto-increment enabled.  Will this become a concurrent insert bottleneck?

I do not need my primary key to be in any sort of order, Just a unique id for that row?


texts: SQL Server 2012: stored procedures permissions

texts: Is it possible to control permissions on a single stored procedure level in SQL Server 2012? It seems that SQL Server Manager doesn't expose such functionality via GUI. Can somebody advice me how to view/set permission on a single stored procedure level?  In particular I'm interested in removing "Connect" right on one procedure for specified account while leaving Connect rights for this account on all the others procedures. Is it possible?


texts:
I have an application that stores XML as XMLType in a table. This is the first time I'm using XMLDB and XMLType features, and was wondering if I could use the results from an XQuery FLWOR expression in a "relational" way.

Considering the following example XML:

<PerfInfo TimeStamp="2014-11-23 17:13:04" GUID="5d4a9a3f-5a1c-478c-b12f-39e9f33172f4">
    <EventLog Count="408">
        <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-17 00:18:31" Provider="Application Error" ID="1000">Faulting application name: wmiprvse.exe</EventRecord>
        <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-17 00:18:43" Provider="Application Error" ID="1000">Faulting application name: wmiprvse.exe</EventRecord>
        <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-18 16:59:28" Provider="McLogEvent" ID="5051">A thread in process C:\Program Files\Common Files\McAfee\SystemCore\mcshield.exe took longer than 90000 ms to complete </EventRecord>
    <EventLog/>
<PerfInfo/>

You'll probably notice that these are extracts from the Windows Event Log.

These results (the actual XML files are, of course, way bigger and contain a lot of other metrics, but the principle remains the same) are stored in following table:

CREATE TABLE SPERF_XML_RAW
(
    "ID"                NUMBER              NOT NULL,
    "GUID"              CHAR(36 CHAR)       NOT NULL,
    "HOSTNAME"          VARCHAR2(16 CHAR)   NOT NULL,
    "USERNAME"          VARCHAR2(16 CHAR)   NOT NULL,
    "DATA_XML"          PUBLIC.XMLTYPE      NOT NULL,
    "DATE_CREATED"      TIMESTAMP(6)        NOT NULL,
    "DATE_INSERTED"     TIMESTAMP(6)        DEFAULT SYSDATE NOT NULL,
    CONSTRAINT SPERF_XML_RAW_PK PRIMARY KEY(ID)
)

I can use XQuery to, for example, query all the Provider names of an Event Record in the different XML documents, like this:

SELECT ID, GUID,
   XMLQuery(
       'for $i in /PerfInfo/EventLog/EventRecord
       return $i/@Provider'
       PASSING DATA_XML RETURNING CONTENT
   ) "DATA_THINGS"
FROM SPERF_XML_RAW;

And the results would be along these lines:

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application ErrorApplication ErrorMcLogEvent

However, the results I would like to have is this:

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    McLogEvent

Or, when querying multiple documents:

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    McLogEvent
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    ESENT
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    Microsoft-Windows-Search
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    McLogEvent
3   cd1054b1-1fe0-4842-a9ed-3f1b3c09ea0f    Application Hang
4   6c95827b-8fba-42d7-b34e-fec0b9e9606a    Group Policy
4   6c95827b-8fba-42d7-b34e-fec0b9e9606a    Microsoft-Restart-Manager

In other words, I want to be able to query my XML document in such a way that each selected Node returns as a Row. Is there a way to achieve this using XMLDB and Oracle 11.2g?

P.S.: In an actual query I would of course retrieve more than just the provider, this is just an example query. I'm looking to build different views into the document to explore the different metrics contained in the documents


texts: Why does my ORDER BY sort two tables before the EXCEPT (slow) and not after (fast)?

texts:
SQL server 2008 R2 query optimizer puzzle

We have two tables, both containing 9 million rows. 70.000 rows are different, the others are the same.

This is fast, 13 seconds,

select * from bigtable1
except select * from similar_bigtable2

This sorts the output and is also fast, 13 seconds as well,

select * into #q from bigtable1
except select * from similar_bigtable2
select * from #q order by sort_column

While this is enormously slow:

;with q as (
    select * from bigtable1
    except select * from similar_bigtable2
)
select * from q order by sort_column

And even a "trick" that I sometimes use to hint SQL Server that it needs to precalculate a certain part of the query before it moves on, doesn't work and results in slow query as well:

;with q as (
    select top 100 percent * from bigtable1
    except select * from similar_bigtable2
)
select * from q order by sort_column

Looking at the query plans the reason is not hard to find:

SQL Server places two sorts of 9 million rows before the hashmatch, while I would prefer it to have added only one sort of 70.000 rows after the hashmatch.

So the question: how can I instruct the query optimizer to do that?


texts:
I'm not an IT pro, but a web developer that happens to have his own blog.
The blog is hosted on a private VM on which I have both IIS and SQL Server.

Sometime my blog, which is not super busy stops responding to requests, and today I did a bit more of troubleshooting and logging via remote desktop I found that every now and then the CPU acts weird and does the following.

It happens for a certain period of time and then goes back to normal. And after a while it starts again. I really have not clue of what might cause that. Can someone help me solve that nasty problem?
Thank you


texts: how to temporarily change the sql server settings in order to do a task and when finished revert back?

texts:
when I run the script below all at once I get this error message:

  Msg 15281, Level 16, State 1, Line 58 SQL Server blocked access to
  STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed
  Queries' because this component is turned off as part of the security
  configuration for this server. A system administrator can enable the
  use of 'Ad Hoc Distributed Queries' by using sp_configure. For more
  information about enabling 'Ad Hoc Distributed Queries', search for
  'Ad Hoc Distributed Queries' in SQL Server Books Online.

But when I run this first and then run the full script then it is all fine.

exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

The impression that I got is that sql-server will look at the configured values in memory, and does not seem to realise that they have changed.

How can I alter this behaviour?

here is the full script:

    SET NOCOUNT ON;

    declare @prevAdvancedOptions int
    declare @prevXpCmdshell int
    declare @adhocdistque  int

-------------------------------------------------------------------------------------------

--SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'
-- because this component is turned off as part of the security configuration for this server.

--A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.
--For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
select @adhocdistque = cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'

PRINT @prevAdvancedOptions
PRINT @prevXpCmdshell
print @adhocdistque

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 1
    reconfigure
end

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 1
    reconfigure
end

if (@adhocdistque  = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
end

/* -----------------------------------------------------------------  do work - begin */

SELECT  *
      FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
      , 'set fmtonly off exec msdb.dbo.sp_help_job')
      --where name in
      --        ( 'WebFeed UKProductOffer Offers'
            --   ,'WebFeed USProductOffer Offers'
            --   ,'WebFeed DEProductOffer Offers'
            --   ,'WebFeed ATProductOffer Offers'
            --   ,'WebFeed FRProductOffer Offers'
            --   ,'WebFeed EUProductOffer Offers'
            --   ,'WebFeed AUProductOffer Offers')

/* -----------------------------------------------------------------  do work - end */

---------------------------------------------------
-- restore the settings as they were previously
---------------------------------------------------

if (@prevXpCmdshell = 0)
begin
    exec sp_configure 'xp_cmdshell', 0
    reconfigure
end

if (@prevAdvancedOptions = 0)
begin
    exec sp_configure 'show advanced options', 0
    reconfigure
end

if (@adhocdistque = 0)
begin
    exec sp_configure 'Ad Hoc Distributed Queries', 0
    reconfigure
end


texts: ibd + frm file exist after table dropped

texts:
I am having trouble with my server running out of space
I am currently running a mariadb instance with 27 databases inside.
When i dropped the table i can see that the folder  and ibd and frm files still exist.
The ibd file is huge ~1.5TB
from everything ive read, i need to run an optimize table statement on the table, but it doesnt exist as its already been dropped. So i tried to create the table but got do a recreate + analyze
i have had innodb_file_per_table set to 1 the whole time.
Just wondering is there any safe way to remove the frm and ibd files to free up space on my server.
I have another TB or two of data on the server so running a mysqldump on all 27 dbs is not an option as i dont have space to move them one by one
Any advice?


texts:
its about the following query.

EXPLAIN SELECT phrase, infinitiv
FROM `dict_verbs_all` `t`
INNER JOIN `dict_verbs_all_language` `verbsGerman_c`
ON (`t`.`id`=`verbsGerman_c`.`verb_id`)
INNER JOIN `dict_verbs_german` `c`
ON (`c`.`id`=`verbsGerman_c`.`verb_language_id`)
WHERE (t.phrase LIKE 'spie%' AND t.language=1)
GROUP BY infinitiv
LIMIT 5

It uses all the time filesort

Using where; Using temporary; Using filesort

Here are my columns

CREATE TABLE `dict_verbs_all` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phrase` varchar(30) NOT NULL,
  `language` smallint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uPhraseLanguage` (`phrase`,`language`)
) ENGINE=MyISAM AUTO_INCREMENT=337740 DEFAULT CHARSET=utf8;

CREATE TABLE `dict_verbs_all_language` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `verb_id` int(11) DEFAULT NULL,
  `verb_language_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `U_verb_conjugation` (`verb_id`,`verb_language_id`),
  KEY `verb_id` (`verb_id`),
  KEY `conjugation_id` (`verb_language_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dict_verbs_german` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `infinitiv` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uInfinitiv` (`infinitiv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have an index on all relevant columns but its not beeing used and I don't know why.
I hope you can help me how to eliminate using temporary and using filesort


texts: when to use embedded PL/SQL gateway with Oracle APEX?

texts:
Oracle Application Express (APEX) requires one of the following three options for an HTTP server: (1) Oracle HTTP Server (with mod_plsql), (2) Oracle Application Express Listener, or (3) an embedded PL/SQL gateway).

I understand the embedded PL/SQL gateway shouldn't be used when interfacing with browsers outside the firewall, which my application requires.

But, could I simply put an Apache Tomcat between it and the external browsers?

If so, what would be the advantages/disadvantages of using this solution compared to Oracle Application Express Listener (assuming it similarly has an Apache Tomcat between it and browsers)?


texts: Selecting large data set on windows and linux

texts:
I need to parse a large data set, something like:

mysql_query("SELECT * FROM largeTable")
while($r = fetch()) {
 //> Do something
}

I just need 1 row to be in memory at time, because I need to operate on single row each time.

The fact is that on windows at the end I see memory_get_peak_usage() = 500M while on debian it's 4M

Is something wrong with my windows?

If this questions doesn't belong here please move to stackoverflow thanks!


texts: Intermittent "Login timeout expired" after migrating to VmWare SQL Server

texts:
We are experiencing intermittent Login timeout expired in our application after migration from a physical standalone SQL Server 2008 R2 / W2k8 R2 to a VMWare SQL Server 2014 / Win 2012 R2. It is happening around 5 times per day, so it's difficult to troubleshoot.

Failure Message

  Error: Microsoft SQL Server Native Client 10.0 : Unable to complete
  login process due to delay in prelogin response. Error: Microsoft
  SQL Server Native Client 10.0 : Login timeout expired.

More Details

I am pretty sure the sp_configure are the same in both instances.
We have less cpu compared to the previous server, but Max Worker Threads is OK.
When I was monitoring connections locally on SQL Server it was working fine at the same time I got Login timeout expired from the application.
Configure the remote query timeout will not solve my problem here.
We are using VMXNET3 Virtual NIC drive.


texts: Connect to a SQL Server Mirror from Oracle Database

texts:
I know we can create a database link to connect to a Microsoft SQL Server from Oracle database. But the problem which I faced with is the SQL database which I want to connect to has a mirror fail-over partner which is configured using SQL Server Mirroring.

As mentioned in SQL Server documentations we can use OLEDB, ODBC and ADO providers by some standard keywords to introduce fail-over partner.

What's the required setting/connection string for mirror server to enable the switching to mirror server automatically?

Note: Previously I asked specifically about: Oracle Database Gateway for SQL Server - Connect to a SQL Server Mirroring Session without any success. In this question I'm looking for other available solutions or knowing if it's possible at all in Oracle?


texts: Using a SELECT command to retrieve data on MySQL

texts:
Say I have three tables - pupils, classes and entries.

Table pupils:

pupil_id (primary key)
first_name
middle_name
last_name
email (unique)
password
reg_date

Table classes:

class_id
subject_name
level_of_entry
exam_board

Table entries:

entry_id (primary key)
exam_date
class_id (foreign key)
pupil_id (foreign key)

Now, for example, let me say that a pupil with the name of Jonathan Parker Smith (pupil_id = 1) was taking a Chemistry exam (class_id = 3) which was 'A2' level_of_entry and part of the 'WJEC' exam board. The entry_id would be 5 and let us say that the exam was to be taken on the 25/06/18. There are other pupils like Jonathan who are taking exams in the 'WJEC' board but with other subjects. How would I create a query script (MySQL) that looks at the 'entries' table and lists the names of all pupils, class name and level of entry for the exams entered with the 'WJEC' exam board? I have done some research and that has led me to believe that I should use a SELECT command...

Any help appreciated,
Thanks.


texts: Why is my SP_HELPINDEX showing a lot of indexes?

texts:
Using sp_helpindex My_table I have this list:

But my table has only these indexes:

Why is this happening? I'm using a query to search for "equal indexes" in a particular table. This query is returning a LOT of indexes that I don't even have in the table.

WITH IndexColumns AS (
SELECT '[' + s.Name + '].[' + T.Name + ']' AS TableName,
i.name AS IndexName,  C.name AS ColumnName, i.index_id,ic.index_column_id,
COUNT(*) OVER(PARTITION BY t.OBJECT_ID, i.index_id) AS ColCount
FROM sys.schemas AS s
JOIN sys.tables AS t ON t.schema_id = s.schema_id
JOIN sys.indexes AS i ON I.OBJECT_ID = T.OBJECT_ID
JOIN sys.index_columns AS IC ON  IC.OBJECT_ID = I.OBJECT_ID
AND IC.index_id = I.index_id
JOIN sys.columns AS C ON  C.OBJECT_ID = IC.OBJECT_ID
AND C.column_id = IC.column_id
WHERE IC.is_included_column = 0
)
SELECT DISTINCT a.TableName, a.IndexName AS Index1, b.IndexName AS Index2
FROM IndexColumns AS a
JOIN IndexColumns AS b ON b.TableName = a.TableName
AND b.IndexName <> a.IndexName
AND b.index_column_id = a.index_column_id
AND  b.ColumnName = a.ColumnName
AND a.index_column_id < 3
AND a.index_id < b.index_id
AND a.ColCount <= B.ColCount
ORDER BY a.TableName, a.IndexName;

Where are these indexes coming from?


texts: Capturing datetime of change in SQL Server CDC

texts:
So we've started exploring using change data capture on one of our production databases.  We'd like to know the datetime of each change.  Reading through walkthrough's and tutorials etc it seems that the standard approach is to use the LSN to relate to the cdc.lsn_time_mapping system table.  This approach works but is not very straightforward nor performant when talking about 100's of thousands of changes a day.

In a test environment I made the following adjustment to the change track tables.  I issued an ALTER TABLE statement to add a column to the end called [__ChangeDateTime] and made it's default value GetDate().  The approach seems to work, the change tracking still functions normally, the datetime's are being captured.  But mucking around with system tables makes me a little nervous.

If this isn't a system field that Microsoft added from the beginning they must of had their reasons.  Since they instead opted for the LSN to cdc.lsn_time_mapping approach am I setting myself up for problems by creating my own hack this way?

UPDATE:

Discovered during testing that GetDate() at times isn't precise enough for our needs - multiple changes sharing the same time.  Recommend using sysdatetime() and datetime2 to move the value out to the nanosecond.  Option for 2008+ only obviously.


texts:
I am currently testing a data guard environment.
In the alert.log of the broker I always see the following message after a switchover:

  2019-05-23 08:08:54.716 7fffffff           0 Operation CTL_BOOTSTRAP
  canceled during phase 0, error = ORA-408

The whole starting data guard broker bootstrap log looks as follows:

Broker Configuration File Locations:

  dg_broker_config_file1 =
  "D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1TEST.DAT"

  dg_broker_config_file2 = "D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE
  \DR2TEST.DAT"

2019-05-23 08:08:50.642                      DMON: Attach state object
2019-05-23 08:08:50.645                      DMON: Opening configuration file 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1TEST.DAT' with blocksize 4096
2019-05-23 08:08:50.648                      DMON: Successfully opened configuration file 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1TEST.DAT'
2019-05-23 08:08:50.656                      DMON: Opening configuration file 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR2TEST.DAT' with blocksize 4096
2019-05-23 08:08:50.659                      DMON: Successfully opened configuration file 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR2TEST.DAT'
2019-05-23 08:08:50.671                      DMON: Opening configuration file 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1TEST.DAT' with blocksize 4096
2019-05-23 08:08:50.674                      DMON: Successfully opened configuration file 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1TEST.DAT'
2019-05-23 08:08:50.688                      DMON: rfafoGetLocks reinitializing dubious PMYSHUT lock value block contents: sts=0, flags=0x0, spare1=0x0, spare2=0x0, cksm=0x0, rndm=0x0
2019-05-23 08:08:50.690                      DMON: Broker state reconciled, version = 0, state = 00000000
2019-05-23 08:08:50.692                      DMON: Broker State Initialized
2019-05-23 08:08:50.694                            Version = 1
2019-05-23 08:08:50.696                            State = 00000000
2019-05-23 08:08:50.698                      DMON: Entered
rfm_get_chief_lock() for CTL_BOOTSTRAP, reason 2
2019-05-23 08:08:50.700 7fffffff           0 DMON: Entered
rfm_get_chief_lock() for CTL_BOOTSTRAP, reason 0
2019-05-23 08:08:53.722                      INSV: Opening configuration file 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1TEST.DAT' with blocksize 4096
2019-05-23 08:08:53.727                      INSV: Successfully opened configuration file 'D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1TEST.DAT'
**2019-05-23 08:08:54.716 7fffffff           0 Operation CTL_BOOTSTRAP canceled during phase 0, error = ORA-408**
2019-05-23 08:08:54.719 7fffffff           0 DMON: start task execution: broker initialization
2019-05-23 08:08:54.721                      DMON: Boot configuration (0.0.0), loading from "D:\ORACLE\PRODUCT\12.2.0\DBHOME_1\DATABASE\DR1TEST.DAT"
2019-05-23 08:08:54.756                      DMON: Verifying broker service TEST_DGB
2019-05-23 08:08:54.759                      DMON: Verifying configuration service TEST_CFG
2019-05-23 08:08:54.761                      DMON: Broker service not registered
2019-05-23 08:08:54.764                      DMON: Adding broker service...
2019-05-23 08:08:54.766                      DMON: Configuration service not registered
2019-05-23 08:08:54.769                      Adding configuration service...
2019-05-23 08:08:54.772                      DMON: Registering 2 service/s with listener(s)
2019-05-23 08:08:54.776                      DMON: Executing SQL [ALTER SYSTEM REGISTER]
2019-05-23 08:08:54.780                      SQL [ALTER SYSTEM REGISTER] Executed successfully
05/23/2019 08:08:54

The switchover is successful at all, but I am not sure how to think about the displayed error. After doing some research, I found it might be related to some compatibility issue. But the compatibility is set to 12.2.0.
I am testing with the base version of the oracle database 12.2.0.

I would appreciate, if anyone could help me!


texts: MySQL 8.0 Triggers Current_User not working

texts:
I have this trigger that inserts a row to a table

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

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

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


texts:
I am designing a database for my application, I got issue in implementing the following logic.

A user may be a Vendor and also a Buyer. I have created a table for USER and created an another table called USERTYPE in that, the type had 'VENDOR' and 'BUYER' but I have to know if a user is both 'vendor' and 'buyer' by using a same email_id (User_id). Any idea or suggestion to implement this logic. Thanks in advance.


texts: PAD_INDEX and FILLFACTOR on clustered Identity index

texts:
I am trying to work out what setting for PAD_INDEX I should use for a clustered index on a IDENTITY column, the table is rarely going to be updated after first insert so I am looking for the setting that will result in the most of the space in the pages being used.

However I have found some documentation on the MSDN that seems to conflict with each other (or at least confuse me enough to ask for clarification).

From: http://msdn.microsoft.com/en-us/library/ms174979.aspx

  PAD_INDEX = { ON | OFF } When ON, the percentage of free space
  specified by FILLFACTOR is applied to the intermediate level pages of
  the index. When OFF or a FILLFACTOR value is not specified, the
  intermediate level pages are filled to near capacity leaving enough
  space for at least one row of the maximum size the index can have,
  considering the set of keys on the intermediate pages. The default is
  OFF.

And from: http://msdn.microsoft.com/en-us/library/ms177459.aspx

  The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

Given the clause When a FILLFACTOR value is not specified, does that mean that the default FILLFACTOR of 0 is ignored regardless of the setting of PAD_INDEX?

Following on from that does that mean that I need to specify both PAD_INDEX = ON and FILLFACTOR = 100 on my identity clustered index to get maximum page usage?


texts: Msg 14027, Level 11, State 1, Procedure sp_MSrepl_addarticle [table] does not exist in the current database

texts:
Before setting up SQL Server snapshot replication, I backed up the database from ServerA and restored it to ServerB. Both servers are running SQL Server 2008 R2 SP1.

In trying to setup snapshot replication from ServerA to ServerB using the GUI, I get the following error:

  Msg 14027, Level 11, State 1, Procedure sp_MSrepl_addarticle, Line
  582 [dbo].[SYSCOLUMNS] does not exist in the current database.

Yes, that is the name of the table and it is indeed a user table. I ran the setup from ServerA which is the Publisher. The collation is the same between the tables on both servers. If I remove that table as an article, the replication is setup just fine.

I've searched the web on this and I'm stumped. Any ideas?


texts: No properties option in Index context menu

texts: I am attempting to analyze the indexes of several of my tables according to a Microsoft article that describes Reorganizing and Rebuilding Indexes. The article says to right-click on the index within SSMS and then select properties. However, I don't see any option for properties on the right-click menu. I am using SSMS v17.1 with an Azure SQL Server v12.0.2000.8 (which I believe is SQL Server 2014).


texts: Greyed out menu options, broken activity monitor

texts:
I recently installed SP1 on my 2012 SQL Server. Ever since then SSMS has behaved a little weirdly when I connect from my pc rather than from the local server itself.

I no longer see the green "running" icons on the server and on the agent, and several menu options are greyed out (Start/Stop/Restart on server menu and agent menu).

Also, when I try to access the Activity Monitor, I get the following error:

Error Detail

I found the following article about the Activity Monitor. I followed the steps there (although I am a local admin on the sql server and should have had all of those permissions already) to no avail. The server is running Windows 2008 R2 Datacenter SP1. When I remote desktop to the server itself and use SSMS directly I have none of these issues, and when a domain admin connects to the server through SSMS remotely everything also works for him.

Any ideas?


texts: Working on a decision tree database design

texts:
I'm doing the conceptual model for a Decision tree database. The goal is store any Decision tree.

I have four entity types: DecisionTree, Node, Branch and Leaf.

Each Decision tree is composed of Decision node(s), Branch(es) and Leaves.
Each Decision node has a name and position.
Each Branch is a path for a possible decision or occurrence.
Each Leaf has represents the class label of the object to be classified.

Example of a decision tree:

Figure 1 provides an example of decision tree with its associated rules, where p(Class #i) is the probability of objects to belong to Class #i.

Example of Trees in SQL databases:

https://www.codeproject.com/Articles/8355/Trees-in-SQL-databases

Representation of a decision tree:

My conceptual design is represented in the following entity-relationship diagram (ERD):

So, the question is: How can I associate an Node, Branch and Leaf with an DecisionTree?


texts: MySQL 5.1.73 to 5.6.17 structure + data export

texts:
Are there any known issues to export structure and data from MySQL 5.1.73 to 5.6.17? Nothing fancy and to export-import user will use phpMyAdmin.

Thanks


texts: Is the default clustered index file physically the same file as the file containing the data records?

texts:
I have read that the records in a sorted data file (for a DB table) are sorted in the same order as the keys in its corresponding clustered index -- and a leaf node of clustered index contains the actual data record corresponding to the key in the leaf node.

Does that mean that they are physically the same on-disk file ?

If yes , then is the clustered index's tree data structure actually physically part of the data file ?

If no, and they are physically separate on-disk files, then does the leaf node contain a pointer to the data record ? -- but that's what happens in a non-clustered index.
I can't find an explicit answer to this query. Kindly help.


texts: unable to add data to table because related record is required

texts:
I have an access database with my work that I am trying to insert into but I keep getting.

  'You cannot add or change a record because a related record is required in table 'Projects'.'

I'm running this query: INSERT INTO Tasks (Assigned,Project,Description) VALUES (@assign,@project,@description)

On this Structure:

With this code in C# with an OleDb... commands and connections Which are working fine for other query's:

//the assign id is already known and is of type integer.
            string query = "SELECT Project_ID FROM Projects WHERE Project_Name = @project";
            OleDbConnection con = new OleDbConnection(con_string);
            OleDbCommand cmd = new OleDbCommand(query, con);
            cmd.Parameters.AddWithValue("@project", projectComboBox.Text);
            con.Open();
            OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                project_id = Convert.ToInt16(reader[0]);
                Console.WriteLine(project_id);
            }

            con.Close();

            Console.WriteLine("submit: " + project_id + " " + employee_id + " " + descriptionTextBox.Text + " " + monthCalendar1.SelectionStart.ToString("MM/DD/YYYY"));
            Console.WriteLine(monthCalendar1.SelectionStart);
            query = "INSERT INTO Tasks (Assigned,Project,Description) VALUES (@assign,@project,@description)";
            con = new OleDbConnection(con_string);
            cmd = new OleDbCommand(query, con);
            cmd.Parameters.AddWithValue("@project", project_id);
            cmd.Parameters.AddWithValue("@assign", employee_id);
            cmd.Parameters.AddWithValue("@description", descriptionTextBox.Text.ToString());
            //cmd.Parameters.AddWithValue("@deadline", monthCalendar1.SelectionStart);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            this.Close();

I have tried looking at other examples of this problem and I don't understand why I'm getting this error. @project has a valid id number of the primary key for a Project, @assign has a valid employee id as well and @description is string of text. Thanks for any help.


texts: Sometimes the updated rows are not locked within instead of update trigger

texts:
Here is as small repro:

create table dbo.t (id int primary key, v int);
insert into dbo.t values (1, 1), (2, 2);

create table dbo.s (id int primary key, v int);
insert into dbo.s values (1, 10);
go

create trigger dbo.tr_t__iou
on dbo.t
instead of update
as
begin
 set nocount on;

 exec sp_lock @@spid;
end;
go

update dbo.t set v = 10 where id = 1;

update t
 set
  v = 10
from
 dbo.s s join
 dbo.t t on t.id = s.id;

update t
 set
  v = 10
from
 (values (1, 10)) s(id, v) join
 dbo.t t on t.id = s.id;
go

drop table dbo.t, dbo.s;
go

sp_lock within the trigger reports U-key lock on the affected row in the first and the last cases, but in the second case there is no lock at all, how can it be explained?


texts: SQL Server transaction replication agent error

texts:
We are running transaction replication on SQL Server 2008 R2 SP3. Distribution agents are reporting :

  "Agent  is retrying after an error. 3 retries attempted"

every 10 minutes. Tried changing the query timeout values, restarted SQL Server agent and stopped and restarted the distribution agent. There are no errors in the job log, SQL Server error logs or windows event logs that appear relevant.


texts: dual table postgres doesn't return timestamp

texts:
I am trying to select now() from my local postgres database dual table:

select now() from dual;

It returns no rows. Just prints column name now(yyyy-MM-dd HH:mm:ss.ffffff)

What is it that I am missing that it cannot get the system time?


texts: High-performance query of two tables with count from second table?


texts:
I presumably appended .png to all entries in the column image like this:

update genres set image=image||'.png'.

I then realised that the images I'm referencing will be jpegs; and I'm too lazy to go and convert them; so I'd rather spend 10 minutes trying to form an SQL query and then result to serverfault; so I want to change them again.

I've tried:

update genres set image = image.slice(0,-4)||'.png';

and then I remember it's SQL, everything is as confusing as its string-concatenation operator.

So then I looked up a few string functions and came up with this:

UPDATE genres SET image = right(overlay(right(image) placing right('.png') FROM 0));

But it just doesn't work. There's always someone who's been working with SQL for 20 years that can write this paragraph in 10 characters of functional code.

I'm postgresql, it's basically the same thing.


texts: How can I append a total to a table and split the table by group (order_id)?

texts:
I am currently using:

select order_id as 'Order ID',
    DATE_FORMAT(date, '%d/%m/%Y') as 'Date',
    prod_title as 'Item',
    amount as 'Amount'
from transactions
where date >= '2016-10-01'
order by order_id,
    amount desc;

This first orders by order_id then by amount in descending order, which allows me to group orders regardless of date.

I'm wondering how I can split each grouping of order_id so I get something like this:

|3265|01|
|3265|02|
---------
|2613|03|
---------
.
.
.
|    |06|<-total

Is that clear? Also, how do I append a total?


texts:
Can we use the following query to update ANSI_NULLS option.

sp_configure 'allow updates', 1

reconfigure with override

update sysobjects set status = status | 0x20000000 where name = 'tblClient'

I am not sure what it is doing. I got this after searching for a solution. Our db is SQL server 2008.

But while i am running this i got an error like this "Ad hoc updates to system catalogs are not allowed."

Please give your suggestions


texts: How to remove all hidden parameters from Oracle Database

texts:
How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended?

SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\'

Here are parameters:

_fast_full_scan_enabled
_gby_hash_aggregation_enabled
_like_with_bind_as_equality
_optimizer_push_pred_cost_based
_sort_elimination_cost_ratio
_sqlexec_progression_cost
_trace_files_public
_b_tree_bitmap_plans

Regards,


texts: Is It Okay to Leave the Connection Open and Set CommandTimeout for Long Durations?

texts:
I have to process daily files and it's a long process that deletes, imports and updates processes.  Like literally 20 to 30 minutes per file for several files a day.

** EDIT:  I also have some other processes that look like they are going to be going for 90+ minutes each and I'm worried about them too.   Like, is this the best strategy?

I have a console application running with something like this:

For each file:

  using (SqlConnection cx = new SqlConnection(connectionString))
        {
           SqlCommand cmd = new SqlCommand("delete from " + tableName + " where fileName = '" + fileName + "' and date > @date", cx);
           cmd.AddWithParameters("@date", fileDate);
           cmd.CommandTimeout = 1280;
           cx.Open();
           cmd.ExecuteNonQuery();
          }

And then:

    using (var sbc = new SqlBulkCopy(connectionString))
      {
               sbc.DestinationTableName = tableName;
               sbc.BatchSize = 750;
               sbc.BulkCopyTimeout = 640;
               sbc.WriteToServer(dt);
       }

And finally:

   System.Data.DataTable dt = new System.Data.DataTable();
   System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();

   using (SqlConnection cx = new SqlConnection(connectionString))
          {
               SqlCommand cmd = = new SqlCommand("SELECT * from " + tableName + " where fileName = '" + fileName + "'", cx);
               cx.Open();
               da.SelectCommand.CommandTimeout = 1280;
               da.Fill(dt);
          }

           // this is followed by reporting on the data

So this is a low priority task but it has to get done and each file has to get processed sequentially.   Is there any problem with setting the connection timeout for so long?   We are on an internal network.   I've run DTA and taken all their indexing advice.

Any other thoughts, advice and ideas welcome.


texts: Why did adding a secondary index not increase select performance?

texts:
I have a table in MySQL called messages, which looks like this :

id (primary key)   | description    |  created_at(timestamp)

This table is supposed to hold chat messages between users of my application. As a result, the number of write operations on it will be high.

The application has an API that returns all the messages between two time stamps. This query will be fairly common too, but less than the number of write operations.

I ran mysqlslap with 100 concurrent connections and around 15000 rows in that table, and got a total time of around 8.6 seconds.

Then I added a secondary index on created_at, hoping to get the results in much less time for my search between two times, but I got an increase of 0.3 seconds for the same inputs.

Why did I not see a significant performance increase?

Edit:

this is what my table looks likes :

DROP TABLE IF EXISTS `mssg`;

CREATE TABLE `mssg` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `body` MEDIUMTEXT NULL DEFAULT NULL,
  `length` VARCHAR NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
);

and this is how i added the index:

ALTER TABLE testalter_tbl ADD INDEX (created_at);

Typically, there will be around 150 to 350 messages returned from the select statements


texts: Should I have a column for every data type or 1 column that stores everything

texts:
I have a database table that currently looks like this:

PageField
-Id (Int, PK)
-FieldType (String) Can be Text, Decimal, Integer or Bit
-Value (This stores the value regardless of the FieldType, so it is not strongly typed)

Should I remove the Value column and replace it with 4 separate columns TextValue, DecimalValue, IntegerValue, and BitValue?

The data type would be set to what is relevant. This would mean in every row 3 of these 4 columns would be NULL.


texts: Microsoft SQL Server Management Studio - default table view, default table parameters

texts:
A friend has recently started teaching me how to design relational databases. Long story short, as I am not SQL-capable yet, we're using SSMS and lots of right-clicking.

And I am frustrated with the stuff - 95% of the new tables I make have ID as first column (non-nullable INT primary key) and I have to change datatype and mark it as primary each time. So it can be even more frustrating, setting table view to Custom requires roughly the same amount of wasted clicks each time as well (though it is "set as default").

What am I doing wrong? Said friend does his part mostly in the code editor (or whatever is the name of the actual feature) and doesn't know the GUI that well.

Is there any way to automate these first steps for new tables? Get them done with only "create table"-click and filling in the name field preferably?

Sorry for my bad English (but I searched through other questions first ;)).


texts: Interrupting query sends wrong thread ID

texts:
In the MySQL command line client (Linux) when I kill a query by pressing CTRL+c this usually works fine.

mysql (slave) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 117920686" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (0.78 sec)

However when I do the same on my live master server (of all servers it must be this one...) it somehow sends the wrong thread ID.

mysql (master) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 770543254" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (5.00 sec) /* <- it obviously hasn't aborted at all */

The ID is way too low. Other threads have IDs like 26534760326. All servers are of the same version:

# yum list installed mysql*
Loaded plugins: dellsysid, rhnplugin, security, ulninfo
This system is receiving updates from RHN Classic or Red Hat Satellite.
Installed Packages
MySQL-python.x86_64                                             1.2.3-0.3.c1.1.el6                              @public_ol6_latest
mysql-community-client.x86_64                                   5.6.27-2.el6                                    @mysql56-community
mysql-community-common.x86_64                                   5.6.27-2.el6                                    @mysql56-community
mysql-community-libs.x86_64                                     5.6.27-2.el6                                    @mysql56-community
mysql-community-libs-compat.x86_64                              5.6.27-2.el6                                    @mysql56-community
mysql-community-release.noarch                                  el6-5                                           @/mysql-community-release-el6-5.noarch
mysql-community-server.x86_64                                   5.6.27-2.el6                                    @mysql56-community

Additional info:

The connection_id() function returns the right value however.

root@ods01:(none) > select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|     26542310314 |
+-----------------+
1 row in set (0.00 sec)

root@ods01:(none) > select sleep(5);
^CCtrl-C -- sending "KILL QUERY 772506538" to server ...
Ctrl-C -- query aborted.
+----------+
| sleep(5) |
+----------+
+----------+
1 row in set (5.00 sec)

Does anyone know how to fix this?


texts: I am in the process of running a lot of data on a computer that has more power than the one I am currently using. However the data is in a SQL Server instance that I have already created and do not want to change. Is there a way to set up the same SQL Server on the other computer?


texts: Regarding Database mail configuration in sql server web edition 2012

texts:
I have enabled the database mail and sql agent service both and just to test whether  the database mail is working or not, i configured the database mail, created the profile and account along with all the required details like (server name, display name, port, ssl, basic authentication) however when i tried to send test email and i am getting error in the server logs that is "unable to send mail because of the mail server (failure sending email)" something like that. I have checked the server name thrice and it is absolutely correct. Please suggest what am i doing wrong. Any help would be greatly appreciaated.

Note:- The same configuration i am using in enterprise edition and it is working perfectly fine.   I am able to get the test email.


texts: SSIS Visual Studio 2008 reference variable scope in expression builder

texts:
When creating an SSIS package in Visual Studio 2008, in the expression builder, I know I can reference a variable by using @[User::FilePath] for example but this only works if you are evaluating the expression for an object that is within the same scope of the variable that you are referencing.

Is it possible to reference the SCOPE of the variable such as @[User:PackageScope:FilePath] or @[User:SQLTaskScope:FilePath] for example so that I can call a variable that is in a different scope then the object I am building the expression for?


texts: SQL Server Always On in EC2\AWS - Add new replicas how to?

texts:
Can someone please answer on this.
we have 2 node WFC with AG group + listener listed to secondary IPs in AWS. one replica in AZ1, another in AZ2.
Now due to perf issues the users added two more nodes. The idea is seamless migration of dbs to new node without any changes to connection string (listener).
How can we make the existing listener work when the listener is tied to secondary IPs of the node 1 and node 2. What are my options, how can we tie the existing listener to Node 3 and Node 4.


texts: How to know if backup file is available before trying to restore

texts: I have a restore job, but i want to add a step before restore step to know if backup file available.so that it should go to restore step only if backup file available.Please let me know a query which i can use before restoration step


texts:
I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file.  Is there a way to do this?  The query has more parameters than is reasonable for a person to be prompted for and we would like to eliminate user error when entering the parameter for this.

The reason we need to issue a parameterized query is that we are seeing a huge difference in performance between a straight sql statement and the same query issued as a parameterized query in our application.  We would like to remove any application code from the chain and have the difference evident with only Oracle tools.


texts: Does size of one particular column in a database affect query performance?

texts:
I have a table user_list

CREATE TABLE IF NOT EXISTS `user_list` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `email` varchar(40) NOT NULL,
  `userdata` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000265 ;

The queries I perform on this table are

SELECT email WHERE id = XXXX
SELECT userdata WHERE id = XXXX
SELECT id WHERE username = XXXX
UPDATE user_list SET userdata WHERE id = XXXX (used pretty
frequently)
UPDATE user_list SET email WHERE id = XXXX
(These are the only I queries I am going to perform on this table)

Now the problem is userdata field stores all the information about the user and is relatively large.Its text generally contains about 2000 characters. Say I am going to insert some 10 million+ entries in the database.

Does the size of this userdata field affect the performance of the queries I mentioned above?

If I store the userdata text as a separate file and just store the path in the database, will there be a significant performance difference?  Since queries 1,3 and 5 do not involve the userdata field, will their performances be increased significantly since I have removed the userdata field from the table?

Is having a separate table user_data_list with the fields for the userdata

----------id----------userdata-----------------

provide any advantages over how it is now?  Does having such a separate table have any disadvantages?


texts: Merge reports 'You must rerun snapshot because current snapshot files are obsolete', gets caught in Death Loop

texts:
We have a very large Merge publication for a data warehouse with some large (.bcp files on the order of 20GB) fact tables that is causing us tremendous trouble.

Specifically, we created a publication and a subscription, and things ran OK w/ a couple hiccups (disk space). After the first hiccup (log space exhausted) we saw this:

You must rerun snapshot because current snapshot files are obsolete.

On restart, it did pretty much what one would expect (went through the process, skipping things it had already done).

At the end we saw the 'nothing to do' message, but immediately it went back into the ftp/apply process, failing b/c when it tries to apply the .sch file 'can't drop the table because it is being used in replication'.

So things are obviously hosed. Restarting from zero might be advised but we did the 'remove publication' stored procedure on the offending subscriber last night so we should have been clean.

Also, this whole process (downloading and applying) took 9 hours, so if possible I would like to get to the root of it before restarting the process. Why would the snapshot have become obsolete in such a short period? What could have gone awry? If you have any questions or need info, let me know.


texts: How to make trigger function to make daily entering-exiting report?

texts:
I have a table called daily_report that register what time/date worker goes out from the building and what time/date he/she comes in.

CREATE TABLE daily_report (
        id serial,
        worker_id integer,
        in_or_out integer,
        the_time time,
        the_date date
        );

I assigned "in" status to 1, "out" to 0. I need a query that shows when the worker first enter the building and when he/she exits it in the specific day.(notice some workers may enter the building  and exit next day, like night guards) . Like :
Select min(time), max (time) group by date........
I tried to join table to itself but cant get a result.
Trigger function also will be ok that records daily report in a new table like worker with id 3 enters 08:00 am, exits 18 00 pm in '2016-01-01' for example

please help me to do that.


texts: SSIS import data or insert data if no match

texts:
I have a situation in which I need to import data from one table to another. The challenge here is in tables I have some columns which are references to other table values (kind of foreign key). I need to match these referencing columns and if fields match I need to map the respective id otherwise I need to insert that value in the referencing table and than map id in the source table data source and import all the data.

Elaborating my problem:

So this is how my ETL look like. Currently I am "On Lookup Match" moving forward with execution but I also need to create new records in the respective table if "Look up don't match". (And this I need to do for all the 8 lookups I am doing). I do not understand how to do all this in sequence and import all the data at final stage.


texts:
I got a set of tables which have been imported into a database by transforming them like shown in the following picture:

Now it is necessary to get the former tables back. Unfortunately the guy who did the importing finished school and is thus is unavailable. Therefore I was asked to find an SQL-based solution to back-transform the tables. I would be able to write a small tool to solve this problem, but I've got no idea how to solve this with SQL.

With Kind Regards,


texts:
I have a query which runs much faster with select top 100 and much slower without top 100. The number of returned records is 0.
Could you explain the difference in query plans or share links where such difference explained?

The query without top text:

SELECT --TOP 100
*
FROM InventTrans
     JOIN
     InventDim
     ON InventDim.DATAAREAID = 'dat' AND
        InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND
      InventTrans.ITEMID = '027743' AND
      InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND
      InventDim.ECC_BUSINESSUNITID = 'Казахстан';

The query plan for the above (without top):

https://pastebin.com/cbtJpxFf

The IO and TIME statistics (without top):

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'INVENTDIM'. Scan count 0, logical reads 988297, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 1, logical reads 1234560, physical reads 0, read-ahead reads 14299, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6256 ms,  elapsed time = 13348 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

The used indexes (without top):

1. INVENTTRANS.I_177TRANSIDIDX
   4 KEYS:
 - DATAAREAID
 - INVENTTRANSID
 - INVENTDIMID
 - RECID
2. INVENTTRANS.I_177ITEMIDX
   3 KEYS:
   - DATAAREAID
   - ITEMID
   - DATEPHYSICAL
3. INVENTDIM.I_698DIMIDIDX
   2 KEYS:
   - DATAAREAID
   - INVENTDIMID

The query with top:

SELECT TOP 100
*
FROM InventTrans
     JOIN
     InventDim
     ON InventDim.DATAAREAID = 'dat' AND
        InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND
      InventTrans.ITEMID = '027743' AND
      InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND
      InventDim.ECC_BUSINESSUNITID = 'Казахстан';

The query plan (with TOP):

https://pastebin.com/0dyu6QZd

The query IO and TIME stats (with TOP):

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 15385, logical reads 82542, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTDIM'. Scan count 1, logical reads 62704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 265 ms,  elapsed time = 257 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

The used indexes (with TOP):

 1. INVENTTRANS.I_177TRANSIDIDX
     4 KEYS:
     - DATAAREAID
     - INVENTTRANSID
     - INVENTDIMID
     - RECID
 2. INVENTTRANS.I_177DIMIDIDX
    3 KEYS:
    - DATAAREAID
    - INVENTDIMID
    - ITEMID
 3. INVENTDIM.I_698DIMIDIDX
    2 KEYS:
    - DATAAREAID
    - INVENTDIMID
 4. INVENTDIM.I_698ECC_BUSUNITLOCIDX
    3 KEYS
    - DATAAREAID
    - ECC_BUSINESSUNITID
    - INVENTLOCATIONID

Will deeply appreciate any help on the topic!


texts: SSIS 2017 Scale Out functionality and use cases

texts:
SSIS 2017 has a scale out feature that distributes executions across multiple SSIS worker instances. I think get the big picture, but just wondering how it divides up the work across the worker instances.

For example if I have a for-each container looping on 1000 files to import, does each worker instance import a single file then report back to the master to get another file to process?

What's a good use case for scale-out?


texts: Storing data as rows as opposed to columns

texts:
Here's a typical way I would store data (obviously not storing the password in plain text)

    USER TABLE

    | UserID | UserName | FullName | EMail   | Password |
    |--------|----------|----------|---------|----------|
    |1       |userAAA   |User Aaa  |aa@aa.com|aAaaA     |
    |1       |userBBB   |User Bbb  |bb@bb.com|bBbbB     |
    |1       |userCCC   |User Ccc  |cc@cc.com|cCccC     |
    |--------|----------|----------|---------|----------|

Is there anything wrong with storing it in the following manner?

    USER TABLE                 ATTRIBUTES TABLE

    | UserID | UserName |      |AttributeID | Attribute |
    |--------|----------|      |------------|-----------|
    |1       |userAAA   |      |1           |Full Name  |
    |1       |userBBB   |      |2           |E-Mail     |
    |1       |userCCC   |      |3           |Password   |
    |--------|----------|      |------------|-----------|

    ATTRIBUTE_VALUES TABLE

    |UserID | AttributeID | AttributeValue |
    |-------|-------------|----------------|
    |1      | 1           |User Aaa        |
    |1      | 2           |aa@aa.com       |
    |1      | 3           |aAaaA           |
    |2      | 1           |User Bbb        |
    |2      | 2           |bb@bb.com       |
    |2      | 3           |bBbbB           |
    |3      | 1           |User Ccc        |
    |3      | 2           |cc@cc.com       |
    |3      | 3           |cCccC           |
    |-------|-------------|----------------|

The huge benefit I see here is the ability to easily add additional attributes to the user. But I really wanted to get another opinion on this.


texts: delete database and configure database options in DBCA greyed out (disabled)

texts:
I am using oracle 11g R2 on Linux Redhat 6.3 and  created a lot of databases. At that time there was no problem. But currently I am seeing that in DBCA (Database Configuration Assistant) application some options are disabled as follows:-

1 Create  a Database - ENABLED

2 Configure Database Options - DISABLED

3 Delete a Database  - DISABLED

4 Manage Templates - ENABLED

I don't understand why this happening but in starting all the options were enabled.

EDIT :

oratab file content, inside etc/oratab

**

# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
Dev:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
Stage:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
Test:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
qa1:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
Dev2:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
Qual:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N
fStage:/oracle11gr2/u01/app/oracle/product/11.2.0/db_1:N

**


texts: how does multiple column index work in details

texts:
if we have a table T1 with columns A,B,C,D,E and an index ( A,B,C) built for it

if we a SQL query joining on columns A,B or A,B,C or A, this index can still be used, but if the query is joining on B or C or B,C  the index is totally useless

I know indexes are often implemented BTree , I want to know how is the implementation detail related with this ?


texts: Is there a way to copy/initialize values in a set

texts:
What I want to do (I know this does not work) is:

update stats
set BandwidthLastMonth = BandwidthThisMonth,
    BandwidthThisMonth=0
where myPk = 3

Is there a way to do this in a select statement? Or do I need to write a stored procedure and hit each record twice (I sure hope not).

My solution needs to work for Sql Azure and Sql Server 2008.


texts:
I have a stored procedure which inserts some records in a few  tables. In couple of tables at least , the inserted records number 10000+. Not more than 15K though. Takes 3-5 mins for this procedure to complete. Also same procedure can be called from multiple user sessions which results in some sessions waiting for 20 mins to get a response. Is there something I can do to reduce this time?

The database recovery mode is FULL(cannot be changed) and hence per my understanding sqlBulkCopy wont help here. Would love to hear your thoughts on this.

The table contains 15 columns. Among which  5 columns are foreign key to this table. There is no identity column but clustered index in combination of all the 5 foreign key columns. I have couple of index on other key columns . The rest of columns are decimal and varchar(50). I do have one varchar(max) column though.

Trying to get query or query same for your reference . A screen grab of most expensive operation(54) in the query:

If the clustered index of the table is on combination of columns instead of an identity column , will the inserts be impacted?

Basically the query is 'insert into this_table select from this table join with 5 other tables whose primary keys are foreign keys here to update those values. Joins are all on keys between the tables. I wouldn't mind posting the query but would you need the schema all tables involved in query as well?

EDIT2:
First of all thanks all of you guys for your answers,comments and thoughts. The object from which read happens turned out to be a heap with no clustered index. Modifying it did give significant improvement in read operation which improved overall .

I marking David Spillet answer as accepted as it gave a methodical way to approach the problem. Learned a few things about posting questions :)

Also thanks for Frisbee for comments and answers. I know i kept on modifying the question :)


texts: How to take SQL Server database under this scenario?

texts:
I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB.

How can I take a full backup in this scenario? How can I split full backup into different files in this ratio?

Please suggest a method to do this.


texts:
I am in need to shrink a data file in SQL Server 2012. I am using shrink files option in tasks of the database and am using "Reorganize pages before releasing unused space" option. SQL Server says minimum size I can shrink the file upto is 350000 MB. What will happen if I try and shrink it to 300000 MB, Will this delete the data from the database?
In simple words, if I shrink the file beyond the minimum size of the file, does it delete the data from the file?


texts: MySQL locking tables / rows on select statement

texts:
I have a web app that does an addition to a field based on a user input.

So say for example User-A inputs 7, my web app would first query the contents of the field for the current number in there, which is say '10' in this case, then adds 7 to it and updates the filed with the total. In this case 17. Then User-B inputs 3, the same routine happens and now the filed contains 20 etc...

My concern is that if 2 or 2000 users query the table at the same time and get the same number back, for example 10, then it is going to go all sorts of wrong when it comes to the addition and writing the new total back.

Should I be explicitly locking the table / row on the SELECT statement? Is that normal practice in this situation and will it solve this issue.

This is basically a aggregate table for a survey system so I can quickly run and display reports on the daily totals etc...


texts: Performance: MySQL id = vs id IN (multiple values)

texts:
I've found posts of = vs IN for single value (link and link), but I'm asking actually for multiple values. Which is likely to perform better:

# Query 1:
SELECT * FROM tbl WHERE id = 50 OR id = 51 OR id = 52

# Query 2:
SELECT * FROM tbl WHERE id IN (50, 51, 52)

# Query 3:
SELECT * FROM tbl WHERE id >= 50 AND id <= 52

Note: id is primary and indexed, obviously.


texts:
Is there a way to do a LIKE operation in DB2 against a varchar column in a case insensitive way, without changing the underlying column definition?

Or is there a workaround like SELECT * FROM mytable WHERE mycolumn LIKE '%string%' OR mycolumn LIKE '%STRING%' that would work?


texts: Tip on modeling Cassandra database schema time querry

texts:
I am working on modeling a database schema for Cassandra from existing relational schema, so in my database there are "events" (some events are scheduled events, some are recurring events) and in each "event" there are multiple "users" so relation is something as follows...

In my cassandra database I currently have uuid of the event along with some other currently not relevant data as a Partition key, my clustering key is participant uuid, recurrence_rule is a static field, start_time is also a static field, status can be one of [NotStarted, Started, Ended], join password is a field in event_by_uuid database as well. So how do I make a table for supporting following querrys:

get all "upcoming events" for a certain participant uuid (events that have start_time in future in relation to start_time>now())
get all "past events" same as 1. but for start_time<now()
get all Started events for a certain participant

Also an issue I have is having this query based on time and having to insert any potential update to the event status to each and every one of those tables event_by_participant


texts:
I am fetching data from MySQL using PHP. I know that most values stored in the database are float. Sometimes, the database outputs values like 3.9999E-5 and sometimes like 0.00104961904762 (14 digits).

How can I truncate the value to 8 digits only. I am currently using:

<? echo sprintf("%.8f",$get_results['Price']);?>


texts: Sql Server Management Studio for 2008 Express?

texts:
I just installed Win7 on a fresh laptop and want to put a lightweight MS Sql Sever on it.  VS2010 already installed SS2008 Express.  So now I just need a management studio for it.

What would you recommend?  I see there is a Management Studio Express, and have also read that you can install Management Studio from the SS2008 full discs.  Is there a preferred route to take?


texts:
In definition;

SELECT *
from T1, T2

Cross Join: If T1 has N rows, T2 has M rows, the result set will have N x M rows.

If T2 has no row, then result of the JOIN has no row also.

However, I want N rows (from T1), what can I do?

Any suggestion?

Note: I want cartesian product, there is no shared column between the tables.


texts:
In SQL Server, there are three database roles that have CONTROL certificate permissions. Those are db_owner, db_DDLadmin and db_securityadmin.

Also, the documentation for decryptbycert specifies that CONTROL permission is needed to use the certificate. Anyone with control permissions can backup the certificate with the private key because it is automatically decrypted by the Database Master key. I've reviewed using database triggers, denying view definition (which disabled backups of the certificate, but also made the certificate unusable to the user) and other options to try preventing certificate backups to no avail.

I do understand that this can be accomplished by creating certificateuser signed procedures that will allow a user to use the encryption without the control permissions on the certificate, but I need a more immediate resolution that works against the users in the above roles.

I want to know if anyone has any ideas or knows of any trace flags that can be used to reserve backing up keys and certificates to only members of sysadmin or anything else that could work ?


texts: Deleting & Reclaiming space from InnoDB table

texts:
I have a 700GB InnoDB table which I'm not writing any more data to (only reading).
I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete part is pretty easy, because I have an auto-inc primary index so I can just iterate in chunks using it, and delete the rows, but that won't bring me back the space. I assume OPTIMIZE TABLE will but that might take forever on a 700GB table, so is there another option I'm overlooking?
Edit by RolandoMySQLDBA
Assuming your table is mydb.mytable, please run the following query and post it here so you can determine diskspace needed for the table's shrinkage:
SELECT
    FORMAT(dat/POWER(1024,3),2) datsize,
    FORMAT(ndx/POWER(1024,3),2) ndxsize,
    FORMAT((dat+ndx)/POWER(1024,3),2) tblsize
FROM (SELECT data_length dat,index_length ndx
FROM information_schema.tables WHERE
table_schema='mydb' AND table_name='mytable') A;

We also need to see the table structure, if allowed.
Edit by Noam
This is the output of the query:
datsize ndxsize tblsize
682.51  47.57   730.08
This is the table structure (SHOW CREATE TABLE)
`CREATE TABLE `mybigtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `tid` bigint(20) NOT NULL,
  `text` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ft` tinyint(1) NOT NULL,
  `irtsd` bigint(20) NOT NULL,
  `irtuid` int(11) NOT NULL,
  `rc` int(11) NOT NULL,
  `r` tinyint(1) NOT NULL,
  `e` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,  `timezone` varchar(5) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uid_tid` (`uid`,`tid`)) ENGINE=InnoDB AUTO_INCREMENT=2006963844 DEFAULT CHARSET=utf8`


texts:
You can use 1 or 2 sequence.

Let's say I have a table tblOrder and 2 columns in it, OrderID and Company. I only use two companies in my table, IBM and Airtel.

What I want is when  Company = IBM then OrderID insert as 1 and when company is Airtel then it should insert OrderID as 1001 and repeat steps with increment 1, meaning next OrderID for IBM should be 2 and for Airtel should be 1002.

I think I need to use two sequences, on one column with case expression.


texts: Why are Denali sequences supposed to perform better than identity columns?

texts:
In his answer to Which is better: identity columns or generated unique id values? mrdenny says:

  When SQL Denali comes out it will support sequences which will be more efficient than identity, but you can't create something more efficient yourself.

I'm not so sure. Knowing Oracle's sequences, I have either to create a trigger for insert, encapsulate each insert into a call of a stored procedure, or pray that I do not forget to properly use the sequence when I do an ad-hoc insert.

I doubt that the advantages of sequences are so obvious.


texts: How to do archiving in a Winforms C# Project with MySQL database?

texts: So I have a C# Winforms Project with MySQL as the backend. I'm wondering how to do archiving on my records. Is it done by inserting records from the active table to an archive table and deleting the records on the active table on the same database? or Is it done by inserting records from the active table in an active database to a clone table on an archive database and deleting the records on the active table? I'm just starting to learn this stuff, so I need assistance if someone could explain or at least give some examples.


texts: Can data be replicated from a MySQL slave back to a master without the slave containing all the master data?

texts:
We have a PHP/Symfony 2 application using MySQL as the database. The database contains a large amount of data.

The application is deployed to a separate server(s) and an empty/clean version of the database deployed. The application is then run on a local network as the application is used where internet access is not always available (hospitals, banks etc).

Once the application has collated all the information are there any solutions for replaying the data back to the original database?

We are not using Master/Slave as we do not want the Slaves to contain all data from the original database.


texts: Create a new schema in a particular database

texts:
I have a Postgres server with multiple databases (e.g DB1, DB2, DB3)

I need to create a schema sch1 in only DB2. How can I do that?

I have tried connect DB2, but it is not working


texts: Referencing a SELECT subquery value in the WHERE clause

texts:
Complete query
SET @vSelect =
    '
    SELECT
        T1.BranchShortName AS BranchShortName,
        (
            SELECT TT1.EmployeeName
            FROM [dbo].[Tb_OPL_Employee] AS TT1
            JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
            JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
            WHERE
                TT3.IdTb_OPL_JobTitles = TT1.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles =
                (
                    SELECT MAX(TTT3.IdTb_OPL_JobTitles)
                    FROM [dbo].[Tb_SYS_Approval] AS TTT3
                    WHERE
                        TTT3.IsPassed = 1
                        AND TTT3.ApprovalLevelCode = ''Approve''
                        AND TTT3.TaskId =TT2.TaskId
                )
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
        ) AS LastApproval,
        (
            SELECT TT1.EmployeeName
            FROM [dbo].[Tb_OPL_Employee] AS TT1
            JOIN [dbo].[Tb_MKT_SKD] AS TT2
                ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
            JOIN [dbo].[Tb_SYS_Approval] AS TT3
                ON TT3.TaskId = TT2.TaskId
            WHERE
                TT1.IdTb_OPL_JobTitles = TT3.IdTb_OPL_JobTitles
                AND TT3.IdTb_OPL_JobTitles =
                (
                    SELECT MIN(TTT3.IdTb_OPL_JobTitles)
                    FROM [dbo].[Tb_SYS_Approval] AS TTT3
                    WHERE
                        TTT3.IsPassed = 0
                        AND TTT3.ApprovalLevelCode = ''Approve''
                        AND TTT3.TaskId =TT2.TaskId
                )
                AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
                AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
        ) AS NextApproval,
        T6.StatusDescription AS Status
    '
SET @vWhere = ' WHERE 1=1 '

IF (@ddlSearchCriteria = '1')
BEGIN
    -- The condition of search textbox is not empty
    IF (@txtSearch <> '')
    BEGIN
        -- The Condition of StartDate is not empty and EndDate is empty
        IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)'

        -- The Condition of StartDate is empty and EndDate is not empty
        IF (@txtStartDate = '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        -- The Condition of StartDate is not empty and EndDate is not empty
        IF (@txtStartDate <> '') AND (@txtEndDate <> '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
                                      AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)
                                      AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'

        IF (@txtStartDate = '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'''

        PRINT @vSelect+@vWhere
    END

How can I get the subquery value for LastApproval to compare like I do with T1.BranchShortName?
I tried changing it like:
... IF (@txtStartDate <> '') AND (@txtEndDate = '')
            SET @vWhere = @vWhere + ' AND **T1.BranchShortName --> LastApproval** LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''  ...

but this gave me a message saying there was an error in my where clause.


texts: Add data from one table to another when data in two other specific fields are identical

texts:
I'm working on populating a cadastral table with codes from a database that comprises historic information about farms. Since both tables contain thousands of rows I'm looking for ways to automate the process. The two tables don't always have a direct correlation but there are ways to update the cadastral table automatically.

The database is PostgreSQL 9.4

The relevant fields in table 1 are:

county_name
farm_name
new_field_for_fk

For the second:

county_name
farm_name
pk

Neither the county_name nor the farm_name are unique identifiers, but for 99% of the time the combination of the two is, and I want to update new_field_for_fk with the data in pk where county_name and farm_name are identical. Any thoughts?


texts:
I need to update a Oracle procedure via a .sql script. I know the procedure works because it is currently running on our server, but I need to update the procedure on a client's box.

I need to be able to hand them a .sql script which they will open and execute with Oracle SQL Developer, which will update the procedure, but when I try to create the .sql file and run the .sql file it fails, because it's trying to run the procedure.

I don't want the procedure to be run, but rather be created or replaced with the following updated procedure:

    CREATE OR REPLACE PROCEDURE updateDBUSER(
         p_userid IN DBUSER.USER_ID%TYPE,
         p_username IN DBUSER.USERNAME%TYPE)
    IS
    BEGIN

      UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

      COMMIT;

    END;


texts: I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea?

texts:
Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as the job scheduler in pretty much every case, rather than the native Windows Task Scheduler.

From my perspective, the SQL Agent has a number of advantages over the native Windows Task Scheduler:

Remote (from my workstation) start/stop/monitoring of tasks
Shared schedules (rather than each task on its own)
Multiple steps and control flow
Different types of tasks
Alerts on failure/completion
Can be configured to act as different users
(Moderately) descriptive error messages, rather than just an error code

However, I can't escape the feeling that this is bad practice - the SQL Agent should be reserved for just database-related tasks, and I should leave OS level tasks running in the Windows Task Scheduler, despite my dislike of its usability.

Is it okay to rely on the SQL Agent in this way? If not, should I consider a third-party Windows task scheduler to get some of the functionality I'm looking for?


texts: Does PostgreSQL do any kind of caching of query results on its own?

texts:
Suppose I do this ten thousand times in quick succession:
SELECT * FROM table WHERE id = $1;

That is, not as a prepared statement which is executed, but as individual queries.
Will PG actually execute the query each and every time, or will it somehow keep track of the fact that it was recently run, and that nothing has changed in between, and instead of running it again, simply return the last result which it has kept around in RAM?
Or do I need to do this client-side with my own application code?
Many years ago, I heard of "memcached" and stuff like that, but could never figure out how to use it, so I never did. I don't use any kind of "abstraction layer" either which might do this on its own, so I'm purely wondering if PG does this internally and automatically as part of some general optimization/resource-saving mechanism.


texts: users have access to ssas tabular instance without giving them a role

texts:
I have created a ssas tabular model. Testing the row level security base on a user I found out that it didn't work.

I tested it on an user which is not known in the SQL database and analysis services. When I ask the user connect to the Analysis Services in MS Excel, he was able to see all the data. All with the company network.

Do I need to change something in the configuration of the firewall/SQL Server/SSAS Tabular? I configure port 2382 for the SSAS Tabular instance.

I use SQL Server 2014 Developer Edition

I hope someone can help me with this. If more information is needed, please let me know.

Thanks and regards Rik


texts: Postgresql startup: runuser: cannot set groups: operation not permitted

texts:
I'm having trouble starting a postgres database. I'm running 9.2 on Centos 5.4 (for compatibility reasons). Here is what I have done so far:

Ran postgres initdb for the directory /opt/database
Updated postgresql.conf to increase sessions, use more of the available ram, and change logging.
Started postgres as a test (it worked)
Added a recovery.conf and changed pg_hba.conf to allow replication (this is a slave)
Ran a pg_basebackup (as user postgres) to get a copy of data from the master

After the completion of the basebackup, I tried to start postgres and it fails with no error output. The only information I have is from the pgstartup.log which says:

runuser: cannot set groups: Operation not permitted

The runuser command has 2755 permissions (setgid sticky bit set). Because of something I found in another forum, I ran /usr/pgsql-9.2/bin/postgres -d 3 -D /opt/database -c config_file=/opt/database/postgresql.conf as user postgres. I received the following output (which has been trimmed to remove useless bits like my ls colors, and my ssh session information).

DEBUG:  postgres: PostmasterMain: initial environment dump:
DEBUG:  -----------------------------------------
DEBUG:      USER=postgres
DEBUG:      LD_LIBRARY_PATH=/usr/pgsql-9.2/lib
DEBUG:      PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
DEBUG:      _=/usr/pgsql-9.2/bin/postgres
DEBUG:      PWD=/opt/database
DEBUG:      INPUTRC=/etc/inputrc
DEBUG:      EDITOR=vim
DEBUG:      SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
DEBUG:      HOME=/var/lib/pgsql
DEBUG:      SHLVL=2
DEBUG:      LOGNAME=postgres
DEBUG:      CVS_RSH=ssh
DEBUG:      LESSOPEN=|/usr/bin/lesspipe.sh %s
DEBUG:      G_BROKEN_FILENAMES=1
DEBUG:      HISTTIMEFORMAT=%F %T:
DEBUG:      PGLOCALEDIR=/usr/pgsql-9.2/share/locale
DEBUG:      PGSYSCONFDIR=/etc/sysconfig/pgsql
DEBUG:      LC_COLLATE=en_US.UTF-8
DEBUG:      LC_CTYPE=en_US.UTF-8
DEBUG:      LC_MESSAGES=en_US.utf8
DEBUG:  -----------------------------------------
DEBUG:  invoking IpcMemoryCreate(size=2220941312)
DEBUG:  SlruScanDirectory invoking callback on pg_notify/0000
DEBUG:  removing file "pg_notify/0000"
DEBUG:  max_safe_fds = 984, usable_fds = 1000, already_open = 6
bash-3.2$ DEBUG:  logger shutting down
DEBUG:  shmem_exit(0): 0 callbacks to make
DEBUG:  proc_exit(0): 0 callbacks to make
DEBUG:  exit(0)
DEBUG:  shmem_exit(-1): 0 callbacks to make
DEBUG:  proc_exit(-1): 0 callbacks to make

Any idea why postgres isn't starting?


texts: Standards or Best Practices for Error Handling in SSRS

texts:
I have encountered a recurring issue with SSRS where "bad" reports go out to clients. The issue is that we have reports with a number of subreports, and one or more subreports will fail to process. When the subreport fails to process, the report sends with an error message in place of the sub report and no actual data.

My question is, is there a way to prevent a report from sending if a subreport fails?

Ideally I would like the outer report to just return an error. Is there a way to have error states in an SSRS report escalate to an outer scope? Is there a coding standard we can enforce that could prevent this issue?

We are currently using both SSRS 2008r2 and SSRS 2016.


texts:
I'm looking to setup a two physical node WFC cluster (server 2016) which has no shared disks. Performing standalone install of SQL server 2016 Standard on each of the nodes and then configuring a BAG on top of that.

In terms of quoroum Im going with node majority as there is no shared disk or witness configured.

Has anyone out there done the similar and is this bad practice? Am I required to give it a qouroum?

Also seems when you shut down SQL service on the primary node there isn't automatic failover... hmm... even though its set the sync commit and auto failover. Something isn't right

Also seems when you shut down SQL service on the primary node there isn't automatic failover... hmm... even though its set the sync commit and auto failover. Something isn't right
From testing it appears if one node goes down then the AV resource just fails over to the other node. Just wondering whether we would have splitbrain scenario as using node majority?

Thoughts?


texts: I have a large Oracle table that need to be re-partition. I was wonder there is a way to know that I have enough space to create a copy of this table with new partition and swap the new one with the old one.


texts: Is it possible to create a user defined type with plpgsql or SQL?

texts:
Distinct from a DOMAIN I was wanting to know if it was possible to create a user-defined type without C.

Specifically I wanted to create a type like this

CREATE TYPE name (
    INPUT = input_function,
    OUTPUT = output_function
    [ , RECEIVE = receive_function ]
    [ , SEND = send_function ]
    [ , TYPMOD_IN = type_modifier_input_function ]
    [ , TYPMOD_OUT = type_modifier_output_function ]
    [ , ANALYZE = analyze_function ]
    [ , INTERNALLENGTH = { internallength | VARIABLE } ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = alignment ]
    [ , STORAGE = storage ]
    [ , LIKE = like_type ]
    [ , CATEGORY = category ]
    [ , PREFERRED = preferred ]
    [ , DEFAULT = default ]
    [ , ELEMENT = element ]
    [ , DELIMITER = delimiter ]
    [ , COLLATABLE = collatable ]
)

input_function and output_function both say,

  The input_function converts the type's external textual representation to the internal representation used by the operators and functions defined for the type. output_function performs the reverse transformation. The input function can be declared as taking one argument of type cstring, or as taking three arguments of types cstring, oid, integer. The first argument is the input text as a C string, the second argument is the type's own OID (except for array types, which instead receive their element type's OID), and the third is the typmod of the destination column, if known (-1 will be passed if not). The input function must return a value of the data type itself. Usually, an input function should be declared STRICT; if it is not, it will be called with a NULL first parameter when reading a NULL input value. The function must still return NULL in this case, unless it raises an error. (This case is mainly meant to support domain input functions, which might need to reject NULL inputs.) The output function must be declared as taking one argument of the new data type. The output function must return type cstring. Output functions are not invoked for NULL values.

In both cases, a cstring is called for, but in either case here, when I try to create it I get

  ERROR:  PL/pgSQL functions cannot accept type cstring

Or,

  ERROR:  SQL functions cannot have arguments of type cstring

Can this be done without C?


texts: A database in MySQL is a schema in Oracle : I do not understand this statement

texts:
Please help me understand this statement:

  A database in MySQL is a schema in Oracle.

I have just started to use Oracle and I find it different from other RDBMS softwares I have used like MSSQL, MySQL and Derby.

For example to create a database, when I use create database ghazals, it throws an error:

ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted

Also, commands like show databases do not work here.


texts: Slow connect time to postgresql on Windows 10

texts:
Connect time to postgresql on my Win 10 machine incredible slow.
It take 2.5-3.5 seconds for a connect.

$ time 'c:/Program Files/PostgreSQL/10/bin/psql.exe' -h 127.0.0.1 -p 5433 -U xxx -c 'select true'
 bool
------
 t
(1 row)

real    0m3.416s
user    0m0.015s
sys     0m0.000s

Same time when i try to use psycopg2 python driver. Same time for postgresql version 9 and 10.
On the other hand connect time to mysql is 0.002, issue only with postgresql.

Here relevant logs with max debug from postgresql

2018-03-19 21:24:43.654 +03 [10048] DEBUG:  00000: forked new backend, pid=21268 socket=5072
2018-03-19 21:24:43.654 +03 [10048] LOCATION:  BackendStartup, postmaster.c:4099
2018-03-19 21:24:45.248 +03 [21268] LOG:  00000: connection received: host=127.0.0.1 port=9897

It fork new backend and then only 2 seconds later logs connection received

UPD
Even if i manage to avoid connection delay of postgresql ( for example via pgbouncer, or if postgresql running in docker) request still take 1.3-2seconds, but from first sent package till last its only 0.022 second, all other time idk what happening but not a network communication between client and server. Same code if run within docker - 0.025 second. From windows - 1.3-2sec but network interaction only 0.022sec

There actually two problems that might be caused by same thing or different, no idea.

Postgresql not sending packet for 1.8 second for unknown reason
Even if first problem eliminated and network interaction down to 0.022 sec whole thing still take 1.3-2 sec instead of 2.5-3.5 (  using either psql or psycopg2)


texts: MySQL 8 Geospatial Queries, MakeEnvelope Workaround?

texts:
I'm trying to work with some data from Google Maps and have a few locations along with their bounding boxs (northeast, southwest) data stored.

Using that data, I have the following query...

SELECT
    *
FROM
    `address_components`
WHERE
    MBRContains (
        ST_MakeEnvelope (
            ST_GeomFromText ( 'POINT(39.643950680292 -79.981536819708)', 4326 ),
            ST_GeomFromText ( 'POINT(39.641252719708 -79.984234780292)', 4326 )
        ),
        `location`
    );

The problem I'm running into with this though is that it's saying:

  3618 - st_makeenvelope(POINT, POINT) has not been implemented for geographic spatial reference systems.

How can I work around this issue?

Additionally, how would I change this so that instead of hard-coding my bounding box, I can select a row based on a place_id and then find all records that its location falls within the envelope of?


texts: Inline function Plan Properties in SQL Server 2008

texts:
Currently I use the below two queries to get some properties from the execution plan in stored procedures, the problem arises when I try to run it in a SQL Server 2008 version. Do you know if there is something similar to get these atributes for a SQL inline table-valued function in SQL Server 2008?

First query

SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    SetOptions                          = PlanAttributes.value
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (N'db')
AND
    object_id = OBJECT_ID (N'procedure')
AND
    PlanAttributes.attribute = 'set_options';
GO

Second query

SELECT
    ExecutionPlan                       = QueryPlans.query_plan ,
    CachedDateTime                      = ProcedureStats.cached_time ,
    LastExecutionDateTime               = ProcedureStats.last_execution_time ,
    ExecutionCount                      = ProcedureStats.execution_count ,
    AverageElapsedTime_Microseconds     = CAST ((CAST (ProcedureStats.total_elapsed_time AS DECIMAL(19,2)) / CAST (ProcedureStats.execution_count AS DECIMAL(19,2))) AS DECIMAL(19,2)) ,
    ANSI_NULLS_Value                    = IIF (CAST (PlanAttributes.value AS INT) & 32 = 32 , N'True' , N'False') ,
    ANSI_PADDING_Value                  = IIF (CAST (PlanAttributes.value AS INT) & 1 = 1 , N'True' , N'False') ,
    ANSI_WARNINGS_Value                 = IIF (CAST (PlanAttributes.value AS INT) & 16 = 16 , N'True' , N'False') ,
    ARITHABORT_Value                    = IIF (CAST (PlanAttributes.value AS INT) & 4096 = 4096 , N'True' , N'False') ,
    CONCAT_NULL_YIELDS_NULL_Value       = IIF (CAST (PlanAttributes.value AS INT) & 8 = 8 , N'True' , N'False') ,
    NUMERIC_ROUNDABORT_Value            = IIF (CAST (PlanAttributes.value AS INT) & 8192 = 8192 , N'True' , N'False') ,
    QUOTED_IDENTIFIER_Value             = IIF (CAST (PlanAttributes.value AS INT) & 64 = 64 , N'True' , N'False')
FROM
    sys.dm_exec_procedure_stats AS ProcedureStats
CROSS APPLY
    sys.dm_exec_query_plan (ProcedureStats.plan_handle) AS QueryPlans
CROSS APPLY
    sys.dm_exec_plan_attributes (ProcedureStats.plan_handle) AS PlanAttributes
WHERE
    database_id = DB_ID (N'db')
AND
    object_id = OBJECT_ID (N'procedure')
AND
    PlanAttributes.attribute = 'set_options';
GO