texts: SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc

texts:
I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans.  I can take care of all the backup stuff, but the table maintenance baffles me some.  Does anyone have any input on how often I should (or how often you do would suffice too) the following tasks?

Check Database Integrity
Rebuild Indexes
Reorganize Indexes
Update Statistics
Shrink Database?

Am I missing anything? Again if you can share how often you do these tasks that would be great...and/or share any general information about your approach to table maintenance that would be helpful.  Lastly does it matter what order I run these tasks in (when setting up a job)?

Oh and I'm open to any links which might be of help!


texts:
I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to page.

exec sp_executesql N'
set arithabort off;
set statistics time on;
set transaction isolation level read uncommitted;

With cte as
(
    Select
        peta_rn = ROW_NUMBER() OVER (ORDER BY  d.LastStatusChangedDateTime  desc),
        d.DocumentID, d.IsReEfiled, d.IGroupID, d.ITypeID, d.RecordingDateTime,
        d.CreatedByAccountID, d.JurisdictionID,
        d.LastStatusChangedDateTime as LastStatusChangedDateTime,  d.IDate,
        d.InstrumentID, d.DocumentStatusID , u.Username, it.Abbreviation AS ITypeAbbreviation,
        ig.Abbreviation AS IGroupAbbreviation, d.DocumentDate
        From ITypes it
        Inner Join Documents d on it.ITypeID = d.ITypeID
        Inner Join Users u on d.UserID = u.UserID
        Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
        Where
            1=1 AND
            (  d.DocumentStatusID = 9  ) And
            (CreatedByAccountID = @0 Or DocumentStatusID = @1 Or DocumentStatusID = @2)  And
            (  d.IGroupID = @3   Or d.IGroupID = @4   ) And
            (d.JurisdictionID = @5 Or DocumentStatusID = @6 Or DocumentStatusID = @7)
)
Select
    cte.DocumentID, cte.IsReEfiled, cte.IGroupID, cte.ITypeID, cte.RecordingDateTime,
    cte.CreatedByAccountID, cte.JurisdictionID,
    cte.LastStatusChangedDateTime as LastStatusChangedDateTime, cte.IDate, cte.InstrumentID,
    cte.DocumentStatusID,cte.IGroupAbbreviation, cte.Username, j.JDAbbreviation, inf.DocumentName,
    cte.ITypeAbbreviation, cte.DocumentDate, ds.Abbreviation as DocumentStatusAbbreviation,
    ds.Name as DocumentStatusName, (SELECT COUNT(*) FROM CTE) AS TotalRecords
    FROM cte
    Inner Join DocumentStatuses ds On cte.DocumentStatusID = ds.DocumentStatusID
    Left Join InstrumentFiles inf On cte.DocumentID = inf.DocumentID
    Inner Join Jurisdictions j on j.JurisdictionID = cte.JurisdictionID
    Where
        1=1 And
        peta_rn>@8 AND
        peta_rn<=@9
    Order by peta_rn',
    N'@0 int,@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int',
    @0=44,@1=5,@2=9,@3=3,@4=4,@5=1,@6=5,@7=9,@8=90000,@9=90050

This is my statistics output:
     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

(50 row(s) affected)

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

and this is my query plan XML. All indexes are created properly. I am unable to post here due to body limit of 30K.

https://www.dropbox.com/s/vrx5ec6cpi8vn33/plan.txt

Any help would be appreciated. Thanks!


texts: Load balancing incoming data using offset / increment vs group replication

texts:
I have a service that accepts large quantities of incoming JSON data from multiple sources. A single client will put a load of about 3.0 on a server, primarily from the Apache service, but also Mysql.

To balance this, I created a master-master relationship between 2 servers where they were each masters of each other, and slaves of each other. They both had auto_increment set to 2 with server one having an offset of 1, and server 2 having an offset of 2.

This resulted in server1 handling all incoming requests and writing odd number rows, and server2 doing the same and writing even number rows but now I have twice the processing power for Apache and can offload the non-time sensitive processes to servers not powering the main website.

The thought was that as we grew beyond 2 servers, we could increase the increment value to 10 and do offsets of 1-10 for each server. I could then adjust the MASTER_DELAY to reduce total connections so it could batch things in groups instead of individual queries.

I tasked a new employee to improve historical backups and in the process he started working on reworking the entire system to do group replication, which sounds great for redundant data, but he is telling me it will also be a solution for load balancing.

Is this a realistic solution / improvement that scales with 2-10+ servers simultaneously receiving hundreds of thousands of rows of content with foreign key pairs or should I go back to my previous setup? I don't understand how it would keep things straight without adjusting the autoincrement like I did with so much simultaneous writing.


texts: Postgres break table columns into multiple tables having unique rows

texts:
I have this table called transactions. It has two columns id and report_id. id is primary key.

I want to create two new tables transaction_ids and report_ids both with just one column id.
Both these new tables are supposed to have the list of unique transaction ids and unique report ids respectively.

The first solution that come to mind is:

Step 1:
insert into transaction_ids (select id from transactions); // as `trasnactions.id` is a primary key, uniqueness is guaranteed

Step 2:
insert into report_ids (select distinct report_id from transactions); // using DISTINCT to make sure uniqueness
-- OR --
create unique index on report_ids(id);
insert into report_ids (select report_id from transactions) on conflict(id) do nothing; // using on conflict to make sure of uniqueness

The problem here is that I am traversing the transactions table twice. I am thinking of this as a for loop where I can just traverse the table once and push both transaction.id and transaction.report_id to the right table on each iteration (assuming I have the on conflict handling present for the table report_ids).

Question 1: Is there a way to do it, without actually ending up writing the for loop. I don't have anything against for loop. It's just I have to write extra lines; the same thing which now I am able to do in just two lines.

Question 2: Also, I am worried, that if I insert the rows one by one in side a for loop, would postgres take much time for this as compared to just asking the whole column in one go. In which case, I would have to compare whether the performance gain by traversing just once is worth the performance loss by inserting rows one by one

I also thought of using CTE:

with _ as (insert into transaction_ids (select id from transactions))
insert into report_ids (select distinct report_id from transactions);

-- OR --

create unique index on report_ids(id);
with _ as (insert into transaction_ids (select id from transactions))
insert into report_ids (select report_id from transactions) on conflict(id) do nothing;

But, IMO even here the problem of traversing twice is present.
Question 3: Does this approach has any advantage over first approach

Question 4: Is there a recommended/preferred/standard way to deal with this situation?

P.S.: I am worried because size of the transactions table is quite big:

select pg_size_pretty(pg_total_relation_size('transactions'));
 pg_size_pretty
----------------
 2282 MB
(1 row)

select count(id) from transactions;
  count
---------
 1465605
(1 row)

select count(report_id) from transactions;
  count
---------
 1360204
(1 row)

select count(distinct report_id) from transactions;
 count
--------
 168508
(1 row)


texts: Are there any in-memory NoSQL databases?

texts:
Given that NoSQL databases are designed to support horizontal scalability by throwing in more servers, are there any that are designed to do entirely without persistent disk storage?

I'm looking at an application that requires horizontal scalability but that also needs to encrypt any data at rest. That could end up meaning pulling a lot of the data from the NoSQL database into an in-memory cache like memcached. That got me wondering: Is there any single product that had the scale and fault-tolerance characteristics of a NoSQL database but operated solely in memory?


texts: I have deleted Oracle listener from windows services

texts:
I am using oracle 11 express edition and I have deleted the Oracle Listener service from windows services mistakenly and now I can't connect to the database.
Could someone help me to recreate the service again using command line.


texts:
I have following student table

ID     Version      Name       Email
1        1         Johan        Johan@email.com
1        2         Johan 1      Johan@email.com
2        1         Saif         saif@email.com
2        2         Saif  1      saif2@email.com
2        3         Saif  4      saif1@email.com

I want to select student records based top version.

ID     Version      Name       Email
1       2          Johan 1      Johan@email.com
2       3          Saif  4      saif1@email.com


texts:
Say I have a reference table like languages with only 20 records. Is it worth indexing a field like language_code (e.g. en, es) that's frequently used in queries? I'm wondering if an efficient server would effectively be indexing columns like this automatically.

This is specifically for MariaDB with InnoDB engine.


texts: Pros and Cons of virtualisation technology for database-server and datastorage

texts:
Yesterday we had a chat about performance and restorability and I realized how many good things a virtualisation environment can bring to me - but as I'm a little sceptic concerning performance I'm asking here. It could be a little GIS specific, but over at the gis-users they said it's to database specific... ;)

Will a database server suffer from severe performance loss through virtualisation? I don't understand the technology to the last detail, but somehow it's a 'blackbox' more, that needs to be processed through hardware. Also will disk access, with all the tricks PostGIS gives us be granted? (clustering, index etc.) - fragmented clustering is like no clustering!

The big advantage is maintenance and scalability. In case of a severe hardware malfunction I can migration within minutes or even realtime to another physical machine.

Who has experience and can point me to good websites or literature on this topic? I remember a few things from the last fossgis and a few in-house benchmarks on ESXi and native Servers and somehow I can't make up my mind whether it's good or not.


texts: Difference between alert log and archive log in oracle databases

texts:
I don't know anything about oracle databases. Actually I'm asking this question for my friend (He currently doesn't have access to internet!).

He wanted to know that what's the difference between alert logs and archive logs in oracle databases? Actually here is the scenario: There was a database for which auditing by alert logs is disabled. Someone came and truncated some data from this database. My friend didn't notice that because auditing by alert logs was disabled. But an oracle expert managed to find information about those transactions by looking at archive logs.

Now what I want to know is that: What type of information is stored in each of alert logs and archive logs? and under what conditions?

I searched documentations of oracle but unfortunately couldn't find anything. The only thing I found was mandatory auditing. But in mandatory auditing information about transactions and changes to the database isn't stored.

Thanks.


texts: Master-Slave semi circle replication not working in MySQL

texts:
I didn't know how to describe it so I am calling it semi circle. Here's what I am doing.

Server A
Server B = This is slave of Server A
Server C = This is slave of Server B

When I update something on Server A then it is reflected on Server B. But the same change is not reflected on Server C.

Only when I update something on Server B then change is reflected on Server C.

How do I make it so any changes done on Server A will come to Server B (which is already working) and then it goes to Server C?

EDIT
Upon investigation I found out that when I make some change on Server A then its log file position changes. But when those changes are reflected on Server B then Server B log file position doesn't change. It is because of this reason Server C doesn't know if there has been any change on Server B unless I explicitly change something (insert,update,delete) on Server B.

So is there a way to tell MySQL to increment log file position when Slave is receiving updates from Master?


texts: Find signed procedures, functions, triggers, assemblies and by which certificates / asymmetric keys

texts: I have created a few Certificates (via CREATE CERTIFICATE) and Asymmetric Keys (via CREATE ASYMMETRIC KEY) and used them to sign and counter-sign various Stored Procedures, User-Defined Functions (UDFs), Triggers, and Assemblies (via ADD SIGNATURE). But now I need to find which Certificates and/or Asymmetric Keys have been used to sign which particular modules.


texts: Decompose a relation to 2NF and preserve all the functional dependencies

texts:
For example, consider the following relation:

R(A, B, C, D, E)

With the functional dependencies (FDs) shown below:

{A→B,  BC→E,  D→C}

Here candidate key is {AD} and the relation is not in second normal form (2NF) because of the partial dependencies A→B,  D→C.

Now, if I decompose R as follows:

R1(AB) ,  R2(CD),  R3(ADE)

...these relations are now in 2NF, but the FD BC→E is lost.

How can I decompose R to comply with 2NF and preserve the FDs?


texts:
Something happennig in my environment

MySQL 5.6.34
RHEL 6
22G buffer pool size

When i stop MySQL it shows:

service mysql stop
.....................................

Until and unless I kill using kill -9 <pid of mysqld_safe>

Post killing when I start service mysql, it will start within 10 second....

But when I stop it, It will wait until an unless I kill the pid of mysql

What should I do...?


texts: Syncing updates to master in master/slave setup [semi-sync replication]

texts:
Background info

I have two MySQL 5.5 servers set up in a Master/Slave configuration with Semi-synchronous replication.

The database is being used for a high-profile WordPress site.

Problem

It seems to work great except for one thing:

When the master is down and changes are made to the slave, they are not written back to the master when it comes back up.

What I've done so far

I tried the answer [here], but it broke my replication completely.

Questions

Is there a more standard way of setting this up or is it generally not supported?
Should I just disallow writes on the slave?
Is there a simple way to notify the master of changes, and sync them upstream?


texts: Error while installing MySQL on RedHat Linux

texts:
I am new to RedHat Linux and am referring to this link for the installation :https://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html I finished the first two steps and when I try doing Step 3, I get the following error:

Loaded plugins: refresh-packagekit, rhnplugin, security
This system is receiving updates from RHN Classic or RHN Satellite.
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.7.10-2.el6 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 5.7.10-2.el6 for package: mysql-community-server-5.7.10-2.el6.x86_64
--> Processing Dependency: mysql-community-client(x86-64) = 5.7.10-2.el6 for package: mysql-community-server-5.7.10-2.el6.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.7.10-2.el6 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) = 5.7.10-2.el6 for package: mysql-community-client-5.7.10-2.el6.x86_64
---> Package mysql-community-common.x86_64 0:5.7.10-2.el6 will be installed
--> Running transaction check
---> Package mysql-community-libs.x86_64 0:5.7.10-2.el6 will be obsoleting
---> Package mysql-libs.x86_64 0:5.5.47-1.el6.remi will be obsoleted
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: php-mysql-5.4.45-3.el6.remi.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_16)(64bit) for package: php-mysql-5.4.45-3.el6.remi.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: php-mysql-5.4.45-3.el6.remi.x86_64
--> Finished Dependency Resolution
Error: Package: php-mysql-5.4.45-3.el6.remi.x86_64 (@remi)
           Requires: libmysqlclient.so.18()(64bit)
           Removing: mysql-libs-5.5.47-1.el6.remi.x86_64 (@remi)
               libmysqlclient.so.18()(64bit)
           Obsoleted By: mysql-community-libs-5.7.10-2.el6.x86_64 (mysql57-community)
               Not found
Error: Package: php-mysql-5.4.45-3.el6.remi.x86_64 (@remi)
           Requires: libmysqlclient.so.18(libmysqlclient_18)(64bit)
           Removing: mysql-libs-5.5.47-1.el6.remi.x86_64 (@remi)
               libmysqlclient.so.18(libmysqlclient_18)(64bit)
           Obsoleted By: mysql-community-libs-5.7.10-2.el6.x86_64 (mysql57-community)
               Not found
Error: Package: php-mysql-5.4.45-3.el6.remi.x86_64 (@remi)
           Requires: libmysqlclient.so.18(libmysqlclient_16)(64bit)
           Removing: mysql-libs-5.5.47-1.el6.remi.x86_64 (@remi)
               libmysqlclient.so.18(libmysqlclient_16)(64bit)
           Obsoleted By: mysql-community-libs-5.7.10-2.el6.x86_64 (mysql57-community)
               Not foundenter code here
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest


texts: Why is tnsping not reliable from ports 20, 21, 22?

texts: I think it's not reliable because those ports are used for FTP, SSH and SFTP but I am not sure that's the correct response in respect for Oracle DBA


texts: Do empty columns take up space in a table?

texts:
I have table that holds from very basic info. Just a title and a few date fields.
There's one field called comments which is varchar(4000)
Most of the time we leave it blank, but some times will enter a large amount of data here.
Is this a really bad design? Or is this just slightly inefficient?

I would assume the creating a separate table for this column would be better.

note: this is sql server 2008


texts: Delete all rows within transaction: what would happen to other transactions?

texts:
I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid introducing another column to my temp table to mark all rows inserted by a particular execution of the function. If I used a global table that would be required, and due to nature of my queries, it'd kill performance.

I want to port this piece of code to VoltDB, but it does not support creating temp tables from stored procedures. This made me think about other approaches. If a stored procedure would start a transaction and delete all rows in a table (not temp), then what would happen to other executions of the same stored procedure running at that moment?

Or if after the delete, the stored procedure begins inserting rows, and another session invokes the same sp, which will want to delete all rows again, what then?

In short, can I create a local (to sp execution) view of data within a transaction by deleting all rows, inserting some rows, then performing queries on the inserted rows, then deleting all rows again before the sp execution ends (and transaction commits)?

This would let me avoid using a rowset_id column for the temporary rows, avoiding much slower joins (this is an EAV implementation).


texts: executing a stored procedure on a monthly schedule

texts:
I'm looking to create an invoicing procedure that executes automatically once a month.   The procedure name is INVOICE_PROC.

I have tried to use a trigger to execute it but I can't get it to work. Would a trigger even be the best way to do this?


texts: Problems with Distributed Availability Group( DAG)

texts:
I have 2 servers in AWS Central joined with AWSCentralAG and I have 2 servers in AWS West joined with AWSWestAG. Obviously, I have the listener name and listener IP address and I able to connect to each other both ways without any problem. Now, I have already created DAG and currently stuck on the part where I cannot join the database to local AG. It returns the following error message.
Msg 35250, Level 16, State 7, Line 4
The connection to the primary replica is not active.  The command cannot be processed

Overview of steps followed:

Created Local AG in AWS Central. This is successful.
Created Local AG in AWS West. This is successful
Created Distributed AG connecting AWS Central and AWS West. This is successful.
Now, the last piece, When I join the database on local AG on AWS West. The above error message about connection to replica is not active comes up.

Troubleshoot steps:

I am able to connect to AWS West Listener from AWS Central and Vice-versa.

I am able to ping all the nodes using both short name and FQDN.

I am able to telnet on Listener port to AWS West from AWS Central and Vice-versa.

I have verified the endpoint state is started. As per the KB, I have retried to start them manually and it was successful. As per the research, this was the common cause of the issue that I am facing.

I am able to failover back and forth on both AWS Central and AWS West.

The domain account for AWS Central(svcsqlawsc) and AWS West(svcsqlawsw) that SQL Services run on have appropriate permissions. They both have been created in all the nodes and verified they had connect endpoint permissions.

I queried sys.availability_replicas and sys.dm_hadr_availability_replica_states. All of them are returning as CONNECTED.

I ran a query impersonating as svc account and they all came back successfully from AWS Central node1.
:CONNECT AWSCENTRAL1NODE1
execute as login = 'AWSWest\svcsqlawswest'
select name, state_desc, port from sys.tcp_endpoints
where name = 'hadr_endpoint' -- started

:CONNECT AWSCENTRAL1NODE2
execute as login = 'AWSWest\svcsqlawswest'
select name, state_desc, port from sys.tcp_endpoints
where name = 'hadr_endpoint' -- started

:CONNECT AWSWEST1NODE1
execute as login = 'AWSCentral\svcsqlawscentral'
select name, state_desc, port from sys.tcp_endpoints
where name = 'hadr_endpoint' -- started

:CONNECT AWSWEST1NODE2
execute as login = 'AWSCentral\svcsqlawscentral'
select name, state_desc, port from sys.tcp_endpoints
where name = 'hadr_endpoint' -- started

I have tried using the GUI method and I have tried with TSQL. Both of them are indicating the same problem.

I am not sure what else I am missing. Appreciate any help that can be provided or point in right direction.


texts:
I'm trying to configure pgbench-tools and went to run it per the README and got this error

-bash-4.2$ ./newset 'Initial Config'
psql: FATAL:  Ident authentication failed for user "postgres"
psql: FATAL:  Ident authentication failed for user "postgres"

I looked at my pg_hba.conf file and local is set to trust, so not sure where the error is coming, I made sure the pgbench-tools/config file is set for user postgres. Trying to figure out where this problem is coming from and haven't seen anywhere on pgbench-tools repository that shines any light.


texts: How can I get PostgreSQL to not advance sequences when COPY fails?

texts:
I'm importing CSV files into a relatively simple PostgreSQL database. (The CSVs are sometimes created manually from information in a book, sometimes created from spreadsheets with a little massaging via scripting.) I do it in small batches to make sure things are going ok, and I've noticed that when COPY fails, it doesn't roll back the sequence attached to the column in the table. (Note: I am importing via pgAdmin.)

For example, let's say this is my table:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm

Then let's say I try to import two more columns and it fails. I fix the error, and then the import succeeds. I'd expect the table to look like this:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm
3  | nop   | qrs
4  | tuv   | wxy

Instead, it looks like this:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm
5  | nop   | qrs
6  | tuv   | wxy

The tables all rely on each other (i.e. pretty much every table has a FK pointing to the ID of some other table), so if the IDs stay predictable, my data entry job gets a lot easier. If not, I have to keep double checking the IDs when I finish a section.

Is there any way to prevent this behavior?


texts:
How can I create trigger for table to insert a row every 30 day?

ID  Date        Days
1   10/7/2016    0
2   09/8/2016    30
3   08/9/2016    60


texts: Causing ORA errors in a program using JDBC. How can I find what's causing them?

texts:
So I'm building a java app to interface with the company's database. Whenever it causes an ORA error, it just prints something like the following to system output:

ORA-00911: invalid character

Is there some way I can see what's causing it? Maybe some SQL statement I can use to see the last 10 errors caused on a table or something? I have SQLDeveloper if that might be useful.


texts: To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question

texts:
We're freeing lots of space in a SQL Server 2008 R2 database -a Loooot! enough to care about- (we're dropping lots of unnecessary data). but the database file conserves its file size. we want to regain it.
I've heard lots of times that using DBCC SHRINKDATABASE reduces the performance of your DBs -As far as I know, because "...A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree" [MSDN]
So I'm planning to use DBCC SHRINKDATABASE and then rebuild our indexes.
There is another performance reason to not use DBCC SHRINKDATABASE to regain disk space?


texts: 1 table with 2 same foreign keys to 3rd table with Primary Key

texts:
First time building a database in Access. I've been searching, but I did not find something to solve my question, so I finally decided to ask.

It may be a really stupid question but what is the best way to format something like the following:

One service is provided to two different recipients (table 1). These recipients are located in a recipient registry (table 2).

What is the best way to join both tables? Shall I create 2 tables: PrimaryRecipient and SecondaryRecipient?  Or a join table with the combination of recipients?


texts:
I created this schema with OpenOffice.

Does this design adhere to 3NF ?

(A relationship link between Equipment and Supplier_Equipment , Customer and Membership should be created but OpenOffice isn't allowing me to do so.)


texts: Is `log_bin` necessary on slave in master-slave MySQL replication?

texts:
I'm running out of disk space on a slave due to MySQL binary logs (mysql-bin.000xxx) being stored on the slave.

Here's the relevant portion of the slave's my.cnf:

binlog_format  = mixed
log_bin        = /var/log/mysql/mysql-bin.log
sync_binlog    = 1

I'm currently catching up to Master, so I expect to see a bunch of mysql-relay-bin.00xxxx's on Slave, but I'm not sure why it also needs mysql-bin.000xxx's.

The Master also has a lot of mysql-bin.000xxx's, but I expect it there.


texts:
Accidental DBA here (DB developer/jack of all trades), and first-time poster.  So please be gentle.  I have an alert on our SQL Server for error 825, and it fired in February.  Then twice in June, and now three times in the past three weeks.  The databases are for a vendor application.  DBCC CHECKDB ran in production after the errors, and I also restored to a test server and ran again, with no errors.  The errors occurred on two of the databases, one of which holds 99% of the application's data.  No rows in suspect_pages.

I also wrote to the software/database vendor, and they pretty told me to do what I already did, and sent a bunch of cut-n-paste research that I've already found.

Everything I found says hardware or drivers.  Our network/hardware is maintained by a third-party provider, and they say they don't see anything wrong with the disks.  We run VMware on EMC - that's about all I know about hardware. SQL is Standard Edition 2008R2 SP3, running on Server 2008R2 Standard.

Oh, I changed DBCC CHECKDB to run daily.

We've been told we should hire a DBA because our disks are healthy.

Any advice?

Thanks,
Pat


texts: Can I force a LOAD DATA LOCAL statement to abort if an error is not loaded?

texts:
I am trying to load a CSV file into a MySQL table with the load data local statement. After loading it, the statement reports

Query OK, 1030 rows affected, 7314 warnings (0.03 sec)
Records: 1043  Deleted: 0  Skipped: 13  Warnings: 7314

Fur my purposes, skipping 13 records is not OK - I'd rather have the statement to fail with an error message.

As the load data local statement is found in an .sql file that I execute from a batch file, I'd also appreciate if there was a possibility to abort the execution of the batch file on the first error.

Is this somehow possible?


texts:
My professor taught me this SQL statement:
SELECT COUNT(length) FROM product

will return 2 with the following dataset:
product
|id | length | code |
|-------------------|
| 1 |    11  | X00  |
| 2 |    11  | C02  |
| 3 |    40  | A31  |

She justified it by saying that COUNT doesn't count duplicates. I disagreed. After trying a lot of DBMS, I've never found one that has this behaviour. Does such an DBMS exist?


texts: Finding the first occurrence where id don't match

texts:
I’m trying to find the first occurrence of SNum where the VerNum don’t match. In this case it should point to SNum = 300 as VerNum 12 is not present.

DROP TABLE #temp1
DROP TABLE #temp2

CREATE TABLE #temp1 (VerNum INT, SNum INT)

INSERT INTO #temp1
VALUES (11,100), (12,100), (11,200), (12,200), (11,300)

CREATE TABLE #temp2 (VerNum INT)

INSERT INTO  #temp2
VALUES (11), (12)

SELECT * FROM #temp1
SELECT * FROM #temp2

A full join is not working, i can loop through the table and get the result I am after, but there must be a better way. Any help is appreciated.


texts:
I am trying to achieve something like this in sql server 2012. But this syntax does not exists. What are the alternatives to it and why does sql server does not support such syntactic sugar?

Which means C1 match to D1, C2 match to D2, Cn match to Dn. When all of the columns match it only will filter out the result.

SELECT * FROM tableA
WHERE
   ([C1], [C2],..., [Cn]) IN (SELECT [D1], [D2],..., [Dn] FROM tableB)


texts: Difference between Seek Predicate and Predicate

texts:
I'm trying to performance tune a query that we have in SQL Server 2014 Enterprise.

I have opened the actual query plan in SQL Sentry Plan Explorer and I can see on one node that it has a Seek Predicate and also a Predicate

What's the difference between Seek Predicate and Predicate?

Note: I can see that there are plenty of problems with this node (e.g. the Estimated vs Actual rows, the residual IO), but the question does not relate to any of that.


texts:
First, this is for a major assignment. I am not asking any of you far more experienced SQL users to do the assignment for me, but I need your help. SO this project runs similarly to a Netflix model, where you have a customer, a queue, a rental plan, DVDs with directors/producers and genres. What I need help with is twofold. First, I need help figuring out what tables I am missing, and which are missing attributes. I know I need a new table for the site's inventory but am not sure where it should go (one of the requirements is a procedure that adds new movies to inventory, and another needs to find the movies that are out of stock). I attached my ERD and the data I used to create the tables I have so far.

CREATE TABLE customer (
customer_id DECIMAL(12) PRIMARY KEY,
cust_first VARCHAR(50),
cust_last VARCHAR(50),
cust_phone DECIMAL(10),
cust_email VARCHAR(50));

CREATE TABLE rental_plan (
plan_id DECIMAL(12) PRIMARY KEY,
customer_id DECIMAL(12),
description VARCHAR(50));

CREATE TABLE account (
account_id DECIMAL(12) PRIMARY KEY,
customer_id DECIMAL(12),
plan_id DECIMAL(12));

CREATE TABLE queue (
account_id DECIMAL(12),
disc_id DECIMAL(12));

CREATE TABLE dvds (
disc_id DECIMAL(12) PRIMARY KEY,
disc_title VARCHAR(255),
genre_id DECIMAL(12),
producer_id DECIMAL(12)
copies DECIMAL(12));

CREATE TABLE producer (
producer_id DECIMAL(12) PRIMARY KEY,
producer_firstname VARCHAR(255),
producer_lastname VARCHAR(255));

CREATE TABLE genre (
genre_id DECIMAL(12) PRIMARY KEY,
genre_name VARCHAR(255),
description VARCHAR(255));

ALTER TABLE account
ADD CONSTRAINT acc_cst_fk
FOREIGN KEY(customer_id)
REFERENCES customer(customer_id);

ALTER TABLE rental_plan
ADD CONSTRAINT rent_cst_fk
FOREIGN KEY(customer_id)
REFERENCES customer(customer_id);

ALTER TABLE queue
ADD CONSTRAINT queue_acct_fk
FOREIGN KEY(account_id)
REFERENCES account(account_id);

ALTER TABLE queue
ADD CONSTRAINT queue_dvd_fk
FOREIGN KEY(disc_id)
REFERENCES dvds(disc_id);

ALTER TABLE dvds
ADD CONSTRAINT dvd_genre_fk
FOREIGN KEY(genre_id)
REFERENCES genre(genre_id);

ALTER TABLE dvds
ADD CONSTRAINT dvd_prod_fk
FOREIGN KEY(producer_id)
REFERENCES producer(producer_id);

This procedure now works. Thanks!

CREATE PROCEDURE RENT_MOVIE
@account_id_arg DECIMAL(12),
@disc_id_arg DECIMAL(12)
AS
BEGIN
INSERT INTO queue(account_id, disc_id)
VALUES (@account_id_arg, @disc_id_arg);
END;

The requirements I am focused on are parameterized procedures. The next is another procedure that adds a movie to a customer's queue as the next movie to be delivered. The second is a procedure that enrolls customers into specific rental plans. The 3rd is a procedure that adds a certain number of movies to a title's inventory, which is now part of the DVDs table. I can probably handle this one myself now. If anyone wants to assist with these, that would be great, but I just wanted you to be aware of the requirements I need to meet if you do choose to assist me with the two issues listed above.


texts:
Hello masters of MS SQL

I have a kind of a problem on my hands. We have quite big DB (5TB) and we've just "enabled" some software archiving which means that most of the data will migrate to other DB's. In other words, this big DB is now almost empty with data and will not grow anymore.

The issue here is I have a big file that I need to shrink (space problem). And I wonder how should I approach this problem? Shrinking will probably take quite a while and I am not sure what will happen if something goes wrong and if it's OK to do backups in the middle of shrinking.

Can somebody help me with this issue and shine a little light on this matter?

This is current situation (data is still being deleted, and I assume we will land at 300~500 GB size)

Thank you in advance


texts: Access-PostgreSQL: Link tables as readonly, edit registers in form

texts:
I have a PostgreSQL database working as backend and I have to develop an Access frontend solution for regular users of this database.

I decided it best to create some forms to manage the input, delition and deletion of data in the datbases, but as I am somewhat new to Access I may not be righ in my assumption here: if I import tables from the database into Access using the linked-table facility, and if I specify such connection as read-only (I want them to use the forms and the forms alone, least they screw everything up), when using the forms, they would connect to my Access's access of the tables, which is read-only, and thus edition, addition and deletion will not be supported for the form, right?

As I though of this, I also thought of creating a button on the form to change the table it is connected and bound to. Basically, it would open an ODBC connection to the real database and bind the form to the same table, but using different access rights.

Here comes the real question: how viable/useful/helpful/problem-solving is this solution and what VBA elements would I need to use? (I just learned about macros and VBA the last week)


texts: How to disable case sensitivity in SQL Server 2012?

texts:
I tried to update collation_name in sys.databases. I am trying to change 'SQL_Latin1_General_CP1_CS_AS' to 'SQL_Latin1_General_CP1_CI_AS' in order to disable case sensitivity in table.

update d set d.collation_name='SQL_Latin1_General_CP1_CI_AS'
--SELECT name, collation_name
FROM sys.databases d
WHERE name = 'db_name'

However, I'm getting this error:

  Msg 259, Level 16, State 1, Line 1
  Ad hoc updates to system catalogs are not allowed.


texts: Statistics: CPU Time vs Elapsed Time. What is more important?

texts:
When performance tuning, what is more important:

CPU time or elapsed time?
Are there scenarios where one of them is more important than the other?

An example: while performance tuning the CPU time will reduce by ~38% but the Elapsed Time increases by ~22%. Is this an improvement?


texts: SQL Server R2 Standard Edition MAXDOP setting

texts:
I'm looking for confirmation/guidance on setting MAXDOP in a SQL Server instance with the
following config:

Version      : SQL Server 2008 R2 Standard Edition
Processor    : 2 x AMD Opteron(TM) Processor 6234 = 24 cores
Hyperthreading enabled

For this processor each individual socket has two NUMA nodes, each of which has 6 cores in it. Microsoft’s KB article, Recommendations and guidelines for the “max degree of parallelism” configuration option (http://support.microsoft.com/kb/2806535) recommends that
“For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node.” so I would want to set MAXDOP to 6 (the number of cores in one NUMA node.)

However, Microsoft’s documentation on the max degree of parallelism Option http://technet.microsoft.com/en-us/library/ms181007(v=SQL.105).aspx tells me that the maximum value that can be used for MAXDOP in SQL Server 2008 R2 Standard Edition is 4.

So, i’m guessing that the version  recommendation of MAXDOP 4 overrides the processor/NUMA recommendation of MAXDOP=6? Anyone else have this configuration and know what happens if I try and set MAXDOP to 6 regardless?


texts: How to manually set growth increment and maximum file size for a database in PostgreSQL

texts:
In Microsoft SQL Server, I use the command below to set the file growth & max size of a database
ALTER DATABASE [NewDatabase]
    MODIFY FILE
    (MAXSIZE = 10MB,
    FILEGROWTH = 1MB);
GO

What is the equivalent command in PostgreSQL to do the same task above?
Does PostgreSQL support setting file growth & max size of a database?
Thank you.


texts: Delete data from a billion row table based on where clause

texts:
I need to delete about 400 million rows from a 1.3 billion row table based on an indexed date field.

The table is about 800 GB in size.

What is the most efficient way to delete the data without causing adverse effects? The table is under heavy use, meaning lots of inserts and updates (which don't affect the rows to be deleted).

There is a maintenance window, which would be fastest, but I may not be able to get a big enough maintenance window. On the flip side, I can take my time with the delete, so there is no rush.


texts:
I'm getting following message:

  Host 'host_name' is blocked because of many connection errors. Unblock
  with 'mysqladmin flush-hosts'

I've tried to follow MySQL :: MySQL 8.0 Reference Manual :: B.6.2.5 Host 'host_name' is blocked like so, yet without success(

# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
# mysqladmin flush-hosts
# echo $?
0
# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2221
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> FLUSH HOSTS;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
#

I even tried to restart mysqld, still getting same message(

Please advise.


texts: How to disable instance recovery in ORACLE?

texts:
I want my DB to stop instance recovery during opening, how to do it?

I want it because it crashes when trying to do instance recovery, I am testing some cases and want to disable it

Is there any possibility?


texts: When to use JSONB type for such a column?

texts:
I am designing some schema in my Postgres 12. And I have an entity (lets call it "Laptop") with an attribute (lets call it "Properties") like:
{ <someKey>: { <someLabel>: <someValue> } }

For example, the laptop has these properties:
{ "cpu": { "count": 8, "model": "Core i7" },
  "disk": { "size": 123456789, "model": "Samsung", "speed": 7200 },
  ...
}

And each laptop has own set of properties...
And now I have to choose how to implement it: as tables or just as a JSON/JSONB column in the table "laptops". What are the criteria / principles when to use JSON/JSONB columns or to design such attributes as tables?


texts: Oracle Secure Backup + Amazon S3 / RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

texts:
I have backup in Amazon S3 that was created without AUTOBACKUP settings.
trying to recover parameter file:
RUN
{
  SET DBID 3427966416;
  ALLOCATE CHANNEL ch1 DEVICE TYPE sbt parms 'SBT_LIBRARY=/home/oracle/S3BCKP/lib/libosbws.so ENV=(OSB_WS_PFILE=/home/oracle/S3BCKP/config/s3backup.cfg)';
  RESTORE SPFILE TO PFILE '/home/oracle/initSUPERPROD.ora' FROM AUTOBACKUP MAXDAYS 12;
  SHUTDOWN ABORT;
}

then I get:
channel ch1: no AUTOBACKUP in 7 days found
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/20/2021 09:21:38
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Backups are in the bucket. How to restore any of them?
upd. downloaded 0000000001 chunk that is relates to controlfile and spfile backuppiece with awscli utility
restore controlfile from '/home/oracle/0000000001';

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/20/2021 10:28:40
ORA-19870: error while restoring backup piece /home/oracle/0000000001
ORA-19505: failed to identify file "/home/oracle/0000000001"
ORA-27048: skgfifi: file header information is invalid


texts: If value is null then take the next higher value

texts:
I have a function which requires 3 values and then uses these values to return a value from a table.
FUNCTION: odwh_data.annual_rental(connection_type,data_rate,date)

CREATE OR REPLACE FUNCTION odwh_data.ead_charge_annual_rental(
    "connection_type$" character,
    "data_rate$" integer,
    "start_datetime$" timestamp without time zone)
  RETURNS numeric AS

SELECT
    annual_rental
FROM
    odwh_system.ead_credit_charge
WHERE
    connection_type = connection_type$
AND data_rate = data_rate$
AND start_datetime$::date BETWEEN COALESCE(valid_from,start_datetime$::date) AND COALESCE(valid_to,start_datetime$::date);

Below is an example of the odwh_system.ead_credit_charge table:

Currently is a data_rate is passed that is not in the list is will not return an annual_rental value. I would like to update this so if the value for data_rate is 30 it will take the next higher value e.g. 100 so annual_rental of £185.00 would be returned.


texts: Is a functional dependency on a "child" table allowed in third normal form (3NF)?

texts:
Consider two simple tables, OrderHeader and OrderDetail which contain, respectively, the data shown below:

OrderHeader

| OrderHeaderID | CustomerID | OrderTotal |
|---------------|------------|------------|
| 1             | 124        | 14.5       |
| 2             | 525        | 35.6       |
| etc           |            |            |

OrderDetail

| OrderDetailID | OrderHeaderID | ProductID |
|---------------|---------------|-----------|
| 1             | 1             | 415       |
| 2             | 1             | 52        |
| etc           |               |           |

The OrderTotal column can be calculated by adding the price of each product from the OrderDetail table.  That sounds like a functional dependency! ie:

SUM(OrderDetail.Product.Price) → OrderHeader.OrderTotal

...but afaik it's not a violation of 3NF.

Is this actually allowed?  If yes, is it disallowed in higher normal forms?


texts:
I have a linked server with SQL Server configured with the security option "For a login not defined in the above list, connections will: Be made using the login's current security context".

What I want to do is start logging which logins are using the linked server, and preferably whether the connection succeeded or failed, and where the login came from (which database, stored procedure, etc.).

Is this possible? I can't find anything obvious.


texts:
I'm a rookie with postgres, and after I started the postgresql-11.service,
I enter in 'su postgres', and try create my db, but appear this message:

[root@127 nemo]# systemctl enable postgresql-11.service
[root@127 nemo]# systemctl start postgresql-11.service
[root@127 nemo]# su postgres
bash-4.4$ createdb mydb
could not change directory to "/home/nemo": Permission denied
createdb: could not connect to database template1: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off"

Can anyone help me?


texts: Why SQL Server installs multiple services for AS and RS, but only one for IS?

texts: When I install multiple SQL Server instances on a same computer, every instance gets its own SQL Server Service for Analysis Services and Reporting Services. But I get only one service for Integration Service, why?


texts: Fail to attach database : MSDTC Check status of transaction

texts:
I am attaching a database from a snapshot taken of the production database volumes. These databases will be anonymised and then restored to our DEV servers.

Here is my attach statement:

CREATE DATABASE MY_DB ON
( FILENAME = N'H:\MY_DB.mdf' ),
( FILENAME = N'j:\MY_DB.ldf' ),
( FILENAME = N'i:\MY_DB.ndf' ),
( FILENAME = N'H:\MY_DB.mdf' ),
( FILENAME = N'i:\MY_DB.ndf' )
 FOR ATTACH
GO

And I am getting this error:

  An error occurred while recovering database 'MY_DB'. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (2:2141366340). Fix MS DTC, and run recovery again.

I have:

Checked security in MSDTC via component services
Restarted MSDTC
Restarted MSSQL
Restored another database from the same volume

Googling around hasn't helped much and my worst fear is having to recreate the volume snapshot just for this database. There are 4 others on the same volume that will have to be redone as well, if I don't find an alternative solution.

Question: Anyone know how to solve the error above?


texts: I have a project which we have to make a database for a car dealership. We have to make a Salersperson be able to view the inventory, add sales people, add cars/ car details. A customer must be able to view inventory, add cars they would think about purchasing, and then purchase the car or have a salesperson purchase the car. Salesperson must have their sales saved . I feel like my design looks a bit clunky and could be broken down better in a few more tables, but I am just drawing a blank when thinking about how else to break this up. Any suggestions?


texts: Set the primary key for many to many relationship

texts:
For the two relations, Customer and Equipment:
There is a limited number of equipments (stock)  and upon hiring I need to store the date of hire and the expected return date.

I sketched this:

Table Customer
customer_id

Table Equipment
equip_id
available_stock

Table Hire_Equipment
customer_id
equip_id
hiring_date
expected_return_date

What I'm not sure about is what is the primary key of Hire_Equipment?
If it is customer_id and equip_id then the customer wont be able to hire the equipment again in the future?
Did I mis-designed the schema? do you suggest a better approach?


texts: why is my trigger called even when the underlying table didn't change?

texts:
I have a quite basic trigger defined as such:

CREATE TRIGGER dbo.foo
ON dbo.bar
FOR INSERT, UPDATE, DELETE
AS
   ...

I also have a stored procedure which at some point does something like this:

Insert into dbo.bar (a) select a from @bar

When @bar contains some data, my trigger is called as expected, but when it doesn't, the trigger is called even though dbo.bar obviously didn't change.

This is confirmed by both SQL profiler and the contents of sys.dm_exec_trigger_stats.

SQL Profiler reports a very low footprint (3 reads, 0 write), but since I have a lot of triggers, this happens a lot, and I end up with significant total_reads figures.

Any hints on why this is happening? Is there a way to change this behavior?


texts:
Is There any tool to verify the backup taken using mysqldump without manual restoration ?

I have to take full backup daily so it is difficult for me to verify the backups daily by restoring them. As it will take time and also temporary disk space.

Please suggest me how to verify my backup online


texts: What are the possible reasons for SQL Server errors on reading operations upon switching ALLOW_SNAPSHOT_ISOLATION` and `READ_COMMITTED_SNAPSHOT ON?

texts:
I have a client-server app running in Windows Server 2008R2 using/interacting SQL Server 2012.

After reading app's documentation as well as feedbacks on internet from those who already accomplished it with the same app (but with SQL Server 2008), I've switched  app's SQL Server ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT isolation level in app's databases to ON.

This resulted in immediate users' complaints about errors from MS SQL Server:

which is:

Соединение с сервером баз данных разорвано администратором
(The connection with database server was terminated by administrator)
Microsoft Microsoft SQL Server Native Client 11.0: Communication link failure
HResult=80004005, HResult=80004005

which stopped after switching OFF these changes.

What is the most strange is that these errors were on reading/viewing operations like generation of reports, opening detalization of entities from opened reports, opening read-only docs, etc.

Eventvwr hadn't registered any insightful events if any at all.

Having hard times to understand why does the switching on ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT ON result in breaking/refusing new connections by MS SQL Server?

UPDATE:

The changes were made by scripts:

USE [master]
GO
ALTER DATABASE [DB_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [DB_NAME]
SET ALLOW_SNAPSHOT_ISOLATION ON --OFF

ALTER DATABASE [DB_NAME]
SET READ_COMMITTED_SNAPSHOT ON --OFF
ALTER DATABASE [DB_NAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

per each database with different database name

And the results were controlled by script:

select name, snapshot_isolation_state_desc, is_read_committed_snapshot_on
from sys.databases

Just to stress that SQL Server worked but rejected requests for reading additional details on already shown reports.


texts: Connecting to SQL Server with SQL Server Management Studio

texts:
I've just install SQL Server 2017, it showed this window:

When I open SQL Server Management Studio it has default Server Name as DESKTOP-6ECNPU1\matt which is the SQL Administrator value of the server. I'm confused because I thought the value that would be needed is the Instance Name MSSQLSERVER. I have connected to the database but I'm not sure if it's the right one, DESKTOP-6ECNPU1\matt could be a previously installed SQL Server that came with Visual Studio. So my question, have I connected to the SQL Server I just installed?


texts: SQL Query Killed, remains in Kill/Rollback with estimated time increasing

texts:
I killed a transaction and now it remains in Killed/Rollback status and whenever I run Kill 64 with statusonly I get this output:

SPID 64: transaction rollback in progress. Estimated rollback completion: 56%. Estimated time remaining: 12860 seconds.

However it stays at 56% and the estimated time only increases. It has been this way for a few hours atleast.

The query that I killed was dbcc shrinkdatabase(new2012, 10)


texts: How to use a SELECT query inside an ON CLAUSE in a LEFT OUTER JOIN in MySQL

texts:
I need to retrieve data from several tables. For now I am getting maximum of them from

     SELECT disc.id,
        users.user_login as 'last_active_user',
        disc.discussion_sub,
        list.todo_list_title,
        todo.todo_title,
        count(comments.comment),
        MAX(CAST(comments.comment_posted AS CHAR))
    FROM abc_discussions as disc
    LEFT OUTER JOIN abc_todo_list as list
    ON disc.comment_table_type = 'todo_list' AND disc.comment_table_id = list.id
    LEFT OUTER JOIN abc_todo as todo
    ON disc.comment_table_type = 'todo_item' AND disc.comment_table_id = todo.id
    LEFT OUTER JOIN abc_users as users
    ON users.user_id = 2
    LEFT OUTER JOIN abc_comments as comments
    ON disc.id = comments.discussion_id
    GROUP BY disc.id

where $last_comment_by->user_id = $wpdb->get_row("SELECT user_id FROM abc_comments ORDER BY comment_posted DESC LIMIT 1" )->user_id;

Is there any way to make it a single query?

I tried

SELECT a.id ,
a.discussion_sub,
b.todo_list_title,
c.todo_title,
ANY_VALUE(q.user_login) as 'last_active_user',
count(comment),
MAX(comment_posted)
FROM abc_discussions a
LEFT JOIN abc_todo_list b
ON a.comment_table_type = 'todo_list' AND a.comment_table_id = b.id
LEFT JOIN abc_todo c
ON a.comment_table_type = 'todo_item' AND a.comment_table_id = c.id
LEFT JOIN (
 SELECT x.user_id as user_id, y.discussion_id as discussion_id
    FROM abc_users x
    LEFT OUTER JOIN abc_comments y
    ON x.user_id = y.user_id ) d
 ON d.discussion_id = a.id
LEFT OUTER JOIN abc_users q
ON q.user_id = d.user_id
 LEFT JOIN abc_comments e
ON a.id = e.discussion_id
GROUP BY a.id

But getting wrong result for user_login and comment count .


texts: What if a connection closes in the middle of a commit?

texts:
I have a few large CSV files to upload to an instance of SQL Server.(Nope, can't use SSMS)
I'm using JDBC to read the CSVs line by line and insert the lines in a table in batches.
Supposing my code is as such:

val connection = DriverManager.getConnection(<urls and credentials>)
connection.setAutoCommit(false)
val stmt = connection.prepareStatement("insert into blah_table values(?,?,?,?,?,?,?)")
while(<csvReader has next>)
{
    //populate the stmt with values
    stmt.setObject(x,y)
    // add a batch
    stmt.addBatch()
}
stmt.executeBatch()
connection.commit()

Say the CSV file had some 20000 lines with some 50-60 or more columns.
I encountered an error by chance where the connection to SQL Server was lost while connection.commit() was being executed.
This resulted in ~11000/20000 lines being inserted.
I went through my application several times hoping to find some error I might have made. Everything appears to be fine.
My question is, is it possible that if the connection is lost in between committing, partial commits are left inserted?
If so, what could be a way out to save from partial commits?


texts: SQL to add zeros in front of existing data to meet field length

texts: How to add zeros in front of existing data of NationalityCode field to make the length 5 ?


texts:
In our current environment we have about 40 databases on one instance all being log shipped and configured as Standby/Read Only. About 90% of those are for reporting purposes and others for read only. The number of failures and maintenance plus user complain about not being able to access the databases when logs are being restored has forced us to rethink about this feature and we are strongly thinking of moving to Replication.

My main concern is can the Replication feature handle this many databases if we put our distributor on a separate instance? Shall we have one distributor or more than one? The network is handling the log shipping very well and I assume network bandwidth will not be an issue.

If replication is not a good option then what other option would be better that requires less maintenance or more importantly less failures.

We are on Amazon Cloud Infrastructure.

any input will be appreciated.


texts: Relation to original tables or to existing linking table

texts:
In my database I have a table with different settings for my app. Each setting is in relation to a guest (table guests) and an event (table events). So basically each guest has specific settings for each event he is linked to.

Every guest which has settings is are allready linked to the events for other reasons so there is an existing event_guest table with the necessary links.

So I'm not exactly sure about how I should link the settings table with the others.

Option 1

I link the settings with the table event_guest which links guests and events.

Option 2

I link the settings with the "original" tables guests and events.

Spontaneous I would go with option 1 but I'm a little bit confused about it...

My concern with option 1 is, that if I have a lot of deep relations, maybe even another table after settings, I need more complex sql queries to get for example data from settings, guests and events. Could this become a performance issue?

Which is the better solution and what are its advantages and disadvantages?


texts: Clustered Columnstore Index on Table with Foreign key

texts:
Using SQL Server 2014, I am trying to create a clustered columnstore index on a table which is referenced by a foreign key.

This is the syntax I am using

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Variant
  ON dbo.Variant WITH (DROP_EXISTING = ON)

And I get this error

  CREATE INDEX statement failed. A clustered columnstore index cannot be created over referencing column 'FK_Variant_Price' on table 'Variant'.

Any suggestions? I have looked online but cannot find anything.


texts: Issue installing Firebird ODBC drivers -- driver eventually no longer found

texts:
I am experiencing an issue with an SSIS job that connects to a Firebird DB using their ODBC driver (version 2.0.4.155).

The SSIS package runs flawlessly in Visual Studio 2008, which is installed on Windows 7 32-bit (don't ask!)
The package fails to execute when deployed to SSIS in SQL Server 2008 64-bit. The error:

  Data source name not found and no default driver specified.

This immediately made me think that a ODBC driver mismatch might be the issue, where SSIS is looking for the 32-bit drivers. So here is what I did:

Uninstalled the Firebird ODBC 64-bit drivers
Installed the Firebird ODBC 32-bit drivers
Modified the Job schedule in SQL Server so it executes in 32-vbit
Package runs successfully after that -- however there is a twist.

I will be able to execute the package again as long as I am still logged in to the SQL server (I logged in to install the Firebird drivers). I can execute it even from a remove SQL Server Management Studio connection -- but as long as I do not log off from the server.

If I log off from the server, then the SSIs job will no longer work (same error as before). This led me to think that this is not in fact a 32/64 bit mismatch, but perhaps some registry or environment variable is not being committed after I log off from thee server due to insufficient permissions (even though I was meant to be admin on that server).

So for my next test:

I uninstalled the 32-bit driver
Reinstalled the 64-bit driver
Remained logged into the SQL server
Executed the job time and again from a remote SQL Server Management Studio: It worked every time

As soon as I logged off from that SQL server, the behavior occurred again. This was hard to troubleshoot at first because the drive installs without throwing any errors and it even works at first.

But I am confident now that this has to do with permissions. Any ideas what I might be missing?


texts:
I have a database in Sql Server 2005. The database is configured to be backed up as Full (sunday) and Differential (weeknights) on a mediaset at a particular path.

When I run the command:

BACKUP DATABASE COB
TO DISK = ’C:BackupsCOB.bak’
WITH COPY_ONLY;

to backup the database to a different path, does that break the pre-configured backup sequence. I know that usually people run the copy_only backup without changing the backup sequence, but I was unsure what would happen if I changed the path. Still Learning. Any help will be much appreciated!


texts: How can I dump all tables to CSV for a PostgreSQL schema?

texts: I have a database with a lot of schemas in it and I want to dump the each of the table contents to CSV.  I'm aware of the COPY command but I'm not sure how to script something that will read all of the tables in a schema and execute the COPY against them.


texts: Postgres: Use result of group by as column names

texts:
I have a messages table in a database, which include a sender id and a message type (and of course many more columns not relevant for this question). I try to create a query which counts how many messages of each type a user have send.

e.g. if I have the following table:

---------------------------
id | user_id | message_type
---------------------------
1  | 1       | private
2  | 1       | public
3  | 1       | private
---------------------------

Then I want to get the following:

---------------------
id | private | public
---------------------
1  | 2       | 1
---------------------

So in fact I want to group by message_type and user_id, but instead of generating multiple rows per user, I want to create multiple columns, one for each message_type

Can I achieve this without hardcoding the message types in my query?


texts: Postresql using subquery select in order by clause

texts:
Is it possible to order by a selected columnname?

  SELECT * FROM tablex ORDER BY  quote_ident((
  select column_name from information_schema.columns
  where table_name = tablex AND column_name LIKE 'Index')) ASC;

The output of this is not ordered according to the columnname Index.

So basically my question is if i can use a select query in the order by clause like this simple one:

SELECT * FROM tablex ORDER BY (SELECT Index FROM table WHERE....);

What did i oversee?


texts: Why is the value 0 for the Max_data_length of my tables in my database

texts:
We are using a commercial version 5.6.16 of MySQL database which seems to have a 4GB limit. We have hit 3.96GB and are experiencing performance issues with the database. We came across some documentation that says you can increase the database size by increasing the max data length. Anyway when I use the call

mysql> show table status like 'weather' \G

in CMD to find my tables size limit. I see that the Max_data_length is 0 and not 4294967295 which is the typical size of the database. So why is my Max_data_length 0?


texts: Ola Hallengren's backups scripts, cleanup only mode

texts: Is it possible to run Ola Hallengren's backup scripts in cleanup only mode? I have a couple of servers where the cleanup time was set too long, and I now have to clean up hundreds of databases worth of backups. I could bang something together with PowerShell or similar, but I'd be more comfortable with some known good code handling it instead.


texts: Postgresql GIN index not used on larger table

texts:
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

I have a table named main_email with column 'sender', type character varying(150), not nullable.

I created both gin and gist trgm indexes:

create index test_gin_sender_idx on main_email using gin (sender gin_trgm_ops);
create index test_gist_sender_idx on main_email using gist (sender gist_trgm_ops);

On my smaller test database that has 17,219 rows in this table the index is used:

explain analyze select * from main_email where sender like '%andrew%';

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on main_email  (cost=4.32..27.56 rows=6 width=1434)
(actual time=1.593..4.032 rows=4327 loops=1)
   Recheck Cond: ((sender)::text ~~ '%andrew%'::text)
   Heap Blocks: exact=1197
   ->  Bitmap Index Scan on test_gist_sender_idx  (cost=0.00..4.32 rows=6 width=0) (actual time=1.363..1.363 rows=4327 loops=1)
         Index Cond: ((sender)::text ~~ '%andrew%'::text)
 Planning time: 0.167 ms
 Execution time: 4.449 ms

On the larger production database with ~1.5 million rows the index is not used and instead I get a parallel seq scan:

explain analyze select * from main_email where sender like '%andrew%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..194958.39 rows=4672 width=853) (actual time=0.310..244.993 rows=4396 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on main_email  (cost=0.00..193491.19 rows=1947 width=853) (actual time=0.049..239.576 rows=1465 loops=3)
         Filter: ((sender)::text ~~ '%andrew%'::text)
         Rows Removed by Filter: 514074
 Planning time: 0.153 ms
 Execution time: 247.316 ms

Why is the planner choosing to do a sequence scan instead of using the index?  Is it really faster to choose the sequence scan?  If so, why and is there any way to further improve the performance of this query?


texts: How to avoid server crash during backup of dabase using a crontab

texts:
I'm on shared hosting, so I want to avoid using too much system resources.
I have a crontab that schedules a MySQL database backup. The database is about 2gb before compression. When that crontab runs, the system seems to crash, because the http server stops for a minute or 2.

Is there anything that I can add or edit to my command that would run the db backup job gradually or in smaller steps to avoid server crashes?

Here is the command that causes the problem:

mysqldump --add-drop-table --user=xxxxxxxxx --password='xxxxxxxxxxxx' db_name | gzip >/path/to/my/backup_directory/db_backup.dmp.gz

Any tips please? or should I ask my host to change any settings in my account?


texts:
FYI - I originally posted this question under the Wordpress Development site here:

https://wordpress.stackexchange.com/questions/145861/how-to-split-the-wordpress-database

I have a site that is used to log client and meal information. It is based on Wordpress, but has quite a bit of custom code. We have custom taxonomies set for the client and meal data.

Our problem is that the database has gotten quite large and it's causing the site to operate very slow. I would like to essentially archive out the different years of the database into separate databases so that we could still refer back to some of the data if we needed to, but decrease the size of the database for the active year.

I've searched for an archival tool or plugin that could do this, but haven't been able to find anything (most of the results are for Wordpress archives - not the actual process of archiving out data from the Wordpress database). For example, in my database, the "postmeta" table currently has 2,656,529 rows, the "posts" table has 241,725 rows, and the "term_relationships" table has 1,164,119 rows. The entire size of the database is 356.7 MB.

We have custom reports created for the front-end admins to be able to generate meal & client reports, but they now will break if you select a date range longer than 1 month.

FYI - I've ran the WP Optimize plugin and have removed all post revisions already (which were very few to begin with). What I need to do is actually split these tables of the database up so that all of the meals that have been logged for 2012 are separate from 2013 which neesd to be separate from 2014.

Does anyone have any suggestions as to what we can do to get the database under control?


texts: How to estimate the I/O operations performed by MySQL queries?

texts:
On Amazon's RDS, you pay about $0.10 per 1 million I/O requests. I've noticed a very high number (in hundreds of thousands) of I/O requests for a very low traffic database. On seeing this, I did some further research, and found this question which describes that for a 6,000 user website, he was generating 800 million I/O requests a month, which would cost him about $80 / month.

Therefore, I want to know in advance how many I/O operations a MySQL query would generate, and how to optimize / minimize them. Is there any way to estimate how many I/O operations a query would perform, and any general rules I can follow to keep them as low as possible?


texts: Validate deployment failed deny policy in SQL managed instance Azure

texts:
I am trying to create a manged instance within an existing subnet in Azure. I have configured the subnet with the script provided by Microsoft which tells me the subnet is prepared.

When I create the managed instance I get

  'Deployment validation failed.
  Additional details from the underlying API that might be helpful: The
  template deployment failed because of policy violation. Please see
  details for more information.'

If I check the activity log I see

  'InvalidTemplateDeployment' and 'Operation name 'deny' Policy action'
  with some further infiormation under the JSON tab e.g.  '
  eventTimestamp": "2019-10-30T10:22:16.9421982Z",
      "id": "/subscriptions/xx/resourceGroups/xx/providers/Microsoft.Sql/managedInstances/xx-sqlmi/events/23572bf7-dbbb-4ba0-a13e-075a8ae58652/ticks/637080277369421982",
      "level": "Error",
      "operationId": "a12adf73-dd1b-4216-80df-dea57d514ba1",
      "operationName": {
          "value": "Microsoft.Authorization/policies/deny/action",
          "localizedValue": "'deny' Policy action." '

None of this seems to me very helpful, how do i diagnose and fix this error? Is it that my network/subnet configuration is wrong? Permissions? A policy which needs to specify managed instances can be created?


texts: How can I recover a dropped table which is not in the recycle bin?

texts:
I dropped a table yesterday and I want to restore it. I know that it's done with flashback but the table is not present in the recycle bin (yes it is set as ON).

So how can I restore a table that is not in the recycle bin. I am logged in as sysdba and also there are no rows neither in the dba_recyclebin nor in the user_recyclebin.


texts: How to add multiple rows to a table that contains only a generated always identity column?

texts:
This more of a Q/A question, perhaps someone is facing the same problem, so I'll ask a question and then answer it.

Say that we have a table defined as:

CREATE TABLE T (x BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY);

and need to generate one row for each row in another table. For one row we could use:

INSERT INTO T (x) VALUES (DEFAULT);

However:

INSERT INTO T (x) SELECT DEFAULT FROM SRCTABLE

does not work, and generates the following error:

SQL0206N  "DEFAULT" is not valid in the context where it is used.
SQLSTATE=42703

How do I add multiple rows to such table without resorting to a cursor?


texts: Safe to Restore Database from Untrusted Sources

texts: I am asking someone to aggregate around 100GB of data for me. I would prefer this to be in a Postgres database instead of something like CSV. I was thinking pg_dump and pg_restore custom format. Is it safe to restore the database onto my machine? Can they inject something malicious in there?


texts: Create a SQL Server Login and User with limited permissions for CRUD application Access

texts:
I'm trying to create a SQL statement to create a login and user in SQL Server that will allow a web application to perform crud actions on a single database, and allowing full access to all tables and stored procedures, views etc.

I know how to create a login and a user... but I then get out of my depth as the best way to continue.

CREATE LOGIN [myUser] WITH PASSWORD=N'xxx', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

CREATE USER [myUser] FOR LOGIN [myUser]
    WITH DEFAULT_SCHEMA = dbo;
GO


texts: How to take single database backup using mysqlbackup command in Mysql 8.0.18 version DB

texts:
I have a mysql instance(8.0.18-commercial MySQL enterprise server) with 8 databases in it and out of those I only need to take the backup of DB named 'Test1'.

Please note that its 300 GB sized DB and I want to take backup and restore it using mysqlbackup command. Is there any option for that, please guide me.


texts: How to decompose the relation R(a,b,c,d,e) whith following FDs into BCNF?

texts:
I have  a FDs

ABC->DE
AB->D
DE->ABCE
E->C

My trial :

Step 1 :

A+ = A
B+ = B
C+ = C
D+ = D
E+ = EC
AB+ = ABD
ABC+ = ABCDE
DE+ = ABCDE

From above we get ABC,DE are our candidate keys

Step 2 :

ABC -> DE ==> no violation. bcoz abc is key.
AB -> D ==> violation.
(ABD)(CE)

in (ABD) AB is a candidate key. So it is in bcnf
in (CE) C is the candidate key. So it is in bcnf.

I have done here. But unable to process further. There is confusion after this that the step 2 is right or not.
Can anyone solve this?


texts: Can't restore sql bak file to a new database

texts:
I can't seem to restore a bak file to the same server but a new database. It works fine if I do it on another computer. I use SQL Server 2012 through SSMS. Everytime I do it it says Exclusive access could not be obtained because the database is in use, even though it's a nonexistent destination database.

Here are the steps:

Right click Databases
Restore Database...
[General] Source from Device, select the bak file
[General] Change the Destination database name to a new name
[Files] Change mdf and ldf Restore As paths to a different, new path
[Options] (unnecessary on the other computer) Check Overwrite the existing database (WITH_REPLACE)
[Options] (unnecessary on the other computer) Check Close existing connections to destination database
Click OK

This is the error message:

  Restore of database 'DELVIPROD_JUNE' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  Additional information:
  System.Data.SqlClient.SqlError:Exclusive access could not be obtained because the database is in use.
  (Microsoft.SqlServer.SmoExtended)


texts: Best way to replicate MySQL database tables

texts:
I'm trying to wrap my head around the many options on how to possibly sync database tables automatically from one database to the next (and next, and next, ...).

We're trying to create a very simple database sharding system for our marketing saas product where we've created ID's that represent a project-id (the id we'd like to "shard" on) and a local-id (primary key of the table). These ID's are 64 bits and look something like this: 1099511627777 (project: 1, local id: 1).

We like to create a system that allows us to scale 'horizontally' by letting us move projects from one database or server to another one or even to a completely new one.

Now, I've tried researching all the options and it's just too much for me to start. We like to create a system where only the database tables are replicated to all the other databases. But only the tables, not the data inside these tables.

Is there such a way to replicate only the tables and thus keeping the 'cluster' of databases in sync with each other? Ideally we want to have our application (Laravel/PHP/MySQL based) run migrations only on one database/server and replicates it automatically to all other databases and servers

Thanks in advance


texts:
I'm able to dump the table of users from one server with

mysqldump -u root -p -h [host address] mysql user > user_table_dump.sql

but how can I restore these users into my localhost enviroment? I tried without success:

mysql -u root -p mysql.user < user_table_dump.sql

mysql -u root -p mysql user < user_table_dump.sql

mysqldump -u root -p -h localhost mysql user < user_table_dump.sql


texts: What impact does UseMicrosoftUpdate have on the UpdateSource option when installing SQL Server 2014?

texts:
The ConfigurationFile.ini generated for SQL Server 2014 contains a new parameter that I cannot find documented anywhere - UseMicrosoftUpdate.

What impact does this parameter have on the UpdateSource and UpdateEnabled parameters?


texts:
mysql> show processlist;

| Id    | User    | Host                    | db           | Command        | Time | State              | Info             || 27098 | DELAYED | localhost               | placeholder  | Delayed insert |  111 | Waiting for INSERT |                  |

I'm finding this process as suspicious, but nothing in ps, netstat, cron.How to get more info about that process? Thanks.


texts: MySQL: Data pagination according data on another table

texts:
I have a database with the following structure

Items table: itemID, itemName
Ads table: AdsId, itemID

I would like to make pagination on items table, 10 items in each page, but first I must retrieve items whose IDs are in the Ads table, and then retrieve other items.
I know I must use limit such as:
SELECT * FROM Items LIMIT $offset, $no_of_records_per_page

And may I joined it with adsTable, such as:
SELECT * FROM Items inner join Ads on Ads.itemID = Items.itemID
LIMIT $offset, $no_of_records_per_page

but how can I achieve what I described?
Thanks in advance.


texts: How to do a simple SELECT/UPDATE/DELETE performance test?

texts:
I have a table which might be causing excessive delays during insertion of new records.  I'd like to run a test against the database, measuring how long it takes to select, insert, and delete from the table.

Are there any built-in tools to do this against an SQL Server database table?


texts: Is it possible to READ LOCK all databases and then UNLOCK one by one?

texts:
I need to take backup of databases from a live server for replication. There could be 2-5 databases each having size of 1-5 GB. Master is a live server so I can not stop mysql service. Since size of data is big, baking up in Read-Only mode could be dangerous too. I'm wondering is it possible to

1. READ LOCK all databases and stop bin-log writing
2. Take backup of a database and UNLOCK only that one.

MySQL Version   : 5.1.61
Storage Engine  : MyISAM

Thanks in advance.


texts: Repeating pattern X amount of times in LIKE

texts:
I took a look at the documentation for patterns from Microsoft (can be found here), and from what I understood, it doesn't say a way for a pattern to repeat a limited number of times. It's either the wildcard (which goes on indefinitely) or looking for a specific character in a location, with each location typed out independently. Is there a way to type out "Pattern X repeated Y times"?

For example, if I wanted to look up account numbers that have exactly 5 digits, what I have now is:

SELECT * FROM table WHERE account LIKE '[0-9][0-9][0-9][0-9][0-9]'

Is there a shorthand way to do that?


texts: MySQL proc table getting crashed frequently

texts:
One of my MySQL server is frequently getting crashed with the below error. I repaired the table but please help me to understand the reason for crash and prevent it.
    Table './mysql/proc' is marked as crashed and last (automatic?) repair failed
+------------+--------+----------+------------------------------------------------------+
| Table      | Op     | Msg_type | Msg_text                                             |
+------------+--------+----------+------------------------------------------------------+
| mysql.proc | repair | info     | Wrong bytesec: 116-114- 97 at 1849604; Skipped       |
| mysql.proc | repair | info     | Found block that points outside data file at 1856336 |
| mysql.proc | repair | info     | Wrong bytesec:  54- 53- 46 at 2513660; Skipped       |
| mysql.proc | repair | info     | Found block that points outside data file at 1900432 |
| mysql.proc | repair | info     | Found block that points outside data file at 1972664 |
| mysql.proc | repair | info     | Wrong bytesec: 116-114- 97 at 1849604; Skipped       |
| mysql.proc | repair | info     | Found block that points outside data file at 2586424 |
| mysql.proc | repair | info     | Found block that points outside data file at 2621952 |
| mysql.proc | repair | warning  | Number of rows changed from 157 to 146               |
| mysql.proc | repair | status   | OK                                                   |
+------------+--------+----------+------------------------------------------------------+

Server is MySQL 5.7.27 community edition and database has got so many partitions and stored procedures.
Thank you,
Peter


texts: character encoding issue when using Invoke-Sqlcmd

texts:
We use a powershell script to download sql scripts from SVN and apply them to our databases. It works fine except when it comes to Unicode characters.
For instance the script contains an insert for 'Saint Barthélemy' and when inserted through the powershell it will end up being 'Saint Barth?lemy'. If the same script is executed through SSMS the value will be inserted correctly.
I use following Powershell code:
Import-Module -Name SqlServer
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $defaultDatabase -InputFile "$tempFolder\$fileName" -AbortOnError -OutputSqlErrors $true -IncludeSqlUserErrors -verbose

Is there any setting that might impact the character translations? This happens with SQL Server 2016


texts: Allowing end users to kill their own queries

texts:
I am an end user (programmer/analyst) without permissions to kill my own queries in our datawarehouse.  This causes problems when I need one killed (like right now - 5:30 on a Friday afternoon) and there is no-one around to do it for me.

I'm looking for a solution that would allow me to see a list of my currently running queries and then specify a query to kill.  I would only have permission to kill my own queries and no others.

All of my queries are read only - I have no permissions to write to the DB.  We access the DB via ODBC from various 3rd party apps, and sometimes from MS SQL Svr Mgmt Studio. Not 100% sure of the server version, but let me know if it's necessary and I can find out.

Is this possible?  If so, what downsides exist.  Ideally I would like to be able to sell the idea to the DBA team and have them implement it.


texts: How to execute a large script on each database in an instance?

texts:
How can I execute a large (>40k char) block of T-SQL on each database in a MSSQL instance, considering the following have character limits which would truncate the command passed in a variable, and I have to reference the database name in the block of code:

sp_foreachdb
sp_executesql
EXEC


texts: How to get the logins and Users automatically removed whenever anybody leaves a organization without any impact

texts:
I wanted to know is there any functionality already available in SQL Server which will automatically removes logins and Users in SQL Server, whenever anybody leaves a organization without any impact. Here leaving from organization means its Windows NT ID has been disabled (Later point of time deleted) in the Active Directory immediately.

If the functionality is not available/cant be automated, then how we can achieve it. Through Powershell Script or some TSQL Script or Need to query AD.

I would like to explain the exact scenario which is happening in my organization as I feel, expalining it will explain my requirement more accurately:

SCENARIO: When Somebody leaves a organization, there is an Access Management team which sends a CSV file with details of those Employees to Windows/AD team. AD team with the help of that CSV will run a powershell script which will disable those accounts from AD.
Now what happens is as a DBA we will not come to know when a person is leaving organization. If somebody leaves from DBA team, we will know that because he is part of our team but for others we will not come to know. So what is happening is we still have lot of Logins in SQL Servers of people who have left the organization.

What I am thinking is if somebody leaves the organization/been disabled from AD, is there any existing functionality in SQL Server or in AD which will notify us/SQL Server or do any action if some account is disabled from AD. In that case we will get notified then and there itself and then we can do the needful like removing dependency from his login, deleting login and users etc.

If functionality is not present, then what is the best way to get identified of it. Should I ask my AM team to send that CSV to us as well and we can run a powershell script against that CSV over all the SQL Servers to find the dependency and automatically remove it. Or there is some other more sophisticated way to achieve it.

Any Help is appreciated. Thanks in advance. :)


texts: How can I query matching MongoDB documents and return a subset of fields from an array of embedded documents?

texts:
I have documents similar to the following:

db.etudiants.insert(
    {
        nom:'emile',
        prenom:'zola',
        cours_reussis:[
            {
                intitule: ['PAI'],
                Periodes: NumberInt(60),
                note: 60,
                date_reussite: NumberInt(2014),
                dispense: true,
                nom_ecole:'EPFC'
            },
            {
                intitule:'TGP',
                Periodes: NumberInt(40),
                note:75,
                date_reussite: NumberInt(2013),
                dispense: true,
                nom_ecole: 'HEB'
            },
            {
                intitule: 'SO2',
                Periodes: NumberInt(60),
                note: 70,
                date_reussite: NumberInt(2015),
                dispense: true,
                nom_ecole: 'ISFCE'
            },
            {
                intitule: 'IBD',
                Periodes: NumberInt(60),
                note: 80,
                date_reussite: NumberInt(2015),
                dispense:true,
                nom_ecole:'ISFCE'
            }
        ]
    }
)

How can query matching documents and return nom_ecole: 'ISFCE' and year (date_reussite) from the cours_reussis array?


texts: Help with Transferring Linked Server to another server?

texts:
Please advice me as I'm trying to move the linked server (Providers with the tables in Catalogs) from SQL server 2005 to another upgraded sqlserver 2008, I scripted out everything from there, but not sure what's the next step or how to import them back in the new server?

Thanks in advance,


texts:
How to select a column to compare two values in it with AND

select distinct c.reg#, a.fname, a.lname, to_char(b.L#) as "L"
  from employee a, driver b, trip c
  where a.e#=b.e#
  and b.l#=c.l#
  and (c.reg#='PKR3344'AND c.reg#='WWF1155')
order by c.reg#;

error no rows selected
I want c.reg# for the two values inclusive


texts: Concurrent connections calculated in Mongodb Atlas

texts: I'm considering using mongodb for a mobile backend database, and I'm slightly confused at something I haven't found an answer to in the documentation. Are the concurrent connections for each tier in Atlas calculated on a per cluster basis, or per shard within each cluster? How does sharding affect the limits on the number of reading/writing operations per second? Have a feeling I'm misunderstanding something crucial since haven't found questions related to this online at all.


texts: Why is sys.dm_exec_connection.connect_time later than sys.dm_exec_sessions.login_time for user processes for some cases?

texts:
I executed the query on my production server.

select  s.session_id,
        s.login_time,
        s.is_user_process,
        c.session_id,
        c.connect_time,
        case when c.Connect_Time <=s.login_time then 1 else 0 end AS is_connect_time_earlier_than_login_time
from sys.dm_exec_connections AS c
 JOIN sys.dm_exec_sessions AS s on s.session_id = c.session_id

Here is the snippet of the output.

session_id  login_time  is_user_process session_id  connect_time    is_connect_time_earlier_than_login_time

167 2018-01-18 20:27:14.330 1   167 2018-01-18 20:12:50.747 1

171 2018-01-18 20:12:57.340 1   171 2018-01-18 20:12:57.347 0

Is there an explanation whey the 2nd row shows that the connect_time is later than the login_time ? OR is it a case of the reported time resolution not being too good in the millisecond range ?


texts:
I know how to script a stored procedure using PowerShell and SMO:

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  | out-null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server"
$srv.databases['MyDatabase'].StoredProcedures['MyProc'].TextBody

But since I want to use the procedure text within a query, I wonder whether I can call SMO in some way within pure T-SQL.

Must only run in development environment.

BTW: A function returning the definition of a stored procedure with a given name would solve my current problem but not answer my question.

Edit:

I want to find which procedures reference a given table when I use this query:

SELECT p.name --, definition
FROM sys.sql_modules m
join sys.objects p on m.object_id = p.object_id
where p.type = 'P'
and definition like '%SearchForThis%'

That is exactly the same information I want to get using SMO.


texts: Import CSV data into SQL using bulk insert with text qualifier

texts:
I am looking for a solution to my task. I am importing 800 000 records in CSV format into SQL server. The data is comma separated and the text qualifier is double quotes. But some data in columns contain comma. When I tried it using SSIS package, getting the following error

  Error: Data conversion failed. The data conversion for column "xyz" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Therefore I googled and found one solution as Bulk Insert. The data is uploaded but with errors. The column containing comma are splitting into another columns. Below is the query.

BULK
INSERT [5022019]
FROM 'E:\5022019.csv'
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Can any one help me out to eliminate the splitting of text into another columns.


texts: How do I copy the structure and contents of some tables from one database to another?

texts:
How do I copy the structure and contents of the tables below in SQL Server Management Studio from database SNO_TEST to SNO_TMP (on the same server)?

The names of the tables are ADMIN_ErrorLog, AdminMissingFiles and SNO_QAErrorCategory


texts: Running query against SQL Server with FreeTDS's bsqldb returns blank output

texts:
Here is the query I ran

select CAST(PageGuid as varchar(max)) + ',' + CAST(TenantGuid as varchar(max)) + '/' + CAST(OriginalFileGuid as varchar(max))
from Pages

This worked with tsql but when run with bsqldb I get empty lines instead of results.

I confirmed that bsqldb works by running other simple queries. So why is this one not working?

Here is the verbose output

bsqldb:163: Verbose operation enabled
bsqldb:212: Query:
    select CAST(PageGuid as varchar(max)) + ',' + CAST(TenantGuid as  varchar(max)) + '/' + CAST(OriginalFileGuid as varchar(max)) from Pages
bsqldb:186: dbsqlsend(): OK
bsqldb:194: dbsqlok(): OK
bsqldb:289: calling dbresults: OK
Result set 1
Freeing prior allocations
Allocating buffers
Allocating compute buffers
Metadata
col     name                            source                          type             size    varies
------  ------------------------------  ------------------------------  ---------------  ------  ------
     1                                                                             text   64512       1

Data

Retrieving return status... none
11084 rows affected
Retrieving output parameters... none
bsqldb:615: dbresults() returned NO_MORE_RESULTS (2):
bsqldb:212: Query:

I'm using FreeTDS 0.64RC1, FreeBSD 8, and SQL Server 2008


texts: ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block"

texts:
I faced an error while logging to database with regular user (non SYS/DBA):

C:\Users\user>sqlplus tester@orcl
ORA-01033 ORACLE initialization or shutdown in progress

However I can connect via sysman@orcl as sysdba user. Before I start with solving the issue, below are the errors that can tell what state my orcl database is in:

SQL> select * from dba_data_files;
ORA-01219: database not open: queries allowed on fixed tables/views only

So I use recover database and alter database open commands and they point at the same logfile and block:

SQL> recover database;
ORA-00600: internal error code, arguments: [3020], [3], [192], [12583104], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 192, file offset is 1572864 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'
ORA-10560: block type 'KTU SMU HEADER BLOCK'

SQL> alter database open;
ORA-01172: recovery of thread 1 stuck at block 192 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed

I did also that:

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

Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORA-12514: TNS:listener does not currently know of service
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

After restarting oracle.exe service and diagnosing the database, the "redo" error has a bit changed (block 705):

ORA-10567: Redo is inconsistent with data block (file# 3, block# 705, file offset is 5775360 bytes)

My log files:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                IS_
---------- ------- ------- ------------------------------------- ---
         3         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO03.LOG     NO
         2         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO02.LOG     NO
         1         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO01.LOG     NO

I'm in a point where I cannot use my database at full even by a sysman user. Please help, advise.

EDIT:
After following this guide which ideally addresses my situation, I get different error and cannot follow it anymore:

SQL> alter system switch logfile;
ORA-01109: database not open

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

Question aside: Who can tell if this db is open or not? :-)


texts: Is splitting a 'users' table for authentication purposes a good idea?

texts:
Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table.

For speeding up my login process, is it a good approach to split my user table, one for their information and one for their login.

If we can run a query similar to the one below from one table:

select username,password from users where username=`test` AND password=****

Is it necessary to split it, and does this speed up my site's login process?


texts:
I am working on site to allow users to view video game dialogue from Japanese games. The idea is to have a way to display the original Japanese text with an English translation. Pretty much in the same way it's displayed in game, so a piece of dialogue from a character could be two words or several paragraphs long.
Here's my current attempt at modeling this.

I wanted to break up the dialogue into scenes to give some context as to where this dialogue is happening. I also wanted several scenes be their own chapters, but the chapter table feels kind of useless now that I've seen up my CRUD operations in the app. Any help is appreciated, thanks!


texts: Inserting data to a table which is being cleaned up by SQL job at the same time

texts:
Our app uses a table in a database and we have a Sql job set up to run overnight which executes a stored procedure which just deletes data older that from the table.

DELETE FROM
        MyTable
    WHERE
        CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());

Now depending on the data stored on that table (on the previous day), the job completes fast or very long. When It is long, the app throws timeout error inserting into the table. Obviously this is reported by the users using the app overnight.

I have following options :-

a) Change the time of the job so that minimum number of users get affected.

b) Delete certain number of rows at a time rather than delete all of them together.

delete top (10) MyTable where CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());
while @@rowcount > 0
    begin
    delete top (10) MyTable where CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());
    end

Could anyone think of a better option?


texts:
I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so here is what I have tried.

SHOW STATUS WHERE `variable_name` = 'Threads_connected';
SHOW STATUS LIKE '%onn%';
SELECT * FROM information_schema.processlist WHERE USER='database_user';

I work for a web hosting company, and one of our clients keeps hitting his max_user_connections limit, so in troubleshooting why I need to know how many connections his user is using right now since he is no a shared server. I am currently using MySQL (InnoDB) version 5.5.36. Any help would be greatly appreciated!