texts: mysqldump incremental backup -- slight confusion

texts:
I am Avinash,

I have a slight confusion about incremental backups.
I am going through the MySQL 5.7 Docs to study the backup process using mysqldump.
I am not using any master-slave configuration. I am only willing to have Point in Time Recovery capabilities. I am aware of the xtrabackup tool, but I want to use the logical backups as well.

The Docs explain how to make a full backup of InnoDB tables

mysqldump -u user -ppasword --single-transaction --flush-logs --master-data --all-databases > full_backup_sunday_1PM.sql

This will make a full backup of all the changes made since mysql-bin.0001 till let's say mysql-bin.0007. Flush logs will flush the logs and switch the current file and all the changes till mysql-bin.0007, mysqldump will be aware of.

--master-data option will print two lines to indicate the point in time recovery beginning time and master log position.

My confusion is at the following command (printed as it is from the docs)

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
--all-databases > backup_sunday_1_PM.sql

in the first command the author uses only --master-data and in the second command he/she uses --master-data=2.
I don't understand the difference. Could you experts please help me out?

Does --master-data=2 indicate that it's an incremental backup of level 1?
Does it indicate anything related to master-slave configuration?
What command should I use to make incremental backups?

The second command again mentions backup of Sunday itself, that's why may be I am getting confused. The author uses Sunday as full backup (in the first command) and under incremental backup (for Monday) explanation (second command) also he uses Sunday in the dump file name.

Thanking you.
Regards,
Avinash


texts: Low Cardinality of Index performing better than High Cardinality?

texts:
For testing purposes I have 2 Tables TableA and TableB in an mysql-InnoDB, both with the same columns

id bigint not null auto_increment,
depot smallint not null,
custno bigint not null,
range_from bigint not null,
range_to bigint not null,
depot2 smallint not null,
primary key (id)

and the same data (~5M rows). Now TableA has the index

unique index (range_from, range_to, custno, depot)

and TableB has the index

unique index (depot, range_from, range_to, custno)

The cardinality of "depot" is very low (~100 different values), the cardinality of "custno" is medium, and the cardinality of range_from and range_to is very high.

The Select

select sql_no_cache custno,depot,range_from,range_to
from TableB
where $value between range_from
                 and range_to
  and depot=$other;

takes about .8 seconds.
Explain says:

+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+
| id | select_type | table     | type  | possible_keys | key        | key_len | ref  | rows    | Extra                    |
+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | TableB    | range | range_from    | range_from | 8       | NULL | 1992762 | Using where; Using index |
+----+-------------+-----------+-------+---------------+------------+---------+------+---------+--------------------------+

Now the same select on TableA takes .03 sec and here is what explain says:

+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+
| id | select_type | table    | type  | possible_keys | key   | key_len | ref  | rows   | Extra                    |
+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | TableA   | range | depot         | depot | 10      | NULL | 107736 | Using where; Using index |
+----+-------------+----------+-------+---------------+-------+---------+------+--------+--------------------------+

Even though the Index starts with a low cardinality column the select outperforms the other index by a factor of at least 20.
Can someone please explain this to me like i am 4 years old? Every article I read so far advocated for using the high cardinality values first in indices.

Mysql version is 5.5.32 btw.


texts: How to migrate a database that has a database master key and certificate for encryption

texts:
Morning y'all.

I'm a little stumped at this one. We have a database that we encrypted a column in a table. This is the master key that I created and the Certificate & Symmetric key. I need to migrate this database to another instance of SQL, but after I migrate the database, I cannot decrypt the information.

Help?

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'omitted';

CREATE CERTIFICATE SecureLoginPasswords
   WITH SUBJECT = 'SecureLogin Customer Passwords for Website';
GO

CREATE SYMMETRIC KEY SSN_Key_01
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE SecureLoginPasswords;
GO


texts: In mongodb, is "sh.getBalancerState() is false" the same as "sh.isBanlancerRunning is false"?

texts: During upgrading config servers to use WiredTIger, I stopped balancer using sh.setBalancerState(false), and then I run sh.getBalancerState(). The output is false. Does this mean the balancer is not running? After this, I just started to upgrade the config servers to use WiredTiger. But after reading the document carefully, I am not sure whether no migrations are in progress after running sh.setBalancerState(false)? If some migrations are running, but I backup the config data and stopped the config servers one by one. What is the bad effect? Now the config servers are all up with WiredTiger, how to check whether the config servers have the same data, especially config data, meta data....?


texts: How do I fix the Code Page in SSIS Lookup Transformation to be 65001?

texts:
I have an SQL server 2019 with databases and tables all set with the collation  Latin1_General_100_CI_AS_SC_UTF8.
The relevant table has code and desc columns which are both of data type varchar.
In SSIS project I have a single Data Flow component:
I have a UTF-8 CSV file read with flat file connection, text column code to match is DT_STR, 65001
I have a Lookup that is set to "Full Cache" and loads the Latin1_General_100_CI_AS_SC_UTF8 table, but SSIS thinks the varchar columns are DT_STR, 1252
Finally the code in both CSV and lookup are matched and desc is sent to destination table which is on the same Latin1_General_100_CI_AS_SC_UTF8 collation. The destination component is set to AlwaysUseDefaultCodePage True and DefaultCodePage 65001.
I now get an error saying the column has more than one code page and cannot run the package.
If not for the mislabeled 1252, this package should run. I believe its something to do with ExternalMetadataXml, which is read-only and says all my lookup varchar columns are CodePage="1252".
If I manually edit the package .dtsx with npp and replace all instances of 1252 with 65001, the package can run and seems to do what I expected, as long as I never touch the lookup component again.. That seems a bit messed up of a solution tho, I am hoping there's someone else who has a cleaner way to fix this. Thanks.
edit 2021/03/10: I thought I should clarify, the lookup is using OLE to read a table from the SQL server, and is trying to match code column onto the code column read from the flat file. The problem is not the flat file, which is already recognised as DT_STR, 65001. The problem is the Lookup widget reading from a table that should be 65001, somehow ends up with 1252. If I didn't use OLE, and instead read from a UTF8 flat file to create the Lookup widget, there is no error.


texts: Granting permissions to a DB user - SQL Server

texts:
I need to grant select and update permissions to a DB user (TestUser) on TestDB database as per below.

When I press ok and re-check the permissions, they are not visible as can be shown below:

From my testing, only permissions at object level (e.g. columns, etc) are displayed here.

Can select permission on the database be visible from somewhere apart by executing T-SQL code? Is this a bug or by design? The same applies to user defined DB role permissions and it is very misleading.


texts:
How can I write this sql query in as hql:

select count(distinct s.id) as prepaid, count(distinct ss.id) as postpaid
from (select * from subscriber where subscriber.ispostpaid=0) s
join (select * from subscriber where ispostpaid=1) ss
where s.subscriber_status='active';

Or can make this query without using join by using only subquery.


texts: When SysServer and Server Property Don't Match

texts:
Pinal suggests removing and adding the correct name based on the below difference discrepancy to fix this CDC error.  That may be correct for a solution, but would this affect anything else?  I tested an ADO connection to both connection instances and they both work, but I'm curious if there will be anything else negatively impacted by his solution.

The code that shows a difference between the two:

SELECT CONVERT(SYSNAME,SERVERPROPERTY('SERVERNAME'))
SELECT * FROM master.dbo.sysservers

One being fully qualified, the other not.  Again, the ADO connection can hit both, but CDC is negatively impact by this.

His solution is like:

sp_dropserver 'domain\instance'
GO
sp_addserver 'full.domain.dom\instance', 'local'
GO

Would that cause connection issues, outside ADO - since I've confirmed ADO still connects to both instance names and types.


texts: Factors to consider while Rebuild/Reorganize of index in SQL Server 2012

texts:
I am getting confused with choosing Index Reorganize/Rebuilding of indexes based on avg_fragmentation_in_percent returned by sys.dm_db_index_physical_stats dm function.

Msdn says:

And also, avg_fragmentation_in_percent value says percentage of logical fragmentation (This is the percentage of out-of-order pages in the leaf pages of an index) existed in index.

Reorganize of index will always fixes physical ordering of pages. so even avg_fragmentation_in_percent is greater than 30% also I can consider Reorganize of index. It will be decreasing the percentage of fragmentation in index.

Please look below example

SELECT
    database_id, object_id,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent,
    page_count,
    avg_fragment_size_in_pages
FROM
    SYS.Dm_db_index_physical_stats (Db_id('BPIGTN_GAL_APP_TST'), Object_id('NM_PPA_PROJECTION_MASTER'), NULL, NULL, 'SAMPLED')

Result

 Database_id Object_id  avg_fragmentation_in_percent  avg_page_space_used_in_percent    page_count  avg_fragment_size_in_pages
    37       913490383        99.36                           60.15                      314           1.003

In above example, We can see 99 % of average fragmentation it means index is having more logical fragmentation(Unordering of pages is more).

So I am going to reorganizing of index which we have seen in above image.

Please look below example for further.

ALTER INDEX PK_NM_PPA_PROJECTION_MASTER_PROJECTION_DETAILS_SID_RS_CONTRACT_SID ON NM_PPA_PROJECTION_MASTER REORGANIZE
GO

SELECT database_id,
       object_id,
       avg_fragmentation_in_percent,
       avg_page_space_used_in_percent,
       page_count,
       avg_fragment_size_in_pages
FROM   SYS.Dm_db_index_physical_stats (Db_id('BPIGTN_GAL_APP_TST'), Object_id('NM_PPA_PROJECTION_MASTER'), NULL, NULL, 'SAMPLED')

Result

Database_id Object_id   avg_fragmentation_in_percent    avg_page_space_used_in_percent  page_count  avg_fragment_size_in_pages
37           913490383           4.18                                       98.91           191       9.55

Above, we have re-organized the index so after reorganize average_fragmentation reduced to 4 percent and also space used is increased from 60 percent to 98 percent.

Msdn Suggesting Avg_Fragmentation_in_percent value to be consider for Reorganize/Rebuild of index.

But Avg_Fragmentation_In_Percent value says only logical fragmentation not amount of space which is not used in index.

So, I am a little bit confused about choosing between Re-organize/Re-build of index.

Can we consider both Avg_fragmentation_in_percent and Avg_page_space_used_in_percent values for choosing Rebuild an index or Reorganize an index?

Any one please suggest me the exact parameters to be consider for choosing Rebuild or Re-Organize of an index?


texts:
I recently forgot my PostgreSQL 9.1 password on my windows 8 and it asks for password on pgAdmin III console.

Can I retrieve the password or reset it?


texts: Sending mail using DatabaseMail in SQL Server 2008

texts:
I have a profile called GPMail, and previously there was an account called  comments@guitarplayer.com, which was working as expected.

Now I want one more account with the same site, so instead of creating new profile I created new account called password@guitarplayer.com inside the same profile. But now when I try to send mail for password, I do not get the option for the account in the stored procedure -- sp_send_dbmail, so whenever I send the mail, the from field has the comments@guitarplayer.com address instead of password@guitarplayer.com.

Is there anyway that I  can specify the account name too with Profile name while sending mails?


texts: Do I need to create tablespace and also add all datafiles before importing a schema using datapump? Or does impdp add all data files itself first and then unloads data into it?


texts: How can can I get object definitions stripped of all comments?

texts:
When I want to know if a stored procedure or views references a given name (table, view or column), I need a version of the object definition stripped of all comments.

Is this possible by a SQL query in an usual DBMS system?

Edit:

For SQL Server I found this solution using [Microsoft.Data.Schema.ScriptDom.Sql].  Here is the minimal PowerShell Code:

$sql = @'
select * from PowerShell -- a comment
where
 psRefnr = 1
'@
$options = new-object Microsoft.Data.Schema.ScriptDom.Sql.SqlScriptGeneratorOptions

$sr = new-Object System.IO.StringReader($sql)

$sg =     new-object Microsoft.Data.Schema.ScriptDom.Sql.Sql100ScriptGenerator($options)
$parser = new-object Microsoft.Data.Schema.ScriptDom.Sql.TSQL100parser($true)

$errors = $null
$fragment = $parser.Parse($sr,([ref]$errors))

$out = $null
$sg.GenerateScript($fragment,([ref]$out))

$out

The advantage of this approach is that it uses the official Microsoft parser.

3rd Edit:

The basic methods to get the procedure definitions in T-SQL are as mentioned [here][1]
are

sp_help proc_name
SELECT [text] FROM sys.syscomments
WHERE OBJECT_NAME(id) =
'proc_name'
SELECT
OBJECT_DEFINITION(OBJECT_ID('proc_name'))

The second and third option return columns of type varchar(max) which can be used in LIKE clauses.

Historical Note:

When '*=' and '=*' became obsolete, syscomments returned the definition in chunks of 255 characters and the two could be placed into different rows.

But the second problem isn't resolved yet. Some faltering developers tended to keep old code as C - style comments, making scanning for code containing deprecated syntax difficult.


texts: Multidimensional pivot in postgres without crosstabs


texts:
What happens exactly when I create an index on the empid column of this employee table

CREATE TABLE employee
(
  empid number(5),
  ename varchar2(30),
  sal   number(10,2)
);

Let's assume the table contains the below data

empid   ename      sal
1       hema       10000
3       suma       10000
2       bheema     15000
4       bhama      20000
5       panama     25000

what exactly my doubt is what will be created and stored internally in index..... to improve the performance , whether sorted content of empid or some other information related to rows....?


texts: always on availability group with different speed disks

texts:
I'm looking to set up SQL Server 2012 installation with an Always On Availability Group, where the 'passive' replica will be hosted at another site on the WAN and using synchronous data commit - the idea being that we will have a hot standby with no loss of data in the event of a failure at our primary site.

One potential problem that I foresee is that the secondary site has slower storage than our primary site.  I don't care about that in the event of a failure, we can live with slow speeds for a period of time until the primary site is restored.  My worry is that, because we are using synchronous commit, that the slower disk speed at the secondary site will affect performance at the primary site during normal operation.

Is this a valid concern, or is it likely that the slower speed will be offset by, for example, the disk not having much read activity in comparison to the primary site?


texts: Return single row with one to many relationship based on evaluated conditions

texts:
Database is Postgres.
I have an issue whereby I have a table with the following columns: ID, Product_ID Customer_ID, Price and Item_Type.
This table is joined on Product_ID to another table. For a Given product ID there is the default price (where the Customer_ID is Blank) for a given item type (New, Renewal, Redemption etc.), there may also be a customer specific price list (where Customer_ID is joined to the Customers Table).
The issue is that this Database has been poorly maintained. For Item_Type there are 2 Prices that for this issue we are interested: Renew and Renewal. Some prices have a Renew price, some have a Renewal Price and some have both.
select p.id, pr.customer_id, p.period, pr.price, pr.item_type
from products p
right join prices pr on
pr.product_id = p.id and pr.period = p.period and pr.customer_id is null
where p.status = 'PURCHASED'

As an example this produces the following:
121751  8407508 12 29.95 RENEW
121751  8407508 12 35.95 RENEWAL

The cause is that this product has 2 different prices for Renew and Renewal - looking at the front end application code - if there is an item_type 'RENEWAL' then this price is used, if not then the 'RENEW' price is used.
I've tried using a case statement, both in the select and in a where clause:
case pr.item_type
    when 'RENEWAL' then pr.price
    when 'RENEW' then pr.price
    end

But I still get both rows - I'm not sure how to do a conditional and hierarchical lookup e.g. if Condition A is matched, then return row that matches Condition A, if no match is found, Condition B is matched, then return row that matches Condition B etc.


texts:
I am copying a database from a live environment to test environment.
I've made a .bak file from the live database and copied the file to the test server.

Question is, should I use "Restore Database" to add it to the test server or "Restore Files and Filegroups"?

(sometimes the database will already exist, sometimes it won't.  In either case I want to just have a clone of the original and don't care about overwriting)

After a bit of playing around it appears both achieve what I want - so what is the real difference, and does it matter which method I use for my situation?


texts: How to check there is enough disk space before overwriting a DB with restore command

texts:
I need to automate the restore of a database each night from a backup but I want to first check if there is enough disk space. In the below Microsoft link it says the verifyonly command is able to do this "Checking for sufficient space on destination devices" but I can't find any examples online of how to use verifyonly with a destination to test. How can I do this?

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sql?view=sql-server-2017


texts:
I've been learning about the bit string data type lately, and I am quite curious about:

At the bottom of this doc page there is the sentence:

  ... plus 5 or 8 bytes overhead depending on the length of the string

How are bit strings handled in other languages such as PHP, Java, C#, C++, etc., through drivers like Npgsql, ODBC, etc.

For question #1, using smallint or bigint will be much more storage efficient, and would perhaps offer a performance gain since integers are supported everywhere. Most programming languages handle bit operations on integers with ease. If that is the case, what is the point of introducing the bit-string data type? Is it Only for cases that need a large amount of bit masks? Bit field indexing maybe? I'm more curious about how bit field indexing is done in PostgreSQL.

For #2, I'm confused, more than curious. For example, what if I store week day bit masks in a bit(7) field, one bit for a day, with lowest bit representing Monday. Then I query for the value in PHP and C++. What will I get? The documentation says I'll have a bit string, however a bit string is not something I can use directly - as with integers. Then in this case, should I give up on bit field?

Can anyone elaborate why and when I should use bit or bit varying?


texts: Is it good practice to have multiple keyspaces in Cassandra?

texts:
I've Cassandra configured on Amazon EC2 having 3 nodes(instances) in single cluster. Now what I want to do is give some space on Cassandra to my clients by creating separate keyspace for each in a single cluster. Number of clients will increase day by day so there is no fix number of keyspaces I can assume to be created.

Will there be a performance issue if I create too many keyspaces in single cluster?

If it's not good practice, then Is there any other workaround to fit my need? I don't want to configure multiple instances of Cassandra.

If Cassandra is not the right choice for this kind of architecture, any other NoSQL database which fits my needs and How?


texts: Suggestion required for Large Database Optimization and performance

texts:
We are creating a large database with estimated growth rate 200 million+ records per annum. The database is use for analysis purpose and we will generate different tabular and graphical reports from available data. we've some concerns and require technical input and advices.

What impact we may see in future for very large table? Is there any limitation on number of records in one table or size of table?
What is the best way to index large data tables like this?

Any suggestion for optimization and performance of database is also appreciated.


texts: SQL Server Management Studio cannot connect because of common algorithm

texts:
I just installed SQL Server Management Studio, and I'm trying to connect to SQL Server 2014. It shows an error message "A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)"

My co-worker's SSMS connects with no issues, so I'm sure the server is working.

From what I've found so far, it's because of the TLS version issue, and I should upgrade SQL Server. Unfortunately, I don't control the server, just the client, and I have no idea when the DBA will get around to updating. I can't just wait around for them to find time to update.

I tried both SSMS 2016 version 13.0.16106.4 and SSMS v17.9.1 version 14.0.17289.0.

What can I do on my machine to make sure it connects to the server?


texts: Explain doesn't show indexed column as key

texts:
Consider the following table structure

CREATE TABLE `games` (
 `game_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `players_no` tinyint(4) DEFAULT NULL,
 `startedon` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `game_type` enum('R','C') NOT NULL DEFAULT 'R',
 `expirydatetime` datetime NOT NULL,
 `users_info` text NOT NULL,
 `lastupdate` datetime NOT NULL,
 `count_temp_user` int(1) NOT NULL DEFAULT '0',
 `msg_pid` varchar(3) NOT NULL,
 PRIMARY KEY (`game_id`),
 KEY `startedon` (`startedon`),
) ENGINE=InnoDB AUTO_INCREMENT=11009966 DEFAULT CHARSET=utf8

Now,Consider the following query

SELECT * FROM `games` WHERE DATE(games.expirydatetime)<CURDATE() AND `players_no` = 2

When i Explain the above query i have the following result

id|select_type|table|type|possible_keys|key |key_len|ref|rows|filtered|Extra
 1 |SIMPLE     |games|ALL |NULL         |NULL|NULL|NULL|314|100.00|Using where

If i add an index on expirydatetime,i still don't see expirydate in key column after explain.Any Explainations for this behavior?Is it because expirydatetime is used within a mysql in-built function?

Will mysql still consider the index on expirydatetime?


texts: SQL Server Automate refresh of one table only from one environment to another

texts:
I'm looking for a best practice/option to refresh one table only from Prod to Staging environment.

Technical Details:

The table in Source is loading daily with new data.
The table in destination should represent new data only when refreshed.

Some of the options I'm thinking:

Replication (Transactional)
SSIS
TSQL Script - SQL Agent Job

My first preferences would be to go with option 3. Will this be a good option? If yes can someone guide me to a script.


texts: Are there any Database functions which allow access to other network services in PostgreSQL

texts:
I have set up a new postgres installation and have to meet the security requirements from my company.
There is one requirement were i'm not quite sure:

Database functions which allow access to other network services (e.g. ,SMTP, HTTP, SNMP, FTP etc.), must be deleted or deactivated.
Motivation: Some database systems provide functions that are normally offered by an application server. For instance it is possible via special stored procedures to send e-mails or launch web queries to external systems.

I have installed postgres (PostgreSQL) 9.3.4. Are there any functions like this in a standard installation on Red Hat Enterprise Linux Server release 6.5 (Santiago)?


texts: counting how many likes one user gives another

texts:
We recently installed a "like" extension on our phpbb message board, which is stored in a table we'll call "likes". I've been struggling to construct a query I've called "admirer", where it counts how many likes one user has given another. The output would ideally look like the following:

user_id_to | like count | user_id_from
---------------------------------------
    sam    |      6     |      mike
    sam    |      3     |      john
    mike   |      4     |      sam
    john   |      5     |      mike

Hopefully that helps. I have a query where it counts how many likes users get from a specified user_id in the WHERE clause as shown below:

SELECT users.username, COUNT(likes.like_id) AS love
FROM likes LEFT JOIN users ON users.user_id = likes.user_id_to
WHERE likes.user_id_from = [someone's user id]
GROUP BY users.username
ORDER BY love DESC;

However, I'm struggling to modify it without the WHERE, and have it display the count of likes from any user to any user. Any suggestions would be greatly appreciated.


texts:
We are setting up password rotation policy to rotate account/password quarterly. We are also planning to use MySQL/Maria/Percona Auditing. Need to figure out the most efficient and feasible way to achieve both with minimum intervention while at password rotation.

  Explanation: I don't want to edit/add/change audit filter rule every
  quarter once we rotate user accounts.

Possible tried solutions:

Tried auditing ROLE with permissions which is attached to application
accounts (subject to rotation) with NO LUCK as auditing filter based
on login accounts.
Configuring exclude_account to (MySQL_schema@localhost) hence logging
for all other FUTURE user accounts by default DOESN'T work as some
process and port monitoring (which runs without mysql user account)
continuously fills up audit logs which is again Problem.
Workable solution is to pre-configure some x accounts with passwords
and enable ALL of them in auditing from Day1. Activate/Deactivate one
account every quarter one-by-one. Reusing same accounts with
different passwords once all accounts have been used.

Looking for More efficient solution or if someone has managed to work out similar issue using audit filtering rules?


texts: Is it possible to restore a database from corrupted .mdf file?

texts:
I know that the answer of the question may vary depending on what exactly is broken in each .mdf file - only looking for advice if there is another way to approach this.

So, I have received some bad news from the database administrator. Something like this happened last night (that's the only info I have from him):

a drive failed
the drive was replace but during the rebuild it failed again .. and again
it was new drive, so it was moved to a different drive slot on the chassis
doing the above, I mistakenly pulled the cachcade drive which in turn locked up any volume with cachecade protection
after re-inserting the cachecade drive, drive activity resumed (drives went to a “locked” state)

It seems the above steps lead to .mdf files corruption and not working properly. So, we started to force attached them to new SQL instance, following the steps below:

Create new database
delete its files
add a corrupted .mdf and ldf files and restore them with the script below:

USE master GO
ALTER DATABASE [] MODIFY FILE(NAME='', FILENAME= '')
ALTER DATABASE [] MODIFY FILE(NAME='', FILENAME= '')
GO

ALTER DATABASE [] SET ONLINE
GO
DBCC TRACEON(3604)
GO
ALTER DATABASE [] SET EMERGENCY
GO

ALTER DATABASE [] SET SINGLE_USER
GO
DBCC CHECKDB('[]', REPAIR_ALLOW_DATA_LOSS)
WITH ALL_ERRORMSGS
GO

ALTER DATABASE [] SET MULTI_USER
GO

He also told me that Repair errors are different per database and some database cannot be repaired at all.

So, have I any other options here or I need to check how the above goes for each database and move some of data (if possible) to database restored from older copy by hand?


texts: What's the greatest value for postgres maintenance_work_mem?

texts:
I set 1960MB successfully for maintenance_work_mem, and got about 30% performance improvement on my COPY batch inserts.

So I'm trying to run the import with even more mem (my machine has 32GB).
Problem: Whenever I set any greater value, eg 2500MB, the postgres x64 server won't start up.

Do I have to configure another property? Or is it just not supported to give the mem values of 2GB+?


texts:
I have a SQL Server 2016 query that returns 127K rows. You can find the query and query plan here. Let me know if you also need tables structure.

I need to join with a table that has only 20 rows, which acts as a replacement for one of the products. In other words, I query products from a main table but, under certain conditions, some of them can be replaced by others.

Problem is that, for that simple table, I have 254K logical reads. I've tried LEFT JOIN and OUTER APPLY.

Any suggestion about how to replace this to avoid that amount of logical reads? Just to mention, only 1 product has a replacement.


texts: Why is Postgres not respecting my log file name convention dictated by "log_filename"?

texts:
I’m using Postgres 9.5 on Ubuntu 14.04.  I have these settings set in my /etc/postgresql/9.5/main/postgresql.conf file

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on           # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 50MB                # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

Although I have restarted my server using the

sudo /etc/init.d/postgresql restart

command, when I run some transaction and check my logs, the log files are not respecting my files names …

myuser@mymachine:~$ ls -al /var/log/postgresql/
total 1912144
drwxrwxr-t  2 root     postgres       4096 Dec 31 14:27 .
drwxrwxr-x 14 root     syslog         4096 Dec 31 06:55 ..
-rw-r-----  1 postgres postgres 1957826560 Dec 31 14:30 postgresql-9.5-main.log
-rw-r-----  1 postgres postgres     194785 Dec 26 06:46 postgresql-9.5-main.log.1

Why is Postgres not respecting the filename convention I specified in the configuration file and how do I get it to?


texts: Is it possible to wrap aggregate functions in Postgres?

texts:
Postgres' string_agg(expr, delimiter) function is great.  But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is what I want 9 times out of 10.  With a non-aggregate function, I could simply do this:

create or replace function public.string_agg(text)
returns text
as $$
    select string_agg($1, ', ');
$$ language sql
immutable;

But since string_agg is an aggregate function, this doesn't work. It complains that the field I'm passing to it needs to be in the GROUP BY clause, which tells me that the query engine doesn't understand that my wrapper is an aggregate function.

I looked at the docs for user-defined aggregates (http://www.postgresql.org/docs/9.1/static/xaggr.html and http://www.postgresql.org/docs/9.1/static/sql-createaggregate.html) but it's not obvious to me how I could define my new string_agg(text) using the existing string_agg(text, text).  It seems I would have to define my new function from scratch, which is more complexity than I want to take on, and I'm also not entirely confident I could mirror the built-in string_agg's behavior perfectly.

So is there some technique for creating a simple wrapper than I'm not seeing?  Or am I going to have to Do More Work?


texts: As far as I can tell, the jcc driver shipped with 11.5 (https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads) still uses JDBC 4.1. Any rumours of, if a later fixpak will switch to JDBC 4.2 ( https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/jdbc_42.html )? I'm primarily interested in the change in date/time handling.


texts: We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that state for a long time. Is there a solution to drop these tables without mysql hanging. We have a machine which has a memory of 122GB and swap of 60 GB. We use an InnoDB Engine Hope the information suffices kindly help us with this issue.


texts: Replace all column values with other values in the same column according to primary key

texts:
I'm querying the following table:

id
value
applicable

id1
5
Unknown

id1
5
Unknown

id1
6
Yes

id 2
5
Unknown

id 2
5
No

id 2
3
Unknown

I would like to replace all "Unknowns" in the applicable column, with the non-unknown values pertaining to each id.
Ideally, I would like to see this:

id
value
applicable

id1
5
Yes

id1
5
yes

id1
6
Yes

id 2
5
No

id 2
5
No

id 2
3
No

Sorry if this a very easy question, I'm new to this!
Thank you


texts: Must declare the scalar variable in cursor SQL SERVER

texts:
I have this problem with this code.
I don't know why, because before set values.I declare a variable.

Moreover, How i can execute every results that cursor give

Error message: Msg 137, Level 15, State 1, Line 5
Must declare the scalar variable "@ejecutable".

My code:

declare @Userdb sysname
declare @ejecutable nvarchar(max)
set @ejecutable = ''
set @Userdb = 'SD-A-YF-LG-D-SQL30_ICCORE_reader';
declare cr1_database cursor local forward_only
for
select 'use ' + d.name + ';
go
if not exist
(
   select 1
    from sys.database_principals dp
    where dp.name = ''' + @Userdb + '''
)
begin
   create user ' + QUOTENAME(@Userdb) + '
   from login ' + QUOTENAME(@Userdb) + '
       with default_schema = dbo;
end
exec sp_addrolemember @RoleName = ''db_datareader''
   , @MemberName = ''' + @Userdb + ''';
go
'
from sys.databases d
where d.database_id > 7
open cr1_database;
fetch next from cr1_database
into @ejecutable;
while @@FETCH_STATUS = 0
begin
   print (@ejecutable);
   fetch next from cr1_database
   into @ejecutable;
end
close cr1_database;
deallocate E cr1_database;


texts: Setting up log shipping with an availability group

texts:
In my production environment I have a SQL Server 2012 AlwaysOn AG.

The Server1 there is database "MessagesDB" and in an AG to secondary Server2 (in synchronous mode).

I want to setup log shipping from Server1.MessagesDB to a data warehouse (Server3).

Is it possible?


texts: Metrics to prove or disprove the need for additional DBA's?

texts:
We currently have 2 DBA’s, me and one other guy and we both feel the need for an additional resource (or two). I have had the conversation with my direct manager on this topic a couple different times, but am having difficulty in getting the notion sold. Most recently our manager shared with us that it is really good to come up with our “soft values” list of things that we want or feel we need to be doing, but what will really help is to come up with “hard values”, showing dollar savings.

Could you help me in understanding first of all if our perceived need is legitimate, and if so, how do we go about coming up with this “hard values” list. I feel like we could come up with those things that we need to do in order to save the company money, but that could just get put at the front of our priorities list, and that much more stress on us in the process.

To give you a high level look at our environment, here are the things that I am looking at in terms of what we are providing support for.

  Production servers – 30+
  Production Databases – 200+
  SQL Versions – 2008/2008R2/2012, looking into 2014 now, and 2016 later in the year
  Applications(3rd party and home grown) – 20+
  Application Teams Supported – 6
  Virtualization – 75% virtualized 25% physical
  Clusters – 3+ more planned
  Replication – 1 distribution server, 2 subscription servers, 24 subscriptions, 6 publication servers, 12 publications
  Log shipping – 8 primaries +more planned, 4 secondaries + more planned, 49 log shipped databases + more planned
  Availability Groups – None at the moment, but exploring the possibility
  Average application upgrade/installs per year that drive version change or DBA resources – 2-3
  Patching (SP, CU’s) – Non-existent at the moment unless an issue comes up
  Cumulative database size – 14TB+
  Reporting Servers – One scale-out deployment consisting of 2 servers, neither of us are well versed in SSRS
  Analysis Server – Two servers, neither of us are well versed in SSAS

What metrics should we use to prove or disprove the need for additional DBA's?

My initial intention was demonstrating our current capacity vs. our planned projects and repeating tasks, but we need to demonstrate the ROI on hiring additional resources.


texts: How can I alter an existing Primary Key on SQL Azure?

texts:
I want to modify an existing primary key on a SQL Azure table.
It currently has one column, and I want to add another.

Now, on SQL Server 2008 this was a piece of cake, just did it in SSMS, poof. Done.
This is how the PK looks like if I script it from SQL Server:

ALTER TABLE [dbo].[Friend] ADD  CONSTRAINT [PK_Friend] PRIMARY KEY CLUSTERED
(
  [UserId] ASC,
  [Id] ASC
)

However, on SQL Azure, when I try to execute the above, it will of course fail:

Table 'Friend' already has a primary key defined on it.

Fine, so I try to drop the key:

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

Ok, so I try to create a temporary clustered index in order to drop the PK:

CREATE CLUSTERED INDEX IX_Test ON [Friend] ([UserId],[Id])

Which results in:
Cannot create more than one clustered index on table 'Friend'. Drop the existing clustered index 'PK_Friend' before creating another.

Great, a catch22 moment.

How do I add the UserId column to my existing PK?


texts:
I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship.

Example: An artist has an album (the album cannot exist without an artist), the album also has many tracks, but the same track can exist in many albums.

So we have a many-to-many relationship between the album and the tracks.

If the album is a weak entity, then its primary key is a foreign key referencing the artist, thus it cannot be a foreign key to another table representing a many-to-many relationship.

The question is: is it possible to have this kind of relationship in SQL, and if so, how do I express it?


texts: Multiply elements of equally sized postgresql array

texts:
In Postgresql 9.6, is there a clean way to multiply corresponding elements within two equally sized arrays?

For example

a = [1,2,9,4,5]
b = [5,8,4,0,12]

function(a, b) would return [5, 16, 36, 0, 60].

I'd prefer it as a function like this, since it'd be used in multiple places.


texts: How to design a database with rare multiple values

texts:
I have a "user" table, with many attributes including "nationality".
the user can have multiple nationalities, so it sounds as if I have to design a new table to store this multiple value.

but what if I stored the first nationality in "user" table, and each of the remaining nationalities belonging to the same user would go to the nationality table.

I am thinking about this solution since it's pretty rare to have a user with multiple nationalities.. so, I can keep my design clean this way.

Is this feasible? is it a bad design?


texts:
I have a data file with 100 million documents. The number of documents is expected to always remain between 100 million and 200 million for atleast a year. I have 15 GB Ram. Currently the update operation takes 2 hours to load. Suppose I would like to increase the performance to require 40 minutes to load, will increasing RAM be required? How much RAM would I need to add in order to achieve that increase? Please do let me know if I must provide additional details.

I have indexes for the updated fields as well as I have set --nojournaling option. Currently I have optimized the update process by using syncdelay 0 and then manually fsync-ing every 100K records. Then I implemented a system with only in-place updates rather than updates that add new fields or increase document size. Now I feel that I am at a point where increasing RAM might be the only way to go.


texts:
I am using SQL Server 2016.
I have setup SQL Agent for Full backup (Weekly) and Transaction Log backup (Daily).
Backup transaction log into a single file using:
BACKUP LOG [XXX] TO  DISK = N'E:\SQLDB\Backup\XXX_Trans_Log.bak' WITH RETAINDAYS = 28, NOFORMAT, NOINIT,  NAME = N'XXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

What I expected is only 28 days transaction log backups will be kept. But now I just found All transaction log backups are kept. So the file grows to very large in size.
After finding this, I change the plan to backup log in a separate file each day by:
DECLARE @Ver VARCHAR(2)
DECLARE @File VARCHAR(100)

SET @Ver = (SELECT RIGHT('0' + CAST((DATEPART(DAY,GETDATE())) AS VARCHAR(2)),2))
SET @File = 'E:\SQLDB\Backup\XXX_Trans_Log_' + @Ver + '.bak'

BACKUP LOG [XXX] TO DISK = @File WITH RETAINDAYS = 28, NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10

I am testing it on testing environment.  Basically it works but I found the log size is quite large, as nobody is using the environment.  Here are the figures:
DB file size = 500MB (38% free)
Log file Size = 865MB (38% free)
DB Backup (Weekly) = 311MB
Trans Log Backup (Daily)= 277MB
Any reason why the trans log backup so large?


texts: Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I need to create separate filegroup?


texts: Why isn't the transaction log releasing space?

texts:
I have a database running under the simple recovery model. The database contains three narrow tables. For two of the tables, data is populated by insert statements. The third table is initially populated by an insert statement, and then the record is completed by an update statement. On average, the database sees 4000 inserts per minute and 2000 updates per minute. It's active, but not exceedingly so.

The database has 10GB of space allocated to the transaction log. According to the Disk Usage report / DBCC SQLPERF(LOGSPACE), the log space usage is steadily increasing. Yesterday, usage was at 33% and growing. Overnight, it has increased to 48% and continues to rise. DBCC OPENTRAN returns no open transactions on the database. Nothing stands out to me when I run sp_whoisactive.

Another database on the same server, with similar activity, a slightly larger transaction log size, and simple recovery model, is cycling between 0% and 10% usage, which is the behavior that I am used to seeing for the first database. What other steps can I take to determine the cause of this behavior and ensure that the transaction log will not fill up entirely?

*Given that I cannot justify the growth in log usage, please assume that "allocating more space to the transaction log" is not a viable solution.


texts: Unable to get the DBMS Logs in Oracle SQl Developer

texts:
I am using Oracle SQL Developer (Version 18.4.0.376) and Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production "CORE  10.2.0.1.0 Production" TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production.

In the SQL Query Builder the output of DBMS_OUTPUT.PUT_LINE is not disabled automatically.

See the following screen shot:

Can any one suggest to me, how I can get rid of this issue?


texts: Readers blocking reader on readable secondaries

texts:
We recently migrated to AG setup for one of our VLDB's around 20+TB.
Current setup is Multi subnet AG with primary accepting mix workload while heavy reporting offloaded to readable secondary replicas.
Database in question has RCSI isolation level setup on primary.
Now i do not understand why we are seeing blocking on secondary where readers are blocking readers. I previously had a question where a DDL change in primary made REDO thread on secondary wait due to SCH_M locks but now situation is different.
I am seeing some basic SELECT queries blocking other SELECT queries. Why is this happening on the readable S replica?
Also the tables in this VLDB are majorly partitioned. Does having or setting lock escalation to auto help? Currently set to Table as default for VLTbls


texts: Logging Stored Procedure Parameter Values

texts:
I've worked in an SQL Server environment before where logging was made part of our stored procedures to capture execution start/end, parameter values and error messages which I found very useful and is something I'm looking to introduce in a new environment.

The tables used for this logging looked like something below, parameters were captured using INSERT statements into a table with the with values being implicitly cast to NVARCHAR.

CREATE TABLE dbo.Execution
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   SchemaName          NVARCHAR(128)               NOT NULL
,   ProcedureName       NVARCHAR(128)               NOT NULL
,   ExecutionStart      DATETIME                    NOT NULL
,   ExecutionEnd        DATETIME                    NULL
,   ExecutionFailed     BIT                         NOT NULL
)

CREATE TABLE dbo.ExecutionError
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   ExecutionId         INT                         NOT NULL
,   CustomErrorMessage  NVARCHAR(8000)              NULL
,   SqlErrorMessage     NVARCHAR(8000)              NULL
)

CREATE TABLE dbo.ExecutionParameter
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   ExecutionId         INT                         NOT NULL
,   ParameterName       NVARCHAR(128)               NOT NULL
,   ParameterValue      NVARCHAR(MAX)               NULL
)

I've been reconsidering the ExecutionParameter table with the possibility of using an SQL_VARIANT data type so I could get the base data type if ever needed for analysis and reporting purposes without having to guess based on the name/value.

However this wouldn't work for parameters that have a data type of NVARCHAR(MAX) or VARCHAR(MAX) and would therefore need to still have the NVARCHAR(MAX) column there which would be NULL the majority of the time.

The use of SQL_VARIANT is tempting but I feel the original table structure works fine and can't really be made any better without making the process more complicated.

Is this something you have done previously, if so how did you implement it? Can you see room for improvement in the above schema without making it overly complicated?

Perhaps an additional table with details of the stored procedure and their parameters which can be referenced? Though I feel this would be difficult to maintain and become confusing as stored procedures are modified over time.


texts: SQL Server 2005 Database Mail Resends Historic Messages following SP4 Build 5324

texts:
I have applied SP4 Build 5324 to a SQL Server 2005 Enterprise active/passive cluster (Build pathway: 4053 to 5000 to 5324).

The SQL Server log contains zero errors.

The SQL Server Agent log contains the following records continuously written since the Build was completed:

[LOG] Unable to read local eventlog (reason: The event log file is
corrupted)
[LOG] Successfully re-opened the local eventlog - NOTE: Some events
may have been missed

Email messages that are marked with dates days and months prior to applying SP4 Build 5324 were subsequently resent [edit] hundreds of times [/edit] ("storm" would be an apt description).

[update] I've confirmed that there were zero unsent items (sysmail_faileditems and sysmail_unsentitems views) prior to applying the service packs. [/update]

If you have encountered this behavior, I would appreciate learning if you identified the cause.

Thank you.

PS: Yes, it is well past the time to upgrade to a newer version of SQL Server. Let's leave that discussion for another day. ;-)


texts: Having troubles creating a specific TRIGGER in MySQL

texts:
So I am trying to implement a trigger in MySQL that is triggered once a new user is registered.
The relation of my tables are:
USER_ACCOUNTS has primary key USER_ID.
RACE_TRACKS has primary key RACETRACK_ID.
HIGHSCORES has as foreign_keys USER_ID and RACETRACK_ID.

The trigger I am trying make, must create records in HIGHSCORES table in a way that each NEW.USER_ID has a record for each of the RACE TRACKS (RACETRACK_ID).

Using phpMyAdmin database management tool I came to this point.

BEGIN
DECLARE trackID INT;
DECLARE done INT DEFAULT FALSE;
DECLARE trackCURSOR CURSOR FOR SELECT USER_ID FROM INSERTED WHERE USER_ID = NEW.USER_ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

INSERT INTO HIGHSCORES SELECT trackID FROM RACE_TRACK;

OPEN trackCURSOR;
    REPEAT
    FETCH trackCURSOR INTO trackID;
    IF NOT done THEN
            BEGIN
            INSERT INTO HIGHSCORES (RACETRACK_ID, USER_ID)
            VALUES (trackID, NEW.USER_ID);
            END;
    END IF;
    UNTIL done END REPEAT;
CLOSE trackCURSOR;
END;

I am not getting any errors but when I am testing it if it works nothing happens. I am a bit confused to say the least because I do not have experience with triggers. If there is any way to enlighten me I would appreciate it.

Thank you in advance.

EDIT:
After some feedback more searching this is how my trigger looks now.

BEGIN
DECLARE v_RACETRACKID INT;
DECLARE FINISHED INTEGER DEFAULT FALSE;
DECLARE racetrackCURSOR CURSOR FOR SELECT RACETRACK_ID FROM RACETRACK;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = TRUE;

OPEN racetrackCURSOR;

get_racetrackID : LOOP
    FETCH racetrackCURSOR INTO v_RACETRACKID;
    IF FINISHED = TRUE THEN
          LEAVE get_racetrackID;
    END IF;

    INSERT INTO HIGHSCORES (RACETRACK_ID, BEST_LAP, BEST_RACETIME_1,
        BEST_RACETIME_2,USER_ID) VALUES (v_RACETRACKID,null,null,null
        ,NEW.USER_ID);
    END LOOP get_racetrackID;
    CLOSE

 END;


texts: ssms does not let me change to server authentication mode and I can not log in

texts:
I have been using SSMS and logging in through server authentication for a week now and yesterday when I tried to login it suddenly told me "no process on the other end of the pipe".
I researched online and it said the problem is that the security property for the server is set to windows authentication and has to be changed to server authentication. So I log in to SSMS through windows authentication first to change it, however it said that I do not have permission to change the authentication mode, which is weird because I ran the SSMS as admin and it was on server authentication previously.

Now I can not access my database at all as when I created the database it was under the SA account and it will just tell me I do not have permission to open the database or do anything basically.

Also just for a side info the problem arised after I restarted my computer I believe, the server property is suddenly changed to windows authentication and I cannot log in through the SA account nor can I change it back to server authentication


texts: Is it Possible to Rollback Table Deletion in Mysql

texts: We Have Mistakenly Dropped a Table in my DB and am using MySql . Is it Possible to ROLL Back or any other options . Please Provide your Valuable Suggestions


texts: Restrict access to my PostgresSQL/ Post GIS database used through QGIS

texts:
We have a small network of users accessing our QGIS data stored on a PostGIS database (in 3 schemas) which sits on my local drive in my computer. This drive is then shared with the others. Due to the other users being beginners in the GIS world I created a number of layer files (.qlr) for them which point back to the data in my PostGIS database. They can only see these layer files.

But I've just realised when they use these layer files they can still edit, add and remove the data.

How do I restrict this?

Is there a simple way in QGIS or even Windows? (setting permissions on the sharing folders does not seem to work)

The database was created through something called 'pgAdmin 4' and I've tried adding another user/role with different privileges but I can't seem to get that to work its seems to deny access completely or its all still wide open.

The privileges I can alter seem to be too high up the hierarchy all I need to restrict is the ability to write and delete to the schema's and tables, leaving myself with editing rights.

I have no idea how to write in SQL, I'm a geographer not IT. There is no Database Administrator here only me and I made this database just by following the steps on the pgAdmin help pages and some luck. So Im seeking idiot proof help.


texts:
Every time we are rolling out a new release same tables are trancated and filled with fresh data. This process is done on a Windows pc with scripts that are run in SqlPlus.

When we do this on our development environment the diakrites are inserted as upsidedown questionmarks (¿) in stead of (ä, ë, ï or ö).

If the data is insert by hand in PL/SQL Developer the data is inserted in the right way. Because of the great amount of data this is not exceptable to do every time. It is also not possible to do this in the acception and production database.

I have tried to find some solution for this problem on the internet, but cloud not find it.  Is there a solution for this problem?


texts: How to change the FROM clause of multiple views at once

texts:
I have multiple views that are the sources for a collection of scripts I use for loading data from NAV into our DWH, meaning the view collects fields from multiple tables in our NAV database and the loading script just has the view as its source.
The problem with this is that the client identifier on the NAV database is changing every couple of months.

As I obviously don't want to go into every single view and update the source table with the new client identifier, my question is, whether or not it is possible to change the source table of multiple views at once. I am using MS SQL Server 13 btw.


texts: Can a MS SQL Server be named different than host computer?

texts:
I have a virtual machine that was built by our IT department to host various processes. They named the computer something incomprehensible like "XYSVC105" and I am not allowed to change it. I have also installed MS SQL Server 2014 on this machine and everything is working great.

However, I have been searching for MS best practices but cannot seem to find if you are able to change the name of the SQL Server to be different than the host computer or if it's recommended not to do it. I would like to change the SQL Server name to something like "OPSRES".

Most of the reasons for this are internal. One of them being that this will be used by a multitude of users and developers. From experience they will remember a simple name like OPSRES and not XYZ145PPQ.


texts:
I'm using Sybase ASE version 16.0.0.1915, (follows t-sql syntax) and trying to create a new trigger. This new trigger emulates another existing trigger, however is slightly modified. It would be a whole lot harder to modify the existing trigger to address both "workflows", so copying into a new trigger with modified details and an order of "2" would be my preferred solution.
I'm reading in the docs, that in order to create a new trigger based on the same action, I need to set the order for the triggers to fire off. If no order is specified, it is assigned order of 0 (first).
Docs
So here is my trigger, the existing similar trigger does not have an order set. When I place "ORDER 2" into the create statement (after the name, before "on" in row 1), I keep getting syntax error -131.
I've also tried placing the "ORDER 2" after the table name, before "for", and after "insert", before "as". All producing the same error -131.
So what am I doing wrong? Is there some other problem with this trigger preventing me to use the ORDER clause?
create trigger "DBA"."WKM_autoFillCL143" on
"DBA"."case_checklist" for insert
as
if((select top 1 "inserted"."code" from "inserted") in( '143' ) )
  begin
    declare @parentRef integer,@desc varchar(255),@desc1 varchar(255),@checkID integer
    set @parentRef = (select top 1 "parent_ref" from "inserted")
    if(@parentRef <> '0')
      begin
        set @desc = (select "description" from "case_checklist" where "checklist_id" = @parentRef)
        if(@desc is not null)
          begin
            set @checkID = (select top 1 "checklist_id" from "inserted")
            update "WKM_RecordChecklistMapping" set "c143" = @checkID where "c142" = @parentRef
            declare @tabid integer
            set @tabid = (select top 1 "tab_id" from "WKM_recordChecklistMapping" where "c142" = @parentRef)
            set @tabid = (select top 1 "tab_id" from "user_tab2_data" where "tab_id" = @tabid)
            if(@tabid is not null)
              begin
                declare @recProvider varchar(255),@recsRequested varchar(255),@dateFrom "datetime",@dateTo "datetime"
                set @recProvider = (select top 1 "Provider_Name" from "user_tab2_data" where "tab_id" = @tabid)
                set @recsRequested = (select top 1 "Records_Requested" from "user_tab2_data" where "tab_id" = @tabid)
                set @dateFrom = (select top 1 "For_Dates_From" from "user_tab2_data" where "tab_id" = @tabid)
                set @dateTo = (select top 1 "Through" from "user_tab2_data" where "tab_id" = @tabid)
                set @desc1 = 'Receipt '+@recProvider+' Records? '+@recsRequested+', dates '+"coalesce"(convert(varchar(255),@dateFrom,1),'00/00/00')+' to '+"coalesce"(convert(varchar(255),@dateTo,1),'00/00/00')
                set @checkID = (select top 1 "checklist_id" from "inserted")
                update "case_checklist" set "description" = @desc1,"staff_assigned" = 'ZKS',"due_date" = ("today"()+7) where "checklist_id" = @checkID
              end
          end
      end
  end


texts: How to monitor flat file for changes then run a SQL import job

texts: I have a file that is a flat text file that needs to be imported into my SQL Server every day.  The problem is that I have to wait for someone else to download and place the file before I can import it.  The download process is from within a proprietary application and not from a website.  How can I automate the process by monitoring for the file change and then starting import job?


texts: Create trigger to insert rows from one table to another with sum operation (group by)

texts:
I have two tables in my database traffic_shift and traffic_daily. With following columns.

Table traffic_shift :-

day     month    year    toll_id    shift    car_single    car_return
 1       9        2016    1          1        10            5
 1       9        2016    1          2        100           50
 1       9        2016    1          3        200           100

 1       9        2016    2          1        15            20
 1       9        2016    2          2        30            40
 1       9        2016    2          3        45            60

 2       9        2016    1          1        12            6
 2       9        2016    1          2        24            12
 2       9        2016    1          3        36            18

 2       9        2016    2          1        18            9
 2       9        2016    2          2        36            18
 2       9        2016    2          3        54            27

traffic_daily table will be like this,

Table traffic_daily

day     month    year    toll_id    car_single    car_return
 1       9       2016     1          310          155
 1       9       2016     2          90           120
 2       9       2016     1          60           30
 2       9       2016     2          108          54

As you can see there are 3 shift in a day for any toll. So the functioning of the trigger should be :

it should sum up the car_single and car_return, date wise for each toll and should insert into the table traffic_daily as shown in above table.

Please help me out with this. I am just stuck at this problem.

Thanks in advance.

EDIT

This is what i could come up with.

DROP TRIGGER after_insert_shift;
DELIMITER $$

CREATE TRIGGER `test`.`after_insert_shift`
AFTER INSERT ON `test`.`shift`
FOR EACH ROW
BEGIN
INSERT INTO daily
SET
`day` = new.day,
`month` = new.month,
`year`= new.year,
toll_id = new.toll_id,
car_single = SELECT SUM(car_single)
    WHERE daily.day = new.day , daily.month = new.month, daily.year = new.month ,
    daily.toll_id = new.toll_id FROM shift GROUP BY
    shift.day,shift.month,shift.year,shift.toll_id;

car_return = SELECT SUM(car_return) WHERE daily.day = new.day ,
    daily.month = new.month, daily.year = new.month ,
    daily.toll_id = new.toll_id FROM shift GROUP BY
    shift.day,shift.month,shift.year,shift.toll_id;

END$$

DELIMITER ;


texts: select single row from table for a particular column combination

texts:
I have a table as below

Id... ColA... ColB... Isdefault

1...      100...    96...   1
2...      100...    96...   0
3...      101...    98...   0
4...      102...    99...   1

I want to have a select statement, where for a particular combination of ColA and ColB, I should get only one Row with the condition that if Isdefault with values both 0 and 1 present for a particular ColA,ColB combination, I should Only select one row with Isdefault=1 .

The output should look like below:

Id... ColA... ColB... Isdefault

1...      100...    96...   1
3...      101...    98...   0
4...      102...    99...   1

Note: Row with Id=2 is not present in the output because the same combination of ColA and ColB exists with isdefault=1

kindly help, thanx in advance.


texts: Restore Stored Procedure from Google Cloud MYSQL?

texts:
I accidentally overwrote a MYSQL stored procedure through the Workbench client connected to a GCP MYSQL instance. I tried restoring a previous backup to recover it, but that only restored the tables - no views or stored procedures.
Is it possible to restore MYSQL SPs from GCP MYSQL backups?


texts: How to convert rows into columns and query on them in Mysql?

texts:
I have three tables Patients, which contains the name of my patients, Controls which represents the UI controls which could be drawn for each illness , and ControlsValues table which contains the values of the controls submitted for each Patient

Lets have some data
Patients Table

|ID  | Name |
|-----------|
| 1  | Ara  |
| 2  | Sada |

Controls table

|ID  | Text | Type     |
|-----------|----------|
| 1  | age  | textbox  |
| 2  |alergy| checkbox |

Then the controlsValues table which is where I want to query at

|ID  | contrlId | value    | patientId |
|---------------|----------|-----------|
| 1  | 1        | 23       | 1         |
| 2  | 2        | true     | 1         |
| 3  | 1        | 26       | 2         |
| 4  | 2        | false    | 2         |

here my problem occurs when I want to return that patient from ControlsValues table that has the (controlId=1 AND value=23) and (controlId=2 AND value=true)
in this case the condition is on two rows not two columns which it is not possible , so I desided to change the rows into the columns depending on controlId but I dont know how and I have been searching for 2 days and seen a lot of samples but none of them helped me to solve my problem


texts: Trigger: Only raise error if related record was NOT created between now and last Sunday

texts:
I have a trigger that prevents inserts into a DELETES table under certain conditions:
CREATE OR REPLACE TRIGGER D202574_custom
BEFORE INSERT ON D202574
FOR EACH ROW
DECLARE
    v_upd_row NUMBER;
BEGIN
    SELECT COUNT(1) INTO v_upd_row
    FROM A202574
    WHERE objectid = :new.sde_deletes_row_id
    AND sde_state_id = :new.deleted_at;

    IF v_upd_row = 0 THEN
         raise_application_error(-20001, 'Deleting has been disabled via a custom trigger.');
    END IF;
END ;

I want to enhance the trigger so that it only raises an error if the related record in the base table was NOT created between now and last Sunday at 11:59 PM.

The relationship would be based on the OBJECTID column.

This is what the base table looks like:
CREATE TABLE A_TEST_BASE_TABLE
(
  OBJECTID          INTEGER NOT NULL,
  SHAPE             SDE.ST_GEOMETRY,
  CREATED_USER      NVARCHAR2(255),
  CREATED_DATE      TIMESTAMP(6),   --<<-- the relevant field
  LAST_EDITED_USER  NVARCHAR2(255),
  LAST_EDITED_DATE  TIMESTAMP(6)
)

What would it take to do this?


texts: Sql Server Dynamic partition with page compressed index

texts:
I created a table with partitions by following this 2012 partitioned index document.
I created partitions monthly based on Date_Id column;
CREATE PARTITION FUNCTION Fnc_Prt_Fact_Sales (INT)
AS RANGE RIGHT FOR VALUES ('20200101', '20200201', '20200301','20200401')

CREATE PARTITION SCHEME Prt_Scheme_Fact_Sales
AS PARTITION Fnc_Prt_Fact_Sales
ALL TO ([PRIMARY])

CREATE TABLE [dbo].[Fact_Sales](
   [Slip_No] [nvarchar](155) NULL,
   [Date_Id] [int] NOT NULL,
   [City_Id] [int] NOT NULL,
   [Store_Id] [int] NOT NULL,
   [Sales] FLOAT
) ON Prt_Scheme_Fact_Sales(Date_Id)
GO

 CREATE  CLUSTERED INDEX [Ix_Fact_Sales] ON [dbo].[Fact_Sales]
(
    [Date_Id] ASC,
    [City_Id] ASC,
    [Store_Id] ASC
) WITH (
   DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4)
)  ON Prt_Scheme_Fact_Sales(Date_Id)
GO

i want to add partitions by dynamically (monthly). If i do that, how do i make data_compression include new added partition.
On Blog, partitions are written by manually.
--> DATA_COMPRESSION = PAGE ON PARTITIONS (1,2,3,4)
if it matters, i ue sqlserver 2017 standart edition.


texts:
I am querying some data from a view using copy (select * from tbl where id in (n) order by 1) command (the table's in the view are ~600GB, almost all the columns included in the view) and it generates around 200Gb of temp files since my current work_mem is with default configuration. I know I must change few params to avoid temporary files. But I am confused on below points.

If the query's result size is > work_mem, will it generate more temp files / use disks?
Is it true that work_mem wont support more than 2Gb?
My RAM is 128Gb and SSD storage, how much work_mem should I need to use this for this query? (I will create a separate user to query this table, and alter the user's work_mem)
Is there any way to cancel the query before if it reaches out of space error?
(I could limit by enabling max temp file limit. But I am not sure how the temp file size will be and how many files will be generated for a specific query)

(i.e., finding the more temp file/huge result(>10G) generating query, trigger pg_cancel_backend for that before it crashes the server)


texts: How do I escape backslashes and forward slashes in Neo4J Cypher?

texts:
When I run,

MATCH (p:person {id:'1'}), (a:Address {street:"11\34, Wall street"})RETURN p, a;

  Invalid input 'S': expected '\', ''', '"', 'b', 'f', 'n', 'r', 't', '_', '%', UTF16 or UTF32

How do I handle \ and / special characters


texts: Trying to understand MySQL Replication with SSL

texts:
I'm trying to get my head around the use of SSL with MySQL replication.
I've spent all day reading blog posts / articles about this, and I'm still no closer to getting an answer to this question:

Essentially I'm trying to understand the relationship between the ca, client & server files.

In my fictional setup I have:

1 x Master DB - Main Site
5 x Slave DBs - Main Site
Each Slave DB connects to a Slave DB on a remote (all different) Customer Site.

Am I right in thinking therefore that I would:

generate the ca-cert.pem and ca-key.pem files on the Master DB
create a set of client-xxx and server-xxx files on each Slave DB (and a copy of the ca-xxx.pem files)
Onto each Customer DB I would copy the ca-cert.pem,client-cert.pem,client-key.pem from the matching Slave DB

If one of the customer DB's was compromised would I need to generate a new ca-cert.pem file on the master, and re-distribute it to all remaining slaves & customer DB's (requiring all my DB's to be restarted).
Or could I just delete the server-cert.pem file from that one slave server?

(obviously I could revoke the username/password - but I'm trying to understand the SSL side at present).


texts:
I am trying to create partitions on a table of one of my test databases called WingTipToys2012.

Following the recomendations of many sources, I created the three file groups called FgOne, FgTwo and FgThree, as follows:

ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgOne;
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgTwo;
ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgThree;

Then, I created one additional file for each file group, as follows:

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file1',
  filename='C:\...\file1.ndf', size=5MB) TO FILEGROUP FgOne;

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file2',
  filename='C:\...\file2.ndf', size=5MB) TO FILEGROUP FgTwo;

ALTER DATABASE WingTipToys2012 ADD FILE (name = N'file3',
  filename='C:\...\file3.ndf', size=5MB) TO FILEGROUP FgThree;

Then I created the partition function, as follows:

CREATE PARTITION FUNCTION WTPartFunction (int)
AS RANGE LEFT FOR VALUES(30, 60);

The problem comes when trying to create the partition scheme. To do so, I execute the following command:

CREATE PARTITION SCHEME WTPartScheme
AS PARTITION WTPartFunction TO (FgOne, FgTwo, FgThree);

But after executing the instructions above, I get the following error:

  Msg 208, Level 16, State 58, Line 1
  Invalid object name 'FgOne'.

It looks like the file group FgOne is not recognized, but I know it's there. Funny enough, SQL server also knows it is there. If I try to create another file group with the same name:

ALTER DATABASE WingTipToys2012 ADD FILEGROUP FgOne;

I get the following error:

  Msg 5035, Level 16, State 1, Line 1Filegroup 'FgOne' already exists in this database. Specify a different name or remove the conflicting filegroup if it is empty.

I am not sure what I am doing wrong. Can anyone help?


texts: Block certain queries from being logged in slow query log

texts: I have mysqld_node running on my mysql server to get statistics to be used by grafana + prometheus to graph dashboard for this MySQL server. However, i noticed that 90% of the queries in my slow query logs are created by mysqld_node, and hence hard to filter queries from Application servers. Is there a way to block queries from being logged in the slow query logged based on the port or some other criteria? The port for mysqld_node is 9104.


texts: Will Ola's default IndexOptimizer job update my stats?

texts:
I just installed Ola Hallengren's MaintenanceSolution.sql maintenance scripts. After looking at the output of the log generated by the IndexOptimize - USER_DATABASES job, I'm concerned that it's not going to update the stats. The reason for my concern is that I see @UpdateStatistics = NULL in the parameters listed in the log output:

Date and time: 2013-07-16 13:58:24
Server: XXXXX
Version: 10.0.5512.0
Edition: Standard Edition (64-bit)
Procedure: [PROD-PH].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'N', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
Source: http://ola.hallengren.com

The motivation behind this is that there is a query that runs once an accounting period (every four weeks) for the date range of the period. The query takes over 20 minutes if I do not manually run sp_updatestats. The query takes less than 5 seconds if the stats are current. The table in question has ~ 1.6M rows but is only growing by ~20K a week. As such, it is nowhere near the 20% auto threshold for a given four week period. Several other tables have a similar growth pattern.

My plan is to schedule this job to run once a week. Will it update the stats as required?


texts: Can I re-replicate a transactional publication? Should I?

texts:
We use a cloud-based SAAS provider as one of our line-of-business applications.  An on-site SQL Server 2008 R2 instance in our DMZ is a transactional replication subscriber to a number of publications on the provider's SQL Server.

We are interested in building a reporting server containing much if not all of the data currently being replicated from our provider.  What is the most appropriate mechanism, if any, to support this objective if we wish to accomplish it in-house (as opposed to the possibility of adding the reporting server as a second subscriber to the provider's publications)?

Conceptually, the most appealing prospect would be to establish our current subscriber as a publisher in its own right and re-replicate the data to the reporting server, but that seems fraught with risk -- our publications total more than 250GB of data, and any issue necessitating the re-initialization of every subscription to our provider's publications would result in a 24-to-36-hour outage.  The biggest upside to "re-replicating" would be the ability to optimize indexes on the reporting server, which to my knowledge would not be practical or even possible using log shipping or database mirroring.

Is there a way to accomplish this in-house using the standard SQL Server stack?  If so, what are the pros and cons?  Or would our best option be to look into additional subscriptions to the SAAS publications?


texts:
Using SSMS 11 to connect to a SQL Server 2012 instance.

When I first load up SSMS and log-in, is it possible to have specific nodes in the object explorer expand? I always expand the tables node in a few of the databases that I am constantly accessing, along with the SQL Agent Jobs node.
Whenever I add a new table via T-SQL, I refresh the cache with CTRL+SHIFT+R, but I also want to refresh the object explorer tables node without having to use my mouse to right click on "Tables" and hit refresh. In the object explorer, is there a shortcut key to refresh the node you're currently working in?

These sound like minor things, but they'd dramatically improve workflow since I encounter them regularly every day.


texts: What permissions are needed to see view definitions via a query?

texts:
I have full admin rights on our database and hence can query and see view definitions. I want however query the views with a read-only user in a JDBC Jenkins job magic. The problem: Unlike my admin-user, the read-only user does not see the code/definitions of a view.

This query give me all the view definitions and the meta data I need for all views when I act as Admin:

SELECT name AS VIEW_NAME,
definition,
create_date,
modify_date
FROM [my_database].[sys].[all_views]
JOIN [my_database].[sys].[sql_modules]
ON [my_database].[sys].[all_views].object_id = [my_database].[sys].[sql_modules].object_id

As a result when executing the query as admin I get entries like:

name        | definition        | create_date | modify_date
sample_view | SELECT * FROM bla | 01.01.2017 | 02.01.2017

However, not so much when I do it with my read-only user, I get

 name        | definition        | create_date | modify_date
 sample_view | null              | 01.01.2017  | 02.01.2017

Here you can see my permission configuration for the read only user. Although I have granted him the necessary permission, the view definitions are not visible to the user in a result set.

It is furthermore really strange that after allowing the user to do SELECT and VIEW definition statements and saving the config, a second entry for SELECT and VIEW DEFINITION was added to the config table.


texts: How can I select rows from a hierarchical query with the lowest level?

texts:
Apologies for the cross-post from StackExchange DBA - I wasn't sure which site was best for this question.

I have a hierarchical query in Oracle 11gR2 that returns something like this:

Parent (Level 1)

Child (Level 2)

Grandchild (Level 3)

Child (Level 2)

Grandchild (Level 3)
Grandchild (Level 3)

Child (Level 2)

The query I would like to write should get all the rows matching some predicate, for the minimum level; i.e. nearest the parent. For example, if one of the child rows matches the predicate, it should return just that row, irrespective of whether any grandchild rows match. If multiple child rows match, it should return all of them, again irrespective of grandchild rows. If no child rows match, it should return any grandchild rows that match, etc. (In the real system I have a lot more than three levels, and lots more rows per level.)

I assume this is possible with analytic functions, but I'm not sure which one to use, or how to integrate it into my query. I've seen similar problems solved using min (level) keep (dense_rank last order by level), but that doesn't seem to do quite what I want.


texts: How do you manage a list for particular object. for eg. user comments on specific object(user post etc.)

texts:
I am new to database and web application development.  I want to know how to store a list for a particular object in a database.  For example, user comments for a particular post(object), or the friends or followers list of a particular user.

Normally we save data in the form of row

|user_id  | user_name | use_password | etc | etc
|123      | neha      | 123          | ... | ...

but here you cannot save them in rows because its a list:

|post_142524 | comment1 | comment2 | comment3 | etc...

How do you store comments?  I can't think of creating new columns for each comment. and I can't create a table which stores data column-wise like this:

|post_142524|
|comment1   |
|comment2   |
|comment3   |
|comment4   |
|comment5   |
etc...

I want to know how to manage lists for particular object.  I've searched around but, I think my search terms were not perfect to get what I am looking for, as I don't even know what term to use to search for this problem.  Any help would be appreciated!


texts:
I have a query running in Postgres 9.3 for more than 24h now (namely an EXPLAIN ANALYZE VERBOSE, linked to another question).

I check the "Server status" in pgAdmin which shows the query "Active". I ran some standard lock detection but it didn't return anything.

I'm not sure why it's taking so long: is it waiting for a lock that it can never get (no point in waiting)? Is it just slow (let it run longer)?

In case it's relevant, the database itself is quite big (500 million rows on one of the foreign table, several millions on others) and the data directory uses 240 out of the 340 GB of the disk, so has ~100 GB left. It is a Windows 2012 Server with 12 GB RAM, 6 GB left.

EDIT: The query itself finally finished after 28h30 (because of recursive-like delete), but the question is still valid: what can I check to make sure I'm not waiting in vain? (removed postgresql-9.3 because it would be better to have a more general answer)


texts: What is the special database "postgres" for?

texts:
I have a PostgreSQL server with several databases running on it. One of the databases is called postgres, and it was there right from the beginning. I could not see any tables inside and I've never used it explicitly, but I noticed, that any user is allowed to create tables within the database (this is not what I want - I do not want any users except the administrator to create any tables anywhere).

I have a feeling that it is some kind of a special database that is required for a normal operation of the server, but what is exactly its purpose? What kind of data is (or could be) stored inside? Does it hold the metadata about other databases (e.g. the information_schema?). Can something nasty happen, if I revoke connect privilege to this database from public? Or if I even drop it?

I googled for half an hour, but always got completely unrelated stuff, because the word postgres is also used for the user postgres and the DBMS itself.


texts:
This is my first question on this forum, so be patient...
My question is about the most efficient way to build a query on the following table:

create table OpenTrades(
    AccountNumber   number,
    SnapshotTime    date,
    Ticket      number,
    OpenTime    date,
    TradeType   varchar2(4),
    TradeSize   number,
    TradeItem   char(6),
    OpenPrice   number,
    TradeSL     number,
    TradeTP     number,
    TradeSwap   number,
    TradeProfit number
);
alter table OpenTrades add constraint OpenTrades_PK Primary Key (AccountNumber, SnapshotTime, Ticket) using index tablespace MyNNIdx;

This table is populated every 15 minutes, and the timestamp of the insert is saved in SnapshotTime column.
The sought query should group records by Week number (based on SnapshotTime), and filter the records so that it would return those with the latest (SnapshotTime) within the same week.

So far, I've tried the following:

select max(ot2.SnapshotTime) from OpenTrades ot1, OpenTrades ot2 where to_date(ot1.SnapshotTime,'IW')=to_date(ot2.SnapshotTime,'IW');.

However, this takes way longer than expected (15 minutes for less than 1500 rows); I'm sure there's a much better way to write this, but so far, it has eluded me. Any idea?
Thanks!


texts: How to match a WiredTiger index file to its corresponding collection

texts:
I have a mongodb database with about 2 billion records, shared between 20 or so collections. Each of these collections has an index on it, based on a single key (other than _id).

I have an index file (named index-1-5374774422504609475.wt) that I'd like to match to a collection. Is there a quick way to match directly which index file corresponds to a collection?

I do mean the actual physical index file in the mongo db directory.

Thanks in advance!


texts:
Why do these two SELECT statements result in a different sort order?

USE tempdb;
CREATE TABLE dbo.OddSort
(
    id INT IDENTITY(1,1) PRIMARY KEY
    , col1 NVARCHAR(2)
    , col2 NVARCHAR(2)
);
GO
INSERT dbo.OddSort (col1, col2)
VALUES (N'e', N'eA')
    , (N'é', N'éB')
    , (N'ë', N'ëC')
    , (N'è', N'èD')
    , (N'ê', N'êE')
    , (N'ē', N'ēF');
GO

SELECT *
FROM dbo.OddSort
ORDER BY col1 COLLATE Latin1_General_100_CS_AS;

╔════╦══════╦══════╗
║ id ║ col1 ║ col2 ║
╠════╬══════╬══════╣
║  1 ║ e    ║ eA   ║
║  2 ║ é    ║ éB   ║
║  4 ║ è    ║ èD   ║  -- should be id 3?
║  5 ║ ê    ║ êE   ║
║  3 ║ ë    ║ ëC   ║
║  6 ║ ē    ║ ēF   ║
╚════╩══════╩══════╝

SELECT *
FROM dbo.OddSort
ORDER BY col2 COLLATE Latin1_General_100_CS_AS;

╔════╦══════╦══════╗
║ id ║ col1 ║ col2 ║
╠════╬══════╬══════╣
║  1 ║ e    ║ eA   ║
║  2 ║ é    ║ éB   ║
║  3 ║ ë    ║ ëC   ║
║  4 ║ è    ║ èD   ║
║  5 ║ ê    ║ êE   ║
║  6 ║ ē    ║ ēF   ║
╚════╩══════╩══════╝


texts: What are the benefits of using Manual Failover Mode in an AlwaysOn Availability Group instance as opposed to Automatic Failover Mode?

texts:
Why wouldn't I want to use Automatic Failover as the Failover Mode for an AlwaysOn Availabiltiy Group instance?

Are there pros for Manual Failover Mode that don't exist for Automatic Failover Mode?


texts: SQL 2012: Executed generated DB script in SQL 2008 R2

texts:
I generated a script (using the wizard from SSMS 2012) to produce a SQL 2008 R2 compatible database script. I ran this script in a SSMS 2008 R2 instance and i got a bunch (30) of these possibly "warning" errors:

  The module 'spMoney_Increase' depends on the missing object
  'dbo.spMoney_Decrease'. The module will still be created; however, it
  cannot run successfully until the object exists.

Do i need to be concerned about these errors? Note that the errors did not break the overall execution and i know trying to put 2012 DB into a 2008 instance is not recommended but you know how client request goes...

-------- Description of Process -------

In SSMS 2012 - Server A

Right clicked on MY_DB > Tasks > Generate Scripts
In the Set Scripting Options > Advanced > I chose the below setup

Then Ok > Next > Finish

In SSMS 2008 R2 - Server B
I created folder locations on the C drive that mimicked Server A so i didn't need to change the DIR paths in the script:

  CREATE DATABASE [MY_DB] ON  PRIMARY ( NAME = N'MY_DB', FILENAME =
  N'C:\DBS\MY_DB.mdf' , SIZE = 16384KB , MAXSIZE = UNLIMITED, FILEGROWTH
  = 1024KB )

  LOG ON  ( NAME = N'MY_DB_log', FILENAME = N'C:\DBS\MY_DB_log.ldf' ,
  SIZE = 1280KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO

Executed the script and got the "warning" error above.


texts:
I have created a POS system for our corporation, in the HQ we have a wamp server with the main database, we also have more than 25 branches across the country.

I will setup a wamp server on each branch, so I can access its database directly when putting wamp online.

I want to make MySQL replication with all branches, so every query on any branch will affect the main database on HQ.

I tried to test but found no one to explain how to do it using wamp on different PCs.


texts: Query Time is doubled because of a column in select

texts:
I have a Stored Procedure which is calculating some data and inserting it into a Temp Table.
Then I am selecting the Data from the Temp Table.
Select
    AssetId = iAssetId,
    .....
    SpeedKM = fSpeed,
    [Address] = sState +', '+ sDistrict +', ' +sPoi +', ' + sRoad +', '+sPoi,
    MapUrl = @sUrlHeader+'/Report/ReportOnMap/?id='
        +CONVERT(VARCHAR(10), @iCompanyId)+'&ReportName=OverspeedReport'
        +'&AssetId='+ CONVERT(VARCHAR(10), iAssetId)
        +'&MaxSpeed='+ CONVERT(VARCHAR(10), fSpeed)
        +'&OverspeedingDate=' + FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss')
        +'&VehicleMonitoringLogId='+ CONVERT(VARCHAR, ol.iVehicleMonitoringId),
    [Locate] = 'Locate'

FROM #overspeedLogs ol
    ORDER BY ol.iAssetId, ol.dtUtcDateTime

Now this Query takes around 1:45 Minutes to execute.
But when I remove the column
MapUrl = @sUrlHeader+'/Report/ReportOnMap/?id='
            +CONVERT(VARCHAR(10), @iCompanyId)+'&ReportName=OverspeedReport'
            +'&AssetId='+ CONVERT(VARCHAR(10), iAssetId)
            +'&MaxSpeed='+ CONVERT(VARCHAR(10), fSpeed)
            +'&OverspeedingDate=' + FORMAT(dtutcDateTime, 'dd-MMM-yyyy HH:mm:ss')
            +'&VehicleMonitoringLogId='+ CONVERT(VARCHAR, ol.iVehicleMonitoringId),

The stored procedure only takes 35 seconds,
There are 4 Converts and 1 Date Format function, which maybe causing this issue - but without changing the logic on the Front End (which is an RDLC report) is there a way to increase the performance of this?
Changing Format to Convert still has similar result - still takes double or more the time. The variable @sUrlHeader is VARCHAR(500).
Execution plans
Without MapUrl Column - 121919 Rows in 2 minutes https://gist.github.com/mdawood1991/580fadf9031824aa22df

With MapUrl Column - 121919 Rows in 7 Minutes (So more than double for more rows) https://gist.github.com/mdawood1991/33817f95aad40d42631b


texts: what AD groups logins my user belongs to?

texts:
I am not sure if I have chosen the right title for this question. what
  I am really after is, given a individual windows AD user, I would like to
  find out the list of the windows AD groups (logins) that have access to an
  specific database in this server

when I run the following query

select
    name,
    principal_id,
    type,
    type_desc,
    default_schema_name,
    create_date,
    modify_date,
    owning_principal_id,
    sid,
    is_fixed_role
from sys.database_principals

in my server

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011
  00:54:03      Copyright (c) Microsoft Corporation     Standard Edition
  (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

I get the following results (partial list):

  I need to know all the permissions a particular login has. this login
  has access to my server/databases through AD groups.

  1) what AD groups, from the list above, does my login belong to?

I have been doing this, below,
but I would really like to find out the list of the AD groups (that have access to this server according to the above picture) that this user belongs to.

  First I execute as the user in question

    EXECUTE AS LOGIN='mycompany\HThorne'

    DECLARE @User VARCHAR(20)
    SELECT @USER = SUBSTRING(SUSER_SNAME(),
CHARINDEX('\', SUSER_SNAME()) + 1, LEN(SUSER_SNAME()))

  i  make sure that I have the right credentials

    SELECT   @USER
            , SUSER_SNAME()
            ,SYSTEM_USER
            , USER_NAME()
            , CURRENT_USER
            , ORIGINAL_LOGIN()
            , USER
            ,SESSION_USER

  I go to the specific database and use the fn_my_permissions - run as
  the user in question

    use WebDataImportStage
    go
    SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
    GO

    REVERT

  and that is giving me the result below:


texts:
When a query or stored procedure has an execution plan greater than 8000 bytes, instead of using plan cache in buffer pool, it will use MPA (multi page allocator) which used memory outside buffer pool. This behavior is changed in SQL 2012 and MPA and SPA are part of buffer pool. But I am using SQL 2008 R2 standard edition.

Does multi page allocation means two or more 8K page will be allocated at once or page larger than 8K in size will be allocated to save execution plan for the query?


texts: MongoDB:Read from slave dirctly,how to guarantee that the data is just up-to-date?

texts: I know that MongoDB is the master/slave architecture,to make the read faster,some read request is directly sent to slaves.But since it is a distributed system,when the data is write to a master,the master will write the data to an editlog,the slaves will read the log to synchronize data of itself.So, the data read directly from slave maybe just dirty data.So , what we can do to make sure that the data read from salve is just the up-to-date data?


texts: How to identify the creator of the temporary table in SQL Server 2005

texts:
On my server many users performing complex queries using tempdb. Sometimes a failed specifying boundary values obtained are very large tables.

So I want to know who created this temporary table.

When I do:

select * from tempdb.dbo.sysobjects where xtype = 'U' order by name

I get the names of the tables like this:

# tablename__________000000004EB1

How to determine the SPID user who created this table.


texts:
I'm new to Cassandra. I have the responsibility to design a basic deployment architecture for a use case. I'd like to know if my solution is correct, and get advice and recommendations from you.

Here is the context: We need to create an application that will present some metrics (Scores, Number of visited pages..) to different kind of users (customers,admins..).

Depending on the requests, some metrics may require the latest available data or not.

We are using third party software that stores RAW DATA into MySQL databases.

Here is the solution that I intend to deploy:

The "RealTime DataCenter" will be populated using either Cassandra Triggers or Talend jobs.


texts:
After searching through many posts I have come up with the code below to handle updating 200,000 to 2 million records daily. The problem is its still takes hours to complete, I have tried to narrow down the records that are updated to only those I need. I'm really new to this but it seems like updating 200,000 records should go pretty fast. Please review the code below and see if I am doing something wrong? Thanks in Advance

P.S. Why is my code not going in a code box?

IF OBJECT_ID('tempdb.dbo.#MinDate') IS NOT NULL
    DROP TABLE #MinDate
GO

SELECT * INTO #MinDate
FROM
    (
    SELECT  n1.TR_SerNum, Min(n1.TR_Scan_Date) As MinDate
    FROM IMB_TBO_Data.dbo.TBO_Trace_Data as t1
        INNER JOIN Trace_Return_Updates as n1
        ON t1.TR_SerNum = n1.TR_SerNum
    WHERE (n1.TR_Scan_Date < t1.First_Scan_Date)
        OR (IsDate(n1.TR_Scan_Date) = 1) AND (t1.First_Scan_Date Is Null)
    GROUP BY n1.TR_SerNum
    ) as n
GO

CREATE INDEX IDX_SerNum ON #MinDate(TR_SerNum)
GO

DECLARE  @UPDATES TABLE (PK_ID int NOT NULL Primary KEY)

DECLARE @x INT

SET @x = 0

WHILE @@ROWCOUNT > 0
    BEGIN
        UPDATE TOP (10000) IMB_TBO_Data.dbo.TBO_Trace_Data
            SET First_Scan_Date = n.MinDate
        OUTPUT inserted.TR_SerNum INTO @Updates
        FROM IMB_TBO_Data.dbo.TBO_Trace_Data t
            INNER JOIN #MinDate n
            ON t.TR_SerNum = n.TR_SerNum
        WHERE NOT EXISTS
            (SELECT * FROM @UPDATES AS Updates
            WHERE UPDates.PK_ID = t.TR_SerNum)
        WAITFOR DELAY '00:00:02'
    END
SET ROWCOUNT  0
IF OBJECT_ID('tempdb.dbo.#MinDate') IS NOT NULL
    DROP TABLE #MinDate;
GO

PRINT N'Update First Scan Date'"


texts: Case-insensitive order while preserving the case of the returned strings

texts:
I have noticed that when ordering, PostgreSQL (I'm currently working with 11 but I think the same applies to 10 and 9) is case sensitive. It returns upper-cased string before lower-cased one.
For instance, let's say I have the friends table with 3 records and the first_name column contains respectively  adam, Tony, Paul.

SELECT name
FROM friends
ORDER BY name

returns

[name]
 Paul
 Tony
 adam

To get them alphabetically ordered regardless of the case, I can use the lower function.

SELECT name
FROM friends
ORDER BY lower(name)

returns

[name]
 adam
 Paul
 Tony

Now I need to join the friends table with the cities table in order to get only the friends associated with a city while keeping the friends alphabetically ordered and without changing the case of their name .

The following query is valid

SELECT DISTINCT lower(friends.name)
FROM friends
INNER JOIN cities
ON friends.city_id = cities.id
ORDER BY lower(friends.name)

But the returned names are all lowercase.
However, the query below

SELECT DISTINCT friends.name
FROM friends
INNER JOIN cities
ON friends.city_id = cities.id
ORDER BY lower(friends.name)

returns the error

  ORDER BY expressions must appear in select list.

What is the simplest way to apply a case-insensitive order while preserving the case of the returned strings?


texts: How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)?

texts:
I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?)

SELECT cars.*, users.*, makers.*, places.*
  FROM cars
  LEFT OUTER JOIN "users" ON "users"."id" = "cars"."user_id"
  LEFT OUTER JOIN "makers" ON "makers"."id" = "cars"."company_id"
  LEFT OUTER JOIN "places" ON "places"."id" = "cars"."location_id"
  WHERE "cars"."deleted_at" IS NULL
    AND "cars"."draft" = 'f'
    AND ((cars.reference_number::text ILIKE '%abc123%')
      OR (makers.name::text ILIKE '%abc123%')
      OR (places.name::text ILIKE '%abc123%'))
  ORDER BY cars.id DESC NULLS LAST
  LIMIT 20
  OFFSET 0;

EXPLAIN ANALYZE output:

Planning time: 1.300 ms
Execution time: 105.936 ms

SELECT output using \timing:

Time: 2100.704 ms (00:02.101)

It seems that EXPLAIN ANALYZE does not include time to read the data.

How can I get the total time without using SQL (and not PSQL console)?

PostgreSQL 9.6.6


texts: how to change drive for Partitioned tables

texts: I have a large table should have been partitioned in different derives but all of them in one drive (D:\sql\import2006.ndf), (D:\sql\import2007.ndf).how can i move them in to their own drives like data2006,data2007 etc without loosing data.


texts: How to select a specific row based on another row from same table


texts: How to get line that causes error when loading from csv to MySQL

texts:
I'm trying to load data from CSV file, but occurs error

Error Code: 1366. Incorrect integer value: 'abcdxxx' for column 'col' at row 123545.

I Have file with multiple breaklines CRLF's in content, the line separator is LF, text tools cannot find returned string, so I suppose there is problem with charset. Do exists posibility to have returned values(line) from file that caused error server returned? I cannot find values that caused error.