texts: SELECT with where between not working as expected

texts:
I'm trying to use this query, to filter between the names C89 to C110.
SELECT id FROM servers WHERE server_name BETWEEN 'C89' AND 'C110'

But this doesn't work. If I change c89 to c90, it at least selects the two servers. It's like it's only using the first 2 numbers in the calculation.
Can anyone help me ? I'm using mariadb 10.5 I'm looking for a solution that doesn't need to add a lot of words into the query. To be as lean as possible for readability


texts: Why would SET ARITHABORT ON dramatically speed up a query?

texts:
The query is a single select containing a lot of grouping levels and aggragate operations.
With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this behavior on SQL Server 2000 and 2008.


texts: What kind of complications would I face downgrading from MySQL 8 to MySQL 5?

texts:
Let's assume I wanted to downgrade to from MySQL 8 to MySQL 5.7. I know this isn't supported, but what kind of complications am I likely to encounter.

The docs say,

  The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you backup your data before starting the upgrade process.

Let's say I buck the caveat, and dump on 8 and revive on 5.7? What should I expect? Dumping on 5.x would product comments like

/*!40101 SET character_set_client = @saved_cs_client */;

These comments are documented as,

  If you add a version number after the ! character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number.

Are those kind of comments still produced by version 8? If so, what kind of scenarios would they not cover?


texts: What is the locking behavior for Updatable/Insertable Views?

texts:
Consider I have a View that is merely a simple join between 2 tables, selecting all columns.

The View is Updatable and Insertable.

What would be the locking behavior when executing an UPDATE or INSERT statement in the following situations:

I’m going to insert a record into the view, and only columns of table A have values.
I’m going to update a record in the view, but only columns from table B were modified.

My doubt is basically if on situation 1, only table A is going to be locked, and if on situation 2, only table B is going to be locked.

I’ve searched for the answer in IBM’s documentation, but couldn’t find an appropriate answer. If possible, I'd like to know where IBM is keeping this information.


texts: Data sync between two local Mysql databases in different system in same netwrok

texts: I am using mysql as database for my application and for the purpose of fail over and I need to sync the data from  one local mysql database in system one to other local database in system 2 connected through same network.So I want to know how to connect local database in other system and sync data automatically


texts:
I created a new login and a new user and I mapped a database in SQL Server 2012. In the login properties in Securables, we selected permissions "deny" for "View any database" for the user to see only the specified database.

The problem is that the user does not see any database.

If I select "Grant" to "View any database", user can see all databases. How can I make the user can see only the specified database?


texts: Error in in sysft_Name while restoring database

texts:
I have a .bak file which gives the following error while restoring:

  System.data.sqlclient.sqlerror:The path 'E:' has invalid attributes. It needs to be a directory. It must not be hidden, read-only, or on a removable drive.

But I verified that the directory is not read only. Anyone know how to restore the three files being restored ( .mdf,.ldf, and sysft)?

The script i had used for restoring database has been given below.

RESTORE DATABASE [new] FROM  DISK = N'E:\a\L11.bak' WITH  FILE = 1,  MOVE N'itest' TO N'E:\a\3.mdf',  MOVE N'itest_log' TO N'E:\a\4.ldf',  MOVE N'sysft_test' TO N'E:\a',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO


texts: Is NEWSEQUENTIALID unique within a database for Many tables?

texts:
Is NEWSEQUENTIALID unique within a database? We plan many tables in a database with NEWSEQUENTIALID, inserting simultaneously and want to ensure every guid will be unique, regardless if they are in different tables.

Regular NEWID() will guarantee uniqueness in a database, and chances of having duplicate is chance of being struck by asteroid/lightning, however they do not alleviate the fragmentation issue.

This article talks about Mac address, however I want to specify question for database specific above.
https://stackoverflow.com/questions/28404964/is-uniqueidentifier-unique-across-databases


texts:
I am reviewing freshly generated scripts out of SSDT publish tools.  These projects were built from a default template, as were the publication profiles.  They set page_verify none for the database.

I was under some hazy impression that the best practice would be to have it set to checksum.

The only thing I can think of is that the SSDT target platform is Azure.  Can someone clear this up with something authoritative?

Thanks


texts:
I have a very large database, roughly 100 GB. I'm executing query:

select * from <table_name>;

and I want to show only the 100th to 200th rows.

I want to understand how this happens internally. Does the database fetch all of the records from disk into memory and sends back 100th to 400th rows to the querying client? Or does there exist any mechanism, so that only those records (100th -200th) are fetched from database - by using indexing mechanism like B-trees etc.?

I found that this is related to pagination concept, but I could not exactly find how it happens internally at the database level.


texts: Oracle - capture row commit order in a table

texts:
Is there a way to capture the order in which rows get committed in a table?

Our application has clients that query the table based on a sequence_id column of the table, that is supposed to reflect the commit order, regularly.
The largest sequence_id received in the previous attempt would be used as the filter to get the new rows.

I want to capture the order in which rows became visible to a read operation (select query). This number will be used by the read operation to decide 'upto what point it received data last time' so it will ask for sequence_id > max_sequence_id recevied_last_time to get the new data.

How do we implement the logic of inserting rows with the correct sequence_id to reflect the order of commits? Using the sequence does not seems to guarantee this.

How do rows become available to a select query? I believe it is after a row is committed, not after a row is inserted. Our tests show that order of rows become available for a read operation is different to the sequence values as the sequence.nextval is calculated at the point of insert and not at the point of commit.


texts: Azure's New Tiers (2018) - Standard vs Premium

texts:
We've done what we can to index things intelligently, but we're beginning to scale up our database usage and have decided scale up (?) from Azure's S4 tier to P1. It looks like P1 has fewer DTUs and is going to be slower for a given amount of processing. It looks to me (and makes sense to me) like the Premium tier is primarily offered so that organizations who care have a high-availability option. I've seen some references to the premium level having a higher "IOPS per DTU", but that seems like it would break the DTU as a unit.

In short: If I'm primarily concerned with performance and not so much with HA, is a higher-numbered (S4/S6/S7...) a better choice than a P1? To put it another way, is one DTU always one DTU, or is a DTU in a tier harder/better/faster/stronger than one from a lower tier?

Graphic below pulled from this page on Azure pricing.

I don't believe this is a duplicate of this similar question, mostly because the other question appears to predate the new tier structure that is key to my question.


texts: Unique ID for related records in a MySQL Table

texts:
I have a requirement to allocate a unique RecordID (ImportID) to multiple rows in a table. We are importing a text file which contain related transactional information but variable record types and I want to be able to track the import by the unique ID. By way of example we import a text file into a rawtext_table (longtext field type) and then make use of SUBSTRING selects according to the file specs to extract the relevant data. The text file, when generated, groups the related transactions - so records relating to transaction 1 will follow in each subsequent row and then record 2 with related transactions etc etc - the problem here is that the sequencing on of the transaction could be the same based on the record type. The raw file would output the following extract using a substring select:
NWR0001 - Joe Soap; ADR0001 - 10 WhoknowsWhere; CNT0001 - 0112001234
NWR0002 - Another Soap; ADR0002 - 10 SomeOtherPlace; CNT0002 - 0115553458
REV0001 - Marie Soap; ADR0001 - 10 WeNowKnowWhere; CNT0001 - 0118881111

The LEFT 3 char is the record types and come in a specific sequence and are inserted in various tables based on the record type, but not all records in the file will have all the types. Records will start with with either NWR or REV and end with the last type related to the transaction. As an example, in the data above the REV001 is a revision record, but has the same ID if we utilised position 4-7 which is the transaction ID.
My requirements is to have a query / stored procedure in MySQL that can step through the records and on each sequence change / block of related transactions allocate a unique ID and insert this into the various tables based on the Record Type (insert statement). So for the information provided above, NWR0001, ADR0001, CNT0001 would all have an ImportID of 1 and NWR0002, ADR0002, CNT0002 and ImportID of 2 and REV0001, ADR0001, CNT0001 would have an ImportID of 3

Any help with this would be greatly appreciated.


texts: What is the importance of customized data spaces in SQL Server and what are the quantitative measures that can be used to efficiently design this?

texts:
I am curious as to what are the advantages of creating custom table spaces for Microsoft SQL Server 2008. As of now, and based on my limited experience all I can really tell/assume are the following

Creating custom table spaces organizes the physical files of the database therefore it might help improve the speed of disk reads
Disk reads might also improve if your table spaces are partitioned.
You will be able to clearly segregate database objects into separate physical locations (tables/clustered indexes/etc) Although I am not sure how this exactly works and how this might help improve the performance of the database

Now I'm not really sure if these assumptions of mine are correct and if so, what are the other advantages aside from what I have mentioned?

On the other hand, are there any quantitative measures that I can use in order to design my custom table spaces? For example, is there any mathematical formulas that I can use in order to set the growth size of table spaces according to the table's number of columns? Or is it more of predicting growth based on a timeline of gathering statistics of the table's usage?

One last thing, Is it much more efficient to segregate table spaces according to database object type? or according to logical design? (e.g. create separate table spaces for indexes and tables or create separate table spaces for each business module/function)

Please take note that I still have little experience with this and feel free to ask for any clarification if my question/s are not understandable

Edit: So I've read a bit about the mdf and ldf files and it seems to slightly address my problem with the growth of the database.

source: http://www.simple-talk.com/content/article.aspx?article=920

but still, Is there any importance of segregating per table space?

Edit 2: Okay based on further reading, if I am not mistaken, this basically falls on the category of segregating into different files/filegroups.. What advantages can I also expect from this aside from having to control the growth of the different mdf/ldf files, instead of maintaining just one big file?


texts: I would like to learn how postgres works i.e how queries are handled internally,the read and write performance ? I don know where to start,help me with refrences


texts: Logging only mySQL logins on heavily used system

texts:
I have an audit requirement where they want all database access (logins) logged.  We have a very large distributed system of hundreds of mySQL servers (shards, slaves, slaves of slaves) that are serving a thousand queries per second.  As a result, I can't just turn on general query logging as recommended in Audit logins on MySQL database, it'll be multiple GB/day per instance and kill our disk IO.

I've looked and see two options to do discretionary logging with filtering - an Oracle plugin and a McAfee plugin.  The Oracle one requires you to be paying them $ for enterprise mySQL, and with hundreds of db servers I'm not really prepared to start doing that today.  The McAfee one is brand new and I'm worried about stability on a large scale system like we have.

Any other good solutions to log only logins to our mySQL databases?  It doesn't have to be integral to mySQL, we'd consider UNIX level port sniffing shenanigans if they'd scale.


texts: DBCC CHECKDB firing every 20 - 60 seconds

texts:
I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message:

Starting up database 'dbname'
CHECKDB for database 'dbname' finished without errors

I noticed this because the end users who are testing this had a problem which I isolated to an error that I noticed in the Windows Event Log (Application Log):

The log for database 'dbname' is not available. Check event log for related messages.

Incidentally, I found no related messages but resolved with offline/online cycle.

The only thing I can think of is that this application collects data every 1ms, but all of this data does not get inserted into the database. Being a dev server, it was config'd so that logs and data are on the same drive. With these two pieces of information in mind, I'm leaning toward this being an IO issue and that the server is attempting to recover from a loss of connection to its storage (SAN). Yet even this doesn't make sense to me because it is not losing all dbs, just this one. What else could be causing this?

Platform: SQL Server 2008 R2 (Ent.) on Windows Server 2008 R2 (Std.)


texts:
I'm creating an automated package in PDQ Deploy which installs the Oracle client, and some patches. Step 1 copies the staging files to the target, which completes successfully. Step 2 attempts to install the Oracle Client silently, and hangs indefinitely.

These are the commands I'm using for the silent install, any idea what's going wrong here? I'm using a template that I found here. The Oracle client version is 11.2.0.4.0

cd C:\PDQStaging\Oracle\64bit\client\
.\setup.exe -silent -debug -force -waitforcompletion
FROM_LOCATION=C:\PDQStaging\Oracle\64bit\client\stage\products.xml
oracle.install.client.installType=”Administrator”
ORACLE_HOME=”C:\Oracle\64bit\product\11.2.0\Client”
ORACLE_HOME_NAME=”OraClient11g_Home1″
ORACLE_BASE=”C:\Oracle”
DECLINE_SECURITY_UPDATES=true

I wanted to avoid using a response file since the above seems like a simpler solution.


texts: Implicit conversion warning despite persisted computed column vs. performance hit from UDF in persisted computed column definition

texts:
I have a need for a computed column to resolve an implicit conversion problem. I have a column that is declared as a VARCHAR that should only ever store integers, but is occasionally populated in error by a third-party application with strings, so that column needs to stay as is.

The table is often joined to one that stores that value as an INT. I defined a PERSISTED computed column that converts that VARCHAR to an INT:

[iOrderNumber] AS (CONVERT([int],
case
   when [ordernumber] like '#%' then (-99) when isnumeric([ordernumber])=(0) then (-99)
   when CONVERT([bigint],[ordernumber],(0))>(2147483647) then (-99)
   else [ordernumber]
end,(0))) PERSISTED NOT NULL

With an appropriate index on the computed column, performance dramatically improved for queries that could now join to the INT column. However, execution plans still show an implicit conversion warning even though the conversion happens when the table is updated, not when it is read from.

I tried using a UDF in the computed column definition to get rid of the warning (I found a blog post that suggested that), but then execution of my queries took much longer and used much more CPU although the logical reads stayed the same. But the UDF did eliminate the warning.

Is there a reason to consider the warning anything other than a bug? Is there a reason to consider the optimizer's handling of a persisted computed column defined with a UDF as anything other than a bug?

More importantly, is there any way to get rid of the warning without incurring the performance penalty from the UDF solution?

I considered using a trigger and a translation table containing the VARCHAR and INT versions of the data instead of using a computed column, but that seems like a lot of unnecessary overhead.


texts: Get the list of all Weight attribute members

texts:
I have, in SSAS, a fact PERSONS which contains the Measures [Number of  PERSONS] and [Weight].
The first one has the value 844.
In order to achieve some calculus, I would like to get the list of all Weights, not only the list of the different ones.
The query
WITH member number as
COUNT (NONEMPTY( [Measures].[Weight]   ))

SELECT number ON 0
FROM [mycube]

produces:

I don't understand this result. It is not the result of an aggregation.
The query:
WITH member number as
COUNT (NONEMPTY([Dim Date].[Date].[Date].members , [Measures].[Weight]   ))

SELECT number ON 0
FROM [mycube]

used to bypass some aggregation produces:

which is not the correct number.
An answer could to create a dimension (I could get the entire list with the function '.members') but it is goes against the purpose of Weight, which is a measure, not a group.
thank you.


texts: Query to check for free disk space and send an alarm if the disk space exceeds threshold value

texts:
I would like to create a job which sends an email alert when my server disk space goes beyond a specific threshold value.Since my data files are bigger there is very little free space on disk partitions.

  Is is possible to write a Query which checks all the disks for the
  free disk space and give an email alert if the disk space goes below
  the set threshold value?


texts: Key/value table: what is a good way to organize it if value could be foreign key?

texts:
Suppose a have a structure of two tables

Table Key names:

|_Key name_|_Parent_key_id_|
| key1     |  NULL         |
| key2     |  key1         |

Table Key values:

|_Key__|_Value__|_Version__|
| key1 | value1 | version1 |
| key2 | value3 | version1 |
| key1 | value2 | version2 |
| key2 | value3 | version2 |

So key values are unique within the Version and keys follow nested structure.

The goal is to organize tables the way that key values could be a string, number (integer or double) or reference (id) to a row from another table.

My thoughts are

Store string, number, id in a single VARCHAR column and make nullable column Reference table name in Key names - so if the column is not NULL, then value is an id.
I'm not feel very good of it because then I face a problem of no type check, and for example floating point number could be written with , or . as decimal separator.
Make 3 nullable columns instead of Value: String value, Number value, Id reference - if one has value, others should be NULL. Again I need Reference table name column to specify which table I reference.

I wonder if some best practices are there. Couldn't find them by myself.

Thank you in advance. We use PostgreSQL.


texts: To have PostgreSQL-like Partial Index in MySQL 5.5

texts:
I have big data where I only select a small interval of data at a time such that the selection is always in a sequence.
I am trying to implement PostgreSQL like Partial index in MySQL which is targeted for such purposes.
I am not sure if the partial unique constraint is the same as the one which I want.

Code in PostgreSQL 9.4

CREATE UNIQUE INDEX dir_events
    ON events (measurement_id)
    USING btree
    (eventBody)
    WHERE is_active;

Attempt on ypercube's partial index in MySQL

CREATE UNIQUE INDEX dir_events
    [index_type] -- TODO what here?
    ON events (measurement_id, is_active)
    [index_type] -- TODO what here?

How can you create PostgreSQL-like partial index in MySQL 5.5 or similar?


texts: Update selected data from same table column as currency

texts:
I am trying to update selected data from a table column and format this data as currency.

My table column holds strings that have both mixed whole numbers and already formatted data. This was due to the fact that I had migrated old data which were whole numbers but any new data or updated old data gets formatted correctly in the application and database.

I would like to select all the non-formatted data from this table column  and then Update this data to currency format.
0 to 0.00,
200 to 200.00,
23702 to 23,702.00,
34000.00 to 34,000.00,
2954795.54 to 2,954,795.54

Any assistance would be highly appreciated.

Thanks


texts: Need help finding inactive users in my database

texts:
I am trying to figure out how to get this figured out.

Problem:

Users of inactive customers are receiving scheduled maintenance notifications

Indicators:

Organizations have a custom binary field to indicate if they are Active
Users have two binary fields to indicate if they can access the
system

Solution:

Identify any users that have their Active and/or CanLogIn values set
to 1 AND
Are associated by OrganizationID to an Organization with the custom
Active field set to 0 (zero)


texts: pg_restore --clean not working because cascade drop

texts:
I'm working with a copy (DB-B) of a database (DB-A) I keep up to date by running daily:
pg_restore -U postgres -d someDB --clean -j 2 -L WhatToImportList admin.dump

But I started noticing duplicate records in DB-B tables. Upon further investigation seems the --clean is not dropping the tables in DB-B because it would require a cascade drop on other tables and views that are in my DB-B but NOT in the origin DB-A.

Is it possible to force the import of data WITHOUT doing a cascade drop? I want to keep all my custom tables, views and functions!
If not, what would be a good duplication strategy where I import the data from DB-A to DB-B but keep all my functions, views and tables I need for my analysis in DB-B?

Thanks.
Edit: possible work around: truncating each table and then importing them... but i'd have to include in the script EACH TABLE.
Edit2:
To help future readers, with this managed to generate a list of all the tables that had to be truncated:
SELECT 'TRUNCATE ' || table_name || ';'
  FROM information_schema.tables
 WHERE table_schema='my_schema'
   AND table_type='BASE TABLE';


texts: Decomposition In Boyce-Codd Normal Form may lose relations?

texts:
Let be the following Realtion R={UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd, ServeurMail} with the functional dependencies:

F = { UtilisateurID → Nom, Prenom
      UtilisateurID, ServeurMail → Login, Passwd;
      AdresseEmail → UtilisateurID;
      AdresseEmail → ServeurEmail;
}

What are all minimal keys?

I said that it was K = { AdresseEmail } as far it gived every others.

In order to put it BCNF I had the following algorithm

We take X→A from F

We create R¹(X,A)

F¹={X → A} R¹ BCNF

    R²=R-{A}

        E¹:F²=FD from F except those that affect A.

        E²: IF R² is BCNF → END

        ELSE We decompose R2 returning to E¹

So I did:

R isn't BCNF because no FD looks like key  → attribute ¬key

R¹={AdresseEmail, ServeurEmail}

E1: F¹={AdresseEmail  → ServeurEmail}

R²=(UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd)

F²=(UtilisateurID  → Nom, Prenom

    AdresseEmail → UtilisateurID

   )

So my BCNF decomposition would actually be:

R¹=(AdresseEmail, ServeurMail) and

R²=(UtilisateurID, Nom, Prenom, AdresseEmail, Login, Passwd).

But we lost AdresseMail  → ServeurEmail

It isn't trivial, X is a (sur)key and A hasn't key attributes therefore it is BCNF.

Is my decomposition right? Did I did a mistake when designing the key?


texts:
I'm not quite sure if this is the right place to ask this. If not I'm sorry in advance.

I'm looking for a table which includes Canadian zip codes, cities, states, and time zones preferably in MySQL syntax.

There are few paid databases out there in web but I'm looking for an open sourced one.

I will remove the question if it's not relevant to this website.


texts:
My team uses Oracle 11 and SQL Developer.  I've been relying heavily on explain plans lately to try and determine the most efficient way to solve various problems.  Recently, a coworker pointed out that explain plan is not always accurate to what actually happens in the database, and that an autotrace is a better indication since the query is actually run against the data.

Testing a query, I've gotten the following results

_________________________________
|      Method        |   Cost   |
|--------------------|----------|
| Query A Explain    |  306,188 |
| Query A Autotrace  |  399,131 |
| Query B Explain    |   99,226 |
| Query B Autotrace  |  137,661 |
|____________________|__________|

When using the autotrace, query A had a cost increase of 30% and Query B of nearly 40%.  Obviously, I should be using query b in both cases, but I don't understand what causes them to differ.


texts: Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

texts:
I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero.
From the DBA I received the following query and it ends with a ?  instead of a parameter. Here is an obfuscated sample of the query:
SELECT
tablex.x_id,
tablex.create_ts,
tablex.update_ts,
tablex.myStatus,
tablex.x_type,
tablex.ami_uploaded,
tablex.work_id,
tablex_capture_ts,
[column1],
[column2],
[column3],
[column4]
FROM     sqltable..tablex
INNER JOIN
sqltable..tableWork ON tablex.work_id = tableWork.work_id
WHERE
(tablex.update_ts >= ?)

According to the DBA, the question mark is replaced with a 'time/date' argument that is one hour in the past.
When I execute this same query locally from a stored procedure,
passing in a parameter that is one hour in the past,
it returns in less than a second. (which to me, means it 'can' use the existing index)
Watching this execute from the ETL, it takes minutes and the
execution plan shows table scans.
There is an update_ts index.

The query engine recommends a second update_ts index with several include columns. I'd like to avoid that if possible as it will add memory pressure and I'm not convinced it solves the real issue. Thoughts?
This seems like a case of the query statistics being skewed and when the query engine sniffs the parameter it avoids using the existing index because the estimated number of rows is beyond the threshold.
My questions:

How does the ? in the SSIS query get handled by sql server? I know parameter sniffing is a complex issue. I've been studying this: http://www.sommarskog.se/query-plan-mysteries.html
If it is the query engine sniffing the parameter (of one hour in the past) and thinking the estimated number of rows is beyond the tripping point, what do I do to fix this?  The DBA has refused the hint  OPTIMIZE for RECOMPILE as an option and I can't say I disagree. (He has a point regarding the bug history) However, these queries happen ONLY from the ETL on schedule times and perhaps that is reason enough to use the HINT regardless of the potential bug??

Also, this is a long issue I've been struggling with. All of these posts are related to this same issue. What a voyage of discovery:
Is this an excessively large 'Lock' time and is it indicative of a problem?
SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?
Any advice is greatly appreciated.

UPDATE 1
This should be the actual execution plan from the local stored procedure version. This version returns within 1 second and exhibits the behavior I WISH the ETL would have:
https://www.brentozar.com/pastetheplan/?id=ry4wy6dBO
UPDATE 2
Now, this is a screen grab of the ETL version that takes minutes to complete. Sorry I cannot provide more details on this particular query:

UPDATE 3
This is a screen grab from a profiler trace done over an hour period. I think this is how the ETL commands are being executed.  I don't yet, know why these all have the same time. I also need to find the prepare as well. Look at those cpu, reads, and duration columns!


texts: Why is mySQL output not procedurally consistent with source code input?

texts:
I've assumed the order of INPUT statements would be output in the order they were declared and processed in the source code. I need help understanding what I observe in this context and "if possible" or "if feasable" be told how to ensure output is mapped to the order of the source code input when running the following example script...

DROP PROCEDURE IF EXISTS csgRunLoop;
DROP TABLE IF EXISTS testUUID;
CREATE TABLE testUUID (id binary(16) PRIMARY KEY);

BEGIN
DECLARE x, y INT DEFAULT 1;

--  WHILE x  <= 3 DO
--  INSERT INTO testUUID VALUES(UUID_TO_BIN(UUID()));
--  SET  x = x + 1;
--  END WHILE;

-- Move the two INSERT statments below WHILE loop or run here above WHILE loop
-- and the output is not consistent with the order the statements were declared
   INSERT INTO testUUID VALUES(UUID_TO_BIN('d6c03d84-2eed-4f15-b681-90fe627fcacf'));
   INSERT INTO testUUID VALUES(UUID_TO_BIN('d6c03d84-2eed-4f15-b681-90fe627fcacf', true));

   WHILE y  <= 3 DO
   INSERT INTO testUUID VALUES(UUID_TO_BIN(UUID(), true));
   SET  y = y + 1;
   END WHILE;

END$$
DELIMITER ;

CALL csgRunLoop();

-- time shifted
SELECT BIN_TO_UUID(id) FROM testUUID;

-- untime shifted
--SELECT BIN_TO_UUID(id, true) FROM testUUID WHERE id LIKE('%');


texts: Backup architecture for frequent backups of business critical DB

texts:
We've got a database (mysql) driven application which contains business critical information, were looking at building a system that will allow us to backup the db frequently (every 15 minutes, ideally less) so that we mitigate the danger of any data loss.

Because the application is private and only being used by a handful of users (at most 12 at the same time) and because all the data is user inputted (not generate / feed / api driven) we dont really need to have too many considerations to scale of the DB or the backup architecture.

Is there an established architecture / workflow for running backups with these constraints ?


texts: Isolation level serializable not working as expected?

texts:
I have this table

create table testing(
c1 text,
c2 text);

I open two transactions simultaneously. t0 t1.. denote series of time snapshots in increasing order
Transaction 1

BEGIN; -- t0
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- t2
SELECT * FROM testing where c2 = 'rand'; -- t4
INSERT INTO testing VALUES ('rand', 'xyz'); -- t6
COMMIT; -- t8

Transaction 2

BEGIN; -- t1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- t3
SELECT * FROM testing where c1 = 'rand1'; -- t5
INSERT INTO testing VALUES ('rand1', 'abc'); -- t7
COMMIT; -- t9

After t9 I get this error
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

But if you see the statements, they are operating on difference values rand and rand1. It doesn't make sense for this error to be thrown as they can never end up in a bad state.

Is my understanding correct?


texts: Postgresql performance issues when issuing many small inserts and updates one at a time

texts:
I have a production web server with postgresql database.

My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in an insert/update to the postgresql database.

In order to avoid overloading my server, the requests are queued and handled one at a time.

So typically my server is doing (not many) reads from the database most of the time, and every hour on the hour it runs many inserts and update, one after the other.

The problem I'm experiencing is that once the updates/inserts start, the performance of the database gets very bad for a couple of minutes and then, even though the updates/inserts continue, the performance gets better and stay at a good level until the updates/inserts are finished.

I asked around and got some good directions that may explain the degradation in the performance of the webserver itself, but since I'm seeing that the database's performance also degrades, there might be more to this.

What can explain this behaviour? How can I fix this?


texts: Removing duplicate-ish paths generated from recursive CTE

texts:
I have some multi-parented data, that I run a recursive CTE against to find common ancestors:

item_id |item_name |path_name    |id_path_array |
--------|----------|-------------|--------------|
1       |D         |B->Two->D    |{9,4,1}       |
2       |E         |B->Two->E    |{9,4,2}       |
13      |W         |B->Two->D->W |{9,4,1,13}    |
14      |Y         |B->Two->D->Y |{9,4,1,14}    |
15      |X         |B->Two->E->X |{9,4,2,15}    |
16      |Z         |B->Two->E->Z |{9,4,2,16}    |

However, what I really want here is just the first two rows; I want to remove the rows that represent paths that have already been covered by a shorter path.  e.g. obviously if 'D' is a common ancestor then all of D's ancestors are too, so I don't want to include the paths for them.

I did think I had something with an array operator:

select * from common_ancestors q1
where exists (
    select 1 from common_ancestors q2
    where q1.id_path_array <@ q2.id_path_array and q1.item_id <> q2.item_id)

but even though it gives the right result here, I'm not convinced it's correct.  I would be happy to hear opinions or alternative solutions .

Desired result:

item_id | item_name | path_name | id_path_array
------: | :-------- | :-------- | :------------
      1 | D         | B->Two->D | {9,4,1}
      2 | E         | B->Two->E | {9,4,2}

db-fiddle here


texts: What does “conn” mean in mongostat utility?

texts: I'm troubleshooting an application issue that uses Mongo. I've just started to use the mongostat utility. Does the "conn" column show the number of established, authenticated TCP connections to the database, or does it show the number of database connections that are actually running an operation against the Mongo database?


texts: PostgreSQL streaming replication stops updating

texts:
I have four PostgreSQL 9.5 instances running on EC2 using m4.8xlarge instances with five PIOPS SSD in a RAID0 setup, and a separate XLOG drive.  Until this morning, I never had more than a minute or two of replication lag, but now replication fails completely on all instances after about 30 minutes.

Restarting Postgres fixes the problem for another half hour.

There's no CPU contention and iowait is usually less than 1%.  Blocking reads to the server, thinking that it might be overwhelmed, does nothing. I can't figure out what the problem is here, other than something on Amazon's end.

Can anyone give me some idea how to troubleshoot this problem?  There's nothing in the logs, replay_location (from pg_stat_replication) just stops updating until I restart the slaves.


texts: Simple SSIS Package Failed User does not exist or has not permission

texts:
I created and build SSIS Package with "Transfer SQL Server Objects
Task".
I Copied the package.dtsx to network folder with permissions    set
to everyone.
I created the SSIS Job where i set path to that   package.
I am owner of source and destination database too.
Package run as 32b.

But Job is failing. What can be wrong ? Why it is searching my credentials COMPANY\MYUSERNAME when the job is executed as COMPANY\SERVICEJOBACCOUNT ? (which also has access to the databases). Thank you

Error is following

Execution failed with the following error:
"ERROR : errorCode=0 description=Cannot find the user 'COMPANY\MYUSERNAME',
because it does not exist or you do not have permission.


texts:
I am new to databases and trying to solve one problem in which I have to build a simple database having few entries and then querying that database and then recording that answers from the database. I have to send many queries one by one. It is kind of experiment to learn database.
Could anyone suggest which simple software or language can I use to do that. I would prefer some high-level language. I know that it is doable in C/C++ but it does look fun in doing C/C++.

Thanks!


texts: Microsoft SQL Server: Prepared Statements

texts:
How do you create and use a prepared statement in SQL Server?

In PostgreSQL, you do something like:

PREPARE prepared (varchar, varchar, etc) AS
    INSERT INTO table(field,field,etc)
    VALUES ($1,$2,etc);
EXECUTE (value,value,etc);

Something similar in MySQL.

I can’t find any examples in SQL Server. I have seen a number of examples using a function, but I don’t think that’s the same thing.

Thanks


texts: using string concatenation to reference a table

texts:
For a database systems project which tracks entities histories, I stumbled acros the following problem.

Assume we have the table "entity"

CREATE TABLE entity (
    eID SERIAL PRIMARY KEY,
    eName VARCHAR(255) NOT NULL,
    eValue1 INTEGER,
    eValue2 INTEGER
);

as well as "entity_history"

CREATE TABLE entity_history (
    eID_FK INTEGER REFERENCES entity(eID),
    time TIMESTAMP DEFAULT current_timestamp,
    type VARCHAR(6) CHECK(type IN ('Value1','Value2')),
    val INTEGER,
    importantHistoricalEntryNotInMainTable TEXT
    UNIQUE(eID_FK, time)
);

Usually, the idea is that the history gets an entry on update/insert of the main table.
I have to do it the other way around (due to the entitys history being created based on external website information which is not stored in the main table upon insert), so whenever an entry is added to the history, it should update the current value of the field in the main table.
Thus I tried using

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity SET ("e" || NEW.type) = NEW.val;

wwhich yields a syntax error.


texts: Oracle Express intermittently stops responding, requiring a OS reboot to fix

texts:
I've been having an issue with Oracle XE the past 2 days and it's really been making me pull my hair out.

There seems to be no rhyme nor reason why this happens, or when it does, but it happened 6 times yesterday and so far twice this morning.  I'm developing a .NET app, and sometimes when I start the app, refresh the page, or do whatever, I get an exception that a connection could not be made to the database.  I then try to connect with sqlplus I just get:

C:\>sqlplus user/password@localhost

SQL*Plus: Release 11.2.0.2.0 Beta on Wed May 1 11:08:53 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

It then just sits there forever.  No timeout, no error, no nothing.  I can't connect with any other database program either, the whole server is just unresponsive.

If I go into the Services manager, I see that the services are indeed running so nothing has crashed:

I've tried restarting OracleServiceXE and OracleXETNSListener.  Both will shut down and restart, however I still cannot make a connection to the server.

The only thing that fixes this problem is a complete Windows reboot.  However, this of course takes 5-10 minutes and is really starting to get old.

There does not appear to be anything related to Oracle around this time in the Windows Event Viewer.  I'm happy to add any other information that would help debug.  Any ideas?

Update:

I did notice a few strange errors in listener.log.  These errors start the exact same time the crashes started happening:

30-APR-2013 08:50:42 * 12531
TNS-12531: TNS:cannot allocate memory
Tue Apr 30 08:52:28 2013
30-APR-2013 08:52:28 * 12531
TNS-12531: TNS:cannot allocate memory
Tue Apr 30 08:52:58 2013
30-APR-2013 08:52:58 * 12531
TNS-12531: TNS:cannot allocate memory

Last 1,000 lines of alert_XE.log are here.


texts: Why do we need myisam spatial extension anyway?

texts:
If just to determine whether a point is within a rectangle,

What is wrong with this query?
EXPLAIN SELECT DISTINCT
  TB.ID,
  TB.Latitude,
  TB.Longitude
FROM
  TableBusiness AS TB

WHERE
  -6.2317830813328 < TB.Latitude AND TB.Latitude < -6.1382169186672
AND TB.FoursquarePeopleCount >5   AND 106.72621691867 < TB.Longitude AND TB.Longitude <106.81978308133
LIMIT
  0, 20

It seems that normal index would do fine. After all TB.Latitude and TB.Longitude is indexed.

But then I want to do something more complicated like nearest neighbor search.

Then somebody says that even with spatial index I can't do that. I have to start with a small rectangle, find all points within that rectangle, and then see if I got 20 points, if not, make the rectangle bigger.

If I have to go through all that, why do I need spatial index?

So what's the point of having spatial index?


texts:
have installed MySQL in a particular folder. I was able to run the server and create accounts, databases etc. However, now whenever I try to start the server, I get an error:

$ mysqld_safe --defaults-file=mysql.cnf &
[1] 2002
[compute-0-5 /amber2/scratch/myname/mysql]$ 130725 17:56:24 mysqld_safe Logging to '/amber2/scratch/myname/mysql/data/compute-0-5.local.err'.
130725 17:56:24 mysqld_safe Starting mysqld daemon with databases from /amber2/scratch/myname/mysql/data
130725 17:56:25 mysqld_safe mysqld from pid file /amber2/scratch/myname/mysql/data/compute-0-5.local.pid ended

[1]+  Done                    mysqld_safe --defaults-file=mysql.cnf

In the error file inside the data folder:

130725 17:17:53 mysqld_safe Starting mysqld daemon with databases from /amber2/scratch/myname/mysql/data
2013-07-25 17:17:54 0 [Warning] option 'read_buffer_size': unsigned value 2147483648 adjusted to 2147479552
2013-07-25 17:17:54 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-07-25 17:17:54 28189 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2013-07-25 17:17:54 28189 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2013-07-25 17:17:54 28189 [Note] Plugin 'FEDERATED' is disabled.
2013-07-25 17:17:54 28189 [Note] InnoDB: The InnoDB memory heap is disabled
2013-07-25 17:17:54 28189 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-07-25 17:17:54 28189 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-07-25 17:17:54 28189 [Note] InnoDB: Using Linux native AIO
2013-07-25 17:17:54 28189 [Note] InnoDB: Not using CPU crc32 instructions
2013-07-25 17:17:54 28189 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-07-25 17:17:54 28189 [Note] InnoDB: Completed initialization of buffer pool
2013-07-25 17:17:54 28189 [Note] InnoDB: Highest supported file format is Barracuda.
InnoDB: Error: pthread_create returned 12
130725 17:17:55 mysqld_safe mysqld from pid file /amber2/scratch/myname/mysql/data/compute-0-18.local.pid ended

But why am I getting the pthread_create error 12? It seems this is related to not enough space. On the device where the mysql folder (/amnber2//scratch/myname/mysql) resides, I do have space:

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              49G  5.0G   41G  11% /
        ...
10.255.255.46:/export/scratch
                       15T   11T  4.2T  72% /amber2/scratch

I also have a few gigs in my home directory quota

$ quota -v
Disk quotas for user myname (uid 41222):
     Filesystem  blocks   quota   limit   grace   files   quota   limit   grace
10.255.255.45:/export/ncms
                22986221  26214400 26214400               0       0       0
10.255.255.46:/export/scratch
                7321108       0       0               0       0       0

i.e., I am using 22G out of 25G:

$ du -sh ~/.
22G /home/ncms/myname/.

I also have free memory:

$ free -mg
             total       used       free     shared    buffers     cached
Mem:            62         41         21          0          0         28
-/+ buffers/cache:         12         49
Swap:           64         13         51

In my mysql configuration file:

myisam_sort_buffer_size=4G
myisam_max_sort_file_size=200G
read_buffer_size=2G

So why am I getting the error pthread_create error while starting the server?


texts:
I created an indexed view with a few fields that i created a full text search based on these fields.

here is my view:

alter VIEW vFullTextSearch
WITH SCHEMABINDING
AS
    SELECT  M.Id as MessageId, M.Subject , M.Body,MR.EmailAddress, MR.FriendlyName,
            M.Subject + ' ' + M.Body + ' ' + MR.EmailAddress + ' ' + MR.FriendlyName as FTS
    FROM DBO.MESSAGE M
        INNER JOIN DBO.MessageRecipient MR ON M.Id = MR.MessageId AND MR.RecipientTypeId=1
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1_vFTS
    ON vFullTextSearch (MessageId);
GO

I indexed all the fields of the table.

I am using this SQL to find results

SELECT M.*
FROM Message AS M
    INNER JOIN FREETEXTTABLE(vFullTextSearch, *, 'Doe') AS KEY_TBL
        ON M.Id = KEY_TBL.[KEY];

but with "John Doe" being the [vFullTextSearch.FriendlyName] value in all the records of my view why am i not gettign results when searching all the fields for 'doe' ?


texts:
Say I create a nonclustered index on a table. After a week I look at the sats, e.g. number of seeks, scans, updates etc. E.g. say I have 10 seeks and 10000 updates. How can I measure the cost of the 10000 updates? That is how can I measure the cost of the 10000 updates on the nonclustered index vs 10 extra scans on the clustered index?
I want to learn when the cost of having the index is more than the benefit. How do I measure this?


texts: Database appearing in one tool but not another

texts:
Can anyone think of a reason why a mySQL database would be accessible from one tool (phpMyAdmin, logged in as db_user) but not from another (Sequel Pro, logged in as db_user).

The user is the same in each case and has full rights on the database in question.

In the case that I can't see it, I can access all other databases I would expect to and there are no obvious errors being returned.

The only thing that separates the tools is that phpMyAdmin is running on the same machine as MySQL so uses localhost whereas Sequel Pro uses the domain name.


texts: Passing in the table into a stored procedure

texts:
Is it possible to pass in the name of a table into a stored procedure?

For example, suppose you have several views of the same table. They all have the exact same structure.

You want a stored procedure that can be run for any of the views.

Something like:

create procedure myprocedure
@tableName varchar(50) = ''
select blah from @tableName where blah = blah2

When I try to do this, I get

Must declare the table variable @tablename

Any ideas how I can do this?


texts:
I installed Posgresql version 10 (stable) through the source package (compiling manually) in the directory opt/pgv10/ .. on my OS LInux Debian Version 8.

I would like to use the HIPOPG extension 1, 2 to enable the creation of hypothetical indexes in postgresql.

I checked on document (link) you have to have the Dev package from postgresql, but I believe my installation does not have this (I think).

I tried to download the file extension .zip but can not proceed with the compilation and installation (with make and make install), because it gives the following message:
    make: pg_config: Command not found
    make: Nothing to be done for 'all'.

On .zip file that I downloaded has the files: HANGELOG.md hypopg - 1.0.0.sql hypopg.control hypopg_import.h META.json test typedefs.list expected hypopg.c hypopg_import.c Makefile README.md TODO.md

In tutorials on the internet, they talk to install via apt-get, but as I installed it manually I believe it will not work.

I tried to run the command:
   create extension hypopg; in psql program, but gave the message below even though I put the hypopg.control files in opt/pgv10/share/postgresql/extension/

ERROR: could not access file "$ libdir / hypopg": No such file or directory

Would anyone know how to manually install an extension and if it would have to have the -dev package and how would I install that package.

Best Regards
Neto


texts: ORA-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair during AWR report creation

texts:
When I create an AWR Report, the following error occurs:

  ORA-13506
  Cause: An attempt was made to perform operation with an
  invalid Start/End Snapshot Pair.

What does this mean, and what should I do about it?


texts: Which of these two queries is better - can be optimized more?

texts:
With regards to the usage of the IF statement, which is better to use?

Query #1:

SELECT
    whatever
FROM
    auctions AS a
LEFT JOIN
    ...............
WHERE
    ...............
ORDER BY IF(a.max_bid > a.start_price, a.max_bid, a.start_price) DESC, a.end_time ASC, start_time DESC, a.auction_id ASC;

Query #2:

SELECT
    IF(a.max_bid > a.start_price, a.max_bid, a.start_price) AS current_price
FROM
    auctions AS a
LEFT JOIN
    ...............
WHERE
    ...............
ORDER BY current_price DESC, a.end_time ASC, start_time DESC, a.auction_id ASC;

Or is there a better way to order it?


texts:
I am learning about database warehouses and data modeling. I came across this blog post. In the section, "DIFFERENT MODELS HAVE DIFFERENT PURPOSES" the author mentions that depending upon the need of the business, there can be two types of data modeling:

Relational
Dimensional

However, in both examples the author says that the modeling is done in 3NF form, I am confused, how is 3NF and star schema both used in dimensional model. Aren't they supposed to be fundamentally different?
Lastly, if I understand correct, the relational data modeling is done for OLTP systems and Dimensional is done for OLAP systems, right?


texts: Grant schema permissions effective for everything existing and everything that will exist

texts:
I'm looking to do what this answer explains but in an Oracle environment:

https://dba.stackexchange.com/a/125400

Essentially what I'm looking to do is

"GRANT schema permissions that are effective for everything existing and everything that will exist in that schema."

with emphasis on "everything that will exist."

The programming department where I work is small (two of us), but the higher-ups have decided to turn on DB auditing as an added measure of security. We currently share one schema (let's call it MAIN) that has read only privileges on the production instance of the company's main database. In addition to be read only, it houses all the functions, packages, and procedures we use on a day-to-day basis to do our work.

So now instead of using one schema, we want to each use our own (USER1 and USER2) that looks at MAIN and sees everything it sees in real time. If USER1 goes in and creates a table/function/procedure called EXAMPLE in MAIN, we want USER2 to be able to go in and immediately see EXAMPLE without grants needing to be run. We're looking for this functionality because it's pretty common that months down the road after creating a query or something for someone, they'll come back and ask for that data again. If that query is stored in USER1's schema, and USER2 is the one fielding the request, they'll have to start from scratch to get the results for the person asking.

Is there a way to do this in Oracle?


texts:
I want to select the 3 numbers between ":". I don't know the length of this strings and the positions of the ":". I have as an example:

SELECT 'Espacio Asignado:358898204672: Espacio Usado:355976458989: Espacio Reclamable:2921745683:' FROM DUAL;

I think that with regexp, but I don't know how.


texts: Physical and logical replication slots on Postgres (same time)

texts:
I wonder if it's possible to have a setup when changes from a single Postgres master streamed to two (or more) replicas same time, but some replicas to use streaming (logical) replication and some to use physical replication?

So far I have only seen examples of how to use one of those, but not both same time?

I'm currently on Postgres 9.6, but might upgrade, if required.

Thanks!


texts: How can I narrow down the cause of a deadlock after the fact?

texts:
We're using a .Net 4.5 web application with SQL Server 2012 on the same machine. We will sporadically see a cluster of "Wait operation timed out" exceptions while using the site. Occasionally we will receive Deadlock exceptions as well. These are always on Select queries, not updates.

I have run sp_lock a few times and noticed there are sometimes as many as 4500 locks acquired by a single process. These are selects with multiple joins. Even though they are shared locks, could that be the source of our problems? Could adding nolock to these queries reduce blocking and timeouts and possible deadlocks? If that's not the right answer, is there a way to know what queries were being run when the timeouts occurred? I have not been able to catch it in action as it usually only lasts a few seconds before the database returns to normal funciton.


texts: How to full text search localized enums?

texts:
Suppose an SQL database like PostgreSQL with a simple table of products:

id  | name        | type
----+-------------+-------
10  | MacBook     | 1
11  | iPhone      | 2
12  | Boeing 747  | 3
13  | MacBook Pro | 1

type is an ID from a separate table product_types:

id | text
---+ ----------
1  | notebook
2  | smartphone
3  | airplane

There are several such tables in the database, let's call them master or enum tables (what is the correct name BTW?).

The web server using this database then understands these enums, and, sure enough, there will be a (Java) enum e.g.

public enum ProductTypes {
  NOTEBOOK(1), SMARTPHONE(2), AIRPLANE(3)
}

And some logic to localize these values to show to the user: String localize(ProductType type, Locale locale). Therefore, at the end of the day, a user that prefers German will see the following line in the list of products:

  Boeing 747 | Flugzeug

In reality, there is a mix of more than one "non-localized" column and more than one enum column:

  Boeing 747 | Flugzeug | 420 Passagiere | 2 Decke | 747-400/-400ER ...

I would now like to build a full text search on top of such database, one that would index product names as well as product types, and of course - for each of the server locales. I am mostly interested in an example implementation for inspiration. Some questions in no particular order:

I need to tell the database all the localized versions of all the enum items, correct? What if the localized names are "content" (i.e. not hardcoded)?
I need to have one full text index per localization, correct?
What if the list of localizations is "content" again, i.e. not known beforehand?


texts: What are the different connections of MySql?

texts:
I have a software(toad for mysql) to connect to my MySql database.
We have to provide some data('username','password' etc) to this Software to connect database.
There is a field known as  'connection type' and it has 6 options

TCP
SSL SSH
NAMED PIPE
MEMMORY
HTTP PROXY
HTTP TUNNEL

Anybody explain about connection type and this six options?


texts: Real Time SQL Server Analysis Services Cube Processing Report

texts: I would like to know if there is a way to generate a report to determine which SQL Server Analysis Services cubes are processing using SQL Server Reporting Services? I would like to be able to run this report without affecting the cube process, in real-time. The end product of this process is to create a way to let end users know the a report cannot be created because cube is processing.


texts:
Currently,SQL Services are using TCP IP protocol. Named pipes protocol is disabled. I am facing issues while running my application status as below :

An error has occurred while establishing a connection to the server.
(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

My Question is ,Will there be any issue if I enable Named Pipes now because I restarted the Named Pipes and restarted SQL Service and issue seems to be resolved. But I am confused why we are getting Named Pipes issue when TCP IP is enabled. Does SQL Server need both TCP IP and Named Pipes to be enabled ?

Thank you


texts: Postgres ERROR: tuple concurrently updated

texts:
I have a large table test in which in user_id 2 have 500000 records. So I want to delete this record in chunks of 100 records but it is given error.
Here is my query:

delete from test where test_id in (select test_id
from test where User_id = 2 limit 100 )

  ERROR: tuple concurrently updated

What is the issue. How can i solved it.


texts:
I have a table payment with a jsonb column application. It contains payments and the total amount for one or more tours. For example, a parent can pay 1000 $ for all his family, e.g. 4 children and his wife. Then one row in payment contains 6 records in the jsonb field application, describing the tour and the customer.

[
   {
    "tour_id":
      {
        "id":"d61802ff-3eec-4a72-97ca-832f51b96bf0","name":"Paris  2018"
      },
      "amount":"500.00",
      "customer_id":
          {
             "id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
             "name":"OBAMA John"
          },
     "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"
   },
   {
    "tour_id":
      {
        "id":"a52d38d4-9bfb-4ffa-1122-e536f04b0c60","name":"London  2018"
      },
      "amount":"500.00",
      "customer_id":
          {
             "id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
             "name":"OBAMA John"
          },
     "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"
   },
]

My id is unique in all the postgres database, I'm using UUID v4. Which is the best way to index this id? Really the name is redundant here, because I have a table for customers and tours, and I could just record the id:

[
   {
    "tour_id":"d61802ff-3eec-4a72-97ca-832f51b96bf0",
    "amount":"500.00",
    "customer_id":"f492b26d-d260-4bcb-8e96-6532fdf38665",
    "id":"5cd8338c-15c2-48c5-b305-9e172bb33a9a"
   },
   (...more elements)

But I'd like to get advise on the best indexing strategy (or alternative solutions).


texts: Calculating the FileStream Data Columns Length matching used FS Drive used space

texts:
Kindly advice me on below :

I am process of calculating the total size of LOB objects with below data types (includes Views and user schema tables) .

select * from information_schema.columns where data_type in
    ('TEXT', 'NTEXT','IMAGE' ,'XML', 'VARBINARY')
    or
    (data_type = 'VARCHAR' and character_maximum_length = -1)
    OR
    (data_type = 'NVARCHAR' and character_maximum_length = -1)

Once I get the output of below query , I am using DATALENGTH function to get the size of each column .

SELECT SUM(DATALENGTH('ColumnName')) / 1048576.0 AS ColumnName  FROM DatabaseName.schema.Tablename

NOTE :  The output will be Megabytes as I am using / 1048576.0

After all the above , my total drive size meant for storing File Stream Data is 1 TB  . But post calculating the length of each LOB Column with DATALENGTH function , it does not match up to the current used space on Drive .

For example :

Current Drive size is 1 TB out of which 951 GB is free space 0.99 TB . which means 50 GB is used by File Stream data type columns . But when I check all columns from above T-SQL I could not get exact size matching used space (50 GB) . Not even close size . they show very minimal usage .

What else could be using the drive apart from the data types mentioned above  ?

How to calculate the size of columns using DATALENGTH function which will match up to used space in File Stream Drive ..


texts:
Is there a command that I could use to check the password settings for MySQL without looking into each account? I'm trying to get password max length, min length, lockout threshold, password history, expiration date and etc.

I'm using MySQL 5.1.50.


texts: One of my Disaster recovery server every day sp_configure values are changing

texts:
We have a DR server, Daily databases are restoring from Production to DR server.

From last week onward, the Sp_Configure Value (Show Advanced) is changing.

How do I find out from where these values are coming from, they are different from the Production server Sp_Configure values.


texts: How to insert values to one table from the table stored in another database in postgresql?

texts:
Here is the structure of table:

`* Table 1 of database A contains mobile number and userId and both field cannot be blank
I want to fetch data from table 1 of database A and insert that with some other data in the table 1 of database B, Following are some clause that I have to follow during insertion and selection of data:
1. Check whether userid exists of a particular phone number from table 1 of Database A If user id exists the  Insert the row to Table B.1 of database B without phone number
2. If userId does not exists in the table A.1 of database A then Insert the row into Table B.1 of database B with null userId and with phone number (i.e. row 3rd of Table B.1)

So far what I have tried is:

WITH useridTab AS
(SELECT *
 FROM dblink('postgres://postgres:password@localhost/databaseA?sslmode=disable',
              E'SELECT mobilenum,game_user_id FROM table1 where mobilenum=\'1234\' UNION ALL
  SELECT \'1234\', null
limit 1') as data(mobilenum text,userid text))
insert into table1 (userid,phonenumber,email,address) select userid ,case when userid is null then mobilenum else 'No One' end,'ABC@mail.com','AbcAddress' from useridtab;

I used DB link to fetch data from table stored in some other database, created CTE of it then used that to insert values to table1 of database B.
Just wanted to whether this approach is correct or some more optimization can be done.


texts: Could somebody clarify whether tablespace expansion procedure in Oracle 11g database requires any downtime or could impact availability of the database? My understanding that this can be done "on the fly" w/o downtime or restricted-mode (at least when we just allocate free space from physical disk)... but I guess there could be some exclusions caveats to this?


texts:
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
In Data Guard environment this command tries to
1.backup archivelog before starting backup
2.delete archivelog before starting backup
3.backup database
4.backup archivelog generated during backup
5.delete archivelog generated during backup which in most cases these logs couldn't send or applied to standby database.
If you configure archivelog deletion policy as in the following it couldn't delete archivelog and shows warning as RMAN-08120: warning: archived log not deleted, not yet applied by standby.
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
How to configure backup with only delete archivelog before starting backup, without trying to delete archivelog during generated backup.


texts: SQL Server - Sudden log autogrowth after database backup

texts:
I have to manage a SQL Server 2008 Database, using SQL Server Management Studio 2012. The database file is about 290Gb. The problem we have is that the log file used to grow a lot.

Doing some internet research (I'm totally new to DB management), I came to the following conclusions, and applied all of them:

Set (what I thought to be) a realistic initial log size; 220 Mb.
Set autogrowth feature to increase in MB (instead of %).
Schedule regular log backups (currently once every 15 minutes).
Schedule a full database backup once a day.
I've scheduled 4 UPDATE tasks every 10 seconds to keep the log growing. These tasks are performed on 21000 records.

This configuration seemed to worked ok. I've been monitoring the behaviour of the log size, and it never needed to autogrow; when the reserved space for the log file was about to get full, the scheduled backup would free it again. After 11 hours, the log size had only increased up to 260MB, which I considered to be Ok (4x10Mb autogrowths in 11 hours).

So I went home, trusting that this behaviour would persist at night. But the following day, I saw that several autogrowths had been performed. The log size increased from 220Mb to 2350 Mb (more than 200 autogrowths!).

I decided not to change anything, and keep monitoring it; the used space for the log file never grew more than the 18%. I increased the autogrowth size from 10 Mb to 50 Mb.

Again, I was pretty confident that this configuration will prevent autogrowths from happening. And again, I was wrong. This morning I've found that a new autogrowth process has been performed. Only once, but it still makes me feel confused.

I've noticed that this last autogrowth operation took place after the full database backup was performed.

Can please anyone explain me if this behaviour is normal? Why the log size keeps stable during all day, and after the database backup is performed, it suddenly grows? And why does it grow, when barely 20% of the reserved space for the log file was being used at peak times, right before the log backup? Any recommendation?


texts:
I have seen this two ways:

mysqldump -u username -ppassword --databases mydb >with.sql
mysqldump -u username -ppassword mydb >without.sql

I did a linux 'diff' on the two and the with.sql file has this:

< CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
<
< USE `mydb`;

While the without.sql doesn't. I have used --databases on mysqldump but I have seen tutorials showing it without when doing a mysqldump on a single database.

Why are they different? Which one is most common to use? I'm preparing a mysqldump of a database for a client which might run it in Linux or Windows, so is there a concern of being more portable?


texts:
see this table:

    CREATE TABLE IF NOT EXISTS `default_messages_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_id` int(11) NOT NULL,
  `owner_user_id` int(11) NOT NULL,
  `to_user_id` mediumint(8) NOT NULL
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

I need to set only one possible combination of the columns message_id, owner_user_id, to_user_id meaning the combination (for example) 1,1,1 only can exists one time so no duplicates is allowed. How I do this? Creating an index between them?

Cheers and thanks in advance


texts: Row level restore with Oracle Recovery Manager (RMAN)

texts:
We're approaching the end of 2012 and I still find myself relying on database exports to maintain the ability to do row level restore operations. While this is a proven method, it sure would be nice to get rid of all the export operations hogging database resources a significant number of hours each day.

A typical scenario would be that a customer accidentally deletes a project or document from an authoring system. To fix this I will need to locate and put back those deleted rows.

I'll pull up a database dump of the customer's schema from a date before the accidental delete, import the data to a new schema. Now I can identify the lost data, and select it back into the production schema.

I realize that you can do this directly with flash recovery, but there's a limit to how much data we can keep in the fast recovery area, and customers tend to not always figure out their mistakes within the allotted time.

Is there a good way to accomplish these kinds of tasks with the use of Oracle RMAN?


texts: How do I get rid of the duplicate code in this query?

texts:
I have this query, which works:
SELECT *, ("last done" + "auto time span"::interval) AS "new last done"
FROM personal."time tabs"
WHERE "reset automatically" = true
 AND ("last done" + "auto time span"::interval) < now();

However, I notice the duplicate code: ("last done" + "auto time span"::interval). Is it possible to make it only mentioned once?
These did sadly NOT work:
SELECT *, ("last done" + "auto time span"::interval) AS "new last done"
FROM personal."time tabs"
WHERE "reset automatically" = true
AND "new last done" < now();

Or:
SELECT *
FROM personal."time tabs"
HAVING ("last done" + "auto time span"::interval) AS "new last done"
WHERE "reset automatically" = true
 AND "new last done" < now();

What is the right way?


texts: Shared memory connection experiencing network wait type

texts:
I have a poorly written application running a SELECT *, resulting in a 2MB result set every few seconds. I cannot correct this. I am confused though,by the connection encountering ASYNC_NETWORK_IO waits. The application is local to the database server.

I have verified the session_id in dm_exec_connections is 'Shared memory'. The client_net_address is 'local machine'.

Anyone ever experienced this before? Or able to explain to me why this is happening?

Thanks!


texts: Is storing attributes in separate tables instead of columns a standard practice?

texts:
This question is a little open-ended and subjective, but hopefully it's still okay for me to ask on here.

I was contracted by a small business to do a project and as part of it received a database dump of their website. I was appalled when I saw the database had 542 tables in it because every attribute is stored in a separate table.

For example, there are tables like this used for identifying customers:

field_data_field_first_name
field_data_field_last_name
field_data_field_email
field_data_field_phone_number

The field_data_field_first_name table has the following structure:

entity_type
bundle
deleted
entity_id
revision_id
language
delta
field_first_name_value
field_first_name_format

That goes on for a total of 542 tables.

Is structuring a database like this a standard practice?

I'm relatively inexperienced with database design, so while I feel whoever designed this database did a rather poor job, I'd like a second opinion before approaching the company about it.

As an aside, they have an index on every column in every table, which seems bad. It probably goes without saying that the database is incredibly slow. It has an e-commerce portion and it takes between 1-2 minutes to query unfilled orders from the database.


texts: Mongo stores data in files named xxx.0 and xxx.ns under the db folder. Is it possible to copy these files to another machine where I have a mongo instance running and merge them to create a single document? Has anyone done this before? Is there a better way to do this?


texts: mongodb - Replica set creation error: Quorum check failed because not enough voting nodes responded

texts:
I am configuring a mongodb replica set. From my current primary node, when rs.add('host1:27017'), it yields this error

Quorum check failed because not enough voting nodes responded; required 2 but only the following 1 voting nodes responded; the following nodes did not respond affirmatively; failed with Server min and max wire version are incompatible (7,7) with client min wire version (6,6)

On my host1 and host2 machines, I already added replication option with the same replSetName, bind_ip, and exposed firewall. As a proof, through command line mongo --host host1 I can still connect to host1's mongo instance. The telnet command also yields the same successful connection. I don't know how to fix this.


texts:
Possible Duplicate:
  Schema-less/flexible + ACID Database?

I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be offered as a SaaS application for customers in similar trade.

I was looking at database options: My choice was an RDBMS: Postgresql/ MySQL. I might scale up to 400 users in the first year, with typically a 20-40 page views/ per day per user - mostly for transactions not static views. Each view will involve fetch data and update data. ACID compliance is necessary(or so I think). So the transaction volume is not huge.

It would have been a no-brainer to pick either of these based on my preference, but for this one requirement, which I believe is typical of a SaaS app: The Schema will be changing as I add more customers/users and for each customer's changing business requirement (I will be offering some limited flexibility only to start with). As I am not a DB expert, based on what I can think of and has read, I can handle that in a number of ways:

Have a traditional RDBMS schema design in MySQl/Postgresql with a single DB hosting multiple tenants. And add enough "free-floating" columns in each table to allow for future changes as I add more customers or changes for an existing customer. This might have a downside of propagating the changes to the DB every time a small change is made to the Schema. I remember reading that in Postgresql schema updates can be done real time without locking. But not sure, how painful or how practical is it in this use case. And also, as the schema changes might also introduce new/ minor SQL changes as well.
Have an RDBMS, but design the database schema in a flexible manner: with a close to entity-attribute-value or just as a key-value store. (Workday, FriendFeed for example)
Have the entire thing in-memory as objects and store them in log files periodically.(e.g., edval, lmax)
Go for a NoSQL DB like MongoDB or Redis. But based on what I can gather, they are not suitable for this use-case and not fully ACID compliant.
Go for some NewSQL Dbs like VoltDb or JustoneDb(cloud based) which retain the SQL and ACID compliant behaviour and are "new-gen" RDBMS.
I looked at neo4j(graphdb), but not sure if that will fit this use-case

In my use case, more than scalability or distributed computing, I am looking at a better way to achieve "Flexibility in Schema + ACID + some reasonable Performance". Most of the articles I could find on the net speak of flexibility in schema as a cause leading to performance(in the case of NoSQL DBs) and scalability while leaving out the ACID/Transactions side.

Is this an "either or" case of 'Schema flexibility vs ACID' transactions or Is there a better way out?


texts: How to implement 'temporal database' to keep Sales records truthful in POS?

texts:
I'm developing a PHP/MySql retail shop management solution (for the first time!) that keeps records of sales and inventory. I have a problem, which will be better understood by the following chronology of events:
2021-01-19: Shopkeer_Joe purchases 2 chairs from supplier_Tom and enters the record into the software which creates produc_id '1' in the database.
2021-01-20: Shopkeeper_Joe sells both chairs to customer_Alicia, with sale_id, '1' referencing product_id '1' in database.
2021-11-20: (10 months later) With zero chairs sold since customer_Alicia bought the last lot, Shopkeeper_Joe decides to order Desks this time (not chairs) from the same supplier_Tom, But this time, instead of inserting a new record for newly supplied Desks, shopkeeper_Joe, for whatever reasons edits the zero-quantity old chair product entry (product_id '1'), and simply renames the product name from Chair to Desk, thus creating a problem now my software is showing that on 20th-January-2021, Alicia bought two Desks when indeed, she bought two chairs!
My question is how can I ensure that the sale records, in my software stay truthful ??
UPDATE:
I don't want to ban users from editing records because they genuinely may have to correct some mistake in their product record. I am actually trying to research if there is any way, I could keep the sale records truthful while keeping the product edit option open. Maybe by keeping a history of records, like 'temporal database', like querying to get 'what was the name of the product on sale date?' and then displaying 'Original Sale' (as issued on sale date), and 'Modified/edited Sale' because of foreign-key referenced product renaming. BUT I don't know much (anything?) about temporal databases, any help/clue would be appreciated.


texts: Updating values in table based on ranges defined in another table

texts:
I have two tables definitions and history with the following schema:

create table definitions (
    id UNIQUEIDENTIFIER,
    revision INT,
    majorVersion INT
  );

create table history (
   id UNIQUEIDENTIFIER,
   revision INT
   );

I want to update table history with another column majorVersion where the value ranges are defined in table definitions. For example, if records in definitions look like:

+--------------------------------------+-----------+---------------+
|                  id                  |  revision |  majorVersion |
+--------------------------------------+-----------+---------------+
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         2 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         4 |             2 |
+--------------------------------------+-----------+---------------+

Then the updated table history should look something like this:

+--------------------------------------+-----------+---------------+
|                  id                  |  revision |  majorVersion |
+--------------------------------------+-----------+---------------+
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         1 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         2 |             1 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         3 |             2 |
| 9f717823-b9ca-4c7b-97f9-7770aaafb468 |         4 |             2 |
+--------------------------------------+-----------+---------------+

Here is a SQL Fiddle with a more number of rows.
The table definitions can contain potentially thousands of different id and multiple majorVersion. Subsequently, table history can contain can contains close to a million rows.

I want to make the query as fast and optimized as possible. One possible solution is to use something like this:

update history
set majorVersion = (
  select top 1 majorVersion
  from definitions
  where definitions.id = history.id
  and definitions.revision >= history.revision
  order by definitions.majorVersion
  )
where history.majorVersion = 0;

But the problem with this is that we are querying rows for every row in table history (which can be very large compared to definitions). Any suggestions on how to improve upon this?


texts: Why can a partition bound expression not include a casting operation?

texts:
In the Postgres CREATE TABLE docs, creating a partition of a table is described like this:

PARTITION OF parent_table { FOR VALUES partition_bound_spec | DEFAULT }
...
partition_bound_expr is any variable-free expression (subqueries, window functions, aggregate functions, and set-returning functions are not allowed). Its data type must match the data type of the corresponding partition key column. The expression is evaluated once at table creation time...

So why is this a syntax error?
CREATE TEMPORARY TABLE foo
(ts timestamptz)
PARTITION BY RANGE(ts)
;

CREATE TEMPORARY TABLE foo_1970_01_01
PARTITION OF foo
FOR VALUES
  FROM ('1970-01-01 00:00+00:00'::timestamptz)
  TO ('1970-02-01 00:00+00:00'::timestamptz)
;

The error is:
ERROR:  syntax error at or near "::"
LINE n: FROM ('1970-01-01 00:00+00:00'::timestamptz)
                                      ^


texts: Convert the Highvalue of user_partition in varchar2

texts:
I have a requirement where I would like to use the HIGH_VALUE field of user_partition table as a Varchar2 or TimeStamp value and I would like to get it used as a Function in SQL Query.

Something like stated below, but that does not seems to be working currently.

create or replace
function SomeFunction(high_val LONG raw)
return varchar2
is
   lHigh_value     LONG;
   strDate_clause  VARCHAR2(100);
begin
   lHigh_value := high_val;
   strDate_clause := lHigh_value;
   return strDate_clause;
end;

Here are the details of the requirement:

I have an application where I have 1000’s of table and all the table are divided in modules , so for example web module has 100 tables , all having partitioning and there name start with a prefix WEB_USER_5MIN for example.

For my application I would like to delete old partition tables (for example if the partition is 2 days old or 2 months old) based on checking of the HIGH_VALUE field since I can’t take the partition names as they are autogenrated by the system.

I have written the following code which is a procedure which works fine.

FOR key_val,max_no_tbl IN select key,value from tbldataconfig where key like 'MaxNoTables5min%'

FOR x IN (SELECT table_name, partition_name, high_value
FROM user_tab_partitions WHERE table_name =key_val)
LOOP
          IF (TO_DATE (SUBSTR (x.high_value, 11, 10), 'YYYY-MM-DD') < max_no_tbl)
          THEN
          BEGIN
                   lv_drop_part :=
                             'ALTER TABLE '|| x.table_name|| ' DROP PARTITION '||                     x.partition_name|| ' UPDATE GLOBAL INDEXES';
                   DBMS_OUTPUT.put_line ( ' Dropping Partition:'|| x.partition_name|| CHR (10));
END LOOP;

LOOP;

But the problem with the above code is that it is having multiple loops  which can be avoided if I can write the code in the following way.

FOR key_val,max_no_tbl IN select key,value from tbldataconfig where key like 'MaxNoTables5min%'

insert into tbl_marked_for_delete SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE  table_name like ''%' || key_val || '%5min || ''' and somefunction(HIGH_VALUE) < (SYSTIMESTAMP-numtodsinterval(max_no_tbl, 'DAY'))

LOOP

Please suggest how can I write the code in order to get HIGH_VALUE Returned as a TIMESTAMP of VARCHAR2 value inside a function.

There are many number of such procedures.


texts: Use Older Versions Of SQL Server Databases With 2012?

texts:
We have been asked to move from SQL Server 2008 R2 over to 2012, the installation completed a few hours ago. Ever since I've been trying to attach databases from 2008 over to 2012.

It seems to be having no effect. Error messages below:

  Attach database failed for Server 'Development\SQLSERVER'.  (Microsoft.SqlServer.Smo)
  Database 'PS_EmpData' cannot be upgraded because its non-release version (539) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

I mean, this table is quite a big database; and the time it would take to re-create with all the inserts would be unreal. We are still operating with our SQL 2008 R2 for production, but development process is underway on 2012.

My overall question is, do we have to re-create the database to use 2012 or is there a work around?


texts: What are the possible causes for an SSIS package execution having an "ended unexpectedly" status

texts:
I have a SQL Agent job that runs an SSIS 2012 package every 30 seconds to export a small amount of data to a CSV file. There is often nothing to export, but when there is, the business wants it done ASAP.

This has been running fine for months without error, but over the weekend it had 3 failures:

Start Time: 7/25/2015, 11:43:13 PM, End Time: 7/25/2015 11:44:36 PM, Status: Ended Unexpectedly
Start Time: 7/25/2015, 11:52:05 PM, End Time: 7/25/2015 11:53:13 PM, Status: Ended Unexpectedly
Start Time: 7/26/2015,  5:01:31 PM, End Time: 7/25/2015  5:01:31 PM, Status: Failed

In each case, there were no records in internal.operation_messages for the operation_id. The All Executions report also showed no Messages and no entries under Execution Overview. Normal durations for this package are in the 4 - 26 second range. So the Ended Unexpectedly executions had unusually long durations. The Failure execution had a duration of zero. There are no maintenance jobs of any kind running at the time of these failures.

I found this in the Application Event Log that correlates to the time of the Failed execution:

  The operation 414582 (GUID:82f992bf-ed2a-4d82-b25d-92a9d3052cd9)
  terminated because the AppDomain 488 is unloaded. Try restarting the
  execution.

I don't know what this means.

I found instances of this message that correlate to the "ended unexpectedly" executions:

  The SSIS Execution Process could not write to the IS catalog:
  MyInstance:SSISDB Error details: Timeout expired. The timeout period
  elapsed prior to completion of the operation or the server is not
  responding

What are some possible causes for these?


texts: Checking if 2 tables ( on different servers ) have the same exact data

texts:
So it seems that the company who is hosting our SQL Servers has been having some trouble with the replication from the production server to the backup server... I believe some of the tables have been replicating correctly though. Replication is done daily (after hours).

Is there a way I can compare 2 of the same tables, 1 from the backup vs 1 from the production server to see if last nights replication worked?

The only way I could find was to run the following query on both servers and seeing if the result matched which "might" mean that to 2 tables contain the same information.

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
        FROM   (

                    SELECT  *
                    FROM    table_to_compare
               ) t1

using the above code, It seems that the table did replicate successfully as the checksum values are the same but I'm not sure how reliable this method is.

Does anyone know of a better method to check this out or if this is a good way?

I'm running SQL Server 2008 on a windows server 2008 computer.

Thanks.


texts: Postgres CREATE OR REPLACE VIEW... can it block?

texts:
I'm looking for a way for a table to be swapped while it's being used. Say we have a view
CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v1;

that is being used, e.g. lots of SELECT * FROM my_data. Then I ingest data into a new table, my_data_v2, and then run:
CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v2;

Would this block on any SELECT statements that are using the original view definition?


texts: Once more about SQL Server virtual accounts

texts:
Let me reboot my question on SQL Server virtual accounts permissions
The (same) MSDN article on the same version 2012 of SQL Server "Configure Windows Service Accounts and Permissions" states in "File System Permissions Related to Unusual Disk Locations" section:

"Virtual accounts cannot be authenticated to a remote location. All virtual accounts use the permission of machine account. Provision the machine account in the format <domain_name>\<computer_name>$."

If "virtual accounts cannot be authenticated to a remote location" then why:

to use permissions of of a machine account and
in the the format of domain account?

The same article, in "New Account Types Available with Windows 7 and Windows Server 2008 R2" section contains:

" Virtual Accounts
Virtual accounts in Windows Server 2008 R2 and Windows 7 are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. If the default value is used for the service accounts during SQL Server setup on Windows Server 2008 R2 or Windows 7, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$"

How should I better understand accessing the network without authentication ?


texts: How can I tell if a SQL Server database is still being used?

texts:
We're looking to decommission a SQL Server instance which has a couple databases still remaining on it.

How can I tell if they are still being used by users or a web application?

I found a forum thread which had a T-SQL query you could run to retrieve the last query date. It seems to work but I want to know if this information is valid enough to drop databases. Is it?

If you have alternative methods that would help as well.


texts: How to sync incomplete replication records in MySQL

texts:
if replication breaks and you end up with a couple of records not pushed out to the slave, is there a way to update the slave without stopping locks on the master and manually transferring a new master copy to the slave?

I am very familiar with this process  fix replication   but I am writing a script that must include a method of resyncing disk data in the event of replication failure (a self repairing script)
I am trying to avoid implementing database locks and mysqldump via Python, if possible.

What is a more seemless method to syncing incomplete data following replication failure, one that would be script friendly?


texts:
My team and I are designing a database for a barber shop. The main focus of the project is to allow customers to schedule appointments with barbers for some sort of hair service.

Our problem is that our professor is telling us to connect two relationships to each other and when we ask why he doesn't really do a good job of explaining why they need to connect. We told our professor that the book forbids the connection of two relationships to each other but he said "the book is wrong". The two relationships that our professor wants us to connect are performs to includes. My team and I think it would be better to connect performs to appointment. Does anyone understand why we would connect those two relationships to each other?

Diagram:


texts: sp_databaserestore: "stopat" gives error "no database backup found"

texts:
Unfortunately I get:

  “No backups for “database xxx” found in “\xxx\xxx$\…”

as soon as I add the @StopAt = ‘2019-03-27T04:05:20 Parameter.

When I comment this Parameter, everything works fine, except that only the 1st logfile after the full backup gets restored – and not all log Backups in the same backup Directory.

Also tried with the date Format in the scripts example: @StopAt = 20192703040520, but I get same error. What am I doing wrong ? Name of Backup files (trn) is like this: tools_backup_2019_03_27_110112_9746376.trn

added 28032019:

First of all thanks for your answers so far !!!

SQL Server version:  2016 standard (13.0.5216.0)

@StopAt = '20190328100000', (set to correct format ? server default language is: german !

this query on same  server:

SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
    ,GETUTCDATE();

gives this result:

2019-03-28 16:10:22.8098949
2019-03-28 16:10:22.8098949 +01:00
2019-03-28 15:10:22.8098949
2019-03-28 16:10:22.807
2019-03-28 16:10:22.807
2019-03-28 15:10:22.807

My goal is to restore "point in time" to march, 28th, 10 am

exact statement:

EXEC dbo.sp_DatabaseRestore
        @Database = 'tools',
        @BackupPathFull = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\',
        @BackupPathDiff = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\',
        @BackupPathLog = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\',
        @RestoreDiff = 0,
        @ContinueLogs = 1,
        @RunRecovery = 1,
        @TestRestore = 0,
        @StopAt = '20190328100000',
        @RunCheckDB = 0,
        @Debug = 1,
        @help = 0,
        @Execute = 'N';

...changing setting for @ContinueLogs, @RunRecovery or @Execute doesn't change anything, error (still):

  Nachricht 50000, Stufe 16, Status 1, Prozedur sp_DatabaseRestore,
  Zeile 486 [Batchstartzeile 0] No backups for "tools" found in
  "\dicvm.de\backupsql$\SV10RXVDB24\tools\"

files in @BackupPathFull directory are as follows (full- and log-Backups only, no diff Backups):

Verzeichnis von \\dicvm.de\backupsql$\SV10RXVDB24\tools

28.03.2019  11:01    <DIR>          .
28.03.2019  11:01    <DIR>          ..
27.03.2019  05:01            14.848 tools_backup_2019_03_27_050103_2371617.trn
27.03.2019  11:01            13.824 tools_backup_2019_03_27_110112_9746376.trn
27.03.2019  17:01             9.728 tools_backup_2019_03_27_170102_2790723.trn
27.03.2019  22:01         1.297.920 tools_backup_2019_03_27_220002_2539287.bak
27.03.2019  23:01            14.336 tools_backup_2019_03_27_230102_5656503.trn
28.03.2019  05:01            13.312 tools_backup_2019_03_28_050103_8892863.trn
28.03.2019  11:01            12.800 tools_backup_2019_03_28_110103_0664649.trn

@laughingVergil: So I assume there is a full backup  - earlier than the @StopAt parameter's time as of 27.03.2019  22:01 - in the target directory (?)
And: Yes: Your assumption is right: I am using Brent Ozar's First Responder Kit in the newest version.


texts:
I have the below Query and I want the Ageing profile to show as a new column
my ageing profile is 0-30,  31-60, 61-90, 91-120, 121-180, 181-365, 365+

I basically want the results to appear lie this with the Net due appearing in the appropriate ageing bracket.

BU   0-30   31-60   61-90   91-120   121-180   181-365   365+
--------------------------------------------------------------
A
B
C

Query:

select
 BU,
 Ageing,
 sum(NetDue) as Netdue
 from [dbo].[vw_FACT_CONSOL_CREDITORS]
 where date = @date
 Group by BU, Ageing
Order by BU ;

I am new to SQL and just trying to learn so apologise as this is basic stuff
Thanks


texts:
Sorry, I'm not a expert in scripting,

I need to create a query in MS SQL Server to get db and db log size on one row. I am using PRTG to monitor 4 SQL servers and i need to know if there's any growth in the database and logs, eater in percentage or in bites,

I have found this new script that is perfect, it gives me all the info i need then i can set thresholds on the outputs,  the problem is PRTG can only read the info form one row, so the only info that shows up in PRTG is the database info, i also need to get the log size.

I have found this script that gives me all the info i need and i would like to send the output to one row if possible.

USE [maccadk]
GO
SELECT
    [TYPE] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;

Thanks
Gilles


texts: Save performance with large update on Index with Included Column

texts:
Points
------------------
PK  QuestionId  int (+30.000.000 distinct values)
PK  EventId     int (large batches where 80.000 rows have the same EventId)
    Value       smallint

The table is about 40 million rows and has performance issues.
There are two main queries:
On QuestionId:

about 30 million different QuestionId values (lot of variation)
a lot of queries during busy times (multiple thousands per minute)

On EventId:

there will be batch updates of +150.000 rows where EventId=X to set the Value=NULL during very busy moments.

So my first thought to get the best performance was that I make EventId,QuestionId the ClusteredIndex so the batch update can easily find all of EventId close to eachother and directly update the values.
My second thought was adding an Index with QuestionId and included column Value so it can read the value directly from the Index (EventId is irrelevant in this case).
But then I thought: would the Clustered Index even matter? As the Included column Value from the Index also needs be updated during the batch update.

While not trading off on query performance - is it possible to get the batch update fast (few seconds) or do I have to accept this process will always be slow without upgrading hardware.
Any other thoughts what would be the best way to set the ClusteredIndex / Indexes?

I know in theory I should test everything out and measure it, but the site is live and used heavily.
I'm a solo developer and I don't have the resources to hire someone. Any estimated guesses and thoughts about this would be really helpful as this gives me already the right direction!


texts:
i'm trying to create a procedure with filters for search.

If the parameter is "NULL" the value of parameter will be column value;

Here my problem: When my parameter and column value is NULL.

I have no results because of this.

I know that query: "COLUMN_NAME IS NULL" return the result even if the value is NULL, but i don't know how to implement this.

I trying to do this with "CASE" but without success.

CREATE PROCEDURE SEARCH_QUERYS
        @ID   int,
        @LIVE bit,
        @DATA datetime
AS
BEGIN
        SELECT * FROM TB_SEARCH
        WHERE
        ID = ISNULL(@ID,ID) AND
        LIVE = ISNULL(@LIVE,LIVE) AND
        DATA = ISNULL(@DATA,DATA)
END


texts: length of the result exceeds the > length limit (2GB) of the target large type

texts:
Getting the following error message on the Sql Server Error Log:

  SQLServer Error: 599, WRITE: The length of the result exceeds the
  length limit (2GB) of the target large type. [SQLSTATE 42000]
  (LogToTableWrite)

How can I find what job is causing this?